Steps To Create Physical Standby Of CDB

Steps to Create a physical standby of a CDB just the same as we perform while creating NON-CDB database , Although some feature are not same in a container database in comparison to non-container database , physical standby database is a transitionally consistent copy or a block to block of oracle production database initially created from a backup copy of primary database , We basically configure    the standby database to protect our database’s data and its availability from any sort of failure or business loss , however we also configured the database’s backup to restore the database from any kind of disaster but restoring and then recovering the database from the backups will take lot of time depends upon the size of the database and also during the time of restore & recovery our primary database will be unavailable and unavailability of database in real time means multiple business’s SLA breach and business loss , Although with the help of physical standby database not only we increase the availability of the database but it can also use for the read only purpose in oracle active data guard environment.

NOTE : In a real-time environment  , creating a standby database doesn’t mean that the job 

                is done even after that we’ve to create several database jobs , oem alert & metrices etc.

Things To Remember In Physical Standby Of A CDB

(1)  The database’s role will be defined at the Root Container Level not at the PDB   level

(2)  Any sort of role transition operation either switchover or failover will execute at the CDB level

(3)  MRP works or must be start at CDB level

(4)  The Redo must be shipped at the Root container level

(5)  Be careful to enable or disable ENABLED_PDBS_ON_STANDBY intialization parameter in primary database before creating a physical standby   

Types Of Standby Databases

(1)  Physical Standby

(2)  Logical Standby

(3)  Snapshot Standby

 In This post , we’ll discuss about how to create physical standby of CDB.

PRIMARY DATABSE	STANDBY DATABASE
US_PRIM	IND_DR

                   Checklist To Create Physical Standby Database

Task	Database
Enable Force Logging	Primary Database
Check Archive log Mode	Primary Database
Configure Primary To Receive Redo	Primary Database
Set Initialization parameter to control redo transport	Primary Database
Set Initialization Parameter for role transition	Primary Database
Create a backup copy of primary database	Primary Database
Create a control file for the standby	Primary Database
Create pfile file from primary database	Primary Database
Copy db backup,standby control file,password file &  pfile from primary to standby server	Primary Database
Create require directory in standby database	Standby Database
Make necessary change in pfile & bring the db in nomount	Standby Database
Copy the control file as per the name & location of control file in pfile	Standby Database
Mount the standby database	Standby Database
Ensure compatible parameter must be same between primary and standby	Primary & Standby Both
Restore database	Standby Database
Create oracle net connection between primary and standby Database	Primary & Standby Both
Start MRP	Standby Database
Cleared all the log group	Standby Database
Drop & Recreate Temp File	Standby Database
Verify MRP is running fine	Standby Database

 Step 1st : Enable Force Logging Mode

The default force logging mode in the database is not enable which means that in data guard configuration it can allow certain data to be loaded without no logged manner and in that case if any of the data get miss since it allow to be loaded in a no logged manner then it require a manual intervention to fix it , So we must have to enable the force logging in the primary database.

                                  Types Of Force Logging

Force logging : this mode causes the data being loded will be send to the standby database using its own connection to the standby.

SQL> alter database force logging;

Database altered.

SQL> select force_logging from v$database;

FORCE_LOGGING

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

YES

Standby Nologging For Data Availability : this mode little bit impact the performance as it cause the data will be end to the standby using its owner connection and the commit is delayed until all the data will be applied to the standby database/s.

SQL> alter database set standby nologging for data availability;

Database altered.

SQL> select force_logging from v$database;

FORCE_LOGGING

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

STANDBY NOLOGGING FOR DATA AVAILABILITY

Standby Nologging For Data Performance : this mode is similar to the “Standby Nologging For Data Availability” except the commit will not get delayed and if standby will not be able to cope up the data being loaded into the primary database then it’ll recovery the data as a normal part of managed recovery in an active data guard environment.

SQL> alter database set standby nologging for load performance;

Database altered.

 SQL>  select force_logging from v$database;

FORCE_LOGGING

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

STANDBY NOLOGGING FOR LOAD PERFORMANCE

 Step 2nd :  Add Standby Redo Logs In Primary Database, So that that can be use in future during the role transition.

