Hi all,

At this scenario, I will explaining how to configure Data Guard Cascading Standby Container Database on 19c.

For this exercise, I have one PRIMARY database and two STANDBY databases.

My data flow chart will be like below:

PRIMARY >>> STANDBY-1 >>> STANDBY-2

My Environments

ROLELOCATIONIPHOSTNAMECDB NAMEPDB NAMEDB UNIQUE NAMEDB VERSIONOS
PRIMARYIstanbul192.168.56.101istanbulISTCDBISTISTPRI19.9.0.0.201020Windows Server 2016 Std.
STANDBY-1Ankara192.168.56.102ankaraISTCDBISTISTANK19.9.0.0.201020Windows Server 2016 Std.
STANDBY-2Diyarbakir192.168.56.103diyarbakirISTCDBISTISTDYB19.9.0.0.201020Windows Server 2016 Std.
My Environments

CONFIGURATION

PRIMARY & STANDBY-1 & STANDBY-2

Turn off firewall.

Edit “hosts” file.

192.168.56.101       istanbul  
192.168.56.102       ankara    
192.168.56.103       diyarbakir
PRIMARY

Install SOFTWARE + CDB + PDB.

STANDBY-1

Install SOFTWARE ONLY.

STANDBY-2

Install SOFTWARE ONLY.

PRIMARY

Change log mode and set archivelog location.

SQL > shu immediate;
SQL > startup mount;
SQL > alter database archivelog;
SQL > alter system set log_archive_dest_1='LOCATION=C:\Archivelog' scope=spfile;
SQL > alter database open;

Force logging.

SQL > alter database force logging;

Add Standby logfiles.

SQL > ALTER DATABASE ADD STANDBY LOGFILE GROUP 5 ('C:\OraDb\ISTPRI\SBY_REDO05.LOG') SIZE 200M;
SQL > ALTER DATABASE ADD STANDBY LOGFILE GROUP 6 ('C:\OraDb\ISTPRI\SBY_REDO06.LOG') SIZE 200M;
SQL > ALTER DATABASE ADD STANDBY LOGFILE GROUP 7 ('C:\OraDb\ISTPRI\SBY_REDO07.LOG') SIZE 200M;
SQL > ALTER DATABASE ADD STANDBY LOGFILE GROUP 4 ('C:\OraDb\ISTPRI\SBY_REDO04.LOG') SIZE 200M;
SQL > SELECT GROUP#,THREAD#,SEQUENCE#,ARCHIVED,STATUS FROM V$STANDBY_LOG;

Edit “listener.ora” file.

SID_LIST_LISTENER =
  (SID_LIST =
    (SID_DESC =
      (SID_NAME = CLRExtProc)
      (ORACLE_HOME = C:\Oracle\app\administrator\product\19.0.0\dbhome_1)
      (PROGRAM = extproc)
      (ENVS = "EXTPROC_DLLS=ONLY:C:\Oracle\app\administrator\product\19.0.0\dbhome_1\bin\oraclr19.dll")
    )
    (SID_DESC =
      (GLOBAL_DBNAME = ISTPRI)
      (ORACLE_HOME = C:\Oracle\app\administrator\product\19.0.0\dbhome_1)
      (SID_NAME = ISTCDB)
    )
    (SID_DESC =
      (GLOBAL_DBNAME = IST)
      (ORACLE_HOME = C:\Oracle\app\administrator\product\19.0.0\dbhome_1)
      (SID_NAME = ISTCDB)
    )
  )

LISTENER =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = TCP)(HOST = istanbul)(PORT = 1521))
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
    )
  )

ADR_BASE_LISTENER = C:\Oracle\app\administrator
STANDBY-1

Edit “listener.ora” file.

SID_LIST_LISTENER =
  (SID_LIST =
    (SID_DESC =
      (SID_NAME = CLRExtProc)
      (ORACLE_HOME = C:\Oracle\app\administrator\product\19.0.0\dbhome_1)
      (PROGRAM = extproc)
      (ENVS = "EXTPROC_DLLS=ONLY:C:\Oracle\app\administrator\product\19.0.0\dbhome_1\bin\oraclr19.dll")
    )
    (SID_DESC =
      (GLOBAL_DBNAME = ISTANK)
      (ORACLE_HOME = C:\Oracle\app\administrator\product\19.0.0\dbhome_1)
      (SID_NAME = ISTCDB)
    )
    (SID_DESC =
      (GLOBAL_DBNAME = IST)
      (ORACLE_HOME = C:\Oracle\app\administrator\product\19.0.0\dbhome_1)
      (SID_NAME = ISTCDB)
    )
  )

LISTENER =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = TCP)(HOST = ankara)(PORT = 1521))
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
    )
  )

ADR_BASE_LISTENER = C:\Oracle\app\administrator
STANDBY-2

Edit “listener.ora” file.

