Oracle Database hang instance analysis

Oracle Database hang instance analysis

Symptom:

Neither common users nor sysdba can log on, resulting in service interruption.

Analysis process:

1. Do hanganalyze and systemstate dump first

$ Sqlplus-prelim "/as sysdba"

SQL> oradebug setmypid

Statement processed.

SQL> oradebug hanganalyze 3

Statement processed.

SQL> oradebug hanganalyze 3

Statement processed.

SQL> oradebug tracefile_name

/U01/app/Oracle/diag/rdbms/db11/db11/trace/db11_ora_2495.trc

SQL>

2. Analyze the trace file

There are 76 sessions blocked by session 494, while session 494 is waiting for the shared pool...

Sessions between 494,496,519 may be independent of each other, and there may be mutual blocking concerns.

Continue log analysis:

"Adjlist" indicates nodenum. Therefore, session 494 is blocked by session 598, and session 496 is also blocked by session 597.

Therefore, sessions with process numbers 553382 and sid = 598 are the source of blocking when the database is hang.

What does session 598 do? It needs to be analyzed from systemstate dump, but systemstate dump is not analyzed before the system restarts.

The process in oracle is either a foreground process or a background process. When oracle is started, the ID of the background process is recorded,

The alter log shows that:

Based on MMAN process speculation, it is related to dynamic SGA adjustment. After the dynamic SGA management is disabled, the system returns to normal and there is no hang.