Posting a very intresting topic on Oracle 11G. Initially I had some issues like ORA-01034, ORA-12528, RMAN-06217, ORA-00845 errors.Unfortunately I was not able to get much detailed information on how to implement this from google. It look me 2 hours to implement - the setup is the same as any Normal Dataguard (Oracle 10G) implementation. No major difference.
This feature is called Real-time Query database - that there is no suspension of Redo Apply. It uses incremental backup feature based on a change-tracking file. I mean like keeping track of all changed blocks through bitmaps. So much better performance. I would say that this should benefit a lot during the real time processing.
Oracle 11G performance improvement (Dataguard specific)
-------------------------------------------------------
1. Faster Failover - Failover in seconds with Fast-Start Failover.
2. Faster Redo Transport.
Optimized async transport for Maximum Performance Mode
Redo Transport Compression for gap fetching:new compression attribute for
log_archive_dest_n.
3. Faster Redo Apply
Parallel media recovery optimization.
4. Faster SQL Apply
Internal optimizations.
5. Fast incremental backup on physical standby database
Support for block change tracking.
(This Picture is about FAST FAILOVER - THIS SETUP NEEDs A THIRD NODE CALLED, SUBSCRIBER (MONITOR) AND DG BROKER SETUP. I will cover this topic soon). Oracle uses the same technology as what Microsoft does with SQL Server 2005, called Mirroring feature here ...!
Most important Prerequisites are
-------------------------------
1. Both the target and destination databases must be on an identical operating system platform.
2. Oracle Net must be aware of both the target and duplicate instances.
3. Both the target and destination databases must have the same sysdba password.
4. The target database must be open or in mount state.
5. If the target database is open, it must be in archivelog mode
Prepare your environments - both primary and secondary databases.This is the basic configuration like any standby setup. So I am skipping this.
Once everything is setup,
starup the primary database (in this case DG database). The primary can be either in mounted or open stage.
Start the standby instance (not mounted).
In pre 11G we used to create the standby control file and copy over it to the standby site. We also copy the datafiles to the standby site.
But in 11G this steps are not needed. Everything is automatic and so this process is called "Database Active Network duplicate" process.
Demo
----
DG - Primary Database
DG1 - Standby Database
From primary database - DG
--------------------------
SQL> alter database force logging;
Database altered.
SQL> select force_logging from v$database;
FOR
---
YES
Active database (network) duplicate (RMAN) command
--------------------------------------------------
RMAN> duplicate target database for standby
2> db_file_name_convert '/dg/','/dg1/'
3> DORECOVER FROM ACTIVE DATABASE
4> spfile
5> parameter_value_convert '/dg/','/dg1/'
6> set log_file_name_convert '/dg/','/dg1/'
7> set fal_client='dgstdby'
8> set fal_server='dg'
9> set log_archive_dest_1='LOCATION=/u01/oracle/dg1/ VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=dg1'
10> set log_archive_dest_2='SERVICE=dg LGWR ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=dg'
11> set standby_archive_dest='/u01/oracle/oradata/dg1'
12> set db_unique_name='dg1';
13>
Starting Duplicate Db at 12-AUG-09
allocated channel: ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: SID=153 device type=DISK
contents of Memory Script:
{
backup as copy reuse
file '/u01/app/oracle/oracle11g/dbs/orapwdg' auxiliary format
'/u01/app/oracle/oracle11g/dbs/orapwdg1' file
'/u01/app/oracle/oracle11g/dbs/spfiledg.ora' auxiliary format
'/u01/app/oracle/oracle11g/dbs/spfiledg1.ora' ;
sql clone "alter system set spfile= ''/u01/app/oracle/oracle11g/dbs/spfiledg1.ora''";
}
executing Memory Script
Starting backup at 12-AUG-09
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=133 device type=DISK
Finished backup at 12-AUG-09
sql statement: alter system set spfile= ''/u01/app/oracle/oracle11g/dbs/spfiledg1.ora''
contents of Memory Script:
{
sql clone "alter system set audit_file_dest =
''/u01/app/oracle/admin/dg1/adump'' comment=
'''' scope=spfile";
sql clone "alter system set control_files =
''/u01/oracle/oradata/dg1/control01.ctl'', ''/u01/oracle/oradata/dg1/control02.ctl'', ''/u01/oracle/oradata/dg1/control03.ctl'' comment=
'''' scope=spfile";
sql clone "alter system set log_file_name_convert =
''/dg/'', ''/dg1/'' comment=
'''' scope=spfile";
sql clone "alter system set fal_client =
''dgstdby'' comment=
'''' scope=spfile";
sql clone "alter system set fal_server =
''dg'' comment=
'''' scope=spfile";
sql clone "alter system set log_archive_dest_1 =
''LOCATION=/u01/oracle/dg1/ VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=dg1'' comment=
'''' scope=spfile";
sql clone "alter system set log_archive_dest_2 =
''SERVICE=dg LGWR ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=dg'' comment=
'''' scope=spfile";
sql clone "alter system set standby_archive_dest =
''/u01/oracle/oradata/dg1'' comment=
'''' scope=spfile";
sql clone "alter system set db_unique_name =
''dg1'' comment=
'''' scope=spfile";
shutdown clone immediate;
startup clone nomount ;
}
executing Memory Script
sql statement: alter system set audit_file_dest = ''/u01/app/oracle/admin/dg1/adump'' comment= '''' scope=spfile
sql statement: alter system set control_files = ''/u01/oracle/oradata/dg1/control01.ctl'', ''/u01/oracle/oradata/dg1/control02.ctl'', ''/u01/oracle/oradata/dg1/control03.ctl'' comment= '''' scope=spfile
sql statement: alter system set log_file_name_convert = ''/dg/'', ''/dg1/'' comment= '''' scope=spfile
sql statement: alter system set fal_client = ''dgstdby'' comment= '''' scope=spfile
sql statement: alter system set fal_server = ''dg'' comment= '''' scope=spfile
sql statement: alter system set log_archive_dest_1 = ''LOCATION=/u01/oracle/dg1/ VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=dg1'' comment= '''' scope=spfile
sql statement: alter system set log_archive_dest_2 = ''SERVICE=dg LGWR ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=dg'' comment= '''' scope=spfile
sql statement: alter system set standby_archive_dest = ''/u01/oracle/oradata/dg1'' comment= '''' scope=spfile
sql statement: alter system set db_unique_name = ''dg1'' comment= '''' scope=spfile
Oracle instance shut down
connected to auxiliary database (not started)
Oracle instance started
Total System Global Area 1255473152 bytes
Fixed Size 1299624 bytes
Variable Size 721423192 bytes
Database Buffers 520093696 bytes
Redo Buffers 12656640 bytes
contents of Memory Script:
{
backup as copy current controlfile for standby auxiliary format '/u01/oracle/oradata/dg1/control01.ctl';
restore clone controlfile to '/u01/oracle/oradata/dg1/control02.ctl' from
'/u01/oracle/oradata/dg1/control01.ctl';
restore clone controlfile to '/u01/oracle/oradata/dg1/control03.ctl' from
'/u01/oracle/oradata/dg1/control01.ctl';
sql clone 'alter database mount standby database';
}
executing Memory Script
Starting backup at 12-AUG-09
using channel ORA_DISK_1
channel ORA_DISK_1: starting datafile copy
copying standby control file
output file name=/u01/app/oracle/oracle11g/dbs/snapcf_dg.f tag=TAG20090812T030007 RECID=1 STAMP=694666809
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:01
Finished backup at 12-AUG-09
Starting restore at 12-AUG-09
allocated channel: ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: SID=154 device type=DISK
channel clone_default: skipped, AUTOBACKUP already found
channel ORA_AUX_DISK_1: skipped, AUTOBACKUP already found
channel ORA_DISK_1: copied control file copy
Finished restore at 12-AUG-09
Starting restore at 12-AUG-09
using channel ORA_AUX_DISK_1
channel clone_default: skipped, AUTOBACKUP already found
channel ORA_AUX_DISK_1: skipped, AUTOBACKUP already found
channel ORA_DISK_1: copied control file copy
Finished restore at 12-AUG-09
sql statement: alter database mount standby database
contents of Memory Script:
{
set newname for tempfile 1 to
"/u01/oracle/oradata/dg1/temp01.dbf";
switch clone tempfile all;
set newname for datafile 1 to
"/u01/oracle/oradata/dg1/system01.dbf";
set newname for datafile 2 to
"/u01/oracle/oradata/dg1/sysaux01.dbf";
set newname for datafile 3 to
"/u01/oracle/oradata/dg1/undotbs01.dbf";
set newname for datafile 4 to
"/u01/oracle/oradata/dg1/users01.dbf";
backup as copy reuse
datafile 1 auxiliary format
"/u01/oracle/oradata/dg1/system01.dbf" datafile
2 auxiliary format
"/u01/oracle/oradata/dg1/sysaux01.dbf" datafile
3 auxiliary format
"/u01/oracle/oradata/dg1/undotbs01.dbf" datafile
4 auxiliary format
"/u01/oracle/oradata/dg1/users01.dbf" ;
sql 'alter system archive log current';
}
executing Memory Script
executing command: SET NEWNAME
renamed tempfile 1 to /u01/oracle/oradata/dg1/temp01.dbf in control file
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
Starting backup at 12-AUG-09
using channel ORA_DISK_1
channel ORA_DISK_1: starting datafile copy
input datafile file number=00001 name=/u01/oracle/oradata/dg/system01.dbf
output file name=/u01/oracle/oradata/dg1/system01.dbf tag=TAG20090812T030032 RECID=0 STAMP=0
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:45
channel ORA_DISK_1: starting datafile copy
input datafile file number=00002 name=/u01/oracle/oradata/dg/sysaux01.dbf
output file name=/u01/oracle/oradata/dg1/sysaux01.dbf tag=TAG20090812T030032 RECID=0 STAMP=0
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:35
channel ORA_DISK_1: starting datafile copy
input datafile file number=00003 name=/u01/oracle/oradata/dg/undotbs01.dbf
output file name=/u01/oracle/oradata/dg1/undotbs01.dbf tag=TAG20090812T030032 RECID=0 STAMP=0
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:03
channel ORA_DISK_1: starting datafile copy
input datafile file number=00004 name=/u01/oracle/oradata/dg/users01.dbf
output file name=/u01/oracle/oradata/dg1/users01.dbf tag=TAG20090812T030032 RECID=0 STAMP=0
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:03
Finished backup at 12-AUG-09
sql statement: alter system archive log current
contents of Memory Script:
{
backup as copy reuse
archivelog like "/u01/oracle/arch/1_4_694653014.dbf" auxiliary format
"/u01/oracle/dg1/1_4_694653014.dbf" ;
catalog clone archivelog "/u01/oracle/dg1/1_4_694653014.dbf";
switch clone datafile all;
}
executing Memory Script
Starting backup at 12-AUG-09
using channel ORA_DISK_1
channel ORA_DISK_1: starting archived log copy
input archived log thread=1 sequence=4 RECID=3 STAMP=694666920
output file name=/u01/oracle/dg1/1_4_694653014.dbf RECID=0 STAMP=0
channel ORA_DISK_1: archived log copy complete, elapsed time: 00:00:01
Finished backup at 12-AUG-09
cataloged archived log
archived log file name=/u01/oracle/dg1/1_4_694653014.dbf RECID=1 STAMP=694666922
datafile 1 switched to datafile copy
input datafile copy RECID=1 STAMP=694666922 file name=/u01/oracle/oradata/dg1/system01.dbf
datafile 2 switched to datafile copy
input datafile copy RECID=2 STAMP=694666922 file name=/u01/oracle/oradata/dg1/sysaux01.dbf
datafile 3 switched to datafile copy
input datafile copy RECID=3 STAMP=694666922 file name=/u01/oracle/oradata/dg1/undotbs01.dbf
datafile 4 switched to datafile copy
input datafile copy RECID=4 STAMP=694666922 file name=/u01/oracle/oradata/dg1/users01.dbf
contents of Memory Script:
{
set until scn 585861;
recover
standby
clone database
delete archivelog
;
}
executing Memory Script
executing command: SET until clause
Starting recover at 12-AUG-09
using channel ORA_AUX_DISK_1
starting media recovery
archived log for thread 1 with sequence 4 is already on disk as file /u01/oracle/dg1/1_4_694653014.dbf
archived log file name=/u01/oracle/dg1/1_4_694653014.dbf thread=1 sequence=4
media recovery complete, elapsed time: 00:00:00
Finished recover at 12-AUG-09
Finished Duplicate Db at 12-AUG-09
Recovery Manager complete.
[oracle@Grid1 oradata]$ sqlplus /nolog
SQL*Plus: Release 11.1.0.6.0 - Production on Wed Aug 12 03:03:05 2009
Copyright (c) 1982, 2007, Oracle. All rights reserved.
SQL> connect sys/sys2@dg1 as sysdba
Connected.
SQL> select status from v$instance;
STATUS
------------
MOUNTED
SQL> exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.1.0.6.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
[oracle@Grid1 oradata]$ ps -ef|grep pmon
oracle 12811 1 0 02:57 ? 00:00:00 ora_pmon_dg
oracle 13649 1 0 03:00 ? 00:00:00 ora_pmon_dg1
oracle 15014 7640 0 03:03 pts/4 00:00:00 grep pmon
[oracle@Grid1 oradata]$ sqlplus /nolog
SQL*Plus: Release 11.1.0.6.0 - Production on Wed Aug 12 03:03:44 2009
Copyright (c) 1982, 2007, Oracle. All rights reserved.
SQL> connect / as sysdba
Connected to an idle instance.
SQL> connect sys/sys2@dg1 as sysdba
Connected.
SQL> select status from v$instance;
STATUS
------------
MOUNTED
Active DataGuard Process
------------------------
SQL> alter database open;
Database altered.
SQL> select DATABASE_ROLE,OPEN_MODE,PROTECTION_MODE from v$database;
DATABASE_ROLE OPEN_MODE PROTECTION_MODE
---------------- ---------- --------------------
PRIMARY READ WRITE MAXIMUM PERFORMANCE
SQL> SELECT PROCESS, STATUS, THREAD#, SEQUENCE#, BLOCK#, BLOCKS FROM V$MANAGED_STANDBY;
PROCESS STATUS THREAD# SEQUENCE# BLOCK# BLOCKS
--------- ------------ ---------- ---------- ---------- ----------
ARCH CONNECTED 0 0 0 0
ARCH CONNECTED 0 0 0 0
ARCH CONNECTED 0 0 0 0
ARCH CONNECTED 0 0 0 0
SQL> alter database close;
alter database open
*
ERROR at line 1:
ORA-01154: database busy. Open, close, mount, and dismount not allowed now
SQL> SELECT PROCESS, STATUS, THREAD#, SEQUENCE#, BLOCK#, BLOCKS FROM V$MANAGED_STANDBY;
PROCESS STATUS THREAD# SEQUENCE# BLOCK# BLOCKS
--------- ------------ ---------- ---------- ---------- ----------
ARCH CONNECTED 0 0 0 0
ARCH CONNECTED 0 0 0 0
ARCH CONNECTED 0 0 0 0
ARCH CONNECTED 0 0 0 0
MRP0 WAIT_FOR_LOG 1 5 0 0
SQL> archive log list
Database log mode Archive Mode
Automatic archival Enabled
Archive destination /u01/oracle/dg1/
Oldest online log sequence 0
Next log sequence to archive 0
Current log sequence 0
SQL> archive log list
Database log mode Archive Mode
Automatic archival Enabled
Archive destination /u01/oracle/dg1/
Oldest online log sequence 13
Next log sequence to archive 0
Current log sequence 15
SQL> SELECT PROCESS, STATUS, THREAD#, SEQUENCE#, BLOCK#, BLOCKS FROM V$MANAGED_STANDBY;
PROCESS STATUS THREAD# SEQUENCE# BLOCK# BLOCKS
--------- ------------ ---------- ---------- ---------- ----------
ARCH CLOSING 1 13 1 2
ARCH CLOSING 1 14 1 2
ARCH CONNECTED 0 0 0 0
ARCH CLOSING 1 12 1 267
MRP0 WAIT_FOR_LOG 1 15 0 0
RFS IDLE 0 0 0 0
RFS IDLE 0 0 0 0
RFS IDLE 0 0 0 0
RFS IDLE 1 15 3 2
9 rows selected.
SQL> select DATABASE_ROLE,OPEN_MODE,PROTECTION_MODE from v$database;
DATABASE_ROLE OPEN_MODE PROTECTION_MODE
---------------- ---------- --------------------
PHYSICAL STANDBY READ ONLY MAXIMUM PERFORMANCE
SQL> select status from v$instance;
STATUS
------------
OPEN
From Node 1 -DG
---------------
SQL> select DATABASE_ROLE,OPEN_MODE,PROTECTION_MODE from v$database;
DATABASE_ROLE OPEN_MODE PROTECTION_MODE
---------------- ---------- --------------------
PRIMARY READ WRITE MAXIMUM PERFORMANCE
SQL> select name from v$database;
NAME
---------
DG
SQL> archive log list
Database log mode Archive Mode
Automatic archival Enabled
Archive destination /u01/oracle/arch
Oldest online log sequence 13
Next log sequence to archive 15
Current log sequence 15
SQL> create table test2_dg as select * from dba_objects;
Table created.
SQL> alter system switch logfile;
System altered.
From Node 2 - DG1
-----------------
SQL> archive log list
Database log mode Archive Mode
Automatic archival Enabled
Archive destination /u01/oracle/dg1/
Oldest online log sequence 13
Next log sequence to archive 0
Current log sequence 15
SQL> SELECT PROCESS, STATUS, THREAD#, SEQUENCE#, BLOCK#, BLOCKS FROM V$MANAGED_STANDBY;
PROCESS STATUS THREAD# SEQUENCE# BLOCK# BLOCKS
--------- ------------ ---------- ---------- ---------- ----------
ARCH CLOSING 1 13 1 2
ARCH CLOSING 1 14 1 2
ARCH CONNECTED 0 0 0 0
ARCH CLOSING 1 12 1 267
MRP0 WAIT_FOR_LOG 1 15 0 0
RFS IDLE 0 0 0 0
RFS IDLE 0 0 0 0
RFS IDLE 0 0 0 0
RFS IDLE 1 15 3 2
9 rows selected.
SQL> desc test2_dg
ERROR:
ORA-04043: object test2_dg does not exist
SQL> archive log list
Database log mode Archive Mode
Automatic archival Enabled
Archive destination /u01/oracle/dg1/
Oldest online log sequence 13
Next log sequence to archive 0
Current log sequence 15
SQL> archive log list
Database log mode Archive Mode
Automatic archival Enabled
Archive destination /u01/oracle/dg1/
Oldest online log sequence 14
Next log sequence to archive 0
Current log sequence 16
SQL> desc test2_dg
Name Null? Type
----------------------------------------- -------- ----------------------------
OWNER VARCHAR2(30)
OBJECT_NAME VARCHAR2(128)
SUBOBJECT_NAME VARCHAR2(30)
OBJECT_ID NUMBER
DATA_OBJECT_ID NUMBER
OBJECT_TYPE VARCHAR2(19)
CREATED DATE
LAST_DDL_TIME DATE
TIMESTAMP VARCHAR2(19)
STATUS VARCHAR2(7)
TEMPORARY VARCHAR2(1)
GENERATED VARCHAR2(1)
SECONDARY VARCHAR2(1)
NAMESPACE NUMBER
EDITION_NAME VARCHAR2(30)
Try to create some objects from DG1 - standby database
-----------------------------------------------------
SQL> create table tet as select * from dba_objects;
create table tet as select * from dba_objects
*
ERROR at line 1:
ORA-00604: error occurred at recursive SQL level 1
ORA-16000: database open for read-only access
SQL> select DATABASE_ROLE, open_mode from v$database;
DATABASE_ROLE OPEN_MODE
---------------- ----------
PHYSICAL STANDBY READ ONLY
EVENT TOTAL_WAITS TOTAL_TIMEOUTS TIME_WAITED AVERAGE_WAIT
---------------------------------------------------------------- ----------- -------------- ----------- ------------
LNS ASYNC dest activation 1 1 100 99.95
LNS ASYNC end of log 5126 4961 500456 97.63
LNS wait on ATTACH 2 0 60 30.11
LNS wait on SENDREQ 179 0 240 1.34
LNS wait on DETACH 4 0 0 0
LNS wait on LGWR 2 0 0 0
LGWR-LNS wait on channel 539 539 602 1.12
LGWR wait for redo copy 104 0 2 .02
8 rows selected.
Hope this helps...
I will cover the Switchover and failover later.
No comments:
Post a Comment