Introduction
Oracle Data Guard (ODG) is a high-availability, disaster recovery, and data protection feature 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.
This is the second guide in our standby database setup series. We covered the manual RMAN backup and restore method in an earlier article. Here, we'll use the RMAN DUPLICATE ... FOR STANDBY FROM ACTIVE DATABASE command instead, a single statement that handles the backup, transfer, restore, and standby control file setup all in one step. This method is best suited for smaller databases, and it requires good network bandwidth between the primary and standby, since everything streams live over the network rather than through backup files staged on disk.
Prerequisites
- Primary server with Oracle Database software installed and a running database.
- Standby server with Oracle Database software installed.
- Network connectivity between the primary and standby servers.
Environment Used in This Guide
| 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 |
Step 1: Verify Connectivity Between Primary and Standby
Since DUPLICATE FROM ACTIVE DATABASE streams data live over the network, confirm both directions work before configuring anything else.
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
Step 2: Configure the Primary Database for Data Guard
Before duplicating, the primary needs the standard Data Guard prerequisites in place: force logging, ARCHIVELOG mode, and the various redo transport parameters. Start by checking the current state.
[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
SQL> col open_mode for a15
SQL> select name,database_role,open_mode,log_mode from v$database;
NAME DATABASE_ROLE OPEN_MODE LOG_MODE
-------- ---------------- --------------- ------------
ORCL PRIMARY READ WRITE ARCHIVELOG
SQL> select banner_full from v$version;
BANNER_FULL
--------------------------------------------------------------------------------
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 your database is in NOARCHIVELOG mode, switch it first:
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 make the Data Guard-specific changes on the primary:
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.
Set DB_FILE_NAME_CONVERT and LOG_FILE_NAME_CONVERT so file paths convert automatically between primary and standby, this matters since the two servers use different directory names here (ORCL vs. ORCLDR).
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.
Several parameters above used SCOPE=SPFILE, so bounce the database to apply them all.
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
Finally, add standby redo logs. These are used once this database's role switches to standby, so they need to exist now even though this server is currently the primary. The number of standby redo log groups should be one more than the number of online redo log groups.
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.
Step 3: Set Up TNS on the Primary
[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)
)
)
Step 4: Create the Pfile and Password File for Transfer
Unlike the manual backup and restore method, we don't need a full RMAN backup here, just the pfile and password file, since DUPLICATE FROM ACTIVE DATABASE pulls the actual datafiles live during the duplicate operation itself.
SQL> create pfile='/u01/app/oracle/rmandc/initorcldc.ora' from spfile;
[oracle@source rmandc]$ cd $ORACLE_HOME/dbs
[oracle@source dbs]$ cp orapworcldc /u01/app/oracle/rmandc/
Step 5: Transfer the Pfile and Password File to the 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
Step 6: Create the Standby Pfile
Modify a copy of the primary's pfile for the standby, adjusting the paths, unique name, and redo transport direction. Below are the key parameters that differ:
*.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'
Here's the full initorcldr.ora with these changes applied:
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 the 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 the password file into $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
Next, create a listener using NETCA, start it, and add TNS entries for both the primary and standby services. If you haven't set up a listener before, see our Listener Creation Using NETCA guide.
[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
Step 7: Start the Standby Instance in Nomount Mode
[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 ~]$
Step 8: Connect RMAN to the Primary and Standby
The primary is connected as the target, using its TNS alias, and the standby is connected as the auxiliary, using its own TNS alias.
[oracle@target ~]$ rman target sys/sys@orcldc auxiliary sys/sys@orcldr
Recovery Manager: Release 19.0.0.0.0 - Production on Mon Jan 12 12: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>
Step 9: Run the RMAN Duplicate for Standby Script
This is the core step. DUPLICATE TARGET DATABASE FOR STANDBY FROM ACTIVE DATABASE combines what would otherwise be several manual steps, backing up the password file, restoring a standby control file, restoring datafiles, and switching them into place, into one command. Because this database was already prepared with the Data Guard parameters in Step 2, RMAN automatically restores a proper standby control file rather than a regular one.
[oracle@target ~]$ rman target sys/sys@orcldc auxiliary sys/sys@orcldr
Recovery Manager: Release 19.0.0.0.0 - Production on Mon JAN 12 12: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
...(remaining internal RMAN memory script output omitted for brevity)...
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
...(remaining per-datafile restore output omitted for brevity)...
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
...(remaining per-datafile switch output omitted for brevity)...
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.
Unlike the plain duplication used for cloning a test database, notice this variant mounts the database explicitly as a standby (alter database mount standby database) and doesn't open it with RESETLOGS at the end, it stays in standby role, ready for managed recovery.
Step 10: Start Real-Time Apply
Enable log shipment on the primary:
SQL> alter system set log_archive_dest_state_2= ENABLE scope=both;
System altered.
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 the standby:
SQL> alter database recover managed standby database disconnect from session;
Database altered.
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
Switch a few logs on the 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
SQL> alter session set nls_date_format= 'DD-MON-YYYY HH24:MI:SS';
Session altered.
SQL> 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#;
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
ARC_DIFF shows 0, confirming the standby is fully synced.
Step 11: Verify Sync With a Real Change
As a final check, create a tablespace on the primary and confirm it automatically appears on the 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.
The new datafile and tablespace appearing on the standby without any manual intervention confirms Data Guard is correctly shipping and applying redo in real time. Compared to the manual backup and restore method, this approach compressed setup into far fewer explicit RMAN commands, at the cost of needing solid network bandwidth between the two servers for the entire duplication window.
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
Oracle DBA with hands-on experience managing production Data Guard, RAC, GoldenGate, and APEX environments. I write practical, tested installation and troubleshooting guides based on real deployment work.
Comments
Post a Comment