这篇文章主要讲解了“AWR跨库导出与导入的方法是什么”，文中的讲解内容简单清晰，易于学习与理解，下面请大家跟着小编的思路慢慢深入，一起来研究和学习“AWR跨库导出与导入的方法是什么”吧！

SYS@honor1>@?/rdbms/admin/awrextr.sql~~~~~~~~~~~~~AWREXTRACT~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ThisscriptwillextracttheAWRdataforarangeofsnapshots~~intoadumpfile.Thescriptwillpromptusersforthe~~followinginformation:~~(1)databaseid~~(2)snapshotrangetoextract~~(3)nameofdirectoryobject~~(4)nameofdumpfile~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~DatabasesinthisWorkloadRepositoryschema~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~DBIdDBNameHost------------------------------------*353046371HONORdb-oracle-node1.cloud.lab.eng.bos.redhat.com*353046371HONORdb-oracle-node2.cloud.lab.eng.bos.redhat.comThedefaultdatabaseidisthelocalone:'353046371'.Tousethisdatabaseid,press<return>tocontinue,otherwiseenteranalternative.Entervaluefordbid:Using353046371forDatabaseIDSpecifythenumberofdaysofsnapshotstochoosefrom~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~Enteringthenumberofdays(n)willresultinthemostrecent(n)daysofsnapshotsbeinglisted.Pressing<return>withoutspecifyinganumberlistsallcompletedsnapshots.Entervaluefornum_days:5Listingthelast5daysofCompletedSnapshotsDBNameSnapIdSnapStarted---------------------------------------HONOR120705Apr202001:56120805Apr202009:38120905Apr202011:00121005Apr202012:00121105Apr202013:00121205Apr202014:00121305Apr202015:00121405Apr202016:00121505Apr202017:00121605Apr202018:00121705Apr202019:00121805Apr202020:00121905Apr202021:00122005Apr202022:01122105Apr202023:00122206Apr202001:55122306Apr202010:34122406Apr202012:00122506Apr202013:00122606Apr202014:00122706Apr202015:00122806Apr202016:00122906Apr202017:00123006Apr202018:00123106Apr202019:00123206Apr202020:00123306Apr202021:00123407Apr202010:07123507Apr202011:00123607Apr202012:00123707Apr202013:00123807Apr202014:00123909Apr202009:55124009Apr202011:00SpecifytheBeginandEndSnapshotIds~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~Entervalueforbegin_snap:1207BeginSnapshotIdspecified:1207Entervalueforend_snap:1217EndSnapshotIdspecified:1217SpecifytheDirectoryName~~~~~~~~~~~~~~~~~~~~~~~~~~DirectoryNameDirectoryPath-------------------------------------------------------------------------------DATA_FILE_DIR/u01/app/oracle/product/11.2/db_1/demo/schema/sales_history/DATA_PUMP_DIR/u01/app/oracle/product/11.2/db_1/rdbms/log/EXPDP/rman/expdpLOG_FILE_DIR/u01/app/oracle/product/11.2/db_1/demo/schema/log/MEDIA_DIR/u01/app/oracle/product/11.2/db_1/demo/schema/product_media/ORACLE_OCM_CONFIG_DIR/u01/app/oracle/product/11.2/db_1/ccr/hosts/db-oracle-node1.cloud.lab.eng.bos.redhat.com/stateORACLE_OCM_CONFIG_DIR2/u01/app/oracle/product/11.2/db_1/ccr/stateSS_OE_XMLDIR/u01/app/oracle/product/11.2/db_1/demo/schema/order_entry/SUBDIR/u01/app/oracle/product/11.2/db_1/demo/schema/order_entry//2002/SepXMLDIR/u01/app/oracle/product/11.2/db_1/rdbms/xmlChooseaDirectoryNamefromtheabovelist(case-sensitive).Entervaluefordirectory_name:EXPDP/*需要大写，小写目录会报错*/Usingthedumpdirectory:EXPDPSpecifytheNameoftheExtractDumpFile~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~Theprefixforthedefaultdumpfilenameisawrdat_1207_1217.Tousethisname,press<return>tocontinue,otherwiseenteranalternative.Entervalueforfile_name:Usingthedumpfileprefix:awrdat_1207_1217||~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~|TheAWRextractdumpfilewillbelocated|inthefollowingdirectory/file:|/rman/expdp|awrdat_1207_1217.dmp|~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~||***AWRExtractStarted...||Thisoperationwilltakeafewmoments.The|progressoftheAWRextractoperationcanbe|monitoredinthefollowingdirectory/file:|/rman/expdp|awrdat_1207_1217.log|EndofAWRExtractSYS@honor1>exit（2）导入

scp到目标数据库服务器：

[oracle@db-oracle-node1expdp]$scp*192.168.204.231:/rman/expdporacle@192.168.204.231'spassword:awrdat_1207_1217.dmp100%12MB11.8MB/s00:00awrdat_1207_1217.log

导入

