ORACLE中startup報(bào)錯(cuò)的示例分析

這篇文章主要介紹ORACLE中startup報(bào)錯(cuò)的示例分析，文中介紹的非常詳細(xì)，具有一定的參考價(jià)值，感興趣的小伙伴們一定要看完！

創(chuàng)新互聯(lián)公司專業(yè)網(wǎng)站設(shè)計(jì)、成都網(wǎng)站制作，集網(wǎng)站策劃、網(wǎng)站設(shè)計(jì)、網(wǎng)站制作于一體，網(wǎng)站seo、網(wǎng)站優(yōu)化、網(wǎng)站營銷、軟文發(fā)布平臺(tái)等專業(yè)人才根據(jù)搜索規(guī)律編程設(shè)計(jì)，讓網(wǎng)站在運(yùn)行后，在搜索中有好的表現(xiàn)，專業(yè)設(shè)計(jì)制作為您帶來效益的網(wǎng)站！讓網(wǎng)站建設(shè)為您創(chuàng)造效益。

oracle數(shù)據(jù)庫無法通過遠(yuǎn)程連接，連接報(bào)錯(cuò)如圖：
操作系統(tǒng)：windows server2008 R2
數(shù)據(jù)庫版本：oracle 11.2.0.1
ORACLE中startup報(bào)錯(cuò)的示例分析
初看報(bào)錯(cuò)貌似數(shù)據(jù)庫正處在打開或關(guān)閉的過程中。查看告警日志，最近的一次數(shù)據(jù)庫啟動(dòng)發(fā)生在上午10:50，部分告警日志如下：
Wed Aug 02 10:51:48 2017
Starting ORACLE instance (normal)
LICENSE_MAX_SESSION = 0
LICENSE_SESSIONS_WARNING = 0
Picked latch-free SCN scheme 3
Wed Aug 02 10:52:01 2017
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.
然后，實(shí)施同事說，他中午那會(huì)startup了一下數(shù)據(jù)庫，但是數(shù)據(jù)庫停在了：“數(shù)據(jù)庫裝載完畢。”好長一段時(shí)間，如圖：
ORACLE中startup報(bào)錯(cuò)的示例分析
然后，啟動(dòng)就報(bào)了ORA-03113，如果所示：

然后，他又說，數(shù)據(jù)庫啟動(dòng)失敗后，他接著重啟了數(shù)據(jù)庫服務(wù)器主機(jī)，然后但是問題依舊。
接下來是我的處理過程：
登錄數(shù)據(jù)庫服務(wù)器，
查看主機(jī)狀態(tài)，CPU、磁盤IO、內(nèi)存等資源很空閑
查看數(shù)據(jù)庫服務(wù)和監(jiān)聽服務(wù)均已啟動(dòng)
登錄數(shù)據(jù)庫sqlplus查看數(shù)據(jù)庫實(shí)例當(dāng)前狀態(tài)是mounted
Microsoft Windows [版本 6.1.7601]

版權(quán)所有 (c) 2009 Microsoft Corporation。保留所有權(quán)利。
C:\Users\Administrator>sqlplus / as sysdba
SQL*Plus: Release 11.2.0.1.0 Production on 星期三 8月 2 13:48:40 2017
Copyright (c) 1982, 2010, Oracle. All rights reserved.
連接到:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL> select status from v$instance;
STATUS
------------
MOUNTED
--嘗試open數(shù)據(jù)庫報(bào)ORA-01154，日志如下：
SQL> alter database open;
alter database open
*
第 1 行出現(xiàn)錯(cuò)誤:
ORA-01154: 數(shù)據(jù)庫忙?，F(xiàn)在不允許打開, 關(guān)閉, 裝載和卸裝

接下來，重啟了操作系統(tǒng)的Oracle實(shí)例服務(wù)，然后重新啟動(dòng)，啟動(dòng)依然停留在“數(shù)據(jù)庫裝載完畢”，日志如下：
從 Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production

