今天在使用awrextr.sql脚本导出AWR数据时碰到这个错误。
数据库版本为10.2.0.5 RAC for HP_UX，错误信息为：
USING the dump file prefix: awrdat_15222_15448 | | ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ | The AWR EXTRACT dump file will be located | IN the following directory/file: | /orabak01/orabak | awrdat_15222_15448.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: | /orabak01/orabak | awrdat_15222_15448.log | Exception encountered IN AWR_EXTRACT ORA-31626: job does NOT exist ORA-31637: cannot CREATE job SYS_EXPORT_TABLE_01 FOR USER SYS ORA-06512: at "SYS.DBMS_SYS_ERROR", line 95 ORA-06512: at "SYS.KUPV$FT_INT", line 672 ORA-39147: cannot migrate DATA Pump queue TABLE ownership TO this instance BEGIN * ERROR at line 1: ORA-31623: a job IS NOT attached TO this SESSION via the specified handle ORA-06512: at "SYS.DBMS_SYS_ERROR", line 79 ORA-06512: at "SYS.DBMS_DATAPUMP", line 938 ORA-06512: at "SYS.DBMS_DATAPUMP", line 4946 ORA-06512: at "SYS.DBMS_SWRF_INTERNAL", line 691 ORA-06512: at "SYS.DBMS_SWRF_INTERNAL", line 1007 ORA-06512: at line 4 |
Oracle的awrextr.sql脚本，本质是利用数据泵导出AWR基表。从错误信息也可以看出，报错发生在DATA PUMP过程中。如果对数据泵有点了解，就会知道，Oracle的数据泵会使用队列表的特性，而ORA-39147错误就是队列表在RAC环境中才可能碰到的问题。
对队列表有一点了解，因此直接在另外的节点上完成了AWR的抽取工作。而回来之后查询了一下MOS，发现导致问题的原因很可能是另外一个节点上存在一个正在运行的数据泵操作。而Oracle的数据泵只允许同时在一个节点上执行。
Mos文档IMPDP Started In RAC Environment Fails With ORA-39147 [ID 879148.1]对这个问题进行了说明，可以通过SELECT STATE FROM DBA_DATAPUMP_JOBS来查看是否存在正在运行的数据泵操作。