This crash envolves 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 smonConnect 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'; # defaultConnect 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 existNow 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:28My 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:08Let'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.bckThe 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 smonI 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 bytesWe 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:44The 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 YYYConnect 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 mountedThe 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.dbfIt'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:43On 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.ctlAt 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 fileYou 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 openedAll 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
SQL>
That's all.
59 comments:
Hi
Thanks for sharing good post on Rman.
thanks
Thank you for visiting my blog.
Regards,
Marco V.
If you wish for to improve your experience only keep visiting this website and be updated with the most recent news posted here.
my page business local directory
An outstanding share! I've just forwarded this onto a colleague who has been doing a little research on this. And he actually bought me dinner due to the fact that I discovered it for him... lol. So let me reword this.... Thanks for the meal!! But yeah, thanx for spending the time to talk about this subject here on your internet site.
Feel free to surf to my web-site sample research papers
When I initially left a comment I appear to have clicked the
-Notify me when new comments are added- checkbox and from now on every
time a comment is added I recieve four emails with the same comment.
There has to be a way you can remove me from that service?
Kudos!
My web-site http://homecashsuccess.org
Thanks for finally talking about > "How to recover a never backed up tablespace after losing its datafile and even the current controlfile after the autobackup feature completes its job" < Loved it!
Also visit my web blog; raspberry ketone
An intriguing discussion is worth comment. I believe that you ought to write more about this issue, it
may not be a taboo matter but generally folks don't speak about such subjects. To the next! Kind regards!!
Here is my web-site; frankenstein essay
Great article.
Also visit my site - local business locator
Magnificent goods from you, man. I've understand your stuff previous to and you're simply too excellent.
I really like what you've acquired here, certainly like what you're stating and the best
way through which you assert it. You're making it enjoyable and you still take care of to stay it wise. I can not wait to learn much more from you. This is actually a wonderful web site.
Feel free to surf to my blog - google local business listing
Nice blog here! Additionally your site so much up very fast!
What host are you the usage of? Can I get your associate hyperlink to your host?
I want my web site loaded up as fast as yours lol
my weblog local business directory usa
Howdy I am so grateful I found your site, I really found you by
mistake, while I was looking on Digg for something else, Anyways I
am here now and would just like to say thanks for a incredible post and a
all round enjoyable blog (I also love the theme/design), I don't have time to browse it all at the moment but I have book-marked it and also added in your RSS feeds, so when I have time I will be back to read a lot more, Please do keep up the great work.
Also visit my homepage: online real estate directory
Do you have a spam issue on this website; I also am a blogger, and I was wondering your situation; many of us have developed some nice
methods and we are looking to swap strategies with others,
please shoot me an email if interested.
Have a look at my blog: film forum manhattan
Hmm is anyone else having problems with the images on this blog
loading? I'm trying to figure out if its a problem on my end or if it's the
blog. Any feedback would be greatly appreciated.
my blog post; sydney white pages for business
What i don't understood is in fact how you are now not actually a lot more well-preferred than you may be right now. You're
so intelligent. You realize therefore significantly when it comes
to this subject, made me in my view believe it from so many varied angles.
Its like men and women are not interested except it's one thing to do with Lady gaga! Your individual stuffs excellent. All the time care for it up!
Review my blog ... local real estate
This web site certainly has all of the information and facts I
needed about this subject and didn't know who to ask.
My weblog; Wraeclast
An intriguing discussion is worth comment. I do think that you ought to publish more about this subject matter,
it might not be a taboo subject but generally people do not talk about these topics.
To the next! Many thanks!!
Take a look at my web-site ... boersenkreis-halle.de
I seriously love your site.. Great colors & theme. Did you develop this amazing site yourself?
Please reply back as I'm trying to create my very own blog and want to learn where you got this from or what the theme is named. Appreciate it!
Also visit my site - bukkit 1.5.2
continuously i used to read smaller articles that also
clear their motive, and that is also happening with this piece of writing which
I am reading now.
Also visit my web blog; Mindcrack Gameplay
Whoa! This blog looks exactly like my old one!
It's on a completely different topic but it has pretty much the same layout and design. Excellent choice of colors!
my weblog; vestal
Hi there it's me, I am also visiting this web page regularly, this website is in fact fastidious and the viewers are actually sharing good thoughts.
My page - comparative essay example
I could not resist commenting. Well written!
my web site: bizsearch local
This text is invaluable. How can I find out more?
My web site - business lookup
Hey! I just noticed another message in another blog that seemed like this.
How do you know all this stuff? That’s one cool post.
Also visit my blog - i'm having trouble getting pregnant
It's going to be ending of mine day, except before ending I am reading this fantastic paragraph to increase my knowledge.
my weblog ... upgrade computer charlotte north carolina
I'd like to thank you for the efforts you've put in writing this site.
I am hoping to view the same high-grade content by you later on as well.
In truth, your creative writing abilities has motivated me to get my own
blog now ;)
my web-site - raspberry ketone diet
Superb, what a website it is! This website gives helpful information to us, keep it
up.
Here is my homepage USA real estate agents directory
Hey there! Someone in my Facebook group shared this website with us so I came to look it over.
I'm definitely enjoying the information. I'm book-marking
and will be tweeting this to my followers! Outstanding blog and brilliant
design and style.
Take a look at my blog post: high school research paper topics
I am curious to find out what blog system you're utilizing? I'm
experiencing some small security problems with my latest website and I'd like to find something more risk-free. Do you have any recommendations?
my blog post purchase essays online
Hi, I do think this is a great site. I stumbledupon it ;) I may revisit
yet again since i have book-marked it. Money and freedom
is the best way to change, may you be rich and continue to guide other people.
Here is my web blog film chat bott竦ilm chat bottestar manhattan curepipe
Thanks , I've just been searching for info about this subject for a while and yours is the best I've discovered till now.
But, what in regards to the conclusion? Are you sure about the source?
My web-site - apa research paper outline format
You actually make it appear so easy with your presentation but
I in finding this matter to be really something which I feel
I'd by no means understand. It kind of feels too complex and extremely wide for me. I am taking a look ahead to your next post, I will attempt to get the hang of it!
my homepage; top 10 research paper topics
Hello mates, its great post about tutoringand fully defined, keep it up all
the time.
Feel free to surf to my web-site ... computer parts charlotte north carolina
I've been browsing online more than three hours these days, but I by no means discovered any interesting article like yours. It's beautiful price enough
for me. Personally, if all webmasters and bloggers made just right content material as you did,
the net shall be much more useful than ever before.
my blog - analysis essay
Hey there just wanted to give you a quick heads up. The
text in your content seem to be running off the screen in Opera.
I'm not sure if this is a formatting issue or something to do with browser compatibility but I figured I'd post to let you know.
The design look great though! Hope you get the issue fixed soon.
Kudos
Here is my webpage - venapro hemorrhoid formula
Hey great blog! Does running a blog such as this take a large amount of work?
I've absolutely no understanding of programming however I had been hoping to start my own blog soon. Anyhow, if you have any suggestions or tips for new blog owners please share. I know this is off topic but I just wanted to ask. Appreciate it!
Also visit my web page: local business listings usa
Thanks very interesting blog!
Review my blog :: Buy Revitol Dermasis
I pay a visit day-to-day a few web sites and sites to read
posts, but this website offers quality based writing.
my blog post - mollahacikoyu.net
Hi this is kinda of off topic but I was wondering if blogs use WYSIWYG editors or if you have
to manually code with HTML. I'm starting a blog soon but have no coding experience so I wanted to get advice from someone with experience. Any help would be greatly appreciated!
Feel free to visit my web page: online yellow pages for small business
What i don't realize is in truth how you are now not really much more neatly-liked than you may be now. You are so intelligent. You already know therefore significantly with regards to this matter, made me individually believe it from a lot of varied angles. Its like women and men don't seem to be interested unless
it is something to do with Woman gaga! Your individual stuffs nice.
Always take care of it up!
Here is my web blog ... Gameplay
I am genuinely grateful to the owner of this website who has shared this wonderful article at here.
Feel free to visit my web-site :: Minecraft Gameplay Part 1
I go to see every day some blogs and blogs to read
articles or reviews, however this blog presents feature based content.
Have a look at my blog - apa term paper template
I'd like to thank you for the efforts you've put in writing this site.
I really hope to check out the same high-grade content by you later on as well.
In fact, your creative writing abilities has motivated me to get my own, personal blog now ;)
Also visit my webpage: film discussion guide
I think the admin of this web page is actually working hard for his site, as here every stuff is quality based
information.
Here is my website :: real estate listing
I comment whenever I especially enjoy a post on a blog or if
I have something to contribute to the discussion.
It's caused by the sincerness communicated in the article I read. And on this article "How to recover a never backed up tablespace after losing its datafile and even the current controlfile after the autobackup feature completes its job". I was excited enough to drop a commenta response :-) I actually do have 2 questions for you if it's okay.
Is it just me or do some of these remarks come across like they are written by brain dead folks?
:-P And, if you are posting on other social sites, I would like to keep up with everything new
you have to post. Could you make a list all of all
your public sites like your Facebook page, twitter feed, or linkedin
profile?
Feel free to visit my homepage US real estate agent directories
I do not know whether it's just me or if everyone else experiencing problems with your website. It appears as though some of the text on your content are running off the screen. Can somebody else please comment and let me know if this is happening to them as well? This may be a issue with my web browser because I've had this happen previously.
Cheers
Feel free to visit my weblog local whitepages
Link exchange is nothing else however it is simply placing
the other person's website link on your page at appropriate place and other person will also do similar in favor of you.
Also visit my web site: charlotte north carolina computers
Hello there I am so thrilled I found your
webpage, I really found you by accident, while I was researching on Askjeeve for something
else, Nonetheless I am here now and would just like to say cheers for a remarkable post and a all round thrilling blog (I also love the theme/design), I don’t have time
to browse it all at the moment but I have saved it and also included your RSS feeds,
so when I have time I will be back to read a great deal more, Please
do keep up the awesome work.
my web-site new businesses directory ()
Hi there! This is kind of off topic but I need some guidance from an
established blog. Is it tough to set up your own blog?
I'm not very techincal but I can figure things out pretty quick. I'm thinking about creating my own but I'm not sure where to begin. Do you have any ideas or suggestions? Many thanks
My webpage - format for writing a research paper
This is the perfect web site for anyone who wishes to understand this topic.
You realize so much its almost tough to argue with you (not that I
really would want to…HaHa). You definitely put a new spin
on a subject that has been discussed for decades.
Great stuff, just excellent!
Here is my web-site - research paper writing help ()
hermes jypsiere bags
Hi, I do think this is an excellent web site. I stumbledupon it ;) I may revisit yet again since i have saved
as a favorite it. Money and freedom is the best
way to change, may you be rich and continue to help other people.
I absolutely love your blog and find a lot of your post's to
be just what I'm looking for. Does one offer guest writers to write content for you?
I wouldn't mid composing a post or elaborating on a lot of the subjects you write in relation to here.
Again, awesome webb site!
Here is my web-site ... hay day game download pc
My brother recommended I might like his web site.
He was entirely right. This post truly made my day.
Yoou cann't imagine simply how much time I had spent for this information! Thanks!
My web site ... hay day gake download foor mac; ,
oakley sunglasses, prada handbags, oakley sunglasses, longchamp handbags, longchamp handbags, louboutin shoes, louis vuitton handbags, coach factory outlet, tiffany and co, coach purses, louis vuitton outlet, polo ralph lauren outlet, air max, prada outlet, longchamp outlet, oakley sunglasses cheap, ray ban sunglasses, louboutin outlet, michael kors outlet, michael kors outlet, tiffany and co, burberry outlet, christian louboutin shoes, coach outlet store online, jordan shoes, polo ralph lauren outlet, louboutin, kate spade handbags, michael kors outlet, coach outlet, air max, gucci outlet, michael kors outlet, ray ban sunglasses, chanel handbags, michael kors outlet, tory burch outlet, nike free, kate spade outlet, louis vuitton outlet, burberry outlet, louis vuitton outlet stores, louis vuitton, nike shoes, michael kors outlet
ugg, hollister, abercrombie and fitch, longchamp, nfl jerseys, lululemon outlet, nike trainers, abercrombie and fitch, soccer shoes, valentino shoes, nike roshe, birkin bag, reebok outlet, insanity workout, instyler, mont blanc, vans shoes, new balance shoes, beats by dre, uggs outlet, ugg australia, giuseppe zanotti, rolex watches, nike roshe run, herve leger, babyliss pro, marc jacobs, barbour, ghd, north face outlet, celine handbags, jimmy choo outlet, asics running shoes, mac cosmetics, north face jackets, bottega veneta, wedding dresses, soccer jerseys, ugg boots, chi flat iron, mcm handbags, p90x, ugg pas cher, nike huarache, ferragamo shoes
converse, air max, gucci, canada goose, juicy couture outlet, canada goose, wedding dresses, moncler, ralph lauren, lancel, montre homme, moncler, louboutin, oakley, karen millen, vans, coach outlet store online, air max, canada goose jackets, ugg, hollister clothing store, louis vuitton, baseball bats, hollister, rolex watches, juicy couture outlet, iphone 6 cases, canada goose uk, canada goose outlet, ugg, moncler, moncler outlet, timberland boots, hollister, supra shoes, moncler, canada goose, converse shoes, toms shoes, moncler, moncler, canada goose, ugg boots, ray ban, parajumpers, canada goose
jordan shoes, christian louboutin, uggs outlet, michael kors outlet online, uggs on sale, louis vuitton outlet, louis vuitton outlet, louis vuitton, ray ban sunglasses, replica watches, christian louboutin uk, chanel handbags, michael kors outlet online, uggs outlet, longchamp outlet, nike air max, michael kors outlet, burberry handbags, tiffany and co, polo outlet, nike free, nike air max, ugg boots, oakley sunglasses, ray ban sunglasses, michael kors outlet online, oakley sunglasses, christian louboutin outlet, longchamp outlet, prada handbags, gucci handbags, prada outlet, oakley sunglasses wholesale, michael kors outlet, oakley sunglasses, kate spade outlet, christian louboutin shoes, louis vuitton outlet, tory burch outlet, ugg boots, michael kors outlet online, burberry outlet, cheap oakley sunglasses, louis vuitton, ray ban sunglasses, nike outlet, longchamp outlet
moncler uk, louis vuitton, thomas sabo, wedding dresses, barbour, moncler, gucci, montre pas cher, supra shoes, hollister, barbour uk, nike air max, karen millen uk, pandora uk, moncler, canada goose uk, coach outlet, ugg, juicy couture outlet, swarovski, canada goose, louis vuitton, moncler outlet, louis vuitton, hollister, ugg,ugg australia,ugg italia, canada goose outlet, replica watches, pandora jewelry, ugg,uggs,uggs canada, moncler, canada goose outlet, ugg pas cher, louis vuitton, juicy couture outlet, swarovski crystal, louis vuitton, canada goose, pandora charms, canada goose outlet, links of london, marc jacobs, lancel, converse, converse outlet, toms shoes, doudoune moncler, moncler, pandora jewelry, canada goose jackets, vans, canada goose
Best Minecraft skins you can find here skins4minecraft.com
“I love women who are bosses and who don’t constantly worry about what their employees think of them. I love women who don’t ask, ‘Is that OK?’ after everything they sayBalance Shoes One of the purposes of this website is to help people about find Top Balance Shoes and to help them get their good qualityiula itum is a Tausug dish. It is exotic even to me. I just had my first taste of the famous Tiula Itum today at Khisna's Muslim Cuisine in Zamboanga.
Post a Comment