Backup based Physical standby Creation using RMAN with Database storage on ASM (Oracle 11g)

Had a sit down last week to get Oracle DataGuard running with ASM on my laptop. Following are the results of the exercise. Basically a step by step on how to create a backup based physical standby using RMAN command where ASM is being used for storage.

Disclaimer: One or more statements in this blog might be incorrect, including this one.

Introduction

Environment Description

A. Physical Machines

B. Oracle Instances and Disk Groups

Assumptions

DG Creation steps

1. PREPARE INFRASTRUCTURE

      1. Prepare First node (DG1) which will hold the initial Primary DB of the DG     
          configuration

      2. Prepare Second node (DG2)

2. CONFIGURE THE MACHINES FOR REDO TRANSPORT

      1. Configure Oracle NET  

      2. Create Password File for Remote Admin access

3. CONFIGURE PRIMARY AND STANDBY DATABASES FOR THE 
     DATAGUARD CONFIGURATION

      1. Create Standby Redo logs (DG1)

      2. Enable Archiving, Force logging and flashback on ORCLA (DG1)

      3. Set required parameters (DG1)

      4. Create PFILE from SPFILE, edit it to be used as standy pfile and send it
          over to DG1 at $ORACLE_HOME/dbs/   

4. BACKUP PRIMARY DATABASE, ARCHIVELOGS and
    CONTROLFILE FOR STANDBY

5. START STANDBY INSTANCE (ORCLB) (DG2)

6. CREATE STANDBY USING RMAN FROM STANDBY SITE (DG2)

7. VERIFY REDO TRANSPORT

8. START REDO APPLY (DG2)

9. ACTIVE DATAGUARD

10. REAL TIME APPLY
           

11. SNAPSHOT STANDBY   

1. Steps to convert physical standby to snapshot standby

2. Steps to convert snapshot standby to physical standby

Introduction

Oracle Data Guard is a high availability, data protection, and disaster recovery solution that comes bundled with the Oracle database 11g Enterprise Edition.

To understand in layman terms, the Data Guard provides redundancy at the database level, as the RAC provides redundancy at the instance level.

Data Guard provides many services that create, maintain, manage, and monitor various standby databases (physical/logical) to protect the primary database from disasters and data corruption.

Data guard is an important element in the Oracle Maximum Availability Architecture (MAA).

Environment Description

A. Physical Machines:

               

    Two nodes DG1 and DG2 were configured with following IP addresses:

     DG1: 192.168.4.1

     DG2: 192.168.4.2

B. Oracle Instances and Disk Groups

               

    Below are the details of the RDBMS/ASM instances, ASM Disk groups on both the nodes

               

    ASM instance @DG1: +ASM  

    ASM instance @DG2: +ASM         

               

    RDBMS instance @DG1: ORCLA | Disk groups: DATA (for db files), FRA (recovery files)

    RDBMS instance @DG2: ORCLB | Disk groups: DATA (for db files), FRA (recovery files)

Assumptions

It is assumed that

1. the physical machines have been setup and are able to ping each other;
    /etc/hosts file entries have been configured.

2. following groups have been created on both the machines:
    dba, oinstall, asmadmin, asmdba, asmoper

3. following users have been created on both the machines:

            oracle -g oinstall -G oinstall,dba,asmdba

            grid    -g oinstall -G oinstall,asmadmin,asmdba,asmoper

4. 'oracleasm' has been configured on both the servers and ASM disks been
    created by its use thereof.

5. the Oracle RDBMS software has been installed on both the machines in

/u01/app/oracle/product/11.2.0/dbhome_1

6. the Grid Infrastructure has been installed on both the machines in

/u01/app/grid/product/11.2.0/gridhome_1

7. ASM diskgroups DATA, FRA have been created on both the machines

DG Creation steps

