Oracle snapshot standby資料庫的scheduler jobs不執行

Oracle snapshot standby資料庫的scheduler jobs不執行

在Oracle 11g中,data guard的快照備用snapshot standby資料庫特性比較適用於快速部署一個臨時的與線上環境相同的測試資料庫,建置方法可參考: .最近在使用過程中發現快照備用snapshot standby資料庫的SCHEDULER JOBS沒有執行,並且在DBA_SCHEDULER_JOBS中也查不到SCHEDULER JOB的資訊.

通過參考文檔Jobs are not working after Dataguard Switchover/Failover (文檔 ID 1292755.1),發現問題出在database_role上,snapshot standby資料庫的v$database.database_role是SNAPSHOT STANDBY,但dba_scheduler_job_roles.database_role卻是跟主庫一致是primary,所以通過dbms_scheduler.set_attribute方法改變對應job_name的database_role即可.
以下是解決方案:
db version:11.2.0.4
os:CentOS 6.6 x86_64
--查看資料庫角色database_role
select database_role from v$database;
/*
DATABASE_ROLE
SNAPSHOT STANDBY
*/
--查看SCHEDULER_JOBS,發現什麼都不顯示
select OWNER,JOB_NAME from DBA_SCHEDULER_JOBS;
/*
OWNER    JOB_NAME
*/
--查看dba_scheduler_job_roles
select job_name,database_role,enabled from dba_scheduler_job_roles;
/*
JOB_NAME                        DATABASE_ROLE    ENABLED
XMLDB_NFS_CLEANUP_JOB            PRIMARY            FALSE
SM$CLEAN_AUTO_SPLIT_MERGE        PRIMARY            TRUE
RSE$CLEAN_RECOVERABLE_SCRIPT    PRIMARY            TRUE
FGR$AUTOPURGE_JOB                PRIMARY            FALSE
BSLN_MAINTAIN_STATS_JOB            PRIMARY            TRUE
DRA_REEVALUATE_OPEN_FAILURES    PRIMARY            TRUE
HM_CREATE_OFFLINE_DICTIONARY    PRIMARY            FALSE
ORA$AUTOTASK_CLEAN                PRIMARY            TRUE
FILE_WATCHER                    PRIMARY            FALSE
PURGE_LOG                        PRIMARY            TRUE
AUTOGATHERACHIEVE                PRIMARY            TRUE
MGMT_STATS_CONFIG_JOB            PRIMARY            TRUE
MGMT_CONFIG_JOB                    PRIMARY            TRUE
RLM$SCHDNEGACTION                PRIMARY            TRUE
RLM$EVTCLEANUP                    PRIMARY            TRUE
*/
--修改需要啟動並執行scheduler job的DATABASE_ROLE
begin
dbms_scheduler.set_attribute(name=>'AUTOGATHERACHIEVE',attribute=>'DATABASE_ROLE',value=>'SNAPSHOT STANDBY');
end;
--查看修改後的dba_scheduler_job_roles,此時DATABASE_ROLE已經修改為SNAPSHOT STANDBY
select job_name,database_role,enabled from dba_scheduler_job_roles where job_name='AUTOGATHERACHIEVE';
/*
JOB_NAME            DATABASE_ROLE        ENABLED
AUTOGATHERACHIEVE    SNAPSHOT STANDBY    TRUE
*/
--查看SCHEDULER_JOBS,也已經顯示出AUTOGATHERACHIEVE這個scheduler job
select owner,job_name from DBA_SCHEDULER_JOBS;
/*
OWNER    JOB_NAME
SYSTEM    AUTOGATHERACHIEVE
*/
--後續通過dba_scheduler_job_log觀察scheduler job是否運行正常

備忘:
在11.2.0.2和11.2.0.3上如果使用dbms_scheduler.set_attribute,可能會出現RA-16612: string value too long for attribute "database_role"的錯誤,可以參考Scheduler Job on a Snapshot Standby database does not exist in DBA_SCHEDULER_JOBS while its logs exist in DBA_SCHEDULER_JOB_RUN_DETAILS (文檔 ID 1551817.1),可嘗試通過apply patch 13399711解決.
當然這個問題的另一個繞開的解決辦法是,基於snapshot standby的原理,自己手動把物理standby轉換成類似的snapshot standby,可參考:http://blog.itpub.net/28539951/viewspace-1767431/,這時由於v$database.database_role是primary,也就不會有上面的問題了.

以下是文檔 ID 1292755.1和1551817.1
    Jobs are not working after Dataguard Switchover/Failover (文檔 ID 1292755.1)   

In this Document
  Symptoms
  Changes
  Cause
  Solution
  References

Applies to:
Oracle Server - Enterprise Edition - Version: 11.1.0.6 to 11.2.0.2 - Release: 11.1 to 11.2
Information in this document applies to any platform.
Symptoms
Scenario:

1) Dataguard 11g with logical standby database

2) Customer implemented DBMS_SCHEDULER  as SYS on both primary and standby site.
The job is listed in DBA_SCHEDULER_JOBS on primary/standby before a switchover/failover-action.

3) Customer performed dataguard switchover.

4) After the switchover the job is gone from view DBA_SCHEDULER_JOBS on NEW primary site,
however the job is visible in DBA_OBJECTS.

 Since the job is still available in dba_objects recreating or dropping the job fails with:

ORA-27477: "SYS.MON_TMP_UNDO_JOB" already exists.

Changes
After upgrade from 10g to 11g  the jobs doesn't work anymore on the new primary in case of a switchover/failover.
This worked fine in 10g.
Cause
A new attribute is introduced in 11g DBMS_SCHEDULER called "DATABASE_ROLE".

