Unable to open Oracle Apex Login Page / Oracle APEX Login Page Blank

Description of Issue

Oracle APEX 5.1 new installation(Embedded plsql gateway configuration) completed successfully, However, Unable to open Oracle Apex Login Page / Oracle APEX Login Page Blank. Tried to access below URL still same issue http://<Machine-Name>:<port-number>/i/apex_version.txt

Cause

Database parameters [shared_servers,DISPATCHERS] are not properly set which are required for XDB

Solution

Verify Listener and below Database Parameters:     $ lsnrctl status     SQL> show parameter local listener     SQL> show parameter dispatchers     SQL> show parameters shared_servers 1) We should have shared_servers > 0, please set the following      alter system set shared_servers = 5;    2) You will also need to set dispatchers      Reference:     How to Setup XDB Protocol Server: FTP, HTTP, WebDAV ( Doc ID 362540.1 )      Specifically:      *************************************************************************      Set the DISPATCHERS parameter.      The DISPATCHERS system parameter is required for XDB protocol registration with the                 Listener. The DISPATCHERS parameter is set in the init.ora.      Run the following in SQLPlus to determine if the dispatchers parameter is already set:      show parameter dispatchers      If XDB dispatchers is not already set, add the following line to the init.ora:      Non-RAC:      dispatchers="(PROTOCOL=TCP)(SERVICE=<sid>XDB)"      Or from the SQL*Plus prompt:      SQL> alter system set dispatchers="(PROTOCOL=TCP)(SERVICE=XDB)" scope=both                 sid='<sid>';      RAC:      instanceid1.dispatchers="(PROTOCOL=TCP) (SERVICE=<instanceid1>XDB)"      instanceid2.dispatchers="(PROTOCOL=TCP) (SERVICE=<instanceid2>XDB)"      Or from the SQL*Plus prompt:       Execute this for each node:       alter system set dispatchers="(PROTOCOL=TCP)(SERVICE=XDB)" scope=both sid='<sid>';       NOTE: Replace <sid> ,<instanceid1>, etc. with the actual values.       For example:       dispatchers="(PROTOCOL=TCP) (SERVICE=OrclXDB)"       If the DISPATCHERS parameter is set then the default value for SHARED_SERVERS is 1.            The value of SHARED_SERVERS parameter should always be >= 1 for this setup to work.

Embedded PL/SQL Gateway: HTTP-404 Bad Request : The HTTP client sent a request that this server could not understand

Issue:

Getting - Bad Request : The HTTP client sent a request that this server could not understand error
WHEN PL/SQL DYNAMIC CONTENT GREATER than default value.



Embedded PL/SQL Gateway: HTTP-404 ORA-6502: PL/SQL: numeric or  value error: character string buffer too small.

After enabling trace we could see below error in trace file.
[Refer --> Enable Logging and Debugging for the Embedded PL/SQL HTTP Gateway (EPG) ( Doc ID 563704.1 )]

"Embedded PL/SQL Gateway: /apex/wwv_flow.accept HTTP-400 Too many arguments passed in. Got 2120 parameters. Upper limit is 2000"

Cause:

These kind of issues will occur due to a modplsql error that indicates that the number of parameters exceeds the number of parameters defined for the modplsql Database Access Descriptor (DAD).
The default value is 2000. Since the APEX page request exceeds the maximum defined, the request fails.

Solution:

To modify the global parameter "max-parameters" to be set for the XDB:

1. Connect to the database through SQLPLUS as SYSDBA user and run the following command to set the parameter at the global level:
SQL> exec dbms_epg.set_global_attribute('max-parameters','6000');
SQL> commit;

2. Verify the value set by above

SQL> SELECT dbms_epg.get_global_attribute('max-parameters') FROM dual;

DBMS_EPG.GET_GLOBAL_ATTRIBUTE('MAX-PARAMETERS')
--------------------------------------------------------------------------------
6000

SQL>

3. Restart the database and listener.

4. Test the issue

Unlock specific USER in the APEX Workspace

If an APEX User is unlocked then follow below procedure to unlock the same

Check the status of an User:

SQL> select WORKSPACE_NAME,USER_NAME,ACCOUNT_LOCKED,DESCRIPTION 
from APEX_040200.apex_workspace_apex_users;

SQL> select DEFAULT_SCHEMA,LAST_FAILED_LOGIN,FAILED_ACCESS_ATTEMPTS,USER_NAME 
from APEX_040200.WWV_FLOW_FND_USER;

Steps to Unlock ADMIN user in the TEST Workspace

Login to SQL*Plus as the owner of the parsing schema if you are making modifications to a workspace.

As an alternative, you can login as SYS or SYSTEM and then do an alter session:

SQL> alter session set current_schema = TEST;

Run the following, which sets the workspace contect using a combination of apex_util.find_security_group_id ./ apex_util.set_security_group_idand issues the appropriate requests. 

SQL> begin
apex_util.set_security_group_id(p_security_group_id => apex_util.find_security_group_id('TEST'));
apex_util.unlock_account(p_user_name => 'ADMIN');
commit;
end;
/