One of the problems I have seen when deploying Data Guard for systems such as RAC One Node and policy managed databases was the static listener configuration you needed in 11.2. This has changed with 12c for the better if you are using Grid Infrastructure.
http://docs.oracle.com/database/121/DGBKR/install.htm
In the section about static listener registration a little addendum can be found (thanks to Patrick Hurley/@phurley for pointing this out to me!):
“A static service needs to be defined and registered only if Oracle Clusterware or Oracle Restart is not being used.”
This is good news, let’s put it to the test; I’m a great fan of Oracle Restart. If I ever find the time I’d like to repeat this test with clustered Grid Infrastructure. I think the quote mentioned earlier still stands true but I would like to see it with my own eyes.
The Setup
To start with I used a dbca-created database named “NCDB” on my server named “server1”. It uses the DATA and RECO disk groups and is a non-CDB (although I’m quite sure that doesn’t matter). The patch level is current at the time of writing, I’m on 12.1.0.2.4 (which is the OJVM and PSU bundle patch for July 2015):
[oracle@server1 ~]$ /u01/app/oracle/product/12.1.0.2/dbhome_1/OPatch/opatch lspatches 21068507;Database PSU 12.1.0.2.4, Oracle JavaVM Component (Jul2015) 20831113;OCW Patch Set Update : 12.1.0.2.4 (20831113) 20831110;Database Patch Set Update : 12.1.0.2.4 (20831110) OPatch succeeded. [oracle@server1 ~]$ /u01/app/oracle/product/12.1.0.2/grid/OPatch/opatch lspatches 20831113;OCW Patch Set Update : 12.1.0.2.4 (20831113) 20831110;Database Patch Set Update : 12.1.0.2.4 (20831110) 20299018;ACFS Patch Set Update : 12.1.0.2.3 (20299018) 19872484;WLM Patch Set Update: 12.1.0.2.2 (19872484) OPatch succeeded.
The future standby database will be called STDBY. This is for demonstration purposes only, I’m not a fan of using db_unique_name implying roles. Imagine you switch over to STDBY and run it as primary database for extended periods of time-that would be potentially confusing.
With that in mind, I created/updated a common tnsnames.ora on server1 and server2:
[oracle@server1 ~]$ cat $ORACLE_HOME/network/admin/tnsnames.ora # tnsnames.ora Network Configuration File: # /u01/app/oracle/product/12.1.0.2/dbhome_1/network/admin/tnsnames.ora # Generated by Oracle configuration tools. NCDB = (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = server1.example.com)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = NCDB) ) ) STDBY = (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = server2.example.com)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = STDBY) ) )
The duplicate command was a simple “duplicate target database for standby” after creating a simple pfile and auxiliary structures (audit_dump_dest, large pages etc) were in place. For this to work you need a copy of the controlfile for the standby in place as well as a backup of the primary database).
RMAN> duplicate target database for standby; Starting Duplicate Db at 27-JUL-15 using channel ORA_AUX_DISK_1 using channel ORA_AUX_DISK_2 contents of Memory Script: { sql clone "alter system set control_files = ''+DATA/STDBY/CONTROLFILE/current.258.886176617'', ''+RECO/STDBY/CONTROLFILE/current.257.886176617'' comment= ''Set by RMAN'' scope=spfile"; restore clone standby controlfile; } executing Memory Script sql statement: alter system set control_files = ''+DATA/STDBY/CONTROLFILE/current.258.886176617'', ''+RECO/STDBY/CONTROLFILE/current.257.886176617'' comment= ''Set by RMAN'' scope=spfile Starting restore at 27-JUL-15 using channel ORA_AUX_DISK_1 using channel ORA_AUX_DISK_2 channel ORA_AUX_DISK_1: starting datafile backup set restore channel ORA_AUX_DISK_1: restoring control file channel ORA_AUX_DISK_1: reading from backup piece +RECO/NCDB/AUTOBACKUP/2015_07_27/s_886176583.273.886176583 channel ORA_AUX_DISK_1: ORA-19870: error while restoring backup piece +RECO/NCDB/AUTOBACKUP/2015_07_27/s_886176583.273.886176583 ORA-19505: failed to identify file "+RECO/NCDB/AUTOBACKUP/2015_07_27/s_886176583.273.886176583" ORA-17503: ksfdopn:2 Failed to open file +RECO/NCDB/AUTOBACKUP/2015_07_27/s_886176583.273.886176583 ORA-15012: ASM file '+RECO/NCDB/AUTOBACKUP/2015_07_27/s_886176583.273.886176583' does not exist failover to previous backup channel ORA_AUX_DISK_1: starting datafile backup set restore channel ORA_AUX_DISK_1: restoring control file channel ORA_AUX_DISK_1: reading from backup piece /u01/oraback/NCDB/09qd3uq4_1_1 channel ORA_AUX_DISK_1: piece handle=/u01/oraback/NCDB/09qd3uq4_1_1 tag=TAG20150727T160940 channel ORA_AUX_DISK_1: restored backup piece 1 channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:01 output file name=+DATA/STDBY/CONTROLFILE/current.259.886176619 output file name=+RECO/STDBY/CONTROLFILE/current.258.886176619 Finished restore at 27-JUL-15 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 newname for clone tempfile 1 to new; switch clone tempfile all; set newname for clone datafile 1 to new; set newname for clone datafile 3 to new; set newname for clone datafile 4 to new; set newname for clone datafile 5 to new; set newname for clone datafile 6 to new; restore clone database ; } executing Memory Script 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 executing command: SET NEWNAME Starting restore at 27-JUL-15 using channel ORA_AUX_DISK_1 using channel ORA_AUX_DISK_2 channel ORA_AUX_DISK_1: starting datafile backup set restore channel ORA_AUX_DISK_1: specifying datafile(s) to restore from backup set channel ORA_AUX_DISK_1: restoring datafile 00004 to +DATA channel ORA_AUX_DISK_1: restoring datafile 00005 to +DATA channel ORA_AUX_DISK_1: restoring datafile 00006 to +DATA channel ORA_AUX_DISK_1: reading from backup piece /u01/oraback/NCDB/05qd3uas_1_1 channel ORA_AUX_DISK_2: starting datafile backup set restore channel ORA_AUX_DISK_2: specifying datafile(s) to restore from backup set channel ORA_AUX_DISK_2: restoring datafile 00001 to +DATA channel ORA_AUX_DISK_2: restoring datafile 00003 to +DATA channel ORA_AUX_DISK_2: reading from backup piece /u01/oraback/NCDB/06qd3uat_1_1 channel ORA_AUX_DISK_1: piece handle=/u01/oraback/NCDB/05qd3uas_1_1 tag=TAG20150727T160132 channel ORA_AUX_DISK_1: restored backup piece 1 channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:15 channel ORA_AUX_DISK_2: piece handle=/u01/oraback/NCDB/06qd3uat_1_1 tag=TAG20150727T160132 channel ORA_AUX_DISK_2: restored backup piece 1 channel ORA_AUX_DISK_2: restore complete, elapsed time: 00:00:15 Finished restore at 27-JUL-15 contents of Memory Script: { switch clone datafile all; } executing Memory Script datafile 1 switched to datafile copy input datafile copy RECID=6 STAMP=886176640 file name=+DATA/STDBY/DATAFILE/system.261.886176625 datafile 3 switched to datafile copy input datafile copy RECID=7 STAMP=886176641 file name=+DATA/STDBY/DATAFILE/sysaux.263.886176625 datafile 4 switched to datafile copy input datafile copy RECID=8 STAMP=886176641 file name=+DATA/STDBY/DATAFILE/undotbs1.262.886176625 datafile 5 switched to datafile copy input datafile copy RECID=9 STAMP=886176641 file name=+DATA/STDBY/DATAFILE/example.260.886176625 datafile 6 switched to datafile copy input datafile copy RECID=10 STAMP=886176641 file name=+DATA/STDBY/DATAFILE/users.264.886176625 Finished Duplicate Db at 27-JUL-15 RMAN>
That’s a working standby database. I will have to register it with Grid Infrastructure next.
[oracle@server2 ~]$ srvctl add database -db STDBY -oraclehome /u01/app/oracle/product/12.1.0.2/dbhome_1 \ > -role physical_standby -startoption MOUNT -policy automatic -diskgroup data,reco
Now all I need to do is set standby_file_management to auto, add standby redo logs to both databases and enable the broker. I’ll not show these here.
Broker Configuration
The next step in the deployment of my standby database is the creation of a Data Guard Broker configuration. I always create it using dgmgrl.
[oracle@server1 ~]$ dgmgrl DGMGRL for Linux: Version 12.1.0.2.0 - 64bit Production Copyright (c) 2000, 2013, Oracle. All rights reserved. Welcome to DGMGRL, type "help" for information. DGMGRL> connect sys@ncdb Password: Connected as SYSDBA. DGMGRL> CREATE CONFIGURATION brokertest AS > PRIMARY DATABASE IS 'NCDB' > CONNECT IDENTIFIER IS 'NCDB'; Configuration "brokertest" created with primary database "NCDB" DGMGRL> add database 'STDBY' as connect identifier is 'STDBY'; Database "STDBY" added DGMGRL> enable configuration; Enabled. DGMGRL> show configuration; Configuration - brokertest Protection Mode: MaxPerformance Members: NCDB - Primary database STDBY - Physical standby database Fast-Start Failover: DISABLED Configuration Status: SUCCESS (status updated 5 seconds ago)
That’s looking good so far! I can see archived redo logs being exchanged, and I can see MRP0 applying data in real time on the standby.
Recap
This is a reference for the settings in the Data Guard Broker. I have not modified any listener.ora file on any host, all I did was to ensure that the tnsnames.ora file has identical contents on both sides. I have listed the database configuration for later reference here:
DGMGRL> show database verbose 'NCDB'; Database - NCDB Role: PRIMARY Intended State: TRANSPORT-ON Instance(s): NCDB Properties: DGConnectIdentifier = 'NCDB' ObserverConnectIdentifier = '' LogXptMode = 'ASYNC' RedoRoutes = '' DelayMins = '0' Binding = 'optional' MaxFailure = '0' MaxConnections = '1' ReopenSecs = '300' NetTimeout = '30' RedoCompression = 'DISABLE' LogShipping = 'ON' PreferredApplyInstance = '' ApplyInstanceTimeout = '0' ApplyLagThreshold = '0' TransportLagThreshold = '0' TransportDisconnectedThreshold = '30' ApplyParallel = 'AUTO' StandbyFileManagement = 'AUTO' ArchiveLagTarget = '0' LogArchiveMaxProcesses = '4' LogArchiveMinSucceedDest = '1' DbFileNameConvert = '' LogFileNameConvert = '' FastStartFailoverTarget = '' InconsistentProperties = '(monitor)' InconsistentLogXptProps = '(monitor)' SendQEntries = '(monitor)' LogXptStatus = '(monitor)' RecvQEntries = '(monitor)' StaticConnectIdentifier = '(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=server1.example.com) (PORT=1521))(CONNECT_DATA=(SERVICE_NAME=NCDB_DGMGRL) (INSTANCE_NAME=NCDB)(SERVER=DEDICATED)))' StandbyArchiveLocation = 'USE_DB_RECOVERY_FILE_DEST' AlternateLocation = '' LogArchiveTrace = '0' LogArchiveFormat = '%t_%s_%r.dbf' TopWaitEvents = '(monitor)' Database Status: SUCCESS DGMGRL> show database verbose 'STDBY'; Database - STDBY Role: PHYSICAL STANDBY Intended State: APPLY-ON Transport Lag: 0 seconds (computed 1 second ago) Apply Lag: 0 seconds (computed 1 second ago) Average Apply Rate: 5.00 KByte/s Active Apply Rate: 0 Byte/s Maximum Apply Rate: 0 Byte/s Real Time Query: OFF Instance(s): STDBY Properties: DGConnectIdentifier = 'STDBY' ObserverConnectIdentifier = '' LogXptMode = 'ASYNC' RedoRoutes = '' DelayMins = '0' Binding = 'optional' MaxFailure = '0' MaxConnections = '1' ReopenSecs = '300' NetTimeout = '30' RedoCompression = 'DISABLE' LogShipping = 'ON' PreferredApplyInstance = '' ApplyInstanceTimeout = '0' ApplyLagThreshold = '0' TransportLagThreshold = '0' TransportDisconnectedThreshold = '30' ApplyParallel = 'AUTO' StandbyFileManagement = 'AUTO' ArchiveLagTarget = '0' LogArchiveMaxProcesses = '4' LogArchiveMinSucceedDest = '1' DbFileNameConvert = '' LogFileNameConvert = '' FastStartFailoverTarget = '' InconsistentProperties = '(monitor)' InconsistentLogXptProps = '(monitor)' SendQEntries = '(monitor)' LogXptStatus = '(monitor)' RecvQEntries = '(monitor)' StaticConnectIdentifier = '(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=192.168.100.52) (PORT=1521))(CONNECT_DATA=(SERVICE_NAME=STDBY_DGMGRL)(INSTANCE_NAME=STDBY) (SERVER=DEDICATED)))' StandbyArchiveLocation = 'USE_DB_RECOVERY_FILE_DEST' AlternateLocation = '' LogArchiveTrace = '0' LogArchiveFormat = '%t_%s_%r.dbf' TopWaitEvents = '(monitor)' Database Status: SUCCESS DGMGRL> show configuration verbose; Configuration - brokertest Protection Mode: MaxPerformance Members: NCDB - Primary database STDBY - Physical standby database Properties: FastStartFailoverThreshold = '30' OperationTimeout = '30' TraceLevel = 'USER' FastStartFailoverLagLimit = '30' CommunicationTimeout = '180' ObserverReconnect = '0' FastStartFailoverAutoReinstate = 'TRUE' FastStartFailoverPmyShutdown = 'TRUE' BystandersFollowRoleChange = 'ALL' ObserverOverride = 'FALSE' ExternalDestination1 = '' ExternalDestination2 = '' PrimaryLostWriteAction = 'CONTINUE' Fast-Start Failover: DISABLED Configuration Status: SUCCESS
Now the big question is: will this allow me to switch over? For the record I am still using my SSH connection (in screen!) on server1, but am now connected to the standby database in dgmgrl.
DGMGRL> validate database 'STDBY' Database Role: Physical standby database Primary Database: NCDB Ready for Switchover: Yes Ready for Failover: Yes (Primary Running) Flashback Database Status: NCDB: Off STDBY: Off Current Log File Groups Configuration: Thread # Online Redo Log Groups Standby Redo Log Groups Status (NCDB) (STDBY) 1 3 2 Insufficient SRLs Future Log File Groups Configuration: Thread # Online Redo Log Groups Standby Redo Log Groups Status (STDBY) (NCDB) 1 3 0 Insufficient SRLs Warning: standby redo logs not configured for thread 1 on NCDB DGMGRL> switchover to 'STDBY'; Performing switchover NOW, please wait... New primary database "STDBY" is opening... Oracle Clusterware is restarting database "NCDB" ... Switchover succeeded, new primary is "STDBY" DGMGRL> show configuration Configuration - brokertest Protection Mode: MaxPerformance Members: STDBY - Primary database NCDB - Physical standby database Fast-Start Failover: DISABLED Configuration Status: SUCCESS (status updated 13 seconds ago) DGMGRL>
Well that seems to have worked!
It seems straight forward though: the SSH connection was made to the future standby host, which could have helped with Oracle Restart restarting the database. What if I try to switch back, but this time connect to server1 (currently in the standby role) and issue the switchover command? Here is the output:
[oracle@server1 ~]$ dgmgrl DGMGRL for Linux: Version 12.1.0.2.0 - 64bit Production Copyright (c) 2000, 2013, Oracle. All rights reserved. Welcome to DGMGRL, type "help" for information. DGMGRL> connect sys@ncdb Password: Connected as SYSDBA. DGMGRL> show configuration Configuration - brokertest Protection Mode: MaxPerformance Members: STDBY - Primary database NCDB - Physical standby database Fast-Start Failover: DISABLED Configuration Status: SUCCESS (status updated 2 seconds ago) DGMGRL> validate database 'NCDB'; Database Role: Physical standby database Primary Database: STDBY Ready for Switchover: Yes Ready for Failover: Yes (Primary Running) Flashback Database Status: STDBY: Off NCDB: Off Current Log File Groups Configuration: Thread # Online Redo Log Groups Standby Redo Log Groups Status (STDBY) (NCDB) 1 3 2 Insufficient SRLs Future Log File Groups Configuration: Thread # Online Redo Log Groups Standby Redo Log Groups Status (NCDB) (STDBY) 1 3 2 Insufficient SRLs
Ready to switch over:
DGMGRL> switchover to 'NCDB' Performing switchover NOW, please wait... New primary database "NCDB" is opening... Oracle Clusterware is restarting database "STDBY" ... Switchover succeeded, new primary is "NCDB" DGMGRL> show configuration Configuration - brokertest Protection Mode: MaxPerformance Members: NCDB - Primary database STDBY - Physical standby database Fast-Start Failover: DISABLED Configuration Status: SUCCESS (status updated 54 seconds ago) DGMGRL>
OK, that worked too-very nice. It’s safe to say that in my lab conditions restarting of databases works pretty well.
Support Impact of the Deprecation Announcement of Oracle Restart with Oracle Database 12c (Doc ID 1584742.1)
Pingback: How to set up data guard broker for RAC « Martins Blog
Pingback: Creating a RAC 12.1 Data Guard Physical Standby environment (4) « Martins Blog