A. EXPLANATION

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
B. 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 4 ('C:\OraDb\ISTPRI\SBY_REDO04.LOG') SIZE 200M;
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 > 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