Steps To Recover A Dropped Tablespace Using TSPITR

我会带着你远行 2022-08-06 10:17 222阅读 0赞

In this Document  
Purpose  
Scope  
Details  
References  
This document is being delivered to you via Oracle Support's Rapid Visibility (RaV) process and therefore has not been subject to an independent technical review.  
APPLIES TO:  
  
Oracle Database - Enterprise Edition - Version 11.2.0.1 to 11.2.0.2 \[Release 11.2\]  
Information in this document applies to any platform.  
PURPOSE  
  
Recover a dropped tablespace using TSPITR.  
  
SCOPE  
  
RMAN automatic Tablespace Point-In-Time Recovery ( TSPITR) enables you to quickly recover one or more tablespaces in an Oracle database to an earlier time, without affecting the state of the rest of the tablespaces and other objects in the database.   
  
Prior to 11.2 version the TSPITR had a restriction of not being able to recover the dropped tablespace.   
From 11.2 this limitation no longer exists. We can recover the dropped tablespace using TSPITR.  
  
The below example is an attempt to show the steps and functioning of TSPITR to recover a dropped tablespace.  
  
DETAILS  
  
Example:  
  
1) Create a tablespace.  
  
SQL> create tablespace users datafile 'D:\\DATABASES\\ORA11G\\users01.dbf' size 10m reuse;  
  
SQL> select tablespace\_name from dba\_tablespaces;  
  
TABLESPACE\_NAME  
\------------------------------  
SYSTEM  
SYSAUX  
UNDOTBS1  
TEMP  
USERS  
  
2) Create a user and create a table in the new tablespace.  
  
SQL> create user tiger identified by tiger;  
SQL> grant dba to tiger;  
SQL> conn tiger/tiger  
SQL> create table objects tablespace users as select \* from dba\_objects ;  
SQL> select count(\*) from objects;  
  
COUNT(\*)  
\----------  
17299  
  
3) Make a note of the current log sequence number.  
  
SQL > select sequence\# from v$log where status='CURRENT';  
  
SEQUENCE\#  
\----------  
21    
  
4) Take a backup of the database and the archivelogs.  
  
% rman target /  
RMAN> backup database plus archivelog;  
  
5) Now login to the database and just perform a few log switches and then drop the tablespace.  
  
SQL> alter system switch logfile;  
SQL> alter system switch logfile;  
SQL> drop tablespace users including contents and datafiles;  
  
6) Now note down the current log sequence number in the database.  
  
SQL> select SEQUENCE\# from v$log where status='CURRENT';  
  
SEQUENCE\#  
\----------  
25  
  
7) The log sequence number in the database when the tablespace was dropped is 25. So if we recover upto sequence 24 then we will be able to get the tablespace back. To recover upto sequence 24 we have use 24+1=25 in the set until clause of the TSPITR statement.  
  
% rman target /  
RMAN> recover tablespace users until logseq 25 auxiliary destination 'd:\\';  
  
Starting recover at 03-JAN-11  
using target database control file instead of recovery catalog  
allocated channel: ORA\_DISK\_1  
channel ORA\_DISK\_1: SID=15 device type=DISK  
RMAN-05026: WARNING: presuming following set of tablespaces applies to specified point-in-time  
  
List of tablespaces expected to have UNDO segments  
Tablespace SYSTEM  
Tablespace UNDOTBS1  
  
Creating automatic instance, with SID='nyxF'  
initialization parameters used for automatic instance:  
db\_name=ORA11G  
db\_unique\_name=nyxF\_tspitr\_ORA11G  
compatible=11.2.0.0.0  
db\_block\_size=8192  
db\_files=200  
sga\_target=280M  
processes=50  
db\_create\_file\_dest=d:\\  
log\_archive\_dest\_1='location=d:\\'  
\#No auxiliary parameter file used  
  
starting up automatic instance ORA11G  
  
Oracle instance started  
Total System Global Area 292933632 bytes  
Fixed Size 1374164 bytes  
Variable Size 100665388 bytes  
Database Buffers 184549376 bytes  
Redo Buffers 6344704 bytes  
Automatic instance created  
  
List of tablespaces that have been dropped from the target database:  
Tablespace users  
  
