ORACLE 19c Job 迁移
一、背景
Oracle 数据库中，如何迁移 job?
二、实施步骤
1. 查看当前数据库中的job
SYS@nocdb> select job, log_user,priv_user ,schema_user,next_date from dba_jobs order by 1;
JOB LOG_USER PRIV_USER SCHEMA_USER NEXT_DATE
---------- --------------- --------------- -------------------- ---------------------------------------------------------------------------
2 SXC SXC SXC 01-JUN-23 02.22.02.000000 PM +08:00
2. expdp导出job
[oracle@ora19c dmp]$ expdp \'/ AS SYSDBA\' directory=DIR_EXP dumpfile=jobs.dmp full=y include=job LOGFILE=job_expdp.log
Export: Release 19.0.0.0.0 - Production on Thu Jun 1 13:30:34 2023
Version 19.3.0.0.0
Copyright (c) 1982, 2019, Oracle and/or its affiliates. All rights reserved.
Password:
Connected to: Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Starting "SYS"."SYS_EXPORT_FULL_01": "/******** AS SYSDBA" directory=DIR_EXP dumpfile=jobs.dmp full=y include=job LOGFILE=job_expdp.log
Processing object type DATABASE_EXPORT/SCHEMA/JOB
Master table "SYS"."SYS_EXPORT_FULL_01" successfully loaded/unloaded
******************************************************************************
Dump file set for SYS.SYS_EXPORT_FULL_01 is:
/home/oracle/dmp/jobs.dmp
Job "SYS"."SYS_EXPORT_FULL_01" successfully completed at Thu Jun 1 13:30:42 2023 elapsed 0 00:00:05
3. impdp导入job
--1)这里，我们就在原环境导入，先把原来的job删掉
SYS@nocdb> BEGIN
2 SYS.DBMS_JOB.REMOVE(2);
3 COMMIT;
4 END;
5 /
PL/SQL procedure successfully completed.
SYS@nocdb> select job, log_user,priv_user ,schema_user,next_date from dba_jobs order by 1;
no rows selected
--2)impdp导入job
[oracle@ora19c dmp]$ impdp \'/ AS SYSDBA\' directory=DIR_EXP dumpfile=jobs.dmp LOGFILE=job_impdp.log
Import: Release 19.0.0.0.0 - Production on Thu Jun 1 13:33:18 2023
Version 19.3.0.0.0
Copyright (c) 1982, 2019, Oracle and/or its affiliates. All rights reserved.
Password:
Connected to: Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Master table "SYS"."SYS_IMPORT_FULL_01" successfully loaded/unloaded
Starting "SYS"."SYS_IMPORT_FULL_01": "/******** AS SYSDBA" directory=DIR_EXP dumpfile=jobs.dmp LOGFILE=job_impdp.log
Processing object type DATABASE_EXPORT/SCHEMA/JOB
Job "SYS"."SYS_IMPORT_FULL_01" successfully completed at Thu Jun 1 13:33:24 2023 elapsed 0 00:00:03
4. 检查job
SYS@nocdb> select job, log_user,priv_user ,schema_user,next_date from dba_jobs order by 1;
JOB LOG_USER PRIV_USER SCHEMA_USER NEXT_DATE
---------- --------------- --------------- -------------------- ---------------------------------------------------------------------------
2 SYS SXC SXC 01-JUN-23 02.22.02.000000 PM +00:00
结论：job 已成功导入到数据库中，但是我们可以看到LOG_USER是我们导入的用户SYS，并不是之前的SXC用户。
但是，我们知道LOG_USER即使是SYS用户，其实并不影响job正常执行。如果客户要求LOG_USER必须和原环境保持一致，那么，我们需要怎么修改呢？
在11g 环境中，我们可以直接update dba_jobs视图，在19c环境中，不行，会抛出如下错误：参考文档：https://www.modb.pro/db/430507
SYS@nocdb> update dba_jobs set log_user='SXC' where schema_user='SXC';
update dba_jobs set log_user='SXC' where schema_user='SXC'
*
ERROR at line 1:
ORA-01779: cannot modify a column which maps to a non key-preserved table
5. 修改log_user
方法1： 使用toad等工具
– 1) 在原环境上，通过toad 连接数据库，捞取job创建语句
DECLARE
X NUMBER;
user_name varchar2(30);
BEGIN
select user into user_name from dual;
execute immediate 'alter session set current_schema = SXC';
BEGIN
SYS.DBMS_JOB.SUBMIT
( job => X
,what => 'part_tab_add_delete;'
,next_date => to_date('01/06/2023 14:22:02','dd/mm/yyyy hh24:mi:ss')
,interval => 'sysdate+1/24'
,no_parse => FALSE
);
SYS.DBMS_OUTPUT.PUT_LINE('Job Number is: ' || to_char(x));
execute immediate 'alter session set current_schema = ' || user_name ;
EXCEPTION
WHEN OTHERS THEN
execute immediate 'alter session set current_schema = ' || user_name ;
RAISE;
END;
COMMIT;
END;
/
–2) 连接到SXC用户下执行
SXC@nocdb> show user
USER is "SXC"
SXC@nocdb> DECLARE
X NUMBER;
user_name varchar2(30);
BEGIN
select user into user_name from dual;
execute immediate 'alter session set current_schema = SXC';
BEGIN
SYS.DBMS_JOB.SUBMIT
( job => X
,what => 'part_tab_add_delete;'
,next_date => to_date('01/06/2023 14:22:02','dd/mm/yyyy hh24:mi:ss')
,interval => 'sysdate+1/24'
,no_parse => FALSE
);
SYS.DBMS_OUTPUT.PUT_LINE('Job Number is: ' || to_char(x));
execute immediate 'alter session set current_schema = ' || user_name ;
EXCEPTION
WHEN OTHERS THEN
execute immediate 'alter session set current_schema = ' || user_name ;
RAISE;
END;
COMMIT;
END;
24 /
PL/SQL procedure successfully completed.
SYS@nocdb> select job, log_user,priv_user ,schema_user,next_date from dba_jobs order by 1;
JOB LOG_USER PRIV_USER SCHEMA_USER NEXT_DATE
---------- --------------- --------------- -------------------- ---------------------------------------------------------------------------
45 SXC SXC SXC 01-JUN-23 02.22.02.000000 PM +08:00
结论：job 创建成功，log_user 和 原环境保持一致，但是job 号从2变成了45。注意：需要有create job权限，否则创建job会抛出如下错误：
ERROR at line 1: ORA-27486: insufficient privileges ORA-06512: at line 20 ORA-06512: at "SYS.DBMS_ISCHED", line 9387 ORA-06512: at "SYS.DBMS_ISCHED", line 9376 ORA-06512: at "SYS.DBMS_ISCHED", line 175 ORA-06512: at "SYS.DBMS_ISCHED", line 9302 ORA-06512: at "SYS.DBMS_IJOB", line 196 ORA-06512: at "SYS.DBMS_JOB", line 168 ORA-06512: at line 8
方法2： 利用dmp文件生成sql语句
–1) 如果没有toad等工具，我们可以利用impdp中sqlfile参数，生成创建job的语句
[oracle@ora19c dmp]$ impdp \'/ AS SYSDBA\' directory=DIR_EXP dumpfile=jobs.dmp LOGFILE=job_impdp.log sqlfile=job.sql
Import: Release 19.0.0.0.0 - Production on Thu Jun 1 13:51:19 2023
Version 19.3.0.0.0
Copyright (c) 1982, 2019, Oracle and/or its affiliates. All rights reserved.
Password:
Connected to: Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Master table "SYS"."SYS_SQL_FILE_FULL_01" successfully loaded/unloaded
Starting "SYS"."SYS_SQL_FILE_FULL_01": "/******** AS SYSDBA" directory=DIR_EXP dumpfile=jobs.dmp LOGFILE=job_impdp.log sqlfile=job.sql
Processing object type DATABASE_EXPORT/SCHEMA/JOB
Job "SYS"."SYS_SQL_FILE_FULL_01" successfully completed at Thu Jun 1 13:51:24 2023 elapsed 0 00:00:02
-- 查看创建job语句
[oracle@ora19c dmp]$ cat job.sql
-- CONNECT SYS
ALTER SESSION SET EVENTS '10150 TRACE NAME CONTEXT FOREVER, LEVEL 1';
ALTER SESSION SET EVENTS '10904 TRACE NAME CONTEXT FOREVER, LEVEL 1';
ALTER SESSION SET EVENTS '25475 TRACE NAME CONTEXT FOREVER, LEVEL 1';
ALTER SESSION SET EVENTS '10407 TRACE NAME CONTEXT FOREVER, LEVEL 1';
ALTER SESSION SET EVENTS '10851 TRACE NAME CONTEXT FOREVER, LEVEL 1';
ALTER SESSION SET EVENTS '22830 TRACE NAME CONTEXT FOREVER, LEVEL 192 ';
-- new object type path: DATABASE_EXPORT/SCHEMA/JOB
BEGIN SYS.DBMS_IJOB.SUBMIT(
JOB=> 2,
LUSER=> 'SXC',
PUSER=> 'SXC',
CUSER=> 'SXC',
NEXT_DATE=> TO_DATE('2023-06-01 14:22:02', 'YYYY-MM-DD:HH24:MI:SS'),
INTERVAL=> 'sysdate+1/24',
BROKEN=> FALSE,
WHAT=> 'part_tab_add_delete;',
NLSENV=> 'NLS_LANGUAGE=''AMERICAN'' NLS_TERRITORY=''AMERICA'' NLS_CURRENCY=''$'' NLS_ISO_CURRENCY=''AMERICA'' NLS_NUMERIC_CHARACTERS=''.,'' NLS_CALENDAR=''GREGORIAN'' NLS_DATE_FORMAT=''YYYY-MM-DD HH24:MI:SS'' NLS_DATE_LANGUAGE=''AMERICAN'' NLS_SORT=''BINARY'' NLS_TIME_FORMAT=''HH.MI.SSXFF AM'' NLS_TIMESTAMP_FORMAT=''DD-MON-RR HH.MI.SSXFF AM'' NLS_TIME_TZ_FORMAT=''HH.MI.SSXFF AM TZR'' NLS_TIMESTAMP_TZ_FORMAT=''DD-MON-RR HH.MI.SSXFF AM TZR'' NLS_DUAL_CURRENCY=''$'' NLS_COMP=''BINARY'' NLS_LENGTH_SEMANTICS=''BYTE'' NLS_NCHAR_CONV_EXCP=''FALSE''',
ENV=> '0102000000000000');
END;
/
-- fixup virtual columns...
-- done fixup virtual columns
–2) 连接到SXC用户下执行
SXC@nocdb> show user
USER is "SXC"
SXC@nocdb> BEGIN SYS.DBMS_IJOB.SUBMIT(
JOB=> 2,
LUSER=> 'SXC',
PUSER=> 'SXC',
CUSER=> 'SXC',
NEXT_DATE=> TO_DATE('2023-06-01 14:22:02', 'YYYY-MM-DD:HH24:MI:SS'),
INTERVAL=> 'sysdate+1/24',
BROKEN=> FALSE,
WHAT=> 'part_tab_add_delete;',
NLSENV=> 'NLS_LANGUAGE=''AMERICAN'' NLS_TERRITORY=''AMERICA'' NLS_CURRENCY=''$'' NLS_ISO_CURRENCY=''AMERICA'' NLS_NUMERIC_CHARACTERS=''.,'' NLS_CALENDAR=''GREGORIAN'' NLS_DATE_FORMAT=''YYYY-MM-DD HH24:MI:SS'' NLS_DATE_LANGUAGE=''AMERICAN'' NLS_SORT=''BINARY'' NLS_TIME_FORMAT=''HH.MI.SSXFF AM'' NLS_TIMESTAMP_FORMAT=''DD-MON-RR HH.MI.SSXFF AM'' NLS_TIME_TZ_FORMAT=''HH.MI.SSXFF AM TZR'' NLS_TIMESTAMP_TZ_FORMAT=''DD-MON-RR HH.MI.SSXFF AM TZR'' NLS_DUAL_CURRENCY=''$'' NLS_COMP=''BINARY'' NLS_LENGTH_SEMANTICS=''BYTE'' NLS_NCHAR_CONV_EXCP=''FALSE''',
ENV=> '0102000000000000');
END;
13 /
BEGIN SYS.DBMS_IJOB.SUBMIT(
*
ERROR at line 1:
ORA-06550: line 1, column 8:
PLS-00201: identifier 'SYS.DBMS_IJOB' must be declared
ORA-06550: line 1, column 8:
PL/SQL: Statement ignored
结论：执行报错，经过对比可以看到，toad 工具生成的SQL语句是调用的SYS.DBMS_JOB包，而impdp生成的是调用的SYS.DBMS_IJOB包，两者不一样。 除了create job权限，还需要我们重新赋予其他权限，在执行。
--1) 赋予DBMS_IJOB包权限
SYS@nocdb> grant EXECUTE on SYS.DBMS_IJOB to SXC ;
Grant succeeded.
SXC@nocdb> show user
USER is "SXC"
SXC@nocdb> BEGIN SYS.DBMS_IJOB.SUBMIT(
JOB=> 2,
LUSER=> 'SXC',
PUSER=> 'SXC',
CUSER=> 'SXC',
NEXT_DATE=> TO_DATE('2023-06-01 14:22:02', 'YYYY-MM-DD:HH24:MI:SS'),
INTERVAL=> 'sysdate+1/24',
BROKEN=> FALSE,
WHAT=> 'part_tab_add_delete;',
NLSENV=> 'NLS_LANGUAGE=''AMERICAN'' NLS_TERRITORY=''AMERICA'' NLS_CURRENCY=''$'' NLS_ISO_CURRENCY=''AMERICA'' NLS_NUMERIC_CHARACTERS=''.,'' NLS_CALENDAR=''GREGORIAN'' NLS_DATE_FORMAT=''YYYY-MM-DD HH24:MI:SS'' NLS_DATE_LANGUAGE=''AMERICAN'' NLS_SORT=''BINARY'' NLS_TIME_FORMAT=''HH.MI.SSXFF AM'' NLS_TIMESTAMP_FORMAT=''DD-MON-RR HH.MI.SSXFF AM'' NLS_TIME_TZ_FORMAT=''HH.MI.SSXFF AM TZR'' NLS_TIMESTAMP_TZ_FORMAT=''DD-MON-RR HH.MI.SSXFF AM TZR'' NLS_DUAL_CURRENCY=''$'' NLS_COMP=''BINARY'' NLS_LENGTH_SEMANTICS=''BYTE'' NLS_NCHAR_CONV_EXCP=''FALSE''',
ENV=> '0102000000000000');
END;
13 /
PL/SQL procedure successfully completed.
SXC@nocdb> commit; --> 注意，这里需要commit，生成的脚本中没有。
Commit complete.
SYS@nocdb> select job, log_user,priv_user ,schema_user,next_date from dba_jobs order by 1;
JOB LOG_USER PRIV_USER SCHEMA_USER NEXT_DATE
---------- --------------- --------------- -------------------- ---------------------------------------------------------------------------
2 SXC SXC SXC 01-JUN-23 02.22.02.000000 PM +08:00
45 SXC SXC SXC 01-JUN-23 02.22.02.000000 PM +08:00
结论：job 创建成功，job号、log_user 都和 原环境保持一致。但是需要多赋予SYS.DBMS_IJOB包的执行权限。
最后修改时间：2023-06-02 09:02:21
【版权声明】本文为墨天轮用户原创内容，转载时必须标注文章的来源（墨天轮），文章链接，文章作者等基本信息，否则作者和墨天轮有权追究责任。如果您发现墨天轮中有涉嫌抄袭或者侵权的内容，欢迎发送邮件至：contact@modb.pro进行举报，并提供相关证据，一经查实，墨天轮将立刻删除相关内容。
