oracledna RAC April 20, 2025 Recover the lost datafile of physical standby database using a copy of a primary database’s datafile This post is to demonstrate how to recover lost (at OS level) datafile of physical standby database using a copy of datafile of primary database in 19c (19.3.0.0.0). Before replacing the lost datafile with the copy of Production datafile, please make sure to have the necessary archive log files which are needed for the recovery of the lost datafile. Ensure to check that source (Primary DB) datafile is corruption free. Run DBV and RMAN validate to check for corruption.   On Primary 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/PROD/users01.dbf blocksize=8192 logfile=/home/oracle/dbv.log [oracle@prod ~]$ dbv file= /u01/app/oracle/oradata/PROD/users01.dbf blocksize=8192 logfile=/home/oracle/dbv.log DBVERIFY: Release 19.0.0.0.0 – Production on Sun Apr 20 17:01:02 2025 Copyright (c) 1982, 2019, Oracle and/or its affiliates.  All rights reserved. Also, validate the datafile with RMAN RMAN> backup validate check logical datafile 7; Starting backup at 20-APR-2025 17:07:24 using target database control file instead of recovery catalog allocated channel: ORA_DISK_1 channel ORA_DISK_1: SID=51 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/PROD/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/PROD/users01.dbf   Block Type Blocks Failing Blocks Processed   ———- ————– —————-   Data       0              60   Index      0              15   Other      0              464 Finished backup at 20-APR-2025 17:07:26     1. Take the backup of Primary database datafile RMAN> backup datafile 7 format ‘/tmp/users_df7_prim.bkp’; Starting backup at 20-APR-2025 17:14:30 using channel ORA_DISK_1 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/PROD/users01.dbf channel ORA_DISK_1: starting piece 1 at 20-APR-2025 17:14:30 channel ORA_DISK_1: finished piece 1 at 20-APR-2025 17:14:31 piece handle=/tmp/users_df7_prim.bkp tag=TAG20250420T171430 comment=NONE channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01 Finished backup at 20-APR-2025 17:14:31 Starting Control File and SPFILE Autobackup at 20-APR-2025 17:14:31 piece handle=/u01/app/oracle/product/19.0.0/db_1/dbs/c-608515774-20250420-00 comment=NONE Finished Control File and SPFILE Autobackup at 20-APR-2025 17:14:33     2. Transfer the backup pieces to standby server     3. In Standby Server, catalog the backup piece RMAN> catalog backuppiece ‘/tmp/users_df7_prim.bkp’; channel default: cataloged backup piece backup piece handle=/tmp/users_df7_prim.bkp RECID=2 STAMP=1198949299 RMAN> list backuppiece ‘/tmp/users_df7_prim.bkp’; List of Backup Pieces BP Key  BS Key  Pc# Cp# Status      Device Type Piece Name ——- ——- — — ———– ———– ———- 2       2       1   1   AVAILABLE   DISK        /tmp/users_df7_prim.bkp RMAN> list backup of datafile 7; List of Backup Sets =================== BS Key  Type LV Size       Device Type Elapsed Time Completion Time ——- —- — ———- ———– ———— ——————– 2       Full    2.38M      DISK        00:00:00     20-APR-2025 17:14:30         BP Key: 2   Status: AVAILABLE  Compressed: NO  Tag: TAG20250420T171430         Piece Name: /tmp/users_df7_prim.bkp   List of Datafiles in backup set 2   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/STDBY/users01.dbf     4. Stop the MRP and Standby database should be in mount state. In active data guard environment, restart the standby DB in mount state.             SQL> alter database recover managed standby database cancel;     5. On Standby DB, restore the datafile from backup piece RMAN> restore datafile 7; Starting restore at 20-APR-2025 17:41:47 using target database control file instead of recovery catalog allocated channel: ORA_DISK_1 channel ORA_DISK_1: SID=26 device type=DISK 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/STDBY/users01.dbf channel ORA_DISK_1: reading from backup piece /tmp/users_df7_prim.bkp channel ORA_DISK_1: piece handle=/tmp/users_df7_prim.bkp tag=TAG20250420T171430 channel ORA_DISK_1: restored backup piece 1 channel ORA_DISK_1: restore complete, elapsed time: 00:00:01 Finished restore at 20-APR-2025 17:41:49     6. Start the MRP on the physical standby database and check the archive sync. SQL> alter database recover managed standby database disconnect from session; Database altered.     Disclaimer: ========== Please note the above information is only for educational purpose and practised in personal test database only. Always test in test database before implementing in production database. The pre-requisites and ways of implementing may vary from one environment to another. Hence, not providing guarantee that it will work in your environment. Popular Posts Recover the lost datafile of physical standby database using a copy of a primary database's datafile Migrate Oracle Database from ASM to Non-ASM Manual GI/DB Patching on Oracle Restart- 19.3 to 19.18 Migrate Oracle database from Non-ASM to ASM Categories ASM (2) Patching (1) RAC (1)