Skip to main content

Standby Database set up using RMAN duplicate || Oracle Data Guard (ODG)

  • Introduction: Oracle Data Guard (ODG) is a high-availability, disaster recovery, and data protection feature or solution provided by Oracle Database. It ensures the availability of critical data by maintaining one or more synchronized standby databases as replicas of a primary database. These standby databases can be used for disaster recovery, reporting, or backups, minimizing downtime and data loss in the event of unexpected failures. So here we will do Standby database set up in an easy and step by step manner. We will use RMAN DUPLICATE method here which is suitable for small database size. Also for this a good network bandwith is required between Primary & Standby.

  • Prerequisites:

    • o Primary server with Oracle database Software installed and running database.
    o Standby server with Oracle database Software installed.
    o Connectivity between Primary and Standby server.

  • Environment:
    • Server Primary Standby
    Hostname Source Target
    IP 192.168.80.51 192.168.80.111
    OS OEL 9 OEL 9
    SID ORCLDC ORCLDR
    Service Name ORCLDC ORCLDR

  • Now start configuring the ODG:

1. Connectivity Test


==> From Source to Target:

[oracle@source ~]$ ping -c 3 target.localdomain
PING target.localdomain (192.168.80.111) 56(84) bytes of data.
64 bytes from target.localdomain (192.168.80.111): icmp_seq=1 ttl=64 time=0.678 ms
64 bytes from target.localdomain (192.168.80.111): icmp_seq=2 ttl=64 time=0.456 ms
64 bytes from target.localdomain (192.168.80.111): icmp_seq=3 ttl=64 time=0.924 ms
--- target.localdomain ping statistics ---
3 packets transmitted, 3 received, 0% packet loss, time 2107ms
rtt min/avg/max/mdev = 0.456/0.686/0.924/0.191 ms


==> From Target to Source:


[oracle@target ~]$ ping -c 3 source.localdomain
PING source.localdomain (192.168.80.51) 56(84) bytes of data.
64 bytes from source.localdomain (192.168.80.51): icmp_seq=1 ttl=64 time=0.690 ms
64 bytes from source.localdomain (192.168.80.51): icmp_seq=2 ttl=64 time=6.61 ms
64 bytes from source.localdomain (192.168.80.51): icmp_seq=3 ttl=64 time=0.315 ms
--- source.localdomain ping statistics ---
3 packets transmitted, 3 received, 0% packet loss, time 2002ms
rtt min/avg/max/mdev = 0.315/2.536/6.605/2.880 ms
2. Primary Database setup
First fetch some details about Primary Database:

[oracle@source ~]$ sqlplus / as sysdba

SQL> def
DEFINE _DATE              = "12-JAN-25" (CHAR)
DEFINE _CONNECT_IDENTIFIER = "orcldc" (CHAR)
DEFINE _USER              = "SYS" (CHAR)
DEFINE _PRIVILEGE         = "AS SYSDBA" (CHAR)
DEFINE _SQLPLUS_RELEASE   = "1924000000" (CHAR)
DEFINE _EDITOR            = "vi" (CHAR)
DEFINE _O_VERSION         = "Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.24.0.0.0" (CHAR)
DEFINE _O_RELEASE         = "1924000000" (CHAR)

SQL> set lines 200 pages 1000
col open_mode for a15
col HOST_NAME for a15

NAME      DATABASE_ROLE    OPEN_MODE       LOG_MODE
--------  ---------------- --------------- ------------
ORCL      PRIMARY          READ WRITE      ARCHIVELOG

SQL> select banner, banner_full from v$version;

BANNER
--------------------------------------------------------------------------------
BANNER_FULL
--------------------------------------------------------------------------------
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.24.0.0.0

SQL> col name for a70
SQL> select file#, name from v$datafile;

FILE# NAME
----- ----------------------------------------------------------------------
1     /u01/app/oracle/oradata/ORCL/system01.dbf
3     /u01/app/oracle/oradata/ORCL/sysaux01.dbf
4     /u01/app/oracle/oradata/ORCL/undotbs01.dbf
5     /u01/app/oracle/oradata/ORCL/pdbseed/system01.dbf
6     /u01/app/oracle/oradata/ORCL/pdbseed/sysaux01.dbf
7     /u01/app/oracle/oradata/ORCL/users01.dbf
8     /u01/app/oracle/oradata/ORCL/pdbseed/undotbs01.dbf
9     /u01/app/oracle/oradata/ORCL/orclpdb/system01.dbf
10    /u01/app/oracle/oradata/ORCL/orclpdb/sysaux01.dbf
11    /u01/app/oracle/oradata/ORCL/orclpdb/undotbs01.dbf
12    /u01/app/oracle/oradata/ORCL/orclpdb/users01.dbf

11 rows selected.

SQL> select name from v$controlfile;

