CONFIGURE DATA GUARD CASCADING STANDBY CONTAINER DATABASE ON 19C

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

ROLE	LOCATION	IP	HOSTNAME	CDB NAME	PDB NAME	DB UNIQUE NAME	DB VERSION	OS
PRIMARY	Istanbul	192.168.56.101	istanbul	ISTCDB	IST	ISTPRI	19.9.0.0.201020	Windows Server 2016 Std.
STANDBY 1	Ankara	192.168.56.102	ankara	ISTCDB	IST	ISTANK	19.9.0.0.201020	Windows Server 2016 Std.
STANDBY 2	Diyarbakir	192.168.56.103	diyarbakir	ISTCDB	IST	ISTDYB	19.9.0.0.201020	Windows 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.

*.audit_file_dest='C:\Oracle\app\Administrator\admin\ISTANK\adump'
*.control_files='C:\OraDb\ISTANK\control01.ctl','C:\OraDb\ISTANK\control02.ctl'
*.db_file_name_convert='C:\OraDb\ISTPRI','C:\OraDb\ISTANK'
*.db_name='ISTCDB'
*.db_unique_name='ISTANK'
*.fal_client='ISTANK'
*.fal_server='ISTPRI'
*.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'
*.standby_file_management='AUTO'

Create below directories.

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

Create database Windows service.

C:\> 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.

C:\> sqlplus / as sysdba
SQL > shu immediate
SQL > startup nomount;

Connect to the RMAN.

C:\> 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.

*.audit_file_dest='C:\Oracle\app\Administrator\admin\ISTDYB\adump'
*.control_files='C:\OraDb\ISTDYB\control01.ctl','C:\OraDb\ISTDYB\control02.ctl'
*.db_file_name_convert='C:\OraDb\ISTPRI','C:\OraDb\ISTDYB'
*.db_name='ISTCDB'
*.db_unique_name='ISTDYB'
*.fal_client='ISTDYB'
*.fal_server='ISTANK'
*.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'
*.standby_file_management='AUTO'

Create below directories.

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

Create database Windows service.

C:\> 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.

C:\> sqlplus / as sysdba
SQL > shu immediate
SQL > startup nomount;

Connect to the RMAN.

C:\> 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.

MÜRSEL BAKAY

September 12, 2021

Oracle

PreviousLIMITING ACCESS TO SENSITIVE DATA USING ORACLE DATA REDACTION

UPGRADING AND CONVERTING A NON-CDB TO A PDB WITH THE SAME OPERATING SYSTEM (ON WINDOWS)

Oracle

PreviousLIMITING ACCESS TO SENSITIVE DATA USING ORACLE DATA REDACTION

Related Posts ...

UPGRADING AND CONVERTING A NON-CDB TO A PDB WITH THE SAME OPERATING SYSTEM (ON WINDOWS)