SID_LIST_LISTENER =
  (SID_LIST =
    (SID_DESC =
      (SID_NAME = CLRExtProc)
      (ORACLE_HOME = C:\Oracle\app\administrator\product\19.0.0\dbhome_1)
      (PROGRAM = extproc)
      (ENVS = "EXTPROC_DLLS=ONLY:C:\Oracle\app\administrator\product\19.0.0\dbhome_1\bin\oraclr19.dll")
    )
    (SID_DESC =
      (GLOBAL_DBNAME = ISTDYB)
      (ORACLE_HOME = C:\Oracle\app\administrator\product\19.0.0\dbhome_1)
      (SID_NAME = ISTCDB)
    )
    (SID_DESC =
      (GLOBAL_DBNAME = IST)
      (ORACLE_HOME = C:\Oracle\app\administrator\product\19.0.0\dbhome_1)
      (SID_NAME = ISTCDB)
    )
  )

LISTENER =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = TCP)(HOST = diyarbakir)(PORT = 1521))
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
    )
  )

ADR_BASE_LISTENER = C:\Oracle\app\administrator
PRIMARY & STANDBY-1 & STANDBY-2

Edit “tnsnames.ora” file.

IST =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = istanbul)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = ISTPRI)
    )
  )

ISTPRI =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = istanbul)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = ISTPRI)
    )
  )

ISTANK =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = ankara)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = ISTANK)
    )
  )

ISTDYB =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = diyarbakir)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = ISTDYB)
    )
  )

Stop&Start listener.

PRIMARY

SQL > ALTER SYSTEM SET db_unique_name='ISTPRI' SCOPE=SPFILE;		
SQL > ALTER SYSTEM SET log_archive_config='dg_config=(ISTPRI,ISTANK,ISTDYB)' SCOPE=SPFILE;				
SQL > ALTER SYSTEM SET log_archive_dest_1='LOCATION=C:\Archive valid_for=(all_logfiles,all_roles) db_unique_name=ISTPRI' SCOPE=SPFILE;
SQL > ALTER SYSTEM SET log_archive_dest_2='service=ISTANK async valid_for=(online_logfiles,primary_role) db_unique_name=ISTANK' SCOPE=SPFILE;
SQL > ALTER SYSTEM SET log_archive_dest_3='service=ISTDYB async valid_for=(standby_logfiles,standby_role) db_unique_name=ISTDYB' SCOPE=SPFILE;
SQL > ALTER SYSTEM SET log_archive_dest_state_3='DEFER' SCOPE=SPFILE;
SQL > ALTER SYSTEM SET fal_server='ISTANK' SCOPE=SPFILE;
SQL > ALTER SYSTEM SET fal_client='ISTPRI' SCOPE=SPFILE;
SQL > ALTER SYSTEM SET standby_file_management='AUTO' SCOPE=SPFILE;
SQL > ALTER SYSTEM SET db_file_name_convert='C:\OraDb\ISTPRI','C:\OraDb\ISTANK' SCOPE=SPFILE;
SQL > ALTER SYSTEM SET log_file_name_convert='C:\OraDb\ISTPRI','C:\OraDb\ISTANK' SCOPE=SPFILE;
SQL > shu immediate
SQL > startup
SQL > create pfile from spfile;

Copy “INITISTCDB.ORA” file to STANDBY-1 and STANDBY-2.

STANDBY-1

Edit “INITISTCDB.ORA” file.

istcdb.__data_transfer_cache_size=0
istcdb.__db_cache_size=654311424
istcdb.__inmemory_ext_roarea=0
istcdb.__inmemory_ext_rwarea=0
istcdb.__java_pool_size=16777216
istcdb.__large_pool_size=16777216
istcdb.__oracle_base='C:\Oracle\app\Administrator'#ORACLE_BASE set from environment
istcdb.__pga_aggregate_target=704643072
istcdb.__sga_target=1023410176
istcdb.__shared_io_pool_size=50331648
istcdb.__shared_pool_size=268435456
istcdb.__streams_pool_size=0
istcdb.__unified_pga_pool_size=0
*.audit_file_dest='C:\Oracle\app\Administrator\admin\ISTANK\adump'
*.audit_trail='db'
*.compatible='19.0.0'
*.control_files='C:\OraDb\ISTANK\control01.ctl','C:\OraDb\ISTANK\control02.ctl'
*.db_block_size=8192
*.db_file_name_convert='C:\OraDb\ISTPRI','C:\OraDb\ISTANK'
*.db_name='ISTCDB'
*.db_unique_name='ISTANK'
*.diagnostic_dest='C:\Oracle\app\Administrator'
*.dispatchers='(PROTOCOL=TCP) (SERVICE=ISTCDBXDB)'
*.enable_pluggable_database=true
*.fal_client='ISTANK'
*.fal_server='ISTPRI'
*.local_listener='LISTENER_ISTCDB'
*.log_archive_config='dg_config=(ISTPRI,ISTANK,ISTDYB)'
*.log_archive_dest_1='LOCATION=C:\Archive valid_for=(all_logfiles,all_roles) db_unique_name=ISTANK'
*.log_archive_dest_2='service=ISTPRI async valid_for=(online_logfiles,primary_role) db_unique_name=ISTPRI'
*.log_archive_dest_3='service=ISTDYB async valid_for=(standby_logfiles,standby_role) db_unique_name=ISTDYB'
*.log_archive_dest_state_3='ENABLE'
*.log_file_name_convert='C:\OraDb\ISTPRI','C:\OraDb\ISTANK'
*.memory_target=1638m
*.nls_language='AMERICAN'
*.nls_territory='AMERICA'
*.open_cursors=300
*.processes=300
*.remote_login_passwordfile='EXCLUSIVE'
*.standby_file_management='AUTO'
*.undo_tablespace='UNDOTBS1'

