Oracle RDBMS

Oracle Autonomous Transaction Processing – Move your Data with Oracle Data Pump – Part 2

In this blog post serie which has three parts, I want to describe how data will be uploaded from an on-premises environment into the Oracle Autonomous Transaction Processing database using Oracle Data Pump. 


Oracle Data Pump Export on On-Premises Database

Oracle recommends the following settings for the Oracle Data Pump export job. If you use already an 18c database, you have to set the version parameter to 12.2 to avoid this error during ATP import process: ORA-39358: Export dump file version 18.0.0 not compatible with target version 12.2.0.

My expdp export job for the schema HR:

The example schema contains these objects – output from expdp job

Transfer the Dump File Set into the Oracle Cloud

The export dump file has to be transferred into the Oracle Cloud Object Storage for later usage. First we have to create an Object Storage Bucket. Login into Object Storage menu and click on the “Create Bucket” button. 

Enter a Bucket Name and click on “Create Bucket”.

Enter the new created bucket. Click on the bucket name link or the “three bullets” on the right side to view the details.

Upload Data

Upload the export dump file, click on “Upload Object”.

“Browse” local for the Oracle Data Pump export file and click on “Upload Object”.

Object Details

Click on object “Details” to verify the object on the Object Storage. 


Note the URL. The URL will be used later for the import process.

Summary of Part 2

Upload objects into the Oracle Cloud Object Storage is very easy. If you don’t want to use the browser functionality, there are other possibilities to upload files like the API.

Now the export dump file is ready to import into the Autonomous Transaction Processing Database.

Oracle Autonomous Transaction Processing – Move your Data with Oracle Data Pump – Part 1

In this blog post serie which has three parts, I want to describe how data will be uploaded from an on-premises environment into the Oracle Autonomous Transaction Processing database Oracle Data Pump. 

Basics about ATP from the Using Oracle Autonomous Transaction Processing User Guide:

Autonomous Transaction Processing is designed to support all standard business applications and deliver scalable query performance.
Autonomous Transaction Processing provides all of the performance of the marketleading Oracle Database in an environment that is tuned and optimized for transaction processing workloads.
As a service Autonomous Transaction Processing does not require database administration. With Autonomous Transaction Processing you do not need to configure or manage any hardware, or install any software. Autonomous Transaction Processing handles creating the database, backing up the database, patching and upgrading the database, and growing or shrinking the database.

This is my setup:

  • Oracle Cloud Infrastructure account with an own Compartment
  • On-premises Oracle RDBMS 18c EE with HR example schema installed on Oracle Linux 7.4
  • Windows 10 64bit client

Creation of the Autonomous Transaction Processing Database

Login into Oracle Cloud Infrastructure in ATP menu and click on the “Create Autonomous Transaction Processing Database” button. 

Set DISPLAY NAME, DATABASE NAME, CPU CORE COUNT, STORAGE and set the Administrator Credentials. These credentials are used later to manage the Autonomous Transaction Processing database. Verify your license situation and click on the “Create Autonomous Transaction Processing Database” button. 

My ATP database is called ATPHRDATA, it contains later data from schema HR.

Some minutes later, the database has state AVAILABLE and is ready to use. Click on the ATP database name link or the “three bullets” on the right side to view the details.

Click on “Service Console”.

Enter admin as username the password from the creation step. Click on “Sign In”.

This is the ATP main dashboard. Here you can manage the ATP database. Click on the “Adminstration” link.

Download the login credentials by click on the “Download Client Credentials” link . ATP uses a SSL encrypted connection. The provided zip file contains all required files and configurations to connect a client by OCI, ODBC or JDBC with the Autonomous Transaction Processing Database. You have to protect this file to prevent unauthorized database access by a password.


The Client Credential Package

Content of the extracted “Client Credentials” package, it will used later for the connection configuration and verification. This file can also be used to configure a connection to the database with the Oracle SQL Developer 17.4 or later.

Oracle Instant Client

Download the newest Oracle Instant Client by click on the “Download Oracle Instant Client”. Older clients than 12.2 do not support ATP connections. 

In my example, I use the 18c “Instant Client Downloads for Microsoft Windows (x64)”  and the additional package with the SQL*Plus and Oracle Data Pump components. 

Client Setup

To verify the connection, I have installed the Oracle Instant Client from above and configured the Windows environment like this:

Oracle Instant Client Installation Directory C:\oracle\product\instantclient_18_3
TNS_ADMIN which contains the extracted ATP database credentials / wallet C:\oracle\network\admin


The sqlnet.ora file which is provided by Oracle ATP has to be modified to the real location of the configuration files. If your TNS_ADMIN is not located in a subdirectory of your ORACE_HOME, change the directory path to the wallet from

to your TNS_ADMIN path:

If the path is not set correctly, you will get an ORA-28759: failure to open file error.

Connection Verification by SQL*Plus

SQL*Plus Connection Test with the user admin – the TNS alias is from the provided tnsnames.ora.

Links –
ATP User Guide –
ATP Introduction –

Summary of Part 1

Now the autonomous database is ready to use, the client connection works fine. The next steps are to export the on-premises data and import them into the Autonomous Transaction Processing Database. 

Oracle Database Backup Service – Encrypt your 12.2 Database Backups to the Cloud

The Oracle RMAN backup encryption is necessary if you want to backup your database into the Oracle cloud. In Oracle 12c, you have three methods available to encrypt an Oracle RMAN backup:

  • with a passphrase
  • with a master encryption key
  • hybrid with a passphrase and an encryption key

On, the basic setup is described here:

In this blog post, I show you how to configure your database environment with a master encryption key and a keystore. I use this solution to to backup and recovery to and into the Oracle cloud. And in the cloud, I don’t like to type in passwords manually for every action or write passwords in backup and restore scripts.

There are also some issues reports like in My Oracle Support Note TDE Wallet Problem in 12c: Cannot do a Set Key operation when an auto-login wallet is present (Doc ID 1944507.1).

Here are steps to create an autologin wallet.

Configure SQLNET.ora in $TNS_ADMIN to use a Keystore

Create Keystore as SYSDBA

Open Keystore

The status is set to OPEN_NO_MASTER_KEY.

Set Master Key

Now the master key has to defined. When you have already defined a wallet earlier and deleted the keys,  you have to set the undocumented parameter to set the master key again. This works here too to set the key. Otherwise you get an ORA-28374: typed master key not found in wallet error. See Master Note For Transparent Data Encryption ( TDE ) (Doc ID 1228046.1) for further information.

Now the status is set to OPEN.

Activate Auto Login

Restart the Database

Verify if the keystore is available and WALLET_TYPE is AUTOLOGIN.

Configure RMAN for Encryption

RMAN Backup Test

A simple RMAN controlfile backup into the Oracle cloud (OPC Backup Module is already configured).

Error message if you want to backup into the Oracle cloud and the encryption is not configured correctly:

Backup Verification in V$BACKUP_PIECE – Column ENCRYPTED