Recovering A Dropped Table Using Tablespace Point In Time Recovery (TSPITR)

Database Name    : SRPRIM
Tablespace Name : MYTS

A table associated with the tablespace MYTS was accidentally dropped on one our Test environment. There was no flashback enabled on this
database and the recyclebin too was purged out. As a result, the table could not be recovered using flashback method. The other methods to
recover the dropped table would be:

1. Duplicate/Clone the database in which the table is dropped until time before the table was dropped to a temporary database, export the
table from the temporary database and import it to the main database.
–This method is the preferrable one. I shall demonstrate using this method in my coming posts.

2. Using Tablespace Point In Time Recovery. Here, the tablespace associate with the table that is dropped would be recovered until the time.
The main disadvantage of this method is that any other successfull transactions that were carried out on this tablespace after the time the
table was dropped, would be lost when you do a point in time recovery.

As this was a test environment and due to lack of time, I had to opt this method but yes, “only upon cofirmation from the productivity
managment site.” 🙂

Schema name: Shivu
Table name : TEST

SQL> select * from test;

CODE
----------
100
200
300
400
500
600
700
800
900
1000

10 rows selected.

Make sure that you have a valid RMAN level 0 backup to proceed.

SQL> select to_char(sysdate,'DD-MON-YYYY HH24:MI:SS') from dual;

