ITPub博客

首页 > Linux操作系统 > Linux操作系统 > AWR导入引发ORA-4031错误

AWR导入引发ORA-4031错误

原创 Linux操作系统 作者:yangtingkun 时间:2011-02-07 23:58:58 0 删除 编辑

尝试在测试数据库上导入AWR数据,引发了ORA-4031错误。

 

 

错误信息为:

SQL> @?/rdbms/admin/awrload
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Disclaimer: This SQL/Plus script. should only be called under
the guidance of Oracle Support.
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

~~~~~~~~~~
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
------------------------------ -------------------------------------------------
ADMIN_DIR                      C:\ADE\aime_10.2_nt_push\oracle/md/admin
AUDIT_DIR                      /tmp/
DATA_FILE_DIR                  E:\oracle\oracle1020\demo\schema\sales_history\
DATA_PUMP_DIR                  E:\oracle\admin\ytk102\dpdump\
D_AWR                          /data/backup
D_OUTPUT                       e:\dmp
D_OUTPUT1                      E:\
LOG_FILE_DIR                   E:\oracle\oracle1020\demo\schema\log\
MEDIA_DIR                      E:\oracle\oracle1020\demo\schema\product_media\
SUBDIR                         E:\oracle\oracle1020\demo\schema\order_entry\/200
                               2/Sep

WORK_DIR                       C:\ADE\aime_10.2_nt_push\oracle/work
XMLDIR                         E:\oracle\oracle1020\demo\schema\order_entry\

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

Enter value for directory_name: D_AWR

Using the dump directory: D_AWR

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_1_47802

Loading from the file name: awrdat_1_47802.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 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
------------------------------ --------- ------------------
EXAMPLE                        PERMANENT
MGMT_ECM_DEPOT_TS              PERMANENT
MGMT_TABLESPACE                PERMANENT
SYSAUX                         PERMANENT *
TEST                           PERMANENT
YANGTK                         PERMANENT

Pressing will result in the recommended default
tablespace (identified by *) being used.

Enter value for default_tablespace: SYSAUX

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 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:
|   /data/backup
|   awrdat_1_47802.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:
|   /data/backup
|   awrdat_1_47802.log
|
Master table "SYS"."SYS_IMPORT_FULL_02" successfully loaded/unloaded
Starting "SYS"."SYS_IMPORT_FULL_02":
Processing object type TABLE_EXPORT/TABLE/TABLE
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
. . imported "AWR_STAGE"."WRH$_SQL_PLAN"                 212.1 MB  769266 rows
. . imported "AWR_STAGE"."WRH$_SQLTEXT"                  114.0 MB   48107 rows
. . imported "AWR_STAGE"."WRH$_ACTIVE_SESSION_HISTORY":"WRH$_ACTIVE_3944144691_46936"  155.2 MB  820442 rows
. . imported "AWR_STAGE"."WRH$_ACTIVE_SESSION_HISTORY":"WRH$_ACTIVE_3944144691_45248"  139.3 MB  725923 rows
. . imported "AWR_STAGE"."WRH$_ACTIVE_SESSION_HISTORY":"WRH$_ACTIVE_3944144691_46552"  135.3 MB  714041 rows
. . imported "AWR_STAGE"."WRH$_ACTIVE_SESSION_HISTORY":"WRH$_ACTIVE_3944144691_47664"  124.4 MB  657252 rows
. . imported "AWR_STAGE"."WRH$_ACTIVE_SESSION_HISTORY":"WRH$_ACTIVE_3944144691_45440"  112.9 MB  594929 rows
Exception encountered in AWR_LOAD
begin
*
ERROR at line 1:
ORA-31626: job does not exist
ORA-39086: cannot retrieve job information
ORA-06512: at "SYS.DBMS_DATAPUMP", line 2745
ORA-06512: at "SYS.DBMS_DATAPUMP", line 3712
ORA-06512: at "SYS.DBMS_SWRF_INTERNAL", line 521
ORA-06512: at "SYS.DBMS_SWRF_INTERNAL", line 659
ORA-06512: at "SYS.DBMS_SWRF_INTERNAL", line 1212
ORA-06512: at line 3


