Oracle作业执行与SQL Develop运行语句（脚本）的区别

问题背景：在SQL Developer界面上执行UPDATE语句是成功的，但在作业上这个UPDATE语句会报错。如目标表有如下触发器：

create or replace TRIGGER CONCEPT."SICKBED_TRG" AFTER UPDATE OF USEDCODEID OR DELETE ON "CONCEPT"."SICKBED" FOR EACH ROW
DECLARE
v_ip varchar2(30);
v_user varchar2(30);
BEGIN
   SELECT sys_context('userenv', 'IP_ADDRESS'), sys_context('userenv', 'SESSION_USER') INTO v_ip, v_user FROM dual;

   CASE
     WHEN UPDATING ('USEDCODEID') THEN
        IF :new.USEDCODEID != :old.USEDCODEID THEN
           INSERT INTO "CONCEPT"."SICKBEDMODIFIEDTRACE"(SICKBEDID, NO, NAME, ORDINAL, GENDERCODEID, ORGANIZATIONID, MODIFYEMPLOYEEID, ISDELETED, ROWVERSION
              , USEDCODEID, PRICEPERDAY, AIRCONDITIONFEE, ITEMID, ISADDFLAG, TYPECODEID, RELEASEDATETIME, REMARK, ISBOOKINGFLAG, ISHIGHQUALITYFLAG, GETWARMTHFEE, GETWARMTHITEMID
              , AIRCONDITIONITEMID, SENDMEDICINEGROUPTYPECODEID, BASEMEDICINEORGANIZATIONID, CHANGEREASONCODEID, GROUPNO, ISTURNOVER, ISHIDE, SICKBEDKINDCODEID, LOCATIONID
              , USEDCODECHANGEDON, ISSPECIAL, ISDAYTIME, OBLIGATECODEID, EMERGENCYFLAG
              , N_NO, N_NAME, N_ORDINAL, N_GENDERCODEID, N_ORGANIZATIONID, N_MODIFYEMPLOYEEID, N_ISDELETED, N_ROWVERSION
              , N_USEDCODEID, N_PRICEPERDAY, N_AIRCONDITIONFEE, N_ITEMID, N_ISADDFLAG, N_TYPECODEID, N_RELEASEDATETIME, N_REMARK, N_ISBOOKINGFLAG, N_ISHIGHQUALITYFLAG, N_GETWARMTHFEE, N_GETWARMTHITEMID
              , N_AIRCONDITIONITEMID, N_SENDMEDICINEGROUPTYPECODEID, N_BASEMEDICINEORGANIZATIONID, N_CHANGEREASONCODEID, N_GROUPNO, N_ISTURNOVER, N_ISHIDE, N_SICKBEDKINDCODEID, N_LOCATIONID
              , N_USEDCODECHANGEDON, N_ISSPECIAL, N_ISDAYTIME, N_OBLIGATECODEID, N_EMERGENCYFLAG
              , USERNAME, IP)
           VALUES(:old.SICKBEDID, :old.NO, :old.NAME, :old.ORDINAL, :old.GENDERCODEID, :old.ORGANIZATIONID, :old.MODIFYEMPLOYEEID, :old.ISDELETED, :old.ROWVERSION
              , :old.USEDCODEID, :old.PRICEPERDAY, :old.AIRCONDITIONFEE, :old.ITEMID, :old.ISADDFLAG, :old.TYPECODEID, :old.RELEASEDATETIME, :old.REMARK, :old.ISBOOKINGFLAG, :old.ISHIGHQUALITYFLAG, :old.GETWARMTHFEE, :old.GETWARMTHITEMID
              , :old.AIRCONDITIONITEMID, :old.SENDMEDICINEGROUPTYPECODEID, :old.BASEMEDICINEORGANIZATIONID, :old.CHANGEREASONCODEID, :old.GROUPNO, :old.ISTURNOVER, :old.ISHIDE, :old.SICKBEDKINDCODEID, :old.LOCATIONID
              , :old.USEDCODECHANGEDON, :old.ISSPECIAL, :old.ISDAYTIME, :old.OBLIGATECODEID, :old.EMERGENCYFLAG
              , :new.NO, :new.NAME, :new.ORDINAL, :new.GENDERCODEID, :new.ORGANIZATIONID, :new.MODIFYEMPLOYEEID, :new.ISDELETED, :new.ROWVERSION
              , :new.USEDCODEID, :new.PRICEPERDAY, :new.AIRCONDITIONFEE, :new.ITEMID, :new.ISADDFLAG, :new.TYPECODEID, :new.RELEASEDATETIME, :new.REMARK, :new.ISBOOKINGFLAG, :new.ISHIGHQUALITYFLAG, :new.GETWARMTHFEE, :new.GETWARMTHITEMID
              , :new.AIRCONDITIONITEMID, :new.SENDMEDICINEGROUPTYPECODEID, :new.BASEMEDICINEORGANIZATIONID, :new.CHANGEREASONCODEID, :new.GROUPNO, :new.ISTURNOVER, :new.ISHIDE, :new.SICKBEDKINDCODEID, :new.LOCATIONID
              , :new.USEDCODECHANGEDON, :new.ISSPECIAL, :new.ISDAYTIME, :new.OBLIGATECODEID, :new.EMERGENCYFLAG
              , v_user, v_ip);
        END IF;
     WHEN DELETING THEN
          RAISE_APPLICATION_ERROR(-20003, '不能物理删除床位表记录');
   END CASE;
