Step by Step Oracle 19c Physical Standby CDB Database Setup

Stepping through the process of setting up an active physical standby database using ORacle 19c CDB database. This is a simple CDB database and steps to configuring primary and standby environments.

Environment details

Primary
DBname: DB19B1
host: snode2
Standby
DBname: DB19B1SB
host: snode3

Prepare Primary database

Enable force logging and archivelog mode

Oracle Database lets you force the writing of redo records even when NOLOGGING has been specified in DDL statements. It is always recomeneed to enable force loggin for replication of databases.

SQL> alter database force logging;

Database altered.

 SQL> archive log list
    Database log mode              Archive Mode
    Automatic archival             Enabled
    Archive destination            db_recovery_file_dest
    Oldest online log sequence     26
    Next log sequence to archive   28
    Current log sequence           28
SQL> 

DB19B1 was already in archive log mode. If the database not archive log mode enabled. Following steps will let you enabled archive log mode.
set log_archive_dest paramters or use Archive destination USE_DB_RECOVERY_FILE_DEST
shutdown and startup the database in mount mode and enable archiving.

SQL> alter database archivelog;

Now you can open the database and check the archive log mode using

SQL> archive log list

Standby Redo log files

It is oracle best practice to enable standby redo logs for standby database. Best way to do this is to add standby redo log in the Primary before starting the duplicating for standby. So an active duplicate command will add standby redo log files as part of this active duplicate command
If you are using Maximum Protection mode, you must have SRLs configured. If you are using Maximum Performance mode, you will want to create SRLs to keep the data loss to a minimum.
Also, it is best practice to add one more standby redo log for each primary thread. The standby redo log must have at least one more redo log group than the redo log at the redo source database, for each redo thread at the redo source database.
This will come in to play when primary is a very busy and switching log very frequently.

Check the current size of the redo log.

SQL> select group#,bytes/1028/1028 from v$log;   

    GROUP# BYTES/1028/1028
    ---------- ---------------
     1      198.446608
     2      198.446608
     3      198.446608

It is 200MB and I have 3 log groups

alter database add standby logfile '/u03/oradata/DB19B1/stdby_redo01.log' SIZE 200M;
alter database add standby logfile '/u03/oradata/DB19B1/stdby_redo02.log' SIZE 200M;
alter database add standby logfile '/u03/oradata/DB19B1/stdby_redo03.log' SIZE 200M;
alter database add standby logfile '/u03/oradata/DB19B1/stdby_redo04.log' SIZE 200M;

Side note, For Oracle RAC primary database, create the number of SRLs equal to the number of ORLs in all primary instances.
For example, if you have a 2-node RAC database with 3 ORLs in each thread, create 6 SRLs (2×3) in your standby.
No matter how many instances are in your standby, the standby needs enough SRLs to support all ORLs in the primary, for all instances.

Primary Database Initilization Parameters

On the primary database, define initialization parameters that control redo transport services while the database is in the primary role. There are additional parameters needed to add that controls the receipt of the redo data and apply services when the primary database is transitioned to the standby role.

alter system set log_archive_config='dg_config=(DB19B1,DB19B1SB)' scope=spfile;
alter system set log_archive_dest_1='location=use_db_recovery_file_dest valid_for=(all_logfiles,all_roles) db_unique_name=DB19B1' scope=spfile;
alter system set log_archive_dest_2='service=DB19B1SB async valid_for=(online_logfiles,primary_role) db_unique_name=DB19B1SB' scope=spfile;
alter system set log_archive_dest_state_1=enable scope=spfile;
alter system set remote_login_passwordfile=exclusive scope=spfile;
alter system set log_archive_format='DB19B1_%t_%s_%r.arc' scope=spfile;
alter system set fal_server=DB19B1SB scope=spfile;
alter system set db_file_name_convert='DB19B1','DB19B1SB' scope=spfile;
alter system set log_file_name_convert='DB19B1','DB19B1SB' scope=spfile;
alter system set standby_file_management=auto scope=spfile;

