With the help of this article today we are going to learn about those steps which are going to use during the restore lost non-system datafiles on different location the database is closed. In our previous post, we have discussed the restore lost non–system datafiles on a different location while the database while the database is open.
We are going to lose two datafiles of two different non-system critical tablespaces and restore them, while the database is CLOSED, to a location other than the original one because I’m experiencing a serious and permanent media failure.
To simulate this scenario We want to remove a datafile from EXAMPLE and APEX tablespaces.
Let’s see first where are located those datafiles.
SQL> select file_name from dba_data_files
2 where TABLESPACE_NAME IN ('EXAMPLE', 'APEX');
FILE_NAME
----------------------------------------------------
/home/oracle/app/oracle/oradata/orcl/example01.dbf
/home/oracle/app/oracle/oradata/orcl/APEX.dbf
/home/oracle/app/oracle/oradata/orcl/example02.dbf
/home/oracle/app/oracle/oradata/orcl/APEX02.dbf
I’m going to remove example02.dbf and APEX02.dbf datafiles because I have a valid backup of EXAMPLE and APEX tablespaces taken days ago using RMAN.
Are you sure you have a valid backup ? Why don’t you verify it ?
Let’s see what id number have those datafiles querying V$DATAFILE view:
SQL> col name format a60 SQL> set linesize 180 SQL> select file#, name from v$DATAFILE; 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/APEX.dbf 7 /home/oracle/app/oracle/oradata/orcl/read_only01.dbf 8 /home/oracle/app/oracle/oradata/orcl/ZZZ01.dbf 9 /home/oracle/app/oracle/oradata/orcl/example02.dbf 10 /home/oracle/app/oracle/oradata/orcl/APEX02.dbf
Now let’s verify if I have a valid backup using restore … preview command for datafiles with 9 and 10 as id number:
RMAN> restore datafile 9,10 preview; Starting restore at 22-01-2013 21:54:35 using channel ORA_DISK_1 List of Backup Sets =================== BS Key Type LV Size Device Type Elapsed Time Completion Time ------- ---- -- ---------- ----------- ------------ ------------------- 227 Full 18.59M DISK 00:00:14 22-01-2013 21:40:51 BP Key: 228 Status: AVAILABLE Compressed: YES Tag: TAG20130122T214051 Piece Name: /home/oracle/app/oracle/flash_recovery_area/ORCL/backupset/2013_01_22/o1_mf_nnndf_TAG20130122T214051_8hyxz3j4_.bkp List of Datafiles in backup set 227 File LV Type Ckp SCN Ckp Time Name ---- -- ---- ---------- ------------------- ---- 9 Full 14790649 22-01-2013 21:40:51 /home/oracle/app/oracle/oradata/orcl/example02.dbf BS Key Type LV Size Device Type Elapsed Time Completion Time ------- ---- -- ---------- ----------- ------------ ------------------- 229 Full 1.55M DISK 00:00:02 22-01-2013 21:53:33 BP Key: 230 Status: AVAILABLE Compressed: YES Tag: TAG20130122T215332 Piece Name: /home/oracle/app/oracle/flash_recovery_area/ORCL/backupset/2013_01_22/o1_mf_nnndf_TAG20130122T215332_8hyypx39_.bkp List of Datafiles in backup set 229 File LV Type Ckp SCN Ckp Time Name ---- -- ---- ---------- ------------------- ---- 10 Full 14791164 22-01-2013 21:53:33 /home/oracle/app/oracle/oradata/orcl/APEX02.dbf archived logs generated after SCN 14790649 not found in repository Media recovery start SCN is 14790649 Recovery must be done beyond SCN 14791164 to clear datafile fuzziness Finished restore at 22-01-2013 21:54:35
The original location of APEX and EXAMPLE datafiles is: /home/oracle/app/oracle/oradata/orcl/
[oracle@localhost orcl]$ pwd /home/oracle/app/oracle/oradata/orcl [oracle@localhost orcl]$ ls -l *02.dbf* -rw-rw---- 1 oracle oracle 1056768 Jan 22 21:53 APEX02.dbf -rw-rw---- 1 oracle oracle 1056768 Jan 22 21:40 example02.dbf -rw-rw---- 1 oracle oracle 20979712 Jan 12 01:32 temp02.dbf
During the restore operation I will instruct RMAN to recreate them on a new destination: /home/oracle/app/oracle/oradata/orcl/non_default_location
[oracle@localhost orcl]$ cd non_default_location/ [oracle@localhost non_default_location]$ pwd /home/oracle/app/oracle/oradata/orcl/non_default_location [oracle@localhost non_default_location]$ ls -l total 0
A media failure happened and I’ve lost those datafiles belonging to EXAMPLE and APEX tablespace:
[oracle@localhost orcl]$ rm example02* [oracle@localhost orcl]$ rm APEX02* [oracle@localhost orcl]$
The instance crashed.
[oracle@localhost non_default_location]$ ps -ef|grep smon
If I try to startup the database it remains in MOUNT mode throwing the error “ORA-01157: cannot identify/lock data file 9 – see DBWR trace file”.
[oracle@localhost non_default_location]$ sqlplus / as sysdba SQL*Plus: Release 11.2.0.2.0 Production on Tue Jan 22 22:05:09 2013 Copyright (c) 1982, 2010, Oracle. All rights reserved. Connected to an idle instance. SQL> startup ORACLE instance started. Total System Global Area 456146944 bytes Fixed Size 1344840 bytes Variable Size 360712888 bytes Database Buffers 88080384 bytes Redo Buffers 6008832 bytes Database mounted. ORA-01157: cannot identify/lock data file 9 - see DBWR trace file ORA-01110: data file 9: '/home/oracle/app/oracle/oradata/orcl/example02.dbf' SQL> select open_mode from V$DATABASE; OPEN_MODE -------------------- MOUNTED
If I have a look at the alert log I can see also the same error for datafile 10:
[oracle@localhost orcl]$ tail -f -n100 /home/oracle/app/oracle/diag/rdbms/orcl/orcl/trace/alert_orcl.log ... Errors in file /home/oracle/app/oracle/diag/rdbms/orcl/orcl/trace/orcl_dbw0_4818.trc: ORA-01157: cannot identify/lock data file 10 - see DBWR trace file ORA-01110: data file 10: '/home/oracle/app/oracle/oradata/orcl/APEX02.dbf' ORA-27037: unable to obtain file status Linux Error: 2: No such file or directory Additional information: 3 ...
Let’s shutdown the instance to use a RMAN script restoring and recovering our database while is closed:
SQL> shutdown immediate; ORA-01109: database not open Database dismounted. ORACLE instance shut down.
As already said due to a permanent disk failure I won’t be able to restore example02.dbf and APEX02.dbf datafiles on the original location.
Like in the previous post I need to use set newname for datafile command to change the name of multiple files during the restore operation and then I have also to run switch datafile all command to update our controlfile with the renamed datafiles.
Note that those commands must be executed inside a run {…} block.
To identify your original datafiles you can use their absolute file numbers, full path or relative file names; to recreate them on a new location you have to specify their full path file names, using eventually some substitution variable like %U to specify a system-generated unique file name and avoid file name collisions.
We already know id numbers, but in this scenario and differently from the previous one, I want to use the full path file name of the missing datafiles.
To restore and recover them on a new location I have to execute the following run {…} block after connected to RMAN and issued a startup mount command:
[oracle@localhost orcl]$ rman target / Recovery Manager: Release 11.2.0.2.0 - Production on Tue Jan 22 22:20:34 2013 Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved. connected to target database (not started) RMAN> startup mount; Oracle instance started database mounted Total System Global Area 456146944 bytes Fixed Size 1344840 bytes Variable Size 360712888 bytes Database Buffers 88080384 bytes Redo Buffers 6008832 bytes
RMAN> run {
2> set newname for datafile '/home/oracle/app/oracle/oradata/orcl/example02.dbf' to '/home/oracle/app/oracle/oradata/orcl/non_default_location/example02.dbf';
3> set newname for datafile '/home/oracle/app/oracle/oradata/orcl/APEX02.dbf' to '/home/oracle/app/oracle/oradata/orcl/non_default_location/APEX02.dbf';
4> restore datafile '/home/oracle/app/oracle/oradata/orcl/example02.dbf', '/home/oracle/app/oracle/oradata/orcl/APEX02.dbf';
5> switch datafile all;
6> recover datafile '/home/oracle/app/oracle/oradata/orcl/non_default_location/example02.dbf','/home/oracle/app/oracle/oradata/orcl/non_default_location/APEX02.dbf';
7> alter database open;
8> }
executing command: SET NEWNAME
executing command: SET NEWNAME
Starting restore at 22-01-2013 22:27:27
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=19 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 00009 to /home/oracle/app/oracle/oradata/orcl/non_default_location/example02.dbf
channel ORA_DISK_1: reading from backup piece /home/oracle/app/oracle/flash_recovery_area/ORCL/backupset/2013_01_22/o1_mf_nnndf_TAG20130122T214051_8hyxz3j4_.bkp
channel ORA_DISK_1: piece handle=/home/oracle/app/oracle/flash_recovery_area/ORCL/backupset/2013_01_22/o1_mf_nnndf_TAG20130122T214051_8hyxz3j4_.bkp tag=TAG20130122T214051
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:01
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 00010 to /home/oracle/app/oracle/oradata/orcl/non_default_location/APEX02.dbf
channel ORA_DISK_1: reading from backup piece /home/oracle/app/oracle/flash_recovery_area/ORCL/backupset/2013_01_22/o1_mf_nnndf_TAG20130122T215332_8hyypx39_.bkp
channel ORA_DISK_1: piece handle=/home/oracle/app/oracle/flash_recovery_area/ORCL/backupset/2013_01_22/o1_mf_nnndf_TAG20130122T215332_8hyypx39_.bkp tag=TAG20130122T215332
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:01
Finished restore at 22-01-2013 22:27:32
datafile 9 switched to datafile copy
input datafile copy RECID=44 STAMP=805415252 file name=/home/oracle/app/oracle/oradata/orcl/non_default_location/example02.dbf
datafile 10 switched to datafile copy
input datafile copy RECID=45 STAMP=805415252 file name=/home/oracle/app/oracle/oradata/orcl/non_default_location/APEX02.dbf
Starting recover at 22-01-2013 22:27:32
using channel ORA_DISK_1
starting media recovery
media recovery complete, elapsed time: 00:00:00
Finished recover at 22-01-2013 22:27:33
database opened
Some considerations need to be made:
– the restore and recover operations were made while database was mounted so I did not need to put those datafiles offline;
– I could use switch datafile command in place of switch datafile all;
– I prefer to specify in these situations the id number, otherwise you have to use the original file name for the restore datafile command and the new file name for the recover datafile command.
This is always due to the fact that controlfile doesn’t have any information about the new location until you execute the switch datafile all command: from that moment the controlfile lose the information about the original location of those datafiles.
Just be careful to use the right file name if you prefer to specify full path file name instead querying V$DATAFILE or issuing report schema to know id numbers.
Let’s see on the new location the two datafiles:
[oracle@localhost non_default_location]$ pwd /home/oracle/app/oracle/oradata/orcl/non_default_location [oracle@localhost non_default_location]$ ls -l total 2072 -rw-rw---- 1 oracle oracle 1056768 Jan 22 22:27 APEX02.dbf -rw-rw---- 1 oracle oracle 1056768 Jan 22 22:27 example02.dbf
Have a look at the new output produced by report schema command:
RMAN> report schema; Report of database schema for database with db_unique_name ORCL List of Permanent Datafiles =========================== File Size(MB) Tablespace RB segs Datafile Name ---- -------- -------------------- ------- ------------------------ 1 911 SYSTEM *** /home/oracle/app/oracle/oradata/orcl/system01.dbf 2 1105 SYSAUX *** /home/oracle/app/oracle/oradata/orcl/sysaux01.dbf 3 475 UNDOTBS1 *** /home/oracle/app/oracle/oradata/orcl/undotbs01.dbf 4 225 USERS *** /home/oracle/app/oracle/oradata/orcl/users01.dbf 5 82 EXAMPLE *** /home/oracle/app/oracle/oradata/orcl/example01.dbf 6 7 APEX *** /home/oracle/app/oracle/oradata/orcl/APEX.dbf 7 1 READ_ONLY *** /home/oracle/app/oracle/oradata/orcl/read_only01.dbf 8 1 ZZZ *** /home/oracle/app/oracle/oradata/orcl/ZZZ01.dbf 9 1 EXAMPLE *** /home/oracle/app/oracle/oradata/orcl/non_default_location/example02.dbf 10 1 APEX *** /home/oracle/app/oracle/oradata/orcl/non_default_location/APEX02.dbf List of Temporary Files ======================= File Size(MB) Tablespace Maxsize(MB) Tempfile Name ---- -------- -------------------- ----------- -------------------- 1 20 TEMP 32767 /home/oracle/app/oracle/oradata/orcl/temp01.dbf 2 20 TEMP 50 /home/oracle/app/oracle/oradata/orcl/temp02.dbf
Few days later we are able to move back those datafiles on their original location because a new disk is ready to be used.
First thing to do is to copy your datafiles using the format clause, specifying you want to create your datafile copy to the original location.
RMAN> backup as copy datafile 9 format='/home/oracle/app/oracle/oradata/orcl/example02.dbf'; Starting backup at 23-01-2013 01:00:15 using channel ORA_DISK_1 channel ORA_DISK_1: starting datafile copy input datafile file number=00009 name=/home/oracle/app/oracle/oradata/orcl/non_default_location/example02.dbf output file name=/home/oracle/app/oracle/oradata/orcl/example02.dbf tag=TAG20130123T010015 RECID=46 STAMP=805424415 channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:01 Finished backup at 23-01-2013 01:00:16 Starting Control File and SPFILE Autobackup at 23-01-2013 01:00:16 piece handle=/home/oracle/app/oracle/flash_recovery_area/ORCL/autobackup/2013_01_23/o1_mf_s_805424416_8hz9o1p5_.bkp comment=NONE Finished Control File and SPFILE Autobackup at 23-01-2013 01:00:20 RMAN> backup as copy datafile 10 format='/home/oracle/app/oracle/oradata/orcl/APEX02.dbf'; Starting backup at 23-01-2013 01:00:42 using channel ORA_DISK_1 channel ORA_DISK_1: starting datafile copy input datafile file number=00010 name=/home/oracle/app/oracle/oradata/orcl/non_default_location/APEX02.dbf output file name=/home/oracle/app/oracle/oradata/orcl/APEX02.dbf tag=TAG20130123T010042 RECID=47 STAMP=805424443 channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:01 Finished backup at 23-01-2013 01:00:44 Starting Control File and SPFILE Autobackup at 23-01-2013 01:00:44 piece handle=/home/oracle/app/oracle/flash_recovery_area/ORCL/autobackup/2013_01_23/o1_mf_s_805424444_8hz9owgy_.bkp comment=NONE Finished Control File and SPFILE Autobackup at 23-01-2013 01:00:45
Next step is to put the same datafiles offline.
RMAN> sql 'alter database datafile 9,10 offline'; sql statement: alter database datafile 9,10 offline
Switch to your new datafiles location updating your controlfiles.
RMAN> switch datafile 9,10 to copy; datafile 9 switched to datafile copy "/home/oracle/app/oracle/oradata/orcl/example02.dbf" datafile 10 switched to datafile copy "/home/oracle/app/oracle/oradata/orcl/APEX02.dbf"
Recover your datafiles because some transactions could be occurred between backup as copy datafile command and putting datafiles offline.
RMAN> recover datafile 9,10; Starting recover at 23-01-2013 01:01:45 using channel ORA_DISK_1 starting media recovery media recovery complete, elapsed time: 00:00:00 Finished recover at 23-01-2013 01:01:46
Let datafiles be available to all the users, putting them online:
RMAN> sql 'alter database datafile 9,10 online'; sql statement: alter database datafile 9,10 online
report schema command displays the new location of example02.dbf and APEX02.dbf datafiles.
RMAN> report schema; Report of database schema for database with db_unique_name ORCL List of Permanent Datafiles =========================== File Size(MB) Tablespace RB segs Datafile Name ---- -------- -------------------- ------- ------------------------ 1 911 SYSTEM *** /home/oracle/app/oracle/oradata/orcl/system01.dbf 2 1105 SYSAUX *** /home/oracle/app/oracle/oradata/orcl/sysaux01.dbf 3 475 UNDOTBS1 *** /home/oracle/app/oracle/oradata/orcl/undotbs01.dbf 4 225 USERS *** /home/oracle/app/oracle/oradata/orcl/users01.dbf 5 82 EXAMPLE *** /home/oracle/app/oracle/oradata/orcl/example01.dbf 6 7 APEX *** /home/oracle/app/oracle/oradata/orcl/APEX.dbf 7 1 READ_ONLY *** /home/oracle/app/oracle/oradata/orcl/read_only01.dbf 8 1 ZZZ *** /home/oracle/app/oracle/oradata/orcl/ZZZ01.dbf 9 1 EXAMPLE *** /home/oracle/app/oracle/oradata/orcl/example02.dbf 10 1 APEX *** /home/oracle/app/oracle/oradata/orcl/APEX02.dbf List of Temporary Files ======================= File Size(MB) Tablespace Maxsize(MB) Tempfile Name ---- -------- -------------------- ----------- -------------------- 1 20 TEMP 32767 /home/oracle/app/oracle/oradata/orcl/temp01.dbf 2 20 TEMP 50 /home/oracle/app/oracle/oradata/orcl/temp02.dbf
We hope this information is useful for the reovery session.
For More Detail , You can join us follow:
LinkedIn Group: Oracle Cloud DBAAS
Facebook Page: OracleHelp
