How to use SQLcl to connect to Oracle Cloud ATP databases

SQLcl (short for: SQL command-line) is a relatively new light-weight SQL command-line tool from Oracle, similar to the much older SQL*Plus tool, but easier to install and with some nice new features. In this blog post I will explain how you can use it to connect to Oracle ATP cloud databases.

Step 1: download and install SQLcl

First, start by downloading and installing the SQLcl software. Installation is as simple as extracting the ZIP file to a new folder (a bit similar to the “installation” of SQL Developer). SQLcl is a Java-based tool, so you also need to have at least version 8 of the Java Runtime Environment (JRE) installed.

Once installed, you can easily test it by going into the “bin” folder where you extracted the software and then run the following command:

sql /nolog

Example from my computer (I’m on Windows 10):

c:\>cd "Program Files (x86)"/sqlcl/bin

c:\Program Files (x86)\sqlcl\bin>sql /nolog

SQLcl: Release 20.2 Production on Tue Jul 21 12:10:01 2020

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


SQL>

Step 2: download Oracle ATP client credentials (Oracle Wallet)

To be able to connect from your client computer to an Oracle Cloud database you first need to download the client credentials (the so called “Oracle Wallet”) from the Oracle Cloud portal site. Note that if you already have a working SQL Developer connection, you probably already have this wallet file somewhere on your system.

Log in to the Oracle Cloud portal and click on the name of the database you want to connect to. Now click on the tab “DB Connection”. The following pop-up window will appear:

You now have the choice between “Instance Wallet” (a wallet for the selected database instance) and “Region Wallet” (a wallet for all the databases in the current region). Select “Instance Wallet” and click on “Download Wallet“.

You are now asked to create a complex password for your wallet:

Download the ZIP file and put it in a local folder on your client computer.

Step 3: connect from SQLcl to Oracle Cloud ATP

To connect with SQLcl to your Oracle Cloud ATP database, use the following steps:

• Go into the SQLcl “bin” folder and run the following command:
  • sql /nolog
• Configure the SQLcl session to use the previously downloaded Oracle Wallet:
  • set cloudconfig C:\Temp\Wallet_MATTHIASDB.zip
• Connect to the Cloud database using the required schema and service name (note that the service names are listed in the tnsnames.ora file in the zipped Oracle Wallet file)
  • connect HR@matthiasdb_medium

Example from my computer:

c:\Program Files (x86)\sqlcl\bin>sql /nolog

SQLcl: Release 20.2 Production on Tue Jul 21 12:20:35 2020

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


SQL> version

Oracle SQLDeveloper Command-Line (SQLcl) version: 20.2.0.0 build: 20.2.0.174.1557
SQL> set cloudconfig C:\Temp\Wallet_MATTHIASDB.zip
Operation is successfully completed.
Operation is successfully completed.

Using temp directory:C:\Users\Matthias\AppData\Local\Temp\oracle_cloud_config3396158891494649054
SQL> connect HR@matthiasdb_medium
Password? (**********?)

Connected.
SQL>

Now try to run some SQLcl commands!

SQL> set sqlformat ansiconsole

SQL> desc employees

Name Null? Type
_________________ ___________ _______________
EMPLOYEE_ID NOT NULL NUMBER(6)
FIRST_NAME VARCHAR2(20)
LAST_NAME NOT NULL VARCHAR2(25)
EMAIL NOT NULL VARCHAR2(25)
PHONE_NUMBER VARCHAR2(20)
HIRE_DATE NOT NULL DATE
JOB_ID NOT NULL VARCHAR2(10)
SALARY NUMBER(8,2)
COMMISSION_PCT NUMBER(2,2)
MANAGER_ID NUMBER(6)
DEPARTMENT_ID NUMBER(4)

SQL> info employees
TABLE: EMPLOYEES
LAST ANALYZED:2020-07-08 22:01:07.0
ROWS :107
SAMPLE SIZE :107
INMEMORY :DISABLED
COMMENTS :employees table. Contains 107 rows. References with departments,
jobs, job_history tables. Contains a self reference.

Type “help” at the command-line if you need help on a specific command. Type “version” to see the version of SQLcl you are using. Note that you can also use the keyboard arrows to scroll between previously executed commands (something that is not possible with SQL*Plus).

That’s it! You can now use SQLcl to connect to Oracle databases in the Cloud.

HTH

Matthias

Installation of HR sample schema on free Oracle ATP cloud database

In this blog post I will show how you can install the sample HR (Human Resource) schema on a Oracle ATP (Autonomous Transaction Processing) database in the Oracle Cloud.

Prerequisites:

• Oracle SQL Developer with connectivity to a Oracle ATP database (please see one of my previous blog posts if you need help with this)

Step 1: download the Sample Schemas scripts from GitHub

Download and extract the Oracle Database Sample Schemas source code zip file from https://github.com/oracle/db-sample-schemas/releases/tag/v19.2

Step 2: create the HR database user

Connect with SQL Developer to your Oracle ATP database using the ADMIN user, and execute the following scripts to create the HR user with the necessary grants:

CREATE USER hr IDENTIFIED BY "&pass";

ALTER USER hr DEFAULT TABLESPACE &tbs
QUOTA UNLIMITED ON &tbs;

ALTER USER hr TEMPORARY TABLESPACE &ttbs;

GRANT CREATE SESSION, CREATE VIEW, ALTER SESSION, CREATE SEQUENCE TO hr;
GRANT CREATE SYNONYM, CREATE DATABASE LINK, RESOURCE , UNLIMITED TABLESPACE TO hr;

GRANT execute ON sys.dbms_stats TO hr;

