RMAN : Tablespace Never backed up

Scenario 1:

1. Full database + archivelog backup taken last night .

2 . Created new tablespace SUMIT and inserted data by creating a table in that particular tablespace .

SQL> select count(*) from sumit_table ;

  COUNT(*)
----------
      2048


3. Lost Current control file :

[oracle@node2 trace]$ rm /data01/app/oracle/oradata/ORCL2/controlfile/o1_mf_cfxpjjql_.ctl
[oracle@node2 trace]$ rm /data01/app/oracle/fast_recovery_area/ORCL2/controlfile/o1_mf_cfxpjk7m_.ctl


4. Autobackup job not yet complete . So we have no controlfile autoback after addition of new tablespace .

6. Datafile of new tablespace got deleted by mistake
[oracle@node2 trace]$ rm /data01/app/oracle/oradata/ORCL2/datafile/o1_mf_sumit_cfxwfx8b_.dbf

AIM : Get the datafile back for tablespace sumit ;

Alert log :
==========

Tue Mar 08 11:18:44 2016
Errors in file /data01/app/oracle/diag/rdbms/orcl2/orcl2/trace/orcl2_m000_16057.trc:
ORA-00210: cannot open the specified control file
ORA-00202: control file: '/data01/app/oracle/oradata/ORCL2/controlfile/o1_mf_cfxpjjql_.ctl'
ORA-27041: unable to open file
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3
Tue Mar 08 11:19:44 2016
Errors in file /data01/app/oracle/diag/rdbms/orcl2/orcl2/trace/orcl2_m000_16076.trc:
ORA-00210: cannot open the specified control file
ORA-00202: control file: '/data01/app/oracle/oradata/ORCL2/controlfile/o1_mf_cfxpjjql_.ctl'
ORA-27041: unable to open file
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3
Tue Mar 08 11:19:50 2016

1. Try to shutdown the database  :

You will not be able to shu immediate because control file is missing . So need to shu abort .

SQL> shu immediate ;

ORA-00210: cannot open the specified control file
ORA-00202: control file: '/data01/app/oracle/oradata/ORCL2/controlfile/o1_mf_cfxpjjql_.ctl'
ORA-27041: unable to open file
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3
SQL>

SQL> startup nomount ;
ORACLE instance started.

Total System Global Area 1135747072 bytes
Fixed Size      2287528 bytes
Variable Size    788531288 bytes
Database Buffers   335544320 bytes
Redo Buffers      9383936 bytes
SQL>

2.Restore the controlfile from last night rman backup :

RMAN> restore controlfile from autobackup ;

Starting restore at 20160308.112954
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=1 device type=DISK

recovery area destination: /data01/app/oracle/fast_recovery_area
database name (or database unique name) used for search: ORCL2
channel ORA_DISK_1: AUTOBACKUP /data01/app/oracle/fast_recovery_area/ORCL2/autobackup/2016_03_08/o1_mf_s_905942221_cfxwdxkn_.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 /data01/app/oracle/fast_recovery_area/ORCL2/autobackup/2016_03_08/o1_mf_s_905942221_cfxwdxkn_.bkp
channel ORA_DISK_1: control file restore from AUTOBACKUP complete
output file name=/data01/app/oracle/oradata/ORCL2/controlfile/o1_mf_cfxpjjql_.ctl
output file name=/data01/app/oracle/fast_recovery_area/ORCL2/controlfile/o1_mf_cfxpjk7m_.ctl
Finished restore at 20160308.112956

3 .Mount the database and  report schema to verify if control file is aware of tbs SUMIT  :  In our case restored control file is not aware of tablespace SUMIT .


RMAN> alter database mount;

using target database control file instead of recovery catalog
Statement processed

RMAN> report schema;

Starting implicit crosscheck backup at 20160308.113157
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=20 device type=DISK
Crosschecked 4 objects
Finished implicit crosscheck backup at 20160308.113158

Starting implicit crosscheck copy at 20160308.113158
using channel ORA_DISK_1
Finished implicit crosscheck copy at 20160308.113158

searching for all files in the recovery area
cataloging files...
cataloging done

