Oracle Awr data dump -- 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).