I run a lot of VM instances of Oracle. Some are to test different installations and versions of Oracle, APEX, APEX Listener, ORDS, etc. It is very frustrating to fire one up and then get faced with the dreaded 400 error from the Oracle connection. Then I have to remember which standard (of several) I was using at the time I installed the instance. As a better practice, it is a good idea to adjust your profile that is preventing APEX from starting up.
Root Cause
In my case - the root cause of the issue is the default settings of the user profile. This controls how the database handles login passwords and other resource details. By default, my instance had a 180 day limit on passwords. This is fine until I fire up an instance I made more than six months ago. An expired account will prevent the ORDS connection from initializing resulting in a web server error. To review your current account and profile settings, you will need a privileged user that can see DBA views.
-- Show the APEX related Accounts
select username, profile, account_status
from dba_users
where username like 'FLOW%'
or username like 'APEX%'
or username like 'ORDS\_%'
escape '\' ;
-- PROFILE details
select * from dba_profiles
where profile in('DEFAULT','APEX_WEB_CONNECTIONS') order by 1,3,2;
Profile-Users
Use Case - Test Machine -or- I don’t care about passwords This is a good use case for situations where either you are on your own test instance, or all of the user connections to your database are locked and controlled. (If your implementation of Oracle APEX does not require SQL*NET connection - keep the accounts locked)
Change the Default Profile to never expire the password for an account using the DEFAULT profile.
-- Option - Change Default to Unlimited --
alter profile default limit password_life_time unlimited;
Use Case - Passwords - Just don’t break my web server
If you want to keep the majority of your accounts using resource and password policies, then consider making a profile for just the WEB SERVER connections.
-- Create a new Profile
create profile apex_web_connections limit
failed_login_attempts 2
password_life_time unlimited
-- password_lock_time 1/24
-- password_grace_time 10
;
-- Change the APEX users to the new profile
-- Used for APEX connection
alter user apex_public_user profile apex_web_connections;
-- Used for REST in APEX
alter user apex_listener profile apex_web_connections;
-- Used for REST in APEX
alter user apex_rest_public_user profile apex_web_connections;
-- Used for REST in ORDS
alter user ords_public_user profile apex_web_connections;
-- Now the users that are configured when the web server was setup
-- will not require the password to be adjusted.
NOTE: Security Risks - any system that allows SQL*NET connections without other security measures (such as SSH tunneling) runs the risk of account locking from malicious behavior. In the example above - two failed logins will lock this account - resulting in ANY APEX connection failing to work. This will prevent the ORDS connection from working and disable all APEX activity.