oraclednaThis post is to demonstrate how to recover lost (at OS level) datafile of primary database using a copy of datafile of physical standby database in 19c (19.3.0.0.0).
Datafile 7 of primary database was lost and need to restore and recover the datafile 7 from physical standby database. Below errors found in alert log of primary database.
Errors in file /u01/app/oracle/diag/rdbms/prod/prod/trace/prod_mz00_3779.trc:
ORA-01110: data file 7: ‘/u01/app/oracle/oradata/PROD/users01.dbf’
ORA-01565: error in identifying file ‘/u01/app/oracle/oradata/PROD/users01.dbf’
ORA-27037: unable to obtain file status
Linux-x86_64 Error: 2: No such file or directory
Additional information: 7
Checker run found 1 new persistent data failures
1. This is important step.
1.a). Before replacing the lost datafile with the copy of Standby datafile, please make sure to have the necessary archive log files which are needed for the recovery of the lost datafile.
1.b). Ensure to check that source (Standby DB) datafile is corruption free. Run DBV and RMAN validate to check for corruption.
On Standby Server, verify whether the source datafile is corruption free not by dbv utility, dbv must return with zero corrupted pages.
dbv file= /u01/app/oracle/oradata/STDBY/users01.dbf blocksize=8192 logfile=/home/oracle/dbv_stdby.log
[oracle@stdby ~]$ dbv file= /u01/app/oracle/oradata/STDBY/users01.dbf blocksize=8192 logfile=/home/oracle/dbv_stdby.log
DBVERIFY: Release 19.0.0.0.0 – Production on Mon Apr 21 10:14:24 2025
Copyright (c) 1982, 2019, Oracle and/or its affiliates. All rights reserved.
[oracle@stdby ~]$ cat dbv_stdby.log
DBVERIFY: Release 19.0.0.0.0 – Production on Mon Apr 21 10:14:24 2025
Copyright (c) 1982, 2019, Oracle and/or its affiliates. All rights reserved.
DBVERIFY – Verification starting : FILE = /u01/app/oracle/oradata/STDBY/users01.dbf
DBVERIFY – Verification complete
Total Pages Examined : 640
Total Pages Processed (Data) : 60
Total Pages Failing (Data) : 0
Total Pages Processed (Index): 15
Total Pages Failing (Index): 0
Total Pages Processed (Other): 464
Total Pages Processed (Seg) : 0
Total Pages Failing (Seg) : 0
Total Pages Empty : 101
Total Pages Marked Corrupt : 0
Total Pages Influx : 0
Total Pages Encrypted : 0
Highest block SCN : 1252497 (0.1252497)
Also, validate the datafile of Standby DB with RMAN.
RMAN> backup validate check logical datafile 7;
Starting backup at 21-APR-2025 10:17:14
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=69 device type=DISK
channel ORA_DISK_1: starting full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
input datafile file number=00007 name=/u01/app/oracle/oradata/STDBY/users01.dbf
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01
List of Datafiles
=================
File Status Marked Corrupt Empty Blocks Blocks Examined High SCN
—- —— ————– ———— ————— ———-
7 OK 0 101 641 1252497
File Name: /u01/app/oracle/oradata/STDBY/users01.dbf
Block Type Blocks Failing Blocks Processed
———- ————– —————-
Data 0 60
Index 0 15
Other 0 464
Finished backup at 21-APR-2025 10:17:17
2. Take the backup of physical standby database datafile
RMAN> backup datafile 7 format ‘/tmp/users_df7_stdby.bkp’;
Starting backup at 21-APR-2025 10:20:30
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=70 device type=DISK
channel ORA_DISK_1: starting full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
input datafile file number=00007 name=/u01/app/oracle/oradata/STDBY/users01.dbf
channel ORA_DISK_1: starting piece 1 at 21-APR-2025 10:20:31
channel ORA_DISK_1: finished piece 1 at 21-APR-2025 10:20:32
piece handle=/tmp/users_df7_stdby.bkp tag=TAG20250421T102031 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01
Finished backup at 21-APR-2025 10:20:32
Starting Control File and SPFILE Autobackup at 21-APR-2025 10:20:32
piece handle=/u01/app/oracle/product/19.0.0/db_1/dbs/c-608515774-20250421-01 comment=NONE
Finished Control File and SPFILE Autobackup at 21-APR-2025 10:20:33
3. Transfer the backup pieces to primary database server
[oracle@stdby tmp]$ scp users_df7_stdby.bkp oracle@XXXXX:/tmp/
The authenticity of host ‘XXX.XXX.XXX.XXX (XXX.XXX.XXX.XXX)’ can’t be established.
ECDSA key fingerprint is SHA256:ePPILxuIgt3vNdG38wHx5foswlo2au4IRCVAtPy6qFo.
ECDSA key fingerprint is MD5:a6:3c:4f:58:f2:b7:b7:7d:19:4f:14:bf:cb:89:bc:d2.
Are you sure you want to continue connecting (yes/no)? yes
Warning: Permanently added ‘XXX.XXX.XXX.XXX’ (ECDSA) to the list of known hosts.
oracle@XXX.XXX.XXX.XXX’s password:
users_df7_stdby.bkp 100% 2448KB 40.6MB/s 00:00
4. In primary DB Server, catalog the backuppiece and check the availability for use.
RMAN> catalog backuppiece ‘/tmp/users_df7_stdby.bkp’;
using target database control file instead of recovery catalog
channel default: cataloged backup piece
backup piece handle=/tmp/users_df7_stdby.bkp RECID=5 STAMP=1199010274
RMAN> list backuppiece ‘/tmp/users_df7_stdby.bkp’;
List of Backup Pieces
BP Key BS Key Pc# Cp# Status Device Type Piece Name
——- ——- — — ———– ———– ———-
5 5 1 1 AVAILABLE DISK /tmp/users_df7_stdby.bkp
RMAN> list backup of datafile 7;
List of Backup Sets
===================
BS Key Type LV Size Device Type Elapsed Time Completion Time
——- —- — ———- ———– ———— ——————–
3 Full 2.38M DISK 00:00:00 20-APR-2025 17:14:30
BP Key: 3 Status: AVAILABLE Compressed: NO Tag: TAG20250420T171430
Piece Name: /tmp/users_df7_prim.bkp
List of Datafiles in backup set 3
File LV Type Ckp SCN Ckp Time Abs Fuz SCN Sparse Name
—- — —- ———- ——————– ———– —— —-
7 Full 2287979 20-APR-2025 17:14:30 NO /u01/app/oracle/oradata/PROD/users01.dbf
BS Key Type LV Size Device Type Elapsed Time Completion Time
——- —- — ———- ———– ———— ——————–
5 Full 2.38M DISK 00:00:00 21-APR-2025 10:20:31
BP Key: 5 Status: AVAILABLE Compressed: NO Tag: TAG20250421T102031
Piece Name: /tmp/users_df7_stdby.bkp
List of Datafiles in backup set 5
File LV Type Ckp SCN Ckp Time Abs Fuz SCN Sparse Name
—- — —- ———- ——————– ———– —— —-
7 Full 2421714 21-APR-2025 10:02:55 NO /u01/app/oracle/oradata/PROD/users01.dbf
5. In primary DB, restore the datafile and recover the datafile.
SQL> alter database datafile 7 offline;
Database altered.
RMAN> restore datafile 7;
Starting restore at 21-APR-2025 10:30:13
using channel ORA_DISK_1
channel ORA_DISK_1: starting datafile backup set restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_DISK_1: restoring datafile 00007 to /u01/app/oracle/oradata/PROD/users01.dbf
channel ORA_DISK_1: reading from backup piece /tmp/users_df7_stdby.bkp
channel ORA_DISK_1: piece handle=/tmp/users_df7_stdby.bkp tag=TAG20250421T102031
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:01
Finished restore at 21-APR-2025 10:30:14
RMAN> recover datafile 7;
Starting recover at 21-APR-2025 10:32:18
using channel ORA_DISK_1
starting media recovery
media recovery complete, elapsed time: 00:00:00
Finished recover at 21-APR-2025 10:32:19
SQL> alter database datafile 7 online;
Database altered.
Disclaimer:
========
Please note the above information is only for educational purposes and is practiced in a personal test database only. Always test in a test database before implementing it in a production database. The pre-requisites and ways of implementing them may vary from one environment to another. Hence, not providing a guarantee that it will work in your environment.
