Upgrade APEX to Latest Version in Oracle Database

1. Stop database 
========================================================
srvctl status database -d APEXDB
srvctl stop database -d APEXDB

2. cold backup 
3. show parameter job queue processes 
4. download and copy software 
5. stop dataguard  <dr machine> - <username>

========================================================

6. Set parameter to disable data guard 
alter system set log_archive_dest_state_2=DEFER scope=both;  
alter system set db_recovery_file_dest_size=100G scope=both;  

7. Start Database and Check files details 
========================================================
set line 250 pagesize 1000
col FILE_NAME for a90
select FILE_ID, FILE_NAME , STATUS, BYTES/1024/1024/1024 GB from dba_data_files order by GB desc ;

select NAME from v$controlfile;

col MEMBER for a90
select member from v$logfile; 

select name from v$tempfile;

set lines 100
col name format a60
select 
   name,  floor(space_limit/1024/1024/1024) "Size GB",
  ceil(space_used/1024/1024/1024) "Used GB"
from   v$recovery_file_dest
order by    name;

8. CREATE RESTORE POINT
========================================================
select * from v$restore_point;

SQL> create restore point BEFORE_UPGRADE guarantee flashback database;

9. CHECK CURRENT Setup 
========================================================
col VERSION_NO for a30
col API_COMPATIBILITY for a30
col PATCH_APPLIED for a30
set lines 150
SELECT DBMS_XDB.GETHTTPPORT FROM DUAL;
select * from apex_release;

alter system set standby_file_management=AUTO scope=both sid='*';
alter database create datafile '/u01/app/oracle/product/12.1.0.2/dbhome_APEXDB/dbs/UNNAMED00025' as '+RECO_P/APEXDB/DATAFILE/apexdb.11482.968172777';


10. DISABLE APEX
========================================================
EXEC DBMS_XDB.SETHTTPPORT(0);    --- This command is enough  
exec DBMS_XDB_CONFIG.setHTTPSPort(0);
exec DBMS_XDB_CONFIG.setHTTPPort(0);
exec DBMS_XDB_CONFIG.setFTPPort(0);


SQL> STARTUP RESTRICT

11. INSTALL NEW APEX
========================================================

$ cd /u03/tmp/apex 

SQL> create tablespace apex19 datafile size 4g autoextend on next 100m ;

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

SQL> @apxchpwd.sql  

SQL> @apex_epg_config.sql /u03/tmp

SQL> ALTER USER ANONYMOUS ACCOUNT UNLOCK;


12. ENABLE APEX
========================================================
SQL> col VERSION_NO for a30
SQL> col API_COMPATIBILITY for a30
SQL> col PATCH_APPLIED for a30
SQL> set lines 150
SQL> SELECT DBMS_XDB.GETHTTPPORT FROM DUAL;
SQL> EXEC DBMS_XDB.SETHTTPPORT(8080); 
SQL> select * from apex_release;
SQL>

SQL> BEGIN
    DBMS_NETWORK_ACL_ADMIN.APPEND_HOST_ACE(
        host => '*',
        ace => xs$ace_type(privilege_list => xs$name_list('connect'),
                           principal_name => 'APEX_180100',
                           principal_type => xs_acl.ptype_db));
END;
/


13. RESTART DATABASE
========================================================
SQL> shut immediate

$ srvctl start database -d APEXDB

14. DELETE RESTORE POINT
========================================================
SQL> drop restore point BEFORE_UPGRADE;

15. DROP OLD APEX USER
========================================================
SQL > SELECT username  FROM dba_users  WHERE 
(  username LIKE 'FLOWS\_______' ESCAPE '\'       OR username LIKE 'APEX\_______' ESCAPE '\' )   
AND username NOT IN ( SELECT schema  FROM dba_registry   WHERE comp_id = 'APEX' );

SQL> DROP USER APEX_050000 CASCADE; -- drop old apex user 
SQL> EXEC DBMS_NETWORK_ACL_ADMIN.DELETE_PRIVILEGE('power_users.xml', 'APEX_050000');


SQL> EXEC DBMS_NETWORK_ACL_ADMIN.DELETE_PRIVILEGE('power_users.xml', 'APEX_050000');
BEGIN DBMS_NETWORK_ACL_ADMIN.DELETE_PRIVILEGE('power_users.xml', 'APEX_050000'); END;