APEX Installation,Uninstallation and Administration in Oracle Database

Hi,
As a DBA we might come across doing the Below task for APEX Component existing
in our Database from Oracle 11g onwards.

Before Uninstalling any component(apex) from Oracle Database(Oracle 11g rel2) discuss with team and inform clearly and once this is confirmed,than follow the below steps,By default in Oracle 11g rel2 Database APEX Component is installed (Apex version 3.2.1.00.10).

SQL> select *from v$version;

BANNER
-------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production

SQL>SELECT COMP_NAME||' '||VERSION||' '||STATUS FROM DBA_REGISTRY;

COMP_NAME||''||VERSION||''||STATUS
--------------------------------------------------------------------------------
OWB 11.2.0.1.0 VALID
Oracle Application Express 3.2.1.00.10 VALID
Oracle Enterprise Manager 11.2.0.1.0 VALID
OLAP Catalog 11.2.0.1.0 VALID
Spatial 11.2.0.1.0 VALID
Oracle Multimedia 11.2.0.1.0 VALID
Oracle XML Database 11.2.0.1.0 VALID
Oracle Text 11.2.0.1.0 VALID
Oracle Expression Filter 11.2.0.1.0 VALID
Oracle Rules Manager 11.2.0.1.0 VALID
Oracle Workspace Manager 11.2.0.1.0 VALID
Oracle Database Catalog Views 11.2.0.1.0 VALID
Oracle Database Packages and Types 11.2.0.1.0 VALID
JServer JAVA Virtual Machine 11.2.0.1.0 VALID
Oracle XDK 11.2.0.1.0 VALID
Oracle Database Java Packages 11.2.0.1.0 VALID
OLAP Analytic Workspace 11.2.0.1.0 VALID
Oracle OLAP API 11.2.0.1.0 VALID


Apex Uninstallation:
Apex uninstallation can be done as follows:
[oracle@node1 apex]$ cd $ORACLE_HOME/apex
SQL> @apxremov.sql
...Removing Application Express
old 1: alter session set current_schema = &APPUN
new 1: alter session set current_schema = APEX_030200

Session altered.
begin
*
ERROR at line 1:
ORA-04063: package body "APEX_030200.WWV_FLOW_UPGRADE" has errors
ORA-06508: PL/SQL: could not find program unit being called:
"APEX_030200.WWV_FLOW_UPGRADE"
ORA-06512: at line 2
no rows selected
old 2: if '&UPGRADE' = '1' then
new 2: if '1' = '1' then
begin
*
ERROR at line 1:
ORA-04063: package body "APEX_030200.WWV_FLOW_UPGRADE" has errors
ORA-06508: PL/SQL: could not find program unit being called:
"APEX_030200.WWV_FLOW_UPGRADE"
ORA-06512: at line 3

Session altered.

PL/SQL procedure successfully completed.

old 1: drop user &APPUN cascade
new 1: drop user APEX_030200 cascade

User dropped.

old 2: if '&UPGRADE' = '1' then
new 2: if '1' = '1' then
begin
*
ERROR at line 1:
ORA-01940: cannot drop a user that is currently connected
ORA-06512: at line 4

old 5: if '&UPGRADE' = '1' then
new 5: if '1' = '1' then
declare
*
ERROR at line 1:
ORA-24231: database access descriptor (DAD) APEX not found
ORA-06512: at "SYS.DBMS_SYS_ERROR", line 86
ORA-06512: at "SYS.DBMS_EPG", line 67
ORA-06512: at "SYS.DBMS_EPG", line 261
ORA-06512: at line 15

old 2: if '&UPGRADE' = '1' then
new 2: if '1' = '1' then
begin
*
ERROR at line 1:
ORA-04043: object VALIDATE_APEX does not exist
ORA-06512: at line 3


...Application Express Removed

After running the above script if some APEX Schemas and synonyms still exist in our Database than we have to drop them manually.Below is the query to find public synonym and drop them .Spool into some sql file,for making this task simpler.

SQL)Spool Drop_pub_synonym.sql

SQL>select 'drop public synonym ' || synonym_name || CHR(10) || '/'
from sys.dba_synonyms
where table_owner in ('FLOWS_010500','FLOWS_010600','FLOWS_020000',
'FLOWS_020100','FLOWS_020200','FLOWS_030000','FLOWS_030100','APEX_030200',
'APEX_040000','FLOWS_FILES');


'DROPPUBLICSYNONYM'||SYNONYM_NAME||CHR(10)||'/'
----------------------------------------------------
drop public synonym APEX_ACTIVITY_LOG
/

drop public synonym APEX_APPLICATION
/

drop public synonym APEX_APPLICATION_FILES
/

drop public synonym APEX_APPLICATION_GLOBAL
/

'DROPPUBLICSYNONYM'||SYNONYM_NAME||CHR(10)||'/'
----------------------------------------------------

drop public synonym APEX_APPLICATION_INSTALL
/

drop public synonym APEX_COLLECTION
/

drop public synonym APEX_COLLECTIONS
/

