全文摘自http://xzh2000.itpub.net/post/96/42411

Let's say you are tying to resolve some performance issues in the production databases. As you saw elsewhere in this article, the AWR data is vital for the analysis. However, analyzing AWR data during a normal production run may not be desirable or even feasible. Rather, you may want to load the data in some central location for comparative analysis. How can you do that?

A new package DBMS_SWRF_INTERNAL has been provided in Oracle Database 10g Release 2 for this purpose. To download it into a Data Pump dumpfile, you would use the procedure AWR_EXTRACT:

  1  begin
  2     DBMS_SWRF_INTERNAL.AWR_EXTRACT (
  3        dmpfile   => 'awr_data.dmp',
  4        dmpdir    => 'TMP_DIR',
  5        bid       => 302,
  6        eid       => 305
  7     );
  8* end;

Let's examine the lines in more detail.

Line	Description
3	The name of the target file for the data is mentioned here. This is a Data Pump export file. If non filename is given, the default value awrdat.dmp is used.
4	The directory object where the dumpfile is written. In this case, you may have defined a directory TMP_DIR as /tmp.
5	The snapshot ID of the beginning snapshot of the period.
6	The end snapshot ID. Here you are exporting the snapshots between 302 and 305.

Now you can take the dumpfile awr_data.dmp to the new location and load it using another procedure in the same package, AWR_LOAD:

 1  begin
  2     DBMS_SWRF_INTERNAL.AWR_LOAD (
  3        SCHNAME => 'ARUP',
  4        dmpfile => 'awr_data',
  5        dmpdir => 'TMP_DIR'
  6     );
  7* end;

In this code, you are loading the contents of the dumpfile awr_data.dmp into the directory specified by the directory object TMP_DIR. When loading the AWR data, it is not loaded into the SYS schema directly; rather, it's staged in a different schema first. The schema name is given in the parameter SCHNAME, as shown in line 3. After staging, the data is moved into the SYS schema:

1  begin
  2     DBMS_SWRF_INTERNAL.MOVE_TO_AWR (
  3        SCHNAME => 'ARUP'
  4     );
  5* end;

Here you are moving the AWR data from the schema ARUP to SYS.

Moving AWR to a different database, as I mentioned above, has a lot of benefits and uses. You can analyze the data in a different database without affecting production too much. In addition, you can build a central repository of AWR data collected from multiple databases.

All these loading steps have been placed into a single file awrload.sql located in $ORACLE_HOME/rdbms/bin directory. Similarly, the script awrextr.sql contains all the steps for the extraction process.

While this mechanism for off-loading production AWR data to a secondary database has been externalized, its main intent in Oracle Database 10g Release 2 is to help troubleshoot any problems reported by customers. With this approach, customers can send raw data in the form of AWR dump files , which support staff can then import into their schema to help reproduce and diagnose problems.

$ORACLE_HOME/rdbms/bin -> $ORACLE_HOME/rdbms/admin

[@more@]

ITPub博客

10gR2 Transport AWR Data

最新文章