The purpose of this blog post is to detail physical standby database creation in the following configuration:
UPDATED 03-JAN-2015: I have updated this blog post to have all instance names and database names uppercase. I have also deferred setting LOG_ARCHIVE_DEST_STATE_2 on primary.
Hostnames and database identifiers are detailed in the following table (0 means primary and 1 means standby):
| primary | standby | |
|---|---|---|
| hostname | ol6twsa0 | ol6twsa1 |
| DB_NAME | CDB0 | CDB0 |
| DB_UNIQUE_NAME | CDB0 | CDB1 |
| datafile disk group name | +DATA | +DATA |
| fast recovery area disk group name | +FRA | +FRA |
Note that we need to have DB_UNIQUE_NAME different from DB_NAME for standby database because this is used by Data Guard to identify each database that must have the same DB_NAME.
You need to enable network connectivity between primary and standby machines. In this configuration I have not used DNS so I have added entries for primary and standby machines to /etc/hosts on both machines:
# cat /etc/hosts 127.0.0.1 localhost localhost.localdomain localhost4 localhost4.localdomain4 ::1 localhost localhost.localdomain localhost6 localhost6.localdomain6 # 192.168.56.76 ol6twsa0 ol6twsa0.localdomain 192.168.56.77 ol6twsa1 ol6twsa1.localdomain
You need also to configure or disable iptables (if not Oracle Net may report ORA-12543 when trying to connect from primary database to standby database):
ORA-12543: TNS:destination host unreachable
I have disabled iptables on both machines with root user:
# chkconfig --list iptables iptables 0:off 1:off 2:on 3:on 4:on 5:on 6:off # chkconfig iptables off # chkconfig --list iptables iptables 0:off 1:off 2:off 3:off 4:off 5:off 6:off # service iptables stop iptables: Setting chains to policy ACCEPT: filter [ OK ] iptables: Flushing firewall rules: [ OK ] iptables: Unloading modules: [ OK ]
On both nodes, add Oracle Net aliases for both databases and aux alias for RMAN DUPLICATE in Oracle Database home tnsnames.ora:
CDB1 =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = ol6twsa1)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = CDB1)
)
)
CDB0 =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = ol6twsa0)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = CDB0)
)
)
AUX =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = ol6twsa1)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = AUX)
)
)
In order to have the instance name displayed in SQL*Plus prompt with the current user, add in /home/oracle/.bash_profile:
export SQLPATH=/home/oracle/scripts
and create /home/oracle/scripts/login.sql with:
set sqlprompt "&&_USER@&&_CONNECT_IDENTIFIER>"
$ srvctl stop database -d CDB0 $ srvctl start database -d CDB0 -o mount $ sqlplus / as sysdba SQL*Plus: Release 12.1.0.2.0 Production on Wed Dec 24 14:11:08 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>alter database archivelog; Database altered. SYS@CDB0>alter database open; Database altered. SYS@CDB0>
SYS@CDB0>select force_logging from v$database; FORCE_LOGGING --------------------------------------- NO SYS@CDB0>alter database force logging; Database altered. SYS@CDB0>select force_logging from v$database; FORCE_LOGGING --------------------------------------- YES SYS@CDB0>
SYS@CDB0>alter system set log_archive_config='dg_config=(CDB0,CDB1)'; System altered. SYS@CDB0>alter system set log_archive_dest_state_2=defer; System altered. SYS@CDB0>alter system set log_archive_dest_2='service=CDB1 lgwr sync valid_for=(online_logfiles,primary_role) db_unique_name=CDB1'; System altered. SYS@CDB0>alter system set standby_file_management=auto; System altered. SYS@CDB0>
Note that setting LOG_ARCHIVE_DEST_STATE_2 to defer will avoid periodic errors (12514 and TNS-12564) in primary instance alert log because by default if LOG_ARCHIVE_DEST_STATE_2 is set to ENABLED then primary instance cannot connect to standby instance which is not yet available.
This assumes that ORACLE_HOME is the same on primary and standby machines.
$ orapwd file=$ORACLE_HOME/dbs/orapwCDB0 password=oracle12c $ scp $ORACLE_HOME/dbs/orapwCDB0 ol6twsa1:$ORACLE_HOME/dbs/orapwCDB1 oracle@ol6twsa1's password: orapwCDB0
SYS@CDB0>alter system set fal_server=CDB1; System altered. SYS@CDB0>alter system set fal_client=CDB0; System altered. SYS@CDB0>
Add 1 to existing number of online redo logs group (database has been created with default DBCA so database has 3 50 MB redo log groups):
SYS@CDB0>alter database add standby logfile '+FRA' size 50M; Database altered. SYS@CDB0>/ Database altered. SYS@CDB0>/ Database altered. SYS@CDB0>/ Database altered.
This is required by RMAN ACTIVE DUPLICATE that needs to use an Oracle Net connection to connect to auxiliary database instance. You need to use GLOBAL_DBNAME to set an Oracle Net alias different from SID_NAME:
SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(GLOBAL_DBNAME = AUX)
(ORACLE_HOME=/u01/app/12.1.0.2/db)
(SID_NAME = CDB1)
)
)
Reload listener on standby machine:
$ lsnrctl reload LSNRCTL for Linux: Version 12.1.0.2.0 - Production on 24-DEC-2014 14:20:02 Copyright (c) 1991, 2014, Oracle. All rights reserved. Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=ol6twsa1)(PORT=1521))) The command completed successfully
Check that aux service is registered and linked to auxiliary standby instance (even if standby instance is not yet started):
$ lsnrctl status LSNRCTL for Linux: Version 12.1.0.2.0 - Production on 24-DEC-2014 14:20:06 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 24-DEC-2014 10:21:38 Uptime 0 days 3 hr. 58 min. 27 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... The command completed successfully
This PFILE is used by the standby auxiliary instance and must be created in Oracle Database home:
$ cat $ORACLE_HOME/dbs/initCDB1.ora db_name=AUX
Start standby auxiliary instance:
$ export ORACLE_SID=CDB1 $ sqlplus / as sysdba SQL*Plus: Release 12.1.0.2.0 Production on Wed Dec 24 14:24:50 2014 Copyright (c) 1982, 2014, Oracle. All rights reserved. Connected to an idle instance. SYS@CDB1>startup nomount; ORACLE instance started. Total System Global Area 218103808 bytes Fixed Size 2922712 bytes Variable Size 159385384 bytes Database Buffers 50331648 bytes Redo Buffers 5464064 bytes SYS@CDB1>Disconnected from Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options $
It will be set to primary instance AUDIT_FILE_DEST by RMAN DUPLICATE (this also assumes that ORACLE_BASE has the same setting on both machines):
$ mkdir -p /u01/app/oracle/admin/CDB0/adump
$ cat dup.ksh
#
set -x
#
rman <<EOR
set echo on
connect target sys/oracle12c@CDB0
connect auxiliary sys/oracle12c@AUX
run {
duplicate target database for standby from active database
spfile
set 'db_unique_name'='CDB1'
set 'compatible'='12.1.0.2';
}
EOR
If you expect duplication to take a lot of time, this script should be run in background with at or nohup.
$ ./dup.ksh
This RMAN script must end with similar output:
Finished Duplicate Db at DD-MON-YY RMAN> Recovery Manager complete.
Detailed output is here.
SYS@CDB1>alter system set log_archive_dest_2='service=CDB0 lgwr sync valid_for=(online_logfiles,primary_role) db_unique_name=CDB0'; System altered. SYS@CDB1>alter system set fal_server='CDB0'; System altered. SYS@CDB1>alter system set fal_client='CDB1'; System altered. SYS@CDB1>
SYS@CDB1>alter database recover managed standby database using current logfile disconnect; Database altered.
Check that standby instance alert log has similar output:
Wed Dec 24 14:40:43 2014 Warning: ALTER DATABASE RECOVER MANAGED STANDBY DATABASE USING CURRENT LOGFILE has been deprecated. Warning: ALTER DATABASE RECOVER MANAGED STANDBY DATABASE USING CURRENT LOGFILE has been deprecated. alter database recover managed standby database using current logfile disconnect Wed Dec 24 14:40:43 2014 Attempt to start background Managed Standby Recovery process (CDB1) Starting background process MRP0 Wed Dec 24 14:40:43 2014 MRP0 started with pid=25, OS id=5635 Wed Dec 24 14:40:43 2014 MRP0: Background Managed Standby Recovery process started (CDB1) Wed Dec 24 14:40:48 2014 Serial Media Recovery started Managed Standby Recovery starting Real Time Apply Wed Dec 24 14:40:48 2014 Waiting for all non-current ORLs to be archived... Wed Dec 24 14:40:48 2014 All non-current ORLs have been archived. Media Recovery Waiting for thread 1 sequence 9 Completed: alter database recover managed standby database using current logfile disconnect
Now you can enable redo transfer from primary database instance:
SYS@CDB0>alter system set log_archive_dest_state_2=enable; System altered. SYS@CDB0>
Primary instance alert log should have similar output:
Wed Dec 24 14:42:05 2014 ALTER SYSTEM SET log_archive_dest_state_2='ENABLE' SCOPE=BOTH; Wed Dec 24 14:42:08 2014 Destination LOG_ARCHIVE_DEST_2 is UNSYNCHRONIZED ****************************************************************** LGWR: Setting 'active' archival for destination LOG_ARCHIVE_DEST_2 ****************************************************************** Starting background process NSS2 Wed Dec 24 14:42:08 2014 NSS2 started with pid=27, OS id=9437 LGWR: Standby redo logfile selected for thread 1 sequence 11 for destination LOG_ARCHIVE_DEST_2 Wed Dec 24 14:42:12 2014 Thread 1 advanced to log sequence 11 (LGWR switch) Current log# 2 seq# 11 mem# 0: +DATA/CDB0/ONLINELOG/group_2.259.867160241 Current log# 2 seq# 11 mem# 1: +FRA/CDB0/ONLINELOG/group_2.269.867160241 Wed Dec 24 14:42:13 2014 Archived Log entry 3 added for thread 1 sequence 10 ID 0x351521a7 dest 1: Wed Dec 24 14:42:14 2014 ARC1: Standby redo logfile selected for thread 1 sequence 10 for destination LOG_ARCHIVE_DEST_2
Standby instance alert log should have similar output:
Wed Dec 24 14:42:14 2014 Network Resource Management enabled for Process (pid 5660) for Exadata I/O Primary database is in MAXIMUM PERFORMANCE mode RFS[1]: Assigned to RFS process (PID:5660) RFS[1]: Selected log 4 for thread 1 sequence 11 dbid 890600873 branch 867160237 Wed Dec 24 14:42:15 2014 RFS[2]: Assigned to RFS process (PID:5658) RFS[2]: Opened log for thread 1 sequence 9 dbid 890600873 branch 867160237 Wed Dec 24 14:42:16 2014 Archived Log entry 1 added for thread 1 sequence 9 rlc 867160237 ID 0x351521a7 dest 2: Wed Dec 24 14:42:17 2014 RFS[3]: Assigned to RFS process (PID:5663) RFS[3]: Selected log 5 for thread 1 sequence 10 dbid 890600873 branch 867160237 Wed Dec 24 14:42:17 2014 Media Recovery Log +FRA/CDB1/ARCHIVELOG/2014_12_24/thread_1_seq_9.271.867163335 Wed Dec 24 14:42:18 2014 Archived Log entry 2 added for thread 1 sequence 10 ID 0x351521a7 dest 1: Wed Dec 24 14:42:18 2014 Media Recovery Log +FRA/CDB1/ARCHIVELOG/2014_12_24/thread_1_seq_10.270.867163339 Media Recovery Waiting for thread 1 sequence 11 (in transit) Wed Dec 24 14:42:20 2014 Recovery of Online Redo Log: Thread 1 Group 4 Seq 11 Reading mem 0 Mem# 0: +DATA/CDB1/ONLINELOG/group_4.275.867163081 Mem# 1: +FRA/CDB1/ONLINELOG/group_4.261.867163081
Test datafile creation on primary instance with:
SYS@CDB0>create tablespace ts1443; Tablespace created.
Check that on standby alert log that the datafile has been created:
Wed Dec 24 14:43:46 2014 Successfully added datafile 5 to media recovery Datafile #5: '+DATA/CDB1/DATAFILE/ts1443.272.867163423'
$ srvctl add database -db CDB1 -oraclehome $ORACLE_HOME -spfile $ORACLE_HOME/dbs/spfileCDB1.ora -role PHYSICAL_STANDBY -startoption MOUNT -dbname CDB1 -diskgroup DATA,FRA
Oracle Data Guard 11g Handbook page 20 (written by several Oracle Corp. employees implementing the Data Guard product) describes switchover process the following way:
There no data loss with switchover i.e. no transaction committed on the primary before the EOR is lost: it must be applied on the standby otherwise switchover cannot successfully complete.
I have used the new 12.1 statements for switchover.
To avoid ORA-16475 in switchover verification step you must set LOG_ARCHIVE_DEST_STATE_2 on standby to ENABLE:
SYS@CDB1>alter system set log_archive_dest_state_2=enable; System altered.
SYS@CDB0>alter database switchover to CDB1 verify; Database altered. SYS@CDB0>
Primary instance alert log says:
Wed Dec 24 14:56:23 2014 SWITCHOVER VERIFY: Send VERIFY request to switchover target CDB1 SWITCHOVER VERIFY COMPLETE Completed: alter database switchover to CDB1 verify
Standby instance alert log says:
Wed Dec 24 14:56:26 2014 SWITCHOVER VERIFY BEGIN SWITCHOVER VERIFY COMPLETE
SYS@CDB0>alter database switchover to CDB1; Database altered. SYS@CDB0>
Corresponding primary instance alert log output is here.
Corresponding standby instance alert log output is here.
SYS@CDB1>alter database open; Database altered. SYS@CDB1>
Corresponding new primary alert log output is here.
Note that connections errors to new standby are expected because new standby instance has been shutdown.
SYS@CDB0>startup mount; ORACLE instance started. Total System Global Area 641728512 bytes Fixed Size 2927672 bytes Variable Size 562037704 bytes Database Buffers 71303168 bytes Redo Buffers 5459968 bytes Database mounted. SYS@CDB0>
Corresponding new standby alert log is here.
Corresponding new primary alert log is here.
Note that you can ignore ORA-12514 ans TNS-12564 errors.
SYS@CDB0>alter database recover managed standby database using current logfile disconnect; Database altered. SYS@CDB0>
Corresponding new standby alert log here.
Switchover has been successfully completed: you can also confirm this by checking V$DATABASE.
On new primary:
SYS@CDB1>select name, db_unique_name, database_role, protection_mode from v$database; NAME DB_UNIQUE_NAME DATABASE_ROLE PROTECTION_MODE --------- ------------------------------ ---------------- -------------------- CDB0 CDB1 PRIMARY MAXIMUM PERFORMANCE SYS@CDB1>
On new standby:
SYS@CDB0>select name, db_unique_name, database_role, protection_mode from v$database; NAME DB_UNIQUE_NAME DATABASE_ROLE PROTECTION_MODE --------- ------------------------------ ---------------- -------------------- CDB0 CDB0 PHYSICAL STANDBY MAXIMUM PERFORMANCE SYS@CDB0>
Next steps should be: