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 third guide in our standby database setup series. We've already covered the manual RMAN backup and restore method and the RMAN Duplicate for standby method. This guide covers a third approach: restoring the standby directly from the primary's service using individual RMAN restore ... from service commands, rather than one combined DUPLICATE statement. Like RMAN Duplicate, this streams data live over the network from the primary rather than through staged backup files, so it's best suited for smaller databases with good network bandwidth between primary and standby. The difference is that this method breaks the restore into separate, explicit RMAN steps rather than a single automated command, giving you more visibility into (and control over) each stage.
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 this method restores 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
As with the other methods in this series, the primary needs force logging, ARCHIVELOG mode, and the standard redo transport parameters in place before we restore anything. Start by checking the current state.
[oracle@source ~]$ sqlplus / as sysdba
SQL> def
DEFINE _DATE = "18-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, 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 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.
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, needed once this database's role switches to standby. 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
As with RMAN Duplicate, this method doesn't need a full RMAN backup, just the pfile and password file, since both restore steps later pull datafiles live from the primary's service.
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 18 11:28 initorcldc.ora
-rw-r-----. 1 oracle oinstall 2.0K Jan 18 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. 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 18 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 18-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 18 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 = "18-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: Restore the Standby Control File from the Primary's Service
This is the first point where this method's approach becomes visible: rather than a combined DUPLICATE command, we run restore standby controlfile from service explicitly, then mount the database as a separate step.
[oracle@target ~]$ rman target /
Recovery Manager: Release 19.0.0.0.0 - Production on Sun Jan 18 11:28:39 2025
Version 19.24.0.0.0
Copyright (c) 1982, 2019, Oracle and/or its affiliates. All rights reserved.
connected to target database: ORCL (not mounted)
RMAN>
RMAN> restore standby controlfile from service 'orcldc';
restore standby controlfile from service 'orcldc';
Starting restore at 18-JAN-25
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=21 device type=DISK
channel ORA_DISK_1: starting datafile backup set restore
channel ORA_DISK_1: using network backup set from service orcldc
channel ORA_DISK_1: restoring control file
channel ORA_DISK_1: restore complete, elapsed time: 00:00:04
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 18-JAN-25
RMAN>
RMAN> alter database mount;
alter database mount;
released channel: ORA_DISK_1
Statement processed
RMAN>
Step 9: Restore the Database from the Primary's Service
With the standby control file in place and the database mounted, this second explicit step restores every datafile directly from the primary over the network.
RMAN> run
{
restore database from service 'orcldc';
}run
2> {
3> restore database from service 'orcldc';
4>
}
Starting restore at 18-JAN-25
Starting implicit crosscheck backup at 18-JAN-25
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=21 device type=DISK
Crosschecked 27 objects
Finished implicit crosscheck backup at 18-JAN-25
Starting implicit crosscheck copy at 18-JAN-25
using channel ORA_DISK_1
Finished implicit crosscheck copy at 18-JAN-25
searching for all files in the recovery area
cataloging files...
no files cataloged
using channel ORA_DISK_1
channel ORA_DISK_1: starting datafile backup set restore
channel ORA_DISK_1: using network backup set from service orcldc
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_DISK_1: restoring datafile 00001 to /u01/app/oracle/oradata/ORCLDR/system01.dbf
channel ORA_DISK_1: restore complete, elapsed time: 00:00:55
channel ORA_DISK_1: starting datafile backup set restore
channel ORA_DISK_1: using network backup set from service orcldc
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_DISK_1: restoring datafile 00003 to /u01/app/oracle/oradata/ORCLDR/sysaux01.dbf
channel ORA_DISK_1: restore complete, elapsed time: 00:00:46
...(remaining per-datafile restore output omitted for brevity)...
channel ORA_DISK_1: starting datafile backup set restore
channel ORA_DISK_1: using network backup set from service orcldc
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_DISK_1: restoring datafile 00012 to /u01/app/oracle/oradata/ORCLDR/orclpdb/users01.dbf
channel ORA_DISK_1: restore complete, elapsed time: 00:00:01
Finished restore at 18-JAN-25
RMAN>
RMAN> report schema;
report schema;
RMAN-06139: warning: control file is not current for REPORT SCHEMA
Report of database schema for database with db_unique_name ORCLDR
List of Permanent Datafiles
===========================
File Size(MB) Tablespace RB segs Datafile Name
---- -------- -------------------- ------- ------------------------
1 1320 SYSTEM *** /u01/app/oracle/oradata/ORCLDR/system01.dbf
3 950 SYSAUX *** /u01/app/oracle/oradata/ORCLDR/sysaux01.dbf
4 710 UNDOTBS1 *** /u01/app/oracle/oradata/ORCLDR/undotbs01.dbf
5 650 PDB$SEED:SYSTEM *** /u01/app/oracle/oradata/ORCLDR/pdbseed/system01.dbf
6 450 PDB$SEED:SYSAUX *** /u01/app/oracle/oradata/ORCLDR/pdbseed/sysaux01.dbf
7 5 USERS *** /u01/app/oracle/oradata/ORCLDR/users01.dbf
8 230 PDB$SEED:UNDOTBS1 *** /u01/app/oracle/oradata/ORCLDR/pdbseed/undotbs01.dbf
9 660 ORCLPDB:SYSTEM *** /u01/app/oracle/oradata/ORCLDR/orclpdb/system01.dbf
10 520 ORCLPDB:SYSAUX *** /u01/app/oracle/oradata/ORCLDR/orclpdb/sysaux01.dbf
11 230 ORCLPDB:UNDOTBS1 *** /u01/app/oracle/oradata/ORCLDR/orclpdb/undotbs01.dbf
12 5 ORCLPDB:USERS *** /u01/app/oracle/oradata/ORCLDR/orclpdb/users01.dbf
List of Temporary Files
=======================
File Size(MB) Tablespace Maxsize(MB) Tempfile Name
---- -------- -------------------- ----------- --------------------
1 20 TEMP 32767 /u01/app/oracle/oradata/ORCLDR/temp01.dbf
2 36 PDB$SEED:TEMP 32767 /u01/app/oracle/oradata/ORCLDR/pdbseed/temp012025-01-22_00-17-43-687-AM.dbf
3 174 ORCLPDB:TEMP 32767 /u01/app/oracle/oradata/ORCLDR/orclpdb/temp01.dbf
RMAN>
RMAN> exit
Recovery Manager complete.
[oracle@target ~]$
Notice this required two separate explicit steps, restoring the control file, then restoring the database, compared to RMAN Duplicate's single combined command. That's the practical tradeoff of this method: more visible steps, easier to troubleshoot if something goes wrong partway through, at the cost of a couple more commands to run.
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
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 18-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 18-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
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 automatically confirms Data Guard is correctly shipping and applying redo in real time.
How This Compares to the Other Methods
Across the series so far: the manual RMAN backup and restore method gives full control and works well without live network access to the primary during restore. RMAN Duplicate compresses everything into one command, fastest to run but hardest to inspect mid-process. This method sits in between, still streaming live from the primary like Duplicate, but broken into explicit, individually inspectable steps, useful if you want the speed of a network restore without losing visibility into exactly what's happening at each stage.
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