11gAWR数据导入19c数据库

原创董宏伟云和恩墨 2022-12-19

989

结论：AWR数据可以从11g导出导入到高版本19c，高版本导入低版本则不兼容

11gAWR导出

使用awrextr.sql导出

SQL> @?/rdbms/admin/awrextr
~~~~~~~~~~~~~
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
------------ ------------ ------------
* 845494326  GBKDB        db1


The default database id is the local one: ' 845494326'.  To use this
database id, press <return> to continue, otherwise enter an alternative.

Enter value for dbid: 

Using  845494326 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: 

Listing all Completed Snapshots


DB Name        Snap Id    Snap Started
------------ --------- ------------------
GBKDB              267 07 Dec 2022 03:00
                   268 07 Dec 2022 04:00
                   269 07 Dec 2022 18:25
                   270 07 Dec 2022 19:00
                   271 07 Dec 2022 20:00
                   272 07 Dec 2022 21:00
                   273 07 Dec 2022 22:00
                   274 07 Dec 2022 23:00
                   275 08 Dec 2022 00:00
                   276 08 Dec 2022 01:00
                   277 08 Dec 2022 02:00

DB Name        Snap Id    Snap Started
------------ --------- ------------------
GBKDB              278 08 Dec 2022 03:00
                   279 08 Dec 2022 04:00
                   280 08 Dec 2022 05:00
                   281 08 Dec 2022 17:46
                   282 13 Dec 2022 02:11
                   283 13 Dec 2022 03:00
                   284 13 Dec 2022 04:00
                   285 13 Dec 2022 22:27
                   286 13 Dec 2022 23:00
                   287 14 Dec 2022 00:00
                   288 14 Dec 2022 01:00


Specify the Begin and End Snapshot Ids
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Enter value for begin_snap: 275
Begin Snapshot Id specified: 275

Enter value for end_snap: 281
End   Snapshot Id specified: 281


Specify the Directory Name
~~~~~~~~~~~~~~~~~~~~~~~~~~

Directory Name                 Directory Path
------------------------------ -------------------------------------------------
DATA_PUMP_DIR                  /u01/app/oracle/admin/gbkdb/dpdump/
ORACLE_OCM_CONFIG_DIR          /u01/app/oracle/product/11.2.0/dbhome_1/ccr/hosts
                               /db1/state

ORACLE_OCM_CONFIG_DIR2         /u01/app/oracle/product/11.2.0/dbhome_1/ccr/state
TEST                           /tmp/test
XMLDIR                         /u01/app/oracle/product/11.2.0/dbhome_1/rdbms/xml

Choose a Directory Name from the above list (case-sensitive).

Enter value for directory_name: DATA_PUMP_DIR

Using the dump directory: DATA_PUMP_DIR

Specify the Name of the Extract Dump File
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
The prefix for the default dump file name is awrdat_275_281.
To use this name, press <return> to continue, otherwise enter
an alternative.

Enter value for file_name: 

Using the dump file prefix: awrdat_275_281
|
| ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
|  The AWR extract dump file will be located
|  in the following directory/file:
|   /u01/app/oracle/admin/gbkdb/dpdump/
|   awrdat_275_281.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:
|   /u01/app/oracle/admin/gbkdb/dpdump/
|   awrdat_275_281.log
|

End of AWR Extract
SQL> exit
复制

19cAWR导入

导入前检查ASH记录数

SQL> show pdbs

    CON_ID CON_NAME                       OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
         2 PDB$SEED                       READ ONLY  NO
         3 S_PDB                          READ WRITE NO
SQL> conn / as sysdba
Connected.
SQL> select count(*) from dba_hist_active_sess_history ;

  COUNT(*)
----------
       179

SQL>  alter session set container=S_PDB;

Session altered.

SQL> select count(*) from dba_hist_active_sess_history ;

  COUNT(*)
----------
        24
复制

使用awrload.sql导入PDB中

进入dmp文件目录查看产生的log，确认导入正常

SQL> show con_name

CON_NAME
------------------------------
S_PDB
SQL> @?/rdbms/admin/awrload
~~~~~~~~~~
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                                   ~
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

Specify the Directory Name
~~~~~~~~~~~~~~~~~~~~~~~~~~

