ORA-31623: a job is not attached to this session via the specified handle

    在使用Oracel Datapump API时碰到ORA-31623(a job is not attached to this session via the specified handle)错误,从故障描述来 看提示 job并没有成功附加到当前session指定的handle。该package的导入导出一直正常运行究竟是什么原因导致的呢?我们拭目以待......

1、故障环境
SQL> select * from v$version where rownum<2;

BANNER
----------------------------------------------------------------
Oracle Database 10g Release 10.2.0.4.0 - 64bit Production

2、异常方式与异常现象
	当前package的使用情况是使用schema为goex_admin执行时正常,而使用goex_webuser执行时收到错误提示。
	即使用不同的用户来执行时存储过程异常.而在此之前类似的问题并不存在,即无论用哪个schema来实现datapump都成功执行。
	被调用的存储过程为:goex_admin.FNO_SYS_DATAPUMP_PKG.export_dump  

-->下面是调用时出现的异常现象
SQL> DECLARE 
  2    dump_name_in VARCHAR2(32767);
  3    overwrite_existing_in CHAR(1);
  4    handle_out NUMBER;
  5    err_num NUMBER;
  6    err_msg VARCHAR2(32767);
  7  
  8  BEGIN 
  9    dump_name_in := 'TEST_EXP';
 10    overwrite_existing_in := 'N';
 11    handle_out := NULL;
 12    err_num := NULL;
 13    err_msg := NULL;
 14  
 15    FNO_SYS_DATAPUMP_PKG.export_dump ( dump_name_in, overwrite_existing_in, handle_out, err_num, err_msg );
 16    DBMS_OUTPUT.Put_Line('HANDLE_OUT = ' || TO_CHAR(handle_out));
 17    DBMS_OUTPUT.Put_Line('ERR_NUM = ' || TO_CHAR(err_num));
 18    DBMS_OUTPUT.Put_Line('ERR_MSG = ' || err_msg);
 19  
 20    DBMS_OUTPUT.Put_Line('');
 21    COMMIT; 
 22  END; 
 23  /
DECLARE
*
ERROR at line 1:
ORA-20001: FNO_SYS_DATAPUMP_PKG.export_dump debugpos=210 (dumpname = TEST_EXP, -->这段信息给出了代码中错误所在的位置为210
handle = null)
ORA-06512: at "GOEX_ADMIN.FNO_SYS_DATAPUMP_PKG", line 738
ORA-06512: at line 15

3、分析异常
-->初步判断有可能是存在权限问题,但以前适用goex_webuser也一直正常,我们来看看代码中的210处位置
BEGIN
   -- Drop table  'Go_Data_Dumping_Job '
   debugpos    := 200;
   drop_db_table( tbl_in => c_db_dump_job_name );    -->此为同一package的过程用于删除已经存在的job
   debugpos    := 210;
   -- Create Datapump Job
   hand        :=                                    -->从210位置来判断,应该是job没有被open,然后就跳到exception的others部分
      DBMS_DATAPUMP.open( operation   => 'EXPORT'    -->由于给出的error 信息不够详细很难以发现到底问题出在哪里
                        , job_mode    => 'SCHEMA'
                        , job_name    => c_db_dump_job_name
                        , version     => 'LATEST' );
EXCEPTION
   WHEN DBMS_DATAPUMP.job_exists THEN
      debugpos    := 220;

      EXECUTE IMMEDIATE   'drop table '
                       || c_db_dump_job_name;

      debugpos    := 230;
      hand        :=
         DBMS_DATAPUMP.open( operation   => 'EXPORT'
                           , job_mode    => 'SCHEMA'
                           , job_name    => c_db_dump_job_name
                           , version     => 'LATEST' );
   WHEN OTHERS THEN
      raise_application_error( -20001,    myspname            
                                       || ' debugpos='       
                                       || TO_CHAR( debugpos )
                                       || ' (dumpname = '
                                       || dump_name_in
                                       || ', handle = '
                                       || NVL( TO_CHAR( hand ), 'null' )
                                       || ')' );
END;
-->修改EXCEPTION的WHEN OTHERS THEN部分,以便能抛出未知异常.使用下面的代码来替换(v_err_code,v_err_msg声明部分略)
   v_err_code  := SQLCODE;
   v_err_msg   := SUBSTR( SQLERRM, 1, 200 );
   DBMS_OUTPUT.put_line( 'Error code: '|| v_err_code );
   DBMS_OUTPUT.put_line( 'Error message: '|| v_err_msg );
     
