Skip to content

Restore datafiles/database from Standby

September 23, 2013

Restore datafiles/database from Standby

Primary DB Name: d041

Standby DB Name: d041dr

In case of Loss of datafile (in d041), restore the datafile quickly from standby (d041dr) instead of Tape backups, without much wasting of time.

PRIMARY/PROD

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

Let’s add some data to table T1:

SQL> insert into mtest.t1 (nam) values (‘helo’);

1 row created.

SQL> commit;

Commit complete.

SQL>  alter system switch logfile;

System altered.

SQL>  alter system switch logfile;

System altered.

SQL>  alter system switch logfile;

System altered.

SQL> select TABLE_NAME,TABLESPACE_NAME from dba_tables where TABLESPACE_NAME='MDATA';
TABLE_NAME                     TABLESPACE_NAME
------------------------------ ------------------------------
T1                             MDATA
MYOBJ                          MDATA
T2                             MDATA

SQL> archive log list

Database log mode              Archive Mode

Automatic archival             Enabled

Archive destination            /u02/oradata/d041/arch/

Oldest online log sequence     15

Next log sequence to archive   15

Current log sequence           18

SQL> select file#,name,status from v$datafile;
FILE#      NAME                                               STATUS
---------- ----------------------------------------           -------
1          /u02/oradata/d041/system01.dbf                     SYSTEM
2          /u02/oradata/d041/sysaux01.dbf                     ONLINE
3          /u02/oradata/d041/undotbs01.dbf                    ONLINE
4          /u02/oradata/d041/users01.dbf                      ONLINE
5          /u02/oradata/d041/mdata01.dbf                      ONLINE

Remove the datafile while database is online, Let’s imitate accidental loss of datafile by removing it manually.

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

Find current open files which are deleted.

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

# find /proc/*/fd -type f -links 0 -exec ls -lrth  {} \;

-rw——-   0 root     root        2.0K Sep 14 12:11 /proc/11/fd/6

-rw——-   0 root     root        2.0K Sep 14 12:11 /proc/11/fd/42

Processes actively using the Datafile:

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

ps -ef |grep dbw

oracle   850     1   0 14:10:20 ?           0:02 ora_dbw0_d041

fuser -u /u02/oradata/d041/mdata01.dbf

/u02/oradata/d041/mdata01.dbf:      886o(oracle)     858o(oracle)     856o(oracle)     852o(oracle)     850o(oracle)

SQL> !rm /u02/oradata/d041/mdata01.dbf

SQL> alter system switch logfile;

System altered.

SQL> select file#,name,status from v$datafile;
FILE#      NAME                                               STATUS
---------- ----------------------------------------           -------
1          /u02/oradata/d041/system01.dbf                     SYSTEM
2          /u02/oradata/d041/sysaux01.dbf                     ONLINE
3          /u02/oradata/d041/undotbs01.dbf                    ONLINE
4          /u02/oradata/d041/users01.dbf                      ONLINE
5          /u02/oradata/d041/mdata01.dbf                      ONLINE

Above Data file 5 still shows as ONLINE. As control file still don’t know about the datafile which is loss, which is still open in background.

If DB gets restarted then the above file would show status as RECOVER.

As we deleted the live File, file is still open in background.

Below Command shows the file is in open state in background and

used by few oracle Background processes as highlighted below,

even after removing the file

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

# find /proc/*/fd -type f -links 0 -exec ls -lrth  {} \;
-rw-------   0 root     root        2.0K Sep 14 12:11 /proc/11/fd/6
-rw-------   0 root     root        2.0K Sep 14 12:11 /proc/11/fd/42
-rw-r-----   0 oracle   oinstall    5.0M Sep 14 12:54 /proc/850/fd/262
-rw-r-----   0 oracle   oinstall    5.0M Sep 14 12:54 /proc/852/fd/265
-rw-r-----   0 oracle   oinstall    5.0M Sep 14 12:54 /proc/856/fd/262
-rw-r-----   0 oracle   oinstall    5.0M Sep 14 12:54 /proc/858/fd/260

ora12crac1:/export/home/oracle: ps -ef |egrep '850|852|856|858'
oracle   850     1   0 15:22:20 ?           0:04 ora_dbw0_d041
oracle   852     1   0 15:22:20 ?           0:12 ora_lgwr_d041
oracle   858     1   0 15:22:21 ?           0:02 ora_smon_d041
oracle   856     1   0 15:22:20 ?           0:10 ora_ckpt_d041

Flush Buffer Cache so the query makes disk reads:

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

SQL> alter system flush buffer_cache;
System altered.
SQL> select count(1)  from mtest.myobj;
select count(1)  from mtest.myobj
*
ERROR at line 1:
ORA-01116: error in opening database file 5
ORA-01110: data file 5: '/u02/oradata/d041/mdata01.dbf'
ORA-27041: unable to open file
Solaris-AMD64 Error: 2: No such file or directory
Additional information: 3
SQL> select FILE_NAME,STATUS from dba_data_files;
select FILE_NAME,STATUS from dba_data_files
*
ERROR at line 1:
ORA-01116: error in opening database file 5
ORA-01110: data file 5: '/u02/oradata/d041/mdata01.dbf'
ORA-27041: unable to open file
Solaris-AMD64 Error: 2: No such file or directory
Additional information: 3
SQL> select * from  v$recover_file;
no rows selected
ora12crac1:/export/home/oracle: echo $ORACLE_SID
d041
ora12crac1:/export/home/oracle: rman target /
Recovery Manager: Release 12.1.0.1.0 - Production on Sat Sep 14 12:59:41 2013
Copyright (c) 1982, 2013, Oracle and/or its affiliates.  All rights reserved.
connected to target database: D041 (DBID=375915797)
RMAN> select file#,name,status from v$datafile;
using target database control file instead of recovery catalog

