Recover lost datafile of primary database by using a copy of datafile of physical standby database oracledna Oracle Core Database April 21, 2025 This 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 And, check the DB block corruption, the below view must return zero rows SQL> select * from v$database_block_corruption; no rows selected 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