contents of Memory Script:  
\{  
\# set requested point in time  
set until logseq 25 thread 1;  
\# restore the controlfile  
restore clone controlfile;  
\# mount the controlfile  
sql clone 'alter database mount clone database';  
\# archive current online log   
sql 'alter system archive log current';  
\# avoid unnecessary autobackups for structural changes during TSPITR  
sql 'begin dbms\_backup\_restore.AutoBackupFlag(FALSE); end;';  
\}  
executing Memory Script  
executing command: SET until clause  
  
Starting restore at 03-JAN-11  
allocated channel: ORA\_AUX\_DISK\_1  
channel ORA\_AUX\_DISK\_1: SID=59 device type=DISK  
  
channel ORA\_AUX\_DISK\_1: starting datafile backup set restore  
channel ORA\_AUX\_DISK\_1: restoring control file  
channel ORA\_AUX\_DISK\_1: reading from backup piece D:\\DATABASES\\ORA11G\\FRA\\ORA11G\\BACKUPSET\\2011\_01\_03\\O1\_MF\_NCSNF\_TAG20110103T092723\_6L2LB86C\_.BKP  
channel ORA\_AUX\_DISK\_1: piece handle=D:\\DATABASES\\ORA11G\\FRA\\ORA11G\\BACKUPSET\\2011\_01\_03\\O1\_MF\_NCSNF\_TAG20110103T092723\_6L2LB86C\_.BKP tag=TAG20110103T092723  
channel ORA\_AUX\_DISK\_1: restored backup piece 1  
channel ORA\_AUX\_DISK\_1: restore complete, elapsed time: 00:00:01  
output file name=D:\\ORA11G\\CONTROLFILE\\O1\_MF\_6L2M39N0\_.CTL  
Finished restore at 03-JAN-11  
  
sql statement: alter database mount clone database  
sql statement: alter system archive log current  
sql statement: begin dbms\_backup\_restore.AutoBackupFlag(FALSE); end;  
  
contents of Memory Script:  
\{  
\# set requested point in time  
set until logseq 25 thread 1;  
\# set destinations for recovery set and auxiliary set datafiles  
set newname for clone datafile 1 to new;  
set newname for clone datafile 3 to new;  
set newname for clone datafile 2 to new;  
set newname for clone tempfile 1 to new;  
set newname for datafile 4 to   
"D:\\DATABASES\\ORA11G\\USERS01.DBF";  
\# switch all tempfiles  
switch clone tempfile all;  
\# restore the tablespaces in the recovery set and the auxiliary set  
restore clone datafile 1, 3, 2, 4;  
switch clone datafile all;  
\}  
executing Memory Script  
executing command: SET until clause  
executing command: SET NEWNAME  
executing command: SET NEWNAME  
executing command: SET NEWNAME  
executing command: SET NEWNAME  
executing command: SET NEWNAME  
renamed tempfile 1 to D:\\ORA11G\\DATAFILE\\O1\_MF\_TEMP\_%U\_.TMP in control file  
  
Starting restore at 03-JAN-11  
using channel ORA\_AUX\_DISK\_1  
  
channel ORA\_AUX\_DISK\_1: starting datafile backup set restore  
channel ORA\_AUX\_DISK\_1: specifying datafile(s) to restore from backup set  
channel ORA\_AUX\_DISK\_1: restoring datafile 00001 to D:\\ORA11G\\DATAFILE\\O1\_MF\_SYSTEM\_%U\_.DBF  
channel ORA\_AUX\_DISK\_1: restoring datafile 00003 to D:\\ORA11G\\DATAFILE\\O1\_MF\_UNDOTBS1\_%U\_.DBF  
channel ORA\_AUX\_DISK\_1: restoring datafile 00002 to D:\\ORA11G\\DATAFILE\\O1\_MF\_SYSAUX\_%U\_.DBF  
channel ORA\_AUX\_DISK\_1: restoring datafile 00004 to D:\\DATABASES\\ORA11G\\USERS01.DBF  
channel ORA\_AUX\_DISK\_1: reading from backup piece D:\\DATABASES\\ORA11G\\FRA\\ORA11G\\BACKUPSET\\2011\_01\_03\\O1\_MF\_NNNDF\_TAG20110103T092723\_6L2L941Y\_.BKP  
channel ORA\_AUX\_DISK\_1: piece handle=D:\\DATABASES\\ORA11G\\FRA\\ORA11G\\BACKUPSET\\2011\_01\_03\\O1\_MF\_NNNDF\_TAG20110103T092723\_6L2L941Y\_.BKP tag=TAG20110103T092723  
channel ORA\_AUX\_DISK\_1: restored backup piece 1  
channel ORA\_AUX\_DISK\_1: restore complete, elapsed time: 00:00:45  
Finished restore at 03-JAN-11  
  
