Manual creation of a physical standby database using an RMAN backup

As indicated in a previous post, we need the following information for setting up a standby database:

• A running standy instance.
• The password file of the primary database.
• A standby version of the primary database's control file.
• The data files of the primary database.

Next to this information setting up a standby database also required configuration taking place, such as static listener entries, instance parameters on both primary and standby database instances, etc.. Let's review all steps one at a time during the creation of our standby database named "berlin".

Creating the standby instance

The first requirement can then be met by e.g. creating an instance parameter file ("init.ora" or pfile) based on the server parameter file (spfile) of the primary database, alter the database unique name and augment any creation locations to match the standby database environnment to be set up. Create a file with the following contents and name it /tmp/initberlin.ora:

*.audit_file_dest='/u01/app/oracle/admin/berlin/adump'
*.audit_trail='none'
*.compatible='19.0.0'
*.control_files='/u01/oradata/berlin/control01.ctl','/u01/fra/berlin/control02.ctl'
*.db_block_size=8192
*.db_create_file_dest='/u01/oradata/berlin/'
*.db_create_online_log_dest_1='/u01/oradata/berlin/'
*.db_create_online_log_dest_2='/u01/fra/berlin/'
*.db_domain='mydomain.com'
*.db_name='thehague'
*.db_unique_name='berlin'
*.db_recovery_file_dest='/u01/fra/berlin'
*.db_recovery_file_dest_size=8g
*.diagnostic_dest='/u01/app/oracle'
*.enable_pluggable_database=true
*.open_cursors=300
*.pga_aggregate_target=512m
*.processes=200
*.remote_login_passwordfile='EXCLUSIVE'
*.sga_target=2g
*.undo_tablespace='UNDOTBS1'

Alternatively, create a parameter file from the primary database's spfile and make the necesary adjustments in that pfile. After having created the instance parameter file on the standby side, create any necessary directories on the standby machine and start the instance:

$ export ORACLE_HOME=/u01/app/oracle/product/19.20/dbhome_1
$ PATH=${ORACLE_HOME}/bin:${PATH}
$ export ORACLE_SID=berlin
$ mkdir -p /u01/app/oracle/admin/berlin/adump /u01/oradata/berlin /u01/fra/berlin
$ sqlplus "/ as sysdba"
SQL> CREATE spfile FROM pfile='/tmp/initberlin.ora';
SQL> STARTUP NOMOUNT;

Note that we have created a skeleton server parameter file. Some adjustments for a standby environment still have to be made:

SQL> ALTER SYSTEM SET db_file_name_convert='thehague','berlin','THEHAGUE','BERLIN' sid='*' scope=spfile;
SQL> ALTER SYSTEM SET log_file_name_convert='thehague','berlin','THEHAGUE','BERLIN' sid='*' scope=spfile;
SQL> ALTER SYSTEM SET standby_file_management=auto sid='*' scope=spfile;
SQL> SHUTDOWN IMMEDIATE;
SQL> STARTUP NOMOUNT;
SQL> EXIT

Remember that we performed similar actions already on the primary database side.

Configuring the password file

If you remember, we already copied over the password file in the previous blog post. In case these steps weren't take yet, when a password file doesn't exist already for the primary database, create one at the primary site using:

$ orapwd file=${ORACLE_HOME}/dbs/orapwthehague

The password file (name is orapw${ORACLE_SID} and is residing in ${ORACLE_HOME}/dbs) should then be copied over to the standby.mydomain.com machine for use with the standby database:

$ scp ${ORACLE_HOME}/dbs/orapwthehague standby.mydomain.com:/u01/app/oracle/product/19.20/dbhome_1/dbs/orapwberlin

Prepare the creation of a copy of the database

To create a copy of your database, you either create a database from a running database (will attempt to create another blog for that), or - as we'll use here - use a full database backup you restore on the standby side. To create a backup, go to the primary side and execute:

$ rman target /
RMAN> CONFIGURE CONTROLFILE AUTOBACKUP on;
RMAN> BACKUP DATABASE PLUS ARCHIVELOG;
RMAN> EXIT

In my case, this left me with a backup at /u01/fra/THEHAGUE:

[oracle@primary ~]$ find /u01/fra/THEHAGUE -type f -print | egrep -vi "archivelog|onlinelog|controlfile"
/u01/fra/THEHAGUE/backupset/2023_12_08/o1_mf_annnn_TAG20231208T201102_lq6tl6ox_.bkp
/u01/fra/THEHAGUE/backupset/2023_12_08/o1_mf_nnndf_TAG20231208T201103_lq6tl81c_.bkp
/u01/fra/THEHAGUE/backupset/2023_12_08/o1_mf_annnn_TAG20231208T201209_lq6tnb0r_.bkp
/u01/fra/THEHAGUE/0C052C8F8C754FD7E0630B80A8C0D630/backupset/2023_12_08/o1_mf_nnndf_TAG20231208T201103_lq6tm13h_.bkp
/u01/fra/THEHAGUE/0C04F90FAB7B4936E0630B80A8C00EB5/backupset/2023_12_08/o1_mf_nnndf_TAG20231208T201103_lq6tmj9o_.bkp
/u01/fra/THEHAGUE/autobackup/2023_12_08/o1_mf_s_1155067931_lq6tncqg_.bkp

These backup sets need to get transferred to the standby site for further processing:

$ scp -r /u01/fra/THEHAGUE/backupset/2023_12_08 standby.mydomain.com:/u01/fra
$ scp -r /u01/fra/THEHAGUE/0BEF010CD3EC123BE0630B80A8C0AF3E/backupset/2023_12_08 standby.mydomain.com:/u01/fra
$ scp -r /u01/fra/THEHAGUE/0BEECE71C9E40CCEE0630B80A8C0CF98/backupset/2023_12_08 standby.mydomain.com:/u01/fra
$ scp -r /u01/fra/THEHAGUE/autobackup/2023_12_08 standby.mydomain.com:/u01/fra

Note that the autobackup backup set is the one containing the control file and spfile backups.

Configuring the standby control file

A standby database requires a standby control file. This is a special kind of control file by which the database will identify its standby role. You can either restore it from a backup, or use SQL*Plus on the primary database side to create such a control file:

$ sqlplus "/ as sysdba"
SQL> ALTER DATABASE CREATE STANDBY CONTROLFILE AS '/tmp/sbcf_berlin.cf';
SQL> EXIT
$ scp /tmp/sbcf_berlin.cf standby.mydomain.com:/tmp

followed by a manual copy of this file to the control file locations.

In our case we'll simply restore it from the autobackup of the control file of the primary database. Log on to the standby database instance (now running already in NOMOUNT mode) from RMAN and restore the control file:

$ rman target /
RMAN> RESTORE STANDBY CONTROLFILE FROM '/u01/fra/2023_10_22/o1_mf_s_1150878051_lm9ht463_.bkp';
RMAN> ALTER DATABASE MOUNT;

Initiating a copy of the database

Note that for ease of use I've copied over all data to the /u01/fra directory on the standby.mydomain.com VM. Of course with any large database this is not a good idea (and won't likely fit). Normally data is either transferred to storage with plenty of disk space available, or by means of shared storage where both primary and standby hosts can read.

To be able to use the copied backup we need to make sure RMAN can find the backup pieces. You do this by removing the current backup information from the standby control file and catalog the correct backup pieces on the standby side:

RMAN> CHANGE BACKUP UNCATALOG NOPROMPT;
RMAN> CATALOG START WITH '/u01/fra/2023_12_08/' NOPROMPT;

No it's time to restore the database:

RMAN> RUN {
  SET NEWNAME FOR database TO new;
  RESTORE DATABASE;
  SWITCH DATAFILE ALL;
  RECOVER DATABASE;
}

Now we have a copy of the database running. Prepare for a Data Guard setup by starting the Data Guard Broker process:

$ sqlplus "/ as sysdba"
SQL> ALTER SYSTEM SET dg_broker_start=true sid='*';
SQL> EXIT

Note: in a real environment you first set the DG_BROKER_CONFIG_FILE1 and DG_BROKER_CONFIG_FILE2 instance parameters to appropriate locations. As this is a play environment we simply let the Broker configuration files reside in the $ORACLE_HOME/dbs directory.

Creating the standby redo log files

For a standby database to operate properly we need to add standby redo log files on both primary and standby database sides. First, navigate to the primary side and check how large the online redo log files are:

sqlplus "/ as sysdba"
SQL> SELECT group#, bytes FROM v$log ORDER BY 1;

    GROUP#      BYTES
---------- ----------
         1  209715200
         2  209715200
         3  209715200

We need to create 1 more standby log group than the total # of active online log groups for a thread. As we're single-instance we deal with only 1 thread. As the example shows, there are 3 online log groups, each with members of a size of 200MB. So we'll create 4 standby log groups of the same size:

SQL> ALTER DATABASE ADD STANDBY LOGFILE GROUP 101 SIZE 200M;

Database altered.

SQL> ALTER DATABASE ADD STANDBY LOGFILE GROUP 102 SIZE 200M;

Database altered.

SQL> ALTER DATABASE ADD STANDBY LOGFILE GROUP 103 SIZE 200M;

Database altered.

SQL> ALTER DATABASE ADD STANDBY LOGFILE GROUP 104 SIZE 200M;

Database altered.

SQL> EXIT

Similarly we need to go to the standby environment and execute the same commands. Should you receive the error

ORA-01156: recovery or flashback in progress may need access to files

then you need to first disable any managed recovery:

SQL> RECOVER MANAGED STANDBY DATABASE CANCEL;
Media recovery complete.

When standby redo log groups have been created on both primary and standby database sides, let's proceed to build up the Data Guard environment!

Creating the Data Guard configuration

Navigate back to the primary database side. First make sure the Data Guard Broker process is running here as well:

sqlplus "/ as sysdba"
SQL> ALTER SYSTEM SET dg_broker_start=true sid='*';
SQL> EXIT

Now start up the Data Guard Manager:

$ dgmgrl
DGMGRL> connect /
Connected to "thehague"
Connected as SYSDG.

Create a new configuration named dg_demo:

DGMGRL> CREATE CONFIGURATION dg_demo AS PRIMARY DATABASE IS thehague CONNECT IDENTIFIER IS thehague_dgt;
Configuration "dg_demo" created with primary database "thehague"
DGMGRL> EDIT DATABASE thehague SET PROPERTY staticconnectidentifier=thehague_dgmgrl;
Property "staticconnectidentifier" updated

Now it's time to add the standby database to this configuration:

DGMGRL> ADD DATABASE berlin AS CONNECT IDENTIFIER IS berlin_dgt;
Database "berlin" added
DGMGRL> EDIT DATABASE berlin SET PROPERTY staticconnectidentifier=berlin_dgmgrl;
Property "staticconnectidentifier" updated
DGMGRL> SHOW CONFIGURATION

Configuration - dg_demo

  Protection Mode: MaxPerformance
  Members:
  thehague - Primary database
    berlin   - Physical standby database

Fast-Start Failover:  Disabled

Configuration Status:
DISABLED

DGMGRL> ENABLE CONFIGURATION;
Enabled.
DGMGRL> SHOW CONFIGURATION

Configuration - dg_demo

  Protection Mode: MaxPerformance
  Members:
  thehague - Primary database
    berlin   - Physical standby database
      Warning: ORA-16854: apply lag could not be determined

Fast-Start Failover:  Disabled

Configuration Status:
WARNING   (status updated 6 seconds ago)

Should you receive any warnings related to the apply lag of the standby database, then check the standby status:

DGMGRL> SHOW DATABASE berlin

Database - berlin

  Role:               PHYSICAL STANDBY
  Intended State:     APPLY-ON
  Transport Lag:      2 minutes (computed 7 seconds ago)
  Apply Lag:          (unknown)
  Average Apply Rate: (unknown)
  Real Time Query:    OFF
  Instance(s):
    berlin

  Database Warning(s):
    ORA-16854: apply lag could not be determined
    ORA-16855: transport lag has exceeded specified threshold

Database Status:
WARNING

In this case, simply disable and re-enable the standby database to get the redo transport moving:

DGMGRL> DISABLE DATABASE berlin
Disabled.
DGMGRL> ENABLE DATABASE berlin
Enabled.

Now the Data Guard environment should be operational:

DGMGRL> SHOW DATABASE berlin

Database - berlin

  Role:               PHYSICAL STANDBY
  Intended State:     APPLY-ON
  Transport Lag:      0 seconds (computed 0 seconds ago)
  Apply Lag:          0 seconds (computed 0 seconds ago)
  Average Apply Rate: 53.00 KByte/s
  Real Time Query:    OFF
  Instance(s):
    berlin

Database Status:
SUCCESS

DGMGRL> SHOW CONFIGURATION

Configuration - dg_demo

  Protection Mode: MaxPerformance
  Members:
  thehague - Primary database
    berlin   - Physical standby database

Fast-Start Failover:  Disabled

Configuration Status:
SUCCESS   (status updated 31 seconds ago)

And, YESSSS!!! We succeeded in creating a standby database environment!