Data Guard Physical Standby Setup Using Active Duplicate

Data Guard Physical Standby Setup Using Active Duplicate

VM Machine Details:

node1.oracle.com (147.43.0.15)

node2.oracle.com (147.43.0.16)

Note:

1. Both VM machines should be ping each, Network adapter setting choose "Host-Only".

2. Configure or add both machine ip addresses in /etc/hosts file.

3. Check or test the connectivity using ping or ssh commands.

Database Details:

Database Name :- PRODUAT

Primary db_unique_name :- PRODUAT

standby db_unique_name :- PRODSIT

Steps:

1. Ensure that the database is in archivelog mode.

SQL> select log_mode from v$database;

LOG_MODE

------------

ARCHIVELOG

2. Enable force logging.

SQL> ALTER DATABASE FORCE LOGGING;

Database altered.

-- Make sure at least one logfile is present.

ALTER SYSTEM SWITCH LOGFILE;

SQL> select force_logging from v$database;

FORCE_LOGGING

---------------------------------------

YES

3. Create standby redologs.

Minimally, the configuration should have one more standby redo log file group than the number of online redo log file groups on the primary database. However, the recommended number of standby redo log file groups is dependent on the number of threads on the primary database. Use the following equation to determine an appropriate number of standby redo log file groups:

(maximum number of logfiles for each thread + 1) * maximum number of threads

SQL> select bytes from v$standby_log;

no rows selected

SQL> SELECT * FROM V$LOGFILE;

SQL> select group#,thread#,bytes from v$log;

    GROUP#    THREAD#      BYTES

---------- ---------- ----------

         1          1   52428800

         2          1   52428800

         3          1   52428800

alter database add standby logfile '/u01/app/oracle/fast_recovery_area/PRODUAT/stbyredo03.log' size 50M;

alter database add standby logfile '/u01/app/oracle/fast_recovery_area/PRODUAT/stbyredo04.log' size 50M;

alter database add standby logfile '/u01/app/oracle/fast_recovery_area/PRODUAT/stbyredo05.log' size 50M;

SQL> select bytes from v$standby_log;

     BYTES

----------

  52428800

  52428800

  52428800

  52428800

Note: We no need to create standby redo log files on standby and Oracle take cares of it during RMAN duplicate. 

4. Modify the primary initialization parameter for dataguard on primary.

SQL> alter system set LOG_ARCHIVE_CONFIG='DG_CONFIG=(PRODUAT,PRODSIT)';

System altered.

SQL> alter system set LOG_ARCHIVE_DEST_1='LOCATION=/u01/app/oracle/fast_recovery_area/PRODUAT/ VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=PRODUAT';

System altered.

SQL> alter system set LOG_ARCHIVE_DEST_2='SERVICE=PRODSIT LGWR ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=PRODSIT';

System altered.

SQL> alter system set LOG_ARCHIVE_DEST_STATE_1=ENABLE;

System altered.

SQL> alter system set standby_file_management=auto scope=both;

System altered.

SQL> alter system set FAL_SERVER=PRODSIT;

System altered.

SQL> alter system set FAL_CLIENT=PRODUAT;

System altered.

SQL> alter system set DB_FILE_NAME_CONVERT='PRODUAT','PRODSIT' scope=spfile;                System altered.

SQL> alter system set LOG_FILE_NAME_CONVERT='PRODUAT','PRODSIT' scope=spfile;

System altered.

5. Create the necessary directories on the standby server.

mkdir -p /u01/app/oracle/oradata/PRODSIT

chown -R oracle:oinstall /u01/app/oracle/oradata/PRODSIT

6. Configure Oracle net service/TNS names for standby system using NETCA or NETMGR 

Make sure tnsnames.ora file contains both entries in Primary and Standby servers

Primary:

[oracle@node1 admin]$ cat tnsnames.ora

# tnsnames.ora Network Configuration File: /u01/MTEST/network/admin/tnsnames.ora

# Generated by Oracle configuration tools.

PRODUAT =

  (DESCRIPTION =

    (ADDRESS_LIST =

      (ADDRESS = (PROTOCOL = TCP)(HOST = node1.oracle.com)(PORT = 1522))

    )

    (CONNECT_DATA =

      (SERVICE_NAME = PRODUAT)

    )

  )

