Introducing the Tidal Automation Adapter for Oracle Database

To run and monitor an Oracle database job from Tidal Automation (TA), you need to create a job definition in TA for it. The database job may already exist as a defined job within the Oracle database or it can be SQL statements defined while creating the TA job definition. The job can either run inline or it can run in the background as a DBMS job. If the job runs in TA, TA maintains a large degree of control but if the job runs as a DBMS job than control over the job is more limited from within Oracle. Job output is available if the job runs in TA but no output can be retrieved from DBMS jobs.

The Oracle Database Adapter is part of the regular TA installation but it can only be used if the appropriate license file is applied to TA. Before beginning the configuration procedures, verify that your environment meets the prerequisites.

Prerequisites

Refer to the TA Compatibility Matrix for a complete list of hardware and software requirements.

The Oracle Database Adapter provides advanced scheduling capabilities through the standard Oracle database scheduler, which is a collection of functions and procedures in the DBMS_SCHEDULER package. Jobs are executed by a job coordinator background process that is automatically started and stopped as needed. By default, this job coordinator is not up and running, but the database does monitor whether there are any jobs to be executed. If jobs are detected, the database starts the job coordinator.

The initialization parameter JOB_QUEUE_PROCESSES only applies to DBMS_JOB. When DBMS_SCHEDULER is used, the coordinator will automatically determine how many job slaves to start based on CPU load and the number of outstanding jobs. In special scenarios a DBA can still limit the maximum number of slaves to be started by the coordinator by setting the MAX_JOB_SLAVE_PROCESSES with the DBMS_SCHEDULER.SET_SCHEDULER_ATTRIBUTE procedure.

To work successfully with the Oracle Database Adapter, the Oracle environment should have

  • DBMS_SCHEDULER package.

  • System Privileges for Connection User:

    A connection user should have privileges to execute the queries.

    select version from v$instance
    select OS_USERNAME, USERNAME, OWNER, OBJ_NAME, ACTION, ACTION_NAME, TIMESTAMP, SESSIONID, ENTRYID,
    NEW_OWNER, NEW_NAME from DBA_AUDIT_TRAIL where ? <= TIMESTAMP and TIMESTAMP < ? order by TIMESTAMP desc
    select current_timestamp from dual
    select sessiontimezone from dual

  • System Privilege for Runtime User and Connection User

    Privilege Name – Name of the privilege or permission set for the user.

    Query Syntax – Query to execute to provide the privilege.

Note: Consult with your Database Administrator before running the Grant queries.

Privilege Name

 Description Query Syntax

Create any job

Enables a user to create any type of job, schedule or program.

GRANT CREATE ANY JOB TO username;

Execute any program

Enables jobs to use any program.

GRANT EXECUTE ANY PROGRAM TO username;

Execute any class

Enables jobs to run under any job class.

GRANT EXECUTE ANY CLASS TO username;

Manage SCHEDULER

Enables a user to create, modify and drop job classes, windows and window groups.

GRANT MANAGE SCHEDULER TO username;

To determine if the user already has a privilege, run this validation syntax:

SELECT GRANTEE, PRIVILEGE FROM DBA_SYS_PRIVS where (GRANTEE = 'username' and PRIVILEGE = 'PRIVILEGE NAME');

Note: The GRANTEE is the respective of the user and the PRIVILEGE is the name of the privilege.

  • Object Privileges:

    Note: Consult with your Database administrator before running the Grant queries.

    Privilege Name

    Description

    		 Query Syntax

    Execute

    This can only be granted for programs and job classes, enabling jobs to use the granted objects.

    GRANT EXECUTE ON Program name (or) procedure name (or) function name TO username;

    Alter

    Enables the user to alter or drop the object it is granted on. The alter operations vary depending on the object.

    GRANT ALTER ON table name (or) Program name TO username;

    ALL

    This can be granted on jobs, programs, schedules and job classes. It enables the user to perform all possible operations on the object it is granted on.

    GRANT ALL ON table name (or) Program name (or) procedure name (or) function name TO username;

To determine if the user already has a privilege, run this validation syntax:

  • Select GRANTEE, TABLE_NAME, PRIVILEGE FROM SYS.DBA_TAB_PRIVS WHERE (GRANTEE='username' AND PRIVILEGE = 'PRIVILEGE NAME');

