Performing Tablespace Point-in-time Recovery.

With an example I will make you understand RMAN Fully Automated TSPITR. Workaround I will truncate a table which resides on USERS tablespace and later I will recover the tablespace to get back my data.

Note that my interaction with the RMAN client is shown as BOLD.

A)Get the rows of my_table.

SQL> select count(*) from my_table;
COUNT(*)
----------
49792
B)Truncate the Table.

SQL> TRUNCATE TABLE MY_TABLE;
Table truncated.

C)Determine the Tablespace which is belong my Dropped Table.

SQL> SELECT TABLESPACE_NAME from DBA_TABLES WHERE TABLE_NAME='MY_TABLE';
TABLESPACE_NAME
------------------------------
USERS

D)Connect to rman and perform TSPITR operation on USERS Tablespace.

SQL> !rman target /
Recovery Manager: Release 10.2.0.1.0 - Production on Thu May 8 04:49:54 2008
Copyright (c) 1982, 2005, Oracle. All rights reserved.
connected to target database: DBASE (DBID=1509380669)

RMAN> RECOVER TABLESPACE USERS UNTIL TIME 'SYSDATE-1/24/60*5' AUXILIARY DESTINATION '/export/home/oracle';

Starting recover at 08-MAY-08
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=159 devtype=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='jDiz'

initialization parameters used for automatic instance:
db_name=DBASE
compatible=10.2.0.1.0
db_block_size=8192
db_files=200
db_unique_name=tspitr_DBASE_jDiz
large_pool_size=1M
shared_pool_size=110M
#No auxiliary parameter file used
db_create_file_dest=/export/home/oracle
control_files=/export/home/oracle/cntrl_tspitr_DBASE_jDiz.f


starting up automatic instance DBASE

Oracle instance started

Total System Global Area 205520896 bytes

Fixed Size 2019576 bytes
Variable Size 146804488 bytes
Database Buffers 50331648 bytes
Redo Buffers 6365184 bytes
Automatic instance created