Configure log_archive_config properly, if there are multiple standby databases all of them has to be part of log_archive_config. If not, FAL request will not be honurd.

alter system set log_archive_dest_state_2=defer scope=spfile;

log_archive_dest_state_2 can be defered until standby is ready.

Set up listener and tns entries. This has to be done on both primary and standby

My listener is not using default port, I am configure static listener entries. It best practice to configure static entry for primary and standby databases.

SID_LIST_LISTENER19B =
(SID_LIST =
(SID_DESC =
(ORACLE_HOME = /u02/app/oracle/product/193000)
(SID_NAME = DB19B1)
)
)

Create listener on both primary and standby then configure the TNS entries in the tnsnames.ora file.

DB19B1 =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = snode2)(PORT = 1528))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = DB19B1)
    )
  )
DB19B1SB =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = snode3)(PORT = 1528))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = DB19B1SB)
    )
  )

Copy the password file to standby $ORACLE_HOME/dbs folder. This is an important step if SYS user is used for redo transport(default). If a different user is used for redo transport (REDO_TRANSPORT_USER) then primary password file has to configured before copying the password fiel to standby.

DB19B1:snode2:oracle$ scp orapwDB19B1 oracle@snode3://u02/app/oracle/product/193000/dbs
  oracle@snode3's password: 
  orapwDB19B1  
  on snode3 (standby)
  cd $ORACLE_HOME/dbs
  mv orapwDB19B1 orapwDB19B1SB

Create an init.ora file for standby (auxialary) db DB19B1SB

cd $ORACLE_HOME/dbs
echo "DB_NAME=DB19B1SB" > initDB19B1SB.ora

Create audit trail location.

/u01/app/oracle/admin/DB19B1SB/adump

Startup the auxialary in nomount mode

DB19B1SB:snode3:oracle$ sqlplus / as sysdba
SQL*Plus: Release 19.0.0.0.0 - Production on Wed Jul 29 12:33:32 2020
Version 19.6.1.0.0
Copyright (c) 1982, 2019, Oracle.  All rights reserved.
Connected to an idle instance.
SQL> startup nomount;
ORACLE instance started.        
Total System Global Area  285211696 bytes
Fixed Size                  8895536 bytes
Variable Size             218103808 bytes
Database Buffers           50331648 bytes
Redo Buffers                7880704 bytes
SQL> exit

Create destination directories. If you are using ASM, then just creating disk is good enough.

$ mkdir /u03/oradata/DB19B1SB/OPTIMAL (This is my PDB)
$ mkdir /u03/oradata/DB19B1SB/pdbseed (This is a CDB database)

Create standby database

