8i | 9i | 10g | 11g | 12c | 13c | 18c | 19c | 21c | 23ai | Misc | PL/SQL | SQL | RAC | WebLogic | Linux

Oracle Cloud : Database as a Service (DBaaS) - Create Service

This service has been superseded by the Oracle Cloud Interface (OCI) Database systems described here.

This article provides a run through of creating a new DBaaS service on the Oracle Cloud.

Create SSH Key
Create Service
Connecting to the VM using SSH
Oracle Compute Cloud Service - Network (Firewall)
Connecting to the database using Oracle Net
Notes

Create SSH Key

Before you start, you are going to need a key pair for authentication to your service.

$ ssh-keygen -b 2048 -t rsa -f myOracleCloudKey
$ chmod 600 myOracleCloudKey*

Enter and confirm the passphrase when prompted. You will be asked to upload the public key during the service creation.

If you have any problems, or need instructions for using PuTTYgen on Windows, check out the documentation here.

Create Service

Log into your Oracle Cloud "My Services" dashboard. Click the "Database" tile.

On the "Oracle Database Cloud Service" page, click the "Create Instance" button.

Oracle Cloud : Database Cloud Service Console

The next page allows you to enter some details about the instance, including the following.

Service Level : "Oracle Database Cloud Service" or "Oracle Database Cloud Service - Virtual Image". Pick the first.
Software Release : 11gR2, 12cR1, 12cR2, 18c
Software Edition : Standard Edition, Enterprise Edition, Enterprise Edition - High Performance, Enterprise Edition - Extreme Performance. Check the definitions of the options included in the Enterprise Edition variants.
Metering Frequency : Hourly, Monthly

Once you are happy with your choices, click the "Next" button.

Oracle Cloud : DBaaS - Instance

Enter the instance details. The "Compute Shape" determines the number of virtual CPUs and memory associated with the service, so pick a shape that is relevant to your performance needs. Enter the database configuration details and your SSH key. Pick the backup configuration appropriate to your system. When you are happy with the configuration, click the "Next" button.

Oracle Cloud : DBaaS - Instance Details

If you are happy with the instance setting listed on the "Confirmation" screen, click the "Create" button.

Oracle Cloud : DBaaS - Confirmation

Wait while the new instance is created. The progress is shown under the status.

Oracle Cloud : DBaaS - My Services - In Progress

Once complete, the status disappears.

Oracle Cloud : DBaaS - My Services - Created

The service hamburger allows you to navigate to a number of management tools. You will need to amend the firewall rules to access these.

Oracle Cloud : DBaaS - My Services - Hamburger

Click on the service name to drill down into the instance. The detail page gives basic information about the instance, including the public IP address and the database connection string.

Oracle Cloud : DBaaS - Instance Detail

The hamburger gives you basic management operations (Start, Stop, Restart, Scale Up/Down) for the service.

Oracle Cloud : DBaaS - Service Hamburger

If there are any patches available for your database, they will be displayed in the "Administration" section.

Connecting to the VM using SSH

Most of the time you will probably be connecting to the "oracle" operating system user. You do this by specifying your private key and connect to the "oracle" user on the public IP address from your service detail page.

$ ssh -i ./myOracleCloudKey oracle@123.123.123.123

[oracle@obtest1 ~]$

Once connected, you can do all the usual stuff.

[oracle@obtest1 u01]$ sqlplus / as sysdba

SQL*Plus: Release 18.0.0.0.0 Production on Fri Mar 2 10:34:18 2018
Version 18.1.0.0.0

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


Connected to:
Oracle Database 18c Enterprise Edition Release 18.0.0.0.0 - Production
Version 18.1.0.0.0

SQL> ALTER SESSION SET CONTAINER = pdb1;

Session altered.

SQL> CREATE USER test IDENTIFIED BY test;

User created.

SQL> GRANT CREATE SESSION TO test;

Grant succeeded.

SQL>

If you need to perform any tasks as root, you must connect to the "opc" user and run them using "sudo".

$ ssh -i ./myOracleCloudKey opc@123.123.123.123
-bash-4.1$ sudo vi /etc/hosts

