机器掉电重启后,出现异常dbid异常导致数据库不能正常启动
Beginning crash recovery of 1 threads
parallel recovery started with 3 processes
Sat Sep 11 10:26:46 2010
Errors in file /u01/app/oracle/admin/logstat1/bdump/logstat1_dbw0_28758.trc:
ORA-01186: file 1 failed verification tests
ORA-01122: database file 1 failed verification check
ORA-01110: data file 1: '/data/oradata/logstat1/system01.dbf'
ORA-01206: file is not part of this database - wrong database id
Sat Sep 11 10:26:46 2010
File 1 not verified due to error ORA-01122
Sat Sep 11 10:26:46 2010
Aborting crash recovery due to error 1221
Sat Sep 11 10:26:46 2010
Errors in file /u01/app/oracle/admin/logstat1/udump/logstat1_ora_9317.trc:
ORA-01221: data file 1 is not the same file to a background process
ORA-1221 signalled during: alter database open...
1、尝试启动
SQL> startup
ORACLE instance started.
Total System Global Area 1795162112 bytes
Fixed Size 1268004 bytes
Variable Size 150996700 bytes
Database Buffers 1627389952 bytes
Redo Buffers 15507456 bytes
Database mounted.
ORA-01221: data file 1 is not the same file to a background process
2、尝试重建控制文件,没有成功
SQL> alter database backup controlfile to trace;
Database altered.
SQL> select d.value || '/' || lower(rtrim(i.instance, chr(0))) || '_ora_' ||
2 p.SPID || '.trc'
3 from (select p.SPID
4 from v$mystat m, v$session s, v$process p
5 where m.STATISTIC# = 1
6 and s.SID = m.SID
7 and p.ADDR = s.PADDR) p,
8 (select t.INSTANCE
9 from v$thread t, v$parameter v
10 where v.name = 'thread'
11 and (v.VALUE = 0 or t.THREAD# = to_number(v.VALUE))) i,
12 (select value from v$parameter where name = 'user_dump_dest') d;
D.VALUE||'/'||LOWER(RTRIM(I.INSTANCE,CHR(0)))||'_ORA_'||P.SPID||'.TRC'
--------------------------------------------------------------------------------
/u01/app/oracle/admin/logstat1/udump/logstat1_ora_14389.trc
SQL> shutdown immediate
ORA-01109: database not open
Database dismounted.
ORACLE instance shut down.
SQL>
SQL> CREATE CONTROLFILE REUSE DATABASE "LOGSTAT1" NORESETLOGS NOARCHIVELOG
2 MAXLOGFILES 16
3 MAXLOGMEMBERS 3
4 MAXDATAFILES 100
5 MAXINSTANCES 8
6 MAXLOGHISTORY 9344
7 LOGFILE
8 GROUP 1 '/data/oradata/logstat1/redo01.log' SIZE 200M,
9 GROUP 2 '/data/oradata/logstat1/redo02.log' SIZE 200M,
10 GROUP 3 '/data/oradata/logstat1/redo03.log' SIZE 200M
11 -- STANDBY LOGFILE
12 DATAFILE
13 '/data/oradata/logstat1/system01.dbf',
14 '/data/oradata/logstat1/undotbs01.dbf',
15 '/data/oradata/logstat1/sysaux01.dbf',
16 '/data/oradata/logstat1/users01.dbf',
17 '/data/oradata/logstat1/data01.dbf',
18 '/data/oradata/logstat1/data_idx01.dbf',
19 '/data/oradata/logstat1/data02.dbf',
20 '/data/oradata/logstat1/data_idx02.dbf',
21 '/data/oradata/logstat1/data03.dbf',
22 '/data/oradata/logstat1/data_idx03.dbf',
23 '/data/oradata/logstat1/data04.dbf',
24 '/data/oradata/logstat1/data_idx04.dbf',
25 '/data/oradata/logstat1/data05.dbf',
26 '/data/oradata/logstat1/data_idx05.dbf',
27 '/data/oradata/logstat1/data06.dbf',
28 '/data/oradata/logstat1/data_idx06.dbf',
29 '/data/oradata/logstat1/data07.dbf',
30 '/data/oradata/logstat1/data_idx07.dbf',
31 '/data/oradata/logstat1/data08.dbf',
32 '/data/oradata/logstat1/data_idx08.dbf',
33 '/data/oradata/logstat1/data09.dbf',
34 '/data/oradata/logstat1/data_idx09.dbf',
35 '/data/oradata/logstat1/data10.dbf',
36 '/data/oradata/logstat1/data_idx10.dbf'
37 CHARACTER SET ZHS16GBK
38 ;
CREATE CONTROLFILE REUSE DATABASE "LOGSTAT1" NORESETLOGS NOARCHIVELOG
*
ERROR at line 1:
ORA-01503: CREATE CONTROLFILE failed
ORA-01159: file is not from same database as previous files - wrong database id
ORA-01517: log member: '/data/oradata/logstat1/redo01.log'
SQL>
SQL> recover database;
ORA-00283: recovery session canceled due to errors
ORA-00600: internal error code, arguments: [kcvhvdf_1], [], [], [], [], [], [],
[]
SQL>select dbid from v$database;
DBID
----------
66815899
3、查看文件头的dbid等信息,发现除temp01.dbf之外其他的dbid都置为0了
SQL> ALTER SESSION SET EVENTS 'immediate trace name file_hdrs level 3';
Session altered.
SQL> select d.value || '/' || lower(rtrim(i.instance, chr(0))) || '_ora_' ||
2 p.SPID || '.trc'
3 from (select p.SPID
4 from v$mystat m, v$session s, v$process p
5 where m.STATISTIC# = 1
6 and s.SID = m.SID
7 and p.ADDR = s.PADDR) p,
8 (select t.INSTANCE
9 from v$thread t, v$parameter v
10 where v.name = 'thread'
11 and (v.VALUE = 0 or t.THREAD# = to_number(v.VALUE))) i,
12 (select value from v$parameter where name = 'user_dump_dest') d;
D.VALUE||'/'||LOWER(RTRIM(I.INSTANCE,CHR(0)))||'_ORA_'||P.SPID||'.TRC'
--------------------------------------------------------------------------------
/u01/app/oracle/admin/logstat1/udump/logstat1_ora_8335.trc
SQL>
[oracle@CNC-BJ-I-5I3 logstat1]$ grep "Db ID" /u01/app/oracle/admin/logstat1/udump/logstat1_ora_8335.trc
Db ID=0=0x0, Db Name='LOGSTAT1'
Db ID=0=0x0, Db Name='LOGSTAT1'
Db ID=0=0x0, Db Name='LOGSTAT1'
Db ID=0=0x0, Db Name='LOGSTAT1'
Db ID=0=0x0, Db Name='LOGSTAT1'
Db ID=0=0x0, Db Name='LOGSTAT1'
Db ID=0=0x0, Db Name='LOGSTAT1'
Db ID=0=0x0, Db Name='LOGSTAT1'
Db ID=0=0x0, Db Name='LOGSTAT1'
Db ID=0=0x0, Db Name='LOGSTAT1'
Db ID=0=0x0, Db Name='LOGSTAT1'
Db ID=0=0x0, Db Name='LOGSTAT1'
Db ID=0=0x0, Db Name='LOGSTAT1'
Db ID=0=0x0, Db Name='LOGSTAT1'
Db ID=0=0x0, Db Name='LOGSTAT1'
Db ID=0=0x0, Db Name='LOGSTAT1'
Db ID=0=0x0, Db Name='LOGSTAT1'
Db ID=0=0x0, Db Name='LOGSTAT1'
Db ID=0=0x0, Db Name='LOGSTAT1'
Db ID=0=0x0, Db Name='LOGSTAT1'
Db ID=0=0x0, Db Name='LOGSTAT1'
Db ID=0=0x0, Db Name='LOGSTAT1'
Db ID=0=0x0, Db Name='LOGSTAT1'
Db ID=0=0x0, Db Name='LOGSTAT1'
Db ID=66815899=0x3fb879b, Db Name='LOGSTAT1'
[oracle@CNC-BJ-I-5I3 logstat1]$
通过bbed验证了dbid 为0
BBED> dump
File: /data/oradata/logstat1/system01.dbf (1)
Block: 1 Offsets: 0 to 511 Dba:0x00400001
------------------------------------------------------------------------
0ba20000 01004000 00000000 00000104 68660000 00000000 0003200a 00000000
BBED> dump
File: /data/oradata/logstat1/temp01.dbf (1)
Block: 1 Offsets: 0 to 511 Dba:0x00400001
------------------------------------------------------------------------
0ba20000 01004000 00000000 00000104 b49e0000 00000000 0003200a 9b87fb03
BBED> dump
File: /data/oradata/logstat1/system01.dbf (1)
Block: 1 Offsets: 0 to 511 Dba:0x00400001
------------------------------------------------------------------------
0ba20000 01004000 00000000 00000104 68660000 00000000 0003200a 9b87fb03
尝试用nid修改dbid,nid只能在数据库正常关闭的情况下修改,再次失败
[oracle@CNC-BJ-I-5I3 ~]$ nid target=sys/***
DBNEWID: Release 10.2.0.4.0 - Production on Sat Sep 11 13:08:49 2010
Copyright (c) 1982, 2007, Oracle. All rights reserved.
Connected to database LOGSTAT1 (DBID=66815899)
NID-00135: There are 1 active threads
Change of database name failed during validation - database is intact.
DBNEWID - Completed with validation errors.
nid更改dbid时的NID-00135错误
Oracle Error :: NID-00135
There are number active threads
Cause
is that the database crashed the last time it was shut down.
Action
Ensure that all threads are closed before retrying the operation. Start and open the database to perform crash recovery, then shut down with the NORMAL or IMMEDIATE options to close it cleanly. Finally, try running the utility again.
select file#,name,bytes from v$datafile;
SQL> col file# for 99
SQL> col name for a39
SQL> col bytes for 9999999999
SQL> select file#,name,bytes from v$datafile;
FILE# NAME BYTES
----- --------------------------------------- -----------
1 /data/oradata/logstat1/system01.dbf 555745280
2 /data/oradata/logstat1/undotbs01.dbf 2233466880
3 /data/oradata/logstat1/sysaux01.dbf 471859200
4 /data/oradata/logstat1/users01.dbf 5242880
5 /data/oradata/logstat1/data01.dbf 8589934592
6 /data/oradata/logstat1/data_idx01.dbf 8589934592
7 /data/oradata/logstat1/data02.dbf 8589934592
8 /data/oradata/logstat1/data_idx02.dbf 8589934592
9 /data/oradata/logstat1/data03.dbf 8589934592
10 /data/oradata/logstat1/data_idx03.dbf 8589934592
11 /data/oradata/logstat1/data04.dbf 8589934592
12 /data/oradata/logstat1/data_idx04.dbf 8589934592
13 /data/oradata/logstat1/data05.dbf 8589934592
14 /data/oradata/logstat1/data_idx05.dbf 8589934592
15 /data/oradata/logstat1/data06.dbf 8589934592
16 /data/oradata/logstat1/data_idx06.dbf 8589934592
17 /data/oradata/logstat1/data07.dbf 8589934592
18 /data/oradata/logstat1/data_idx07.dbf 8589934592
19 /data/oradata/logstat1/data08.dbf 8589934592
20 /data/oradata/logstat1/data_idx08.dbf 8589934592
21 /data/oradata/logstat1/data09.dbf 8589934592
22 /data/oradata/logstat1/data_idx09.dbf 8589934592
23 /data/oradata/logstat1/data10.dbf 8589934592
24 /data/oradata/logstat1/data_idx10.dbf 8589934592
24 rows selected.
4、通过bbed修改dbid
分成两步执行,否则会出现错误BBED-00209: invalid number (0x9b87fb03)
set offset 28
modify /x 9b87
set offset 30
modify /x fb03
sum apply
修改完毕再次检查dbid正常
[oracle@CNC-BJ-I-5I3 lib]$ grep "Db ID" /u01/app/oracle/admin/logstat1/udump/logstat1_ora_15578.trc
Db ID=66815899=0x3fb879b, Db Name='LOGSTAT1'
Db ID=66815899=0x3fb879b, Db Name='LOGSTAT1'
Db ID=66815899=0x3fb879b, Db Name='LOGSTAT1'
Db ID=66815899=0x3fb879b, Db Name='LOGSTAT1'
Db ID=66815899=0x3fb879b, Db Name='LOGSTAT1'
Db ID=66815899=0x3fb879b, Db Name='LOGSTAT1'
Db ID=66815899=0x3fb879b, Db Name='LOGSTAT1'
Db ID=66815899=0x3fb879b, Db Name='LOGSTAT1'
Db ID=66815899=0x3fb879b, Db Name='LOGSTAT1'
Db ID=66815899=0x3fb879b, Db Name='LOGSTAT1'
Db ID=66815899=0x3fb879b, Db Name='LOGSTAT1'
Db ID=66815899=0x3fb879b, Db Name='LOGSTAT1'
Db ID=66815899=0x3fb879b, Db Name='LOGSTAT1'
Db ID=66815899=0x3fb879b, Db Name='LOGSTAT1'
Db ID=66815899=0x3fb879b, Db Name='LOGSTAT1'
Db ID=66815899=0x3fb879b, Db Name='LOGSTAT1'
Db ID=66815899=0x3fb879b, Db Name='LOGSTAT1'
Db ID=66815899=0x3fb879b, Db Name='LOGSTAT1'
Db ID=66815899=0x3fb879b, Db Name='LOGSTAT1'
Db ID=66815899=0x3fb879b, Db Name='LOGSTAT1'
Db ID=66815899=0x3fb879b, Db Name='LOGSTAT1'
Db ID=66815899=0x3fb879b, Db Name='LOGSTAT1'
Db ID=66815899=0x3fb879b, Db Name='LOGSTAT1'
Db ID=66815899=0x3fb879b, Db Name='LOGSTAT1'
Db ID=66815899=0x3fb879b, Db Name='LOGSTAT1'
[oracle@CNC-BJ-I-5I3 lib]$
数据库正常启动
参考如下:
http://itspace.javaeye.com/blog/611988