SQL> show user;
USER is "GOEX_WEBUSER"
SQL> alter package goex_admin.FNO_SYS_DATAPUMP_PKG compile;

Package altered.

SQL> alter package goex_admin.FNO_SYS_DATAPUMP_PKG compile body;

Package body altered.

-->再次执行则ORA-31623错误出现,为故障的解决提供了线索
SQL> DECLARE 
  2    dump_name_in VARCHAR2(32767);
  3    overwrite_existing_in CHAR(1);
  4    handle_out NUMBER;
  5    err_num NUMBER;
  6    err_msg VARCHAR2(32767);
  7  
  8  BEGIN 
  9    dump_name_in := 'X';
 10    overwrite_existing_in := 'N';
 11    handle_out := NULL;
 12    err_num := NULL;
 13    err_msg := NULL;
 14  
 15    goex_admin.FNO_SYS_DATAPUMP_PKG.export_dump ( dump_name_in, overwrite_existing_in, handle_out, err_num, err_msg );
 16    COMMIT; 
 17  END; 
 18  /
DECLARE
*
ERROR at line 1:
ORA-31623: a job is not attached to this session via the specified handle   
ORA-06512: at "GOEX_ADMIN.FNO_SYS_DATAPUMP_PKG", line 709
ORA-06512: at line 15

SQL> ho oerr ora 31623
31623, 00000, "a job is not attached to this session via the specified handle"
// *Cause:  An attempt to reference a job using a handle which is invalid or
//          no longer valid for the current session.
// *Action: Select a handle corresponding to a valid active job or start a
//          new job.

-->分析上面的ORA描述job不能附加到指定的handle.其Action是选择一个有效的handle或启动一个新的job.
-->Google了一下该ORA,有个关于这个问题的描述是由于当前schema(goex_webuser)没有创建表的权限.
-->于是按下面的方式授予goex_webuser建表权限,当时并没有检查该权限是否授予,而是直接为其授予.
SQL> conn sys / as sysdba
Enter password: 
Connected.
SQL> grant create any table to goex_webuser;

Grant succeeded.

SQL> conn goex_webuser/goex_webuser;
Error accessing PRODUCT_USER_PROFILE
Warning:  Product user profile information not loaded!
You may need to run PUPBLD.SQL as SYSTEM
Connected.
SQL> DECLARE 
  2    dump_name_in VARCHAR2(32767);
  3    overwrite_existing_in CHAR(1);
  4    handle_out NUMBER;
  err_num NUMBER;
  5    6    err_msg VARCHAR2(32767);
  7  
  8  BEGIN 
  9    dump_name_in := 'X';
 10    overwrite_existing_in := 'N';
 11    handle_out := NULL;
 12    err_num := NULL;
 13    err_msg := NULL;
 14  
 15    goex_admin.FNO_SYS_DATAPUMP_PKG.export_dump ( dump_name_in, overwrite_existing_in, handle_out, err_num, err_msg );
 16    COMMIT; 
 17  END; 
 18  /

PL/SQL procedure successfully completed.   -->授予create any table权限之后存储过程成功执行了

4、故障再现
-->后续再次使用GOEX_WEBUSER执行时故障依旧
SQL> show user;
USER is "GOEX_WEBUSER"
SQL> DECLARE 
  2    dump_name_in VARCHAR2(32767);
  3    overwrite_existing_in CHAR(1);
  4    handle_out NUMBER;
  5    err_num NUMBER;
  6    err_msg VARCHAR2(32767);
  7  
  8  BEGIN 
  9    dump_name_in := 'N';
 10    overwrite_existing_in := 'N';
 11    handle_out := NULL;
 12    err_num := NULL;
 13    err_msg := NULL;
 14  
 15    goex_admin.FNO_SYS_DATAPUMP_PKG.export_dump ( dump_name_in, overwrite_existing_in, handle_out, err_num, err_msg );
 16    COMMIT; 
 17  END; 
 18  /
DECLARE
*
ERROR at line 1:
ORA-31623: a job is not attached to this session via the specified handle
ORA-06512: at "GOEX_ADMIN.FNO_SYS_DATAPUMP_PKG", line 709
ORA-06512: at line 15

SQL> /
DECLARE
*
ERROR at line 1:
ORA-31623: a job is not attached to this session via the specified handle
ORA-06512: at "GOEX_ADMIN.FNO_SYS_DATAPUMP_PKG", line 709
ORA-06512: at line 15

SQL> /
DECLARE
*
ERROR at line 1:
ORA-31623: a job is not attached to this session via the specified handle
ORA-06512: at "GOEX_ADMIN.FNO_SYS_DATAPUMP_PKG", line 709
ORA-06512: at line 15