NAME
----------------------------------------------------------------------
/u01/app/oracle/oradata/ORCL/control01.ctl
/u01/app/oracle/fast_recovery_area/ORCL/control02.ctl

SQL> col MEMBER for a40
SQL> select group#, type, MEMBER from v$logfile order by group#;

GROUP# TYPE    MEMBER
------ ------- ----------------------------------------
1      ONLINE  /u01/app/oracle/oradata/ORCL/redo01.log
2      ONLINE  /u01/app/oracle/oradata/ORCL/redo02.log
3      ONLINE  /u01/app/oracle/oradata/ORCL/redo03.log
** If database is in NOARCHIVELOG mode then do below steps:

ALTER SYSTEM SET db_recovery_file_dest='/u01/app/oracle/fast_recovery_area' SCOPE=BOTH;
ALTER SYSTEM SET db_recovery_file_dest_size=50G SCOPE=BOTH;
SHUT IMMEDIATE;
STARTUP MOUNT;
ALTER DATABASE ARCHIVELOG;
ALTER DATABASE OPEN;
Now let's do changes in Primary Database for ODG:

SQL> SELECT name, force_logging, log_mode FROM v$database;
NAME    FORCE_LOGGING  LOG_MODE
------- -------------- ------------
ORCL    NO             ARCHIVELOG

SQL> ALTER DATABASE FORCE LOGGING;
Database altered.

SQL> SELECT name, force_logging, log_mode FROM v$database;
NAME    FORCE_LOGGING  LOG_MODE
------- -------------- ------------
ORCL    YES            ARCHIVELOG

SQL> SHOW PARAMETER db_name;
NAME        TYPE    VALUE
----------- ------- ------------------------------
db_name     string  orcl

SQL> SHOW PARAMETER db_unique_name;
NAME             TYPE    VALUE
---------------- ------- ------------------------------
db_unique_name   string  ORCLDC

SQL> ALTER SYSTEM SET LOG_ARCHIVE_CONFIG='DG_CONFIG=(ORCLDC,ORCLDR)';
System altered.

SQL> SHOW PARAMETER LOG_ARCHIVE_CONFIG;
NAME                TYPE    VALUE
------------------- ------- ------------------------------
log_archive_config  string  DG_CONFIG=(ORCLDC,ORCLDR)

SQL> ALTER SYSTEM SET LOG_ARCHIVE_DEST_2='SERVICE=ORCLDR NOAFFIRM ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=ORCLDR';
System altered.

SQL> SHOW PARAMETER LOG_ARCHIVE_DEST_2;
NAME                  TYPE    VALUE
--------------------- ------- -------------------------------------------------------
log_archive_dest_2    string  SERVICE=ORCLDR NOAFFIRM ASYNC
                               VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE)
                               DB_UNIQUE_NAME=ORCLDR

log_archive_dest_20   string
log_archive_dest_21   string
log_archive_dest_22   string
log_archive_dest_23   string
log_archive_dest_24   string
log_archive_dest_25   string
log_archive_dest_26   string
log_archive_dest_27   string
log_archive_dest_28   string
log_archive_dest_29   string

SQL> SHOW PARAMETER LOG_ARCHIVE_FORMAT;
NAME                  TYPE    VALUE
--------------------- ------- ------------------------------
log_archive_format    string  %t_%s_%r.dbf

SQL> ALTER SYSTEM SET LOG_ARCHIVE_FORMAT='%t_%s_%r.arc' SCOPE=SPFILE;
System altered.

SQL> SHOW PARAMETER LOG_ARCHIVE_FORMAT;
NAME                  TYPE    VALUE
--------------------- ------- ------------------------------
log_archive_format    string  %t_%s_%r.dbf

SQL> ALTER SYSTEM SET LOG_ARCHIVE_MAX_PROCESSES=30 SCOPE=SPFILE;
System altered.

SQL> ALTER SYSTEM SET REMOTE_LOGIN_PASSWORDFILE=EXCLUSIVE SCOPE=SPFILE;
System altered.

SQL> ALTER SYSTEM SET FAL_SERVER=ORCLDR;
System altered.

SQL> ALTER SYSTEM SET STANDBY_FILE_MANAGEMENT=AUTO;
System altered.

SQL> ALTER SYSTEM SET archive_lag_target=900;
System altered.
Now we need to set DB_FILE_NAME_CONVERT & LOG_FILE_NAME_CONVERT parameter so that datafile and logfile locations will be converted automatically from Primary to Standby and vice-versa.

SQL> ALTER SYSTEM SET DB_FILE_NAME_CONVERT='/u01/app/oracle/oradata/ORCLDR','/u01/app/oracle/oradata/ORCL' SCOPE=SPFILE;
System altered.

