暂无图片
暂无图片
6
暂无图片
暂无图片
暂无图片

19c DBMS_JOB 变化及数据迁移job问题

原创 心在梦在²º²² 2022-07-07
2796

19c DBMS_JOB 变化

Oracle 19c注意事项: DBMS_JOB 变化

DBMS_SCHEDULER 是一种新的JOB调度形式,提供了功能更加强大和跟踪的功能,说是新是相对DBMS_JOB, schedure从10G时引入已经十多年, 用于替换DBMS_JOB,从12c 开始就已经dbms_jobs是deprecated,但是一直可以使用向前兼容,

注意:从ORACLE 19C开始 DBMS_JOB总是以DBMS_SCHEDULER的形式创建,并且dbms_job仍然有效只是多了一层对应关系。
dbms_job也只是调用了dbms_scheduler.

官方文档描述:
Oracle Scheduler replaces the DBMS_JOB package. Although DBMS_JOB is still supported for backward compatibility, Oracle strongly recommends that you switch from DBMS_JOB to Oracle Scheduler.

In upgrades of Oracle Database 19c and later releases, if the upgrade can recreate existing DBMS_JOB jobs using DBMS_SCHEDULER, then for backward compatibility, after the upgrade, DBMS_JOB continues to act as a legacy interface to the DBMS_SCHEDULER job.

1. 不同版本测试

11g 环境下测试

SQL> select * from v$version; BANNER ---------------------------------------------------------------------------------------- Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production PL/SQL Release 11.2.0.4.0 - Production CORE 11.2.0.4.0 Production TNS for Linux: Version 11.2.0.4.0 - Production NLSRTL Version 11.2.0.4.0 - Production -- 创建dbms job SQL> CREATE TABLE SXC.TJ_ARCH (time varchar2(20),id int,cnt int); Table created. SQL> DECLARE X NUMBER; BEGIN SYS.DBMS_JOB.SUBMIT ( job => X ,what => 'insert into SXC.TJ_ARCH select * from (select to_char(first_time, ''yyyy-mm-dd hh24'') FirstTime, THREAD#, count(*) from v$log_history where to_char(first_time, ''yyyy-mm-dd hh24'') =to_char(sysdate - 1 / 24, ''yyyy-mm-dd hh24'') group by to_char(first_time, ''yyyy-mm-dd hh24''), THREAD#);' ,next_date => to_date('23-06-2016 11:00:04','dd/mm/yyyy hh24:mi:ss') ,interval => 'sysdate+1/24' ,no_parse => FALSE ); SYS.DBMS_OUTPUT.PUT_LINE('Job Number is: ' || to_char(x)); COMMIT; END; / PL/SQL procedure successfully completed. -- 检查dbms job SQL> set line222 SQL> col LOG_USER for a10 SQL> col PRIV_USER for a10 SQL> col SCHEMA_USER for a20 SQL> select job,LOG_USER,PRIV_USER ,SCHEMA_USER from dba_jobs order by 1; JOB LOG_USER PRIV_USER SCHEMA_USER ---------- ---------- ---------- -------------------- 84 SXC SXC SXC -- 检查scheduler job SQL> col owner for a20 SQL> col JOB_NAME for a40 SQL> select owner, job_name, state ,substr(job_action,1,50) from dba_scheduler_jobs where owner='SXC'; no rows selected

结论: 只创建了dbms job,并未创建scheduler job.

19c 环境下测试

-- 因为是cdb架构,我们选择同tns的方式连接到其中一个pdb [oracle@ora19c ~]$ sqlplus sxc/sxc@ORCLPDB1; SQL*Plus: Release 19.0.0.0.0 - Production on Thu Jul 7 23:13:33 2022 Version 19.3.0.0.0 Copyright (c) 1982, 2019, Oracle. All rights reserved. Last Successful login time: Thu Jul 07 2022 23:11:33 +08:00 Connected to: Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production Version 19.3.0.0.0 SQL> select * from v$version; BANNER BANNER_FULL BANNER_LEGACY CON_ID ---------------------------------------------------------------------------------- ------------------------- Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production 0 Version 19.3.0.0.0 -- 创建job SQL> CREATE TABLE SXC.TJ_ARCH (time varchar2(20),id int,cnt int); Table created. SQL> DECLARE X NUMBER; BEGIN SYS.DBMS_JOB.SUBMIT ( job => X ,what => 'insert into SXC.TJ_ARCH select * from (select to_char(first_time, ''yyyy-mm-dd hh24'') FirstTime, THREAD#, count(*) from v$log_history where to_char(first_time, ''yyyy-mm-dd hh24'') =to_char(sysdate - 1 / 24, ''yyyy-mm-dd hh24'') group by to_char(first_time, ''yyyy-mm-dd hh24''), THREAD#);' ,next_date => to_date('23-06-2016 11:00:04','dd/mm/yyyy hh24:mi:ss') ,interval => 'sysdate+1/24' ,no_parse => FALSE ); SYS.DBMS_OUTPUT.PUT_LINE('Job Number is: ' || to_char(x)); COMMIT; END; / PL/SQL procedure successfully completed. -- 检查dbms job SQL> set line222 SQL> col LOG_USER for a10 SQL> col PRIV_USER for a10 SQL> col SCHEMA_USER for a20 SQL> select job,LOG_USER,PRIV_USER ,SCHEMA_USER from dba_jobs order by 1; JOB LOG_USER PRIV_USER SCHEMA_USER ---------- ---------- ---------- -------------------- 22 SXC SXC SXC -- 检查scheduler job SQL> col owner for a20 SQL> col JOB_NAME for a40 SQL> select owner, job_name, state ,substr(job_action,1,50) from dba_scheduler_jobs where owner='SXC'; OWNER JOB_NAME STATE SUBSTR(JOB_ACTION,1,50) -------------------- ---------------------------------------- ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------ SXC DBMS_JOB$_22 SCHEDULED insert into SXC.TJ_ARCH select * from (selec

结论: 即创建了dbms job,也创建scheduler job.

注意: dba_jobs view中仍然可以查到 job,同样在dba_scheduler_jobs中也可以查到对应的记录,并且job_name 为 “DBMS_JOB$” 和job num号。

删除job

-- 1.删除dbms job BEGIN SYS.DBMS_JOB.REMOVE(22); COMMIT; END; / PL/SQL procedure successfully completed. -- 2.删除scheduler job begin DBMS_SCHEDULER.DROP_JOB(JOB_NAME => 'DBMS_JOB$_23'); end; / PL/SQL procedure successfully completed.

结论: 19c中,不管用上面哪个方式删除job,dbms_job 和 scheduler_job都会被同时删除.

数据迁移job问题

​ 因为oracle数据库用户很多,当我们采用数据泵方式迁移时候,一般会统一用system用户进行导入/导出,可能会导致普通用户的job作业停止工作。

使用一下语句查看系统里所有的job:

SQL> select LOG_USER,PRIV_USER ,SCHEMA_USER,NEXT_DATE,broken from dba_jobs order by 1; LOG_USER PRIV_USER SCHEMA_USER NEXT_DATE B ---------- ---------- -------------------- ------------------- - SYSTEM EEP EEP 2022-07-03 01:00:00 N

发现所有普通用户job的LOG_USER和PRIV_USER字段都变成了system,,而SCHEMA_USER还是原来的用户的schema名字。这是由于imp导入用户与job的属主用户不同造成的。

解决方法:

  1. 用job属主用户进行导入,不过重新导入麻烦。

  2. 更新dba_jobs视图

-- login sys as sysdba(以sysdba角色登录,执行一下语句修正两个字段LOG_USER和PRIV_USER的值为SCHEMA_USER字段的值) SQL> update dba_jobs set log_user='username',priv_user='username' where schema_user='username'; SQL> commit;

注意: 在19c环境下,执行更新操作,会抛出如下错误,不在支持,在11g环境中可以执行。

SQL> update dba_jobs set priv_user='EEP' where schema_user='EEP'; update dba_jobs set priv_user='EEP' where schema_user='EEP' * ERROR at line 1: ORA-01779: cannot modify a column which maps to a non key-preserved table
最后修改时间:2022-07-21 13:37:19
「喜欢这篇文章,您的关注和赞赏是给作者最好的鼓励」
关注作者
【版权声明】本文为墨天轮用户原创内容,转载时必须标注文章的来源(墨天轮),文章链接,文章作者等基本信息,否则作者和墨天轮有权追究责任。如果您发现墨天轮中有涉嫌抄袭或者侵权的内容,欢迎发送邮件至:contact@modb.pro进行举报,并提供相关证据,一经查实,墨天轮将立刻删除相关内容。

评论