Note : Always create the standby redo log with the same size of primary’s redo log group and always uses n+1 formula to create standby redo.

SQL> alter database add standby logfile '/u01/oradata/US_PRIM/STDREDO04.log' size 209715200;

Database altered.

SQL> alter database add standby logfile '/u01/oradata/US_PRIM/STDREDO05.log' size 209715200;

Database altered.

SQL> alter database add standby logfile '/u01/oradata/US_PRIM/STDREDO06.log' size 209715200;

Database altered.

SQL> alter database add standby logfile '/u01/oradata/US_PRIM/STDREDO07.log' size 209715200;

Database altered.

 GROUP# TYPE          MEMBER

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

     3 ONLINE             /u01/oradata/US_PRIM/redo03.log

     2 ONLINE             /u01/oradata/US_PRIM/redo02.log

     1 ONLINE             /u01/oradata/US_PRIM/redo01.log

     4 STANDBY            /u01/oradata/US_PRIM/STDREDO04.log

     5 STANDBY            /u01/oradata/US_PRIM/STDREDO05.log

     6 STANDBY            /u01/oradata/US_PRIM/STDREDO06.log

     7 STANDBY            /u01/oradata/US_PRIM/STDREDO07.log

7 rows selected.

Step 3rd : Set Initilization Parameter in primary database so that it can control the redo transport service while working in primary role

Initialization Parameters	Value
DB_NAME	DB_NAME OF THE DB
DB_UNIQUE_NAME	DB_UNIQUE_NAME Of The Database
LOG_ARCHIVE_DEST_1	Local archive destination
LOG_ARCHIVE_DEST_2	Standby destination for REDO Transport
REMOTE_LOGIN_PASSWORD_FILE	Must be Exclusive OR Shared
Log_archive_config	To get full functionality of the dataguard

 SQL> alter system set log_archive_config='DG_CONFIG=(US_PRIM,IND_DR)' scope=both;

System altered.

SQL> show parameter log_archive_config

NAME                                                     TYPE         VALUE

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

log_archive_config                  string         DG_CONFIG=(US_PRIM,IND_DR)

 SQL> show parameter db_name

NAME                                                     TYPE         VALUE

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

db_name                                    string         US_PRIM

SQL>

SQL> show parameter db_unique_name

NAME                                                     TYPE         VALUE

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

db_unique_name                                 string         US_PRIM

SQL>

SQL> show parameter remote_login_password

NAME                                                     TYPE         VALUE

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

remote_login_passwordfile               string         EXCLUSIVE

SQL>  alter system set log_archive_dest_1='LOCATION=USE_DB_RECOVERY_FILE_DEST' scope=both;

System altered.

SQL> alter system set log_archive_dest_2='SERVICE=IND_DR ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=IND_DR' scope=both;

System altered.

Step 4th : The initialization parameter we’ve set in step 3 was to control the redo transport service , although we also need to set some additional parameter that will be use by primary database after the role transition operation

Initialization Parmater	Value
FAL_SERVER	DB_UNIQUE_NAME OF Primary database
DB_FILE_NAME_CONVERT	‘Primary_dbfile_path’, ‘STANDBY_DbFiles_Path’
LOG_FILE_NAME_CONVERT	‘Primary_dbfile_path’, ‘STANDBY_DbFiles_Path’
STANDBY_FILE_MANAGEMENT	AUTO

 Ø FAL_SERVER parameter value will be use by standby database to fetch the gap sequence from primary database

Ø  DB_FILE_NAME_CONVERT parameter value will be use by  standby database to create datafile in standby dbfile location

Ø  LOG_FILE_NAME_CONVERT Parameter value will be use by standby to create redo logs in standby logfile location

Ø  STANDBY_FILE_MANAGMENT parameter value must be AUTO so that datafile add & remove will also be done automatically in standby database.

SQL> alter system set FAL_SERVER='IND_DR' scope=both;

System altered.

SQL> alter system set db_file_name_convert='/u01/oradata/US_PRIM/','/u01/oradata/IND_DR' scope=spfile;

