本文参考了尛样儿的这篇文章的整体思路,在自己的理解上添加了自己试验的脚本。
大家可以点击http://blog.itpub.net/23135684/viewspace-626935/查看原帖
这次演示的环境在线日志:3组每组1个成员,以后再试验多成员的。
一.说明:
1.以下所说的当前日志指日志状态为CURRENT,ACTIVE,非当前日志指日志状态为INACTIVE
2.不用考虑归档和非归档模式,2种模式下的Redo丢失情况一样。
二.丢失Redo的4种情况:
第一种情况:非当前日志,正常关闭。
第二种情况:非当前日志,非正常关闭。
第三种情况:当前日志,正常关闭。
第四种情况:当前日志,非正常关闭。
三.处理方法:
第一、二种情况的处理方法一样,直接把日志文件clear即可。
[oracle@xuexi1 ~]$ sqlplus / as sysdba
SQL*Plus: Release 11.2.0.4.0 Production on Thu Jul 3 16:08:12 2014
Copyright (c) 1982, 2013, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL>
SQL>
SQL> select group#,thread#,status,archived from v$log;
GROUP# THREAD# STATUS ARC
---------- ---------- ---------------- ---
1 1 INACTIVE NO
2 1 INACTIVE NO
3 1 CURRENT NO
SQL> col member for a50
SQL> select * from v$logfile;
GROUP# STATUS TYPE MEMBER IS_
---------- ------- ------- -------------------------------------------------- ---
3 ONLINE /home/oracle/app/oradata/SBDB/redo03.log NO
2 ONLINE /home/oracle/app/oradata/SBDB/redo02.log NO
1 ONLINE /home/oracle/app/oradata/SBDB/redo01.log NO
SQL>
SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL>
上面是我要进行测试的环境,里面有3组日志每组一个成员文件,当前日志组是:组3,我就删除组1和组2的文件了。我是管了数据库才删除文件的。
然后我启动数据库,发现有错误,查看相关日志,发现在线日志丢失。
Additional information: 3
Errors in file /home/oracle/app/diag/rdbms/sbdb/SBDB/trace/SBDB_ora_15544.trc:
ORA-00313: open failed for members of log group 1 of thread
ORA-00312: online log 1 thread 1: '/home/oracle/app/oradata/SBDB/redo01.log'
Thu Jul 03 16:15:41 2014
Errors in file /home/oracle/app/diag/rdbms/sbdb/SBDB/trace/SBDB_m000_15546.trc:
ORA-00313: open failed for members of log group 1 of thread 1
ORA-00312: online log 1 thread 1: '/home/oracle/app/oradata/SBDB/redo01.log'
ORA-27037: unable to obtain file status
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3
System state dump requested by (instance=1, osid=15544), summary=[abnormal instance termination].
System State dumped to trace file /home/oracle/app/diag/rdbms/sbdb/SBDB/trace/SBDB_diag_15486_20140703161542.trc
USER (ospid: 15544): terminating the instance due to error 313
Dumping diagnostic data in directory=[cdmp_20140703161542], requested by (instance=1, osid=15544), summary=[abnormal instance termination].
Instance terminated by USER, pid = 15544
[root@xuexi1 trace]#
[root@xuexi1 trace]#
[root@xuexi1 trace]# pwd
/home/oracle/app/diag/rdbms/sbdb/SBDB/trace
[root@xuexi1 trace]#
查看相关日志,大家可以看到提示,是少了。
SQL> conn / as sysdba
Connected to an idle instance.
SQL> shutdown abort
ORACLE instance shut down.
SQL>
SQL> startup mount
ORACLE instance started.
Total System Global Area 730714112 bytes
Fixed Size 2256832 bytes
Variable Size 478150720 bytes
Database Buffers 243269632 bytes
Redo Buffers 7036928 bytes
Database mounted.
SQL> select group#,thread#,status,archived from v$log;
GROUP# THREAD# STATUS ARC
---------- ---------- ---------------- ---
1 1 INACTIVE NO
3 1 CURRENT NO
2 1 INACTIVE NO
SQL> alter database clear logfile group 1;
Database altered.
SQL> alter database clear logfile group 2;
Database altered.
SQL> alter database open;
Database altered.
SQL>
这里我直接进入数据库,起到mount状态,然后运行alter database clear logfile group *;把2个日志文件重新建,这个时候,大家去OS里面看看,那2个文件重新创建了。数据库也启动了。
第三种情况的处理办法
[oracle@xuexi1 ~]$ sqlplus / as sysdba
SQL*Plus: Release 11.2.0.4.0 Production on Thu Jul 3 17:13:51 2014
Copyright (c) 1982, 2013, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL> set linesize 1000
SQL> col member for a60
SQL> select * from v$logfile;
GROUP# STATUS TYPE MEMBER IS_
---------- ------- ------- ------------------------------------------------------------ ---
3 ONLINE /home/oracle/app/oradata/SBDB/redo03.log NO
2 ONLINE /home/oracle/app/oradata/SBDB/redo02.log NO
1 ONLINE /home/oracle/app/oradata/SBDB/redo01.log NO
SQL>
SQL> select group#,thread#,status,archived from v$log;
GROUP# THREAD# STATUS ARC
---------- ---------- ---------------- ---
1 1 INACTIVE NO
2 1 INACTIVE NO
3 1 CURRENT NO
SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL>
我把数据库正常关闭后,去删除3号对应的文件去了。
SQL> startup
ORACLE instance started.
Total System Global Area 730714112 bytes
Fixed Size 2256832 bytes
Variable Size 478150720 bytes
Database Buffers 243269632 bytes
Redo Buffers 7036928 bytes
Database mounted.
ORA-03113: end-of-file on communication channel
Process ID: 15322
Session ID: 125 Serial number: 5
SQL> shutdown immediate
ORA-24324: service handle not initialized
ORA-01041: internal error. hostdef extension doesn't exist
SQL>
SQL> conn / as sysdba
Connected to an idle instance.
SQL> startup mount;
ORACLE instance started.
Total System Global Area 730714112 bytes
Fixed Size 2256832 bytes
Variable Size 478150720 bytes
Database Buffers 243269632 bytes
Redo Buffers 7036928 bytes
Database mounted.
SQL> recover database until cancel
Media recovery complete.
SQL>
SQL> alter database open resetlogs;
Database altered.
SQL>
这样就启动了。因为是正常关闭,online redo log里面的东西就没用了,可以都删除了,就用resetlog属性了。
第四种情况的处理方法:
1.通过备份来还原、恢复数据。
2.通过修改参数文件中的参数
_allow_resetlogs_corruption=TRUE
SQL> shutdown abort
ORACLE instance shut down.
SQL>
SQL> startup
ORACLE instance started.
Total System Global Area 730714112 bytes
Fixed Size 2256832 bytes
Variable Size 478150720 bytes
Database Buffers 243269632 bytes
Redo Buffers 7036928 bytes
Database mounted.
ORA-00313: open failed for members of log group 1 of thread 1
ORA-00312: online log 1 thread 1: '/home/oracle/app/oradata/SBDB/redo01.log'
ORA-27037: unable to obtain file status
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3
SQL> conn / as sysdba
Connected.
SQL> select group#,thread#,status,archived from v$log;
GROUP# THREAD# STATUS ARC
---------- ---------- ---------------- ---
1 1 CURRENT NO
3 1 INACTIVE NO
2 1 UNUSED NO
SQL>
SQL> select file#,checkpoint_change#,fuzzy from v$datafile_header;
FILE# CHECKPOINT_CHANGE# FUZ
---------- ------------------ ---
1 964567 YES
2 964567 YES
3 964567 YES
4 964567 YES
SQL> alter database clear logfile group 1;
alter database clear logfile group 1
*
ERROR at line 1:
ORA-01624: log 1 needed for crash recovery of instance SBDB (thread 1)
ORA-00312: online log 1 thread 1: '/home/oracle/app/oradata/SBDB/redo01.log'
SQL> recover database until cancel;
ORA-00279: change 964567 generated at 07/03/2014 16:32:54 needed for thread 1
ORA-00289: suggestion : /home/oracle/app/product/11.1.0/db_1/dbs/arch1_4_831201553.dbf
ORA-00280: change 964567 for thread 1 is in sequence #4
Specify log: {<RET>=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: '/home/oracle/app/oradata/SBDB/system01.dbf'
ORA-01112: media recovery not started
SQL> alter database open resetlogs;
alter database open resetlogs
*
ERROR at line 1:
ORA-01194: file 1 needs more recovery to be consistent
ORA-01110: data file 1: '/home/oracle/app/oradata/SBDB/system01.dbf'
上面的操作,就是模拟数据库异常关闭,然后启动后,我们尝试用前面的方法去恢复数据库,所有的方法都不成啊。于是使用在pfile添加_allow_resetlogs_corruption=TRUE来解决了。
第一步:创建pfile文件
SQL> startup nomount
ORACLE instance started.
Total System Global Area 730714112 bytes
Fixed Size 2256832 bytes
Variable Size 478150720 bytes
Database Buffers 243269632 bytes
Redo Buffers 7036928 bytes
SQL> create pfile from spfile;
File created.
SQL> shutdown immediate
ORA-01507: database not mounted
ORACLE instance shut down.
SQL>
最后把数据库停了吧,一会还要启动到nomount状态呢,直接修改pfile添加上面的属性后
SQL> startup pfile=/home/oracle/app/product/11.1.0/db_1/dbs/initSBDB.ora
ORACLE instance started.
Total System Global Area 730714112 bytes
Fixed Size 2256832 bytes
Variable Size 478150720 bytes
Database Buffers 243269632 bytes
Redo Buffers 7036928 bytes
Database mounted.
ORA-01589: must use RESETLOGS or NORESETLOGS option for database open
SQL> alter database open resetlogs;
Database altered.
SQL>
这样的话,数据库就拉起来了。但是这是不安全的,"虽然能够启动数据库到open状态,但是启动后的数据库数据字典、数据有可能导致不一致的情况出现,故需要在open下把整个数据库export,然后删除库,重建,再将export的数据import到新的数据库中。"引原帖。
四.验证数据库是否正常关闭的方法
这个暂时没有弄,大家可以看原帖
五.结论:
非正常关闭的当前日志丢失,可能导致数据库启动后的混乱,并可能造成少量数据的丢失。其他情况不会导致数据的丢失。
版权所有,文章允许转载,但必须以链接方式注明源地址,否则追究法律责任!