在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,可参考:,这时由于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