System altered.
SQL> alter system set log_file_name_convert='/u01/oradata/US_PRIM/','/u01/oradata/IND_DR' scope=spfile;

System altered.

 SQL> alter system set standby_file_management=AUTO scope=both;
System altered.

Step 5th : Take Full backup of the primary database

Run {

Allocate chancel c1 device type disk format=’/u01/oradata/%U.bkp’;

Allocate chancel c2 device type disk format=’/u01/oradata/%U.bkp’;

Backp incremental level 0 database plus archivelog;

}

Step 6th : Create standby control file

SQL>Alter database create standby controlfile as ‘/u01/oradata/control01.ctl’;

SQL>Database altered

Step 7th : Create pfile from primary database for standby database

SQL>create pfile=’/u01/oradata/initIND_DR.ora’ from spfile;

File created.

Step 8th : copy below files from primary database To Standby Database Server

Ø Standby Control file

Ø  Pfile

Ø  Database backup files

Ø  Password file

Step 9th : create necessary directories & make require change in pfile which is copied from primary server to standby server

• Control_files
• Db_unique_name
• FAL_SERVER
•  LOG_ARCHIVE_DEST_2
•  DB_FILE_NAME_CONVERT
•  LOG_FILE_NAME_CONVERT

Step 10 : Start standby database in nomount mode

SQL> startup nomount pfile='/u01/oradata/initIND_DR.ora';

ORACLE instance started.

Total System Global Area 1728050736 bytes

Fixed Size                      9135664 bytes

Variable Size               402653184 bytes

Database Buffers     1308622848 bytes

Redo Buffers                 7639040 bytes

Step 11th : Copy standby controlfile as per the name & location of control_file  parameter 

                            in pfile and mount the database

 SQL> show parameter control_files

NAME                                                     TYPE         VALUE

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

control_files                              string         /u01/oradata/IND_DR/control01.ctl, 

                                                                        /u01/app/oracle/recovery_area/IND_DR/control02.ctl

SQL> !ls -ltr /u01/oradata/IND_DR/control01.ctl /u01/app/oracle/recovery_area/IND_DR/control02.ctl

-rwxr-xr-x. 1 oracle oinstall 18726912 Dec 17 18:21 /u01/oradata/IND_DR/control01.ctl

-rwxr-xr-x. 1 oracle oinstall 18726912 Dec 17 18:21 /u01/app/oracle/recovery_area/IND_DR/control02.ctl

SQL> alter database mount;

Database altered.

 Step 12: check compatible parameter between primary & standby database

Note : The compatible parameter between primary & standby database must be same otherwise redo transport service may be unable to transmit redo from primary database to standby database.

NAME                                                     TYPE         VALUE

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

db_unique_name                                 string         US_PRIM

SQL>

SQL> show parameter compatible

 

NAME                                                     TYPE         VALUE

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

compatible                                 string         19.0.0

SQL> show parameter db_unique_name

NAME                                                     TYPE         VALUE

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

db_unique_name                                 string         IND_DR

SQL> show parameter compatible

 

NAME                                                     TYPE         VALUE

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

compatible                                 string         19.0.0

 

Step 13: Restore The database using primary db backup piece

RMAN> restore database;

Starting restore at 17-DEC-23

allocated channel: ORA_DISK_1

channel ORA_DISK_1: SID=25 device type=DISK

channel ORA_DISK_1: starting datafile backup set restore

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

channel ORA_DISK_1: restoring datafile 00003 to /u01/oradata/IND_DR/sysaux01.dbf

channel ORA_DISK_1: restoring datafile 00004 to /u01/oradata/IND_DR/undotbs01.dbf

-

 

channel ORA_DISK_1: piece handle=/u01/oradata/DB_BKP/072e986u_1_1 tag=TAG20231217T165909

channel ORA_DISK_1: restored backup piece 1

channel ORA_DISK_1: restore complete, elapsed time: 00:00:15

Finished restore at 17-DEC-23

RMAN>

Step 14: Create oracle net connection as given below in both primary and standby database

