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 :
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.
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> exitI 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
[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> exitI 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:
Very nice article, thx for sharing.
DanyC
Thanks Hemant for the wonderful post. It is really very easy to follow article.
Satwinder
Very useful, thanks
You always get all my issue resolve....this is fantastic
Post a Comment