记一次oracle故障排查记录

故障现象

window操作系统,oracle 11.2.0.1.0版本,意外断电后计算机服务中可以启动oracle服务和监听服务,但是无法连接;
使用sqlplus连接上去发现数据库未启动,尝试手动starup启动数据库,提示”ORA-03113:通信通道的文件结尾,进程id:7776 会话 ID: 322 序列号:25”的错误。
进入到oracle 日志目录下,在alter_yangquan123.log发现有明显报错“ORA-00338: log 1 of thread 1 is more recent than control file”,该错误意思是redo日志中的记录比控制文件新。

ORA-00338: log 1 of thread 1 is more recent than control file

手动创建控制文件

1
2
3
4
oradebug setmypid
oradebug tracefile_name
alter database backup controlfile to trace;
select value from v$diag_info where name ='Diag Trace';

ora

查看错误日志

在alert_yangquan123.log日志中,我们可以看到以下这段

1
2
3
alter database backup controlfile to trace
Backup controlfile written to trace file e:\app\adminsilu\diag\rdbms\yangquan123\trace\yangquan123_ora_20768.trc
Completed: alter database backup controlfile to trace

打开e:\app\adminsilu\diag\rdbms\yangquan123\trace\yangquan123_ora_20768.trc文件,复制一下这段,在sql窗口执行

手动运行sql语句启动

sqlplus / as sysdba

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
SQL> STARTUP NOMOUNT #在sql窗口执行以下命令
CREATE CONTROLFILE REUSE DATABASE "YANGQUAN" NORESETLOGS NOARCHIVELOG
MAXLOGFILES 16
MAXLOGMEMBERS 3
MAXDATAFILES 100
MAXINSTANCES 8
MAXLOGHISTORY 292
LOGFILE
GROUP 1 'E:\APP\ADMINSILU\ORADATA\YANGQUAN123\REDO01.LOG' SIZE 50M BLOCKSIZE 512,
GROUP 2 'E:\APP\ADMINSILU\ORADATA\YANGQUAN123\REDO02.LOG' SIZE 50M BLOCKSIZE 512,
GROUP 3 'E:\APP\ADMINSILU\ORADATA\YANGQUAN123\REDO03.LOG' SIZE 50M BLOCKSIZE 512
-- STANDBY LOGFILE
DATAFILE
'E:\APP\ADMINSILU\ORADATA\YANGQUAN123\SYSTEM01.DBF',
'E:\APP\ADMINSILU\ORADATA\YANGQUAN123\SYSAUX01.DBF',
'E:\APP\ADMINSILU\ORADATA\YANGQUAN123\UNDOTBS01.DBF',
'E:\APP\ADMINSILU\ORADATA\YANGQUAN123\USERS01.DBF',
'E:\APP\ADMINSILU\ORADATA\YANGQUAN123\EXAMPLE01.DBF',
'E:\ORACLE_DATABASEFILES\AIR_DATA.DBF'
CHARACTER SET ZHS16GBK
;

启动之后查看数据库状态

1
2
select name,open_mode from v$database; #数据库状态已经Open了
select * from dual;

但是,但是几秒钟之后实例就宕掉了
ora

ORA-00600: internal error code, arguments: [4194], [], [], [], [], [], [], [], [], [], [], []

再次查看alter日志,发现有新的报错了ORA-00600: internal error code, arguments: [4194], [], [], [], [], [], [], [], [], [], [], []
ora

通过spfile创建生成pfile

1
SQL> create pfile from spfile; #创建出来的文件windows在$ORACLE_HOME/database目录下,Linux在$ORACLE_HOME/dbs目录下,格式为initORACLE_SID.ora

关闭数据库

1
SQL> shutdown immediate

修改pfile

打开inityangquan123.ora文件,修改其中的两行内容

1
2
*.undo_management='MANUAL'
*.undo_tablespace='UNDOTBS'

使用pfile启动数据库

1
startup restrict pfile='E:\app\adminsilu\product\11.2.0\dbhome_1\database\inityangquan123.ora'

查看undo文件

1
2
3
4
5
SQL> select tablespace_name, status, segment_name from dba_rollback_segs where status != 'OFFLINE';
TABLESPACE_NAME STATUS SEGMENT_NAME
------------------------------ ---------------- ------------------------------
SYSTEM ONLINE SYSTEM

新建一个UNDO表空间

1
SQL> create undo tablespace UNDOTBS1 datafile 'E:\APP\ADMINSILU\ORADATA\YANGQUAN123\UNDOTBS_01.dbf' size 4G;

删除旧的UNDO表空间

1
SQL> drop tablespace UNDOTBS including contents and datafiles;

关闭数据库实例

1
SQL> shutdown immediate;

启动数据库实例到NOMOUNT状态

1
SQL> startup nomount;

修改spfile中的undo_tablespace参数

1
SQL> alter system set undo_tablespace='UNDOTBS1' scope=spfile;

启动数据库实例(使用spfile)

1
2
SQL> startup;
SQL> select name,open_mode from v$database; #查看数据库状态并监测

几分钟后发现数据库未关闭,但是日志中依然有报错

ORA-08102: index key not found,obj#289,file 1,block 2025(2);

删除索引

1
2
3
4
5
6
7
8
9
SQL> col object_name format a15;
SQL> col owner format a10;
SQL> select o.owner, o.object_name, o.object_id, o.object_type from dba_objects o where o.object_id = 289;
OWNER      OBJECT_NAME      OBJECT_ID OBJECT_TYPE
----------    ---------------           ----------      -------------------
SYS           I_JOB_NEXT             289              INDEX
SQL> drop index i_job_next;

重建索引

1
SQL> create index i_job_next on job$ (next_date);

数据库实例未能加到监听中

检查了各种文件都正常,手动alter system register也不行,无奈之下,使用netca重新配置了监听。

参考链接:
https://blog.csdn.net/zwk626542417/article/details/39667999
http://www.cnblogs.com/kerrycode/p/6085447.html
https://blog.csdn.net/lwei_998/article/details/5920133

独学无友,孤陋寡闻;学习记录,欢迎拍砖!