全自动 TSPITR –一条命令即可完成 TSPITR 的测试

PHPABC Oracle 1,000 次浏览 , 没有评论

今天下午得空10分钟,抽空体验了一下Oracle 10g开始提供的全自动TSPITR的方法,只需要一条命令即可完成,感觉不错,有人说这个全自动的TSPITR除了要满足TSPITR的必须要求外,
还要具备这样那样的条件,否则不容易成功,不过我测试却很顺利, 莫非与RP有关系? 哈哈

下面看看我的体验吧
SQL> select current_scn,to_char(sysdate,’yyyy-mm-dd hh24:mi:ss’) from v$database;
CURRENT_SCN TO_CHAR(SYSDATE,’YYYY-MM-DDHH
———– ———————————
874247 2010-06-28 07:23:29

SQL> select count(*) from study.zrp_test;
COUNT(*)
———-
9509

SQL> select owner,table_name,tablespace_name from dba_tables where table_name=’ZRP_TEST’;

OWNER TABLE_NAME TABLESPACE_NAME
——— ————- —————
STUDY ZRP_TEST MYTBS1

SQL> drop table study.zrp_test purge; –模拟误drop表
Table dropped.

下面需要借助TSPITR恢复MYTBS1这个表空间到2010-06-28 07:23:29这个时间点,从而找回我要的表ZRP_TEST,下面我的恢复过程,我就执行了一条简单的命令

[oracle@odd] /oracle> rman target/

Recovery Manager: Release 10.2.0.2.0 – Production on Mon Jun 28 07:25:31 2010

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

connected to target database: TEST (DBID=2020047879)

RMAN> RECOVER TABLESPACE mytbs1 UNTIL scn 874247 AUXILIARY DESTINATION ‘/oracle/oradata/aux’;

下面就是系统自动输出的了,我什么也不用管了.

Starting recover at 28-JUN-10
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=160 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=’Bdwc’

initialization parameters used for automatic instance:
db_name=TEST
compatible=10.2.0
db_block_size=8192
db_files=200
db_unique_name=tspitr_TEST_Bdwc
large_pool_size=1M
shared_pool_size=110M
#No auxiliary parameter file used
db_create_file_dest=/oracle/oradata/aux
control_files=/oracle/oradata/aux/cntrl_tspitr_TEST_Bdwc.f

starting up automatic instance TEST

Oracle instance started

Total System Global Area 201326592 bytes

Fixed Size 1259936 bytes
Variable Size 146802272 bytes
Database Buffers 50331648 bytes
Redo Buffers 2932736 bytes
Automatic instance created

contents of Memory Script:
{
# set the until clause
set until scn 874247;
# 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 28-JUN-10
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 /oracle/oradata/backup/test/cf_c-2020047879-20100628-01
channel ORA_AUX_DISK_1: restored backup piece 1
piece handle=/oracle/oradata/backup/test/cf_c-2020047879-20100628-01 tag=TAG20100628T050316
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:02
output filename=/oracle/oradata/aux/cntrl_tspitr_TEST_Bdwc.f
Finished restore at 28-JUN-10

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 scn 874247;
plsql <<<– tspitr_2 declare sqlstatement varchar2(512); offline_not_needed exception; pragma exception_init(offline_not_needed, -01539); begin sqlstatement := ‘alter tablespace ‘|| ‘MYTBS1′ ||’ offline for recover’; krmicd.writeMsg(6162, sqlstatement); krmicd.execSql(sqlstatement); exception when offline_not_needed then null; 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 an omf destination tempfile
set newname for clone tempfile 2 to new;
# set a destination filename for restore
set newname for datafile 5 to
“/oracle/oradata/test/mytbs1.dbf”;
# rename all tempfiles
switch clone tempfile all;
# restore the tablespaces in the recovery set plus the auxilliary tablespaces
restore clone datafile 1, 2, 5;
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 5 online”;
# make the controlfile point at the restored datafiles, then recover them
recover clone database tablespace “MYTBS1”, “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 MYTBS1 offline for recover

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 /oracle/oradata/aux/TSPITR_TEST_BDWC/datafile/o1_mf_temp_%u_.tmp in control file
renamed temporary file 2 to /oracle/oradata/aux/TSPITR_TEST_BDWC/datafile/o1_mf_temp02_%u_.tmp in control file

Starting restore at 28-JUN-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 /oracle/oradata/aux/TSPITR_TEST_BDWC/datafile/o1_mf_system_%u_.dbf
restoring datafile 00002 to /oracle/oradata/aux/TSPITR_TEST_BDWC/datafile/o1_mf_undotbs1_%u_.dbf
restoring datafile 00005 to /oracle/oradata/test/mytbs1.dbf
channel ORA_AUX_DISK_1: reading from backup piece /oracle/oradata/backup/test/TEST_2hlh9mtr_1_1
channel ORA_AUX_DISK_1: restored backup piece 1
piece handle=/oracle/oradata/backup/test/TEST_2hlh9mtr_1_1 tag=TAG20100627T141418
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:46
Finished restore at 28-JUN-10

datafile 1 switched to datafile copy
input datafile copy recid=28 stamp=722849208 filename=/oracle/oradata/aux/TSPITR_TEST_BDWC/datafile/o1_mf_system_62hqj9q0_.dbf
datafile 2 switched to datafile copy
input datafile copy recid=29 stamp=722849208 filename=/oracle/oradata/aux/TSPITR_TEST_BDWC/datafile/o1_mf_undotbs1_62hqj9rv_.dbf

sql statement: alter database datafile 1 online

sql statement: alter database datafile 2 online

sql statement: alter database datafile 5 online

Starting recover at 28-JUN-10
using channel ORA_AUX_DISK_1

starting media recovery

archive log thread 1 sequence 44 is already on disk as file /oracle/oradata/arch/test/1_44_721919543.dbf
archive log thread 1 sequence 45 is already on disk as file /oracle/oradata/arch/test/1_45_721919543.dbf
archive log thread 1 sequence 46 is already on disk as file /oracle/oradata/arch/test/1_46_721919543.dbf
archive log thread 1 sequence 47 is already on disk as file /oracle/oradata/arch/test/1_47_721919543.dbf
archive log thread 1 sequence 1 is already on disk as file /oracle/oradata/arch/test/1_1_722787765.dbf
archive log thread 1 sequence 1 is already on disk as file /oracle/oradata/arch/test/1_1_722840583.dbf
archive log filename=/oracle/oradata/arch/test/1_44_721919543.dbf thread=1 sequence=44
archive log filename=/oracle/oradata/arch/test/1_45_721919543.dbf thread=1 sequence=45
archive log filename=/oracle/oradata/arch/test/1_46_721919543.dbf thread=1 sequence=46
archive log filename=/oracle/oradata/arch/test/1_47_721919543.dbf thread=1 sequence=47
archive log filename=/oracle/oradata/arch/test/1_1_722787765.dbf thread=1 sequence=1
archive log filename=/oracle/oradata/arch/test/1_1_722840583.dbf thread=1 sequence=1
media recovery complete, elapsed time: 00:00:06
Finished recover at 28-JUN-10

database opened

contents of Memory Script:
{
# export the tablespaces in the recovery set
host ‘exp userid =”/@(DESCRIPTION=(ADDRESS=(PROTOCOL=beq)(PROGRAM=/oracle/product/10.2.0/db_1/bin/oracle)(ARGV0=oracleBdwc)(ARGS=^'(DESCRIPTION=(LOCAL=YES)
(ADDRESS=(PROTOCOL=beq)))^’)(ENVS=^’ORACLE_SID=Bdwc^’))(CONNECT_DATA=(SID=Bdwc))) as sysdba” point_in_time_recover=y tablespaces=
MYTBS1 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 MYTBS1 online”;
sql “alter tablespace MYTBS1 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.2.0 – Production on Mon Jun 28 07:27:27 2010

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

Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.2.0 – Production
With the Partitioning, OLAP and Data Mining options
Export done in US7ASCII character set and AL16UTF16 NCHAR character set
server uses ZHS16GBK character set (possible charset conversion)
Note: table data (rows) will not be exported

About to export Tablespace Point-in-time Recovery objects…
For tablespace MYTBS1 …
. exporting cluster definitions
. exporting table definitions
. . exporting table ZRP_TEST
. 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.2.0 – Production on Mon Jun 28 07:27:42 2010

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

Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.2.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 ZHS16GBK character set (possible charset conversion)
. importing SYS’s objects into SYS
. importing STUDY’s objects into STUDY
. . importing table “ZRP_TEST”
. importing SYS’s objects into SYS
Import terminated successfully without warnings.
host command complete

sql statement: alter tablespace MYTBS1 online

sql statement: alter tablespace MYTBS1 offline

sql statement: begin dbms_backup_restore.AutoBackupFlag(TRUE); end;

Removing automatic instance
Automatic instance removed
auxiliary instance file /oracle/oradata/aux/cntrl_tspitr_TEST_Bdwc.f deleted
auxiliary instance file /oracle/oradata/aux/TSPITR_TEST_BDWC/datafile/o1_mf_system_62hqj9q0_.dbf deleted
auxiliary instance file /oracle/oradata/aux/TSPITR_TEST_BDWC/datafile/o1_mf_undotbs1_62hqj9rv_.dbf deleted
auxiliary instance file /oracle/oradata/aux/TSPITR_TEST_BDWC/datafile/o1_mf_temp_62hqlp7s_.tmp deleted
auxiliary instance file /oracle/oradata/aux/TSPITR_TEST_BDWC/datafile/o1_mf_temp02_62hqlpgc_.tmp deleted
auxiliary instance file /oracle/oradata/aux/TSPITR_TEST_BDWC/onlinelog/o1_mf_1_62hql0h1_.log deleted
auxiliary instance file /oracle/oradata/aux/TSPITR_TEST_BDWC/onlinelog/o1_mf_2_62hql3gw_.log deleted
auxiliary instance file /oracle/oradata/aux/TSPITR_TEST_BDWC/onlinelog/o1_mf_3_62hql6jb_.log deleted
auxiliary instance file /oracle/oradata/aux/TSPITR_TEST_BDWC/onlinelog/o1_mf_4_62hqlblo_.log deleted
auxiliary instance file /oracle/oradata/aux/TSPITR_TEST_BDWC/onlinelog/o1_mf_5_62hqlf6f_.log deleted
Finished recover at 28-JUN-10

RMAN>
到这里,表空间MYTBS1已经被恢复了!

我剩下所要做的就是:
SQL> alter tablespace mytbs1 online;
Tablespace altered.

然后去验证一下我的表是不是被找回来了:
SQL> select count(*) from study.zrp_test;

COUNT(*)
———-
9509
SQL> select owner,table_name,tablespace_name from dba_tables where table_name=’ZRP_TEST’;
OWNER TABLE_NAME TABLESPACE_NAME
——- ————- —————
STUDY ZRP_TEST MYTBS1

SQL>
是不是很爽?

发表评论

电子邮件地址不会被公开。 必填项已用*标注

此站点使用Akismet来减少垃圾评论。了解我们如何处理您的评论数据

Go