datafile 1 switched to datafile copy  
input datafile copy RECID=12 STAMP=739446137 file name=D:\\ORA11G\\DATAFILE\\O1\_MF\_SYSTEM\_6L2M3MRW\_.DBF  
datafile 3 switched to datafile copy  
input datafile copy RECID=13 STAMP=739446137 file name=D:\\ORA11G\\DATAFILE\\O1\_MF\_UNDOTBS1\_6L2M3N8S\_.DBF  
datafile 2 switched to datafile copy  
input datafile copy RECID=14 STAMP=739446137 file name=D:\\ORA11G\\DATAFILE\\O1\_MF\_SYSAUX\_6L2M3N1G\_.DBF  
  
contents of Memory Script:  
\{  
\# set requested point in time  
set until logseq 25 thread 1;  
\# online the datafiles restored or switched  
sql clone "alter database datafile 1 online";  
sql clone "alter database datafile 3 online";  
sql clone "alter database datafile 2 online";  
sql clone "alter database datafile 4 online";  
\# recover and open resetlogs  
recover clone database tablespace "USERS", "SYSTEM", "UNDOTBS1", "SYSAUX" delete archivelog;  
alter clone database open resetlogs;  
\}  
executing Memory Script  
  
executing command: SET until clause  
sql statement: alter database datafile 1 online  
sql statement: alter database datafile 3 online  
sql statement: alter database datafile 2 online  
sql statement: alter database datafile 4 online  
  
Starting recover at 03-JAN-11  
using channel ORA\_AUX\_DISK\_1  
  
starting media recovery  
  
archived log for thread 1 with sequence 22 is already on disk as file D:\\DATABASES\\ORA11G\\FRA\\ORA11G\\ARCHIVELOG\\2011\_01\_03\\O1\_MF\_1\_22\_6L2LB9M1\_.ARC  
archived log for thread 1 with sequence 23 is already on disk as file D:\\DATABASES\\ORA11G\\FRA\\ORA11G\\ARCHIVELOG\\2011\_01\_03\\O1\_MF\_1\_23\_6L2LPHCQ\_.ARC  
archived log for thread 1 with sequence 24 is already on disk as file D:\\DATABASES\\ORA11G\\FRA\\ORA11G\\ARCHIVELOG\\2011\_01\_03\\O1\_MF\_1\_24\_6L2LPQ2G\_.ARC  
archived log file name=D:\\DATABASES\\ORA11G\\FRA\\ORA11G\\ARCHIVELOG\\2011\_01\_03\\O1\_MF\_1\_22\_6L2LB9M1\_.ARC thread=1 sequence=22  
archived log file name=D:\\DATABASES\\ORA11G\\FRA\\ORA11G\\ARCHIVELOG\\2011\_01\_03\\O1\_MF\_1\_23\_6L2LPHCQ\_.ARC thread=1 sequence=23  
archived log file name=D:\\DATABASES\\ORA11G\\FRA\\ORA11G\\ARCHIVELOG\\2011\_01\_03\\O1\_MF\_1\_24\_6L2LPQ2G\_.ARC thread=1 sequence=24  
media recovery complete, elapsed time: 00:00:04  
Finished recover at 03-JAN-11  
  
database opened  
  
contents of Memory Script:  
\{  
\# make read only the tablespace that will be exported  
sql clone 'alter tablespace USERS read only';  
\# create directory for datapump import  
sql "create or replace directory TSPITR\_DIROBJ\_DPDIR as ''  
d:\\''";  
\# create directory for datapump export  
sql clone "create or replace directory TSPITR\_DIROBJ\_DPDIR as ''  
d:\\''";  
\}  
executing Memory Script  
  
sql statement: alter tablespace USERS read only  
sql statement: create or replace directory TSPITR\_DIROBJ\_DPDIR as ''d:\\''  
sql statement: create or replace directory TSPITR\_DIROBJ\_DPDIR as ''d:\\''  
  
