01 January, 2012

Datafiles not Restored -- using V$DATAFILE and V$DATAFILE_HEADER

Building on my previous blog post "Does a STARTUP MOUNT verify datafiles ?",  here's how I can use the V$DATAFILE and V$DATAFILE_HEADER views from the controlfile to identify datafiles that are / are not restored.  This is a case where I have to do a FULL Restore.

I first restore the controlfile :
MAN> startup nomount;

connected to target database (not started)
Oracle instance started

Total System Global Area     313860096 bytes

Fixed Size                     1336232 bytes
Variable Size                281021528 bytes
Database Buffers              25165824 bytes
Redo Buffers                   6336512 bytes

RMAN> restore controlfile from autobackup;

Starting restore at 01-JAN-12
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=192 device type=DISK

recovery area destination: /home/oracle/app/oracle/flash_recovery_area
database name (or database unique name) used for search: ORCL
channel ORA_DISK_1: AUTOBACKUP /home/oracle/app/oracle/flash_recovery_area/ORCL/autobackup/2012_01_01/o1_mf_n_771420416_7hznn10m_.bkp found in the recovery area
channel ORA_DISK_1: looking for AUTOBACKUP on day: 20120101
channel ORA_DISK_1: restoring control file from AUTOBACKUP /home/oracle/app/oracle/flash_recovery_area/ORCL/autobackup/2012_01_01/o1_mf_n_771420416_7hznn10m_.bkp
channel ORA_DISK_1: control file restore from AUTOBACKUP complete
output file name=/home/oracle/app/oracle/oradata/orcl/control01.ctl
output file name=/home/oracle/app/oracle/flash_recovery_area/orcl/control02.ctl
Finished restore at 01-JAN-12

RMAN> 
RMAN> alter database mount;

database mounted
released channel: ORA_DISK_1

RMAN> exit


Recovery Manager complete.
I then query the V$DATAFILE and V$DATAFILE_HEADER views. Remember : There are NO datafiles present on this server. I only have the controlfile. I find (see the output below) that V$DATAFILE shows all the files expected to be present but V$DATAFILE_HEADER does not find any files.
oracle@linux64 ~]$ sqlplus '/ as sysdba'

SQL*Plus: Release 11.2.0.1.0 Production on Sun Jan 1 11:34:25 2012

Copyright (c) 1982, 2009, Oracle.  All rights reserved.


Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> set pages600       
SQL> select file#, name from v$datafile order by 1;

     FILE#
----------
NAME
--------------------------------------------------------------------------------
         1
/home/oracle/app/oracle/oradata/orcl/system01.dbf

         2
/home/oracle/app/oracle/oradata/orcl/sysaux01.dbf

         3
/home/oracle/app/oracle/oradata/orcl/undotbs01.dbf

         4
/home/oracle/app/oracle/oradata/orcl/users01.dbf

         5
/home/oracle/app/oracle/oradata/orcl/example01.dbf

         6
/home/oracle/app/oracle/oradata/orcl/FLOW_1046101119510758.dbf

         7
/home/oracle/app/oracle/oradata/orcl/FLOW_1146416395631714.dbf

         8
/home/oracle/app/oracle/oradata/orcl/FLOW_1170420963682633.dbf

         9
/home/oracle/app/oracle/oradata/orcl/FLOW_1194425963955800.dbf

        10
/home/oracle/app/oracle/oradata/orcl/FLOW_1218408858999342.dbf

        11
/home/oracle/app/oracle/oradata/orcl/FLOW_1242310449730067.dbf

        12
/home/oracle/app/oracle/oradata/orcl/FLOW_1266412439758696.dbf

        13
/home/oracle/app/oracle/oradata/orcl/APEX_1295922881855015.dbf

        14
/oradata/add_tbs.dbf


14 rows selected.

SQL> 
SQL> select file#, name from v$datafile_header order by 1;

     FILE#
----------
NAME
--------------------------------------------------------------------------------
         1


         2


         3


         4


         5


         6


         7


         8


         9


        10


        11


        12


        13


        14



14 rows selected.

SQL> 
SQL> select file#, nvl(name,'File Not Found') from v$datafile_header order by 1;

     FILE#
----------
NVL(NAME,'FILENOTFOUND')
--------------------------------------------------------------------------------
         1
File Not Found

         2
File Not Found

         3
File Not Found

         4
File Not Found

         5
File Not Found

         6
File Not Found

         7
File Not Found

         8
File Not Found

         9
File Not Found

        10
File Not Found

        11
File Not Found

        12
File Not Found

        13
File Not Found

        14
File Not Found


14 rows selected.

SQL> 
SQL> exit
Thus, all 14 entries are present in V$DATAFILE_HEADER but with a NULL NAME.

I then begin a RESTORE :
[oracle@linux64 ~]$ rman target /

Recovery Manager: Release 11.2.0.1.0 - Production on Sun Jan 1 11:36:07 2012

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

connected to target database: ORCL (DBID=1229390655, not open)

RMAN> 
RMAN> restore database;

Starting restore at 01-JAN-12
Starting implicit crosscheck backup at 01-JAN-12
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=192 device type=DISK
Crosschecked 12 objects
Finished implicit crosscheck backup at 01-JAN-12

Starting implicit crosscheck copy at 01-JAN-12
using channel ORA_DISK_1
Finished implicit crosscheck copy at 01-JAN-12

searching for all files in the recovery area
cataloging files...
cataloging done

List of Cataloged Files
=======================
File Name: /home/oracle/app/oracle/flash_recovery_area/ORCL/autobackup/2012_01_01/o1_mf_n_771420416_7hznn10m_.bkp

using channel ORA_DISK_1

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 00001 to /home/oracle/app/oracle/oradata/orcl/system01.dbf
channel ORA_DISK_1: restoring datafile 00002 to /home/oracle/app/oracle/oradata/orcl/sysaux01.dbf
channel ORA_DISK_1: restoring datafile 00003 to /home/oracle/app/oracle/oradata/orcl/undotbs01.dbf
channel ORA_DISK_1: restoring datafile 00014 to /oradata/add_tbs.dbf
channel ORA_DISK_1: reading from backup piece /home/oracle/app/oracle/flash_recovery_area/ORCL/backupset/2012_01_01/o1_mf_nnndf_TAG20120101T112516_7hznjx24_.bkp
channel ORA_DISK_1: ORA-19870: error while restoring backup piece /home/oracle/app/oracle/flash_recovery_area/ORCL/backupset/2012_01_01/o1_mf_nnndf_TAG20120101T112516_7hznjx24_.bkp
ORA-19504: failed to create file "/oradata/add_tbs.dbf"
ORA-27037: unable to obtain file status
Linux Error: 13: Permission denied
Additional information: 6

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 00004 to /home/oracle/app/oracle/oradata/orcl/users01.dbf
channel ORA_DISK_1: restoring datafile 00007 to /home/oracle/app/oracle/oradata/orcl/FLOW_1146416395631714.dbf
channel ORA_DISK_1: reading from backup piece /home/oracle/app/oracle/flash_recovery_area/ORCL/backupset/2012_01_01/o1_mf_nnndf_TAG20120101T112516_7hznm84k_.bkp
channel ORA_DISK_1: piece handle=/home/oracle/app/oracle/flash_recovery_area/ORCL/backupset/2012_01_01/o1_mf_nnndf_TAG20120101T112516_7hznm84k_.bkp tag=TAG20120101T112516
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:15
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 00005 to /home/oracle/app/oracle/oradata/orcl/example01.dbf
channel ORA_DISK_1: restoring datafile 00006 to /home/oracle/app/oracle/oradata/orcl/FLOW_1046101119510758.dbf
channel ORA_DISK_1: restoring datafile 00008 to /home/oracle/app/oracle/oradata/orcl/FLOW_1170420963682633.dbf
channel ORA_DISK_1: reading from backup piece /home/oracle/app/oracle/flash_recovery_area/ORCL/backupset/2012_01_01/o1_mf_nnndf_TAG20120101T112516_7hznmq8q_.bkp
channel ORA_DISK_1: piece handle=/home/oracle/app/oracle/flash_recovery_area/ORCL/backupset/2012_01_01/o1_mf_nnndf_TAG20120101T112516_7hznmq8q_.bkp tag=TAG20120101T112516
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:07
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 00009 to /home/oracle/app/oracle/oradata/orcl/FLOW_1194425963955800.dbf
channel ORA_DISK_1: restoring datafile 00010 to /home/oracle/app/oracle/oradata/orcl/FLOW_1218408858999342.dbf
channel ORA_DISK_1: restoring datafile 00011 to /home/oracle/app/oracle/oradata/orcl/FLOW_1242310449730067.dbf
channel ORA_DISK_1: restoring datafile 00012 to /home/oracle/app/oracle/oradata/orcl/FLOW_1266412439758696.dbf
channel ORA_DISK_1: restoring datafile 00013 to /home/oracle/app/oracle/oradata/orcl/APEX_1295922881855015.dbf
channel ORA_DISK_1: reading from backup piece /home/oracle/app/oracle/flash_recovery_area/ORCL/backupset/2012_01_01/o1_mf_nnndf_TAG20120101T112516_7hznmydw_.bkp
channel ORA_DISK_1: piece handle=/home/oracle/app/oracle/flash_recovery_area/ORCL/backupset/2012_01_01/o1_mf_nnndf_TAG20120101T112516_7hznmydw_.bkp tag=TAG20120101T112516
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:01
failover to previous backup

