RMAN DUPLICATION METHOD TO CREATE STANDBY DATABASE IN ORACLE

Configure network files on both primary and standby database. Create a static listener both in primary and standby databases for consistent connectivity

Listener.ora

-bash-4.2$ cat /apps01/product/12.1.0/dbhome_1/network/admin/listener.ora
# listener.ora Network Configuration File: /apps01/product/12.1.0/dbhome_1/network/admin/listener.ora
# Generated by Oracle configuration tools.

SID_LIST_LISTENER =
  (SID_LIST =
    (SID_DESC =
      (GLOBAL_DBNAME = orcl19x1)
      (ORACLE_HOME = /data01/product/19.0.0)
      (SID_NAME = orcl19x1)
    )
    (SID_DESC =
      (GLOBAL_DBNAME = dbx01)
      (ORACLE_HOME = /apps01/product/12.1.0/dbhome_1)
      (SID_NAME = dbx01)
    )
  )
LISTENER =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.56.123)(PORT = 1521))
  )

ADR_BASE_LISTENER = /apps01/base

Tnsnames.ora

DBX00 =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.56.112)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = dbx00)
    )
  )
DBX01 =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.56.123)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = dbx01)(UR = A)
    )
  )

Verify the tnsping from both primary and standby database

Primary database:

[oracle@dc01x02 admin]$ tnsping DBX01

TNS Ping Utility for Linux: Version 12.1.0.2.0 - Production on 21-SEP-2021 05:14:22

Copyright (c) 1997, 2014, Oracle.  All rights reserved.

Used parameter files:
/apps01/12.1.0/grid/network/admin/sqlnet.ora


Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.56.123)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = DBX01)))
OK (10 msec)

Standby database:
-bash-4.2$ tnsping DBX00

TNS Ping Utility for Linux: Version 12.1.0.2.0 - Production on 21-SEP-2021 05:13:05

Copyright (c) 1997, 2014, Oracle.  All rights reserved.

Used parameter files:
/apps01/product/12.1.0/dbhome_1/network/admin/sqlnet.ora


Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.56.112)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = dbx00)))
OK (0 msec)

Set the password for sys account and scp the password file from primary to standby $ORACLE_HOME/dbs location

SQL> alter user sys identified by password;

[oracle@dc01x02 dbs]$ scp orapwdbx00_1 oracle@192.168.56.123:/apps01/product/12.1.0/dbhome_1/dbs
oracle@192.168.56.123's password:
orapwdbx00_1                                                                                         100%   18KB   4.3MB/s   00:00

Check space on standby database

-bash-4.2$ df -h
Filesystem      Size  Used Avail Use% Mounted on
devtmpfs        1.8G     0  1.8G   0% /dev
tmpfs           1.9G  835M 1012M  46% /dev/shm
tmpfs           1.9G  8.9M  1.8G   1% /run
tmpfs           1.9G     0  1.9G   0% /sys/fs/cgroup
/dev/sda2        20G   19G  735M  97% /
/dev/sda5        28G   26G  1.8G  94% /data01
/dev/sda1        30G   18G   13G  59% /apps01
tmpfs           370M   16K  370M   1% /run/user/42
tmpfs           370M     0  370M   0% /run/user/1001

Create a dummy pfile in standby database by copying the parameters from pfile of primary database.

Specify db_file_name_convert and log_file_name_convert , if source datafile and logfile location are different between primary and standby database. If multiple directories needs to be changed for each datafiles as per your size capacity in standby, then you have to specify set newname clause in the command.

Eg: Source datafile location : 12 datafiles from +DATA should be stored in destination datafile location : +DATA,+RECO,+REDO with each location containing 4 datafiles (3*4=12)

-bash-4.2$ cat /apps01/product/12.1.0/dbhome_1/dbs/initdbx01.ora
*.db_name='dbx00'
*.db_unique_name='dbx01'
*.db_recovery_file_dest='/apps01/FRA'
*.db_recovery_file_dest_size=5000m
*.diagnostic_dest='/apps01/base'
*.dispatchers='(PROTOCOL=TCP) (SERVICE=dbx01XDB)'
*.enable_pluggable_database=true
*.local_listener='LISTENER_DBX01'
*.log_archive_format='%t_%s_%r.dbf'
*.memory_target=1440m
*.open_cursors=300
*.processes=300
*.remote_login_passwordfile='EXCLUSIVE'
*.undo_tablespace='UNDOTBS1'
*.db_file_name_convert='+DATAC1/DBX00','/apps01/base/oradata/dbx01'
*.log_file_name_convert='+DATAC1/DBX00','/apps01/base/oradata/dbx01'
*.compatible='12.1.0.2.0'

Start the standby database in nomount state using the pfile

SQL> startup nomount pfile='/apps01/product/12.1.0/dbhome_1/dbs/initdbx01.ora';
ORACLE instance started.
Total System Global Area 1509949440 bytes
Fixed Size                  2924640 bytes
Variable Size             973082528 bytes
Database Buffers          520093696 bytes
Redo Buffers               13848576 bytes

Create the required directories from the source datafiles

-bash-4.2$ mkdir /apps01/base/oradata/dbx01/onlinelog/
-bash-4.2$ mkdir -p /apps01/base/oradata/dbx01/CC5E4A28ABF115B5E053CD38A8C001EC/datafile/
-bash-4.2$ mkdir -p /apps01/base/oradata/dbx01/cc5ebf37a8122a8be053cd38a8c0efdf/datafile/
-bash-4.2$ mkdir -p /apps01/base/oradata/dbx01/cc5eb83e86c4281fe053cd38a8c0db1b/datafile/
-bash-4.2$ mkdir -p /apps01/base/oradata/dbx01/fd9ac20f64d244d7e043b6a9e80a2f2f/datafile

Start RMAN duplication from standby database. Connect to target as primary and auxiliary as standby database. As this is 12c version, backupset duplication method is used for duplication.

-bash-4.2$ rman target sys/password@dbx00 auxiliary sys/password@dbx01

Recovery Manager: Release 12.1.0.2.0 - Production on Tue Sep 21 04:43:54 2021

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

connected to target database: DBX00 (DBID=2195240283)
connected to auxiliary database: DBX00 (not mounted)

RMAN> run
{
allocate channel c1 device type disk;
allocate channel c2 device type disk;
allocate channel c3 device type disk;
allocate auxiliary channel c4 device type disk;
duplicate target database for standby from active database using backupset section size 300M;
}2> 3> 4> 5> 6> 7> 8>

using target database control file instead of recovery catalog
allocated channel: c1
channel c1: SID=76 instance=dbx00_1 device type=DISK

allocated channel: c2
channel c2: SID=87 instance=dbx00_1 device type=DISK

allocated channel: c3
channel c3: SID=81 instance=dbx00_1 device type=DISK

allocated channel: c4
channel c4: SID=1 device type=DISK

Starting Duplicate Db at 21-SEP-21

contents of Memory Script:
{
   backup as copy reuse
   targetfile  '+DATAC1/DBX00/PASSWORD/pwddbx00.256.1083714689' auxiliary format
 '/apps01/product/12.1.0/dbhome_1/dbs/orapwdbx01'   ;
}
executing Memory Script

Starting backup at 21-SEP-21
Finished backup at 21-SEP-21

contents of Memory Script:
{
   sql clone "create spfile from memory";
   shutdown clone immediate;
   startup clone nomount;
   restore clone from service  'dbx00' standby controlfile;
}
executing Memory Script

sql statement: create spfile from memory

Oracle instance shut down

connected to auxiliary database (not started)
Oracle instance started

Total System Global Area    1509949440 bytes

Fixed Size                     2924640 bytes
Variable Size                989859744 bytes
Database Buffers             503316480 bytes
Redo Buffers                  13848576 bytes
allocated channel: c4
channel c4: SID=21 device type=DISK

Starting restore at 21-SEP-21

