Database (Oracle)

The Oracle job steps support various Oracle database operations.

 

 

This job step allows you to connect to an Oracle database and establish a handle. That handle can then be passed to many of the other database job steps. The advantage of this approach is that you can perform database-to-database operations concurrently.

 

 

Platforms Supported

Windows.

 

Software Pre-Requisites

Design and Windows: OLE/DB drivers are used as specified. For Oracle: Oracle .Net Provider version 2.102.2.20 (for 32-bit) and 2.102.3 (for 64-bit).

 

Job Step Properties

 

DataSource – This property references the “source” data source for the export.

 

Credentials – The object path of a User Account object. Clicking on the “Helper” will cause a tree display of all ActiveBatch containers. You may then select a User Account object. The User Account credentials must have proper access to the data source. Typically, the credentials will be a valid database username and password for this data source (unless Windows authentication is used in which the username/password will be a valid Windows account).

 

Return Job Step

 

ConnectionHandle – This property represents the connection handle to the database. Use the Disconnect job step to disconnect from the database.

 

 

This job step is used with Connect to disconnect from an established connection. See previous Connect job step for example usage.

 

Platforms Supported

Windows.

 

Software Pre-Requisites

Design and Windows: OLE/DB drivers are used as specified. For Oracle: Oracle .Net Provider version 2.102.2.20 (for 32-bit) and 2.102.3 (for 64-bit).

 

Job Step Properties

 

Connection Handle – This property represents the established connection handle to the database you wish to disconnect.

 

 

This job step allows you to export (backup) an Oracle database, schema, tablespace, tables or metadata.

 

 

Platforms Supported

Windows, UNIX-based.

 

Software Pre-Requisites

Design and Windows: OLE/DB drivers are used as specified. For Oracle: Oracle .Net Provider version 2.102.2.20 (for 32-bit) and 2.102.3 (for 64-bit).

 

UNIX: Oracle JDBC drivers are used. TNSNAMES.ORA file must be available under the ORACLE_HOME environment variable.

 

Job Steps Properties

 

Connection Information – This set of properties indicates how the job step is to connect to the database.

  • Datasource

    • DataSource – This property references the “source” data source for the export.

      • This property also supports ActiveBatch variables (as in the example above which is referencing the Job Scheduler’s own database).

    • Credentials – The object path of a User Account object. Clicking on the “Helper” will cause a tree display of all ActiveBatch containers. You may then select a User Account object. The User Account credentials must have proper access to the data source. Typically, the credentials will be a valid database username and password for this data source (unless Windows authentication is used in which the username/password will be a valid Windows account).

  • Handle – Windows: This property represents a ConnectionHandle from an earlier successful Connect job step.

 

Mode – This dropdown indicates the type of export to be performed. The following types are available:

 

  • Full: Operates on the full set of databases excluding SYS, XDB,ORDSYS, MDSYS, CTXSYS, ORDPLUGINS, and LBACSYS schemas.

     

  • Schema: Operates on a specific schema. Defaults to the schema of the specified user. All objects in the selected schemas are processed. You cannot specify SYS, XDB, ORDSYS, MDSYS, CTXSYS, ORDPLUGINS, or LBACSYS schemas for this mode.

     

  • Table: Operates on a set of selected tables. Defaults to all of the tables in the current user's schema. Only tables and their dependent objects are processed.

     

  • Tablespace: Operates on a set of selected tablespaces. No defaulting is performed. Tables that have storage in the specified tablespaces. are processed in the same manner as in Table mode.

     

  • Metadata: Operates on metadata for tables (and their dependent objects) within a set of selected tablespaces to perform a transportable tablespace export/import.

     

  • Number of Threads: The degree of parallelism to perform the operation. This parameter also controls the number of files that are created in the specified backup directory. Each file is appended with a number, based on this parameter. For example, a value of '4' will create 4 separate files, named '<Backup File Name>1.dmp'-through-'<Backup File Name>4.dmp.' Import operations (with no database link) must specify the same file name and number of threads.

 

Backup Directory – A valid directory on the Oracle Database server in which to store or from which to load the exported files. Required for exporting; required for importing only if not using a database link. (e.g., 'C:\oracle\backup').

 

Backup File Name – The root name of the exported file(s). A number is appended to the end of the file(s), depending on 'Number Of Threads.' For two (2) threads, two files are produced, one with '1' appended to it and one with '2' appended. The same name must be specified when importing as when exporting. Required for exporting; required for importing only if not using a database link.(e.g., 'MyDBBackup').

 