creating datafile file number=14 name=/oradata/add_tbs.dbf
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of restore command at 01/01/2012 11:36:45
ORA-01119: error in creating database file '/oradata/add_tbs.dbf'
ORA-27037: unable to obtain file status
Linux Error: 13: Permission denied
Additional information: 6
RMAN-06956: create datafile failed; retry after removing /oradata/add_tbs.dbf from OS

RMAN> 
RMAN> restore database;

Starting restore at 01-JAN-12
using channel ORA_DISK_1

skipping datafile 4; already restored to file /home/oracle/app/oracle/oradata/orcl/users01.dbf
skipping datafile 7; already restored to file /home/oracle/app/oracle/oradata/orcl/FLOW_1146416395631714.dbf
skipping datafile 5; already restored to file /home/oracle/app/oracle/oradata/orcl/example01.dbf
skipping datafile 6; already restored to file /home/oracle/app/oracle/oradata/orcl/FLOW_1046101119510758.dbf
skipping datafile 8; already restored to file /home/oracle/app/oracle/oradata/orcl/FLOW_1170420963682633.dbf
skipping datafile 9; already restored to file /home/oracle/app/oracle/oradata/orcl/FLOW_1194425963955800.dbf
skipping datafile 10; already restored to file /home/oracle/app/oracle/oradata/orcl/FLOW_1218408858999342.dbf
skipping datafile 11; already restored to file /home/oracle/app/oracle/oradata/orcl/FLOW_1242310449730067.dbf
skipping datafile 12; already restored to file /home/oracle/app/oracle/oradata/orcl/FLOW_1266412439758696.dbf
skipping datafile 13; already restored to file /home/oracle/app/oracle/oradata/orcl/APEX_1295922881855015.dbf
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 00001 to /home/oracle/app/oracle/oradata/orcl/system01.dbf
channel ORA_DISK_1: restoring datafile 00002 to /home/oracle/app/oracle/oradata/orcl/sysaux01.dbf
channel ORA_DISK_1: restoring datafile 00003 to /home/oracle/app/oracle/oradata/orcl/undotbs01.dbf
channel ORA_DISK_1: restoring datafile 00014 to /oradata/add_tbs.dbf
channel ORA_DISK_1: reading from backup piece /home/oracle/app/oracle/flash_recovery_area/ORCL/backupset/2012_01_01/o1_mf_nnndf_TAG20120101T112516_7hznjx24_.bkp
channel ORA_DISK_1: ORA-19870: error while restoring backup piece /home/oracle/app/oracle/flash_recovery_area/ORCL/backupset/2012_01_01/o1_mf_nnndf_TAG20120101T112516_7hznjx24_.bkp
ORA-19504: failed to create file "/oradata/add_tbs.dbf"
ORA-27037: unable to obtain file status
Linux Error: 13: Permission denied
Additional information: 6

