Create a Job

You can create a SQL job to perform administrative operations on a single Managed Database or a Database Group.

The information provided when creating a job is to enable the following components to connect and interact:

  • The single Managed Database or the Database Group on which the job will be executed.
  • For External Databases, the Oracle Cloud Infrastructure Management Agent that is installed on a host and has a connection to the External Database is used to execute the job.
    Note

    The Management Agent is not required for Oracle Cloud Databases and Autonomous Databases.
  • The Oracle Cloud Infrastructure Object Storage bucket in which job results for a Query type job are stored.

To create a job:

  1. Go to the Managed database details page of the Managed Database or the Database group details page of the Database Group for which you want to create the job.
  2. On the left pane under Resources, click Jobs.
  3. In the Jobs section, click the Definitions tab and then click Create job.
  4. In the Create job panel:
    1. Provide the following information for the SQL job:
      1. Job name: Enter a unique name for the job.
      2. Job description: Optionally, enter a description for the job.
      3. SQL type: Select the SQL type. The available options are:
        • Query
        • DML
        • DDL
        • PL*SQL
      4. Choose compartment: Select the compartment in which you want to create the job. The compartment in which the Managed Database or Database Group resides is selected by default, however, you can opt to create the job in a different compartment.
      5. Timeout: Depending on the time the SQL job is likely to take, select one of the options in the drop-down list, and based on the selected option the job is prioritized.
    2. Provide schedule details for the job by selecting one of the following options:
      • One time (immediately): Select to execute the job once (immediately).
      • One time (later): Select to execute the job once (later) and specify the time when it should be executed.
      • Repeating: Select to execute a job multiple times at the specified frequency. If you want to specify an end date for the scheduled job, then select the Define end date check box.
    3. Select one of the available options in the Credential type drop-down list to specify database credentials to connect to the Managed Database. For information on credential types, see Use Credentials to Perform Diagnostics & Management Tasks.
      Note

      • If creating a job for a Database Group, you cannot use named credentials and you will have to select the New credential option in the Credential type drop-down list and specify the user name, user password secret, and role. Also, when creating a job for a Database Group, the database credentials must apply to all the databases in the Database Group.
      • When creating a scheduled job (to be executed once or multiple times), if the Service policy is required... message is displayed, click Add policy to view the service policy that grants Database Management (dpd) the permission to read the secret, and automatically add it. The Oracle Cloud Infrastructure IAM service creates the policy and it's added to a collection of policies called DBMgmt_Service_Policy in the IAM service.
    4. For a Query type job, select the Object Storage bucket in which the job results will be stored. The Oracle object storage for job results section with the Bucket name drop-down list is only displayed if you've selected the Query type job. The Object Storage bucket can be in the same compartment as the job or in another compartment, and you must have the required Object Storage service permissions to select the bucket.

      When creating a scheduled job (to be executed once or multiple times), if the Service policy is required... message is displayed, click Add policy to view the service policy that grants Database Management (dpd) the permission to write the results of the scheduled job to an Object Storage bucket, and automatically add it. The IAM service creates the policy and it's added to a collection of policies called DBMgmt_Service_Policy in the IAM service.

      For information on:

    5. Click Load SQL to load the SQL command or enter it in the SQL command field. If you're entering a SQL command, then do not add a trailing ; or /.
    6. For Query, DML, and PL*SQL type jobs, optionally add bind variables in the Bind variables section. If bind variables are used as placeholders that must be replaced with valid values for the statement to execute successfully, then click Add bind variable and specify the following information:
      1. Position: Select the bind variable position.
      2. Type: Select the type to specify if the bind variable is an in-bind or out-bind variable.
      3. Data type: Select the relevant data type.
      4. Array type: Enter the array type for the Array of numbers and Array of strings data types. Note that this field is not displayed if you select Number, String, or CLOB in the Data type field.
      5. Value: Enter the bind variable value.

      To add more bind variable values, click Add bind variable and provide the required information. For information on bind variables, see Bind Variables and Cursors in Oracle Database SQL Tuning Guide.

    7. Optionally, click Show advanced options to add free-form or defined tags to the job. If you have the permissions required to create a job, then you also have permissions to add free-form tags. To add a defined tag, you must have permissions to use the tag namespace.

      For information on:

    8. Click Create job.
The new job is listed on the Definitions tab in the Jobs section of the Managed Database or Database Group. The status of the job is Active if it's being executed and Inactive if it's already executed.

You can click the Actions icon (Actions) for the job and perform the following tasks:

  • View details: Click to go to the Job details page and view the details of the job, job runs and executions, the associated Managed Databases on which the job is executed, and perform tag-related tasks. For more information, see Monitor Job Runs and Executions.
  • Clone job: Click to clone the job.
  • Edit job: Click to edit the details of scheduled repeating jobs in Active state.
  • Move job: Click to move the job from the current compartment to another compartment.
  • Delete job: Click to delete the job.

On the Runs tab in the Jobs section, you can view the associated job runs and executions. The job runs for the time period selected in the Time period field are displayed along with an overview of the status of the job executions: Succeeded, Failed, and In progress. You can click the execution status links to filter the executions by status.

You can build metric queries and create alarms on the job execution status in the Oracle Cloud Infrastructure Monitoring service using the dbmgmtJobExecutionsCount metric. For more information, see Database Management Metrics for Oracle Databases.

Here's an example of a query that you can use to create a "failed job" alarm:

dbmgmtJobExecutionsCount[1m]{managedDbId = "<OCID of the Oracle Database>", status = "Failed"}.mean()

In the query, the managedDbId dimension is used to notify you of any failed jobs for the Managed Database whose OCID is mentioned in the query. Similarly, you can use the following dimensions:

  • managedDbGroupId: To be notified of failed jobs for all the Managed Databases in a Database Group.
  • jobId: To be notified if a particular job fails.

For more information on how to create an alarm, see Creating a Basic Alarm.