Create a physical standby database with Oracle 12.1.0.2 and RMAN active duplication

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

  • Oracle Linux 6.5 64-bit with VirtualBox 4.3.16
  • Oracle Grid Infrastructure (GI) 12.1.0.2: database storage is using ASM so Oracle Managed Files (OMF) are automatically used
  • Oracle Database Entreprise Edition 12.1.0.2.
  • Primary database is a multitenant database named cdb0 with 2 pluggable databases pdb1 and pdb2.
  • Hostnames and database identifiers are detailed in the following table (0 means primary and 1 means standby):

    primary standby
    hostname ol6twsa0 ol6twsa1
    DB_NAME cdb cdb
    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 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:

    # 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  ]
    

    Oracle Net configuration

    On both nodes, add Oracle Net aliases for both databases and aux alias for RMAN DUPLICATE in Oracle Database home tnsnames.ora:

    CDB0 =
      (DESCRIPTION =
        (ADDRESS = (PROTOCOL = TCP)(HOST = ol6twsa0)(PORT = 1521))
        (CONNECT_DATA =
          (SERVER = DEDICATED)
          (SERVICE_NAME = cdb0)
        )
      )
    
    CDB1 =
      (DESCRIPTION =
        (ADDRESS = (PROTOCOL = TCP)(HOST = ol6twsa1)(PORT = 1521))
        (CONNECT_DATA =
          (SERVER = DEDICATED)
          (SERVICE_NAME = cdb1)
        )
      )
    
    AUX =
      (DESCRIPTION =
        (ADDRESS = (PROTOCOL = TCP)(HOST = ol6twsa1)(PORT = 1521))
        (CONNECT_DATA =
          (SERVER = DEDICATED)
          (SERVICE_NAME = aux)
        )
      )
    
    

    SQL*Plus configuration

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

    Put primary database in ARCHIVELOG mode

    $ 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 Tue Nov 25 20:10:41 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>
    
    

    Put primary database in force logging mode

    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>
    

    Set LOG_ARCHIVE_CONFIG and LOG_ARCHIVE_DEST_2 instance parameters on primary database

    SYS@cdb0>alter system set log_archive_config='dg_config=(cdb0,cdb1)';
    
    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_2 will trigger periodic errors (12514 and TNS-12564) in primary instance alert log because by default LOG_ARCHIVE_DEST_STATE_2 is set to ENABLED and primary instance cannot connect to standby instance which is not yet available.

    Create primary database password file if needed and copy it to standby machine

    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                                                                        100% 7680     7.5KB/s   00:00
    

    Set FAL_SERVER and FAL_CLIENT server on primary database

    SYS@cdb0>alter system set fal_server=cdb1;
    
    System altered.
    
    SYS@cdb0>alter system set fal_client=cdb0;
    
    System altered.
    
    SYS@cdb0>
    
    

    Create standby redo logs on primary database

    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.
    
    SYS@cdb0>
    
    

    Add static service for standby instance in GI Oracle Home listener.ora on standby machine

    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 25-NOV-2014 20:28:04
    
    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 25-NOV-2014 20:28:11
    
    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                25-NOV-2014 19:14:53
    Uptime                    0 days 1 hr. 13 min. 18 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
    $
    

    Create PFILE for standby database instance

    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 Tue Nov 25 20:31:14 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>exit
    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
    $
    

    Create needed AUDIT_FILE_DEST directory

    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 $ORACLE_BASE/admin/cdb/adump
    

    Prepare RMAN DUPLICATE script on standby machine

    $ cat dup.ksh
    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
    

    Start RMAN DUPLICATE script

    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.

    Set LOG_ARCHIVE_DEST_2, FAL_SERVER and FAL_CLIENT on standby database

    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>
    

    Start redo apply in real time mode on standby instance

    SQL>  alter database recover managed standby database using current logfile disconnect;
    
    Database altered.
    

    Check that standby instance alert log has similar output:

    Tue Nov 25 21:17:55 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
    Tue Nov 25 21:17:55 2014
    Attempt to start background Managed Standby Recovery process (cdb1)
    Starting background process MRP0
    Tue Nov 25 21:17:55 2014
    MRP0 started with pid=25, OS id=6717
    Tue Nov 25 21:17:55 2014
    MRP0: Background Managed Standby Recovery process started (cdb1)
    Tue Nov 25 21:18:00 2014
    Serial Media Recovery started
    Managed Standby Recovery starting Real Time Apply
    Tue Nov 25 21:18:00 2014
    Waiting for all non-current ORLs to be archived...
    Tue Nov 25 21:18:00 2014
    All non-current ORLs have been archived.
    Media Recovery Waiting for thread 1 sequence 34
    Completed: alter database recover managed standby database using current logfile disconnect
    Tue Nov 25 21:18:01 2014
    RFS[1]: Assigned to RFS process (PID:6731)
    RFS[1]: Opened log for thread 1 sequence 34 dbid 1979975503 branch 863371986
    Tue Nov 25 21:18:02 2014
    Archived Log entry 1 added for thread 1 sequence 34 rlc 863371986 ID 0x76151f3f dest 2:
    Tue Nov 25 21:18:03 2014
    Media Recovery Log +FRA/CDB1/ARCHIVELOG/2014_11_25/thread_1_seq_34.273.864595081
    Media Recovery Waiting for thread 1 sequence 35
    

    At this stage, trying to create a tablespace on primary database will not be replicated in “real-time” (if real time apply is enabled datafile creation should be displayed in standby alert log quickly – depending on current redo size – after primary alert log):

    SYS@cdb0>create tablespace ts2118;
    
    Tablespace created.
    
    

    To really enable real time apply, I needed to disable and enable redo transfer from primary instance with:

    SYS@cdb0>alter system set log_archive_dest_state_2=defer;
    
    System altered.
    
    SYS@cdb0>alter system set log_archive_dest_state_2=enable;
    
    System altered.
    
    SYS@cdb0>
    

    In standby alert log you should see similar output with datafile creation:

    Tue Nov 25 21:19:27 2014
    Network Resource Management enabled for Process  (pid 6746) for Exadata I/O
    Primary database is in MAXIMUM PERFORMANCE mode
    RFS[2]: Assigned to RFS process (PID:6746)
    RFS[2]: Selected log 4 for thread 1 sequence 36 dbid 1979975503 branch 863371986
    Tue Nov 25 21:19:29 2014
    RFS[3]: Assigned to RFS process (PID:6748)
    RFS[3]: Selected log 5 for thread 1 sequence 35 dbid 1979975503 branch 863371986
    Tue Nov 25 21:19:30 2014
    Archived Log entry 2 added for thread 1 sequence 35 ID 0x76151f3f dest 1:
    Tue Nov 25 21:19:30 2014
    Media Recovery Log +FRA/CDB1/ARCHIVELOG/2014_11_25/thread_1_seq_35.270.864595169
    Successfully added datafile 5 to media recovery
    Datafile #5: '+DATA/CDB1/DATAFILE/ts2118.269.864595171'
    Media Recovery Waiting for thread 1 sequence 36 (in transit)
    Tue Nov 25 21:19:36 2014
    Recovery of Online Redo Log: Thread 1 Group 4 Seq 36 Reading mem 0
      Mem# 0: +DATA/CDB1/ONLINELOG/group_4.274.864594793
      Mem# 1: +FRA/CDB1/ONLINELOG/group_4.262.864594795
    

    Add standby database to GI configuration

    $ srvctl add database -db cdb1 -oraclehome $ORACLE_HOME -spfile $ORACLE_HOME/dbs/spfilecdb1.ora -role PHYSICAL_STANDBY -startoption MOUNT -dbname cdb -diskgroup DATA,FRA
    

    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 the 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, convers the standby to the primary role.
  • 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.

    STEP 1: on primary instance, verify if switchover is possible

    SYS@cdb0>alter database switchover to cdb1 verify;
    
    Database altered.
    
    SYS@cdb0>
    

    Primary instance alert log says:

    Tue Nov 25 21:39:50 2014
    alter database switchover to cdb1 verify
    Tue Nov 25 21:39:51 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:

    Tue Nov 25 21:39:52 2014
    SWITCHOVER VERIFY BEGIN
    SWITCHOVER VERIFY COMPLETE
    

    STEP 2: on primary instance, switchover to standby

    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.

    STEP 3: open new primary database (former standby)

    SYS@cdb1>alter database open;
    
    Database altered.
    
    SYS@cdb1>
    

    Corresponding new primary alert log output is here.
    Note that connections errors to new standby expected because new standby instance has been shutdown.

    STEP 4: restart new standby instance (former primary)

    SYS@cdb0>startup mount;
    ORACLE instance started.
    
    Total System Global Area  641728512 bytes
    Fixed Size                  2927672 bytes
    Variable Size             549454792 bytes
    Database Buffers           83886080 bytes
    Redo Buffers                5459968 bytes
    Database mounted.
    SYS@cdb0>
    

    Corresponding new standby alert log is here.

    New primary alert log only says:

    Tue Nov 25 21:48:31 2014
    ALTER SYSTEM SET log_archive_dest_state_2='ENABLE' SCOPE=MEMORY SID='*';
    

    STEP 5: start redo apply on new standby

    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
    --------- ------------------------------ ---------------- --------------------
    CDB       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
    --------- ------------------------------ ---------------- --------------------
    CDB       CDB0                           PHYSICAL STANDBY MAXIMUM PERFORMANCE
    
    SYS@cdb0>
    
    

    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.
  • Follow

    Get every new post delivered to your Inbox.