Introduction

Recently two new databases set up had been checked by QA process and they did not make that check due to the fact that Apex was setup in the sysaux tablespace. Of course it is important to follow the standards so some  extra work  would be needed to pass  the checks . House rules state that for Apex installation a dedicated tablespace will be needed. Time to play and make things right with two scenarios.  Scenario 1 will be faulty installation with a deinstall and an install after. Scenario 2 will be an install( During which action noticed that Oracle text is a mandatory component that needs to be in place before install Apex). Below notes I have used  to investigate and fix the challenge. As always  Google is you friend when exploring such solutions.

Being curious by nature of course had to explore two approaches. Primarily installing a new part to an already existing database can be done with DBCA. As another option it is still a good challenge to see if you can do it yourself by using the command line too. Hmm does it still show that I grew up with Oracle 7.3.4 as a starters kit ?

Details

First lets explore if there is a current installation of Apex in place.

col  comp_name format a60
set lines 144
select COMP_NAME,status from dba_registry order by 1;

or

SELECT STATUS FROM DBA_REGISTRY
WHERE COMP_ID = 'APEX';

VERSION
------------------------------
3.2.1.00.12

Deinstall

As part of activities looked on  the Web and found below Blog.  Big Thank you to the author Matthias Hoys for documenting:

De-installing Apex in 11gr2

So in my case would have set Environment to Oracle 11gr2 and  move to the apex directory before  starting sqlplus .

cd /opt/oracle/product/11203_ee_64/db/apex/

sqlplus /nolog
connect / as sysdba
@/opt/oracle/product/11203_ee_64/db/apex/apxremov.sql

Once the scripts has completed check again:

SELECT STATUS FROM DBA_REGISTRY
WHERE COMP_ID = 'APEX';

no rows selected

Now let’s do it proper and create a dedicated tablespace for apex installation first:

create tablespace APEX datafile ‘+CRMMST_DATA01’ size 4096M autoextend off
EXTENT MANAGEMENT LOCAL SEGMENT SPACE MANAGEMENT  AUTO UNIFORM SIZE 1M;

Note. If you settle for  uniform size tablespace, make sure your extend-size is big enough. I had the issue that install will fail  if extend size is too small (apex install will fail with  ORA-60019). Error Detail:

create table wwv_mig_forms (
*
ERROR at line 1:
ORA-60019: Creating initial extent of size 14 in tablespace of extent size 8

ORA-60019: Creating initial extent of size string in tablespace of extent size string
Cause: Creation of SECUREFILE segment failed due to small tablespace extent size.
Action: Create tablespace with larger extent size and reissue command.
## my ts was uniform size 64K when created dedicated ts. Recreated ts with uniform size 1M.

Install Apex manually  (with Oracle Text in place):

## make sure you are in this directory:
cd /opt/oracle/product/11203_ee_64/db/apex 
@/opt/oracle/product/11203_ee_64/db/apex/apexins apex apex temp /i/

Since more than one script will run, made sure to head to the Apex directory in my Oracle installation before running  the starting script in sqlplus.  My new created dedicated  tablespace apex  is part of  parameters needed. ( @apexins.sql tablespace_apex tablespace_files tablespace_temp images ).

 

Install Apex manually (with Oracle Text  not in place):

In case Oracle Text is not installed in the database, the apex install will complain about that. In such case you will have to install Oracle Text first.

Oracle Text:

cd /opt/oracle/product/11203_ee_64/db/ctx/admin

Start your sqlplus session and run:

spool /tmp/catctx.log
@catctx.sql CTXSYS SYSAUX TEMP NOLOCK;

Among the parameters username,tablespace to create objects into, temp, account status after install.

After that installed Apex:

## make sure you are in this directory:
cd /opt/oracle/product/11203_ee_64/db/apex 
@/opt/oracle/product/11203_ee_64/db/apex/apexins apex apex temp /i/

## check status :

SQL> SELECT STATUS FROM DBA_REGISTRY
WHERE COMP_ID = ‘APEX’;  2

STATUS
———–
VALID

## Another issue and blushing when in the end  turned out i had proper tablespace NOT in place. Ran the script with the parameters but the tablespace had not been created:

 

APPLICATION 4411 - APEX  - System Messages Set Credentials... begin * ERROR at line 1: 
ORA-04063: package body "APEX_030200.WWV_FLOW_API" has errors ORA-06508: PL/SQL: could not find program unit being called: 
"APEX_030200.WWV_FLOW_API" ORA-06512: at line 4 
## and did notice this package remained invalid even after remove part of apex. 
Solution: drop package htmldb_system; drop public synonym htmldb_system; 
Retry.

Mandatory Aftercare (last man standing).
Check for invalid objects and recompile them.
select owner ,object_name,object_type from dba_objects where  status <> ‘VALID’ order by 1,2;

@?/rdbms/admin/utlrp

and check again .

 

Happy reading,

 

Mathijs

 

Btw. Loved this post by one of the great bloggers in general and on this Apex  in particular :

Oracle-Base oracle-application-express-apex-4-2-installation