Oracle Compute Cloud Service - Network (Firewall)

The DBaaS services are run under the Oracle Compute Cloud (IaaS). This has it's own firewall configuration, allowing you to limit access to your services. By default, all endpoints except SSH are disabled. There are a number of predefined "Security Rules" to open up the assorted endpoints, but they typically open the endpoints to public, which is rather risky. Instead, you should define custom rules, opening access to ports from specific machines.

Navigate to the main "Oracle Database Cloud Service".
Click on the hamburger next to the service of interest.
Click the "Access Rules" option on the popup menu.
Click the "Create Rule" button.
Enter a "Rule Name".
Select "" as the "Source" and enter your IP address in resulting box.
Select "DB_1" as the "Destination".
Enter "1521" as the "Destination Port(s)".
Leave "TCP" as the "Protocol".
Click the "Create" button.

You should now be able to connect to the database from the specified IP address.

You will need to do a similar process for the other tools you want to connect to, like APEX, DB Express etc.

Connecting to the database using Oracle Net

The "sqlnet.ora" file on the server contains the following entries, so any connections to the server are encrypted using Native Network Encryption by default.

SQLNET.ENCRYPTION_SERVER = required
SQLNET.CRYPTO_CHECKSUM_TYPES_SERVER = (SHA1)
SQLNET.CRYPTO_CHECKSUM_SERVER = required
ENCRYPTION_WALLET_LOCATION = (SOURCE=(METHOD=FILE)(METHOD_DATA=(DIRECTORY=/u01/app/oracle/admin/cdb1/tde_wallet)))
SQLNET.ENCRYPTION_TYPES_SERVER = (AES256, AES192, AES128)
NAMES.DIRECTORY_PATH = (TNSNAMES, EZCONNECT)
SQLNET.WALLET_OVERRIDE = FALSE
SQLNET.EXPIRE_TIME = 10
SSL_VERSION = 1.2
WALLET_LOCATION = (SOURCE=(METHOD=FILE)(METHOD_DATA=(DIRECTORY=/u01/app/oracle/admin/cdb1/db_wallet)))

SQL*Net access is disabled by default, but you can enable it as described above. Once enabled, create a local "tnsnames.ora" entry as follows. The connection details are available from your service detail screen.

pdb1_oc=
  (DESCRIPTION=
    (ADDRESS=
      (PROTOCOL=TCP)
      (HOST=123.123.123.123)
      (PORT=1521)
    )
    (CONNECT_DATA=
      (SERVICE_NAME=pdb1.my-identity.oraclecloud.internal)
    )
  )

Now you can connect to the database.

C:\>sqlplus test/test@pdb1_oc

SQL*Plus: Release 12.2.0.1.0 Production on Fri Mar 2 12:14:37 2018

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

Last Successful login time: Fri Mar 02 2018 12:14:29 +00:00

Connected to:
Oracle Database 18c Enterprise Edition Release 18.0.0.0.0 - Production

SQL>

Alternatively, connect using the EZconnect URL.

C:\>sqlplus test/test@123.123.123.123:1521/pdb1.my-identity.oraclecloud.internal

SQL*Plus: Release 12.2.0.1.0 Production on Fri Mar 2 12:16:15 2018

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

Last Successful login time: Fri Mar 02 2018 12:14:38 +00:00

Connected to:
Oracle Database 18c Enterprise Edition Release 18.0.0.0.0 - Production

SQL>

Notes

Assuming your public IP address were 123.123.123.123 and you've opened the relevant services on the firewall, the following URLs are available.

DBaaS Monitor: https://123.123.123.123/dbaas_monitor/
Username: dbaas_monitor
Password: (set during installation)

APEX: https://123.123.123.123/ords/pdb1
Workspace: INTERNAL
Username : ADMIN
Password : (set during installation)

DB Express : https://123.123.123.123:5500/em

The database created by the "Oracle Database Cloud Service" option does not have multiplexed redo logs, so you will need to sort this manually.

For more information see:

Hope this helps. Regards Tim...

Back to the Top.