1. Migrate Apache Tomcat workloads to the cloud
  2. Migrate the Application Database

Migrate the Application Database

The Oracle Autonomous Transaction Processing removes the need to maintain, patch and optimize the database host and software, and therefore only provides access to the schema level of the database. For this reason, the migration tools available to migrate an on-premises database to Autonomous Transaction Processing are based on datapump, a schema-level migration tool included with Oracle Database.

About Migrating the Application Database

The database migration process consists in creating a database schema dump on the source database, uploading it to object storage on Oracle Cloud Infrastructure (OCI), configuring the Autonomous Transaction Processing database to import from object storage and import the database schema dump from Oracle Cloud Infrastructure Object Storage.

The following is an overview of the process:

  1. Export the database schema(s) into a local dump file.
  2. Move the dump file to Object Storage using the OCI CLI.
  3. Get the OCID of the autonomous database.
  4. Get the wallet to access the autonomous database from the source database (or some alternative host).
  5. Create a local tunnel to the autonomous database for configuration.
  6. Get an OCI Auth Token for the autonomous database to access Oracle Cloud Infrastructure Object Storage.
  7. Configure the autonomous database access credentials.
  8. Import the dump file from Oracle Cloud Infrastructure Object Storage into the autonomous database.

Export the Database Schema and Data

Export the database schema and data into a single dump file.

  1. Create an export folder for the dump file.
    Note that we are creating a single file dump, which may be extremely large in some circumstances. See other options for using datapump to create multi-part files.
    EXPORT_DB_DIRNAME=export
mkdir /home/oracle/${ DIRNAME}
# drop directory if it exists
echo "DROP DIRECTORY ${EXPORT_DB_DIRNAME};" | sqlplus system/PASSWORD@$HOST:PORT/SERVICE
# create a directory object in the DB for export with datapump, pointing to the folder created above
echo "CREATE DIRECTORY ${EXPORT_DB_DIRNAME} AS '/home/oracle/export/';" | sqlplus 
system/PASSWORD@$HOST:PORT/SERVICE
# export the schema 
expdp system/PASSWORD@$HOST:PORT/SERVICE schemas=SCHEMA_NAME 
DIRECTORY=${EXPORT_DB_DIRNAME}
  2. Move the dump file to the Oracle Cloud Infrastructure Object Storage bucket that you created earlier by using the Oracle Cloud Infrastructure (OCI) command-line interface.
    oci os object put \
-bn bucket-name \
--file /home/oracle/export/expdat.dmp \
--name expdat.dmp

Get the OCID of the Autonomous Database

Get the OCID, private endpoint IP, and Private Endpoint URL of the Oracle Autonomous Transaction Processing database.

  1. In the Oracle Cloud Infrastructure Console, select Oracle Database, then Autonomous Transaction Processing.
  2. Select the compartment from the Compartments list on the left side of the page.
    The compartment is where you deployed the resources.
  3. Click the database you provisioned earlier to get to the details.
  4. Copy the OCID, Private Endpoint IP, and the Private Endpoint URL (hostname) and save them in a notepad for later.

Get the Database Wallet File

Get the Oracle Autonomous Transaction Processing database wallet file and update the location by setting the directory to point to the wallet location in the sqlnet.ora file.

  1. Use the Oracle Cloud Infrastructure (OCI) command-line interface to get the Autonomous Transaction Processing database wallet file using your OCID and password.
    oci db autonomous-database generate-wallet --autonomous-database-
id your ATP OCID --file wallet.zip --password your-password
  2. Unzip the wallet.
    unzip wallet.zip
  3. Edit the sqlnet.ora file and replace the directory value to match the location of the unzipped wallet files.
    WALLET_LOCATION = (SOURCE = (METHOD = file) (METHOD_DATA = 
(DIRECTORY="/home/oracle/ ")))
SSL_SERVER_DN_MATCH=yes
  4. Set the TNS_ADMIN environment variable to match the folder where the wallet was unzipped.
    export TNS_ADMIN=$(pwd)

Create a Local Tunnel to the Autonomous Database Through a Bastion Host

Create a tunnel by configuring the public IP address of the bastion host.

  1. Get the public IP of the bastion host from the terraform output.
    If the Apache Tomcat server was deployed in a public subnet, you can use the public IP of the Tomcat server, otherwise use the bastion host public IP address.
  2. Define the bastion IP to create a tunnel.
    export BASTION_IP=Public IP of the bastion
  3. Run the command to export the DB host using the Private Endpoint IP address for the Autonomous Transaction Processing database and ssh to the database host.
    This DB_HOST is the Private Endpoint IP gathered earlier. 
    export DB_HOST=10.0.2.2
