AWRのリポジトリデータを Data Pump export file にエクスポートして他のデータベースにインポートする手順。
インポート
$ scp -i ~/mykey.pem awrdump.dmp clckwrk@ec2-*-***-***-**.ap-northeast-1.compute.amazonaws.com:~/
SQL> set linesize 200
SQL> col owner for a10
SQL> col privilege for a10
SQL> col directory_name for a30
SQL> col grantee for a20
SQL> col directory_path for a50
SQL> select d.owner, d.directory_name, p.grantee, p.privilege, d.directory_path
from dba_directories d, dba_tab_privs p
where d.owner = p.owner
and d.directory_name = p.table_name
order by d.owner, d.directory_name;
OWNER DIRECTORY_NAME GRANTEE PRIVILEGE DIRECTORY_PATH
SYS DATA_PUMP_DIR IMP_FULL_DATABASE READ /oracle/CWDB01/admin/CWDB01/dpdump/
SYS DATA_PUMP_DIR EXP_FULL_DATABASE READ /oracle/CWDB01/admin/CWDB01/dpdump/
SYS DATA_PUMP_DIR IMP_FULL_DATABASE WRITE /oracle/CWDB01/admin/CWDB01/dpdump/
SYS DATA_PUMP_DIR EXP_FULL_DATABASE WRITE /oracle/CWDB01/admin/CWDB01/dpdump/
SYS DATA_PUMP_DIR IMP_FULL_DATABASE READ /oracle/CWDB01/admin/CWDB01/dpdump/
SYS DATA_PUMP_DIR EXP_FULL_DATABASE READ /oracle/CWDB01/admin/CWDB01/dpdump/
SYS DATA_PUMP_DIR IMP_FULL_DATABASE WRITE /oracle/CWDB01/admin/CWDB01/dpdump/
SYS DATA_PUMP_DIR EXP_FULL_DATABASE WRITE /oracle/CWDB01/admin/CWDB01/dpdump/
$ sudo cp awrdump.dmp /oracle/CWDB01/admin/CWDB01/dpdump/
SQL> @?/rdbms/admin/awrload.sql
(中略)
Specify the Directory Name
~~~~~~~~~~~~~~~~~~~~~~~~~~
(中略)
Enter value for directory_name: DATA_PUMP_DIR ★ディレクトリオブジェクト
(中略)
Specify the Name of the Dump File to Load
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Please specify the prefix of the dump file (.dmp) to load:
(中略)
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.
(中略)
Enter value for file_name: awrdump ★ダンプファイル名(拡張子.dmpは自動的に付与されるのでつけない)
(中略)
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.
(中略)
Enter value for schema_name: AWR_STAGE ★テンポラリで作成するスキーマ名(任意の名前でよい)
(中略)
Choose the Temporary tablespace for the AWR_STAGE user
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Choose the AWR_STAGE user's temporary tablespace.
(中略)
Enter value for temporary_tablespace: TEMP ★一時表領域
(中略)
... Creating AWR_STAGE user
|
| ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
| Loading the AWR data from the following
| directory/file:
| /oracle/CWDB01/admin/CWDB01/dpdump/
| awrdump.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:
| /oracle/CWDB01/admin/CWDB01/dpdump/
| awrdump.log
|
... Dropping AWR_STAGE user
End of AWR Load
$ tail -5 /oracle/CWDB01/admin/CWDB01/dpdump/awrdump.log
. . imported "AWR_STAGE"."WRM$_PDB_IN_SNAP":"WRM$_PDB_IN_SNAP_MXDB_MXSN" 0 KB 0 rows in 0 seconds
. . imported "AWR_STAGE"."WRM$_PDB_IN_SNAP_BL" 0 KB 0 rows in 0 seconds
. . imported "AWR_STAGE"."WRM$_WR_CONTROL" 14.76 KB 1 rows in 0 seconds
Completed 220 TABLE_EXPORT/TABLE/TABLE_DATA objects in 28 seconds
Job "SYS"."SYS_IMPORT_FULL_02" successfully completed at Thu Aug 8 18:08:42 2019 elapsed 0 00:00:36