oracle修复探索ORACLE不完全恢复之--基于cancel的恢复 第二篇
浏览量: 次 发布日期:2023-08-11 21:27:18
基于cancel 的不一致性恢复(归档丢失) 第二篇
主要适用于:基于Cancel的不完全恢复适用场景:Recover时,所需的某个归档日志损坏,或主机断电,current状态的联机日志损坏。
创建测试表
创建wwl002表,切换日志,再创建新的wwl003表,主机断电,删除当前日志,模拟文件损坏。
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: {
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'
恢复之后,使用Resetlogs选项,仍无法打开数据库,提示数据文件不一致,System表空间需要进一步的恢复。
当前日志损坏时,不能基于当前的数据库做不完全恢复。只能用以前的备份,做一个基于Cancel的不完全恢复。
重建下控制文件:
SQL> CREATE CONTROLFILE REUSE DATABASE"WWL" 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.
兆柏数据恢复公司
检查_allow_resetlogs_corruption隐含参数是否关闭,确定已经关闭
SQL> show parameter_allow_resetlogs_corruption
NAME TYPE VALUE
----------------------------------------------- ------------------------------
_allow_resetlogs_corruption boolean FALSE
SQL>
因为重建了控制文件,默认是没有制定temp表空间,这里制定下:
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
-----------------------oracle修复------- -----------------
WWL001 TABLE
WWL002 TABLE
可以看到表WWL002存在,表WWL003不存在。因为WWL003的创建,是保存在当前REDO日志中的,而当前REDO日志损坏,所有当前日志中保存的操作全部丢失了。
归档日志、或者REDO日志损失,数据库就只能恢复到丢失的日志之前了。
oracle修复
重要数据丢失请联系兆柏数据恢复公司
. oracle数据库归档日志路径,Oracle数据库归档日志路径详解
. oracle删除后怎么恢复,Oracle数据库删除后恢复指南
. 移动硬盘无法识别修复,移动硬盘无法识别?教你几招轻松修复
. WIN11系统不能安装oracle,oracle11g下载官网
. oracle数据库delete的数据怎么恢复,Oracle数据库中误删数据的恢复方法
. oracle不付费可以用吗,Oracle不付费可以使用吗?全面解析Oracle产品的
. 移动硬盘不识别修复要多少钱,移动硬盘不识别修复费用解析——全面了解修复成本
. oracle数据库要钱吗,Oracle数据库的使用费用解析
. oracle执行存储过程执行时间,Oracle存储过程执行时间分析及优化策略
. 移动硬盘读不出 修复,移动硬盘读不出怎么办?全面解析修复方法
. 电脑硬盘维修费用,如分区表出错或数据丢失,修复费用较低,通常在200至500元之间