Wednesday, 3 February 2021

The Database Developer VM: installing Apex, ORDS 21.x und SQLDeveloper Web

This post is part of my Oracle Developer VM  Project series. It is not meant to replace the official documentation in any way and what I describe is no recommended configuration. It is only meant to be a guide, if you follow my DB Developer VM Project and want to add these services to your VM.

Compared to my other posts, this is a bunch of tools to be installed in one go. The reason is that if you want ORDS and Apex, then it is the best way to install Apex first. But it won't run without ORDS, so both should be installed in one go. And SQLDeveloper Web comes for free with ORDS. But despite the number of additional tools, this is one of the shorter posts of this series.

According to the documentation, memory_target needs to be set to at least 300M for Apex, but in my setup the database won't start with this amount of memory. I get the following error:

ORA-00838: Specified value of MEMORY_TARGET is too small, needs to be at least 6304M

So first, we will set that parameter to the required value.

1
2
alter system set memory_target='6304M' scope=spfile;
startup force;

This should give you the following: 
Download the Apex install bundle and unzip it (eg. to /u01/apex). In the unpacked folder run the installer

sqlplus sys/your_password@orclpdb as sysdba @apexins sysaux sysaux temp /i/

Leave the session open and create the Apex Admin user by running @apxchpwd.sql.
Unlock the APEX_PUBLIC_USER and change his password, which has been randomly set at installation

alter user apex_public_user identified by oracle account unlock;

Still in the same session, run @apex_rest_config.sql and set both passwords for  the APEX_LISTENER user and the APEX_REST_PUBLIC_USER user.

To use web services and for sending email, network services need to be enabled. Copy the following from the documentation and run it:

1
2
3
4
5
6
7
BEGIN    
   DBMS_NETWORK_ACL_ADMIN.APPEND_HOST_ACE(
      host => '*',
      ace => xs$ace_type(privilege_list => xs$name_list('connect'),
                         principal_name => 'APEX_200200',
                         principal_type => xs_acl.ptype_db));
END;

So much for the Apex part. Download the latest ORDS from https://www.oracle.com/database/technologies/appdev/rest-data-services-downloads.html and unzip the contents to your target directory (eg. /u01/ords).

Go to the directory where you have unzipped ORDS and run

java -jar ords.war

ORDS will notice, that this is a first time run and will ask for some configuration parameters. In most cases, you can just accept the defaults (except for the passwords, of course). If you have followed my guide so far, the only parameters, that need to be set, are:
  • location to store configuration data => /u01/ords/params/
  • database service name: orclpdb
  • Enter the APEX static resources location:/u01/apex/images/
After the setup, ORDS will keep running in standalone mode. We are not done yet, but give it a try to check if everything went fine so far. Open Firefox and enter http://localhost:8080, that should give you the Apex login page (as this is the first call, it will take a few seconds).
Following the documentation, we need to set maxFormContentSize on startup by running 

java -Dorg.eclipse.jetty.server.Request.maxFormContentSize=3000000 -jar ords.war

I created a runords.sh with this line in the same directory as ords.war for starting ORDS. Looking at the ORDS terminal window, you will see the following warnings:
Though this is not a production environment, I prefer to configure the system to run without these warnings. At least it makes it easier to notice, if something really goes wrong. So I set these parameters to the recommended values from the documentation.
Go to /u01/ords/params/ords/ and edit defaults.xml. Add the following lines before the closing </properties>:

<entry key="jdbc.MaxLimit">20</entry>
<entry key="jdbc.InitialLimit">10</entry>

I also got the following error:
INFO Disabling document root because the specified folder does not exist: /u01/ords/params/ords/standalone/doc_root
To get rid of this INFO either create that directory or edit /u01/ords/params/ords/standalone/standalone.properties and set standalone.doc.root to an existing folder.

A few errors I ran into were all like
WARNING The pool named: |apex|al| is invalid and will be ignored: The username or password for the connection pool named |apex|al|, are invalid, expired, or the account is locked
Probably I messed up my passwords when running the installer. This can be fixed by setting the passwords for the following users (and unlocking them, just in case):

1
2
3
4
5
6
7
8
alter user ORDS_PUBLIC_USER account unlock;
alter user APEX_PUBLIC_USER account unlock;
alter user APEX_LISTENER account unlock;
alter user APEX_REST_PUBLIC_USER account unlock;
alter user ORDS_PUBLIC_USER identified by oracle;
alter user APEX_PUBLIC_USER identified by oracle;
alter user APEX_LISTENER identified by oracle;
alter user APEX_REST_PUBLIC_USER identified by oracle;

Now ORDS should come up with any errors.
Just to double check that everything is there, call http://localhost:8080/ords/sql-developer
Of course, you need a rest enabled schema to log in. That's it, everything is running.

Posted by at
Labels: , , , , , , , ,