Solution

In an Oracle 11g Data Guard environment you have to define the database role ('PRIMARY' or 'LOGICAL STANDBY') in the DBMS_SCHEDULER package.

SQL> select job_name,database_role,enabled from dba_scheduler_job_roles;

 dbms_scheduler.set_attribute(name=>'xxxx',
attribute=>'DATABASE_ROLE',value=>'LOGICAL STANDBY');

For more details please see

Oracle? Data Guard, Concepts and Administration, 11g Release 2 (11.2)
Appendix C.8.2: Unsupported PL/SQL Supplied Packages

and

Oracle Database, PL/SQL Packages and Types Reference, 11g Release 2 (11.2)
Chapter 128: DBMS_SCHEDULER

and

Oracle? Database Administrator's Guide, 11g Release 2 (11.2)
Scheduler Support for Oracle Data Guard

#########################################################################################
    Scheduler Job on a Snapshot Standby database does not exist in DBA_SCHEDULER_JOBS while its logs exist in DBA_SCHEDULER_JOB_RUN_DETAILS (文檔 ID 1551817.1)   

In this Document
    Symptoms
    Cause
    Solution
    References

Applies to:
Oracle Database - Enterprise Edition - Version 11.2.0.3 and later
Information in this document applies to any platform.
Symptoms

+ Oracle Database 11.2.0.3 (without fix of Bug 13399711).

+ On a Snapshot Standby database, a scheduler job record is not shown in DBA_SCHEDULER_JOBS view when its logs exist in DBA_SCHEDULER_JOB_RUN_DETAILS view.

+ Health Check reports zero potential errors on this instance.

+ There is an entry for this concerned job in OBJ$ & SCHEDULER$_JOB. However, no record is shown up in DBA_SCHEDULER_JOBS view.

+ Recreating this job errors out with ORA-27477 error (claiming that job already exists) as it actually exists in OBJ$ & SCHEDULER$_JOB.

+ Dropping this scheduler job or setting its DATABASE_ROLE to LOGICAL STANDBY errors out with ORA-27476 error claiming that it does not exist as it does not show up in DBA_SCHEDULER_JOBS view.

+ When trying to set the database_role of the job to SNAPSHOT STANDBY, the following error is reported:
SQL> exec DBMS_SCHEDULER.SET_ATTRIBUTE('&scheduler_job_owner.&scheduler_job_name','database_role','SNAPSHOT STANDBY');
BEGIN  DBMS_SCHEDULER.SET_ATTRIBUTE('&scheduler_job_owner.&scheduler_job_name','database_role','SNAPSHOT STANDBY'); END;
*
ERROR at line 1:
ORA-16612: string value too long for attribute "database_role"
ORA-06512: at "SYS.DBMS_ISCHED", line 4478
ORA-06512: at "SYS.DBMS_SCHEDULER", line 2862
ORA-06512: at line 1

 + As a result, the scheduler job no longer runs on this standby database.
Cause

Bug 16217211 was created particularly for this problem.

The job was not shown in DBA_SCHEDULER_JOBS view because of the fact that it was filtered out due to the fact that DATABASE_ROLE of the scheduler job was set to PRIMARY while DATABASE_ROLE of the instance was set to SNAPSHOT STANDBY. Hence, the scheduler job will be executed on this instance if and only if the instance turns to be PRIMARY.
For a record to appear in DBA_SCHEDULER_JOBS view, the DATABASE_ROLE of both the scheduler job and the instance must match.

 
 
Solution

1) Please apply Patch 13399711 on the culprit snapshot standby database. This patch is available on top of 11.2.0.3 for Linux x86-64. This patch fixes the ORA-16612 error that is reported when trying to set the DATABASE_ROLE of the scheduler job to SNAPSHOT STANDBY to match that of the instance.

2) Then, kindly execute the following:

# sqlplus / as sysdba
set line 150

select DBID, NAME, DATABASE_ROLE from V$DATABASE;
select * from DBA_SCHEDULER_JOBS where OWNER='&scheduler_job_owner' and JOB_NAME='&scheduler_job_name';

set serveroutput on

Declare
v_database_database_role VARCHAR2(50) := '';
v_job_database_role VARCHAR2(50) := '';
Begin
-- checking database_role of the job
DBMS_SCHEDULER.GET_ATTRIBUTE ('&scheduler_job_owner.&scheduler_job_name', 'DATABASE_ROLE', v_job_database_role);
DBMS_OUTPUT.PUT_LINE ('Database Role of the concerned job is: ' || NVL(v_job_database_role, 'unknown'));

-- checking the database_role of the database
select DATABASE_ROLE into v_database_database_role from V$DATABASE;
DBMS_OUTPUT.PUT_LINE ('Database Role of the database is: ' || NVL(v_database_database_role, 'unknown'));

-- setting the database_role of the job to that of the database (if not matching)
If ((NVL(upper(v_job_database_role),'') <> NVL(upper(v_database_database_role),'')) and v_database_database_role is not null) Then
DBMS_SCHEDULER.SET_ATTRIBUTE ('&scheduler_job_owner.&scheduler_job_name', 'DATABASE_ROLE', '''' || v_database_database_role || '''');
End IF;

-- checking new database_role of the job
DBMS_OUTPUT.PUT_LINE ('Current Database Role of the concerned job is: ' || NVL(v_database_database_role, 'unknown'));

End;
/
select * from DBA_SCHEDULER_JOBS where OWNER='&scheduler_job_owner' and JOB_NAME='&scheduler_job_name';