[oracle@oratest admin]$ pwd

/u01/app/oracle/product/19.0.0/dbhome_1/network/admin

Primary Database Net Connection

Standby Database Net Connection

US_PRIM =   (DESCRIPTION =   (ADDRESS_LIST =     (ADDRESS = (PROTOCOL = TCP)(HOST = oratest)(PORT = 1521))    )     (CONNECT_DATA =       (SERVER = DEDICATED)       (SERVICE_NAME = US_PRIM)       (UR=A)     )   )    IND_DR =   (DESCRIPTION =     (ADDRESS_LIST =     (ADDRESS = (PROTOCOL = TCP)(HOST = IND_DR)(PORT = 1522))      )       (CONNECT_DATA =       (SERVER = DEDICATED)       (SERVICE_NAME = IND_DR)       (UR=A)     )   )

US_PRIM =   (DESCRIPTION =   (ADDRESS_LIST =     (ADDRESS = (PROTOCOL = TCP)(HOST = oratest)(PORT = 1521))    )     (CONNECT_DATA =       (SERVER = DEDICATED)       (SERVICE_NAME = US_PRIM)       (UR=A)     )   )    IND_DR =   (DESCRIPTION =     (ADDRESS_LIST =     (ADDRESS = (PROTOCOL = TCP)(HOST = IND_DR)(PORT = 1522))      )       (CONNECT_DATA =       (SERVER = DEDICATED)       (SERVICE_NAME = IND_DR)       (UR=A)     )   )

 

Step 15: Clear log group created on standby database after restore

SQL> alter database clear logfile group 1;

Database altered.

SQL> alter database clear logfile group 2;

Database altered.

SQL> alter database clear logfile group 3;

Database altered.

SQL> alter database clear logfile group 4;

Database altered.

SQL> alter database clear logfile group 5;

Database altered.

SQL> alter database clear logfile group 6;

Database altered.

SQL> alter database clear logfile group 7;

Database altered.

 

Step 16 : Drop & Recreate Temp Files In Standby Database

Note : Drop & re-create tempfile otherwise you’ll get error highlighted in a box

                 everytime when Standby will restart.

    CON_ID|TABLESPACE_NAME       |FILE_NAME                                                                           |BYTES/1024/1024

----------|------------------------------|----------------------------------------------------------------------------------------------------|---------------

        3|TEMP                     |/u01/app/oracle/oradata/IND_DR/0D40A9D4D52D26BDE0630901A8C0E8F7/datafile/o1_mf_temp_lrjt24w7_.tmp   |          36

        1|TEMP                     |/u01/app/oracle/oradata/IND_DR/datafile/o1_mf_temp_lrjsz8bj_.tmp                           |          20

SQL> alter database open read only;

Database altered.

SQL> alter tablespace TEMP add tempfile '/u01/app/oracle/oradata/IND_DR/datafile/TEMP01.dbf' size 50M;

Tablespace altered.

SQL> alter tablespace TEMP drop tempfile '/u01/app/oracle/oradata/IND_DR/datafile/o1_mf_temp_lrjsz8bj_.tmp';

Tablespace altered.

 

SQL> alter session set container=PDB1;

Session altered.

SQL> show con_name

CON_NAME

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

PDB1

SQL> alter tablespace TEMP add tempfile '/u01/app/oracle/oradata/IND_DR/datafile/PDB1_TEMP01' size 20M;

Tablespace altered.

 

SQL> alter tablespace TEMP drop tempfile '/u01/app/oracle/oradata/IND_DR/0D40A9D4D52D26BDE0630901A8C0E8F7/datafile/o1_mf_temp_lrjt24w7_.tmp';

Tablespace altered.

 

    CON_ID|TABLESPACE_N|FILE_NAME                                                |BYTES/1024/1024

----------|------------|---------------------------------------------------|---------------

         3|TEMP        |/u01/app/oracle/oradata/IND_DR/datafile/PDB1_TEMP01|               20

         1|TEMP        |/u01/app/oracle/oradata/IND_DR/datafile/TEMP01.dbf |               50

Step 17th :  Start MRP Process

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

