Apply latest PSU patch on Oracle 11gr2

Hello Guys,

Here are the below step to apply latest PSU patch to you databases running on ASM file system. Note, this is not a clustered environment. This is single instance database with ASM file system.

Please download the latest PSU patch from metalink. In my case latest patch id is 17272731 and I have downloaded the file as p17272731_112030_Linux-x86-64.zip

I have copied this zip file to "/oradata/PSUOCT2013/" directory location in my server. And then unzip the file downloaded from metalink.

Here are few details before we proceed:

Oracle Version: 11.2.0.3.0
OS Version: Red Hat Enterprise Linux Server release 5.10 (x86_64)
Oracle Doc ID: 1494646.1
ORACLE_HOME: /oradata/app/oracle/product/11.2.0.3/db/
GRID_HOME: /oradata/app/oracle/product/11.2.0.3/grid/

Note : Take full backup of all the database before starting the activity.



Step 1:  Stop all the Oracle services running.

If this is a GI Home environment, as the database home owner execute:
Stop the ASM Instance and Databases
#crsctl stop resources



Step 2: Backing up the Grid Home and RDBMS Home

Make a tar backup for both the homes.
tar -zcvf grid-rdbms-home_ddmmyy.tar.gz /oradata/app/oracle



Step3: Run the pre root script

As the root user execute:
# /oradata/app/oracle/product/11.2.0.3/grid/crs/install/rootcrs.pl -unlock

If this is an Oracle Restart Home, as the root user execute:
# /oradata/app/oracle/product/11.2.0.3/grid/crs/install/roothas.pl –unlock


Step 4: Apply the CRS patch

As the GI home owner execute:
$/oradata/app/oracle/product/11.2.0.3/grid/OPatch/opatch napply -oh /oradata/app/oracle/product/11.2.0.3/grid –local /oradata/PSUOCT2013/17076717

As the GI home owner execute:
$/oradata/app/oracle/product/11.2.0.3/grid/OPatch/opatch apply -oh /oradata/app/oracle/product/11.2.0.3/grid -local /oradata/PSUOCT2013/16902043




Step 5: Run the pre script for DB component of the patch

As the database home owner execute:
$/oradata/PSUOCT2013/17076717/custom/server/17076717/custom/scripts/prepatch.sh -dbhome /oradata/app/oracle/product/11.2.0.3/db



Step 6: Apply the DB patch

As the database home owner execute:
$/oradata/app/oracle/product/11.2.0.3/db/OPatch/opatch napply –oh /oradata/app/oracle/product/11.2.0.3/db –local /oradata/PSUOCT2013/17076717/custom/server/17076717

$/oradata/app/oracle/product/11.2.0.3/db/OPatch/opatch apply -oh /oradata/app/oracle/product/11.2.0.3/db -local /oradata/PSUOCT2013/16902043



Step 7: Run the post script for DB component of the patch.

As the database home owner execute:
/oradata/PSUOCT2013/17076717/custom/server/17076717/custom/scripts/postpatch.sh -dbhome /oradata/app/oracle/product/11.2.0.3/db



Step 8: Run the post script

If this is an Oracle Restart Home, as the root user execute:
# /oradata/app/oracle/product/11.2.0.3/grid/crs/install/roothas.pl -patch



Step 9: Start the Oracle services

If this is an Oracle Restart Home environment, as the database home owner execute:
#crsctl start resource -all

Start the ASM Instance and Databases

Step 10:

For each database instance running on the Oracle home being patched, connect to the database using SQL*Plus. Connect as SYSDBA and run the catbundle.sql script as follows:
cd $ORACLE_HOME/rdbms/admin
sqlplus /nolog
SQL> CONNECT / AS SYSDBA
SQL> STARTUP
SQL> @catbundle.sql psu apply
SQL> QUIT

Good luck !!!!!!!!!!

Drop table commands ends up with ORA-02449

Hello,

Many have noticed that when you try to drop table we face ORA-02449 error. This is due to the primary key in the table which is referring to the foreign key of another table.

Error occurred is :

ORA-02449: unique/primary keys in table referenced by foreign keys


To overcome this situation, make sure you do not need this table. And then fire below command to delete the table. This drop statement with cascade constraints option will delete the primary key constraints from the table and drop the table.


DROP TABLE EMP CASCADE CONSTRAINTS;