Metadata Filters – Filters to limit the objects included in operation.

 

Overwrite Files – This Boolean property indicates whether any remnants from preceding backups can be overwritten.

 

 

This job step allows you to retrieve the status of a previously executed Oracle Job.

 

 

Platforms Supported

Windows, UNIX-based.

 

Software Pre-Requisites

Design and Windows: OLE/DB drivers are used as specified. For Oracle: Oracle .Net Provider version 2.102.2.20 (for 32-bit) and 2.102.3 (for 64-bit).

 

UNIX: Oracle JDBC drivers are used. TNSNAMES.ORA file must be available under the ORACLE_HOME environment variable.

 

Job Steps Properties

 

Connection Information – This set of properties indicates how the job step is to connect to the database.

 

  • Datasource

    • DataSource – This property references the “source” data source that the Oracle Job was started on.

      • This property also supports ActiveBatch variables (as in the example above which is referencing the Job Scheduler’s own database).

    • Credentials – The object path of a User Account object. Clicking on the “Helper” will cause a tree display of all ActiveBatch containers. You may then select a User Account object. The User Account credentials must have proper access to the data source. Typically, the credentials will be a valid database username and password for this data source (unless Windows authentication is used in which the username/password will be a valid Windows account).

  • Handle – Windows: This property represents a ConnectionHandle from an earlier successful Connect job step.

     

JobName – This property represents the name of the Oracle job that is executing. You can click on the dropdown helper to obtain a list of enumerable Oracle jobs. If your job is not present, you can type in the name of the job.

 

JobInstance – This property indicates whether you want to synchronize on a specific running Oracle Job instance or whether any instance will do. This property is useful when multiple instances of an Oracle Job are running. “AnyInstance” represents any instance. “SpecificInstance” represents a specific instance using an InstanceId. The InstanceId is one of the properties returned when the Oracle Job was successfully launched.

 

Return Step Value

 

An execution variable object named “OracleJobReturnValue” is returned. This structure contains the data source, job name, job status and instanceid of the Oracle Job. Job Status represents the status of the Oracle Job itself.

 

 

This job step allows you to import (restore) an Oracle database, schema, tablespace, tables or metadata.

 

 

Platforms Supported

Windows, UNIX-based.

 

Software Pre-Requisites

Design and Windows: OLE/DB drivers are used as specified. For Oracle: Oracle .Net Provider version 2.102.2.20 (for 32-bit) and 2.102.3 (for 64-bit).

 

UNIX: Oracle JDBC drivers are used. TNSNAMES.ORA file must be available under the ORACLE_HOME environment variable.

 

Job Steps Properties

 

Connection Information - This set of properties indicates how the job step is to connect to the database.

 

  • Datasource

    • DataSource – This property references the “source” data source for the import.

      • This property also supports ActiveBatch variables (as in the example above which is referencing the Job Scheduler’s own database).

    • Credentials – The object path of a User Account object. Clicking on the “Helper” will cause a tree display of all ActiveBatch containers. You may then select a User Account object. The User Account credentials must have proper access to the data source. Typically, the credentials will be a valid database username and password for this data source (unless Windows authentication is used in which the username/password will be a valid Windows account).

  • Handle – Windows: This property represents a ConnectionHandle from an earlier successful Connect job step.

 

Mode – This dropdown indicates the type of export to be performed. The following types are available:

 

  • Full: Operates on the full set of databases excluding SYS, XDB,ORDSYS, MDSYS, CTXSYS, ORDPLUGINS, and LBACSYS schemas.

     

  • Schema: Operates on a specific schema. Defaults to the schema of the specified user. All objects in the selected schemas are processed. You cannot specify SYS, XDB, ORDSYS, MDSYS, CTXSYS, ORDPLUGINS, or LBACSYS schemas for this mode.

     

  • Table: Operates on a set of selected tables. Defaults to all of the tables in the current user's schema. Only tables and their dependent objects are processed.

     

  • Tablespace: Operates on a set of selected tablespaces. No defaulting is performed. Tables that have storage in the specified tablespaces. are processed in the same manner as in Table mode.

     

  • Metadata: Operates on metadata for tables (and their dependent objects) within a set of selected tablespaces to perform a transportable tablespace export/import.

     

  • Number of Threads: The degree of parallelism to perform the operation. This parameter also controls the number of files that are created in the specified backup directory. Each file is appended with a number, based on this parameter. For example, a value of '4' will create 4 separate files, named '<Backup File Name>1.dmp'-through-'<Backup File Name>4.dmp.' Import operations (with no database link) must specify the same file name and number of threads.

     

