In this post, we are going to learn steps which are use recover a never backed up tablespace after losing its datafile and even the current controlfile after the autobackup feature completes its job. This crash involves the lost of the “never backed up” tablespace and of the current controlfile.
Let’s start with an example. Our instance is up and running.
[oracle@localhost orcl]$ ps -ef | grep smon oracle 13600 1 0 06:11 ? 00:00:00 ora_smon_orcl oracle 13794 13766 0 06:24 pts/5 00:00:00 grep smon
Connect with RMAN client to see persistent settings.
[oracle@localhost orcl]$ rman target / RMAN> show all; using target database control file instead of recovery catalog RMAN configuration parameters for database with db_unique_name ORCL are: CONFIGURE RETENTION POLICY TO REDUNDANCY 1; # default CONFIGURE BACKUP OPTIMIZATION ON; CONFIGURE DEFAULT DEVICE TYPE TO DISK; # default CONFIGURE CONTROLFILE AUTOBACKUP ON; CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO '%F'; # default CONFIGURE DEVICE TYPE DISK BACKUP TYPE TO COMPRESSED BACKUPSET PARALLELISM 1; CONFIGURE DATAFILE BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default CONFIGURE ARCHIVELOG BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default CONFIGURE MAXSETSIZE TO UNLIMITED; # default CONFIGURE ENCRYPTION FOR DATABASE OFF; # default CONFIGURE ENCRYPTION ALGORITHM 'AES128'; # default CONFIGURE COMPRESSION ALGORITHM 'HIGH' AS OF RELEASE 'DEFAULT' OPTIMIZE FOR LOAD TRUE; CONFIGURE ARCHIVELOG DELETION POLICY TO NONE; # default CONFIGURE SNAPSHOT CONTROLFILE NAME TO '/home/oracle/app/oracle/product/11.2.0/dbhome_2/dbs/snapcf_orcl.f'; # default
Connect with sqlplus client and (try to) drop the tablespace YYY including its contents and datafiles.
[oracle@localhost orcl]$ sqlplus / as sysdba SQL> drop tablespace YYY including contents and datafiles; drop tablespace YYY including contents and datafiles * ERROR at line 1: ORA-00959: tablespace 'YYY' does not exist
Now delete all backups and copies. I want to be sure I don’t have a valid backup of YYY tablespace.
[oracle@localhost orcl]$ rman target / RMAN> delete noprompt backup; using target database control file instead of recovery catalog allocated channel: ORA_DISK_1 channel ORA_DISK_1: SID=34 device type=DISK List of Backup Pieces BP Key BS Key Pc# Cp# Status Device Type Piece Name ------- ------- --- --- ----------- ----------- ---------- 139 138 1 1 AVAILABLE DISK /home/oracle/app/oracle/flash_recovery_area/ORCL/backupset/2012_11_27/o1_mf_annnn_TAG20121127T055405_8c9khy7t_.bkp 140 139 1 1 AVAILABLE DISK /home/oracle/app/oracle/flash_recovery_area/ORCL/autobackup/2012_11_27/o1_mf_s_800431150_8c9kshfg_.bkp 141 140 1 1 AVAILABLE DISK /home/oracle/app/oracle/flash_recovery_area/ORCL/backupset/2012_11_27/o1_mf_nnndf_TAG20121127T055407_8c9kj0ty_.bkp 142 141 1 1 AVAILABLE DISK /home/oracle/app/oracle/flash_recovery_area/ORCL/backupset/2012_11_27/o1_mf_annnn_TAG20121127T060546_8c9l5vgg_.bkp 143 142 1 1 AVAILABLE DISK /home/oracle/app/oracle/flash_recovery_area/ORCL/autobackup/2012_11_27/o1_mf_s_800432539_8c9m4wy1_.bkp deleted backup piece backup piece handle=/home/oracle/app/oracle/flash_recovery_area/ORCL/backupset/2012_11_27/o1_mf_annnn_TAG20121127T055405_8c9khy7t_.bkp RECID=139 STAMP=800430846 deleted backup piece backup piece handle=/home/oracle/app/oracle/flash_recovery_area/ORCL/autobackup/2012_11_27/o1_mf_s_800431150_8c9kshfg_.bkp RECID=140 STAMP=800431151 deleted backup piece backup piece handle=/home/oracle/app/oracle/flash_recovery_area/ORCL/backupset/2012_11_27/o1_mf_nnndf_TAG20121127T055407_8c9kj0ty_.bkp RECID=141 STAMP=800430848 deleted backup piece backup piece handle=/home/oracle/app/oracle/flash_recovery_area/ORCL/backupset/2012_11_27/o1_mf_annnn_TAG20121127T060546_8c9l5vgg_.bkp RECID=142 STAMP=800431547 deleted backup piece backup piece handle=/home/oracle/app/oracle/flash_recovery_area/ORCL/autobackup/2012_11_27/o1_mf_s_800432539_8c9m4wy1_.bkp RECID=143 STAMP=800432540 Deleted 5 objects RMAN> delete noprompt copy; released channel: ORA_DISK_1 allocated channel: ORA_DISK_1 channel ORA_DISK_1: SID=34 device type=DISK specification does not match any datafile copy in the repository specification does not match any control file copy in the repository List of Archived Log Copies for database with db_unique_name ORCL ===================================================================== Key Thrd Seq S Low Time ------- ---- ------- - ------------------- 159 1 6 A 27-11-2012 04:11:24 Name: /home/oracle/app/oracle/flash_recovery_area/ORCL/archivelog/2012_11_27/o1_mf_1_6_8c9khxlq_.arc 168 1 7 A 27-11-2012 05:54:05 Name: /home/oracle/app/oracle/flash_recovery_area/ORCL/archivelog/2012_11_27/o1_mf_1_7_8c9m31n0_.arc 160 1 7 A 27-11-2012 05:54:05 Name: /home/oracle/app/oracle/flash_recovery_area/ORCL/archivelog/2012_11_27/o1_mf_1_7_8c9l5tb5_.arc 169 1 8 A 27-11-2012 06:05:46 Name: /home/oracle/app/oracle/flash_recovery_area/ORCL/archivelog/2012_11_27/o1_mf_1_8_8c9m31rn_.arc 161 1 8 A 27-11-2012 06:05:46 Name: /home/oracle/app/oracle/flash_recovery_area/ORCL/archivelog/2012_11_27/o1_mf_1_8_8c9lcht5_.arc 170 1 9 A 27-11-2012 06:08:47 Name: /home/oracle/app/oracle/flash_recovery_area/ORCL/archivelog/2012_11_27/o1_mf_1_9_8c9m31yz_.arc deleted archived log archived log file name=/home/oracle/app/oracle/flash_recovery_area/ORCL/archivelog/2012_11_27/o1_mf_1_6_8c9khxlq_.arc RECID=159 STAMP=800430845 deleted archived log archived log file name=/home/oracle/app/oracle/flash_recovery_area/ORCL/archivelog/2012_11_27/o1_mf_1_7_8c9m31n0_.arc RECID=168 STAMP=800432481 deleted archived log archived log file name=/home/oracle/app/oracle/flash_recovery_area/ORCL/archivelog/2012_11_27/o1_mf_1_7_8c9l5tb5_.arc RECID=160 STAMP=800431546 deleted archived log archived log file name=/home/oracle/app/oracle/flash_recovery_area/ORCL/archivelog/2012_11_27/o1_mf_1_8_8c9m31rn_.arc RECID=169 STAMP=800432481 deleted archived log archived log file name=/home/oracle/app/oracle/flash_recovery_area/ORCL/archivelog/2012_11_27/o1_mf_1_8_8c9lcht5_.arc RECID=161 STAMP=800431979 deleted archived log archived log file name=/home/oracle/app/oracle/flash_recovery_area/ORCL/archivelog/2012_11_27/o1_mf_1_9_8c9m31yz_.arc RECID=170 STAMP=800432482 Deleted 6 objects
Now it’s time to take a full database backup including the archived redo log. This backup doesn’t contain a tablespace named YYY.
RMAN> backup database plus archivelog; Starting backup at 27-11-2012 06:25:54 current log archived using channel ORA_DISK_1 channel ORA_DISK_1: starting compressed archived log backup set channel ORA_DISK_1: specifying archived log(s) in backup set input archived log thread=1 sequence=1 RECID=171 STAMP=800432754 channel ORA_DISK_1: starting piece 1 at 27-11-2012 06:25:54 channel ORA_DISK_1: finished piece 1 at 27-11-2012 06:25:55 piece handle=/home/oracle/app/oracle/flash_recovery_area/ORCL/backupset/2012_11_27/o1_mf_annnn_TAG20121127T062554_8c9mclss_.bkp tag=TAG20121127T062554 comment=NONE channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01 Finished backup at 27-11-2012 06:25:55 Starting backup at 27-11-2012 06:25:56 using channel ORA_DISK_1 channel ORA_DISK_1: starting compressed full datafile backup set channel ORA_DISK_1: specifying datafile(s) in backup set input datafile file number=00002 name=/home/oracle/app/oracle/oradata/orcl/sysaux01.dbf input datafile file number=00001 name=/home/oracle/app/oracle/oradata/orcl/system01.dbf input datafile file number=00004 name=/home/oracle/app/oracle/oradata/orcl/users01.dbf input datafile file number=00005 name=/home/oracle/app/oracle/oradata/orcl/example01.dbf input datafile file number=00003 name=/home/oracle/app/oracle/oradata/orcl/undotbs01.dbf input datafile file number=00006 name=/home/oracle/app/oracle/oradata/orcl/APEX_1930613455248703.dbf input datafile file number=00007 name=/home/oracle/app/oracle/oradata/orcl/read_only01.dbf input datafile file number=00009 name=/home/oracle/app/oracle/oradata/orcl/example02.dbf channel ORA_DISK_1: starting piece 1 at 27-11-2012 06:25:56 channel ORA_DISK_1: finished piece 1 at 27-11-2012 06:36:22 piece handle=/home/oracle/app/oracle/flash_recovery_area/ORCL/backupset/2012_11_27/o1_mf_nnndf_TAG20121127T062556_8c9mcnw9_.bkp tag=TAG20121127T062556 comment=NONE channel ORA_DISK_1: backup set complete, elapsed time: 00:10:26 Finished backup at 27-11-2012 06:36:22 Starting backup at 27-11-2012 06:36:22 current log archived using channel ORA_DISK_1 channel ORA_DISK_1: starting compressed archived log backup set channel ORA_DISK_1: specifying archived log(s) in backup set input archived log thread=1 sequence=2 RECID=172 STAMP=800433383 channel ORA_DISK_1: starting piece 1 at 27-11-2012 06:36:23 channel ORA_DISK_1: finished piece 1 at 27-11-2012 06:36:25 piece handle=/home/oracle/app/oracle/flash_recovery_area/ORCL/backupset/2012_11_27/o1_mf_annnn_TAG20121127T063623_8c9mz80m_.bkp tag=TAG20121127T063623 comment=NONE channel ORA_DISK_1: backup set complete, elapsed time: 00:00:02 Finished backup at 27-11-2012 06:36:25 Starting Control File and SPFILE Autobackup at 27-11-2012 06:36:25 piece handle=/home/oracle/app/oracle/flash_recovery_area/ORCL/autobackup/2012_11_27/o1_mf_s_800433385_8c9mz9sj_.bkp comment=NONE Finished Control File and SPFILE Autobackup at 27-11-2012 06:36:28
My only current backup is formed by the following backup sets:
RMAN> list backup; List of Backup Sets =================== BS Key Size Device Type Elapsed Time Completion Time ------- ---------- ----------- ------------ ------------------- 143 247.00K DISK 00:00:00 27-11-2012 06:25:54 BP Key: 144 Status: AVAILABLE Compressed: YES Tag: TAG20121127T062554 Piece Name: /home/oracle/app/oracle/flash_recovery_area/ORCL/backupset/2012_11_27/o1_mf_annnn_TAG20121127T062554_8c9mclss_.bkp List of Archived Logs in backup set 143 Thrd Seq Low SCN Low Time Next SCN Next Time ---- ------- ---------- ------------------- ---------- --------- 1 1 14560297 27-11-2012 06:21:21 14560798 27-11-2012 06:25:54 BS Key Type LV Size Device Type Elapsed Time Completion Time ------- ---- -- ---------- ----------- ------------ ------------------- 144 Full 9.45M DISK 00:00:02 27-11-2012 06:31:37 BP Key: 145 Status: AVAILABLE Compressed: NO Tag: TAG20121127T063135 Piece Name: /home/oracle/app/oracle/flash_recovery_area/ORCL/autobackup/2012_11_27/o1_mf_s_800433095_8c9mp8mn_.bkp SPFILE Included: Modification time: 27-11-2012 06:12:53 SPFILE db_unique_name: ORCL Control File Included: Ckp SCN: 14560974 Ckp time: 27-11-2012 06:31:35 BS Key Type LV Size Device Type Elapsed Time Completion Time ------- ---- -- ---------- ----------- ------------ ------------------- 145 Full 680.23M DISK 00:10:18 27-11-2012 06:36:14 BP Key: 146 Status: AVAILABLE Compressed: YES Tag: TAG20121127T062556 Piece Name: /home/oracle/app/oracle/flash_recovery_area/ORCL/backupset/2012_11_27/o1_mf_nnndf_TAG20121127T062556_8c9mcnw9_.bkp List of Datafiles in backup set 145 File LV Type Ckp SCN Ckp Time Name ---- -- ---- ---------- ------------------- ---- 1 Full 14560804 27-11-2012 06:25:56 /home/oracle/app/oracle/oradata/orcl/system01.dbf 2 Full 14560804 27-11-2012 06:25:56 /home/oracle/app/oracle/oradata/orcl/sysaux01.dbf 3 Full 14560804 27-11-2012 06:25:56 /home/oracle/app/oracle/oradata/orcl/undotbs01.dbf 4 Full 14560804 27-11-2012 06:25:56 /home/oracle/app/oracle/oradata/orcl/users01.dbf 5 Full 14560804 27-11-2012 06:25:56 /home/oracle/app/oracle/oradata/orcl/example01.dbf 6 Full 14560804 27-11-2012 06:25:56 /home/oracle/app/oracle/oradata/orcl/APEX_1930613455248703.dbf 7 Full 13915815 02-09-2012 22:03:34 /home/oracle/app/oracle/oradata/orcl/read_only01.dbf 9 Full 14560804 27-11-2012 06:25:56 /home/oracle/app/oracle/oradata/orcl/example02.dbf BS Key Size Device Type Elapsed Time Completion Time ------- ---------- ----------- ------------ ------------------- 146 224.00K DISK 00:00:01 27-11-2012 06:36:24 BP Key: 147 Status: AVAILABLE Compressed: YES Tag: TAG20121127T063623 Piece Name: /home/oracle/app/oracle/flash_recovery_area/ORCL/backupset/2012_11_27/o1_mf_annnn_TAG20121127T063623_8c9mz80m_.bkp List of Archived Logs in backup set 146 Thrd Seq Low SCN Low Time Next SCN Next Time ---- ------- ---------- ------------------- ---------- --------- 1 2 14560798 27-11-2012 06:25:54 14561201 27-11-2012 06:36:23 BS Key Type LV Size Device Type Elapsed Time Completion Time ------- ---- -- ---------- ----------- ------------ ------------------- 147 Full 9.45M DISK 00:00:01 27-11-2012 06:36:26 BP Key: 148 Status: AVAILABLE Compressed: NO Tag: TAG20121127T063625 Piece Name: /home/oracle/app/oracle/flash_recovery_area/ORCL/autobackup/2012_11_27/o1_mf_s_800433385_8c9mz9sj_.bkp SPFILE Included: Modification time: 27-11-2012 06:12:53 SPFILE db_unique_name: ORCL Control File Included: Ckp SCN: 14561210 Ckp time: 27-11-2012 06:36:25
RMAN> list copy; specification does not match any datafile copy in the repository specification does not match any control file copy in the repository List of Archived Log Copies for database with db_unique_name ORCL ===================================================================== Key Thrd Seq S Low Time ------- ---- ------- - ------------------- 171 1 1 A 27-11-2012 06:21:21 Name: /home/oracle/app/oracle/flash_recovery_area/ORCL/archivelog/2012_11_27/o1_mf_1_1_8c9mcl8b_.arc 172 1 2 A 27-11-2012 06:25:54 Name: /home/oracle/app/oracle/flash_recovery_area/ORCL/archivelog/2012_11_27/o1_mf_1_2_8c9mz7dq_.arc
I’m going to create the new YYY tablespace and a new table (IMPORTANT_YYY_TRANSACTION) containing some committed rows.
[oracle@localhost orcl]$ sqlplus / as sysdba SQL> create tablespace YYY DATAFILE '/home/oracle/app/oracle/oradata/orcl/tbs01.dbf' size 1M autoextend on next 1M maxsize 10M; Tablespace created. SQL> create table IMPORTANT_YYY_TRANSACTION (a number, b number) tablespace YYY; Table created. SQL> begin for indx IN 1 .. 1000 loop execute immediate 'insert into IMPORTANT_YYY_TRANSACTION(a,b) values (:1, :2)' using indx, indx*2; end loop; commit; end; 2 3 4 5 6 7 8 / PL/SQL procedure successfully completed. SQL> alter system switch logfile; System altered.
In Oracle Database 11gR2 the autobackup feature starts within a delay trying to encompass all of the structural changes made to the database rather than creating a new backup of the controlfile on each structural change. This delay could be critical for you and for your transactions and simply implies different ways to restore and recover them. Because I don’t want to wait 5 minutes before autobackup controlfile feature starts and completes its job, I force the database to get a controlfile copy using RMAN. The important concept in this scenario is that I have a valid controlfile copy taken after the creation of a new and never backed up tablespace.
[oracle@localhost orcl]$ rman target / RMAN> backup current controlfile; Starting backup at 27-11-2012 06:50:02 using target database control file instead of recovery catalog allocated channel: ORA_DISK_1 channel ORA_DISK_1: SID=48 device type=DISK channel ORA_DISK_1: starting compressed full datafile backup set channel ORA_DISK_1: specifying datafile(s) in backup set including current control file in backup set channel ORA_DISK_1: starting piece 1 at 27-11-2012 06:50:06 channel ORA_DISK_1: finished piece 1 at 27-11-2012 06:50:07 piece handle=/home/oracle/app/oracle/flash_recovery_area/ORCL/backupset/2012_11_27/o1_mf_ncnnf_TAG20121127T065003_8c9nryp2_.bkp tag=TAG20121127T065003 comment=NONE channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01 Finished backup at 27-11-2012 06:50:07 Starting Control File and SPFILE Autobackup at 27-11-2012 06:50:07 piece handle=/home/oracle/app/oracle/flash_recovery_area/ORCL/autobackup/2012_11_27/o1_mf_s_800434207_8c9ns04h_.bkp comment=NONE Finished Control File and SPFILE Autobackup at 27-11-2012 06:50:08
Let’s simulate a loss of our current controlfile and of our “never backed up” new tablespace (YYY).
[oracle@localhost orcl]$ mv control01.ctl control01.ctl.bck [oracle@localhost orcl]$ mv /home/oracle/app/oracle/flash_recovery_area/orcl/control02.ctl /home/oracle/app/oracle/flash_recovery_area/orcl/control02.ctl.bck [oracle@localhost orcl]$ mv tbs01.dbf tbs01.dbf.bck
The instance is still up and running
[oracle@localhost orcl]$ ps -ef|grep smon oracle 13600 1 0 06:11 ? 00:00:01 ora_smon_orcl oracle 14016 13766 1 06:50 pts/5 00:00:00 grep smon
I force the instance to abort
[oracle@localhost orcl]$ sqlplus / as sysdba SQL> shutdown abort; ORACLE instance shut down.
Let’s try to recover our database. Connecting the RMAN client, the output log shows a “not started” instance.
[oracle@localhost orcl]$ rman target / Recovery Manager: Release 11.2.0.2.0 - Production on Tue Nov 27 06:51:19 2012 Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved. connected to target database (not started)
Let’s start the instance in nomount mode.
RMAN> startup nomount; Oracle instance started Total System Global Area 456146944 bytes Fixed Size 1344840 bytes Variable Size 394267320 bytes Database Buffers 54525952 bytes Redo Buffers 6008832 bytes
We have lost our current controlfile and want to restore it using the autobackup feature. RMAN is able to search from the available backup set and find the right one, in my case it is able to use “o1_mf_s_800434207_8c9ns04h_.bkp” backup set
RMAN> restore controlfile from autobackup; Starting restore at 27-11-2012 06:51:38 using target database control file instead of recovery catalog allocated channel: ORA_DISK_1 channel ORA_DISK_1: SID=19 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_11_27/o1_mf_s_800434207_8c9ns04h_.bkp found in the recovery area AUTOBACKUP search with format "%F" not attempted because DBID was not set channel ORA_DISK_1: restoring control file from AUTOBACKUP /home/oracle/app/oracle/flash_recovery_area/ORCL/autobackup/2012_11_27/o1_mf_s_800434207_8c9ns04h_.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 27-11-2012 06:51:44
The restored controlfile has references about YYY tablespace: that autobackup of the control file was created indeed after the creation of the lost tablespace.
[oracle@localhost orcl]$ strings /home/oracle/app/oracle/oradata/orcl/control01.ctl|grep -i YYY YYY YYY
Connect again with RMAN client to the “not mounted” instance.
[oracle@localhost orcl]$ rman target / Recovery Manager: Release 11.2.0.2.0 - Production on Tue Nov 27 06:52:16 2012 Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved. connected to target database: ORCL (not mounted)
A controlfile is now available to mount the instance.
RMAN> alter database mount; using target database control file instead of recovery catalog database mounted
The report schema command in “List of Permanent Datafiles” section has information about YYY tablespace and its datafile, even if it is not able to get the right size.
RMAN> report schema; Starting implicit crosscheck backup at 27-11-2012 06:52:27 allocated channel: ORA_DISK_1 channel ORA_DISK_1: SID=1 device type=DISK Crosschecked 6 objects Finished implicit crosscheck backup at 27-11-2012 06:52:29 Starting implicit crosscheck copy at 27-11-2012 06:52:29 using channel ORA_DISK_1 Finished implicit crosscheck copy at 27-11-2012 06:52:29 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_07_21/o1_mf_s_789203952_80ogm1c3_.bkp File Name: /home/oracle/app/oracle/flash_recovery_area/ORCL/autobackup/2012_07_21/o1_mf_s_789209074_80omm3d0_.bkp File Name: /home/oracle/app/oracle/flash_recovery_area/ORCL/autobackup/2012_11_27/o1_mf_s_800431548_8c9l5xbv_.bkp File Name: /home/oracle/app/oracle/flash_recovery_area/ORCL/autobackup/2012_11_27/o1_mf_s_800434207_8c9ns04h_.bkp File Name: /home/oracle/app/oracle/flash_recovery_area/ORCL/autobackup/2012_10_05/o1_mf_s_795852591_86xq10nr_.bkp File Name: /home/oracle/app/oracle/flash_recovery_area/ORCL/autobackup/2012_10_05/o1_mf_s_795834324_86x564xb_.bkp File Name: /home/oracle/app/oracle/flash_recovery_area/ORCL/autobackup/2012_09_26/o1_mf_s_795045371_867q3d12_.bkp File Name: /home/oracle/app/oracle/flash_recovery_area/ORCL/autobackup/2012_07_17/o1_mf_s_788864449_80c39jlo_.bkp File Name: /home/oracle/app/oracle/flash_recovery_area/ORCL/autobackup/2012_11_23/o1_mf_s_800101490_8bzkk05s_.bkp RMAN-06139: WARNING: control file is not current for 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 65 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_1930613455248703 *** /home/oracle/app/oracle/oradata/orcl/APEX_1930613455248703.dbf 7 1 READ_ONLY *** /home/oracle/app/oracle/oradata/orcl/read_only01.dbf 8 0 YYY *** /home/oracle/app/oracle/oradata/orcl/tbs01.dbf 9 1 EXAMPLE2 *** /home/oracle/app/oracle/oradata/orcl/example02.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
It’s now possible to restore YYY tablespace
RMAN> restore tablespace YYY; Starting restore at 27-11-2012 06:52:43 using channel ORA_DISK_1 creating datafile file number=8 name=/home/oracle/app/oracle/oradata/orcl/tbs01.dbf restore not done; all files read only, offline, or already restored Finished restore at 27-11-2012 06:52:43
On my local directory “tbs01.dbf” datafile is created.
[oracle@localhost orcl]$ ll -lrt total 2825100 -rw-rw---- 1 oracle oracle 1056768 Sep 3 22:00 read_only01.dbf drwxrwxr-x 2 oracle oracle 4096 Nov 9 07:51 non_default_location -rw-rw---- 1 oracle oracle 20979712 Nov 27 02:47 temp02.dbf -rw-rw---- 1 oracle oracle 20979712 Nov 27 06:25 temp01.dbf -rw-rw---- 1 oracle oracle 52429312 Nov 27 06:36 redo02.log -rw-rw---- 1 oracle oracle 52429312 Nov 27 06:36 redo02b.log -rw-rw---- 1 oracle oracle 235937792 Nov 27 06:40 users01.dbf -rw-rw---- 1 oracle oracle 1056768 Nov 27 06:40 example02.dbf -rw-rw---- 1 oracle oracle 85991424 Nov 27 06:40 example01.dbf -rw-rw---- 1 oracle oracle 7348224 Nov 27 06:40 APEX_1930613455248703.dbf -rw-rw---- 1 oracle oracle 1056768 Nov 27 06:48 tbs01.dbf.bck -rw-rw---- 1 oracle oracle 52429312 Nov 27 06:49 redo03.log -rw-rw---- 1 oracle oracle 52429312 Nov 27 06:49 redo03b.log -rw-rw---- 1 oracle oracle 68165632 Nov 27 06:50 undotbs01.dbf -rw-rw---- 1 oracle oracle 955260928 Nov 27 06:50 system01.dbf -rw-rw---- 1 oracle oracle 1158684672 Nov 27 06:50 sysaux01.dbf -rw-rw---- 1 oracle oracle 52429312 Nov 27 06:50 redo01.log -rw-rw---- 1 oracle oracle 52429312 Nov 27 06:50 redo01b.log -rw-rw---- 1 oracle oracle 9846784 Nov 27 06:51 control01.ctl.bck -rw-rw---- 1 oracle oracle 1056768 Nov 27 06:52 tbs01.dbf -rw-rw---- 1 oracle oracle 9846784 Nov 27 06:52 control01.ctl
At this step you cannot directly recover your tablespace as the “RMAN-06067: RECOVER DATABASE required with a backup or created control file” error suggests.
[oracle@localhost orcl]$ rman target / Recovery Manager: Release 11.2.0.2.0 - Production on Tue Nov 27 06:52:59 2012 Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved. connected to target database: ORCL (DBID=1229390655, not open) RMAN> recover tablespace YYY; Starting recover at 27-11-2012 06:53:09 using target database control file instead of recovery catalog allocated channel: ORA_DISK_1 channel ORA_DISK_1: SID=19 device type=DISK RMAN-00571: =========================================================== RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS =============== RMAN-00571: =========================================================== RMAN-03002: failure of recover command at 11/27/2012 06:53:11 RMAN-06067: RECOVER DATABASE required with a backup or created control file
You have to recover the entire database…
RMAN> recover database; Starting recover at 27-11-2012 06:53:17 using channel ORA_DISK_1 datafile 7 not processed because file is read-only starting media recovery archived log for thread 1 with sequence 3 is already on disk as file /home/oracle/app/oracle/flash_recovery_area/ORCL/archivelog/2012_11_27/o1_mf_1_3_8c9nr8rf_.arc archived log for thread 1 with sequence 4 is already on disk as file /home/oracle/app/oracle/oradata/orcl/redo01.log archived log file name=/home/oracle/app/oracle/flash_recovery_area/ORCL/archivelog/2012_11_27/o1_mf_1_3_8c9nr8rf_.arc thread=1 sequence=3 archived log file name=/home/oracle/app/oracle/oradata/orcl/redo01.log thread=1 sequence=4 media recovery complete, elapsed time: 00:00:00 Finished recover at 27-11-2012 06:53:18
…and finally open it with the resetlogs option
RMAN> alter database open resetlogs; database opened
All previous data inserted and committed into IMPORTANT_YYY_TRANSACTION table are available again.
[oracle@localhost orcl]$ sqlplus / as sysdba SQL> select count(*) from IMPORTANT_YYY_TRANSACTION; COUNT(*) ---------- 1000
Thank you for giving your valuable time to read the above information.
For More Detail , You can join us follow:
LinkedIn Group: Oracle Cloud DBAAS
Facebook Page: OracleHelp
