探索oracle不完全恢复之--基于cancel的恢复 第二篇
基于cancel 的不一致性恢复（归档丢失） 第二篇
创建测试表
sql> conn wwl/wwl
connected.
sql> select * from tab;
tname tabtype clusterid
------------------------------ -----------------
wwl001 table
sql> create table wwl002 as select *from wwl001;
table created.
sql> conn / as sysdba
connected.
切换日志
sql> alter system switch logfile;
system altered.
后再创建第二张表
sql> conn wwl/wwl
connected.
sql> create table wwl003 as select *from wwl001;
table created.
查看当前日志组，，确定当前活动的日志组，是组4
sql> conn / as sysdba
connected.
sql> set line 200
sql> select * from v$log;
group# thread# sequence# bytes members arc status first_change# first_tim
---------- ---------- ---------- -------------------- --- ---------------- ------------- ---------
4 1 2 134217728 2 yes active 1716929 30-jul-12
5 1 1 134217728 2 yes inactive 1692728 27-jul-12
6 1 3 134217728 2 no current 1720396 30-jul-12
7 1 0 134217728 2 yes unused 0
定位当前日志组的日志文件，有两个。
sql> col member format a30
sql> select * from v$logfile;
group# status type member is_
---------- ------- ------------------------------------- ---
7 online /dbbak2/oradata/wwl/redo7a.log no
7 online /dbbak2/oradata/wwl/redo7b.log no
6 online /dbbak2/oradata/wwl/redo6a.log no
6 online /dbbak2/oradata/wwl/redo6b.log no
5 stale online /dbbak2/oradata/wwl/redo5a.log no
5 stale online /dbbak2/oradata/wwl/redo5b.log no
4 online /dbbak2/oradata/wwl/redo4a.log no
4 online /dbbak2/oradata/wwl/redo4b.log no
8 rows selected.
删除当前日志组文件，模拟在线事务丢失：
sql> !rm -f/dbbak2/oradata/wwl/redo4a.log
sql> !rm -f/dbbak2/oradata/wwl/redo4b.log
模拟服务器断电
sql> shutdown abort;
oracle instance shut down.
恢复步骤:
1、尝试启动数据库的时候报当前日志丢失。
sql> startup
oracle instance started.
total system global area 100663296 bytes
fixed size 1217884 bytes
variable size 88083108 bytes
database buffers 8388608 bytes
redo buffers 2973696 bytes
database mounted.
ora-00313: open failedfor members of log group 4 of thread 1
ora-00312: online log 4thread 1: '/dbbak2/oradata/wwl/redo4b.log'
ora-27037: unable toobtain file status
linux error: 2: no suchfile or directory
additional information: 3
ora-00312: online log 4thread 1: '/dbbak2/oradata/wwl/redo4a.log'
ora-27037: unable toobtain file status
linux error: 2: no suchfile or directory
additional information: 3
2、尝试clear redo4
sql> alter database clear logfile group4;
alter database clear logfile group 4
*
error at line 1:
ora-01624: log 4 needed for crash recoveryof instance wwl (thread 1)
ora-00312: online log 4 thread 1:'/dbbak2/oradata/wwl/redo4a.log'
ora-00312: online log 4 thread 1:'/dbbak2/oradata/wwl/redo4b.log'
3、在当前库做基于cancel的不完全恢复
sql> recover database until cancel;
ora-00279: change 1716930 generated at07/30/2012 11:03:51 needed for thread 1
ora-00289: suggestion :/dbsoft/product/10.2.0/db_1/dbs/arch1_2_789761098.dbf
ora-00280: change 1716930 for thread 1 isin sequence #2
specify log: {=suggested |filename | auto | cancel}
auto
ora-00279: change 1720396 generated at07/30/2012 13:37:21 needed for thread 1
ora-00289: suggestion :/dbsoft/product/10.2.0/db_1/dbs/arch1_3_789761098.dbf
ora-00280: change 1720396 for thread 1 isin sequence #3
ora-00278: log file'/dbsoft/product/10.2.0/db_1/dbs/arch1_2_789761098.dbf' no longer needed forthis recovery
ora-00308: cannot open archived log'/dbsoft/product/10.2.0/db_1/dbs/arch1_3_789761098.dbf'
ora-27037: unable to obtain file status
linux error: 2: no such file or directory
additional information: 3
ora-01547: warning: recover succeeded butopen resetlogs would get error below
ora-01194: file 1 needs more recovery to beconsistent
ora-01110: data file 1:'/dbbak2/oradata/wwl/system01.dbf'
做完恢复之后必须使用resetlogs选项打开数据库：
sql> alter database open resetlogs;
alter database open resetlogs
*
error at line 1:
ora-01194: file 1 needs more recovery to beconsistent
ora-01110: data file 1:'/dbbak2/oradata/wwl/system01.dbf'
重建下控制文件：
sql> create controlfile reuse databasewwl resetlogs archivelog
maxlogfiles 16
maxlogmembers 3
'/dbbak2/oradata/wwl/redo4a.log',
maxdatafiles 100
maxinstances 8
group 5 (
maxloghistory 292
logfile
group 4 (
'/dbbak2/oradata/wwl/redo4a.log',
'/dbbak2/oradata/wwl/redo4b.log'
group 6 (
)size 128m,
group 5 (
'/dbbak2/oradata/wwl/redo5a.log',
'/dbbak2/oradata/wwl/redo5b.log'
'/dbbak2/oradata/wwl/redo7b.log'
)size 128m,
group 6 (
'/dbbak2/oradata/wwl/redo6a.log',
'/dbbak2/oradata/wwl/redo6b.log'
)size 128m,
group 7 (
'/dbbak2/oradata/wwl/redo7a.log',
'/dbbak2/oradata/wwl/redo7b.log'
)size 128m
-- standby logfile
datafile
'/dbbak2/oradata/wwl/system01.dbf',
'/dbbak2/oradata/wwl/undotbs01.dbf',
'/dbbak2/oradata/wwl/sysaux01.dbf',
'/dbbak2/oradata/wwl/users01.dbf',
'/dbbak2/oradata/wwl/wwl001',
'/dbbak2/oradata/wwl/wwl002',
'/dbbak2/oradata/wwl/wwl003'
character set zhs16cgb231280
34 ;
control file created.
再次打开，结果还是不行
sql> alter database open resetlogs;
alter database open resetlogs
*
error at line 1:
ora-01194: file 1 needs more recovery to beconsistent
ora-01110: data file 1:'/dbbak2/oradata/wwl/system01.dbf'
可以尝试使用_allow_resetlogs_corruption隐含参数来打开数据库
sql> alter system set_allow_resetlogs_corruption=true scope=spfile;
system altered.
修改完参数之后重启数据库到mount状态
sql> shutdown immediate
ora-01109: database not open
database dismounted.
oracle instance shut down.
sql> startup;
oracle instance started.
total system global area 100663296 bytes
fixed size 1217884 bytes
variable size 88083108 bytes
database buffers 8388608 bytes
redo buffers 2973696 bytes
database mounted.
ora-01589: must use resetlogs ornoresetlogs option for database open
同样以resetlogs模式启动数据库
sql> alter database open resetlogs;
database altered.
一定记得关闭该参数
sql> alter system set_allow_resetlogs_corruption=false scope=spfile;
system altered.
让参数关闭生效，再次启动数据库
sql> startup force;
oracle instance started.
total system global area 100663296 bytes
fixed size 1217884 bytes
variable size 88083108 bytes
database buffers 8388608 bytes
redo buffers 2973696 bytes
database mounted.
database opened.
sql> show parameter_allow_resetlogs_corruption
name type value
----------------------------------------------- ------------------------------
_allow_resetlogs_corruption boolean false
sql>
sql> alter tablespace temp01 addtempfile '/dbbak2/oradata/wwl/temp01.dbf' reuse;
tablespace altered.
检查数据
sql> conn wwl/wwl
connected.
sql> select * from tab;
tname tabtype clusterid
------------------------------ -----------------
wwl001 table
wwl002 table
相关阅读：
探索oracle之rman_01概念
探索oracle之rman_02基本使用
探索oracle之rman_03非一致性备份
探索oracle之rman_04非一致性备份
探索oracle之rman_05增量备份
探索oracle之rman_06备份策略
探索oracle之rman_07单个数据文件丢失恢复
探索oracle之rman_07整个业务表空间丢失恢复
探索oracle之rman_07 磁盘损坏数据丢失恢复
探索oracle之rman_07 数据库所有文件全部丢失恢复
探索oracle之rman_07 重做日志redu文件丢失恢复
探索oracle之rman_07 参数文件丢失恢复
探索oracle之rman_07控制文件丢失恢复
探索oracle之rman_07 system表空间丢失恢复