06 December 2021

Remove Apex from 12.1 CDB and PDBs

When Apex is installed on 12.1 Oracle DB and you see it in the CDB and PDB's, take care when removing it.  I have found the below steps usefull. In the Oracle documentation and on several blogs the apxremov.sql is simply run on the CDB and that should be it. But if it is installed in all PDB's too (not used, just installed) it wont work removing it like that.

Main steps to remove it completely, details below:
  1. Remove from all PDBS
  2. Remove from PDB$SEED  (otherwise it will give errors when creating new PDBs)
  3. Remove from CDB

If you don't follow this order, you risk that damaging your database and need to recover/restore.

Note:

You need to run the _oracle_script parameter to prevent the ORA-28014 error.
ORA-28014: Cannot Drop Administrative Users (Doc ID 1566042.1)

Details:

-- From CDB :
create restore point BEF_APEX_REMOVE guarantee flashback database;

An extra backup of database is highly recommended.

1 Remove from all PDBs (one-by-one):


-- ======== Generic removal ===========
alter session set "_oracle_script"=true;
@?/apex/apxremov.sql

-- cleanup invalid synonyms (clean.sql):
-- You may pre-create the clean.sql file
set lines 120
set heading off feedback off
spool drinvpub.tmp
select 'ALTER SESSION SET "_oracle_script"=true;' from dual;
select 'drop public synonym '|| object_name ||';'
from dba_objects
where owner='PUBLIC'
and status='INVALID'
and (object_name like 'APEX%'
 or object_name like 'WWV_FLOW%'
 or object_name like 'WWV_%'
 or object_name like 'HTMLDB%'
 or object_name like 'DEVELOPMENT_SERVICE_%'
 or object_name in ('V','F','Z','NV','P','WS')
)
;
spool off
set heading on
set feedback on

@drinvpub.tmp

--- Check:
col comp_name format a40
select comp_name,status from dba_registry

2.  Remove from PDB$SEED

-- on CDB run:
alter session set "_oracle_script"=true;
alter pluggable database PDB$SEED close;
alter pluggable database PDB$SEED open read write;
alter session set container=PDB$SEED;
-- Now Follow the Generic Removal
alter pluggable database PDB$SEED close;
alter pluggable database PDB$SEED open read only;

3. Remove from CDB

-- For the CDB follow the steps as under PDB


No comments:

Post a Comment