Home > 11g, Oracle DBA > How to ship AWR reports to another location using awrextr?

How to ship AWR reports to another location using awrextr?

February 14, 2014

AWR reports are very very important for tuning any database. At times you may not get access to PRODUCTION databases(sounds stupid, but really happens though you are DBA but will not have access to PROD) but you need to review the PRODUCTION database, during that time, one of the best way is to get the dump of AWR snapshots from the PRODUCTION database and load them to your local database(YOU can upload to any database, should not be clone of PRODUCTION) and you can review entire database locally with out accessing PRODUCTION database. Following are the steps to achieve the same.

1) extract the AWR data from the SOURCE database. (@?/rdbms/admin/awrextr.sql)
2) Ship the data to TARGET machine/server.
3) load the AWR data to TARGET DATABASE(using @?/rdbms/admin/awrload.sql).

Please note:- at TARGET location use awrrpti.sql to generate the AWR reports.

This is very very simple. Few points which i have noticed.
1) awrextr.sql takes following inputs.
a) File location, where datapump dump should be placed.
b) snapshot range(being and end snap shot range) need to be provided.
c) file name to be provided.
d) during the execution, it will not show any progress, but you can view by tailing the log file. log file is nothing but datapump log file which is located in the same location where dump file will be created.

2) awrload.sql takes following inputs.
a) file location from where we need to load the data.

Reference:-
How to Transport AWR Data [ID 872733.1]

For your ready information i am here with placing the entire log.(Few areas i trimmed).
=========================================
12:40:01 SQL> @?/rdbms/admin/awrextr.sql
~~~~~~~~~~~~~
AWR EXTRACT
~~~~~~~~~~~~~
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
~ This script will extract the AWR data for a range of snapshots ~
~ into a dump file. The script will prompt users for the ~
~ following information: ~
~ (1) database id ~
~ (2) snapshot range to extract ~
~ (3) name of directory object ~
~ (4) name of dump file ~
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

Databases in this Workload Repository schema
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

DB Id DB Name Host
———— ———— ————
* 1364724409 ORCL rsvmsb330
* 1364724409 ORCL rsvmsb332
3374413649 ORATEST rsvmsb330

The default database id is the local one: ‘1364724409’. To use this
database id, press to continue, otherwise enter an alternative.

Enter value for dbid: 1364724409

Using 1364724409 for Database ID

Specify the number of days of snapshots to choose from
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Entering the number of days (n) will result in the most recent
(n) days of snapshots being listed. Pressing without
specifying a number lists all completed snapshots.

Enter value for num_days: 1

Listing the last day’s Completed Snapshots

DB Name Snap Id Snap Started
———— ——— ——————
ORCL 865 13 Feb 2014 00:00
…………………………………………………………
…………………………………………………………
…………………………………………………………

DB Name Snap Id Snap Started
———— ——— ——————
ORCL 887 13 Feb 2014 11:00
888 13 Feb 2014 11:30
889 13 Feb 2014 12:00
890 13 Feb 2014 12:30
891 13 Feb 2014 12:37
892 13 Feb 2014 12:37
893 13 Feb 2014 12:38
894 13 Feb 2014 12:39

Specify the Begin and End Snapshot Ids
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Enter value for begin_snap: 887
Begin Snapshot Id specified: 887

Enter value for end_snap: 894
End Snapshot Id specified: 894

Specify the Directory Name
~~~~~~~~~~~~~~~~~~~~~~~~~~

Directory Name Directory Path
—————————— ————————————————-
DATA_FILE_DIR /u01/app/oracle/product/11.2.0/dbhome_1/demo/sche
ma/sales_history/

DATA_PUMP_DIR /u01/app/oracle/product/11.2.0/dbhome_1/rdbms/log
/

LOG_FILE_DIR /u01/app/oracle/product/11.2.0/dbhome_1/demo/sche
ma/log/

MEDIA_DIR /u01/app/oracle/product/11.2.0/dbhome_1/demo/sche
ma/product_media/

Directory Name Directory Path
—————————— ————————————————-

