Installing Oracle APEX 26.1 with ORDS 26.1 and Data Reporter on Oracle Database 26ai Free

[admin]

Oracle APEX 26.1 is the latest release of Oracle's low code development platform. This article walks through the complete installation on Oracle Database 26ai Free edition on Windows including all errors encountered and how they were resolved.

1. What is Data Reporter
   
   Data Reporter is a new feature in Oracle APEX 26.1 that allows workspace administrators and developers to create and manage reporting applications without writing code. Before you can access Data Reporter the following setup steps are required.
2. Configure Authentication Schemes. To access Data Reporter you must configure one of the following authentication schemes: SAML, Social Sign-In, or HTTP Header Variable.
3. Create tables. A Workspace administrator creates tables on which to build Data Reporter reporting applications.
4. Set Up Data Reporter. The first time you access Data Reporter a Welcome page appears. A Workspace administrator uses the links on the Welcome page to create an initial dataset and Data Reporter reporting application.
5. Create reports. Once a Workspace administrator creates an initial reporting application they can select the application and start creating reports.
6. Manage users. APEX users with developer privileges have full access to Data Reporter and can manage users. Administrators can also create and manage users exclusively within Data Reporter on the Manage User page.

Prerequisites and Downloads

Before starting download the following software.

1. Oracle APEX 26.1 from oracle.com/tools/downloads/apex-downloads
2. Oracle ORDS 26.1 from oracle.com/database/sqldeveloper/technologies/db-actions/download
3. Java JDK 17 from oracle.com/java/technologies/javase/jdk17-archive-downloads.html
4. Oracle Database 26ai Free must already be installed and running or 19c as shown in the image below,
   
   apex_db19c.jpeg

ORDS 26.1 requires Java 17 minimum. If you have Java 1.8 installed for Oracle EBS client do not uninstall it. Both can coexist as long as you set the PATH correctly for each session.

Environment Variables

Always set the following before starting any work with Oracle 26ai Free. This is especially important if you have Oracle EBS client installed which brings its own older SQL*Plus and Java versions.

Code: Select all

set ORACLE_HOME=C:\app\PC\product\26ai\dbhomeFree
set JAVA_HOME=C:\Program Files\Java\jdk-17
set ORDS_HOME=C:\app\PC\product\26ai\ords26
set PATH=%ORACLE_HOME%\bin;%JAVA_HOME%\bin;%ORDS_HOME%\bin;%PATH%
set ORACLE_SID=FREE

Common Errors Due to Wrong SQL*Plus Version

If you have Oracle EBS client installed you will have SQL*Plus 10g or similar in your PATH. Connecting with the wrong binary gives the following errors.

• ORA-12560: TNS protocol adapter error when using the old SQL*Plus binary
• ORA-28040: No matching authentication protocol when old SQL*Plus connects to Oracle 26ai

Always set the environment variables above before connecting.

Pre Installation Checks

Connect to FREEPDB1 as SYS and run the following checks.

Check database version:

Code: Select all

SELECT version FROM v$instance;

Check character set which must be AL32UTF8:

Code: Select all

SELECT value FROM nls_database_parameters WHERE parameter = 'NLS_CHARACTERSET';

Check free space in SYSTEM and SYSAUX tablespaces:

Code: Select all

SELECT tablespace_name, ROUND(SUM(bytes)/1024/1024) mb_free
FROM dba_free_space
WHERE tablespace_name IN ('SYSAUX','SYSTEM')
GROUP BY tablespace_name
ORDER BY 1;

In our case SYSTEM tablespace in FREEPDB1 had only 5MB free which would cause the installation to fail. Get the actual datafile paths first.

Code: Select all

SELECT file_name FROM dba_data_files WHERE tablespace_name = 'SYSTEM';
SELECT file_name FROM dba_data_files WHERE tablespace_name = 'SYSAUX';

Then enable autoextend on both datafiles. Note that Oracle 26ai Free uses bigfile tablespaces so you cannot add additional datafiles. Autoextend is the only option.

Code: Select all

ALTER DATABASE DATAFILE 'C:\APP\PC\PRODUCT\26AI\ORADATA\FREE\FREEPDB1\SYSTEM01.DBF' AUTOEXTEND ON NEXT 100M MAXSIZE 3000M;
ALTER DATABASE DATAFILE 'C:\APP\PC\PRODUCT\26AI\ORADATA\FREE\FREEPDB1\SYSAUX01.DBF' AUTOEXTEND ON NEXT 100M MAXSIZE 3000M;

Installing Oracle APEX 26.1

Connect to FREEPDB1 as SYS then change to the directory where you unzipped APEX and run the installer.

Code: Select all

@apexins.sql SYSAUX SYSAUX TEMP /i/

Thank you for installing Oracle APEX 26.1.0

Oracle APEX is installed in the APEX_260100 schema.

The structure of the link to the Oracle APEX Administration Services is as follows:
http://host:port/ords/apex_admin

The structure of the link to the Oracle APEX development environment is as follows:
http://host:port/ords/apex


timing for: Phase 3 (Switch)
Elapsed: 0.12


timing for: Complete Installation
Elapsed: 3.47

The installation completed in approximately 3.47 minutes and APEX_260100 schema was created.