PRODSIT =

  (DESCRIPTION =

    (ADDRESS_LIST =

      (ADDRESS = (PROTOCOL = TCP)(HOST = node2.oracle.com)(PORT = 1522))

    )

    (CONNECT_DATA =

      (SERVICE_NAME = PRODSIT)

    )

  )

Standby:

[oracle@node2 admin]$ cat tnsnames.ora

# tnsnames.ora Network Configuration File: /u01/app/oracle/product/12.1.0/dbhome_1/network/admin/tnsnames.ora

# Generated by Oracle configuration tools.

PRODSIT =

  (DESCRIPTION =

    (ADDRESS_LIST =

      (ADDRESS = (PROTOCOL = TCP)(HOST = node2.oracle.com)(PORT = 1522))

    )

    (CONNECT_DATA =

      (SERVICE_NAME = PRODSIT)

    )

  )

PRODUAT =

  (DESCRIPTION =

    (ADDRESS_LIST =

      (ADDRESS = (PROTOCOL = TCP)(HOST = node1.oracle.com)(PORT = 1522))

    )

    (CONNECT_DATA =

      (SERVICE_NAME = PRODUAT)

    )

  )

7. Check with the SQL*Net configuration using the following commands on the Primary and Standby

tnsping MQMPROD

tnsping MQMDR

8. Create the standby database

-Copy the password file from the primary $ORACLE_HOME/dbs and rename it to the standby database name.

-Create a initialization parameter with only one parameter DB_NAME.

DB_NAME=PRODUAT

DB_UNIQUE_NAME=PRODSIT

compatible='12.1.0.2.0'

log_file_name_convert='PRODUAT','PRODSIT'

9. Create the necessary directories in the standby location to place database files and trace files ($ADR_HOME)

mkdir -p /u01/app/oracle/admin/MQMDR/adump

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

export ORACLE_SID=MQMDR

sqlplus "/ as sysdba"

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

11. Verify if the connection 'AS SYSDBA' is working

sqlplus /nolog

SQL> connect sys/Welcome1@MQMDR AS SYSDBA

Connected.

SQL> connect sys/Welcome1@MQMPROD AS SYSDBA

Connected.

12. Connect to RMAN, specifying a full connect string for both the TARGET and AUXILIARY instances ( Issue on Standby )

Connect RMAN using target and auxiliary should connected as “not mounted” only.

[oracle@node2 ~]$ rman target sys/Welcome1@PRODUAT auxiliary sys/Welcome1@PRODSIT

Recovery Manager: Release 12.1.0.2.0 - Production on Fri Aug 28 05:23:39 2020

Copyright (c) 1982, 2014, Oracle and/or its affiliates.  All rights reserved.

connected to target database: PRODUAT (DBID=1381890412)

connected to auxiliary database: PRODUAT (not mounted)

RMAN> duplicate target database for standby from active database nofilenamecheck dorecover;

An explanation of the above RMAN command.

FOR STANDBY: This tells the DUPLICATE command is to be used for a standby, so it will not force a DBID change.

FROM ACTIVE DATABASE: The DUPLICATE will be created directly from the source datafile, without an additional backup step.

DORECOVER: The DUPLICATE will include the recovery step, bringing the standby up to the current point in time.

NOFILENAMECHECK: Destination file locations are not checked.

Once the command is complete, we can start the apply process.

Output:

Starting Duplicate Db at 28-AUG-20

using target database control file instead of recovery catalog

allocated channel: ORA_AUX_DISK_1

channel ORA_AUX_DISK_1: SID=23 device type=DISK

current log archived

contents of Memory Script:

{

   backup as copy reuse

   targetfile  '/u01/MTEST/dbs/orapwPRODUAT' auxiliary format 

 '/u01/app/oracle/product/12.1.0/dbhome_1/dbs/orapwPRODSIT'   ;

}

executing Memory Script

Starting backup at 28-AUG-20

allocated channel: ORA_DISK_1

channel ORA_DISK_1: SID=55 device type=DISK

Finished backup at 28-AUG-20

contents of Memory Script:

