How To Identify Database Idle Sessions

The below scripts will identify the Database Idle Session .When on firing the below the scripts, it will prompt for the number of minutes the session is idle for.

 
SQL> select 
sid, username, status,
 to_char(logon_time,’dd-mm-yy hh:mi:ss’) “LOGON”,
floor(last_call_et/3600)||’:'||  floor(mod(last_call_et,3600)/60)||’:'||
mod(mod(last_call_et,3600),60) “IDLE”,
program
from   v$session
where  type=’USER’
and   (LAST_CALL_ET / 60) > &minutes
order by last_call_et;

Enjoy    :-) 

Difference Between OBSOLETE AND EXPIRED Backup

RMAN considers backups of datafiles and control files as obsolete, that is, no longer needed for recovery, according to criteria that we specify in the CONFIGURE command. We can then use the REPORT OBSOLETE  command to view obsolete files and DELETE OBSOLETE to delete them .

For ex  :  we set our retention policy to redundancy 2. this means we always want to keep at least 2 backup, after 2 backup, if we take an another backup oldest one become obsolete because there is 3 backup and we want to keep 2. if our flash recovery area is full then obsolete backups can be overwrite.

A status of "expired" means that the backup piece or backup set is not found in the backup destination or missing .Since backup info is hold in our controlfile and catalog . Our controlfile thinks that there is a backup under a directory with a name but someone delete this file from operating system. We can run crosscheck command to check if these files are exist and if rman found a file is missing then mark that backup record as expired which means is no more exists.

Enjoy   :-) 

Daily Tasks Of A Database Administrator

The Database Administrator is one of the most difficult and critical positions to fill and retain. DBAs must be able to react, communicate, and plan across many different business functions.  Here we will find a set of common tasks needed to be performed daily by any DBA. The tasks are as below :  

  1.) Regular Monitoring of The free space in Database.

  2.) Verify instance status

  3.) Check alerts logs, Trace files and Listener Logs

  4.) Check configured metrics

  5.) Tablespace Usage

  6.) Check RMAN backups

  7.) Check users User sessions

  8.) Check memory usage

  9.) Check network load

10.) Object modifications

11.) Check User sessions

12.) Redo log status

13.) Analyzing the performance of theDatabase

14.) Checking the long running queries on the database

15.) Monitoring the Top SQL Statements

16.) Monitoring Sessions

17.) Monitoring System Statistics

18.) Are the Oracle Names and LDAP Servers up and responding to requests.

19.) Check to ensure that no objects exist in the database with the status ‘INVALID’

Enjoy    :-) 

Does the Optimizer need a hint?

Today I have gone through a very useful post regarding the use of the hints in Oracle.Though there are lots of articles on the internet but Oracle Corporation has not documented the vast majority of hints.Here i am providing the link of Jonathan Lewis blog who is one of the Master in the Oracle Tunning .

http://jonathanlewis.wordpress.com/2008/05/02/rules-for-hinting/

I’ve written a number of notes about hinting in fact, by using at the “Select Category” list to the right, I see that I have (so far) tagged 26 different articles (and this will be the 27th) with the hints tag. So I’ve decided it was time that I made clear my basic guidelines on safe hinting, as follows:

1.) Don’t
2.) If you must use hints, then assume you’ve used them incorrectly.

3.) On every patch or upgrade to Oracle, assume every piece of hinted SQL is going to do the wrong thing. Because of (2) above; you’ve been lucky so far, but the patch/upgrade lets you discover your mistake.

4.) Every time you apply some DDL to an object that appears in a piece of hinted SQL assume that the hinted SQL is going to do the wrong thing. Because of (2) above; you’ve been lucky so far, but the structural change lets you discover your mistake.

You will appreciate from these guidelines that I don’t really approve of using hints. The only reason that I leave them in place on a production system is when I’m sure that there is no alternative safe mechanism for making the optimizer do what I want. (And that does mean that I will use hints sometimes on a production system.)

What I use them for on test systems is to check whether a particular execution plan is actually possible, and to track down bugs in the optimizer.

Finally, for the purposes of education, I use them to demonstrate execution plans without first having to craft data sets and set database parameters to make a plan appear ‘spontaneously’.

Always be cautious about adding hints to production systems.

For More References click below hints  : 

http://jonathanlewis.wordpress.com/category/hints/

http://www.morganslibrary.com/reference/hints.html

Enjoy     :-) 

Rman Retention Policy Based On Redundancy Policy

I found many of us uses the Rman Recovery window Retention Policy . I have not find the rman Redundancy policy used . Here we will discuss the Disadvantages of the Redundancy Policy.   

The REDUNDANCY parameter of the CONFIGURE RETENTION POLICY command specifies how many backups of each datafile and control file that RMAN should keep. In other words, if the number of backups for a specific datafile or control file exceeds the REDUNDANCY setting, then RMAN considers the extra backups as obsolete.

Suppose we have a RMAN retention policy of "REDUNDANCY 2". This means that as long as we have at least two backups of the same datafile, controlfile/spfile or archivelog the other older backups become obsolete and RMAN is allowed to safely remove them.

Now, let's also suppose that every night we backup our database using the following script:
SQL> CONFIGURE CONTROLFILE AUTOBACKUP ON;
SQL> rman {
                        backup database plus archivelog;
                        delete noprompt obsolete redundancy 2;
                     }

The backup task is quite simple    :    first of all it ensures that we have the controlfile autobackup feature on, then it backups the database and archives and, at the end, it deletes all obsolete backups using the REDUNDANCY 2 retention policy.

Using the above approach we might think that we can restore our database as it was two days ago, right? For example, if we have a backup taken on Monday and another one taken on Tuesday we may restore our database as it was within the (Monday_last_backup - Today) time interval. Well, that's wrong!

Consider the following scenario :

1.)  On Monday night we backup the database using the above script;

2.) On Tuesday, during the day, we drop a tablespace. Because this is a structural database change a controlfile autobackup will be triggered. Ieeei, we have a new controlfile backup.

3.) On Tuesday night we backup again the database... nothing unusual, right? 

Well, the tricky part is regarding the DELETE OBSOLETE command. When the backup script will run this command, RMAN finds out three controlfile backups: One is originating from the Monday backup, One is from the structural change and the third is from our just finished Tuesday backup database command. Now according to the retention policy of "REDUNDANCY 2", RMAN will assume that it is safe to delete the backup of the controlfile taken on Monday night backup because it's out of our retention policy and because this backup is the oldest one. Uuups... this means that we gonna have a big problem restoring the database as it was before our structural change because we don't have a controlfile backup from that time.

So, if we intend to incomplete recover our database to a previous time in the past it's really a good idea to switch to a retention policy based on a "RECOVERY WINDOW" instead. In our case a RECOVERY WINDOW OF 2 DAYS would be more appropriate.

Reference : Click Here

 

Enjoy   :-)