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