当前日志损坏的案例（一）

[oracle@ts01 oracle]$ sqlplus '/ as sysdba'

SQL*Plus: Release 9.2.0.4.0 - Production on Tue Nov 15 10:35:11 2005

Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.

Connected to:

Oracle9i Enterprise Edition Release 9.2.0.4.0 - Production

With the Partitioning, OLAP and Oracle Data Mining options

JServer Release 9.2.0.4.0 - Production

SQL> conn / as sysdba

Connected.

SQL>

在另一个SESSION：

[oracle@ts01 oracle]$ cd /oracle/oradata/TSMISC02

[oracle@ts01 TSMISC02]$ ll

total 681820

drwxr-xr-x 2 oracle oinstall 4096 Nov 15 08:52 archive

-rw-r----- 1 oracle oinstall 1662976 Nov 15 10:35 control01.ctl

-rw-r----- 1 oracle oinstall 1662976 Nov 15 10:35 control02.ctl

-rw-r----- 1 oracle oinstall 1662976 Nov 15 10:35 control03.ctl

-rw-r----- 1 oracle oinstall 20979712 Nov 15 09:23 drsys01.dbf

-rw-r----- 1 oracle oinstall 26222592 Nov 15 09:23 indx01.dbf

-rw-r----- 1 oracle oinstall 20979712 Nov 15 09:23 odm01.dbf

-rw-r----- 1 oracle oinstall 2097664 Nov 15 10:35 redo01.log

-rw-r----- 1 oracle oinstall 2097664 Nov 15 05:48 redo02.log

-rw-r----- 1 oracle oinstall 2097664 Nov 15 08:52 redo03.log

-rw-r----- 1 oracle oinstall 387981312 Nov 15 10:26 system01.dbf

-rw-r----- 1 oracle oinstall 52436992 Nov 15 09:23 system02.dbf

-rw-r----- 1 oracle oinstall 42999808 Nov 1 14:16 temp01.dbf

-rw-r----- 1 oracle oinstall 10493952 Nov 15 09:23 tools01.dbf

-rw-r----- 1 oracle oinstall 52436992 Nov 15 10:28 undotbs2.dbf

-rw-r----- 1 oracle oinstall 26222592 Nov 15 09:23 users01.dbf

-rw-r----- 1 oracle oinstall 47194112 Nov 15 09:23 xdb01.dbf

[oracle@ts01 TSMISC02]$ cat redo01.log| od -x|head

0000000 0200 0000 0200 0000 1000 0000 5c5d 5a5b

0000020 0000 0000 1606 0000 0000 0000 0000 0000

0000040 0000 0000 0000 0000 0000 0000 0000 0000

*

0001000 003e 0000 0001 0000 f0c1 223c 0000 fec1

0001020 0000 0920 0000 0920 98c2 6798 5354 494d

0001040 4353 3230 01ec 0000 1000 0000 0200 0000

0001060 0001 0002 15c2 6798 0000 0000 0000 0000

0001100 0000 0000 0000 0000 0000 0000 0000 0000

0001120 0000 0000 0000 0000 0000 0000 6854 6572

[oracle@ts01 TSMISC02]$ >redo01.log

[oracle@ts01 TSMISC02]$ ll

total 679764

drwxr-xr-x 2 oracle oinstall 4096 Nov 15 08:52 archive

-rw-r----- 1 oracle oinstall 1662976 Nov 15 11:12 control01.ctl

-rw-r----- 1 oracle oinstall 1662976 Nov 15 11:12 control02.ctl

-rw-r----- 1 oracle oinstall 1662976 Nov 15 11:12 control03.ctl

-rw-r----- 1 oracle oinstall 20979712 Nov 15 09:23 drsys01.dbf

-rw-r----- 1 oracle oinstall 26222592 Nov 15 09:23 indx01.dbf

-rw-r----- 1 oracle oinstall 20979712 Nov 15 09:23 odm01.dbf

-rw-r----- 1 oracle oinstall 0 Nov 15 11:12 redo01.log

-rw-r----- 1 oracle oinstall 2097664 Nov 15 05:48 redo02.log

-rw-r----- 1 oracle oinstall 2097664 Nov 15 08:52 redo03.log

-rw-r----- 1 oracle oinstall 387981312 Nov 15 10:57 system01.dbf

-rw-r----- 1 oracle oinstall 52436992 Nov 15 09:23 system02.dbf

