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