1. PREPARE INFRASTRUCTURE

   
    1. Prepare First node (DG1) which will hold the initial Primary DB 
        of the DG configuration:

               

        i. Login as 'oracle'. Install Oracle RDBMS Software

        ii. Login as 'grid'. Install Grid Infrastructure Software

        iii. Login as 'root'.Configure ASM disks DATA, FRA using oracleasm

        iv. Login as 'grid'.Create ASM diskgroups DATA, FRA using ASMCA or   

             ASMCMD utility

        v. Login as 'oracle'.Create database using ASM as storage using the DBCA                                 utility

    2. Prepare Second node (DG2)

               

        i. Login as 'oracle'. Install Oracle RDBMS Software

        ii. Login as 'grid'. Install Grid Infrastructure Software

        iii. Login as 'root'.Configure ASM disks DATA, FRA using oracleasm

        iv. Login as 'grid'.Create ASM diskgroups DATA, FRA using ASMCA or                                            ASMCMD utility

2. CONFIGURE THE MACHINES FOR REDO TRANSPORT

 
    1. Configure Oracle NET 

               

       i. Create Services (ORCLA, ORCLB) in TNSNAMES.ora file on both the                                  databases.                         

      
ORCLA =

  (DESCRIPTION =

      (ADDRESS_LIST =

        (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.4.1)(PORT = 1521))

      )

      (CONNECT_DATA =

              (SERVICE_NAME = orcla)

      )

  )

ORCLB =

  (DESCRIPTION =

      (ADDRESS_LIST =

        (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.4.2)(PORT = 1521))

      )

      (CONNECT_DATA =

              (SERVICE_NAME = orclb)

      )

  )

    ii. Create SID_LIST entries to statically register these services 

           in LISTENER.ora file for both the nodes.

         (SID_LIST=

           (SID_DESC=

             (GLOBAL_DBNAME=orcla)

             (SID_NAME=orcla)

             (ORACLE_HOME=/u01/app/oracle/product/11.2.0/dbhome_1)

           )

           (SID_DESC=

             (GLOBAL_DBNAME=orclb)

             (SID_NAME=orclb)

             (ORACLE_HOME=/u01/app/oracle/product/11.2.0/dbhome_1)

           )

           .

           .

         )

               

        It is recommended to use the NETMGR utility for this as these files
        are very cryptic and sensitive to whitespaces.

                            
        Verify the service configuration using TNSPING utility.

    2. Create Password File for Remote Admin access

     

        i.  Create password file for the ORCLA (DG1) database

               orapwd file=orapworcla password=mypassword

     ii.  Send over this password file to DG2 at location 

            $ORACLE_HOME/dbs/ and rename to orapworclb

               DG1> scp orapworcla oracle@dg2:$ORACLE_HOME/dbs/

         DG2> mv orapworcla orapworclb

3. CONFIGURE PRIMARY AND STANDBY DATABASES FOR THE 
    DATAGUARD CONFIGURATION

           

     1. Create Standby Redo logs (DG1)

         The standby redo logs (SRL) are just like the ordinary online redo logs
       (ORL), just that they receive redo data from another (primary) database.

       In case the SRL are configured, the atomic metric of redo data sent from
       the primary to standby is the change vector whereas if the online redo logs
       are not configured, the atomic metric of redo data sent from the primary to
       standby is the archive logfile.

                               

       Oracle recommends that the size of SRL's be equal to the size of ORL's
       and that the number of SRL's be one greater than number of ORL's for
       each thread.

       This is to account for gap between redo generation rate on primary and
       redo archival rate on standby.

               

                   n -> count of ORL groups (in my case it was 3)

                   n+1 -> count of SRL groups (recommended)

                               

                   size of ORL file = size of SRL file (recommended)

       Use following statement to find the redo log groups, members and
       the size of the online redo files.

  select a.group#, member, bytes/1024/1024 size_mb, type 
    from v$log a, v$logfile b 
  where a.group#=b.group#;

             

             

  SQL> ALTER DATABASE ADD STANDBY LOGFILE GROUP 4 ('+DATA', '+FRA') 
       size 50M;

      SQL> ALTER DATABASE ADD STANDBY LOGFILE GROUP 5 ('+DATA', '+FRA') 
           size 50M;

      SQL> ALTER DATABASE ADD STANDBY LOGFILE GROUP 6 ('+DATA', '+FRA') 

           size 50M;

      SQL> ALTER DATABASE ADD STANDBY LOGFILE GROUP 7 ('+DATA', '+FRA') 

           size 50M;

               

    2. Enable Archiving, Force logging and flashback on ORCLA (DG1)

               