&pass = Password for the HR user (must be complex: more than 12 bytes long, contain upper and lower case characters and not contain the username)

&tbs = default tablespace for the HR user (for example: DATA)

&ttbs = temporary tablespace for the HR user (for example: TEMP)

You should see the following script output:

User HR created.

old:ALTER USER hr DEFAULT TABLESPACE &tbs
QUOTA UNLIMITED ON &tbs
new:ALTER USER hr DEFAULT TABLESPACE DATA
QUOTA UNLIMITED ON DATA

User HR altered.

old:ALTER USER hr TEMPORARY TABLESPACE &ttbs
new:ALTER USER hr TEMPORARY TABLESPACE TEMP

User HR altered.


Grant succeeded.


Grant succeeded.


Grant succeeded.

Step 3: create the HR schema objects

Use SQL Developer to connect to your Oracle ATP database with the newly created HR user (see step 2), and load and execute the following sql files in this order as scripts (see step 1):

• db-sample-schemas-19.2\human_resources\hr_cre.sql (for creating the objects)
• db-sample-schemas-19.2\human_resources\hr_popul.sql (for populating the tables)
• db-sample-schemas-19.2\human_resources\hr_idx.sql (for creating the indexes)
• db-sample-schemas-19.2\human_resources\hr_code.sql (for creating the procedures)
• db-sample-schemas-19.2\human_resources\hr_comnt.sql (for creating the comments)
• db-sample-schemas-19.2\human_resources\hr_analz.sql (for gathering the schema statistics)

That’s it! You should now have a fully functional HR schema in your Oracle ATP database.

HTH

Matthias

 

 

SQL Developer 20.2: download, installation and connectivity to Oracle ATP cloud databases

SQL Developer 20.2, the latest version of Oracle’s free database management & SQL development tool, has been released recently. This version succeeds SQL Developer version 19.4 (smart readers may notice that Oracle has skipped version 20.1).

The following new features are included with this new version:

• Debugger – option to use DBMS_DEBUG for database debugging
• Load options include SET LOADFORMAT and SET LOADMETHOD for more flexibility on loading table data
• ORDS installer supports:
  • REST SQL and SQL Developer Web configuration
  • Custom JDBC urls and TNS
  • Allows non-SYSDBA user to install/upgrade, repair and uninstall ORDS
• SQLcl Liquibase new commands: ROLLBACKSQL, DIFF, DBDOC, VERSION
• Liquibase changeSet IDs are now generated as a hash of the content
• Explain Plan/Autotrace Search
• Include Orakafka ZIP in all distributions
• Java Home updated to 8u221
• Oracle NoSQL plugin updated to version 19.3.12
• Database Copy added ‘Ignore Storage’ to avoid tablespace dependencies
• SQL Editor Code template options panel moved to code editor page
• Code formatter new setting for number of commas to allow before a linebreak on PL/SQL call
• Added SET CODESCAN to look for SQL Injection vulnerabilities
• TABLE IMPORT enhanced to ignore virtual columns

Let’s see if we can easily install it and create a connection to a Oracle ATP database in the cloud.

Step 1: download & install

SQL Developer 20.2 can be downloaded here: https://www.oracle.com/tools/downloads/sqldev-downloads.html

Note that there are multiple versions available, please pick the correct version for your operating system (I’m on Windows 64-bit and usually take the version with JDK 8 included). Before you can download the software you need to agree to the Oracle License Agreement (beware that some countries have limited rights to use software from Oracle) and log in with an Oracle account:

Now before you extract the downloaded zip file, please compare the MD5 and SHA-1 hash of the file with the hash published on the download page. This to make sure the software is genuine. On Windows you can do this with a tool called “File Checksum Integrity Verifier” (FCIV). You can download it here: https://support.microsoft.com/en-us/help/889768/how-to-compute-the-md5-or-sha-1-cryptographic-hash-values-for-a-file

Most extraction utilities also have built-in hash comparison tools.

Once you have verified that the two hashes are correct, extract the zip file into a new folder.

To start SQL Developer open the folder “sqldeveloper” and double-click on “sqldeveloper.exe“. If you had a previous installation of SQL Developer, it will ask you to import preferences (database connections and settings) from a previous version. Click No if you want to start with a clean installation.

Step 2: connect to Oracle ATP cloud database

To connect from a local installation of SQL Developer to a Oracle ATP cloud database, you first need to download the client credentials (Oracle Wallet) from the Oracle Cloud portal site.

Log in to the Oracle Cloud portal and click on the name of the database you want to connect to. Now click on the tab “DB Connection”. The following pop-up window will appear:

You now have the choice between “Instance Wallet” (a wallet for the selected database instance) and “Region Wallet” (a wallet for all the databases in the current region). Select “Instance Wallet” and click on “Download Wallet“.

You are now asked to create a complex password for your wallet:

When you have downloaded the zipped Wallet file, go to SQL Developer and click on the green “+” sign > New Database Connection. In the database connection window fill in the required information (connection name, username, password) and select “Cloud Wallet” as Connection Type. Browse to the Instance Wallet zip file that you downloaded earlier. You will now see that the “Service” drop down list will have some predefined database service names (this is from the documentation):

• tpurgent: The highest priority application connection service for time critical transaction processing operations. This connection service supports manual parallelism.
• tp: A typical application connection service for transaction processing operations. This connection service does not run with parallelism.
• high: A high priority application connection service for reporting and batch operations. All operations run in parallel and are subject to queuing.
• medium: A typical application connection service for reporting and batch operations. All operations run in parallel and are subject to queuing. Using this service the degree of parallelism is limited to four (4).
• low: A lowest priority application connection service for reporting or batch processing operations. This connection service does not run with parallelism.

