技术开发 频道
IT168首页 > 技术开发 > 技术开发技术 > 正文

如何用RMAN恢复一个DROP/TRUNCATE/DML误操作的表

作者：IT168 杨宝秋 编辑： 晓熊 2007-09-20 00:00
分享

3.4 恢复一：以表空间级完成表数据的恢复

    适用于表空间不大的状况
3.4.1 建立AUXILIARY 实例
3.4.1.1 建立AUXILIARY 的pfile
    假定实例名为aux1，在$ORACLE_HOME/dbs 下拷备主库的pfile 生成initaux1.ora，变动如下条目：
变动background_dump_dest，core_dump_dest，user_dump_dest，log_archive_dest_N 参数，并在操作系统建立相应的目录。

    建立控制文件及要恢复的数据文件、日志文件的存放目录
设定LOCK_NAME_SPACE 参数，可以为任意值，如’aux1’，如果你的AUXILIARY 实例与主库同一台主机，这个参数必须设置

添加/变动SERVICE_NAMES 参数为aux1
修订INSTANCE_NAME 参数为aux1
不需要变动DB_NAME 参数：
注释掉log_archive_start 参数

3.4.1.2 建立aux1 的口令文件 
$cd $ORACLE_HOME/dbs
$orapwd file=orapwaux1 password=change_on_install entries=3
3.4.2 启动AUXILIARY 到nomount 状态
$ echo $ORACLE_SID
aux1
$ sqlplus '/ as sysdba'
SQL*Plus: Release 9.2.0.6.0 -Production on Fri Jun 15 13:23:05 2007
Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.
Connected to an idle instance.
SQL> startup nomount;
ORACLE instance started.
Total System Global Area 236423584 bytes
Fixed Size 731552 bytes
Variable Size 167772160 bytes
Database Buffers 67108864 bytes
Redo Buffers 811008 bytes
SQL>
3.4.3 aux1 上启动Rman 执行如下脚本完成恢复
3.4.3.1 脚本
$ echo $ORACLE_SID
aux1
$ rman target / catalog rman/rman@prod
Recovery Manager: Release 9.2.0.6.0 - 64bit Production
Copyright (c) 1995, 2002, Oracle Corporation. All rights reserved.
connected to target database: orcl (not mounted)
connected to recovery catalog database
RMAN> run
2> {
3> allocate channel c1 type disk;
4> allocate channel c2 type disk;
5>
6> set until scn 111391;
7>
8> restore controlfile;
9>
10> sql 'alter database mount clone database';
11>
12> set newname for datafile 1 to '/yang/oradata/aux1/system01.dbf';
13> set newname for datafile 2 to '/yang/oradata/aux1/undotbs01.dbf';
14> set newname for datafile 6 to '/yang/oradata/aux1/users01.dbf';
15> set newname for datafile 7 to '/yang/oradata/aux1/users02.dbf';
16>
17> restore tablespace system,undotbs1,users;
18> switch datafile all;
19>
20> sql 'alter database datafile 1,2,6,7 online';
21> recover database skip forever tablespace TEMP,EXAMPLE,INDX,TOOLS;
22>
23> release channel c1;
24> release channel c2;
25> }
3.4.3.2 Rman 日志
allocated channel: c1
channel c1: sid=11 devtype=DISK
allocated channel: c2
channel c2: sid=12 devtype=DISK
executing command: SET until clause
Starting restore at 15-JUN-07
channel c1: starting datafile backupset restore
channel c1: restoring controlfile
output filename=/yang/oradata/aux1/control01.ctl
channel c1: restored backup piece 1
piece handle=/yang/backup/db_incr0_625323865_31_1 tag=TAG20070615T130424 params=NULL
channel c1: restore complete
replicating controlfile
input filename=/yang/oradata/aux1/control01.ctl
Finished restore at 15-JUN-07
sql statement: alter database mount clone database
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
Starting restore at 15-JUN-07
channel c1: starting datafile backupset restore
channel c1: specifying datafile(s) to restore from backup set
restoring datafile 00006 to /yang/oradata/aux1/users01.dbf
channel c2: starting datafile backupset restore
channel c2: specifying datafile(s) to restore from backup set
restoring datafile 00002 to /yang/oradata/aux1/undotbs01.dbf
restoring datafile 00007 to /yang/oradata/aux1/users02.dbf
channel c1: restored backup piece 1
piece handle=/yang/backup/db_incr0_625323864_29_1 tag=TAG20070615T130424 params=NULL
channel c1: restore complete
channel c1: starting datafile backupset restore
channel c1: specifying datafile(s) to restore from backup set
restoring datafile 00001 to /yang/oradata/aux1/system01.dbf
channel c1: restored backup piece 1
piece handle=/yang/backup/db_incr0_625323865_31_1 tag=TAG20070615T130424 params=NULL
channel c1: restore complete
channel c2: restored backup piece 1
piece handle=/yang/backup/db_incr0_625323865_30_1 tag=TAG20070615T130424 params=NULL
channel c2: restore complete
Finished restore at 15-JUN-07
datafile 1 switched to datafile copy
input datafilecopy recid=6 stamp=625325256 filename=/yang/oradata/aux1/system01.dbf
datafile 2 switched to datafile copy
input datafilecopy recid=7 stamp=625325256 filename=/yang/oradata/aux1/undotbs01.dbf
datafile 6 switched to datafile copy
input datafilecopy recid=8 stamp=625325256 filename=/yang/oradata/aux1/users01.dbf
datafile 7 switched to datafile copy
input datafilecopy recid=9 stamp=625325256 filename=/yang/oradata/aux1/users02.dbf
sql statement: alter database datafile 1,2,6,7 online
Starting recover at 15-JUN-07
starting media recovery
channel c1: starting archive log restore to default destination
channel c2: starting archive log restore to default destination
channel c1: restoring archive log
archive log thread=1 sequence=22
channel c2: restoring archive log
archive log thread=1 sequence=23
channel c1: restored backup piece 1
piece handle=/yang/backup/arch_625323891_34_1 tag=TAG20070615T130450 params=NULL
channel c1: restore complete
archive log filename=/yang/arch1/arch1_22.arc thread=1 sequence=22
channel c2: restored backup piece 1
piece handle=/yang/backup/arch_625323893_35_1 tag=TAG20070615T130450 params=NULL
channel c2: restore complete
archive log filename=/yang/arch1/arch1_23.arc thread=1 sequence=23
channel c1: starting archive log restore to default destination
channel c1: restoring archive log
archive log thread=1 sequence=24
channel c1: restoring archive log
archive log thread=1 sequence=25
channel c1: restored backup piece 1
piece handle=/yang/backup/arch_625324785_36_1 tag=TAG20070615T131945 params=NULL
channel c1: restore complete
archive log filename=/yang/arch1/arch1_24.arc thread=1 sequence=24
archive log filename=/yang/arch1/arch1_25.arc thread=1 sequence=25
media recovery complete
Finished recover at 15-JUN-07
released channel: c1
released channel: c2
RMAN>

