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