Directory Name                 Directory Path
------------------------------ -------------------------------------------------
DATA_PUMP_DIR                  /u01/app/oracle/product/19.0.0/db_1/rdbms/log/EBE
                               8A723C6CFD8A3E053C81FA8C05B9D

DBMS_OPTIM_ADMINDIR            /u01/app/oracle/product/19.0.0/db_1/rdbms/admin
DBMS_OPTIM_LOGDIR              /u01/app/oracle/product/19.0.0/db_1/cfgtoollogs
EXP                            /goldengate/exp
JAVA$JOX$CUJS$DIRECTORY$       /u01/app/oracle/product/19.0.0/db_1/javavm/admin/
OPATCH_INST_DIR                /u01/app/oracle/product/19.0.0/db_1/OPatch
OPATCH_LOG_DIR                 /u01/app/oracle/product/19.0.0/db_1/rdbms/log
OPATCH_SCRIPT_DIR              /u01/app/oracle/product/19.0.0/db_1/QOpatch
ORACLE_BASE                    /u01/app/oracle

Directory Name                 Directory Path
------------------------------ -------------------------------------------------
ORACLE_HOME                    /u01/app/oracle/product/19.0.0/db_1
ORACLE_OCM_CONFIG_DIR          /u01/app/oracle/product/19.0.0/db_1/ccr/state
ORACLE_OCM_CONFIG_DIR2         /u01/app/oracle/product/19.0.0/db_1/ccr/state
SDO_DIR_ADMIN                  /u01/app/oracle/product/19.0.0/db_1/md/admin
SDO_DIR_WORK
XMLDIR                         /u01/app/oracle/product/19.0.0/db_1/rdbms/xml
XSDDIR                         /u01/app/oracle/product/19.0.0/db_1/rdbms/xml/sch
                               ema


Choose a Directory Name from the list above (case-sensitive).

Enter value for directory_name: EXP

Using the dump directory: EXP

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_275_281

Loading from the file name: awrdat_275_281.dmp


|
| ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
|  Loading the AWR data from the following
|  directory/file:
|   /goldengate/exp
|   awrdat_275_281.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:
|   /goldengate/exp
|   awrdat_275_281.log
|

End of AWR Load
复制

导入后检查ASH记录数

数据已经导入到pdb中，cdb中记录增长5行，为自身会话正常增长。其中22条为导入数据

SQL> show con_name

S_PDB
SQL> select count(*) from dba_hist_active_sess_history ;

        47
SQL> conn / as sysdba
Connected.
SQL> select count(*) from dba_hist_active_sess_history ;

       184
SQL> select count(*) from containers(dba_hist_active_sess_history);

       231


SQL>  alter session set container=s_pdb;

Session altered.

SQL> select dbid,con_dbid,con_id,count(*) from (dba_hist_active_sess_history) group by  dbid,con_dbid,con_id;

      DBID   CON_DBID     CON_ID   COUNT(*)
---------- ---------- ---------- ----------
 845494326  845494326                    22
3345856831 3222111119          3         37

复制

生成AWR报告

awrrpti.sql脚本生成报告

SQL> alter session set container=s_pdb;

Session altered.

SQL> @?/rdbms/admin/awrrpti

Specify the Report Type
~~~~~~~~~~~~~~~~~~~~~~~
AWR reports can be generated in the following formats.  Please enter the
name of the format at the prompt. Default value is 'html'.

   'html'          HTML format (default)
   'text'          Text format
   'active-html'   Includes Performance Hub active report

Enter value for report_type: 



Type Specified: html


Specify the location of AWR Data
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
AWR_ROOT - Use AWR data from root (default)
AWR_PDB - Use AWR data from PDB
Enter value for awr_location: AWR_PDB

Location of AWR Data Specified: AWR_PDB


Instances in this Workload Repository schema
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
  DB Id      Inst Num   DB Name      Instance     Host
------------ ---------- ---------    ----------   ------
  845494326      1      GBKDB        gbkdb        db1

Enter value for dbid: 845494326
Using 845494326 for database Id
Enter value for inst_num: 1
Using 1 for instance number


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: 

Listing all Completed Snapshots
Instance     DB Name      Snap Id       Snap Started    Snap Level
------------ ------------ ---------- ------------------ ----------