-rw-r----- 1 oracle oinstall 42999808 Nov 1 14:16 temp01.dbf

-rw-r----- 1 oracle oinstall 10493952 Nov 15 09:23 tools01.dbf

-rw-r----- 1 oracle oinstall 52436992 Nov 15 11:11 undotbs2.dbf

-rw-r----- 1 oracle oinstall 26222592 Nov 15 09:23 users01.dbf

-rw-r----- 1 oracle oinstall 47194112 Nov 15 09:23 xdb01.dbf

[oracle@ts01 TSMISC02]$

回到SESSION2：

SQL> select * from v$log;

GROUP# THREAD# SEQUENCE# BYTES MEMBERS ARC STATUS

---------- ---------- ---------- ---------- ---------- --- ----------------

FIRST_CHANGE# FIRST_TIM

------------- ---------

1 1 62 2097152 1 NO CURRENT

658443 15-NOV-05

2 1 60 2097152 1 YES INACTIVE

641419 15-NOV-05

3 1 61 2097152 1 YES INACTIVE

649920 15-NOV-05

SQL> set linesize 132

SQL> l

1* select * from v$log

SQL> /

GROUP# THREAD# SEQUENCE# BYTES MEMBERS ARC STATUS FIRST_CHANGE# FIRST_TIM

---------- ---------- ---------- ---------- ---------- --- ---------------- ------------- ---------

1 1 62 2097152 1 NO CURRENT 658443 15-NOV-05

2 1 60 2097152 1 YES INACTIVE 641419 15-NOV-05

3 1 61 2097152 1 YES INACTIVE 649920 15-NOV-05

SQL> alter system switch logfile;

alter system switch logfile

*

ERROR at line 1:

ORA-03113: end-of-file on communication channel

SQL> /

alter system switch logfile

*

ERROR at line 1:

ORA-03114: not connected to ORACLE

SQL> conn / as sysdba

Connected to an idle instance.

SQL> /

alter system switch logfile

*

ERROR at line 1:

ORA-01034: ORACLE not available

SQL> conn / as sysdba

Connected to an idle instance.

SQL>

启动数据库，报错：

SQL> conn / as sysdba

Connected to an idle instance.

SQL>

SQL> startup

ORACLE instance started.

Total System Global Area 236000356 bytes

Fixed Size 451684 bytes

Variable Size 201326592 bytes

Database Buffers 33554432 bytes

Redo Buffers 667648 bytes

Database mounted.

ORA-00316: log 1 of thread 1, type 0 in header is not log file

ORA-00312: online log 1 thread 1: '/oracle/oradata/TSMISC02/redo01.log'

SQL>

SQL> select name,OPEN_MODE from v$database;

NAME OPEN_MODE

--------- ----------

TSMISC02 MOUNTED

SQL> select * from v$instance;

INSTANCE_NUMBER INSTANCE_NAME

--------------- ----------------

HOST_NAME

----------------------------------------------------------------

VERSION STARTUP_T STATUS PAR THREAD# ARCHIVE LOG_SWITCH_

----------------- --------- ------------ --- ---------- ------- -----------

LOGINS SHU DATABASE_STATUS INSTANCE_ROLE ACTIVE_ST

---------- --- ----------------- ------------------ ---------

1 TSMISC02

ts01

9.2.0.4.0 15-NOV-05 MOUNTED NO 1 STARTED

ALLOWED NO ACTIVE PRIMARY_INSTANCE NORMAL

SQL> set linesize 132

SQL> select * from v$log;

GROUP# THREAD# SEQUENCE# BYTES MEMBERS ARC STATUS FIRST_CHANGE# FIRST_TIM

---------- ---------- ---------- ---------- ---------- --- ---------------- ------------- ---------

1 1 62 2097152 1 NO INVALIDATED 658443 15-NOV-05

2 1 0 2097152 1 YES UNUSED 0 15-NOV-05

3 1 61 2097152 1 YES INACTIVE 649920 15-NOV-05

SQL> col member for a100

SQL> select * from v$logfile;

GROUP# STATUS TYPE MEMBER

---------- ------- ------- ----------------------------------------------------------------------------------------------------

1 ONLINE /oracle/oradata/TSMISC02/redo01.log

2 ONLINE /oracle/oradata/TSMISC02/redo02.log

3 ONLINE /oracle/oradata/TSMISC02/redo03.log

SQL>