table dropped.

Not able to see password value in Oracle 11g

Many of us are habitual of resetting the user password by values. If you will fire the command in Oracle 11g and try to query DBA_USERS. You will find no values for password. See below.

SQL> select username,password from dba_users where username='SCOTT';

USERNAME                       PASSWORD
------------------------------ ------------------------------
SCOTT






To get the password value in Oracle 11g , you need to query USER$ table as given below.





SQL> select NAME,PASSWORD from SYS.USERS$ where name='SCOTT';

NAME                           PASSWORD
------------------------------ ------------------------------
SCOTT                          A654888Z34402S74





Now you can change the password using values.

Oracle 18c onwards,

SQL> select spare4 from sys.user$ where name='GSMUSER';

SPARE4
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
S:D3258850C99652C76A8D12BB8F5764249179BCD83E2A94B8AE24D1E3EC65;T:3E0EF13A60C6511DA27BA714B9A99B5D77E94ADD2AB9E76A9C36F935DF8714DFF3E956756E49AEECD1C35AA949C8DE548264602AB6706B037BFE8A2378BEECA0D4330F8EE0B26E4B0792CDF0D5BB5D6A

SQL>alter user GSMUSER identified by values 'S:D3258850C99652C76A8D12BB8F5764249179BCD83E2A94B8AE24D1E3EC65;T:3E0EF13A60C6511DA27BA714B9A99B5D77E94ADD2AB9E76A9C36F935DF8714DFF3E956756E49AEECD1C35AA949C8DE548264602AB6706B037BFE8A2378BEECA0D4330F8EE0B26E4B0792CDF0D5BB5D6A';

Listener not starting on Oracle 11g Transparent gateway server

Oracle transparent gateway is a product that can be used to access data from non-Oracle databases (eg. MS-SQL,DB2,etc), and even non-relational data sources.

Transparent Gateways are most commonly used and enable access to non-Oracle data sources as if they were Oracle databases. They allow you to interact with these foreign databases without regard to differences in SQL implementation and with the added ability to perform distributed processing.

Some of the available transparent gateways:

Microsoft SQL Server

Informix

Sybase

IBM DB2

IBM DB2/400

IBM DRDA

EDA SQL

Please see below link for solution:

http://onelineroracledba.blogspot.in/2013/06/listener-not-starting-on-oracle11g.html

Good luck !!!!

Create physical standby database using RMAN duplicate command from active database feature

This is new feature in Oracle 11g. We can create a standby database using RMAN duplicate command.

Please refer below link for step by step method:

http://onelineroracledba.blogspot.in/2013/06/standby-database-using-11g-rman.html

Good luck !!!!

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.

Free Space at tablespace level

One of the most basic administration task of Oracle DBA is to monitor the free space in tablespaces. Below script will give the total space allocated in megabytes, total used space in megabytes and how much percent of tablespace is free.

Here is the query:

select a.tablespace_name , A.Allocated "Total Space allocated", B.Freespace "Total Free space" , round(b.freespace/a.allocated*100) "% Free" from
(select tablespace_name ,sum(bytes)/1024/1024 Allocated from dba_data_files group by tablespace_name) A ,(select tablespace_name,sum(bytes)/1024/1024 Freespace
from dba_free_space group by tablespace_name) B
where a.tablespace_name=b.tablespace_name;

Example:


SQL> select a.tablespace_name , A.Allocated "Total Space allocated", B.Freespace "Total Free space" , round(b.freespace/a.allocated*100) "% Free" from
(select tablespace_name ,sum(bytes)/1024/1024 Allocated from dba_data_files group by tablespace_name) A ,(select tablespace_name,sum(bytes)/1024/1024 Freespace
  2    3  from dba_free_space group by tablespace_name) B
where a.tablespace_name=b.tablespace_name;
  4
TABLESPACE_NAME    Total Space allocated       Total Free space     % Free
--------------------------    ---------------------        ----------------    ----------
UNDOTBS1                                  30                                23.375              78
SYSAUX                                     240                                 2.4375              1
USERS                                           5                                   1.9375             39
SYSTEM                                     480                                  7.125               1
EXAMPLE                                  100                                 31.75                32
ABC                                             25                                 24.8125            99

6 rows selected.

++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++


Script to calculate free tablespace considering maxsize clause.


