Oracle Awr data dump -- Very important while migration

Most of time after migration  of database to new server we observe performance degradation . Usually we dont have historical performance data before migration which land us is big mess.

To overcome this , its very important to  take awr dump from existing database before migration . 

Extract AWR data

For Extract the AWR data from source database run the awrextr.sql script which extracts AWR data for a range of snapshots from the database into a Data Pump export file.

 
1. Run the following script for extract AWR:
@?/rdbms/admin/awrextr.sql;

2. Script ask for select DBID
Enter value for dbid:

3. Enter the number of days backup export:
Enter value for num_days: 2

4. It will list the 2 days snapshot in AWR. Choose the begin and end snapshot for export:
Enter value for begin_snap: 76
Begin Snapshot Id specified: 76
Enter value for end_snap: 86
End Snapshot Id specified: 86

5. List the Directory present in Database, Choose the directory location and dump file name:
Enter value for directory_name: ORACLE_HOME
.....
.....

Enter value for file_name:awrdat_76_86.dmp

6. Now dump is generated.
The AWR extract dump file will be located
| in the following directory/file:
| /usr/tmp/
| awrdat_76_86.dmp

Load the AWR data to target

For loading the extracted AWR data with awrload.sql script. It will first create a staging schema where the snapshot data is transferred

from the Data Pump file into the database

1. Run the AWRload.sql script for start loading data.

@?/rdbms/admin/awrload.sql

2. Specify the directory name where export file exists.

Enter value for directory_name:

3. Put the prefix of name of dump file:

Enter value for file_name:

4. Specify the name of staging schema where data loaded i.e AWRSTAGE

Enter value for schema_name:

5. Specify the default or temporary tablespace.

Enter value for default_tablespace: SYSAUX

-----------

Enter value for temporary_tablespace: TEMP

6. Loading of data is successful.

*** 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:

| /usr/tmp/

| AWRDAT_76_86.log

The process will then prompt for the staging schema name, the default is AWR_STAGE. If you accept the default, the script will create the AWR_STAGE user after asking you for default tablespaces. Once it has completed the awr load process, the script will drop the AWR_STAGE user.

After the process completes, the AWR tables now have new data in them! You can query DBA_HIST_SNAPSHOT or any of the other DBA_HIST views (including DBA_HIST_ACTIVE_SESS_HISTORY).