Thomas Zhang的杂货铺
22 06, 2008
使用RMAN实现TSPITR(Tablespace Point-in-time Recovery)
作者 tomszrp 22:18 | Permalink 静态链接网址 | Comments 最新回复 (0) | Trackback 引用 (0) | 解决方案

摘要

TSPITR是大家都比较熟悉的一项功能了，我却从来没有在实际的生产系统中用过她，不成想，今天在我自己身上碰到了.

　

今天很郁闷，一不小心把几个原来toms_tools和toms_dbms工具包使用的字典表全部给干掉了。而且很干净，直接purge了。其实也没什么特别重要的数据，不过重新配置一边实在是很不爽的一件事。于是想到了用TSPITR技术来恢复我的这些表(都在同一个表空间data_01上)，我这个开发库也只有我自己，恢复到我删除前的那个点，也没有什么数据丢失之类的麻烦.闲着也是闲着，穷折腾呗。

周五的时候，铿锵的分公司下面有人不小心把一个表给drop掉了(9i)，我当时还嘀咕：怎么这么不小心。没想到我一个通宵下来，也会这么晕，哈哈！

最后铿锵采用DUL找回了数据，我就不玩DUL了，来个TSPITR吧。下面的过程，是我成功找会数据后的再现过程

　

环境说明

   我的Oracle环境是windows xp + oracle 10.2.0.2
   db_name:ora10g
   
   我以toms_tools_def字典表的恢复为例说明。
   SQL> select * from toms_tools_def;
   
   REGION NAME       RAC_SID      RAC_NAME     STORAGE_SCALE
   ------ ---------- ------------ ------------ -------------
      300 全省       0            AB                   0.001
      530 菏泽       1            A                    0.500
      531 济南       2            B                    1.000
      532 青岛       1            B                    1.300
      533 淄博       1            A                    0.700
      534 德州       2            A                    0.400
      535 烟台       1            A                    1.000
      536 潍坊       2            A                    0.850
      537 济宁       2            A                    0.800
      538 泰安       1            A                    0.400
      539 临沂       2            B                    1.000
      543 滨州       1            B                    0.400
      546 东营       2            A                    0.400
      631 威海       2            B                    0.500
      632 枣庄       1            A                    0.400
      633 日照       2            B                    0.300
      634 莱芜       1            B                    0.100
      635 聊城       1            B                    0.500  

   SQL> alter session set nls_date_format='yyyy-mm-dd hh24:mi:ss';  
   Session altered.
      
   在2008-06-22 17:52:30前后,我不小心drop掉了这个表。
   SQL> drop table toms_tools_def purge;   
   Table dropped
   
   SQL> 
   SQL> select sysdate from dual;  
   SYSDATE
   -------------------
   2008-06-22 17:52:47
   
   SQL>

下面我借助RMAN将表空间data_01恢复到2008-06-22 17:50:00这个时间点。这样我就自然找回了我的那张表



恢复准备工作

为辅助数据库准备参数文件并建立相应的文件目录

源库参数

*.background_dump_dest='c:oracle10gadminora10gbdump'
*.core_dump_dest='c:oracle10gadminora10gcdump'
*.user_dump_dest='c:oracle10gadminora10gudump'

*.control_files='C:oracle10goradataora10gCONTROL01.CTL',

'C:oracle10goradataora10gCONTROL02.CTL','C:oracle10goradataora10gCONTROL03.CTL'

*.db_name='ora10g'
*.instance_name='ora10g'
*.service_names='ora10g'

辅助库参数

*.background_dump_dest='c:oracle10gadmintomsbdump'
*.core_dump_dest='c:oracle10gadmintomscdump'
*.user_dump_dest='c:oracle10gadmintomsudump'

*.control_files='C:oracle10goradatatomsCONTROL01.CTL',

'C:oracle10goradatatomsCONTROL02.CTL','C:oracle10goradatatomsCONTROL03.CTL'

*.db_name='ora10g'
*.instance_name='toms'
*.service_names='toms'
*.db_file_name_convert=("C:oracle10goradataora10g", "C:oracle10goradatatoms")
*.log_file_name_convert=("C:oracle10goradataora10g", "C:oracle10goradatatoms")
*.db_unique_name=toms

注：1)最后三个参数是为辅助库新加的。

2)同时创建上面列出的不存在的目录（略）

创建辅助实例并启动到nomout阶段

C:>oradim -new -sid toms -intpwd sysadmin -startmode manual -pfile C:oracle10gdatabaseinittoms.ora
Instance created.

C:>set oracle_sid=toms
C:>sqlplus "/as sysdba"
SQL*Plus: Release 10.2.0.2.0 - Production on Sun Jun 22 17:53:54 2008
Copyright (c) 1982, 2005, Oracle. All Rights Reserved.

Connected to an idle instance.

SQL> startup nomount;
ORACLE instance started.

