Restore/Recover datafile from Standby without database catalog (19c)

This tutorial explains the steps to restore and recover a datafile lost from an Oracle standby datafiles.

• Production Server:
  • Hostname: wadodb1
  • Connection name: wadodb
  • db_unique_name: wadodb

• Standby Server:
  • Hostname: wadodb2
  • Connection name: wadodbsb
  • db_unique_name: wadodbsb

Problem:

$ sqlplus / as sysdba
 SQL*Plus: Release 19.0.0.0.0 - Production on Fri May 19 06:35:54 2023
 Version 19.19.0.0.0
 Copyright (c) 1982, 2022, Oracle.  All rights reserved.
 Connected to an idle instance.
 SQL> startup
 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.
 ORA-01157: cannot identify/lock data file 3 - see DBWR trace file
 ORA-01110: data file 3: '/data/wadodb/sysaux01.dbf'

Note: We don’t have backup in production:

Check if we have a backup of the datafile number 3.

RMAN>  list backup of datafile 3;
 specification does not match any backup in the repository
 RMAN>

Step 1 (Solution):

Connect to the Standby as primary connection and to the primary as auxiliary connection, and make a backup of the datafile 3.

$ rman target sys/PASSWORD@wadodbsb auxiliary sys/PASSWORD@wadodb
Recovery Manager: Release 19.0.0.0.0 - Production on Fri May 19 06:45:21 2023
 Version 19.19.0.0.0
 Copyright (c) 1982, 2019, Oracle and/or its affiliates.  All rights reserved.
 connected to target database: WADODB (DBID=1714169206)
 connected to auxiliary database: WADODB (DBID=1714169206, not open)
 RMAN>
 RMAN> backup as copy datafile 3 auxiliary format '/home/oracle/backup/sysaux01.dbf';
 Starting backup at 19-MAY-23
 using target database control file instead of recovery catalog
 allocated channel: ORA_DISK_1
 channel ORA_DISK_1: SID=427 device type=DISK
 channel ORA_DISK_1: starting datafile copy
 input datafile file number=00003 name=/data/wadodb/WADODBSB/datafile/o1_mf_sysaux_l601jxjc_.dbf
 output file name=/home/oracle/backup/sysaux01.dbf tag=TAG20230519T064828
 channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:07
 Finished backup at 19-MAY-23
 RMAN>

Step 2: (Solution)

Connect to the primary database, catalog the backup of the datafile as copy, restore and recover the datafile.

$ rman target sys/oracle_4U@wadodb
 Recovery Manager: Release 19.0.0.0.0 - Production on Fri May 19 06:49:03 2023
 Version 19.19.0.0.0
 Copyright (c) 1982, 2019, Oracle and/or its affiliates.  All rights reserved.
 connected to target database: WADODB (DBID=1714169206, not open)
 RMAN> catalog datafilecopy '/home/oracle/backup/sysaux01.dbf';
 using target database control file instead of recovery catalog
 cataloged datafile copy
 datafile copy file name=/home/oracle/backup/sysaux01.dbf RECID=1 STAMP=1137221360
 RMAN> restore datafile 3;
 Starting restore at 19-MAY-23
 using channel ORA_DISK_1
 channel ORA_DISK_1: restoring datafile 00003
 input datafile copy RECID=1 STAMP=1137221360 file name=/home/oracle/backup/sysaux01.dbf
 destination for restore of datafile 00003: /data/wadodb/sysaux01.dbf
 channel ORA_DISK_1: copied datafile copy of datafile 00003, elapsed time: 00:00:03
 output file name=/data/wadodb/sysaux01.dbf RECID=0 STAMP=0
 Finished restore at 19-MAY-23
 RMAN> recover datafile 3;
 Starting recover at 19-MAY-23
 using channel ORA_DISK_1
 starting media recovery
 media recovery complete, elapsed time: 00:00:01
 Finished recover at 19-MAY-23

Step 3: (Solution)

Open the database

RMAN> alter database open;
 Statement processed

Enjoy!