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!

Bookmark the permalink.
Loading Facebook Comments ...

Leave a Reply