Creation of a standby database using RMAN DUPLICATE

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

For an RMAN DUPLCATE action we need a running auxiliary instance. For this we can create a dummy instance parameter file (in the example: /tmp/init.ora) with the following contents:

*.db_domain='mydomain.com'
*.db_name='thehague'
*.db_unique_name='berlin'

Then we'll start the auxiliary 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> STARTUP NOMOUNT pfile='/tmp/init.ora';
SQL> EXIT

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

Create the standby database

Creating the standby database using RMAN DUPLICATE involves less steps than a manual creation of a standby database. We simply use RMAN's ability to duplicate the datafiles, create the SPFILE and such. The target database on which to operate is the primary database (thehague in our case) and the auxiliary database is the database to which the copy the target database to (berlin in the given example):

[oracle@standby ~]$ rman target sys/oracle@thehague_dgmgrl auxiliary sys/oracle@berlin_dgmgrl
RMAN> RUN {
2>    DUPLICATE TARGET DATABASE
3>    FOR STANDBY
4>    FROM ACTIVE DATABASE
5>    SPFILE
6>       PARAMETER_VALUE_CONVERT 'thehague', 'berlin', 'THEHAGUE', 'BERLIN'
7>       SET db_name='thehague'
8>       SET db_unique_name='berlin'
9>       SET db_create_file_dest='/u01/oradata/berlin/'
10>       SET db_create_online_log_dest_1='/u01/oradata/berlin/'
11>       SET db_create_online_log_dest_2='/u01/fra/berlin/'
12>       SET db_file_name_convert='thehague','berlin','THEHAGUE','BERLIN'
13>       SET log_file_name_convert='thehague','berlin','THEHAGUE','BERLIN'
14>       SET standby_file_management='auto';
15> }
Starting Duplicate Db at 11-DEC-23
using target database control file instead of recovery catalog
allocated channel: ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: SID=37 device type=DISK

contents of Memory Script:
{
   backup as copy reuse
   passwordfile auxiliary format  '/u01/app/oracle/product/19.20/dbhome_1/dbs/orapwberlin'   ;
   restore clone from service  'thehague_dgmgrl' spfile to
 '/u01/app/oracle/product/19.20/dbhome_1/dbs/spfileberlin.ora';
   sql clone "alter system set spfile= ''/u01/app/oracle/product/19.20/dbhome_1/dbs/spfileberlin.ora''";
}
executing Memory Script

Starting backup at 11-DEC-23
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=1639 device type=DISK
Finished backup at 11-DEC-23

Starting restore at 11-DEC-23
using channel ORA_AUX_DISK_1

channel ORA_AUX_DISK_1: starting datafile backup set restore
channel ORA_AUX_DISK_1: using network backup set from service thehague_dgmgrl
channel ORA_AUX_DISK_1: restoring SPFILE
output file name=/u01/app/oracle/product/19.20/dbhome_1/dbs/spfileberlin.ora
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:01
Finished restore at 11-DEC-23

sql statement: alter system set spfile= ''/u01/app/oracle/product/19.20/dbhome_1/dbs/spfileberlin.ora''

