ORACLE通过redo来保证数据的重演,从而使得在数据库故障的时候,可以恢复,在数据库中redo文件至关重要。?????????
1.对于联机日志损坏要根据日志状态进行分析,联机日志一般分为Current Active Inactive和unused四种状态,unused日志组还没有使用。Inactive状态的日志不会造成数据的丢失,而Active和Current状态的日志一般会造成数据的丢失。
1)模拟错误,Inactive的日志损坏
SYS@ENMOEDU > select GROUP#,ARCHIVED,STATUS from v$log;
GROUP# ARC STATUS
---------- --- ----------------
1 YES INACTIVE
2 NO CURRENT
3 YES INACTIVE
[oracle@ENMOEDU ENMOEDU]$ ls
control01.ctl he.dbf redo01.log redo02.log redo03.log redo04.log sample01.dbf system01.dbf undotbs01.dbf
control03.ct redo01b.log redo02b.log redo03b.log redo04b.log sysaux01.dbf temp01.dbf undotbs02.dbf users01.dbf
[oracle@ENMOEDU ENMOEDU]$ rm -rf redo01.log
[oracle@ENMOEDU ENMOEDU]$ rm -rf redo01b.log
SYS@ENMOEDU > shutdown immedaite;
SP2-0717: illegal SHUTDOWN option
SYS@ENMOEDU > shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SYS@ENMOEDU > startup
ORACLE instance started.
Total System Global Area 422670336 bytes
Fixed Size 1345380 bytes
Variable Size 343935132 bytes
Database Buffers 71303168 bytes
Redo Buffers 6086656 bytes
Database mounted.
ORA-03113: end-of-file on communication channel
Process ID: 3777
Session ID: 125 Serial number: 5
2)查看alter日志
Tue Feb 25 22:42:59 2014
ARC3 started with pid=24, OS id=5077
ARC1: Becoming the 'no FAL' ARCH
ARC1: Becoming the 'no SRL' ARCH
ARC2: Becoming the heartbeat ARCH
Errors in file /u01/app/oracle/diag/rdbms/enmoedu/ENMOEDU/trace/ENMOEDU_lgwr_5036.trc:
ORA-00313: open failed for members of log group 1 of thread 1
ORA-00312: online log 1 thread 1: '/u01/app/oracle/oradata/ENMOEDU/redo01b.log'
ORA-27037: unable to obtain file status
Linux Error: 2: No such file or directory
Additional information: 3
ORA-00312: online log 1 thread 1: '/u01/app/oracle/oradata/ENMOEDU/redo01.log'
ORA-27037: unable to obtain file status
Linux Error: 2: No such file or directory
Additional information: 3
Errors in file /u01/app/oracle/diag/rdbms/enmoedu/ENMOEDU/trace/ENMOEDU_lgwr_5036.trc:
ORA-00313: open failed for members of log group 1 of thread 1
ORA-00312: online log 1 thread 1: '/u01/app/oracle/oradata/ENMOEDU/redo01b.log'
ORA-27037: unable to obtain file status
Linux Error: 2: No such file or directory
Additional information: 3
ORA-00312: online log 1 thread 1: '/u01/app/oracle/oradata/ENMOEDU/redo01.log'
ORA-27037: unable to obtain file status
Linux Error: 2: No such file or directory
Additional information: 3
Errors in file /u01/app/oracle/diag/rdbms/enmoedu/ENMOEDU/trace/ENMOEDU_ora_5069.trc:
ORA-00313: open failed for members of log group 1 of thread
ORA-00312: online log 1 thread 1: '/u01/app/oracle/oradata/ENMOEDU/redo01.log'
ORA-00312: online log 1 thread 1: '/u01/app/oracle/oradata/ENMOEDU/redo01b.log'
USER (ospid: 5069): terminating the instance due to error 313
System state dump requested by (instance=1, osid=5069), summary=[abnormal instance termination].
System State dumped to trace file /u01/app/oracle/diag/rdbms/enmoedu/ENMOEDU/trace/ENMOEDU_diag_5026.trc
Dumping diagnostic data in directory=[cdmp_20140225224300], requested by (instance=1, osid=5069), summary=[abnormal instance termination].
Instance terminated by USER, pid = 5069
3)由于日志组1已经被归档,状态是Inactive,这个日志内容在instance racovery时不需要,这种日志可以简单的清空或者删除都可以。
在mount状态下,清空日志组,在打开数据库就行。
[oracle@ENMOEDU ~]$ sqlplus / as sysdba
SQL*Plus: Release 11.2.0.3.0 Production on Tue Feb 25 22:45:26 2014
Copyright (c) 1982, 2011, Oracle. All rights reserved.
Connected to an idle instance.
SYS@ENMOEDU > startup mount
ORACLE instance started.
Total System Global Area 422670336 bytes
Fixed Size 1345380 bytes
Variable Size 339740828 bytes
Database Buffers 75497472 bytes
Redo Buffers 6086656 bytes
Database mounted.
SYS@ENMOEDU > alter database clear logfile group 1;
Database altered.
SYS@ENMOEDU > alter database open;
Database altered.
也可以把损坏的日志组,直接删除,再重建(如果当前一共两个日志组,一定要先建一个日志组,再删除)。
SYS@ENMOEDU > alter database drop logfile group 1;
Database altered.
SYS@ENMOEDU > alter database add logfile group 3('/u01/app/oracle/oradata/ENMOEDU/redo03.log','/u01/app/oracle/oradata/ENMOEDU/redo03b.log') size 50M;
Database altered.
2.Active状态说明这个日志记录的修改的数据还没有记录到数据文件,Current是数据库当前正在使用的日志。但是如果数据库中还有未决的事务,需要使用这些日志进行恢复,这是就会造成数据的丢失,所进行的就是不完全恢复。
1)模拟错误,删除CURRENT状态下的日志组
SYS@ENMOEDU > select GROUP#,ARCHIVED,STATUS from v$log;
GROUP# ARC STATUS
---------- --- ----------------
1 YES INACTIVE
2 NO CURRENT
4 YES INACTIVE
[oracle@ENMOEDU ENMOEDU]$ ls
control01.ctl he.dbf redo01.log redo02.log redo03.log redo04.log sample01.dbf system01.dbf undotbs01.dbf users011.dbf.bak
control03.ct redo01b.log redo02b.log redo03b.log redo04b.log rman01.dbf sysaux01.dbf temp01.dbf undotbs02.dbf users01.dbf
[oracle@ENMOEDU ENMOEDU]$ rm -rf redo02b.log
[oracle@ENMOEDU ENMOEDU]$ rm -rf redo02.log
SYS@ENMOEDU > shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SYS@ENMOEDU > startup
ORACLE instance started.
Total System Global Area 422670336 bytes
Fixed Size 1345380 bytes
Variable Size 339740828 bytes
Database Buffers 75497472 bytes
Redo Buffers 6086656 bytes
Database mounted.
ORA-03113: end-of-file on communication channel
Process ID: 5714
Session ID: 125 Serial number: 5
2)查看Alter日志
Tue Feb 25 23:01:51 2014
ARC3 started with pid=24, OS id=5722
ARC1: Archival started
ARC2: Archival started
ARC2: Becoming the 'no FAL' ARCH
ARC2: Becoming the 'no SRL' ARCH
ARC1: Becoming the heartbeat ARCH
Errors in file /u01/app/oracle/diag/rdbms/enmoedu/ENMOEDU/trace/ENMOEDU_lgwr_5682.trc:
ORA-00313: open failed for members of log group 2 of thread 1
ORA-00312: online log 2 thread 1: '/u01/app/oracle/oradata/ENMOEDU/redo02b.log'
ORA-27037: unable to obtain file status
Linux Error: 2: No such file or directory
Additional information: 3
ORA-00312: online log 2 thread 1: '/u01/app/oracle/oradata/ENMOEDU/redo02.log'
ORA-27037: unable to obtain file status
Linux Error: 2: No such file or directory
Additional information: 3
Errors in file /u01/app/oracle/diag/rdbms/enmoedu/ENMOEDU/trace/ENMOEDU_lgwr_5682.trc:
ORA-00313: open failed for members of log group 2 of thread 1
ORA-00312: online log 2 thread 1: '/u01/app/oracle/oradata/ENMOEDU/redo02b.log'
ORA-27037: unable to obtain file status
Linux Error: 2: No such file or directory
Additional information: 3
ORA-00312: online log 2 thread 1: '/u01/app/oracle/oradata/ENMOEDU/redo02.log'
ORA-27037: unable to obtain file status
Linux Error: 2: No such file or directory
Additional information: 3
Errors in file /u01/app/oracle/diag/rdbms/enmoedu/ENMOEDU/trace/ENMOEDU_ora_5714.trc:
ORA-00313: open failed for members of log group 1 of thread
ORA-00312: online log 2 thread 1: '/u01/app/oracle/oradata/ENMOEDU/redo02.log'
ORA-00312: online log 2 thread 1: '/u01/app/oracle/oradata/ENMOEDU/redo02b.log'
USER (ospid: 5714): terminating the instance due to error 313
System state dump requested by (instance=1, osid=5714), summary=[abnormal instance termination].
System State dumped to trace file /u01/app/oracle/diag/rdbms/enmoedu/ENMOEDU/trace/ENMOEDU_diag_5672.trc
Dumping diagnostic data in directory=[cdmp_20140225230152], requested by (instance=1, osid=5714), summary=[abnormal instance termination].
Instance terminated by USER, pid = 5714
3)数据库进行恢复
[oracle@ENMOEDU ~]$ sqlplus / as sysdba
SQL*Plus: Release 11.2.0.3.0 Production on Tue Feb 25 23:04:30 2014
Copyright (c) 1982, 2011, Oracle. All rights reserved.
Connected to an idle instance.
SYS@ENMOEDU > startup mount
ORACLE instance started.
Total System Global Area 422670336 bytes
Fixed Size 1345380 bytes
Variable Size 339740828 bytes
Database Buffers 75497472 bytes
Redo Buffers 6086656 bytes
Database mounted.
4)执行alter database open resetlogs;会看到有报错,需要进行不完全恢复
SYS@ENMOEDU > alter database open resetlogs;
alter database open resetlogs
*
ERROR at line 1:
ORA-01139: RESETLOGS option only valid after an incomplete database recovery
5)对数据库进行不完全恢复,以resetlogs的方式打开数据库
SYS@ENMOEDU > recover database until cancel;
Media recovery complete.
SYS@ENMOEDU > alter database open resetlogs;
Database altered.
总结:如果是current状态的文件丢失,实例在mount状态下,执行‘alter database open resetlogs’命令;如果需要,进行一次完全恢复,启动数据库。如果是inacrtive状态的日志损坏,把该日志组drop和clear都可以。
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/29435844/viewspace-1090316/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/29435844/viewspace-1090316/