Create below directories.

C:\Oracle\app\administrator\admin\ISTANK\adump
C:\OraDb\ISTANK
C:\Archive

Create database Windows service.

oradim -new -intpwd <SYS_PASSWORD> -sid ISTCDB -startmode auto -pfile C:\Oracle\app\administrator\product\19.0.0\dbhome_1\database\INITISTCDB.ORA

Startup the database.

sqlplus / as sysdba
SQL > shu immediate
SQL > startup nomount;

Connect to the RMAN.

rman target sys/"<SYS_PASSWORD>"@ISTPRI auxiliary sys/"<SYS_PASSWORD>"@ISTANK

Start duplicate database with the RMAN.

RMAN >
 run
{
allocate channel p1 type disk;
allocate auxiliary channel s1 type disk;
duplicate target database for standby from active database nofilenamecheck;
}

Start recovery database.

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

Check archivelog apply.

SQL > SELECT sequence#, first_time, next_time, applied FROM v$archived_log ORDER BY sequence#;
STANDBY-2

Edit “INITISTCDB.ORA” file.

istcdb.__data_transfer_cache_size=0
istcdb.__db_cache_size=654311424
istcdb.__inmemory_ext_roarea=0
istcdb.__inmemory_ext_rwarea=0
istcdb.__java_pool_size=16777216
istcdb.__large_pool_size=16777216
istcdb.__oracle_base='C:\Oracle\app\Administrator'#ORACLE_BASE set from environment
istcdb.__pga_aggregate_target=704643072
istcdb.__sga_target=1023410176
istcdb.__shared_io_pool_size=50331648
istcdb.__shared_pool_size=268435456
istcdb.__streams_pool_size=0
istcdb.__unified_pga_pool_size=0
*.audit_file_dest='C:\Oracle\app\Administrator\admin\ISTDYB\adump'
*.audit_trail='db'
*.compatible='19.0.0'
*.control_files='C:\OraDb\ISTDYB\control01.ctl','C:\OraDb\ISTDYB\control02.ctl'
*.db_block_size=8192
*.db_file_name_convert='C:\OraDb\ISTPRI','C:\OraDb\ISTDYB'
*.db_name='ISTCDB'
*.db_unique_name='ISTDYB'
*.diagnostic_dest='C:\Oracle\app\Administrator'
*.dispatchers='(PROTOCOL=TCP) (SERVICE=ISTCDBXDB)'
*.enable_pluggable_database=true
*.fal_client='ISTDYB'
*.fal_server='ISTANK'
*.local_listener='LISTENER_ISTCDB'
*.log_archive_config='dg_config=(ISTPRI,ISTANK,ISTDYB)'
*.log_archive_dest_1='LOCATION=C:\Archive'
*.log_file_name_convert='C:\OraDb\ISTPRI','C:\OraDb\ISTDYB'
*.memory_target=1638m
*.nls_language='AMERICAN'
*.nls_territory='AMERICA'
*.open_cursors=300
*.processes=300
*.remote_login_passwordfile='EXCLUSIVE'
*.standby_file_management='AUTO'
*.undo_tablespace='UNDOTBS1'

Create below directories.

C:\Oracle\app\administrator\admin\ISTDYB\adump
C:\OraDb\ISTDYB
C:\Archive

Create database Windows service.

oradim -new -intpwd <SYS_PASSWORD> -sid ISTCDB -startmode auto -pfile C:\Oracle\app\administrator\product\19.0.0\dbhome_1\database\INITISTCDB.ORA

Startup the database.

sqlplus / as sysdba
SQL > shu immediate
SQL > startup nomount;

Connect to the RMAN.

rman target sys/"<SYS_PASSWORD>"@ISTPRI auxiliary sys/"<SYS_PASSWORD>"@ISTDYB

Start duplicate database with the RMAN.

RMAN > run
{
allocate channel p1 type disk;
allocate auxiliary channel s1 type disk;
duplicate target database for standby from active database nofilenamecheck;
}

Start recovery database.

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

Check archivelog apply.

SQL > SELECT sequence#, first_time, next_time, applied FROM v$archived_log ORDER BY sequence#;

That’s all.

See you later.

Oracle

19cCASCADINGCONTAINERDATA GUARDSTANDBY