Generating multiple AWR reports using single command

AWR reports are very essential part of performance tuning in Oracle databases. Many a times we require multiple copies of AWR reports so that we can compare it with each other.

Traditional method of generating AWR reports (@?/rdbms/admin/awrrpt.sql) just generate single report at a time. Also generating AWR reports for many hours with scheduled interval will make process very time consuming.

So here u can use below procedure which will create several AWR reports in HTML format in a given directory, based on a begin/end snapshot id parameters.


 

CREATE OR REPLACE PROCEDURE create_multiple_awr (begin_snap number,end_snap number, directory varchar2 )
as
 v_Instance_number v$instance.instance_number%TYPE;
 v_Instance_name v$instance.instance_name%TYPE;
 v_dbid V$database.dbid%TYPE;
 v_file UTL_FILE.file_type;
BEGIN
/* Collecting instance information: Instance_number, Instance_name and Dbid */
 SELECT instance_number, instance_name
 into v_Instance_number,v_Instance_name
 FROM gv$instance
 ORDER BY 1;
 SELECT dbid
 INTO v_dbid
 FROM v$database;
/* Creating a database directory which will point to the acual wanted report directory in the OS */
 EXECUTE IMMEDIATE('CREATE OR REPLACE DIRECTORY TEMP_DIR AS '''||directory||'''');
/*
 Looping on all the snapshots from the begin_snap input parameter to the end_snap input parameter
 On each snapshot pair we will create a file in the given directory which will contain the AWR report.
 We use DBMS_WORKLOAD_REPOSITORY.AWR_REPORT_HTML build in procedure to create the HTML report.
*/
 FOR i IN begin_snap..end_snap-1 LOOP
 BEGIN
 --Creating and Naming the file:
 v_file := UTL_FILE.fopen('TEMP_DIR','awr_' || v_Instance_name ||'_'|| v_Instance_number || '_' || i || '_' || (i+1) || '.html', 'w', 32767);
 FOR c_AWRReport IN (
 SELECT output FROM TABLE (DBMS_WORKLOAD_REPOSITORY.AWR_REPORT_HTML( v_dbid, v_Instance_number, i, i+1))
 ) LOOP
 --Writing the AWR HTML report content to the file:
 UTL_FILE.PUT_LINE(v_file, c_AWRReport.output);
 END LOOP;
 --Closing the file:
 UTL_FILE.fclose(v_file);
 END;
 END LOOP;
/* Dropping the database directory which we've created earlier. */
 EXECUTE IMMEDIATE('DROP DIRECTORY TEMP_DIR');
END;

 

U have to create this procedure under SYS schema.

Usage : exec create_multiple_awr (<begin_snap_id>,<end_snap_id>,'<location_to_store_awr_reports>’);

Please find below the example:


SQL> select SNAP_ID,BEGIN_INTERVAL_TIME from dba_hist_snapshot where BEGIN_INTERVAL_TIME >sysdate-1/4 order by 1;
SNAP_ID BEGIN_INTERVAL_TIME
---------- ---------------------------------------------------------------------------
100 30-MAY-14 07.00.41.288 PM
101 30-MAY-14 08.00.47.856 PM
102 30-MAY-14 09.00.54.191 PM
103 30-MAY-14 10.00.58.109 PM
104 30-MAY-14 11.00.01.497 PM
SQL>
SQL> !pwd
/home/tibcomft/awr
SQL> !ls -l
total 0
SQL> exec create_multiple_awr (100,104,'/home/tibcomft/awr');
PL/SQL procedure successfully completed.
SQL> !ls -l
total 1596
-rw-r--r-- 1 tibcomft oinstall 412119 May 31 00:06 awr_TIBCOMFT_1_100_101.html
-rw-r--r-- 1 tibcomft oinstall 405498 May 31 00:06 awr_TIBCOMFT_1_101_102.html
-rw-r--r-- 1 tibcomft oinstall 403072 May 31 00:06 awr_TIBCOMFT_1_102_103.html
-rw-r--r-- 1 tibcomft oinstall 392942 May 31 00:07 awr_TIBCOMFT_1_103_104.html
SQL>

Hope so u will find this post very useful 🙂

Cheers

Regards,

Adityanath

Advertisements

Recreating undo tablespace in oracle database

Many a time situation comes, when undo tablespace becomes very big in size and due to space crunch , we need to recreate undo tablespace with appropriate size & drop old one.

This post provides u simple steps to perform this activity.


 

SQL> SHOW PARAMETER UNDO
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
undo_management string AUTO
undo_retention integer 900
undo_tablespace string UNDOTBS1

Create new undo tablespace with appropriate size:

 

SQL> CREATE UNDO TABLESPACE UNDOTBS2 DATAFILE '/U01/ORACLE/DB1/ORADATA/UNDOTBS2_01.DBF' SIZE 50OM AUTOEXTEND ON MAXSIZE 30G;

Change undo_tablespace initialization parameter with new undo tablespace value

 

SQL>ALTER SYSTEM SET UNDO_TABLESPACE=UNDOTBS2 SCOPE=BOTH;

SQL> SHOW PARAMETER UNDO
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
undo_management string AUTO
undo_retention integer 900
undo_tablespace string UNDOTBS2

At this point all new transaction will start using new undo tablespace – (in our case UNDOTBS2) for holding undo values & we are ready to drop old undo tablespace – (in our case UNDOTBS1)

SQL> DROP TABLESPACE UNDOTBS1 INCLUDING CONTENTS AND DATAFILES;

 

This command can be successful, if your all transactions are either committed or roll backed else this command will error out giving following error.

ORA-30013: undo tablespace ‘UNDOTBS1’ is currently in use


 

Now u can use following command to find out session which are still using old undo tablespace. Either u can ask end users to commit or rollback their sessions or kill them gracefully.

SELECT A.NAME,B.STATUS , D.USERNAME , D.SID , D.SERIAL# FROM V$ROLLNAME A,V$ROLLSTAT B, V$TRANSACTION C , V$SESSION D
WHERE A.USN = B.USN
AND A.USN = C.XIDUSN
AND C.SES_ADDR = D.SADDR
AND A.NAME IN (
SELECT SEGMENT_NAME
FROM DBA_SEGMENTS
WHERE TABLESPACE_NAME = 'UNDOTBS1'
);

once all sessions are cleared, u have to wait till time of undo_retention passes (in our case 15 minutes), then u can drop old tablespace.

Hope so this post will be useful for u 🙂

Cheers

Regards,

Adityanath

Script to calculate exact free space in a tablespace considering maxsize clause

Many a times we receive false alerts for low free space on the oracle tablespaces, as our traditional tablespace monitoring scripts does not consider maxsize clause while calculating free space in the tablespace.

Please find below the tablespace monitoring script – which gives you exact free space in tablespace:

 


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;

 

Hope so this will be very useful for you 🙂

Cheers…

Regards,

Adityanath