Quick Troubleshooting Steps for Daily Activities

How to detect locking issues in Database ?

-During this,, you need to find Locking sessions and Kill 1Session among those

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

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

                  56                             83

Process for Killing the session

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

Sql> alter system kill session '56,83';

How to Relink the oracle binaries

-Run the below command

$ORACLE_HOME/bin/relink all

How to gather Instance hang analysis data

-Use the 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

What will you do, If sqlplus executable is corrupted then

-Run these below commands

$cd $ORACLE_HOME/sqlplus/lib

$make –f ins_sqlplus.mk install

What will you do, 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

-run these below command

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

How to check the detailed error message

-using the ‘oerr’ Utility

$oerr ora 0600

What is 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

  The Alert log file are in two format

  Log.xml

  Alert_<SID>.log

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

 -below are the commands 

  $ adrci

 

  Adrci>show alert

  Adrci>help

  Adrci>show incident

Follow us on Facebook, and stay updated

See Also Health Check – When the Application Users complaints for an issue, what to do ? 

Server Maintenance Tasks for a DBA

General Tips to Prepare for an Oracle DBA Job Interview  *HOT*