• expdp迁移是否迁移JOB


    客户疑问,11.2.0.3expdp按照用户进行导出,导入到11.2.0.4环境下,确认JOB是否导出;

    JOB测试
    1.疑问,什么情况下JOB是正常的;
    2.数据泵expdp,impdp是否会迁移JOB;
    3.如何手工迁移JOB

    一、 创建测试JOB,模拟正常现象

    create table TT(C_DATE DATE); 
    create or replace procedure PRO_TT is
     begin 
     insert into TT values(sysdate); 
     commit;
     end;
     /
    exec PRO_TT;
    SQL> select * from tt;
    
    declare   
    job number;      
    begin
    dbms_job.submit(job,'PRO_TT;',sysdate,'sysdate+1/1440');
    end;
    /

    SQL> col what for a20
    SQL> select JOB,WHAT,BROKEN from user_jobs
    JOB WHAT B
    ---------- -------------------- -
    3 PRO_TT; N

    begin

    dbms_job.run(3);
    end;
    /

    SQL> select * from tt;
    C_DATE
    ------------
    08-JAN-20
    08-JAN-20

     

    二 、模拟数据泵导出,观察是否能迁移JOB

    $expdp scott/tiger directory=dump dumpfile=scott.dmp
    
    . . exported "SCOTT"."T_PART_TABLE":"PART_P180613" 0 KB 0 rows
    Master table "SCOTT"."SYS_EXPORT_SCHEMA_01" successfully loaded/unloaded
    ******************************************************************************
    Dump file set for SCOTT.SYS_EXPORT_SCHEMA_01 is:
    /home/oracle/scott.dmp
    
    Estimate in progress using BLOCKS method...
    Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
    Total estimation using BLOCKS method: 1.265 GB
    Processing object type SCHEMA_EXPORT/USER
    Processing object type SCHEMA_EXPORT/SYSTEM_GRANT
    Processing object type SCHEMA_EXPORT/ROLE_GRANT
    Processing object type SCHEMA_EXPORT/DEFAULT_ROLE
    Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA
    Processing object type SCHEMA_EXPORT/TABLE/TABLE
    Processing object type SCHEMA_EXPORT/TABLE/COMMENT
    Processing object type SCHEMA_EXPORT/PROCEDURE/PROCEDURE
    Processing object type SCHEMA_EXPORT/PROCEDURE/ALTER_PROCEDURE
    Processing object type SCHEMA_EXPORT/TABLE/INDEX/INDEX
    Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
    Processing object type SCHEMA_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
    Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/REF_CONSTRAINT
    Processing object type SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
    
    Processing object type SCHEMA_EXPORT/JOB
    
    . . exported "SCOTT"."A1":"P1" 3.876 MB 9575 rows
    
    impdp scott/tiger directory=dump dumpfile=scott.dmp sqlfile=scott.sql
    
    -- new object type path: SCHEMA_EXPORT/PROCEDURE/PROCEDURE
    CREATE procedure PRO_TT is
    begin
    insert into TT values(sysdate);
    commit;
    end;
    /
    -- new object type path: SCHEMA_EXPORT/PROCEDURE/ALTER_PROCEDURE
    
    ALTER PROCEDURE "SCOTT"."PRO_TT" 
    COMPILE 
    PLSQL_OPTIMIZE_LEVEL= 2
    PLSQL_CODE_TYPE= INTERPRETED
    PLSQL_DEBUG= FALSE PLSCOPE_SETTINGS= 'IDENTIFIERS:NONE'
    
    REUSE SETTINGS TIMESTAMP '2020-01-08 03:13:00'
    /
    
    
    
    -- new object type path: SCHEMA_EXPORT/JOB
    BEGIN SYS.DBMS_IJOB.SUBMIT(
    JOB=> 3,
    LUSER=> 'SCOTT',
    PUSER=> 'SCOTT',
    CUSER=> 'SCOTT',
    NEXT_DATE=> TO_DATE('2020-01-08 03:19:32', 'YYYY-MM-DD:HH24:MI:SS'),
    INTERVAL=> 'sysdate+1/1440',
    BROKEN=> FALSE,
    WHAT=> 'PRO_TT;',
    NLSENV=> 'NLS_LANGUAGE=''AMERICAN'' NLS_TERRITORY=''AMERICA'' NLS_CURRENCY=''$''
    NLS_ISO_CURRENCY=''AMERICA'' NLS_NUMERIC_CHARACTERS=''.,''
    NLS_DATE_FORMAT=''DD-MON-RR'' NLS_DATE_LANGUAGE=''AMERICAN'' NLS_SORT=''BINARY''', ENV=> '0102000200000000'); END; / 发现存在BEGIN SYS.DBMS_IJOB.SUBMIT调用这个包的过程,但是如果再新环境存在这个JOB编号,可能会导致创建失败。(未测试)
    手工删除JOB后,实际导入,JOB正常创建,无异常现象。

    begin
    dbms_job.remove(3);
    commit;
    end;

    select JOB,WHAT,BROKEN from user_jobs;

    impdp scott/tiger directory=dump dumpfile=scott.dmp INCLUDE=JOB

    SQL> select JOB,WHAT,BROKEN from user_jobs

    JOB WHAT B
    ---------- -------------------- -
    3 PRO_TT; N

    说明数据泵可以迁移JOB,如果需要单独迁移可以使用数据泵参数 --导出也可以指定用户类型对象JOB导出

    expdp scott/tiger directory=dump dumpfile=scott.dmp

    impdp scott/tiger directory=dump dumpfile=scott.dmp INCLUDE=JOB

      三、手工迁移数据泵JOB

    1.尽可能精选化JOB需要迁移的范围,直说结论,排除NEXT_DATE=4000/1/1的,因为JOB不会再执行了,并且排除BROKEN=Y的损坏的JOB,以及排除用户SYS,SYSMAN等系统用户
    2.对比JOB调用的NEXT_DATE时间,INTERVAL,SCHEMA_USER用户完全一致的情况下,存在无需迁移,不存在需要手工迁移
    3.迁移方式expdp,按照上述expdp导出JOB,然后导入
    手工,根据调用的what 存储过程,以及NEXT_DATE下一次的执行时间,SCHEMA_USER用户,INTERVAL间隔调用参数,手工重建。
    查询DBA_JOBS视图,官方文档视图定义。
    BROKEN
    Y: no attempt is made to run this job YES这个损坏了,不会再尝试运行此作业

      N: an attempt is made to run this job   NO还没怀!

    Is the job BROKEN?

    select job,broken from dba_jobs where job=<job_number>;

    If broken, then check the alert log and trace files to diagnose the issue.

    To remove broken status
    EXEC DBMS_JOB.BROKEN(<job_no>,FALSE);

    Oracle官方定义未找到,找到一个帖子,说明粘贴如下
    https://community.oracle.com/thread/435918
    The job gets broken, if it encounters 16 contiguous failures (which is this case) or you explicitly
    call the dbms_job.broken(job_id, true). In both cases, the atribute broken is set to Y and next_date is set to 1-1-4000.
    The next_date will be reset back when you reset the broken state using dbms_job.broken(job_id, false).
    However, the failure counter is not reset back to 0 until succesfull completion of the job. That means,
    if there is an error in the job procedure (the one specified in what attribute), the job will get broken again
    after the first attempt to execute it.
    You can manualy force the job to run by calling dbms_job.run(job_id). Look in your alert log,
    if there is an error reported for the procedure executed by this job.

    经过查找了一些资料,了解到,假如某一个JOB执行的失败次数,连续超过了16次,那么该JOB的NEXT_TIME就会变成了4000/1/1日,即不再让该JOB执行了,
    也即该JOB已被数据库认定为失效JOB。
    实际测试,删除存储过程,让JOB跑失败。但是失败8次后,JOB=3的,Broken=Y,不清楚是否会计算累加,并且NEXT_DATE正常。 JOB LOG_USER PRIV_USER SCHEMA_USE INTERVAL WHAT B NEXT_DATE
    ---------- ---------- ---------- ---------- ------------------------------ -------------------- - ------------ 23 SCOTT SCOTT SCOTT sysdate+3/1440 PRO_TT; N 08-JAN-20 3 SCOTT SCOTT SCOTT sysdate+1/1440 PRO_TT; Y 01-JAN-00 经过查找了一些资料,了解到,假如某一个JOB执行的失败次数,连续超过了16次,那么该JOB的NEXT_TIME就会变成了4000/1/1日,
    即不再让该JOB执行了,也即该JOB已被数据库认定为失效JOB。 Errors
    in file /11.2.0.4/app/oracle/diag/rdbms/gbk11/gbk11/trace/gbk11_j000_11578.trc: ORA-12012: error on auto execute of job 23 ORA-06550: line 1, column 96: PLS-00905: object SCOTT.PRO_TT is invalid ORA-06550: line 1, column 96: PL/SQL: Statement ignored
    --JOB创建中调用的WHAT=PRO_TT, 创建JOB用户是SCOTT,当前执行时间SYSDATE立即,下一次间隔时间=INTERVAL
    declare   
    job number;      
    begin
    dbms_job.submit(job,'PRO_TT;',sysdate,'sysdate+1/1440');
    end;
    /


  • 相关阅读:
    [GDOI2018]滑稽子图
    单位根反演学习笔记
    ODOO/OPENERP的网页模块QWEB简述
    odoo中的QWeb模板引擎
    项目管理)沟通管理
    从vc6升级到vc7的一些问题及解决方法
    vc++ 2005 发布程序
    颜色取反
    几个VC6.0到VC9.0的错误解决方案
    测试计划测试用例
  • 原文地址:https://www.cnblogs.com/lvcha001/p/12195160.html
Copyright © 2020-2023  润新知