5、进一步分析
-->检查alert log file发现下列提示信息,且不论是使用GOEX_WEBUSER还是使用GOEX_ADMIN都出现该提示
kupprdp: master process DM00 started with pid=28, OS id=6858
         to execute - SYS.KUPM$MCP.MAIN('Go_Data_Dumping_Job', 'GOEX_WEBUSER', 
             'KUPC$C_1_20120321131105', 'KUPC$S_1_20120321131105', 0);
kupprdp: worker process DW01 started with worker id=1, pid=29, OS id=6862
         to execute - SYS.KUPW$WORKER.MAIN('Go_Data_Dumping_Job', 'GOEX_WEBUSER');
Wed Mar 21 13:13:00 2012
The value (30) of MAXTRANS parameter ignored.
Wed Mar 21 13:13:10 2012
The value (30) of MAXTRANS parameter ignored. -->这个提示每执行一次就提示一次,对应上面执行语句时使用 “/”来再次执行PL/SQL
The value (30) of MAXTRANS parameter ignored.
The value (30) of MAXTRANS parameter ignored.

Wed Mar 21 14:13:46 2012
The value (30) of MAXTRANS parameter ignored.
kupprdp: master process DM00 started with pid=30, OS id=12040
         to execute - SYS.KUPM$MCP.MAIN('Go_Data_Dumping_Job', 'GOEX_ADMIN', 'KUPC$C_1_20120321141346',
              'KUPC$S_1_20120321141346', 0);
kupprdp: worker process DW01 started with worker id=1, pid=31, OS id=12042
         to execute - SYS.KUPW$WORKER.MAIN('Go_Data_Dumping_Job', 'GOEX_ADMIN');

-->关于从alert log file得到的错误信息,Metalink给出了BUG号为:6347775
-->此提示信息是由于在创建备份主表时使用了如下语句:
-->CREATE TABLE <table_name> (<columns_list>) INITRANS 10 MAXTRANS 30
-->由于Maxtrans参数在10gR1时已经废弃,所以在告警日志中出现提示,不影响数据的导入导出,仅仅是增大告警日志文件的大小
-->由此可知告警日志获得的信息无助于当前故障的解决
-->Metalink上[ID 308388.1]描述ORA-31623为由于流池和Java池导致该故障发生,然使用GOEX_ADMIN导出正常,因此排除这个原因.
                  
6、彻底解决
-->使用sys登陆到数据库,查看视图DBA_DATAPUMP_JOBS 

DBA_DATAPUMP_JOBS:
  Identifies all active Data Pump jobs in the database, regardless of their state, on an instance (or on all instances
for Real Application Clusters). It also show all Data Pump master tables not currently associated with an active job.

-->视图DBA_DATAPUMP_JOBS给出了当前活动JOB相关的一些信息

SQL> conn / as sysdba
Connected.

-->下面的查询发现有一个名为Go_Data_Dumping_Job的job_name且其owner为GOEX_WEBUSER
-->实际上该job_name对应于一个以job_name命名的临时表名
SQL> select owner_name, job_name from dba_datapump_jobs;

OWNER_NAME                     JOB_NAME
------------------------------ ------------------------------
GOEX_WEBUSER                   Go_Data_Dumping_Job

SQL> drop table GOEX_WEBUSER.Go_Data_Dumping_Job purge;
drop table GOEX_WEBUSER.Go_Data_Dumping_Job purge
                        *
ERROR at line 1:
ORA-00942: table or view does not exist

SQL> drop table GOEX_WEBUSER."Go_Data_Dumping_Job" purge;   -->删除该临时表名

Table dropped.

SQL> conn goex_webuser/goex_webuser;
Error accessing PRODUCT_USER_PROFILE
Warning:  Product user profile information not loaded!
You may need to run PUPBLD.SQL as SYSTEM
Connected.
SQL> DECLARE                                   -->再次调用datapump存储过程被成功执行
  2    dump_name_in VARCHAR2(32767);
  3    overwrite_existing_in CHAR(1);
  handle_out NUMBER;
  4    5    err_num NUMBER;
  6    err_msg VARCHAR2(32767);
  7  
  8  BEGIN 
  9    dump_name_in := NULL;
 10    overwrite_existing_in := 'N';
 11    handle_out := NULL;
 12    err_num := NULL;
 13    err_msg := NULL;
 14  
 15    goex_admin.FNO_SYS_DATAPUMP_PKG.export_dump ( dump_name_in, overwrite_existing_in, handle_out, err_num, err_msg );
 16    COMMIT; 
 17  END; 
 18  /