Total System Global Area 104857600 bytes
Fixed Size 1288028 bytes
Variable Size 83888292 bytes
Database Buffers 16777216 bytes
Redo Buffers 2904064 bytes
SQL> exit
Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.2.0 - Production
With the Partitioning, OLAP and Data Mining options

　

恢复阶段
C:>rman target sys/admin@ora10g auxiliary /

Recovery Manager: Release 10.2.0.2.0 - Production on Sun Jun 22 17:54:04 2008
Copyright (c) 1982, 2005, Oracle. All rights reserved.

connected to target database: ORA10G (DBID=3964185094)
connected to auxiliary database: ORA10G (not mounted)

RMAN> run{ 2> allocate auxiliary channel a_c_1 type disk; 3> allocate channel c_1 type disk; 4> recover tablespace data_01 until time "to_date('2008-06-22 17:50:00', 'yyyy-mm-dd hh24:mi:ss')"; 5> }

using target database control file instead of recovery catalog
allocated channel: a_c_1
channel a_c_1: sid=47 devtype=DISK

allocated channel: c_1
channel c_1: sid=30 devtype=DISK

Starting recover at 22-JUN-08
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 UNDO1

contents of Memory Script:
{
# set the until clause
set until time "to_date('2008-06-22 17:50:00', 'yyyy-mm-dd hh24:mi:ss')";
# 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 22-JUN-08

channel a_c_1: starting datafile backupset restore
channel a_c_1: restoring control file
channel a_c_1: reading from backup piece C:FLASH_RECOVERY_AREAORA10GBACKUPSET
2008_06_22O1_MF_NCSNF_TAG20080622T174510_45W7WODC_.BKP
channel a_c_1: restored backup piece 1
piece handle=C:FLASH_RECOVERY_AREAORA10GBACKUPSET2008_06_22O1_MF_NCSNF_TAG2
0080622T174510_45W7WODC_.BKP tag=TAG20080622T174510
channel a_c_1: restore complete, elapsed time: 00:00:03
output filename=C:ORACLE10GORADATATOMSCONTROL01.CTL
output filename=C:ORACLE10GORADATATOMSCONTROL02.CTL
output filename=C:ORACLE10GORADATATOMSCONTROL03.CTL
Finished restore at 22-JUN-08

sql statement: alter database mount clone database

sql statement: alter system archive log current

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

contents of Memory Script:
{
# generated tablespace point-in-time recovery script
# set the until clause
set until time "to_date('2008-06-22 17:50:00', 'yyyy-mm-dd hh24:mi:ss')";
plsql <<<-- tspitr_2
declare
sqlstatement varchar2(512);
offline_not_needed exception;
pragma exception_init(offline_not_needed, -01539);
begin
sqlstatement := 'alter tablespace '|| 'DATA_01' ||' offline for recover';
krmicd.writeMsg(6162, sqlstatement);
krmicd.execSql(sqlstatement);
exception
when offline_not_needed then
null;
end; >>>;
# set a destination filename for restore
set newname for datafile 1 to
"C:ORACLE10GORADATATOMSSYSTEM.DBF";
# set a destination filename for restore
set newname for datafile 2 to
"C:ORACLE10GORADATATOMSUNDOTBS01.DBF";
# set a destination tempfile
set newname for tempfile 1 to
"C:ORACLE10GORADATATOMSTEMP01.DBF";
# set a destination filename for restore
set newname for datafile 5 to
"C:ORACLE10GORADATAORA10GDATA_01.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 "DATA_01", "SYSTEM", "UNDO1" delete archivelo
g;
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 DATA_01 offline for recover

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME


Starting restore at 22-JUN-08

channel a_c_1: starting datafile backupset restore
channel a_c_1: specifying datafile(s) to restore from backup set
restoring datafile 00001 to C:ORACLE10GORADATATOMSSYSTEM.DBF
restoring datafile 00002 to C:ORACLE10GORADATATOMSUNDOTBS01.DBF
restoring datafile 00005 to C:ORACLE10GORADATAORA10GDATA_01.DBF
channel a_c_1: reading from backup piece C:FLASH_RECOVERY_AREAORA10GBACKUPSET
2008_06_22O1_MF_NNNDF_TAG20080622T174510_45W7SBJX_.BKP
channel a_c_1: restored backup piece 1
piece handle=C:FLASH_RECOVERY_AREAORA10GBACKUPSET2008_06_22O1_MF_NNNDF_TAG2
0080622T174510_45W7SBJX_.BKP tag=TAG20080622T174510
channel a_c_1: restore complete, elapsed time: 00:01:25
Finished restore at 22-JUN-08

datafile 5 switched to datafile copy
input datafile copy recid=36 stamp=658086992 filename=C:ORACLE10GORADATAORA10
GDATA_01.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 22-JUN-08

starting media recovery

archive log thread 1 sequence 236 is already on disk as file C:FLASH_RECOVERY_A
REAORA10GARCHIVELOG2008_06_22O1_MF_1_236_45W8C9PG_.ARC
archive log filename=C:FLASH_RECOVERY_AREAORA10GARCHIVELOG2008_06_22O1_MF_1
_236_45W8C9PG_.ARC thread=1 sequence=236
media recovery complete, elapsed time: 00:00:03
Finished recover at 22-JUN-08

database opened

contents of Memory Script:
{
# export the tablespaces in the recovery set
host 'exp userid ="/@ as sysdba" point_in_time_recover=y tablespaces=
DATA_01 file=
tspitr_a.dmp';
# shutdown clone before import
shutdown clone immediate
# import the tablespaces in the recovery set
host 'imp userid ="sys/admin@ora10g as sysdba" point_in_time_recover=y file=
tspitr_a.dmp';
# online/offline the tablespace imported
sql "alter tablespace DATA_01 online";
sql "alter tablespace DATA_01 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 Sun Jun 22 17:57:19 2008

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


Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.2.0 - Produc
tion
With the Partitioning, OLAP and Data Mining options
Export done in ZHS16GBK 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 DATA_01 ...
. exporting cluster definitions
. exporting table definitions
. . exporting table TOMS_SOURCE_SQL
. . exporting table TOMS_TAB_COLUMNS
. . exporting table TOMS_REGION_DEF
. . exporting table TOMS_SQL
. . exporting table TOMS_TABLES
. . exporting table SERVER_INFO
. . exporting table GSM_RESOURCE
. . exporting table TOMS_TOOLS_DEF
. 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 Sun Jun 22 17:58:00 2008

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


Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.2.0 - Produc
tion
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 ZHS16GBK character set and AL16UTF16 NCHAR character set
. importing SYS's objects into SYS
. importing STUDY's objects into STUDY
. . importing table "TOMS_SOURCE_SQL"
. . importing table "TOMS_TAB_COLUMNS"
. . importing table "TOMS_REGION_DEF"
. . importing table "TOMS_SQL"
. . importing table "TOMS_TABLES"
. . importing table "SERVER_INFO"
. . importing table "GSM_RESOURCE"
. . importing table "TOMS_TOOLS_DEF"
. importing SYS's objects into SYS
Import terminated successfully without warnings.
host command complete

sql statement: alter tablespace DATA_01 online

sql statement: alter tablespace DATA_01 offline

sql statement: begin dbms_backup_restore.AutoBackupFlag(TRUE); end;
auxiliary instance file C:ORACLE10GORADATATOMSCONTROL01.CTL deleted
auxiliary instance file C:ORACLE10GORADATATOMSCONTROL02.CTL deleted
auxiliary instance file C:ORACLE10GORADATATOMSCONTROL03.CTL deleted
auxiliary instance file C:ORACLE10GORADATATOMSSYSTEM.DBF deleted
auxiliary instance file C:ORACLE10GORADATATOMSUNDOTBS01.DBF deleted
auxiliary instance file C:ORACLE10GORADATATOMSTEMP01.DBF deleted
auxiliary instance file C:ORACLE10GORADATATOMSREDO01.LOG deleted
auxiliary instance file C:ORACLE10GORADATATOMSREDO02.LOG deleted
auxiliary instance file C:ORACLE10GORADATATOMSREDO03.LOG deleted
Finished recover at 22-JUN-08
released channel: c_1

RMAN>

　

验证阶段

返回到源数据库，将表空间data_01 online

SQL> alter tablespace data_01 online;
Tablespace altered.

SQL> select * from toms_tools_def;

REGION NAME RAC_SID RAC_NAME STORAGE_SCALE
------ ---------- ------------ ------------ -------------
300 全省 0 AB 0.001
530 菏泽 1 A 0.500
531 济南 2 B 1.000
532 青岛 1 B 1.300
533 淄博 1 A 0.700
534 德州 2 A 0.400
535 烟台 1 A 1.000
536 潍坊 2 A 0.850
537 济宁 2 A 0.800
538 泰安 1 A 0.400
539 临沂 2 B 1.000
543 滨州 1 B 0.400
546 东营 2 A 0.400
631 威海 2 B 0.500
632 枣庄 1 A 0.400
633 日照 2 B 0.300
634 莱芜 1 B 0.100
635 聊城 1 B 0.500

好了，表找回来了。

　

结束阶段

删除toms辅助实例及之前创建的参数文件、密码文件和相关目录


C:>oradim -delete -sid toms
Instance deleted.

C:>

。。。

OK，问题搞定了。继续折腾其他的了。。。
Comments
博客日历
« 八月 2008 »
一 二 三 四 五 六 日
        1 2 3
4 5 6 7 8 9 10
11 12 13 14 15 16 17
18 19 20 21 22 23 24
25 26 27 28 29 30 31
搜索
最新发表
文章分类
文章归档
网站链接
新闻聚合
©2004 TOTO.net    Powered by pLog Designed by toto