failover to previous backup

creating datafile file number=14 name=/oradata/add_tbs.dbf
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of restore command at 01/01/2012 11:37:42
ORA-01119: error in creating database file '/oradata/add_tbs.dbf'
ORA-27037: unable to obtain file status
Linux Error: 13: Permission denied
Additional information: 6
RMAN-06956: create datafile failed; retry after removing /oradata/add_tbs.dbf from OS

RMAN> 
RMAN> exit
I get an OS permissions error for File #14 ('/oradata/add_tbs.dbf') because oracle does not have write permissions for this directory path on this server !  Very unfortunately, RMAN isn't currently intelligent enough to skip this one file and restore the other files from the same backuppiece. Apparently, all 4 files in the backup piece are not restored !
RMAN does skip over datafiles 4 to 13 that have already been restored and does not attempt to restore these files at the second RESTORE execution.
Before attempting to restore these 4 files, I also use the V$DATAFILE and V$DATAFILE_HEADER views to identify them :
oracle@linux64 ~]$ sqlplus '/ as sysdba'

SQL*Plus: Release 11.2.0.1.0 Production on Sun Jan 1 11:42:06 2012

Copyright (c) 1982, 2009, Oracle.  All rights reserved.


Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> set pages600
SQL> select file#, name from v$datafile
  2  minus
  3  select file#, name from v$datafile_header
  4  order by 1;

     FILE#
----------
NAME
--------------------------------------------------------------------------------
         1
/home/oracle/app/oracle/oradata/orcl/system01.dbf

         2
/home/oracle/app/oracle/oradata/orcl/sysaux01.dbf

         3
/home/oracle/app/oracle/oradata/orcl/undotbs01.dbf

        14
/oradata/add_tbs.dbf


SQL> 
SQL> exit
I then RESTORE the 4 files individually :
[oracle@linux64 ~]$ rman target /

Recovery Manager: Release 11.2.0.1.0 - Production on Sun Jan 1 11:42:52 2012

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

connected to target database: ORCL (DBID=1229390655, not open)

RMAN> restore datafile 1;

Starting restore at 01-JAN-12
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=192 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 00001 to /home/oracle/app/oracle/oradata/orcl/system01.dbf
channel ORA_DISK_1: reading from backup piece /home/oracle/app/oracle/flash_recovery_area/ORCL/backupset/2012_01_01/o1_mf_nnndf_TAG20120101T112516_7hznjx24_.bkp
channel ORA_DISK_1: piece handle=/home/oracle/app/oracle/flash_recovery_area/ORCL/backupset/2012_01_01/o1_mf_nnndf_TAG20120101T112516_7hznjx24_.bkp tag=TAG20120101T112516
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:01:15
Finished restore at 01-JAN-12

RMAN> restore datafile 2;

Starting restore at 01-JAN-12
using channel ORA_DISK_1

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 00002 to /home/oracle/app/oracle/oradata/orcl/sysaux01.dbf
channel ORA_DISK_1: reading from backup piece /home/oracle/app/oracle/flash_recovery_area/ORCL/backupset/2012_01_01/o1_mf_nnndf_TAG20120101T112516_7hznjx24_.bkp
channel ORA_DISK_1: piece handle=/home/oracle/app/oracle/flash_recovery_area/ORCL/backupset/2012_01_01/o1_mf_nnndf_TAG20120101T112516_7hznjx24_.bkp tag=TAG20120101T112516
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:01:15
Finished restore at 01-JAN-12

RMAN> restore datafile 3;

