TSPITR - RMAN

Oracle 11gR2 : Tablespace point in time recovery

This is new feature for Oracle 11g R2.

I'm going to demonstrate how to recover dropped Tablespace using RMAN Tablespace point in time recovery.

Step 1: Create Tablespace

SQL> conn / as sysdba

Connected.

SQL> create tablespace testtbs datafile 'd:\backup\testtbs01.dbf' size 100m;

Tablespace created.

SQL> create user testtbs identified by testtbs default tablespace testtbs;

User created.

SQL> grant connect,resource to testtbs;

Grant succeeded.

SQL> conn testtbs/testtbs;

Connected.

SQL> create table test(empname varchar2(20),city varchar2(20));

Table created.

SQL> insert into test values('sagar','rivaz');

1 row created.

SQL> insert into test values('babbar','noida');

1 row created.

SQL> commit;

Commit complete.

SQL> conn testdb/testdb;

Connected.

(Note : This table allocated for other tablespace , i just insert data for this table for check data consistent after doing TBPITR)

SQL> create table d(empname varchar2(20));

Table created.

SQL> insert into d values('kareena');

1 row created.

SQL> insert into d values('sam');

1 row created.

SQL> commit;

Commit complete.

Step 2: Backup database Plus archivelog

C:\Users\mSAGAR>set oracle_sid=sagardb

C:\Users\mSAGAR>rman target sys/Admin123

Recovery Manager: Release 11.2.0.1.0 - Production on Sat Jan 1 15:39:34 2011

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

connected to target database: SAGARDB (DBID=1652383192)

RMAN> backup database plus archivelog;

Starting backup at 01-JAN-11

current log archived

using target database control file instead of recovery catalog

allocated channel: ORA_DISK_1

channel ORA_DISK_1: SID=30 device type=DISK

Step 3: Note Current SCN

SQL> conn / as sysdba

Connected.

SQL> select current_scn from v$database;

CURRENT_SCN

-----------

8448197

Step 4: Drop tablespace

SQL> drop tablespace testtbs including contents and datafiles;

Tablespace dropped.

Step 5: I just added data for another table allocated for other tablespace due to just check for data consistent.

SQL> conn testdb/testdb;

Connected.

SQL> insert into d values('azmi');

1 row created.

SQL> commit;

Commit complete.

Step 6: Create auxiliary & Recover tablespace using tablespace point in time recovery.

C:\Windows\system32>rman target sys/Admin123

Recovery Manager: Release 11.2.0.1.0 - Production on Sat Jan 1 14:45:08 2011

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

connected to target database: SAGARDB (DBID=1652383192)

RMAN> run{

2> recover tablespace testtbs

3> until scn 8448197

4> auxiliary destination 'D:\backupnew';

5> }

executing command: SET NEWNAME

Starting recover at 01-JAN-11

using target database control file instead of recovery catalog

allocated channel: ORA_DISK_1

channel ORA_DISK_1: SID=31 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 UNDOTBS2

Creating automatic instance, with SID='Bopd'

initialization parameters used for automatic instance:

db_name=SAGARDB

db_unique_name=Bopd_tspitr_SAGARDB

compatible=11.2.0.0.0

db_block_size=8192

db_files=200

sga_target=280M

processes=50

db_create_file_dest=D:\backupnew

log_archive_dest_1='location=D:\backupnew'

#No auxiliary parameter file used

starting up automatic instance SAGARDB

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 testtbs

contents of Memory Script:

{

# set requested point in time

set until scn 8448197;

# 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 01-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 C:\APP\ORACLE\MSAGAR\FLASH_RECOVERY_AREA\SAGARDB\AUTOBACKUP\2011_01_01\O1_MF_N_739286122_6KXZOWTT_.BKP

channel ORA_AUX_DISK_1: piece handle=C:\APP\ORACLE\MSAGAR\FLASH_RECOVERY_AREA\SAGARDB\AUTOBACKUP\2011_01_01\O1_MF_N_739286122_6KXZOWTT_.BKP tag=TAG20110101T13152

1

channel ORA_AUX_DISK_1: restored backup piece 1

channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:01:00

output file name=D:\BACKUPNEW\SAGARDB\CONTROLFILE\O1_MF_6KY50OH2_.CTL

Finished restore at 01-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 scn 8448197;

# set destinations for recovery set and auxiliary set datafiles

set newname for clone datafile 1 to new;

set newname for clone datafile 8 to new;

set newname for clone datafile 2 to new;

set newname for clone tempfile 1 to new;

set newname for datafile 3 to

"D:\BACKUP\TESTTBS01.DBF";

# switch all tempfiles

switch clone tempfile all;

# restore the tablespaces in the recovery set and the auxiliary set

restore clone datafile 1, 8, 2, 3;

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:\BACKUPNEW\SAGARDB\DATAFILE\O1_MF_TEMP_%U_.TMP in control file

Starting restore at 01-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:\BACKUPNEW\SAGARDB\DATAFILE\O1_MF_SYSTEM_%U_.DBF

channel ORA_AUX_DISK_1: restoring datafile 00008 to D:\BACKUPNEW\SAGARDB\DATAFILE\O1_MF_UNDOTBS2_%U_.DBF

channel ORA_AUX_DISK_1: restoring datafile 00002 to D:\BACKUPNEW\SAGARDB\DATAFILE\O1_MF_SYSAUX_%U_.DBF

channel ORA_AUX_DISK_1: restoring datafile 00003 to D:\BACKUP\TESTTBS01.DBF

channel ORA_AUX_DISK_1: reading from backup piece C:\APP\ORACLE\MSAGAR\FLASH_RECOVERY_AREA\SAGARDB\BACKUPSET\2011_01_01\O1_MF_NNNDF_TAG20110101T131020_6KXZDFVB_.

BKP

channel ORA_AUX_DISK_1: restored backup piece 1

channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:03:06

Finished restore at 01-JAN-11

datafile 1 switched to datafile copy

input datafile copy RECID=388 STAMP=739291881 file name=D:\BACKUPNEW\SAGARDB\DATAFILE\O1_MF_SYSTEM_6KY53Y6C_.DBF

datafile 8 switched to datafile copy

input datafile copy RECID=389 STAMP=739291881 file name=D:\BACKUPNEW\SAGARDB\DATAFILE\O1_MF_UNDOTBS2_6KY53YJT_.DBF

datafile 2 switched to datafile copy

input datafile copy RECID=390 STAMP=739291881 file name=D:\BACKUPNEW\SAGARDB\DATAFILE\O1_MF_SYSAUX_6KY53YG4_.DBF

contents of Memory Script:

{

# set requested point in time

set until scn 8448197;

# online the datafiles restored or switched

sql clone "alter database datafile 1 online";

sql clone "alter database datafile 8 online";

sql clone "alter database datafile 2 online";

sql clone "alter database datafile 3 online";

# recover and open resetlogs

recover clone database tablespace "TESTTBS", "SYSTEM", "UNDOTBS2", "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 8 online

sql statement: alter database datafile 2 online

sql statement: alter database datafile 3 online

Starting recover at 01-JAN-11

using channel ORA_AUX_DISK_1

starting media recovery

archived log for thread 1 with sequence 17 is already on disk as file C:\APP\ORACLE\MSAGAR\FLASH_RECOVERY_AREA\SAGARDB\ARCHIVELOG\2011_01_01\O1_MF_1_17_6KY53B3J_

.ARC

channel ORA_AUX_DISK_1: starting archived log restore to default destination

channel ORA_AUX_DISK_1: restoring archived log

archived log thread=1 sequence=16

channel ORA_AUX_DISK_1: reading from backup piece C:\APP\ORACLE\MSAGAR\FLASH_RECOVERY_AREA\SAGARDB\BACKUPSET\2011_01_01\O1_MF_ANNNN_TAG20110101T131514_6KXZOPGN_.

BKP

channel ORA_AUX_DISK_1: piece handle=C:\APP\ORACLE\MSAGAR\FLASH_RECOVERY_AREA\SAGARDB\BACKUPSET\2011_01_01\O1_MF_ANNNN_TAG20110101T131514_6KXZOPGN_.BKP tag=TAG20

110101T131514

channel ORA_AUX_DISK_1: restored backup piece 1

channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:01

archived log file name=D:\BACKUPNEW\ARC0000000016_0738861267.0001 thread=1 sequence=16

channel clone_default: deleting archived log(s)

archived log file name=D:\BACKUPNEW\ARC0000000016_0738861267.0001 RECID=777 STAMP=739291886

archived log file name=C:\APP\ORACLE\MSAGAR\FLASH_RECOVERY_AREA\SAGARDB\ARCHIVELOG\2011_01_01\O1_MF_1_17_6KY53B3J_.ARC thread=1 sequence=17

media recovery complete, elapsed time: 00:00:06

Finished recover at 01-JAN-11

database opened

contents of Memory Script:

{

# make read only the tablespace that will be exported

sql clone 'alter tablespace TESTTBS read only';

# create directory for datapump import

sql "create or replace directory TSPITR_DIROBJ_DPDIR as ''

D:\backupnew''";

# create directory for datapump export

sql clone "create or replace directory TSPITR_DIROBJ_DPDIR as ''

D:\backupnew''";

}

executing Memory Script

sql statement: alter tablespace TESTTBS read only

sql statement: create or replace directory TSPITR_DIROBJ_DPDIR as ''D:\backupnew''

sql statement: create or replace directory TSPITR_DIROBJ_DPDIR as ''D:\backupnew''

Performing export of metadata...

EXPDP> Starting "SYS"."TSPITR_EXP_Bopd":

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_Bopd" successfully loaded/unloaded

EXPDP> ******************************************************************************

EXPDP> Dump file set for SYS.TSPITR_EXP_Bopd is:

EXPDP> D:\BACKUPNEW\TSPITR_BOPD_37645.DMP

EXPDP> ******************************************************************************

EXPDP> Datafiles required for transportable tablespace TESTTBS:

EXPDP> D:\BACKUP\TESTTBS01.DBF

EXPDP> Job "SYS"."TSPITR_EXP_Bopd" successfully completed at 15:12:24

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_Bopd" successfully loaded/unloaded

IMPDP> Starting "SYS"."TSPITR_IMP_Bopd":

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_Bopd" successfully completed at 15:16:47

Import completed

contents of Memory Script:

{

# make read write and offline the imported tablespaces

sql 'alter tablespace TESTTBS read write';

sql 'alter tablespace TESTTBS offline';

# enable autobackups after TSPITR is finished

sql 'begin dbms_backup_restore.AutoBackupFlag(TRUE); end;';

}

executing Memory Script

sql statement: alter tablespace TESTTBS read write

sql statement: alter tablespace TESTTBS offline

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

Removing automatic instance

Automatic instance removed

auxiliary instance file D:\BACKUPNEW\SAGARDB\DATAFILE\O1_MF_TEMP_6KY5C761_.TMP deleted

auxiliary instance file D:\BACKUPNEW\SAGARDB\ONLINELOG\O1_MF_4_6KY5BT82_.LOG deleted

auxiliary instance file D:\BACKUPNEW\SAGARDB\ONLINELOG\O1_MF_3_6KY5BQSZ_.LOG deleted

auxiliary instance file D:\BACKUPNEW\SAGARDB\ONLINELOG\O1_MF_2_6KY5BOYS_.LOG deleted

auxiliary instance file D:\BACKUPNEW\SAGARDB\ONLINELOG\O1_MF_1_6KY5BMV3_.LOG deleted

auxiliary instance file D:\BACKUPNEW\SAGARDB\DATAFILE\O1_MF_SYSAUX_6KY53YG4_.DBF deleted

auxiliary instance file D:\BACKUPNEW\SAGARDB\DATAFILE\O1_MF_UNDOTBS2_6KY53YJT_.DBF deleted

auxiliary instance file D:\BACKUPNEW\SAGARDB\DATAFILE\O1_MF_SYSTEM_6KY53Y6C_.DBF deleted

auxiliary instance file D:\BACKUPNEW\SAGARDB\CONTROLFILE\O1_MF_6KY50OH2_.CTL deleted

Finished recover at 01-JAN-11

RMAN>

Step 7: Check tablespace status

SQL> select status,tablespace_name from dba_tablespaces where tablespace_name like 'TESTTBS%';;

STATUS TABLESPACE_NAME

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

OFFLINE TESTTBS

11 rows selected.

Alter tablespace online

SQL> alter tablespace testtbs online;

Tablespace altered.

Step 8: Check table

SQL> conn testtbs/testtbs;

Connected.

SQL> select * from tab;

TNAME TABTYPE CLUSTERID

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

TEST TABLE

SQL> select * from test;

EMPNAME CITY

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

SAGAR RIVAZ

babbar noida

SQL>

And also I'm go to check other table for data consistent.

SQL> conn testdb/testdb;

Connected.

SQL> select * from d;

EMPNAME

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

azmi

kareena

sam

Now successfully recovered dropped tablespace using RMAN point in time recovery.