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 Displays the data asset type based on the icon you select in the Select data asset type panel. For more information, see Creating a Data Asset.
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 an option and provide the login credentials information:

  • Upload wallet: Under Wallet file, drag your wallet file to the Drop file space, or click Select file to browse and then select the wallet to upload. Optionally, enter the Wallet password.
  • Select database: Select the Region, enter the Tenant OCID, select the Compartment, and select the Autonomous DB. Optionally, enter the Wallet password.
  • Use vault secret OCID: Enter the OCID of the secret for the wallet, and then 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 check box 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 specify. You can change the identifier value, but after you create and save the connection, you cannot update the identifier.
Description

Add a description. This field is optional.
Username and Password

If you selected Upload wallet or Select database, enter the user name and password.
Username and Vault Secret OCID for database password

If you selected Use vault secret OCID, enter the user name and then the OCID of the secret for the database password 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, you can click Test connection to ensure you have entered the data asset details correctly.

A success or failure message displays, indicating whether the test was successful or not. If the test fails, review your 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.