Starting restore at 01-JAN-12
using channel ORA_DISK_1

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 00003 to /home/oracle/app/oracle/oradata/orcl/undotbs01.dbf
channel ORA_DISK_1: reading from backup piece /home/oracle/app/oracle/flash_recovery_area/ORCL/backupset/2012_01_01/o1_mf_nnndf_TAG20120101T112516_7hznjx24_.bkp
channel ORA_DISK_1: piece handle=/home/oracle/app/oracle/flash_recovery_area/ORCL/backupset/2012_01_01/o1_mf_nnndf_TAG20120101T112516_7hznjx24_.bkp tag=TAG20120101T112516
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:15
Finished restore at 01-JAN-12

RMAN> run
2> {set newname for datafile 14 to '/home/oracle/app/oracle/oradata/orcl/add_tbs.dbf';
3> restore datafile 14;
4> }

executing command: SET NEWNAME

Starting restore at 01-JAN-12
using channel ORA_DISK_1

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 00014 to /home/oracle/app/oracle/oradata/orcl/add_tbs.dbf
channel ORA_DISK_1: reading from backup piece /home/oracle/app/oracle/flash_recovery_area/ORCL/backupset/2012_01_01/o1_mf_nnndf_TAG20120101T112516_7hznjx24_.bkp
channel ORA_DISK_1: piece handle=/home/oracle/app/oracle/flash_recovery_area/ORCL/backupset/2012_01_01/o1_mf_nnndf_TAG20120101T112516_7hznjx24_.bkp tag=TAG20120101T112516
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:03
Finished restore at 01-JAN-12

RMAN> 
However, since I have used a NEWNAME for datafile #14, I must SWITCH it before I can use RECOVER DATABASE :
RMAN> recover database; 

Starting recover at 01-JAN-12
using channel ORA_DISK_1
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of recover command at 01/01/2012 11:48:44
RMAN-06094: datafile 14 must be restored

RMAN> switch datafile 14 to copy;

datafile 14 switched to datafile copy "/home/oracle/app/oracle/oradata/orcl/add_tbs.dbf"

RMAN> recover database;

Starting recover at 01-JAN-12
using channel ORA_DISK_1

starting media recovery

archived log for thread 1 with sequence 2 is already on disk as file /home/oracle/app/oracle/flash_recovery_area/ORCL/archivelog/2012_01_01/o1_mf_1_2_7hznmzh7_.arc
archived log for thread 1 with sequence 3 is already on disk as file /home/oracle/app/oracle/oradata/orcl/redo03.log
archived log file name=/home/oracle/app/oracle/flash_recovery_area/ORCL/archivelog/2012_01_01/o1_mf_1_2_7hznmzh7_.arc thread=1 sequence=2
archived log file name=/home/oracle/app/oracle/oradata/orcl/redo03.log thread=1 sequence=3
media recovery complete, elapsed time: 00:00:00
Finished recover at 01-JAN-12

RMAN> 
RMAN> exit
I now verify the datafiles again, before actually OPENing the database :
[oracle@linux64 ~]$ sqlplus

SQL*Plus: Release 11.2.0.1.0 Production on Sun Jan 1 11:51:23 2012

Copyright (c) 1982, 2009, Oracle.  All rights reserved.

Enter user-name: / as sysdba

Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> select file#, name from v$datafile
  2  minus
  3  select file#, name from v$datafile_header
  4  order by 1;

no rows selected

SQL> select name from v$datafile where file#=14;

NAME
--------------------------------------------------------------------------------
/home/oracle/app/oracle/oradata/orcl/add_tbs.dbf

SQL> alter database open resetlogs;

Database altered.

SQL>
Now it should be clear how V$DATAFILE and V$DATAFILE_HEADER are different. V$DATAFILE_HEADER does actually verify the datafile, while V$DATAFILE only reads the controlfile.

.
.
.

4 comments:

DanyC said...

Very nice article, thx for sharing.

DanyC

Anonymous said...

Thanks Hemant for the wonderful post. It is really very easy to follow article.

Satwinder

Unknown said...

Very useful, thanks

Alli Ismaila said...

You always get all my issue resolve....this is fantastic