Analyze Invoices with Oracle Analytics and AI Document Understanding

OCI Document Understanding is an AI service that enables developers to extract text, tables, and other key data from document files through APIs and command line interface tools.

In this blog I will show you how we can visualize invoices in oracle analytics using AI document understanding pre-trained model.

Pre-requisite:

1. Upload sample invoices in OCI bucket object storage.

2. Create a Oracle Analytics Could Instance in OCI.

Steps:

1. Create a CSV file:

Once we have all invoice images in bucket, we have to create a CSV file including your bucket URL.

something like below.

2. Register model in Oracle Analytics Cloud:

Go to Analytics home page and click on 3 dots on top right corner and select Register Model/Function and then select OCI Document Understanding Models

Once selected, create a new connection by clicking create connection button. I already have connection so I select that.

On next screen you have to select Model, I select Pretrained Document Key Value Extraction and select compartment, bucket (where we upload all invoices), document type (I select invoices) and give any name to Model.

3. Apply AI model to our images:

On Analytics home page , Click on Create button from top right corner and click Data Flow.

On this screen we have to choose the CSV file we created in Step 1 and click add.

You will see below screen

Now click on the + sign and click on Apply AI model and select the model we created in step 2 and click ok.

scroll down and under parameters select the column as Bucket URL.

Now again click on + sign and select Save data. Saved data should contain the image name and URLs as well as the key values text/number extracted.

Now after few mins you will see all the data. Data flow will load the images, analyze them with pre-trained AI model and extract key values from the images. In this example, the invoices.

Data flow will load all the information into dataset that we can use to explore and visualize.

4. Now Visualize the dataset:

From Oracle Analytics home page again click on create and click workbook

Click on new dataset generated by data flow in previous step and add to workbook.

Now from the Visualization, add the custom visualization plugin object to see all the images in your bucket.

On how to download and use custom visualization see my other blog custom plugin

Create a new table to show all the columns of your dataset or may be few columns whatever required.

something like below

I use the filter on image objects so when I click on any image it will show the data of that invoice on right side.

This is really cool! You can do many other things like to show only invoices where Invoice total is more than $1000 etc.

You can also store the dataset result in Autonomous database to do more things.

Hope you like the Blog!

How to Import Custom visualization in Oracle Analytics Cloud to see Images

 

In this Blog I will show you how to download and import Image Gallery Plugin into OCI Oracle Analytics Cloud. using this plugin we can see Images in OAC.

Steps:

1. Download the plugin from Oracle website.

Go to this URL  and search for image and download the Extension.

2. Import the Extension into Oracle Analytics

Go to Oracle Analytics home page and click on Console

Now click on Extensions

Click on Upload Extension

Once uploaded it will show into this page.

3. Check and use the new custom visualization extension

Now we can create a new workbook and we can see the new visualization.

Go to workbook, click on Visualization and scroll till end to see the new custom visualization.

This way we can download and import any visualization in Oracle analytics.

How to Upgrade OCI BaseDB (DBCS) from 19c to 23c

 

Recently there was a requirement to upgrade DBCS PDB from 19c to 23c. There might be other ways to do that but I found the best and easiest way is to create new 23c DBCS and then move 19c PDB using Refreshable clone method and then use Auto upgrade to upgrade it.

I will show you how to do that in this blog post.

Pre-requisite: I already have all the networking setup and have 2 DBCS, one 19c and one 23c.

Benefit of this approach :

1. We already have 23c DBCS with latest Grid/ASM software. so we just need to move the PDB to this DBCS and upgrade it. It will shorter the downtime.

2. Another thing is when we use refreshable clone method, there is no downtime when files copied over and rolls the copies of the data files forward with redo from source. Downtime only start at the time of upgrade.

3. Another benefit is rollback scenario, we already have our source 19c PDB, if anything wrong happen during the process you can just open and use the source PDB as rollback plan.

So let's start..

Source: 19c DBCS with CDB name DB122 and PDB name Db19c

Target: 23c DBCS with CDB name DB1229 and PDB name Db23c (This is existing PDB on 23c DBCS)

By Default every Oracle Home have the AutoUpgrade jar file. I copied the autoupgrade.jar from 23c DBCS home to 19c DBCS home.

To use auto upgrade I created a config file called upgrade.cfg, which I store on both DBCS systems.

 

