Troubleshooting

 Troubleshooting

 Oracle troubleshooting is an advanced concept, but step-by step directions are available for proper troubleshooting of Oracle performance problems. 

 Always check the first error message - Oracle will frequently throw multiple errors, but it's always the first error that will lead you to the root cause of the problem

Gather Instance hang analysis data:

Oradebug utility:

           The SQL*Plus utility has an undocumented argument invoked as (-prelim) to allow you to connect to a hung Oracle database that is not accepting new connections. The “sqlplus –prelim” option allows you to access a hung database to quickly run an oradebug trace report.

$sqlplus –prelim / as sysdba

Sql>oradebug setmypid
Statement processed.

Sql>oradebug unlimit
 Statement processed.

Sql>oradebug hanganalyze 12
Hang Analysis in /oradata/appsdb/9.2.0/admin/apps_apps/udump/apps_ora_2614.trc

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 01652

 

Important Log files For monitoring

a)alert log file :=> background_dump_dest

b)listener log file:=> $ORACLE_HOME/network/log
c)user trace files:=> user_dump_dest
d)core trace file :=> core_dump_dest