SQL> ALTER SYSTEM SET LOG_FILE_NAME_CONVERT='/u01/app/oracle/oradata/ORCLDR','/u01/app/oracle/oradata/ORCL' SCOPE=SPFILE;
System altered.
Now just take a bounce of Database so that all parameter will get reflects.

SQL> SHUT IMMEDIATE;
Database closed.
Database dismounted.
ORACLE instance shut down.

SQL> STARTUP;
ORACLE instance started.
Total System Global Area     1459616616 bytes
Fixed Size                      9177960 bytes
Variable Size                 905969664 bytes
Database Buffers              536870912 bytes
Redo Buffers                    7598080 bytes
Database mounted.
Database opened.

SQL> SHOW PDBS;
    CON_ID CON_NAME                       OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
         2 PDB$SEED                       READ ONLY  NO
         3 ORCLPDB                        READ WRITE NO

SQL> SHOW PARAMETER LOG_ARCHIVE_FORMAT;
NAME                  TYPE     VALUE
--------------------- -------- ------------------------------
log_archive_format    string   %t_%s_%r.arc

SQL> SHOW PARAMETER LOG_ARCHIVE_MAX_PROCESSES;
NAME                       TYPE     VALUE
-------------------------- -------- ------------------------------
log_archive_max_processes  integer  30

SQL> SHOW PARAMETER REMOTE_LOGIN_PASSWORDFILE;
NAME                        TYPE     VALUE
--------------------------- -------- ------------------------------
remote_login_passwordfile   string   EXCLUSIVE
Now add standby redo logfiles which will be used to apply the changes whenever primary becomes standby. Number of Standby logs should be one more than the number of Online logs. 

SQL> ALTER DATABASE ADD STANDBY LOGFILE ('/u01/app/oracle/oradata/ORCL/standby_redo01.log') SIZE 200M;
Database altered.

SQL> ALTER DATABASE ADD STANDBY LOGFILE ('/u01/app/oracle/oradata/ORCL/standby_redo02.log') SIZE 200M;
Database altered.

SQL> ALTER DATABASE ADD STANDBY LOGFILE ('/u01/app/oracle/oradata/ORCL/standby_redo03.log') SIZE 200M;
Database altered.

SQL> ALTER DATABASE ADD STANDBY LOGFILE ('/u01/app/oracle/oradata/ORCL/standby_redo04.log') SIZE 200M;
Database altered.

SQL> SELECT group#, type, member FROM v$logfile ORDER BY group#;
GROUP#  TYPE     MEMBER
------  -------  -------------------------------------------------------------------------------
1       ONLINE   /u01/app/oracle/oradata/ORCL/redo01.log
2       ONLINE   /u01/app/oracle/oradata/ORCL/redo02.log
3       ONLINE   /u01/app/oracle/oradata/ORCL/redo03.log
4       STANDBY  /u01/app/oracle/oradata/ORCL/standby_redo01.log
5       STANDBY  /u01/app/oracle/oradata/ORCL/standby_redo02.log
6       STANDBY  /u01/app/oracle/oradata/ORCL/standby_redo03.log
7       STANDBY  /u01/app/oracle/oradata/ORCL/standby_redo04.log

7 rows selected.
3. Primary Service setup 

[oracle@source ~]$ cd $ORACLE_HOME/network/admin
[oracle@source admin]$ cat tnsnames.ora
# tnsnames.ora Network Configuration File: /u01/app/oracle/product/19.0.0/dbhome_1/network/admin/tnsnames.ora
# Generated by Oracle configuration tools.

ORCLDC =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = source.localdomain)(PORT = 1521))
    )
    (CONNECT_DATA =
      (SERVICE_NAME = orcldc)
    )
  )

ORCLDR =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = target.localdomain)(PORT = 1521))
    )
    (CONNECT_DATA =
      (SERVICE_NAME = orcldr)
    )
  )
4.Primary Database and files backup
Create pfile:

SQL> create pfile='/u01/app/oracle/rmandc/initorcldc.ora' from spfile;
Copy password file:

[oracle@source rmandc]$ cd $ORACLE_HOME/dbs
[oracle@source dbs]$ cp orapworcldc /u01/app/oracle/rmandc/
5.Transfer password & pfile from Primary to Standby 

[oracle@source dbs]$ cd /u01/app/oracle/rmandc/
[oracle@source rmandc]$ ls -lrth
total 1.3G
-rw-r--r--. 1 oracle oinstall 1.6K Jan 12 11:28 initorcldc.ora
-rw-r-----. 1 oracle oinstall 2.0K Jan 12 11:39 orapworcldc
[oracle@source rmandc]$ scp * oracle@target:/u01/app/oracle/rmandr
The authenticity of host 'target (192.168.80.111)' can't be established.
ED25519 key fingerprint is SHA256:u1O6svnS8kY1i6Mv88TDIlYsvqDyISi2Uz3ZBc8kKfY.
This key is not known by any other names
Are you sure you want to continue connecting (yes/no/[fingerprint])? yes
Warning: Permanently added 'target' (ED25519) to the list of known hosts.
oracle@target's password:
initorcldc.ora                                100%   1632    52.9KB/s   00:00
orapworcldc                                   100%   2048   105.3KB/s   00:00
6.Standby Database setup
Do the changes in Primary pfile and create pfile initorcldr.ora for Standby. Below parameters have been modified. Sample file is shown further below: 

