AWR跨庫匯出與匯入
本文件分為如下幾個部分
1.AWR匯出awrextr.sql
2.AWR匯入awrload.sql
3.為匯入awr生成報告awrrpti.sql
4.刪除匯入的AWR資料dbms_workload_repository.DROP_SNAPSHOT_RANGE
說明：
其實匯出指令碼awrextr.sql/awrload.sql，是建立了expdp/impdp作業，可以透過v$datapump_jobs檢視查詢。
（1）匯出
SYS@honor1 > @?/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 ------------ ------------ ------------ * 353046371 HONOR db-oracle-no de1.cloud.la b.eng.bos.re dhat.com * 353046371 HONOR db-oracle-no de2.cloud.la b.eng.bos.re dhat.com The default database id is the local one: ' 353046371'. To use this database id, press <return> to continue, otherwise enter an alternative. Enter value for dbid: Using 353046371 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 <return> without specifying a number lists all completed snapshots. Enter value for num_days: 5 Listing the last 5 days of Completed Snapshots DB Name Snap Id Snap Started ------------ --------- ------------------ HONOR 1207 05 Apr 2020 01:56 1208 05 Apr 2020 09:38 1209 05 Apr 2020 11:00 1210 05 Apr 2020 12:00 1211 05 Apr 2020 13:00 1212 05 Apr 2020 14:00 1213 05 Apr 2020 15:00 1214 05 Apr 2020 16:00 1215 05 Apr 2020 17:00 1216 05 Apr 2020 18:00 1217 05 Apr 2020 19:00 1218 05 Apr 2020 20:00 1219 05 Apr 2020 21:00 1220 05 Apr 2020 22:01 1221 05 Apr 2020 23:00 1222 06 Apr 2020 01:55 1223 06 Apr 2020 10:34 1224 06 Apr 2020 12:00 1225 06 Apr 2020 13:00 1226 06 Apr 2020 14:00 1227 06 Apr 2020 15:00 1228 06 Apr 2020 16:00 1229 06 Apr 2020 17:00 1230 06 Apr 2020 18:00 1231 06 Apr 2020 19:00 1232 06 Apr 2020 20:00 1233 06 Apr 2020 21:00 1234 07 Apr 2020 10:07 1235 07 Apr 2020 11:00 1236 07 Apr 2020 12:00 1237 07 Apr 2020 13:00 1238 07 Apr 2020 14:00 1239 09 Apr 2020 09:55 1240 09 Apr 2020 11:00 Specify the Begin and End Snapshot Ids ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ Enter value for begin_snap: 1207 Begin Snapshot Id specified: 1207 Enter value for end_snap: 1217 End Snapshot Id specified: 1217 Specify the Directory Name ~~~~~~~~~~~~~~~~~~~~~~~~~~ Directory Name Directory Path ------------------------------ ------------------------------------------------- DATA_FILE_DIR /u01/app/oracle/product/11.2/db_1/demo/schema/sal es_history/ DATA_PUMP_DIR /u01/app/oracle/product/11.2/db_1/rdbms/log/ EXPDP /rman/expdp LOG_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/pro duct_media/ ORACLE_OCM_CONFIG_DIR /u01/app/oracle/product/11.2/db_1/ccr/hosts/db-or acle-node1.cloud.lab.eng.bos.redhat.com/state ORACLE_OCM_CONFIG_DIR2 /u01/app/oracle/product/11.2/db_1/ccr/state SS_OE_XMLDIR /u01/app/oracle/product/11.2/db_1/demo/schema/ord er_entry/ SUBDIR /u01/app/oracle/product/11.2/db_1/demo/schema/ord er_entry//2002/Sep XMLDIR /u01/app/oracle/product/11.2/db_1/rdbms/xml Choose a Directory Name from the above list (case-sensitive). Enter value for directory_name: EXPDP /* 需要大寫，小寫目錄會報錯 */ Using the dump directory: EXPDP Specify the Name of the Extract Dump File ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ The prefix for the default dump file name is awrdat_1207_1217. To use this name, press <return> to continue, otherwise enter an alternative. Enter value for file_name: Using the dump file prefix: awrdat_1207_1217 | | ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ | The AWR extract dump file will be located | in the following directory/file: | /rman/expdp | awrdat_1207_1217.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: | /rman/expdp | awrdat_1207_1217.log | End of AWR Extract SYS@honor1 > exit
（2）匯入
scp到目標資料庫伺服器：
[oracle@db-oracle-node1 expdp]$ scp * 192.168.204.231:/rman/expdp oracle@192.168.204.231's password: awrdat_1207_1217.dmp 100% 12MB 11.8MB/s 00:00 awrdat_1207_1217.log
匯入
11:40:59 SYS@messay > @?/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_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/expdp LOG_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/si ngle/state ORACLE_OCM_CONFIG_DIR2 /oracle/app/oracle/product/11.2/db_1/ccr/state SS_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/Sep XMLDIR /oracle/app/oracle/product/11.2/db_1/rdbms/xml Elapsed: 00:00:00.00 Choose a Directory Name from the list above (case-sensitive). Enter value for directory_name: EXPDP Using the dump directory: EXPDP Elapsed: 00:00:00.00 Elapsed: 00:00:00.00 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: awrdat_1207_1217 Loading from the file name: awrdat_1207_1217.dmp Elapsed: 00:00:00.00 Elapsed: 00:00:00.00 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 <return> to continue, otherwise enter an alternative. Enter value for schema_name: Using the staging schema name: AWR_STAGE Elapsed: 00:00:00.00 Elapsed: 00:00:00.00 Choose the Default tablespace for the AWR_STAGE user ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ Choose the AWR_STAGE users's default tablespace. This is the tablespace in which the AWR data will be staged. TABLESPACE_NAME -------------------------------------------------------------------------------- CONTENTS DEFAULT TABLESPACE --------------------------- ------------------ EXAMPLE PERMANENT SYSAUX PERMANENT * USERS PERMANENT Elapsed: 00:00:00.01 Pressing <return> will result in the recommended default tablespace (identified by *) being used. Enter value for default_tablespace: Using tablespace SYSAUX as the default tablespace for the AWR_STAGE Elapsed: 00:00:00.00 Elapsed: 00:00:00.00 Choose the Temporary tablespace for the AWR_STAGE user ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ Choose the AWR_STAGE user's temporary tablespace. TABLESPACE_NAME -------------------------------------------------------------------------------- CONTENTS DEFAULT TEMP TABLESPACE --------------------------- ----------------------- TEMP TEMPORARY * Elapsed: 00:00:00.02 Pressing <return> will result in the database's default temporary tablespace (identified by *) being used. Enter value for temporary_tablespace: Using tablespace TEMP as the temporary tablespace for AWR_STAGE Elapsed: 00:00:00.00 Elapsed: 00:00:00.00 ... Creating AWR_STAGE user Elapsed: 00:00:00.16 Elapsed: 00:00:00.05 Elapsed: 00:00:00.00 | | ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ | Loading the AWR data from the following | directory/file: | /rman/expdp | awrdat_1207_1217.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: | /rman/expdp | awrdat_1207_1217.log | Elapsed: 00:00:00.02 Elapsed: 00:00:56.84 Elapsed: 00:00:25.56 ... Dropping AWR_STAGE user Elapsed: 00:00:17.97 End of AWR Load
（3）生成awr
需要透過awrrpti.sql指令碼生成快照：
11:46:21 SYS@messay > @?/rdbms/admin/awrrpti.sql
（4）刪除匯入awr
查詢當前快照
select snap_id,dbid,instance_number from dba_hist_snapshot order by 2,1;
刪除匯入的快照：
12:07:52 SYS@messay > exec dbms_workload_repository.DROP_SNAPSHOT_RANGE(LOW_SNAP_ID=>1207,HIGH_SNAP_ID=>1217,DBID=>353046371);
再次查詢：
select snap_id,dbid,instance_number from dba_hist_snapshot order by 2,1;
來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/31439444/viewspace-2685151/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- 匯入和匯出AWR的資料
- Mysql 資料庫匯入與匯出MySql資料庫
- Oracle資料庫匯入匯出。imp匯入命令和exp匯出命令Oracle資料庫
- Angular Excel 匯入與匯出AngularExcel
- OracleDatabase——資料庫表空間dmp匯出與匯入OracleDatabase資料庫
- ASP.NET 開源匯入匯出庫Magicodes.IE 完成Csv匯入匯出ASP.NET
- sqoop部署及匯入與匯出OOP
- Mongodb資料的匯出與匯入MongoDB
- 伺服器批次匯入與匯出伺服器
- [Docker核心之容器、資料庫檔案的匯入匯出、容器映象的匯入匯出]Docker資料庫
- SQL資料庫的匯入和匯出SQL資料庫
- 資料庫 MySQL 資料匯入匯出資料庫MySql
- Activity 流程模型匯入匯出-activity流程模型匯入匯出模型
- Mysql匯入&匯出MySql
- doris匯入匯出
- esayExcel匯入匯出Excel
- js匯入匯出總結與實踐JS
- excel的匯入與匯出---通用版Excel
- mysqldump匯出匯入所有庫、某些庫、某些表的例子MySql
- 開源匯入匯出庫Magicodes.IE 多sheet匯入教程
- vue excel匯入匯出VueExcel
- navlicat 匯入匯出SQLSQL
- Mongodb的備份恢復與匯出匯入MongoDB
- MongoDB--Mongodb 中資料匯出與匯入MongoDB
- .NET 開源匯入匯出庫 Magicodes.IE 2.5釋出
- Windows環境下，動態連結庫（DLL）的“匯入”與“匯出”概念Windows
- SVN庫匯出與還原
- .NET Core開源匯入匯出庫 Magicodes.IE 2.3釋出
- sqoop資料匯入匯出OOP
- Oracle 資料匯入匯出Oracle
- 資料泵匯出匯入
- .net6 匯入匯出
- Oracle資料匯入匯出Oracle
- phpMyAdmin匯入/匯出資料PHP
- 【docker】docker映象匯出匯入Docker
- SpringBoot+Vue之表格的CRUD與匯入匯出Spring BootVue
- sqoop用法之mysql與hive資料匯入匯出OOPMySqlHive
- Vue框架下實現匯入匯出Excel、匯出PDFVue框架Excel