Extracting AWR data and loading it in another system

As a DBA, I find myself trying to support customers remotely, who frequently ask for another Automatic Workload Repository (AWR) or a special query. I’ve found a cool feature that enables customer's DBAs to extract the AWR and then load it locally.

Extracting

1. Login as SYS and at the SQL prompt, enter:
SQL> @?/rdbms/admin/awrextr.sql
2. Enter your database id or press <Enter> to use the current default.
Enter value for dbid:
Using 3342354369 for Database ID
3. Specify the number of days for which you want to list snapshot Ids.
Enter value for num_days: 1
4. After the list displays, enter the beginning and ending snapshot ID.
Enter value for begin_snap: 2670
Enter value for end_snap: 2672
5. A list of directory objects is displayed. Specify the directory object pointing to the directory where the export dump file will be stored.
Enter value for directory_name: DATA_PUMP_DIR
6. Specify the prefix for the default dump file name
Using the dump file prefix: awrdat_2670_2672.
7. An export log file and dump file will be created in the directory corresponding to the directory object you specified:
/oracle/admin/db/dpdump/awrdat_2670_2672.log
/oracle/admin/db/dpdump/awrdat_2670_2672.dmp

Loading

1. The dump file should be moved to the target database and located in one of the directories that has already been defined in the target database. Use SQL to find the directories:
column dirpath format a50 heading 'Directory Path'
column dirname format a30 heading 'Directory Name'
SELECT directory_name dirname, directory_path dirpath
FROM DBA_DIRECTORIES
ORDER BY directory_name;
2. Login as SYS and at the SQL prompt, enter:
SQL> @?/rdbms/admin/awrload.sql
3. A list of directory objects is displayed. Specify the directory object pointing to the directory where the dump file is located.
Enter value for directory_name: DATA_PUMP_DIR
4. Specify the prefix for the dump file name (without the .dmp suffix)
Enter value for file_name: awrdat_2670_2672
5. Enter temporary schema for this load or press <Enter> to use the default: AWR_STAGE
Enter value for schema_name: AWR_STAGE
6. Specify the default tablespace for the staging schema or press <Enter> to use the default: SYSAUX
Enter value for default_tablespace: SYSAUX
7. Specify the temporary tablespace for the staging schema: or press <Enter> to use the default: TEMP
Enter value for temporary_tablespace: TEMP
After the process completes, the AWR tables will contain new data! You can query DBA_HIST_SNAPSHOT or any of the other DBA_HIST views (including DBA_HIST_ACTIVE_SESS_HISTORY).
The standard AWR and Active Session History (ASH) reports will use the current database ID, which won’t be the same as the database ID of the data you have just loaded.

Luckily, you can just use the ‘i’ versions of the reports for that.

Invoke these scripts when you want to pick a database other than the default:
  • awrrpti.sql - Workload Repository Report Instance
  • awrgrpti.sql - Workload Repository RAC (Global) Report
  • awrgdrpi.sql - RAC Version of Compare Period Report
  • awrddrpi.sql - Workload Repository Compare Periods Report

Comments

Popular posts from this blog

Fixing & Registering ORACLE_HOMES in Central Inventory

Export in Pl/Sql via DBMS_DATAPUMP