Hi everyone!
Hope you are doing good!
I got a call from a client during their DR test.
We do know that usually DR test means when the entire workload is moved to DR site.
Some clients never execute DR test. 🙂
For this specific client, their DR is composed by:
- They break volume replication from PROD to DR for volumes used by application servers;
- They start application server using the replicated volumes;
- They convert Standby Database to Snapshot Standby;
- Key people from client executes all the workload they understand which is critical for functional validation.
OK, with that said, they noticed that after database conversion to Snapshot Standby, the Scheduler Jobs were not visible on database.
I did a simple test in an isolated database.
First, I created a job on PRIMARY (Prod) database:
SQL>
BEGIN
DBMS_SCHEDULER.CREATE_JOB (
job_name => 'TEST_MSG',
job_type => 'PLSQL_BLOCK',
job_action => 'BEGIN DBMS_OUTPUT.PUT_LINE(''Test message''); END;',
number_of_arguments => 0,
start_date => SYSTIMESTAMP,
repeat_interval => 'FREQ=MINUTELY;INTERVAL=3',
end_date => NULL,
enabled => TRUE,
auto_drop => FALSE,
comments => '');
DBMS_SCHEDULER.SET_ATTRIBUTE(
name => 'TEST_MSG',
attribute =>'store_output', value => TRUE);
DBMS_SCHEDULER.SET_ATTRIBUTE(
name => 'TEST_MSG',
attribute =>'logging_level', value => DBMS_SCHEDULER.LOGGING_FULL);
END;
/
PL/SQL procedure successfully completed.
Verifying the job execution details:
col job_name for a30
col output for a100
col run_duration for a20
col actual_start_date for a40
SQL> select job_name,status,actual_start_date,run_duration,output
from DBA_SCHEDULER_JOB_RUN_DETAILS
where job_name='TEST_MSG'
order by actual_start_date;
Output:
Great! A simple job is working as expected.
Let’s run some checks on recently converted Snapshot Standby database.
So, after we converted the Physical Standby to Snapshot Standby database, we first verified the database role:
SQL> select database_role from v$database;
DATABASE_ROLE
----------------
SNAPSHOT STANDBY
Now, we verified if there are jobs on database:
SQL> select owner,job_name
from dba_scheduler_jobs
WHERE OWNER NOT IN ('SYS','ORACLE_OCM');
no rows selected
Weird, isn’t?
If we try to create the job on Snapshot Standby database, we’ll got an error:
ORA-27477: “MARCUS.””TEST_MSG” already exists.
Let’s check another view: DBA_SCHEDULER_JOB_RUN_DETAILS:
SQL> select distinct owner,job_name
from dba_scheduler_job_run_details
WHERE OWNER NOT IN ('SYS','ORACLE_OCM');
OWNER JOB_NAME
------------------------------ ------------------------------
MARCUS TEST_MSG
Well, job is present on DBA_SCHEDULER_JOB_RUN_DETAILS, this can be explained because as this was a Standby Database, all changes that happened at Primary were replicated to Standby, right? Yes!
So, what explains that job is not visible on DBA_SCHEDULER_JOBS view?
The answer for this is:
When we create a scheduler job, an attribute called DATABASE_ROLE is set to job during job creation. By default, the value for this attribute is PRIMARY.
We can read about this on official documentation here: https://docs.oracle.com/en/database/oracle/oracle-database/19/arpls/DBMS_SCHEDULER.html#GUID-159AF1D6-5345-4DC6-B825-84F71A9D90B9
Below we have the screenshot from the official documentation:
As we can see, the default is primary.
OK, let’s check the DATABASE_ROLE attribute for this job on PRIMARY:
DECLARE
V_DATABASE_DATABASE_ROLE VARCHAR2(50) := '';
V_JOB_DATABASE_ROLE VARCHAR2(50) := '';
BEGIN
DBMS_SCHEDULER.GET_ATTRIBUTE ('MARCUS.TEST_MSG', 'DATABASE_ROLE', V_JOB_DATABASE_ROLE);
DBMS_OUTPUT.PUT_LINE ('Database Role of the concerned job is: ' || NVL(V_JOB_DATABASE_ROLE, 'unknown'));
END;
/
Database Role of the concerned job is: PRIMARY
Yes, confirmed that this is the reason why job is not showing on Snapshot Standby database!
Let’s check some internal tables on DB:
SQL> select o.obj#, o.name, j.database_role
from obj$ o, scheduler$_job j
where o.obj# = j.obj#
and o.name='TEST_MSG';
OBJ# NAME DATABASE_ROLE
---------- --------------- ----------------
305574 TEST_MSG PRIMARY
With the above query we can confirm that object does exist on DB, this is the reason if we try to create the job on Snapshot Standby, it will fail.
We can fix this easily running the procedure DBMS_SCHEDULER.SET_ATTRIBUTE.
What we did for this client and I believe that is a good solution (and this is the reason I am sharing here through this blog post) is creating an AFTER STARTUP ON DATABASE checking the database role, and, if database role is Snapshot Standby, run the procedure DBMS_SCHEDULER.SET_ATTRIBUTE to “fix” the job.
The code for trigger is here:
SQL>
CREATE OR REPLACE TRIGGER after_startup_trigger
AFTER STARTUP ON DATABASE
DECLARE
db_role VARCHAR2(100);
v_owner VARCHAR2(100);
v_job_name VARCHAR2(100);
CURSOR c_jobs IS
SELECT DISTINCT owner,job_name
FROM dba_scheduler_job_run_details
WHERE owner NOT IN ( 'SYS', 'ORACLE_OCM' );
BEGIN
SELECT database_role INTO db_role
FROM v$database;
IF db_role = 'SNAPSHOT STANDBY' THEN
OPEN c_jobs;
LOOP
FETCH c_jobs INTO v_owner,v_job_name;
EXIT WHEN c_jobs%notfound;
dbms_scheduler.set_attribute(v_owner||'.'|| v_job_name, 'DATABASE_ROLE', 'SNAPSHOT STANDBY');
END LOOP;
CLOSE c_jobs;
END IF;
END;
/
PL/SQL procedure successfully completed.
So what we did:
- Created the trigger on PRIMARY;
- Converted DB back to Standby Database;
- When Apply Lag was zero, we converted DB to Snapshot Standby.
Verified the jobs on DBA_SCHEDULER_JOBS view:
SQL> select distinct owner,job_name
from dba_scheduler_jobs
WHERE OWNER NOT IN ('SYS','ORACLE_OCM');
OWNER JOB_NAME
------------------------------ ------------------------------
MARCUS TEST_MSG
OK, let’s check the DATABASE_ROLE attribute for this job on Snapshot Standby:
DECLARE
V_DATABASE_DATABASE_ROLE VARCHAR2(50) := '';
V_JOB_DATABASE_ROLE VARCHAR2(50) := '';
BEGIN
DBMS_SCHEDULER.GET_ATTRIBUTE ('MARCUS.TEST_MSG', 'DATABASE_ROLE', V_JOB_DATABASE_ROLE);
DBMS_OUTPUT.PUT_LINE ('Database Role of the concerned job is: ' || NVL(V_JOB_DATABASE_ROLE, 'unknown'));
END;
/
Database Role of the concerned job is: SNAPSHOT STANDBY
Great!!
If you are not interested in create a trigger, but you want to fix this manually on Snapshot Standby database, on PRIMARY, run the below query:
SQL> SELECT 'EXECUTE DBMS_SCHEDULER.SET_ATTRIBUTE('''||OWNER||'.'||JOB_NAME||''',''DATABASE_ROLE'',''SNAPSHOT STANDBY'');'
FROM DBA_SCHEDULER_JOBS
WHERE OWNER NOT IN ('SYS','ORACLE_OCM');
EXECUTE DBMS_SCHEDULER.SET_ATTRIBUTE('MARCUS.TEST_MSG','DATABASE_ROLE','SNAPSHOT STANDBY');
Just copy the output from PRIMARY and execute it on SNAPSHOT STANDBY database. That’s it!
Keep in mind that trigger will automate this for you and your DB will be already prepared for this scenario!
Trigger is created and replicated to DR, trigger will execute every time after STARTUP the database. On PRIMARY database, nothing will happen (as per IF condition), in case a SWITCHOVER or FAILOVER operation, new database role will be PRIMARY, so, the job will continue running. And, in case of a conversion to SNAPSHOT STANDBY, trigger will be fired after STARTUP and the proper database role will be set for the jobs. With this, the DR test will not be impacted!
Hope this helps!
Peace,
Vinicius
Related posts
Disclaimer
My postings reflect my own views and do not necessarily represent the views of my employer, Accenture.