The purpose of this blog post is to detail physical standby database creation in the following configuration:
Hostnames and database identifiers are detailed in the following table (0 suffix means primary and 1 suffix means standby):
| primary | standby | |
| hostname | ol7ttsa0 | ol7ttsa1 |
| DB_NAME | DB0 | DB0 |
| DB_UNIQUE_NAME | DB0 | DB1 |
| Oracle Net alias | ADB0 | ADB1 |
| ASM datafile directory | +DATA | +DATA |
| ASM fast recovery area directory | +RECO | +RECO |
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:
127.0.0.1 localhost localhost.localdomain localhost4 localhost4.localdomain4 ::1 localhost localhost.localdomain localhost6 localhost6.localdomain6 # 192.168.56.40 ol7ttfs0 ol7ttfs0.localdomain 192.168.56.29 ol7ttfs1 ol7ttfs1.localdomain
I have also enabled Oracle Net port 1521 oo both machines primary ...
# firewall-cmd --zone=public --add-port=1521/tcp success # firewall-cmd --zone=public --list-all public (active) target: default icmp-block-inversion: no interfaces: enp0s3 enp0s8 sources: services: dhcpv6-client ssh ports: 1521/tcp protocols: masquerade: no forward-ports: sourceports: icmp-blocks: rich rules: # firewall-cmd --zone=public --add-port=1521/tcp --permanent Warning: ALREADY_ENABLED: 1521:tcp success
... and standby:
# firewall-cmd --zone=public --add-port=1521/tcp success # firewall-cmd --zone=public --list-all public (active) target: default icmp-block-inversion: no interfaces: enp0s3 enp0s8 sources: services: dhcpv6-client ssh ports: 1521/tcp protocols: masquerade: no forward-ports: sourceports: icmp-blocks: rich rules: # firewall-cmd --zone=public --add-port=1521/tcp --permanent success
On both nodes I have added Oracle Net aliases for both databases in Oracle Database home tnsnames.ora:
ADB1=(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=ol7ttsa1)(PORT=1521))(CONNECT_DATA=(SERVICE_NAME=DB1))) # ADB0=(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=ol7ttsa0)(PORT=1521))(CONNECT_DATA=(SERVICE_NAME=DB0)))
I have also made sure that listener is started on primary node and that DB0 instance is registered:
$ lsnrctl status LSNRCTL for Linux: Version 12.2.0.1.0 - Production on 10-OCT-2017 15:47:19 Copyright (c) 1991, 2016, Oracle. All rights reserved. Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=ol7ttsa0.localdomain)(PORT=1521))) STATUS of the LISTENER ------------------------ Alias LISTENER Version TNSLSNR for Linux: Version 12.2.0.1.0 - Production Start Date 10-OCT-2017 11:26:12 Uptime 0 days 4 hr. 21 min. 7 sec Trace Level off Security ON: Local OS Authentication SNMP OFF Listener Parameter File /u01/gi12201/network/admin/listener.ora Listener Log File /u01/base/diag/tnslsnr/ol7ttsa0/listener/alert/log.xml Listening Endpoints Summary... (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=ol7ttsa0.localdomain)(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 "+ASM_DATA" has 1 instance(s). Instance "+ASM", status READY, has 1 handler(s) for this service... Service "+ASM_RECO" has 1 instance(s). Instance "+ASM", status READY, has 1 handler(s) for this service... Service "DB0" has 1 instance(s). Instance "DB0", status READY, has 1 handler(s) for this service... Service "DB0XDB" has 1 instance(s). Instance "DB0", status READY, has 1 handler(s) for this service... The command completed successfully
In order to have the instance name displayed in SQL*Plus prompt with the current user I have added in /home/oracle/.bash_profile:
export ORACLE_PATH=/home/oracle/scripts
and I have created /home/oracle/scripts/login.sql with following content:
set sqlprompt "&&_USER@&&_CONNECT_IDENTIFIER>"
I have configured primary database in ARCHIVELOG mode:
$ sqlplus / as sysdba SQL*Plus: Release 12.2.0.1.0 Production on Tue Oct 10 15:48:01 2017 Copyright (c) 1982, 2016, Oracle. All rights reserved. Connected to: Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production SYS@DB0>select db_unique_name, log_mode from v$database; DB_UNIQUE_NAME LOG_MODE ------------------------------ ------------ DB0 NOARCHIVELOG SYS@DB0>shutdown immediate Database closed. Database dismounted. ORACLE instance shut down. SYS@DB0>startup mount ORACLE instance started. Total System Global Area 1174405120 bytes Fixed Size 8619984 bytes Variable Size 436209712 bytes Database Buffers 721420288 bytes Redo Buffers 8155136 bytes Database mounted. SYS@DB0>alter database archivelog; Database altered. SYS@DB0>alter database open; Database altered. SYS@DB0>select db_unique_name, log_mode from v$database; DB_UNIQUE_NAME LOG_MODE ------------------------------ ------------ DB0 ARCHIVELOG SYS@DB0>
I have put primary database in force logging mode:
SYS@DB0>select force_logging from v$database; FORCE_LOGGING --------------------------------------- NO SYS@DB0>alter database force logging; Database altered. SYS@DB0>select force_logging from v$database; FORCE_LOGGING --------------------------------------- YES
I have set LOG_ARCHIVE_CONFIG and LOG_ARCHIVE_DEST_2 instance parameters on primary database:
SYS@DB0>alter system set log_archive_config='dg_config=(DB0,DB1)'; System altered. SYS@DB0>alter system set log_archive_dest_2='service=ADB1 lgwr sync valid_for=(online_logfiles, primary_role) db_unique_name=DB1'; System altered. SYS@DB0>alter system set standby_file_management=auto; System altered. SYS@DB0>alter system set log_archive_dest_state_2=defer; System altered.
Note that setting LOG_ARCHIVE_DEST_STATE_2 to defer will avoid periodic errors (TNS-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.
I have checked that primary database already has a password file:
SYS@DB0>select username from v$pwfile_users; USERNAME -------------------------------------------------------------------------------- SYS SYSDG SYSBACKUP SYSKM
I have set FAL_SERVER and FAL_CLIENT parameters on primary database:
SYS@DB0>alter system set fal_server=ADB1; System altered. SYS@DB0>alter system set fal_client=ADB0; System altered.
I have created standby redo logs on primary database (I have added 1 to existing number of online redo logs group):
OPS$ORACLE@DB0>select group#, bytes/1024/1024 from v$log;
GROUP# BYTES/1024/1024
---------- ---------------
1 100
2 100
3 100
OPS$ORACLE@DB0>alter database add standby logfile size 100M;
Database altered.
OPS$ORACLE@DB0>/
Database altered.
OPS$ORACLE@DB0>/
Database altered.
OPS$ORACLE@DB0>/
Database altered.
OPS$ORACLE@DB0>select group#, bytes/1024/1024 as mb from v$standby_log;
GROUP# MB
---------- ----------
4 100
5 100
6 100
7 100
OPS$ORACLE@DB0>
In order to avoid following errors:
ORA-17627: ORA-12543: TNS:destination host unreachable ORA-17629: Cannot connect to the remote database server
you need to open an additional port on the standby node with:
# firewall-cmd --zone=public --add-port=1522/tcp success
This is because DBCA starts a specific listener running on the first available port after the default listener port on the standby node
and primary instance needs to connect to auxiliary standby instance using this specific listener.
In my environement I have seen following listener.ora created in Oracle database home:
LISTENER20171010163413 =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = ol7ttsa1)(PORT = 1522))
)
SID_LIST_LISTENER20171010163413 =
(SID_LIST =
(SID_DESC =
(SID_NAME = DB1)
)
)
I have also checked the specific listener status:
$ lsnrctl status LISTENER20171010163413 LSNRCTL for Linux: Version 12.2.0.1.0 - Production on 10-OCT-2017 16:45:01 Copyright (c) 1991, 2016, Oracle. All rights reserved. Connecting to (ADDRESS=(PROTOCOL=TCP)(HOST=ol7ttsa1)(PORT=1522)) STATUS of the LISTENER ------------------------ Alias LISTENER20171010163413 Version TNSLSNR for Linux: Version 12.2.0.1.0 - Production Start Date 10-OCT-2017 16:34:31 Uptime 0 days 0 hr. 10 min. 29 sec Trace Level off Security ON: Local OS Authentication SNMP OFF Listener Parameter File /u01/db12201/network/admin/listener.ora Listener Log File /u01/oracle/diag/tnslsnr/ol7ttsa1/listener20171010163413/alert/log.xml Listening Endpoints Summary... (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=ol7ttsa1)(PORT=1522))) Services Summary... Service "DB1" has 1 instance(s). Instance "DB1", status UNKNOWN, has 1 handler(s) for this service... The command completed successfully
On standby node I have run following script:
$ dbca -silent -createDuplicateDB -primaryDBConnectionString ol7ttsa0:1521/DB0 -gdbName DB0 -sid DB1 -sysPassword oracle12c -createAsStandby -dbUniqueName DB1
Output is:
Listener config step 33% complete Auxiliary instance creation 66% complete RMAN duplicate 100% complete Look at the log file "/u01/oracle/cfgtoollogs/dbca/DB1/DB00.log" for further details.
I have run additional configuration steps that have not been run by DBCA to configure the new standby database:
SYS@DB1>alter system set log_archive_dest_2='service=ADB0 lgwr sync valid_for=(online_logfiles, primary_role) db_unique_name=DB0'; System altered. SYS@DB1>alter system set fal_server=ABD0; System altered. SYS@DB1>alter system set fal_client=ADB1; System altered.
I have enabled archive log destination on primary:
OPS$ORACLE@DB0>alter system set log_archive_dest_state_2=enable; System altered. OPS$ORACLE@DB0>
I have started redo apply in real time mode on standby instance:
SYS@DB1>alter database recover managed standby database using current logfile disconnect; Database altered. SYS@DB1>
I have checked standby instance alert log.
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 is 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.
To avoid ORA-16475 in switchover verification step you must set LOG_ARCHIVE_DEST_STATE_2 on standby to ENABLE:
SYS@DB1>alter system set log_archive_dest_state_2=enable; System altered. SYS@DB1>
I have used the new release 12 statements for switchover.
STEP 1: on primary instance, verify if switchover is possible
OPS$ORACLE@DB0>alter database switchover to DB1 verify; Database altered. OPS$ORACLE@DB0>
Primary instance alert log says:
017-10-10T17:07:22.228664+02:00 alter database switchover to DB1 verify 2017-10-10T17:07:22.615332+02:00 SWITCHOVER VERIFY: Send VERIFY request to switchover target DB1 SWITCHOVER VERIFY COMPLETE: READY FOR SWITCHOVER Completed: alter database switchover to DB1 verify
Standby instance alert log says:
2017-10-10T17:07:22.121851+02:00 SWITCHOVER VERIFY BEGIN SWITCHOVER VERIFY COMPLETE
STEP 2: on primary instance, switchover to standby
SYS@DB0>alter database switchover to DB1; Database altered. SYS@DB0>
Corresponding primary instance alert log output is here.
Corresponding standby instance alert log output is here.
STEP 3: open new primary database (former standby)
SYS@DB1>select * from dual; select * from dual * ERROR at line 1: ORA-03135: connection lost contact Process ID: 31152 Session ID: 60 Serial number: 49 SYS@DB1>Disconnected from Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production $ sqlplus / as sysdba SQL*Plus: Release 12.2.0.1.0 Production on Tue Oct 10 17:12:09 2017 Copyright (c) 1982, 2016, Oracle. All rights reserved. Connected to: Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production SYS@DB1>alter database open; Database altered. SYS@DB1>
Corresponding new primary alert log output is here
Note that connections errors to new standby are expected because new standby instance has been shutdown.
STEP 4: restart new standby instance (former primary)
SYS@DB0>select * from dual; select * from dual * ERROR at line 1: ORA-01034: ORACLE not available Process ID: 17974 Session ID: 75 Serial number: 39754 SYS@DB0>Disconnected from Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production $ sqlplus / as sysdba SQL*Plus: Release 12.2.0.1.0 Production on Tue Oct 10 17:16:48 2017 Copyright (c) 1982, 2016, Oracle. All rights reserved. Connected to an idle instance. SYS@DB0>startup mount; ORACLE instance started. Total System Global Area 1174405120 bytes Fixed Size 8619984 bytes Variable Size 436209712 bytes Database Buffers 721420288 bytes Redo Buffers 8155136 bytes Database mounted. SYS@DB0>
Corresponding new standby alert log is here
STEP 5: start redo apply on new standby
SYS@DB0>alter database recover managed standby database using current logfile disconnect; Database altered. SYS@DB0>
Corresponding new standby alert log is here.
Switchover has been successfully completed. I have checked V$DATABASE.DATABASE_ROLE:
On new primary:
SYS@DB1>select name, db_unique_name, database_role, protection_mode from v$database; NAME DB_UNIQUE_NAME DATABASE_ROLE PROTECTION_MODE --------- ------------------------------ ---------------- -------------------- DB0 DB1 PRIMARY MAXIMUM PERFORMANCE SYS@DB1>
On new standby:
SYS@DB0>select name, db_unique_name, database_role, protection_mode from v$database; NAME DB_UNIQUE_NAME DATABASE_ROLE PROTECTION_MODE --------- ------------------------------ ---------------- -------------------- DB0 DB0 PHYSICAL STANDBY MAXIMUM PERFORMANCE SYS@DB0>
Next steps should be: