Find sessions performing sort operations : TEMP Tablespace occupants

Most of the time, user will ask you to check long running sessions in database and you will figure out the query which the session is performing sort/join operation – reading the query you can say the query is related to sorting. But  you will also see the high usage of Temporary segments or will see wait events such as “Direct Path Temp Read” Or “Direct Path Temp Write”.

To rectify this you can use views such as  V$SORT_USAGE and V$SORT_SEGMENT to identify the other sessions performing sort operation

Oracle provides two basic dynamic performance views for online monitoring of temporary tablespace usage.

These are v$sort_usage to query online sessions using temporary segments and v$sort_segment to query the size of the temporary segments in the database.

V$SORT_USAGE has below main columns  which can be useful here:

USERNAME       –              Session User name.

SQL_ID                –              unique identifier of the SQL that requires the sort.

EXTENTS           –              number of extents in the temporary segment being used by particular sessions.

BLOCKS              –              number of blocks in the temporary segment being used by particular sessions.

SESSION_ADDR – address of the session, can be used to identify the session in V$SESSION according to the SADDR column.

--Use below query to find the sessions consuming temp segments

SELECT S.sid || ‘,’ || S.serial# sid_serial, S.username, S.osuser, P.spid, S.module,
P.program, SUM (T.blocks) * TBS.block_size / 1024 / 1024 mb_used, T.tablespace,
COUNT(*) statements
FROM v$sort_usage T, v$session S, dba_tablespaces TBS, v$process P
WHERE T.session_addr = S.saddr AND S.paddr = P.addr
AND T.tablespace = TBS.tablespace_name GROUP BY S.sid, S.serial#, S.username, S.osuser, P.spid, S.module,
P.program, TBS.block_size, T.tablespace ORDER BY sid_serial;
 

This will give the sid and serial# which be used to find current query that is taking temp segment using view v$sqltext and passing sql_id of the sid and serial#

using  V$SORT_SEGMENT

SELECT A.tablespace_name tablespace, D.mb_total,
SUM (A.used_blocks * D.block_size) / 1024 / 1024 mb_used,
D.mb_total-SUM (A.used_blocks * D.block_size) / 1024 / 1024 mb_free
FROM v$sort_segment A,
( SELECT B.name, C.block_size, SUM (C.bytes) / 1024 / 1024 mb_total
FROM v$tablespace B, v$tempfile C
WHERE B.ts#= C.ts# GROUP BY B.name, C.block_size ) D WHERE A.tablespace_name = D.name
GROUP by A.tablespace_name, D.mb_total;

How to identify the applied patche-set of your Oracle Home

Many a times you will need to find the patch level of your database.

The easiest way to get this information is from the OPatch utility.  The OPatch utility can be found under $ORACLE_HOME/OPatch directory.

The command below will list out all the currently applied patches to your Oracle environment using the files in the oraInventory.

Example:

$ORACLE_HOME/OPatch/opatch lsinventory

The same information can be seen from dynamic views

Example:

SQL>select * from sys.v$version ;

SQL>select * from sys.registry$history;

Worth Sharing: Things you should NOT do when you are angry

 Things you should NOT do when you are angry  😼

*Hit your phone/gadgets on the wall.

*Punch the door/wall multiple times until your knuckles start to bleed.

*Verbally abuse people who love you and care for you, online or offline.

(All these things will only add up to the pain and will hurt you even more later.)

Things you can do when you get angry 😊

*Punch a pillow/cushion/a pile of blankets. (Find a punching bag if these are too soft for you).

*Use this anger to better yourself and do a ton of crunches/pushups/your fav. exercise at home/gym.

*Go on a long drive, preferably alone and notice the beauty of nature that surrounds you. You can also simply walk or run.

*Grab a pen and write the hell out of your thoughts. It's not childish. It's one of the smartest things you can do to yourself. Don't hesitate, no one except you is going to see that.

(Vent your anger. Don't contain it. Anger is like poison. You need to let it You need to let it out and make sure you don't hurt yourself in the process.)

Best of luck. Share to others in need. 💙 😊

Happy New Year
May this new year bring all your dreams and happiness come true.. Stay Blessed

Steps to Schedule any RMAN Jobs In Windows Server

This post is about the backup configuration made in Windows server and what are the backup strategies mainly used in most of production databases.

Backup strategy followed in most of  production Databases.
1) Weekly RMAN full backup
2) Daily Archive log backup
3) Daily incremental backup

In windows server environment, we use “scheduler” (such as cronjob in UNIX) to schedule any job.

The important steps to configure a scheduler are as follows :

Taking Database backup weekly(incremental level 0):
First create a cmd file (command file ) in windows: 

Let us create a file name week_backup.cmd using a text file editor(any editor) write the rman script to take the backup.

Assume, I want to take full backup (i.e, LEVEL 0 Backup)

-        Then the script for taking the weekly Level 0 backup would be like this, (here controlfile autobackup is off(default)).

Step 1.

week_backup.cmd

RMAN>run{ 
Allocate channel ch1 type disk format '\path of taking backup_\%d_data_%U'; 
Backup incremental level=0 database tag=’full_backup'; 
Release channel ch1; 
Allocate channel t1 type disk format '\ backup location \%d_ctrl_%U'; 
Backup current controlfile; 
Release channel t1; 
}

Step 2.

Create the .bat file (Batch file is for batch job execution by scheduler)
Let us create a batch file week_backup.bat

The script looks like as below:

week_backup.bat 

rman target sys/db1@db1

cmdfile=\Path of cmd file\week_backup.cmd   <-------- you need to mention the file location we created at Step 1 above.
log=E:\Path of log file\Full_DB_Backup_%date:~4,2%_%date:~7,2%_%date:~10%.log
The file will be created as follows
Full_DB_Backup_<Current_Date>.log

This batch file is used executing this file through schedule.

Step 3.

Here comes the scheduling part. We have to use scheduler for scheduling job:

Goto- Start Menu>

 control panel >scheduler tasks >Add scheduler task >command prompt >Perform this task > weekly >choose day and timings when you want to run this job weekly >specify your administrator username and password >finish
 

Configuring  Database backup Daily(incremental LEVEL 1)

For this activity, all the above steps remains same, but the scripts and scheduler task option changes here:

1. backup_incr_1.cmd  

run{
allocate channel ch1 type disk format '\path of backup location_\%d_data_%U';
backup incremental level=1 database tag=Level_1_Backup';
release channel ch1;
}

2. backup_incr_1.bat 

rman target sys/db1@DBNAME 
cmdfile=\Path of cmd file created in Step1 above\ backup_incr_1.cmd 
log=E:\Logfile location\Level_1_Backup_%date:~4,2%_%date:~7,2%_%date:~10%.log

Now, Configure the scheduler as we did above in Step 3 and  simply  change the Scheduler option > as ‘daily’ and timings whichever  you want.

 -

For information on  UNIX System Commands you can check an article here – UNIX for DBA's

Click here for RMAN recovery scenarios

Everything comes to us that belongs to us, if we create the capacity to receive it

-Rabindranath Tagore