oralce 的redo 日志包括online 的与非在线日志， 非在线日志损坏已在本blog 里说明了，相对简单，现在就online 日志的损坏进行了，试验
create 一个test 的表
SQL> create table test as select * from dba_objects;
Table created.
SQL> insert into test select * from test;
74510 rows created.
SQL> /
149020 rows created.
SQL> /
298040 rows created.
SQL> commit;
Commit complete.
查看一下日志情况，redo3 为在线日志
SQL> select * from v$log;
GROUP# THREAD# SEQUENCE# BYTES BLOCKSIZE MEMBERS ARCHIVED
---------- ---------- ---------- ---------- ---------- ---------- ---------
STATUS FIRST_CHANGE# FIRST_TIME
------------------------------------------------ ------------- ---------------
NEXT_CHANGE# NEXT_TIME
------------ ---------------
1 1 1 52428800 512 1 NO
INACTIVE 995548 03-DEC-11
998757 03-DEC-11
2 1 2 52428800 512 1 NO
ACTIVE 998757 03-DEC-11
1006077 03-DEC-11
GROUP# THREAD# SEQUENCE# BYTES BLOCKSIZE MEMBERS ARCHIVED
---------- ---------- ---------- ---------- ---------- ---------- ---------
STATUS FIRST_CHANGE# FIRST_TIME
------------------------------------------------ ------------- ---------------
NEXT_CHANGE# NEXT_TIME
------------ ---------------
3 1 3 52428800 512 1 NO
CURRENT 1006077 03-DEC-11
2.8147E+14
删除在线日志redo3.log
rm -rf redo3.log
SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
数据库不能正常打开
SQL> startup;
ORACLE instance started.
Total System Global Area 296456192 bytes
Fixed Size 2227784 bytes
Variable Size 109052344 bytes
Database Buffers 180355072 bytes
Redo Buffers 4820992 bytes
Database mounted.
ORA-03113: end-of-file on communication channel
Process ID: 8956
Session ID: 1 Serial number: 5
-----查看alter 日志
SQL> host
oracle@rac1:~> cd /opt/oracle/diag/rdbms/mos5200/mos5200/trace/
oracle@rac1:/opt/oracle/diag/rdbms/mos5200/mos5200/trace> tail -400 alert_mos5200.log
Sat Dec 03 03:20:14 2011
Starting ORACLE instance (normal)
****************** Large Pages Information *****************
Total Shared Global Region in Large Pages = 0 KB (0%)
Large Pages used by this instance: 0 (0 KB)
Large Pages unused system wide = 0 (0 KB) (alloc incr 4096 KB)
Large Pages configured system wide = 0 (0 KB)
Large Page size = 2048 KB
RECOMMENDATION:
Total Shared Global Region size is 286 MB. For optimal performance,
prior to the next instance restart increase the number
of unused Large Pages by atleast 143 2048 KB Large Pages (286 MB)
system wide to get 100% of the Shared
Global Region allocated with Large pages
***********************************************************
LICENSE_MAX_SESSION = 0
LICENSE_SESSIONS_WARNING = 0
Picked latch-free SCN scheme 3
Using LOG_ARCHIVE_DEST_1 parameter default value as /opt/oracle/product/11g/db/dbs/arch
Autotune of undo retention is turned on.
IMODE=BR
ILAT =27
LICENSE_MAX_USERS = 0
SYS auditing is disabled
Starting up:
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options.
ORACLE_HOME = /opt/oracle/product/11g/db
System name: Linux
Node name: rac1
Release: 2.6.16.60-0.21-default
Version: #1 Tue May 6 12:41:02 UTC 2008
Machine: x86_64
VM name: VMWare Version: 6
Using parameter settings in server-side spfile /opt/oracle/product/11g/db/dbs/spfilemos5200.ora
System parameters with non-default values:
processes = 150
sga_target = 284M
control_files = "/opt/oracle/oradata/mos5200/control01.ctl"
control_files = "/opt/oracle/oradata/mos5200/control02.ctl"
db_block_size = 8192
compatible = "11.2.0.0.0"
undo_tablespace = "UNDOTBS1"
remote_login_passwordfile= "EXCLUSIVE"
db_domain = ""
dispatchers = "(PROTOCOL=TCP) (SERVICE=mos5200XDB)"
audit_file_dest = "/opt/oracle/admin/mos5200/adump"
audit_trail = "DB"
db_name = "mos5200"
open_cursors = 300
pga_aggregate_target = 108M
diagnostic_dest = "/opt/oracle"
Sat Dec 03 03:20:16 2011
PMON started with pid=2, OS id=8844
Sat Dec 03 03:20:16 2011
PSP0 started with pid=3, OS id=8846
Sat Dec 03 03:20:17 2011
VKTM started with pid=4, OS id=8848 at elevated priority
VKTM running at (4)millisec precision with DBRM quantum (100)ms
Sat Dec 03 03:20:17 2011
GEN0 started with pid=5, OS id=8852
Sat Dec 03 03:20:17 2011
DIAG started with pid=6, OS id=8854
Sat Dec 03 03:20:17 2011
DBRM started with pid=7, OS id=8856
Sat Dec 03 03:20:17 2011
DIA0 started with pid=8, OS id=8858
Sat Dec 03 03:20:17 2011
MMAN started with pid=9, OS id=8860
Sat Dec 03 03:20:17 2011
DBW0 started with pid=10, OS id=8862
Sat Dec 03 03:20:17 2011
LGWR started with pid=11, OS id=8864
Sat Dec 03 03:20:17 2011
CKPT started with pid=12, OS id=8866
Sat Dec 03 03:20:17 2011
SMON started with pid=13, OS id=8868
Sat Dec 03 03:20:17 2011
RECO started with pid=14, OS id=8870
Sat Dec 03 03:20:17 2011
MMON started with pid=15, OS id=8872
starting up 1 dispatcher(s) for network address '(ADDRESS=(PARTIAL=YES)(PROTOCOL=TCP))'...
Sat Dec 03 03:20:17 2011
MMNL started with pid=16, OS id=8874
starting up 1 shared server(s) ...
ORACLE_BASE from environment = /opt/oracle
Sat Dec 03 03:20:17 2011
ALTER DATABASE MOUNT
Successful mount of redo thread 1, with mount id 3313002097
Database mounted in Exclusive Mode
Lost write protection disabled
Completed: ALTER DATABASE MOUNT
Sat Dec 03 03:20:21 2011
ALTER DATABASE OPEN
Thread 1 opened at log sequence 2
Current log# 2 seq# 2 mem# 0: /opt/oracle/oradata/mos5200/redo02.log
Successful open of redo thread 1
MTTR advisory is disabled because FAST_START_MTTR_TARGET is not set
SMON: enabling cache recovery
[8884] Successfully onlined Undo Tablespace 2.
Undo initialization finished serial:0 start:22913766 end:22914126 diff:360 (3 seconds)
Verifying file header compatibility for 11g tablespace encryption..
Verifying 11g file header compatibility for tablespace encryption completed
SMON: enabling tx recovery
Database Characterset is WE8MSWIN1252
No Resource Manager plan active
replication_dependency_tracking turned off (no async multimaster replication found)
Starting background process QMNC
Sat Dec 03 03:20:25 2011
QMNC started with pid=20, OS id=8886
Completed: ALTER DATABASE OPEN
Sat Dec 03 03:20:28 2011
Starting background process CJQ0
Sat Dec 03 03:20:28 2011
CJQ0 started with pid=22, OS id=8898
Sat Dec 03 03:20:45 2011
Starting background process SMCO
Sat Dec 03 03:20:45 2011
SMCO started with pid=24, OS id=8904
Sat Dec 03 03:21:06 2011
Thread 1 advanced to log sequence 3 (LGWR switch)
Current log# 3 seq# 3 mem# 0: /opt/oracle/oradata/mos5200/redo03.log
Sat Dec 03 03:21:39 2011
Shutting down instance (immediate)
Stopping background process SMCO
Shutting down instance: further logons disabled
Sat Dec 03 03:22:02 2011
kkjcre1p: unable to spawn jobq slave process, slot 0, error 1089
kkjcre1p: unable to spawn jobq slave process, slot 0, error 1089
kkjcre1p: unable to spawn jobq slave process, slot 0, error 1089
kkjcre1p: unable to spawn jobq slave process, slot 0, error 1089
Stopping background process CJQ0
Sat Dec 03 03:22:07 2011
Stopping background process QMNC
Stopping background process MMNL
Stopping background process MMON
License high water mark = 1
All dispatchers and shared servers shutdown
ALTER DATABASE CLOSE NORMAL
Sat Dec 03 03:22:11 2011
SMON: disabling tx recovery
SMON: disabling cache recovery
Sat Dec 03 03:22:11 2011
Shutting down archive processes
Archiving is disabled
Archive process shutdown avoided: 0 active
Thread 1 closed at log sequence 3
Successful close of redo thread 1
Completed: ALTER DATABASE CLOSE NORMAL
ALTER DATABASE DISMOUNT
Shutting down archive processes
Archiving is disabled
Completed: ALTER DATABASE DISMOUNT
ARCH: Archival disabled due to shutdown: 1089
Shutting down archive processes
Archiving is disabled
ARCH: Archival disabled due to shutdown: 1089
Shutting down archive processes
Archiving is disabled
Sat Dec 03 03:22:12 2011
Stopping background process VKTM
Sat Dec 03 03:22:14 2011
Instance shutdown complete
Sat Dec 03 03:22:36 2011
Starting ORACLE instance (normal)
****************** Large Pages Information *****************
Total Shared Global Region in Large Pages = 0 KB (0%)
Large Pages used by this instance: 0 (0 KB)
Large Pages unused system wide = 0 (0 KB) (alloc incr 4096 KB)
Large Pages configured system wide = 0 (0 KB)
Large Page size = 2048 KB
RECOMMENDATION:
Total Shared Global Region size is 286 MB. For optimal performance,
prior to the next instance restart increase the number
of unused Large Pages by atleast 143 2048 KB Large Pages (286 MB)
system wide to get 100% of the Shared
Global Region allocated with Large pages
***********************************************************
LICENSE_MAX_SESSION = 0
LICENSE_SESSIONS_WARNING = 0
Picked latch-free SCN scheme 3
Using LOG_ARCHIVE_DEST_1 parameter default value as /opt/oracle/product/11g/db/dbs/arch
Autotune of undo retention is turned on.
IMODE=BR
ILAT =27
LICENSE_MAX_USERS = 0
SYS auditing is disabled
Starting up:
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options.
ORACLE_HOME = /opt/oracle/product/11g/db
System name: Linux
Node name: rac1
Release: 2.6.16.60-0.21-default
Version: #1 Tue May 6 12:41:02 UTC 2008
Machine: x86_64
VM name: VMWare Version: 6
Using parameter settings in server-side spfile /opt/oracle/product/11g/db/dbs/spfilemos5200.ora
System parameters with non-default values:
processes = 150
sga_target = 284M
control_files = "/opt/oracle/oradata/mos5200/control01.ctl"
control_files = "/opt/oracle/oradata/mos5200/control02.ctl"
db_block_size = 8192
compatible = "11.2.0.0.0"
undo_tablespace = "UNDOTBS1"
remote_login_passwordfile= "EXCLUSIVE"
db_domain = ""
dispatchers = "(PROTOCOL=TCP) (SERVICE=mos5200XDB)"
audit_file_dest = "/opt/oracle/admin/mos5200/adump"
audit_trail = "DB"
db_name = "mos5200"
open_cursors = 300
pga_aggregate_target = 108M
diagnostic_dest = "/opt/oracle"
Sat Dec 03 03:22:36 2011
PMON started with pid=2, OS id=8916
Sat Dec 03 03:22:36 2011
PSP0 started with pid=3, OS id=8918
Sat Dec 03 03:22:37 2011
VKTM started with pid=4, OS id=8920 at elevated priority
VKTM running at (4)millisec precision with DBRM quantum (100)ms
Sat Dec 03 03:22:37 2011
GEN0 started with pid=5, OS id=8924
Sat Dec 03 03:22:37 2011
DIAG started with pid=6, OS id=8926
Sat Dec 03 03:22:37 2011
DBRM started with pid=7, OS id=8928
Sat Dec 03 03:22:37 2011
DIA0 started with pid=8, OS id=8930
Sat Dec 03 03:22:37 2011
MMAN started with pid=9, OS id=8932
Sat Dec 03 03:22:37 2011
DBW0 started with pid=10, OS id=8934
Sat Dec 03 03:22:37 2011
LGWR started with pid=11, OS id=8936
Sat Dec 03 03:22:37 2011
CKPT started with pid=12, OS id=8938
Sat Dec 03 03:22:37 2011
SMON started with pid=13, OS id=8940
Sat Dec 03 03:22:38 2011
RECO started with pid=14, OS id=8942
Sat Dec 03 03:22:38 2011
MMON started with pid=15, OS id=8944
starting up 1 dispatcher(s) for network address '(ADDRESS=(PARTIAL=YES)(PROTOCOL=TCP))'...
Sat Dec 03 03:22:38 2011
MMNL started with pid=16, OS id=8946
starting up 1 shared server(s) ...
ORACLE_BASE from environment = /opt/oracle
Sat Dec 03 03:22:38 2011
ALTER DATABASE MOUNT
Successful mount of redo thread 1, with mount id 3313000190
Database mounted in Exclusive Mode
Lost write protection disabled
Completed: ALTER DATABASE MOUNT
Sat Dec 03 03:22:42 2011
ALTER DATABASE OPEN
Errors in file /opt/oracle/diag/rdbms/mos5200/mos5200/trace/mos5200_lgwr_8936.trc:
ORA-00313: open failed for members of log group 3 of thread 1
ORA-00312: online log 3 thread 1: '/opt/oracle/oradata/mos5200/redo03.log'
ORA-27037: unable to obtain file status
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3
Errors in file /opt/oracle/diag/rdbms/mos5200/mos5200/trace/mos5200_lgwr_8936.trc:
ORA-00313: open failed for members of log group 3 of thread 1
ORA-00312: online log 3 thread 1: '/opt/oracle/oradata/mos5200/redo03.log'
ORA-27037: unable to obtain file status
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3
Errors in file /opt/oracle/diag/rdbms/mos5200/mos5200/trace/mos5200_ora_8956.trc:
在线日志redo3.log 丢失
ORA-00313: open failed for members of log group 1 of thread
ORA-00312: online log 3 thread 1: '/opt/oracle/oradata/mos5200/redo03.log'
Sat Dec 03 03:22:44 2011
Errors in file /opt/oracle/diag/rdbms/mos5200/mos5200/trace/mos5200_m000_8958.trc:
ORA-00313: open failed for members of log group 3 of thread 1
ORA-00312: online log 3 thread 1: '/opt/oracle/oradata/mos5200/redo03.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, sid=8956), summary=[abnormal instance termination].
System State dumped to trace file /opt/oracle/diag/rdbms/mos5200/mos5200/trace/mos5200_diag_8926.trc
USER (ospid: 8956): terminating the instance due to error 313
Dumping diagnostic data in directory=[cdmp_20111203032244], requested by (instance=1, sid=8956), summary=[abnormal instance termination].
Checker run found 2 new persistent data failures
Instance terminated by USER, pid = 8956
oracle@rac1:/opt/oracle/diag/rdbms/mos5200/mos5200/trace> exit
exit
打开数据库到mount 状态
SQL> startup mount;
ORA-24324: service handle not initialized
ORA-01041: internal error. hostdef extension doesn't exist
SQL> exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
oracle@rac1:~> export ORACLE_SID=mos5200
oracle@rac1:~> sqlplus / as sysdba
SQL*Plus: Release 11.2.0.3.0 Production on Sat Dec 3 03:25:25 2011
Copyright (c) 1982, 2011, Oracle. All rights reserved.
Connected to an idle instance.
SQL> startup mount;
ORACLE instance started.
Total System Global Area 296456192 bytes
Fixed Size 2227784 bytes
Variable Size 109052344 bytes
Database Buffers 180355072 bytes
Redo Buffers 4820992 bytes
Database mounted.
-----修改_allow_resetlogs_corruption 为true
SQL> alter system set "_allow_resetlogs_corruption"=true scope=spfile;
System altered.
SQL> shutdown immediate;
ORA-01109: database not open
Database dismounted.
ORACLE instance shut down.
SQL> startup mount;
ORACLE instance started.
Total System Global Area 296456192 bytes
Fixed Size 2227784 bytes
Variable Size 109052344 bytes
Database Buffers 180355072 bytes
Redo Buffers 4820992 bytes
Database mounted.
SQL> recover database using backup controlfile until cancel;
ORA-00279: change 1006366 generated at 12/03/2011 03:22:11 needed for thread 1
ORA-00289: suggestion : /opt/oracle/product/11g/db/dbs/arch1_3_768885075.dbf
ORA-00280: change 1006366 for thread 1 is in sequence #3
Specify log: {=suggested | filename | AUTO | CANCEL}
CANCEL
Media recovery cancelled.
SQL> alter database open resetlogs;
Database altered.
SQL>
数据库打开
以上是经较幸运的打开了，如果有ora-600 常见的2662 错误就要采用一些其它的方法打开。
来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/8013558/viewspace-750827/，如需转载，请注明出处，否则将追究法律责任。
转载自原文链接, 如需删除请联系管理员。
原文链接：在线日志损坏，转载请注明来源！