i.                    Enable Archiving (required)

                     SQL> SHUTDOWN IMMEDIATE

                     SQL> STARTUP MOUNT

                     SQL> ALTER DATABASE ARCHIVELOG;

ii.                  Enable Force Logging (required)

                                 SQL> ALTER DATABASE FORCE LOGGING;

iii.                  Turn Flashback on (recommended and also required 
               for certain operations)

                                  SQL> ALTER DATABASE FLASHBACK ON;

                     SQL> ALTER DATABASE OPEN;

               

    3. Set required parameters (DG1)

               

      i.   SQL> alter system set DB_UNIQUE_NAME=orcla scope=both;

      ii.  SQL> alter system set 
               LOG_ARCHIVE_CONFIG='dg_config=(orcla,orclb)'

            scope=both;

      iii. SQL> alter system set 
            LOG_ARCHIVE_DEST_1='location=USE_DB_RECOVERY_FILE_DEST

                                valid_for=(all_logfiles,all_roles)

                                db_unique_name=orcla'

      scope=both;

     iv.  SQL> alter system set 
            LOG_ARCHIVE_DEST_2='service=orclb

                                valid_for=(online_logfiles,primary_roles)

                                db_unique_name=orclb'

    scope=both;

     v. REMOTE_LOGIN_PASSWORDFILE = EXCLUSIVE

     vi.  SQL> alter system set FAL_SERVER=orclb scope=both;

     vii. SQL> alter system set STANDBY_FILE_MANAGEMENT=auto scope=both;

     The parameters DB_FILE_NAME_CONVERT | LOG_FILE_NAME_CONVERT
     have not been used because we will use the DB_CREATE_FILE_DEST parameter
     in the initorclb.ora file

                                               

     When creating standby database with ASM (which in turn uses OMF), we can use
     either DB_CREATE_FILE_DEST or the CONVERT parameters in the
     standby target's init.ora file.

      We'll use DB_CREATE_FILE_DEST='+DATA'

    4. Create PFILE from SPFILE, edit it to be used as standy pfile and send it over

          to DG1 at $ORACLE_HOME/dbs/

               

          i. Create PFILE

                               

             SQL> create PFILE='$HOME/pfile/sby_pfile.ora' from spfile;

                               

          ii. Parameters of note in the standby PFILE

                               

        a. DB_UNIQUE_NAME = orclb

                       

        b. DB_NAME = orcla

                       

        c. LOG_ARCHIVE_CONFIG='dg_config(orcla,orclb)'

                       

        d. LOG_ARCHIVE_DEST_1='location=USE_DB_RECOVERY_FILE_DEST

                               valid_for=(all_logfiles,all_roles)

                               db_unique_name=orclb'

                                                                                   

              e. LOG_ARCHIVE_DEST_2='service=orcla 
                   valid_for=(online_logfiles,primary_roles)

                   db_unique_name=orcla'

                                         

        f. REMOTE_LOGIN_PASSWORDFILE = EXCLUSIVE

                       

        g. FAL_SERVER=orcla

                       

        h. STANDBY_FILE_MANAGEMENT = AUTO

                             
    5. Create required Directories (DG2)

        Based on the parameters present in the init.ora file for the
        orclb, create the directories.

