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