oracledna
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).
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.
