The test case assumes there is only one table: AUTO_TABLE in the tablespace AUTO_TBS.
The situation could be more complicated than what will be shown below if there are other tables in the tablespace that have been updated after the target time to which the tablespace will be recovered.
ref: http://download.oracle.com/docs/cd/B19306_01/backup.102/b14191/rcmtspit002.htm#i1010246
1. Information about the auto_table
a. auto_table resides in the AUTO_TBS tablespace
TABLE_NAME TABLESPACE_NAME
------------------------------ ------------------------------
.....
AUTO_TABLE AUTO_TBS
b. Number of rows
scott@ORCL> select count(*) from auto_table;
COUNT(*)
----------
66065
c. Timestamp just before truncate
scott@ORCL> select sysdate from dual;
SYSDATE
--------------------
07-OCT-2007 19:43:11
scott@ORCL> truncate table auto_table;
Table truncated.
The objective is to recover the auto_table to the point before the truncate.
2. Planing and Preparing for TSPITR
2.1 Choosing the Right Target Time for TSPITR
07-OCT-2007 19:43:11
2.2 Determining the Recovery Set: Analyzing Data Relationships
SELECT *
FROM SYS.TS_PITR_CHECK
WHERE (
TS1_NAME IN ('AUTO_TBS')
AND TS2_NAME NOT IN ('AUTO_TBS')
)
OR (
TS1_NAME NOT IN ('AUTO_TBS')
AND TS2_NAME IN ('AUTO_TBS')
);
Note: There are no complications for this case - no dependencies of auto_table on other objects in auto_tbs or other tablespaces
2.3 Identifying and Preserving Objects That Will Be Lost After TSPITR
SELECT OWNER, NAME, TABLESPACE_NAME,
TO_CHAR(CREATION_TIME, 'YYYY-MM-DD:HH24:MI:SS')
FROM TS_PITR_OBJECTS_TO_BE_DROPPED
WHERE TABLESPACE_NAME IN ('AUTO_TBS')
AND CREATION_TIME > TO_DATE('07-OCT-2007 19:43:11','YY-MON-DD:HH24:MI:SS')
ORDER BY TABLESPACE_NAME, CREATION_TIME;
Note:
You can preserve such objects, once they are identified, by exporting them before TSPITR using an Oracle export utility (Data Pump Export or Original Export) and re-importing them afterwards using the corresponding import utility.
In this case, we don't have any such objects.
3. Performing Basic RMAN TSPITR
Three options:
A. Fully automated TSPITR
B. Customized TSPITR with an automatic auxiliary instance
C. TSPITR with your own auxiliary instance
3.1 Take the tablespace AUTO_TBS offline
sys@ORCL> alter tablespace auto_tbs offline;
Tablespace altered.
3.2 Rocover using 'Fully automated TSPITR'
Note: Using an Auxiliary Destination for Automated RMAN TSPITR
e:\auxdest
start the RMAN client, connecting to the target database and, if applicable, a recovery catalog.
The following is the RMAN command:
RMAN> RECOVER TABLESPACE auto_tbs
2> UNTIL TIME '07-OCT-2007 19:43:11'
3> AUXILIARY DESTINATION 'e:\auxdest'
4> ;
Appendix A shows the full RMAN screen output.
3.3 Backing Up Recovered Tablespaces After TSPITR
RMAN> BACKUP TABLESPACE AUTO_TBS
3.4 Bring the tablespaces online, as follows:
RMAN> SQL "ALTER TABLESPACE AUTO_TBS ONLINE";
Appendix A:
===========
RMAN> RECOVER TABLESPACE auto_tbs
2> UNTIL TIME '07-OCT-2007 19:43:11'
3> AUXILIARY DESTINATION 'e:\auxdest'
4> ;
Starting recover at 07-OCT-2007 20:10:36
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=158 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
tablespace UNDO_BATCH
Creating automatic instance, with SID='aFbc'
initialization parameters used for automatic instance:
db_name=ORCL
compatible=10.2.0.1.0
db_block_size=8192
db_files=200
db_unique_name=tspitr_ORCL_aFbc
large_pool_size=1M
shared_pool_size=110M
#No auxiliary parameter file used
db_create_file_dest=e:\auxdest
control_files=e:\auxdest/cntrl_tspitr_ORCL_aFbc.f
starting up automatic instance ORCL
Oracle instance started
Total System Global Area 201326592 bytes
Fixed Size 1248092 bytes
Variable Size 146801828 bytes
Database Buffers 50331648 bytes
Redo Buffers 2945024 bytes
Automatic instance created
contents of Memory Script:
{
# set the until clause
set until time "07-OCT-2007 19:43:11";
# 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 07-OCT-2007 20:12:09
allocated channel: ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: sid=36 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 E:\ORAFLASH\ORCL\BACKUPSET\2007_10_07\O1_MF_NCSN0_TAG2007100
LND6K2_.BKP
channel ORA_AUX_DISK_1: restored backup piece 1
piece handle=E:\ORAFLASH\ORCL\BACKUPSET\2007_10_07\O1_MF_NCSN0_TAG20071007T173142_3JLND6K2_.BKP tag=TAG2007100
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:05
output filename=E:\AUXDEST\CNTRL_TSPITR_ORCL_AFBC.F
Finished restore at 07-OCT-2007 20:12:48
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 "07-OCT-2007 19:43:11";
# 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 filename for restore
set newname for clone datafile 8 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 6 to
"E:\ORADATA\ORCL\AUTO_TBS_DATA01.DBF";
# rename all tempfiles
switch clone tempfile all;
# restore the tablespaces in the recovery set plus the auxilliary tablespaces
restore clone datafile 1, 2, 8, 6;
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 8 online";
#online the datafiles restored or flipped
sql clone "alter database datafile 6 online";
# make the controlfile point at the restored datafiles, then recover them
recover clone database tablespace "AUTO_TBS", "SYSTEM", "UNDOTBS1", "UNDO_BATCH" 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
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
renamed temporary file 1 to E:\AUXDEST\TSPITR_O\DATAFILE\O1_MF_TEMP_%U_.TMP in control file
Starting restore at 07-OCT-2007 20:16:10
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 E:\AUXDEST\TSPITR_O\DATAFILE\O1_MF_SYSTEM_%U_.DBF
restoring datafile 00002 to E:\AUXDEST\TSPITR_O\DATAFILE\O1_MF_UNDOTBS1_%U_.DBF
restoring datafile 00008 to E:\AUXDEST\TSPITR_O\DATAFILE\O1_MF_UNDO_BAT_%U_.DBF
restoring datafile 00006 to E:\ORADATA\ORCL\AUTO_TBS_DATA01.DBF
channel ORA_AUX_DISK_1: reading from backup piece E:\ORAFLASH\ORCL\BACKUPSET\2007_10_07\O1_MF_NNND0_TAG20071007T173142_3J
LN26F5_.BKP
channel ORA_AUX_DISK_1: restored backup piece 1
piece handle=E:\ORAFLASH\ORCL\BACKUPSET\2007_10_07\O1_MF_NNND0_TAG20071007T173142_3JLN26F5_.BKP tag=TAG20071007T173142
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:06:26
Finished restore at 07-OCT-2007 20:23:10
datafile 1 switched to datafile copy
input datafile copy recid=9 stamp=635372617 filename=E:\AUXDEST\TSPITR_O\DATAFILE\O1_MF_SYSTEM_3JLXQD89_.DBF
datafile 2 switched to datafile copy
input datafile copy recid=10 stamp=635372617 filename=E:\AUXDEST\TSPITR_O\DATAFILE\O1_MF_UNDOTBS1_3JLXQDRW_.DBF
datafile 8 switched to datafile copy
input datafile copy recid=11 stamp=635372618 filename=E:\AUXDEST\TSPITR_O\DATAFILE\O1_MF_UNDO_BAT_3JLXQF00_.DBF
sql statement: alter database datafile 1 online
sql statement: alter database datafile 2 online
sql statement: alter database datafile 8 online
sql statement: alter database datafile 6 online
Starting recover at 07-OCT-2007 20:24:23
using channel ORA_AUX_DISK_1
using channel ORA_AUX_DISK_1
starting media recovery
archive log thread 1 sequence 9 is already on disk as file E:\ORAFLASH\ORCL\ARCHIVELOG\2007_10_07\O1_MF_1_9_3JLOS56T_.ARC
archive log thread 1 sequence 10 is already on disk as file E:\ORAFLASH\ORCL\ARCHIVELOG\2007_10_07\O1_MF_1_10_3JLXKK6T_.A
RC
archive log filename=E:\ORAFLASH\ORCL\ARCHIVELOG\2007_10_07\O1_MF_1_9_3JLOS56T_.ARC thread=1 sequence=9
archive log filename=E:\ORAFLASH\ORCL\ARCHIVELOG\2007_10_07\O1_MF_1_10_3JLXKK6T_.ARC thread=1 sequence=10
media recovery complete, elapsed time: 00:00:13
Finished recover at 07-OCT-2007 20:25:37
database opened
contents of Memory Script:
{
# export the tablespaces in the recovery set
host 'exp userid =\"/@(DESCRIPTION=(ADDRESS=(PROTOCOL=beq)(PROGRAM=oracle)(ARGV0=oracleaFbc)(ARGS=^'(DESCRIPTION=(LOCAL=Y
ES)(ADDRESS=(PROTOCOL=beq)))^')(ENVS=^'ORACLE_SID=aFbc^'))(CONNECT_DATA=(SID=aFbc))) as sysdba\" point_in_time_recover=y
tablespaces=
AUTO_TBS 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 AUTO_TBS online";
sql "alter tablespace AUTO_TBS 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 Sun Oct 7 20:26:49 2007
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 WE8MSWIN1252 character set and AL16UTF16 NCHAR character set
Note: table data (rows) will not be exported
About to export Tablespace Point-in-time Recovery objects...
For tablespace AUTO_TBS ...
. exporting cluster definitions
. exporting table definitions
. . exporting table AUTO_TABLE
. 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.2.0.1.0 - Production on Sun Oct 7 20:30:59 2007
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 WE8MSWIN1252 character set and AL16UTF16 NCHAR character set
. importing SYS's objects into SYS
. importing SCOTT's objects into SCOTT
. . importing table "AUTO_TABLE"
. importing SYS's objects into SYS
Import terminated successfully without warnings.
host command complete
sql statement: alter tablespace AUTO_TBS online
sql statement: alter tablespace AUTO_TBS offline
sql statement: begin dbms_backup_restore.AutoBackupFlag(TRUE); end;
Removing automatic instance
Automatic instance removed
auxiliary instance file E:\AUXDEST\CNTRL_TSPITR_ORCL_AFBC.F deleted
auxiliary instance file E:\AUXDEST\TSPITR_O\DATAFILE\O1_MF_SYSTEM_3JLXQD89_.DBF deleted
auxiliary instance file E:\AUXDEST\TSPITR_O\DATAFILE\O1_MF_UNDOTBS1_3JLXQDRW_.DBF deleted
auxiliary instance file E:\AUXDEST\TSPITR_O\DATAFILE\O1_MF_UNDO_BAT_3JLXQF00_.DBF deleted
auxiliary instance file E:\AUXDEST\TSPITR_O\DATAFILE\O1_MF_TEMP_3JLY97MK_.TMP deleted
auxiliary instance file E:\AUXDEST\TSPITR_O\ONLINELOG\O1_MF_1_3JLY8G4W_.LOG deleted
auxiliary instance file E:\AUXDEST\TSPITR_O\ONLINELOG\O1_MF_2_3JLY8OX9_.LOG deleted
auxiliary instance file E:\AUXDEST\TSPITR_O\ONLINELOG\O1_MF_3_3JLY8WJM_.LOG deleted
Finished recover at 07-OCT-2007 20:32:10
RMAN>
No comments:
Post a Comment