DB19B1SB:snode3:oracle$ rman
Recovery Manager: Release 19.0.0.0.0 - Production on Thu Jul 30 09:34:55 2020
Version 19.6.1.0.0
Copyright (c) 1982, 2019, Oracle and/or its affiliates. All rights reserved.
RMAN>
RMAN> connect target sys/oracle@DB19B1
connect auxiliary sys/oracle@DB19B1SB
connected to target database: DB19B1 (DBID=4243339102)
RMAN>
RMAN>
connected to auxiliary database: DB19B1 (not mounted)
RMAN> run
2> {
3> allocate channel p1 type disk;
4> allocate channel p2 type disk;
5> allocate auxiliary channel a1 type disk;
6> duplicate target database for standby from active database
7> spfile
8> parameter_value_convert 'DB19B1','DB19B1SB'
9> set db_unique_name='DB19B1SB'
10> set db_name="DB19B1"
11> set control_files='/u03/oradata/DB19B1SB/control01.ctl','/u03/oradata/DB19B1SB/flash_recovery_area/control02.ctl'
12> set db_file_name_convert='/DB19B1/','/DB19B1SB/'
13> set log_file_name_convert='/DB19B1/','/DB19B1SB/'
14> set standby_file_management='AUTO'
15> set log_archive_config='dg_config=(DB19B1,DB19B1SB)'
16> set db_recovery_file_dest='/u03/oradata/DB19B1SB/flash_recovery_area/';
17> release channel p1;
18> release channel p2;
19> }
using target database control file instead of recovery catalog
allocated channel: p1
channel p1: SID=31 device type=DISK
allocated channel: p2
channel p2: SID=185 device type=DISK
allocated channel: a1
channel a1: SID=13 device type=DISK
Starting Duplicate Db at 30-JUL-20
contents of Memory Script:
{
backup as copy reuse
passwordfile auxiliary format '/u02/app/oracle/product/193000/dbs/orapwDB19B1SB' targetfile
'/u02/app/oracle/product/193000/dbs/spfileDB19B1.ora' auxiliary format
'/u02/app/oracle/product/193000/dbs/spfileDB19B1SB.ora' ;
sql clone "alter system set spfile= ''/u02/app/oracle/product/193000/dbs/spfileDB19B1SB.ora''";
}
executing Memory Script
Starting backup at 30-JUL-20
Finished backup at 30-JUL-20
sql statement: alter system set spfile= ''/u02/app/oracle/product/193000/dbs/spfileDB19B1SB.ora''
contents of Memory Script:
{
sql clone "alter system set audit_file_dest =
''/u01/app/oracle/admin/DB19B1SB/adump'' comment=
'''' scope=spfile";
sql clone "alter system set dispatchers =
''(PROTOCOL=TCP) (SERVICE=DB19B1SBXDB)'' comment=
'''' scope=spfile";
sql clone "alter system set db_unique_name =
''DB19B1SB'' comment=
'''' scope=spfile";
sql clone "alter system set db_name =
''DB19B1'' comment=
'''' scope=spfile";
sql clone "alter system set control_files =
''/u03/oradata/DB19B1SB/control01.ctl'', ''/u03/oradata/DB19B1SB/flash_recovery_area/control02.ctl'' comment=
'''' scope=spfile";
sql clone "alter system set db_file_name_convert =
''/DB19B1/'', ''/DB19B1SB/'' comment=
'''' scope=spfile";
sql clone "alter system set log_file_name_convert =
''/DB19B1/'', ''/DB19B1SB/'' comment=
'''' scope=spfile";
sql clone "alter system set standby_file_management =
''AUTO'' comment=
'''' scope=spfile";
sql clone "alter system set log_archive_config =
''dg_config=(DB19B1,DB19B1SB)'' comment=
'''' scope=spfile";
sql clone "alter system set db_recovery_file_dest =
''/u03/oradata/DB19B1SB/flash_recovery_area/'' comment=
'''' scope=spfile";
shutdown clone immediate;
startup clone nomount;
}
executing Memory Script
sql statement: alter system set audit_file_dest = ''/u01/app/oracle/admin/DB19B1SB/adump'' comment= '''' scope=spfile
sql statement: alter system set dispatchers = ''(PROTOCOL=TCP) (SERVICE=DB19B1SBXDB)'' comment= '''' scope=spfile
sql statement: alter system set db_unique_name = ''DB19B1SB'' comment= '''' scope=spfile
sql statement: alter system set db_name = ''DB19B1'' comment= '''' scope=spfile
sql statement: alter system set control_files = ''/u03/oradata/DB19B1SB/control01.ctl'', ''/u03/oradata/DB19B1SB/flash_recovery_area/control02.ctl'' comment= '''' scope=spfile
sql statement: alter system set db_file_name_convert = ''/DB19B1/'', ''/DB19B1SB/'' comment= '''' scope=spfile
sql statement: alter system set log_file_name_convert = ''/DB19B1/'', ''/DB19B1SB/'' comment= '''' scope=spfile
sql statement: alter system set standby_file_management = ''AUTO'' comment= '''' scope=spfile
sql statement: alter system set log_archive_config = ''dg_config=(DB19B1,DB19B1SB)'' comment= '''' scope=spfile
sql statement: alter system set db_recovery_file_dest = ''/u03/oradata/DB19B1SB/flash_recovery_area/'' comment= '''' scope=spfile
Oracle instance shut down
connected to auxiliary database (not started)
Oracle instance started
Total System Global Area 1761605264 bytes
Fixed Size 9135760 bytes
Variable Size 419430400 bytes
Database Buffers 1325400064 bytes
Redo Buffers 7639040 bytes
allocated channel: a1
channel a1: SID=331 device type=DISK
contents of Memory Script:
{
backup as copy current controlfile for standby auxiliary format '/u03/oradata/DB19B1SB/control01.ctl';
restore clone primary controlfile to '/u03/oradata/DB19B1SB/flash_recovery_area/control02.ctl' from
'/u03/oradata/DB19B1SB/control01.ctl';
}
executing Memory Script
Starting backup at 30-JUL-20
channel p1: starting datafile copy
copying standby control file
output file name=/u02/app/oracle/product/193000/dbs/snapcf_DB19B1.f tag=TAG20200730T093628
channel p1: datafile copy complete, elapsed time: 00:00:03
Finished backup at 30-JUL-20
Starting restore at 30-JUL-20
channel a1: copied control file copy
Finished restore at 30-JUL-20
contents of Memory Script:
{
sql clone 'alter database mount standby database';
}
executing Memory Script
sql statement: alter database mount standby database
contents of Memory Script:
{
set newname for tempfile 1 to
"/u03/oradata/DB19B1SB/temp01.dbf";
set newname for tempfile 2 to
"/u03/oradata/DB19B1SB/pdbseed/temp01.dbf";
set newname for tempfile 3 to
"/u03/oradata/DB19B1SB/OPTIMAL/temp01.dbf";
switch clone tempfile all;
set newname for datafile 1 to
"/u03/oradata/DB19B1SB/system01.dbf";
set newname for datafile 2 to
"/u03/oradata/DB19B1SB/pdbseed/system01.dbf";
set newname for datafile 3 to
"/u03/oradata/DB19B1SB/sysaux01.dbf";
set newname for datafile 4 to
"/u03/oradata/DB19B1SB/pdbseed/sysaux01.dbf";
set newname for datafile 5 to
"/u03/oradata/DB19B1SB/undotbs01.dbf";
set newname for datafile 6 to
"/u03/oradata/DB19B1SB/pdbseed/undotbs01.dbf";
set newname for datafile 7 to
"/u03/oradata/DB19B1SB/users01.dbf";
set newname for datafile 8 to
"/u03/oradata/DB19B1SB/OPTIMAL/system01.dbf";
set newname for datafile 9 to
"/u03/oradata/DB19B1SB/OPTIMAL/sysaux01.dbf";
set newname for datafile 10 to
"/u03/oradata/DB19B1SB/OPTIMAL/undotbs01.dbf";
set newname for datafile 11 to
"/u03/oradata/DB19B1SB/OPTIMAL/users01.dbf";
backup as copy reuse
datafile 1 auxiliary format
"/u03/oradata/DB19B1SB/system01.dbf" datafile
2 auxiliary format
"/u03/oradata/DB19B1SB/pdbseed/system01.dbf" datafile
3 auxiliary format
"/u03/oradata/DB19B1SB/sysaux01.dbf" datafile
4 auxiliary format
"/u03/oradata/DB19B1SB/pdbseed/sysaux01.dbf" datafile
5 auxiliary format
"/u03/oradata/DB19B1SB/undotbs01.dbf" datafile
6 auxiliary format
"/u03/oradata/DB19B1SB/pdbseed/undotbs01.dbf" datafile
7 auxiliary format
"/u03/oradata/DB19B1SB/users01.dbf" datafile
8 auxiliary format
"/u03/oradata/DB19B1SB/OPTIMAL/system01.dbf" datafile
9 auxiliary format
"/u03/oradata/DB19B1SB/OPTIMAL/sysaux01.dbf" datafile
10 auxiliary format
"/u03/oradata/DB19B1SB/OPTIMAL/undotbs01.dbf" datafile
11 auxiliary format
"/u03/oradata/DB19B1SB/OPTIMAL/users01.dbf" ;
sql 'alter system archive log current';
}
executing Memory Script
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
renamed tempfile 1 to /u03/oradata/DB19B1SB/temp01.dbf in control file
renamed tempfile 2 to /u03/oradata/DB19B1SB/pdbseed/temp01.dbf in control file
renamed tempfile 3 to /u03/oradata/DB19B1SB/OPTIMAL/temp01.dbf in control file
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
Starting backup at 30-JUL-20
channel p1: starting datafile copy
input datafile file number=00001 name=/u03/oradata/DB19B1/system01.dbf
channel p2: starting datafile copy
input datafile file number=00003 name=/u03/oradata/DB19B1/sysaux01.dbf
output file name=/u03/oradata/DB19B1SB/system01.dbf tag=TAG20200730T093644
channel p1: datafile copy complete, elapsed time: 00:00:35
channel p1: starting datafile copy
input datafile file number=00008 name=/u03/oradata/DB19B1/OPTIMAL/system01.dbf
output file name=/u03/oradata/DB19B1SB/sysaux01.dbf tag=TAG20200730T093644
channel p2: datafile copy complete, elapsed time: 00:00:36
channel p2: starting datafile copy
input datafile file number=00002 name=/u03/oradata/DB19B1/pdbseed/system01.dbf
output file name=/u03/oradata/DB19B1SB/OPTIMAL/system01.dbf tag=TAG20200730T093644
channel p1: datafile copy complete, elapsed time: 00:00:15
channel p1: starting datafile copy
input datafile file number=00005 name=/u03/oradata/DB19B1/undotbs01.dbf
output file name=/u03/oradata/DB19B1SB/pdbseed/system01.dbf tag=TAG20200730T093644
channel p2: datafile copy complete, elapsed time: 00:00:23
channel p2: starting datafile copy
input datafile file number=00004 name=/u03/oradata/DB19B1/pdbseed/sysaux01.dbf
output file name=/u03/oradata/DB19B1SB/undotbs01.dbf tag=TAG20200730T093644
channel p1: datafile copy complete, elapsed time: 00:00:23
channel p1: starting datafile copy
input datafile file number=00009 name=/u03/oradata/DB19B1/OPTIMAL/sysaux01.dbf
output file name=/u03/oradata/DB19B1SB/pdbseed/sysaux01.dbf tag=TAG20200730T093644
channel p2: datafile copy complete, elapsed time: 00:00:15
channel p2: starting datafile copy
input datafile file number=00006 name=/u03/oradata/DB19B1/pdbseed/undotbs01.dbf
output file name=/u03/oradata/DB19B1SB/OPTIMAL/sysaux01.dbf tag=TAG20200730T093644
channel p1: datafile copy complete, elapsed time: 00:00:16
channel p1: starting datafile copy
input datafile file number=00010 name=/u03/oradata/DB19B1/OPTIMAL/undotbs01.dbf
output file name=/u03/oradata/DB19B1SB/pdbseed/undotbs01.dbf tag=TAG20200730T093644
channel p2: datafile copy complete, elapsed time: 00:00:16
channel p2: starting datafile copy
input datafile file number=00007 name=/u03/oradata/DB19B1/users01.dbf
output file name=/u03/oradata/DB19B1SB/users01.dbf tag=TAG20200730T093644
channel p2: datafile copy complete, elapsed time: 00:00:03
channel p2: starting datafile copy
input datafile file number=00011 name=/u03/oradata/DB19B1/OPTIMAL/users01.dbf
output file name=/u03/oradata/DB19B1SB/OPTIMAL/users01.dbf tag=TAG20200730T093644
channel p2: datafile copy complete, elapsed time: 00:00:03
output file name=/u03/oradata/DB19B1SB/OPTIMAL/undotbs01.dbf tag=TAG20200730T093644
channel p1: datafile copy complete, elapsed time: 00:00:11
Finished backup at 30-JUL-20
sql statement: alter system archive log current
contents of Memory Script:
{
switch clone datafile all;
}
executing Memory Script
datafile 1 switched to datafile copy
input datafile copy RECID=1 STAMP=1047116316 file name=/u03/oradata/DB19B1SB/system01.dbf
datafile 2 switched to datafile copy
input datafile copy RECID=2 STAMP=1047116316 file name=/u03/oradata/DB19B1SB/pdbseed/system01.dbf
datafile 3 switched to datafile copy
input datafile copy RECID=3 STAMP=1047116316 file name=/u03/oradata/DB19B1SB/sysaux01.dbf
datafile 4 switched to datafile copy
input datafile copy RECID=4 STAMP=1047116316 file name=/u03/oradata/DB19B1SB/pdbseed/sysaux01.dbf
datafile 5 switched to datafile copy
input datafile copy RECID=5 STAMP=1047116316 file name=/u03/oradata/DB19B1SB/undotbs01.dbf
datafile 6 switched to datafile copy
input datafile copy RECID=6 STAMP=1047116316 file name=/u03/oradata/DB19B1SB/pdbseed/undotbs01.dbf
datafile 7 switched to datafile copy
input datafile copy RECID=7 STAMP=1047116316 file name=/u03/oradata/DB19B1SB/users01.dbf
datafile 8 switched to datafile copy
input datafile copy RECID=8 STAMP=1047116316 file name=/u03/oradata/DB19B1SB/OPTIMAL/system01.dbf
datafile 9 switched to datafile copy
input datafile copy RECID=9 STAMP=1047116316 file name=/u03/oradata/DB19B1SB/OPTIMAL/sysaux01.dbf
datafile 10 switched to datafile copy
input datafile copy RECID=10 STAMP=1047116316 file name=/u03/oradata/DB19B1SB/OPTIMAL/undotbs01.dbf
datafile 11 switched to datafile copy
input datafile copy RECID=11 STAMP=1047116316 file name=/u03/oradata/DB19B1SB/OPTIMAL/users01.dbf
Finished Duplicate Db at 30-JUL-20
released channel: p1
released channel: p2
released channel: a1
RMAN>

