Session Related Scripts

How to kill inactive sessions logged in more than 15 minutes.

Many a times, we get request from users to kill the sessions which are recently logged in their database. You can use below query to check sessions logged in more than 15Minutes

SELECT 'alter system kill session (' || SID || ',' || SERIAL# || ') immediate;'FROM V$SESSION WHERE (USERNAME = 'WATSON' AND STATUS = 'INACTIVE' and seconds_in_wait > 900);

'ALTER SYSTEM KILL SESSION('||SID||','||SERIAL#||');'                              
--------------------------------------------------------------------------------
alter system kill session (531,2378) immediate;                                              
alter system kill session (613,3053) immediate;                                              
alter system kill session (762,351) immediate;                                              
alter system kill session (843,5830) immediate;                                              
alter system kill session (126,116) immediate;                                              
alter system kill session (456,1333) immediate;           

Below are some more scripts related to Sessions

To Check the session details

=========================================

select sid,serial#,to_char(logon_time,'DD-MON-YY HH24:MMS') "StartTime",status,action

action from  v$session where SID = &&SID;

Wait Event for specific  SID :

=================

select sid,event,wait_time,state from v$session_wait where sid='&v_sid' order by wait_time;

Inactive Sessions Details

====================

set lines 200 pages 2000

col program for a30

col machine for a23

col username for a10

col osuser for a22

col schemaname for a10

col LOGON_TIME for a22

select sid,serial#,machine,program,osuser,username,schemaname,status,to_char(logon_time, 'dd-mon-yyyy hh24:mi:ss') as "logon_time" from v$session

where username is not null and username not in ('SYS','SYSTEM') order by logon_time;

Below Query spool inactive session details to kill:

=========================================

set head off

spool kill_sql.sql

select 'alter system kill session '||''''||SID||','||serial#||''''||' immediate;' from v$session where sid IN

(select SID from v$session where action like 'FRM:%' and status = 'INACTIVE' and logon_time > sysdate -1 );

spool off

=========================================

To find details of frond end users using SID

=============================================================

SELECT b.SID SID, c.spid,b.serial# "Serial#", d.user_name "User Name", logon_time,

ROUND ((SYSDATE - logon_time) * 24, 2) "session_time(HR)", status

FROM fnd_logins a, v$session b, v$process c, fnd_user d

WHERE b.paddr = c.addr

AND a.pid = c.pid

AND a.spid = b.process

AND d.user_id = a.user_id

AND (d.user_name = 'USER_NAME' OR 1 = 1)

AND ((SYSDATE - logon_time) * 24) > 10

AND b.SID=&Session_id

ORDER BY d.user_name, logon_time;

======================================

Locked Session Details:

======================================

col "logon" for a16

col username for a12

col spid for 99999

col osuser for a10

col machine for a15

col sid for 999999

col program for a25

col module for a20

select a.username,a.sid,a.serial#,a.process ,b.spid,

to_char(a.logon_time,'dd:mm:yyyy hh24:mi')

"logon", a.status,a.osuser,a.machine,a.program,a.module from v$session a, v$process b

where a.paddr=b.addr and a.username is not null and a.sid=&sid

order by logon_time;

=========================================

20 ASM Realtime | Interview Questions | 3+ years

Download link : https://www.scribd.com/document/461028011/Asm-Interview-Scenarios-Oracle-dba-Help-Blog

Happy Learning ..!

How to check ORA Error on DB Server – Beginner Tip

ORA errors are always displayed with error code only.  Because it is difficult for Oracle to put every errors description in Alert log .

Now Assume , you are stucked in a situation..  you got an ORA error, but you are not aware of what to do now.

Aaaa- ha… !

Here I will tell you a quick tip –

Always use this utility before heading to Google and search errors

To check ORA errors description on database server itself, go to shell prompt and type as below.

General format :

$ oerr ora  < error_code >

Example  :

$ oerr ora 01555

Output :  

ORA-01555: snapshot too old: rollback segment number string with name "string" too small

Cause: Rollback records needed by a reader for consistent read are overwritten by other writers.

Action: If in Automatic Undo Management mode, increase the setting of UNDO_RETENTION. Otherwise, use larger rollback segments

