Longest running session in Oracle database

This script helps in identifying the session which is running on Oracle database from long time. In various performance issue scenario, DBA and application developer is keen to know about the session which is taking long time.

set pages 10000
set lines 180
col USERNAME for a25
col SESSION_RUNNING_FROM for a50
SELECT s.SID,s.SERIAL#,s.USERNAME,s.STATUS,s.SQL_ID,floor((sysdate-s.logon_time)*24) || ' HOURS ' || mod(floor((sysdate-s.logon_time)*24*60),60) || ' MINUTES ' || mod(floor((sysdate-s.logon_time)*24*60*60),60)|| ' SECS ' Session_running_since  FROM v$session s where s.username<>'SYS' order by SESSION_RUNNING_SINCE desc;

SID	SERIAL#	USERNAME	STATUS	SQL_ID	SESSION_RUNNING_SINCE
158	30	ASHISH	ACTIVE	90ya7z9gh6cx1	0 HOURS 48 MINUTES 44 SECS
152	34	ASHISH	INACTIVE		0 HOURS 40 MINUTES 20 SECS

With the above output, it becomes easier to identify the session which is running from long time. Session_running_since column will show the session running in Hours:Minutes:Seconds(HH:MM:SS) format. Also sid and sql_id is available to investigate further.

ORA-00980: synonym translation is no longer valid

As the name suggests, we can get an idea that while synonym called it is not accessible. There are more than one possibility for this error.

1.) The base table, on which synonym is created, is deleted.
2.)  The entire schema of the base table is dropped and we forget to drop the synonym.
3.) If synonym created on remote database, that is, via dblink. Then please check if dblink is working.

Below query will all the synonyms in the database.

select * from dba_synonyms where table_owner not in ('SYS','SYSTEM');

To avoid looking at the large number of synonyms , you can specify synonym name to look for the table name.

select * from dba_synonyms where table_owner not in ('SYS','SYSTEM') and synonym_name='TEST';

Change Database Name and DBID Using NID

NID is a utility to change the database name and database ID. There are many scenario where we need to restore the database backup and we required to change the name of the database. Recreating a controlfile is another option, but nid makes it much more simpler.

You can find this utility in $ORACLE_HOME/bin/nid

The syntax is :

nid TARGET=sys@"connection_entry" DBNAME="NewDBName"

Example:

nid TARGET=sys@ASHISHDB DBNAME=ASHIUAT

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

Below is the example to change the database name:

C:\Users\ashish>sqlplus sys as sysdba

SQL*Plus: Release 11.1.0.6.0 - Production on Fri Oct 5 19:38:51 2012

Copyright (c) 1982, 2007, Oracle.  All rights reserved.

Enter password:

Connected to:

Oracle Database 11g Enterprise Edition Release 11.1.0.6.0 - 64bit Production

With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> select name from v$database;

NAME

---------

ASHISHDB

SQL> exit

Disconnected from Oracle Database 11g Enterprise Edition Release 11.1.0.6.0 - 64bit Production

With the Partitioning, OLAP, Data Mining and Real Application Testing options

C:\Users\ashish>nid TARGET=sys@ASHISHDB DBNAME=ASHIUAT

DBNEWID: Release 11.1.0.6.0 - Production on Fri Oct 5 19:39:06 2012

Copyright (c) 1982, 2007, Oracle.  All rights reserved.

Password:

Connected to database ASHISHDB (DBID=356825129)

Connected to server version 11.1.0

Control Files in database:

    E:\DATA\DATAFILE\ASHISHDB\CONTROL001.CTL

    E:\DATA\DATAFILE\ASHISHDB\CONTROL002.CTL

    E:\DATA\DATAFILE\ASHISHDB\CONTROL003.CTL

Change database ID and database name ASHISHDB to ASHIUAT? (Y/[N]) => Y

Note: If you do not want to change DBID press N.

Proceeding with operation

Changing database ID from 356825129 to 2425101660

Changing database name from ASHISHDB to ASHIUAT

    Control File E:\DATA\DATAFILE\ASHISHDB\CONTROL001.CTL - modified

    Control File E:\DATA\DATAFILE\ASHISHDB\CONTROL002.CTL - modified

    Control File E:\DATA\DATAFILE\ASHISHDB\CONTROL003.CTL - modified

    Datafile E:\DATA\DATAFILE\ASHISHDB\SYSTEM.DBF - dbid changed, wrote new name

    Datafile E:\DATA\DATAFILE\ASHISHDB\USR01.DBF - dbid changed, wrote new name

    Datafile E:\DATA\DATAFILE\ASHISHDB\SYSAUX.DBF - dbid changed, wrote new name

    Datafile E:\DATA\DATAFILE\ASHISHDB\UNDOTBS1.DBF - dbid changed, wrote new name

    Datafile E:\DATA\DATAFILE\ASHISHDB\DATA01.DBF - dbid changed, wrote new name

    Datafile E:\DATA\DATAFILE\ASHISHDB\TEMP01.DBF - dbid changed, wrote new name

    Control File E:\DATA\DATAFILE\ASHISHDB\CONTROL001.CTL - dbid changed, wrote new name

    Control File E:\DATA\DATAFILE\ASHISHDB\CONTROL002.CTL - dbid changed, wrote new name

    Control File E:\DATA\DATAFILE\ASHISHDB\CONTROL003.CTL - dbid changed, wrote new name

    Instance shut down

Database name changed to ASHIUAT.

Modify parameter file and generate a new password file before restarting.

Database ID for database ASHIUAT changed to 2425101660.

All previous backups and archived redo logs for this database are unusable.

Database has been shutdown, open database with RESETLOGS option.

Succesfully changed database name and ID.

DBNEWID - Completed succesfully.

Now modify the pfile according to new database name:

db_name=ASHIUAT

SQL> startup mount;

ORACLE instance started.

Total System Global Area 1073131520 bytes

Fixed Size                  2121800 bytes

Variable Size             658509752 bytes

Database Buffers          406847488 bytes

Redo Buffers                5652480 bytes

Database mounted.

SQL> select name from v$database;

NAME

---------

ASHIUAT

SQL> alter database open resetlogs;

Database altered.

Thats all. Here are the steps to change the database name and database ID. Dont forget to modify the listener.ora and tnsnames.ora.

ORA-29278: SMTP transient error: 421 4.3.2 Service not available, closing transmission channel

While setting up access control list in Oracle 11g you may face this error. This error is purely linked to the smtp server.

Firstly, you can query the dictionaryDBA_NETWORK_ACLS and check if you have added correct entry under host. Host column will be displaying your SMTP server.

For example:

select acl , host , lower_port , upper_port from DBA_NETWORK_ACLS;

ACL                                          HOST                 LOWER_PORT UPPER_PORT

------------------------------ -------------------- ----------    ----------

/sys/acls/SMTP_SERVER.xml      10.67.65.188

SELECT * FROM TABLE(DBMS_NETWORK_ACL_UTILITY.DOMAINS('10.67.65.188'));

COLUMN_VALUE

--------------------------------------------------------------------------------

10.67.65.188

10.67.65.*

10.67.*

10.*

*

If the smtp host entry is ok. Then contact sysadmin team to check the SMTP server services.

SQL currently using the most resources

While investigating performance issue, we may need to find out the sql which is most resource intensive. Below is the script, this will produce the list on resource intensive sqls currently running in you database.

Here goes script as;

select active_session_history.user_id,
dba_users.username,
sqlarea.sql_text,
sqlarea.sql_id,
sum(active_session_history.wait_time +
active_session_history.time_waited) ttl_wait_time
from v$active_session_history active_session_history,
v$sqlarea sqlarea,
dba_users
where active_session_history.sample_time between sysdate - 60/2880 and sysdate
and active_session_history.sql_id = sqlarea.sql_id
and active_session_history.user_id = dba_users.user_id
group by active_session_history.user_id,sqlarea.sql_text, dba_users.username,sqlarea.sql_id
order by ttl_wait_time desc;

Example,

The output you will be getting as

USER_ID	USERNAME	SQL_TEXT	SQL_ID	TTL_WAIT_TIME
0	SYS	"xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx"	gm9t6yefb1yu6	2,802,675
331	ASHISH	"xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx"	cb7vqpgadycjp	492,902
322	OTHERS	"xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx"	8tyhx4x2x47ga	485,157
322	OTHERS	"xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx"	4d3dhvjvuf880	454,847
322	OTHERS	"xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx"	4fq3fkixrsp9d	432,484
322	OTHERS	"xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx"	3t3umn6pod3ds	142,767
322	OTHERS	"xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx"	rs0tel46zczna	98,390

The above output will provide you the sql which is most resource extensive. Also you get sql_id for your further investigation.

Monitoring Oracle database from Windows 7

To monitoring if all databases are up and running you can use this script with ease. Using this script you can check the status of database running in any operating system from your local windows client.I have written a small script. The approach I am following:

1.) Write a script (.sql file) to check the status of the database.
2.) Call this script (.sql file) from batch file.

We can add the the number of databases to the script(.sql) file we want to monitor. In below example, you will get better idea.


Prerequisites:

1.) Oracle client should be installed on your local windows machine.
2.) Your tnsnames.ora file should have all the database entries you want to monitor.


Example:

1.) Write a script (.sql file)

I will create file as health.sql

spool D:\Ashish\DBHEALTH.log
select 'Database '||name||' is '||status from v$database,v$instance;

conn "userid"/"password"@"service_name"
select 'Database '||name||' is '||status from v$database,v$instance;

conn "userid"/"password"@"service_name"
select 'Database '||name||' is '||status from v$database,v$instance;

spool off
exit;
/


2.) Create a batch file

sqlplus "userid"/"password"@"service_name" @D:\Ashish\health.sql

And save this file with .bat extension.

Note: Whatever service name you will mention in batch file, you will get first output for this database. And then the sequence you mentioned in script(.sql file)

say, If you create a batch file as,

sqlplus system/PASSWORD@DB1 @D:\Ashish\health.sql

Then in report, you will get the output of DB1, then DB2 and so on.

3.) Once your .sql file and .bat file are in place, you are good to go. Then simple double click the batch file and DBHEALTH.log file will be created.

Content of DBHEALTH.log


'DATABASE'||NAME||'IS'||STATUS                                                 
----------------------------------                                             
Database DB1 is OPEN                                                        

Connected.

'DATABASE'||NAME||'IS'||STATUS                                                 
----------------------------------                                             
Database DB2 is OPEN                                                        

Connected.

'DATABASE'||NAME||'IS'||STATUS                                                 
----------------------------------                                             
Database DB3 is OPEN       


I will be working on to get a better customized output and share once I am done.