Thursday, July 31, 2014

Cloning a New Virtual Machine from Existing VM Box

Follow below Steps to clone virtual machine from existing VM Box.

Here I am cloning VM Box PrimDB to TestDB.

Open command prompt and run below steps.

C:\Users\Chowdari> cd C:\Program Files\Oracle\VirtualBox
C:\Program Files\Oracle\VirtualBox> VBoxManage clonehd D:\VirtualMachines\PrimDB\PrimDB.vmdk C:\VirtualMachines\TestDB\TestDB.vmdk

Create the "TestDB" virtual machine in VirtualBox as same way like "PrimDB", with the exception of using an existing "TestDB.vmdk" in virtual hard drive. Follow below process.

Choose "Use existing hard disk" and select "TestDB.vmdk" from path C:\VirtualMachines\TestDB\TestDB.vmdk

Done cloning. Now you can use TestDB Virtual Box.

Hope this will help you... :)

Best Regards,

Sunday, July 27, 2014

Script: Some Useful Oracle Database Monitoring Commands for DBA's - PART1

Find Blocking session:

select INST_ID,sid,serial#,username,status,BLOCKING_SESSION_STATUS,
 from gv$session a where BLOCKING_SESSION  IS NOT NULL;

Find Blocked Object:

select c.owner,c.object_name,c.object_type,b.sid,b.serial#,b.status,b.osuser,b.machine
from gv$locked_object a ,gv$session b,dba_objects c
where b.sid = a.session_id and a.object_id = c.object_id;

Identify the Hung Materialized View: 

select VS.INST_ID,VL.SID||','||VS.SERIAL#,VS.USERNAME,ao.object_name,
'alter system kill session '''||vl.SID||','||vs.SERIAL#|| ',@'||vs.inst_id ||''' IMMEDIATE;'
  from GV$LOCK VL, Gv$session VS, all_objects ao
 where vl.type = 'JI' and vl.Lmode = 6 and VS.SID=VL.SID
 and VS.INST_ID=VL.inst_id and vl.ID1=ao.object_id;

Find Pending Transaction:

select * from pending_trans$ where state='prepared'
select 'commit force '''||local_tran_id||''';' from pending_trans$ where state='prepared'
execute DBMS_TRANSACTION.PURGE_LOST_DB_ENTRY('transanction_id');


select inst_id,count(1)  from gv$session g
where type != 'BACKGROUND' and status='ACTIVE' GROUP BY INST_ID order by inst_id;

Active Session Count Excluding Background Sessions:

select inst_id,username,count(1)   from gv$session
 where type != 'BACKGROUND' and status='ACTIVE' and username is not null
 group by inst_id,username order by count(1) desc;

Program-wise Session count:

select inst_id,program,count(1)
from gv$session where type<>'BACKGROUND' AND USERNAME IS NOT NULL
group by inst_id,program order by 2 desc;

w3wp.exe sessions - Worker Process:

select inst_id,machine,count(1) from gv$session
where program='w3wp.exe' and status='INACTIVE'
group by inst_id,machine
order by 2;

User Session Consuming Concurrency, Cluster, User I/O:

Select inst_id "Inst",SID||','||serial# "SidS#",username,PROGRAM,sql_id,wait_class,status,machine,terminal,logon_time
from gv$session where type != 'BACKGROUND'   and status = 'ACTIVE'
and username is not null and wait_class in
('Concurrency', 'System I/O', 'User I/O','Network','Apllication')
order by inst_id, username;

Find FRA Size:

select name,
       space_limit / 1024 / 1024 / 1024 as Total_size,
       space_used / 1024 / 1024 / 1024 as Used,
       SPACE_RECLAIMABLE / 1024 / 1024 / 1024 as reclaimable,
       NUMBER_OF_FILES as "number"

Thats it..Hope this will help you.. :)

Best Regards,

Saturday, July 12, 2014

AIOUG (All India Oracle User Group) Conference Sangam 14

The Oracle user conference, Sangam 14 is being conducted by AIOUG (All India Oracle User Group) at  Bangalore on 7th, 8th and 9th November 2014.

More details for this event go to

Keep in touch with AIOUG announcements by joining social network channels.

Have a nice day!!! :-)

Best Regards,