Upgraded - APEX 3.2 ->4.1

I posted about APEX 4.1 was released & sample - install & configure with Embedded PL/SQL Gateway. I was curious to upgrade APEX on Oracle 11.2.0.2(version: 3.2.1.00.12).

SQL> select * from v$version;

BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production
PL/SQL Release 11.2.0.2.0 - Production
CORE 11.2.0.2.0 Production
TNS for Linux: Version 11.2.0.2.0 - Production
NLSRTL Version 11.2.0.2.0 - Production

SQL> select version from dba_registry where comp_id='APEX';
VERSION
------------------------------
3.2.1.00.12

SQL> create tablespace APEX datafile '+DATA' size 200M autoextend on next 20M maxsize unlimited;

Tablespace created.

SQL> @apexins APEX APEX TEMP /i/

Session altered.

. ____ ____ ____ ____
. / \ | \ /\ / | /
.| || / / \ | | |
.| ||--- ---- | | |--
.| || \ / \ | | |
. \____/ | \/ \ \____ |____ \____
.
. Application Express (APEX) Installation.
..........................................
.
... Checking prerequisites
.
.
.
-- Now beginning upgrade. This will take several minutes.-------
-- Ensuring template names are unique -------
-- Migrating metadata to new schema -------
-- Switching builder to new schema -------
-- Migrating SQL Workshop metadata -------
-- Upgrading new schema. -------
-- Copying preferences to new schema. -------
-- Upgrading Websheet objects. -------
Upgrade completed successfully no errors encountered.
-- Upgrade is complete -----------------------------------------
timing for: Upgrade Elapsed: 00:02:41.37
.
.
.
...Compiled 690 out of 2826 objects considered, 0 failed compilation 10:36:24
...247 packages
...239 package bodies
...436 tables
...12 functions
...19 procedures
...3 sequences
...438 triggers
...1238 indexes
...184 views
...0 libraries
...6 types
...0 type bodies
...0 operators
...0 index types
...Begin key object existence check 10:36:24
...Completed key object existence check 10:36:24
...Setting DBMS Registry 10:36:24
...Setting DBMS Registry Complete 10:36:24
...Exiting validate 10:36:24
timing for: Validate Installation
Elapsed: 00:02:44.86
timing for: Development Installation
Elapsed: 00:18:19.60

SQL> select version from dba_registry where comp_id='APEX';

VERSION
------------------------------
4.1.0.00.32

SQL> @apxchpwd
Enter a value below for the password for the Application Express ADMIN user.

Enter a password for the ADMIN user []

Session altered.

...changing password for ADMIN

PL/SQL procedure successfully completed.

Commit complete.

SQL> @apex_epg_config /tmp

PL/SQL procedure successfully completed.

PL/SQL procedure successfully completed.

Directory created.

PL/SQL procedure successfully completed.

Commit complete.

PL/SQL procedure successfully completed.

PL/SQL procedure successfully completed.

timing for: Load Images
Elapsed: 00:02:09.27

Session altered.

PL/SQL procedure successfully completed.

Commit complete.

Session altered.

Directory dropped.

SQL> ALTER USER ANONYMOUS ACCOUNT UNLOCK;

User altered.

SQL> SELECT DBMS_XDB.GETHTTPPORT FROM DUAL;

GETHTTPPORT
-----------
0

SQL> EXEC DBMS_XDB.SETHTTPPORT(8080);

PL/SQL procedure successfully completed.

SQL> !lsnrctl status | grep 8080
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=mytest)(PORT=8080))(Presentation=HTTP)(Session=RAW)

*** then you can run script to Enable Network Services in Oracle Database 11g ****

DECLARE
ACL_PATH VARCHAR2(4000);
ACL_ID RAW(16);
BEGIN
-- Look for the ACL currently assigned to '*' 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 = '*' 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 '*'.
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;

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_041000',
'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;

Tested !!!