Let’s take the “medium” service name and click on Test to test the connection.

If the connection test was successful, click on Save to save the connection – it will appear in the Connections window under Oracle Connections. Click on the plus sign to open the connection to your database. Now try to run some queries!

This concludes my blog post where I showed how you can install the latest version of SQL Developer and connect to a Oracle ATP database in the cloud.

HTH

Matthias

 

 

 

 

 

How to use Python to parse RESTful web service data from Oracle ATP

In this blog post I will show how you can create and configure a RESTful web service on Oracle ATP (Autonomous Transaction Processing) and use Python to read and parse the JSON data from this web service.

Prerequisites:

• access to Oracle Cloud and Oracle ATP database including APEX (I’m using an “always free” ATP database version 19c, with APEX v19.2)
• an installation of Python 3.6 or higher (I’m using Visual Studio Code with the Python 3.8.2 extension)

Step 1: create a new APEX workspace and install the Sample REST Services application

• log in to Oracle Cloud
• click on the name of your ATP database:

• click on Tools > Open APEX
• click on Administration Services and log in with the ADMIN user:

• click on Create Workspace:
  • Database user = REST_EXAMPLE
  • Password = complex password
  • Workspace Name = REST_EXAMPLE

• Now log out and log in to the REST_EXAMPLE workspace using the REST_EXAMPLE user
• Click on App Builder > Install a Productivity or Sample App
• Search for the app “Sample REST Services”, click on the name and then “Install App”

• click “Next” and “Install App”
• Click on the Run icon and log in with the user REST_EXAMPLE to test the sample application:

• click on “Test Service Endpoints” and “Test Endpoints”. If all goes well, they should be green:

• now click on Home > SQL Workshop > RESTful Services
• click on “Register schema with ORDS”:

• in the modal window leave the default settings and click on Save Schema Attributes. This will REST-enable the schema “REST_EXAMPLE” and also install the sample module oracle.example.hr:

• under RESTful Data Services click on the method “GET” under the resource template “employees/”:

• now click on the “Copy to clipboard” link next to the Full URL and paste this URL in a new browser tab. Verify that you can see or download the list of employees in JSON format in your web browser.

Step 2: protect the RESTful web service

The RESTful web service created in step 1 should work fine now, but none of the resource templates is protected, which means that anyone who knows the URL can access the service now, and this is not what we want. So, let’s create a new user in APEX to protect our service.

• click on Administration > Manage Users and Groups:

• click on Create User:
  • Username = REST_USER
  • Email address = fill in a valid email address
  • Password = fill in a complex password
  • Require Change of Password on First Use = No
  • Group Assignments = RESTful Services
  • leave the rest default
• Now click on SQL Workshop > RESTful Services
• Click on Privileges > Create Privilege
  • Name = oracle.example.hr.privilege
  • Title = oracle.example.hr.privilege
  • Roles = RESTful Services
  • Protected Modules = oracle.example.hr

• when you now click on the “oracle.example.hr” module you will see that the resource templates are properly protected now:

• if you now try one of the URL’s again, you should see a 401 Unauthorized warning:

• click on “Sign In” with the REST_USER account (created earlier in Step 2) to verify that you can download the web service data.

Step 3: access the RESTful web service from Python

Now that we have our web services in place and the modules are protected, let’s see if we can download the list of employees using a Python script.

One remark regarding the web service protection: for this blog post I used Basic Authentication – this is only considered secure (to some degree) when used together with SSL/TLS. A much better and secure way would be to use OAuth2, but this is out of the scope for my example.

Here is the piece of Python code that worked for me:

import urllib.request
import json
import sys
import base64
import getpass

def printEmployees(data):
    # Use the json module to load the string data into a dictionary
    theJSON = json.loads(data)
    j = 0

    try:
        for i in theJSON["items"]:
            print (i["ename"])
            j += 1
        print ('------------------')
        print (f'{j} employees found\n')
    except:
        print(f'An error occurred: {sys.exc_info()}')

def main():
    # define a variable to hold the source URL
    urlData = "https://<xyz>.oraclecloudapps.com/ords/rest_example/hr/employees/"

    username = input('Username: ')
    password = getpass.getpass('Password: ')

    req = urllib.request.Request(urlData)

    credentials = ('%s:%s' % (username, password))
    encoded_credentials = base64.b64encode(credentials.encode('ascii'))
    req.add_header('Authorization', 'Basic %s' % encoded_credentials.decode("ascii")) 

    # open the URL and read the data
    try:
        webUrl = urllib.request.urlopen(req)
        if (webUrl.getcode() == 200):
            data = webUrl.read()
            # print out the list of employees
            printEmployees(data)
        else:
            print ('Received an error from server, cannot retrieve results ' + str(webUrl.getcode()))
    except:
        print(f'An error occurred: {sys.exc_info()}')

if __name__ == "__main__": main()

When you run this code, it will ask for the username (= REST_USER) and password, add Basic Authentication credentials to the HTTP header, parse the JSON output and print the employee names (“ename”) including a count of the number of employees.

Note that you will need to replace the “urlData” variable with the correct URL for your environment (for security reasons I truncated the URL here).

Let’s try to run the script on my local Windows desktop:

C:\Users\Matthias\Desktop\>py matthias_jsondata_emp_atp.py
Username: REST_USER
Password:
SMITH
ALLEN
WARD
JONES
MARTIN
BLAKE
CLARK
------------------
7 employees found

Victory! :-)

HTH

Matthias

 

 