channel c4: starting datafile backup set restore
channel c4: using network backup set from service dbx00
channel c4: restoring control file
channel c4: restore complete, elapsed time: 00:00:03
output file name=/apps01/FRA/DBX01/controlfile/o1_mf_jnl5gscz_.ctl
Finished restore at 21-SEP-21

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
 "/apps01/base/oradata/dbx01/tempfile/temp.258.1083714929";
   set newname for tempfile  2 to
 "/apps01/base/oradata/dbx01/fd9ac20f64d244d7e043b6a9e80a2f2f/datafile/pdbseed_temp012021-09-19_11-56-29-pm.dbf";
   set newname for tempfile  3 to
 "/apps01/base/oradata/dbx01/cc5eb83e86c4281fe053cd38a8c0db1b/tempfile/temp.276.1083716869";
   set newname for tempfile  4 to
 "/apps01/base/oradata/dbx01/cc5ebf37a8122a8be053cd38a8c0efdf/tempfile/temp.261.1083716969";
   switch clone tempfile all;
   set newname for datafile  1 to
 "/apps01/base/oradata/dbx01/datafile/system.268.1083714775";
   set newname for datafile  3 to
 "/apps01/base/oradata/dbx01/datafile/sysaux.273.1083714719";
   set newname for datafile  4 to
 "/apps01/base/oradata/dbx01/datafile/undotbs1.266.1083714851";
   set newname for datafile  5 to
 "/apps01/base/oradata/dbx01/fd9ac20f64d244d7e043b6a9e80a2f2f/datafile/system.260.1083714939";
   set newname for datafile  6 to
 "/apps01/base/oradata/dbx01/datafile/users.257.1083714849";
   set newname for datafile  7 to
 "/apps01/base/oradata/dbx01/fd9ac20f64d244d7e043b6a9e80a2f2f/datafile/sysaux.267.1083714939";
   set newname for datafile  8 to
 "/apps01/base/oradata/dbx01/cc5eb83e86c4281fe053cd38a8c0db1b/datafile/system.270.1083716807";
   set newname for datafile  9 to
 "/apps01/base/oradata/dbx01/cc5eb83e86c4281fe053cd38a8c0db1b/datafile/sysaux.277.1083716807";
   set newname for datafile  10 to
 "/apps01/base/oradata/dbx01/cc5eb83e86c4281fe053cd38a8c0db1b/datafile/users.274.1083716893";
   set newname for datafile  11 to
 "/apps01/base/oradata/dbx01/cc5ebf37a8122a8be053cd38a8c0efdf/datafile/system.269.1083716919";
   set newname for datafile  12 to
 "/apps01/base/oradata/dbx01/cc5ebf37a8122a8be053cd38a8c0efdf/datafile/sysaux.272.1083716919";
   set newname for datafile  13 to
 "/apps01/base/oradata/dbx01/cc5ebf37a8122a8be053cd38a8c0efdf/datafile/users.278.1083717009";
   restore
   from service  'dbx00'   section size
 300 m   clone database
   ;
   sql 'alter system archive log current';
}
executing Memory Script

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

renamed tempfile 1 to /apps01/base/oradata/dbx01/tempfile/temp.258.1083714929 in control file
renamed tempfile 2 to /apps01/base/oradata/dbx01/fd9ac20f64d244d7e043b6a9e80a2f2f/datafile/pdbseed_temp012021-09-19_11-56-29-pm.dbf in control file
renamed tempfile 3 to /apps01/base/oradata/dbx01/cc5eb83e86c4281fe053cd38a8c0db1b/tempfile/temp.276.1083716869 in control file
renamed tempfile 4 to /apps01/base/oradata/dbx01/cc5ebf37a8122a8be053cd38a8c0efdf/tempfile/temp.261.1083716969 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

executing command: SET NEWNAME

Starting restore at 21-SEP-21

