Step-by-Step Instructions for Creating a Physical Standby Database of a Container Database (CDB)

Before creating a physical standby database you must first ensure the primary database is properly configured.

Configuration Overview

Primary database: cdb1 (cdb1)
Standby database: cdb1 (cdb1_stb)

Physical Hardware: Must be exactly the same ( Hardware/OS/filesystem )
Oracle Home Location: Must be the same location ( can be different but not recommended )
Database open modes:-

Primary - Open Mode
Standby - Mount Mode

Preparing the primary database for physical standby database creation

On the primary database make sure you have performed the following tasks below:-
Note!! Perform these tasks only once. After you have completed these steps, the database is prepared to serve as the primary database for one or more standby databases.

Changes on Primary Database

Enable force Logging:

Place the primary database in FORCE LOGGING mode.

SQL> ALTER DATABASE FORCE LOGGING;
SQL> SELECT name, force_logging FROM v$database;

When you issue this statement, the primary database must at least be mounted (it can also be open).

Enable Archiving

SQL> archive log list;
If archiving is not enabled, issue the following SQL statements to put the primary database in ARCHIVELOG mode and enable automatic archiving:
SQL> SHUTDOWN IMMEDIATE;
SQL> STARTUP MOUNT;
SQL> ALTER DATABASE ARCHIVELOG;
SQL> ALTER DATABASE OPEN;

Configure redo transport authentication

Oracle Data Guard uses Oracle Net sessions to transport redo data and control messages between the members of an Oracle Data Guard configuration. The redo transport sessions are authenticated using either the Secure Socket Layer (SSL) protocol or a remote login password file.
We will use the remote login password file for authentication.
Copy the password file to every physical standby database in the configuration.
The password file is located in $ORACLE_HOME/dbs directory, the filename convention for the password file is orapw< ORACLE_SID >.
If you don't have a password file you can create one using the steps below;

Create a password file for the standby database
SQL> show parameter remote_login_passwordfile;
If this parameter is not set to exclusive follow the steps below:-

SQL> alter system set remote_login_passwordfile=exclusive scope=spfile;
Restart database
Finally, create the password file using the command below;
# orapwd file=$ORACLE_HOME/dbs/orapw< ORACLE_SID > password=< the sys password &gt force=y;

Configure the Primary Database to Receive Redo Data

Configure the primary database to receive redo if this is the first time a standby database is added to the configuration.
To create a standby redo log, use the sql command ALTER DATABASE ADD STANDBY LOGFILE...

Add the same number of standby log files as redo log files/groups
First, check the current log groups and add current + 1 standby log groups

SQL> select f.group#, f.member, l.bytes/1024/1024 size_mb from v$logfile f, v$log l where f.group#=l.group#

In our configuration, we have 3 redo log groups with one member each (size 50mb) so we will add 4 (3 + 1) standby log groups.

SQL> alter database add standby logfile group 11 ('/u02/oradata/cdb1/stb_redo11.log') size 50m;
SQL> alter database add standby logfile group 12 ('/u02/oradata/cdb1/stb_redo12.log') size 50m;
SQL> alter database add standby logfile group 13 ('/u02/oradata/cdb1/stb_redo13.log') size 50m;
SQL> alter database add standby logfile group 14 ('/u02/oradata/cdb1/stb_redo14.log') size 50m;

SQL> select group#, member from v$logfile;

Standby file management parameter

Set the standby file management parameter to auto.

SQL> alter system set standby_file_management = 'AUTO';

Increase the size of recovery destination

SQL> alter system set db_recovery_file_dest_size=20g;

Review all the parameter changes in primary

Verify all the parameters once again in production. Make sure all changes are set perfectly.

Create a Physical Standby Database

Create a virtual machine for standby server with Oracle Linux (OS) installed.
Install Oracle binaries. Do not create any database.
Setup networking between prod and standby server ( Make sure you can ping the service names of prod and standby server )

Note! Oracle networking does not depend on physical DB presence

For the steps above you can check out my previous post for the installation of Oracle binaries.

Configure Redo Transport - On Primary server

On the primary database, you define initialization parameters that control redo transport services while the database is in the primary role.

SQL> alter system set log_archive_dest_1 = 'location=use_db_recovery_file_dest valid_for=(all_logfiles, all_roles) db_unique_name=cdb1';

SQL> alter system set log_archive_dest_2 = 'service=cdb1_stb async valid_for=(online_logfile,primary_role) db_unique_name=cdb1_stb';

SQL> alter system set fal_server = 'cdb1_stb';

SQL> alter system set log_archive_config = 'dg_config=(cdb1,cdb1_stb)';

Configure Standby Server

Create a parameter file for the standby database

On primary create pfile from spfile; and copy it to the standby server.
Also copy the password file from cdb1 to cdb1_stb.

SQL> create pfile from spfile;
$ cd $ORACLE_HOME/dbs
$ scp initcdb1.ora orapwcdb1 oracle@arandy.stb:$ORACLE_HOME/dbs
On the standby server:
Make the following changes to the parameter file (pfile) you copied from the primary:-
$ cd $ORACLE_HOME/dbs
$ vi initcdb1.ora

In LOG_ARCHIVE_DEST_1 change db_unique_name to the standby SID (in this case cdb1_stb)
In LOG_ARCHIVE_DEST_2 change the service and db_unique_name to the primary SID (in this case cdb1)
Change FAL_SERVER to the primary SID (cdb1)
db_unique_name=cdb1_stb

Make sure you have created respective files location directories on standby (should be the same as primary for our configuration).

$ mkdir -p /u01/app/oracle/admin/cdb1/adump /u02/oradata/cdb1/ /u01/app/oracle/fast_recovery_area/cdb1/ /u01/app/oracle/fast_recovery_area

Review all the parameters and networking between primary and standby.

Create Standby Database

For this configuration, we will be using the rman duplicate command to create our standby database.

- On standby, create spfile

SQL> startup nomount pfile=$ORACLE_HOME/dbs/initcdb1.ora

SQL> create spfile from pfile;

SQL> shutdown

SQL> startup nomount

SQL> show parameter spfile

SQL> exit;

- Duplicate the primary server via RMAN

On primary:

Connect to rman with sys user. replace with your sys password.

$ rman target sys/< sys password >@cdb1

Connect to the auxiliary server (standby) with same sys user and password because we are using the same password file.

RMAN> connect auxiliary sys/< sys password >@cdb1_stb

Next duplicate database:

RMAN> duplicate target database for standby from active database nofilenamecheck;

CHECK THE DATABASE STATUS AND ROLES
Execute below command in both primary and standby server

SQL> select name, database_role, open_mode from v$database;

STARTING LOG APPLY SERVICE ON STANDBY

To start log apply service on standby

SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT FROM SESSION;

Verify log apply

SQL> select process, status, sequence# from v$managed_standby;

STOPPING LOG APPLY SERVICE ON STANDBY

SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL;

Caution: Any information or materials on this blog is provided for educational purposes only. It has been tested internally, however, we do not guarantee that it will work for you. Ensure that you run it in your test environment before using.

Thanks,
randyDBA { serving to discover }

randydba Blog { Database - Tips & Techniques }