With the Partitioning, OLAP, Data Mining and Real Application Testing options(情況復(fù)雜) 斷開
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 例程已經(jīng)啟動(dòng)。
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
數(shù)據(jù)庫裝載完畢。
--觀察數(shù)據(jù)庫告警日志，12:59分?jǐn)?shù)據(jù)庫有ORA-00949，有實(shí)例進(jìn)程超時(shí)等待
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: 持有入隊(duì) [CF] 的時(shí)間過長 (超過 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數(shù)據(jù)庫被重啟，日志如下（猜想是主機(jī)重啟后的自動(dòng)重啟）
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連接報(bào)錯(cuò)：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: 正在打開, 關(guān)閉, 裝載或卸裝數(shù)據(jù)庫
Wed Aug 02 13:48:39 2017
TNS-12535: TNS: 操作超時(shí)
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: 正在打開, 關(guān)閉, 裝載或卸裝數(shù)據(jù)庫
再看自己重啟后的告警日志
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 recoveryof 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
從啟動(dòng)告警日志可以看到，數(shù)據(jù)庫肯定被異常終止過，當(dāng)前數(shù)據(jù)庫增在從redo日志恢復(fù)，此時(shí)需要耐心等待；再次詢問實(shí)施同事并讓其關(guān)閉遠(yuǎn)程的所有oracle客戶端程序。
然后等待20分鐘，數(shù)據(jù)庫完成了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
--在數(shù)據(jù)庫open的過程中，本地登錄sqlplus查看會(huì)話信息，數(shù)據(jù)庫確實(shí)開啟了并行進(jìn)程恢復(fù)：
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行。

數(shù)據(jù)庫完成open后查看數(shù)據(jù)庫狀態(tài)，數(shù)據(jù)庫恢復(fù)正常：
SQL> startup
ORACLE 例程已經(jīng)啟動(dòng)。
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
數(shù)據(jù)庫裝載完畢。
數(shù)據(jù)庫已經(jīng)打開。
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>

注意：本次故障中，需要實(shí)施同事明白Oracle數(shù)據(jù)庫的啟動(dòng)和關(guān)閉過程；重啟操作系統(tǒng)前，需要先關(guān)閉oracle監(jiān)聽、確定當(dāng)前數(shù)據(jù)庫中沒有執(zhí)行計(jì)劃任務(wù)或存儲(chǔ)過程、
關(guān)閉oracle數(shù)據(jù)庫實(shí)例、關(guān)閉oracle數(shù)據(jù)庫服務(wù)、重啟操作系統(tǒng)，如果不是這個(gè)順序，就可能導(dǎo)致數(shù)據(jù)文件損壞，數(shù)據(jù)庫啟動(dòng)需要恢復(fù)而打開過程很慢，需要耐心等待。

以上是“ORACLE中startup報(bào)錯(cuò)的示例分析”這篇文章的所有內(nèi)容，感謝各位的閱讀！希望分享的內(nèi)容對(duì)大家有幫助，更多相關(guān)知識(shí)，歡迎關(guān)注創(chuàng)新互聯(lián)行業(yè)資訊頻道！

當(dāng)前題目：ORACLE中startup報(bào)錯(cuò)的示例分析
網(wǎng)站路徑：http://bm7419.com/article22/igojcc.html

成都網(wǎng)站建設(shè)公司_創(chuàng)新互聯(lián)，為您提供網(wǎng)站導(dǎo)航、ChatGPT、做網(wǎng)站、關(guān)鍵詞優(yōu)化、微信小程序、定制開發(fā)

聲明：本網(wǎng)站發(fā)布的內(nèi)容（圖片、視頻和文字）以用戶投稿、用戶轉(zhuǎn)載內(nèi)容為主，如果涉及侵權(quán)請盡快告知，我們將會(huì)在第一時(shí)間刪除。文章觀點(diǎn)不代表本網(wǎng)站立場，如需處理請聯(lián)系客服。電話：028-86922220；郵箱：631063699@qq.com。內(nèi)容未經(jīng)允許不得轉(zhuǎn)載，或轉(zhuǎn)載時(shí)需注明來源：創(chuàng)新互聯(lián)

猜你還喜歡下面的內(nèi)容