{

   restore clone from service  'PRODUAT' standby controlfile;

}

executing Memory Script

Starting restore at 28-AUG-20

using channel ORA_AUX_DISK_1

channel ORA_AUX_DISK_1: starting datafile backup set restore

channel ORA_AUX_DISK_1: using network backup set from service PRODUAT

channel ORA_AUX_DISK_1: restoring control file

channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:03

output file name=/u01/app/oracle/product/12.1.0/dbhome_1/dbs/cntrlPRODSIT.dbf

Finished restore at 28-AUG-20

contents of Memory Script:

{

   sql clone 'alter database mount standby database';

}

executing Memory Script

sql statement: alter database mount standby database

contents of Memory Script:

{

   set newname for tempfile  1 to 

 "/u01/app/oracle/oradata/PRODUAT/datafile/o1_mf_temp_hnkbdtkj_.tmp";

   switch clone tempfile all;

   set newname for datafile  1 to 

 "/u01/app/oracle/oradata/PRODUAT/datafile/o1_mf_system_hnkb8mqx_.dbf";

   set newname for datafile  3 to 

 "/u01/app/oracle/oradata/PRODUAT/datafile/o1_mf_sysaux_hnkb68cx_.dbf";

   set newname for datafile  4 to 

 "/u01/app/oracle/oradata/PRODUAT/datafile/o1_mf_undotbs1_hnkbcn7x_.dbf";

   set newname for datafile  6 to 

 "/u01/app/oracle/oradata/PRODUAT/datafile/o1_mf_users_hnkbcm4m_.dbf";

   restore

   from service  'PRODUAT'   clone database

   ; sql 'alter system archive log current';

}

executing Memory Script

executing command: SET NEWNAME

renamed tempfile 1 to /u01/app/oracle/oradata/PRODUAT/datafile/o1_mf_temp_hnkbdtkj_.tmp in control file

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

Starting restore at 28-AUG-20

using channel ORA_AUX_DISK_1

channel ORA_AUX_DISK_1: starting datafile backup set restore

channel ORA_AUX_DISK_1: using network backup set from service PRODUAT

channel ORA_AUX_DISK_1: specifying datafile(s) to restore from backup set

channel ORA_AUX_DISK_1: restoring datafile 00001 to /u01/app/oracle/oradata/PRODUAT/datafile/o1_mf_system_hnkb8mqx_.dbf

channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:35

channel ORA_AUX_DISK_1: starting datafile backup set restore

channel ORA_AUX_DISK_1: using network backup set from service PRODUAT

channel ORA_AUX_DISK_1: specifying datafile(s) to restore from backup set

channel ORA_AUX_DISK_1: restoring datafile 00003 to /u01/app/oracle/oradata/PRODUAT/datafile/o1_mf_sysaux_hnkb68cx_.dbf

channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:25

channel ORA_AUX_DISK_1: starting datafile backup set restore

channel ORA_AUX_DISK_1: using network backup set from service PRODUAT

channel ORA_AUX_DISK_1: specifying datafile(s) to restore from backup set

channel ORA_AUX_DISK_1: restoring datafile 00004 to /u01/app/oracle/oradata/PRODUAT/datafile/o1_mf_undotbs1_hnkbcn7x_.dbf

channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:02

channel ORA_AUX_DISK_1: starting datafile backup set restore

channel ORA_AUX_DISK_1: using network backup set from service PRODUAT

channel ORA_AUX_DISK_1: specifying datafile(s) to restore from backup set

channel ORA_AUX_DISK_1: restoring datafile 00006 to /u01/app/oracle/oradata/PRODUAT/datafile/o1_mf_users_hnkbcm4m_.dbf

channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:01

Finished restore at 28-AUG-20

sql statement: alter system archive log current

current log archived

contents of Memory Script:

{

   restore clone force from service  'PRODUAT' 

           archivelog from scn  1618367;

   switch clone datafile all;

}

executing Memory Script

Starting restore at 28-AUG-20

using channel ORA_AUX_DISK_1

channel ORA_AUX_DISK_1: starting archived log restore to default destination

channel ORA_AUX_DISK_1: using network backup set from service PRODUAT

channel ORA_AUX_DISK_1: restoring archived log

