Table of contents
Preamble
Even if you can manage your Data Guard configuration with SQL commands it is strongly suggested to use Data Guard Broker (DGB) as its command line tool dgmgrl ease a lot day-to-day administration and monitoring.
The product is apparently a bit buggy in 10g but except few remaining *strange* things product is now quite efficient in 11gR2.
This article is the follow-up of previous post on creating a physical standby database.
Please note that once you have decided to start using DGB you cannot use anymore SQL commands as DGB takes preference.
Data Guard Broker configuration
On primary and all your standby database set dg_broker_start parameter to true:
SQL> show parameter dg NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ dg_broker_config_file1 string /ora_rac/software/dbs/dr1rac1. dat dg_broker_config_file2 string /ora_rac/software/dbs/dr2rac1. dat dg_broker_start BOOLEAN FALSE SQL> ALTER SYSTEM SET dg_broker_start=TRUE; SYSTEM altered. |
One of the most *strange* requirement for Data Guard Broker is the need to have all listener listening for a global database name made of <db_name>_DGMGRL.<db_domain> which means for our environment the following listener.ora file (do crossover configuration of all your standby instances for failover and switchover capabilities):
LISTENER_RAC1=(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=IPC)(KEY=LISTENER_RAC1)))) ENABLE_GLOBAL_DYNAMIC_ENDPOINT_LISTENER_RAC1=ON SID_LIST_LISTENER_RAC1 = (SID_LIST = (SID_DESC = (GLOBAL_DBNAME = rac1.world) (ORACLE_HOME = /ora_rac/software) (SID_NAME = rac1) ) (SID_DESC = (GLOBAL_DBNAME = rac1_DGMGRL.world) (ORACLE_HOME = /ora_rac/software) (SID_NAME = rac1) ) ) |
[orarac@server1 ~]$ lsnrctl status listener_rac1 LSNRCTL for Linux: Version 11.2.0.2.0 - Production on 26-MAY-2011 16:10:22 Copyright (c) 1991, 2010, Oracle. All rights reserved. Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=LISTENER_RAC1))) STATUS of the LISTENER ------------------------ Alias LISTENER_RAC1 Version TNSLSNR for Linux: Version 11.2.0.2.0 - Production Start Date 26-MAY-2011 14:12:44 Uptime 0 days 1 hr. 57 min. 37 sec Trace Level off Security ON: Local OS Authentication SNMP OFF Listener Parameter File /ora_rac/software/network/admin/listener.ora Listener Log File /ora_rac/software/log/diag/tnslsnr/server1/listener_rac1/alert/log.xml Listening Endpoints Summary... (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=LISTENER_RAC1))) (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=192.168.56.101)(PORT=1541))) Services Summary... Service "rac1.world" has 2 instance(s). Instance "rac1", status UNKNOWN, has 1 handler(s) for this service... Instance "rac1", status READY, has 1 handler(s) for this service... Service "rac1_DGMGRL.world" has 1 instance(s). Instance "rac1", status UNKNOWN, has 1 handler(s) for this service... The command completed successfully |
Enter is Data Guard Broker command line interface and create the configuration:
[orarac@server1 admin]$ dgmgrl DGMGRL for Linux: Version 11.2.0.2.0 - 64bit Production Copyright (c) 2000, 2009, Oracle. All rights reserved. Welcome to DGMGRL, type "help" for information. DGMGRL> connect sys/password Connected. DGMGRL> create configuration 'DGConfig1' as primary database is 'rac1' connect identifier is rac1; Configuration "DGConfig1" created with primary database "rac1" DGMGRL> show configuration Configuration - DGConfig1 Protection Mode: MaxPerformance Databases: rac1 - Primary database Fast-Start Failover: DISABLED Configuration Status: DISABLED DGMGRL> add database 'rac2' as connect identifier is rac2; Database "rac2" added DGMGRL> show configuration Configuration - DGConfig1 Protection Mode: MaxPerformance Databases: rac1 - Primary database rac2 - Physical standby database Fast-Start Failover: DISABLED Configuration Status: DISABLED DGMGRL> enable configuration Enabled. DGMGRL> show configuration Configuration - DGConfig1 Protection Mode: MaxPerformance Databases: rac1 - Primary database rac2 - Physical standby database Warning: ORA-16792: configurable property value is inconsistent with database setting Fast-Start Failover: DISABLED Configuration Status: WARNING |
If you get few inconsistencies, no issue, correct them with:
DGMGRL> show database 'rac2' InconsistentProperties; INCONSISTENT PROPERTIES INSTANCE_NAME PROPERTY_NAME MEMORY_VALUE SPFILE_VALUE BROKER_VALUE rac2 ArchiveLagTarget 0 0 rac2 LogArchiveMaxProcesses 4 4 rac2 LogArchiveMinSucceedDest 1 1 rac2 LogArchiveTrace 0 (missing) 0 rac2 LogArchiveFormat %t_%s_%r.dbf (missing) %t_%s_%r.dbf DGMGRL> edit database 'rac2' set property 'ArchiveLagTarget'=0; Property "ArchiveLagTarget" updated DGMGRL> edit database 'rac2' set property 'LogArchiveMaxProcesses'=4; Property "LogArchiveMaxProcesses" updated DGMGRL> edit database 'rac2' set property 'LogArchiveMinSucceedDest'=1; Property "LogArchiveMinSucceedDest" updated DGMGRL> edit database 'rac2' set property 'LogArchiveTrace'=0; Property "LogArchiveTrace" updated DGMGRL> edit database 'rac2' set property 'LogArchiveFormat'='%t_%s_%r.dbf'; Property "LogArchiveFormat" updated DGMGRL> show database 'rac2' InconsistentProperties; INCONSISTENT PROPERTIES INSTANCE_NAME PROPERTY_NAME MEMORY_VALUE SPFILE_VALUE BROKER_VALUE |
But if all went fine when creating physical standby database you should directly get the below configuration:
DGMGRL> show configuration Configuration - DGConfig1 Protection MODE: MaxPerformance Databases: rac1 - PRIMARY DATABASE rac2 - Physical standby DATABASE Fast-START Failover: DISABLED Configuration Status: SUCCESS DGMGRL> |
To get detailed status of a database:
DGMGRL> show database verbose rac2; Database - rac2 Role: PHYSICAL STANDBY Intended State: APPLY-ON Transport Lag: 0 seconds Apply Lag: 0 seconds Real Time Query: OFF Instance(s): rac2 Properties: DGConnectIdentifier = 'rac2' ObserverConnectIdentifier = '' LogXptMode = 'ASYNC' DelayMins = '0' Binding = 'OPTIONAL' MaxFailure = '0' MaxConnections = '1' ReopenSecs = '300' NetTimeout = '30' RedoCompression = 'DISABLE' LogShipping = 'ON' PreferredApplyInstance = '' ApplyInstanceTimeout = '0' ApplyParallel = 'AUTO' StandbyFileManagement = 'auto' ArchiveLagTarget = '0' LogArchiveMaxProcesses = '4' LogArchiveMinSucceedDest = '1' DbFileNameConvert = '' LogFileNameConvert = '' FastStartFailoverTarget = '' InconsistentProperties = '(monitor)' InconsistentLogXptProps = '(monitor)' SendQEntries = '(monitor)' LogXptStatus = '(monitor)' RecvQEntries = '(monitor)' SidName = 'rac2' StaticConnectIdentifier = '(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=server1.domain.com)(PORT=1541))(CONNECT_DATA=(SERVICE_NAME=rac2_DGMGRL.world)(INSTANCE_NAME=rac2)(SERVER=DEDICATED)))' StandbyArchiveLocation = 'USE_DB_RECOVERY_FILE_DEST' AlternateLocation = '' LogArchiveTrace = '0' LogArchiveFormat = '%t_%s_%r.dbf' TopWaitEvents = '(monitor)' Database Status: SUCCESS |
Remark:
Strangely one service is added to your listeners configuration i.e. <db_name>_DGB.<db_domain> service is added (Oracle preparing next step ?):
[orarac@server1 ~]$ lsnrctl status listener_rac1 LSNRCTL for Linux: Version 11.2.0.2.0 - Production on 26-MAY-2011 16:17:12 Copyright (c) 1991, 2010, Oracle. All rights reserved. Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=LISTENER_RAC1))) STATUS of the LISTENER ------------------------ Alias LISTENER_RAC1 Version TNSLSNR for Linux: Version 11.2.0.2.0 - Production Start Date 26-MAY-2011 14:12:44 Uptime 0 days 2 hr. 4 min. 27 sec Trace Level off Security ON: Local OS Authentication SNMP OFF Listener Parameter File /ora_rac/software/network/admin/listener.ora Listener Log File /ora_rac/software/log/diag/tnslsnr/server1/listener_rac1/alert/log.xml Listening Endpoints Summary... (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=LISTENER_RAC1))) (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=192.168.56.101)(PORT=1541))) Services Summary... Service "rac1.world" has 2 instance(s). Instance "rac1", status UNKNOWN, has 1 handler(s) for this service... Instance "rac1", status READY, has 1 handler(s) for this service... Service "rac1_DGB.world" has 1 instance(s). Instance "rac1", status READY, has 1 handler(s) for this service... Service "rac1_DGMGRL.world" has 1 instance(s). Instance "rac1", status UNKNOWN, has 1 handler(s) for this service... The command completed successfully |
Switchover
You start with a configuration like the following:
[orarac@server1 ~]$ dgmgrl DGMGRL for Linux: Version 11.2.0.2.0 - 64bit Production Copyright (c) 2000, 2009, Oracle. All rights reserved. Welcome to DGMGRL, type "help" for information. DGMGRL> connect sys/password Connected. DGMGRL> show configuration verbose Configuration - DGConfig1 Protection Mode: MaxPerformance Databases: rac1 - Primary database rac2 - Physical standby database Properties: FastStartFailoverThreshold = '30' OperationTimeout = '30' FastStartFailoverLagLimit = '30' CommunicationTimeout = '180' FastStartFailoverAutoReinstate = 'TRUE' FastStartFailoverPmyShutdown = 'TRUE' BystandersFollowRoleChange = 'ALL' Fast-Start Failover: DISABLED Configuration Status: SUCCESS |
To test it create first a test database on the primary instance (rac1):
SQL> CREATE TABLE yjaquier.test(val NUMBER, descr VARCHAR2(20)); TABLE created. SQL> INSERT INTO yjaquier.test VALUES(1,'One'); 1 ROW created. SQL> INSERT INTO yjaquier.test VALUES(2,'Two'); 1 ROW created. SQL> INSERT INTO yjaquier.test VALUES(3,'Three'); 1 ROW created. SQL> COMMIT; COMMIT complete. SQL> SELECT * FROM yjaquier.test; VAL DESCR ---------- -------------------- 1 One 2 Two 3 Three |
Switchover is as simple as one command:
DGMGRL> switchover to rac2 Performing switchover NOW, please wait... New primary database "rac2" is opening... Operation requires shutdown of instance "rac1" on database "rac1" Shutting down instance "rac1"... ORACLE instance shut down. Operation requires startup of instance "rac1" on database "rac1" Starting instance "rac1"... ORACLE instance started. Database mounted. Switchover succeeded, new primary is "rac2" DGMGRL> show configuration Configuration - DGConfig1 Protection Mode: MaxPerformance Databases: rac2 - Primary database rac1 - Physical standby database Fast-Start Failover: DISABLED Configuration Status: SUCCESS |
Rac2 instance is now your new primary instance and you can connect to it and check that your test table is there with its rows:
SQL> SELECT * FROM yjaquier.test; VAL DESCR ---------- -------------------- 1 One 2 Two 3 Three |
Then you can go back to initial configuration with:
DGMGRL> switchover to rac1 Performing switchover NOW, please wait... New primary database "rac1" is opening... Operation requires shutdown of instance "rac2" on database "rac2" Shutting down instance "rac2"... ORACLE instance shut down. Operation requires startup of instance "rac2" on database "rac2" Starting instance "rac2"... ORACLE instance started. Database mounted. Switchover succeeded, new primary is "rac1" DGMGRL> show configuration Configuration - DGConfig1 Protection Mode: MaxPerformance Databases: rac1 - Primary database rac2 - Physical standby database Fast-Start Failover: DISABLED Configuration Status: SUCCESS |
Failover
This option should be your last choice and you must first try to recover your primary database. If this cannot be done or not done within a correct time frame you can perform a (complete) failover. Please note that your initial primary will be lost (you will need to reinstate or recreate it from backup).
First you need to remove your primary database from Oracle restart configuration to avoid an automatic startup. To check current status use:
[oracrs@server1 ~]$ srvctl config database -d rac1 -a Database unique name: rac1 Database name: rac1 Oracle home: /ora_rac/software Oracle user: oracrs Spfile: +DATA/rac1/spfilerac1.ora Domain: world Start options: open Stop options: immediate Database role: PRIMARY Management policy: AUTOMATIC Disk Groups: DATA Services: Database is enabled |
Disable automatic restart with:
[oracrs@server1 ~]$ srvctl disable database -d rac1 [oracrs@server1 ~]$ srvctl config database -d rac1 -a Database unique name: rac1 Database name: rac1 Oracle home: /ora_rac/software Oracle user: oracrs Spfile: +DATA/rac1/spfilerac1.ora Domain: world Start options: open Stop options: immediate Database role: PRIMARY Management policy: AUTOMATIC Disk Groups: DATA Services: Database is disabled |
[orarac@server1 ~]$ ps -ef | grep pmon | grep -v grep oracrs 3520 1 0 May24 ? 00:00:49 asm_pmon_+ASM orarac 14138 1 0 May26 ? 00:00:12 ora_pmon_rac1 [orarac@server1 ~]$ kill -9 14138 [orarac@server1 ~]$ ps -ef | grep orarac root 1555 1389 0 May26 pts/2 00:00:00 su - orarac orarac 1556 1555 0 May26 pts/2 00:00:00 -bash orarac 12423 1556 0 10:28 pts/2 00:00:00 ps -ef orarac 12424 1556 0 10:28 pts/2 00:00:00 grep orarac orarac 14036 1 0 May26 ? 00:00:00 oraclerac1 (DESCRIPTION=(LOCAL=YES)(ADDRESS=(PROTOCOL=beq))) |
Your DGB configuration will now shows:
DGMGRL> show configuration Configuration - DGConfig1 Protection Mode: MaxPerformance Databases: rac1 - Primary database rac2 - Physical standby database Fast-Start Failover: DISABLED Configuration Status: ORA-01034: ORACLE not available ORA-16625: cannot reach database "rac1" DGM-17017: unable to determine configuration status |
Failover is simply:
DGMGRL> failover to rac2; Performing failover NOW, please wait... Failover succeeded, new primary is "rac2" |
Now your old standby is the new primary and, as expected, the original primary needs to be reinstated:
DGMGRL> show configuration Configuration - DGConfig1 Protection Mode: MaxPerformance Databases: rac2 - Primary database rac1 - Physical standby database (disabled) ORA-16661: the standby database needs to be reinstated Fast-Start Failover: DISABLED Configuration Status: SUCCESS |
We can try to reinstate it with:
DGMGRL> reinstate database rac1; Reinstating database "rac1", please wait... Error: ORA-16653: failed to reinstate database Failed. Reinstatement of database "rac1" failed |
But failing and as explained in Oracle note we would have needed to configure flashback database to be able to do so automatically…
If you try to start rac1 database in MOUNT state and issue again a request to reinstate you will now get clear error message:
DGMGRL> reinstate database rac1; Reinstating database "rac1", please wait... Error: ORA-16827: Flashback Database is disabled Failed. Reinstatement of database "rac1" failed DGMGRL> show configuration Configuration - DGConfig1 Protection Mode: MaxPerformance Databases: rac2 - Primary database rac1 - Physical standby database (disabled) ORA-16795: the standby database needs to be re-created Fast-Start Failover: DISABLED Configuration Status: SUCCESS |
Message is pretty clear and we will use an RMAN backup to recreate the original primary database. We will create a backup on a local filesystem and transfer it to other server (in same directory). It would be obviously more convenient to have a NFS mount point and be able to share backupset between servers:
[orarac@server2 dbs]$ rman target / Recovery Manager: Release 11.2.0.2.0 - Production on Fri May 27 14:21:03 2011 Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved. connected to target database: RAC1 (DBID=656357419) RMAN> run { allocate channel disk1 type disk; backup as compressed backupset database format '/var/tmp/failover_%U.bck' tag 'failover'; backup as compressed backupset current controlfile for standby format '/var/tmp/failover_%U.bck' tag 'failover'; sql 'alter system archive log current'; backup as compressed backupset format '/var/tmp/failover_%U.bck' archivelog all tag 'failover'; release channel disk1; } using target database control file instead of recovery catalog allocated channel: disk1 channel disk1: SID=35 device type=DISK Starting backup at 27-MAY-11 channel disk1: starting compressed full datafile backup set channel disk1: specifying datafile(s) in backup set input datafile file number=00001 name=+DATA/rac2/datafile/system.257.752168731 input datafile file number=00002 name=+DATA/rac2/datafile/sysaux.256.752168733 input datafile file number=00003 name=+DATA/rac2/datafile/undotbs1.259.752168801 input datafile file number=00004 name=+DATA/rac2/datafile/users.258.752168801 channel disk1: starting piece 1 at 27-MAY-11 channel disk1: finished piece 1 at 27-MAY-11 piece handle=/var/tmp/failover_0jmdcr6n_1_1.bck tag=FAILOVER comment=NONE channel disk1: backup set complete, elapsed time: 00:00:26 channel disk1: starting compressed full datafile backup set channel disk1: specifying datafile(s) in backup set including current control file in backup set including current SPFILE in backup set channel disk1: starting piece 1 at 27-MAY-11 channel disk1: finished piece 1 at 27-MAY-11 piece handle=/var/tmp/failover_0kmdcr7h_1_1.bck tag=FAILOVER comment=NONE channel disk1: backup set complete, elapsed time: 00:00:01 Finished backup at 27-MAY-11 Starting backup at 27-MAY-11 channel disk1: starting compressed full datafile backup set channel disk1: specifying datafile(s) in backup set including standby control file in backup set channel disk1: starting piece 1 at 27-MAY-11 channel disk1: finished piece 1 at 27-MAY-11 piece handle=/var/tmp/failover_0lmdcr7p_1_1.bck tag=FAILOVER comment=NONE channel disk1: backup set complete, elapsed time: 00:00:03 Finished backup at 27-MAY-11 sql statement: alter system archive log current Starting backup at 27-MAY-11 current log archived channel disk1: starting compressed archived log backup set channel disk1: specifying archived log(s) in backup set input archived log thread=1 sequence=81 RECID=2 STAMP=752169049 input archived log thread=1 sequence=82 RECID=1 STAMP=752169049 input archived log thread=1 sequence=83 RECID=3 STAMP=752169081 input archived log thread=1 sequence=84 RECID=4 STAMP=752169986 input archived log thread=1 sequence=85 RECID=5 STAMP=752170022 input archived log thread=1 sequence=86 RECID=6 STAMP=752170030 input archived log thread=1 sequence=87 RECID=7 STAMP=752170030 input archived log thread=1 sequence=88 RECID=8 STAMP=752170389 input archived log thread=1 sequence=89 RECID=9 STAMP=752170705 input archived log thread=1 sequence=90 RECID=10 STAMP=752170711 input archived log thread=1 sequence=91 RECID=11 STAMP=752170721 input archived log thread=1 sequence=92 RECID=12 STAMP=752170722 input archived log thread=1 sequence=93 RECID=13 STAMP=752170741 input archived log thread=1 sequence=94 RECID=17 STAMP=752170766 input archived log thread=1 sequence=95 RECID=20 STAMP=752170796 input archived log thread=1 sequence=96 RECID=22 STAMP=752171712 input archived log thread=1 sequence=97 RECID=23 STAMP=752171717 input archived log thread=1 sequence=98 RECID=26 STAMP=752171759 input archived log thread=1 sequence=99 RECID=27 STAMP=752171760 input archived log thread=1 sequence=100 RECID=25 STAMP=752171759 input archived log thread=1 sequence=101 RECID=28 STAMP=752171761 input archived log thread=1 sequence=102 RECID=29 STAMP=752171765 input archived log thread=1 sequence=103 RECID=30 STAMP=752171788 input archived log thread=1 sequence=104 RECID=31 STAMP=752173007 input archived log thread=1 sequence=105 RECID=32 STAMP=752173020 input archived log thread=1 sequence=106 RECID=33 STAMP=752173144 input archived log thread=1 sequence=107 RECID=34 STAMP=752173149 input archived log thread=1 sequence=108 RECID=35 STAMP=752176484 input archived log thread=1 sequence=109 RECID=36 STAMP=752176488 input archived log thread=1 sequence=110 RECID=37 STAMP=752198466 input archived log thread=1 sequence=111 RECID=40 STAMP=752236328 channel disk1: starting piece 1 at 27-MAY-11 channel disk1: finished piece 1 at 27-MAY-11 piece handle=/var/tmp/failover_0mmdcr8c_1_1.bck tag=FAILOVER comment=NONE channel disk1: backup set complete, elapsed time: 00:00:07 channel disk1: starting compressed archived log backup set channel disk1: specifying archived log(s) in backup set input archived log thread=1 sequence=1 RECID=39 STAMP=752236292 input archived log thread=1 sequence=2 RECID=38 STAMP=752236292 input archived log thread=1 sequence=3 RECID=41 STAMP=752237116 input archived log thread=1 sequence=4 RECID=42 STAMP=752250116 input archived log thread=1 sequence=5 RECID=43 STAMP=752250116 channel disk1: starting piece 1 at 27-MAY-11 channel disk1: finished piece 1 at 27-MAY-11 piece handle=/var/tmp/failover_0nmdcr8j_1_1.bck tag=FAILOVER comment=NONE channel disk1: backup set complete, elapsed time: 00:00:01 Finished backup at 27-MAY-11 released channel: disk1 |
You can verify it with:
RMAN> list backup; using target database control file instead of recovery catalog List of Backup Sets =================== BS Key Type LV Size Device Type Elapsed Time Completion Time ------- ---- -- ---------- ----------- ------------ --------------- 5 Full 59.73M DISK 00:00:18 27-MAY-11 BP Key: 5 Status: AVAILABLE Compressed: YES Tag: FAILOVER Piece Name: /var/tmp/failover_0jmdcr6n_1_1.bck List of Datafiles in backup set 5 File LV Type Ckp SCN Ckp Time Name ---- -- ---- ---------- --------- ---- 1 Full 776663 27-MAY-11 +DATA/rac2/datafile/system.257.752168731 2 Full 776663 27-MAY-11 +DATA/rac2/datafile/sysaux.256.752168733 3 Full 776663 27-MAY-11 +DATA/rac2/datafile/undotbs1.259.752168801 4 Full 776663 27-MAY-11 +DATA/rac2/datafile/users.258.752168801 BS Key Type LV Size Device Type Elapsed Time Completion Time ------- ---- -- ---------- ----------- ------------ --------------- 6 Full 1.05M DISK 00:00:03 27-MAY-11 BP Key: 6 Status: AVAILABLE Compressed: YES Tag: FAILOVER Piece Name: /var/tmp/failover_0kmdcr7h_1_1.bck SPFILE Included: Modification time: 27-MAY-11 SPFILE db_unique_name: RAC2 Control File Included: Ckp SCN: 776687 Ckp time: 27-MAY-11 BS Key Type LV Size Device Type Elapsed Time Completion Time ------- ---- -- ---------- ----------- ------------ --------------- 7 Full 1.05M DISK 00:00:04 27-MAY-11 BP Key: 7 Status: AVAILABLE Compressed: YES Tag: FAILOVER Piece Name: /var/tmp/failover_0lmdcr7p_1_1.bck Standby Control File Included: Ckp SCN: 776714 Ckp time: 27-MAY-11 BS Key Size Device Type Elapsed Time Completion Time ------- ---------- ----------- ------------ --------------- 8 16.76M DISK 00:00:05 27-MAY-11 BP Key: 8 Status: AVAILABLE Compressed: YES Tag: FAILOVER Piece Name: /var/tmp/failover_0mmdcr8c_1_1.bck List of Archived Logs in backup set 8 Thrd Seq Low SCN Low Time Next SCN Next Time ---- ------- ---------- --------- ---------- --------- 1 81 605606 26-MAY-11 608163 26-MAY-11 1 82 608163 26-MAY-11 608247 26-MAY-11 1 83 608247 26-MAY-11 608496 26-MAY-11 1 84 608496 26-MAY-11 609567 26-MAY-11 1 85 609567 26-MAY-11 609607 26-MAY-11 1 86 609607 26-MAY-11 609612 26-MAY-11 1 87 609612 26-MAY-11 609617 26-MAY-11 1 88 609617 26-MAY-11 609990 26-MAY-11 1 89 609990 26-MAY-11 610821 26-MAY-11 1 90 610821 26-MAY-11 630832 26-MAY-11 1 91 630832 26-MAY-11 630835 26-MAY-11 1 92 630835 26-MAY-11 630841 26-MAY-11 1 93 630841 26-MAY-11 630948 26-MAY-11 1 94 630948 26-MAY-11 630971 26-MAY-11 1 95 630971 26-MAY-11 631035 26-MAY-11 1 96 631035 26-MAY-11 632269 26-MAY-11 1 97 632269 26-MAY-11 652279 26-MAY-11 1 98 652279 26-MAY-11 652282 26-MAY-11 1 99 652282 26-MAY-11 652289 26-MAY-11 1 100 652289 26-MAY-11 652395 26-MAY-11 1 101 652395 26-MAY-11 652403 26-MAY-11 1 102 652403 26-MAY-11 652418 26-MAY-11 1 103 652418 26-MAY-11 652469 26-MAY-11 1 104 652469 26-MAY-11 653997 26-MAY-11 1 105 653997 26-MAY-11 654034 26-MAY-11 1 106 654034 26-MAY-11 654180 26-MAY-11 1 107 654180 26-MAY-11 654189 26-MAY-11 1 108 654189 26-MAY-11 678241 26-MAY-11 1 109 678241 26-MAY-11 678249 26-MAY-11 1 110 678249 26-MAY-11 710680 27-MAY-11 1 111 710680 27-MAY-11 757966 27-MAY-11 (TERMINAL) BS Key Size Device Type Elapsed Time Completion Time ------- ---------- ----------- ------------ --------------- 9 3.02M DISK 00:00:01 27-MAY-11 BP Key: 9 Status: AVAILABLE Compressed: YES Tag: FAILOVER Piece Name: /var/tmp/failover_0nmdcr8j_1_1.bck List of Archived Logs in backup set 9 Thrd Seq Low SCN Low Time Next SCN Next Time ---- ------- ---------- --------- ---------- --------- 1 1 757966 27-MAY-11 757969 27-MAY-11 1 2 757969 27-MAY-11 757976 27-MAY-11 1 3 757976 27-MAY-11 759115 27-MAY-11 1 4 759115 27-MAY-11 776732 27-MAY-11 1 5 776732 27-MAY-11 776744 27-MAY-11 |
Start in NOMOUNT state the original primary database. The spfile to use is either the original one (that you should still have) or a copy of the standby initialization parameters file that you modify to match the original primary instance environment:
[orarac@server1 /]$ sqlplus / AS sysdba SQL*Plus: RELEASE 11.2.0.2.0 Production ON Fri May 27 14:33:25 2011 Copyright (c) 1982, 2010, Oracle. ALL rights reserved. Connected TO an idle instance. SQL> startup nomount ORACLE instance started. Total SYSTEM Global Area 208769024 bytes Fixed SIZE 2225064 bytes Variable SIZE 167775320 bytes DATABASE Buffers 33554432 bytes Redo Buffers 5214208 bytes |
Remote copy backupset files to server1 in exact same target directory i.e. /var/tmp in our case (or better use a NFS shared mount point).
Duplicate rac2 instance to recreate rac1 instance with following RMAN script:
[orarac@server1 /]$ rman target sys/password@rac2 auxiliary / Recovery Manager: Release 11.2.0.2.0 - Production on Fri May 27 14:33:42 2011 Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved. connected to target database: RAC1 (DBID=656357419) connected to auxiliary database: RAC1 (not mounted) RMAN> run { allocate channel disk1 device type disk; allocate auxiliary channel disk2 device type disk; duplicate target database for standby nofilenamecheck dorecover; release channel disk1; release channel disk2; } using target database control file instead of recovery catalog allocated channel: disk1 channel disk1: SID=47 device type=DISK allocated channel: disk2 channel disk2: SID=25 device type=DISK Starting Duplicate Db at 27-MAY-11 contents of Memory Script: { set until scn 776744; sql clone "alter system set control_files = ''+DATA/rac1/controlfile/current.267.752250849'', ''+DATA/rac1/controlfile/current.257.752250851'' comment= ''Set by RMAN'' scope=spfile"; restore clone standby controlfile; } executing Memory Script executing command: SET until clause sql statement: alter system set control_files = ''+DATA/rac1/controlfile/current.267.752250849'', ''+DATA/rac1/controlfile/current.257.752250851'' comment= ''Set by RMAN'' scope=spfile Starting restore at 27-MAY-11 channel disk2: starting datafile backup set restore channel disk2: restoring control file channel disk2: reading from backup piece /var/tmp/failover_0lmdcr7p_1_1.bck channel disk2: piece handle=/var/tmp/failover_0lmdcr7p_1_1.bck tag=FAILOVER channel disk2: restored backup piece 1 channel disk2: restore complete, elapsed time: 00:00:07 output file name=+DATA/rac1/controlfile/current.269.752250853 output file name=+DATA/rac1/controlfile/current.256.752250857 Finished restore at 27-MAY-11 contents of Memory Script: { sql clone 'alter database mount standby database'; } executing Memory Script sql statement: alter database mount standby database contents of Memory Script: { set until scn 776744; set newname for clone tempfile 1 to new; switch clone tempfile all; set newname for clone datafile 1 to new; set newname for clone datafile 2 to new; set newname for clone datafile 3 to new; set newname for clone datafile 4 to new; restore clone database ; } executing Memory Script executing command: SET until clause executing command: SET NEWNAME renamed tempfile 1 to +DATA in control file executing command: SET NEWNAME executing command: SET NEWNAME executing command: SET NEWNAME executing command: SET NEWNAME Starting restore at 27-MAY-11 channel disk2: starting datafile backup set restore channel disk2: specifying datafile(s) to restore from backup set channel disk2: restoring datafile 00001 to +DATA channel disk2: restoring datafile 00002 to +DATA channel disk2: restoring datafile 00003 to +DATA channel disk2: restoring datafile 00004 to +DATA channel disk2: reading from backup piece /var/tmp/failover_0jmdcr6n_1_1.bck channel disk2: piece handle=/var/tmp/failover_0jmdcr6n_1_1.bck tag=FAILOVER channel disk2: restored backup piece 1 channel disk2: restore complete, elapsed time: 00:01:56 Finished restore at 27-MAY-11 contents of Memory Script: { switch clone datafile all; } executing Memory Script datafile 1 switched to datafile copy input datafile copy RECID=11 STAMP=752250993 file name=+DATA/rac1/datafile/system.258.752250877 datafile 2 switched to datafile copy input datafile copy RECID=12 STAMP=752250993 file name=+DATA/rac1/datafile/sysaux.259.752250879 datafile 3 switched to datafile copy input datafile copy RECID=13 STAMP=752250993 file name=+DATA/rac1/datafile/undotbs1.285.752250883 datafile 4 switched to datafile copy input datafile copy RECID=14 STAMP=752250993 file name=+DATA/rac1/datafile/users.260.752250883 contents of Memory Script: { set until scn 776744; recover standby clone database delete archivelog ; } executing Memory Script executing command: SET until clause Starting recover at 27-MAY-11 starting media recovery channel disk2: starting archived log restore to default destination channel disk2: restoring archived log archived log thread=1 sequence=4 channel disk2: restoring archived log archived log thread=1 sequence=5 channel disk2: reading from backup piece /var/tmp/failover_0nmdcr8j_1_1.bck channel disk2: piece handle=/var/tmp/failover_0nmdcr8j_1_1.bck tag=FAILOVER channel disk2: restored backup piece 1 channel disk2: restore complete, elapsed time: 00:00:07 archived log file name=+DATA/rac1/archivelog/2011_05_27/thread_1_seq_4.284.752251001 thread=1 sequence=4 channel clone_default: deleting archived log(s) archived log file name=+DATA/rac1/archivelog/2011_05_27/thread_1_seq_4.284.752251001 RECID=2 STAMP=752251005 archived log file name=+DATA/rac1/archivelog/2011_05_27/thread_1_seq_5.262.752251003 thread=1 sequence=5 channel clone_default: deleting archived log(s) archived log file name=+DATA/rac1/archivelog/2011_05_27/thread_1_seq_5.262.752251003 RECID=1 STAMP=752251004 media recovery complete, elapsed time: 00:00:02 Finished recover at 27-MAY-11 Finished Duplicate Db at 27-MAY-11 released channel: disk1 released channel: disk2 |
Then the final magic command:
DGMGRL> enable database rac1; Enabled. DGMGRL> show configuration Configuration - DGConfig1 Protection Mode: MaxPerformance Databases: rac2 - Primary database rac1 - Physical standby database Fast-Start Failover: DISABLED Configuration Status: SUCCESS |
You can even switchover to be back in your original configuration:
DGMGRL> switchover to rac1 Performing switchover NOW, please wait... New primary database "rac1" is opening... Operation requires shutdown of instance "rac2" on database "rac2" Shutting down instance "rac2"... ORACLE instance shut down. Operation requires startup of instance "rac2" on database "rac2" Starting instance "rac2"... ORACLE instance started. Database mounted. Switchover succeeded, new primary is "rac1" DGMGRL> show configuration Configuration - DGConfig1 Protection Mode: MaxPerformance Databases: rac1 - Primary database rac2 - Physical standby database Fast-Start Failover: DISABLED Configuration Status: SUCCESS |
Finally you can re-enable automatic startup of your primary database:
[oracrs@server1 ~]$ srvctl enable database -d rac1 [oracrs@server1 ~]$ srvctl config database -d rac1 -a Database unique name: rac1 Database name: rac1 Oracle home: /ora_rac/software Oracle user: oracrs Spfile: +DATA/rac1/spfilerac1.ora Domain: world Start options: open Stop options: immediate Database role: PRIMARY Management policy: AUTOMATIC Disk Groups: DATA Services: Database is enabled |
References
- 11gR2 Clusterware and Grid Home – What You Need to Know [ID 1053147.1]
- Failover/Reinstate within Dataguard configuration fails with ORA-16653 /ORA-16795 [ID 1161094.1]
- RMAN: ORA-19504, ORA-27038 Errors During Backup when a Backupset is in Place [ID 1082911.6]
- Creating a Standby Database on a new host [ID 374069.1]
- Reinstating a Physical Standby Using Backups Instead of Flashback [ID 416310.1]
Lodh says:
Hello yannick
My opinion is that, to always setup your DG broker on a separate client server which can observe both primary & standby DBs and also ensure that the broker is not affected by primary system crash.