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
$ 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