This might take time as per your computer specifications like processor, RAM, Storage speed etc. If it is taking 20 or more minutes means your specifications are low and nothing wrong with intallation.


After installation run the following to set the APEX ADMIN password.

Code: Select all

@apxchpwd.sql

Also run apex_rest_config.sql to configure REST services for APEX. It will prompt for two passwords.

Code: Select all

@apex_rest_config.sql

Installing ORDS 26.1

Issue 1: SYS Authentication Failure

The most frustrating issue during ORDS installation was that the ORDS JDBC thin driver could not authenticate SYS AS SYSDBA even though SQL*Plus connected successfully with the same password. This error appeared consistently during both interactive and non interactive install attempts.

ORA-01017: invalid credential or not authorized; logon denied

The root cause is that ORDS JDBC thin driver handles SYS AS SYSDBA authentication differently from SQL*Plus. This fails in some configurations even when the password file exists and remote_login_passwordfile is set to EXCLUSIVE.

The solution is to create a dedicated ORDS installer user and grant it the required privileges.

Code: Select all

CREATE USER ords_installer IDENTIFIED BY Oracle123;
GRANT ALL PRIVILEGES TO ords_installer WITH ADMIN OPTION;

Then run the ORDS installer privileges script provided with ORDS. Note: do not run this script with SYS as the target user. You will get ORA-01749 because SYS cannot grant privileges to itself.

Code: Select all

@C:\app\PC\product\26ai\ords26\scripts\installer\ords_installer_privileges.sql ords_installer

Also run apex_rest_config.sql from the APEX directory before starting ORDS installation.

Issue 2: Partial Installation Failure

A partial failed ORDS installation attempt left the ORDS_METADATA schema in an inconsistent state. On the next install attempt ORDS reported the following error.

PDB FREEPDB1 error, the ORDS schema version does not exist. This may be due to a previous ORDS installation failure.

To clean up the failed installation drop the partial schemas in sqlplus connected to FREEPDB1 as SYS.

Code: Select all

DROP USER ords_metadata CASCADE;
DROP USER ords_public_user CASCADE;

Then retry ORDS installation using interactive mode.

Code: Select all

ords --config C:\app\PC\product\26ai\ords26 install --interactive

During interactive install when prompted for the APEX static resources location set it to the images folder inside your APEX unzip directory.

Code: Select all

C:\app\PC\product\26ai\dbhomeFree\apex\images

Configuring Data Reporter

Data_Reporter.png

Data Reporter in APEX 26.1 requires one of three authentication schemes configured at workspace level before it can be accessed.

• SAML
• Social Sign-In
• HTTP Header Variable

For a demo environment Social Sign-In with Google is the easiest option.

Step 1: Create Google OAuth Credentials

Go to Google Cloud Console at console.cloud.google.com and create OAuth 2.0 credentials with the following settings.

• Application type: Web application
• Authorized redirect URI: http://localhost:8080/ords/apex_authentication.callback

Copy the Client ID and Client Secret once created.

Step 2: Configure Social Sign-In in APEX

Go to your APEX workspace and create a Social Sign-In authentication scheme with the following values.

• Authentication Provider: Google
• Scope: openid profile email
• Username Attribute: email

Paste the Client ID and Client Secret into the OAuth2 Credentials section.

Step 3: Grant Network ACL Access

When accessing Data Reporter for the first time you may get the following error.

The HTTP request to https://accounts.google.com/.well-known ... figuration failed.

This happens because APEX schema does not have network ACL access to Google endpoints. Run the following in sqlplus connected to FREEPDB1 as SYS to grant access to all three required Google endpoints.

Code: Select all

BEGIN
  DBMS_NETWORK_ACL_ADMIN.APPEND_HOST_ACE(
    host => 'accounts.google.com',
    ace  => xs$ace_type(
              privilege_list => xs$name_list('connect','resolve'),
              principal_name => 'APEX_260100',
              principal_type => xs_acl.ptype_db));
END;
/

BEGIN
  DBMS_NETWORK_ACL_ADMIN.APPEND_HOST_ACE(
    host => 'oauth2.googleapis.com',
    ace  => xs$ace_type(
              privilege_list => xs$name_list('connect','resolve'),
              principal_name => 'APEX_260100',
              principal_type => xs_acl.ptype_db));
END;
/

BEGIN
  DBMS_NETWORK_ACL_ADMIN.APPEND_HOST_ACE(
    host => 'openidconnect.googleapis.com',
    ace  => xs$ace_type(
              privilege_list => xs$name_list('connect','resolve'),
              principal_name => 'APEX_260100',
              principal_type => xs_acl.ptype_db));
END;
/

Step 4: Create Workspace User

After successful Google authentication you may get the following error.

Access denied by Application security check. You are not authorized to view this application.

Go to APEX Admin at http://localhost:8080/ords/apex_admin then Manage Workspaces then Manage Developers and Users. Create a user with your Google email address as the username and enable the following.

• User is a workspace administrator: Yes
• User is a developer: Yes
• App Builder Access: Yes
• Data Reporter Access: Yes

Set any password as it will not be used since login goes through Google SSO. Data Reporter is now ready to use.