3.4.3.3 脚本说明
    设定了SET UNTIL SCN 111391: 使USERS 表空间恢复到HR.T1 表数据存在的状态。
mount clone database：出于安全方面的考虑，使所有的数据文件都OFFLINE 。
Set newname：使Rman 恢复数据文件到aux1 的相应目录，其是与switch 配合使用的。
Switch datafile all: 相当于mount 状态下的alter database rename file … to ..; 即变更控制文件中相应数据文件的路径为set newname 设定的路径。 
Recover database skip forever tablespace: 即跳过这些不需要恢复的表空间加快恢复的速度，forever 选项在做不完全恢时是必须的，相当于Rman 在恢复时对相应表空间的数据文件在offline 的同时加了drop 选项。
3.4.4 以RESETLOGS 选项打开AUXILIARY 库
3.4.4.1 变更controlfile 中的online redolog 的路径，resetlogs 打开时这些日志将会被创立 

SQL> select status from v$instance;
STATUS
------------ 
MOUNTED
SQL> alter database rename file '/yang/oradata/orcl/redo01.log' to '/yang/oradata/aux1/redo01.log';
Database altered.
SQL> alter database rename file '/yang/oradata/orcl/redo02.log' to '/yang/oradata/aux1/redo02.log';
Database altered.
SQL> alter database rename file '/yang/oradata/orcl/redo03.log' to '/yang/oradata/aux1/redo03.log';
Database altered.
SQL>
3.4.4.2 打开数据库
SQL> alter database open resetlogs;
Database altered.
SQL> select count(*) from hr.T1;
COUNT(*)
---------- 
20
SQL>

可见，HR.T1 这张表已经恢复成功。

0
12345查看全文
第1页：问题的提出第2页：恢复的流程第3页：HR 用户下建一张表T1 模拟问题第4页：以表空间级完成表数据的恢复第5页：exp 出HR.T1 这张表并在主库完成导入
相关文章
    盛拓传媒简介 关于IT168 广告服务 使用条款 投稿指南 联系我们
    北京盛拓优讯信息技术有限公司. 版权所有 中华人民共和国增值电信业务经营许可证 编号：京B2-20170206 北京市公安局海淀分局网监中心备案编号：11010802020118
    广播电视节目制作经营许可证:(京) 字第25315号 信息系统安全等级保护备案：11010813655-00001 京公网安备11010802020118号
    违法和不良信息举报电话 :010-58859066,18101376593,shengtuo20@it168.com