检查alert日志：

Tue Nov 15 11:30:13 2005

Started first pass scan

Tue Nov 15 11:30:13 2005

Errors in file /oracle/admin/TSMISC02/udump/tsmisc02_ora_29582.trc:

ORA-00316: log 1 of thread 1, type 0 in header is not log file

ORA-00312: online log 1 thread 1: '/oracle/oradata/TSMISC02/redo01.log'

ORA-316 signalled during: ALTER DATABASE OPEN...

Tue Nov 15 11:31:09 2005

Restarting dead background process QMN0

QMN0 started with pid=9

Tue Nov 15 11:36:10 2005

Restarting dead background process QMN0

QMN0 started with pid=9

Tue Nov 15 11:41:11 2005

Restarting dead background process QMN0

QMN0 started with pid=9

Tue Nov 15 11:46:12 2005

Restarting dead background process QMN0

QMN0 started with pid=9

Tue Nov 15 11:51:13 2005

Restarting dead background process QMN0

QMN0 started with pid=9

Tue Nov 15 11:56:14 2005

Restarting dead background process QMN0

QMN0 started with pid=9

。。。

相关trace文件内容：

[oracle@ts01 udump]$ ll tsmisc02_ora_29582.trc

-rw-r----- 1 oracle oinstall 912 Nov 15 11:30 tsmisc02_ora_29582.trc

[oracle@ts01 udump]$ cat tsmisc02_ora_29582.trc

/oracle/admin/TSMISC02/udump/tsmisc02_ora_29582.trc

Oracle9i Enterprise Edition Release 9.2.0.4.0 - Production

With the Partitioning, OLAP and Oracle Data Mining options

JServer Release 9.2.0.4.0 - Production

ORACLE_HOME = /oracle/product/920

System name: Linux

Node name: ts01

Release: 2.4.21-4.EL

Version: #1 Fri Oct 3 18:13:58 EDT 2003

Machine: i686

Instance name: TSMISC02

Redo thread mounted by this instance: 1

Oracle process number: 14

Unix process pid: 29582, image: oracle@ts01 (TNS V1-V3)

*** SESSION ID:(11.3) 2005-11-15 11:30:13.297

Thread checkpoint rba:0x00003e.00000002.0010 scn:0x0000.000a0c0b

On-disk rba:0x00003e.00000c1e.0000 scn:0x0000.000a257e

Use incremental checkpoint cache-low RBA

Thread 1 recovery from rba:0x00003e.0000095f.0000 scn:0x0000.00000000

ORA-00316: log 1 of thread 1, type 0 in header is not log file

ORA-00312: online log 1 thread 1: '/oracle/oradata/TSMISC02/redo01.log'

[oracle@ts01 udump]$

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 thread 1

ORA-00312: online log 1 thread 1: '/oracle/oradata/TSMISC02/redo01.log'

SQL> alter database clear unarchived logfile group 1;

alter database clear unarchived logfile group 1

*

ERROR at line 1:

ORA-01624: log 1 needed for crash recovery of thread 1

ORA-00312: online log 1 thread 1: '/oracle/oradata/TSMISC02/redo01.log'

SQL>

SQL> archive log list;

Database log mode Archive Mode

Automatic archival Enabled

Archive destination /oracle/oradata/TSMISC02/archive

Oldest online log sequence 61

Next log sequence to archive 62

Current log sequence 62

SQL> select * from v$log;

GROUP# THREAD# SEQUENCE# BYTES MEMBERS ARC STATUS FIRST_CHANGE# FIRST_TIM

---------- ---------- ---------- ---------- ---------- --- ---------------- ------------- ---------

1 1 62 2097152 1 NO INVALIDATED 658443 15-NOV-05

2 1 0 2097152 1 YES UNUSED 0 15-NOV-05

3 1 61 2097152 1 YES INACTIVE 649920 15-NOV-05

SQL>

断定，损坏的是current 的日志。

• ·CSDN blog 添加音乐和天气预报效果
• ·WebServices调用存储过程
• ·大量的歌曲免费上传空间 【网络搜集】
• ·前触发器和后触发器简介(downmoon)
• ·当前日志损坏的案例（二）
• ·A Better File Upload Pr
• ·查询数据库后返回Iterator
• ·获取所有用户表及根据表Id取得表字段信息
• ·"用户界面和多媒体"版面问
• ·[项目管理]管理中的人