??? TSPITR [example]

Need to recover EXAMPLE tablespace (TSPITR)

Have TTTTT table:

SQL> create table TTTTT (id number) tablespace example;

Table created.

$ date
Thu May 1 01:31:13 ICT 2008

SQL> desc TTTTT
Name Null? Type
----------------------------------------- -------- ----------------------------
ID NUMBER

$ date
Thu May 1 01:31:41 ICT 2008

DROP "PURGE" option:

SQL> drop table TTTTT purge;

Table dropped.

SQL> desc TTTTT
ERROR:
ORA-04043: object TTTTT does not exist

$ date
Thu May 1 01:34:06 ICT 2008

After drop table "PURGE" option:
Need recover "TTTTT" table on EXAMPLE tablespace ["Thu May 1 01:32:00 ICT 2008"]

>>>>>>>>>>>>>>>>>>>
SQL> select FILE_NAME, STATUS, ONLINE_STATUS from dba_data_files

FILE_NAME STATUS ONLINE_
------------------------------------------------------------ --------- -------
+DATA/testdb/datafile/users.1085.651074273 AVAILABLE ONLINE
+DATA/testdb/datafile/undotbs1.1086.651074273 AVAILABLE ONLINE
+DATA/testdb/datafile/sysaux.1087.651074273 AVAILABLE ONLINE
+DATA/testdb/datafile/system.270.651074273 AVAILABLE SYSTEM
+DATA/testdb/datafile/example.1075.651074487 AVAILABLE ONLINE

>>>>>>>>>>>>>>>>>>>

1. create inittspitr_testdb.ora file ($ORACLE_HOME/dbs) for auxiliary instance.

DB_NAME=testdb
DB_UNIQUE_NAME=tspitr_testdb
CONTROL_FILES='/oradata/testdb/tmp/controlfile.dbf'
DB_FILE_NAME_CONVERT=('+DATA/testdb', '/oradata/testdb/tmp')
LOG_FILE_NAME_CONVERT=('+DATA/testdb', '/oradata/testdb/tmp')
COMPATIBLE=11.1.0.0.0
remote_login_passwordfile='EXCLUSIVE'

2. Create service for target database (example: testdb).

3. use SQL*PLUS to start tspitr_testdb instance (auxiliary)

$ export ORACLE_SID=tspitr_testdb
$ sqlplus / as sysoper
SQL> startup nomount;
ORACLE instance started.

Total System Global Area 217157632 bytes
Fixed Size 2142976 bytes
Variable Size 159386880 bytes
Database Buffers 50331648 bytes
Redo Buffers 5296128 bytes
.
.
.

4. use RMAN connect to the target and auxiliary database instances.

$export ORACLE_SID=tspitr_testdb
$rman target sys/password@testdb auxiliary / catalog rman/password@catalog
connected to target database: TESTDB (DBID=2434402578)
connected to recovery catalog database
connected to auxiliary database: TESTDB (not mounted)

5. execute TSPITR

RMAN> run{
SET NEWNAME FOR DATAFILE '+DATA/testdb/datafile/example.1075.651074487'
TO '/oradata/testdb/tmp/example01.dbf';
SET NEWNAME FOR DATAFILE '+DATA/testdb/datafile/system.270.651074273'
TO '/oradata/testdb/tmp/system01.dbf';
SET NEWNAME FOR DATAFILE '+DATA/testdb/datafile/sysaux.1087.651074273'
TO '/oradata/testdb/tmp/sysaux01.dbf';
SET NEWNAME FOR DATAFILE '+DATA/testdb/datafile/undotbs1.1086.651074273'
TO '/oradata/testdb/tmp/undo01.dbf';
ALLOCATE AUXILIARY CHANNEL t1 DEVICE TYPE DISK;
RECOVER TABLESPACE example until time "to_date('May:01:2008 01:32:00', 'Mon:DD:YYYY HH24:MI:SS')";
}

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

allocated channel: t1
channel t1: SID=97 device type=DISK

Starting recover at 01-MAY-08

contents of Memory Script:
{
# set the until clause
set until time "to_date('May:01:2008 01:32:00', 'Mon:DD:YYYY HH24:MI:SS')";
# restore the controlfile
restore clone controlfile;
# mount the controlfile
sql clone 'alter database mount clone database';
# archive current online log for tspitr to a resent until time
sql 'alter system archive log current';
# avoid unnecessary autobackups for structural changes during TSPITR
sql 'begin dbms_backup_restore.AutoBackupFlag(FALSE); end;';
# resync catalog after controlfile restore
resync catalog;
}
executing Memory Script

executing command: SET until clause

Starting restore at 01-MAY-08

channel t1: starting datafile backup set restore
channel t1: restoring control file
.
.
.
Starting recover at 01-MAY-08

starting media recovery
.
.
.
media recovery complete, elapsed time: 00:00:01
Finished recover at 01-MAY-08

released channel: t1
.
.
.

5. After auxiliary database instances "open" (resetlogs), that we check auxiliary database instances.

Connect...
SQL> desc TTTTT
Name Null? Type
----------------------------------------- -------- ----------------
ID NUMBER

We can recover "TTTTT" table:

>>>>>>>>>>>>>>>>>>>
SQL> select FILE_NAME, STATUS, ONLINE_STATUS from dba_data_files

FILE_NAME STATUS ONLINE_
------------------------------------------------------------ --------- -------
/oradata/testdb/tmp/users.1085.651074273 AVAILABLE OFFLINE
/oradata/testdb/tmp/undo01.dbf AVAILABLE ONLINE
/oradata/testdb/tmp/sysaux01.dbf AVAILABLE ONLINE
/oradata/testdb/tmp/system01.dbf AVAILABLE SYSTEM
/oradata/testdb/tmp/example01.dbf AVAILABLE ONLINE

>>>>>>>>>>>>>>>>>>>
This case we recover example tablespace, It'll recover example + system + sysaux + undo tablespaces except USERS tablespace or..... other, so we'll see online_status be offline.