Now we can enable Primary to ship logs to standby and enable standby MRP process.

DB19B1SB:snode3:oracle$ sqlplus as sysdba
SQL*Plus: Release 19.0.0.0.0 - Production on Thu Jul 30 09:55:04 2020
Version 19.6.1.0.0
Copyright (c) 1982, 2019, Oracle. All rights reserved.
Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.6.1.0.0
SQL> alter database recover managed standby database disconnect from session;
Database altered.
SQL>

From standby alert log, you can see it is recovering and waiting fro next Log seq.

2020-07-30T09:56:15.986173-07:00
PR00 (PID:35581): FAL: Error fetching gap sequence, no FAL server specified
2020-07-30T09:56:26.486196-07:00
PR00 (PID:35581): FAL: Error fetching gap sequence, no FAL server specified
:::
2020-07-30T09:58:09.336067-07:00
rfs (PID:35533): Archived Log entry 19 added for B-1039684766.T-1.S-28 ID 0xfcecc25e LAD:2
2020-07-30T09:58:10.317755-07:00
PR00 (PID:35581): Media Recovery Log /u02/app/oracle/product/193000/dbs/db_recovery_file_dest1_28_1039684766.dbf
2020-07-30T09:58:11.347752-07:00
PR00 (PID:35581): Media Recovery Log /u02/app/oracle/product/193000/dbs/db_recovery_file_dest1_29_1039684766.dbf
2020-07-30T09:58:11.517740-07:00
PR00 (PID:35581): Media Recovery Log /u02/app/oracle/product/193000/dbs/db_recovery_file_dest1_30_1039684766.dbf
2020-07-30T09:58:12.501884-07:00
PR00 (PID:35581): Media Recovery Log /u02/app/oracle/product/193000/dbs/db_recovery_file_dest1_31_1039684766.dbf
2020-07-30T09:58:12.733028-07:00
PR00 (PID:35581): Media Recovery Log /u02/app/oracle/product/193000/dbs/db_recovery_file_dest1_32_1039684766.dbf
PR00 (PID:35581): Media Recovery Waiting for T-1.S-33 (in transit)