awr导出导入分析.docx
《awr导出导入分析.docx》由会员分享，可在线阅读，更多相关《awr导出导入分析.docx（15页珍藏版）》请在冰豆网上搜索。
awr导出导入分析
awr导出/导入/分析
很多时候我们直接在客户机器上分析awr不太方便,需要通过收集客户awr信息到另一台机器上进行分析数据库性能等.这种情况下,就需要对客户的awr数据进行导出,然后导入到其他机器上,再进行深入分析.
导出awr数据
SQL>@?
/rdbms/admin/awrextr.sql
~~~~~~~~~~~~~
AWREXTRACT
~~~~~~~~~~~~~
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
~ThisscriptwillextracttheAWRdataforarangeofsnapshots~
~intoadumpfile.Thescriptwillpromptusersforthe~
~followinginformation:
~
~
（1）databaseid~
~
（2）snapshotrangetoextract~
~（3）nameofdirectoryobject~
~（4）nameofdumpfile~
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
DatabasesinthisWorkloadRepositoryschema
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
DBIdDBNameHost
------------------------------------
*1393262699XIFENFEIXIFENFEI-PC
3753332923FDJDBora1
3753332923FDJDBora2
Thedefaultdatabaseidisthelocalone:
'1393262699'.Tousethis
databaseid,presstocontinue,otherwiseenteranalternative.
输入dbid的值:
3753332923<--需要输入
Using3753332923forDatabaseID
Specifythenumberofdaysofsnapshotstochoosefrom
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Enteringthenumberofdays（n）willresultinthemostrecent
（n）daysofsnapshotsbeinglisted.Pressingwithout
specifyinganumberlistsallcompletedsnapshots.
输入num_days的值:
1<--需要输入
Listingthelastday'sCompletedSnapshots
DBNameSnapIdSnapStarted
---------------------------------------
FDJDB906234月201200:
00
907234月201201:
00
908234月201202:
00
909234月201203:
00
910234月201204:
00
911234月201205:
00
912234月201206:
00
913234月201207:
00
914234月201208:
00
915234月201209:
00
916234月201210:
00
917234月201211:
00
918234月201212:
00
919234月201213:
00
SpecifytheBeginandEndSnapshotIds
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
输入begin_snap的值:
906<--需要输入
BeginSnapshotIdspecified:
906
输入end_snap的值:
907<--需要输入
EndSnapshotIdspecified:
907
SpecifytheDirectoryName
~~~~~~~~~~~~~~~~~~~~~~~~~~
DirectoryNameDirectoryPath
-------------------------------------------------------------------------------
DATA_FILE_DIRE:
\oracle\product\11.2.0\dbhome_1\demo\schema\sales_history\
DATA_PUMP_DIRE:
\oracle\product\11.2.0\dbhome_1\rdbms\log\
LOG_FILE_DIRE:
\oracle\product\11.2.0\dbhome_1\demo\schema\log\
MEDIA_DIRE:
\oracle\product\11.2.0\dbhome_1\demo\schema\product_media\
ORACLE_OCM_CONFIG_DIRE:
\oracle\product\11.2.0\dbhome_1\ccr\state
SS_OE_XMLDIRE:
\oracle\product\11.2.0\dbhome_1\demo\schema\order_entry\
SUBDIRE:
\oracle\product\11.2.0\dbhome_1\demo\schema\order_entry\/2002/Sep
XMLDIRE:
\oracle\product\11.2.0\dbhome_1\rdbms\xml
ChooseaDirectoryNamefromtheabovelist（case-sensitive）.
输入directory_name的值:
DATA_PUMP_DIR<--需要输入（注意大小写）
Usingthedumpdirectory:
DATA_PUMP_DIR
SpecifytheNameoftheExtractDumpFile
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Theprefixforthedefaultdumpfilenameisawrdat_906_907.
Tousethisname,presstocontinue,otherwiseenter
analternative.
输入file_name的值:
xifenfei_awr<--需要输入
Usingthedumpfileprefix:
xifenfei_awr
|
|~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
|TheAWRextractdumpfilewillbelocated
|inthefollowingdirectory/file:
|E:
\oracle\product\11.2.0\dbhome_1\rdbms\log\
|xifenfei_awr.dmp
|~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
|
|***AWRExtractStarted...
|
|Thisoperationwilltakeafewmoments.The
|progressoftheAWRextractoperationcanbe
|monitoredinthefollowingdirectory/file:
|E:
\oracle\product\11.2.0\dbhome_1\rdbms\log\
|xifenfei_awr.log
|可以通过查看E:
\oracle\product\11.2.0\dbhome_1\rdbms\log\xifenfei_awr.log
|监控导出awr数据进度
EndofAWRExtract
导入awr数据
SQL>@E:
\oracle\product\11.2.0\dbhome_1\RDBMS\ADMIN\awrload.sql
~~~~~~~~~~
AWRLOAD
~~~~~~~~~~
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
~ThisscriptwillloadtheAWRdatafromadumpfile.The~
~scriptwillpromptusersforthefollowinginformation:
~
~
（1）nameofdirectoryobject~
~
（2）nameofdumpfile~
~（3）stagingschemanametoloadAWRdatainto~
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
SpecifytheDirectoryName
~~~~~~~~~~~~~~~~~~~~~~~~~~
DirectoryNameDirectoryPath
-------------------------------------------------------------------------------
DATA_FILE_DIRE:
\oracle\product\11.2.0\dbhome_1\demo\schema\sales_history\
DATA_PUMP_DIRE:
\oracle\product\11.2.0\dbhome_1\rdbms\log\
LOG_FILE_DIRE:
\oracle\product\11.2.0\dbhome_1\demo\schema\log\
MEDIA_DIRE:
\oracle\product\11.2.0\dbhome_1\demo\schema\product_media\
ORACLE_OCM_CONFIG_DIRE:
\oracle\product\11.2.0\dbhome_1\ccr\state
SS_OE_XMLDIRE:
\oracle\product\11.2.0\dbhome_1\demo\schema\order_entry\
SUBDIRE:
\oracle\product\11.2.0\dbhome_1\demo\schema\order_entry\/2002/Sep
XMLDIRE:
\oracle\product\11.2.0\dbhome_1\rdbms\xml
ChooseaDirectoryNamefromthelistabove（case-sensitive）.
输入directory_name的值:
DATA_PUMP_DIR<--需要输入（注意大小写）
Usingthedumpdirectory:
DATA_PUMP_DIR
SpecifytheNameoftheDumpFiletoLoad
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Pleasespecifytheprefixofthedumpfile（.dmp）toload:
输入file_name的值:
awrdat_751_919<--需要输入（文件后缀名一定要是.dmp）
Loadingfromthefilename:
awrdat_751_919.dmp
StagingSchematoLoadAWRSnapshotData
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Thenextstepistocreatethestagingschema
wheretheAWRsnapshotdatawillbeloaded.
Afterloadingthedataintothestagingschema,
thedatawillbetransferredintotheAWRtables
intheSYSschema.
ThedefaultstagingschemanameisAWR_STAGE.
Tousethisname,presstocontinue,otherwiseenter
analternative.
输入schema_name的值:
XFF_AWR<--需要输入（临时创建用户）
Usingthestagingschemaname:
XFF_AWR
ChoosetheDefaulttablespacefortheXFF_AWRuser
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
ChoosetheXFF_AWRusers'sdefaulttablespace.Thisisthe
tablespaceinwhichtheAWRdatawillbestaged.
TABLESPACE_NAMECONTENTSDEFAULTTABLESPACE
---------------------------------------------------------
EXAMPLEPERMANENT
SYSAUXPERMANENT*
USERSPERMANENT
Pressingwillresultintherecommendeddefault
tablespace（identifiedby*）beingused.
输入default_tablespace的值:
EXAMPLE<--需要输入
UsingtablespaceEXAMPLEasthedefaulttablespacefortheXFF_AWR
ChoosetheTemporarytablespacefortheXFF_AWRuser
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
ChoosetheXFF_AWRuser'stemporarytablespace.
TABLESPACE_NAMECONTENTSDEFAULTTEMPTABLESPACE
--------------------------------------------------------------
TEMPTEMPORARY*
Pressingwillresultinthedatabase'sdefaulttemporary
tablespace（identifiedby*）beingused.
输入temporary_tablespace的值:
TEMP<--需要输入
UsingtablespaceTEMPasthetemporarytablespaceforXFF_AWR
...CreatingXFF_AWRuser（临时用户创建）
|
|~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
|LoadingtheAWRdatafromthefollowing
|directory/file:
|E:
\oracle\product\11.2.0\dbhome_1\rdbms\log\
|awrdat_751_919.dmp
|~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
|
|***AWRLoadStarted...
|
|Thisoperationwilltakeafewmoments.The
|progressoftheAWRloadoperationcanbe
|monitoredinthefollowingdirectory/file:
|E:
\oracle\product\11.2.0\dbhome_1\rdbms\log\
|awrdat_751_919.log
|
|可以通过查看E:
\oracle\product\11.2.0\dbhome_1\rdbms\log\awrdat_751_919.log
|监控导出awr数据进度
...DroppingXFF_AWRuser（临时用户被删除）
EndofAWRLoad
查看awr报告
SQL>@?
/RDBMS/admin/awrrpti.sql
SpecifytheReportType
~~~~~~~~~~~~~~~~~~~~~~~
WouldyoulikeanHTMLreport,oraplaintextreport?
Enter'html'foranHTMLreport,or'text'forplaintext
Defaultsto'html'
输入report_type的值:
html<--需要输入
TypeSpecified:
html
InstancesinthisWorkloadRepositoryschema
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
DBIdInstNumDBNameInstanceHost
--------------------------------------------------------
37533329232FDJDBfdjdb2ora2
37533329231FDJDBfdjdb1ora1
*13932626991XIFENFEIxffXIFENFEI-PC
输入dbid的值:
3753332923<--需要输入
Using3753332923fordatabaseId
输入inst_num的值:
1<--需要输入
Using1forinstancenumber
Specifythenumberofdaysofsnapshotstochoosefrom
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Enteringthenumberofdays（n）willresultinthemostrecent
（n）daysofsnapshotsbeinglisted.Pressingwithout
specifyinganumberlistsallcompletedsnapshots.
输入num_days的值:
1<--需要输入
Listingthelastday'sCompletedSnapshots
Snap
InstanceDBNameSnapIdSnapStartedLevel
--------------------------------------------------------
fdjdb1FDJDB906234月201200:
001
907234月201201:
001
908234月201202:
001
909234月201203:
001
910234月201204:
001
911234月201205:
001
912234月201206:
001
913234月201207:
001
914234月201208:
001
915234月201209:
001
916234月201210:
001
917234月201211:
001
918234月201212:
001
919234月201213:
001
SpecifytheBeginandEndSnapshotIds
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
输入begin_snap的值:
917<--需要输入
BeginSnapshotIdspecified:
917
输入end_snap的值:
918<--需要输入
EndSnapshotIdspecified:
918
SpecifytheReportName
~~~~~~~~~~~~~~~~~~~~~~~
Thedefaultreportfilenameisawrrpt_1_917_918.html.Tousethisname,
presstocontinue,otherwiseenteranalternative.
输入report_name的值:
xifenfei_awr.html<--需要输入
使用execDBMS_SWRF_INTERNAL.UNREGISTER_DATABASE（dbid）;清理掉不需要的数据库的awr报告
awr导出脚本
begin
/*callPL/SQLroutinetoextractthedata*/
dbms_swrf_internal.awr_extract（dmpfile=>:
dmpfile,
dmpdir=>:
dmpdir,
bid=>:
bid,
eid=>:
eid,
dbid=>:
dbid）;
dbms_swrf_internal.clear_awr_dbid;
end;
/
awr导入脚本
begin
/*c
升级会员 