FILE#      NAME                                               STATUS
---------- ----------------------------------------           -------
1          /u02/oradata/d041/system01.dbf                     SYSTEM
2          /u02/oradata/d041/sysaux01.dbf                     ONLINE
3          /u02/oradata/d041/undotbs01.dbf                    ONLINE
4          /u02/oradata/d041/users01.dbf                      ONLINE
5          /u02/oradata/d041/mdata01.dbf                      ONLINE
RMAN> alter database datafile 5 offline;
using target database control file instead of recovery catalog
Statement processed
RMAN>  select FILE#,NAME,STATUS from v$datafile where  FILE#=5;
FILE#       NAME                                          STATUS
---------- --------------------------------------------- -------
5          /u02/oradata/d041/mdata01.dbf                 RECOVER
SQL> set line 200
SQL> col ERROR for a40
SQL> select * from  v$recover_file;
FILE#      ONLINE  ONLINE_ ERROR                                       CHANGE# TIME          CON_ID
---------- ------- ------- ---------------------------------------- ---------- --------- ----------
5          OFFLINE OFFLINE FILE NOT FOUND                                    0                    0

Verifying the status of datafile in Standby:

Standby/DR

===========

SQL> archive log list
Database log mode              Archive Mode
Automatic archival             Enabled
Archive destination            /u02/oradata/d041dr/arch/
Oldest online log sequence     7
Next log sequence to archive   0
Current log sequence           9
SQL> select file#,name,status from v$datafile;
FILE#      NAME                                               STATUS
---------- ----------------------------------------           -------
1          /u02/oradata/d041/system01.dbf                     SYSTEM
2          /u02/oradata/d041/sysaux01.dbf                     ONLINE
3          /u02/oradata/d041/undotbs01.dbf                    ONLINE
4          /u02/oradata/d041/users01.dbf                      ONLINE
5          /u02/oradata/d041/mdata01.dbf                      ONLINE

PRIMARY/PROD

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

Now let’s restore the lost datafile from Standby database.

 

RMAN> restore datafile 5 from service d041dr SECTION SIZE 120M using compressed backupset;
Starting restore at 14-SEP-13
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=50 device type=DISK
channel ORA_DISK_1: starting datafile backup set restore
channel ORA_DISK_1: using compressed network backup set from service d041dr
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_DISK_1: restoring datafile 00005 to /u02/oradata/d041/mdata01.dbf
channel ORA_DISK_1: restore complete, elapsed time: 00:00:15
Finished restore at 14-SEP-13
RMAN> recover datafile 5;
Starting recover at 14-SEP-13
using channel ORA_DISK_1
starting media recovery
archived log for thread 1 with sequence 9 is already on disk as file /u02/oradata/d041/arch/d041_arch_T1S9R825873327.arc
archived log for thread 1 with sequence 10 is already on disk as file /u02/oradata/d041/arch/d041_arch_T1S10R825873327.arc
archived log for thread 1 with sequence 11 is already on disk as file /u02/oradata/d041/arch/d041_arch_T1S11R825873327.arc
archived log for thread 1 with sequence 12 is already on disk as file /u02/oradata/d041/arch/d041_arch_T1S12R825873327.arc
archived log for thread 1 with sequence 13 is already on disk as file /u02/oradata/d041/arch/d041_arch_T1S13R825873327.arc
archived log for thread 1 with sequence 14 is already on disk as file /u02/oradata/d041/arch/d041_arch_T1S14R825873327.arc
archived log for thread 1 with sequence 15 is already on disk as file /u02/oradata/d041/arch/d041_arch_T1S15R825873327.arc
archived log for thread 1 with sequence 16 is already on disk as file /u02/oradata/d041/arch/d041_arch_T1S16R825873327.arc
archived log for thread 1 with sequence 17 is already on disk as file /u02/oradata/d041/arch/d041_arch_T1S17R825873327.arc
archived log file name=/u02/oradata/d041/arch/d041_arch_T1S9R825873327.arc thread=1 sequence=9
archived log file name=/u02/oradata/d041/arch/d041_arch_T1S10R825873327.arc thread=1 sequence=10
archived log file name=/u02/oradata/d041/arch/d041_arch_T1S11R825873327.arc thread=1 sequence=11
archived log file name=/u02/oradata/d041/arch/d041_arch_T1S12R825873327.arc thread=1 sequence=12
archived log file name=/u02/oradata/d041/arch/d041_arch_T1S13R825873327.arc thread=1 sequence=13
archived log file name=/u02/oradata/d041/arch/d041_arch_T1S14R825873327.arc thread=1 sequence=14
archived log file name=/u02/oradata/d041/arch/d041_arch_T1S16R825873327.arc thread=1 sequence=16
media recovery complete, elapsed time: 00:00:04
Finished recover at 14-SEP-13
RMAN> select FILE#,NAME,STATUS from v$datafile where  FILE#=5;
FILE#       NAME                                               STATUS
---------- --------------------------------------------------  -------
5          /u02/oradata/d041/mdata01.dbf                       OFFLINE
RMAN> alter database datafile 5 online;
Statement processed
RMAN> select FILE#,NAME,STATUS from v$datafile where  FILE#=5;
FILE#       NAME                                          STATUS
---------- ---------------------------------------------  -------
5           /u02/oradata/d041/mdata01.dbf                 ONLINE
SQL> select * from mtest.t1;
NAM
--------------------
Helo
SQL> select count(1)  from mtest.myobj;
COUNT(1)
----------
18528

Reference:

http://docs.oracle.com/cd/E16655_01/backup.121/e17630/rcmadvre.htm#BRADV680

Advertisements

From → 12c

One Comment

Trackbacks & Pingbacks

  1. 12c | Maleshg

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: