Tagged ORACLE DATABASE

Deploying an Oracle Database with Persistence Enabled in Oracle Kubernetes Engine in Ten Minutes or Less


In a previous post I explained how to create the same thing using an image published in docker registry under my user. Well… that post is not working anymore because I deleted the image for some reasons.

The method exposed here is better because the deployment file pulls the official image published here.

Therefore you only have to create a user, accept the licence agreement (just in case you acknowledge it) and follow the steps explained here.

Hope it helps! 😉

 

Microservices and SODA: JSON Data Stored in an Oracle Database Accessed through​ REST or Java


Simple Oracle Document Access

SODA (Simple Oracle Document Access) is a non-SQL style of storing/retrieving JSON data in an Oracle database.

cold summer alcohol cocktail
Photo by Pixabay on Pexels.com

It’s so easy to work with SODA! Let’s get started.

First, enable ORDS schema in your database.

Second, deploy ORDS in K8s (you can also deploy ORDS standalone in your laptop or in a container if you don’t have one, here you can find an example).

Now let’s try it:

Create a collection:

A collection is a table that transparently stores JSON data for you. Execute the following instruction remarked in bold:

curl -i -X PUT http://130.61.67.88:8080/ords/hr/soda/latest/shopcartJSON

HTTP/1.1 201 Created

Date: Sat, 22 Dec 2018 19:15:26 GMT

X-Frame-Options: SAMEORIGIN

Cache-Control: private,must-revalidate,max-age=0

Location: http://130.61.67.88:8080/ords/hr/soda/latest/shopcartJSON/

Content-Length: 0

Insert JSON in the database:

Download this to a file called po.json and execute the following (please note the ID of the new doc created)

curl -X POST --data-binary @po.json -H "Content-Type: application/json" "http://130.61.67.88:8080/ords/hr/soda/latest/shopcartJSON/"

{"items":[{"id":"1C0C85A9B7084DAD88AC9155DC998578","etag":"D17DBB877A3B846EDB32789589F8CDB791634359D615376D938D167AAA3E1F46","lastModified":"2018-12-22T19:21:02.243934Z","created":"2018-12-22T19:21:02.243934Z"}],"hasMore":false,"count":1}

Just in case you haven’t noticed the magic of this, take a look at the following picture:

sodajson.png

Retrieve document by ID:

The JSON data is stored in a table. Now we can retrieve the document by the ID that was previously given as follows:

curl -X GET http://130.61.67.88:8080/ords/hr/soda/latest/shopcartJSON/1C0C85A9B7084DAD88AC9155DC998578

{"PONumber": 0,

"Reference": "ABANDA-20140803",

"Requestor": "Amit Banda",

...

"UPCCode": 85391181828},

"Quantity": 9.0}]

Search for documents:

So far, so good. But wait, there is more! Let’s search for the content of a JSON attribute, in this case, documents with PO number is 0:

curl -X POST --data-binary '{"PONumber":"0"}' -H "Content-Type: application/json" http://130.61.67.88:8080/ords/hr/soda/latest/shopcartJSON?action=query

{"items":[{"id":"1C0C85A9B7084DAD88AC9155DC998578","etag":"D17DBB877A3B846EDB32789589F8CDB791634359D615376D938D167AAA3E1F46","lastModified":"2018-12-22T19:21:02.243934Z","created":"2018-12-22T19:21:02.243934Z","value":{"PONumber": 0,...]}}],"hasMore":false,"count":1}

Bulk insert:

Now, let’s insert a bunch of JSON’s, first download this content and save as POlist.json and execute the following in bold:

curl -X POST --data-binary @POlist.json -H "Content-Type: application/json" http://130.61.67.88:8080/ords/hr/soda/latest/shopcartJSON?action=insert

{"items":[{"id":"0A6DF1E1A3A745D98DC3E3DD648732B2","...22T19:45:42.157916"}],"hasMore":false,"count":70,"itemsInserted":70}

As you can observe, we have inserted several objects with one shot.

Update a document:

Let’s update JSON with PO=0 we searched for before (take note of the ID):

curl -i -X PUT --data-binary '{"PONumber" : "99999"}' -H "Content-Type: application/json" http://130.61.67.88:8080/ords/hr/soda/latest/shopcartJSON/1C0C85A9B7084DAD88AC9155DC998578

HTTP/1.1 200 OK

Date: Sat, 22 Dec 2018 19:55:10 GMT

X-Frame-Options: SAMEORIGIN

Cache-Control: no-cache,must-revalidate,no-store,max-age=0

ETag: BC9497081F1E314EE19DD777F2ED7E8425F51C21E7EC1968CEA8110A684C3F05

Last-Modified: Sat, 22 Dec 2018 19:55:10 GMT

Location: http://130.61.67.88:8080/ords/hr/soda/latest/shopcartJSON/1C0C85A9B7084DAD88AC9155DC998578

Content-Length: 0

 

List records, delete records and so forth:

And so on… Take a look at the SODA REST, Java and whitepaper documentation.

Discover more features

Take a look at the documentation on how to create more complex queries and many other features.

Last, but not least, notice that the JSON object can be different in each row, there is no constraint that forces to a specific structure.

Microservices and SODA

As you probably have noticed, SODA is nice for CRUD operations with JSON data (such as the list of products in the shopping cart) in the database. In addition, you can utilize a relational model for the rest of your app. In brief, SODA is good for building microservices for the modern applications of the digital era.

And finally, by storing your data in an Oracle database, you get benefit of it’s inherent robustness, reliability, resilience, high availability, fault tolerant, distributed (sharding), in-memory, backup/recovery and more.

ordsarch

That’s all folks!

Enjoy 😉

 

 

Setup ORDS in a PDB


ORDS utilizes several schemas for working properly. Follow these steps just in case your database hasn’t been set up.

NOTE: you need DBA privileges as well as 2 tablespaces created before proceeding, ask your dba or get this info before starting (although you can also manage it on the fly if you have a few database skills…)

ords

In a machine with java configured and ords.war downloaded to a working directory issue the following command and provide the correct values when prompted:

java -jar ords.war setup
Enter the name of the database server [localhost]: (the IP or name of your database server or scan o vip or whatever such as 13x.x6y.x6y.xyz, ask your dba)
Enter the database listen port [1521]: (this is the default port...ask your dba)
Enter 1 to specify the database service name, or 2 to specify the database SID [1]:
Enter the database service name:(ask your dba, for exmaple orclpdb1.localdomain)
Enter 1 if you want to verify/install Oracle REST Data Services schema or 2 to skip this step [1]:
Enter the database password for ORDS_PUBLIC_USER:(a password provided by you, write it down, you'll need later...)
Confirm password:
Requires SYS AS SYSDBA to verify Oracle REST Data Services schema.

Enter the database password for SYS AS SYSDBA:(you must be a dba or lucky to have been given to you)
Confirm password:
Retrieving information.
Enter the default tablespace for ORDS_METADATA [SYSAUX]:( the name of an existing tblespace such as USERS, ask your dba)
Enter the temporary tablespace for ORDS_METADATA [TEMP]:
Enter the default tablespace for ORDS_PUBLIC_USER [SYSAUX]:USERS
Enter the temporary tablespace for ORDS_PUBLIC_USER [TEMP]:
Enter 1 if you want to use PL/SQL Gateway or 2 to skip this step.
If using Oracle Application Express or migrating from mod_plsql then you must enter 1 [1]:
Enter the PL/SQL Gateway database user name [APEX_PUBLIC_USER]:
Enter the database password for APEX_PUBLIC_USER:
Confirm password:
Enter 1 to specify passwords for Application Express RESTful Services database users (APEX_LISTENER, APEX_REST_PUBLIC_USER) or 2 to skip this step [1]:
Enter the database password for APEX_LISTENER:(a password provided by you, write it down, you'll need later...)
Confirm password:
Enter the database password for APEX_REST_PUBLIC_USER:(a password provided by you, write it down, you'll need later...)
Confirm password:
Dec 22, 2018 12:02:54 PM
INFO: reloaded pools: []
Installing Oracle REST Data Services version 18.3.0.r2701456
... Log file written to /Users/javiermugueta/ords_install_core_2018-12-22_120256_00476.log
... Verified database prerequisites
... Created Oracle REST Data Services schema
... Created Oracle REST Data Services proxy user
... Granted privileges to Oracle REST Data Services
... Created Oracle REST Data Services database objects
... Log file written to /Users/javiermugueta/ords_install_datamodel_2018-12-22_120538_00171.log
... Log file written to /Users/javiermugueta/ords_install_apex_2018-12-22_120551_00321.log
Completed installation for Oracle REST Data Services version 18.3.0.r2701456. Elapsed time: 00:03:08.81

That’s all!

Enjoy;-)

Deploy ORDS (Oracle Rest Data Services) to Kubernetes Cluster


ords

ORDS is a piece of software that acts as an intermediary between Oracle Database and the layers where business logic is implemented and/or consumed.

NOTE: If your database hasn’t been set up for ORDS just google for it or take a look at this post.

With ORDS you expose database objects as REST. ORDS can be started standalone or in an application server. Deploying ORDS in Kubernetes as stateless pods with several replicas provide an elastic layer of rest exposition, out of the database, with high availability and fault tolerant.

ordsarch.png

First of all, we need a container ready to run ORDS in standalone mode. For this particular case, we are utilising one already done, see yaml file mentioned in next steps.

kuberneteslogo

Let’s start creating a secret for storing the password for the user that makes de connection to the database:

kubectl create secret generic ordspassword --type=string --from-literal=password=[somepassword]
Now, take a look and download this yaml file here and make changes regarding the database connection service and floating IP (SCAN in the case of a RAC or host IP in case of a single instance).
        env:
        - name: DB_HOSTNAME
          value: "example:172.16.0.7"
        - name: DB_PORT
          value: "example:1521"
        - name: DB_SERVICENAME
          value: "example:jsonpdb.xxxxxx.yyyyyy.oraclevcn.com"
        - name: APEX_PUBLIC_USER_NAME
          value: "ORDS_PUBLIC_USER"
        - name: ORDS_PASS
          valueFrom:
After that, issue the following command:
kubectl apply -f my.yaml
That’s it, verify the service is available this way:
kubectl get svc
javiermugueta$ kubectl get svc

NAME TYPE CLUSTER-IP EXTERNAL-IP PORT(S) AGE

eciveci LoadBalancer 10.96.92.19 130.61.15.199 3000:31522/TCP 4d

kubernetes ClusterIP 10.96.0.1 <none> 443/TCP 15d

ordscontainer LoadBalancer 10.96.229.248 130.61.70.73 8080:31523/TCP 2d
Now check whatever URL that you may have enabled in the schema. For more info take a look here or here.
http://serviceip:serviceport/ords/hr/employees/
That’s all!
Enjoy 😉

Deploying an Oracle Database with Persistence in Oracle Kubernetes Engine


UPDATE: Follow this post for the deployment part, as it has been updated, the rest of the content is still valid, thanks.

Today we are deploying an Oracle database instance in a K8s cluster making them persistent so that data is not lost after container/pod restarts. Please follow this link to get the yaml file or simply execute the following:

kubectl apply -f https://raw.githubusercontent.com/javiermugueta/k8s-orcldb/master/orcldb.yaml
persistentvolumeclaim/data-pvc created
deployment.apps/orcldb created
service/oracledb created

After a while a new pod and service should be created, check it with:

kubectl get po
NAME READY STATUS RESTARTS AGE
orcldb-796ddfdd6f-tgfqf 1/1 Running 0 5m
kubectl get svc
NAME TYPE CLUSTER-IP EXTERNAL-IP PORT(S) AGE
oracledb LoadBalancer 10.96.9.195 130.61.66.33 1521:31521/TCP 5m

Write down the external IP of the service created and set up a new connection to the database with your preferred tool as follows (password is Oradoc_db1):

Once connected, issue the following:

create table t (c varchar2(10));
insert into t (c) values ('aaaaaaaaaa');
insert into t select * from t;
commit;
select count(*) from t;

Table T created.
1 row inserted.
1 row inserted.
Commit complete.
COUNT(*)
----------
2

Now delete the pod:

kubectl delete po orcldb-796ddfdd6f-tgfqf

You’ll notice that the connection to the database is lost until the pod recreates again (you can’t have more than one replica for this particular use case):

But after a while, the pod gets created again and if you reconnect to the database you’ll find your data in there:

That’s all folks!

Enjoy 🙂