Append Data for AWR_STAGE.WRM$_DATABASE_INSTANCE.
Append Data for AWR_STAGE.WRM$_SNAPSHOT.
Append Data for AWR_STAGE.WRH$_STAT_NAME.
Append Data for AWR_STAGE.WRH$_PARAMETER_NAME.
.
.
.
Append Data for AWR_STAGE.WRH$_ACTIVE_SESSION_HISTORY_BL.
Append Data for AWR_STAGE.WRM$_SNAP_ERROR.
Finished MOVE_TO_AWR procedure
... Dropping AWR_STAGE user
drop user AWR_STAGE cascade
*
ERROR at line 1:
ORA-00604: error occurred at recursive SQL level 2
ORA-04031: unable to allocate 2080 bytes of shared memory ("shared pool","BEGIN
BEGIN
IF (xdb.DB...","PL/SQL DIANA","PAR.C:parchk:page")

 

End of AWR Load

如果仅从屏幕输出看,似乎ORA-4031错误出现在最后drop user处,其实一开始的ORA-31626的错误也是ORA-4031错误引起的,可以通过数据泵的输出日志观察这一点:

[oracle@yans1 backup]$ more awrdat_1_47802.log
Master table "SYS"."SYS_IMPORT_FULL_02" successfully loaded/unloaded
Starting "SYS"."SYS_IMPORT_FULL_02": 
Processing object type TABLE_EXPORT/TABLE/TABLE
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
. . imported "AWR_STAGE"."WRH$_SQL_PLAN"                 212.1 MB  769266 rows
. . imported "AWR_STAGE"."WRH$_SQLTEXT"                  114.0 MB   48107 rows
. . imported "AWR_STAGE"."WRH$_ACTIVE_SESSION_HISTORY":"WRH$_ACTIVE_3944144691_46936"  155.2 MB  820442 rows
. . imported "AWR_STAGE"."WRH$_ACTIVE_SESSION_HISTORY":"WRH$_ACTIVE_3944144691_45248"  139.3 MB  725923 rows
. . imported "AWR_STAGE"."WRH$_ACTIVE_SESSION_HISTORY":"WRH$_ACTIVE_3944144691_46552"  135.3 MB  714041 rows
. . imported "AWR_STAGE"."WRH$_ACTIVE_SESSION_HISTORY":"WRH$_ACTIVE_3944144691_47664"  124.4 MB  657252 rows
. . imported "AWR_STAGE"."WRH$_ACTIVE_SESSION_HISTORY":"WRH$_ACTIVE_3944144691_45440"  112.9 MB  594929 rows
ORA-39097: Data Pump job encountered unexpected error -4031
ORA-39065: unexpected master process exception in MAIN
ORA-04031: unable to allocate 2080 bytes of shared memory ("shared pool","BEGIN :1 := sys.kupc$que_int...","PL/SQL DIANA","PAR.C:par
chk:page")
Job "SYS"."SYS_IMPORT_FULL_02" stopped due to fatal error at 10:04:22

检查数据库配置发现,这个测试数据库的SGA_TARGET只有500M左右,将SGA调整到2G左右,重启数据库,删除AWR_STAGE用户后,重新执行导入,操作顺利完成。

这个AWR导入对于共享池内容要求还比较高的,虽然500MSGA确实小了点,但是对于没有其他人使用的单用户测试系统来说,也说的过去了,至少我Windows环境上的测试数据库一般也就这么大的SGA

 

来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/4227/viewspace-686856/,如需转载,请注明出处,否则将追究法律责任。

请登录后发表评论 登录
全部评论
暂无介绍

注册时间:2007-12-29

  • 博文量
    1954
  • 访问量
    10651872