PL/SQL procedure successfully completed.

-->Author:Robinson Cheng
-->Blog: http://blog.csdn.net/robinson_0612

6、修正代码段
	 -->为原来的procedure添加如下代码段以生成schema的名字,以避免在删除由于job产生的表名时错误(v_schema应先声明)
	 SELECT SYS_CONTEXT( 'USERENV', 'CURRENT_USER' ) INTO v_schema FROM dual;
	 -->原来的schema能够导出自身的所有对象,且能够删除导出故障时生成的临时表,因此没有报错
   WHEN DBMS_DATAPUMP.job_exists THEN
            debugpos    := 220;
            EXECUTE IMMEDIATE   'drop table '
                             || v_schema
                             || '.'
                             || c_db_dump_job_name;		
   -->同理procedure drop_db_table也应当添加一个schema参数传入,此处不再演示
   
7、总结
a、在使用Oracle Datapump API时应注意如果定义了job_name则任意导致job失败或挂起的情形都将导致以job_name命名的table存在.
b、对于当前失败的job,再次执行时会碰到job(ORA-31634)已经存在的提示,此时应删除对应表名再使用原来的job_name再次实现Datapump.
c、对于出现的故障一般的建议是不要使用自定义的Job_name,由Oracle自动生成Job_name以自动销毁异常的job.
d、本文的例子得到的是ORA-31623错误而不是ORA-31634,貌似与ORA-31634 毫不相干,但删除job_name后一切正常.
e、在PL/SQL代码中,应尽可能的使用SQLCODE,SQLERRM来抛出不可预知的故障.

本文参与腾讯云自媒体分享计划,欢迎正在阅读的你也加入,一起分享。

发表于

我来说两句

0 条评论
登录 后参与评论

相关文章

来自专栏杨建荣的学习笔记

基于时间点的不完全恢复的例子(r6笔记第9天)

说到不完全恢复,一般有三种场景,基于时间点的不完全恢复,基于scn的不完全恢复,基于cancel的不完全恢复。 三种情况都是不完全恢复采用的方式,而不完全恢复都...

2685
来自专栏跟着阿笨一起玩NET

SET XACT_ABORT ON 数据库事务

转载:http://www.cnblogs.com/rob0121/articles/2320932.html

851
来自专栏数据库新发现

关于checkpoint cnt和checkpoint scn

SQL> alter session set events 'immediate trace name CONTROLF level 10';

1412
来自专栏Hadoop实操

CM启动报InnoDB engine not found分析

将/tmp目录权限修改为777,重启mysql和cloudera-scm-server服务

4205
来自专栏数据和云

从商用到开源:15个维度,全面剖析DB2与MySQL数据库的差异

编辑手记 MySQL是目前最流行的开源数据库,由于其部署方便,运维简单,被广泛用于互联网的各个领域。随着整体IT架构的变更,传统的金融,电信业务,也逐渐走上从商...

5757
来自专栏杨建荣的学习笔记

一个SQL性能问题的优化探索(一)(r11笔记第33天)

今天同事问我一个问题,看起来比较常规,但是仔细分析了一圈,发现实在是有些晕,我隐隐感觉这是一个bug,但是有感觉问题还有很多需要确认和理解的细节。 同事...

3489
来自专栏杨建荣的学习笔记

看似诡异的tablespace online问题(r5笔记第95天)

今天在做一些演示的时候,在虚拟机上装了两套数据库软件,10g和11g的。还是在演示普通数据文件迁移的时候还是碰到了一些意料之外的问题,从当时的情况来看感觉还是比...

3037
来自专栏乐沙弥的世界

MySQL 慢查询日志(Slow Query Log)

    同大多数关系型数据库一样,日志文件是MySQL数据库的重要组成部分。MySQL有几种不同的日志文件,通常包括错误日志文件,二进制日志,通用日志,慢查询日...

982
来自专栏杨建荣的学习笔记

浅谈exp/imp(下) (r5笔记第84天)

相关链接:浅谈exp/imp(上) (r5笔记第81天) 你可能 不了解的dump文件 在工作中,dump文件对于dba而言是再平常不过的文件了。不过因为dum...

3239
来自专栏乐沙弥的世界

SQL*PLus 帮助手册(SP2-0171)

    对于经常在SQL*Plus 下工作的大师们而言,总是时不时查询SQL*Plus的帮助命令。着实太多了,记不住。SQL*Plus下直接提供了help命令来...

2303

扫码关注云+社区