END;

由于UPDATE CONCEPT.SICKEBD表时，会通过触发器插入跟踪表数据，跟踪表的字段是不允许空的，如下获取数据库上下文数据在SQL Developer上运行得到正确的值，而在作业获取IP地址则为空（导致UPDATE语句的作业失败）：
SELECT sys_context('userenv', 'IP_ADDRESS'), sys_context('userenv', 'SESSION_USER') INTO v_ip, v_user FROM dual;
所以上述语句需改为：SELECT NVL(sys_context('userenv', 'IP_ADDRESS'), NVL(sys_context('USERENV','HOST'),'JOB_IP')), sys_context('userenv', 'SESSION_USER') INTO v_ip, v_user FROM dual;

其次，关于分布式事务在SQL Developer上能正常运行，而在SQL Developer正常执行，而在作业里面就不能用分布式事务（只能提交Oracle部分，再提交SQL Server链接数据库部分），譬如下面存储过程

create or replace PROCEDURE "APPS"."JOBCORRECTSICKBEDUSEDCODE"
AS
  v_sickbedids VARCHAR2(8000);
BEGIN
  DELETE CONCEPT.SICKBEDMODIFIEDTRACE WHERE TRUNC(MODIFIEDON) < TRUNC(SYSDATE) - 30;
  
  --更新床位状态
  INSERT INTO "CONCEPT"."TBL_ORGANIZATIONSICKBEDTOMODIFY"(ORGANIZATIONID, SICKBEDID)
  select s.ORGANIZATIONID, s.SICKBEDID from concept.sickbed s, entity.organization o
  where s.ORGANIZATIONID=o.ORGANIZATIONID and s.ISDELETED=0 and o.ISDELETED=0 and s.USEDCODEID=1 -- and s.ORGANIZATIONID not in (430,1606) 
  and s.SICKBEDID>0 and s.SICKBEDID not in (select sickbedid from prpa.encounter e
  where e.isdeleted=0 and e.encounterkindcodeid=1 and e.sickbedid>0 --and e.partitionflag=0
  and (e.statuscodeid=1 or (e.statuscodeid=3 and trunc(e.dischargeon)>trunc(sysdate))));

  UPDATE CONCEPT.SICKBED t
  SET t.UsedCodeId = 0
  WHERE t.SICKBEDID IN ( SELECT SICKBEDID FROM CONCEPT.TBL_ORGANIZATIONSICKBEDTOMODIFY WHERE ISMODIFIED=0 );

  SELECT wmsys.wm_concat(ii.EXTENSION) INTO v_sickbedids FROM CONCEPT.TBL_ORGANIZATIONSICKBEDTOMODIFY s, CONCEPT.SICKBEDII ii WHERE s.SICKBEDID=ii.SICKBEDID and s.ISMODIFIED=0;
  --dbms_output.put_line ('v_sickbedids:' || v_sickbedids);

  IF v_sickbedids IS NOT NULL THEN
    DBO.pHIS30_ReleaseSickBedStatus@IP(-1, v_sickbedids);
  END if;

  UPDATE CONCEPT.TBL_ORGANIZATIONSICKBEDTOMODIFY SET ISMODIFIED=1, MODIFIEDON=SYSDATE WHERE ISMODIFIED=0 ;
  COMMIT;
END;

需要改为非分布式事务来处理（作业用时）：

create or replace PROCEDURE "APPS"."JOBCORRECTSICKBEDUSEDCODE"
AS
  v_sickbedids VARCHAR2(8000);
