DanBrother的部落格

Using RMAN to Restore A Dropped Tablespace without Resetlogs (11gR2)

##############################################

# Create a Tablespace named “DROP_TEST_TBS”

##############################################

SQL> create tablespace drop_test_tbs datafile size 10M ;

SQL> col tablespace_name format a20

SQL> col file_id format 999

SQL> col file_name format a50

SQL> select tablespace_name,file_id,file_name from dba_data_files where tablespace_name = 'DROP_TEST_TBS';

>> 

TABLESPACE_NAME     FILE_ID FILE_NAME

-------------------- ------- --------------------------------------------------

DROP_TEST_TBS             9 +DATA/testdb/datafile/drop_test_tbs.276.828894561

###############################

# Perform RMAN backup

###############################

$ mkdir /backup/tmp_backup_dir

$ rman target /

RMAN> run

{

backup as compressed backupset database format '/backup/tmp_backup_dir/db_%U'

plus archivelog format '/backup/tmp_backup_dir/arch_%U';

backup current controlfile format '/backup/tmp_backup_dir/control.bk';

backup spfile format '/backup/tmp_backup_dir/spfiletestdb.bak';

}

[oracle@myhost ~]$ ll -h /backup/tmp_backup_dir/

total 265M

-rw-r----- 1 oracle dba 12M Oct 15 16:37 arch_7pomfrpb_1_1

-rw-r----- 1 oracle dba 123K Oct 15 16:40 arch_7romfrv1_1_1

-rw-r----- 1 oracle dba 9.8M Oct 15 16:40 control.bk

-rw-r----- 1 oracle dba 243M Oct 15 16:39 db_7qomfrpq_1_1

-rw-r----- 1 oracle dba 96K Oct 15 16:40 spfiletestdb.bak

###############################

# Drop the Tablespace

###############################

SQL> drop tablespace DROP_TEST_TBS;

Tablespace dropped.

col tablespace_name format a20

col file_id format 999

col file_name format a50

select tablespace_name,file_id,file_name from dba_data_files where tablespace_name = 'DROP_TEST_TBS';

>> 

no rows selected

RMAN> list backup;

List of Backup Sets

===================

BS Key Size       Device Type Elapsed Time Completion Time

------- ---------- ----------- ------------ ---------------

198     11.86M     DISK       00:00:07     15-OCT-13

       BP Key: 199   Status: AVAILABLE Compressed: YES Tag: TAG20131015T163659

       Piece Name: /backup/tmp_backup_dir/arch_7pomfrpb_1_1

List of Archived Logs in backup set 198

Thrd Seq     Low SCN  Low Time Next SCN   Next Time

---- ------- ---------- --------- ---------- ---------

1   125     3826246   15-OCT-13 3840921   15-OCT-13

1   126     3840921   15-OCT-13 3871351   15-OCT-13

1   127     3871351   15-OCT-13 3872150   15-OCT-13

1   128     3872150   15-OCT-13 3872180   15-OCT-13

1   129     3872180   15-OCT-13 3874996   15-OCT-13

1   130     3874996   15-OCT-13 3875246   15-OCT-13

1   131     3875246   15-OCT-13 3875318   15-OCT-13

1   132     3875318   15-OCT-13 3875441   15-OCT-13

1   133     3875441   15-OCT-13 3876825   15-OCT-13

1   134     3876825   15-OCT-13 3876849   15-OCT-13

1   135     3876849   15-OCT-13 3877396   15-OCT-13

1   136     3877396   15-OCT-13 3877836   15-OCT-13

1   137     3877836   15-OCT-13 3877982   15-OCT-13

1   138     3877982   15-OCT-13 3878130   15-OCT-13

1   139     3878130   15-OCT-13 3878569   15-OCT-13

1   140     3878569   15-OCT-13 3878829   15-OCT-13

1   141     3878829   15-OCT-13 3878857   15-OCT-13

1   142     3878857   15-OCT-13 3879260   15-OCT-13

1   143     3879260   15-OCT-13 3884189   15-OCT-13

BS Key Type LV Size       Device Type Elapsed Time Completion Time

------- ---- -- ---------- ----------- ------------ ---------------

199     Full   242.54M   DISK       00:02:38     15-OCT-13

       BP Key: 200   Status: AVAILABLE Compressed: YES Tag: TAG20131015T163714

       Piece Name: /backup/tmp_backup_dir/db_7qomfrpq_1_1

