Trace Files — 8b : More Performance Evaluation from Trace File

Continuing with the same table as in the previous example, but with the addition of(a) Statistics on the table(b) An Index on the CREATED column SQL> select count(*) from all_objects_many_list; COUNT(*)---------- 7197952SQL> SQL> select min(created) from all_objects_many_list;MIN(CREAT---------28-AUG-11SQL> SQL> select min(created), max(created) from all_objects_many_list;MIN(CREAT MAX(CREAT--------- ---------28-AUG-11 15-NOV-15SQL> SQL> Let’s examine the tkprof output first.

Using oracle hanganalyze tool of oradebug utility to analyze oracle hang

Sometime you may find that your SQL session is hanging or some application that accesses your database is hanging. The application that access an Oracle database basically creates a session to the database. And it is that session that will be hanging which to normal usr looks like the application is hanging.

Whether it is the SQL session/statement or an application that is hanging sometime it becomes difficult to find out what is causing the database to hang.

You may use the usual tools such as AWR (automatic workload repository) and ADDM (automatic database diagnostic monitor) to find out what is causing the database to hang. But many time the information you receive from this tool is not enough to determine what is causing it or to identify which is causing it.

Moreover if your database instance is hanging then you can not run AWR and ADDM.

In such case you can use a tool called hanganalyze. This tool is provided by oracle and is part of oradebug utility. This is very handy to find out exactly which session is causing the hang situation.

Here are the steps how you to you oracle hanganalyze tool:

Preparation:
Login as sysdba and run the following:

sqlplus / as sysdba
create user joe identified by joe;
grant create session to joe;
grant resource to joe;

Simulate a session hang (in this case row locking):
Login as user joe from two sessions. Lets refer them session 1 and session 2.

From session 1 do the following:

create table dept (
deptno number,
dname varchar2(20),
location varchar2(20));

insert into dept values (10, ‘Finance’,’London’);
insert into dept values (20, ‘HR’,’London’);

commit;

SQL> select * from dept;

DEPTNO DNAME LOCATION
———- ——————– ——————–
10 Finance London
20 HR London

update dept
set location=’New York’
where deptno = 10;

Now from session 2 try to update the same data by executing the code below:

update dept
set location=’Singapore’
where deptno = 10;

You will notice that the session is hanging. It is because the first update in session 1 has updated the same tow and the operation is not yet committed. So provide data consistency session 1 has locked the row and hence now session 2 needs to wait until session 1 commits or rollback;

Now run hanganalyze to find out exactly which session is blocking whom.
Login as sys user and find out the spid of the session that is hanged.

sqlplus / as sysdba

select a.sid, a.serial#, b.spid ospid, to_char(logon_time,’dd-Mon-rr hh24:mi’) Logintime
from gv$session a, gv$process b
where a.inst_id = b.inst_id and a.paddr = b.addr and status = ‘ACTIVE’;

Note down the ospid of the hanged session. Then run the following:

SQL> oradebug setospid [spid]
SQL> oradebug unlimit;
SQL> oradebug hanganalyze 3

This will generate a trace file which will contain the detail why the oracle database session is hanging. Even though the trace file will contain loads of information still it is quite easy to find out the real culprit.

Open the trace file and find out the session marked as “Chains most likely to have caused the hang:”. There you will see Chain 1 and Chain 2 which are basically session operations.
Going further down where these Chains are defined, you will see information such as session, serial#, blocking session, current SQL etc.

Soem extract of my trace file is as below:

Chains most likely to have caused the hang:
[a] Chain 1 Signature: ‘SQL*Net message from client’
Chain 1 Signature Hash: 0x38c48850
[b] Chain 2 Signature: ‘LNS ASYNC end of log’
Chain 2 Signature Hash: 0x8ceed34f

and section that provide chain detail

Chain 1:
——————————————————————————-
Oracle session identified by:
{
instance: 1 (dtcnmh.dtcnmh)
os id: 2653
process id: 36, oracle@SERV01A (TNS V1-V3)
session id: 770
session serial #: 11739
}
is waiting for ‘enq: TX – row lock contention’ with wait info:
{
p1: ‘name|mode’=0×54580006
p2: ‘usn
p3: ‘sequence’=0x507
time in wait: 59.699862 sec
timeout after: never
wait id: 26
blocking: 0 sessions
current sql: update dept
set location=’Singapore’

I hope this helps.

Performing Oracle RMAN Cold backup

Cold backup is done when database is not open. While performing cold backup using RMAN the database need to be in MOUNT mode. This is because RMAN needs the data file details which are available while database is in MOUNT mode.

Cold backup is also called consistent backup. This is because before bringing the database in MOUNT mode, the database is first shutdown with IMMEDIATE or TRANSACTIONAL option. This means that database sequence number are all synchronised.

Below are the steps to do RMAN cold backup to an external drive in Linux.

Step1: Mount the external drive
Step2: Run rman_cold_backup.sh in nohup.

The content of rman_cold_backup.sh is below:

#!/bin/sh
# Set Oracle Environment for DB
#. ~/.bashrc
. ~/.bash_profile

echo
echo “`date` – Started `basename $0`”
echo

export NLS_DATE_FORMAT=’DD-MON-YY HH24:MI:SS’
rman target /
run {
shutdown immediate;
startup mount;
allocate channel prmy1 type disk format ‘/media/full_backup_%d_%s_%p’;
allocate channel prmy2 type disk format ‘/media/full_backup_%d_%s_%p’;
allocate channel prmy3 type disk format ‘/media/full_backup_%d_%s_%p’;
allocate channel prmy4 type disk format ‘/media/full_backup_%d_%s_%p’;
BACKUP CURRENT CONTROLFILE format ‘/media/ctrl_file_%d_%s_%p’;
BACKUP AS COMPRESSED BACKUPSET DATABASE;
release channel prmy1;
release channel prmy2;
release channel prmy3;
release channel prmy4;
alter database open;
}
EOF
if [ $? -eq 0 ]
then
echo “=====================”
echo “RMAN Backup Completed”
echo “=====================”
else
echo “==================”
echo “RMAN Backup Failed”
echo “==================”
exit 1
fi

echo
echo “`date` – Finished `basename $0`”
echo

As this backup is a cold backup the, the archive log files will be not required to restore and recover the database. Hence archive log files are not added.

However if you wish to take backup of arrive logs as well you can change

BACKUP AS COMPRESSED BACKUPSET DATABASE;

To

BACKUP AS COMPRESSED BACKUPSET DATABASE PLUS ARCHIVELOG;

In the above script.

Remember to run the script in nohup.
Also remember to check there is enough space in the external drive.