App Development with Oracle Cloud Infrastructure & Oracle Autonomous Database: Get Started (DEV5345)

Version 1

    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