Monday, December 11, 2023

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!
Posted by at No comments:

Saturday, December 9, 2023

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!
Posted by at No comments:
Subscribe to: Posts (Atom)