Recovering Data File in Primary Database from the standby database.
You have two approaches for data file restoration when your data file gets corrupted or deleted in data guard environment.
- Restore from RMAN backup
- Recover it from Standby Database.
In our routine, we use the first approach where we simply restore it using RMAN restore command and recover it through incremental backup or archive logs.
In the second approach, we will restore data file with help of Physical Standby database.
In this article, we will see steps for recovering data file in Primary Database using Standby database approach.
Approach 1: Restore From RMAN backup
We can simply restore it using RMAN restore and recover command.
RESTORE DATAFILE; RECOVER DATAFILE ;
Approach 2: Recover it from Standby Database :
Let’s simulate this case :
Step 1: Create the tablespace
SQL> create tablespace test datafile '/u01/oracle/oradata/dbupgrade/test01.dbf' size 5m; Tablespace created.
Step 2 : Create the table in test tablespace
SQL> create table a (no number) tablespace test; Table created.
add some records to a table.
Step 3: Manually delete the physical file of this tablespace :
[oracle@localhost dbupgrade]$ rm -rfv test01.dbf removed `test01.dbf'
Step 4: Connect with RMAN using the target as Standby and Auxiliary as Primary :
[oracle@localhost dbupgrade]$ rman target sys/oracle@std_dbupgrade auxiliary sys/oracle@dbupgrade Recovery Manager: Release 12.2.0.1.0 - Production on Thu Jul 12 09:36:43 2018 Copyright (c) 1982, 2017, Oracle and/or its affiliates. All rights reserved. connected to target database: DBUPGRAD (DBID=1987568604) connected to auxiliary database: DBUPGRAD (DBID=1987568604)
Step 5: Backup that datafile using the following command.
RMAN> BACKUP AS COPY DATAFILE 5 AUXILIARY FORMAT '/u01/test01.dbf'; Starting backup at 12-JUL-18 using target database control file instead of recovery catalog allocated channel: ORA_DISK_1 channel ORA_DISK_1: SID=66 device type=DISK channel ORA_DISK_1: starting datafile copy input datafile file number=00005 name=/u01/oracle/oradata/dbupgrade/test01.dbf output file name=/u01/test01.dbf tag=TAG20180712T093756 channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:01 Finished backup at 12-JUL-18
Step 6: Now connect primary as target and catalog as a catalog database.
Note: You need catalog database for this approach.
[oracle@localhost admin]$ rman catalog rcatown/rcatown@std_upgdb Recovery Manager: Release 12.2.0.1.0 - Production on Thu Jul 12 10:35:21 2018 Copyright (c) 1982, 2017, Oracle and/or its affiliates. All rights reserved. connected to recovery catalog database RMAN> connect target sys/oracle@std_dbupgrade; connected to target database: DBUPGRAD (DBID=1987568604, not open)
Step 7: Catalog datafile backup
RMAN> catalog datafilecopy '/u01/test01.dbf';
Step 8: Run the following script to set newname for datafile and then switch it to the new name and then recover datafile.
RMAN> run{
set newname for datafile 5 to '/u01/test01.dbf';
switch datafile 5;
}2> 3> 4>
executing command: SET NEWNAME
datafile 5 switched to datafile copy
input datafile copy RECID=11 STAMP=981282541 file name=/u01/test01.dbf
starting full resync of recovery catalog
full resync complete
RMAN> recover datafile 5;
Starting recover at 12-JUL-18
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=44 device type=DISK
starting media recovery
media recovery complete, elapsed time: 00:00:02
Finished recover at 12-JUL-18
RMAN>
Stay tuned for More articles on Oracle DataGuard
Thank you for giving your valuable time to read the above information.
If you want to be updated with all our articles send us the Invitation or Follow us:
Telegram Channel: https://t.me/helporacle
Skant Gupta’s LinkedIn: www.linkedin.com/in/skantali/
Joel Perez’s LinkedIn: Joel Perez’s Profile
LinkedIn Group: Oracle Cloud DBAAS
Facebook Page: OracleHelp
