导出/导入 snap信息

由于数据库迁移,希望通过awrdd对比一下迁移前与迁移后的性能.
1.导出源库 awr 信息


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
------------ ------------ ------------
* 3217450098 HQTDSKF      nhqtds


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


Enter value for dbid: 3217450098


Using 3217450098 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: 8


Listing the last 8 days of Completed Snapshots


DB Name        Snap Id    Snap Started
------------ --------- ------------------
HQTDSKF           8454 01 Mar 2017 00:00
                  8455 01 Mar 2017 01:00
                  8456 01 Mar 2017 02:00
-----------------------------------------------------
Specify the Begin and End Snapshot Ids
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Enter value for begin_snap: 8454
Begin Snapshot Id specified: 8454


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




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


Directory Name                 Directory Path
------------------------------ -------------------------------------------------
DATA_PUMP_DIR                  /u01/app/oracle/admin/hqtdskf/dpdump/
DUMP                           /oradata/nhqtdskf
EXPDIR                         /dumpback/expdir
ORACLE_OCM_CONFIG_DIR          /u01/app/oracle/product/11.2.0/dbhome_1/ccr/state
SQLT$BDUMP                     /u01/app/oracle/diag/rdbms/jctds/jctds3/trace
SQLT$STAGE                     /u01/app/oracle/diag/rdbms/jctds/jctds3/trace
SQLT$UDUMP                     /u01/app/oracle/diag/rdbms/jctds/jctds3/trace
TEST                           /oradata/nhqtdskf
TRACE_DIR_SRC_4_TCB            /u01/app/oracle/diag/rdbms/jctds/jctds3/trace
TRCA$INPUT1                    /u01/app/oracle/diag/rdbms/jctds/jctds3/trace
TRCA$INPUT2                    /u01/app/oracle/diag/rdbms/jctds/jctds3/trace


Directory Name                 Directory Path
------------------------------ -------------------------------------------------
TRCA$STAGE                     /u01/app/oracle/diag/rdbms/jctds/jctds3/trace
XMLDIR                         /u01/app/oracle/product/11.2.0/dbhome_1/rdbms/xml


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


Enter value for directory_name: TEST


Using the dump directory: TEST


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


Enter value for file_name: mm.dmp 


Using the dump file prefix: mm.dmp
|
| ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
|  The AWR extract dump file will be located
|  in the following directory/file:
|   /oradata/nhqtdskf
|   mm.dmp.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:
|   /oradata/nhqtdskf
|   mm.dmp.log


End of AWR Extract
SQL> 


导出完成了


2.导入目标库 


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_PUMP_DIR                  /u01/app/oracle/admin/test/dpdump/
ORACLE_OCM_CONFIG_DIR          /u01/app/oracle/product/11.2.0/dbhome_1/ccr/state
SQLT$BDUMP                     /u01/app/oracle/diag/rdbms/wh/test/trace
SQLT$DIAG                      /u01/app/oracle/diag/rdbms/wh/test/trace
SQLT$STAGE                     /u01/app/oracle/diag/rdbms/wh/test/trace
SQLT$UDUMP                     /u01/app/oracle/diag/rdbms/wh/test/trace
TEST                           /oradata/nhqtdskf
TRCA$INPUT1                    /u01/app/oracle/diag/rdbms/wh/test/trace
TRCA$INPUT2                    /u01/app/oracle/diag/rdbms/wh/test/trace
TRCA$STAGE                     /u01/app/oracle/diag/rdbms/wh/test/trace
XMLDIR                         /u01/app/oracle/product/11.2.0/dbhome_1/rdbms/xml


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


Enter value for directory_name: TEST


Using the dump directory: TEST


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: mm.dmp


Loading from the file name: mm.dmp.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: TESTUSER


Using the staging schema name: TESTUSER


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


TABLESPACE_NAME                CONTENTS  DEFAULT TABLESPACE
------------------------------ --------- ------------------
SYSAUX                         PERMANENT *
USERS                          PERMANENT


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


Enter value for default_tablespace: SYSAUX


Using tablespace SYSAUX as the default tablespace for the TESTUSER




Choose the Temporary tablespace for the TESTUSER user
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Choose the TESTUSER 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: TEMP


Using tablespace TEMP as the temporary tablespace for TESTUSER




... Creating TESTUSER user


|
| ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
|  Loading the AWR data from the following
|  directory/file:
|   /oradata/nhqtdskf
|   mm.dmp.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:
|   /oradata/nhqtdskf
|   mm.dmp.log
|
... Dropping TESTUSER user


End of AWR Load
SQL> 






3.进行对比
查询一下信息


SQL> select distinct dbid from dba_hist_snapshot;


      DBID
----------
3114505484
3217450098


3217450098  是源库


SQL> select dbid from v$database;


      DBID
----------
3114505484
这个是当前库


我们对源库 3月7日12点的数据与 当前库  3月8日12点的数据进行对比
set line 999
select snap_id,begin_interval_time,end_interval_time from dba_hist_snapshot where dbid = 3217450098 order by begin_interval_time asc;
 8611 07-MAR-17 12.00.14.627 PM                                                   07-MAR-17 01.00.18.365 PM
 8612 07-MAR-17 01.00.18.365 PM                                                   07-MAR-17 02.00.21.985 PM


应该是 8611与8612 这两个snap


查看一下目标库  12 点的 snap信息
set line 999
select snap_id,begin_interval_time,end_interval_time from dba_hist_snapshot where dbid = 3114505484 order by begin_interval_time asc;
7767 08-MAR-17 12.00.55.675 PM                                                   08-MAR-17 01.00.27.035 PM
7768 08-MAR-17 01.00.27.035 PM                                                   08-MAR-17 02.00.30.535 PM
应该是 7767 与 7768 两个snap信息


spool awr_compare_report.html
set echo off;
set veri off;
set feedback off;
set head off
set verify off
set lines 8000
select * from TABLE(DBMS_WORKLOAD_REPOSITORY.awr_diff_report_html(3217450098,1,8611,8612,3114505484,1,7767,7768));
spool off


拿到报告就可以分析啦.

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/7569309/viewspace-2134944/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/7569309/viewspace-2134944/