11:40:59SYS@messay>@?/rdbms/admin/awrload.sql~~~~~~~~~~AWRLOAD~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ThisscriptwillloadtheAWRdatafromadumpfile.The~~scriptwillpromptusersforthefollowinginformation:~~(1)nameofdirectoryobject~~(2)nameofdumpfile~~(3)stagingschemanametoloadAWRdatainto~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~SpecifytheDirectoryName~~~~~~~~~~~~~~~~~~~~~~~~~~DirectoryNameDirectoryPath-------------------------------------------------------------------------------DATA_FILE_DIR/oracle/app/oracle/product/11.2/db_1/demo/schema/sales_history/DATA_PUMP_DIR/oracle/app/oracle/admin/messay/dpdump/EXPDP/rman/expdpLOG_FILE_DIR/oracle/app/oracle/product/11.2/db_1/demo/schema/log/MEDIA_DIR/oracle/app/oracle/product/11.2/db_1/demo/schema/product_media/ORACLE_OCM_CONFIG_DIR/oracle/app/oracle/product/11.2/db_1/ccr/hosts/single/stateORACLE_OCM_CONFIG_DIR2/oracle/app/oracle/product/11.2/db_1/ccr/stateSS_OE_XMLDIR/oracle/app/oracle/product/11.2/db_1/demo/schema/order_entry/SUBDIR/oracle/app/oracle/product/11.2/db_1/demo/schema/order_entry//2002/SepXMLDIR/oracle/app/oracle/product/11.2/db_1/rdbms/xmlElapsed:00:00:00.00ChooseaDirectoryNamefromthelistabove(case-sensitive).Entervaluefordirectory_name:EXPDPUsingthedumpdirectory:EXPDPElapsed:00:00:00.00Elapsed:00:00:00.00SpecifytheNameoftheDumpFiletoLoad~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~Pleasespecifytheprefixofthedumpfile(.dmp)toload:Entervalueforfile_name:awrdat_1207_1217Loadingfromthefilename:awrdat_1207_1217.dmpElapsed:00:00:00.00Elapsed:00:00:00.00StagingSchematoLoadAWRSnapshotData~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ThenextstepistocreatethestagingschemawheretheAWRsnapshotdatawillbeloaded.Afterloadingthedataintothestagingschema,thedatawillbetransferredintotheAWRtablesintheSYSschema.ThedefaultstagingschemanameisAWR_STAGE.Tousethisname,press<return>tocontinue,otherwiseenteranalternative.Entervalueforschema_name:Usingthestagingschemaname:AWR_STAGEElapsed:00:00:00.00Elapsed:00:00:00.00ChoosetheDefaulttablespacefortheAWR_STAGEuser~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ChoosetheAWR_STAGEusers'sdefaulttablespace.ThisisthetablespaceinwhichtheAWRdatawillbestaged.TABLESPACE_NAME--------------------------------------------------------------------------------CONTENTSDEFAULTTABLESPACE---------------------------------------------EXAMPLEPERMANENTSYSAUXPERMANENT*USERSPERMANENTElapsed:00:00:00.01Pressing<return>willresultintherecommendeddefaulttablespace(identifiedby*)beingused.Entervaluefordefault_tablespace:UsingtablespaceSYSAUXasthedefaulttablespacefortheAWR_STAGEElapsed:00:00:00.00Elapsed:00:00:00.00ChoosetheTemporarytablespacefortheAWR_STAGEuser~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ChoosetheAWR_STAGEuser'stemporarytablespace.TABLESPACE_NAME--------------------------------------------------------------------------------CONTENTSDEFAULTTEMPTABLESPACE--------------------------------------------------TEMPTEMPORARY*Elapsed:00:00:00.02Pressing<return>willresultinthedatabase'sdefaulttemporarytablespace(identifiedby*)beingused.Entervaluefortemporary_tablespace:UsingtablespaceTEMPasthetemporarytablespaceforAWR_STAGEElapsed:00:00:00.00Elapsed:00:00:00.00...CreatingAWR_STAGEuserElapsed:00:00:00.16Elapsed:00:00:00.05Elapsed:00:00:00.00||~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~|LoadingtheAWRdatafromthefollowing|directory/file:|/rman/expdp|awrdat_1207_1217.dmp|~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~||***AWRLoadStarted...||Thisoperationwilltakeafewmoments.The|progressoftheAWRloadoperationcanbe|monitoredinthefollowingdirectory/file:|/rman/expdp|awrdat_1207_1217.log|Elapsed:00:00:00.02Elapsed:00:00:56.84Elapsed:00:00:25.56...DroppingAWR_STAGEuserElapsed:00:00:17.97EndofAWRLoad（3）生成awr

需要通过awrrpti.sql脚本生成快照：

11:46:21SYS@messay>@?/rdbms/admin/awrrpti.sql（4）删除导入awr

查询当前快照

selectsnap_id,dbid,instance_numberfromdba_hist_snapshotorderby2,1;

删除导入的快照：

12:07:52SYS@messay>execdbms_workload_repository.DROP_SNAPSHOT_RANGE(LOW_SNAP_ID=>1207,HIGH_SNAP_ID=>1217,DBID=>353046371);

再次查询：

selectsnap_id,dbid,instance_numberfromdba_hist_snapshotorderby2,1;

感谢各位的阅读，以上就是“AWR跨库导出与导入的方法是什么”的内容了，经过本文的学习后，相信大家对AWR跨库导出与导入的方法是什么这一问题有了更深刻的体会，具体使用情况还需要大家实践验证。这里是亿速云，小编将为大家推送更多相关知识点的文章，欢迎关注！