Restore/Recover datafile In Standby From Service In 12c/19c

This tutorial explains the steps to restore and recover a datafile lost in an Oracle standby database. We'll walk through the process step by step using a practical example.

Environment Details

• Production Database (db_unique_name): DHCP
• Standby Database (db_unique_name): DHCPDR

Step 1: Start the Standby Database in MOUNT State

$ sqlplus / as sysdba
Recovery Manager: Release 19.0.0.0.0 - Production on Tue Jan 28 15:40:58 2025
Version 19.16.0.0.0
Copyright (c) 1982, 2019, Oracle and/or its affiliates. All rights reserved.
Connected to an idle instance.
SQL> startup mount
ORACLE instance started.
Total System Global Area 1073737776 bytes
Fixed Size 8933424 bytes
Variable Size 926941184 bytes
Database Buffers 130023424 bytes
Redo Buffers 7839744 bytes
Database mounted.

Step 2: Verify the Standby Database Status

SQL> select name,database_role,open_mode,log_mode from v$database;
NAME DATABASE_ROLE OPEN_MODE LOG_MODE
--------- --------------------- ------------- ---------------
DHCP PHYSICAL STANDBY MOUNTED ARCHIVELOG

Step 3: Connect to RMAN and Restore the Datafile

ora19c@DHCPDR:~$ rman target /
Recovery Manager: Release 19.0.0.0.0 - Production on Tue Jan 28 15:48:58 2025
Version 19.16.0.0.0
Copyright (c) 1982, 2019, Oracle and/or its affiliates. All rights reserved.
connected to target database: DHCP (DBID=1881015276, not open) RMAN> restore datafile 53 from service DHCP;
Starting restore at 28-JAN-25
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=392 device type=DISK
allocated channel: ORA_DISK_2
channel ORA_DISK_2: SID=2 device type=DISK
allocated channel: ORA_DISK_3
channel ORA_DISK_3: SID=237 device type=DISK
allocated channel: ORA_DISK_4
channel ORA_DISK_4: SID=276 device type=DISK
channel ORA_DISK_1: starting datafile backup set restore
channel ORA_DISK_1: using network backup set from service DHCP
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_DISK_1: restoring datafile 0053 to /sapdata2/DHCP/datafiles/sr_datafile_53.dbf
channel ORA_DISK_1: restore complete, elapsed time: 01:44:06
Finished restore at 28-JAN-25
Recovery Manager complete.

Important Notes:

• Ensure the standby database is in MOUNT state before starting the restore
• Verify the database role and status before proceeding
• Make sure the service name is correctly specified in the RMAN restore command
• Monitor the restore progress and verify completion

The restore process successfully completed, and the datafile has been restored to its original location. The elapsed time for this restore was approximately 1 minutes and 44 seconds.

Please feel free to ask any questions in the comments below. 🙂
Toufique Khan