This article will show how to Create and Setup a dataguard physical standby with Oracle database 12cR2.
Before starting, you will need ...
... Oracle RDBMS 12c installed on primary and standby server
(Check Silent install of Oracle 12c RDBMS)
... Oracle database instance already created on the primary server
(Check Silent database 12c creation with dbca)
About licensing...
- Dataguard is included in Enterprise Edition (but Active Dataguard is an extra cost option)
- Both primary and standby database require a separate EE licenses
This table detailled primary and standby server information I used for this article:
| PRIMARY | STANDBY | |
| Hostname | oralab01 | oralab02 |
| DB_NAME | UXOCDBRA | UXOCDBRA |
| DB_UNIQUE_NAME | UXOCDBRAC | UXOSTBY |
| Datafile location | +DATA | /u01/app/oracle/oradata/UXOSTBY |
| Fast recovery area location | +FRA | /u01/app/oracle/fast_recovery_area/UXOSTBY |
Configure Primary db
On oralab01, connect to uxocdbrac with sqlplus / as sysdba :
-- If not already, Enable flashback -- Check with: select flashback_on from v$database; SQL> ALTER DATABASE FLASHBACK ON; Database altered. -- Enable force logging SQL> ALTER DATABASE FORCE LOGGING; Database altered. SQL> ALTER SYSTEM SWITCH LOGFILE; System SWITCH altered. -- Create standby log files to the primary server -- Size: same size or larger than existing redo logs -- Number: As many as existing redo logs plus one SQL> ALTER DATABASE ADD STANDBY LOGFILE '+FRA' SIZE 100M ; Database altered. SQL> ALTER DATABASE ADD STANDBY LOGFILE '+FRA' SIZE 100M ; Database altered. SQL> ALTER DATABASE ADD STANDBY LOGFILE '+FRA' SIZE 100M ; Database altered. SQL> ALTER DATABASE ADD STANDBY LOGFILE '+FRA' SIZE 100M ; Database altered. -- Check standby logs files SQL> SELECT GROUP#, THREAD#, SEQUENCE#, ARCHIVED, STATUS FROM V$STANDBY_LOG ; GROUP# THREAD# SEQUENCE# ARC STATUS ------ ---------- ---------- --- ---------- 1 0 0 YES UNASSIGNED 2 0 0 YES UNASSIGNED 3 0 0 YES UNASSIGNED 7 0 0 YES UNASSIGNED -- Set standby_file_management to auto SQL> show parameter STANDBY_FILE NAME TYPE VALUE ----------------------- ------ ------ standby_file_management string MANUAL SQL> ALTER SYSTEM SET STANDBY_FILE_MANAGEMENT=AUTO; System SET altered. -- Check Archive Log mode SQL> SELECT log_mode FROM v$database; LOG_MODE ------------ NOARCHIVELOG -- Activate Archive Log if NOARCHIVELOG SQL> SHUTDOWN IMMEDIATE; SQL> STARTUP MOUNT; SQL> ALTER DATABASE ARCHIVELOG; SQL> ALTER DATABASE OPEN; -- Get db_name SQL> show parameter db_name NAME TYPE VALUE ------- ------ -------- db_name string UXOCDBRA -- Get db_unique_name SQL> show parameter db_unique_name NAME TYPE VALUE -------------- ------ --------- db_unique_name string UXOCDBRAC
Add the following tns entries to $ORACLE_HOME/network/admin/tnsnames.ora:
UXOCDB = (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = oralab01)(PORT = 1521)) (CONNECT_DATA = (SERVICE_NAME = UXOCDBRAC) ) ) UXOSTBY = (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = oralab02)(PORT = 1521)) (CONNECT_DATA = (SERVICE_NAME = UXOSTBY) ) )
Create Physical Standby
Db configuration files
On oralab02 as oracle user:
Expand/Collapse
# Set env [oracle@oralab02]$ ORACLE_HOME=/u01/app/oracle/product/12.2.0/db1 # Copy or manually edit tnsnames.ora to add tns entries # [oracle]$ scp oracle@oralab01:$ORACLE_HOME/network/admin/tnsnames.ora $ORACLE_HOME/network/admin/. [oracle@oralab02]$ cat >> $ORACLE_HOME/network/admin/tnsnames.ora <<_EOF_ UXOCDB = (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = oralab01)(PORT = 1521)) (CONNECT_DATA = (SERVICE_NAME = UXOCDBRAC) ) ) UXOSTBY = (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = oralab02)(PORT = 1521)) (CONNECT_DATA = (SERVICE_NAME = UXOSTBY) ) ) _EOF_ # Copy or manually create oracle password # oracle paswword need to be the same for UXOCDBRAC and UXOSTBY # [oracle]$ scp oracle@oralab01:$ORACLE_HOME/network/admin/tnsnames.ora $ORACLE_HOME/network/admin/. [oracle@oralab02]$ orapwd file=/u01/app/oracle/product/12.2.0/db1/dbs/orapwUXOSTBY password=OraSys_pw0 entries=10 format=12 force=y # Add listener entery to listener.ora [oracle@oralab02]$ cat >> $(lsnrctl status | grep "Parameter File" | tr -s ' ' | cut -d' ' -f4) <<_EOF_ SID_LIST_LISTENER = (SID_LIST = (SID_DESC = (GLOBAL_DBNAME = UXOSTBY_DG) (ORACLE_HOME = /u01/app/oracle/product/12.2.0/db1) (SID_NAME = UXOSTBY) ) ) _EOF_ # Restart listener [oracle@oralab02]$ srvctl stop listener -node oralab02 [oracle@oralab02]$ srvctl start listener -node oralab02
Duplicate db for standby
On oralab02 as oracle user:
Expand/Collapse
[oracle@oralab02]$ echo "*.db_name='UXOCDBRA'" > /tmp/initUXOSTBY.ora [oracle@oralab02]$ mkdir -p /u01/app/oracle/oradata/UXOSTBY/ [oracle@oralab02]$ mkdir -p /u01/app/oracle/fast_recovery_area/UXOSTBY [oracle@oralab02]$ mkdir -p /u01/app/oracle/admin/UXOSTBY/adump [oracle@oralab02]$ export ORACLE_SID=UXOSTBY [oracle@oralab02]$ sqlplus / as sysdba SQL> STARTUP NOMOUNT PFILE='/tmp/initUXOSTBY.ora'; SQL> exit; [oracle@oralab02]$ rman TARGET sys/OraSys_pw0@UXOCDB AUXILIARY sys/OraSys_pw0@UXOSTBY Recovery Manager: Release 12.2.0.1.0 - Production Copyright (c) 1982, 2017, Oracle and/or its affiliates. All rights reserved. connected to target database: UXOCDBRA (DBID=3833405939) connected to auxiliary database: UXOSTBY (not mounted) RMAN> DUPLICATE TARGET DATABASE FOR STANDBY FROM ACTIVE DATABASE DORECOVER SPFILE SET db_unique_name='UXOSTBY' COMMENT 'Is standby' SET db_file_name_convert='+DATA/','/u01/app/oracle/oradata/UXOSTBY/' SET log_file_name_convert='+FRA/','/u01/app/oracle/fast_recovery_area/UXOSTBY/','+DATA/','/u01/app/oracle/oradata/UXOSTBY/' SET job_queue_processes='0' SET thread='0' SET instance_number='0' SET cluster_database='FALSE' SET control_files='/u01/app/oracle/oradata/UXOSTBY/controlfile/control01.ctl','/u01/app/oracle/fast_recovery_area/UXOSTBY/controlfile/control02.ctl' NOFILENAMECHECK; Starting Duplicate Db at 14/10/2017 12:44:43 using target database control file instead of recovery catalog allocated channel: ORA_AUX_DISK_1 channel ORA_AUX_DISK_1: SID=24 device type=DISK current log archived contents of Memory Script: { backup as copy reuse targetfile '+DATA/UXOCDBRAC/PASSWORD/pwduxocdbrac.285.957088799' auxiliary format '/u01/app/oracle/product/12.2.0/db1/dbs/orapwUXOSTBY' ; restore clone from service 'UXOCDB' spfile to '/u01/app/oracle/product/12.2.0/db1/dbs/spfileUXOSTBY.ora'; sql clone "alter system set spfile= ''/u01/app/oracle/product/12.2.0/db1/dbs/spfileUXOSTBY.ora''"; } executing Memory Script Starting backup at 14/10/2017 12:44:45 allocated channel: ORA_DISK_1 channel ORA_DISK_1: SID=47 instance=UXOCDB11 device type=DISK Finished backup at 14/10/2017 12:44:47 Starting restore at 14/10/2017 12:44:47 using channel ORA_AUX_DISK_1 channel ORA_AUX_DISK_1: starting datafile backup set restore channel ORA_AUX_DISK_1: using network backup set from service UXOCDB channel ORA_AUX_DISK_1: restoring SPFILE output file name=/u01/app/oracle/product/12.2.0/db1/dbs/spfileUXOSTBY.ora channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:01 Finished restore at 14/10/2017 12:44:49 sql statement: alter system set spfile= ''/u01/app/oracle/product/12.2.0/db1/dbs/spfileUXOSTBY.ora'' contents of Memory Script: { sql clone "alter system set db_unique_name = ''UXOSTBY'' comment= ''Is standby'' scope=spfile"; sql clone "alter system set db_file_name_convert = ''+DATA/'', ''/u01/app/oracle/oradata/'' comment= '''' scope=spfile"; sql clone "alter system set log_file_name_convert = ''+FRA/'', ''/u01/app/oracle/fast_recovery_area/'', ''+DATA/'', ''/u01/app/oracle/oradata/'' comment= '''' scope=spfile"; sql clone "alter system set job_queue_processes = 0 comment= '''' scope=spfile"; sql clone "alter system set thread = 0 comment= '''' scope=spfile"; sql clone "alter system set instance_number = 0 comment= '''' scope=spfile"; sql clone "alter system set cluster_database = FALSE comment= '''' scope=spfile"; sql clone "alter system set control_files = ''/u01/app/oracle/oradata/controlfile/control01.ctl'', ''/u01/app/oracle/fast_recovery_area/controlfile/control02.ctl'' comment= '''' scope=spfile"; shutdown clone immediate; startup clone nomount; } executing Memory Script sql statement: alter system set db_unique_name = ''UXOSTBY'' comment= ''Is standby'' scope=spfile sql statement: alter system set db_file_name_convert = ''+DATA/'', ''/u01/app/oracle/oradata/'' comment= '''' scope=spfile sql statement: alter system set log_file_name_convert = ''+FRA/'', ''/u01/app/oracle/fast_recovery_area/'', ''+DATA/'', ''/u01/app/oracle/oradata/'' comment= '''' scope=spfile sql statement: alter system set job_queue_processes = 0 comment= '''' scope=spfile sql statement: alter system set thread = 0 comment= '''' scope=spfile sql statement: alter system set instance_number = 0 comment= '''' scope=spfile sql statement: alter system set cluster_database = FALSE comment= '''' scope=spfile sql statement: alter system set control_files = ''/u01/app/oracle/oradata/controlfile/control01.ctl'', ''/u01/app/oracle/fast_recovery_area/controlfile/control02.ctl'' comment= '''' scope=spfile Oracle instance shut down connected to auxiliary database (not started) Oracle instance started Total System Global Area 3254779904 bytes Fixed Size 8798024 bytes Variable Size 872415416 bytes Database Buffers 2365587456 bytes Redo Buffers 7979008 bytes contents of Memory Script: { restore clone from service 'UXOCDB' standby controlfile; } executing Memory Script Starting restore at 14/10/2017 12:45:32 allocated channel: ORA_AUX_DISK_1 channel ORA_AUX_DISK_1: SID=466 device type=DISK channel ORA_AUX_DISK_1: starting datafile backup set restore channel ORA_AUX_DISK_1: using network backup set from service UXOCDB channel ORA_AUX_DISK_1: restoring control file channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:02 output file name=/u01/app/oracle/oradata/controlfile/control01.ctl output file name=/u01/app/oracle/fast_recovery_area/controlfile/control02.ctl Finished restore at 14/10/2017 12:45:35 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 tempfile 1 to "/u01/app/oracle/oradata/uxocdbrac/tempfile/temp.292.957088847"; set newname for tempfile 2 to "/u01/app/oracle/oradata/uxocdbrac/5b41d09d321678cae0531f00a8c01a09/tempfile/temp.290.957088847"; set newname for tempfile 3 to "/u01/app/oracle/oradata/uxocdbrac/5b431c857c2e1771e0531f00a8c0e26c/tempfile/temp.308.957094397"; switch clone tempfile all; set newname for datafile 1 to "/u01/app/oracle/oradata/uxocdbrac/datafile/system.286.957088835"; set newname for datafile 2 to "/u01/app/oracle/oradata/uxocdbrac/5b41d09d321678cae0531f00a8c01a09/datafile/system.297.957088837"; set newname for datafile 3 to "/u01/app/oracle/oradata/uxocdbrac/datafile/sysaux.293.957088843"; set newname for datafile 4 to "/u01/app/oracle/oradata/uxocdbrac/5b41d09d321678cae0531f00a8c01a09/datafile/sysaux.291.957088843"; set newname for datafile 5 to "/u01/app/oracle/oradata/uxocdbrac/datafile/undotbs1.289.957088845"; set newname for datafile 6 to "/u01/app/oracle/oradata/uxocdbrac/5b41d09d321678cae0531f00a8c01a09/datafile/undotbs1.294.957088847"; set newname for datafile 7 to "/u01/app/oracle/oradata/uxocdbrac/datafile/users.296.957088865"; set newname for datafile 8 to "/u01/app/oracle/oradata/uxocdbrac/5b431c857c2e1771e0531f00a8c0e26c/datafile/system.299.957094389"; set newname for datafile 9 to "/u01/app/oracle/oradata/uxocdbrac/5b431c857c2e1771e0531f00a8c0e26c/datafile/sysaux.306.957094389"; set newname for datafile 10 to "/u01/app/oracle/oradata/uxocdbrac/5b431c857c2e1771e0531f00a8c0e26c/datafile/undotbs1.307.957094389"; set newname for datafile 11 to "/u01/app/oracle/oradata/uxocdbrac/5b431c857c2e1771e0531f00a8c0e26c/datafile/users.304.957094407"; set newname for datafile 12 to "/u01/app/oracle/oradata/uxocdbrac/5b431c857c2e1771e0531f00a8c0e26c/datafile/mgmt_ecm_depot_ts.305.957113993"; set newname for datafile 13 to "/u01/app/oracle/oradata/uxocdbrac/5b431c857c2e1771e0531f00a8c0e26c/datafile/mgmt_tablespace.301.957113995"; set newname for datafile 14 to "/u01/app/oracle/oradata/uxocdbrac/5b431c857c2e1771e0531f00a8c0e26c/datafile/mgmt_ad4j_ts.300.957113995"; restore from nonsparse from service 'UXOCDB' clone database ; sql 'alter system archive log current'; } executing Memory Script executing command: SET NEWNAME executing command: SET NEWNAME executing command: SET NEWNAME renamed tempfile 1 to /u01/app/oracle/oradata/uxocdbrac/tempfile/temp.292.957088847 in control file renamed tempfile 2 to /u01/app/oracle/oradata/uxocdbrac/5b41d09d321678cae0531f00a8c01a09/tempfile/temp.290.957088847 in control file renamed tempfile 3 to /u01/app/oracle/oradata/uxocdbrac/5b431c857c2e1771e0531f00a8c0e26c/tempfile/temp.308.957094397 in control file executing command: SET NEWNAME executing command: SET NEWNAME executing command: SET NEWNAME executing command: SET NEWNAME executing command: SET NEWNAME executing command: SET NEWNAME executing command: SET NEWNAME executing command: SET NEWNAME executing command: SET NEWNAME executing command: SET NEWNAME executing command: SET NEWNAME executing command: SET NEWNAME executing command: SET NEWNAME executing command: SET NEWNAME Starting restore at 14/10/2017 12:45:41 using channel ORA_AUX_DISK_1 skipping datafile 1; already restored to SCN 3776013 skipping datafile 2; already restored to SCN 1344541 skipping datafile 3; already restored to SCN 3776013 skipping datafile 4; already restored to SCN 1344541 skipping datafile 5; already restored to SCN 3776013 skipping datafile 6; already restored to SCN 1344541 skipping datafile 7; already restored to SCN 3776013 skipping datafile 8; already restored to SCN 3776013 skipping datafile 9; already restored to SCN 3776013 skipping datafile 10; already restored to SCN 3776013 skipping datafile 11; already restored to SCN 3776013 skipping datafile 12; already restored to SCN 3776013 skipping datafile 13; already restored to SCN 3776013 skipping datafile 14; already restored to SCN 3776013 restore not done; all files read only, offline, excluded, or already restored Finished restore at 14/10/2017 12:45:42 sql statement: alter system archive log current current log archived contents of Memory Script: { restore clone force from service 'UXOCDB' archivelog from scn 3777598; switch clone datafile all; } executing Memory Script Starting restore at 14/10/2017 12:45:45 using channel ORA_AUX_DISK_1 channel ORA_AUX_DISK_1: starting archived log restore to default destination channel ORA_AUX_DISK_1: using network backup set from service UXOCDB channel ORA_AUX_DISK_1: restoring archived log archived log thread=1 sequence=17 channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:07 channel ORA_AUX_DISK_1: starting archived log restore to default destination channel ORA_AUX_DISK_1: using network backup set from service UXOCDB channel ORA_AUX_DISK_1: restoring archived log archived log thread=1 sequence=18 channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:02 Finished restore at 14/10/2017 12:45:54 datafile 1 switched to datafile copy input datafile copy RECID=1 STAMP=957357954 file name=/u01/app/oracle/oradata/uxocdbrac/datafile/system.286.957088835 datafile 2 switched to datafile copy input datafile copy RECID=2 STAMP=957357954 file name=/u01/app/oracle/oradata/uxocdbrac/5b41d09d321678cae0531f00a8c01a09/datafile/system.297.957088837 datafile 3 switched to datafile copy input datafile copy RECID=3 STAMP=957357954 file name=/u01/app/oracle/oradata/uxocdbrac/datafile/sysaux.293.957088843 datafile 4 switched to datafile copy input datafile copy RECID=4 STAMP=957357954 file name=/u01/app/oracle/oradata/uxocdbrac/5b41d09d321678cae0531f00a8c01a09/datafile/sysaux.291.957088843 datafile 5 switched to datafile copy input datafile copy RECID=5 STAMP=957357954 file name=/u01/app/oracle/oradata/uxocdbrac/datafile/undotbs1.289.957088845 datafile 6 switched to datafile copy input datafile copy RECID=6 STAMP=957357954 file name=/u01/app/oracle/oradata/uxocdbrac/5b41d09d321678cae0531f00a8c01a09/datafile/undotbs1.294.957088847 datafile 7 switched to datafile copy input datafile copy RECID=7 STAMP=957357954 file name=/u01/app/oracle/oradata/uxocdbrac/datafile/users.296.957088865 datafile 8 switched to datafile copy input datafile copy RECID=8 STAMP=957357954 file name=/u01/app/oracle/oradata/uxocdbrac/5b431c857c2e1771e0531f00a8c0e26c/datafile/system.299.957094389 datafile 9 switched to datafile copy input datafile copy RECID=9 STAMP=957357954 file name=/u01/app/oracle/oradata/uxocdbrac/5b431c857c2e1771e0531f00a8c0e26c/datafile/sysaux.306.957094389 datafile 10 switched to datafile copy input datafile copy RECID=10 STAMP=957357954 file name=/u01/app/oracle/oradata/uxocdbrac/5b431c857c2e1771e0531f00a8c0e26c/datafile/undotbs1.307.957094389 datafile 11 switched to datafile copy input datafile copy RECID=11 STAMP=957357954 file name=/u01/app/oracle/oradata/uxocdbrac/5b431c857c2e1771e0531f00a8c0e26c/datafile/users.304.957094407 datafile 12 switched to datafile copy input datafile copy RECID=12 STAMP=957357954 file name=/u01/app/oracle/oradata/uxocdbrac/5b431c857c2e1771e0531f00a8c0e26c/datafile/mgmt_ecm_depot_ts.305.957113993 datafile 13 switched to datafile copy input datafile copy RECID=13 STAMP=957357954 file name=/u01/app/oracle/oradata/uxocdbrac/5b431c857c2e1771e0531f00a8c0e26c/datafile/mgmt_tablespace.301.957113995 datafile 14 switched to datafile copy input datafile copy RECID=14 STAMP=957357954 file name=/u01/app/oracle/oradata/uxocdbrac/5b431c857c2e1771e0531f00a8c0e26c/datafile/mgmt_ad4j_ts.300.957113995 contents of Memory Script: { set until scn 3777926; recover standby clone database delete archivelog ; } executing Memory Script executing command: SET until clause Starting recover at 14/10/2017 12:45:55 using channel ORA_AUX_DISK_1 starting media recovery archived log for thread 1 with sequence 17 is already on disk as file +FRA/UXOSTBY/ARCHIVELOG/2017_10_14/thread_1_seq_17.349.957357949 archived log for thread 1 with sequence 18 is already on disk as file +FRA/UXOSTBY/ARCHIVELOG/2017_10_14/thread_1_seq_18.350.957357953 archived log file name=+FRA/UXOCDBRAC/ARCHIVELOG/2017_10_14/thread_1_seq_16.346.957357885 thread=1 sequence=16 archived log file name=+FRA/UXOSTBY/ARCHIVELOG/2017_10_14/thread_1_seq_17.349.957357949 thread=1 sequence=17 archived log file name=+FRA/UXOSTBY/ARCHIVELOG/2017_10_14/thread_1_seq_18.350.957357953 thread=1 sequence=18 media recovery complete, elapsed time: 00:00:01 Finished recover at 14/10/2017 12:45:59 Finished Duplicate Db at 14/10/2017 12:46:06
Dataguard standby database is now created, you can add it to the cluster or oracle restart as follow:
[oracle@oralab02]$ srvctl add database -db UXOSTBY \ -oraclehome /u01/app/oracle/product/12.2.0/db1 \ -dbtype SINGLE -instance UXOSTBY \ -pwfile /u01/app/oracle/product/12.2.0/db1/dbs/orapwUXOSTBY \ -spfile /u01/app/oracle/product/12.2.0/db1/dbs/spfileUXOSTBY.ora \ -role PHYSICAL_STANDBY -dbname UXOCDBRA \ -startoption mount -node oralab02
Configure dataguard
We now have a primary database and a standby database, we need to start the Data Guard Broker first. So execute the following SQL command on the both databases :
SQL> ALTER SYSTEM SET dg_broker_start=true;
Use dgmgrl tool on oralab01 primary server to configure dataguard:
Expand/Collapse
[oracle]$ dgmgrl sys/OraSys_pw0@UXOCDB DGMGRL for Linux: Release 12.2.0.1.0 - Production Copyright (c) 1982, 2017, Oracle and/or its affiliates. All rights reserved. Welcome to DGMGRL, type "help" for information. Connected to "UXOCDBRAC" Connected as SYSDG. DGMGRL> CREATE CONFIGURATION uxocdb_dg_config AS PRIMARY DATABASE IS UXOCDBRAC CONNECT IDENTIFIER IS UXOCDBRAC; Configuration "uxocdb_dg_config" created with primary database "uxocdbrac" DGMGRL> ADD DATABASE UXOSTBY AS CONNECT IDENTIFIER IS UXOSTBY MAINTAINED AS PHYSICAL; Database "uxostby" added DGMGRL> ENABLE CONFIGURATION; Enabled. DGMGRL> show configuration ; Configuration - uxocdb_dg_config Protection Mode: MaxPerformance Members: uxocdbrac - Primary database uxostby - Physical standby database Fast-Start Failover: DISABLED Configuration Status: SUCCESS (status updated 59 seconds ago)
Test dataguard
switchover test
[oracle]$ dgmgrl sys/OraSys_pw0@UXOSTBY -- Initial configuration DGMGRL> show configuration ; Configuration - uxocdb_dg_config Protection Mode: MaxPerformance Members: uxocdbrac - Primary database uxostby - Physical standby database Fast-Start Failover: DISABLED Configuration Status: SUCCESS (status updated 59 seconds ago) -- Initiate switchover DGMGRL> SWITCHOVER TO uxostby ; Performing switchover NOW, please wait... Operation requires a connection to database "uxostby" Connecting ... Connected to "UXOSTBY" Connected as SYSDBA. New primary database "uxostby" is opening... Oracle Clusterware is restarting database "uxocdbrac" ... Connected to "UXOCDBRAC" Connected to "UXOCDBRAC" Switchover succeeded, new primary is "uxostby" -- Check again configuration to check uxostby is now primary DGMGRL> show configuration ; Configuration - uxocdb_dg_config Protection Mode: MaxPerformance Members: uxostby - Primary database uxocdbrac - Physical standby database Fast-Start Failover: DISABLED Configuration Status: SUCCESS (status updated 21 seconds ago) -- Switch back to uxocdbrac as primary DGMGRL> SWITCHOVER TO UXOCDBRAC
failover test
[oracle]$ dgmgrl sys/OraSys_pw0@UXOSTBY -- Initial configuration DGMGRL> show configuration ; Configuration - uxocdb_dg_config Protection Mode: MaxPerformance Members: uxocdbrac - Primary database uxostby - Physical standby database Fast-Start Failover: DISABLED Configuration Status: SUCCESS (status updated 59 seconds ago) -- Initiate failover DGMGRL> FAILOVER TO UXOSTBY ; Performing failover NOW, please wait... Failover succeeded, new primary is "uxostby" -- Show configuration after failover DGMGRL> show configuration ; Configuration - uxocdb_dg_config Protection Mode: MaxPerformance Members: uxostby - Primary database uxocdbrac - Physical standby database (disabled) ORA-16661: the standby database needs to be reinstated Fast-Start Failover: DISABLED Configuration Status: SUCCESS (status updated 14 seconds ago) -- Initiate reinstate to get uxocdbrac as standby DGMGRL> REINSTATE DATABASE uxocdbrac ; Reinstating database "uxocdbrac", please wait... Reinstatement of database "uxocdbrac" succeeded -- Show configuration after reinstate DGMGRL> show configuration ; Configuration - uxocdb_dg_config Protection Mode: MaxPerformance Members: uxostby - Primary database uxocdbrac - Physical standby database Fast-Start Failover: DISABLED Configuration Status: SUCCESS (status updated 6 seconds ago) -- Switch back to uxocdbrac as primary DGMGRL> SWITCHOVER TO UXOCDBRAC ;
Errors encounter and fix
ORA-16653 on reinstate database
error logs
Log on UXOCDBRAC:
/u01/app/oracle/diag/rdbms/uxocdbrac/UXOCDB11/trace/drcUXOCDB11.log
... 10/14/2017 16:50:45 Forwarding CTL_REINST_ENABLE operation to member uxostby for processing 10/14/2017 16:50:47 Updated broker configuration file available, loading from "/u01/app/oracle/product/12.2.0/db1/dbs/dr2UXOCDBRAC.dat" 10/14/2017 16:50:51 Failed to connect to remote database uxostby. Error is ORA-12514 Failed to send message to member uxostby. Error code is ORA-12514. ...
LOG on UXOSTBY:
/u01/app/oracle/diag/rdbms/uxostby/UXOSTBY/trace/drcUXOSTBY.log
... 10/14/2017 16:50:45 REINSTATE DATABASE uxocdbrac Database uxocdbrac can be reinstated 10/14/2017 16:51:06 Site 1 encountered error 16653. Please fix the error and retry the REINSTATE DATABASE command again RSM0 failed to defer destination, error ORA-3 REINSTATE DATABASE uxocdbrac completed with error ORA-16653 ...
solution
First check listener log for error, and I can find that:
/u01/app/grid/diag/tnslsnr/oralab02/listener/alert/log.xml
... <msg time='2017-10-14T16:50:51.291+02:00' org_id='oracle' comp_id='tnslsnr' type='UNKNOWN' level='16' host_id='oralab02.uxora.com' host_addr='192.168.0.32' pid='18639'> <txt>14-OCT-2017 16:50:51 * (CONNECT_DATA=(SERVER=DEDICATED)(SERVICE_NAME=uxostby_DGB)(INSTANCE_NAME=UXOSTBY)(CID=(PROGRAM=oracle)(HOST=oralab01.uxora.com)(USER=oracle))) * (ADDRESS=(PROTOCOL=tcp)(HOST=192.168.0.31)(PORT=31098)) * establish * uxostby_DGB * 12514 </txt> </msg> <msg time='2017-10-14T16:50:51.291+02:00' org_id='oracle' comp_id='tnslsnr' type='UNKNOWN' level='16' host_id='oralab02.uxora.com' host_addr='192.168.0.32' pid='18639'> <txt>TNS-12514: TNS:listener does not currently know of service requested in connect descriptor </txt> </msg> ...
oracle try to connect to standby database with (SERVICE_NAME=uxostby_DGB) but it does not exist.
Normally It should exist if you have added standby database to the cluster (or Oracle restart) with srvctl add database.
If not, you can manually add it by adding this entry to /u01/app/12.2.0/grid/network/admin/listener.ora:
$ cat >> $(lsnrctl status | grep "Parameter File" | tr -s ' ' | cut -d' ' -f4) <<_EOF_ SID_LIST_LISTENER = (SID_LIST = (SID_DESC = (GLOBAL_DBNAME = UXOSTBY_DG) (ORACLE_HOME = /u01/app/oracle/product/12.2.0/db1) (SID_NAME = UXOSTBY) ) (SID_DESC = (GLOBAL_DBNAME = UXOSTBY_DGB) (ORACLE_HOME = /u01/app/oracle/product/12.2.0/db1) (SID_NAME = UXOSTBY) ) ) _EOF_
Warning: ORA-16714
error log
Just after creating dataguard configuration with dgmgrl: Expand/Collapse
DGMGRL> SHOW DATABASE UXOSTBY Database - uxostby Role: PHYSICAL STANDBY Intended State: APPLY-ON Transport Lag: 0 seconds (computed 1 second ago) Apply Lag: (unknown) Average Apply Rate: (unknown) Real Time Query: OFF Instance(s): UXOSTBY Warning: ORA-16714: the value of property ArchiveLagTarget is inconsistent with the member setting Warning: ORA-16714: the value of property LogArchiveMaxProcesses is inconsistent with the member setting Warning: ORA-16714: the value of property LogArchiveMinSucceedDest is inconsistent with the member setting Warning: ORA-16714: the value of property DataGuardSyncLatency is inconsistent with the member setting Warning: ORA-16714: the value of property LogArchiveTrace is inconsistent with the member setting Warning: ORA-16675: database instance restart required for property value modification to take effect Warning: ORA-16714: the value of property LogArchiveFormat is inconsistent with the member setting Database Error(s): ORA-16766: Redo Apply is stopped Database Warning(s): ORA-16854: apply lag could not be determined Database Status: ERROR
solution
Check inconsistent properties and fix it in database (standby db in this case):
Expand/Collapse
[oracle@oralab02]$ dgmgrl / DGMGRL> show database UXOSTBY InconsistentProperties ; INCONSISTENT PROPERTIES INSTANCE_NAME PROPERTY_NAME MEMORY_VALUE SPFILE_VALUE BROKER_VALUE UXOSTBY ArchiveLagTarget 0 0 UXOSTBY LogArchiveMaxProcesses 4 4 UXOSTBY LogArchiveMinSucceedDest 1 1 UXOSTBY DataGuardSyncLatency 0 0 UXOSTBY LogArchiveTrace 0 (missing) 0 UXOSTBY LogArchiveFormat %t_%s_%r.dbf (missing) %t_%s_%r.dbf [oracle@oralab02]$ sqlplus / as sysdba SQL> alter system set archive_lag_target=0 scope=both sid='*'; SQL> alter system set log_archive_max_processes=4 scope=both sid='*'; SQL> alter system set log_archive_min_succeed_dest=1 scope=both sid='*'; SQL> alter system set data_guard_sync_latency=0 scope=both sid='*'; SQL> alter system set log_archive_trace=0 scope=both sid='*'; SQL> alter system set log_archive_format='%t_%s_%r.dbf' scope=spfile sid='*'; SQL> shutdown immediate; SQL> startup mount; [oracle@oralab02]$ dgmgrl / "show database UXOSTBY" Database - uxostby 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: 47.00 KByte/s Real Time Query: OFF Instance(s): UXOSTBY Database Status: SUCCESS
Others
Activating Fast-Start Failover (FSFO)
On primary server as oracle:
# Enable FSFO [oracle]$ dgmgrl / "ENABLE FAST_START FAILOVER;" Enabled. # Create observer [oracle]$ nohup dgmgrl sys/OraSys_pw0 "start observer uxocdb_dg_obs" 2>&1 > $HOME/uxocdb_dg_obs.log &
You can create a second observer on the standby server with the same command:
nohup dgmgrl sys/OraSys_pw0 "start observer uxostby_dg_obs" 2>&1 > $HOME/uxostby_dg_obs.log &
Setting RMAN deletion policy
On primary database, set :
RMAN> configure archivelog deletion policy to shipped to all standby backed up 1 times to disk;
On all standby database, set :
RMAN> configure archivelog deletion policy to applied on standby;
Removing standby db
[oracle@oralab01]$ dgmgrl sys/OraSys_pw0@UXOCDB -- Check current configuration DGMGRL> show configuration ; Configuration - uxocdb_dg_config Protection Mode: MaxPerformance Members: uxocdbrac - Primary database uxostby - Physical standby database Error: ORA-16810: multiple errors or warnings detected for the member Fast-Start Failover: DISABLED Configuration Status: ERROR (status updated 52 seconds ago) -- Disable stby db DGMGRL> disable database uxostby ; Disabled. -- Remove stby db from configuration - but error DGMGRL> remove database uxostby ; Error: ORA-16867: cannot remove a database that is specified in a FastStartFailoverTarget configuration property -- Error - need to disable configuration before DGMGRL> disable configuration ; Disabled. -- Now stby db can be removed from configuration DGMGRL> remove database uxostby ; Removed database "uxostby" from the configuration -- Check configuration again DGMGRL> show configuration; Configuration - uxocdb_dg_config Protection Mode: MaxPerformance Members: uxocdbrac - Primary database Fast-Start Failover: DISABLED Configuration Status: DISABLED -- OPTIONAL - Delete configuration DGMGRL> remove configuration ; Removed configuration [oracle@oralab01]$ sqlplus / as sysdba SQL> ALTER SYSTEM SET dg_broker_start=false ; SQL> show parameter log_archive_config; NAME TYPE VALUE ------------------ ------ --------------------- log_archive_config string dg_config=(UXOCDBRAC) [oracle@oralab01]$ srvctl stop database -db uxostby -stopoption abort [oracle@oralab01]$ srvctl remove database -db uxostby # Delete stby db on oralab02 server [oracle@oralab02]$ dbca -silent -deleteDatabase -sourceDB UXOSTBY -sysDBAUserName sys -sysDBAPassword OraSys_pw0 # Then delete manually stby db datafiles if not done by dbca
HTH,
Michel.
Reference
Data Guard Concepts and Administration (docs.oracle.com)
Data Guard Physical Standby Setup Using the Data Guard Broker in Oracle Database 12c (oracle-base.com)
Enjoyed this article? Please like it or share it.
Please connect with one of social login below (or fill up name and email)