Friday, December 25, 2009

How to check for blocking session in oracle?

It happens many a times that a user calls you and tells you that he has executed a query a long time back which is a very small update or delete statement and the Sql screen is in a hung state. In this case it is proably the user session is being blocked by some other session. So the easiest way to track this is using the v$session view in oracle 10g. This view has a field blocking_session, which will tell you what sid is blocking your session.
e.g
suppose user joe calls and tells you the same problem, his userid is XY123
-
sql>select sid,serial#,blocking_session grom v$session where username='XY123';

output

sql> 523,1234,321

so here we can see session 321 is blocking session 523 of joe.

I have always found this useful as i come across this situation many a times.

Sunday, December 20, 2009

Unix - whoami and who am i

In many unix books most of us have read these words or commands whoami and who am i..These commands are useful when you work in an environment where people have different login ids and you are using sudo access or are first logging thorugh a different name and then switching to another user.
suppose i login with username joe and then i use su - to switch to user pam. Now if i will use the command
$id
joe gid() uid()
$su - pam
password: *******
$
$id
pam gid() uid()
ok...now i will use whoami
$whoami
pam

ok and now i use who am i

$who am i
joe

So you see the difference. its very clear from the example.
Sometimes you might see many screens open, but you really don't know who had initially logged into the system . At that time these commands are very handy.

Saturday, December 19, 2009

Track long running queries in Oracle

It happens sometime or shall i say most of the time that your manager or user calls and asks the DBA ...the db is very slow..can you check what is running in the system or can you check for any long running queries in the database. In this case oracle has provided us a view call v$session_longops, this is a fantastic view and it really helps in checking out for long running queries. for a simple example just run the query -

select * from v$session_longops where sofar <> totalwork;

if it is RAC you should use gv$session_longops.

This is just a basic example and will give you lots of information about long queries running in the system, and will also tell you how much work is done and how much work is left..time shall i say..

Saturday, December 5, 2009

Oracle Database- Who is using my undo Tablespace??

It Happened once that i came in morning and logged onto the database. I was just checking tablespaces when i observed undo tablespace is totally full....so what was eatin up my undo..in such case oracle provides you a view called V$transaction that gives you information about transactions using Oracle db undo tablespace. This view can be joined with V$session and with help of this you can easily come to know which user is using your undo tablespace. In my case some developer has left running an update query which was to update a major table at night and had not committed..I called up the developer..and told him to either commit or rollback..he committed his transaction..and undo got freed..

Sunday, November 29, 2009

Tracing a User Seeion in Oracle - DBMS_SUPPORT

Many a times we need to trace a user session.

For this oracle has supplied a package called DBMS_TRACE.

To Trace a user session we can start Sql Tracing by using -

exec dbms_support.start_trace_in_session(sid,serial#,binds=>true,waits=>true);

Since we are setting waits=> true this will capture wait information of Sqls.

And Binds=> true will capture bind values.

To stop tracing -

exec dbms_support.stop_trace_in_session(sid,serial#)

Saturday, November 28, 2009

Oracle RAC with Veritas (SFRAC)

In one of the projects that we are handling, we have SFRAC. This is the first time i am workin on any setup that has Veritas Storage foundation for RAC implemented. I was much excited to work on something new , and after reading for sometime and going through veritas docs, i am somewhat comfortable in handling issues.
Will update this post more, in some time.

Friday, November 27, 2009

CRS Commands

CRS is cluster ready services. CRS controls oracle clusterware and services controlled by oracle cluster. For this there are CRS deamons that run continuously on both the nodes and monitor the operation of cluster.
In this post i will tell few basic commands to stop and start CRS.

To check whether crs deamons are running or not we use-

crsctl check crs

And if you have to check all services controlled by cluster, then use the command -

crs_stat -t

This will give you status of all services , In ideal conditions status is ONLINE and if service is down it will post the status as OFFLINE

Suppose i want to stop crs , in this case i will use -

crsctl stop crs

sometimes a service may get down due to some error , this can be started by using -

crs_start servicename

Thursday, November 26, 2009

Some More RAC Commands

Today i will share how to shutdown single instance in an RAC cluster, We Will again be using SRVCTL for this..

To shutdown a single instance or one node of RAC cluster we use the command -

srvctl stop instance -d databasename -i instancename


To start instance -

srvctl start instance -d databasename -i instancename

This is pretty much useful. Sometimes your system team may ask to to shutdown the database on one node, for some maintenance activity. At that time these commands are very handy.

Sometimes SRVCTL may not work , and may give error. This can happen if GSD is down or because of some cluster ware error. In this case we can use the conventional method of shutting down the instance . Just export ORACLE_SID=MYSID and use shutdown command.

Wednesday, November 25, 2009

srvctl utility to start and stop Oracle RAC Database

To Manage an RAC Database , srvctl command line tool is utilized. It is the easiest way to start and stop database instances in an RAC Cluster. Much can be performed with srvctl command line utility.

I will just post down some examples -

1. To check the status of Database -

srvctl status database -d MYSID

2.To Start entire database (Both the instances of cluster) -

srvctl start database -d MYSID

3. To stop Entire database (Both the instances of cluster) -

srvctl stop database -d MYSID

I will update this with some more commands soon!!

If you are new to RAC you may initially find it hard to remember these commands, so the easiest way is simply put them in shell script.
This is the way i used to do it when for the first time i had worked on an RAC Database.

Just create scripts like start.sh, stop.sh and put your commands in these shell scripts. So that every time you just need to run the shell scripts. :-)

Tuesday, November 24, 2009

New To Oracle RAC??

Oracle RAC, this is an Awesome offering of Oracle. In my DBA Experience most of the experience is On RAC, u may say i got lucky from the starting. For those who really want to have a feel of this , should try Implementing this On Vmware. It Really works well!! You can try all the stuff like adding nodes,deleting nodes, patching etc..just do a google search..but the best document is by vincent chan, i hope its still available on Oracle Website..

Hi

Hey I have finally joined blogger....:-)