The purpose of this blog article is to show how to configure Data Guard Broker and to use it with some new Oracle 12c features.
The configuration I have used is the following:
Remember that you must already have created a standby database because Data Guard Broker does not allow to create a standby database: it must done manually before
(or with Grid Control but that is outside the scope of my blog).
SYSDG is new 12c system privilege designed to be used instead of SYSDBA by Data Guard Broker.
Connect on primary database instance and create an account named dga (like Data Guard Administrator) and grant SYSDG privilege:
SYS@CDB1>create user dga identified by dga; User created. SYS@CDB1>grant sysdg to dga; Grant succeeded. SYS@CDB1>select * from v$pwfile_users; USERNAME SYSDB SYSOP SYSAS SYSBA SYSDG SYSKM CON_ID ------------------------------ ----- ----- ----- ----- ----- ----- ---------- SYS TRUE TRUE FALSE FALSE FALSE FALSE 0 DGA FALSE FALSE FALSE FALSE TRUE FALSE 0 SYS@CDB1>
Note that the password file has been updated.
If you want use this new database account instead of SYS to manage your Data Guard configuration you must make sure that this user account can be used on primary and on standby databases. The dga account has also been created by Data Guard on the standby database (because it belongs to dictionary SYS.USER$ table in SYSTEM tablespace) but the standby password file has not been updated (because Data Guard does not work on the password file that is not a tablespace datafile).
Copy the password file from primary node to standby node:
$ ls -la $ORACLE_HOME/dbs/orapw* -rw-r----- 1 oracle oinstall 7680 Dec 24 14:15 /u01/app/12.1.0.2/db/dbs/orapwCDB0 $ scp oracle@ol6twsa1:$ORACLE_HOME/dbs/orapwCDB1 $ORACLE_HOME/dbs/orapwCDB0 oracle@ol6twsa1's password: orapwCDB1 100% 7680 7.5KB/s 00:00
Connect to standby database and check that the dga account with SYSDG privilege is known by standby database:
$ sqlplus / as sysdba SQL*Plus: Release 12.1.0.2.0 Production on Mon Dec 29 21:13:38 2014 Copyright (c) 1982, 2014, Oracle. All rights reserved. Connected to: Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production With the Partitioning, Automatic Storage Management, OLAP, Advanced Analytics and Real Application Testing options SYS@CDB0>select * from v$pwfile_users; USERNAME SYSDB SYSOP SYSAS SYSBA SYSDG SYSKM CON_ID ------------------------------ ----- ----- ----- ----- ----- ----- ---------- SYS TRUE TRUE FALSE FALSE FALSE FALSE 0 DGA FALSE FALSE FALSE FALSE TRUE FALSE 0
You must set this parameter on both primary and standby database (because Data Guard does not work on the SPFILE which is not a tablespace datafile): you can also use Data Guard Broker to run SQL statement instead of SQL*Plus. Connect first to primary instance and then use DGMGRL CONNECT statement to connect to standby instance. The SQL keyword can be used to run SQL statement on the current database instance:
$ 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 / Connected as SYSDG. DGMGRL> sql "alter system set dg_broker_start=true"; Succeeded. DGMGRL> connect dga/dga@CDB0 Connected as SYSDG. DGMGRL> sql "alter system set dg_broker_start=true"; Succeeded. DGMGRL>
Note that DGMGRL is using SYSDG privilege by default.
This is a mandatory step before creating a Data Guard Broker configuration that must be run on both primary and standby databases:
DGMGRL> sql "alter system set log_archive_dest_2=''" Succeeded. DGMGRL> connect / Connected as SYSDG. DGMGRL> sql "alter system set log_archive_dest_2=''" Succeeded.
DGMGRL> create configuration CDB as primary database is CDB1 connect identifier is CDB1; Configuration "cdb" created with primary database "cdb1" DGMGRL> add database CDB0 as connect identifier is CDB0; Database "cdb0" added DGMGRL> show configuration; Configuration - cdb Protection Mode: MaxPerformance Members: cdb1 - Primary database cdb0 - Physical standby database Fast-Start Failover: DISABLED Configuration Status: DISABLED DGMGRL>
Enable the configuration with:
DGMGRL> enable configuration; Enabled. DGMGRL>
Primary instance alert logs says:
Mon Dec 29 21:39:33 2014 RSM0 started with pid=36, OS id=6478 Mon Dec 29 21:39:37 2014 Using STANDBY_ARCHIVE_DEST parameter default value as USE_DB_RECOVERY_FILE_DEST Mon Dec 29 21:39:37 2014 ALTER SYSTEM SET log_archive_dest_2='service="cdb0"','ASYNC NOAFFIRM delay=0 optional compression=disable max_failure=0 max_connections=1 reopen=300 db_unique_name="cdb0" net_timeout=30','valid_for=(online_logfile,all_roles)' SCOPE=BOTH; Mon Dec 29 21:39:37 2014 ****************************************************************** TT01: Setting 'active' archival for destination LOG_ARCHIVE_DEST_2 Mon Dec 29 21:39:37 2014 ALTER SYSTEM SET log_archive_dest_state_2='ENABLE' SCOPE=BOTH; ****************************************************************** Mon Dec 29 21:39:37 2014 Using STANDBY_ARCHIVE_DEST parameter default value as USE_DB_RECOVERY_FILE_DEST Mon Dec 29 21:39:37 2014 ALTER SYSTEM SET log_archive_dest_1='location=USE_DB_RECOVERY_FILE_DEST','valid_for=(ALL_LOGFILES, ALL_ROLES)' SCOPE=BOTH; Mon Dec 29 21:39:38 2014 ALTER SYSTEM SET log_archive_trace=0 SCOPE=BOTH SID='CDB1'; Mon Dec 29 21:39:38 2014 ALTER SYSTEM SET log_archive_format='%t_%s_%r.dbf' SCOPE=SPFILE SID='CDB1'; Mon Dec 29 21:39:38 2014 ALTER SYSTEM SET standby_file_management='AUTO' SCOPE=BOTH SID='*'; Mon Dec 29 21:39:38 2014 ALTER SYSTEM SET archive_lag_target=0 SCOPE=BOTH SID='*'; Mon Dec 29 21:39:38 2014 ALTER SYSTEM SET log_archive_max_processes=4 SCOPE=BOTH SID='*'; Mon Dec 29 21:39:38 2014 ALTER SYSTEM SET log_archive_min_succeed_dest=1 SCOPE=BOTH SID='*'; Mon Dec 29 21:39:38 2014 TT01: Standby redo logfile selected for thread 1 sequence 16 for destination LOG_ARCHIVE_DEST_2 Mon Dec 29 21:39:40 2014 LGWR: Failed to archive log 2 thread 1 sequence 16 (3113) Mon Dec 29 21:39:42 2014 Thread 1 advanced to log sequence 17 (LGWR switch) Current log# 3 seq# 17 mem# 0: +DATA/CDB1/ONLINELOG/group_3.260.867163077 Current log# 3 seq# 17 mem# 1: +FRA/CDB1/ONLINELOG/group_3.262.867163079 Mon Dec 29 21:39:45 2014 ****************************************************************** TT01: Setting 'active' archival for destination LOG_ARCHIVE_DEST_2 ****************************************************************** Mon Dec 29 21:39:45 2014 Archived Log entry 13 added for thread 1 sequence 16 ID 0x35159442 dest 1: Mon Dec 29 21:39:48 2014 TT01: Standby redo logfile selected for thread 1 sequence 17 for destination LOG_ARCHIVE_DEST_2
Standby alert log says:
Mon Dec 29 21:39:28 2014 NSV0 started with pid=30, OS id=6753 Starting background process RSM0 Mon Dec 29 21:39:33 2014 RSM0 started with pid=37, OS id=6759 Mon Dec 29 21:39:37 2014 Using STANDBY_ARCHIVE_DEST parameter default value as USE_DB_RECOVERY_FILE_DEST Mon Dec 29 21:39:37 2014 ALTER SYSTEM SET log_archive_dest_1='location=USE_DB_RECOVERY_FILE_DEST','valid_for=(ALL_LOGFILES, ALL_ROLES)' SCOPE=BOTH; Mon Dec 29 21:39:37 2014 ALTER SYSTEM SET log_archive_trace=0 SCOPE=BOTH SID='CDB0'; Mon Dec 29 21:39:37 2014 ALTER SYSTEM SET log_archive_format='%t_%s_%r.dbf' SCOPE=SPFILE SID='CDB0'; Mon Dec 29 21:39:37 2014 ALTER SYSTEM SET standby_file_management='AUTO' SCOPE=BOTH SID='*'; Mon Dec 29 21:39:37 2014 ALTER SYSTEM SET archive_lag_target=0 SCOPE=BOTH SID='*'; Mon Dec 29 21:39:37 2014 Network Resource Management enabled for Process (pid 6763) for Exadata I/O Primary database is in MAXIMUM PERFORMANCE mode Mon Dec 29 21:39:38 2014 ALTER SYSTEM SET log_archive_max_processes=4 SCOPE=BOTH SID='*'; Mon Dec 29 21:39:38 2014 ALTER SYSTEM SET log_archive_min_succeed_dest=1 SCOPE=BOTH SID='*'; Mon Dec 29 21:39:38 2014 ALTER SYSTEM SET fal_server='cdb1' SCOPE=BOTH; RFS[1]: Assigned to RFS process (PID:6763) RFS[1]: Selected log 4 for thread 1 sequence 16 dbid 890600873 branch 867160237 Mon Dec 29 21:39:46 2014 Network Resource Management enabled for Process (pid 6795) for Exadata I/O Primary database is in MAXIMUM PERFORMANCE mode Re-archiving standby log 4 thread 1 sequence 16 Mon Dec 29 21:39:47 2014 Archived Log entry 12 added for thread 1 sequence 16 ID 0x35159442 dest 1: RFS[2]: Assigned to RFS process (PID:6795) Mon Dec 29 21:39:47 2014 Media Recovery Waiting for thread 1 sequence 17 (in transit) Mon Dec 29 21:39:47 2014 RFS[2]: Selected log 4 for thread 1 sequence 17 dbid 890600873 branch 867160237 Mon Dec 29 21:39:49 2014 Recovery of Online Redo Log: Thread 1 Group 4 Seq 17 Reading mem 0 Mem# 0: +FRA/CDB0/ONLINELOG/group_4.266.867161821
DGMGRL> show configuration; Configuration - cdb Protection Mode: MaxPerformance Members: cdb1 - Primary database cdb0 - Physical standby database Fast-Start Failover: DISABLED Configuration Status: SUCCESS (status updated 32 seconds ago) DGMGRL> DGMGRL> show database cdb1; Database - cdb1 Role: PRIMARY Intended State: TRANSPORT-ON Instance(s): CDB1 Database Status: SUCCESS DGMGRL> show database cdb0; Database - cdb0 Role: PHYSICAL STANDBY Intended State: APPLY-ON Transport Lag: 0 seconds (computed 0 seconds ago) Apply Lag: 0 seconds (computed 0 seconds ago) Average Apply Rate: 0 Byte/s Real Time Query: OFF Instance(s): CDB0 Database Status: SUCCESS DGMGRL>
Note that SHOW CONFIGURATION reports the time when status has been updated, SHOW DATABASE reports also when transport lag and apply lag have been computed and that SHOW DATABASE also reports the average apply rate.
The new 12c VALIDATE DATABASE command allows to check switchover possibility. Run for standby database:
DGMGRL> validate database cdb0; Database Role: Physical standby database Primary Database: cdb1 Ready for Switchover: Yes Ready for Failover: Yes (Primary Running) Flashback Database Status: cdb1: Off cdb0: Off Current Log File Groups Configuration: Thread # Online Redo Log Groups Standby Redo Log Groups Status (cdb1) (cdb0) 1 3 2 Insufficient SRLs Future Log File Groups Configuration: Thread # Online Redo Log Groups Standby Redo Log Groups Status (cdb0) (cdb1) 1 3 2 Insufficient SRLs
Standby alert log says:
Mon Dec 29 21:44:17 2014 SWITCHOVER VERIFY BEGIN SWITCHOVER VERIFY WARNING: no standby database is defined in LOG_ARCHIVE_DEST_n to protect this database if it is converted to a primary database SWITCHOVER VERIFY COMPLETE
Note that the broker reports an insufficient number of standby redo log (SRL) groups. But this is wrong because standby has 3 onlinre redo log groups and 4 SRLs groups:
SYS@CDB0>select group# from v$log; GROUP# ---------- 1 2 3 SYS@CDB0>select group# from v$standby_log; GROUP# ---------- 4 5 6 7
Run for primary database:
DGMGRL> validate database cdb1; Database Role: Primary database Ready for Switchover: Yes Flashback Database Status: cdb1: Off DGMGRL>
Primary alert log says:
Mon Dec 29 21:44:18 2014 SWITCHOVER VERIFY: Send VERIFY request to switchover target cdb0 SWITCHOVER VERIFY COMPLETE SWITCHOVER VERIFY WARNING: switchover target has no standby database defined in LOG_ARCHIVE_DEST_n parameter. If the switchover target is converted to a primary database, the new primary database will not be protected. Data Guard Broker: Switchover processing will set LOG_ARCHIVE_DEST_n parameter. Continuing switchover
You must connect with database account and database password to standby instance:
DGMGRL> connect dga/dga@CDB0 Connected as SYSDG. DGMGRL> switchover to cdb0; Performing switchover NOW, please wait... New primary database "cdb0" is opening... Oracle Clusterware is restarting database "cdb1" ... Switchover succeeded, new primary is "cdb0" DGMGRL>
Former standby (new primary) alert log is here.
Former primary (new standby) alert log is here.
DGMGRL> show configuration; Configuration - cdb Protection Mode: MaxPerformance Members: cdb0 - Primary database cdb1 - Physical standby database Fast-Start Failover: DISABLED Configuration Status: SUCCESS (status updated 28 seconds ago) DGMGRL> show database cdb0; Database - cdb0 Role: PRIMARY Intended State: TRANSPORT-ON Instance(s): CDB0 Database Status: SUCCESS DGMGRL> show database cdb1; Database - cdb1 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: 3.00 KByte/s Real Time Query: OFF Instance(s): CDB1 Database Status: SUCCESS DGMGRL>
Another new 12c feature that has been used is that Oracle Restart (or RAC) does not require static Oracle Net aliases XXX_DGMGRL in listener.ora.
On standby node:
$ lsnrctl status LSNRCTL for Linux: Version 12.1.0.2.0 - Production on 29-DEC-2014 22:15:39 Copyright (c) 1991, 2014, Oracle. All rights reserved. Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=ol6twsa1)(PORT=1521))) STATUS of the LISTENER ------------------------ Alias LISTENER Version TNSLSNR for Linux: Version 12.1.0.2.0 - Production Start Date 29-DEC-2014 18:45:44 Uptime 0 days 3 hr. 29 min. 55 sec Trace Level off Security ON: Local OS Authentication SNMP OFF Listener Parameter File /u01/app/12.1.0.2/grid/network/admin/listener.ora Listener Log File /u01/app/oracle/diag/tnslsnr/ol6twsa1/listener/alert/log.xml Listening Endpoints Summary... (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=ol6twsa1)(PORT=1521))) (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521))) Services Summary... Service "+ASM" has 1 instance(s). Instance "+ASM", status READY, has 1 handler(s) for this service... Service "AUX" has 1 instance(s). Instance "CDB1", status UNKNOWN, has 1 handler(s) for this service... Service "CDB1" has 1 instance(s). Instance "CDB1", status READY, has 1 handler(s) for this service... Service "CDB1_DGB" has 1 instance(s). Instance "CDB1", status READY, has 1 handler(s) for this service... The command completed successfully $
On primary node:
$ lsnrctl status LSNRCTL for Linux: Version 12.1.0.2.0 - Production on 29-DEC-2014 22:18:07 Copyright (c) 1991, 2014, Oracle. All rights reserved. Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=ol6twsa0)(PORT=1521))) STATUS of the LISTENER ------------------------ Alias LISTENER Version TNSLSNR for Linux: Version 12.1.0.2.0 - Production Start Date 29-DEC-2014 18:45:39 Uptime 0 days 3 hr. 32 min. 28 sec Trace Level off Security ON: Local OS Authentication SNMP OFF Listener Parameter File /u01/app/12.1.0.2/grid/network/admin/listener.ora Listener Log File /u01/app/oracle/diag/tnslsnr/ol6twsa0/listener/alert/log.xml Listening Endpoints Summary... (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=ol6twsa0)(PORT=1521))) (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521))) Services Summary... Service "+ASM" has 1 instance(s). Instance "+ASM", status READY, has 1 handler(s) for this service... Service "CDB0" has 1 instance(s). Instance "CDB0", status READY, has 1 handler(s) for this service... Service "CDB0XDB" has 1 instance(s). Instance "CDB0", status READY, has 1 handler(s) for this service... Service "CDB0_DGB" has 1 instance(s). Instance "CDB0", status READY, has 1 handler(s) for this service... The command completed successfully $
Note that XXX_DGB services are created automatically by the Data Guard Broker.