Step by Step Data Guard Configuration in 19c

1) PREREQUISITES: Requirements before starting the DATA GUARD CONFIGURATION.

NOTE:

1) In PRIMARY SERVER DG11 we need to install the BINARIES OF ORACLE 19c and then create only 1 PRIMARY DB NAMED ORCL by using ./dbca or manually as required.

2) In STANDBY SERVER DG11S we only need to install the BINARIES OF ORACLE 19c. Do not create any STANDBY DB as we will created in during CONFIDURATION of DATA GUARD which is discussed ahead.

2) COMPLETE STEP BY STEP CONFIGURATION FOR PRIMARY DATABASE.

The high level steps require to configure a Primary DB are given below. These are as follow:

STEP 1: Create LISTENER for PRIMARY DB and start the LISTENER by DISABLING FIREWALL.

A) Create a LISTENER for PRIMARY DB

B) DISABLE the FIREWALL for the PRIMARY DB.

To disable the firewall for the Primary DB we need to first connect with the root user and then by
giving command we have to follow the below steps.

C) Start the LISTENER created for the PRIMARY DB.

STEP 2: Create TNSNAMES.ORA FILE for PRIMARY DB and check TNSPING for PRIMARY DB and STANDBY DB.

A) Creation of TNSNAMES.ORA FILE in Primary DB

B) Check TNSPING for PRIMARY DB and STANDBY DB

Remember that to check the TNSPING for both, Primary DB and Standby DB, LISTENER and TNSNAMES.ORA files
should be created in both the DBs and LISTENER should be STARTED in both the DBs.

STEP 3: ENABLE ARCHIVE LOG for Primary DB.

If the Primary DB is in No Archive Log Mode, then we need to Enable it.

Here the Archive log mode for the Primary DB is already Enabled, hence no need to enable the Archive log mode.

If Archive log mode is Disabled, then

Shutdown the DB.
Start the DB in MOUNT mode.
Enable the Archive log mode by giving command "SQL> ALTER DATABASE archivelog;"
Check if Enabled by giving command "SQL> SELECT log_mode FROM v$database;"
Alter the Primary DB in Open mode by giving command "SQL> ALTER DATABASE open;"

STEP 4: ENABLE FORCE LOGGING for Primary DB.

STEP 5: Check DB_NAME and DB_UNIQUE_NAME for the Primary DB.

STEP 6: Set LOG_ARCHIVE_CONFIG parameter for Primary DB.

STEP 7: Set the LOG_ARCHIVE_DEST parameter for the Primary DB.

STEP 8: Set the LOG_ARCHIVE_DEST_STATE parameter to ENABLE for the Primary DB.

STEP 9: Set the LOG_ARCHIVE_FORMAT parameter for the Primary DB.

STEP 10: Set the LOG_ARCHIVE_MAX_PROCESSES parameter to 30 for the Primary DB.

STEP 11: Set the REMOTE_LOGIN_PASSWORDFILE parameter to EXCLUSIVE for the Primary DB.

STEP 12: Set the FAL_SERVER and FAL_CLIENT parameter for the Primary DB.

STEP 13: Set the STANDBY_FILE_MANAGEMENT parameter to MANUAL for Primary DB.

To add Standby Redo Log files to the Primary DB, it is always necessary to first set the
STANDBY_FILE_MANAGEMENT parameter to MANUAL and then add SRL’s to the DB.

STEP 14: Add STANDBY REDOLOG FILES (SRLs) to the Primary DB while the STANDBY_FILE_MANAGEMENT=MANUAL

A) Check the existing REDO LOG FILES in Primary DB.

Before adding any SRLs to the Primary DB, it is neccessary to check the existing Redo-log files in the
Primary DB because the GROUP NUMBER of the new SRLs we create should not overlap with the old ones i.e. 1,2,3.
Also the size of the new SRLs should be same as size of existing Redo-log files in the Primary DB i.e. 200MB.

B) Add new SRLs to the Primary DB.

C) Verify if SRLs are added to the PRIMARY DB.

Also in another tab check if physically the SRLs are created.

STEP 15: Set the STANDBY_FILE_MANAGEMENT parameter to AUTO for Primary DB.

Now that we have added Standby Redo Log files to the Primary DB, it is always necessary to set the
STANDBY_FILE_MANAGEMENT parameter to AUTO in Primary DB.

STEP 16: Send PASSWORD FILE from PRIMARY DB to the STANDBY DB.

3) COMPLETE STEP BY STEP CONFIGURATION FOR STANDBY DATABASE.

The high level steps require to configure a Standby DB are given below. These are as follow:

STEP 1: Make entry in ORATAB FILE before restoring Standby DB.

STEP 2: Create LISTENER for STANDBY DB and start the LISTENER by DISABLING FIREWALL.

