Prepare Autonomous Data Warehouse for Data Import

Provision an Oracle Autonomous Data Warehouse Instance

Provision an instance of Oracle Autonomous Data Warehouse in the Oracle Cloud Infrastructure service console.

Sign in to the Oracle Cloud Infrastructure service console.
Click the Autonomous Data Warehouse menu and select Create Autonomous Database.
On the Create Autonomous Database page, select the appropriate Compartment.
Enter a Display Name for the data warehouse.
Enter a Database Name.
Click Data Warehouse for workload type.
Specify the CPU Core Count and Storage (in terabytes) that are appropriate for your workload based on the number of concurrent user and queries, the volume of data, and so on. For a small data sample (1 MB file) for example, specify a CPU Core Count of 2 and 1 TB of storage.
If you want to automatically scale CPU Core Count and Storage values based on changes in your workload, click Auto Scaling.
Specify a password for the Admin account.
Specify the type of network access:
- Allow secure access from anywhere: Access the database using a public endpoint that you secure with an access control list (ACL).
- Virtual Cloud Network: This option creates a private endpoint for your database within a specified VCN.
  - Virtual Cloud Network: Select the VCN in which to launch the database. Click Change Compartment to select a VCN in a different compartment.
  - Subnet: Select the subnet to attach to the database. Click Change Compartment to select a subnet in a different compartment.
  - Hostname prefix: Optionally, specify a host name prefix for the database and associate a DNS name with the database instance.
  - Network security groups: Specify at least one network security group (NSG) for your database. An NSG functions as a virtual firewall, allowing you to apply a set of ingress and egress security rules to your database.
Click a Bring Your Own License if you want to use an existing on-premises license or click License Included to provision a license with the data warehouse.

Use Access Control Lists to Govern Access

Navigate to the Service Console for Oracle Autonomous Data Warehouse.
Click the instance name to open the console page for that instance.
Click the Actions menu and select Access Control Lists.
Specify one or more addresses that are allowed to connect to the data warehouse, either as a comma-separated list or as Classless Inter-Domain Routing (CIDR) blocks. You can specify multiple entries by clicking Additional Entry.
Click Update.

Obtain Wallet Credentials

Integration services can use a wallet that contains client credentials to access Oracle Autonomous Data Warehouse. To create a wallet, you must have an administrator account for Oracle Autonomous Data Warehouse.

Navigate to the Service Console for Oracle Autonomous Data Warehouse.
Click Administration.
On the Administration page click Download Client Credentials.
In the Client Credentials dialog, enter a wallet password and confirm the password.
Click Download to save the client security credentials zip file.

You must protect this file to prevent unauthorized database access.

Prepare to Connect to the Data Warehouse with Oracle SQL*Plus

SQL*Plus is a command-line interface used to connect to and query an Oracle database from your local workstation.

To connect to an Oracle Autonomous Data Warehouse, you must specify the location of the downloaded credential wallet and update certain environment variables. The following examples show SQL*Plus on a Linux operating system.

Unzip the downloaded wallet credentials file if you haven't yet.

For example:

<base_folder>/adwus1/dbcred
unzip Wallet_adwus1.zip
  Inflating: cwallet.sso
  Inflating: tnsnames.ora
  Inflating: tructstore.jks
  Inflating: ojdc.properties
  Inflating: sqlnet.ora
  Inflating: ewallet.pl2
  Inflating: keystore.jks

Update the wallet location specified in the sqlnet.ora file with the following information, where <base_folder> is the sign-in user's base folder:

WALLET_LOCATION=(SOURCE=(METHOD=file)(METHOD_DATA=(DIRECTORY=",<base_folder>/adwus1/dbcred")))SSL_SERVER_DN_MATCH=yes

Verify the change:

cat sqlnet.ora WALLET_LOCATION=(SOURCE=(METHOD=file)(METHOD_DATA=(DIRECTORY=",<base_folder>/adwus1/dbcred")))SSL_SERVER_DN_MATCH=yes

Set the following environment variables, where <base_folder> is the sign-in user's base folder:

export LD_LIBRARY_PATH=/usr/lib/oracle/18.5/client64/lib:$LD_LIBRARY_PATH
export PATH=/usr/lib/oracle/18.5/client64/bin:$PATH
export TNS_ADMIN=<base_folder>/adwus1/dbcred

Create Target User Schema

To import and analyze data in Oracle Autonomous Data Warehouse, you must create a user and grant them access privileges.

Connect to your target database with SQL*Plus as a user with admin access.

Create the user adwus2 and grant the user the necessary privileges:

drop user adwus2 cascade;
create user adwus2 identified by ADWpass123_#;
grant EXECUTE on UTL_HTTP to adwus2;
grant EXECUTE on DBMS_CREDENTIAL to adwus2;
grant EXECUTE on DBMS_RANDOM to adwus2;
grant CREATE ANY TABLE, DROP ANY TABLE, INSERT ANY TABLE, SELECT ANY TABLE to adwus2;
grant CREATE PUBLIC SYNONYM to adwus2;
grant CREATE PROCEDURE to adwus2;
grant ALTER SESSION to adwus2;
grant CREATE SESSION to adwus2;
grant SELECT on v\$instance to adwus2;
grant SELECT on v\$session to adwus2;
grant SELECT on v\$mystat to adwus2;
grant SELECT on gv\$sql_monitor to adwus2;
grant execute on dbms_sqltune to adwus2 ;
grant read,write on directory DATA_PUMP_DIR to adwus2;
grant dwrole to adwus2;
grant pdb_dba to adwus2;
grant select on sys.v_\$database to adwus2 ;
grant select on sys.v_\$instance to adwus2 ;
grant select on sys.v_\$sysstat to adwus2 ;
grant select on sys.v_\$cell_config_info to adwus2 ;
grant select on sys.v_\$cell_global to adwus2 ;
grant select on sys.v_\$cell_ioreason to adwus2 ;
grant select on sys.v_\$cell_db to adwus2 ;
grant select on sys.v_\$cell_state to adwus2 ;
alter user adwus2 quota unlimited on DATA;
alter user adwus2 default tablespace DATA;