Troubleshooting

 

Troubleshooting

 

 

How to gather Instance hang analysis data

Oradebug utility

$sqlplus –prelim / as sysdba

Sql>oradebug setmypid

Sql>oradebug unlimit

Sql>oradebug hanganalyze 3

 

Open another terminal

$sqlplus –prelim / as sysdba

Sql>oradebug setmypid

Sql>oradebug unlimit

Sql>oradebug dump systemstate 256

 

How to detect locking issues

Sql>select a.SID "Blocking Session." ,b.SID "Blocked Session" from v$lock a, v$lock b 

       where a.SID != b.SID and a.ID1 = b.ID1  and a.ID2 = b.ID2  and b.request > 0  and a.block = 1;

                  

   Blocking Session.      Blocked Session

                    -----------------            ---------------

                  38                             24

 

Killing the session

Sql>select sid,serial# from v$session where sid=38;

Sql> alter system kill session '38,49';

 

Relink oracle binaries

$ORACLE_HOME/bin/relink all

 

If sqlplus executable is corrupted then

 $cd $ORACLE_HOME/sqlplus/lib

$make –f ins_sqlplus.mk install

 

If lsnrctl utility is corrupted then

$ORACLE_HOME/network/lib

$make -f ins_net_server.mk install

 

IF oracle, exp, imp, sqlldr, tkprof utilites are corrupted then

$ cd $ORACLE_HOME/rdbms/lib
$ make -f ins_rdbms.mk install

 

How to check the detailed error message

$oerr ora 1578

 

ADR = Automatic Diagnostic Repository

 

* in 11g user_dump_dest,core_dump_dest,background_dump_dest are replace by diagnostic_dest

 

Sub-dirs under diagnostic_dest 

       

  Alert rdbms incidents

 

  Alert log file in two format

  Log.xml

  Alert_<SID>.log

 

  adrci Utility  to see the content of alert log or incident details

  $adrci

  Adrci>show alert

  Adrci>help

  Adrci>show incident