*.audit_file_dest='/u01/app/oracle/admin/orcldr/adump'
*.control_files='/u01/app/oracle/oradata/ORCLDR/control01.ctl','/u01/app/oracle/fast_recovery_area/ORCLDR/control02.ctl'
*.db_file_name_convert='/u01/app/oracle/oradata/ORCL','/u01/app/oracle/oradata/ORCLDR'
*.db_unique_name='ORCLDR'
*.log_file_name_convert='/u01/app/oracle/oradata/ORCL','/u01/app/oracle/oradata/ORCLDR'
*.fal_server='ORCLDC'
*.log_archive_dest_2='SERVICE=ORCLDC NOAFFIRM ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=ORCLDC'




orcldr.__data_transfer_cache_size=0
orcldr.__db_cache_size=486539264
orcldr.__inmemory_ext_roarea=0
orcldr.__inmemory_ext_rwarea=0
orcldr.__java_pool_size=16777216
orcldr.__large_pool_size=16777216
orcldr.__oracle_base='/u01/app/oracle'  # ORACLE_BASE set from environment
orcldr.__pga_aggregate_target=587202560
orcldr.__sga_target=872415232
orcldr.__shared_io_pool_size=50331648
orcldr.__shared_pool_size=285212672
orcldr.__streams_pool_size=0
orcldr.__unified_pga_pool_size=0
*.archive_lag_target=900
*.audit_file_dest='/u01/app/oracle/admin/orcldr/adump'
*.audit_trail='db'
*.compatible='19.0.0'
*.control_files='/u01/app/oracle/oradata/ORCLDR/control01.ctl','/u01/app/oracle/fast_recovery_area/ORCLDR/control02.ctl'
*.db_block_size=8192
*.db_file_name_convert='/u01/app/oracle/oradata/ORCL','/u01/app/oracle/oradata/ORCLDR'
*.db_name='orcl'
*.db_recovery_file_dest='/u01/app/oracle/fast_recovery_area'
*.db_recovery_file_dest_size=8192m
*.db_unique_name='ORCLDR'
*.diagnostic_dest='/u01/app/oracle'
*.dispatchers='(PROTOCOL=TCP) (SERVICE=orcldcXDB)'
*.enable_pluggable_database=true
*.fal_server='ORCLDC'
*.log_archive_config='DG_CONFIG=(ORCLDC,ORCLDR)'
*.log_archive_dest_2='SERVICE=ORCLDC NOAFFIRM ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=ORCLDC'
*.log_archive_format='%t_%s_%r.arc'
*.log_archive_max_processes=30
*.log_file_name_convert='/u01/app/oracle/oradata/ORCL','/u01/app/oracle/oradata/ORCLDR'
*.memory_target=1384m
*.nls_language='AMERICAN'
*.nls_territory='AMERICA'
*.open_cursors=300
*.processes=300
*.remote_login_passwordfile='EXCLUSIVE'
*.standby_file_management='AUTO'
*.undo_tablespace='UNDOTBS1'
Create required directories:

[oracle@target rmandr]$ mkdir -p /u01/app/oracle/admin/orcldr/adump
[oracle@target rmandr]$ mkdir -p /u01/app/oracle/oradata/ORCLDR/
[oracle@target rmandr]$ mkdir -p /u01/app/oracle/fast_recovery_area/ORCLDR/
Copy password file in $ORACLE_HOME/dbs :

[oracle@target rmandr]$ cp orapworcldc $ORACLE_HOME/dbs/orapworcldr
[oracle@target rmandr]$ ls -lrth $ORACLE_HOME/dbs/orapworcldr
-rw-r-----. 1 oracle oinstall 2.0K Jan 12 12:02 /u01/app/oracle/product/19.0.0/dbhome_1/dbs/orapworcldr
Create listener using NETCA. Start it. Also add TNS service entries for Primary & Standby in tnsnames.ora file.

Refer Create listener using NETCA


[oracle@target ~]$ 
[oracle@target ~]$ cd $ORACLE_HOME/network/admin
[oracle@target admin]$
[oracle@target admin]$ cat listener.ora
# listener.ora Network Configuration File: /u01/app/oracle/product/19.0.0/dbhome_1/network/admin/listener.ora
# Generated by Oracle configuration tools.

LISTENER =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = TCP)(HOST = target.localdomain)(PORT = 1521))
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
    )
  )
