kill session in RAC

Login to all the nodes/machines and try to query :

select sid, serial# from v$session where sid='@@@';

kill the session :

alter system kill session 'sid,serial#' immediate;

select status from v$session where sid='@@@';
If the status says INVALID then ignore, else

while killing the session, if the status says "ORA-00031: session marked for kill", the status will show "killed". In this case, try to kill the session in the OS.

Run the below query to get the spid along with other useful details.

SET LINESIZE 100
COLUMN spid FORMAT A10
COLUMN username FORMAT A10
COLUMN program FORMAT A45

SELECT s.inst_id,
s.sid,
s.serial#,
p.spid,
s.username,
s.program
FROM gv$session s
JOIN gv$process p ON p.addr = s.paddr AND p.inst_id = s.inst_id
WHERE s.type != 'BACKGROUND';

Get the spid for the appropriate sid and in the OS level, issue the following command.

kill -9 spid

Sometimes, you would get the below error :

ORA-00027 cannot kill current session

In this case, make sure you and the users have logged out of the session.

alert log file

SQL>show parameter background dump dest;

In OS prompt, go to the location, you can find the alert log file named something similar to the one below :

alert_instancename.log

Open it with either more or tail command.

read trace file

SQL>show parameter background dump dest;

In OS prompt, go to the location.
you will find .trc and .trm files which are not in human readable format. To read the trace file, do the following.

$tkprof .trc filename

The output file will be created with .prf

open the .prf file using more or tail command.

check whether a database is primary or standby

SQL> select DATABASE_ROLE from v$database;

DATABASE_ROLE
-------------------------------
PRIMARY

SQL> select DATABASE_ROLE from v$database;

DATABASE_ROLE
-------------------------------
PHYSICAL STANDBY

uptime command

uptime

The uptime command prints the current time, the length of time the system has been up, the number of users online, and the load average. The load average is the number of runnable processes over the preceding 1-, 5-, 15-minute intervals. The output of the uptime command is, essentially, the heading line provided by the w command.

Example
8:11pm up 6 day(s), 16:09, 1 user, load average: 0.23, 0.25, 0.24

free space and used space in a database

free space
----------
SQL> SELECT sum(bytes)/1024/1024/1024 "free space in GB"
2 FROM dba_free_space;

used space
----------
SQL> SELECT sum(bytes)/1024/1024/1024 "used space in GB"
2 FROM dba_segments
3 /

Upload agent

./emctl upload agent

flush shared pool

SELECT PROPNAME, PROPVALUE FROM SERVER_CONFIG_PROPERTIES;

Normally this query returns several rows of data for a user. However, when the bug is in affect it generates this error: “ORA-00942: table or view does not exist”

To fix this issue please login to the database as the sys user and flush the shared pool using this command:

alter system flush shared_pool;

Run the above command once or twice as per the user's requirement.

spikes

This image looks as if there is a spike but actually the database is looking good.



This image is the 7 day view which clearly shows that there is no spike. This image and the one before this belong to the same database during same period. This looks no different than any other day.





These are the spikes which are worrisome.

row level contention or lock

SELECT sid, sql_text
FROM v$session s
LEFT JOIN v$sql q ON q.sql_id=s.sql_id
WHERE state = 'WAITING' AND wait_class != 'Idle'
AND event = 'enq: TX - row lock contention';

/home filesystem

if /home filesystem fills up, the DBA has to take care, not the unix sa