Backup Directory – A valid directory on the Oracle Database server in which to store or from which to load the exported files. Required for exporting; required for importing only if not using a database link. (e.g., 'C:\oracle\backup')

 

Backup File Name – The root name of the exported file(s). A number is appended to the end of the file(s), depending on 'Number Of Threads.'\nFor two (2) threads, two files are produced, one with '1' appended to it and one with '2' appended. The same name must be specified when importing as when exporting. Required for exporting; required for importing only if not using a database link. (e.g., 'MyDBBackup').

 

Metadata Filters – Filters to limit the objects included in operation.

 

Database Link – If specified, this provides the name of a database link to the remote database that will be the source of data and metadata for the import, without the need for intermediary '.DMP' files. Thus, if this parameter is specified there is no need to specify 'Backup Directory' and 'Backup File Name.' In order to use this parameter, you must create a link on the target database that points to the source database.

 

Table Exists Action – Specifies the action to take when data attempts to be loaded into a preexisting table. If 'Truncate,' rows are removed from a preexisting table before inserting rows from the Import. Note that if 'Truncate' is specified on tables referenced by foreign key constraints, it will act as a 'Replace.' If 'Replace,' preexisting tables are replaced with new definitions. Before creating the new table, the old table is dropped. If 'Append,' new rows are added to the existing rows in the table. If 'Skip,' the preexisting table is left unchanged.

 

Mapping – Specify this parameter if you want to remap one or more schemas and/or tablespaces from the source database to new schemas and/or tablespaces in the target database.

 

 

This job step allows you to execute an Oracle PL/SQL block. The code referenced in the block must be valid PL/SQL code for the Oracle release you expect to run. Oracle OLE/DB drivers will be used to execute the code block.

 

 

Platforms Supported

Windows, UNIX-based.

 

Software Pre-Requisites

Design and Windows: OLE/DB drivers are used as specified. For Oracle: Oracle .Net Provider version 2.102.2.20 or later (for 32-bit) and 2.102.3 or later (for 64-bit).

 

UNIX: Oracle JDBC drivers are used. TNSNAMES.ORA file must be available under the ORACLE_HOME environment variable.

 

Job Steps Properties

 

Connection Information - This set of properties indicates how the job step is to connect to the database.

 

  • Datasource

    • DataSource – This property references the target data source for the PL/SQL Block.

      • This property also supports ActiveBatch variables (as in the example above which is referencing the Job Scheduler’s own database).

    • Credentials – The object path of a User Account object. Clicking on the “Helper” will cause a tree display of all ActiveBatch containers. You may then select a User Account object. The User Account credentials must have proper access to the data source. Typically, the credentials will be a valid database username and password for this data source (unless Windows authentication is used in which the username/password will be a valid Windows account).

  • Handle – Windows: This property represents a ConnectionHandle from an earlier successful Connect job step.

 

Content – This property represents the PL/SQL block of code. The content can be multi-line (clicking on the Helper button will cause an edit box to appear that will allow multi-line code entry or edit).

 

Output Results – This optional Boolean property indicates whether the output of the PLSQL should be written to the job’s log file.

 

Return Value Code – This optional property indicates what should be returned via the Job Return Value. Two (2) selections are possible: None and Dataset.

 

Job Return Value

 

Dataset – This object contains useful information concerning the PL/SQL Block that was executed.

 

 

The above example uses a Connect job step to establish a connection handle. The handle is then passed to the PLSQL job step as a Connection Information parameter.

 

 

This job step allows you to execute an Oracle Job within the ActiveBatch environment. ActiveBatch Oracle Jobs are implemented through Oracle’s DBMS_SCHEDULER interface. Running Oracle Jobs within ActiveBatch allows you to leverage your existing jobs while also removing the limitations present in the Oracle database software.

 

 

Platforms Supported

Windows, UNIX-based.

 

Software Pre-Requisites

Design and Windows: OLE/DB drivers are used as specified. For Oracle: Oracle .Net Provider version 2.102.2.20 (for 32-bit) and 2.102.3 (for 64-bit).

 