drop public synonym APEX_CSS

'DROPPUBLICSYNONYM'||SYNONYM_NAME||CHR(10)||'/'
----------------------------------------------------
/

drop public synonym APEX_CUSTOM_AUTH
/

drop public synonym APEX_DEBUG_MESSAGE
/

drop public synonym APEX_FEEDBACK_TYPES
/


'DROPPUBLICSYNONYM'||SYNONYM_NAME||CHR(10)||'/'
----------------------------------------------------
drop public synonym APEX_INSTANCE_ADMIN
/

drop public synonym APEX_ITEM
/

drop public synonym APEX_JAVASCRIPT
/

drop public synonym APEX_LANG
/

'DROPPUBLICSYNONYM'||SYNONYM_NAME||CHR(10)||'/'
----------------------------------------------------

drop public synonym APEX_LDAP
/

drop public synonym APEX_LOGIN
/

drop public synonym APEX_MAIL
/

drop public synonym APEX_MAIL_ATTACHMENTS

'DROPPUBLICSYNONYM'||SYNONYM_NAME||CHR(10)||'/'
----------------------------------------------------
/

drop public synonym APEX_MAIL_LOG
/

drop public synonym APEX_MAIL_QUEUE
/

drop public synonym APEX_PLSQL_JOB
/


'DROPPUBLICSYNONYM'||SYNONYM_NAME||CHR(10)||'/'
----------------------------------------------------
drop public synonym APEX_PLSQL_JOBS
/

drop public synonym APEX_PLUGIN
/

drop public synonym APEX_PLUGIN_UTIL
/

drop public synonym APEX_REST
/

'DROPPUBLICSYNONYM'||SYNONYM_NAME||CHR(10)||'/'
----------------------------------------------------

drop public synonym APEX_SITE_ADMIN_PRIVS
/

drop public synonym APEX_UI_DEFAULT
/

drop public synonym APEX_USER_ACCESS_LOG
/

drop public synonym APEX_UTIL

'DROPPUBLICSYNONYM'||SYNONYM_NAME||CHR(10)||'/'
----------------------------------------------------
/

drop public synonym APEX_WEB_SERVICE
/

drop public synonym HTMLDB_ACTIVITY_LOG
/

drop public synonym HTMLDB_APPLICATION
/


'DROPPUBLICSYNONYM'||SYNONYM_NAME||CHR(10)||'/'
----------------------------------------------------
drop public synonym HTMLDB_APPLICATION_FILES
/

drop public synonym HTMLDB_APPLICATION_GLOBAL
/

drop public synonym HTMLDB_COLLECTION
/

drop public synonym HTMLDB_COLLECTIONS
/

'DROPPUBLICSYNONYM'||SYNONYM_NAME||CHR(10)||'/'
----------------------------------------------------

drop public synonym HTMLDB_LDAP
/

drop public synonym HTMLDB_MAIL
/

drop public synonym HTMLDB_MAIL_LOG
/

drop public synonym HTMLDB_MAIL_QUEUE

'DROPPUBLICSYNONYM'||SYNONYM_NAME||CHR(10)||'/'
----------------------------------------------------
/

drop public synonym HTMLDB_PLSQL_JOB
/

drop public synonym HTMLDB_PLSQL_JOBS
/

all synonyms are dropped for apex users:
---------------------------------=------------

SQL> select synonym_name from sys.dba_synonyms where owner = 'FLOWS_FILES';
no rows selected

SQL>SPOOL OFF
SQL>@Drop_pub_synonym.sql
All the synonyms are dropped.
We have to make sure the below all the APEX related schemas are dropped or else drop them manually,In my case I upgraded to Apex 4.x so additional user APEX_040000 is also present:
SQL>DROP USER APEX_040000 CASCADE;
SQL>DROP USER APEX_PUBLIC_USER CASCADE;
SQLDROP USER FLOW_FILES CASCADE;
SQLDROP USER APEX_03000 CASCADE;
Now our Apex Uninstallation is successful.

Apex Installation:


1) Go to $ORACLE_HOME/apex path: For Oracle 11g rel2 Database apex component is already present when we install the software in our Operating system in '$ORACLE_HOME/apex' path.

$cd $ORACLE_HOME/apex
$pwd
/u01/oracle11g/product/11.2.0/dbhome_1/apex

2)Connect to SQL-PLUS and execute apexins for installing apex
SQL>@apexins SYSAUX SYSAUX TEMP /i/

Apply any upgraded Apex Component if required by following same process as above.

Apex Patching:
For patching the apex component,Run apxpatch.sql.
For example:
SQL>@apxpatch.sql

Apex ADMIN user password change:

We might get request from our APEX team to change the APEX ADMIN user password after Apex installation,We can change the APEX ADMIN user password by executing 'apxchpwd.sql'.

$cd $ORACLE_HOME/apex
SQL>@apxchpwd.sql
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.

Hope it helps in doing APEX administration.If I come across some other Apex
related task than I will be posting the same.


Best regards,

Rafi.