List of Datafiles in backup set 199

File LV Type Ckp SCN   Ckp Time Name

---- -- ---- ---------- --------- ----

1       Full 3884214   15-OCT-13 +DATA/testdb/datafile/system.257.826989973

2       Full 3884214   15-OCT-13 +DATA/testdb/datafile/sysaux.261.826989973

3       Full 3884214   15-OCT-13 +DATA/testdb/datafile/undotbs1.260.826989973

4       Full 3884214   15-OCT-13 +DATA/testdb/datafile/users.259.826989973

5       Full 3884214   15-OCT-13 +DATA/testdb/datafile/test_tbs.258.826989987

6       Full 3884214   15-OCT-13 +DATA/testdb/datafile/indx.272.827856335

7       Full 3884214   15-OCT-13 +DATA/testdb/datafile/test_tbs2.275.828555959

8       Full 3884214   15-OCT-13 +DATA/testdb/datafile/user_data.274.828893327

9       Full 3884214   15-OCT-13

BS Key Size       Device Type Elapsed Time Completion Time

------- ---------- ----------- ------------ ---------------

200     122.00K   DISK       00:00:00     15-OCT-13

       BP Key: 201   Status: AVAILABLE Compressed: YES Tag: TAG20131015T164001

       Piece Name: /backup/tmp_backup_dir/arch_7romfrv1_1_1

List of Archived Logs in backup set 200

Thrd Seq     Low SCN   Low Time Next SCN   Next Time

---- ------- ---------- --------- ---------- ---------

1   144     3884189   15-OCT-13 3884429   15-OCT-13

BS Key Type LV Size       Device Type Elapsed Time Completion Time

------- ---- -- ---------- ----------- ------------ ---------------

201     Full   9.73M     DISK       00:00:01     15-OCT-13

       BP Key: 202   Status: AVAILABLE Compressed: NO Tag: TAG20131015T164002

       Piece Name: /backup/tmp_backup_dir/control.bk

Control File Included: Ckp SCN: 3884440     Ckp time: 15-OCT-13

BS Key Type LV Size       Device Type Elapsed Time Completion Time

------- ---- -- ---------- ----------- ------------ ---------------

202     Full   80.00K     DISK       00:00:00     15-OCT-13

       BP Key: 203   Status: AVAILABLE Compressed: NO Tag: TAG20131015T164004

       Piece Name: /backup/tmp_backup_dir/spfiletestdb.bak

SPFILE Included: Modification time: 15-OCT-13

SPFILE db_unique_name: TESTDB

BS Key Type LV Size       Device Type Elapsed Time Completion Time

------- ---- -- ---------- ----------- ------------ ---------------

203     Full   9.77M     DISK       00:00:01     15-OCT-13

       BP Key: 204   Status: AVAILABLE Compressed: NO Tag: TAG20131015T164005

       Piece Name: /backup/controlfile/control_c-3243840509-20131015-0d

SPFILE Included: Modification time: 15-OCT-13

SPFILE db_unique_name: TESTDB

Control File Included: Ckp SCN: 3884455     Ckp time: 15-OCT-13

###################################

# Restore the Dropped Tablespace

###################################

RMAN> run

{

recover tablespace drop_test_tbs

until scn 3884440

auxiliary destination '/backup/tmp_backup_dir';

}

SQL> col tablespace_name format a20

SQL> select tablespace_name,status from dba_tablespaces

where tablespace_name = 'DROP_TEST_TBS';

TABLESPACE_NAME     STATUS

-------------------- ---------------------------

DROP_TEST_TBS       OFFLINE

SQL> alter tablespace DROP_TEST_TBS online;

SQL> select tablespace_name,file_id,file_name from dba_data_files where tablespace_name = 'DROP_TEST_TBS';

>>  

TABLESPACE_NAME     FILE_ID FILE_NAME

-------------------- ------- --------------------------------------------------

DROP_TEST_TBS             9 +DATA/testdb/datafile/drop_test_tbs.276.828895923

SQL> archive log list

Database log mode             Archive Mode

Automatic archival             Enabled

Archive destination            USE_DB_RECOVERY_FILE_DEST

Oldest online log sequence     145

Next log sequence to archive   147

Current log sequence           147