Restore Missing Datafile In Standby Database Oracle 12c

 

Restore Missing Datafile In Standby Database Oracle 12c

In oracle 12c, if datafile is missing is standby database, then it can be restored easily from primary database using RESTORE DATAFILE FROM SERVICE command. This is one of the new feature of dataguard 12c . 

DEMO:

PRIMDB – Primary database

STYDB – Standby database

 

Check status of primary and standby database:

--PRIMARY

 

SQL> select DB_UNIQUE_NAME,OPEN_MODE,DATABASE_ROLE from v$database;

 

DB_UNIQUE_NAME OPEN_MODE DATABASE_ROLE

------------------------------ -------------------- ----------------

PRIMDB READ WRITE PRIMARY

-- STANDBY

SQL> select name,OPEN_MODE,DATABASE_ROLE from v$database;

 

NAME OPEN_MODE DATABASE_ROLE

--------- -------------------- ----------------

STYDB READ ONLY WITH APPLY PHYSICAL STANDBY

 

Lets remove one file from standby database:[STYDB]:

 

SQL> select file_name from dba_data_files;

FILE_NAME

--------------------------------------------------------------------------------

/archive/PRIMDB/PRIMDB/system01.dbf

/archive/PRIMDB/PRIMDB/sysaux01.dbf

/archive/PRIMDB/PRIMDB/undotbs01.dbf

/archive/PRIMDB/PRIMDB/users01.dbf

mv /archive/PRIMDB/PRIMDB/users01.dbf /archive/PRIMDB/PRIMDB/users01.dbf_bkp

-- Try to query any table

SQL> select file_name from dba_data_files;

select file_name from dba_data_files

*

ERROR at line 1:

ORA-01116: error in opening database file 4

ORA-01110: data file 4: '/archive/PRIMDB/PRIMDB/users01.dbf'

ORA-27041: unable to open file

SVR4 Error: 2: No such file or directory

Additional information: 3

CHECK FROM RMAN: 

RMAN> list failure;

using target database control file instead of recovery catalog

Database Role: PHYSICAL STANDBY

List of Database Failures

=========================

Failure ID Priority Status Time Detected Summary

---------- -------- --------- ------------- -------

322 CRITICAL OPEN 18-OCT-16 One or more non-system datafiles are missing

SOLUTION:

Now follow below steps to restore the datafile from primary:

NOTE : All activities need to be done on standby database

1. CANCEL THE RECOVERY( STANDBY)

SQL> recover managed standby database cancel;

Media recovery complete.

2 . START STANDBY IN MOUNT STAGE( IF IN READ-ONLY MODE):

SQL> shutdown immediate;

Database closed.

Database dismounted.

ORACLE instance shut down.

SQL> startup mount

ORACLE instance started.

Total System Global Area 1.3935E+10 bytes

Fixed Size 6002112 bytes

Variable Size 4630514240 bytes

Database Buffers 9193914368 bytes

Redo Buffers 104153088 bytes

Database mounted.

3. RESTORE THE DATAFILE ( STANDBY)

Now connect to RMAN in standby and use restore command.

 

SYNTAX – RESTORE DATAFILE < FILE_ID> FROM SERVICE < PRIMARY DB SERVICE NAME >

rman target /

Recovery Manager: Release 12.1.0.1.0 - Production on Tue Oct 18 18:04:15 2016

Copyright (c) 1982, 2013, Oracle and/or its affiliates. All rights reserved.

connected to target database: STYDB (DBID=599956155, not open)

RMAN> restore datafile 4 from service PRIMDB;

Starting restore at 18-OCT-16

using target database control file instead of recovery catalog

allocated channel: ORA_DISK_1

channel ORA_DISK_1: SID=705 device type=DISK

channel ORA_DISK_1: starting datafile backup set restore

channel ORA_DISK_1: using network backup set from service PRIMDB

channel ORA_DISK_1: specifying datafile(s) to restore from backup set

channel ORA_DISK_1: restoring datafile 00004 to /archive/PRIMDB/PRIMDB/users01.dbf

channel ORA_DISK_1: restore complete, elapsed time: 00:00:01

Finished restore at 18-OCT-16

4. START THE RECOVERY ( STANDBY)

SQL> recover managed standby database using current logfile disconnect from session;

Media recovery complete.

5. CANCEL RECOVERY AND START DATABASE IN READ ONLY:

SQL> recover managed standby database cancel;

Media recovery complete.

SQL> shutdown immediate;

ORA-01109: database not open

Database dismounted.

ORACLE instance shut down.

SQL> startup

ORACLE instance started.

Total System Global Area 1.3935E+10 bytes

Fixed Size 6002112 bytes

Variable Size 4630514240 bytes

Database Buffers 9193914368 bytes

Redo Buffers 104153088 bytes

Database mounted.

Database opened.

6 . START THE RECOVERY AGAIN:

 

SQL> recover managed standby database using current logfile disconnect from session;

Media recovery complete.

Now all datafiles are online :

 

SQL> select file_name from dba_data_files;

FILE_NAME

--------------------------------------------------------------------------------

/archive/PRIMDB/PRIMDB/system01.dbf

/archive/PRIMDB/PRIMDB/sysaux01.dbf

/archive/PRIMDB/PRIMDB/undotbs01.dbf

/archive/PRIMDB/PRIMDB/users01.dbf