Creating Oracle Database in a Cloud

Login to Oracle Cloud console, go to Autonomous Transaction Processing page:

and click Create Autonomous Database button:

select the following database parameters:

Go to database page and download wallet file:

enter password and click “Download” button:

Connecting with SQL Developer

Start SQL Developer on a client machine and create a connection with the following parameters:

SQL Developer of version higher than 18.2 does not ask for wallet password.

Configuring OCI on Windows

Ensure that the wallet contains the following files:

cwallet.sso
ewallet.p12
keystore.jks
ojdbc.properties
sqlnet.ora
tnsnames.ora
truststore.jks

Then check if OCI home directory is in the path:

Define TNS_ADMIN environment variable according to docs:

set TNS_ADMIN=C:\oracle\product\12.1.0\client_1\Network\Admin

Copy wallet files into a subdirectory of C:\oracle\product\12.1.0\client_1\Network\Admin, for example nwxdb2 and add the following to C:\oracle\product\12.1.0\client_1\Network\Admin\sqlnet.ora:

SQLNET.AUTHENTICATION_SERVICES= (NTS)
NAMES.DIRECTORY_PATH= (TNSNAMES, EZCONNECT)

NAME.DIRECTORY_PATH= (TNSNAMES, LDAP, EZCONNECT, HOSTNAME)
WALLET_LOCATION =
  (SOURCE =
    (METHOD = FILE)
    (METHOD_DATA =
      (DIRECTORY = C:\oracle\product\12.1.0\client_1\Network\Admin\nwxdb2)
    )
  )
SSL_SERVER_DN_MATCH=yes
SQLNET.WALLET_OVERRIDE = TRUE
SSL_CLIENT_AUTHENTICATION = TRUE

append the content of C:\oracle\product\12.1.0\client_1\Network\Admin\nwxdb2\tnsnames.ora to C:\oracle\product\12.1.0\client_1\Network\Admin\tnsnames.ora, so you have names like (nwxdb2_high, nwxdb2_low, nwxdb2_medium, nwxdb2_tp, nwxdb2_tpurgent).

Then connect with sqlplus tool to test the connection:

sqlplus ADMIN/<password>@nwxdb2_high
SQL*Plus: Release 12.2.0.1.0 Production on Fri May 29 12:36:13 2020

Copyright (c) 1982, 2016, Oracle.  All rights reserved.

Last Successful login time: Fri May 29 2020 12:27:40 +03:00

Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production

SQL> select * from dual;

D
-
X

and specify nwxdb2_high as wallet alias along with the database user ID and password in your software connection parameters.

Adding credentials to the wallet

Autonomous DB wallet does not contain credentials but they can be added to the wallet with mkstore utility that is included to Oracle Database installation (not to Oracle Client) with the following steps.

Ensure that there are no credentials in the wallet:

mkstore -wrl nwxdb2/ -listCredential
Oracle Secret Store Tool Release 19.0.0.0.0 - Production
Version 19.3.0.0.0
Copyright (c) 2004, 2019, Oracle and/or its affiliates. All rights reserved.

Enter wallet password:
List credential (index: connect_string username)

Add them:

TNS_ADMIN=/home/oracle/work/nwxdb2;export TNS_ADMIN
mkstore -wrl nwxdb2/ -createCredential nwxdb2_high admin <password>

Ensure that the credentials were added correctly:

mkstore -wrl nwxdb2/ -listCredential
Oracle Secret Store Tool Release 19.0.0.0.0 - Production
Version 19.3.0.0.0
Copyright (c) 2004, 2019, Oracle and/or its affiliates. All rights reserved.

Enter wallet password:
List credential (index: connect_string username)
1: nwxdb2_high admin

Copy updated wallet to C:\oracle\product\12.1.0\client_1\Network\Admin and ensure that sqlplus connects without user ID and password at the client side:

sqlplus /@nwxdb2_high
SQL*Plus: Release 12.2.0.1.0 Production on Fri May 29 12:55:15 2020

Copyright (c) 1982, 2016, Oracle.  All rights reserved.

Last Successful login time: Fri May 29 2020 12:47:40 +03:00

Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production

SQL> select * from dual;

D
-
X

Finally, specify the wallet alias in your software connection parameters.

Possible connections errors

Possible connections errors are:

  • ORA-28860: Fatal SSL error
  • ORA-01017: invalid username/password; logon denied

Links:

Leave a Reply

Your email address will not be published.