contents of Memory Script:
{
# set the until clause
set until time "SYSDATE-1/24/60*5";
# 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 08-MAY-08
allocated channel: ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: sid=37 devtype=DISK

channel ORA_AUX_DISK_1: starting datafile backupset restore
channel ORA_AUX_DISK_1: restoring control file
channel ORA_AUX_DISK_1: reading from backup piece /oradata2/flash_recovery_area/DBASE/backupset/2008_05_07/
o1_mf_ncsnf_TAG20080507T222432_424s4rpy_.bkp
channel ORA_AUX_DISK_1: restored backup piece 1
piece handle=/oradata2/flash_recovery_area/DBASE/backupset/2008_05_07/
o1_mf_ncsnf_TAG20080507T222432_424s4rpy_.bkp tag=TAG20080507T222432
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:02
output filename=/export/home/oracle/cntrl_tspitr_DBASE_jDiz.f
Finished restore at 08-MAY-08

sql statement: alter database mount clone database

sql statement: alter system archive log current

sql statement: begin dbms_backup_restore.AutoBackupFlag(FALSE); end;
released channel: ORA_DISK_1
released channel: ORA_AUX_DISK_1

contents of Memory Script:
{
# generated tablespace point-in-time recovery script
# set the until clause
set until time "SYSDATE-1/24/60*5";
plsql <<<-- tspitr_2declaresqlstatement varchar2(512);offline_not_needed exception;pragma exception_init(offline_not_needed, -01539);beginsqlstatement := 'alter tablespace '|| 'USERS' ||' offline for recover';krmicd.writeMsg(6162, sqlstatement);krmicd.execSql(sqlstatement);exceptionwhen offline_not_needed thennull;end; >>>;
# set an omf destination filename for restore
set newname for clone datafile 1 to new;
# set an omf destination filename for restore
set newname for clone datafile 2 to new;
# set an omf destination tempfile
set newname for clone tempfile 1 to new;
# set a destination filename for restore
set newname for datafile 4 to
"/oradata2/data1/dbase/users01.dbf";
# rename all tempfiles
switch clone tempfile all;
# restore the tablespaces in the recovery set plus the auxilliary tablespaces
restore 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 them
recover 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 Unix these two lines would do that:
#sql clone "create tablespace aux_tspitr_tmp
# datafile ''/tmp/aux_tspitr_tmp.dbf'' size 500K";
}
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

executing command: SET NEWNAME

renamed temporary file 1 to /export/home/oracle/TSPITR_DBASE_JDIZ/datafile/
o1_mf_temp_%u_.tmp in control file

Starting restore at 08-MAY-08
allocated channel: ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: sid=39 devtype=DISK

channel ORA_AUX_DISK_1: starting datafile backupset restore
channel ORA_AUX_DISK_1: specifying datafile(s) to restore from backup set
restoring datafile 00001 to /export/home/oracle/TSPITR_DBASE_JDIZ/datafile/
o1_mf_system_%u_.dbf
restoring datafile 00002 to /export/home/oracle/TSPITR_DBASE_JDIZ/datafile/
o1_mf_undotbs1_%u_.dbf
restoring datafile 00004 to /oradata2/data1/dbase/users01.dbf
channel ORA_AUX_DISK_1: reading from backup piece /oradata2/flash_recovery_area/DBASE/backupset/2008_05_07/
o1_mf_nnndf_TAG20080507T222432_424s30dp_.bkp
channel ORA_AUX_DISK_1: restored backup piece 1
piece handle=/oradata2/flash_recovery_area/DBASE/backupset/2008_05_07/
o1_mf_nnndf_TAG20080507T222432_424s30dp_.bkp tag=TAG20080507T222432
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:36
Finished restore at 08-MAY-08

datafile 1 switched to datafile copy
input datafile copy recid=6 stamp=654151900 filename=/export/home/oracle/TSPITR_DBASE_JDIZ/datafile/
o1_mf_system_425hqsjn_.dbf
datafile 2 switched to datafile copy
input datafile copy recid=7 stamp=654151900 filename=/export/home/oracle/TSPITR_DBASE_JDIZ/datafile/
o1_mf_undotbs1_425hqsjr_.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 08-MAY-08
using channel ORA_AUX_DISK_1

starting media recovery

archive log thread 1 sequence 3 is already on disk as file /oradata2/flash_recovery_area/DBASE/archivelog/2008_05_08/o1_mf_1_3_425h5ok9_.arc
archive log thread 1 sequence 4 is already on disk as file /oradata2/flash_recovery_area/DBASE/archivelog/2008_05_08/o1_mf_1_4_425hqnx5_.arc
archive log filename=/oradata2/flash_recovery_area/DBASE/archivelog/2008_05_08/o1_mf_1_3_425h5ok9_.arc thread=1 sequence=3
archive log filename=/oradata2/flash_recovery_area/DBASE/archivelog/2008_05_08/o1_mf_1_4_425hqnx5_.arc thread=1 sequence=4
media recovery complete, elapsed time: 00:00:07
Finished recover at 08-MAY-08

database opened

contents of Memory Script:
{
# export the tablespaces in the recovery set
host 'exp userid =\"/@\(DESCRIPTION=\(ADDRESS=\(PROTOCOL=beq\)\
(PROGRAM=/oracle/app/oracle/product/10.2.0/db_1/bin/oracle\)\
(ARGV0=oraclejDiz\)\(ARGS=^'\(DESCRIPTION=\(LOCAL=YES\)\
(ADDRESS=\(PROTOCOL=beq\)\)\)^'\)\(ENVS=^'ORACLE_SID=jDiz^'\)\)\(CONNECT_DATA=\(SID=jDiz\)\)\) 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 imported
sql "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.2.0.1.0 - Production on Thu May 8 04:51:58 2008

Copyright (c) 1982, 2005, Oracle. All rights reserved.


Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options
Export done in US7ASCII character set and AL16UTF16 NCHAR character set
server uses WE8ISO8859P1 character set (possible charset conversion)
Note: 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
EXP-00091: Exporting questionable statistics.
EXP-00091: Exporting questionable statistics.
. . exporting table EMP
EXP-00091: Exporting questionable statistics.
EXP-00091: Exporting questionable statistics.
. . exporting table BONUS
EXP-00091: Exporting questionable statistics.
. . exporting table SALGRADE
EXP-00091: Exporting questionable statistics.
. . exporting table MY_TABLE
. exporting referential integrity constraints
. exporting triggers
. end point-in-time recovery
Export terminated successfully with warnings.
host command complete

database closed
database dismounted
Oracle instance shut down


Import: Release 10.2.0.1.0 - Production on Thu May 8 04:52:19 2008

Copyright (c) 1982, 2005, Oracle. All rights reserved.


Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options

Export file created by EXPORT:V10.02.01 via conventional path
About to import Tablespace Point-in-time Recovery objects...
import done in US7ASCII character set and AL16UTF16 NCHAR character set
import server uses WE8ISO8859P1 character set (possible charset conversion)
. importing SYS's objects into SYS
. importing SCOTT's objects into SCOTT
. . importing table "DEPT"
. . importing table "EMP"
. . importing table "BONUS"
. . importing table "SALGRADE"
. importing ARJU's objects into ARJU
. . importing table "MY_TABLE"
. importing SCOTT's objects into SCOTT
. 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 instance
Automatic instance removed
auxiliary instance file /export/home/oracle/cntrl_tspitr_DBASE_jDiz.f deleted
auxiliary instance file /export/home/oracle/TSPITR_DBASE_JDIZ/datafile/o1_mf_system_425hqsjn_.dbf deleted
auxiliary instance file /export/home/oracle/TSPITR_DBASE_JDIZ/datafile/o1_mf_undotbs1_425hqsjr_.dbf deleted
auxiliary instance file /export/home/oracle/TSPITR_DBASE_JDIZ/datafile/o1_mf_temp_425hs9ot_.tmp deleted
auxiliary instance file /export/home/oracle/TSPITR_DBASE_JDIZ/onlinelog/o1_mf_1_425hs5oy_.log deleted
auxiliary instance file /export/home/oracle/TSPITR_DBASE_JDIZ/onlinelog/o1_mf_2_425hs6hl_.log deleted
auxiliary instance file /export/home/oracle/TSPITR_DBASE_JDIZ/onlinelog/o1_mf_3_425hs7kc_.log deleted
Finished recover at 08-MAY-08

E)Make the backup of Tablespace and make it Online.

RMAN> SQL'ALTER TABLESPACE USERS ONLINE';
sql statement: ALTER TABLESPACE USERS ONLINE

RMAN> exit;

Recovery Manager complete.

F)Check the Objects.

SQL> select count(*) from my_table;

COUNT(*)
----------
49792


Related Documents:
---------------------
When you use TSPITR
Limitations of TSPITR