数据丢失原因:集群多次切换,多次执行shutdown abort,集群默认执行的命令是abort方式,导致控制文件全部损坏。
基础环境介绍:
suse11sp2
oracle10.2.0.5
suse HA
oracle归档模式
处理问题思路:
1、检查是否有幸存控制文件。因为控制文件有多个,尝试使用单个控制文件启动数据库,如果有完好的控制文件,就无需回复了,直接拷贝改名解决;
2、如果所有控制文件均损坏,看看有没有备份。利用备份恢复是较高效的方法。
3、如果连备份都没有,那就用最后一招,重建控制文件吧。
我这里由于是测试环境,为了测试集群搭建的一个数据库,没有考虑过备份问题,所以现在的情况是第三种,控制文件全部损坏且无备份。解决问题的过程如下:
一、问题定位
从双机软件的报错看到,数据库起不来。
查看alert发现如下报错:
ALTER DATABASE MOUNT
Mon May 13 16:26:31 CST 2013
Setting recovery target incarnation to 1
Mon May 13 16:26:31 CST 2013
Errors in file /opt/oracle/admin/ebai/bdump/ebai_lgwr_78381.trc:
ORA-00600: internal error code, arguments: [kccpb_sanity_check_2], [5597], [5596], [0x000000000], [], [], [], []
Mon May 13 16:26:31 CST 2013
Errors in file /opt/oracle/admin/ebai/bdump/ebai_lgwr_78381.trc:
ORA-00600: internal error code, arguments: [kccpb_sanity_check_2], [5597], [5596], [0x000000000], [], [], [], []
Mon May 13 16:26:31 CST 2013
LGWR: terminating instance due to error 470
Instance terminated by LGWR, pid = 78381
上网搜索查明原因是控制文件损坏。
二、解决问题的过程:
1、检查是否有幸存控制文件,看能不能偷个懒。
SQL> create pfile from spfile;
File created.
SQL> exit
Disconnected
ebdbbak:/eb_db/oracle/product/10.2/db/dbs> ls
alert_ebai.log initdw.ora init.ora orapwebai
hc_ebai.dat initebai.ora lkEBAI spfileebai.ora
ebdbbak:/eb_db/oracle/product/10.2/db/dbs> cp spfileebai.ora spfileebai.ora.2013 0514
ebdbbak:/eb_db/oracle/product/10.2/db/dbs> vi initebai.ora
注释掉控制文件,只保留一个做测试
ebdbbak:/eb_db/oracle/product/10.2/db/dbs> whoami
oracle
ebdbbak:/eb_db/oracle/product/10.2/db/dbs> pwd
/eb_db/oracle/product/10.2/db/dbs
ebdbbak:/eb_db/oracle/product/10.2/db/dbs> ls
alert_ebai.log hc_ebai.dat initdw.ora initebai.ora init.ora lkEBAI orapwebai spfileebai.ora spfileebai.ora.20130514
ebdbbak:/eb_db/oracle/product/10.2/db/dbs> sqlplus /nolog
SQL*Plus: Release 10.2.0.5.0 - Production on Tue May 14 08:41:22 2013
Copyright (c) 1982, 2010, Oracle. All Rights Reserved.
SQL> conn / as sysdba
Connected to an idle instance.
SQL> startup pfile='/eb_db/oracle/product/10.2/db/dbs/initebai.ora'
ORACLE instance started.
Total System Global Area 1.7180E+10 bytes
Fixed Size 2126480 bytes
Variable Size 2080378224 bytes
Database Buffers 1.5049E+10 bytes
Redo Buffers 48201728 bytes
Database mounted.
ORA-03113: end-of-file on communication channel
SQL> select open_mode from v$database;
ERROR:
ORA-03114: not connected to ORACLE
这次换control02尝试,依然报错:
SQL> conn / as sysdba
Connected to an idle instance.
SQL> startup pfile='/eb_db/oracle/product/10.2/db/dbs/initebai.ora'
ORACLE instance started.
Total System Global Area 1.7180E+10 bytes
Fixed Size 2126480 bytes
Variable Size 2080378224 bytes
Database Buffers 1.5049E+10 bytes
Redo Buffers 48201728 bytes
Database mounted.
ORA-01122: database file 1 failed verification check
ORA-01110: data file 1: '/eb_db/oracle/oradata/ebai/system01.dbf'
ORA-01207: file is more recent than control file - old control file
不但没解决问题,还把system表空间撤出来了。
2、重建控制文件过程
由于测试环境,没有控制文件备份,尝试重建控制文件。过程如下:
SQL> startup pfile='/eb_db/oracle/product/10.2/db/dbs/initebai.ora'
ORACLE instance started.
Total System Global Area 1.7180E+10 bytes
Fixed Size 2126480 bytes
Variable Size 2080378224 bytes
Database Buffers 1.5049E+10 bytes
Redo Buffers 48201728 bytes
Database mounted.
ORA-01122: database file 1 failed verification check
ORA-01110: data file 1: '/eb_db/oracle/oradata/ebai/system01.dbf'
ORA-01207: file is more recent than control file - old control file
SQL> alter database backup controlfile to trace;
Database altered.
SQL> @gettrcname
SP2-0310: unable to open file "gettrcname.sql"
上网查到原因,据说这个脚本是个人写的,不是oracle程序下面的工具脚本,于是我也重建脚本如下:
SQL> host vi gettrcname.sql
SELECT d.VALUE
|| '/'
|| LOWER (RTRIM (i.INSTANCE, CHR (0)))
|| '_ora_'
|| p.spid
|| '.trc' trace_file_name
FROM (SELECT p.spid
FROM SYS.v$mystat m, SYS.v$session s, SYS.v$process p
WHERE m.statistic# = 1 AND s.SID = m.SID AND p.addr = s.paddr) p,
(SELECT t.INSTANCE
FROM SYS.v$thread t, SYS.v$parameter v
WHERE v.NAME = 'thread'
AND (v.VALUE = 0 OR t.thread# = TO_NUMBER (v.VALUE))) i,
(SELECT VALUE
FROM SYS.v$parameter
WHERE NAME = 'user_dump_dest') d
/
SQL> @gettrcname
TRACE_FILE_NAME
--------------------------------------------------------------------------------
/opt/oracle/admin/ebai/udump/ebai_ora_10248.trc
ebdbbak:/eb_db/oracle> sqlplus / as sysdba
SQL*Plus: Release 10.2.0.5.0 - Production on Tue May 14 09:48:01 2013
Copyright (c) 1982, 2010, Oracle. All Rights Reserved.
Connected to an idle instance.
SQL> set echo on
SQL> @createctlfile
SQL>
SQL> STARTUP NOMOUNT
ORACLE instance started.
Total System Global Area 1.7180E+10 bytes
Fixed Size 2126480 bytes
Variable Size 2080378224 bytes
Database Buffers 1.5049E+10 bytes
Redo Buffers 48201728 bytes
SQL> CREATE CONTROLFILE REUSE DATABASE "EBAI" NORESETLOGS ARCHIVELOG
2 MAXLOGFILES 16
3 MAXLOGMEMBERS 3
4 MAXDATAFILES 300
5 MAXINSTANCES 8
6 MAXLOGHISTORY 584
7 LOGFILE
8 GROUP 1 (
9 '/eb_db/oracle/redologA/redo01a.log',
10 '/eb_db/oracle/redologB/redo01b.log'
11 ) SIZE 500M,
12 GROUP 2 (
13 '/eb_db/oracle/redologA/redo02a.log',
14 '/eb_db/oracle/redologB/redo02b.log'
15 ) SIZE 500M,
16 GROUP 3 (
17 '/eb_db/oracle/redologA/redo03a.log',
18 '/eb_db/oracle/redologB/redo03b.log'
19 ) SIZE 500M,
20 GROUP 4 (
21 '/eb_db/oracle/redologA/redo04a.log',
22 '/eb_db/oracle/redologB/redo04b.log'
23 ) SIZE 500M
24 -- STANDBY LOGFILE
25 DATAFILE
26 '/eb_db/oracle/oradata/ebai/system01.dbf',
27 '/eb_db/oracle/oradata/ebai/undotbs01.dbf',
28 '/eb_db/oracle/oradata/ebai/sysaux01.dbf',
29 '/eb_db/oracle/oradata/ebai/users01.dbf',
30 '/eb_db/oracle/oradata/ebai/users02.dbf',
31 '/eb_db/oracle/oradata/ebai/users09.dbf',
32 '/eb_db/oracle/oradata/ebai/users08.dbf',
33 '/eb_db/oracle/oradata/ebai/users07.dbf',
34 '/eb_db/oracle/oradata/ebai/users06.dbf',
35 '/eb_db/oracle/oradata/ebai/users05.dbf',
36 '/eb_db/oracle/oradata/ebai/users03.dbf',
37 '/eb_db/oracle/oradata/ebai/users04.dbf',
38 '/eb_db/oracle/oradata/ebai/undotbs02.dbf',
39 '/eb_db/oracle/oradata/ebai/indx01.dbf',
40 '/eb_db/oracle/oradata/ebai/indx02.dbf',
41 '/eb_db/oracle/oradata/ebai/indx03.dbf',
42 '/eb_db/oracle/oradata/ebai/indx04.dbf',
43 '/eb_db/oracle/oradata/ebai/indx05.dbf'
44 CHARACTER SET UTF8
45 ;
Control file created.
SQL>
SQL>
SQL> RECOVER DATABASE
Media recovery complete.
SQL>
SQL> ALTER SYSTEM ARCHIVE LOG ALL;
System altered.
SQL>
SQL> ALTER DATABASE OPEN;
Database altered.
SQL>
SQL> ALTER TABLESPACE TEMP ADD TEMPFILE '/eb_db/oracle/oradata/ebai/temp01.dbf' REUSE;
Tablespace altered.
SQL>
参考链接:http://www.eygle.com/archives/2004/10/backup_and_recreate_controlfile.html
___________________________________________________________________________________
版权所有,文章允许转载,但必须以链接方式注明源地址,否则追究法律责任!
Author: laven54 (lurou)
Email: laven54@163.com
Blog: http://blog.csdn.net/laven54