The GRANTEE refers to the user and PRIVILEGE references the specific privilege.

  • Read access to the database scheduler views.

    Note: Consult with your Database Administrator before running the Grant queries.

    DBA_SCHEDULER_SCHEDULES

    View Description Query Syntax

    These views show all schedules.

    GRANT Privilege Type ON DBA_SCHEDULER_SCHEDULES to username;

    DBA_SCHEDULER_PROGRAMS

    These views show all programs.

    GRANT Privilege Type ON DBA_SCHEDULER_PROGRAMS to username;

    DBA_SCHEDULER_PROGRAM_ARGS

    These views show all arguments registered with all programs as well as the default values if they exist.

     

    GRANT Privilege Type ON DBA_SCHEDULER_PROGRAM_ARGS to username;

    DBA_SCHEDULER_JOBS

    These views show all jobs, enabled as well as disabled.

     

    GRANT Privilege Type ON DBA_SCHEDULER_JOBS to username;

    DBA_SCHEDULER_GLOBAL_ATTRIBUTE

    These views show the current values of TA attributes.

    GRANT Privilege Type ON DBA_SCHEDULER_GLOBAL_ATTRIBUTE to username;

    DBA_SCHEDULER_JOB_ARGS

    These views show all arguments for all jobs, assigned and unassigned.

    GRANT Privilege Type ON DBA_SCHEDULER_JOB_ARGS to username;

    DBA_SCHEDULER_JOB_CLASSES

    These views show all job classes.

    GRANT Privilege Type ON DBA_SCHEDULER_JOB_CLASSES to username;

    DBA_SCHEDULER_WINDOWS

    These views show all windows.

    GRANT Privilege Type ON DBA_SCHEDULER_WINDOWS to username;

    DBA_SCHEDULER_JOB_RUN_DETAILS

    These views show all completed (failed or successful) job runs.

    GRANT Privilege Type ON DBA_SCHEDULER_JOB_RUN_DETAILS to username;

    DBA_SCHEDULER_WINDOW_GROUPS

    These views show all window groups.

    GRANT Privilege Type ON DBA_SCHEDULER_WINDOW_GROUPS to username;

    DBA_SCHEDULER_WINGROUP_MEMBERS

    These views show the members of all window groups, one row for each group member.

    GRANT Privilege Type ON DBA_SCHEDULER_WINGROUP_MEMBERS to username;

     

    DBA_SCHEDULER_RUNNING_JOBS

    These views show state information on all jobs that are currently being run.

    GRANT Privilege Type ON DBA_SCHEDULER_RUNNING_JOBS to username;

To determine if the user already has a privilege, run this validation syntax:

select GRANTEE, TABLE_NAME, PRIVILEGE FROM SYS.DBA_TAB_PRIVS WHERE (GRANTEE='username' AND PRIVILEGE ='SELECT' );

Assigning a role to DBA Scheduler views

SELECT_CATALOG_ROLE role grants read-only access to Data Dictionary (DBA_%) views. To verify if the user has the SELECT_CATALOG_ROLE role run this query.
SELECT GRANTEE, GRANTED_ROLE FROM DBA_ROLE_PRIVS where (GRANTEE = 'username' and GRANTED_ROLE = 'SELECT_CATALOG_ROLE');

Terms to Know

  • Background Job – A job that runs in the background runs within Oracle rather than inline in TA. TA cannot retrieve job output from a job that runs in Oracle.

  • Database Job – A type of job that can be run from the Oracle Database Adapter. A database job in this case is a SQL statement or SQL block that has been predefined and saved in the database.

  • Database Schema – A group of related database objects, like tables and indexes, owned by a user account. When defining an event in an Oracle database, a change to a specified schema must be specified as the event.

  • Delimiter – A character inserted into unformatted job output as a means to separate output values that would otherwise run together.

  • JDBC (Java DataBase Connectivity) – Definition of a connection between the database and the Client Manager using the Java programming language.

  • PL/SQL (Procedural Language/Structured Query Language) – The proprietary version of SQL developed by the Oracle corporation to interact with relational databases. One of the job types that can be defined in the Oracle Database Adapter is PL/SQL consisting of a SQL block.

  • Session – A number used to identify the database session within Oracle that the job ran in.

  • SQL (Structured Query Language) – The most common computer language used with RDBMS (relational database management systems) such as Oracle and Microsoft SQL Server to retrieve and manipulate data.

  • SQL block – A series of programming instructions or statements in the SQL database language. A block of SQL statements is treated as a single unit similar to a paragraph of sentences.