ssh -4 -M -S socket -fnNT -L 1522:${DB_HOST}:1522 opc@${BASTION_IP} cat -
    When prompted, enter yes to acknowledge the new host.
  4. Edit the /etc/hosts file to point to the Private Endpoint Host of the autonomous database to 127.0.0.1 (localhost).
    This looks up the Private Endpoint Host in the tnsnames.ora file. 
    ATP_HOSTNAME=$(sed 's|.*(host=\([a-z0-9.-]*\)).*|\1|;' 
tnsnames.ora | head -n1)
sudo su -c "printf \"127.0.0.1  ${ATP_HOSTNAME}\n\"  >> 
/etc/hosts"
  5. Verify that the endpoint is correct.
    cat /etc/hosts
    The output should be similar to the following:
    127.0.0.1    localhost
::1    localhost ip6-localhost ip6-loopback
fe00::0    ip6-localnet
ff00::0    ip6-mcastprefix
ff02::1    ip6-allnodes
ff02::2    ip6-allrouters
172.19.0.3    58aa534ef636
127.0.0.1  jrhdeexg.adb.region

Generate an Auth Token

Generate a token to use when moving your data to Oracle Cloud Infrastructure Object Storage and into the Oracle Autonomous Transaction Processing database. You only see the Auth Token string when you create it, so be sure to copy the Auth Token to a secure location immediately.
  1. Log in to the Oracle Cloud Infrastructure Console
  2. Click the User icon to open the Profile menu, then click User Settings.
    Take note of your full user name (it might be your email, or your email prefixed with the single sign-on service you use, if activated).
  3. Click Auth Tokens, then click Generate Token.
  4. Provide a meaningful description for the Auth Token and click Generate Token.
  5. Copy the token and save it so that you can retrieve it later.
    The token is only displayed once. When you close the dialog, you will not be able see the token in the Console again.
  6. Click Close.

Configure the Cloud Credential in the Autonomous Database

Add your Oracle Cloud Infrastructure (OCI) username and Auth Token password as the default credentials for the Oracle Autonomous Transaction Processing database.

  1. Using SQLPLus Instant Client, connect to the remote Autonomous Transaction Processing database through the tunnel created earlier.
    sqlplus admin@atp_db_name_high
  2. At the prompt, enter the admin password for the Autonomous Transaction Processing database.
    The atp_admin_password that was configured in the terraform.tfvars file.
  3. Ensure that you're logged into a SQLPlus prompt.
    SQL>
  4. Create the OCI cloud credential in Autonomous Transaction Processing database using the following command, with your OCI username and OCI Auth Token password.
    SET DEFINE OFF
BEGIN
DBMS_CLOUD.CREATE_CREDENTIAL(
    credential_name => 'DEF_CRED_NAME',
    username => 'oci username',
    password => 'oci auth token'
);
END;
/
  5. Set your credentials as the default credential.
    ALTER DATABASE PROPERTY SET DEFAULT_CREDENTIAL = 
'ADMIN.DEF_CRED_NAME';
  6. Exit SQLPlus.
    SQL> exit

Import the Dump File into the Autonomous Database

Use datapump to import the data dump into the Autonomous Transaction Processing database.

  1. Define the environment variables for the region (such as us-ashburn-1), the namespace, bucket, filename, Autonomous Transaction Processing database name (such as atpdb), and Autonomous Transaction Processing admin password.
    export REGION=your-OCI-region
export NAMESPACE=your namespace
export BUCKET=atp-upload
export FILENAME=expdat.dmp
export ATP_DB_NAME=your ATP DB name
export ATP_PASSWORD=atp_admin_password
  2. Run the following command:
    impdp admin/${ATP_PASSWORD}@${ATP_DB_NAME}_low 
directory=data_pump_dir 
dumpfile=default_credential:https://objectstorage.${REGION}.oracl
ecloud.com/n/${NAMESPACE}/b/${BUCKET}/o/${FILENAME} parallel=16 
exclude=cluster,db_link

    The first attempt will fail with an error: ORA-01950: no privileges on tablespace 'DATA'. Go to the next step to give the user created from the database schema a quota.

  3. Grant the user unlimited tablespace. 
    echo "GRANT UNLIMITED TABLESPACE TO USER;" | sqlplus 
admin/${ATP_PASSWORD}@${ATP_DB_NAME}_low
    The following output should appear:
    Grant Succeeded
  4. Drop the tables that were created in Step 2 to allow the data to be created on the next run. 
    echo "DROP TABLE TABLE_NAME;" | sqlplus 
admin/${ATP_PASSWORD}@${ATP_DB_NAME}_low
  5. Run the following command to import the data now that the user has a valid quota.
    impdp admin/${ATP_PASSWORD}@${ATP_DB_NAME}_low 
directory=data_pump_dir 
dumpfile=default_credential:https://objectstorage.${REGION}.oracl
ecloud.com/n/${NAMESPACE}/b/${BUCKET}/o/${FILENAME} parallel=16 
exclude=cluster,db_link
    You'll see errors related to the user already existing. This is normal because objects were created when we ran the command the first time in Step 2.
The database is migrated.