Oracle DBA Workshop: APEX

Monday, March 11, 2019

ORA-01653 error while upgrading APEX from 4.2 to 5.0 and Application Express is already installed (APEX_050000)

Trying to upgrading from 4.2 to 5.0

Steps followed

- Initiated @apexins.sql APEX SYSAUX TEMP /i/
- In the middle of the upgrade I got failed due to
ERROR at line 1:
ORA-01653: unable to extend table APEX_050000.WWV_FLOW_UPGRADE_PROGRESS by 8 in
tablespace APEX
ORA-06512: at "APEX_050000.WWV_FLOW_UPGRADE", line 973
ORA-06512: at "APEX_050000.WWV_FLOW_UPGRADE", line 1659
ORA-01658: unable to create INITIAL extent for segment in tablespace APEX
ORA-06512: at "APEX_050000.WWV_FLOW_UPGRADE", line 3715
ORA-06512: at line 4

- I have added more space to APEX
- Now tried to initiated @apexins.sql APEX SYSAUX TEMP /i/ but I got Error: This version ofApplication Express is already installed (APEX_050000).

How to fix this issue?

I am getting "Error: This version of Application Express is already installed" error when i am re initiating apex upgrade, so commented "execute immediate 'bogus statement to force exit';" line in "apxprereq.sql"

Next I got "ORA-01920: user name 'APEX_040200' conflicts with another user or role name", this time commented "create user ^APPUN identified by "^ADM_PWD" password expire account lock default tablespace ^DATTS temporary tablespace ^TEMPTBL;" line in "coreins.sql"

Now initiate upgrade using "@apexins.sql APEX SYSAUX TEMP /i/"

Everything went fine. And all existing applications working fine with new look and feel in APEX5.

Friday, March 1, 2019

How to upgrade APEX from 4.2 to 5.0?

Pre-Installation checks

select comp_name, version from dba_registry;

COMP_NAME VERSION

-------------------------------------------------- --------------------------

Oracle Enterprise Manager 11.2.0.3.0

Oracle Application Express 4.2.6.00.03

OWB 11.2.0.3.0

OLAP Catalog 11.2.0.3.0

Spatial 11.2.0.3.0

Oracle Multimedia 11.2.0.3.0

Oracle XML Database 11.2.0.3.0

Oracle Text 11.2.0.3.0

Oracle Expression Filter 11.2.0.3.0

Oracle Rules Manager 11.2.0.3.0

Oracle Workspace Manager 11.2.0.3.0

COMP_NAME VERSION

-------------------------------------------------- --------------------------

Oracle Database Catalog Views 11.2.0.3.0

Oracle Database Packages and Types 11.2.0.3.0

JServer JAVA Virtual Machine 11.2.0.3.0

Oracle XDK 11.2.0.3.0

Oracle Database Java Packages 11.2.0.3.0

OLAP Analytic Workspace 11.2.0.3.0

Oracle OLAP API 11.2.0.3.0

select account_status from dba_users where username = 'APEX_PUBLIC_USER';

ACCOUNT_STATUS

--------------------------------

OPEN

select account_status from dba_users where username = 'ANONYMOUS';

ACCOUNT_STATUS

--------------------------------

OPEN

select default_tablespace, temporary_tablespace from dba_users where username = 'APEX_040200';

DEFAULT_TABLESPACE TEMPORARY_TABLESPACE

------------------------------ ------------------------------

APEX TEMP

The script we need to run to do the installation – apexins.sql – takes three parameters :

The default tabelspace of the APEX owner schema
The default tablespace of FLOWS_FILES schema
A temporary tablespace in the database
A virtual directory for APEX images.

We already know that the default tablespace for the APEX owner is APEX

We also know that the temporary tablespace is called TEMP

As for the FLOWS_FILES schema…

select default_tablespace from dba_users where username = 'FLOWS_FILES';

DEFAULT_TABLESPACE

------------------------------

SYSAUX

cd /media/mnt01/app/oracle/product/11.2.0/db_1/apex5.0/apex

@apexins.sql APEX SYSAUX TEMP /i/

@apxchpwd.sql

@apxldimg.sql /media/mnt01/app/oracle/product/11.2.0/db_1/apex5.0

For embedded PL/SQL Gateway

@apex_epg_config.sql /media/mnt01/app/oracle/product/11.2.0/db_1/apex5.0

select dbms_xdb.gethttpport from dual;

http://hostname:port/apex/apex_admin