Oracle在线redolog文件丢失后的恢复
今天一个开发库启动不了了,发过来报错一看是日志文件损坏了(见下图),接着说了一下前因后果。说是年前服务器掉电了,然后就再没有启动起来过。今天有人用才想到要处理。
青河ssl适用于网站、小程序/APP、API接口等需要进行数据传输应用场景,ssl证书未来市场广阔!成为成都创新互联公司的ssl证书销售渠道,可以享受市场价格4-6折优惠!如果有意向欢迎电话联系或者加微信:13518219792(备注:SSL证书合作)期待与您的合作!
先说一下大体的思路,如果损坏的redo log是INACTIVE状态的,也就是实例崩溃恢复用不到的redo log,那处理起来比较容易,直接alter database clear logfile group #;或alter database clear unarchived logfile group #;重建日志组就行了。建议重建日志文件级后对数据库做一个全库备份,特别是强制clear后,造成的归档日志文件断层。在如果损坏的redo log是ACTIVE或CURRENT状态的,也就是实例崩溃恢复需要用到的redo log,那处理起来就比较麻烦了,损坏这种redo log就意味着丢失数据。
redo log的三种状态:
INACTIVE:日志对应的修改已经被写入硬盘
ACTIVE:日志对应的修改还没有被写入硬盘
CURRENT:实例正在使用的日志文件
由于这个开发库有种种的问题,恢复起来遇到了各种情况,这里用一个虚拟机上的数据库演示如果CURRENT或ACTIVE状态的日志文件损坏的情况下如何恢复。
1、构造场景
删除一张表的数据但不提交,然后在另一个会话中把数据库shutdown abort。再删除所有的redo log文件。
#session 1 sys@ORCL>delete from zx; 2858 rows deleted. #session 2 sys@ORCL>select group#,status from v$log; GROUP# STATUS ---------- ------------------------------------------------ 1 INACTIVE 2 ACTIVE 3 CURRENT sys@ORCL>shutdown abort; ORACLE instance shut down. #删除redo log文件 [oracle@rhel6 ~]$ cd /u02/app/oracle/oradata/orcl/ [oracle@rhel6 orcl]$ ls -l total 1944992 -rw-r----- 1 oracle oinstall 9748480 Feb 24 23:56 control01.ctl -rw-r----- 1 oracle oinstall 9748480 Feb 24 23:56 control02.ctl -rw-r----- 1 oracle oinstall 328343552 Feb 24 23:54 example01.dbf -rw-r----- 1 oracle oinstall 52429312 Feb 24 23:54 redo01.log -rw-r----- 1 oracle oinstall 52429312 Feb 24 23:55 redo02.log -rw-r----- 1 oracle oinstall 52429312 Feb 24 23:55 redo03.log -rw-r----- 1 oracle oinstall 545267712 Feb 24 23:54 sysaux01.dbf -rw-r----- 1 oracle oinstall 796925952 Feb 24 23:54 system01.dbf -rw-r----- 1 oracle oinstall 30416896 Feb 24 13:58 temp01.dbf -rw-r----- 1 oracle oinstall 110108672 Feb 24 23:54 undotbs01.dbf -rw-r----- 1 oracle oinstall 5251072 Feb 24 23:54 users01.dbf [oracle@rhel6 orcl]$ rm redo*log l[oracle@rhel6 orcl]$ ls -l total 1791212 -rw-r----- 1 oracle oinstall 9748480 Feb 24 23:56 control01.ctl -rw-r----- 1 oracle oinstall 9748480 Feb 24 23:56 control02.ctl -rw-r----- 1 oracle oinstall 328343552 Feb 24 23:54 example01.dbf -rw-r----- 1 oracle oinstall 545267712 Feb 24 23:54 sysaux01.dbf -rw-r----- 1 oracle oinstall 796925952 Feb 24 23:54 system01.dbf -rw-r----- 1 oracle oinstall 30416896 Feb 24 13:58 temp01.dbf -rw-r----- 1 oracle oinstall 110108672 Feb 24 23:54 undotbs01.dbf -rw-r----- 1 oracle oinstall 5251072 Feb 24 23:54 users01.dbf
2、启动数据库出现报错
idle>startup ORACLE instance started. Total System Global Area 1603411968 bytes Fixed Size 2253664 bytes Variable Size 1476398240 bytes Database Buffers 117440512 bytes Redo Buffers 7319552 bytes Database mounted. ORA-00313: open failed for members of log group 2 of thread 1 ORA-00312: online log 2 thread 1: '/u02/app/oracle/oradata/orcl/redo02.log' ORA-27037: unable to obtain file status Linux-x86_64 Error: 2: No such file or directory Additional information: 3
3、尝试使用clear方式重建日志组出现报错
idle>alter database clear logfile group 2; alter database clear logfile group 2 * ERROR at line 1: ORA-01624: log 2 needed for crash recovery of instance orcl (thread 1) ORA-00312: online log 2 thread 1: '/u02/app/oracle/oradata/orcl/redo02.log' idle>alter database clear unarchived logfile group 2; alter database clear unarchived logfile group 2 * ERROR at line 1: ORA-01624: log 2 needed for crash recovery of instance orcl (thread 1) ORA-00312: online log 2 thread 1: '/u02/app/oracle/oradata/orcl/redo02.log'
从报错信息中可以看出log 2是实例崩溃恢复所需要的日志文件,不能直接重建。
4、这种情况下使用隐含参数_allow_resetlogs_corruption,创建pfile,把*._allow_resetlogs_corruption=TRUE加入到pfile中。然后mount数据库,强制不完全恢复,再open resetlogs
idle>create pfile='/home/oracle/initorcl.ora' from spfile; File created. [oracle@rhel6 orcl]$ vi /home/oracle/initorcl.ora idle>shutdown immediate; ORA-01109: database not open Database dismounted. ORACLE instance shut down. idle>startup pfile='/home/oracle/initorcl.ora' mount; ORACLE instance started. Total System Global Area 1603411968 bytes Fixed Size 2253664 bytes Variable Size 1476398240 bytes Database Buffers 117440512 bytes Redo Buffers 7319552 bytes Database mounted. idle>show parameter _allow_ NAME TYPE VALUE ------------------------------------ --------------------------------- ------------------------------ _allow_resetlogs_corruption boolean TRUE idle>recover database until cancel; ORA-00279: change 1023441 generated at 02/24/2017 23:54:54 needed for thread 1 ORA-00289: suggestion : /u02/app/oracle/product/11.2.4/db1/dbs/arch2_2_936817668.dbf ORA-00280: change 1023441 for thread 1 is in sequence #2 Specify log: {=suggested | filename | AUTO | CANCEL} cancel ORA-01547: warning: RECOVER succeeded but OPEN RESETLOGS would get error below ORA-01194: file 1 needs more recovery to be consistent ORA-01110: data file 1: '/u02/app/oracle/oradata/orcl/system01.dbf' ORA-01112: media recovery not started idle>alter database open resetlogs; Database altered. idle>select open_mode from v$database; OPEN_MODE ------------------------------------------------------------ READ WRITE
可以看到现在数据库已经被open了。
5、再次查看第一步中被删除的数据的表,数据仍然存在说明丢失CURRENT或ACTIVE状态的日志文件会导致数据丢失。
idle>select count(*) from zx; COUNT(*) ---------- 2858
以上是在虚拟机上做测试的恢复过程,但是对于前面说到的开发库的恢复就没有这个过程简单了。可以说是解决了一个报错又出来新的报错。
在使用_allow_resetlogs_corruption参数执行不完全恢复,open resetlogs 时,遇到了ORA-01248
SQL> alter database open resetlogs; alter database open resetlogs * ERROR at line 1: ORA-01248: file 5 was created in the future of incomplete recovery
于是先把这个文件offline drop
SQL> alter database datafile 5 offline drop;
再次open resetlogs时又遇到了ORA-00704和ORA-01555
SQL> alter database open resetlogs; alter database open resetlogs * ERROR at line 1: ORA-01092: ORACLE instance terminated. Disconnection forced ORA-00704: bootstrap process failure ORA-00704: bootstrap process failure ORA-00604: error occurred at recursive SQL level 1 ORA-01555: snapshot too old: rollback segment number 5 with name "_SYSSMU5_4116806824$" too small Process ID: 3396 Session ID: 573 Serial number: 51
由于现在的水平有限,在网上查资料也没有能解决这一系列的问题,最后没办法只能重建库,重新导数据了。
如果哪位遇到了类似的问题,而且解决了,也请分享一下经验。
其实上午在模拟这个问题的时候,在open resetlogs时还遇到了一个经典的报错ORA-600 [2662],这个错误可以参考eygle的博客http://www.eygle.com/archives/2005/12/oracle_diagnostics_howto_deal_2662_error.html
参考:http://iquicksandi.blog.163.com/blog/static/13228526220107642655204/
http://www.linuxidc.com/Linux/2012-02/53426.htm
http://www.killdb.com/2014/06/19/%E6%95%B0%E6%8D%AE%E5%BA%93open%E6%8A%A5%E9%94%99ora-01555-snapshot-too-old.html
http://www.askmaclean.com/archives/%E3%80%90oracle%E6%81%A2%E5%A4%8D%E3%80%91ora-704.html
网页题目:Oracle在线redolog文件丢失后的恢复
本文URL:http://scyanting.com/article/jeoohc.html