How to create a SSL connection between Oracle 10.2.0.5 and Amazon Aurora Data Migration Services

Amazon have documented this process, however this is for 11g source Oracle database only, and there are a few steps that must be adhered to in order to get this to work. Please note, there are a few changes from 10g to 11g using orapki, specifically with the auto_login command.
Please set your Oracle environment so the $ORACLE_HOME is set. This document has been written for UNIX environments.

Please note: Oracle 10.2.0.5 only supports SHA1 certificates.

The steps as follows:

Step 1 – On the Oracle server, create a directory you will use to work with the self-signed certificate.

mkdir my_ssl

Step 2 – Change into the directory you created in the previous step.

cd my_ssl

Step 3 – Create a root key.

openssl genrsa -out self-rootCA.key 1024

Step 4 – Self sign a root certificate using the root key you created in the previous step.

openssl req -x509 -new -nodes -key self-rootCA.key -sha1 -days 1024 -out self-rootCA.pem

Step 5 – Create an Oracle wallet directory for the Oracle database.

mkdir $ORACLE_HOME/ora_ssl_wallet

Step 6 – Create a new Oracle wallet.

orapki wallet create -wallet $ORACLE_HOME/ora_ssl_wallet -pwd Welcome1 -auto_login

Step 7 – Add the root certificate to the Oracle wallet.

orapki wallet add -wallet $ORACLE_HOME/ora_ssl_wallet -trusted_cert -cert self-rootCA.pem -pwd Welcome1

Step 8 – List the contents of the Oracle wallet. The list should include the root certificate.

orapki wallet display -wallet $ORACLE_HOME/ora_ssl_wallet

Step 9 – Generate the Certificate Signing Request (CSR) using the ORAPKI utility.

orapki wallet add -wallet $ORACLE_HOME/ora_ssl_wallet -dn "CN=MyToll, OU=GT, O=Toll, L=Melbourne, ST=Victoria, C=AU" -keysize 1024 -pwd Welcome1

Step 10 – List the contents of the Oracle wallet. The list should include the CSR.

orapki wallet display -wallet $ORACLE_HOME/ora_ssl_wallet

Step 11 – Export the CSR from the Oracle wallet.

orapki wallet export -wallet $ORACLE_HOME/ora_ssl_wallet -dn "CN=MyToll, OU=GT, O=Toll, L=Melbourne, ST=Victoria, C=AU" -request self-signed-oracle.csr -pwd Welcome1

Step 12 – Sign the CSR using the root certificate.

openssl x509 -req -in self-signed-oracle.csr -CA self-rootCA.pem -CAkey self-rootCA.key -CAcreateserial -out self-signed-oracle.crt -days 365 -sha1

Step 13 – Add the Client certificate to the server wallet.

orapki wallet add -wallet $ORACLE_HOME/ora_ssl_wallet -user_cert -cert self-signed-oracle.crt -pwd Welcome1

Step 14 – List the content of the Oracle wallet.

orapki wallet display -wallet $ORACLE_HOME/ora_ssl_wallet

Step 15 – Configure sqlnet.ora file ($ORACLE_HOME/network/admin/sqlnet.ora).

WALLET_LOCATION =
   (SOURCE =
     (METHOD = FILE)
     (METHOD_DATA = (DIRECTORY = /app/oracle/10.2.0.5/ora_ssl_wallet))
   )

SSL_VERSION = 1.0
SSL_CLIENT_AUTHENTICATION = FALSE
SSL_CIPHER_SUITES = (SSL_RSA_WITH_AES_256_CBC_SHA)

Step 16 – Stop the Oracle listener.

lsnrctl stop

Step 17 – Add entries for SSL in the listener.ora file $ORACLE_HOME/network/admin/listener.ora.

WALLET_LOCATION =
   (SOURCE =
     (METHOD = FILE)
     (METHOD_DATA = (DIRECTORY = /app/oracle/10.2.0.5/ora_ssl_wallet))
   )

SID_LIST_LISTENER =
  (SID_LIST =
    (SID_DESC =
      (SID_NAME = PLSExtProc)
      (ORACLE_HOME = /app/oracle/10.2.0.5)
      (PROGRAM = extproc)
    )
  )

LISTENER =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = TCP)(HOST = orahost)(PORT = 1521))
      (ADDRESS = (PROTOCOL = TCPS)(HOST = orahost)(PORT = 1522))
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
    )
  )

Step 18 – Configure the tnsnames.ora file $ORACLE_HOME/network/admin/tnsnames.ora.

DMS_SSL =
   (DESCRIPTION =
     (ADDRESS_LIST =
     (ADDRESS = (PROTOCOL = TCPS)(HOST = orahost)(PORT = 1522)))
     (CONNECT_DATA = (SERVICE_NAME = DATABASENAME))
   )

Step 19 – Restart the Oracle listener.

lsnrctl start

Step 20 – Show the Oracle listener status.

lsnrctl status

Step 21 – Test the SSL connection to the database from localhost using sqlplus and the SSL tnsnames entry.

Step 22 – Verify that you successfully connected using SSL.

SELECT SYS_CONTEXT('USERENV', 'network_protocol') FROM DUAL;

		SYS_CONTEXT('USERENV','NETWORK_PROTOCOL')
		--------------------------------------------------------------------------------
		tcps

Step 23 – Change directory to the directory with the self-signed certificate.

cd ~/my_ssl

Step 24 – Create a new client Oracle wallet that AWS DMS will use.

orapki wallet create -wallet ./ -auto_login

Step 25 – Add the self-signed root certificate to the Oracle wallet. There is no auto_login in 10.2.0.5

orapki wallet add -wallet ./ -trusted_cert -cert rootCA.pem

Step 26 – List the contents of the Oracle wallet that AWS DMS will use. The list should include the self-signed root certificate.

orapki wallet display -wallet ./

Step 27 – Upload the Oracle wallet you just created to AWS DMS. Note: you may have to create a new replication instance