gbkdb        GBKDB              275  08 Dec 2022 00:00    1
                                276  08 Dec 2022 01:00    1
                                277  08 Dec 2022 02:00    1
                                278  08 Dec 2022 03:00    1
                                279  08 Dec 2022 04:00    1
                                280  08 Dec 2022 05:00    1
                                281  08 Dec 2022 17:46    1


Specify the Begin and End Snapshot Ids
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Enter value for begin_snap: 279
Begin Snapshot Id specified: 279

Enter value for end_snap: 280
End   Snapshot Id specified: 280



Specify the Report Name
~~~~~~~~~~~~~~~~~~~~~~~
The default report file name is awrrpt_1_279_280.html.  To use this name,
press <return> to continue, otherwise enter an alternative.

Enter value for report_name: 

Using the report name awrrpt_1_279_280.html
......
......
End of Report
</body></html>

Report written to awrrpt_1_279_280.html
SQL>  

复制

使用命令手工生成报告

保存结果为html文件即可查看

set lines 32767 pages 0 long 999999999 
select output from table(dbms_workload_repository.AWR_REPORT_HTML( 845494326,1,279,280,0));
复制

附：19cAWR导入11g库报错如下

SQL> @?/rdbms/admin/awrload
~~~~~~~~~~
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_PUMP_DIR                  /u01/app/oracle/admin/gbkdb/dpdump/
ORACLE_OCM_CONFIG_DIR          /u01/app/oracle/product/11.2.0/dbhome_1/ccr/hosts
                               /db1/state

ORACLE_OCM_CONFIG_DIR2         /u01/app/oracle/product/11.2.0/dbhome_1/ccr/state
TEST                           /tmp/test
XMLDIR                         /u01/app/oracle/product/11.2.0/dbhome_1/rdbms/xml

Choose a Directory Name from the list above (case-sensitive).

Enter value for directory_name: DATA_PUMP_DIR

Using the dump directory: DATA_PUMP_DIR

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_117_121

Loading from the file name: awrdat_117_121.dmp

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

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
--------------------------- ------------------
SYSAUX
PERMANENT                   *

TS_B
PERMANENT

TS_C
PERMANENT


TABLESPACE_NAME
--------------------------------------------------------------------------------
CONTENTS                    DEFAULT TABLESPACE
--------------------------- ------------------
TTS_NAME
PERMANENT

USERS1
PERMANENT


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


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                   *


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


... Creating AWR_STAGE user

|
| ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
|  Loading the AWR data from the following
|  directory/file:
|   /u01/app/oracle/admin/gbkdb/dpdump/
|   awrdat_117_121.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:
|   /u01/app/oracle/admin/gbkdb/dpdump/
|   awrdat_117_121.log
|
DBMS_DATAPUMP.ADD_FILE(dump file)
ORA-39001: invalid argument value
Exception encountered in AWR_LOAD
begin
*
ERROR at line 1:
ORA-20115: datapump import encountered error:
ORA-39001: invalid argument value
ORA-39000: bad dump file specification
ORA-39142: incompatible version number 5.1 in dump file
"/u01/app/oracle/admin/gbkdb/dpdump/awrdat_117_121.dmp"
ORA-06512: at "SYS.DBMS_SWRF_INTERNAL", line 1751
ORA-06512: at line 3


begin
*
ERROR at line 1:
ORA-20106: AWR tables do not exist for the 'AWR_STAGE' user
ORA-06512: at "SYS.DBMS_SWRF_INTERNAL", line 2954
ORA-00942: table or view does not exist
ORA-06512: at line 3


... Dropping AWR_STAGE user

End of AWR Load
SQL> exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
[oracle@db1 dpdump]$ oerr ora 39142
39142, 00000, "incompatible version number %s in dump file \"%s\""
// *Cause:  A dump file was specified for an import operation whose version
//          number is incompatible with the dump file version of the
//          Data Pump product currently running on the system. Usually this
//          message indicates that the dump file was produced by a newer
//          version of the Data Pump export utility.
// *Action: Import this dump file using the Data Pump import utility with
//          the same version as the export which created the file.
[oracle@db1 dpdump]$ 
复制

最后修改时间：2022-12-19 15:49:46

「喜欢这篇文章，您的关注和赞赏是给作者最好的鼓励」

关注作者

1人已赞赏

duke

2年前

AWR数据可以从11g导出导入到高版本19c，高版本导入低版本则不兼容

2年前