archived log thread=1 sequence=14

channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:01

channel ORA_AUX_DISK_1: starting archived log restore to default destination

channel ORA_AUX_DISK_1: using network backup set from service PRODUAT

channel ORA_AUX_DISK_1: restoring archived log

archived log thread=1 sequence=15

channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:01

Finished restore at 28-AUG-20

datafile 1 switched to datafile copy

input datafile copy RECID=1 STAMP=1049648103 file name=/u01/app/oracle/oradata/PRODUAT/datafile/o1_mf_system_hnkb8mqx_.dbf

datafile 3 switched to datafile copy

input datafile copy RECID=2 STAMP=1049648103 file name=/u01/app/oracle/oradata/PRODUAT/datafile/o1_mf_sysaux_hnkb68cx_.dbf

datafile 4 switched to datafile copy

input datafile copy RECID=3 STAMP=1049648103 file name=/u01/app/oracle/oradata/PRODUAT/datafile/o1_mf_undotbs1_hnkbcn7x_.dbf

datafile 6 switched to datafile copy

input datafile copy RECID=4 STAMP=1049648103 file name=/u01/app/oracle/oradata/PRODUAT/datafile/o1_mf_users_hnkbcm4m_.dbf

contents of Memory Script:

{

   set until scn  1618687;

   recover

   standby

   clone database

    delete archivelog

   ;}

executing Memory Script

executing command: SET until clause

Starting recover at 28-AUG-20

using channel ORA_AUX_DISK_1

starting media recovery

archived log for thread 1 with sequence 14 is already on disk as file /u01/app/oracle/product/12.1.0/dbhome_1/dbs/arch1_14_1049631726.dbf

archived log for thread 1 with sequence 15 is already on disk as file /u01/app/oracle/product/12.1.0/dbhome_1/dbs/arch1_15_1049631726.dbf

archived log file name=/u01/app/oracle/product/12.1.0/dbhome_1/dbs/arch1_14_1049631726.dbf thread=1 sequence=14

archived log file name=/u01/app/oracle/product/12.1.0/dbhome_1/dbs/arch1_15_1049631726.dbf thread=1 sequence=15

media recovery complete, elapsed time: 00:00:00

Finished recover at 28-AUG-20

Finished Duplicate Db at 28-AUG-20

12. 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.

SQL> alter database recover managed standby database disconnect from session;

Database altered.

SQL> select NAME,CONTROLFILE_TYPE,OPEN_MODE,DATABASE_ROLE,PROTECTION_MODE from v$database;

NAME      CONTROL OPEN_MODE            DATABASE_ROLE    PROTECTION_MODE

--------- ------- -------------------- ---------------- --------------------

PRODUAT   STANDBY MOUNTED              PHYSICAL STANDBY MAXIMUM PERFORMANCE

SQL> host

[oracle@node2 ~]$ ps -ef|grep mrp

oracle   10077     1  0 17:17 ?        00:00:02 ora_mrp0_PRODSIT

oracle   10158 10129  0 17:23 pts/1    00:00:00 grep mrp

The Managed Recovery Process (MRP) applies information from the archived redo logs to the standby database. When performing managed recovery operations, log apply services automatically apply archived redo logs to maintain transactional synchronization with the primary database.

Alertlog:

Fri Aug 28 17:17:52 2020

alter database recover managed standby database disconnect from session

Fri Aug 28 17:17:52 2020

Attempt to start background Managed Standby Recovery process (PRODSIT)

Starting background process MRP0

Fri Aug 28 17:17:52 2020

MRP0 started with pid=21, OS id=10077 

Fri Aug 28 17:17:52 2020

MRP0: Background Managed Standby Recovery process started (PRODSIT)

Fri Aug 28 17:17:57 2020

Serial Media Recovery started

Managed Standby Recovery starting Real Time Apply

Fri Aug 28 17:17:57 2020

Waiting for all non-current ORLs to be archived...

Fri Aug 28 17:17:57 2020

All non-current ORLs have been archived.

Media Recovery Waiting for thread 1 sequence 16

Completed: alter database recover managed standby database disconnect from session

Note:

If you face any SYNC issue, try to set the standby related parameters.