Monday, November 16, 2009

APEX: Install 3.2.1 on 11gR2

Yes, I know it comes pre-installed with 11g. No, I didn't bother to check what the default version was either. I needed practice so I removed it and re-installed it. This follows my recent install of 11gR2 on OEL v5.4.

First up, get the latest version from apex.oracle.com. I unzipped the file in a shared folder (which was then mounted to OEL) in the following directory (from OEL):
/mnt/software/oracle/apex/apex
In another window, I open up the installation doc.

From the database server, I moved to that directory and logged in as SYS. Here's the list of files in that directory:
[oracle@oracledb apex]$ ls -l
apex_epg_config_core.sql
apex_epg_config.sql
apexins.sql
apexvalidate.sql
apxchpwd.sql
apxconf.sql
apxdbmig.sql
apxdevrm.sql
apxdvins.sql
apxe101.sql
apxe102.sql
apxe111.sql
apxldimg.sql
apxpatch.sql
apxrelod.sql
apxremov.sql
apxrtins.sql
apxsqler.sql
apxxemig.sql
apxxepwd.sql
catapx.sql
coreins.sql
devins.sql
endins.sql
load_trans.sql
Since it's already installed, I want to remove it. To do so, I need to run apxremov.sql. I run it and a few minutes later its gone. I confirm this by looking in the DBA_REGISTRY view:
SELECT comp_name, version
FROM dba_registry
ORDER BY comp_name;


There are 4 basic installation scenarios:
  1. Downloading from OTN and configuring the embedded PL/SQL gateway (11g)
  2. Downloading from OTN and configuring Oracle HTTP Server (9.2.0.3 -- 11gR2)
  3. Installing from the database and configuring the embedded PL/SQL gateway (11g)
  4. Installing from the database and configuring the Oracle HTTP server (11g)
I'm going with #1.

I've removed the default installation of APEX, now I'm going to install it. I'm still logged in to SYS so I just need to run the apexins.sql script.
SQL>@apexins.sql SYSAUX SYSAUX temp /i/
The first parameter SYSAUX is the tablespace where APEX user will ive. The second SYSAUX is where the APEX files will live. TEMP is the name of the temporary tablespace and /i/ is the image (virtual) directory. Installation on my standalone machine took about 30 minutes.

The installation script logs you out at the end, so for the next step, you have to login as SYS account. Now we'll be setting the ADMIN account password. This password will be used here:



or here if you use INTERNAL as the workspace name (thanks John!):



Script to change the ADMIN password: apxchpwd.sql

Since this is technically an upgrade, I need to run apxldimg.sql
SQL> @apxldimg /mnt/software/oracle/apex

PL/SQL procedure successfully completed.

Directory created.

PL/SQL procedure successfully completed.

PL/SQL procedure successfully completed.

PL/SQL procedure successfully completed.

Commit complete.
timing for: Load Images
Elapsed: 00:02:33.48

Directory dropped.
Verify that the ANONYMOUS account is unlocked, if it isn't unlock it.
ALTER USER ANONYMOUS ACCOUNT UNLOCK;
We need to verify the EPG port now:
SELECT DBMS_XDB.GETHTTPPORT FROM DUAL;
If a 0 (zero) is returned, EPG is not set-up yet. I'm going to use port 8080 because I'm too lazy to figure out, for now, how to set it up on port 80.
EXEC dbms_epg.sethttpport( 8080 );
Finally we have to enable network services. Since this is my own sandbox, security is not a concern. I'm going with the default as provided by the documentation:
DECLARE
ACL_PATH VARCHAR2(4000);
ACL_ID RAW(16);
BEGIN
SELECT ACL
INTO ACL_PATH
FROM DBA_NETWORK_ACLS
WHERE HOST = '*'
AND LOWER_PORT IS NULL
AND UPPER_PORT IS NULL;

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_030200',
'connect') IS NULL
THEN
DBMS_NETWORK_ACL_ADMIN.ADD_PRIVILEGE(ACL_PATH,
'APEX_030200', TRUE, 'connect');
END IF;

EXCEPTION
WHEN NO_DATA_FOUND THEN
DBMS_NETWORK_ACL_ADMIN.CREATE_ACL
( 'power_users.xml',
'ACL that lets power users to connect to everywhere',
'APEX_030200',
TRUE,
'connect' );
DBMS_NETWORK_ACL_ADMIN.ASSIGN_ACL('power_users.xml','*');
END;
/
COMMIT;
You should be ready to go. The url will be http://localhost:8080/apex/apex_admin

If you are prompted with a XDB login window like this:



You may have forgotten to unlock the ANONYMOUS account. (I did, naturally).

3 comments:

Unknown said...

There's another alternative as well, called the APEX Listener, instead of using the EPG or Apache.

That will probably be the preferred way for APEX 4.0

Dimitri

oraclenerd said...

@dimitri

I've heard/read about it...I am looking forward to it. Will aid in selling it (APEX) to shops with Oracle and everything else OSS.

Unknown said...

Dimitri,

I followed your guild and was able to installed with success, but can't run the script at the finall part. Please the error I am getting:
DECLARE
*
ERROR at line 1:
ORA-01403: no data found
ORA-06512: at "SYS.DBMS_NETWORK_ACL_ADMIN", line 513
ORA-06512: at "SYS.DBMS_NETWORK_ACL_ADMIN", line 446
ORA-06512: at line 29
ORA-01403: no data found


SQL> COMMIT;

Commit complete.