BEGIN
  DELETE CONCEPT.SICKBEDMODIFIEDTRACE WHERE TRUNC(MODIFIEDON) < TRUNC(SYSDATE) - 30;
  
  --更新床位状态
  INSERT INTO "CONCEPT"."TBL_ORGANIZATIONSICKBEDTOMODIFY"(ORGANIZATIONID, SICKBEDID)
  select s.ORGANIZATIONID, s.SICKBEDID from concept.sickbed s, entity.organization o
  where s.ORGANIZATIONID=o.ORGANIZATIONID and s.ISDELETED=0 and o.ISDELETED=0 and s.USEDCODEID=1 -- and s.ORGANIZATIONID not in (430,1606) 
  and s.SICKBEDID>0 and s.SICKBEDID not in (select sickbedid from prpa.encounter e
  where e.isdeleted=0 and e.encounterkindcodeid=1 and e.sickbedid>0 --and e.partitionflag=0
  and (e.statuscodeid=1 or (e.statuscodeid=3 and trunc(e.dischargeon)>trunc(sysdate))));

  UPDATE CONCEPT.SICKBED t
  SET t.UsedCodeId = 0
  WHERE t.SICKBEDID IN ( SELECT SICKBEDID FROM CONCEPT.TBL_ORGANIZATIONSICKBEDTOMODIFY WHERE ISMODIFIED=0 );

  SELECT wmsys.wm_concat(ii.EXTENSION) INTO v_sickbedids FROM CONCEPT.TBL_ORGANIZATIONSICKBEDTOMODIFY s, CONCEPT.SICKBEDII ii WHERE s.SICKBEDID=ii.SICKBEDID and s.ISMODIFIED=0;
  --dbms_output.put_line ('v_sickbedids:' || v_sickbedids);

  UPDATE CONCEPT.TBL_ORGANIZATIONSICKBEDTOMODIFY SET ISMODIFIED=1, MODIFIEDON=SYSDATE WHERE ISMODIFIED=0 ;
  COMMIT;

  IF v_sickbedids IS NOT NULL THEN
    DBO.pHIS30_ReleaseSickBedStatus@IP(-1, v_sickbedids);
  END if;
  COMMIT;
END;

关于分布式事务（两阶段提交）报错处理：
ORA-01591: 锁被未决分布式事务处理 20.8.1052573 持有
或如下报错：

ORA-02054: 未决事务处理 20.15.1052595

ORA-28500: 连接 ORACLE 到非 Oracle 系统时返回此信息:

[Oracle][ODBC SQL Server Driver][SQL Server]对象名 'RECOVER.HS_TRANSACTION_LOG' 无效。 {42S02,NativeErr = 208}

ORA-02063: 紧接着 2 lines (起自 IP)

解决办法：查询dba_2pc_pending视图，可看到确实有此事务，将此分布式事务rollback
rollback force '20.8.1052573';

如上述仍未能回滚该分布式事务，则进行手工清理事务（见下文详解）

【转载】ORACLE悬疑分布式事务问题处理（链接：http://ym.baisou.ltd/post/645.html）
当需要在多个Oracle数据库之间进行数据一致性操作时，就会用到分布式事务。
例如：

insert into T_log@remote_db; —远程(异构)数据库插入
insert into T_local; —本地数据库插入
commit;
分布在本地和远程两个db的事务同时操作，这就构成了一个分布式事务。

分布式事务采用Two-Phase Commit提交机制，保证分布在各个节点的子事务能够全部提交或全部回滚的原子性。在这种机制下，事务处理过程分为三个阶段：

PREPARE：发起分布式事务的节点通知各个关联节点准备提交或回滚。各关联节点此时会做三个事情：刷新redo信息到redo log中；将持有的锁转换为悬疑事务锁；取各节点中最大的SCN号进行同步
COMMIT：写入commited SCN，释放锁资源
FORGET：悬疑事务表和关联的数据库视图信息清理
由于分布式事务涉及到多个数据库之间进行操作，偶尔会遇到一些异常情况（例如系统或网络中断）导致上述三个阶段出现异常，这就在一个或多个节点上，产生不完整的“悬疑分布式事务”。
大多数情况下，出现这种问题，Oracle会由Reco进程进行自动修复，Oracle数据库会在dba_2pc_pending 和dba_2pc_neighbors等多个视图中记录分布式事务相关的信息，事实上reco进程也是基于这些信息去做自动修复的。
Reco进程会尝试连接到其他节点获取分布式事务信息，然后尝试修复失败的事务，并将对应的事务中的记录删除。

但有些情况下（例如节点无法正常访问或事务表中记录的数据不完整），Reco进程不能正常完成这个工作，就会抛出异常。对于分布式事务，对应的异常代码区间是ORA-02040 - ORA-02099，可通过alert日志查看到错误信息。
例如：

ORA-02054: transaction in-doubt
The transaction is neither committed or rolled back locally, and we have lost communication with the global coordinator.
此时往往需要手工处理进行干预。
以下是三种常见的分布式事务问题场景：

[场景一] dba_2pc_pending视图中有数据，但分布式事务已经不存在；
[场景二] 分布式事务存在，但dba_2pc视图中没有数据；
[场景三] 事务和视图数据都有，但是执行commit force或rollback force时hang住。
通过报错会有提示，例如：