WITH NEW_TBS AS
 (
 SELECT FILE_ID, TABLESPACE_NAME, FILE_NAME,
 DECODE (AUTOEXTENSIBLE,
 'YES', GREATEST (BYTES, MAXBYTES),
 BYTES
 ) MYSIZE,
 DECODE (AUTOEXTENSIBLE,
 'YES', CASE
 WHEN (MAXBYTES > BYTES)
 THEN (MAXBYTES - BYTES)
 ELSE 0
 END,
 0
 ) GROWTH
 FROM DBA_DATA_FILES)
 SELECT NEW_TBS.TABLESPACE_NAME,
 ROUND (SUM (NEW_TBS.MYSIZE) / (1024 * 1024)) TOTSIZE,
 ROUND (SUM (GROWTH) / (1024 * 1024)) GROWTH,
 ROUND ((SUM (NVL (FREEBYTES, 0))) / (1024 * 1024)) DFS,
 ROUND ((SUM (NVL (FREEBYTES, 0)) + SUM (GROWTH)) / (1024 * 1024)
 ) TOTFREE,
 ROUND ( (SUM (NVL (FREEBYTES, 0)) + SUM (GROWTH))
 / SUM (NEW_TBS.MYSIZE)
 * 100
 ) PERC
 FROM NEW_TBS, (SELECT FILE_ID, SUM (BYTES) FREEBYTES
 FROM DBA_FREE_SPACE
 GROUP BY FILE_ID) DFS
 WHERE NEW_TBS.FILE_ID = DFS.FILE_ID(+)
 AND NEW_TBS.TABLESPACE_NAME NOT LIKE '%UNDOTB%'
 GROUP BY NEW_TBS.TABLESPACE_NAME
 ORDER BY 6
 ;
Also please find below more simpler scripts which just gives u tablespace details which matches following criteria:

Free space < 20 GB  & Free percentage < 20 %

WITH NEW_TBS AS
 (
 SELECT FILE_ID, TABLESPACE_NAME, FILE_NAME,
 DECODE (AUTOEXTENSIBLE,
 'YES', GREATEST (BYTES, MAXBYTES),
 BYTES
 ) MYSIZE,
 DECODE (AUTOEXTENSIBLE,
 'YES', CASE
 WHEN (MAXBYTES > BYTES)
 THEN (MAXBYTES - BYTES)
 ELSE 0
 END,
 0
 ) GROWTH
 FROM DBA_DATA_FILES)
 SELECT * FROM
 (SELECT 'TABLESPACE NAME:',NEW_TBS.TABLESPACE_NAME,
 'FREE MB:', ROUND ((SUM (NVL (FREEBYTES, 0)) + SUM (GROWTH)) / (1024 * 1024)) AS TOTFREE,
 'FREE PERCENT:', ROUND ( (SUM (NVL (FREEBYTES, 0)) + SUM (GROWTH))/ SUM (NEW_TBS.MYSIZE)* 100) AS PERC
 FROM NEW_TBS, (SELECT FILE_ID, SUM (BYTES) FREEBYTES
 FROM DBA_FREE_SPACE
 GROUP BY FILE_ID) DFS
 WHERE NEW_TBS.FILE_ID = DFS.FILE_ID(+)
 AND NEW_TBS.TABLESPACE_NAME NOT LIKE '%UNDOTB%'
 GROUP BY NEW_TBS.TABLESPACE_NAME) A
 WHERE A.PERC < 20 AND A.TOTFREE < 20480
 ORDER BY 6;

Achive log generation per day oracle

Sometimes we may face issues like since there was high number of archive log generation; the filesystem may get full. This will result into non functioning of database.

It may happen that a database is generating 35 archives log on weekdays and more than 100 archive logs weekends due to heavy transactions on weekend. If we have the archive log generation data handy we can plan our action accordingly.

By using this query we can get the trend of the number of archives logs generated on per day basis, minimum and maximum sequence of the archive log and physical space occupied at OS level in megabytes(MB).
Note: We can get the actual size of archive log generated only if we have not specified any value to ARCHIVE_LAG_TARGET.

Here the query goes as :

