Automate Starting and Stopping Workspace

You may ask is there a way to automate the stop and start of an OCI Data Integration workspace or any OCI resource that is billed per use. One way to do this is to do this via the Oracle Autonomous databases. The OCI PLSQL SDK is available in all autonomous databases which makes it fantastically simple to perform operations like this. But scheduling? The Oracle database has your back on that too. The DBMS_SCHEDULER package can create schedules and easily run PLSQL programs.

The Autonomous Database uses the scheduler itself for doing housekeeping jobs — if it relies on the scheduler, why can’t you?

There are PLSQL procedures for stopping/starting the Data Integration workspace, let’s see how we do it.

Defining the Schedules

The two procedure calls below create the schedule for starting — we will start at 2300 hours for example, and stop at 0800 hours in the morning.

BEGIN
 DBMS_SCHEDULER.create_schedule (
  schedule_name => 'daily_start_sched',
  start_date => SYSTIMESTAMP,
  repeat_interval => 'freq=daily; byhour=23; byminute=0; bysecond=0;',
  end_date => NULL,
  comments => 'Run every day at 2300 hours everyday');


 DBMS_SCHEDULER.create_schedule (
  schedule_name => 'daily_stop_sched',
  start_date => SYSTIMESTAMP,
  repeat_interval => 'freq=daily; byhour=8; byminute=0; bysecond=0;',
  end_date => NULL,
  comments => 'Run every day at 0800 hours everyday');
END;
/

Defining the Programs

We will create 2 programs, one for starting and one for stopping below;

BEGIN
  DBMS_SCHEDULER.create_program(
  program_name => 'stop_di_program',
  program_type => 'PLSQL_BLOCK',
  program_action => 'declare stopw dbms_cloud_oci_di_data_integration_stop_workspace_response_t;
  WSID VARCHAR2(255) := ''WORKSPACE_OCID'';
  REGION VARCHAR2(255) := ''us-ashburn-1'';
  CRED VARCHAR2(255) := ''YOUR_CREDENTIAL'';
begin
    stopw := DBMS_CLOUD_OCI_DI_DATA_INTEGRATION.STOP_WORKSPACE(workspace_id => WSID, quiesce_timeout => 1800000,
          is_force_operation => 1, REGION => REGION, CREDENTIAL_NAME => CRED);
end;',
  enabled => TRUE,
  comments => 'Program to stop workspace.');
  DBMS_SCHEDULER.enable (name=>'stop_di_program');
END;
/

BEGIN
  DBMS_SCHEDULER.create_program(
  program_name => 'start_di_program',
  program_type => 'PLSQL_BLOCK',
  program_action => 'declare startw dbms_cloud_oci_di_data_integration_start_workspace_response_t;
  WSID VARCHAR2(255) := ''YOUR_WORKSPACE_OCID'';
  REGION VARCHAR2(255) := ''us-ashburn-1'';
  CRED VARCHAR2(255) := ''YOUR_CREDENTIAL'';
begin
    startw := DBMS_CLOUD_OCI_DI_DATA_INTEGRATION.START_WORKSPACE(workspace_id => WSID, REGION => REGION, CREDENTIAL_NAME => CRED);
end;',
  enabled => TRUE,
  comments => 'Program to start workspace.');
  DBMS_SCHEDULER.enable (name=>'start_di_program');
END;
/

Defining the Scheduled Jobs

Below you can see how we create the scheduled jobs tying together the schedules and the programs.

BEGIN
DBMS_SCHEDULER.create_job (
  job_name => 'stop_workspace',
  program_name => 'stop_di_program',
  schedule_name => 'daily_stop_sched',
  enabled => TRUE,
  comments => 'Stop DI workspace scheduler job');

DBMS_SCHEDULER.create_job (
  job_name => 'start_workspace',
  program_name => 'start_di_program',
  schedule_name => 'daily_start_sched',
  enabled => TRUE,
  comments => 'Start DI workspace scheduler job');
END;
/

That’s it, these APIs will be kicked off using the schedules defined by the Autonomous Database.

If you want to cleanup the programs, schedules and so on use this script;

-- Cleanup all example
BEGIN
  DBMS_SCHEDULER.drop_job (job_name=>'start_workspace');
  DBMS_SCHEDULER.drop_job (job_name=>'stop_workspace');

  DBMS_SCHEDULER.drop_program(program_name => 'start_di_program');
  DBMS_SCHEDULER.drop_program(program_name => 'stop_di_program');

  DBMS_SCHEDULER.drop_schedule (schedule_name => 'daily_start_sched');
  DBMS_SCHEDULER.drop_schedule (schedule_name => 'daily_stop_sched');
END;
/

Summary

Hope you found this useful, PLSQL and the Autonomous Database is a great way of leveraging tools already at your disposal for this automation. Check out the documentation for OCI Data Integration below. Send me comments, questions and ideas, would love to hear them.

Related resources

Data Integration

Executing Data Integration Tasks using Oracle PLSQL