How to Configure the Data Guard with Cascading Standby Database on Oracle 19C DB
Posted by Mir Sayeed Hassan on February 16th, 2025
How to Configure the Data Guard with Cascading Standby Database on Oracle 19C DB
Brief about this concept
– I would like to configure the data guard with the cascading standby database on Oracle Database 19C.
– To perform this we should have configure 1 Primary database, 2 Standby database
Flow should be like this
PRIMARY > STANDBY 1 > STANDBY 2
Our Environments
ROLE LOCATION IP HOSTNAME CDB NAME PDB NAME DB UNIQUE NAME DB VERSION OS PRIMARY Bangalore 192.168.***.165 PRI-TESTDB PRITESTD TESTDB1 PRITESTDB 19.3.0.0.0 Oracle Linux Server 7.8 STANDBY 1 Chennai 192.168.***.166 TESTDB-STD1 PRITESTD TESTDB1 TESTDBSTD1 19.3.0.0.0 Oracle Linux Server 7.8 STANDBY 2 Mumbai 192.168.***.167 TESTDB-STD2 PRITESTD TESTDB1 TESTDBSTD2 19.3.0.0.0 Oracle Linux Server 7.8
Login to the all the server and add the hostname
First turn off firewall on all the 3 server
Primary DB
[root@PRI-TESTDB ~]# cat /etc/hosts 127.0.0.1 localhost localhost.localdomain localhost4 localhost4.localdomain4 ::1 localhost localhost.localdomain localhost6 localhost6.localdomain6 192.168.***.165 PRI-TESTDB 192.168.***.166 TESTDB-STD1 192.168.***.167 TESTDB-STD2
Standby DB 1
[root@TESTDB-STD1 ~]# cat /etc/hosts 127.0.0.1 localhost localhost.localdomain localhost4 localhost4.localdomain4 ::1 localhost localhost.localdomain localhost6 localhost6.localdomain6 192.168.***.165 PRI-TESTDB 192.168.***.166 TESTDB-STD1 192.168.***.167 TESTDB-STD2
Standby DB 3
[root@localhost ~]# cat /etc/hosts 127.0.0.1 localhost localhost.localdomain localhost4 localhost4.localdomain4 ::1 localhost localhost.localdomain localhost6 localhost6.localdomain6 192.168.***.165 PRI-TESTDB 192.168.***.166 TESTDB-STD1 192.168.***.167 TESTDB-STD2
Configuration on Primary DB Server
Install SOFTWARE + CDB + PDB.
SQL> select name, version, status from v$instance, v$database; NAME VERSION STATUS ---------------------------------- PRITESTD 19.0.0.0.0 OPEN
Enable the database in archivelog mode.
SQL > shu immediate; SQL > startup mount; SQL > alter database archivelog; SQL> alter system set log_archive_dest_1='LOCATION=/u01/app/oracle/fast_recovery_area'; System altered.
SQL> show parameter log_archive_dest_1 NAME TYPE VALUE ------------------------------------------------------------------------------ log_archive_dest_1 string LOCATION=/u01/app/oracle/fast_recovery_area
SQL > alter database open;
Create a default location for storing datafile.
SQL> alter system set db_create_online_log_dest_1='/u01/app/oracle/oradata/'; System altered.
Create the require logfile
SQL> alter database add logfile group 4 size 100m; Database altered.
SQL> alter database add logfile group 5 size 100m; Database altered.
SQL> select * from V$log; GROUP# THREAD# SEQUENCE# BYTES BLOCKSIZE MEMBERS ARC STATUS FIRST_CHANGE# FIRST_TIM NEXT_CHANGE# NEXT_TIME CON_ID -------------------------------------------------------------------------------------------------------------------------------------------- 1 1 56 104857600 512 1 YES ACTIVE 2533483 15-DEC-24 2533504 15-DEC-24 0 2 1 58 104857600 512 1 YES ACTIVE 2533507 15-DEC-24 2533510 15-DEC-24 0 3 1 57 104857600 512 1 YES ACTIVE 2533504 15-DEC-24 2533507 15-DEC-24 0 4 1 59 104857600 512 1 YES ACTIVE 2533510 15-DEC-24 2533513 15-DEC-24 0 5 1 60 104857600 512 1 NO CURRENT 2533513 15-DEC-24 1.8447E+19 0
Add Standby logfile
SQL> alter database add standby logfile group 11 size 100m; Database altered.
SQL> alter database add standby logfile group 12 size 100m; Database altered.
SQL> alter database add standby logfile group 13 size 100m; Database altered.
SQL> alter database add standby logfile group 14 size 100m; Database altered.
SQL> alter database add standby logfile group 15 size 100m; Database altered.
SQL> alter database add standby logfile group 16 size 100m; Database altered.
Verify
SQL> SELECT GROUP#,THREAD#,SEQUENCE#,ARCHIVED,STATUS FROM V$STANDBY_LOG; GROUP# THREAD# SEQUENCE# ARC STATUS ---------- ---------- ---------- --- ---------- 11 0 0 YES UNASSIGNED 12 0 0 YES UNASSIGNED 13 0 0 YES UNASSIGNED 14 0 0 YES UNASSIGNED 15 0 0 YES UNASSIGNED 16 0 0 YES UNASSIGNED
Add the Database entries into the tnsnames.ora file on PRIMARY DATABASE, STANDBY DATABASE 1 & STANDBY DATABASE 2
[oracle@PRITESTDB ~]$ vi $ORACLE_HOME/network/admin/tnsnames.ora PRITESTDB = (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = PRI-TESTDB)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = PRITESTDB) ) ) TESTDBSTD1 = (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = TESTDB-STD1)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = TESTDBSTD1) ) ) TESTDBSTD2 = (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = TESTDB-STD2)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = TESTDBSTD2) ) )
Transfer the ORAPASSWD File from Primary to both Standby database server 1 & 2
From Primary DB Server to Standby Server
[oracle@PRI-TESTDB ~]$ cd $ORACLE_HOME/dbs [oracle@PRI-TESTDB dbs]$ scp orapwPRITESTDB oracle@192.168.***.166:/u01/app/oracle/product/19C/dbhome_1/dbs/orapwTESTDBSTD1
[oracle@PRI-TESTDB ~]$ cd $ORACLE_HOME/dbs [oracle@PRI-TESTDB dbs]$ scp orapwPRITESTDB oracle@192.168.***.166:/u01/app/oracle/product/19C/dbhome_1/dbs/orapwTESTDBSTD2
Configure the require parameter in Primary db for data guard syncronization.
SQL> show parameter db_unique_name NAME TYPE VALUE ------------------------------------- db_unique_name string PRITESTDB
SQL> ALTER SYSTEM SET log_archive_config='dg_config=(PRITESTDB,TESTDBSTD1,TESTDBSTD2)' SCOPE=SPFILE; System altered.
SQL> ALTER SYSTEM SET log_archive_dest_1='LOCATION=C:\Archive valid_for=(all_logfiles,all_roles) db_unique_name=PRITESTDB' SCOPE=SPFILE; System altered.
SQL> ALTER SYSTEM SET log_archive_dest_2='service=TESTDBSTD1 async valid_for=(online_logfiles,primary_role) db_unique_name=TESTDBSTD1' SCOPE=SPFILE; System altered.
SQL> ALTER SYSTEM SET log_archive_dest_3='service=TESTDBSTD2 async valid_for=(standby_logfiles,standby_role) db_unique_name=TESTDBSTD2' SCOPE=SPFILE; System altered.
SQL> ALTER SYSTEM SET log_archive_dest_state_3='DEFER' SCOPE=SPFILE; System altered.
SQL> ALTER SYSTEM SET fal_server='TESTDBSTD1' SCOPE=SPFILE; System altered.
SQL> ALTER SYSTEM SET fal_client='PRITESTDB' SCOPE=SPFILE; System altered.
SQL> ALTER SYSTEM SET standby_file_management='AUTO' SCOPE=SPFILE; System altered.
SQL> ALTER SYSTEM SET db_file_name_convert='/u01/app/oracle/oradata/PRITESTDB/','/u01/app/oracle/oradata/TESTDBSTD1/' SCOPE=SPFILE; System altered.
SQL> ALTER SYSTEM SET log_file_name_convert='/u01/app/oracle/oradata/PRITESTDB/','/u01/app/oracle/oradata/TESTDBSTD1/' SCOPE=SPFILE; System altered.
Create a pfile from spfile before change for backup purpose.
SQL> create pfile='/tmp/pfile_new_15feb25.ora' from spfile; File created.
SQL> shu immediate; SQL> startup;
Configure the STANDBY DATABASE 1: TESTDBSTD1
Create a pfile:
vi initTESTDBSTD1.ora TESTDBSTD1.__data_transfer_cache_size=0 TESTDBSTD1.__db_cache_size=587202560 TESTDBSTD1.__inmemory_ext_roarea=0 TESTDBSTD1.__inmemory_ext_rwarea=0 TESTDBSTD1.__java_pool_size=0 TESTDBSTD1.__large_pool_size=16777216 TESTDBSTD1.__oracle_base='/u01/app/oracle'#ORACLE_BASE set from environment TESTDBSTD1.__pga_aggregate_target=536870912 TESTDBSTD1.__sga_target=1577058304 TESTDBSTD1.__shared_io_pool_size=0 TESTDBSTD1.__shared_pool_size=956301312 TESTDBSTD1.__streams_pool_size=0 TESTDBSTD1.__unified_pga_pool_size=0 *.audit_file_dest='/u01/app/oracle/admin/TESTDBSTD1/adump' *.audit_trail='db' *.compatible='19.0.0' *.control_files='/u01/app/oracle/oradata/TESTDBSTD1/control01.ctl','/u01/app/oracle/oradata/TESTDBSTD1/control02.ctl' *.db_block_size=8192 *.db_create_online_log_dest_1='/u01/app/oracle/oradata/' *.db_file_name_convert='/u01/app/oracle/oradata/PRITESTDB/','/u01/app/oracle/oradata/TESTDBSTD1/' *.db_name='PRITESTD' *.db_recovery_file_dest_size=10737418240 *.db_recovery_file_dest='/u01/app/oracle/fast_recovery_area/' *.db_unique_name='TESTDBSTD1' *.diagnostic_dest='/u01/app/oracle' *.dispatchers='(PROTOCOL=TCP) (SERVICE=PRITESTDBXDB)' *.enable_pluggable_database=true *.fal_client='TESTDBSTD1' *.fal_server='PRITESTDB' *.local_listener='(ADDRESS=(PROTOCOL=TCP)(HOST=TESTDB-STD1)(PORT=1521))' *.log_archive_config='dg_config=(PRITESTDB,TESTDBSTD1,TESTDBSTD2)' *.log_archive_dest_1='LOCATION=/u01/app/oracle/fast_recovery_area/TESTDBSTD1/archivelog/ valid_for=(all_logfiles,all_roles) db_unique_name=TESTDBSTD1' *.log_archive_dest_2='service=PRITESTDB async valid_for=(online_logfiles,primary_role) db_unique_name=PRITESTDB' *.log_archive_dest_3='service=TESTDBSTD2 async valid_for=(standby_logfiles,standby_role) db_unique_name=TESTDBSTD2' *.log_archive_dest_state_3='ENABLE' *.log_file_name_convert='/u01/app/oracle/oradata/PRITESTDB/','/u01/app/oracle/oradata/TESTDBSTD1/' *.nls_language='AMERICAN' *.nls_territory='AMERICA' *.open_cursors=300 *.pga_aggregate_target=500m *.processes=5000 *.remote_login_passwordfile='EXCLUSIVE' *.sga_target=1500m *.standby_file_management='AUTO' *.undo_tablespace='UNDOTBS1'
Create the below directory on STANDBY DB 1
[oracle@TESTDBSTD1 dbs]$ mkdir -p /u01/app/oracle/admin/TESTDBSTD1/adump [oracle@TESTDBSTD1 dbs]$ mkdir -p /u01/app/oracle/oradata/TESTDBSTD1/ [oracle@TESTDBSTD1 dbs]$ mkdir -p /u01/app/oracle/oradata/ [oracle@TESTDBSTD1 dbs]$ mkdir -p /u01/app/oracle/oradata/PRITESTDB/ [oracle@TESTDBSTD1 dbs]$ mkdir -p /u01/app/oracle/fast_recovery_area/ [oracle@TESTDBSTD1 dbs]$ mkdir -p /u01/app/oracle/fast_recovery_area/TESTDBSTD1/archivelog/
Backup the Primary database and transfer backup file into the Startup database server 1 & restore on it.
[oracle@TESTDBSTD1 ~]$ ls arch_PRITESTD_20250215_7.bck controlstby.ctl df_PRITESTD_20250215_5.bck df_PRITESTD_20250215_6.bck
Restore on Standby DB 1
On Standby database TESTDBSTD1
Startup database in nomount mode.
SQL> startup nomount; ORACLE instance started. Total System Global Area 1577056568 bytes Fixed Size 9144632 bytes Variable Size 956301312 bytes Database Buffers 603979776 bytes Redo Buffers 7630848 bytes
[oracle@TESTDB-STD1 fulldb_bkp]$ rman target /
Recovery Manager: Release 19.0.0.0.0 - Production on Sat Feb 15 16:21:42 2025
Version 19.3.0.0.0
Copyright (c) 1982, 2019, Oracle and/or its affiliates. All rights reserved.
connected to target database: PRITESTD (DBID=2511388085, not open)
RMAN> restore controlfile from '/u01/app/oracle/backup/rman/fulldb_bkp/controlstby.ctl'
RMAN> alter database mount;
RMAN> catalog start with '/u01/app/oracle/backup/rman/fulldb_bkp/';
RMAN> run {
2> set newname for database to '/u01/app/oracle/oradata/TESTDBSTD1/%b';
3> restore database;
4> switch datafile all;
5> }
Start recovery process of database
SQL> alter database recover managed standby database using current logfile disconnect from session; Database altered.
Check the archivelog applied process.
SQL> select process, status, thread#, sequence#, block#, blocks from v$managed_standby ; SQL> select thread#, max(sequence#) FROM v$archived_log where applied='YES' group by thread#;
Check the process of applied archive log and sync of database.
SQL> @stbytest.sql; PROCESS STATUS THREAD# SEQUENCE# BLOCK# BLOCKS --------- ------------ ---------- ------------------------------ ARCH CLOSING 1 327 1 1 DGRD ALLOCATED 0 0 0 0 DGRD ALLOCATED 0 0 0 0 ARCH CLOSING 1 327 1 1 ARCH CLOSING 1 326 1 7 ARCH CLOSING 1 314 1 1 RFS IDLE 1 0 0 0 LNS WRITING 1 328 157 1 RFS IDLE 1 0 0 0 RFS IDLE 0 0 0 0 RFS IDLE 0 0 0 0 RFS IDLE 0 0 0 0 MRP0 APPLYING_LOG 1 328 157 204800 DGRD ALLOCATED 0 0 0 0 RFS IDLE 1 328 1 1 15 rows selected. THREAD# MAX(SEQUENCE#) ---------- -------------- 1 327
Configure the STANDBY DATABASE 2: TESTDBSTD2
Create a pfile
vi initTESTDBSTD2.ora TESTDBSTD2.__data_transfer_cache_size=0 TESTDBSTD2.__db_cache_size=1090519040 TESTDBSTD2.__inmemory_ext_roarea=0 TESTDBSTD2.__inmemory_ext_rwarea=0 TESTDBSTD2.__java_pool_size=0 TESTDBSTD2.__large_pool_size=16777216 TESTDBSTD2.__oracle_base='/u01/app/oracle'#ORACLE_BASE set from environment TESTDBSTD2.__pga_aggregate_target=536870912 TESTDBSTD2.__sga_target=1577058304 TESTDBSTD2.__shared_io_pool_size=83886080 TESTDBSTD2.__shared_pool_size=369098752 TESTDBSTD2.__streams_pool_size=0 TESTDBSTD2.__unified_pga_pool_size=0 *.audit_file_dest='/u01/app/oracle/admin/TESTDBSTD2/adump' *.audit_trail='db' *.compatible='19.0.0' *.control_files='/u01/app/oracle/oradata/TESTDBSTD2/control01.ctl','/u01/app/oracle/oradata/TESTDBSTD2/control02.ctl' *.db_block_size=8192 *.db_create_online_log_dest_1='/u01/app/oracle/oradata/' *.db_file_name_convert='/u01/app/oracle/oradata/TESTDBSTD1/','/u01/app/oracle/oradata/TESTDBSTD2/' *.db_name='PRITESTD' *.db_recovery_file_dest_size=10737418240 *.db_recovery_file_dest='/u01/app/oracle/fast_recovery_area/' *.db_unique_name='TESTDBSTD2' *.diagnostic_dest='/u01/app/oracle' *.dispatchers='(PROTOCOL=TCP) (SERVICE=PRITESTDBXDB)' *.enable_pluggable_database=true *.fal_client='TESTDBSTD2' *.fal_server='TESTDBSTD1' *.log_archive_config='dg_config=(PRITESTDB,TESTDBSTD1,TESTDBSTD2)' *.log_archive_dest_1='LOCATION=/u01/app/oracle/fast_recovery_area/TESTDBSTD2/archivelog/ valid_for=(all_logfiles,all_roles) db_unique_name=TESTDBSTD2' *.log_archive_dest_3='service=TESTDBSTD1 async valid_for=(standby_logfiles,standby_role) db_unique_name=TESTDBSTD1' *.log_archive_dest_state_3='ENABLE' *.log_file_name_convert='/u01/app/oracle/oradata/TESTDBSTD1/','/u01/app/oracle/oradata/TESTDBSTD2/' *.nls_language='AMERICAN' *.nls_territory='AMERICA' *.open_cursors=300 *.pga_aggregate_target=500m *.processes=5000 *.remote_login_passwordfile='EXCLUSIVE' *.sga_target=1500m *.standby_file_management='AUTO' *.undo_tablespace='UNDOTBS1'
Create the below directory on STANDBY DB 2
[oracle@TESTDB-STD2 ~]$ mkdir -p /u01/app/oracle/admin/TESTDBSTD2/adump [oracle@TESTDB-STD2 ~]$ mkdir -p /u01/app/oracle/oradata/TESTDBSTD2/ [oracle@TESTDB-STD2 ~]$ mkdir -p /u01/app/oracle/fast_recovery_area/ [oracle@TESTDB-STD2 ~]$ mkdir -p /u01/app/oracle/fast_recovery_area/TESTDBSTD2/archivelog/ [oracle@TESTDB-STD2 ~]$ mkdir -p /u01/app/oracle/oradata/TESTDBSTD2/
Backup the Primary database and transfer into the Startup database server 1 & restore on it.
[oracle@TESTDB-STD2 ~]$ ls arch_PRITESTD_20250215_7.bck controlstby.ctl df_PRITESTD_20250215_5.bck df_PRITESTD_20250215_6.bck
Restore on Standby DB Server 1
On Standby database TESTDBSTD1
Startup database in nomount mode:
SQL> startup nomount; ORACLE instance started. Total System Global Area 1577056568 bytes Fixed Size 9144632 bytes Variable Size 956301312 bytes Database Buffers 603979776 bytes Redo Buffers 7630848 bytes
[oracle@TESTDB-STD2 ~]$ rman target / Recovery Manager: Release 19.0.0.0.0 - Production on Sun Feb 16 10:26:53 2025 Version 19.3.0.0.0 Copyright (c) 1982, 2019, Oracle and/or its affiliates. All rights reserved. connected to target database: PRITESTD (not mounted) RMAN> restore controlfile from '/u01/app/oracle/backup/rman/fulldb_bkp/controlstby.ctl' RMAN> alter database mount; RMAN> crosscheck backup; RMAN> delete noprompt expired backup; RMAN> crosscheck archivelog all; RMAN> delete noprompt expired archivelog all;
RMAN> catalog start with '/u01/app/oracle/backup/rman/fulldb_bkp/';
RMAN> run {
2> set newname for database to '/u01/app/oracle/oradata/TESTDBSTD2/%b';
3> restore database;
4> switch datafile all;
5> }
Start recovery process of database
SQL> alter database recover managed standby database using current logfile disconnect from session; Database altered.
Check the archivelog applied process.
SQL> select process, status, thread#, sequence#, block#, blocks from v$managed_standby ; SQL> select thread#, max(sequence#) FROM v$archived_log where applied='YES' group by thread#;
Check the process of applied archive log and sync of database.
SQL> @/home/oracle/scripts/stbytest.sql; PROCESS STATUS THREAD# SEQUENCE# BLOCK# BLOCKS --------- ------------------------------------------------------------ ARCH CLOSING 1 325 1 1 DGRD ALLOCATED 0 0 0 0 DGRD ALLOCATED 0 0 0 0 ARCH CLOSING 1 327 1 1 ARCH CLOSING 1 326 1 7 ARCH CLOSING 1 324 1 1 MRP0 APPLYING_LOG 1 328 157 204800 RFS IDLE 1 0 0 0 RFS IDLE 1 328 157 1 RFS IDLE 0 0 0 0 RFS IDLE 0 0 0 0 RFS IDLE 0 0 0 0 LNS CONNECTED 0 0 0 0 LNS WRITING 1 328 1 1 14 rows selected.
THREAD# MAX(SEQUENCE#)
———- ————–
1 327