[oracle@source admin]$ cat upgrade.cfg
global.autoupg_log_dir=/u01/app/oracle/cfgtoollogs/autoupgrade
global.keystore=/u01/app/oracle/cfgtoollogs/keystore
upg1.source_home=/u01/app/oracle/product/19.0.0.0/dbhome_1
upg1.target_home=/u01/app/oracle/product/23.0.0.0/dbhome_1
upg1.sid=DB122
upg1.pdbs=DB19C
upg1.target_cdb=DB1229
upg1.source_dblink.DB19C=CLONEPDB 600
upg1.target_pdb_copy_option.DB19C=file_name_convert=none
upg1.target_version=23
upg1.start_time=28/12/2023 22:00:00
[oracle@source admin]$

Where:

global.autoupg_log_dir = is the location where autoupgrade logs will collect

global.keystore = this is the autoupgrade keystore where we store TDE passwords for source and target PDBs

source and target homes where specify the source and target CDB homes

sid and PDB is the source CDB and source PDB respectively.

target_CDB is the target CDB name.

source_dblink has the name of db link (clonepdb) and rate at which redo transfer.

target_pdb_copy_option = I am using ASM and OMF

start_time : This is very important parameter to specify because this parameter decide when downtime starts. At this point, AutoUpgrade refreshes the PDB for the last time and then moves on with the upgrade.

start_time is optional , if you won't specify, it right away start upgrade after the refresh.

Now we have to create a dblink that we used in the above config file:

Connect to source DBCS PDB and create a user for Db link and grant privileges.

[oracle@source ~]$ . oraenv
ORACLE_SID = [DB122] ?
The Oracle base has been set to /u01/app/oracle
[oracle@source ~]$ echo $ORACLE_SID
DB122
[oracle@source ~]$ sqlplus / as sysdba

SQL*Plus: Release 19.0.0.0.0 - Production on Thu Dec 28 20:28:52 2023
Version 19.20.0.0.0

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


Connected to:
Oracle Database 19c EE High Perf Release 19.0.0.0.0 - Production
Version 19.20.0.0.0

SQL> show pdbs

    CON_ID CON_NAME                       OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
         2 PDB$SEED                       READ ONLY  NO
         3 DB19C                          READ WRITE NO
SQL> alter session set container= DB19C;

Session altered.

SQL> show pdbs

    CON_ID CON_NAME                       OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
         3 DB19C                          READ WRITE NO

SQL> create user test identified by XXXXXXXXXXXXXXX;

User created.

SQL> grant create session, create pluggable database, select_catalog_role to test;

Grant succeeded.

SQL> grant read on sys.enc$ to test;

Grant succeeded.

SQL>

Now connect to Target DBCS CDB and create a db link:

SQL> create database link clonepdb connect to test identified by XXXXXXXXXXX using 'DB19C';

Database link created.

SQL> select * from dual@clonepdb;

D
-
X

Now we can analyze and execute preupgrade fixups on source PDB

[oracle@source admin]$ java -jar autoupgrade.jar -config upgrade.cfg -mode analyze

and

[oracle@source admin]$ java -jar autoupgrade.jar -config upgrade.cfg -mode fixups

We will see below error when analyze and do prefixups

Error Message:The following checks have ERROR severity and no fixup is available or

the fixup failed to resolve the issue. Fix them manually before continuing:

DB19C TDE_PASSWORDS_REQUIRED

DB19C TARGET_CDB_AVAILABILITY

For now we can safely ignore these as we will fix these on target side.

On target DBCS:

Since our PDB is encrypted, we have to add source (db122) and target CDB(db1229) TDE password to Auto Upgrade keystore that we defined in config file.

[oracle@target admin]$ java -jar autoupgrade.jar -config upgrade.cfg -load_password
Processing config file ...

Starting AutoUpgrade Password Loader - Type help for available options
Creating new AutoUpgrade keystore - Password required
Enter password:
Enter password again:
AutoUpgrade keystore was successfully created

TDE> add DB122
Enter your secret/Password:
Re-enter your secret/Password:
TDE> add DB1229
Enter your secret/Password:
Re-enter your secret/Password:
TDE> save
Convert the AutoUpgrade keystore to auto-login [YES|NO] ? yes
TDE> exit

AutoUpgrade Password Loader finished - Exiting AutoUpgrade
[oracle@target admin]$

Now we are ready to start the Auto Upgrade in deploy mode.

[oracle@target admin]$ java -jar autoupgrade.jar -config upgrade.cfg -mode deploy
AutoUpgrade 23.3.230728 launched with default internal options
Processing config file ...
Loading AutoUpgrade keystore
AutoUpgrade keystore was successfully loaded
+--------------------------------+
| Starting AutoUpgrade execution |
+--------------------------------+
1 PDB(s) will be processed
Type 'help' to list console commands
upg> Copying remote database 'DB19C' as 'DB19C' for job 100
Remote database 'DB19C' created as PDB 'DB19C' for job 100
Job 100 completed
------------------- Final Summary --------------------
Number of databases            [ 1 ]

Jobs finished                  [1]
Jobs failed                    [0]
Jobs restored                  [0]
Jobs pending                   [0]



Please check the summary report at:
/u01/app/oracle/cfgtoollogs/autoupgrade/cfgtoollogs/upgrade/auto/status/status.html
/u01/app/oracle/cfgtoollogs/autoupgrade/cfgtoollogs/upgrade/auto/status/status.log
[oracle@target admin]$

So what this deploy command will do:

1. Copies the datafiles to target DBCS over the DB link.

2. Refresh the datafiles using source redo.

3. As per the start_time parameter in config file, it will do the final refresh and disconnects the PDB from source, downtime starts here.

4. Upgrade the PDB

Now let's check the target CDB for our new PDB

[oracle@target postchecks]$ sqlplus / as sysdba

SQL*Plus: Release 23.0.0.0.0 - Production on Fri Dec 29 04:58:32 2023
Version 23.3.0.23.09

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


Connected to:
Oracle Database 23c Enterprise Edition Release 23.0.0.0.0 - Production
Version 23.3.0.23.09

SQL> show pdbs

    CON_ID CON_NAME                       OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
         2 PDB$SEED                       READ ONLY  NO
         3 DB23C                          READ WRITE NO
         5 DB19C                          READ WRITE NO
SQL>

We can see that PDB is there in read write mode.

So Upgrade is done and now we have upgraded PDB on 23c DBCS :)

Last thing to check, do we have this PDB on OCI console in 23c DBCS CDB. let's check.

yes we have it, so all good!

ADB App Store - Oracle autonomous database App store and how to deploy application

 

The ADB App store: is an application launchpad for Oracle supported applications. Using ADB app store, tenants can install and manage the life cycle of these applications. 

Currently below applications can be deployed using ADB App store..

Ask Oracle: This app translates the user text input to SQL and run queries against the database.

Data Visual Studio: is a visualization tool for the analytics data.

OCI Cost & Usage Analytics: Monitor and manage OCI resource consumption.

OCI Log Analytics: give insights about the logs created in oci for services like VCN, load balancers etc. For now this app only supports logs for VCN service.

Workload Analytics & Reports (WAR): a visualization tool for AWR.

More applications are in pipeline....

Pre-requisite: Should have autonomous database deployed in OCI.

Installation:

Connect to ADB using SQL*Plus as ADMIN user and run below script to install ADB App store

SQL> set define off;

SQL> @adb_app_store_install.sql

You can find all scripts at this Oracle GitHub repository https://github.com/oracle-samples/adbs-tools 

Once App store installed successfully, run below script to get the ADB App store application URL .

SQL> @adb_app_store_get_URL.sql

We can use the URL from above output to login ADB APP store.

For credentials, We can use the default database user "ADB_APP_STORE" which exists in all Autonomous Databases.

We just need to unlock this user and set the new password to use it.

 

 

Now I can access the App store using above username/password and URL.

From here you can click on any application and you will see the details on how to install and what are the pre-requisites etc. For example I click on "OCI Cost & Usage Analytics" to see the details.

For this app I need to be in root compartment to set some policies. For now let me use "Workload Analytics & Reports (WAR)" app, as this does not need any Pre-requisite if you see below.

So I just click on Install button and its installed, in above screenshot its already installed, that's why you see uninstalled and open button.

Let me click on open button, you will get below screen.

I used my ADMIN user to login and see below screen.

Here we can analyze AWR dataset and investigate performance issues. I click on Analyze button.

We can do many things here to investigate issue and can also generate AWR reports.

Same way we can install other app from ADB App store and take benefits. 

Hope you like this blog!

How to automate start and stop of DBCS node using REST API in OCI

 

Recently we migrated databases from ODA (Oracle database appliance) to OCI DBCS for a client. And client asking to stop the dev DBCS node from 5 PM to 6 AM in the morning to save cost.

NOTE: For Virtual machine DB systems - Stopping a node stops billing for all OCPUs associated with that node, and billing resumes when you restart the node.

So this task is possible by console -> go to console and stop the DBCS node and again start in morning.

But we can not automate this from console. I found REST API is the best and easiest way to automate this task.

Please see my previous post on deployment of DBCS using REST API.

