TSPITR恢复drop 表空间的例子

TSPITR例子

前几天误删了一个表空间，用TSPITR例子恢复了，就此些了一个简单的文档供大家参考。

RMAN Tablespace Point in Time Recovery (TSPITR)例子
                                     Create by Gavin(waityou81@hotmail.com)

在我们不小心drop一个表或表空间时，可以用TSPITR恢复。
TSPITR的原理是把数据库或数据库的子集到一个时间点恢复到辅助（auxiliary）数据库，然后把要恢复的表导出并导入到目标库中，从而找到丢失的数据。前提是有rman的备份。
下面是我做的一个试验供大家参考。
环境：
目标库（target database）:9204,rhel 3
rman catalog db:9205 window2000
target db 和aux（auxiliary） db 在同一台机器上。

1.        新建一个文件夹用于存放aux数据库的数据文件，位置任选
mkdir /opt/oracle/admin/aux
把init_sid.ora拷贝到$ORACLE_HOME/dbs/下并改为init_aux.ora
修改init_aux.ora
db_block_size = <same size as the target>
DB_NAME=aux
compatible = 9.2.0.0   /* should be the same as the target*/
CONTROL_FILES=(/opt/oracle/admin/aux /control01.ctl)
lock_name_space=aux
#db_file_name_convert=("/opt/oracle/oradata/test","/opt/oracle/admin/aux"
#log_file_name_convert=("/opt/oracle/oradata/test","/opt/oracle/admin/aux"
带＃的也可以不设，在复制脚本中可以使用rename指定路径，建议设置。
还要注掉有关归档的参数，类似
#log_archive_start=true
#log_archive_dest=/opt/oracle/product/9.2.0/dbs/arch
#log_archive_format=%s.dat

2.创建辅助实例
在target数据库上运行
export ORACLE_SID=aux
sqlplus /nolog
conn / as sysdba
启动辅助实例到nomunt状态（并保证目标数据库是open的）
startup nomount pfile=$ORACLE_HOME/dbs/init_aux.ora
3.创建aux库密码文件
orapw file=orapwaux password=sys entries=30

修改监听文件
vi /opt/oracle/product/9.2.0/network/admin/listener.ora
加入
(SID_DESC =
  (GLOBAL_DBNAME = aux)
  (ORACLE_HOME = /opt/oracle/product/9.2.0)
  (SID_NAME = aux)
)
重启监听

修改rman库的tnsname.ora，加入aux库的别名
4.复制数据库
在rman db上运行
rman catalog rman/rman target sys/sys@test2
CONNECT AUXILIARY SYS/sys@aux
运行复制脚本，类似
run {
SET until time "TO_DATE('2006-01-09 17:26:00','YYYY-MM-DD HH24:MI:SS')";
DUPLICATE TARGET DATABASE TO AUX;
}
如果不想复制一些表空间，可以skip它们，脚本类似
run {
SET until time "TO_DATE('2006-01-09 17:26:00','YYYY-MM-DD HH24:MI:SS')";
DUPLICATE TARGET DATABASE TO AUX
SKIP TABLESPACE indx,tools;
}
但是system，undo表空间是不能skip的。
还可以指定redolog文件的大小，脚本类似
run {
SET until time "TO_DATE('2006-01-09 17:26:00','YYYY-MM-DD HH24:MI:SS')";
DUPLICATE TARGET DATABASE TO AUX
SKIP TABLESPACE indx,tools
LOGFILE
GROUP 1 ('/opt/oracle/admin/aux/redo01.log') SIZE 10M ,
GROUP 2 ('/opt/oracle/admin/aux/redo02.log') SIZE 10M ,
GROUP 3 ('/opt/oracle/admin/aux/redo03.log') SIZE 10M ;
}

屏幕输出
C:/Documents and Settings/Administrator>rman catalog rman/rman target sys/sys@test2

Recovery Manager: Release 9.2.0.5.0 - Production

Copyright (c) 1995, 2002, Oracle Corporation.  All rights reserved.

connected to target database: TEST (DBID=1877002542)
connected to recovery catalog database

RMAN> CONNECT AUXILIARY SYS/sys@aux

connected to auxiliary database: aux (not mounted)

RMAN> run {
2> SET until time "TO_DATE('2006-01-09 17:26:00','YYYY-MM-DD HH24:MI:SS')";
3> DUPLICATE TARGET DATABASE TO AUX
4> SKIP TABLESPACE indx,tools,users
5> LOGFILE
6> GROUP 1 ('/opt/oracle/admin/aux/redo01.log') SIZE 10M ,
7> GROUP 2 ('/opt/oracle/admin/aux/redo02.log') SIZE 10M ,
8> GROUP 3 ('/opt/oracle/admin/aux/redo03.log') SIZE 10M ;
9> }

executing command: SET until clause

Starting Duplicate Db at 12-JAN-06
allocated channel: ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: sid=10 devtype=DISK
Datafile 3 skipped by request
Datafile 4 skipped by request
Datafile 5 skipped by request

printing stored script: Memory Script
{
   set until scn  122599;
   set newname for datafile  1 to
"/opt/oracle/admin/aux/system01.dbf";
   set newname for datafile  2 to
"/opt/oracle/admin/aux/undotbs01.dbf";
   set newname for datafile  6 to
"/opt/oracle/admin/aux/tspitr.ora";
   restore
   check readonly
   clone database
   skip tablespace  USERS, TOOLS, INDX   ;
}
executing script: Memory Script

executing command: SET until clause

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

Starting restore at 12-JAN-06