Performing export of metadata...  
EXPDP> Starting "SYS"."TSPITR\_EXP\_nyxF":   
EXPDP> Processing object type TRANSPORTABLE\_EXPORT/PLUGTS\_BLK  
EXPDP> Processing object type TRANSPORTABLE\_EXPORT/TABLE  
EXPDP> Processing object type TRANSPORTABLE\_EXPORT/POST\_INSTANCE/PLUGTS\_BLK  
EXPDP> Master table "SYS"."TSPITR\_EXP\_nyxF" successfully loaded/unloaded  
EXPDP> \*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*  
EXPDP> Dump file set for SYS.TSPITR\_EXP\_nyxF is:  
EXPDP> D:\\TSPITR\_NYXF\_54517.DMP  
EXPDP> \*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*  
EXPDP> Datafiles required for transportable tablespace USERS:  
EXPDP> D:\\DATABASES\\ORA11G\\USERS01.DBF  
EXPDP> Job "SYS"."TSPITR\_EXP\_nyxF" successfully completed at 09:43:07  
Export completed  
  
contents of Memory Script:  
\{  
\# shutdown clone before import  
shutdown clone immediate  
\}  
executing Memory Script  
  
database closed  
database dismounted  
Oracle instance shut down  
  
Performing import of metadata...  
IMPDP> Master table "SYS"."TSPITR\_IMP\_nyxF" successfully loaded/unloaded  
IMPDP> Starting "SYS"."TSPITR\_IMP\_nyxF":   
IMPDP> Processing object type TRANSPORTABLE\_EXPORT/PLUGTS\_BLK  
IMPDP> Processing object type TRANSPORTABLE\_EXPORT/TABLE  
IMPDP> Processing object type TRANSPORTABLE\_EXPORT/POST\_INSTANCE/PLUGTS\_BLK  
IMPDP> Job "SYS"."TSPITR\_IMP\_nyxF" successfully completed at 09:43:29  
Import completed  
  
contents of Memory Script:  
\{  
\# make read write and offline the imported tablespaces  
sql 'alter tablespace USERS read write';  
sql 'alter tablespace USERS offline';  
\# enable autobackups after TSPITR is finished  
sql 'begin dbms\_backup\_restore.AutoBackupFlag(TRUE); end;';  
\}  
executing Memory Script  
  
sql statement: alter tablespace USERS read write  
sql statement: alter tablespace USERS offline  
sql statement: begin dbms\_backup\_restore.AutoBackupFlag(TRUE); end;  
  
Removing automatic instance  
Automatic instance removed  
auxiliary instance file D:\\ORA11G\\DATAFILE\\O1\_MF\_TEMP\_6L2M5G8S\_.TMP deleted  
auxiliary instance file D:\\ORA11G\\ONLINELOG\\O1\_MF\_3\_6L2M5CJM\_.LOG deleted  
auxiliary instance file D:\\ORA11G\\ONLINELOG\\O1\_MF\_2\_6L2M5BNZ\_.LOG deleted  
auxiliary instance file D:\\ORA11G\\ONLINELOG\\O1\_MF\_1\_6L2M59N0\_.LOG deleted  
auxiliary instance file D:\\ORA11G\\DATAFILE\\O1\_MF\_SYSAUX\_6L2M3N1G\_.DBF deleted  
auxiliary instance file D:\\ORA11G\\DATAFILE\\O1\_MF\_UNDOTBS1\_6L2M3N8S\_.DBF deleted  
auxiliary instance file D:\\ORA11G\\DATAFILE\\O1\_MF\_SYSTEM\_6L2M3MRW\_.DBF deleted  
auxiliary instance file D:\\ORA11G\\CONTROLFILE\\O1\_MF\_6L2M39N0\_.CTL deleted  
Finished recover at 03-JAN-11  
  
8) Here rman uses transportable tablespace mechanism to plug the dropped tablespace back in to the database.  
  
SQL> select tablespace\_name,status,plugged\_in from dba\_tablespaces;  
  
TABLESPACE\_NAME     STATUS   PLU  
\----------------    -------  ---  
SYSTEM              ONLINE   NO  
SYSAUX              ONLINE   NO  
UNDOTBS1            ONLINE   NO  
TEMP                ONLINE   NO  
USERS               OFFLINE  YES  ===> Plugged\_in is YES.  
  
9) Online the tablespace.  
  
SQL > alter tablespace users online;  
  
10) Check for the data.  
  
SQL> conn tiger/tiger  
SQL> select count(\*) from objects;  
  
COUNT(\*)  
\----------  
17299