UNIX: Oracle JDBC drivers are used. TNSNAMES.ORA file must be available under the ORACLE_HOME environment variable.

 

Job Steps Properties

 

Connection Information - This set of properties indicates how the job step is to connect to the database.

 

  • Datasource

    • DataSource – This property references the “source” data source that the Oracle Job will be started on.

      • This property also supports ActiveBatch variables (as in the example above which is referencing the Job Scheduler’s own database).

    • Credentials – The object path of a User Account object. Clicking on the “Helper” will cause a tree display of all ActiveBatch containers. You may then select a User Account object. The User Account credentials must have proper access to the data source. Typically, the credentials will be a valid database username and password for this data source (unless Windows authentication is used in which the username/password will be a valid Windows account).

  • Handle – Windows: This property represents a ConnectionHandle from an earlier successful Connect job step.

 

JobName – This property represents the name of the Oracle job you want to execute. You can click on the dropdown helper to obtain a list of enumerable Oracle jobs. If your job is not present, you can type in the name of the job.

 

WaitCompletion – This Boolean property indicates whether ActiveBatch should wait for the job to complete. A value of true means the ActiveBatch job should wait for Oracle job completion. The default is true (or to wait).

 

PollingInterval – This property indicates how often ActiveBatch should check on the progress of the Oracle job. This property is only applicable if WaitCompletion=True. A value of zero (0) indicates that the default ActiveBatch value of 30 seconds will be used.

 

Return Step Value

 

If the Job Step completes properly an execution variable object named “OracleJobReturnValue” is returned. This structure contains the data source, job name, job status and instanceid of the Oracle Job. InstanceId can be used in SynchronizeJob if this step did not wait for completion. Job Status represents the status of the Oracle Job itself.

 

 

This job step allows you to synchronize on an existing executing Oracle Job and wait for its completion. This step complements the Oracle Start Job step in that you can elect to not wait for completion when the Oracle Job is started; perform any additional overlapping processing and then synchronize and wait for the Oracle Job to complete.

 

 

Platforms Supported

Windows, UNIX-based.

 

Software Pre-Requisites

Design and Windows: OLE/DB drivers are used as specified. For Oracle: Oracle .Net Provider version 2.102.2.20 (for 32-bit) and 2.102.3 (for 64-bit).

 

UNIX: Oracle JDBC drivers are used. TNSNAMES.ORA file must be available under the ORACLE_HOME environment variable.

 

Job Steps Properties

 

Connection Information - This set of properties indicates how the job step is to connect to the database.

 

  • Datasource

    • DataSource – This property references the “source” data source.

      • This property also supports ActiveBatch variables (as in the example above which is referencing the Job Scheduler’s own database).

    • Credentials – The object path of a User Account object. Clicking on the “Helper” will cause a tree display of all ActiveBatch containers. You may then select a User Account object. The User Account credentials must have proper access to the data source. Typically, the credentials will be a valid database username and password for this data source (unless Windows authentication is used in which the username/password will be a valid Windows account).

  • Handle – Windows: This property represents a ConnectionHandle from an earlier successful Connect job step.

 

JobName – This property represents the name of the Oracle job that is executing. You can click on the dropdown helper to obtain a list of enumerable Oracle jobs. If your job is not present, you can type in the name of the job.

 

JobInstance – This property indicates whether you want to synchronize on a specific running Oracle Job instance or whether any instance will do. This property is useful when multiple instances of an Oracle Job are running. “AnyInstance” represents any instance. “SpecificInstance” represents a specific instance using an InstanceId. The InstanceId is one of the properties returned when the Oracle Job was successfully launched.

 

EvaluateCompletionStatus – This Boolean property indicates whether the Oracle Job’s completion status should be reflected in how this Job Step completes. A value of true means that if the Oracle Job completes successfully then the SynchronizeJob step completes successfully. If the Job fails, the job step will fail as well. A value of false means that the job step will complete based on its own merits and the return value “JobStatus” (as noted below) needs to be checked. The default is true.

 

PollingInterval – This property indicates how often ActiveBatch should check on the progress of the Oracle job. This property is only applicable if WaitCompletion=True. A value of zero (0) indicates that the default ActiveBatch value of 30 seconds will be used.

 

Return Step Value

 

If the Job Step completes properly an execution variable object named “OracleJobReturnValue” is returned. This structure contains the data source, job name, job status and instanceid of the Oracle Job. Job Status represents the status of the Oracle Job itself.