Home Administration Removing Oracle Application Express (APEX) on 19c database fails

Removing Oracle Application Express (APEX) on 19c database fails

Oracle APEX Release 21.1.0 was released on May 12, 2021. My 19c Oracle Database came with Oracle Application Express Release 18.1. I had a requirement to install the latest version of Apex on our 19c Oracle Database. Oracle recommends to containerize Apex installs within a PDB for easier manageability. So, I decided to remove the default Apex version from the CDB (including PDB) so I can then install the latest version of Apex in the PDB.

Issue

Removing Oracle Application Express (APEX) on 19c database fails with below error.

SQL*Plus: Release 19.0.0.0.0 - Production on Tue May 18 21:03:13 2021
Version 19.9.0.0.0

Copyright (c) 1982, 2020, Oracle.  All rights reserved.


Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.9.0.0.0

SQL> @apxremov_cdb.sql

PL/SQL procedure successfully completed.

Performing de-installation in multitenant container database in the background.
The de-installation progress is spooled into apxremov*_cdb*.log files.

Please wait...

/bin/bash: ^OH_HOME/perl/bin/perl: No such file or directory

/bin/bash: ^OH_HOME/perl/bin/perl: No such file or directory


De-installation completed. Log files for each container can be found in:

apxremov*_cdb*.log

You can quickly scan for ORA errors or compilation errors by using a utility
like grep:

grep ORA- *.log
grep PLS- *.log

Cause of the problem

The new script apxremov_cdb.sql is missing a line that causes an issue with the substitution variables used in the script. It is missing set define '^'

Fix

Backup the $ORACLE_HOME/apex/apxremov_cdb.sql and edit the sql file to add the below line after all the REM statements.

set define '^'

The edited script should look like this.

Rem  Copyright (c) Oracle Corporation 1999 - 2014. All Rights Reserved.
Rem
Rem    NAME
Rem      apxremov_cdb.sql
Rem
Rem    DESCRIPTION
Rem      Removes Application Express in a multitenant container database.
Rem
Rem    NOTES
Rem      Assumes the SYS user is connected. Must be run locally to the database and the
Rem      ORACLE_HOME environment variable must be set.
Rem      You must exit the SQL*Plus session prior to running apexins_con.sql
Rem
Rem    REQUIRENTS
Rem      - Oracle Database 12.1.0.1 or later
Rem
Rem
Rem
Rem    MODIFIED   (MM/DD/YYYY)
Rem      jstraub   09/05/2012 - Created
Rem      jstraub   06/17/2013 - Updated to call scripts separately that drop object and then drop schemas
Rem      jstraub   06/30/2014 - Changed references from installation to de-installation
Rem      jstraub   11/21/2014 - Adapted from apxremov_con.sql

set define '^'
whenever sqlerror exit

column :xe_home new_value OH_HOME NOPRINT
variable xe_home varchar2(255)

set serverout on
begin
-- get oracle_home
    sys.dbms_system.get_env('ORACLE_HOME',:xe_home);
    if length(:xe_home) = 0 then
        sys.dbms_output.put_line(lpad('-',80,'-'));
        raise_application_error (
            -20001,
            'Oracle Home environment variable not set' );
    end if;
end;
/
whenever sqlerror continue

set termout off
select :xe_home from sys.dual;
set termout on

prompt Performing de-installation in multitenant container database in the background.
prompt The de-installation progress is spooled into apxremov*_cdb*.log files.
prompt
prompt Please wait...
prompt

host ^OH_HOME/perl/bin/perl -I ^OH_HOME/rdbms/admin ^OH_HOME/rdbms/admin/catcon.pl -b apxremov1_cdb apxremov1.sql
host ^OH_HOME/perl/bin/perl -I ^OH_HOME/rdbms/admin ^OH_HOME/rdbms/admin/catcon.pl -b apxremov2_cdb apxremov2.sql

prompt
prompt De-installation completed. Log files for each container can be found in:
prompt
prompt apxremov*_cdb*.log
prompt
prompt You can quickly scan for ORA errors or compilation errors by using a utility
prompt like grep:
prompt
prompt grep ORA- *.log
prompt grep PLS- *.log
prompt

Proceed to uninstall

Now proceed to uninstall from the CDB using the edited file. It should now uninstall Apex from the CDB and the PDBs. Here’s the log of the uninstall.

SQL*Plus: Release 19.0.0.0.0 - Production on Tue May 18 22:13:17 2021
Version 19.9.0.0.0

Copyright (c) 1982, 2020, Oracle.  All rights reserved.


Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.9.0.0.0

SQL> @apxremov_cdb.sql

PL/SQL procedure successfully completed.

Performing de-installation in multitenant container database in the background.
The de-installation progress is spooled into apxremov*_cdb*.log files.

Please wait...

catcon::set_log_file_base_path: ALL catcon-related output will be written to [/u01/app/oracle/product/19.0.0.0/dbhome_1/apex/apxremov1_cdb_catcon_114949.lst]

catcon::set_log_file_base_path: catcon: See [/u01/app/oracle/product/19.0.0.0/dbhome_1/apex/apxremov1_cdb*.log] files for output generated by scripts

catcon::set_log_file_base_path: catcon: See [/u01/app/oracle/product/19.0.0.0/dbhome_1/apex/apxremov1_cdb_*.lst] files for spool files, if any

catcon.pl: completed successfully

catcon::set_log_file_base_path: ALL catcon-related output will be written to [/u01/app/oracle/product/19.0.0.0/dbhome_1/apex/apxremov2_cdb_catcon_126912.lst]

catcon::set_log_file_base_path: catcon: See [/u01/app/oracle/product/19.0.0.0/dbhome_1/apex/apxremov2_cdb*.log] files for output generated by scripts

catcon::set_log_file_base_path: catcon: See [/u01/app/oracle/product/19.0.0.0/dbhome_1/apex/apxremov2_cdb_*.lst] files for spool files, if any

catcon.pl: completed successfully


De-installation completed. Log files for each container can be found in:

apxremov*_cdb*.log

You can quickly scan for ORA errors or compilation errors by using a utility
like grep:

grep ORA- *.log
grep PLS- *.log

References

Removing the Oracle Application Express Release 18.1 Schema from a CDB

Previous articleSQLs slow in 19c

LEAVE A REPLY

Please enter your comment!
Please enter your name here

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Latest posts

Removing Oracle Application Express (APEX) on 19c database fails

Oracle APEX Release 21.1.0 was released on May 12, 2021. My 19c Oracle Database came with Oracle...

SQLs slow in 19c

Issue We patched our Oracle databases to 19.9 and this issue started. Some of...

Steps to remove OEM Agent

Recently I migrated all targets (Cluster, Databases, Instances, ASM, etc) and their jobs to a 13c OEM...

DBCA fails with ORA-27125 on 19c

Issue I recently encountered an issue where DBCA failed with ORA-27125 when creating a...

You might also likeRELATED
Recommended to you