In my above post I specify the oci-curl script that you can copy. 

Here is the link again to copy script oci-curl

OCI-CURL will sign and encrypt your APIs so they are not sent in clear over the internet.

You basically need to change Tenancy OCID, USER OCID, KeyFingerprint and private key .pem file location in that script. You can easily get these from OCI console.

Pre-requisite:

1. I am assuming you already have VCN, subnet, security rules and compartment.

2. Running DBCS

3. OCI-CURL script with above changes as per your tenancy.

So what else we need

API endpoint for database: I used  database.ca-toronto-1.oraclecloud.com , if your cloud database is located somewhere else you can use that. full list here .. API Endpoints

DB Node Action command: POST /20160918/dbNodes/{dbNodeId}

How it works:

I created a very small shell script to stop DBCS node.

$ cat stop_dbsystem.sh

#!/bin/bash

. ./oci-curl.sh

oci-curl database.ca-toronto-1.oraclecloud.com POST ./empty.json /20160918/dbNodes/<dbnodeid>?action=stop

where 

oci-curl.sh : will load and source the function with our tenant data.

database.ca-toronto-1.oraclecloud.com : API endpoint that listens for DB service from Toronto region. It might be different for your region is your region is not Toronto.

POST : API Request

./empty.json : This is the empty file I used for start and stop dbcs node, because we have to use any config json file for POST API request. Here we are not changing anything like OCPU etc. that's why its empty.

/20160918/dbNodes/<dbnodeid>?action=stop : This is basically the command we have to use for start/stop dbcs node. The first number in this command is the API version. and dbnodeid is OCID of dbcs node we have to copy from console to use here.

After that just make this file executable.

chmod +x stop_dbsystem.sh

After running the above script I saw my DBCS node start stopping. here is the output response.

Once DBCS node stopped.

I created another small script to start the DBCS node.

$ cat start_dbsystem.sh

#!/bin/bash

. ./oci-curl.sh

oci-curl database.ca-toronto-1.oraclecloud.com POST ./empty.json /20160918/dbNodes/<dbnodeid>?action=start

and then run this script to start dbcs node, here is the output response.

We can see dbnode starting via oci console.

Now as per client requirement, we can schedule these stop and start script via crontab.

I hope you like the blog!

How to use Document Understanding AI service in Process Automation

 Info:

Recently, Oracle added new Document Understanding feature with OCI Process Automation.

In Process Automation forms, intelligent document processing is implemented with the Document Understanding control that uses out of the box pretrained AI models from OCI Document Understanding AI service to automatically detect, classify and extract texts and objects from uploaded documents.

Now you can upload your document like passport or driver license and this service automatically extract data. In real life scenarios, we used this service where we have to show our passport, driver license or any other doc.

Like Hotel check-in, rental car, hospital, visa service etc.

Another example, I still remember during Covid period , we have to use ArriveCAN app to enter Canada which scan our passport to extract all information.

So let's test this in our process automation forms. In my previous post I created a business process of "Time Off Request application". You can see that BLOG here.

This is not the best case to use document understanding as this is just time off request, but this is just to show you how we can use this service with our business process.

So let me open the web form which I developed in my last blog, and you can see on the right below there is AI service Document Understanding.

Now let's drag and drop the document understanding to our web form canvas.

Select the control and edit its properties on left side in General tab, I label it "Upload Document"

As you can see it only accept below file types as of now. Oracle might add more types later.

Now click on the Document tab in properties next to General tab. Here you can see only these 2 Document types supported: Passport or Driver license.

I select Passport. As soon as I select passport as document type, it will create the data object: passport with related definitions such as firstname, lastname, country and so on.

The data object and data definitions are displayed under the Data section.

Now select the Generate Control property to automatically generate controls for the created data definitions.

Under messages section if we expend we will see all the default messages that will be displayed, if required we can edit those messages.

Now there is a styling tab, I am not doing any changes there.

Now you can click on Preview button top to just see how it will look like.

Now let's activate this from the top Activate button and click on Override. it will deactivate old version and create new snapshot.

Now click on Test in Workspace

click on the Submit Time off Request and the form is open for employee to fill up information.

Now the fun part will start, I fill all the information and then I have my passport copy on my laptop in .PNG format which I drag and drop in form and within few seconds you will see all information automatically fill-up in the form from my passport.

you will also see this message.

Now we can submit the request for Team lead approval. Now the process is same as I showed in my last Blog. This One I just wanted to test this new cool feature with process Automation.

Hope you like the Blog!