ORACLE_OCM_CONFIG_DIR /u01/app/oracle/product/11.2.0/dbhome_1/ccr/state
SS_OE_XMLDIR /u01/app/oracle/product/11.2.0/dbhome_1/demo/sche
ma/order_entry/

SUBDIR /u01/app/oracle/product/11.2.0/dbhome_1/demo/sche
ma/order_entry//2002/Sep

XMLDIR /ade/b/2125410156/oracle/rdbms/xml

Choose a Directory Name from the above list (case-sensitive).

Enter value for directory_name: DATA_PUMP_DIR

Using the dump directory: DATA_PUMP_DIR

Specify the Name of the Extract Dump File
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
The prefix for the default dump file name is awrdat_887_894.
To use this name, press to continue, otherwise enter
an alternative.

Enter value for file_name: SOMI_TEST

Using the dump file prefix: SOMI_TEST
|
| ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
| The AWR extract dump file will be located
| in the following directory/file:
| /u01/app/oracle/product/11.2.0/dbhome_1/rdbms/log/
| SOMI_TEST.dmp
| ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
|
| *** AWR Extract Started …
|
| This operation will take a few moments. The
| progress of the AWR extract operation can be
| monitored in the following directory/file:
| /u01/app/oracle/product/11.2.0/dbhome_1/rdbms/log/
| SOMI_TEST.log
|

End of AWR Extract
12:53:17 SQL>
18:35:27 SQL>
================================END OF extraction Completed=====================================

NOW ship the file to TARGET SERVER and give the necessary read permissions on the OS level.
i have moved file to /u01/oratest/admin/oratest/dpdump/ location which is referred as DATA_PUMP_DIR in TARGET DB.
================================START OF LOADING PROCEDURE======================================
[oratest@rsvmsb330 ~]$ echo $ORACLE_SID
oratest
[oratest@rsvmsb330 ~]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.1.0 Production on Thu Feb 13 19:22:35 2014

Copyright (c) 1982, 2009, Oracle. All rights reserved.

Connected to an idle instance.

SQL> startup
ORACLE instance started.

Total System Global Area 304807936 bytes
Fixed Size 2212856 bytes
Variable Size 192941064 bytes
Database Buffers 104857600 bytes
Redo Buffers 4796416 bytes
Database mounted.
Database opened.
SQL> @?/rdbms/admin/awrload.sql
~~~~~~~~~~
AWR LOAD
~~~~~~~~~~
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
~ This script will load the AWR data from a dump file. The ~
~ script will prompt users for the following information: ~
~ (1) name of directory object ~
~ (2) name of dump file ~
~ (3) staging schema name to load AWR data into ~
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

Specify the Directory Name
~~~~~~~~~~~~~~~~~~~~~~~~~~

Directory Name Directory Path
—————————— ————————————————-
DATA_FILE_DIR /u01/oratest/product/11.2.0/dbhome_1/demo/schema/
sales_history/

DATA_PUMP_DIR /u01/oratest/admin/oratest/dpdump/
LOG_FILE_DIR /u01/oratest/product/11.2.0/dbhome_1/demo/schema/
log/

MEDIA_DIR /u01/oratest/product/11.2.0/dbhome_1/demo/schema/
product_media/

ORACLE_OCM_CONFIG_DIR /u01/oratest/product/11.2.0/dbhome_1/ccr/state

Directory Name Directory Path
—————————— ————————————————-
SS_OE_XMLDIR /u01/oratest/product/11.2.0/dbhome_1/demo/schema/
order_entry/

SUBDIR /u01/oratest/product/11.2.0/dbhome_1/demo/schema/
order_entry//2002/Sep

XMLDIR /ade/b/2125410156/oracle/rdbms/xml

Choose a Directory Name from the list above (case-sensitive).

Enter value for directory_name: DATA_PUMP_DIR

Using the dump directory: DATA_PUMP_DIR

Specify the Name of the Dump File to Load
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Please specify the prefix of the dump file (.dmp) to load:

Enter value for file_name: SOMI_TEST

Loading from the file name: SOMI_TEST.dmp