SID_LIST_LISTENER =
   (SID_LIST =
      (SID_DESC = (GLOBAL_DBNAME =  orcldr)
                  (ORACLE_HOME = /u01/app/oracle/product/19.0.0/dbhome_1)
                  (SID_NAME =  orcldr)
       )
    )
[oracle@target admin]$
[oracle@target admin]$ cat tnsnames.ora
# tnsnames.ora Network Configuration File: /u01/app/oracle/product/19.0.0/dbhome_1/network/admin/tnsnames.ora
# Generated by Oracle configuration tools.


ORCLDC =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = source.localdomain)(PORT = 1521))
    )
    (CONNECT_DATA =
      (SERVICE_NAME = orcldc)
    )
  )


ORCLDR =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = target.localdomain)(PORT = 1521))
    )
    (CONNECT_DATA =
      (SERVICE_NAME = orcldr)
    )
  )

[oracle@target admin]$ lsnrctl start
Copyright (c) 1991, 2024, Oracle.  All rights reserved.
Starting /u01/app/oracle/product/19.0.0/dbhome_1/bin/tnslsnr: please wait...
TNSLSNR for Linux: Version 19.0.0.0.0 - Production
System parameter file is /u01/app/oracle/product/19.0.0/dbhome_1/network/admin/listener.ora
Log messages written to /u01/app/oracle/diag/tnslsnr/target/listener/alert/log.xml
Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=target.localdomain)(PORT=1521)))
Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521)))
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=target.localdomain)(PORT=1521)))
STATUS of the LISTENER
------------------------
Alias                     LISTENER
Version                   TNSLSNR for Linux: Version 19.0.0.0.0 - Production
Start Date                12-JAN-2025 12:09:54
Uptime                    0 days 0 hr. 0 min. 2 sec
Trace Level               off
Security                  ON: Local OS Authentication
SNMP                      OFF
Listener Parameter File   /u01/app/oracle/product/19.0.0/dbhome_1/network/admin/listener.ora
Listener Log File         /u01/app/oracle/diag/tnslsnr/target/listener/alert/log.xml
Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=target.localdomain)(PORT=1521)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521)))
  Services Summary...
Service "ORCLDR" has 1 instance(s).
  Instance "orcldr", status UNKNOWN, has 1 handler(s) for this service...
The command completed successfully
7.Startup Standby instance. 

[oracle@target ~]$ . oraenv
ORACLE_SID = [orcl] ? orcldr
ORACLE_HOME = [/home/oracle] ? /u01/app/oracle/product/19.0.0/dbhome_1
The Oracle base remains unchanged with value /u01/app/oracle

[oracle@target ~]$ sqlplus / as sysdba
SQL*Plus: Release 19.0.0.0.0 - Production on Wed Jan 12 12:13:03 2025
Version 19.24.0.0.0

Copyright (c) 1982, 2024, Oracle.  All rights reserved.

Connected to an idle instance.
SQL> startup nomount pfile='/u01/app/oracle/rmandr/initorcldr.ora';
ORACLE instance started.

Total System Global Area 1459616616 bytes
Fixed Size                  9177960 bytes
Variable Size             905969664 bytes
Database Buffers          536870912 bytes
Redo Buffers                7598080 bytes
SQL>

SQL> def
DEFINE _DATE           = "12-JAN-25" (CHAR)
DEFINE _CONNECT_IDENTIFIER = "orcldr" (CHAR)
DEFINE _USER           = "SYS" (CHAR)
DEFINE _PRIVILEGE      = "AS SYSDBA" (CHAR)
DEFINE _SQLPLUS_RELEASE = "1924000000" (CHAR)
DEFINE _EDITOR         = "vi" (CHAR)
DEFINE _O_VERSION      = "Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.24.0.0.0" (CHAR)
DEFINE _O_RELEASE      = "1924000000" (CHAR)
SQL> exit
Disconnected from Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.24.0.0.0
[oracle@target ~]$
8. Now connect with RMAN using Target & Auxiliary clause. Primary TNS alias will be used for target & Standby TNS alias will be used for Auxiliary.

[oracle@target ~]$ rman target sys/sys@orcldc auxiliary sys/sys@orcldr

Recovery Manager: Release 19.0.0.0.0 - Production on Mon Jan 12 20:32:02 2025
Version 19.24.0.0.0

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

connected to target database: ORCL (DBID=1718803653)
connected to auxiliary database: ORCL (not mounted)

RMAN>
8. Now run the below RMAN duplicate script.
run
{
duplicate target database for standby from active database;
}

[oracle@target ~]$ rman target sys/sys@orcldc auxiliary sys/sys@orcldr

Recovery Manager: Release 19.0.0.0.0 - Production on Mon JAN 12 11:32:02 2025
Version 19.24.0.0.0

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

