Wednesday, December 12, 2012

How to recover a never backed up tablespace after losing its datafile and even the current controlfile after the autobackup feature completes its job

This post will take into consideration a Recovery Manager setting using the CONTROLFILE AUTOBACKUP feature, a tablespace created after the only available full backup (so this backup doesn't have information on this tablespace), some rows committed on the new tablespace, a crash happened after a backup of the current controlfile was completed.
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 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

SQL> 
That's all.

59 comments:

goutham said...

Hi
Thanks for sharing good post on Rman.



thanks

Marco V. said...

Thank you for visiting my blog.

Regards,
Marco V.

Anonymous said...

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

Anonymous said...

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

Anonymous said...

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

Anonymous said...

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

Anonymous said...

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

Anonymous said...

Great article.

Also visit my site - local business locator

Anonymous said...

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

Anonymous said...

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

Anonymous said...

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

Anonymous said...

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

Anonymous said...

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

Anonymous said...

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

Anonymous said...

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

Anonymous said...

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

Anonymous said...

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

Anonymous said...

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

Anonymous said...

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

Anonymous said...

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

Anonymous said...

I could not resist commenting. Well written!



my web site: bizsearch local

Anonymous said...

This text is invaluable. How can I find out more?

My web site - business lookup

Anonymous said...

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

Anonymous said...

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

Anonymous said...

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

Anonymous said...

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

Anonymous said...

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

Anonymous said...

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

Anonymous said...

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

Anonymous said...

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

Anonymous said...

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

Anonymous said...

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

Anonymous said...

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

Anonymous said...

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

Anonymous said...

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

Anonymous said...

Thanks very interesting blog!

Review my blog :: Buy Revitol Dermasis

Anonymous said...

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

Anonymous said...

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

Anonymous said...

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

Anonymous said...

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

Anonymous said...

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

Anonymous said...

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

Anonymous said...

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

Anonymous said...

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

Anonymous said...

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

Anonymous said...

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

Anonymous said...

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 ()

Anonymous said...

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

Anonymous said...

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 ()

Anonymous said...

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.

Anonymous said...

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

Anonymous said...

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; ,

oakleyses said...

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

oakleyses said...

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

oakleyses said...

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

oakleyses said...

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

oakleyses said...

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

Unknown said...

Best Minecraft skins you can find here skins4minecraft.com

Unknown said...

“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.