A) Create a LISTENER for STANDBY DB

B) DISABLE the FIREWALL for the PRIMARY DB.

To disable the firewall for the Primary DB we need to first connect with the root user and then by
giving command we have to follow the below steps.

C) Start the LISTENER created for the STANDBY DB.

STEP 3: Create TNSNAMES.ORA FILE for STANDBY DB and check TNSPING for PRIMARY DB and STANDBY DB.

A) Creation of TNSNAMES.ORA FILE in Standby DB

B) Check TNSPING for PRIMARY DB and STANDBY DB

Remember that to check the TNSPING for both, Primary DB and Standby DB, LISTENER and TNSNAMES.ORA files
should be created in both the DBs and LISTENER should be STARTED in both the DBs.

STEP 4: Set the ENVIRONMENT of Standby DB

STEP 5: Create a P-FILE for Standby DB and create required DIRECTORIES.

A) Create a P-FILE for Standby DB with only GLOBAL_DB_Name inside it.

NOTE:
We created a blank P-FILE for the Standby DB and just added the Global_DB_NAME as *.db_name='orcl' to it.

B) Create required directories needed for Standby DB Cloning.

NOTE:
We are creating above directories keeping in mind that GLOBAL DB NAME 'orcl' is used instead of DB UNIQUE NAME 'orcls'. If we use DB UNIQUE NAME 'orcls' then the DUPLICATING/CLONING process of Primary DB to Standby DB may fail because the Primary DB does not have any directory named with DB UNIQUE NAME 'orcls'.

STEP 6: STARTUP the DB in NOMOUNT mode using new P-FILE created for the Standby DB.

STEP 7: Connect to AUXILIARY DB using RMAN PROMPT.

STEP 8: RUN the SCRIPT in RMAN PROMPT to CLONE the Primary DB to Standby DB.

Hence the PRIMARY DB has been successfully duplicated as STANDBY DB and can now be checked if both the DB's are in SYNC or not.

STEP 9: STARTUP the DB in READ_ONLY mode and VALIDATE some parameters and RECTIFY them if required.

A) STARTUP the DB in READ_ONLY mode.

B) Validate some Parameters in READ_ONLY mode.

NOTE:

Here the ‘log_archive_dest_2’ is displaying name of STANDBY DB i.e ‘ORCLS’ which is WRONG.
—- Instead it should display the name of PRIMARY DB i.e ‘ORCL’. Hence we need to rectify this.

RESET the ‘log_archive_dest_2’ parameter

Now the new location for ‘log_archive_dest_2’ parameter is set to PRIMARY DB which is ‘ORCL’.

Hence all parameters looks fine.

4) COMPLETE STEP BY STEP PROCESS FOR MAKING THE PRIMARY DATABASE and STANDBY DATABASE IN SYNC.

The high level steps require to configure a Primary DB are given below. These are as follow:

STEP 1: Check the MAXIMUM SEQUENCE of ARCHIVED LOGS applied in Primary DB and Standby DB.

Here in PRIMARY DB the Maximum logs that were archived were 23.
So we did ‘alter system switch logfile’ twice to archive 2 more logs and thus the Maximum logs that were archived becomes 25.

Now these archives should be applied to the Standby DB also and the Maximum archived logs applied in the Standby DB should also be 25.

This query verifies that archives from the Primary DB are being received by the RFS in Standby DB.

Here we can see that since the ‘standby_file_management’ is ‘AUTO’, the Archives from the Primary DB are
being received by RFS of Standby DB but not getting applied in Standby DB.
Hence Max Sequence = 25 but Sequence Applied = 23 creating a difference of 2 Archives.

This is because the MRP process of the Standby DB is not started. When MRP process will be started then the remaining Archives will be taken from SRL by MRP and applied in the Standby DB.
This will make the Archive difference 0.

STEP 2: START the MRP Process in the STANDBY DB for applying ARCHIVES of PRIMARY DB.

FOR STANDBY DB – ORCLS

A) View to check if MRP process is running or not.

B) START the MRP process in the STANDBY DB and verify.

If we want to STOP the MRP process in the STANDBY DB
In case of you want to cancel apply process, issue following command:

C) Verify if MRP process in Standby DB is started or not.

Hence the MRP process has STARTED in the STANDBY DB.

Now lets see if the remaining archives are being applied to the Standby DB by the MRP.

STEP 3: Check whether the REMAINING ARCHIVED LOGS are applied by MRP to the Standby DB.

A) Check SEQUENCE NUMBER of the archives received by Standby DB.

B) Check SEQUENCE APPLIED is same as SEQUENCE NUMBER in Standby DB.

Hence we can see that the Difference in Archive logs is 0 and hence the MRP is working properly
and applying the Archived logs of the Primary DB to the Standby DB efficiently.

Hope it helped ..