connected to target database: ORCL (DBID=1718803653)
connected to auxiliary database: ORCL (not mounted)

RMAN>

RMAN> run
{
duplicate target database for standby from active database;
}run
2> {
3> duplicate target database for standby from active database;
4>
}
Starting Duplicate Db at 12-JAN-25
using target database control file instead of recovery catalog
allocated channel: ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: SID=237 device type=DISK

contents of Memory Script:
{
   backup as copy reuse
   passwordfile auxiliary format  '/u01/app/oracle/product/19.0.0/dbhome_1/dbs/orapworcldr'   ;
}
executing Memory Script

Starting backup at 12-JAN-25
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=291 device type=DISK
Finished backup at 12-Jan-25

contents of Memory Script:
{
   restore clone from service  'orcldc' standby controlfile;
}
executing Memory Script

Starting restore at 12-JAN-25
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 orcldc
channel ORA_AUX_DISK_1: restoring control file
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:05
output file name=/u01/app/oracle/oradata/ORCLDR/control01.ctl
output file name=/u01/app/oracle/fast_recovery_area/ORCLDR/control02.ctl
Finished restore at 12-JAN-25

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/app/oracle/oradata/ORCLDR/temp01.dbf";
   set newname for tempfile  2 to
 "/u01/app/oracle/oradata/ORCLDR/pdbseed/temp012025-01-22_00-17-43-687-AM.dbf";
   set newname for tempfile  3 to
 "/u01/app/oracle/oradata/ORCLDR/orclpdb/temp01.dbf";
   switch clone tempfile all;
   set newname for datafile  1 to
 "/u01/app/oracle/oradata/ORCLDR/system01.dbf";
   set newname for datafile  3 to
 "/u01/app/oracle/oradata/ORCLDR/sysaux01.dbf";
   set newname for datafile  4 to
 "/u01/app/oracle/oradata/ORCLDR/undotbs01.dbf";
   set newname for datafile  5 to
 "/u01/app/oracle/oradata/ORCLDR/pdbseed/system01.dbf";
   set newname for datafile  6 to
 "/u01/app/oracle/oradata/ORCLDR/pdbseed/sysaux01.dbf";
   set newname for datafile  7 to
 "/u01/app/oracle/oradata/ORCLDR/users01.dbf";
   set newname for datafile  8 to
 "/u01/app/oracle/oradata/ORCLDR/pdbseed/undotbs01.dbf";
   set newname for datafile  9 to
 "/u01/app/oracle/oradata/ORCLDR/orclpdb/system01.dbf";
   set newname for datafile  10 to
 "/u01/app/oracle/oradata/ORCLDR/orclpdb/sysaux01.dbf";
   set newname for datafile  11 to
 "/u01/app/oracle/oradata/ORCLDR/orclpdb/undotbs01.dbf";
   set newname for datafile  12 to
 "/u01/app/oracle/oradata/ORCLDR/orclpdb/users01.dbf";
   restore
   from  nonsparse   from service
 'orcldc'   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/app/oracle/oradata/ORCLDR/temp01.dbf in control file
renamed tempfile 2 to /u01/app/oracle/oradata/ORCLDR/pdbseed/temp012025-01-22_00-17-43-687-AM.dbf in control file
renamed tempfile 3 to /u01/app/oracle/oradata/ORCLDR/orclpdb/temp01.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

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

