Create an Oracle 12.2.0.1 physical standby database with DBCA

The purpose of this blog post is to detail physical standby database creation in the following configuration:

  • Oracle Linux 7.3 64-bit
  • Oracle Database Entreprise Edition 12.2.0.1
  • Database storage is using ASM
  • Primary database is a non-container database
  • Standby database is created with DBCA: this is a new 12.2.0.1 feature.
  • 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.

    Linux configuration

    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
    

    Oracle Net configuration

    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
    

    SQL*Plus configuration

    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>"
    

    Primary database configuration

    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
    

    Create the standby with DBCA

    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.

    Switchover

    Oracle Data Guard 11g Handbook page 20 (written by several Oracle Corp. employees implementing the Data Guard product) describes switchover process the following way:

  • notifies the primary database that a switchover is about to occur.
  • disconnects all users from the primary.
  • generates a special redo record that signals the End Of Redo (EOR).
  • converts the primary database into a standby database.
  • once the standby database applies the final EOR record, guaranteeing that no data has been lost, converts the standby to the primary role.
  • 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:

  • deciding what database protection you need and to change it if needed
  • deciding whether you want to manage Data Guard only with SQL statements or use Data Guard Broker
  • deciding whether you want to use Flashback database option (useful to rebuild primary database in case of failover)
  • deciding whether standby database should be backed up or not
  • deciding how to purge archived redo logs on standby
  • Advertisements