Oracle RMAN TSPITR

Oracle RMAN TSPITR

Oracle RMAN TSPITR through we can recover one or more tablespace or other database objects without any trouble or affect rest of database objects.

We can use RMAN TSPITR in the following conditions

1. Recovering data lost after an erroneous TRUNCATE TABLE statement;
2. Recovering from logical corruption of a table;
3. Undoing the effects of an incorrect batch job or other DML statement that has affected only a subset of the database;
4. Recovering a logical schema to a point different from the rest of the physical database, when multiple schemas exist in separate tablespaces of one physical database.

Limitations of TSPITR

http://download.oracle.com/docs/cd/B19306_01/backup.102/b14191/rcmtspit001.htm#ABC1041015
Choosing the Right Target Time for TSPITR

It is extremely important that we choose the right target time or SCN for TSPITR. becuase once we bring tablespace ONLINE after TSPITR, we can't use any backup from a time earlier than the moment we brought the tablespace online.

Basic RMAN TSPITR

There are three options available for TSPITR

1. Fully automated TSPITR

2. Customized TSPITR with an automatic auxiliary instance

3. TSPITR with our own auxiliary instance

NOTE: Oracle recommended to use FULLY AUTOMATED TSPITR.

In Fully automated TSPITR oracle done entire process, there are two requirement which we need to prepare.

1. Specify auxiliary destination

2. Manually configure rman channel for auxiliary instance

Let see how RMAN TSPITR help us to recover one or more objects in

database with few commands

Deni working as an Oracle DBA in ABC bank, there developement team by mistake run wrong script which update all tables with wrong entries in particular schema.

Here I am using "scott" schema or "USERS" tablespace

NOTE: Must You have valid database backup & DB run in Archivelog mode to perform TSPITR.

Wrong script truncate all tables in Scott Schema, Now deni have task to recover all tables.

17:53:33 SQL> truncate table SCOTT.SALGRADE;
Table truncated.

For example above salgrade table is truncated by mistake from development team.

17:53:44 SQL> host rman target /
Recovery Manager: Release 10.1.0.2.0 - Production
Copyright (c) 1995, 2004, Oracle. All rights reserved.
connected to target database: TEST (DBID=1959343326)

RMAN> recover tablespace USERS until time "to_date('27-JUL-2008 17:53:23','DD-MON-YYYY HH24:MI:SS')" AUXILIARY DESTINATION='f:\auxiliary';

Starting recover at 27-JUL-08

using target database controlfile instead of recovery catalog

allocated channel: ORA_DISK_1

channel ORA_DISK_1: sid=143 devtype=DISK

Creating automatic instance
initialization parameters used for automatic instance:db_name=TEST

compatible=10.1.0.2.0

db_block_size=8192

db_unique_name=tspitr_TEST_rmzx

large_pool_size=1M

#No auxiliary parameter file used

db_create_file_dest=f:\auxiliary

control_files=f:\auxiliary/cntrl_tspitr_TEST_rmzx.f

starting up automatic instance TEST
Oracle instance started

Total System Global Area 117440512 bytes
Fixed Size 787728 bytes

Variable Size 65272560 bytes

Database Buffers 50331648 bytes

Redo Buffers 1048576 bytes

