Shulou(Shulou.com)05/31 Report--

This article mainly introduces the example analysis of startup error report in ORACLE, which is very detailed and has certain reference value. Friends who are interested must finish it!

The oracle database cannot be connected remotely. The connection error is shown in the figure:

Operating system: windows server2008 R2

Database version: oracle 11.2.0.1

At first glance, it seems that the database is in the process of opening or closing. Check the alarm log. The last database startup occurred at 10:50, and some of the alarm logs are as follows:

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.

Then, the implementation colleague said that he had startup the database at noon, but the database stopped: "the database is loaded." For a long time, as in the picture:

Then, the startup will report the ORA-03113, if shown:

Then, he added, after the database failed to start, he then restarted the database server host, but the problem remained.

Here's how I handle it:

Log in to the database server

Check the status of the host, CPU, disk IO, memory and other resources are idle

View database service and monitoring service have been started

Log in to the database sqlplus to view the current status of the database instance is mounted

Microsoft Windows [version 6.1.7601]

Copyright (c) 2009 Microsoft Corporation. All rights reserved.

C:\ Users\ Administrator > sqlplus / as sysdba

SQL*Plus: Release 11.2.0.1.0 Production on Wednesday August 2 13:48:40 2017

Copyright (c) 1982, 2010, Oracle. All rights reserved.

Connect to:

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

-- try to report the open database to ORA-01154. The log is as follows:

SQL > alter database open

Alter database open

*

An error occurred on line 1:

ORA-01154: the database is busy. Opening, closing, loading and unloading are not allowed now

Next, restart the Oracle instance service of the operating system, and then restart it, and the startup still stays at "database loading completed". The log is as follows:

From Oracle Database 11g Enterprise Edition Release 11.2.0.1.0-64bit Production

With the Partitioning, OLAP, Data Mining and Real Application Testing options (complicated) disconnect

C:\ Users\ Administrator > sqlplus / as sysdba

SQL*Plus: Release 11.2.0.1.0 Production on Wednesday August 2 13:58:58 2017

Copyright (c) 1982, 2010, Oracle. All rights reserved.

Connected to an idle routine.

SQL > startup

The ORACLE routine has been started.

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

The database is loaded.

-- observe the database alarm log. At 12:59, there is ORA-00949 in the database and there are instance processes waiting for timeout.

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: holding enrollment [CF] for too long (more than 900s) (by '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 the database is restarted, and the log is as follows (presumably automatic restart after host restart)

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 connection error was observed: 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: opening, closing, mounting, or unmounting the database

Wed Aug 02 13:48:39 2017

TNS-12535: TNS: operation timed out

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) (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: opening, closing, mounting, or unmounting the database

Then take a look at the alarm log after rebooting.

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

As you can see from the startup alarm log, the database must have been terminated abnormally. As the current database is recovering from the redo log, you need to wait patiently. Ask the implementation colleague again and ask him to close all remote oracle client programs.

Then wait 20 minutes, and the database completes the open operation.

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

-in the process of database open, log in to sqlplus locally to view the session information, and the database does enable parallel process recovery:

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)

4828 ORACLE.EXE (P022)

51 6024 ORACLE.EXE (P012)

52 5824 ORACLE.EXE (P023)

100 5880 ORACLE.EXE (P013)

4624 ORACLE.EXE (P024)

148 6048 ORACLE.EXE (P014)

150 2452 ORACLE.EXE (P025)

198 6028 ORACLE.EXE (P000)

200 3060 ORACLE.EXE (P026)

251st 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 (P0.05)

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 lines have been selected.

Check the database status after the database completes open, and the database returns to normal:

SQL > startup

The ORACLE routine has been started.

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

The database is loaded.

The database is already open.

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 >

Note: in this failure, colleagues need to understand the startup and shutdown process of Oracle database; before restarting the operating system, you need to turn off oracle snooping, make sure that no planned tasks or stored procedures are executed in the current database,

Shutting down the oracle database instance, shutting down the oracle database service, and restarting the operating system, if not in this order, may lead to data file corruption. The database startup needs to be recovered and the opening process is slow, which requires patience.

The above is all the contents of the article "sample Analysis of startup errors in ORACLE". Thank you for reading! Hope to share the content to help you, more related knowledge, welcome to follow the industry information channel!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.