本文主要介绍了ORACLE中启动错误的实例分析,非常详细,具有一定的参考价值。感兴趣的朋友一定要看完!
Oracle数据库无法远程连接,连接错误如图所示:
操作:windows server2008 R2
数据库:甲骨文11.2.0.1公司
乍一看,似乎数据库正在打开或关闭的过程中。检查警报日志。最新的数据库启动发生在上午10336050。一些警报日志如下:
2017年8月2日星期三10:51:48
正在启动ORACLE实例(正常)
LICENSE_MAX_SESSION=0
许可证_会话_警告=0
拾取无闩锁SCN方案3
2017年8月2日星期三10:52:01
撤消保留的自动调整已打开。
IMODE=BR
ILAT=86
LICENSE_MAX_USERS=0
系统审计被禁用
启动:
Oracle数据库11g企业版版本11 . 2 . 0 . 1 . 0-64位生产
使用分区、OLAP、数据挖掘和实际应用测试选项。
然后,实现同事说他中午启动数据库,但是数据库停在:“数据库加载了。”良久,如图所示:
然后,启动时报告ORA-03113,如所示:
然后,他说数据库启动失败后,他又重启了数据库服务器主机,然后问题依然存在。
接下来是我的处理:
登录到数据库服务器,
检查主机状态,CPU、磁盘IO、内存等资源是否空闲。
视图数据库服务和侦听服务都已启动。
登录到数据库sqlplus,检查数据库实例的当前状态是否已装载。
windows[版本6.1.7601]
版权所有(c)2009MicrosoftCorporation。保留所有权利。
C: \用户\管理员qplus/assysdba
SQL * PLUS :版本11.2.0.1.0生产日期:2017年8月21日星期三3:483360402017
版权所有(c)1982,2010,甲骨文
连接到:
oracledatabase 11 genterpriseditionrelease 11 . 2 . 0 . 1 . 0-64bit production
OLAP,数据挖掘和真实应用测试选项
SQLselectstatusfromv $实例;
状态
-
安装好的
-尝试将数据库打开到ORA-01154。日志如下:
SQL alter数据库打开;
更改数据库打开
*
第1行出现错误:
ORA-01154:数据库正忙。现在不允许打开、关闭、装载和卸载。
接下来,重新启动操作系统的Oracle实例服务,然后重新启动它。启动仍停留在“数据库已加载”,日志如下:
从 Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options(情况复杂) 断开
C:\Users\Administrator>sqlplus / as sysdba
SQL*Plus: Release 11.2.0.1.0 Production on 星期三 8月 2 13:58:58 2017
Copyright (c) 1982, 2010, Oracle. All rights reserved.
已连接到空闲例程。
SQL> startup
ORACLE 例程已经启动。
Total System Global Area 2.0577E+10 bytes
Fixed Size 2184672 bytes
Variable Size 8589937184 bytes
Database Buffers 1.1945E+10 bytes
Redo Buffers 39743488 bytes
数据库装载完毕。
--观察数据库告警日志,12:59分数据库有ORA-00949,有实例进程超时等待
Wed Aug 02 12:59:29 2017
Errors in file d:\oraclehome\oracle\diag\rdbms\bmi\bmi\trace\bmi_arc1_3604.trc (incident=164377):
ORA-00494: 持有入队 [CF] 的时间过长 (超过 900 秒) (由 'inst 1, osid 3084')
Incident details in: d:\oraclehome\oracle\diag\rdbms\bmi\bmi\incident\incdir_164377\bmi_arc1_3604_i164377.trc
Killing enqueue blocker (pid=3084) on resource CF-00000000-00000000 by (pid=3604)
by killing session 638.1
Killing enqueue blocker (pid=3084) on resource CF-00000000-00000000 by (pid=3604)
by terminating the process
ARC1 (ospid: 3604): terminating the instance due to error 2103
--13:20数据库被重启,日志如下(猜想是主机重启后的自动重启)
Wed Aug 02 13:19:29 2017
Starting ORACLE instance (normal)
LICENSE_MAX_SESSION = 0
LICENSE_SESSIONS_WARNING = 0
Picked latch-free SCN scheme 3
Autotune of undo retention is turned on.
IMODE=BR
ILAT =86
LICENSE_MAX_USERS = 0
SYS auditing is disabled
Starting up:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options.
--观察到TNS连接报错:ORA-01155
Wed Aug 02 13:34:35 2017
Errors in file d:\oraclehome\oracle\diag\rdbms\bmi\bmi\trace\bmi_m000_4620.trc:
ORA-01155: 正在打开, 关闭, 装载或卸装数据库
Wed Aug 02 13:48:39 2017
TNS-12535: TNS: 操作超时
ns secondary err code: 12606
nt main err code: 0
nt secondary err code: 0
nt OS err code: 0
Client address: (ADDRESS=(PROTOCOL=tcp)(HOST=fe80::94b2:4372:a24c:4b6e%11)(PORT=49179))
WARNING: inbound connection timed out (ORA-3136)
Wed Aug 02 13:49:38 2017
Errors in file d:\oraclehome\oracle\diag\rdbms\bmi\bmi\trace\bmi_m000_5596.trc:
ORA-01155: 正在打开, 关闭, 装载或卸装数据库
再看自己重启后的告警日志
Wed Aug 02 13:59:07 2017
Starting ORACLE instance (normal)
LICENSE_MAX_SESSION = 0
LICENSE_SESSIONS_WARNING = 0
Picked latch-free SCN scheme 3
Autotune of undo retention is turned on.
IMODE=BR
ILAT =86
LICENSE_MAX_USERS = 0
SYS auditing is disabled
Starting up:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options.
......
Wed Aug 02 13:59:12 2017
ALTER DATABASE MOUNT
Wed Aug 02 13:59:12 2017
MMNL started with pid=17, OS id=2012
Successful mount of redo thread 1, with mount id 271372528
Database mounted in Exclusive Mode
Lost write protection disabled
Completed: ALTER DATABASE MOUNT
Wed Aug 02 13:59:17 2017
ALTER DATABASE OPEN
Beginning crash recovery of 1 threads
parallel recovery started with 15 processes
Wed Aug 02 13:59:41 2017
Started redo scan
Wed Aug 02 14:01:55 2017
Completed redo scan
read 8951211 KB redo, 764700 data blocks need recovery
Wed Aug 02 14:03:22 2017
Started redo application at
Thread 1: logseq 364892, block 508132
Recovery of Online Redo Log: Thread 1 Group 7 Seq 364892 Reading mem 0
Mem# 0: D:\ORACLEHOME\ORACLE\ORADATA\BMI\REDO07_01.LOG
Mem# 1: D:\ORACLEHOME\ORACLE\ORADATA\BMI\REDO07_02.LOG
Recovery of Online Redo Log: Thread 1 Group 21 Seq 364893 Reading mem 0
Mem# 0: D:\ORACLEHOME\ORACLE\ORADATA\BMI\REDO21_01.LOG
Mem# 1: D:\ORACLEHOME\ORACLE\ORADATA\BMI\REDO21_02.LOG
从启动告警日志可以看到,数据库肯定被异常终止过,当前数据库增在从redo日志恢复,此时需要耐心等待;再次询问实施同事并让其关闭远程的所有oracle客户端程序。
然后等待20分钟,数据库完成了open操作
Wed Aug 02 14:19:19 2017
Completed crash recovery at
Thread 1: logseq 364905, block 820661, scn 14976863197959
764700 data blocks read, 474332 data blocks written, 8951211 redo k-bytes read
Wed Aug 02 14:19:37 2017
LGWR: STARTING ARCH PROCESSES
Wed Aug 02 14:19:37 2017
ARC0 started with pid=37, OS id=1072
ARC0: Archival started
LGWR: STARTING ARCH PROCESSES COMPLETE
ARC0: STARTING ARCH PROCESSES
Wed Aug 02 14:19:38 2017
ARC1 started with pid=38, OS id=5408
Wed Aug 02 14:19:38 2017
ARC2 started with pid=39, OS id=4412
ARC1: Archival started
ARC2: Archival started
ARC2: Becoming the 'no FAL' ARCH
ARC2: Becoming the 'no SRL' ARCH
ARC1: Becoming the heartbeat ARCH
Wed Aug 02 14:19:38 2017
ARC3 started with pid=40, OS id=5536
ARC3: Archival started
ARC0: STARTING ARCH PROCESSES COMPLETE
Thread 1 advanced to log sequence 364906 (thread open)
Thread 1 opened at log sequence 364906
Current log# 6 seq# 364906 mem# 0: D:\ORACLEHOME\ORACLE\ORADATA\BMI\REDO06_01.LOG
Current log# 6 seq# 364906 mem# 1: D:\ORACLEHOME\ORACLE\ORADATA\BMI\REDO06_02.LOG
Successful open of redo thread 1
Wed Aug 02 14:19:45 2017
MTTR advisory is disabled because FAST_START_MTTR_TARGET is not set
Wed Aug 02 14:19:45 2017
SMON: enabling cache recovery
Successfully onlined Undo Tablespace 2.
Verifying file header compatibility for 11g tablespace encryption..
Verifying 11g file header compatibility for tablespace encryption completed
SMON: enabling tx recovery
Database Characterset is ZHS16GBK
No Resource Manager plan active
replication_dependency_tracking turned off (no async multimaster replication found)
Wed Aug 02 14:20:02 2017
Starting background process QMNC
Wed Aug 02 14:20:02 2017
QMNC started with pid=58, OS id=5700
Completed: ALTER DATABASE OPEN
--在数据库open的过程中,本地登录sqlplus查看会话信息,数据库确实开启了并行进程恢复:
SQL> select sid,process,program from v$session
2 where
3 type = 'USER'
4 and
5 SID not in (select DISTINCT SID from v$mystat);
SID PROCESS PROGRAM
---------- ------------------------ ----------------------------------------------------------------
3 3668 ORACLE.EXE (P011)
4 828 ORACLE.EXE (P022)
51 6024 ORACLE.EXE (P012)
52 5824 ORACLE.EXE (P023)
100 5880 ORACLE.EXE (P013)
101 4624 ORACLE.EXE (P024)
148 6048 ORACLE.EXE (P014)
150 2452 ORACLE.EXE (P025)
198 6028 ORACLE.EXE (P000)
200 3060 ORACLE.EXE (P026)
251 4844 ORACLE.EXE (P027)
298 4556 ORACLE.EXE (P001)
299 4356 ORACLE.EXE (P028)
347 4828 ORACLE.EXE (P002)
348 5956 ORACLE.EXE (P029)
396 5716 ORACLE.EXE (P003)
397 4288 ORACLE.EXE (P030)
443 5992 ORACLE.EXE (P004)
444 2828 ORACLE.EXE (P015)
445 4140 ORACLE.EXE (P031)
492 4704 ORACLE.EXE (P005)
493 5840 ORACLE.EXE (P016)
541 6004 ORACLE.EXE (P006)
542 2216 ORACLE.EXE (P017)
590 5048 ORACLE.EXE (P018)
591 1808 ORACLE.EXE (P007)
639 5632 ORACLE.EXE (P019)
640 4936 ORACLE.EXE (P008)
641 4436 ORACLE.EXE (J000)
689 5576 ORACLE.EXE (P020)
690 5884 ORACLE.EXE (P009)
737 6064 ORACLE.EXE (P021)
739 6012 ORACLE.EXE (P010)
已选择33行。
数据库完成open后查看数据库状态,数据库恢复正常:
SQL> startup
ORACLE 例程已经启动。
Total System Global Area 2.0577E+10 bytes
Fixed Size 2184672 bytes
Variable Size 8589937184 bytes
Database Buffers 1.1945E+10 bytes
Redo Buffers 39743488 bytes
数据库装载完毕。
数据库已经打开。
SQL> select instance_name,status from v$instance;
INSTANCE_NAME STATUS
---------------- ------------
bmi OPEN
SQL>
SQL> select name,open_mode from v$database;
NAME OPEN_MODE
--------- --------------------
BMI READ WRITE
SQL>
注意:本次故障中,需要实施同事明白Oracle数据库的启动和关闭过程;重启操作系统前,需要先关闭oracle监听、确定当前数据库中没有执行计划任务或存储过程、
关闭oracle数据库实例、关闭oracle数据库服务、重启操作系统,如果不是这个顺序,就可能导致数据文件损坏,数据库启动需要恢复而打开过程很慢,需要耐心等待。
以上是“ORACLE中startup报错的示例分析”这篇文章的所有内容,感谢各位的阅读!希望分享的内容对大家有帮助,更多相关知识,欢迎关注行业资讯频道!
内容来源网络,如有侵权,联系删除,本文地址:https://www.230890.com/zhan/99265.html