Deploying Oracle Database XE 18c as container on Play With Docker (PWD)

Play with Docker (PWD) is a free Docker environment that you can use for up to 4 hours – you only need a web browser and a Docker account! It uses the “Docker-in-Docker” technology.

Now let’s see if we can use this environment for the installation of Oracle XE 18c…

First, you will need a (free) Docker account – go to https://www.docker.com/ to create one if you have not done this already.

Next, go to https://labs.play-with-docker.com/, log in with your Docker account and click on Start.

Once you are logged in click on the “wrench” icon and click on “1 Manager and 1 Worker” to create a manager node and a worker node – this should only take a couple of seconds.

We are now going to use the Manager node to deploy Oracle XE 18c as container.

In the “manager1” command window, enter the following command to clone the Oracle Docker Git project:

$ git clone https://github.com/oracle/docker-images.git

You will now see a folder with the name “docker-images”. Enter this folder and execute the build script for Oracle XE 18c (version = 18.4.0, Express Edition = x, see here for more information):

$ cd docker-images/OracleDatabase/SingleInstance/dockerfiles
$ ./buildDockerImage.sh -v 18.4.0 -x

The download and installation of the necessary software libraries and packages will now start, and this can take a while (somewhere between 15 and 30 minutes), because it will also download the 5.2 GB RPM software binaries file for Oracle XE 18c.

After the installation you should see something like this:

Let’s check the creation of the docker image with the following command:

$ docker images
REPOSITORY TAG IMAGE ID CREATED SIZE
oracle/database 18.4.0-xe 67a09c069d53 2 minutes ago 5.87GB

We can now use this Docker image to start a “containerised” Oracle XE 18c database on the “manager1” host, but before we do this, let’s create some local folders so that the container mount points can be mapped to local host storage. You also need to change the ownership of the local host folders to UID 54321, which is the UID of the “oracle” user within the container:

$ mkdir -p /home/xedb1/oradata
$ mkdir -p /home/xedb1/scripts/setup
$ mkdir -p /home/xedb1/scripts/startup
$ chown 54321:54321 /home/xedb1/oradata
$ chown 54321:54321 /home/xedb1/
$ chown 54321:54321 /home/xedb1/oradata
$ chown 54321:54321 /home/xedb1/scripts/setup
$ chown 54321:54321 /home/xedb1/scripts/startup

Now that the local folders have been created and the permissions have been set, let’s start a container using the “docker run” command. Please note that you need to replace <password> with a proper password in the below command:

docker run --name xedb1 \
-d \
-p 51521:1521 \
-p 55500:5500 \
-e ORACLE_PWD=<password> \
-e ORACLE_CHARACTERSET=AL32UTF8 \
-v /home/xedb1/oradata:/opt/oracle/oradata \
-v /home/xedb1/scripts/setup:/opt/oracle/scripts/setup \
-v /home/xedb1/scripts/startup:/opt/oracle/scripts/startup \
oracle/database:18.4.0-xe

After a couple of minutes, check the status of the container with the command “docker container ls -a”:

$ docker container ls -a
CONTAINER ID IMAGE COMMAND CREATED STATUS PORTS NAMES
7b95cd801cdc oracle/database:18.4.0-xe "/bin/sh -c 'exec $O…" 22 minutes ago Up About a minute (healthy) 0.0.0.0:51521->1521/tcp, 0.0.0.0:55500->5500/tcp xedb1

You should now be able to connect to the container database “xedb1” using the following commands:

$ docker exec -it --user=oracle xedb1 bash
[oracle@7b95cd801cdc /]$ . oraenv
ORACLE_SID = [XE] ? 
The Oracle base remains unchanged with value /opt/oracle
[oracle@7b95cd801cdc /]$ sqlplus /nolog

SQL*Plus: Release 18.0.0.0.0 - Production on Tue Jun 16 16:15:15 2020
Version 18.4.0.0.0

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

SQL> conn / as sysdba
Connected.

Victory! Finally, let’s try to connect to the pluggable database XEPDB1 and create a new user account:

SQL> alter session set container=XEPDB1;

Session altered.

SQL> create user Matthias identified by Matthias123;

User created.

Nice!

This concludes my blog post, where I have shown how you can use the free Play with Docker environment to deploy Oracle XE 18c as docker container.

HTH

Matthias

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

How to upgrade APEX from version 19.2 to version 20.1 on Oracle Database 18c XE

APEX 20.1 is out! Oracle released it yesterday, and you can download it here. Check the Release Notes, the Installation Guide and the overview of Known Issues.

Some of the new features in APEX 20.1:

• Redwood UI: a new user interface
• Oracle JET and Third Party Library Updates: upgrades to JET 8.0.0, jQuery 3.4.1, jQuery UI 1.12.1 and FullCalendar 3.10.0
• New JavaScript APIs: apex.locale, apex.da.cancel, apex.theme.mq
• Universal Theme: new link target attribute
• New Interactive Grid Editing with Control Breaks
• Friendly URL Syntax: APEX applications now support two types of URL syntax: Friendly URL Syntax and legacy f?p Syntax.
• Mega Menu Navigation: Mega Menu Navigation renders application navigation as a collapsible floating panel that displays all navigation items at once
• Remote Application Deployment: deploy an application to remote APEX instances using REST Enabled SQL references
• Split Export Files into Separate Scripts
• Interactive Grid Support for URL Filtering
• Application Backups: APEX automatically backs up modified applications as a part of daily maintenance

This looks promising! Something to note regarding web browser support:

Support for Internet Explorer (IE) 11 is deprecated.