Starting restore at 12-JAN-25
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 orcldc
channel ORA_AUX_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_AUX_DISK_1: restoring datafile 00001 to /u01/app/oracle/oradata/ORCLDR/system01.dbf
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:56
channel ORA_AUX_DISK_1: starting datafile backup set restore
channel ORA_AUX_DISK_1: using network backup set from service orcldc
channel ORA_AUX_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_AUX_DISK_1: restoring datafile 00003 to /u01/app/oracle/oradata/ORCLDR/sysaux01.dbf
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:35
channel ORA_AUX_DISK_1: starting datafile backup set restore
channel ORA_AUX_DISK_1: using network backup set from service orcldc
channel ORA_AUX_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_AUX_DISK_1: restoring datafile 00004 to /u01/app/oracle/oradata/ORCLDR/undotbs01.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 orcldc
channel ORA_AUX_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_AUX_DISK_1: restoring datafile 00005 to /u01/app/oracle/oradata/ORCLDR/pdbseed/system01.dbf
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:36
channel ORA_AUX_DISK_1: starting datafile backup set restore
channel ORA_AUX_DISK_1: using network backup set from service orcldc
channel ORA_AUX_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_AUX_DISK_1: restoring datafile 00006 to /u01/app/oracle/oradata/ORCLDR/pdbseed/sysaux01.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 orcldc
channel ORA_AUX_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_AUX_DISK_1: restoring datafile 00007 to /u01/app/oracle/oradata/ORCLDR/users01.dbf
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:02
channel ORA_AUX_DISK_1: starting datafile backup set restore
channel ORA_AUX_DISK_1: using network backup set from service orcldc
channel ORA_AUX_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_AUX_DISK_1: restoring datafile 00008 to /u01/app/oracle/oradata/ORCLDR/pdbseed/undotbs01.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 orcldc
channel ORA_AUX_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_AUX_DISK_1: restoring datafile 00009 to /u01/app/oracle/oradata/ORCLDR/orclpdb/system01.dbf
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:36
channel ORA_AUX_DISK_1: starting datafile backup set restore
channel ORA_AUX_DISK_1: using network backup set from service orcldc
channel ORA_AUX_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_AUX_DISK_1: restoring datafile 00010 to /u01/app/oracle/oradata/ORCLDR/orclpdb/sysaux01.dbf
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:26
channel ORA_AUX_DISK_1: starting datafile backup set restore
channel ORA_AUX_DISK_1: using network backup set from service orcldc
channel ORA_AUX_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_AUX_DISK_1: restoring datafile 00011 to /u01/app/oracle/oradata/ORCLDR/orclpdb/undotbs01.dbf
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:08
channel ORA_AUX_DISK_1: starting datafile backup set restore
channel ORA_AUX_DISK_1: using network backup set from service orcldc
channel ORA_AUX_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_AUX_DISK_1: restoring datafile 00012 to /u01/app/oracle/oradata/ORCLDR/orclpdb/users01.dbf
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:01
Finished restore at 12-JAN-25

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=1200947919 file name=/u01/app/oracle/oradata/ORCLDR/system01.dbf
datafile 3 switched to datafile copy
input datafile copy RECID=5 STAMP=1200947919 file name=/u01/app/oracle/oradata/ORCLDR/sysaux01.dbf
datafile 4 switched to datafile copy
input datafile copy RECID=6 STAMP=1200947919 file name=/u01/app/oracle/oradata/ORCLDR/undotbs01.dbf
datafile 5 switched to datafile copy
input datafile copy RECID=7 STAMP=1200947919 file name=/u01/app/oracle/oradata/ORCLDR/pdbseed/system01.dbf
datafile 6 switched to datafile copy
input datafile copy RECID=8 STAMP=1200947919 file name=/u01/app/oracle/oradata/ORCLDR/pdbseed/sysaux01.dbf
datafile 7 switched to datafile copy
input datafile copy RECID=9 STAMP=1200947919 file name=/u01/app/oracle/oradata/ORCLDR/users01.dbf
datafile 8 switched to datafile copy
input datafile copy RECID=10 STAMP=1200947919 file name=/u01/app/oracle/oradata/ORCLDR/pdbseed/undotbs01.dbf
datafile 9 switched to datafile copy
input datafile copy RECID=11 STAMP=1200947919 file name=/u01/app/oracle/oradata/ORCLDR/orclpdb/system01.dbf
datafile 10 switched to datafile copy
input datafile copy RECID=12 STAMP=1200947919 file name=/u01/app/oracle/oradata/ORCLDR/orclpdb/sysaux01.dbf
datafile 11 switched to datafile copy
input datafile copy RECID=13 STAMP=1200947919 file name=/u01/app/oracle/oradata/ORCLDR/orclpdb/undotbs01.dbf
datafile 12 switched to datafile copy
input datafile copy RECID=14 STAMP=1200947919 file name=/u01/app/oracle/oradata/ORCLDR/orclpdb/users01.dbf
Finished Duplicate Db at 12-JAN-25

RMAN>
RMAN> exit
Recovery Manager complete.
9. Now start real time apply
Start log shipment on Primary:

SQL> alter system set log_archive_dest_state_2= ENABLE scope=both;

SQL> show parameter log_archive_dest_state_2;

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
log_archive_dest_state_2             string      ENABLE
log_archive_dest_state_20            string      enable
log_archive_dest_state_21            string      enable
log_archive_dest_state_22            string      enable
log_archive_dest_state_23            string      enable
log_archive_dest_state_24            string      enable
log_archive_dest_state_25            string      enable
log_archive_dest_state_26            string      enable
log_archive_dest_state_27            string      enable
log_archive_dest_state_28            string      enable
log_archive_dest_state_29            string      enable

SQL> set lines 200 pages 1000
SQL> col DEST_NAME for a20
SQL> col DESTINATION for a20
SQL> col error for a30

SQL> select DEST_ID, DEST_NAME, STATUS, ERROR from v$archive_dest where DEST_NAME='LOG_ARCHIVE_DEST_2';

DEST_ID DEST_NAME            STATUS    ERROR
------- -------------------- --------- ------------------------------
      2 LOG_ARCHIVE_DEST_2   VALID     

SQL> select DEST_ID, DEST_NAME, DESTINATION, STATUS, ERROR 
     from v$archive_dest_status where status not in ('INVALID','INACTIVE');