select trunc(first_time) on_date,
       thread# thread,
       min(sequence#) min_sequence,
       max(sequence#) max_sequence,
       max(sequence#) - min(sequence#) nos_archives,
       (max(sequence#) - min(sequence#)) * log_avg_mb req_space_mb
from   v$log_history,
       (select avg(bytes/1024/1024) log_avg_mb
        from   v$log)
group  by trunc(first_time), thread#, log_avg_mb
order by trunc(first_time) asc


Example:

SQL> select trunc(first_time) on_date,
       thread# thread,
       min(sequence#) min_sequence,
       max(sequence#) max_sequence,
       max(sequence#) - min(sequence#) nos_archives,
       (max(sequence#) - min(sequence#)) * log_avg_mb req_space_mb
from   v$log_history,
       (select avg(bytes/1024/1024) log_avg_mb
        from   v$log)
group  by trunc(first_time), thread#, log_avg_mb
order by trunc(first_time) asc;

ON_DATE        THREAD MIN_SEQUENCE MAX_SEQUENCE NOS_ARCHIVES REQ_SPACE_MB
---------- ---------- ------------ ------------ ------------ ------------

06/05/2013          1           23           28            5          625
06/08/2013          1           29           29            0            0
06/09/2013          1           30           30            0            0
06/10/2013          1           31           31            0            0
06/11/2013          1           32           32            0            0
06/12/2013          1           33           33            0            0
06/13/2013          1           34           42            8         1000
06/14/2013          1           43           83           40         5000
06/15/2013          1           84           84            0            0
06/16/2013          1           85           85            0            0
06/17/2013          1           86           86            0            0
06/18/2013          1           87          158           71         8875
06/19/2013          1          159          159            0            0
06/21/2013          1          160          183           23         2875


In the above example, we can identify that on 18th June 2013; 8875 MB of achive log was generated. The minimum sequence was 87 and maximum sequence was 158.
Total 71 number of archive logs generated.

Few people may get output only for last seven, simultaneously few can get the output for as long as 5 years. This all depends upon the size of the control file.

Hope this helps some way to the DBA's. Good luck.

Identify locked objects at oracle

This query helps the DBA and application developer to identify the locked objects. This query gives you the detail for locked object,owner of object and the hostname of the user who has acquired lock on object.

SELECT c.owner, c.object_name, c.object_type, b.SID, b.serial#, b.status,b.osuser, b.machine
 FROM v$locked_object a, v$session b, dba_objects c
 WHERE b.SID = a.session_id AND a.object_id = c.object_id;

Example:

SELECT c.owner, c.object_name, c.object_type, b.SID, b.serial#, b.status,
b.osuser, b.machine
FROM v$locked_object a, v$session b, dba_objects c
WHERE b.SID = a.session_id AND a.object_id = c.object_id;

OWNER      OBJECT_NAM OBJECT_TYPE                SID    SERIAL# STATUS   OSUSER          MACHINE
---------- ---------- ------------------- ---------- ---------- -------- --------------- ---------------
ASHISH     T1         TABLE                      148         10 INACTIVE oracle          oracle10g.works
                                                                                         tation.com

Above example, shows table t1 is locked. User has occupied lock on oracle10g.workstation.com machine.

Most waiting session in Oracle

While working on performance issues of the database. Many times DBA needs to identify the user session which is waiting the most for the resources.

The below query will identify the most waiting sessions for resources.

select sesion.sid,
sesion.username,
sum(active_session_history.wait_time +
active_session_history.time_waited) ttl_wait_time
from v$active_session_history active_session_history,
v$session sesion
where active_session_history.sample_time between sysdate - 60/2880 and sysdate
and active_session_history.session_id = sesion.sid
group by sesion.sid, sesion.username
order by 3;


Example:


SQL> select sesion.sid,
  2  sesion.username,
  3  sum(active_session_history.wait_time +
  4  active_session_history.time_waited) ttl_wait_time
  5  from v$active_session_history active_session_history,
  6  v$session sesion
  7  where active_session_history.sample_time between sysdate - 60/2880 and sysdate
  8  and active_session_history.session_id = sesion.sid
  9  group by sesion.sid, sesion.username
 10  order by 3;

       SID USERNAME                       TTL_WAIT_TIME
---------- ------------------------------ -------------
       145                                                                  0
       164     USER1                                             113
       166                                                          66819
       151     TEST                                        4885256


Here we can identify the user session with sid 151 waited for 4885256 seconds.

Good luck.