Automatic instance created
contents of Memory Script:{

# set the until clauseset until time "to_date('27-JUL-2008 17:53:23','DD-MON-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;';}executing Memory Script
executing command: SET until clause
Starting restore at 27-JUL-08

allocated channel: ORA_AUX_DISK_1

channel ORA_AUX_DISK_1: sid=29 devtype=DISK
channel ORA_AUX_DISK_1: starting datafile backupset restore

channel ORA_AUX_DISK_1: restoring controlfile

channel ORA_AUX_DISK_1: restored backup piece 1

piece handle=F:\ORACLE\PRODUCT\10.1.0\FLASH_RECOVERY_AREA\TEST\BACKUPSET\2008_07_27\O1_MF_NCSNF_TAG20080727T175226_48RZFO1D_.BKP tag=TAG20080727T175226

channel ORA_AUX_DISK_1: restore complete

output filename=F:\AUXILIARY\CNTRL_TSPITR_TEST_RMZX.F

Finished restore at 27-JUL-08

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:{

# generated tablespace point-in-time recovery script

# set the until clauseset until time "to_date('27-JUL-2008 17:53:23','DD-MON-YYYY HH24:MI:SS')";plsql <<<-- tspitr_2

declare sql

statement varchar2(512); offline_not_needed exception; pragma exception_init(offline_not_needed, -01539);begin sqlstatement := 'alter tablespace ' 'USERS' ' offline for recover'; krmicd.writeMsg(6162, sqlstatement); krmicd.execSql(sqlstatement);exception when offline_not_needed then null;end; >>>;

# set an omf destination filename for restoreset newname for clone datafile 1 to new;

# set an omf destination filename for restoreset newname for clone datafile 2 to new;

# set a destination filename for restoreset newname for datafile 4 to "F:\ORACLE\PRODUCT\10.1.0\ORADATA\TEST\USERS01.DBF";

# restore the tablespaces in the recovery set plus the auxilliary tablespacesrestore clone datafile 1, 2, 4;

switch clone datafile all;

#online the datafiles restored or flipped

sql clone "alter database datafile 1 online";

#online the datafiles restored or flipped

sql clone "alter database datafile 2 online";

#online the datafiles restored or flipped

sql clone "alter database datafile 4 online";

# make the controlfile point at the restored datafiles, then recover themrecover clone database tablespace "USERS", "SYSTEM", "UNDOTBS1" delete archivelog;alter clone database open resetlogs;

# PLUG HERE the creation of a temporary tablespace if export fails due to lack of temporary space.

# For example in Windows these two lines would do that:

#sql clone "create tablespace aux_tspitr_tmp

# datafile ''/tmp/aux_tspitr_tmp.dbf'' size 500K";

sql clone "alter tablespace temp

add tempfile ''f:/tmp/aux_tspitr_tmp.dbf'' size 5M";

}

executing Memory Script
executing command: SET until clause
sql statement: alter tablespace USERS offline for recover
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
Starting restore at 27-JUL-08using channel ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: starting datafile backupset restorechannel ORA_AUX_DISK_1: specifying datafile(s) to restore from backup setrestoring datafile 00001 to F:\AUXILIARY\TSPITR_T\DATAFILE\O1_MF_SYSTEM_%U_.DBFrestoring datafile 00002 to F:\AUXILIARY\TSPITR_T\DATAFILE\O1_MF_UNDOTBS1_%U_.DBFrestoring datafile 00004 to F:\ORACLE\PRODUCT\10.1.0\ORADATA\TEST\USERS01.DBFchannel ORA_AUX_DISK_1: restored backup piece 1piece handle=F:\ORACLE\PRODUCT\10.1.0\FLASH_RECOVERY_AREA\TEST\BACKUPSET\2008_07_27\O1_MF_NNNDF_TAG20080727T175226_48RZDV59_.BKP tag=TAG20080727T175226channel ORA_AUX_DISK_1: restore completeFinished restore at 27-JUL-08
datafile 1 switched to datafile copyinput datafilecopy recid=3 stamp=661197349 filename=F:\AUXILIARY\TSPITR_T\DATAFILE\O1_MF_SYSTEM_48RZLD8P_.DBFdatafile 2 switched to datafile copyinput datafilecopy recid=2 stamp=661197327 filename=F:\AUXILIARY\TSPITR_T\DATAFILE\O1_MF_UNDOTBS1_48RZLDB5_.DBF
sql statement: alter database datafile 1 online
sql statement: alter database datafile 2 online
sql statement: alter database datafile 4 online
Starting recover at 27-JUL-08using channel ORA_AUX_DISK_1
starting media recovery
archive log thread 1 sequence 5 is already on disk as file F:\ORACLE\PRODUCT\10.1.0\FLASH_RECOVERY_AREA\TEST\ARCHIVELOG\2008_07_27\O1_MF_1_5_48RZL9PD_.ARCarchive log filename=F:\ORACLE\PRODUCT\10.1.0\FLASH_RECOVERY_AREA\TEST\ARCHIVELOG\2008_07_27\O1_MF_1_5_48RZL9PD_.ARC thread=1 sequence=5

media recovery complete

Finished recover at 27-JUL-08
database opened
sql statement: alter tablespace temp add tempfile ''f:/tmp/aux_tspitr_tmp.dbf''size 5M
contents of Memory Script:{

# export the tablespaces in the recovery set

host 'exp userid =\"/@(DESCRIPTION=(ADDRESS=(PROTOCOL=beq)(PROGRAM=oracle)(ARGV0[/a]=oraclermzx)(ARGS=^'(DESCRIPTION=(LOCAL=YES)(ADDRESS=(PROTOCOL=beq)))^')(ENVS=^'ORACLE_SID=rmzx^'))(CONNECT_DATA=(SID=rmzx))) as sysdba\" point_in_time_recover=y tablespaces= USERS file=tspitr_a.dmp';

# shutdown clone before import

shutdown clone immediate

# import the tablespaces in the recovery set

host 'imp userid =\"/@ as sysdba\" point_in_time_recover=y file=tspitr_a.dmp';

# online/offline the tablespace importedsql "alter tablespace USERS online";sql "alter tablespace USERS offline";

# enable autobackups in case user does open resetlogs from RMAN after TSPITR

sql 'begin dbms_backup_restore.AutoBackupFlag(TRUE); end;';}executing Memory Script

Export: Release 10.1.0.2.0 - Production on Sun Jul 27 17:56:06 2008
Copyright (c) 1982, 2004, Oracle. All rights reserved.
Connected to: Oracle Database 10g Enterprise Edition Release 10.1.0.2.0 - ProductionWith the Partitioning, OLAP and Data Mining optionsExport done in WE8MSWIN1252 character set and AL16UTF16 NCHAR character setNote: table data (rows) will not be exported
About to export Tablespace Point-in-time Recovery objects...

For tablespace USERS ....

exporting cluster definitions.

exporting table definitions. .

exporting table DEPT. .

exporting table EMP. .

exporting table BONUS. .

exporting table SALGRADE.

exporting referential integrity constraints.

exporting triggers. end point-in-time recovery

Export terminated successfully without warnings.

host command complete
database closed

database dismounted

Oracle instance shut down

Import: Release 10.1.0.2.0 - Production on Sun Jul 27 17:56:21 2008
Copyright (c) 1982, 2004, Oracle. All rights reserved.
Connected to: Oracle Database 10g Enterprise Edition Release 10.1.0.2.0 - Production

With the Partitioning, OLAP and Data Mining options
Export file created by EXPORT:V10.01.00 via conventional pathAbout to import Tablespace Point-in-time Recovery objects...

import done in WE8MSWIN1252 character set and AL16UTF16 NCHAR character set.

importing SCOTT's objects into SCOTT. .

importing table "DEPT". .

importing table "EMP". .

importing table "BONUS". .

importing table "SALGRADE".

importing SYS's objects into SYS

Import terminated successfully without warnings.

host command complete
sql statement: alter tablespace USERS online
sql statement: alter tablespace USERS offline
sql statement: begin dbms_backup_restore.AutoBackupFlag(TRUE); end;
Removing automatic instanceAutomatic instance removedauxiliary instance datafile F:\AUXILIARY\CNTRL_TSPITR_TEST_RMZX.F deletedauxiliary instance datafile F:\AUXILIARY\TSPITR_T\DATAFILE\O1_MF_SYSTEM_48RZLD8P_.DBF deletedauxiliary instance datafile F:\AUXILIARY\TSPITR_T\DATAFILE\O1_MF_UNDOTBS1_48RZLDB5_.DBF deletedauxiliary instance datafile F:\AUXILIARY\TSPITR_T\ONLINELOG\O1_MF_1_48RZMKWQ_.LOG deletedauxiliary instance datafile F:\AUXILIARY\TSPITR_T\ONLINELOG\O1_MF_2_48RZML59_.LOG deletedauxiliary instance datafile F:\AUXILIARY\TSPITR_T\ONLINELOG\O1_MF_3_48RZMLDM_.LOG deletedFinished recover at 27-JUL-08

RMAN> backup tablespace USERS;

NOTE: Oracle Recommended to Take tablespace backup

Starting backup at 27-JUL-08

using channel ORA_DISK_1

channel ORA_DISK_1: starting full datafile backupset

channel ORA_DISK_1: specifying datafile(s) in backupsetinput datafile fno=00004 name=F:\ORACLE\PRODUCT\10.1.0\ORADATA\TEST\USERS01.DBFchannel ORA_DISK_1: starting piece 1 at 27-JUL-08channel ORA_DISK_1: finished piece 1 at 27-JUL-08piece handle=F:\ORACLE\PRODUCT\10.1.0\FLASH_RECOVERY_AREA\TEST\BACKUPSET\2008_07_27\O1_MF_NNNDF_TAG20080727T175710_48RZOPXP_.BKP comment=NONEchannel ORA_DISK_1: backup set complete, elapsed time: 00:00:01Finished backup at 27-JUL-08

RMAN> sql "alter tablespace USERS online";
sql statement: alter tablespace USERS online

NOTE: Now take tablespace ONLINE.

RMAN> exit
Recovery Manager complete.

17:57:45 SQL> select count(*) from scott.salgrade;
COUNT(*)

----------

5

User sysdba connect must be use Operating system authentication otherwise following error occurs.

RMAN-00571: ===========================================================

RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============

RMAN-00571: ===========================================================

RMAN-03002: failure of recover command at 07/27/2008 18:11:19RMAN-04006: error from auxiliary database:

ORA-01031: insufficient privileges

If your getting below error

About to export Tablespace Point-in-time Recovery objects...
EXP-00008: ORACLE error 25153 encountered
ORA-25153: Temporary Tablespace is Empty
ORA-06512: at "SYS.DBMS_PITR", line 887
ORA-06512: at line 1
EXP-00000: Export terminated unsuccessfully
host command complete

check solution: http://dbataj.blogspot.com/2008/07/rman-tspitr-ora-25153.html