导入AWR报告过程发生的常见错误——PATH权限设置

在导入AWR过程中”离奇”报错:

SQL> @?/rdbms/admin/awrload

 

~~~~~~~~~~省略若干~~~~~~~~~~

 

Specify the Directory Name
~~~~~~~~~~~~~~~~~~~~~~~~~~
Directory Name Directory Path
—————————— ————————————————-
DATA_FILE_DIR /home/oracle/app/oracle/product/11.2.0/dbhome_1/d
                               emo/schema/sales_history/
DATA_PUMP_DIR /home/oracle/app/oracle/admin/orcl/dpdump/
IMP /tmp/marshall/imp
LOG_FILE_DIR /home/oracle/app/oracle/product/11.2.0/dbhome_1/d
                               emo/schema/log/

          MEDIA_DIR /home/oracle/app/oracle/product/11.2.0/dbhome_1/d
                               emo/schema/product_media/
         Directory Name Directory Path
         —————————— ————————————————-
         ORACLE_OCM_CONFIG_DIR /home/oracle/app/oracle/product/11.2.0/dbhome_1/c
                               cr/state
         SS_OE_XMLDIR /home/oracle/app/oracle/product/11.2.0/dbhome_1/d
                               emo/schema/order_entry/
         SUBDIR /home/oracle/app/oracle/product/11.2.0/dbhome_1/d
                               emo/schema/order_entry//2002/Sep
         XMLDIR /ade/b/1191423112/oracle/rdbms/xml
         Choose a Directory Name from the list above (case-sensitive).
         Enter value for directory_name: IMP
         Using the dump directory: IMP

 

~~~~~~~~~~省略若干~~~~~~~~~~

 

… Creating AWR_STAGE user

| ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

| Loading the AWR data from the following

| directory/file:

| /tmp/marshall/imp

| awrdat_33512_33690.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:

| /tmp/marshall/imp

| awrdat_33512_33690.log

|

DBMS_DATAPUMP.ADD_FILE(log file)

ORA-39002: invalid operation

Exception encountered in AWR_LOAD

begin

*

ERROR at line 1:

ORA-20115: datapump import encountered error:

ORA-39002: invalid operation

ORA-39070: Unable to open the log file.

ORA-29283: invalid file operation

ORA-06512: at “SYS.UTL_FILE”, line 536

ORA-29283: invalid file operation

ORA-06512: at “SYS.DBMS_SWRF_INTERNAL”, line 1713

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 2679

ORA-00942: table or view does not exist

ORA-06512: at line 3

 

 

… Dropping AWR_STAGE user

 

End of AWR Load

 

突然蹦出十几个ORA错误,别紧张,很多错误号你压根查不到。按理说导入AWR不会有错误,库也是最近建的,没有进行什么DDL操作,回忆一下:

  1. 昨天把数据库切换至归档模式
  2. 昨天把数据库切换至force logging

想到了昨天,却把刚刚进行的操作忘记了,在十分钟前新建了一个文件夹/tmp/marshall/imp,然后在SQLPLUS中使用create directory创建目录IMP,指向/tmp/marshall/imp。这很正常啊,因为之前导入时我也进行过相关操作,错误究竟出在哪呢?

再次仔细查看,原来是使用root用户在/tmp下创建的一系列目录,而oracle用户对此目录无访问权限。

重新在/home/oracle 下用oracle用户创建了一条路径,在SQLPLUS中创建目录,再次导入,成功!

总结:遇到问题,还需平心静气。有时ORA代码不能提供太多帮助时,回忆下之前做过的可能会导致问题的操作,然后进行测试。另外,做任何事情时,尤其是对于DBA来说,更应细心,大胆猜测。对于已成熟的操作步骤,应按部就班,切莫浮躁,迈的步子大了,裤裆容易裂了

对于AWR报告的导入导出,可参照以下两篇经典文章:

盖国强(eygle)《Using Create directory & UTL_FILE in Oracle》http://www.eygle.com/archives/2005/04/using_create_di.html

盖国强(eygle)《AWR 与 Statspack 数据的导出与迁移》http://www.eygle.com/archives/2010/08/awr_statspack_extract.html

普人特福的博客cnzz&51la for wordpress,cnzz for wordpress,51la for wordpress