Also read  :  Working with Redo Logs

ORA-01194: file 1 needs more recovery to be consistent

ORA-01194: file 1 needs more recovery to be consistent

ORA-01110: data file 1: '/u01/dump01/files/data/system01'

If you try to give “alter database open resetlogs” command after cloning the database  using cold backup you probably see this error.

To troubleshoot this, follow these steps

SQL>  recover database;
ORA-00283: recovery session cancelled due to errors
ORA-01610: recovery using the BACKUP CONTROLFILE option must be done
 

So, you must recover using the backed up controlfile . Why ? Because the controlfile has information of archives that need to be applied during recovery.

Lets do this

SQL>  recover database until cancel using BACKUP CONTROLFILE;
ORA-00283: recovery session cancelled due to errors

Viola…!!

Now shutdown the database

SQL>shut immediate

Go to $ORACLE_HOME/dbs and locate the pfile of respective database.

And  add below parameter in the pfile

 _allow_resetlogs_corruption=true

SQL> startup mount pfile= ‘$ORACLE_HOME/dbs/initDBNAME.ora’;

ORACLE instance started.
Total System Global Area 1069252608 bytes
Fixed Size                  2188408 bytes
Variable Size             436214664 bytes
Database Buffers          616562688 bytes
Redo Buffers               14286848 bytes
Database mounted.

SQL> select name,open_mode from v$database;

NAME      OPEN_MODE
--------- --------------------
TEST      MOUNTED

SQL> alter database open resetlogs;

Database altered.

SQL> select name,open_mode from v$database;

NAME      OPEN_MODE
--------- --------------------
TEST      READ WRITE

Done . . !!

Working with Redo Logs

Script to View Online RedoLog Information :

SQL> SELECT * FROM V$LOGFILE;

OR

SQL > SELECT a.group#

,a.thread# ,a.status grp_status

,b.member member ,b.status mem_status

,a.bytes/1024/1024 mbytes FROM v$log a, v$logfile b

WHERE a.group# = b.group#

ORDER BY a.group#, b.member;

Status for Online Redo Log of V$LOG View  :

CURRENT The log group is currently being written to by the log writer.

ACTIVE The log group is required for crash recovery and may or may not have been archived.

CLEARING The log group is being cleared out by an ALTER DATABASE CLEAR LOGFILE command.

CLEARING_CURRENT The current log group is being cleared of a closed thread.

INACTIVE The log group isn’t required for crash recovery and may or may not have been archived.

UNUSED The log group has never been written to; it was recently created.

Status of Online Redo Log File Members in V$LOGFILE View

INVALID The log file member is inaccessible or has been recently created.

DELETED The log file member is no longer in use.

STALE The log file member’s contents aren’t complete.

NULL The log file member is being used by the database.

How to Rename the Redolog files:

1 . Shut down the DB

2. Rename the redolog files on OS level (using ‘mv’ command in UNIX)

3. Startup mount

SQL> alter database rename file ‘<loc with prev file name> to <loc with current file name>;

Do the same for all renamed redolog files

SQL> alter database open;

How to Multiplex the redolog files:

To protect against a failure involving the redo log itself, Oracle Database allows a multiplexed redo log, meaning that two or more identical copies of the redo log can be automatically maintained in separate locations. For the most benefit, these locations should be on separate disks. Even if all copies of the redo log are on the same disk, however, the redundancy can help protect against I/O errors, file corruption, and so on. When redo log files are multiplexed, LGWR concurrently writes the same redo log information to multiple identical redo log files, thereby eliminating a single point of redo log failure.

Multiplexing is implemented by creating groups of redo log files. A group consists of a redo log file and its multiplexed copies. Each identical copy is said to be a member of the group. Each redo log group is defined by a number, such as group 1, group 2, and so on.

FOR EXAMPLE:

In group 1 all memebers will have same type of data, if one member is corrupted then another member is user for recovery

SQL> SELECT MEMBER FROM V$LOGFILE WHERE GROUP# = ‘1’;

SQL>ALTER DATABASE ADD LOGFILE MEMBER ‘<>loc with redolog file name> TO GROUP <group_num>;