Oracle Cloud Infrastructure Documentation

Autonomous Data Warehouse or Autonomous Transaction Processing Data Asset

Using Autonomous Data Warehouse or Autonomous Transaction Processing as source or target data assets in Data Integration require database privileges and additional IAM policies.

Before you create an Autonomous Data Warehouse or Autonomous Transaction Processing data asset, ensure that you understand the permissions you might need to set up, and the use of a default staging location.

Before You Begin

When you use Autonomous Data Warehouse or Autonomous Transaction Processing as a target, Data Integration uses an Object Storage bucket to stage the data before loading it into the target.

Policies and permissions to use Object Storage, and database privileges must be in place. Use the following checklist to ensure that you have the required setup you need.

Item Requirement or Consideration

Database privileges

Your database administrator must grant privileges to the Autonomous Data Warehouse or Autonomous Transaction Processing user in Data Integration. For example:

GRANT EXECUTE ON DBMS_CLOUD TO <USER_NAME>

GRANT DWROLE TO <USER_NAME>

GRANT READ, WRITE on DIRECTORY DATA_PUMP_DIR TO <USER_NAME>

Policies to allow Data Integration to use Object Storage for staging data

When you use Autonomous Data Warehouse or Autonomous Transaction Processing as a target data asset, Data Integration uses an Object Storage bucket to stage the data before loading it into the target.

Ensure that you enable the PAR_MANAGE permission on the staging bucket. For example:

allow any-user to manage buckets in <compartment-name> where ALL {request.principal.type = 'disworkspace', request.principal.id = '<workspace-ocid>', request.permission = 'PAR_MANAGE'}

In addition, you must create all the required Object Storage policies.

Object Storage default staging location

A default staging location is an existing Object Storage bucket that Data Integration uses to stage data before loading the data into the Autonomous Data Warehouse or Autonomous Transaction Processing target.

You can set up a default staging location for an Autonomous Data Warehouse or Autonomous Transaction Processing data asset when you create or edit the data asset.

As you add such a target data asset in a data flow or data loader task, Data Integration automatically uses the default staging location that has been set.

ADW or ATP Data Asset Properties

To use a secret and wallet for the database password, see OCI Vault Secrets and Oracle Wallets.

To create a data asset that connects to an ADW or ATP source, complete the following properties and connection details.

Field Description
Properties
Name

Enter a name for the data asset.
Identifier

The identifier is a system-generated value based on the name you specify. You can change the identifier value, but after you create and save the data asset, you cannot update the identifier.
Description

Add a description. This field is optional.
Type The field shows the data source type that you selected in the Select data asset type panel (as described in Creating a Data Asset). Data Integration displays the data asset property fields and default connection fields based on this type.
Autonomous database login credentials

To provide the login credentials for the autonomous database, you can upload the wallet, automatically retrieve the wallet, or use secrets in Oracle Cloud Infrastructure Vault to retrieve the wallet and wallet password.

Select one of the following options and provide the required information for that option:

  • Upload wallet: Under Wallet file, drag your wallet file to the Drop a file space, or click Select a file to browse and select the wallet to upload. Optionally, enter the wallet password.
  • Select database: Select the region and enter the tenant OCID. Then select the compartment and autonomous database. Optionally, enter the wallet password.
  • Use vault secret OCID: Enter the OCID of the secret for the wallet. Optionally, enter the OCID of the secret for the wallet password.

The wallet password is the password provided while downloading the client credentials file from the autonomous database. You must have the required permissions to connect to the autonomous database and download the client credentials file.

Note: If you use a cross-tenancy OCID, you require policies to connect to the autonomous database.
Treat NUMBER columns without precision and scale as VARCHAR

Select this checkbox to treat numbers without precision and scale as VARCHAR to prevent data loss.

For example, if you have a numeric data type with a precision value that's greater than 38, or if you have numeric data types without precision and scale properties.
Enable policies to use data asset

Click Show more information to view the details of the policy name and policy statements required to use the Autonomous Data Warehouse or Autonomous Transaction Processing data asset. The list of policies appears based on the option that you selected to provide the login credentials for the autonomous database.

If you have already added the required policies, ignore the information box. Else, specify the correct group name and compartment in the statements.

If you are an administrator, you can add the policies by clicking Add policies. If you are not an administrator, click Copy policies. You can then send these policies to the administrator and get them added.

Default connection information
Name Enter a name for the default connection.
Identifier The identifier is a system-generated value based on the name. You can change the identifier value, but after you create and save, you cannot update the identifier.
Description (Optional) Add a description.
Username and Password

Enter the user name and password when either one of the following conditions is met:

  • Upload wallet and Use password are selected
  • Select database and Use password are selected
Username and Vault secret OCID for database password

If you selected Use vault secret OCID, enter the user name and the vault secret OCID.

The OCID, from OCI Vault, is the OCID of the secret for the database password that's associated with the user.
TNS alias

Select the database service to use.

Default staging location

You can set up an Object Storage bucket to use as the default staging location for this data asset. When a default staging location is set up, Data Integration uses the specified bucket whenever this data asset is added as a target in a data flow or data loader task. To specify a default staging location, make the following selections in the order as displayed:
Object storage data asset

Select an Object Storage data asset.
Connection

Select a connection.
Compartment

Select a compartment.
Bucket

Select a bucket.

The bucket is the Object Storage bucket that Data Integration uses for staging data before loading the data into the target.

(Optional) Test connection
Test connection After you complete all the required fields, click Test connection to ensure that the data asset details have been entered correctly and the connection can be established. A success message appears if the test is successful. If the test fails with an error message, review the connection settings and try again.

The Use vault secret OCID option provides the login credentials for the autonomous database. If the test connection fails because of missing policies, Data Integration displays a list of policies that must be added.

Another reason for a test connection failure might be issues with the host's fully qualified domain name (FQDN). Try again by using an IP address.