using channel ORA_AUX_DISK_1
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 /opt/oracle/admin/aux/system01.dbf
restoring datafile 00002 to /opt/oracle/admin/aux/undotbs01.dbf
restoring datafile 00006 to /opt/oracle/admin/aux/tspitr.ora
channel ORA_AUX_DISK_1: restored backup piece 1
piece handle=/opt/oracle/product/9.2.0/dbs/rmandbbak/01h8ef5v_1_1 tag=DBL0 params=NULL
channel ORA_AUX_DISK_1: restore complete
Finished restore at 12-JAN-06
sql statement: CREATE CONTROLFILE REUSE SET DATABASE "AUX" RESETLOGS ARCHIVELOG
  MAXLOGFILES      5
  MAXLOGMEMBERS      3
  MAXDATAFILES      100
  MAXINSTANCES     1
  MAXLOGHISTORY      226
LOGFILE
  GROUP  1 ( '/opt/oracle/admin/aux/redo01.log' ) SIZE   10485760 ,
  GROUP  2 ( '/opt/oracle/admin/aux/redo02.log' ) SIZE   10485760 ,
  GROUP  3 ( '/opt/oracle/admin/aux/redo03.log' ) SIZE   10485760
DATAFILE
  '/opt/oracle/admin/aux/system01.dbf'
CHARACTER SET ZHS16GBK


printing stored script: Memory Script
{
   switch clone datafile all;
}
executing script: Memory Script

datafile 2 switched to datafile copy
input datafilecopy recid=1 stamp=579555207 filename=/opt/oracle/admin/aux/undotbs01.dbf
datafile 6 switched to datafile copy
input datafilecopy recid=2 stamp=579555207 filename=/opt/oracle/admin/aux/tspitr.ora

printing stored script: Memory Script
{
   set until time  "TO_DATE('2006-01-09 17:26:00','YYYY-MM-DD HH24:MI:SS')";
   recover
   clone database
    delete archivelog
   ;
}
executing script: Memory Script

executing command: SET until clause

Starting recover at 12-JAN-06
using channel ORA_AUX_DISK_1
datafile 3 not processed because file is offline
datafile 4 not processed because file is offline
datafile 5 not processed because file is offline

starting media recovery

archive log thread 1 sequence 7 is already on disk as file /opt/oracle/product/9.2.0/dbs/arch/7.dat
archive log thread 1 sequence 8 is already on disk as file /opt/oracle/product/9.2.0/dbs/arch/8.dat
archive log filename=/opt/oracle/product/9.2.0/dbs/arch/7.dat thread=1 sequence=7
archive log filename=/opt/oracle/product/9.2.0/dbs/arch/8.dat thread=1 sequence=8
media recovery complete
Finished recover at 12-JAN-06

printing stored script: Memory Script
{
   shutdown clone;
   startup clone nomount ;
}
executing script: Memory Script

database dismounted
Oracle instance shut down

connected to auxiliary database (not started)
Oracle instance started

Total System Global Area     135337420 bytes

Fixed Size                      452044 bytes
Variable Size                109051904 bytes
Database Buffers              25165824 bytes
Redo Buffers                    667648 bytes
sql statement: CREATE CONTROLFILE REUSE SET DATABASE "AUX" RESETLOGS ARCHIVELOG
  MAXLOGFILES      5
  MAXLOGMEMBERS      3
  MAXDATAFILES      100
  MAXINSTANCES     1
  MAXLOGHISTORY      226
LOGFILE
  GROUP  1 ( '/opt/oracle/admin/aux/redo01.log' ) SIZE   10485760 ,
  GROUP  2 ( '/opt/oracle/admin/aux/redo02.log' ) SIZE   10485760 ,
  GROUP  3 ( '/opt/oracle/admin/aux/redo03.log' ) SIZE   10485760
DATAFILE
  '/opt/oracle/admin/aux/system01.dbf'
CHARACTER SET ZHS16GBK


printing stored script: Memory Script
{
   catalog clone datafilecopy  "/opt/oracle/admin/aux/undotbs01.dbf";
   catalog clone datafilecopy  "/opt/oracle/admin/aux/tspitr.ora";
   switch clone datafile all;
}
executing script: Memory Script

cataloged datafile copy
datafile copy filename=/opt/oracle/admin/aux/undotbs01.dbf recid=1 stamp=579555218

cataloged datafile copy
datafile copy filename=/opt/oracle/admin/aux/tspitr.ora recid=2 stamp=579555218

datafile 2 switched to datafile copy
input datafilecopy recid=1 stamp=579555218 filename=/opt/oracle/admin/aux/undotbs01.dbf
datafile 6 switched to datafile copy
input datafilecopy recid=2 stamp=579555218 filename=/opt/oracle/admin/aux/tspitr.ora

printing stored script: Memory Script
{
   Alter clone database open resetlogs;
}
executing script: Memory Script

database opened

printing stored script: Memory Script
{
# drop offline and skipped tablespaces
sql clone "drop tablespace  USERS including contents";
# drop offline and skipped tablespaces
sql clone "drop tablespace  TOOLS including contents";
# drop offline and skipped tablespaces
sql clone "drop tablespace  INDX including contents";
}
executing script: Memory Script

sql statement: drop tablespace  USERS including contents

sql statement: drop tablespace  TOOLS including contents

sql statement: drop tablespace  INDX including contents
Finished Duplicate Db at 12-JAN-06

RMAN>
这是你就可以打开aux库，导出想恢复的数据了，呵呵
具体细节请参考Oracle官方文档。
参考metalink文档id: 109979.1,228257.1,223543.1,180436.1