The APEX is installed by default on a 11g Oracle database. Please see post here for install and upgrade on APEX. This is done on a Linux x86-64 machine.
I will use the Embedded Pl/SQL gateway from Oralce to access the APEX from the database server directly. Please check my other post on EPG here - http://oegmodplsql.blogspot.com/
Steps:
1- Make sure that the EPG is installed and running by executing this script
$ORACLE_HOME/rdbms/admin/epgstat.sql
2- Make sure that listener.ora has the following lines:
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=amghost2)
(PORT=8080))(Presentation=HTTP)(Session=RAW))
3- Fix the ACLs as:
DECLARE
ACL_PATH VARCHAR2(4000);
ACL_ID RAW(16);
BEGIN
-- Look for the ACL currently assigned to 'localhost' and give APEX_040100
-- the "connect" privilege if APEX_040100 does not have the privilege yet.
SELECT ACL INTO ACL_PATH FROM DBA_NETWORK_ACLS
WHERE HOST = 'localhost' AND LOWER_PORT IS NULL AND UPPER_PORT IS NULL;
-- Before checking the privilege, ensure that the ACL is valid
-- (for example, does not contain stale references to dropped users).
-- If it does, the following exception will be raised:
--
-- ORA-44416: Invalid ACL: Unresolved principal 'APEX_040100'
-- ORA-06512: at "XDB.DBMS_XDBZ", line ...
--
SELECT SYS_OP_R2O(extractValue(P.RES, '/Resource/XMLRef')) INTO ACL_ID
FROM XDB.XDB$ACL A, PATH_VIEW P
WHERE extractValue(P.RES, '/Resource/XMLRef') = REF(A) AND
EQUALS_PATH(P.RES, ACL_PATH) = 1;
DBMS_XDBZ.ValidateACL(ACL_ID);
IF DBMS_NETWORK_ACL_ADMIN.CHECK_PRIVILEGE(ACL_PATH, 'APEX_040100',
'connect') IS NULL THEN
DBMS_NETWORK_ACL_ADMIN.ADD_PRIVILEGE(ACL_PATH,
'APEX_040100', TRUE, 'connect');
END IF;
EXCEPTION
-- When no ACL has been assigned to 'localhost'.
WHEN NO_DATA_FOUND THEN
DBMS_NETWORK_ACL_ADMIN.CREATE_ACL('local-access-users.xml',
'ACL that lets power users to connect to everywhere',
'APEX_040100', TRUE, 'connect');
DBMS_NETWORK_ACL_ADMIN.ASSIGN_ACL('local-access-users.xml','localhost');
END;
/
COMMIT;
ACL_PATH VARCHAR2(4000);
ACL_ID RAW(16);
BEGIN
-- Look for the ACL currently assigned to 'localhost' and give APEX_040100
-- the "connect" privilege if APEX_040100 does not have the privilege yet.
SELECT ACL INTO ACL_PATH FROM DBA_NETWORK_ACLS
WHERE HOST = 'localhost' AND LOWER_PORT IS NULL AND UPPER_PORT IS NULL;
-- Before checking the privilege, ensure that the ACL is valid
-- (for example, does not contain stale references to dropped users).
-- If it does, the following exception will be raised:
--
-- ORA-44416: Invalid ACL: Unresolved principal 'APEX_040100'
-- ORA-06512: at "XDB.DBMS_XDBZ", line ...
--
SELECT SYS_OP_R2O(extractValue(P.RES, '/Resource/XMLRef')) INTO ACL_ID
FROM XDB.XDB$ACL A, PATH_VIEW P
WHERE extractValue(P.RES, '/Resource/XMLRef') = REF(A) AND
EQUALS_PATH(P.RES, ACL_PATH) = 1;
DBMS_XDBZ.ValidateACL(ACL_ID);
IF DBMS_NETWORK_ACL_ADMIN.CHECK_PRIVILEGE(ACL_PATH, 'APEX_040100',
'connect') IS NULL THEN
DBMS_NETWORK_ACL_ADMIN.ADD_PRIVILEGE(ACL_PATH,
'APEX_040100', TRUE, 'connect');
END IF;
EXCEPTION
-- When no ACL has been assigned to 'localhost'.
WHEN NO_DATA_FOUND THEN
DBMS_NETWORK_ACL_ADMIN.CREATE_ACL('local-access-users.xml',
'ACL that lets power users to connect to everywhere',
'APEX_040100', TRUE, 'connect');
DBMS_NETWORK_ACL_ADMIN.ASSIGN_ACL('local-access-users.xml','localhost');
END;
/
COMMIT;
4- Unlock the ANONYMOUS user and change its password.
5- Execute following steps:
A- execute this as sysdba
exec DBMS_XDB.setHTTPPort(8080);
## You can set any port that you desire the EPG to listen at. However this port and the listener.ora port should be same and if you choose to go port 80 then root privileges need to be sorted out.
B- Add above line in listener.ora and restart the listener.
6- Now access the APEX as.
http://<db server name>:8080/apex
Enjoy !!
Hi Mukesh,
ReplyDeletenice posts you've done here.
In the Apex with Oracle EPG you mention port 8080.. can you use also 8181 (as https port) as you can use it in Glassfish for example?
Second question... if you have to install on windows environment, what would be your preferred production implementation if there will be public access to the apex site, meaning secure and non-secure connections to the apex site.
Regards,
Darko
Hello Madevski,
ReplyDeleteYes, you can change the port to whatever you like. You can do this by:
exec DBMS_XDB.setHTTPPort(8181);
On the question regarding Windows - you can do all of the above in windows - the only difference is the way the path is and the commands are executed. In my 19 years, I have seen very few implementation on Windows. I am not sure as why you want Oracle to be on Windows but yes you can install it.
My suggestion would be a low-cost solution as Linux - its easy and can be manipulated completely. On Windows you will have to deal with registry sometime for complete delete and other stuff.
If you need help as how to setup and you get errors - let me know and will go from there.
On the Secure connection - you will have to implement the SSL. If you are trying to have the public access - I will suggest implementing APEX listener as a separate web server other than DB and implement lot of security that way.
There are lots of secure way to access your app but then the flexibility exists in Linux/UNIX environments.
Hope this helps..