DEST_ID DEST_NAME            DESTINATION          STATUS    ERROR
------- -------------------- -------------------- --------- ------------------------------
      1 LOG_ARCHIVE_DEST_1   /u01/app/oracle/product/19.0.0/dbhome_1/dbs/arch VALID     
      2 LOG_ARCHIVE_DEST_2   ORCLDR               VALID
Start Apply At Standby:

SQL> alter database recover managed standby database disconnect from session;
SQL> select process,status,thread#,sequence#,block# from v$managed_standby where process like '%MRP%';

PROCESS   STATUS          THREAD#  SEQUENCE#     BLOCK#
--------- ------------ ---------- ---------- ----------
MRP0      APPLYING_LOG          1         26        903
Now do some log switches on Primary and check the sync.


==> On Primary:


SQL> alter system switch logfile;
System altered.
SQL> /
System altered.
SQL> /
System altered.
SQL> set lines 200 pages 300
alter session set nls_date_format= 'DD-MON-YYYY HH24:MI:SS';
select d.db_unique_name, a.thread#, b.last_seq, a.applied_seq, a.last_app_timestamp, b.last_seq - a.applied_seq ARC_DIFF
FROM
(select thread#, MAX(sequence#) applied_seq, MAX(next_time) last_app_timestamp from gv$archived_log where applied='YES' group by thread#) a,
(select thread#, MAX(sequence#) last_seq from gv$archived_log group by thread#) b,
(select db_unique_name from v$database) d where a.thread#=b.thread#;
Session altered.

DB_UNIQUE_NAME                    THREAD#   LAST_SEQ APPLIED_SEQ LAST_APP_TIMESTAMP     ARC_DIFF
------------------------------ ---------- ---------- ----------- -------------------- ----------
ORCLDC                                  1         29          27 22-JAN-2025 12:57:12          2


==> On Standby:


SQL> select d.db_unique_name, a.thread#, b.last_seq, a.applied_seq, b.last_seq - a.applied_seq ARC_DIFF, a.last_app_timestamp,
round((sysdate - a.last_app_timestamp)*24*60,2) Gap_in_Mins, round((sysdate - a.last_app_timestamp)*24*60*60,2) Gap_in_Seconds
FROM
(select thread#, MAX(sequence#) applied_seq, MAX(next_time) last_app_timestamp from v$archived_log where REGISTRAR='RFS' and applied='YES' group by thread#) a,
(select thread#, MAX(sequence#) last_seq from gv$archived_log group by thread#) b,
(select db_unique_name from v$database) d where a.thread#=b.thread#;

DB_UNIQUE_NAME                    THREAD#   LAST_SEQ APPLIED_SEQ   ARC_DIFF LAST_APP_TIMESTAMP   GAP_IN_MINS GAP_IN_SECONDS
------------------------------ ---------- ---------- ----------- ---------- -------------------- ----------- --------------
ORCLDR                                  1         29          29          0 22-JAN-2025 12:57:18        3.78            227
10. Create tablespace on Primary and check on Standby 


==> On Primary:


SQL> show pdbs
    CON_ID CON_NAME                       OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
         2 PDB$SEED                       READ ONLY  NO
         3 ORCLPDB                        READ WRITE NO

SQL> alter session set container=ORCLPDB;
Session altered.

SQL> create tablespace APPDATA datafile '/u01/app/oracle/oradata/ORCL/orclpdb/appdata01.dbf' size 1g;
Tablespace created.


==> On Standby:


SQL> show pdbs
    CON_ID CON_NAME                       OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
         2 PDB$SEED                       MOUNTED
         3 ORCLPDB                        MOUNTED

SQL> alter session set container=ORCLPDB;
Session altered.

SQL> select name from v$datafile;
NAME
----------------------------------------------------------------------
/u01/app/oracle/oradata/ORCLDR/orclpdb/system01.dbf
/u01/app/oracle/oradata/ORCLDR/orclpdb/sysaux01.dbf
/u01/app/oracle/oradata/ORCLDR/orclpdb/undotbs01.dbf
/u01/app/oracle/oradata/ORCLDR/orclpdb/users01.dbf
/u01/app/oracle/oradata/ORCLDR/orclpdb/appdata01.dbf

SQL> select name from v$tablespace;
NAME
----------------------------------------------------------------------
SYSTEM
SYSAUX
UNDOTBS1
TEMP
USERS
APPDATA

6 rows selected.


Thank you for reading!

I hope this content has been helpful to you. Your feedback and suggestions are always welcome — feel free to leave a comment or reach out with any queries.

Abhishek Shrivastava

📧 Email: oraeasyy@gmail.com
🌐 Website: www.oraeasy.com

Follow Us

Location: Noida, Uttar Pradesh, India

Comments

Post a Comment

↑