List of Cataloged Files
=======================
File Name: /data01/app/oracle/fast_recovery_area/ORCL2/autobackup/2016_03_08/o1_mf_s_905942221_cfxwdxkn_.bkp
File Name: /data01/app/oracle/fast_recovery_area/ORCL2/archivelog/2016_03_08/o1_mf_1_6_cfxy1r1d_.arc
File Name: /data01/app/oracle/fast_recovery_area/ORCL2/archivelog/2016_03_08/o1_mf_1_7_cfxyx64v_.arc
File Name: /data01/app/oracle/fast_recovery_area/ORCL2/archivelog/2016_03_08/o1_mf_1_8_cfxyxv1m_.arc
File Name: /data01/app/oracle/fast_recovery_area/ORCL2/archivelog/2016_03_08/o1_mf_1_5_cfxy1pxh_.arc

RMAN-06139: WARNING: control file is not current for REPORT SCHEMA
Report of database schema for database with db_unique_name ORCL2

List of Permanent Datafiles
===========================
File Size(MB) Tablespace           RB segs Datafile Name
---- -------- -------------------- ------- ------------------------
1    770      SYSTEM               ***     /data01/app/oracle/oradata/ORCL2/datafile/o1_mf_system_cfxpdor9_.dbf
2    150      TSH_DATA            ***     /data01/app/oracle/oradata/ORCL2/datafile/o1_mf_tsh_data_cfxvz9do_.dbf
3    700      SYSAUX               ***     /data01/app/oracle/oradata/ORCL2/datafile/o1_mf_sysaux_cfxpb0jb_.dbf
4    55       UNDOTBS1             ***     /data01/app/oracle/oradata/ORCL2/datafile/o1_mf_undotbs1_cfxpj05f_.dbf
6    5        USERS                ***     /data01/app/oracle/oradata/ORCL2/datafile/o1_mf_users_cfxphz0d_.dbf

List of Temporary Files
=======================
File Size(MB) Tablespace           Maxsize(MB) Tempfile Name
---- -------- -------------------- ----------- --------------------
1    60       TEMP                 32767       /data01/app/oracle/oradata/ORCL2/datafile/o1_mf_temp_cfxpk4nq_.tmp

4.Restore  :

The "RMAN-20202: Tablespace not found in the recovery catalog" and "RMAN-06019: could not translate tablespace name "SUMIT"" errors are thrown, because the controlfile used has no information about that tablespace as you can see in the following output:

RMAN> restore tablespace sumit ;

Starting restore at 20160308.113247
using channel ORA_DISK_1
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of restore command at 03/08/2016 11:32:47
RMAN-20202: Tablespace not found in the recovery catalog
RMAN-06019: could not translate tablespace name "SUMIT"

 To recover the missing and "never backed up" tablespace you must restore and recover the entire database.

 RMAN> restore database;

Starting restore at 20160308.113325
using channel ORA_DISK_1

channel ORA_DISK_1: starting datafile backup set restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_DISK_1: restoring datafile 00001 to /data01/app/oracle/oradata/ORCL2/datafile/o1_mf_system_cfxpdor9_.dbf
channel ORA_DISK_1: restoring datafile 00002 to /data01/app/oracle/oradata/ORCL2/datafile/o1_mf_tsh_data_cfxvz9do_.dbf
channel ORA_DISK_1: restoring datafile 00003 to /data01/app/oracle/oradata/ORCL2/datafile/o1_mf_sysaux_cfxpb0jb_.dbf
channel ORA_DISK_1: restoring datafile 00004 to /data01/app/oracle/oradata/ORCL2/datafile/o1_mf_undotbs1_cfxpj05f_.dbf
channel ORA_DISK_1: restoring datafile 00006 to /data01/app/oracle/oradata/ORCL2/datafile/o1_mf_users_cfxphz0d_.dbf
channel ORA_DISK_1: reading from backup piece /data01/app/oracle/fast_recovery_area/ORCL2/backupset/2016_03_08/o1_mf_nnndf_TAG20160308T103603_cfxwc4ds_.bkp
channel ORA_DISK_1: piece handle=/data01/app/oracle/fast_recovery_area/ORCL2/backupset/2016_03_08/o1_mf_nnndf_TAG20160308T103603_cfxwc4ds_.bkp tag=TAG20160308T103603
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:55
Finished restore at 20160308.113420