contents of Memory Script:
{
   sql clone "alter system set  audit_file_dest =
 ''/u01/app/oracle/admin/berlin/adump'' comment=
 '''' scope=spfile";
   sql clone "alter system set  control_files =
 ''/u01/oradata/berlin/BERLIN/controlfile/o1_mf_lq6r2lxd_.ctl'', ''/u01/fra/BERLIN/controlfile/o1_mf_lq6r2m6n_.ctl'' comment=
 '''' scope=spfile";
   sql clone "alter system set  dispatchers =
 ''(PROTOCOL=TCP) (SERVICE=berlinXDB)'' comment=
 '''' scope=spfile";
   sql clone "alter system set  db_name =
 ''thehague'' comment=
 '''' scope=spfile";
   sql clone "alter system set  db_unique_name =
 ''berlin'' comment=
 '''' scope=spfile";
   sql clone "alter system set  db_create_file_dest =
 ''/u01/oradata/berlin/'' comment=
 '''' scope=spfile";
   sql clone "alter system set  db_create_online_log_dest_1 =
 ''/u01/oradata/berlin/'' comment=
 '''' scope=spfile";
   sql clone "alter system set  db_create_online_log_dest_2 =
 ''/u01/fra/berlin/'' comment=
 '''' scope=spfile";
   sql clone "alter system set  db_file_name_convert =
 ''thehague'', ''berlin'', ''THEHAGUE'', ''BERLIN'' comment=
 '''' scope=spfile";
   sql clone "alter system set  log_file_name_convert =
 ''thehague'', ''berlin'', ''THEHAGUE'', ''BERLIN'' comment=
 '''' scope=spfile";
   sql clone "alter system set  standby_file_management =
 ''auto'' comment=
 '''' scope=spfile";
   shutdown clone immediate;
   startup clone nomount;
}
executing Memory Script

sql statement: alter system set  audit_file_dest =  ''/u01/app/oracle/admin/berlin/adump'' comment= '''' scope=spfile

sql statement: alter system set  control_files =  ''/u01/oradata/berlin/BERLIN/controlfile/o1_mf_lq6r2lxd_.ctl'',
''/u01/fra/BERLIN/controlfile/o1_mf_lq6r2m6n_.ctl'' comment= '''' scope=spfile

sql statement: alter system set  dispatchers =  ''(PROTOCOL=TCP) (SERVICE=berlinXDB)'' comment= '''' scope=spfile

sql statement: alter system set  db_name =  ''thehague'' comment= '''' scope=spfile

sql statement: alter system set  db_unique_name =  ''berlin'' comment= '''' scope=spfile

sql statement: alter system set  db_create_file_dest =  ''/u01/oradata/berlin/'' comment= '''' scope=spfile

sql statement: alter system set  db_create_online_log_dest_1 =  ''/u01/oradata/berlin/'' comment= '''' scope=spfile

sql statement: alter system set  db_create_online_log_dest_2 =  ''/u01/fra/berlin/'' comment= '''' scope=spfile

sql statement: alter system set  db_file_name_convert =  ''thehague'', ''berlin'', ''THEHAGUE'', ''BERLIN'' comment= '''' scope=spfile

sql statement: alter system set  log_file_name_convert =  ''thehague'', ''berlin'', ''THEHAGUE'', ''BERLIN'' comment= ''''
scope=spfile

sql statement: alter system set  standby_file_management =  ''auto'' comment= '''' scope=spfile

Oracle instance shut down

connected to auxiliary database (not started)
Oracle instance started

Total System Global Area    4294963240 bytes

Fixed Size                     9175080 bytes
Variable Size                805306368 bytes
Database Buffers            3472883712 bytes
Redo Buffers                   7598080 bytes

contents of Memory Script:
{
   sql clone "alter system set  control_files =
  ''/u01/oradata/berlin/BERLIN/controlfile/o1_mf_lq6r2lxd_.ctl'', ''/u01/fra/BERLIN/controlfile/o1_mf_lq6r2m6n_.ctl'' comment=
''Set by RMAN'' scope=spfile";
   restore clone from service  'thehague_dgmgrl' standby controlfile;
}
executing Memory Script

sql statement: alter system set  control_files =   ''/u01/oradata/berlin/BERLIN/controlfile/o1_mf_lq6r2lxd_.ctl'',
''/u01/fra/BERLIN/controlfile/o1_mf_lq6r2m6n_.ctl'' comment= ''Set by RMAN'' scope=spfile

Starting restore at 11-DEC-23
allocated channel: ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: SID=1595 device type=DISK

channel ORA_AUX_DISK_1: starting datafile backup set restore
channel ORA_AUX_DISK_1: using network backup set from service thehague_dgmgrl
channel ORA_AUX_DISK_1: restoring control file
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:02
output file name=/u01/oradata/berlin/BERLIN/controlfile/o1_mf_lq6r2lxd_.ctl
output file name=/u01/fra/BERLIN/controlfile/o1_mf_lq6r2m6n_.ctl
Finished restore at 11-DEC-23

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
 "/u01/oradata/berlin/THEHAGUE/datafile/o1_mf_temp_lq6r38qq_.tmp";
   set newname for tempfile  2 to
 "/u01/oradata/berlin/THEHAGUE/datafile/temp012023-12-08_19-44-23-203-PM.dbf";
   set newname for tempfile  3 to
 "/u01/oradata/berlin/THEHAGUE/0C052C8F8C754FD7E0630B80A8C0D630/datafile/o1_mf_temp_lq6sv86r_.dbf";
   switch clone tempfile all;
   set newname for datafile  1 to
 "/u01/oradata/berlin/THEHAGUE/datafile/o1_mf_system_lq6qzxfp_.dbf";
   set newname for datafile  3 to
 "/u01/oradata/berlin/THEHAGUE/datafile/o1_mf_sysaux_lq6r0plj_.dbf";
   set newname for datafile  4 to
 "/u01/oradata/berlin/THEHAGUE/datafile/o1_mf_undotbs1_lq6r15px_.dbf";
   set newname for datafile  5 to
 "/u01/oradata/berlin/THEHAGUE/datafile/o1_mf_system_lq6rzbhy_.dbf";
   set newname for datafile  6 to
 "/u01/oradata/berlin/THEHAGUE/datafile/o1_mf_sysaux_lq6rzbj8_.dbf";
   set newname for datafile  7 to
 "/u01/oradata/berlin/THEHAGUE/datafile/o1_mf_users_lq6r16sw_.dbf";
   set newname for datafile  8 to
 "/u01/oradata/berlin/THEHAGUE/datafile/o1_mf_undotbs1_lq6rzbjm_.dbf";
   set newname for datafile  9 to
 "/u01/oradata/berlin/THEHAGUE/0C052C8F8C754FD7E0630B80A8C0D630/datafile/o1_mf_system_lq6sv862_.dbf";
   set newname for datafile  10 to
 "/u01/oradata/berlin/THEHAGUE/0C052C8F8C754FD7E0630B80A8C0D630/datafile/o1_mf_sysaux_lq6sv86r_.dbf";
   set newname for datafile  11 to
 "/u01/oradata/berlin/THEHAGUE/0C052C8F8C754FD7E0630B80A8C0D630/datafile/o1_mf_undotbs1_lq6sv86r_.dbf";
   set newname for datafile  12 to
 "/u01/oradata/berlin/THEHAGUE/0C052C8F8C754FD7E0630B80A8C0D630/datafile/o1_mf_users_lq6svvwv_.dbf";
   restore
   from  nonsparse   from service
 'thehague_dgmgrl'   clone database
   ;
   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 /u01/oradata/berlin/THEHAGUE/datafile/o1_mf_temp_lq6r38qq_.tmp in control file
renamed tempfile 2 to /u01/oradata/berlin/THEHAGUE/datafile/temp012023-12-08_19-44-23-203-PM.dbf in control file
renamed tempfile 3 to /u01/oradata/berlin/THEHAGUE/0C052C8F8C754FD7E0630B80A8C0D630/datafile/o1_mf_temp_lq6sv86r_.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 restore at 11-DEC-23
using channel ORA_AUX_DISK_1

channel ORA_AUX_DISK_1: starting datafile backup set restore
channel ORA_AUX_DISK_1: using network backup set from service thehague_dgmgrl
channel ORA_AUX_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_AUX_DISK_1: restoring datafile 00001 to /u01/oradata/berlin/THEHAGUE/datafile/o1_mf_system_lq6qzxfp_.dbf
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:07
channel ORA_AUX_DISK_1: starting datafile backup set restore
channel ORA_AUX_DISK_1: using network backup set from service thehague_dgmgrl
channel ORA_AUX_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_AUX_DISK_1: restoring datafile 00003 to /u01/oradata/berlin/THEHAGUE/datafile/o1_mf_sysaux_lq6r0plj_.dbf
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:15
channel ORA_AUX_DISK_1: starting datafile backup set restore
channel ORA_AUX_DISK_1: using network backup set from service thehague_dgmgrl
channel ORA_AUX_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_AUX_DISK_1: restoring datafile 00004 to /u01/oradata/berlin/THEHAGUE/datafile/o1_mf_undotbs1_lq6r15px_.dbf
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:03
channel ORA_AUX_DISK_1: starting datafile backup set restore
channel ORA_AUX_DISK_1: using network backup set from service thehague_dgmgrl
channel ORA_AUX_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_AUX_DISK_1: restoring datafile 00005 to /u01/oradata/berlin/THEHAGUE/datafile/o1_mf_system_lq6rzbhy_.dbf
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:04
channel ORA_AUX_DISK_1: starting datafile backup set restore
channel ORA_AUX_DISK_1: using network backup set from service thehague_dgmgrl
channel ORA_AUX_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_AUX_DISK_1: restoring datafile 00006 to /u01/oradata/berlin/THEHAGUE/datafile/o1_mf_sysaux_lq6rzbj8_.dbf
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:03
channel ORA_AUX_DISK_1: starting datafile backup set restore
channel ORA_AUX_DISK_1: using network backup set from service thehague_dgmgrl
channel ORA_AUX_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_AUX_DISK_1: restoring datafile 00007 to /u01/oradata/berlin/THEHAGUE/datafile/o1_mf_users_lq6r16sw_.dbf
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:01
channel ORA_AUX_DISK_1: starting datafile backup set restore
channel ORA_AUX_DISK_1: using network backup set from service thehague_dgmgrl
channel ORA_AUX_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_AUX_DISK_1: restoring datafile 00008 to /u01/oradata/berlin/THEHAGUE/datafile/o1_mf_undotbs1_lq6rzbjm_.dbf
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:03
channel ORA_AUX_DISK_1: starting datafile backup set restore
channel ORA_AUX_DISK_1: using network backup set from service thehague_dgmgrl
channel ORA_AUX_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_AUX_DISK_1: restoring datafile 00009 to
/u01/oradata/berlin/THEHAGUE/0C052C8F8C754FD7E0630B80A8C0D630/datafile/o1_mf_system_lq6sv862_.dbf
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:07
channel ORA_AUX_DISK_1: starting datafile backup set restore
channel ORA_AUX_DISK_1: using network backup set from service thehague_dgmgrl
channel ORA_AUX_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_AUX_DISK_1: restoring datafile 00010 to
/u01/oradata/berlin/THEHAGUE/0C052C8F8C754FD7E0630B80A8C0D630/datafile/o1_mf_sysaux_lq6sv86r_.dbf
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:03
channel ORA_AUX_DISK_1: starting datafile backup set restore
channel ORA_AUX_DISK_1: using network backup set from service thehague_dgmgrl
channel ORA_AUX_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_AUX_DISK_1: restoring datafile 00011 to
/u01/oradata/berlin/THEHAGUE/0C052C8F8C754FD7E0630B80A8C0D630/datafile/o1_mf_undotbs1_lq6sv86r_.dbf
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:03
channel ORA_AUX_DISK_1: starting datafile backup set restore
channel ORA_AUX_DISK_1: using network backup set from service thehague_dgmgrl
channel ORA_AUX_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_AUX_DISK_1: restoring datafile 00012 to
/u01/oradata/berlin/THEHAGUE/0C052C8F8C754FD7E0630B80A8C0D630/datafile/o1_mf_users_lq6svvwv_.dbf
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:01
Finished restore at 11-DEC-23

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=4 STAMP=1155315390 file name=/u01/oradata/berlin/BERLIN/datafile/o1_mf_system_lqgd7vpq_.dbf
datafile 3 switched to datafile copy
input datafile copy RECID=5 STAMP=1155315390 file name=/u01/oradata/berlin/BERLIN/datafile/o1_mf_sysaux_lqgd82v5_.dbf
datafile 4 switched to datafile copy
input datafile copy RECID=6 STAMP=1155315390 file name=/u01/oradata/berlin/BERLIN/datafile/o1_mf_undotbs1_lqgd8l1p_.dbf
datafile 5 switched to datafile copy
input datafile copy RECID=7 STAMP=1155315390 file
name=/u01/oradata/berlin/BERLIN/0C04F90FAB7B4936E0630B80A8C00EB5/datafile/o1_mf_system_lqgd8o3f_.dbf
datafile 6 switched to datafile copy
input datafile copy RECID=8 STAMP=1155315390 file
name=/u01/oradata/berlin/BERLIN/0C04F90FAB7B4936E0630B80A8C00EB5/datafile/o1_mf_sysaux_lqgd8r5z_.dbf
datafile 7 switched to datafile copy
input datafile copy RECID=9 STAMP=1155315390 file name=/u01/oradata/berlin/BERLIN/datafile/o1_mf_users_lqgd8vb2_.dbf
datafile 8 switched to datafile copy
input datafile copy RECID=10 STAMP=1155315390 file
name=/u01/oradata/berlin/BERLIN/0C04F90FAB7B4936E0630B80A8C00EB5/datafile/o1_mf_undotbs1_lqgd8wf0_.dbf
datafile 9 switched to datafile copy
input datafile copy RECID=11 STAMP=1155315390 file
name=/u01/oradata/berlin/BERLIN/0C052C8F8C754FD7E0630B80A8C0D630/datafile/o1_mf_system_lqgd8zj0_.dbf
datafile 10 switched to datafile copy
input datafile copy RECID=12 STAMP=1155315390 file
name=/u01/oradata/berlin/BERLIN/0C052C8F8C754FD7E0630B80A8C0D630/datafile/o1_mf_sysaux_lqgd96ml_.dbf
datafile 11 switched to datafile copy
input datafile copy RECID=13 STAMP=1155315390 file
name=/u01/oradata/berlin/BERLIN/0C052C8F8C754FD7E0630B80A8C0D630/datafile/o1_mf_undotbs1_lqgd99q2_.dbf
datafile 12 switched to datafile copy
input datafile copy RECID=14 STAMP=1155315390 file
name=/u01/oradata/berlin/BERLIN/0C052C8F8C754FD7E0630B80A8C0D630/datafile/o1_mf_users_lqgd9dsd_.dbf
Finished Duplicate Db at 11-DEC-23

RMAN> EXIT

Creating the Data Guard configuration

Make sure the Data Guard Broker process is running:

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

Perform this action on both primary and standby sides if necessary. Now start up the Data Guard Manager on the primary side:

[oracle@primary ~]$ dgmgrl
DGMGRL for Linux: Release 19.0.0.0.0 - Production on Mon Dec 11 16:57:57 2023
Version 19.20.0.0.0

Copyright (c) 1982, 2019, Oracle and/or its affiliates.  All rights reserved.

Welcome to DGMGRL, type "help" for information.
DGMGRL> CONNECT /
Connected to "thehague"
Connected as SYSDG.
DGMGRL> SHOW CONFIGURATION

Configuration - dg_demo

  Protection Mode: MaxPerformance
  Members:
  thehague - Primary database

Fast-Start Failover:  Disabled

Configuration Status:
SUCCESS   (status updated 57 seconds ago)

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> ENABLE DATABASE berlin
Enabled.
DGMGRL>SHOW CONFIGURATION
Configuration - dg_demo

  Protection Mode: MaxPerformance
  Members:
  thehague - Primary database
    berlin   - Physical standby database
      Error: ORA-16810: multiple errors or warnings detected for the member

Fast-Start Failover:  Disabled

Configuration Status:
ERROR   (status updated 39 seconds ago)

DGMGRL> SHOW DATABASE berlin

Database - berlin

  Role:               PHYSICAL STANDBY
  Intended State:     APPLY-ON
  Transport Lag:      0 seconds (computed 14 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

Database Status:
WARNING

DGMGRL> /

Database - berlin

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

Database Status:
SUCCESS

DGMGRL> EXIT

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