Staging Schema to Load AWR Snapshot Data
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
The next step is to create the staging schema
where the AWR snapshot data will be loaded.
After loading the data into the staging schema,
the data will be transferred into the AWR tables
in the SYS schema.

The default staging schema name is AWR_STAGE.
To use this name, press to continue, otherwise enter
an alternative.

Enter value for schema_name:

Using the staging schema name: AWR_STAGE

Choose the Default tablespace for the AWR_STAGE user
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Choose the AWR_STAGE users’s default tablespace. This is the
tablespace in which the AWR data will be staged.

TABLESPACE_NAME CONTENTS DEFAULT TABLESPACE
—————————— ——— ——————
EXAMPLE PERMANENT
SYSAUX PERMANENT *
USERS PERMANENT

Pressing will result in the recommended default
tablespace (identified by *) being used.

Enter value for default_tablespace:

Using tablespace SYSAUX as the default tablespace for the AWR_STAGE

Choose the Temporary tablespace for the AWR_STAGE user
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Choose the AWR_STAGE user’s temporary tablespace.

TABLESPACE_NAME CONTENTS DEFAULT TEMP TABLESPACE
—————————— ——— ———————–
TEMP TEMPORARY *

Pressing will result in the database’s default temporary
tablespace (identified by *) being used.

Enter value for temporary_tablespace:

Using tablespace TEMP as the temporary tablespace for AWR_STAGE

… Creating AWR_STAGE user

|
| ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
| Loading the AWR data from the following
| directory/file:
| /u01/oratest/admin/oratest/dpdump/
| SOMI_TEST.dmp
| ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
|
| *** AWR Load Started …
|
| This operation will take a few moments. The
| progress of the AWR load operation can be
| monitored in the following directory/file:
| /u01/oratest/admin/oratest/dpdump/
| SOMI_TEST.log
|
… Dropping AWR_STAGE user

End of AWR Load
SQL>
SQL> @?/rdbms/admin/awrrpti.sql

Specify the Report Type
~~~~~~~~~~~~~~~~~~~~~~~
Would you like an HTML report, or a plain text report?
Enter ‘html’ for an HTML report, or ‘text’ for plain text
Defaults to ‘html’
Enter value for report_type:

Type Specified: html

Instances in this Workload Repository schema
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

DB Id Inst Num DB Name Instance Host
———— ——– ———— ———— ————
1364724409 1 ORCL orcl1 rsvmsb330
1364724409 2 ORCL orcl2 rsvmsb332
* 3374413649 1 ORATEST oratest rsvmsb330

Enter value for dbid: 1364724409
Using 1364724409 for database Id
Enter value for inst_num: 1
Using 1 for instance number

Specify the number of days of snapshots to choose from
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Entering the number of days (n) will result in the most recent
(n) days of snapshots being listed. Pressing without
specifying a number lists all completed snapshots.

Enter value for num_days: 2

Listing the last 2 days of Completed Snapshots

Snap
Instance DB Name Snap Id Snap Started Level
———— ———— ——— —————— —–
orcl1 ORCL 887 13 Feb 2014 11:00 1
888 13 Feb 2014 11:30 1
889 13 Feb 2014 12:00 1
890 13 Feb 2014 12:30 1
891 13 Feb 2014 12:37 1
892 13 Feb 2014 12:37 1
893 13 Feb 2014 12:38 1
894 13 Feb 2014 12:39 1

Specify the Begin and End Snapshot Ids
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Enter value for begin_snap: 887
Begin Snapshot Id specified: 887

Enter value for end_snap: 894
End Snapshot Id specified: 894

Specify the Report Name
~~~~~~~~~~~~~~~~~~~~~~~
The default report file name is awrrpt_1_887_894.html. To use this name,
press to continue, otherwise enter an alternative.

Enter value for report_name:

Using the report name awrrpt_1_887_894.html

AWR Report for DB: ORCL, Inst: orcl1, Snaps: 887-894

……..rest trimmed..

End of Report

Report written to awrrpt_1_887_894.html
SQL> exit

Categories: 11g, Oracle DBA