Installing the Oracle Application Express Software on 11i

Installation Steps:
1. Setup R11i Instance and make sure all the components are working fine.

2. Login as Oracle Database User of the 11i Instance.

3. Make a directory to hold apex_2.2.zip

4. Move apex_2.2.zip to the directory created

5. Unzip apex_2.2.zip

6. sqlplus sys/SYS_password as sysdba

7. Create a Tablespace for apex22 on the 11i Database.
SQL> create tablespace apex22 datafile '/oracle/Apps/proddata/apex22.dbf'
size 100m autoextend on next 50m maxsize 2048m extent management local;

8. SQL> @apexins.sql welcome apex22 apex22 temp /i/ UPGRADE (here welcome is the apex_password and UPGRADE is the SID of the database)
Syntax :apexins
welcome- is the password for the Oracle Application Express administrator account.
apex22- is the name of the tablespace for the Oracle Application Express application user
apex22- is the name of the tablespace for the Oracle Application Express files user.
temp - is the name of the temporary tablespace.
/i/- is the virtual directory for Oracle Application Express images. Later copied to /IAS_ORACLE_HOME/Apache/images
UPGRADE- is the Oracle Net connect string to the database.

SQL> @apexins welcome apex22 apex22 TEMP /i/ upgrade

. Application Express Installation.
.....
.....
.....
.....

$tail -f (logfile_name)
Thank you for installing Oracle Application Express.

Oracle Application Express is installed in the FLOWS_020200 schema.

The structure of the link to the Application Express administration services is as follows:
http://host:port/pls/apex/apex_admin

The structure of the link to the Application Express development interface is as follows:
http://host:port/pls/apex

-- 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. -------
ERRORS EXIST!!!
...There are 10 errors in the log table!
...Select upgrade_error from WWV_FLOW_UPGRADE_PROGRESS to review errors.
-- Upgrade is complete -----------------------------------------
timing for: Upgrade
Elapsed: 00:01:28.95
JOB_QUEUE_PROCESSES: 10
Completing registration process.
Validating installation.
FAILED CHECK FOR PACKAGE BODY WWV_FLOW_CUSTOM_AUTH_SSO
timing for: Validate Installation
Elapsed: 00:00:02.28
timing for: Complete Installation
Elapsed: 00:12:25.01
Disconnected from Oracle9i Enterprise Edition Release 9.2.0.7.0 - Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.7.0 - Production

Post Installation Steps:
1. Stop Application 11i using adstpall.sh

2. Recompiling Invalid PL/SQL Packages
SQL> @?/rdbms/admin/utlrp.sql

3. Copying the Images Directory to 11i Middle Tier
cp -rf apex/images $IAS_ORACLE_HOME/Apache/images

4. Configuring Oracle HTTP Server
Oracle Application Express must have access to Oracle HTTP Server with mod_plsql

4.1: Modifying the wdbsvr.app File
vi $IAS_ORACLE_HOME/Apache/modplsql/cfg/wdbsvr.app

4.2: Comment "custom_auth=CUSTOM"

[WVGATEWAY]
defaultDAD = UPGRADE
administrators = system
adminPath = /admin_/
; custom_auth = CUSTOM
;

4.3: Add an entry for Oracle Application Express using the following syntax
[DAD_apex]
connect_string = genius:1576:upgrade
password = apex_password (apex_password during install here it is welcome)
username = apex_public_user (Retain this as apex_public_user)
default_page = apex
document_table = wwv_flow_file_objects$
document_path = docs
document_proc = wwv_flow_file_mgr.process_download
reuse = Yes
enablesso = No
stateful = STATELESS_RESET
nls_lang = American_America.UTF8
$

Apex_password must be the same as we give during @apexins.sql
NLS language particulars must meet the characterset of database.

4.4: Modify the plsql.conf File
vi $IAS_ORACLE_HOME/Apache/modplsql/cfg/plsql.conf

4.5: Add or Check the following entry to the end of the file and save

SetHandler pls_handler
Order deny,allow
Allow from all

4.6: Modify the security.conf File
vi $IAS_ORACLE_HOME/Apache/Apache/conf/security.conf

4.7: Add or check the following entry between and Tag and save
# Allow for partial urls for default pages. i.e. /pls/sid or /pls/sid/
SecFilterSelective "REQUEST_URI" ^/pls/[-a-z0-9_]+(/){0,1}$ "allow,nolog"


4.8: Modify the httpd.conf File
vi $IAS_ORACLE_HOME/Apache/Apache/conf/httpd.conf

4.9: Add the following entry to the httpd.conf and save
Alias /i/ "/oracle/Apps/prodora/iAS/Apache/Apache/images/"
AddType text/xml xbl
AddType text/x-component htc

5. Start Application 11i using adstrtall.sh from $COMMON_TOP/admin/scripts/SID_hostname/

6. Applying the PL/SQL Web Toolkit Patch
Apply the PL/SQL Web Toolkit patch to every database where you wish to run ApEx 2.2
Review the README.txt file contained in the directory apex/patch/bug4554072

7. If JOB_QUEUE_PROCESSES is not enabled and working properly, you cannot successfully execute a script.

We have completed Apex Installation on Release 11i.
ADMIN URL :http://genius.chainsys.com:8055/apex/apex_admin


Some Troubleshooting :
I forgot the admin password and used the following procedure to recover it;


SQL> alter user FLOWS_020200 account unlock;

User altered.

SQL>
SQL> alter user FLOWS_020200 identified by FLOWS_020200;

User altered.

SQL> connect FLOWS_020200/FLOWS_020200
Connected.
SQL> begin
wwv_flow_api.set_security_group_id(p_security_group_id=>10);
wwv_flow_fnd_user_api.create_fnd_user(
p_user_name => 'adminnew',
p_email_address => 'balaji.rs@chain-sys.com',
p_web_password => 'adminnew') ;
end;
/

PL/SQL procedure successfully completed.

SQL> commit;

Commit complete.

SQL> alter user FLOWS_020200 account lock;

User altered.

SQL>


Now logged in as adminnew user
>> Manage Workspace
>> Existing Workspace
>> Select INTERNAL
>> Manage USERS
>> Select ADMIN and Reset the Password

Now login as ADMIN and delete ADMINNEW

If you want to delete and reinstall Apex:

To remove schemas after a failed new installation:

1. Start SQL*Plus and connect the database where Oracle Application Express is installed as SYS or SYSTEM.

2. Execute the following commands:

drop user FLOWS_020200 cascade;
drop user FLOWS_FILES cascade;