Starting with release 20.2, only the current and prior major release of Microsoft Edge along with Google Chrome, Mozilla Firefox, Apple Safari will be supported.

Let’s see if I can easily upgrade my Oracle 18c XE installation from APEX 19.2 to APEX 20.1.

This is my setup:

• Oracle 18c XE on CentOS Linux 7
• APEX 19.2 installed in pluggable database XEPDB1
• ORDS 19.4 running as standalone installation using port 8443

Before we start with the upgrade, let’s check the installation requirements for APEX 20.1:

• MEMORY_TARGET: at least 300 MB: ok
• ORDS: at least version 19.1: ok
• free disk space: at least 1 GB: ok

Looks like the requirements are ok, so we can continue with the upgrade.

I started by downloading the software zip file (I choose the “All Languages” version), copying it to my Linux server (don’t unzip it before copying, or it will take ages) and unzipping it into a new directory (I put it in a new directory “/home/oracle/apex201”). It’s important to keep the installation files somewhere because you might need some of the SQL scripts or files later on.

My current installation of APEX 19.2 is installed in the pluggable database XEPDB1 and is using tablespace SYSAUX. During the upgrade, new database objects will be created in a new schema and the application metadata will be migrated to the new release. So the schema used for APEX 19.2 (APEX_190200) will remain untouched.

Let’s first create a new tablespace with name “APEX_201” in database XEPDB1. This tablespace will be used to store the objects for the APEX 20.1 installation:

$ sqlplus /nolog

SQL*Plus: Release 18.0.0.0.0 - Production on Fri Apr 24 11:20:54 2020
Version 18.4.0.0.0

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

SQL> conn / as sysdba
Connected.
SQL> alter session set container=XEPDB1;

Session altered.

SQL> CREATE TABLESPACE APEX_201 DATAFILE '/opt/oracle/oradata/XE/XEPDB1/apex201_01.dbf' SIZE 100M AUTOEXTEND ON NEXT 100M;

Tablespace created.

Now before we continue with the upgrade, we need to shut down ORDS so that no one can access the APEX environment during the upgrade. I do this by running the kill command on the ORDS process ID:

$ ps -ef|grep ords
oracle 8984 6599 0 11:30 pts/1 00:00:00 grep --color=auto ords
oracle 12275 1 0 Apr17 ? 00:21:14 java -Duser.timezone=UTC -Dorg.eclipse.jetty.server.Request.maxFormContentSize=20971520 -jar ords.war standalone
$ kill 12275

Now that the new tablespace is created and ORDS has been stopped, let’s run the upgrade process. This is done by executing the “apexins.sql” command from the unzipped APEX 20.1 installation folder. This command requires four arguments:

• the tablespace name for the APEX application user (in our case: APEX_201)
• the tablespace name for the APEX files user (in our case: APEX_201)
• the name of the temporary tablespace (in our case: TEMP)
• the name of the virtual directory for APEX images (in most cases, this will be /i/)

Let’s run the upgrade command:

$ cd /home/oracle/apex201/apex/
$ sqlplus /nolog

SQL*Plus: Release 18.0.0.0.0 - Production on Fri Apr 24 11:40:45 2020
Version 18.4.0.0.0

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

SQL> conn / as sysdba
Connected.
SQL> alter session set container=XEPDB1;

Session altered.

SQL> @apexins.sql APEX_201 APEX_201 TEMP /i/

You will now see a lot of output. After a while (it took around 12 minutes on my virtual machine), you should see the following message:

Thank you for installing Oracle Application Express 20.1.0.00.13

Oracle Application Express is installed in the APEX_200100 schema.

The structure of the link to the Application Express administration services is as follows:
http://host:port/pls/apex/apex_admin (Oracle HTTP Server with mod_plsql)
http://host:port/apex/apex_admin (Oracle XML DB HTTP listener with the embedded PL/SQL gateway)
http://host:port/apex/apex_admin (Oracle REST Data Services)

The structure of the link to the Application Express development interface is as follows:
http://host:port/pls/apex (Oracle HTTP Server with mod_plsql)
http://host:port/apex (Oracle XML DB HTTP listener with the embedded PL/SQL gateway)
http://host:port/apex (Oracle REST Data Services)

timing for: Phase 3 (Switch)
Elapsed: 00:00:52.79
timing for: Complete Installation
Elapsed: 00:12:25.64

It looks like the upgrade finished without errors! You can find the complete installation log file in the folder from where you executed the “apexins.sql” command.

If you forgot or want to change the password of the APEX Instance Administrator account, now’s the time to change it. You can do this by running the “apxchpwd.sql” file that can be found in the same folder as the “apexins.sql” script. For an upgrade this is not required, during the upgrade the password of the previous Instance Administrator account will be preserved.

The last thing that we need to do now is to copy the modified images and static files from the unzipped APEX 20.1 installation folder to the /i/ folder that is defined in ORDS (in my case, this is /home/oracle/ords194/images). I always take a backup copy of the old images folder first:

$ cd /home/oracle/ords194
$ cp -Rf images images_192
$ cp -Rf /home/oracle/apex201/apex/images/* /home/oracle/ords194/images/

We can now restart ORDS and see if we can connect to our upgraded APEX 20.1 instance!

Browsing to https://servername:8443/i/apex_version.txt should display the following message:

Application Express Version: 20.1

This is how the new Administration interface looks like:

This is the dark mode:

And this is the new development interface in dark mode:

Let’s start testing! I already discovered two issues:

• dark mode is not “sticky” between pages in the Administration interface
• file downloads from an Interactive Grid (PDF/CSV) were being blocked by the  pop-up blocker in Google Chrome

 

HTH

Matthias

 

 

 

 

 

 

 

 

 

 

 

How to verify the installation of APEX Patch Set Bundles on Always Free Oracle ATP databases

I recently blogged about Oracle’s Always Free ATP cloud database which includes APEX (currently version 19.2) out of the box.

Between two minor versions of APEX Oracle also releases evolving Patch Set Bundles which contain fixes for bugs discovered after the release (a bit comparable with Microsoft patches). When you have a valid Oracle support contract you can download these Patch Set Bundles and apply them to your own APEX installation. The current Patch Set Bundle for APEX 19.2 has number 30392181 and contains about 40 bug fixes.

Now I was wondering if Patch Set Bundle 30392181 is also applied to the Always Free Oracle ATP databases, as on these databases there is no possibility to apply these patches yourself.

To verify this, I connected to my database using SQL Developer Web and user ADMIN, and I executed the following query (this is documented in the Patch Set README.txt file):

select APEX_INSTANCE_ADMIN.GET_PARAMETER('APEX_19_2_0_PATCH_30392181') from dual;

I received the following output:

ORA-20987: APEX - Instance parameter not found - Contact your application administrator.
ORA-06512: at "APEX_190200.WWV_FLOW_ERROR", line 1126 ORA-06512: at "APEX_190200.WWV_FLOW_ERROR", line 1493 ORA-06512: at "APEX_190200.WWV_FLOW_INSTANCE_ADMIN", line 207 
ORA-06512: at "APEX_190200.WWV_FLOW_INSTANCE_ADMIN", line 393

First I thought that this error message meant that the Patch Set Bundle has not been applied (yet) to my database.

However, during today’s APEX@Home conference I received the following workaround:

select * from apex_instance_parameters where name = 'APEX_19_2_0_PATCH_30392181';

This query worked fine! I now have the following output:

Great! This means that Patch Set Bundle 30392181 has been applied to my Always Free Oracle ATP database on the 17th of March! This also means that something which would normally only be available with a paid support contract, is also free :-)

HTH

Matthias

Oracle Database 19c now available on Oracle Autonomous Database

Oracle recently announced that Oracle Database version 19c is now available on its Oracle Autonomous Database cloud infrastructure.

Oracle Database 19c is the latest version of the Oracle Database 12.2 “family” and it has Premier Support until March 2023. You can see a list of new features here: https://docs.oracle.com/en/database/oracle/oracle-database/19/newft/new-features.html#GUID-5490FE65-562B-49DC-9246-661592C630F9.

As explained here, there are several ways to upgrade your Autonomous cloud databases from 18c to 19c:

• Manual Upgrade by Performing a Full Clone
• Manual Upgrade by Cloning from a Backup
• Automatic Upgrade to Oracle Database 19c

The manual upgrade process is currently only available for users of paid database instances. For users of an Always Free Autonomous Database (ATP or ADW), they will need to wait till their databases get upgraded automatically after September 2020. This will happen after an announcement sent out by Oracle. According to Oracle, the upgrade process should not take longer than 10 minutes.

 

Matthias

 

How to request an (always free!) Oracle APEX Cloud Database

Last year Oracle released its “Oracle Cloud Free Tier” program. This means that you can get your own Oracle cloud database for free – and what’s more, it’s *always* free, so the offer will not expire after a year and you are charged nothing except if you start using one of the paid options. In this blog post I will explain how you can request a free Oracle cloud database and install APEX on top of it. Please note that you need to enter a valid credit card during the sign-in process (a very small amount will be withdrawn and refunded, but nothing will be charged as long as you stay within the capacity limits of the free program).

At the time of writing of this blog post, the following is included in the free cloud offering:

• Databases
  • Autonomous Transaction Processing or Autonomous Data Warehouse. 2 databases total, each with 1 OCPU and 20 GB storage.
• Storage
  • 2 Block Volumes, 100 GB total. 10 GB Object Storage. 10 GB Archive Storage.
• Compute
  • 2 virtual machines with 1/8 OCPU and 1 GB memory each.
• Additional Services
  • Load Balancer: 1 instance, 10 Mbps bandwidth. Monitoring: 500 million ingestion datapoints, 1 billion retrieval datapoints. Notifications: 1 million sent through HTTPS per month, 1,000 sent through email per month. Outbound Data Transfer: 10 TB per month.

New accounts also have access to a 30-day trial period for an expanded set of services.

Step 1: Oracle Cloud Account Signup

• click on “Start for free” on the intro page https://www.oracle.com/cloud/free/
• Enter your Country/Territory and e-mail address, read the Terms of Use and click Next.
• Select “Personal Use” as Account Type and choose a unique Cloud Account Name (this will be used in service URL’s). Now for the Home Region you need to select a region where the “Always Free Eligible” option is available, I selected Germany Central (Frankfurt) because that’s the closest one to my own region (Belgium).

• Fill in your name and address details, and a valid mobile phone number. Click on “Next: Verify Mobile Number“.
• You should now receive a text message with a verification code. Enter the verification code in the Code text field (note that you only have two minutes for this) and click on “Verify Code“.
• In the next screen, enter a strong password and click on “Next: Payment Information“.
• You will get a message before entering the credit card details:
• Enter your credit card details and click on Finish.
• Check the checkbox to agree with the Terms and Conditions, and click on “Complete Sign-Up“. Your account will now be created.
• You will now receiver a confirmation e-mail. Click on “Sign In To Oracle Cloud” in the e-mail, or go to https://www.oracle.com/cloud/free/ and click on “Sign In To Oracle Cloud“.
• For the Cloud Account Name, enter the unique Cloud Account Name that you entered during the registration process (see above). Enter the strong password (see above) when it is requested.
• You should now see the Oracle Cloud home page. Well done! :-)

Step 2: Create a free Oracle ATP Database

ATP stands for “Autonomous Transaction Processing“. It’s Oracle’s self-driving, self-repairing and self-securing Cloud Database, based on Oracle version 18c (at the time of writing this blog post).

Now let’s create our first free ATP database:

• Go to https://www.oracle.com/cloud/free/ and sign in with your Cloud Account Name.
• Click on “Create an ATP database“.
• Fill in the following information:
  • Compartment: leave as is.
  • Display name: a user-friendly name, for example “My first ATP database”.
  • Database name: for example, “MATTHIASDB”.
  • Workload type: Transaction Processing
  • Deployment type: Shared Infrastructure
  • Configure the database: make sure to leave the “Always Free” option to ON, so that only free options can be selected.
  • Choose database version: 18c
  • OCPU Count should be 1, Storage (TB) should be 0.02 (so 20 GB), and Auto scaling OFF.
  • Fill in a password for the ADMIN database account.
  • Choose network access: allow secure access from everywhere
  • License type: License Included
  • Click on “Create Autonomous Database”.
  • You will now come to an overview screen where you will see the message “ATP Provisioning…”. This means that the creation of the database is in progress.
  • After a while (it took only a couple of minutes when I tried) it should turn to green, your free ATP database is now ready!

Step 3: Use SQL Developer Web to connect to your free Oracle ATP Database

SQL Developer Web is the cloud version of the SQL Developer desktop application (which is also free, btw). You can use it to connect to your ATP database and browse database objects or run SQL queries.

• In the list of Autonomous Databases, click on the display name of your ATP database.
• Now click on the Tools tab (next to Autonomous Database Information).
• Now click on “Open SQL Developer Web”.
• Log in with user ADMIN and the password you created earlier (see above). The SQL Developer Web environment should now load into your web browser.
• Now try to run a query, for example put “select * from v$version” in the Worksheet window and click on the green arrow. You should see some output in the Query Result window:
• You can also browse database object using the Navigator window on the left side.

Please note that SQL Developer Web is a very basic tool compared to the desktop version of SQL Developer (which already exists for a very long time). It’s also possible to connect the desktop version of SQL Developer to your ATP Cloud Database, see for example here: https://dgielis.blogspot.com/2019/09/free-oracle-cloud-3-connecting-with-sql.html

Step 4: Access APEX in your free Oracle ATP Database

APEX (Oracle Application Express) is a low-code, web-based development platform that is included with Oracle ATP. The good news is that it’s already pre-installed, so when you have your ATP database ready, no additional steps are needed to install or configure APEX. At the time of writing, the version of APEX that is included with Oracle ATP is APEX 19.2.

• In the list of Autonomous Databases, click on the display name of your ATP database.
• Now click on the Tools tab (next to Autonomous Database Information).
• Click on “Open APEX“.
• Log in with the password of the ADMIN database account (see above).
• Click on “Create Workspace” to create your first APEX Workspace. In this example, I entered MATTHIAS as database user and MY_WORKSPACE as Workspace name.
• Now in the Admin menu (top right of the page), click on “Sign Out“.
• Click on “Return to Sign In Page” and enter the Workspace name, user name and password that you just created.
• Now click on “App Gallery”, “Sample Interactive Grids” and “Install App” to install the Sample Interactive Grids application in your workspace.
• If the installation went fine, click on App Builder and then on the small arrow next to the Sample Interactive Grids application to run it. Log in with the Workspace user that you created earlier (in my case, user MATTHIAS).
• That’s it! You have now successfully installed your first APEX application in your free Oracle ATP Database!
•  The cool thing is that you now use this URL from everywhere, so you just created your first public APEX 19.2 application on Oracle Cloud!

In a future blog post I will explain some more things that you can do with your free Oracle Cloud account.

HTH

Matthias

How to export AWS CLI CloudWatch data to Oracle using SQL*Loader

Environment: AWS CLI 1.17.7, Linux CentOS 7, Oracle XE 18c

In my previous blog post I explained how you can load AWS CLI CloudWatch data into a MySQL database. In this blog post I will explain how you can do something similar using Oracle as database. The goal is to retrieve CPU CloudWatch metrics from an Oracle database on RDS and load the data into a local Oracle database using the utility SQL*Loader.

Note: I assume that you already installed the AWS CLI tools and also have access and connectivity to an Oracle database (for this example I used Oracle XE 18c, installed on the same host).

Let’s first create a table to store the AWS CloudWatch data:

CREATE TABLE MATTHIAS.AWS_CLOUDWATCH_CPU_MON
(
AC_ID NUMBER GENERATED ALWAYS AS IDENTITY,
INSTANCE_ID VARCHAR(50) NOT NULL,
CPU_MAX_PERCENT NUMBER NOT NULL,
MON_TIMESTAMP TIMESTAMP NOT NULL,
PRIMARY KEY (AC_ID)
);

ALTER TABLE MATTHIAS.AWS_CLOUDWATCH_CPU_MON
ADD CONSTRAINT aws_cl_uc1 UNIQUE (INSTANCE_ID,MON_TIMESTAMP);

Some explanation about the columns in the database table:

• AC_ID: auto-generated ID and primary key constraint
• INSTANCE_ID: the name of the RDS database instance
• CPU_MAX_PERCENT: the max CPU CloudWatch metric (a decimal number)
• MON_TIMESTAMP: date and time for the CPU metric

Similar to the MySQL example in my previous blog post, we will first get the data using AWS CLI, print the data to a text file, filter out the unneeded columns, and finally load the text file into Oracle using SQL*Loader.

Let’s start by creating two files to store the SQL*Loader configuration data: the parameter file (sqlldr.par) and the control file (sqlldr.ctl).

In the parameter file we will store the Oracle database connection details, the name of the control file, and the name of the log file:

$ cd /home/mhoys/oracle-scripts

$ vi sqlldr.par

USERID=MATTHIAS/<password>@xepdb1
CONTROL=sqlldr.ctl
LOG=sqlldr.log

$ chmod 600 sqlldr.par

MATTHIAS is the database user that will be used to connect to the Oracle database that has the connect identifier “xepdb1”.

Don’t forget to change the file permissions to 600, so that only the file owner can read the file.

Now let’s create the SQL*Loader control file:

$ cd /home/mhoys/oracle-scripts

$ vi sqlldr.ctl

OPTIONS (SKIP=1)
LOAD DATA
INFILE 'aws_cloudwatch_cpu_mon.txt'
INTO TABLE aws_cloudwatch_cpu_mon
APPEND
FIELDS TERMINATED BY "\t"
TRAILING NULLCOLS
(INSTANCE_ID CONSTANT "rdsoradb001",
CPU_MAX_PERCENT DECIMAL EXTERNAL,
MON_TIMESTAMP "to_timestamp(:MON_TIMESTAMP,'YYYY-MM-DD\"T\"hh24:mi:ss\"Z\"')" )

Some explanation:

• OPTIONS (SKIP=1): the first row in the file aws_cloudwatch_cpu_mon.txt (the output of the AWS CLI command) is empty, so we’re going to skip it
• INFILE ‘aws_cloudwatch_cpu_mon.txt’: the name of the AWS CLI output file where we are reading the data from
• APPEND: this means we are going to append new data to the existing data in our table
• FIELDS TERMINATED BY “\t”: the fields in the text file are delimited by the TAB character
• INSTANCE_ID CONSTANT “rdsoradb001”: the name of the RDS database, treated as constant here
• CPU_MAX_PERCENT DECIMAL EXTERNAL: the max CPU CloudWatch metric is a decimal value (for example: 25.00)
• MON_TIMESTAMP “to_timestamp(:MON_TIMESTAMP,’YYYY-MM-DD\”T\”hh24:mi:ss\”Z\”‘)” ): the date value from AWS CloudWatch is returned in the format “2020-02-05T12:46:00Z”, so we are converting it here to the Oracle TIMESTAMP format.

For your info, the full SQL*Loader documentation can be found here.

Now that we have the SQL*Loader parameter file and control file in place, let’s create the main shell script to call the AWS CLI and SQL*Loader commands:

$ cd /home/mhoys/oracle-scripts

$ vi aws_cw_cpu_ora_load.sh

#!/bin/bash

# change to the script directory
cd /home/mhoys/oracle-scripts

# set Oracle environment variables (needed for cron)
ORACLE_BASE=/opt/oracle; export ORACLE_BASE
ORACLE_HOME=/opt/oracle/product/18c/dbhomeXE;export ORACLE_HOME

start_time=`date --utc +%FT%TZ -d "12 hours ago"`
end_time=`date --utc +%FT%TZ`
instance_id="rdsoradb001"
file_name="aws_cloudwatch_cpu_mon.txt"

# run aws cloudwatch script and print output to text file
/home/mhoys/.local/bin/aws cloudwatch get-metric-statistics --metric-name CPUUtilization --start-time "$start_time" --end-time "$end_time" --period 300 --namespace AWS/RDS --statistics Maximum --dimensions Name=DBInstanceIdentifier,Value="$instance_id" --output text|awk '{print $2 "\t" $3}' > $file_name

# run sqlloader to load text file into Oracle database
/opt/oracle/product/18c/dbhomeXE/bin/sqlldr parfile=sqlldr.par

Here we are getting the CPU Max metrics for our RDS database instance “rdsoradb001” for the last 12 hours, with an interval of 5 minutes (300 seconds) – so there will be 144 records in total. We only need the second and third column of the output, that’s why we are piping the results to the “awk” command.

In the last line we are calling the SQL*Loader executable (sqlldr), passing the parameter file that we created earlier (the parameter file contains the connection details and the name of the control file to be used).

Once you have saved the script, let’s try to run it manually:

$ cd /home/mhoys/oracle-scripts

$ ./aws_cw_cpu_ora_load.sh

SQL*Loader: Release 18.0.0.0.0 - Production on Wed Feb 5 17:43:07 2020
Version 18.4.0.0.0

Copyright (c) 1982, 2018, Oracle and/or its affiliates. All rights reserved.

Path used: Conventional
Commit point reached - logical record count 144

Table AWS_CLOUDWATCH_CPU_MON:
144 Rows successfully loaded.

Check the log file:
sqlldr.log
for more information about the load.

Victory! 144 records were loaded into the table AWS_CLOUDWATCH_CPU_MON. Check the sqlldr.log and aws_cloudwatch_cpu_mon.txt files that should have been created in the same directory. If there are rejected records, there should also be a file called “aws_cloudwatch_cpu_mon.bad”.

We can now schedule the script using the cron scheduler:

$ crontab -e

0 8,20 * * * /home/mhoys/oracle-scripts/aws_cw_cpu_ora_load.sh > /dev/null

In this example we scheduled the script to run twice daily (at 08:00 and 20:00). The standard output is redirected to /dev/null to avoid that cron sends an e-mail on every execution of the script.

That’s it!

HTH

Matthias