ORA-01591: lock held by in-doubt distributed transaction 10.20.360
这个10.20.360就是我们需要检查分布式事务ID

场景一：dba_2pc视图中有数据，但分布式事务已经不存在
视图有数据，那么先检查数据的状态

select * from dba_2pc_pending where local_tran_id=‘10.20.360’;
主要看state字段。
如果事务已经是committed, rollback forced或者commit forced状态，表示事务已经完成了，但是在FORGET阶段处理时，数据库字典的信息没能及时清除。此时，我们调用oracle的清理丢失事务信息的语句就可以完成处理：

execute DBMS_TRANSACTION.PURGE_LOST_DB_ENTRY ('10.20.360');
如果事务是PREPARED状态，但是在事务表中又没有活动的事务：

SELECT KTUXEUSN, KTUXESLT, KTUXESQN, /* Transaction ID */
KTUXESTA Status,
KTUXECFL Flags
FROM x$ktuxe
WHERE ktuxesta!=‘INACTIVE’
AND ktuxeusn= 10; —注意替换这里的回滚段号(xid=usn.slot.(sqn+1))
-—-没有活动的事务
那此时需要手工清理丢失事务的信息

set transaction use rollback segment SYSTEM;
delete from sys.pending_trans$ where local_tran_id = ;
delete from sys.pending_sessions$ where local_tran_id = ;
delete from sys.pending_sub_sessions$ where local_tran_id = ;
commit;

场景二：分布式事务存在，但dba_2pc_pending视图中没有数据

遇到ORA-2054, ORA-1591等错误，检查dba_2pc_pending视图没有记录，这种场景不常见，只在少数极端的情况下出现。
先确认现象，分别检查x$ktuxe和 dba_2pc_pending视图，查询语句与场景一相同
在这种情况下无论是执行commit force还是rollback force，都会直接抛出异常：

commit force ‘10.20.360’;
ORA-02058: no prepared transaction found with ID 10.20.360
这时我们需要将视图对应的基表数据补入，然后再执行rollback force。

alter system disable distributed recovery;
insert into pending_trans$ (
LOCAL_TRAN_ID, GLOBAL_TRAN_FMT,
GLOBAL_ORACLE_ID,STATE, STATUS,
SESSION_VECTOR,RECO_VECTOR,TYPE#, FAIL_TIME, RECO_TIME)
values( ‘10.20.360’, /* <== Replace this with your local tran id */
306206, ‘xxxxxxxx.00000.0.0.0’,
‘prepared’,‘P’,
hextoraw( ‘00000001’ ),hextoraw( ‘00000000’ ), 0, sysdate, sysdate );
insert into pending_sessions$
values( ‘10.20.360’,1, hextoraw(‘00000000’), ‘C’, 0, 1433927502, ‘’, 14);
–1433927502为DBID, 14为userid
commit;
rollback force ‘10.20.360’;
EXECUTE DBMS_TRANSACTION.PURGE_LOST_DB_ENTRY(‘10.20.360’);
–手工清理事务信息

场景三：事务和视图数据都有，但是执行commit force或rollback force时hang住

如果视图和事务表中都有数据，而且状态是PREPARED，先执行commit force或rollback force，通常就能解决问题，但有时候也会遇到执行force处理时hang住。
尝试purge事务信息时，有提示报错：

BEGIN DBMS_TRANSACTION.PURGE_LOST_DB_ENTRY(‘10.20.360’); END;
*
ERROR at line 1:
ORA-06510: PL/SQL: unhandled user-defined exception
ORA-06512: at “SYS.DBMS_TRANSACTION”, line 94
ORA-06512: at line 1
此时需要进行场景一和场景二的结合起来的所有步骤：

先将视图对应的基表数据删除
delete from sys.pending_trans$ where local_tran_id = ‘10.20.360’;
delete from sys.pending_sessions$ where local_tran_id = ‘10.20.360’;
delete from sys.pending_sub_sessions$ where local_tran_id =‘10.20.360’;
commit;
再插入pending_trans 和 pendingsessions 和pending_sessions 和pendingsessions数据，见场景二
rollback force ‘10.20.360’;
Purge the transaction:
exec dbms_transaction.purge_lost_db_entry(‘10.20.360’);

完整实例：http://github.crmeb.net/u/defu
来自 “开源世界 ” ，【转载】链接：http://ym.baisou.ltd/post/645.html

posted @ 2023-09-06 10:40 Chr☆s 阅读(71) 评论(0) 编辑收藏举报

会员力量，点亮园子希望

刷新页面返回顶部

Chr☆s Kwok 的技术笔记

.NET, C#, WPF, WCF, WF, .NetCore & LINQ ... I know how it works because I know why it works ...

Oracle作业执行与SQL Develop运行语句（脚本）的区别

公告