Shulou(Shulou.com)05/31 Report--

This article mainly explains the "AWR cross-database export and import method is what", the article explains the content is simple and clear, easy to learn and understand, the following please follow the editor's ideas slowly in-depth, together to study and learn "AWR cross-database export and import method is what" it!

(1) Export 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.comThe default database id is the local one: '353046371. To use thisdatabase id, press to continue, otherwise enter an alternative.Enter value for dbid: Using 353046371 for Database IDSpecify 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 withoutspecifying a number lists all completed snapshots.Enter value for num_days: 5Listing the last 5 days of Completed SnapshotsDB 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:00Specify the Begin and End Snapshot Ids~~Enter value for begin_snap: 1207Begin Snapshot Id specified: 1207Enter value for end_snap: 1217End Snapshot Id specified: 1217Specify 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/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/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/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/ord er_entry/SUBDIR / u01/app/oracle/product/11.2/db_1/demo/schema/ord er_entry//2002/SepXMLDIR / u01/app/oracle/product/11.2/db_1/rdbms/xmlChoose a Directory Name from the above list (case-sensitive). Enter value for directory_name: EXPDP / * uppercase required Lowercase directories will report errors * / Using the dump directory: EXPDPSpecify the Name of the Extract Dump File~~The prefix for the default dump file name is awrdat_1207_1217.To use this name, press to continue, otherwise enteran 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 ExtractSYS@honor1 > exit (2) Import

Scp to the target database server:

[oracle@db-oracle-node1 expdp] $scp * 192.168.204.231:/rman/expdporacle@192.168.204.231's password: awrdat_1207_1217.dmp 100% 12MB 11.8MB/s 00:00 awrdat_1207_1217.log

Import

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/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/si ngle/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.00Choose a Directory Name from the list above (case-sensitive). Enter value for directory_name: EXPDPUsing the dump directory: EXPDPElapsed: 00:00:00.00Elapsed: 00:00:00.00Specify 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_1217Loading from the file name: awrdat_1207_1217.dmpElapsed: 00:00:00.00Elapsed: 00:00:00.00Staging Schema to Load AWR Snapshot Data~~The next step is to create the staging schemawhere the AWR snapshot data will be loaded.After loading the data into the staging schema The data will be transferred into the AWR tablesin the SYS schema.The default staging schema name is AWR_STAGE.To use this name, press to continue, otherwise enteran alternative.Enter value for schema_name: Using the staging schema name: AWR_STAGEElapsed: 00:00:00.00Elapsed: 00:00:00.00Choose the Default tablespace for the AWR_STAGE user~~~Choose the AWR_STAGE users's default tablespace. This is thetablespace in which the AWR data will be staged.TABLESPACE_NAME----CONTENTS DEFAULT TABLESPACE- -- EXAMPLEPERMANENTSYSAUXPERMANENT * USERSPERMANENTElapsed: 00:00:00.01Pressing will result in the recommended defaulttablespace (identified by *) being used.Enter value for default_tablespace: Using tablespace SYSAUX as the defaulttablespace for the AWR_STAGEElapsed: 00:00:00.00Elapsed: 00:00:00.00Choose the Temporary tablespace for the AWR_STAGE user~~~Choose the AWR_STAGE user's temporary tablespace .TABLESPACE _ NAME----CONTENTS DEFAULT TEMP TABLESPACE -TEMPTEMPORARY * Elapsed: 00:00:00.02Pressing will result in the database's default temporarytablespace (identified by *) being used.Enter value for temporary_tablespace: Using tablespace TEMP as the temporarytablespace for AWR_STAGEElapsed: 00:00:00.00Elapsed: 0000 identified by 00.00.. Creating AWR_STAGE userElapsed: 00:00:00.16Elapsed: 00:00:00.05Elapsed: 00 Creating AWR_STAGE userElapsed 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.02Elapsed: 00:00:56.84Elapsed: 00, 00, 00, 25. 56. Dropping AWR_STAGE userElapsed: 00:00:17.97End of AWR Load (3) generates awr

You need to generate a snapshot through an awrrpti.sql script:

11:46:21 SYS@messay > @? / rdbms/admin/awrrpti.sql (4) Delete imported awr

Query the current snapshot

Select snap_id,dbid,instance_number from dba_hist_snapshot order by 2,1

Delete the imported snapshot:

12:07:52 SYS@messay > exec dbms_workload_repository.DROP_SNAPSHOT_RANGE (LOW_SNAP_ID= > 1207 Magneto SNAPTIC ID = > 1217 Magi DBID = > 353046371)

Query again:

Thank you for your reading, the above is the content of "what is the method of AWR cross-database export and import". After the study of this article, I believe you have a deeper understanding of what the method of AWR cross-database export and import is, and the specific use needs to be verified in practice. Here is, the editor will push for you more related knowledge points of the article, welcome to follow!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.