Steps Used During Demonstrations for OracleCode 2019 Session DEV5345

Preparation before Demos

1. Launch Cloud Developer Image

Make sure you add your public ssh key. If you don’t have an ssh key pair, see the documentation on how to generate one.

2. ssh to your instance running Cloud Developer Image, set up tunnel

ssh -i <path to ssh private key> -L 5901:localhost:5901 opc@<instance IP address>

3. Set VNC password and start VNC server

$ vncpasswd

Then:

$ vncserver -geometry 1600x1200 -geometry 800x600 -geometry 1920x1200

4. Configure OCI Client and add API Key to Profile

Run

$ oci setup config

and follow the directions, providing the necessary OCIDs as described in the documentation on Required Keys and OCIDs.

Remember to upload your API key by following the instructions in the same documentation. If you accepted all the defaults during the oci client setup, the public key to upload is the output of this:

$ cat /home/opc/.oci/oci_api_key_public.pem

Create Database

5. Create Autonomous Transaction Processing Database using the OCI CLI

First, use the oci-metadata utility to assign your tenancy’s OCID (aka compartment id) to an environment variable. As of 9/16/2019, there is an issue with the Cloud Developer Image that requires the following command to be run before you begin:

$ sudo rm /var/tmp/oci-utils.log

After that, you can invoke the commands to

$ export C=`oci-metadata -g compartmentid --value-only`
$ oci db autonomous-database create --compartment-id $C --db-name <DATABASE NAME> --cpu-core-count 1 --data-storage-size-in-tbs 1 --admin-password “<YOUR PASSWORD>”

You should see output similar to:

    {
      "data": {
        "autonomous-container-database-id": null, 
        "compartment-id": "ocid1.tenancy.oc1..aaaaaaaa5eldpsknwduzu4m533r3h5oxthv6y ydmyeqok3lunzc6ri3sjw7q", 
        "connection-strings": null, 
        "connection-urls": null, 
        "cpu-core-count": 1, 
        "data-storage-size-in-tbs": 1, 
        "db-name": "owadb1", 
        "db-version": null, 
        "db-workload": "OLTP", 
        "defined-tags": {}, 
        "display-name": "autonomousdatabase20190915163711", 
        "freeform-tags": {}, 
        "id": "ocid1.autonomousdatabase.oc1.uk-london-1.abwgiljrahdoyzxqemcriad7lka dd22ofc3trsvojg4y53dfs4xlmnoomdhq", 
        "is-auto-scaling-enabled": false, 
        "is-dedicated": false, 
        "is-preview": false, 
        "license-model": "BRING_YOUR_OWN_LICENSE", 
        "lifecycle-details": null, 
        "lifecycle-state": "PROVISIONING", 
        "service-console-url": null, 
        "time-created": "2019-09-15T16:37:11.073000+00:00", 
        "used-data-storage-size-in-tbs": null, 
        "whitelisted-ips": null
      }, 
      "etag": "5ed81868", 
      "opc-work-request-id": "ocid1.coreservicesworkrequest.oc1.uk-london-1.abwgilj rviyijwgzwqucpirxco2632wd2exz7th52elxvi4t72yqussyzyka"
    }

5.1 Check on the Database Provisioning Progress

Next, export the the ID of the database being provision to an environment variable, DB_ID so you can use it later.

$ export DB_ID=`oci db autonomous-database list --compartment-id $C | jq -r '.data[] | select( ."db-name" == “<DATABASE NAME>" ).id'`

You can check the database state with the following command. Initially, this command will return PROVISIONING

$ oci db autonomous-database get --autonomous-database-id $DB_ID | jq -r '.data["lifecycle-state"]'
AVAILABLE

Get Connected using SQLcl

6.Download and extract Credentials (wallet)

$ oci db autonomous-database generate-wallet --autonomous-database-id $DB_ID --password <YOUR PASSWORD> --file wallet.zip

7.Set TNS_ADMIN environment variable, add it to .bash_profile, and extract wallet.zip

$ export TNS_ADMIN="`cat /etc/ld.so.conf.d/oracle-instantclient.conf`/network/admin"
$ sudo -E unzip ~/wallet.zip -d $TNS_ADMIN

8. Start and configure SQLcl

$ sql /nolog
SQL> set cloudconfig /home/opc/wallet.zip

9. Connect to admin user

`SQL> connect admin/<YOUR PASSWORD>@<YOUR DATABASE NAME>_high

Get connected with SQL Developer

10. Start VNC client and initialize the Oracle Linux desktop

Start a VNC client, connect to vnc://localhost:5901. Follow the instructions to initialize the Oracle Linux desktop.

11. Start SQL Developer and Create a Connection

From the main Desktop menu, choose Applications > Programming > SQL Developer

To create a database connection (See figure 4.):

In the connections panel, click the icon to create a New Database Connection…

• Name your connection
• For Connection Type, choose Cloud Wallet
• Browse for the wallet.zip you downloaded earlier

You can leave the default Service unless you have other Autonomous Databases in this tenancy

Connecting Python To Autonomous Database

12 Interactive Python

Using system python version 2.7, you can import the already installed cx_Oracle module and connect to Autonomous Database like so:

$ python
>>> import cx_Oracle
>>> db=cx_Oracle.connect("admin/<YOUR PASSWORD>@<YOUR DATABASE NAME>_high")
>>> db.version
>>> '18.4.0.0.0'

For Python 3.6, we can install the cx_Oracle module via pip:

$ sudo python3 -m pip install cx_Oracle
WARNING: Running pip install with root privileges is generally not a good idea. Try `__main__.py install --user` instead.
Collecting cx_Oracle
  Downloading https://files.pythonhosted.org/packages/6a/6f/0ab8c62992bb694c851f9cd6d19974d27e9fca8970fc27794b9653fd174f/cx_Oracle-7.2.2-cp36-cp36m-manylinux1_x86_64.whl (731kB)
    100% |████████████████████████████████| 737kB 1.5MB/s 
Installing collected packages: cx-Oracle
Successfully installed cx-Oracle-7.2.2

Then, again, to connect:

$ python3
Python 3.6.8 (default, Aug  7 2019, 08:02:28) 
[GCC 4.8.5 20150623 (Red Hat 4.8.5-39.0.1)] on linux
Type "help", "copyright", "credits" or "license" for more information.
>>> import cx_Oracle
>>> db=cx_Oracle.connect("<YOUR PASSWORD>@<YOUR DATABASE NAME>_high")
>>> db.version
'18.4.0.0.0'

Connecting Node.js to Autonomous Database using node-oracledb

13. Based on sample code from from node-oracledb repo on github.com/oracle

Clone the node-oracledb repo from the Oracle organization on github.com

$ git clone https://github.com/oracle/node-oracledb

Edit dbconfig.js using VSCode

• Open Visual Studio Code via Desktop menu Applications > Programming > VsCode - OSS
• Open dbconfig.js in /home/opc/node-oracledb/examples
• Change the username, password and connection string to match your details and
• Save the file

Run connect.js

The Node.js 10.x as well as the node-oracledb module version 4 are already installed, so you are ready to run connect.js

$ node connect.js