sitemap

RSS地图

收藏本站

设为首页

Oracle研究中心

当前位置:Oracle研究中心 > 故障案例 >

案例:awrload导入awr数据时ORA-06502 ORA-06512 ORA-31640报错

时间:2016-04-22 21:39   来源:Oracle研究中心   作者:惜分飞   点击:

天萃荷净

异机使用awrload导入awr数据ORA-06502/ORA-06512错误

出现两种常见错误说明

SQL> @?/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           ~
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Oracle AWR报告分析 Specify the Directory Name ~~~~~~~~~~~~~~~~~~~~~~~~~~ Directory Name Directory Path ------------------------------ ------------------------------------------------- DATA_PUMP_DIR /u01/oracle/admin/ora11g/dpdump/ ORACLE_OCM_CONFIG_DIR /u01/oracle/oracle/product/11.2.0/db_1/ccr/state XMLDIR /u01/oracle/oracle/product/11.2.0/db_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_70441_70885-vpos-primary Loading from the file name: awrdat_70441_70885-vpos-primary.dmp begin * ERROR at line 1: ORA-06502: PL/SQL: numeric or value error: character string buffer too small ORA-06512: at line 2

因为导出来的awr数据库的编码是AL32UTF8,而现在的编码是ZHS16GBK,所以解决方法是设置NLS_LANG为合适编码

[oracle@xifenfei ~]$ env|grep NLS_LANG
NLS_LANG=AMERICAN_AMERICA.ZHS16GBK
[oracle@xifenfei ~]$ export NLS_LANG=AMERICAN_AMERICA.AL32UTF8

ORA-31640

SQL> @?/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_PUMP_DIR                  /u01/oracle/admin/ora11g/dpdump/
ORACLE_OCM_CONFIG_DIR          /u01/oracle/oracle/product/11.2.0/db_1/ccr/state
XMLDIR                         /u01/oracle/oracle/product/11.2.0/db_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_70441_70885-vpos-primary  <--注意输入

Loading from the file name: awrdat_70441_70885-vpos-primary.dmp

…………

| ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
|  Loading the AWR data from the following
|  directory/file:
|   /u01/oracle/admin/ora11g/dpdump/
|   awrdat_70441_70885-vpos-primar.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/oracle/admin/ora11g/dpdump/
|   awrdat_70441_70885-vpos-primar.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-31640: unable to open dump file 
"/u01/oracle/admin/ora11g/dpdump/awrdat_70441_70885-vpos-primar.dmp" for read
ORA-27037: unable to obtain file status
Linux Error: 2: No such file or directory
Additional information: 3
ORA-06512: at "SYS.DBMS_SWRF_INTERNAL", line 1717
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 2920
ORA-00942: table or view does not exist
ORA-06512: at line 3


... Dropping AWR_STAGE user

End of AWR Load

这里我们可以看到,dmp文件名为31个字符,而在提示文件名的时候是30个字符,从而出现了dmp文件不存在而导致的相关错误,解决方案重命名dmp文件,名称不超过30个字符
 

-----------------温馨提示--------------------
操作有风险,动手需谨慎
Oracle研究中心
http://www.oracleplus.net
本文由大师惜分飞原创分享,转载请尽量保留本站网址

--------------------------------------ORACLE-DBA----------------------------------------

最权威、专业的Oracle案例资源汇总之案例:awrload导入awr数据时ORA-06502 ORA-06512 ORA-31640报错

本文由大师惜分飞原创分享,网址:http://www.oracleplus.net/arch/106.html

Oracle研究中心

关键词:

awrload导入awr数据时ORA-06502 ORA-06512 ORA-31640