oracle停库对ogg有影响吗,Oracle 11g 测试停库对job的影响-CSDN博客

环境：

OS:Centos 7.5

DB:Oracle 11.2.0.4.0单实例

场景一：job没开始，停库, 再次起库后，起库时间为job开始时间，开始自动运行job,不会自动补停库期间错过的job.

场景二：job运行一半，停库，再次起库后，起库时间为job开始运行时间，不会接着停库前执行一半的job继续执行，job重新开始执行。

场景三：停库前，将job_queue_processes参数设置为0，正在运行的job会继续运行完成，后续的job将不会自动运行。再将job_queue_processes参数改回1000，job将开始运行。

job_queue_processes取值范围为0到1000

当设定该值为0的时候则任意方式创建的job都不会运行。

当设定该值大于1时，且并行执行job时，至少一个为协调进程。其总数不会超出job_queue_processes的值。

实验过程如下：

创建测试数据

---sys

SQL> grant execute on DBMS_LOCK to cjc;

---cjc

SQL> create table t1(id number,itime varchar2(200));

SQL>

CREATE OR REPLACE PROCEDURE test_pro is

begin

for i in 1 .. 10 loop

insert into t1 values (i, to_char(sysdate, 'yyyy/mm/dd hh24:mi:ss'));

DBMS_LOCK.SLEEP(10);

commit;

end loop;

end;

SQL> exec test_pro;

PL/SQL procedure successfully completed.

Elapsed: 00:01:40.09

创建job

SQL>

declare

job_id pls_integer;

begin

sys.dbms_job.submit(job => job_id,

what => 'test_pro;',

next_date => to_date('2020-07-02 22:25:00',

'yyyy-mm-dd hh24:mi:ss'),

interval => 'TRUNC(sysdate,''mi'') + 3 / (24 * 60)');

commit;

end;

场景一：job没开始，停库

select job, schema_user, last_date, this_date, next_date, interval

from dba_jobs

where schema_user = 'CJC';

JOBSCHEMA_USERLAST_DATETHIS_DATENEXT_DATEINTERVAL

123CJC2020/7/2 22:52:142020/7/2 22:55:00TRUNC(sysdate,'mi') + 3 / (24 * 60)

22:54

SQL> shutdown immediate

22:56

SQL> startup

select * from dba_jobs_running;

SIDJOBFAILURESLAST_DATELAST_SECTHIS_DATETHIS_SECINSTANCE

1292302020/7/2 22:52:1422:52:142020/7/2 22:56:2422:56:240

SQL>

select job, schema_user, last_date, this_date, next_date, interval

from dba_jobs

where schema_user = 'CJC';

JOBSCHEMA_USERLAST_DATETHIS_DATE NEXT_DATE INTERVAL

123CJC2020/7/2 22:52:142020/7/2 22:56:242020/7/2 22:55:00TRUNC(sysdate,'mi') + 3 / (24 * 60)

SQL> select count(*) from t1;

COUNT(*)

----------

场景二：job执行一半，停库

select job, schema_user, last_date, this_date, next_date, interval

from dba_jobs

where schema_user = 'CJC';

JOBSCHEMA_USERLAST_DATETHIS_DATENEXT_DATEINTERVAL

123CJC2020/7/2 22:56:242020/7/2 22:59:00TRUNC(sysdate,'mi') + 3 / (24 * 60)

select * from dba_jobs_running

SIDJOBFAILURESLAST_DATELAST_SECTHIS_DATETHIS_SECINSTANCE

1292302020/7/2 22:56:2422:56:242020/7/2 22:59:0022:59:000

SQL> select count(*) from t1;

COUNT(*)

----------

22:59

SQL> shutdown immediate

23:00

SQL> startup

select * from dba_jobs_running

SIDJOBFAILURESLAST_DATELAST_SECTHIS_DATETHIS_SECINSTANCE

1302302020/7/2 22:56:2422:56:242020/7/2 23:00:3323:00:330

select job, schema_user, last_date, this_date, next_date, interval

from dba_jobs

where schema_user = 'CJC';

JOBSCHEMA_USERLAST_DATETHIS_DATENEXT_DATEINTERVAL

123CJC2020/7/2 22:56:242020/7/2 23:00:332020/7/2 22:59:00TRUNC(sysdate,'mi') + 3 / (24 * 60)

SQL> select count(*) from t1;

COUNT(*)

----------

场景三：调节job_queue_processes参数

SQL> show parameter job_queue_processes

SQL> select * from dba_jobs_running;

SQL> alter system set job_queue_processes=0;

SQL> select * from dba_jobs_running;

SQL> alter system set job_queue_processes=1000;

SQL> select * from dba_jobs_running;

SQL> select count(*) from t1;

欢迎关注我的微信公众号"IT小Chen"，共同学习，共同成长！！

viewspace-2702554