skipping datafile 5; already restored to SCN 1744270
skipping datafile 7; already restored to SCN 1744270
skipping datafile 8; already restored to SCN 1857203
skipping datafile 9; already restored to SCN 1857203
skipping datafile 10; already restored to SCN 1857203
skipping datafile 11; already restored to SCN 1857214
skipping datafile 12; already restored to SCN 1857214
skipping datafile 13; already restored to SCN 1857214
channel c4: starting datafile backup set restore
channel c4: using network backup set from service dbx00
channel c4: specifying datafile(s) to restore from backup set
channel c4: restoring datafile 00001 to /apps01/base/oradata/dbx01/datafile/system.268.1083714775
channel c4: restoring section 1 of 3
channel c4: restore complete, elapsed time: 00:00:36
channel c4: starting datafile backup set restore
channel c4: using network backup set from service dbx00
channel c4: specifying datafile(s) to restore from backup set
channel c4: restoring datafile 00001 to /apps01/base/oradata/dbx01/datafile/system.268.1083714775
channel c4: restoring section 2 of 3
channel c4: restore complete, elapsed time: 00:00:26
channel c4: starting datafile backup set restore
channel c4: using network backup set from service dbx00
channel c4: specifying datafile(s) to restore from backup set
channel c4: restoring datafile 00001 to /apps01/base/oradata/dbx01/datafile/system.268.1083714775
channel c4: restoring section 3 of 3
channel c4: restore complete, elapsed time: 00:00:26
channel c4: starting datafile backup set restore
channel c4: using network backup set from service dbx00
channel c4: specifying datafile(s) to restore from backup set
channel c4: restoring datafile 00003 to /apps01/base/oradata/dbx01/datafile/sysaux.273.1083714719
channel c4: restoring section 1 of 3
channel c4: restore complete, elapsed time: 00:00:26
channel c4: starting datafile backup set restore
channel c4: using network backup set from service dbx00
channel c4: specifying datafile(s) to restore from backup set
channel c4: restoring datafile 00003 to /apps01/base/oradata/dbx01/datafile/sysaux.273.1083714719
channel c4: restoring section 2 of 3
channel c4: restore complete, elapsed time: 00:00:36
channel c4: starting datafile backup set restore
channel c4: using network backup set from service dbx00
channel c4: specifying datafile(s) to restore from backup set
channel c4: restoring datafile 00003 to /apps01/base/oradata/dbx01/datafile/sysaux.273.1083714719
channel c4: restoring section 3 of 3
channel c4: restore complete, elapsed time: 00:00:25
channel c4: starting datafile backup set restore
channel c4: using network backup set from service dbx00
channel c4: specifying datafile(s) to restore from backup set
channel c4: restoring datafile 00004 to /apps01/base/oradata/dbx01/datafile/undotbs1.266.1083714851
channel c4: restoring section 1 of 1
channel c4: restore complete, elapsed time: 00:00:46
channel c4: starting datafile backup set restore
channel c4: using network backup set from service dbx00
channel c4: specifying datafile(s) to restore from backup set
channel c4: restoring datafile 00006 to /apps01/base/oradata/dbx01/datafile/users.257.1083714849
channel c4: restoring section 1 of 1
channel c4: restore complete, elapsed time: 00:00:26
Finished restore at 21-SEP-21

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=3 STAMP=1083818974 file name=/apps01/base/oradata/dbx01/datafile/system.268.1083714775
datafile 3 switched to datafile copy
input datafile copy RECID=4 STAMP=1083819034 file name=/apps01/base/oradata/dbx01/datafile/sysaux.273.1083714719
datafile 4 switched to datafile copy
input datafile copy RECID=5 STAMP=1083819095 file name=/apps01/base/oradata/dbx01/datafile/undotbs1.266.1083714851
datafile 5 switched to datafile copy
input datafile copy RECID=6 STAMP=1083819095 file name=/apps01/base/oradata/dbx01/fd9ac20f64d244d7e043b6a9e80a2f2f/datafile/system.260.1083714939
datafile 6 switched to datafile copy
input datafile copy RECID=7 STAMP=1083819095 file name=/apps01/base/oradata/dbx01/datafile/users.257.1083714849
datafile 7 switched to datafile copy
input datafile copy RECID=8 STAMP=1083819095 file name=/apps01/base/oradata/dbx01/fd9ac20f64d244d7e043b6a9e80a2f2f/datafile/sysaux.267.1083714939
datafile 8 switched to datafile copy
input datafile copy RECID=9 STAMP=1083819095 file name=/apps01/base/oradata/dbx01/cc5eb83e86c4281fe053cd38a8c0db1b/datafile/system.270.1083716807
datafile 9 switched to datafile copy
input datafile copy RECID=10 STAMP=1083819095 file name=/apps01/base/oradata/dbx01/cc5eb83e86c4281fe053cd38a8c0db1b/datafile/sysaux.277.1083716807
datafile 10 switched to datafile copy
input datafile copy RECID=11 STAMP=1083819095 file name=/apps01/base/oradata/dbx01/cc5eb83e86c4281fe053cd38a8c0db1b/datafile/users.274.1083716893
datafile 11 switched to datafile copy
input datafile copy RECID=12 STAMP=1083819095 file name=/apps01/base/oradata/dbx01/cc5ebf37a8122a8be053cd38a8c0efdf/datafile/system.269.1083716919
datafile 12 switched to datafile copy
input datafile copy RECID=13 STAMP=1083819095 file name=/apps01/base/oradata/dbx01/cc5ebf37a8122a8be053cd38a8c0efdf/datafile/sysaux.272.1083716919
datafile 13 switched to datafile copy
input datafile copy RECID=14 STAMP=1083819095 file name=/apps01/base/oradata/dbx01/cc5ebf37a8122a8be053cd38a8c0efdf/datafile/users.278.1083717009
Finished Duplicate Db at 21-SEP-21
released channel: c1
released channel: c2
released channel: c3
released channel: c4

1 Comment

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s