4. BACKUP PRIMARY DATABASE, ARCHIVELOGS and CONTROLFILE 
    FOR STANDBY

     
    1. Backup database plus archivelog

    

        The controlfile for standby has to be backed up seperately because the 

         include current controlfile for standby' works only with backupsets 

         containing only the datafiles, while we are also backing up archivelogs.               

           

            RMAN> backup FORMAT '$HOME/bkups/%U.dbf'

             database plus archivelog tag=for_stand;

           

      2. Backup controlfile for standby

            RMAN> backup FORMAT '$HOME/bkups/%U.dbf

             current controlfile for standby;

       3. Copy these files to identical location on the DG2

            $ scp $HOME/bkups/* oracle@dg2:$HOME/bkups/

5. START STANDBY INSTANCE (ORCLB) (DG2)

     Start the Standby database instance ORCLA

                                    
     SQL> startup nomount

6. CREATE STANDBY USING RMAN FROM STANDBY SITE (DG2)

                
    1. Connect to target and auxiliary using RMAN

         RMAN> connect target sys/mypassword@orcla

     RMAN> connect auxiliary /

    2. Run DUPLICATE Command

     RMAN> run {

                allocate channel c1 device type disk;
                allocate auxiliary channel c2 device type disk;
                DUPLICATE TARGET DATABASE FOR STANDBY 
                    nofilenamecheck;      
               }

7. VERIFY REDO TRANSPORT

    Check that the redo transport is happening.

     1.       Check current log sequence number on the Primary               

            DG1_SQL> archive log list

   

     2.       Check current log sequence number on the Standby

       

            DG2_SQL> archive log list         

    

     3.       Switch log group on Primary             

                      
       DG1_SQL> alter system switch logfile;         

    

     4.       Check current log sequence number on the Standby. 

            It should have changed.           

            
       DG2_SQL> archive log list

8. START REDO APPLY (DG2)

     After confirming that redo transport is happening, we can start the redo apply
     on the standby database. In Physical Standby database, the MRP process is
     responsible for the redo apply.

   SQL> alter database RECOVER MANAGED STANDBY DATABASE 
        disconnect;   

9. ACTIVE DATAGUARD

    When redo apply is active, the physical standby database cannot be open because 
     it is constantly in recovery mode. If we OPEN the database in READ ONLY
     mode, we have to stop the redo apply.

     Active dataguard is a feature available in Oracle 11g Enterprise Edition that allows
     the standby database to be open in READ ONLY mode with redo apply active. 
     Active dataguard requires a separate licence and entices extra cost.

     To enable Active Dataguard, after STEP 8, perform the following steps 

     on standby db (orclb)

     i. SQL> shutdown immediate

    ii. SQL> startup  -- the physical standby will 

                         open in READ ONLY mode

     iii. SQL> alter database RECOVER MANAGED STANDBY DATABASE

  disconnect;         -- start redo apply 

     Now the database is in 'READ ONLY WITH APPLY' mode and ACTIVE dataguard 
     is configured


10. REAL TIME APPLY
   
       In normal redo apply mode, the MRP process applies the redo data from
       standby's Archived logs to the DB.
       With Real Time Apply, the MRP process applies the redo data directly from
       the Standby Redo Logs (SRL). The Standby redo logs are archived normally.

       This enables standby database to be updated with the redo data as soon as

        it is received and written to the SRL by the RFS process.

       To start Real time apply use the "USING CURRENT LOGFILE" clause when 

       starting the redo apply as:

            SQL> alter database recover managed standby database USING

CURRENT LOGFILE disconnect;          

11. SNAPSHOT STANDBY

A physical standby DB can be converted to a snapshot standby which is a READ WRITE image of the primary database in the DG configuration, from a particular instance of time.

Once the physical standby DB is converted to the snapshot standby database, the REDO APPLY cannot be active, but the redo transport keeps happening. The snapshot standby thus keeps diverging from the primary database because of lack of redo apply and also because of the changes being done on itself, since it is in Read Write mode.

               

Flashback database must be turned on to convert the snapshot standby back to physical standby.

               

1. Steps to convert physical standby to snapshot standby

               

i. Ensure that standby database is mounted but not open

                               

     SQL> select OPEN_MODE from v$database;

                               

ii. Stop redo apply if it is active

                               

     SQL> alter database RECOVER MANAGED STANDBY 
        DATABASE cancel;

                               

iii. Turn Flashback on (Best Practice)

                               

  SQL> alter database flashback on;

                               

iv. Convert physical standby to snapshot standby

                               

     SQL> ALTER DATABASE CONVERT TO SNAPSHOT
        STANDBY;                                          

               

2. Steps to convert snapshot standby to physical standby

               

i. Ensure that standby database is mounted but not open

                               

     SQL> select OPEN_MODE from v$database;

                               

ii. Convert physical standby to snapshot standby

                               

     SQL> ALTER DATABASE CONVERT TO PHYSICAL STANDBY;

                               

iii. Start Redo apply

               

     SQL> alter database recover managed standby 
        database USING CURRENT LOGFILE disconnect;