Home » RDBMS Server » Backup & Recovery » AWR to be created on a local Machine. (oracle version 10g Windows XP)
AWR to be created on a local Machine. [message #541547] Wed, 01 February 2012 01:47 Go to next message
muktha_22
Messages: 527
Registered: December 2009
Senior Member
Hi All,

I need your's help.

Q: I have a script, which is generating the AWR report on the server side.
We have 5 servers. All the server's AWR report to be copied (or To be created Directly) on the local machine.
Kindly help me on this.

CREATE OR REPLACE PROCEDURE CreateAwrReports(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;
start_id number;
end_id number;
BEGIN
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;
SELECT MAX(E1.snap_id), MAX(E2.snap_id) 
into end_id,start_id
FROM dba_hist_snapshot E1, (SELECT  snap_id FROM dba_hist_snapshot 
	    WHERE snap_id NOT IN ( SELECT MAX(snap_id) 
FROM dba_hist_snapshot)) E2;

EXECUTE IMMEDIATE('CREATE OR REPLACE DIRECTORY TEMP_DIR AS '''||directory||'''');

BEGIN
v_file := UTL_FILE.fopen('TEMP_DIR', 'awr_' || v_Instance_name ||'_'|| v_Instance_number ||'.html',
 'w', 32767);
FOR c_AWRReport IN (
SELECT output FROM TABLE (DBMS_WORKLOAD_REPOSITORY.AWR_REPORT_HTML( v_dbid, v_Instance_number,
  start_id, end_id))
) LOOP
UTL_FILE.PUT_LINE(v_file, c_AWRReport.output);
END LOOP;
UTL_FILE.fclose(v_file);
END;
end;


Regards:
Muktha

[Updated on: Wed, 01 February 2012 02:05] by Moderator

Report message to a moderator

Re: AWR to be created on a local Machine. [message #541549 is a reply to message #541547] Wed, 01 February 2012 02:04 Go to previous messageGo to next message
Michel Cadot
Messages: 68641
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
You still should learn how to indent code and this is the FIRST thing you have to do keeping your lines in maximum 80 character width.

Regards
Michel

Report message to a moderator

Re: AWR to be created on a local Machine. [message #541598 is a reply to message #541549] Wed, 01 February 2012 05:11 Go to previous messageGo to next message
muktha_22
Messages: 527
Registered: December 2009
Senior Member
Hi Michel,

Sorry for the mistake.
Kindly help me.

CREATE OR REPLACE PROCEDURE 
CreateAwrReports(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;
start_id number;
end_id number;
BEGIN
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;
SELECT MAX(E1.snap_id), MAX(E2.snap_id) 
into end_id,start_id
FROM dba_hist_snapshot E1, (SELECT  snap_id FROM dba_hist_snapshot 
	    WHERE snap_id NOT IN ( SELECT MAX(snap_id) 
FROM dba_hist_snapshot)) E2;

EXECUTE IMMEDIATE('CREATE OR REPLACE DIRECTORY TEMP_DIR 
 AS '''||directory||'''');

BEGIN
v_file := UTL_FILE.fopen('TEMP_DIR', 'awr_' 
  || v_Instance_name ||'_'|| v_Instance_number ||'.html',
 'w', 32767);
FOR c_AWRReport IN (
SELECT output FROM TABLE (DBMS_WORKLOAD_REPOSITORY.AWR_REPORT_HTML
   ( v_dbid, v_Instance_number,
  start_id, end_id))
) LOOP
UTL_FILE.PUT_LINE(v_file, c_AWRReport.output);
END LOOP;
UTL_FILE.fclose(v_file);
END;
end;


Regards:
Muktha

Report message to a moderator

Re: AWR to be created on a local Machine. [message #541623 is a reply to message #541598] Wed, 01 February 2012 06:05 Go to previous messageGo to next message
Michel Cadot
Messages: 68641
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Oh dear! Do you think this is indented?
Do you understand what to indent a code means?

Regards
Michel

Report message to a moderator

Re: AWR to be created on a local Machine. [message #541634 is a reply to message #541623] Wed, 01 February 2012 06:40 Go to previous message
John Watson
Messages: 8929
Registered: January 2010
Location: Global Village
Senior Member
This took about 30 thirty sconds, using the code formatter on http://www.dpriver.com/pp/sqlformat.htm
CREATE OR replace PROCEDURE Createawrreports(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;
  start_id          NUMBER;
  end_id            NUMBER;
BEGIN
  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;

  SELECT MAX(e1.snap_id),
         MAX(e2.snap_id)
  INTO   end_id, start_id
  FROM   dba_hist_snapshot e1,
         (SELECT snap_id
          FROM   dba_hist_snapshot
          WHERE  snap_id NOT IN (SELECT MAX(snap_id)
                                 FROM   dba_hist_snapshot)) e2;

  EXECUTE IMMEDIATE('CREATE OR REPLACE DIRECTORY TEMP_DIR 
 AS '''||directory||'''');

  BEGIN
      v_file := utl_file.Fopen('TEMP_DIR', 'awr_'
                                           || v_instance_name
                                           ||'_'
                                           || v_instance_number
                                           ||'.html', 'w', 32767);

      FOR c_awrreport IN (SELECT output
                          FROM   TABLE (dbms_workload_repository.Awr_report_html
                                 (
                                        v_dbid, v_instance_number,
                                                start_id,
                                                end_id))) LOOP
          utl_file.Put_line(v_file, c_awrreport.output);
      END LOOP;

      utl_file.Fclose(v_file);
  END;
END;
I'm not going to help you again, Muktha, until you start thinking. To begin with, you might want to think about running the awrrpt.sql script and the way it uses the SPOOL command.

Report message to a moderator

Previous Topic: Having problem in changing the location of data files and temp files from autobackup of controlfile
Next Topic: Backup archive log file using RMAN
Goto Forum:
  

[ Syndicate this forum (XML) ] [ RSS ]

Current Time: Fri Apr 19 07:41:52 CDT 2024
.:: Forum Home :: Blogger Home :: Wiki Home :: Contact :: Privacy ::.