ASYNC Standby Database Creation 19c

RMAN DUPLICATE FROM ACTIVE DATABASE command without shutting down the primary and using primary active database files (No need to take a backup)

Creating a standby database using RMAN (Recovery Manager) involves several steps. A standby database is a copy of the primary database that is kept in sync by applying redo logs, providing a failover option in case the primary database becomes unavailable. 

1. Configure the Primary Database for Data Guard

• Ensure the primary database is in ARCHIVELOG mode:

          SELECT log_mode FROM v$database;

• If not, enable ARCHIVELOG mode

         SHUTDOWN IMMEDIATE;
         ALTER DATABASE ARCHIVELOG;
         ALTER DATABASE OPEN;
         STARTUP MOUNT;

• Set up force logging to ensure that all transactions are logged:

          ALTER DATABASE FORCE LOGGING;

• Create a Password File 

          cd $ORACLE_HOME/dbs 

         orapwd file=$ORACLE_HOME/dbs/orapwprimary_db password=your_password entries=5

•  Configure primary database initialization parameters in the pfile or spfile. Add these parameters:

       DB_UNIQUE_NAME = 'primary_db'

       LOG_ARCHIVE_CONFIG = 'DG_CONFIG=(primary_db,standby_db)'

       LOG_ARCHIVE_DEST_1 = 'LOCATION=/archive_logs VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=primary_db'

      LOG_ARCHIVE_DEST_2 = 'SERVICE=standby_db ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=standby_db'
       LOG_ARCHIVE_FORMAT = 'arch_%t_%s_%r.arc'
       LOG_ARCHIVE_MAX_PROCESSES = 4
      REMOTE_LOGIN_PASSWORDFILE = EXCLUSIVE
      FAL_SERVER = standby_db
      FAL_CLIENT = primary_db

      STANDBY_FILE_MANAGEMENT = AUTO

This setting is relevant to ASYNC (asyncronous) datagurard setup.

• Configure Standby Redo Logs (Optional but recommended): Standby redo logs improve the efficiency of the standby database. Create them on the primary database with the same size as your online redo logs. Only if stanby Redo Logs will be created then the dataguard can be in realtime sync without the swith of logfiles.

Check the size and the name of the present log files

SELECT GROUP#,
MEMBER,
BYTES/1024/1024 AS SIZE_MB
FROM V$LOGFILE
JOIN V$LOG USING (GROUP#);

Add standby logfiles of same size. For naming convesion keep some buffer beween the start name of standby redo log file and end name of  primary redo logfile

ALTER DATABASE ADD STANDBY LOGFILE

      '<location_of_standby_redo_log_file_1>' SIZE <size>

      '<location_of_standby_redo_log_file_2>' SIZE <size>;

2. Prepare the Standby Server

• Ensure the standby server is ready to receive the database copy (hardware and OS configuration should match the primary).
• Install the same Oracle binaries as on the primary server.
• Create the necessary directory structure to hold datafiles, control files, archived logs, etc.
• Create a pfile for the standby database. Use the same DB_NAME as the primary but a unique DB_UNIQUE_NAME in the initialization parameters for the standby database. 

   DB_NAME = 'primary_db'
   DB_UNIQUE_NAME = 'standby_db'
   LOG_ARCHIVE_CONFIG = 'DG_CONFIG=(primary_db,standby_db)'
   LOG_ARCHIVE_DEST_1 = 'LOCATION=/archive_logs VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=standby_db'
   FAL_SERVER = primary_db
   FAL_CLIENT = standby_db

   STANDBY_FILE_MANAGEMENT = AUTO

• Set up a listener for the standby database, and configure the tnsnames.ora for both the primary and standby. 

Listener on the Primary Server (listener.ora) :

SID_LIST_LISTENER =
  (SID_LIST =
    (SID_DESC =
      (GLOBAL_DBNAME = primary_db)
      (ORACLE_HOME = /u01/app/oracle/product/19.0.0/dbhome_1)
      (SID_NAME = primary_db)
    )
  )

LISTENER =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = TCP)(HOST = primary_host)(PORT = 1521))
    )

  )

Listener on the Standby Server (listener.ora) :

SID_LIST_LISTENER =
  (SID_LIST =
    (SID_DESC =
      (GLOBAL_DBNAME = standby_db)
      (ORACLE_HOME = /u01/app/oracle/product/19.0.0/dbhome_1)
      (SID_NAME = standby_db)
    )
  )

LISTENER =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = TCP)(HOST = standby_host)(PORT = 1521))
    )
  )

After configuring the listener, start it on both servers:

lsnrctl start

tnsnames.ora on the Primary Server:

primary_db =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = primary_host)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = primary_db)
    )
  )

standby_db =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = standby_host)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = standby_db)
    )
  )


tnsnames.ora on the Standby Server:

primary_db =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = primary_host)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = primary_db)
    )
  )

standby_db =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = standby_host)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = standby_db)
    )
  )

Test the Connectivity: On the primary and secondary server:

tnsping standby_db

tnsping primary_db

• Copy the password file from the primary $ORACLE_HOME/dbs and rename it to the standby database name.The username is required to be SYS and the password needs to be the same on the Primary and Standby.The best practice for this is to copy the password file as suggested.The password file name must match the instance name/SID used at the standby site, not the DB_NAME.

• Set the environment variable ORACLE_SID to the standby service and start the standby-instance.

         export ORACLE_SID=standby_db
         sqlplus "/ as sysdba"

        SQL> startup nomount pfile=$ORACLE_HOME/dbs/init.ora

• On the secondary system invoke the RMAN executable and connect to the primary and the auxiliary database ( i.e., the standby)

rman target sys/<password>@primary_db auxiliary sys/<password>@secondary_db
RMAN> run {
allocate channel prmy1 type disk;
allocate channel prmy2 type disk;
allocate channel prmy3 type disk;
allocate channel prmy4 type disk;
allocate auxiliary channel stby type disk;
duplicate target database for standby from active database
spfile
  parameter_value_convert 'primary_db','secondary_db'
  set db_unique_name='secondary_db'
  set db_file_name_convert='/primary_db/','/boston/'
  set log_file_name_convert='/primary_db/','/secondary_db/'
  set control_files='/<path>/control01.ctl'
  set log_archive_max_processes='5'
  set fal_client='secondary_db'
  set fal_server='primary_db'
  set standby_file_management='MANUAL'
  set log_archive_config='dg_config=(primary_db,secondary_db)'
  set log_archive_dest_2='service=chicago ASYNC valid_for=(ONLINE_LOGFILE,PRIMARY_ROLE) db_unique_name=primary_db'
;

}

 3. Start managed recovery

Connect to standby using SQL*Plus and start the MRP (Managed Recovery Process). 

Compare the primary last sequence and MRP (Managed Recovery Process) applying sequence.

alter database recover managed standby database disconnect from session;

4. Open standby database in Read Only (active dataguard)

alter database recover managed standby database cancel;
alter database open;

alter database recover managed standby database disconnect;