APEX Install & Upgrade

I will discuss APEX 4.1 install and upgrade from 3.2 in 11gR2 environment. There are some stuff those are important and I will make a note of that. Version 3.2 comes pre-install in 11gR2. There is another version between 3.2 and 4.1 and that is 4.0.


1- Please download the APEX 4.1  The download URL is http://www.oracle.com/technetwork/developer-tools/apex/downloads/index.html


[ This is APEX. Will discuss APEX listener as one of the way to access the APEX among others. I will post them in this blog with different post]


2- unzip this in a directory. In my case it is /stage/apex/* - after download and unzip you will get the apex directory under which everything is.


Install:


Steps for Install are straight forward. Please note that the steps for upgrade and install are same and they only differ at the end (post install).


1- create  tablespaces  APEX_DATA, APEX_FILES, and APEX_TEMP. I choose to do that you could install it it SYSAUX & TEMP as it comes installed there by default.


2- cd /stage/apex and login to oracle as sysdba


3- at the SQL prompt issue following command:


SQL>@apexins APEX_DATA APEX_FILES  APEX_TEMP /i/


The last argument is how the images will be referenced when called from the web servers.


Here you have a choice of having full development environment or a runtime environment.


apexins.sql = Full Development ENvironment
apxrtins.sql = Runtime Environment


We can always change back and forth between these environments by using following scripts as:

apxdevrm.sql = converts a full development environment to a Runtime environment

apxdvins.sql = converts a Runtime environment to a full development environment.

4- Now we will build the images directory so that we could use in the web server. Login as sysdba and execute following.


SQL>apxldimg.sql /stage


Please note that the images directory is /stage/apex/images for me - so I give the argument as /stage. In any case give the base directory where apex was unzipped.

It will load/build images directory and will create /stage/apex/images/build directory. Now we cas tar the /stage/apex/images directory and keep it.


5- We will now unlock the APEX_PUBLIC_USER and change it's password by

SQL>alter user APEX_PUBLIC_USER  account unlock;

SQL>alter user APEX_PUBLIC_USER  identified by apex41;


This is our proxy login username that then access APEX_04100 - the APEX schema.


6- Now we will change the ADMIN password (Its optional for Upgrade as if you know the old password then there is no need to change). Please note that this is the temporary password and at first login via the browser it need to be changed - so choose the temporary password at this time and not the real one. login as sysdba and run following command:


SQL>apxchpwd.sql 


I typed temp as password at the prompt.


Please note that 11gR2 has default profile that has the password expire time as 180 days. You have 2 options - one disable this in default profile or make a different profile with other expiry time or disable time.



   a- disable default profile password time (As sysdba).


     SQL> alter profile default limit password_life_time unlimited;

  b- Create as (just an example)


    SQL>  create profile developer limit password_life_time 365;

     Profile created.


Here is the difference in my development environment. I changed the default to unlimited and developer to 365.



SQL> select * from dba_profiles where profile in ('DEFAULT','DEVELOPER') order by profile;




PROFILE    RESOURCE_NAME             RESOURCE LIMIT
---------- ------------------------- -------- --------------------
DEFAULT    COMPOSITE_LIMIT           KERNEL   UNLIMITED
DEFAULT    PASSWORD_LOCK_TIME        PASSWORD 1
DEFAULT    PASSWORD_VERIFY_FUNCTION  PASSWORD NULL
DEFAULT    PASSWORD_REUSE_MAX        PASSWORD UNLIMITED
DEFAULT    PASSWORD_REUSE_TIME       PASSWORD UNLIMITED
DEFAULT    PASSWORD_LIFE_TIME        PASSWORD UNLIMITED
DEFAULT    FAILED_LOGIN_ATTEMPTS     PASSWORD 10
DEFAULT    PRIVATE_SGA               KERNEL   UNLIMITED
DEFAULT    CONNECT_TIME              KERNEL   UNLIMITED
DEFAULT    IDLE_TIME                 KERNEL   UNLIMITED
DEFAULT    LOGICAL_READS_PER_CALL    KERNEL   UNLIMITED
DEFAULT    LOGICAL_READS_PER_SESSION KERNEL   UNLIMITED
DEFAULT    CPU_PER_CALL              KERNEL   UNLIMITED
DEFAULT    CPU_PER_SESSION           KERNEL   UNLIMITED
DEFAULT    SESSIONS_PER_USER         KERNEL   UNLIMITED
DEFAULT    PASSWORD_GRACE_TIME       PASSWORD 7
DEVELOPER  PASSWORD_GRACE_TIME       PASSWORD DEFAULT
DEVELOPER  COMPOSITE_LIMIT           KERNEL   DEFAULT
DEVELOPER  PASSWORD_REUSE_TIME       PASSWORD DEFAULT
DEVELOPER  IDLE_TIME                 KERNEL   DEFAULT
DEVELOPER  PASSWORD_VERIFY_FUNCTION  PASSWORD DEFAULT
DEVELOPER  LOGICAL_READS_PER_CALL    KERNEL   DEFAULT
DEVELOPER  PASSWORD_LIFE_TIME        PASSWORD 365
DEVELOPER  LOGICAL_READS_PER_SESSION KERNEL   DEFAULT
DEVELOPER  PASSWORD_LOCK_TIME        PASSWORD DEFAULT
DEVELOPER  CPU_PER_CALL              KERNEL   DEFAULT
DEVELOPER  FAILED_LOGIN_ATTEMPTS     PASSWORD DEFAULT
DEVELOPER  CPU_PER_SESSION           KERNEL   DEFAULT
DEVELOPER  PASSWORD_REUSE_MAX        PASSWORD DEFAULT
DEVELOPER  SESSIONS_PER_USER         KERNEL   DEFAULT
DEVELOPER  PRIVATE_SGA               KERNEL   DEFAULT
DEVELOPER  CONNECT_TIME              KERNEL   DEFAULT

32 rows selected.






7- Now we need to run ACL to get the connect access to APEX040100 from the hosts as:






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_040100', 
     'connect') IS NULL THEN 
      DBMS_NETWORK_ACL_ADMIN.ADD_PRIVILEGE(ACL_PATH, 
     'APEX_040100', 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_040100', TRUE, 'connect');
  DBMS_NETWORK_ACL_ADMIN.ASSIGN_ACL('power_users.xml','*');
END;
/
COMMIT;




The above gives access to all. However if you want limit the access - you can do sometjing like:



DECLARE
  ACL_PATH  VARCHAR2(4000);
  ACL_ID    RAW(16);
BEGIN
  SELECT ACL INTO ACL_PATH FROM DBA_NETWORK_ACLS
   WHERE HOST = 'localhost' 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_040100', 
     'connect') IS NULL THEN 
      DBMS_NETWORK_ACL_ADMIN.ADD_PRIVILEGE(ACL_PATH, 
     'APEX_040100', TRUE, 'connect'); 
  END IF;

EXCEPTION
   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;

8- If other language are needed then run following after making sure that the nls_lang is set to American_America.AL32UTF8.

SQL>connect as sysdba

SQL>@load_trans.sql GERMAN -- Suppose you need the GERMAN lang. ALso make sure that you download the APEX 4.1 in that language and unzip it and then from /stage/lang/apex run above.

9- Now set the nob_queue_process parameter to an appropriate value mine is set to 40.

Now you are ready to access the APEX.

I will post the access via tomcat7, APEX listener and EPG in comping posts.

Upgrade:

All the steps are same as install. You may not run the ADMIN password change step and then you need to run the following script to find out which schema to remove. In this case it will be APEX_030200. However, please note that the following script also print the workspace user those you might have created with APEX prefix. Please be careful of as what to remove. I only removed APEX030200.

SQL> SELECT username

   FROM dba_users 
 WHERE (username LIKE 'FLOWS_%' OR USERNAME LIKE 'APEX_%') 
   AND USERNAME NOT IN (
        SELECT 'FLOWS_FILES' 
          FROM DUAL 
         UNION 
        SELECT 'APEX_PUBLIC_USER' FROM DUAL 
         UNION
        SELECT SCHEMA s 
           FROM dba_registry
         WHERE comp_id = 'APEX');

SQL> drop user APEX_030200 cascade;

SQL>EXEC DBMS_NETWORK_ACL_ADMIN.DELETE_PRIVILEGE('power_users.xml', 'APEX_030200');

That is all for this post. Will follow up with web server to access APEX.