Recovering of a dropped tablespace
Tablespace Point In Time Recovery (TSPITR)
method cannot be used to recover from a case of a dropped tablespace.
When u drop a
tablespace including the contents and datafiles. The controlfile will no longer have any records of the dropped tablespace. If all u attempt
to recover the dropped tablespace using the RMAN RECOVER u will hit a error
massage like
RMAN-06019:
could not translate tablespace name "COLA"
There are some work around step to get rid
of the above massage.
23:30:36 SQL> drop tablespace cola
including contents and datafiles;
C:\>rman target backup/backup
RMAN> recover tablespace cola;
Starting recover at 08-FEB-12
using channel ORA_DISK_1
starting media recovery
media recovery complete, elapsed time:
00:00:06
Finished recover at 08-FEB-12
RMAN> restore datafile 6;
Starting restore at 08-FEB-12
using channel ORA_DISK_1
RMAN-00571: ===============================================
RMAN-00569: ======= ERROR MESSAGE STACK
FOLLOWS =========
RMAN-00571: ===============================================
RMAN-03002: failure of restore command at
02/08/2012 23:31:00
RMAN-20201: datafile not found in the
recovery catalog
RMAN-06010: could not translate tablespace
name "COLA"
So to recover
from a dropped tablespace,
1)
Do a
point in time recovery of the whole database until the time the tablespace was
dropped.
Let us examine
the first option using the example shown below:
In this example,
CONTROLFILE AUTOBACKUP has been turned on and Flashback has been enabled for
the database.
With Flashback
enabled, the db_recovery_file_dest will have a sub-directory ‘autobackup’ as
shown below for each day
Directory of
E:\Food_database\backup\FOOD\AUTOBACKUP\2012_02_08
2/08/2012 11:30 PM
<DIR> .
2/08/2012 11:30 PM
<DIR> ..
2/08/2012 03:40 PM 7,995,392
O1_MF_S_774718807_7M52B8SD_.BKP
2/08/2012 03:55 PM 7,995,392
O1_MF_S_774719720_7M536S4D_.BKP
2/08/2012 11:09 PM 7,995,392
O1_MF_S_774745763_7M5WNNPG_.BKP
2/08/2012 11:23 PM
7,995,392
O1_MF_S_774746627_7M5XHN7T_.BKP
2/08/2012 11:24 PM 7,995,392
O1_MF_S_774746653_7M5XJG00_.BKP
2/08/2012 11:28 PM 7,995,392
O1_MF_S_774746936_7M5XS9QG_.BKP
2/08/2012 11:29 PM 7,995,392
O1_MF_S_774746943_7M5XSJN0_.BKP
2/08/2012 11:30 PM 7,995,392
O1_MF_S_774747042_7M5XWMCQ_.BKP
8 File(s) 63,963,136 bytes
2 Dir(s) 24,172,269,568 bytes free
When we drop the
tablespace we are changing the structure of the database and since controlfile
autobackup has been turned on, we see another backup file has been created in
the autobackup location in the flash recovery area on disk.
23:30:36 SQL> drop tablespace cola
including contents and datafiles;
Tablespace
dropped.
We then shutdown
the database, startup in nomount mode and attempt to restore the controlfile
from autobackup.
The most recent
controlfile autobackup has been restored, but since this has been taken after
the tablespace was dropped, the tablespace which has been dropped (COLA) is not
referenced in the control file that we just restored. If we try to restore and
recover the database, the dropped tablespace will not be restored.
23:47:29 SQL> startup nomount
ORACLE instance started.
Total System Global Area 452984832 bytes
Fixed Size 1249536 bytes
Variable Size 117444352 bytes
Database Buffers 331350016 bytes
Redo Buffers 2940928 bytes
C:\>rman target backup/backup
Recovery Manager: Release 10.2.0.1.0 -
Production on Wed Feb 8 23:48:12 2012
Copyright (c) 1982, 2005, Oracle. All rights reserved.
connected to target database: food (not
mounted)
RMAN> restore controlfile from
autobackup;
Starting restore at 08-FEB-12
using target database control file instead
of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=147 devtype=DISK
recovery area destination:
E:\Food_database\backup
database name (or database unique name)
used for search: FOOD
channel ORA_DISK_1: autobackup found in the
recovery area
channel ORA_DISK_1: autobackup found:
E:\FOOD_DATABASE\BACKUP\FOOD\AUTOBACKUP\2012_02_08\O1_MF_S_774747042_7M5XWMCQ_.BKP
channel ORA_DISK_1: control file restore
from autobackup complete
output
filename=E:\ORACLE\PRODUCT\10.2.0\ORADATA\FOOD\CONTROL01.CTL
output
filename=E:\ORACLE\PRODUCT\10.2.0\ORADATA\FOOD\CONTROL02.CTL
output
filename=E:\ORACLE\PRODUCT\10.2.0\ORADATA\FOOD\CONTROL03.CTL
Finished restore at 08-FEB-12
RMAN> alter database mount;
RMAN> report schema;
Starting implicit crosscheck backup at
08-FEB-12
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=146 devtype=DISK
Crosschecked 8 objects
Finished implicit crosscheck backup at
08-FEB-12
Starting implicit crosscheck copy at
08-FEB-12
using channel ORA_DISK_1
Finished implicit crosscheck copy at
08-FEB-12
searching for all files in the recovery
area
cataloging files...
cataloging done
List of Cataloged Files
=======================
File Name: E:\FOOD_DATABASE\BACKUP\FOOD\AUTOBACKUP\2012_02_08\O1_MF_S_774747042_7M5XWMCQ_.BKP
RMAN-06139: WARNING: control file is not
current for REPORT SCHEMA
Report of database schema
List of Permanent Datafiles
===========================
File Size(MB) Tablespace RB segs Datafile Name
---- -------- -------------------- -------
------------------------
1
490 SYSTEM ***
E:\ORACLE\PRODUCT\10.2.0\ORADATA\FOOD\SYSTEM01.DBF
2
2048 UNDOTBS1 ***
E:\ORACLE\PRODUCT\10.2.0\ORADATA\FOOD\UNDOTBS01.DBF
3
270 SYSAUX ***
E:\ORACLE\PRODUCT\10.2.0\ORADATA\FOOD\SYSAUX01.DBF
4
3072 USERS ***
E:\ORACLE\PRODUCT\10.2.0\ORADATA\FOOD\USERS01.DBF
5
100 EXAMPLE ***
E:\ORACLE\PRODUCT\10.2.0\ORADATA\FOOD\EXAMPLE01.DBF
7
2048 BEVERAGES ***
E:\ORACLE\PRODUCT\10.2.0\ORADATA\FOOD\BEVERAGES01.DBF
8
2048 BEVERAGES *** E:\ORACLE\PRODUCT\10.2.0\ORADATA\FOOD\BEVERAGES02.DBF
9
165 UNDOTBS1 ***
E:\ORACLE\PRODUCT\10.2.0\ORADATA\FOOD\UNDOTBS02.DBF
List of Temporary Files
=======================
File Size(MB) Tablespace Maxsize(MB) Tempfile Name
---- -------- --------------------
----------- --------------------
1
65 TEMP 32767
E:\ORACLE\PRODUCT\10.2.0\ORADATA\FOOD\TEMP01.DBF
We will need to
restore a backup of the controlfile which contains records for the tablespace COLA.
We use the RESTORE CONTROLFILE FROM command to restore a specific controlfile
autobackup.
RMAN> restore
controlfile from
'E:\Food_database\backup\FOOD\AUTOBACKUP\2012_02_08\O1_MF_S_774719720_7M536S4D_.BKP';
Starting restore
at 08-FEB-12
using target
database control file instead of recovery catalog
allocated
channel: ORA_DISK_1
channel
ORA_DISK_1: sid=147 devtype=DISK
channel
ORA_DISK_1: restoring control file
channel
ORA_DISK_1: restore complete, elapsed time: 00:00:05
output
filename=E:\ORACLE\PRODUCT\10.2.0\ORADATA\FOOD\CONTROL01.CTL
output
filename=E:\ORACLE\PRODUCT\10.2.0\ORADATA\FOOD\CONTROL02.CTL
output
filename=E:\ORACLE\PRODUCT\10.2.0\ORADATA\FOOD\CONTROL03.CTL
Finished restore
at 08-FEB-12
RMAN> alter
database mount;
database mounted
released channel:
ORA_DISK_1
RMAN> report
schema;
Starting implicit
crosscheck backup at 08-FEB-12
allocated
channel: ORA_DISK_1
channel
ORA_DISK_1: sid=146 devtype=DISK
Crosschecked 2
objects
Finished implicit
crosscheck backup at 08-FEB-12
Starting implicit
crosscheck copy at 08-FEB-12
using channel
ORA_DISK_1
Finished implicit
crosscheck copy at 08-FEB-12
searching for all
files in the recovery area
cataloging
files...
cataloging done
List of Cataloged
Files
=======================
File Name:
E:\FOOD_DATABASE\BACKUP\FOOD\AUTOBACKUP\2012_02_08\O1_MF_S_774719720_7M536S4D_.BKP
File Name:
E:\FOOD_DATABASE\BACKUP\FOOD\AUTOBACKUP\2012_02_08\O1_MF_S_774745763_7M5WNNPG_.BKP
File Name:
E:\FOOD_DATABASE\BACKUP\FOOD\AUTOBACKUP\2012_02_08\O1_MF_S_774746627_7M5XHN7T_.BKP
File Name:
E:\FOOD_DATABASE\BACKUP\FOOD\AUTOBACKUP\2012_02_08\O1_MF_S_774746653_7M5XJG00_.BKP
File Name:
E:\FOOD_DATABASE\BACKUP\FOOD\AUTOBACKUP\2012_02_08\O1_MF_S_774746936_7M5XS9QG_.BKP
File Name:
E:\FOOD_DATABASE\BACKUP\FOOD\AUTOBACKUP\2012_02_08\O1_MF_S_774746943_7M5XSJN0_.BKP
File Name:
E:\FOOD_DATABASE\BACKUP\FOOD\AUTOBACKUP\2012_02_08\O1_MF_S_774747042_7M5XWMCQ_.BKP
RMAN-06139:
WARNING: control file is not current for REPORT SCHEMA
Report of
database schema
List of Permanent
Datafiles
===========================
File Size(MB)
Tablespace RB segs Datafile
Name
---- --------
-------------------- ------- ------------------------
1 490
SYSTEM ***
E:\ORACLE\PRODUCT\10.2.0\ORADATA\FOOD\SYSTEM01.DBF
2 2048
UNDOTBS1 ***
E:\ORACLE\PRODUCT\10.2.0\ORADATA\FOOD\UNDOTBS01.DBF
3 270
SYSAUX ***
E:\ORACLE\PRODUCT\10.2.0\ORADATA\FOOD\SYSAUX01.DBF
4 3072
USERS ***
E:\ORACLE\PRODUCT\10.2.0\ORADATA\FOOD\USERS01.DBF
5 100
EXAMPLE ***
E:\ORACLE\PRODUCT\10.2.0\ORADATA\FOOD\EXAMPLE01.DBF
6 0 COLA *** E:\ORACLE\PRODUCT\10.2.0\ORADATA\FOOD\COLA1.DBF
7 2048
BEVERAGES ***
E:\ORACLE\PRODUCT\10.2.0\ORADATA\FOOD\BEVERAGES01.DBF
8 2048
BEVERAGES ***
E:\ORACLE\PRODUCT\10.2.0\ORADATA\FOOD\BEVERAGES02.DBF
9 165
UNDOTBS1 ***
E:\ORACLE\PRODUCT\10.2.0\ORADATA\FOOD\UNDOTBS02.DBF
List of Temporary
Files
=======================
File Size(MB)
Tablespace Maxsize(MB) Tempfile
Name
---- --------
-------------------- ----------- --------------------
1 65
TEMP 32767
E:\ORACLE\PRODUCT\10.2.0\ORADATA\FOOD\TEMP01.DBF
The alert log
will also show the time when the tablespace was dropped. We can also see that a
controlfile autobackup has taken place after the tablespace was dropped.
Wed Feb 08 23:30:39 2012
drop tablespace cola including contents and
datafiles
Now that we know
the time the tablespace was dropped, we can do a point in time recovery of the
DATABASE in order to recover the tablespace which has been dropped.
RMAN> run
2> {
3> set until time
"to_date('08-feb-2012 23:30:39','DD-MON-YYYY HH24:Mi:SS')";
4> restore database;
5> recover database;
6> }
executing command: SET until clause
Starting restore at 09-FEB-12
using channel ORA_DISK_1
channel ORA_DISK_1: starting datafile backupset
restore
channel ORA_DISK_1: specifying datafile(s)
to restore from backup set
restoring datafile 00001 to
E:\ORACLE\PRODUCT\10.2.0\ORADATA\FOOD\SYSTEM01.DBF
restoring datafile 00002 to
E:\ORACLE\PRODUCT\10.2.0\ORADATA\FOOD\UNDOTBS01.DBF
restoring datafile 00003 to
E:\ORACLE\PRODUCT\10.2.0\ORADATA\FOOD\SYSAUX01.DBF
restoring datafile 00004 to
E:\ORACLE\PRODUCT\10.2.0\ORADATA\FOOD\USERS01.DBF
restoring datafile 00005 to
E:\ORACLE\PRODUCT\10.2.0\ORADATA\FOOD\EXAMPLE01.DBF
restoring datafile 00006 to E:\ORACLE\PRODUCT\10.2.0\ORADATA\FOOD\COLA1.DBF
restoring datafile 00007 to
E:\ORACLE\PRODUCT\10.2.0\ORADATA\FOOD\BEVERAGES01.DBF
restoring datafile 00008 to
E:\ORACLE\PRODUCT\10.2.0\ORADATA\FOOD\BEVERAGES02.DBF
restoring datafile 00009 to
E:\ORACLE\PRODUCT\10.2.0\ORADATA\FOOD\UNDOTBS02.DBF
channel ORA_DISK_1: reading from backup
piece
E:\FOOD_DATABASE\BACKUP\FOOD\BACKUPSET\2012_02_08\O1_MF_NNNDF_TAG20120208T152908_7M51OPDP_.BKP
channel ORA_DISK_1: restored backup piece 1
piece handle=E:\FOOD_DATABASE\BACKUP\FOOD\BACKUPSET\2012_02_08\O1_MF_NNNDF_TAG20120208T152908_7M51OPDP_.BKP
tag=TAG20120208T152908
channel ORA_DISK_1: restore complete,
elapsed time: 00:09:15
Finished restore at 09-FEB-12
Starting recover at 09-FEB-12
using channel ORA_DISK_1
starting media recovery
archive log thread 1 sequence 652 is
already on disk as file E:\ORACLE\PRODUCT\10.2.0\ORADATA\FOOD\REDO03.LOG
archive log thread 1 sequence 653 is
already on disk as file E:\ORACLE\PRODUCT\10.2.0\ORADATA\FOOD\REDO01.LOG
archive log
filename=E:\ORACLE\PRODUCT\10.2.0\ORADATA\FOOD\REDO03.LOG thread=1 sequence=652
archive log
filename=E:\ORACLE\PRODUCT\10.2.0\ORADATA\FOOD\REDO01.LOG thread=1 sequence=653
media recovery complete, elapsed time:
00:00:24
Finished recover at 09-FEB-12
RMAN> alter database open resetlogs;
database opened
We can now see
that the tablespace which has been dropped has been recovered
00:14:12 SQL> set line 150
set pagesize 150
ttitle dba_tablespace
select
tablespace_name,block_size,status,contents,logging,extent_management,segment_space_management,retention
from dba_tablespaces;
set pagesize 150
col file_name for a30;
ttitle dba_data_files
select
file_id,FILE_NAME,TABLESPACE_NAME,BYTES/1024/1024
MB,BLOCKS,STATUS,AUTOEXTENSIBLE,INCREMENT_BY,ONLINE_STATUS from dba_data_files
order by file_id;
Thu Feb 09
page 1
dba_tablespace
TABLESPACE_NAME BLOCK_SIZE STATUS CONTENTS
LOGGING EXTENT_MAN SEGMEN
RETENTION
------------------------------ ----------
--------- --------- --------- ---------- ------ -----------
SYSTEM 8192 ONLINE PERMANENT LOGGING LOCAL
MANUAL NOT APPLY
UNDOTBS1 8192 ONLINE UNDO
LOGGING LOCAL MANUAL NOGUARANTEE
SYSAUX 8192 ONLINE PERMANENT LOGGING LOCAL
AUTO NOT APPLY
TEMP 8192
ONLINE TEMPORARY NOLOGGING LOCAL MANUAL NOT APPLY
USERS 8192
ONLINE PERMANENT LOGGING LOCAL
AUTO NOT APPLY
EXAMPLE 8192 ONLINE PERMANENT NOLOGGING LOCAL AUTO
NOT APPLY
COLA 8192
ONLINE PERMANENT LOGGING LOCAL
AUTO NOT APPLY
BEVERAGES 8192 ONLINE PERMANENT LOGGING LOCAL
AUTO NOT APPLY
8 rows selected.
Elapsed: 00:00:00.06
Thu Feb 09
page 1
dba_data_files
FILE_ID FILE_NAME
TABLESPACE_NAME
MB BLOCKS STATUS AUT INCREMENT_BY ONLINE_
---------- ------------------------------
------------------------------ ---------- ---------- --------- --- ------------
-------
1 E:\ORACLE\PRODUCT\10.2.0\ORADA SYSTEM 490 62720 AVAILABLE YES 1280 SYSTEM
TA\FOOD\SYSTEM01.DBF
2 E:\ORACLE\PRODUCT\10.2.0\ORADA UNDOTBS1 2048 262144 AVAILABLE YES 640 ONLINE
TA\FOOD\UNDOTBS01.DBF
3 E:\ORACLE\PRODUCT\10.2.0\ORADA SYSAUX 270 34560 AVAILABLE YES 1280 ONLINE
TA\FOOD\SYSAUX01.DBF
4 E:\ORACLE\PRODUCT\10.2.0\ORADA USERS 3072 393216 AVAILABLE YES 160 ONLINE
TA\FOOD\USERS01.DBF
5 E:\ORACLE\PRODUCT\10.2.0\ORADA EXAMPLE 100 12800 AVAILABLE YES 80 ONLINE
TA\FOOD\EXAMPLE01.DBF
6 E:\ORACLE\PRODUCT\10.2.0\ORADA
COLA
10 1280 AVAILABLE NO 0 ONLINE
TA\FOOD\COLA1.DBF
7 E:\ORACLE\PRODUCT\10.2.0\ORADA BEVERAGES 2048 262144 AVAILABLE NO 0 ONLINE
TA\FOOD\BEVERAGES01.DBF
8 E:\ORACLE\PRODUCT\10.2.0\ORADA BEVERAGES 2048 262144 AVAILABLE NO 0 ONLINE
TA\FOOD\BEVERAGES02.DBF
9 E:\ORACLE\PRODUCT\10.2.0\ORADA UNDOTBS1 165 21120 AVAILABLE YES 640 ONLINE
TA\FOOD\UNDOTBS02.DBF
9 rows selected.
Thus u recover the dropped tablespace.
