How to Manually Upgrade Oracle APEX from 4.2 to 20.2 and configure

Manually Upgrade APEX from oracle apex 4.2 to oracle apex 20.2

To upgrade to latest Application Express, download the latest version from the oracle metalink.

Upgrading APEX

1. Check the current APEX Version: (Existing or Installed Version)

SQL> Select Comp_name, status, Version From Dba_Registry where comp_id='APEX';

COMP_NAME                                STATUS       VERSION

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

Oracle Application Express               VALID        4.2.3.00.08

SQL>

2. From the directory which holds the APEX unzipped software, connect to sqlplus as SYS user and run apexins.sql

$ cd $ORACLE_HOME/apex

SQL> sqlplus /as sysdba

SQL> @apexins.sql APEX_SPACE APEX_SPACE TEMP /i/

3. Set Admin Password 

Now run the change password script for the ADMIN user as following and the password must follow the complexity rules, for example, I provided the password as Apx#123.

SQL> @apxchpwd.sql

4. Unlock the APEX_PUBLIC_USER  and other users

Unlock the APEX_PUBLIC_USER account and specify the password. Don’t forget to connect to Oracle using SQL*PLUS if disconnected. 

SQL> ALTER USER APEX_PUBLIC_USER ACCOUNT UNLOCK IDENTIFIED BY Apx#123;

SQL> ALTER USER APEX_LISTENER ACCOUNT UNLOCK IDENTIFIED BY Apx#123;

SQL> ALTER USER APEX_REST_PUBLIC_USER ACCOUNT UNLOCK IDENTIFIED BY Apx#123;

SQL> ALTER USER APEX_INSTANCE_ADMIN_USER ACCOUNT UNLOCK IDENTIFIED BY Apx#123;

SQL> ALTER USER APEX_200200 ACCOUNT UNLOCK IDENTIFIED BY Apx#123;

SQL> ALTER USER ORDSYS ACCOUNT UNLOCK IDENTIFIED BY Apx#123;

5. Run Configure Apex RESTful Service Script 

SQL> @apex_rest_config.sql

This will ask you to give APEX_LISTENER and APEX_PUBLIC_USER password to set which will be used later for the ORDS setup.

6. Check the upgraded APEX Version:

Select Comp_name, status, Version From Dba_Registry where comp_id='APEX';

COMP_NAME                                STATUS       VERSION

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

Oracle Application Express               VALID        20.2.0.00.20

SQL>

APEX has been successfully upgraded to version 20.2.0.00.20…

7. Configuration Oracle Application Express with ORDS:

Making required directory structure where ORDS server installation.

Here are the steps on Linux

ORDS Server

$ mkdir -p /cloaneappl/ords

$ mkdir -p /cloaneappl/ords/conf

$ cd /cloaneappl

$ unzip ords.3.0.12.263.15.32.zip -d /cloaneappl/ords

Note: Copy the images from where oracle apex(Database Installed) installed and past where ORDS Server.

In my case oracle apex and ORDS server are seprate.

Database Server

$ cd $ORACLE_HOME/apex (10.24.248.40 database server)

$ scp -r images applmgr@10.24.248.39:/cloaneappl/ords/

ORDS Server

$ java -jar apex.war configdir /cloaneappl/ords/conf

$ java -jar apex.war static /cloaneappl/ords/images

$ java -jar apex.war standalone(Provide images location first time)

$ nohup java -jar apex.war > startApex_15MAR23.log &

8. Deinstallation of ORDS

1. Deinstall the database schemas using

$ java –jar ords.war uninstall

In effect this removes the 2 schemas from the database

2. Optionally remove the ORDS installation directories

3. Optionally remove the ORDS tablespace from the database

Error: FRM-40501: ORACLE error: unable to reserve record for update or delete

This error message appears when the user tries to update a record in oracle e-business suite forms screen. Most of us knows the reason behind is that the same record is being updated by another user and the modifications have not been saved yet.

The below SQL can be used to list the details of the session which has locked any database object. You should be able to find the details of your record in this list.

WITH sessions_inline AS
 (SELECT client_identifier user_name
        ,sid seesion_id,SERIAL# serials#,(SELECT responsibility_name
          FROM   fnd_responsibility_vl
          WHERE  responsibility_key = SUBSTR(action
                                            ,INSTR(action 
                                                  ,'/'
                                                  ,1
                                                  ,1) + 1)) responsibility
        ,DECODE(SUBSTR(module
                      ,INSTR(module
                            ,':'
                            ,1
                            ,2) + 1
                      ,INSTR(module
                            ,':'
                            ,1
                            ,3) - INSTR(module
                                       ,':'
                                       ,1
                                       ,2) - 1)
               ,'frm'
               ,'Forms Screen'
               ,'wf'
               ,'Workflow'
               ,'cp'
               ,'Concurrent Process'
               ,'fwk'
               ,'Self Service Page'
               ,'bes'
               ,'Business Event'
               ,'gsm'
               ,'Workflow') usage_type
               ,SUBSTR(module
               ,INSTR(module
                     ,':'
                     ,1
                     ,3) + 1) ebs_module, ses.*
  FROM   v$session ses
  WHERE  1 = 1)
SELECT seesion_id
      ,SERIALS#
      ,obj.object_name
      ,user_name
      ,responsibility
      ,usage_type
      ,ebs_module
      ,DECODE(usage_type
             ,'Forms Screen'
             ,DECODE(ebs_module
                    ,'FNDSCSGN'
                    ,'EBS Navigator'
                    ,(SELECT user_form_name FROM fnd_form_vl WHERE form_name = ebs_module))) forms_screen
      ,DECODE(usage_type
             ,'Concurrent Process'
             ,(SELECT user_concurrent_program_name FROM fnd_concurrent_programs_vl WHERE concurrent_program_name = ebs_module)) concurrent_program
      ,DECODE(usage_type
             ,'Concurrent Process'
             ,(SELECT user_concurrent_queue_name FROM fnd_concurrent_queues_vl WHERE concurrent_queue_name = ebs_module)) concurrent_manager
FROM   sessions_inline ses
      ,v$locked_object lck
      ,all_objects     obj
WHERE  lck.session_id = ses.sid
AND    obj.object_id = lck.object_id
ORDER  BY ses.usage_type;

Above Query give the details object level lock. so we can run below query for checking next level.

select

   c.owner,

   c.object_name,

   c.object_type,

   b.sid,

   b.serial#,

   b.status,

   b.osuser,

   b.machine

from

v$locked_object a ,

v$session b,

dba_objects c

where b.sid = a.session_id

and a.object_id = c.object_id

and c.object_name='CE_SECURITY_PROFILES_GT';

In my case i am going to kill below lock.

SQL> alter system kill session '1144,10881';

System altered.

Issue resolved check and try again.