TO_CHAR(SYSDATE,'DD-
--------------------
04-MAY-2013 11:38:12

Table TEST has been dropped and also the recyclebin has been purged.

SQL> drop table shivu.test;

Table dropped.

SQL>
SQL> purge recyclebin;

Recyclebin purged.

SQL> select count(*) from shivu.test;
select count(*) from shivu.test
 *
ERROR at line 1:
ORA-00942: table or view does not exist

Now lets, perform the tablespace point in time recovery until the time before the table was dropped.
For TSPITR, we need to specify the auxiliary destination where RMAN would create a temporary database by restoring the controlfile, SYSTEM
tablespace, UNDO tablespace, SYSAUX tablespace and finally the tablespace that needs to be recovered.
Once the temporary database is restored and recovered, RMAN automatically exports the contents of tablespace to be recovered from the
temproary tablespace to a dump file and imports this dump into the Target(Main) database where the table was dropped.

[oracle@ora1-1 dbs]$ rman target /

Recovery Manager: Release 11.2.0.3.0 - Production on Sat May 4 14:07:23 2013

Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.

connected to target database: SRPRIM (DBID=216679430)

RMAN> run
2> {
3> recover tablespace MYTS until time "to_date('2013-05-04:11:38:00','YYYY-MM:DD:HH24:MI:SS')" auxiliary destination '+FRA';
4> }

Starting recover at 04-MAY-13
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=51 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='snya'

initialization parameters used for automatic instance:
db_name=SRPRIM
db_unique_name=snya_tspitr_SRPRIM
compatible=11.2.0.0.0
db_block_size=8192
db_files=200
sga_target=280M
processes=50
db_create_file_dest=+FRA
log_archive_dest_1='location=+FRA'
#No auxiliary parameter file used
starting up automatic instance SRPRIM

Oracle instance started

Total System Global Area 292278272 bytes

Fixed Size 2227744 bytes
Variable Size 100663776 bytes
Database Buffers 184549376 bytes
Redo Buffers 4837376 bytes
Automatic instance created
List of tablespaces that have been dropped from the target database:
Tablespace MYTS

contents of Memory Script:
{
# set requested point in time
set until time "to_date('2013-05-04:11:38:00','YYYY-MM:DD:HH24:MI:SS')";
# 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 04-MAY-13
allocated channel: ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: SID=23 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 /u01/autobackup/c-216679430-20130503-00
channel ORA_AUX_DISK_1: piece handle=/u01/autobackup/c-216679430-20130503-00 tag=TAG20130503T202409
channel ORA_AUX_DISK_1: restored backup piece 1
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:07
output file name=+FRA/srprim/controlfile/current.446.814543667
Finished restore at 04-MAY-13

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 time "to_date('2013-05-04:11:38:00','YYYY-MM:DD:HH24:MI:SS')";
# 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 6 to new;
set newname for datafile 8 to new;
# switch all tempfiles
switch clone tempfile all;
# restore the tablespaces in the recovery set and the auxiliary set
restore clone datafile 1, 3, 2, 6, 8;
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

executing command: SET NEWNAME

renamed tempfile 1 to +FRA in control file

Starting restore at 04-MAY-13
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 00002 to +FRA
channel ORA_AUX_DISK_1: reading from backup piece +FRA/srprim/backupset/2013_05_03/nnndn0_tag20130503t202042_0.425.814479643
channel ORA_AUX_DISK_1: piece handle=+FRA/srprim/backupset/2013_05_03/nnndn0_tag20130503t202042_0.425.814479643 tag=TAG20130503T202042
channel ORA_AUX_DISK_1: restored backup piece 1
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:46
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 +FRA
channel ORA_AUX_DISK_1: restoring datafile 00003 to +FRA
channel ORA_AUX_DISK_1: restoring datafile 00006 to +DATA
channel ORA_AUX_DISK_1: restoring datafile 00008 to +DATA
channel ORA_AUX_DISK_1: reading from backup piece +FRA/srprim/backupset/2013_05_03/nnndn0_tag20130503t202042_0.426.814479645
channel ORA_AUX_DISK_1: piece handle=+FRA/srprim/backupset/2013_05_03/nnndn0_tag20130503t202042_0.426.814479645 tag=TAG20130503T202042
channel ORA_AUX_DISK_1: restored backup piece 1
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:01:35
Finished restore at 04-MAY-13

datafile 6 switched to datafile copy
input datafile copy RECID=13 STAMP=814543825 file name=+DATA/srprim/datafile/myts.275.814543731
datafile 8 switched to datafile copy
input datafile copy RECID=14 STAMP=814543825 file name=+DATA/srprim/datafile/myts.276.814543733
datafile 1 switched to datafile copy
input datafile copy RECID=15 STAMP=814543825 file name=+FRA/srprim/datafile/system.450.814543729
datafile 3 switched to datafile copy
input datafile copy RECID=16 STAMP=814543825 file name=+FRA/srprim/datafile/undotbs1.452.814543733
datafile 2 switched to datafile copy
input datafile copy RECID=17 STAMP=814543825 file name=+FRA/srprim/datafile/sysaux.448.814543685

contents of Memory Script:
{
# set requested point in time
set until time "to_date('2013-05-04:11:38:00','YYYY-MM:DD:HH24:MI:SS')";
# 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 6 online";
sql clone "alter database datafile 8 online";
# recover and open resetlogs
recover clone database tablespace "MYTS", "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 6 online

sql statement: alter database datafile 8 online

Starting recover at 04-MAY-13
using channel ORA_AUX_DISK_1

starting media recovery

archived log for thread 1 with sequence 283 is already on disk as file +FRA/srprim/archivelog/2013_05_03/thread_1_seq_283.427.814479821
archived log for thread 1 with sequence 284 is already on disk as file +FRA/srprim/archivelog/2013_05_03/thread_1_seq_284.429.814479833
archived log for thread 1 with sequence 285 is already on disk as file +FRA/srprim/archivelog/2013_05_04/thread_1_seq_285.433.814534647
archived log for thread 1 with sequence 286 is already on disk as file +FRA/srprim/archivelog/2013_05_04/thread_1_seq_286.434.814534651
archived log for thread 1 with sequence 287 is already on disk as file +FRA/srprim/archivelog/2013_05_04/thread_1_seq_287.435.814534887
archived log file name=+FRA/srprim/archivelog/2013_05_03/thread_1_seq_283.427.814479821 thread=1 sequence=283
archived log file name=+FRA/srprim/archivelog/2013_05_03/thread_1_seq_284.429.814479833 thread=1 sequence=284
archived log file name=+FRA/srprim/archivelog/2013_05_04/thread_1_seq_285.433.814534647 thread=1 sequence=285
archived log file name=+FRA/srprim/archivelog/2013_05_04/thread_1_seq_286.434.814534651 thread=1 sequence=286
archived log file name=+FRA/srprim/archivelog/2013_05_04/thread_1_seq_287.435.814534887 thread=1 sequence=287
media recovery complete, elapsed time: 00:00:08
Finished recover at 04-MAY-13

database opened

contents of Memory Script:
{
# make read only the tablespace that will be exported
sql clone 'alter tablespace MYTS read only';
# create directory for datapump import
sql "create or replace directory TSPITR_DIROBJ_DPDIR as ''
+FRA''";
# create directory for datapump export
sql clone "create or replace directory TSPITR_DIROBJ_DPDIR as ''
+FRA''";
}
executing Memory Script

sql statement: alter tablespace MYTS read only

sql statement: create or replace directory TSPITR_DIROBJ_DPDIR as ''+FRA''

sql statement: create or replace directory TSPITR_DIROBJ_DPDIR as ''+FRA''

Performing export of metadata...
 EXPDP> Starting "SYS"."TSPITR_EXP_snya":
 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_snya" successfully loaded/unloaded
 EXPDP> ******************************************************************************
 EXPDP> Dump file set for SYS.TSPITR_EXP_snya is:
 EXPDP> +FRA/tspitr_snya_51808.dmp
 EXPDP> ******************************************************************************
 EXPDP> Datafiles required for transportable tablespace MYTS:
 EXPDP> +DATA/srprim/datafile/myts.275.814543731
 EXPDP> +DATA/srprim/datafile/myts.276.814543733
 EXPDP> Job "SYS"."TSPITR_EXP_snya" successfully completed at 14:13:10
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_snya" successfully loaded/unloaded
 IMPDP> Starting "SYS"."TSPITR_IMP_snya":
 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_snya" successfully completed at 14:13:46
Import completed
contents of Memory Script:
{
# make read write and offline the imported tablespaces
sql 'alter tablespace MYTS read write';
sql 'alter tablespace MYTS offline';
# enable autobackups after TSPITR is finished
sql 'begin dbms_backup_restore.AutoBackupFlag(TRUE); end;';
}
executing Memory Script

sql statement: alter tablespace MYTS read write

sql statement: alter tablespace MYTS offline

sql statement: begin dbms_backup_restore.AutoBackupFlag(TRUE); end;

Removing automatic instance
Automatic instance removed
auxiliary instance file +FRA/srprim/tempfile/temp.456.814543869 deleted
auxiliary instance file +FRA/srprim/onlinelog/group_3.455.814543857 deleted
auxiliary instance file +FRA/srprim/onlinelog/group_2.454.814543849 deleted
auxiliary instance file +FRA/srprim/onlinelog/group_1.453.814543837 deleted
auxiliary instance file +FRA/srprim/datafile/sysaux.448.814543685 deleted
auxiliary instance file +FRA/srprim/datafile/undotbs1.452.814543733 deleted
auxiliary instance file +FRA/srprim/datafile/system.450.814543729 deleted
auxiliary instance file +FRA/srprim/controlfile/current.446.814543667 deleted
Finished recover at 04-MAY-13

RMAN> exit

Once the import is done successfully, RMAN automatically deletes the temporary database that it had created earlier.
Now, lets connect to the main database and check if we are able to access the dropped table. But, before that, you need to bring the
tablespace online.
This can be noticed here.

[oracle@ora1-1 dbs]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.3.0 Production on Sat May 4 14:16:25 2013

Copyright (c) 1982, 2011, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, Automatic Storage Management, OLAP, DataMining and Real Application Testing options

SQL> set linesize 300
SQL> col name for a60
SQL> select name,status from v$datafile;

NAME                                           STATUS
---------------------------------------------  ------------
+DATA/srprim/datafile/system.257.800915677     SYSTEM
+DATA/srprim/datafile/sysaux.258.800915679     ONLINE
+DATA/srprim/datafile/undotbs1.265.800915683   ONLINE
+DATA/srprim/datafile/users.256.800915683      ONLINE
+DATA/srprim/datafile/example.259.800915681    ONLINE
+DATA/srprim/datafile/myts.276.814543733       OFFLINE
+DATA/srprim/datafile/tbs.268.804023681        ONLINE
+DATA/srprim/datafile/myts.275.814543731       OFFLINE

8 rows selected.

SQL> select tablespace_name,status from dba_tablespaces;

TABLESPACE_NAME             STATUS
--------------------------- ---------
SYSTEM                      ONLINE
SYSAUX                      ONLINE
UNDOTBS1                    ONLINE
TEMP                        ONLINE
USERS                       ONLINE
EXAMPLE                     ONLINE
MYTS                        OFFLINE
TBS                         ONLINE

8 rows selected.

You can notice that the tablespace MYTS is offline. Before proceeding, this tablespace needs to be made online.

SQL> alter tablespace MYTS online;

Tablespace altered.

SQL> select tablespace_name,status from dba_tablespaces;

TABLESPACE_NAME     STATUS
------------------- ---------
SYSTEM              ONLINE
SYSAUX              ONLINE
UNDOTBS1            ONLINE
TEMP                ONLINE
USERS               ONLINE
EXAMPLE             ONLINE
MYTS                ONLINE
TBS                 ONLINE

8 rows selected.

Now lets check the contents of the table “TEST” of schema “SHIVU”.

SQL> select count(*) from shivu.test;

COUNT(*)
----------
 10

SQL> select * from shivu.test;

CODE
----------
 100
 200
 300
 400
 500
 600
 700
 800
 900
 1000

10 rows selected.

We are able to get back the contents of the TEST table.

Here we go !!

COPYRIGHT

© Shivananda Rao P, 2012 to 2018. Unauthorized use and/or duplication of this material without express and written permission from this blog’s author and/or owner is strictly prohibited. Excerpts and links may be used, provided that full and clear credit is given to Shivananda Rao and http://www.shivanandarao-oracle.com with appropriate and specific direction to the original content.

DISCLAIMER

The views expressed here are my own and do not necessarily reflect the views of any other individual, business entity, or organization. The views expressed by visitors on this blog are theirs solely and may not reflect mine.