Database altered.

Step 18th : Verify if MRP is running fine

Note A. Since v$managed_standby is deprecated as of oracle 12c , So use Use

                   v$dataguard_process

Note B. Recovery logmerger role shows that redo is being applied at the standby

Standby Database Output

SQL> select name,role,action,group#,thread#,sequence#,BLOCK#,BLOCK_COUNT,DELAY_MINS from v$dataguard_process;

 

NAME |ROLE                         |ACTION         |           GROUP#|   THREAD#| SEQUENCE#|   BLOCK#|BLOCK_COUNT|DELAY_MINS

-----|------------------------|------------|----------|----------|----------|----------|-----------|----------

LGWR |log writer                  |IDLE               |                0|      0|              0|              0|       0|              0

TMON |redo transport monitor  |IDLE      |                0|      0|              0|              0|       0|              0

TT00 |gap manager              |IDLE               |                0|      0|              0|              0|       0|              0

TT01 |redo transport timer    |IDLE             |                0|      0|              0|              0|       0|              0

ARC0 |archive local              |IDLE               |                0|      0|              0|              0|       0|              0

ARC1 |archive redo               |IDLE               |                0|      0|              0|              0|       0|              0

ARC2 |archive redo               |IDLE               |                0|      0|              0|              0|       0|              0

ARC3 |archive redo               |IDLE               |                0|      0|              0|              0|       0|              0

rfs  |RFS ping                         |IDLE               |                0|      1|               5|              0|       0|              0

rfs  |RFS async         |IDLE               |                4|      1|               5|          10087|                 1|               0

MRP0 |managed recovery                  |IDLE               |                0|      0|              0|              0|       0|                    0

 

NAME |ROLE                         |ACTION         |           GROUP#|   THREAD#| SEQUENCE#|   BLOCK#|BLOCK_COUNT|DELAY_MINS

-----|------------------------|------------|----------|----------|----------|----------|-----------|----------

PR00 |recovery logmerger      |APPLYING_LOG|      0|      1|               5|          10087|     409600|                    0

PR01 |recovery apply slave    |IDLE             |                0|      0|              0|              0|       0|              0

PR02 |recovery apply slave    |IDLE            |                0|      0|              0|              0|       0|              0

 

14 rows selected.

Primary Database  Output

 

SQL> select name,role,action,group#,thread#,sequence#,BLOCK#,BLOCK_COUNT,DELAY_MINS from v$dataguard_process;

 

NAME |ROLE                         |ACTION         |           GROUP#|   THREAD#| SEQUENCE#|   BLOCK#|BLOCK_COUNT|DELAY_MINS

-----|------------------------|------------|----------|----------|----------|----------|-----------|----------

LGWR |log writer                  |IDLE               |                0|      0|              0|              0|       0|              0

TMON |redo transport monitor  |IDLE      |                0|      0|              0|              0|       0|              0

TT00 |gap manager              |IDLE               |                0|      1|               5|              0|       0|              0

TT01 |redo transport timer    |IDLE             |                0|      0|              0|              0|       0|              0

ARC0 |archive local              |IDLE               |                0|      0|              0|              0|       0|              0

ARC1 |archive redo               |IDLE               |                0|      0|              0|              0|       0|              0

ARC2 |archive redo               |IDLE               |                0|      0|              0|              0|       0|              0

ARC3 |archive redo               |IDLE               |                0|      0|              0|              0|       0|              0

TT02 |async ORL multi       |WRITING      |                2|      1|               5|           7909| 10|             0

TT03 |heartbeat redo informer |IDLE        |                0|      0|              0|              0|       0|              0

TT05 |controlfile update      |IDLE               |                0|      0|              0|              0|       0|              0

 

NAME |ROLE                         |ACTION         |           GROUP#|   THREAD#| SEQUENCE#|   BLOCK#|BLOCK_COUNT|DELAY_MINS

-----|------------------------|------------|----------|----------|----------|----------|-----------|----------

TT04 |async ORL single       |WRITING      |                2|      1|               5|          10053|   1|               0

 

12 rows selected.