Applies to:
Oracle Server - Enterprise Edition - Version: 10.2.0.1 to 11.2.0.2 - Release: 10.2 to 11.2Information in this document applies to any platform.
Goal
The document is a guide to transport AWR data between systems.This is specially useful when we want to setup a testcase on a separate sever and need the awr information there.
Solution
To transport AWR data, you need to first extract the AWR snapshot data from the database on the source system. Then load the data into the database on the target system.
Extracting AWR Data
At the SQL prompt, enter:
1. @$ORACLE_HOME/rdbms/admin/awrextr.sql
A list of the databases in the AWR schema is displayed.
2. Specify the database from which the AWR data will be extracted:
Enter value for db_id: 1377863381
3. Specify the number of days for which you want to list snapshot Ids.
Enter value for num_days: 2
A list of existing snapshots for the specified time range is displayed. In this example, snapshots captured in the last 2 days are displayed.
4. Define the range of snapshots for which AWR data will be extracted by specifying a beginning and ending snapshot Id:
Enter value for begin_snap: 30
Enter value for end_snap: 40
A list of directory objects is displayed.
5. Specify the directory object pointing to the directory where the export dump file will be stored.
6. Specify the prefix for name of the export dump file (the .dmp suffix will be automatically appended):
ex:-
Enter value for file_name: awrdata_30_40
Once the dump file is created, you can use Data Pump to transport the file to another system.
Loading AWR Data
1. @$ORACLE_HOME/rdbms/admin/awrextr.sql
A list of the databases in the AWR schema is displayed.
2. Specify the database from which the AWR data will be extracted:
Enter value for db_id: 1377863381
3. Specify the number of days for which you want to list snapshot Ids.
Enter value for num_days: 2
A list of existing snapshots for the specified time range is displayed. In this example, snapshots captured in the last 2 days are displayed.
4. Define the range of snapshots for which AWR data will be extracted by specifying a beginning and ending snapshot Id:
Enter value for begin_snap: 30
Enter value for end_snap: 40
A list of directory objects is displayed.
5. Specify the directory object pointing to the directory where the export dump file will be stored.
6. Specify the prefix for name of the export dump file (the .dmp suffix will be automatically appended):
ex:-
Enter value for file_name: awrdata_30_40
Once the dump file is created, you can use Data Pump to transport the file to another system.
Loading AWR Data
1. At the SQL prompt, run:
$ORACLE_HOME/rdbms/admin/awrload.sql
A list of directory objects is displayed.
2. Specify the directory object pointing to the directory where the export dump file is located:
Enter value for directory_name: DATA_PUMP_DIR
In this example, the directory object DATA_PUMP_DIR is selected.
3. Specify the prefix for name of the export dump file (the .dmp suffix will be automatically appended):
Enter value for file_name: awrdata_30_40
In this example, the export dump file named awrdata_30_40 is selected.
4. Specify the name of the staging schema where the AWR data will be loaded:
Enter value for schema_name: AWR_STAGE
In this example, a staging schema named AWR_STAGE will be created where the AWR data will be loaded.
5. Specify the default tablespace for the staging schema:
Enter value for default_tablespace: SYSAUX
In this example, the SYSAUX tablespace is selected.
6. Specify the temporary tablespace for the staging schema:
Enter value for temporary_tablespace: TEMP
In this example, the TEMP tablespace is selected.
7. A staging schema named AWR_STAGE will be created where the AWR data will be loaded. After the AWR data is loaded into the AWR_STAGE schema, the data will be transferred into the AWR tables in the SYS schema:
Processing object type TABLE_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
Completed 113 CONSTRAINT objects in 11 seconds
Processing object type TABLE_EXPORT/TABLE/CONSTRAINT/REF_CONSTRAINT
Completed 1 REF_CONSTRAINT objects in 1 seconds
Job "SYS"."SYS_IMPORT_FULL_03" successfully completed at 09:29:30
... Dropping AWR_STAGE user
End of AWR Load
$ORACLE_HOME/rdbms/admin/awrload.sql
A list of directory objects is displayed.
2. Specify the directory object pointing to the directory where the export dump file is located:
Enter value for directory_name: DATA_PUMP_DIR
In this example, the directory object DATA_PUMP_DIR is selected.
3. Specify the prefix for name of the export dump file (the .dmp suffix will be automatically appended):
Enter value for file_name: awrdata_30_40
In this example, the export dump file named awrdata_30_40 is selected.
4. Specify the name of the staging schema where the AWR data will be loaded:
Enter value for schema_name: AWR_STAGE
In this example, a staging schema named AWR_STAGE will be created where the AWR data will be loaded.
5. Specify the default tablespace for the staging schema:
Enter value for default_tablespace: SYSAUX
In this example, the SYSAUX tablespace is selected.
6. Specify the temporary tablespace for the staging schema:
Enter value for temporary_tablespace: TEMP
In this example, the TEMP tablespace is selected.
7. A staging schema named AWR_STAGE will be created where the AWR data will be loaded. After the AWR data is loaded into the AWR_STAGE schema, the data will be transferred into the AWR tables in the SYS schema:
Processing object type TABLE_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
Completed 113 CONSTRAINT objects in 11 seconds
Processing object type TABLE_EXPORT/TABLE/CONSTRAINT/REF_CONSTRAINT
Completed 1 REF_CONSTRAINT objects in 1 seconds
Job "SYS"."SYS_IMPORT_FULL_03" successfully completed at 09:29:30
... Dropping AWR_STAGE user
End of AWR Load
After the AWR data is loaded, the staging schema will be dropped automatically.
This information is also provided in the manual, with further detail here:Transporting Automatic Workload Repository Data
Niciun comentariu:
Trimiteți un comentariu