mysql myawr 死锁_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 ~

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

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个字符