5 .Recover database :

RMAN> recover database;

Starting recover at 20160308.113501
using channel ORA_DISK_1

starting media recovery

archived log for thread 1 with sequence 4 is already on disk as file /data01/app/oracle/fast_recovery_area/ORCL2/archivelog/2016_03_08/o1_mf_1_4_cfxwdvgt_.arc
archived log for thread 1 with sequence 5 is already on disk as file /data01/app/oracle/fast_recovery_area/ORCL2/archivelog/2016_03_08/o1_mf_1_5_cfxy1pxh_.arc
archived log for thread 1 with sequence 6 is already on disk as file /data01/app/oracle/fast_recovery_area/ORCL2/archivelog/2016_03_08/o1_mf_1_6_cfxy1r1d_.arc
archived log for thread 1 with sequence 7 is already on disk as file /data01/app/oracle/fast_recovery_area/ORCL2/archivelog/2016_03_08/o1_mf_1_7_cfxyx64v_.arc
archived log for thread 1 with sequence 8 is already on disk as file /data01/app/oracle/fast_recovery_area/ORCL2/archivelog/2016_03_08/o1_mf_1_8_cfxyxv1m_.arc
archived log for thread 1 with sequence 9 is already on disk as file /data01/app/oracle/fast_recovery_area/ORCL2/onlinelog/o1_mf_2_cfxs974b_.log
archived log file name=/data01/app/oracle/fast_recovery_area/ORCL2/archivelog/2016_03_08/o1_mf_1_4_cfxwdvgt_.arc thread=1 sequence=4
archived log file name=/data01/app/oracle/fast_recovery_area/ORCL2/archivelog/2016_03_08/o1_mf_1_5_cfxy1pxh_.arc thread=1 sequence=5
creating datafile file number=5 name=/data01/app/oracle/oradata/ORCL2/datafile/o1_mf_sumit_cfxwfx8b_.dbf
archived log file name=/data01/app/oracle/fast_recovery_area/ORCL2/archivelog/2016_03_08/ o1_mf_1_5_cfxy1pxh_.arc thread=1 sequence=5
archived log file name=/data01/app/oracle/fast_recovery_area/ORCL2/archivelog/2016_03_08/o1_mf_1_6_cfxy1r1d_.arc thread=1 sequence=6
archived log file name=/data01/app/oracle/fast_recovery_area/ORCL2/archivelog/2016_03_08/o1_mf_1_7_cfxyx64v_.arc thread=1 sequence=7
archived log file name=/data01/app/oracle/fast_recovery_area/ORCL2/archivelog/2016_03_08/o1_mf_1_8_cfxyxv1m_.arc thread=1 sequence=8
archived log file name=/data01/app/oracle/fast_recovery_area/ORCL2/onlinelog/o1_mf_2_cfxs974b_.log thread=1 sequence=9
media recovery complete, elapsed time: 00:00:02
Finished recover at 20160308.113508

6. Verify Datafile and Data :

[oracle@node2 datafile]$ ls -l
total 1875304
-rw-r----- 1 oracle oper 157294592 Mar  8 11:35 o1_mf_sumit_cfxzsqx7_.dbf
-rw-r----- 1 oracle oper 734011392 Mar  8 11:35 o1_mf_sysaux_cfxpb0jb_.dbf
-rw-r----- 1 oracle oper 807411712 Mar  8 11:35 o1_mf_system_cfxpdor9_.dbf
-rw-r----- 1 oracle oper  62922752 Mar  8 10:35 o1_mf_temp_cfxpk4nq_.tmp
-rw-r----- 1 oracle oper 157294592 Mar  8 11:35 o1_mf_tsh_data_cfxvz9do_.dbf
-rw-r----- 1 oracle oper  57679872 Mar  8 11:35 o1_mf_undotbs1_cfxpj05f_.dbf
-rw-r----- 1 oracle oper   5251072 Mar  8 11:35 o1_mf_users_cfxphz0d_.dbf


SQL>  select count(*) from sumit_table ;

  COUNT(*)
----------
      2048