Reinstall Apex (without loosing data)

Recently I have place that new version of Apex is published and very soon I got untended problem with installation. Here is mine situation and steps that I did:

1. Have successfully installed Apex 4.1
2. renamed "apex" directory to "apex_old"
3. Put new installation in apex directory (good praxis to have production version always in same dir)
4. Successfully upgraded to Apex 4.2 from "apex" directory
5. Tested through WEB that new Apex 4.2 is running. All looks nice and all old applications were converted to new version.

So, as any tedious DBA, wanted to remove old 4.1 version, with mine minds somewhere in new Apex interface ... not thinking actually what should I delete in real life.

The problem

Deletion action I perform in next steps:

1. Shutdown Oracle XMLDB HTTP server, that was upp and running
   EXEC DBMS_XDB.SETHTTPPORT(0);
2. Wanted to delete APEX_040100 schema using apex apxremov.sql script from "apex_old" directory. This is really script for removing, in mine case, obsolete APEX_040100 schema.
3. I run it and have notice that it removes not only APEX_040100 schema but FLOWS_FILES schema as well-and this was very bad for Apex 4.2.
4. Started Oracle XMLDB HTTP server
   EXEC DBMS_XDB.SETHTTPPORT(8080);
   gave me information that Apex WEB page is not running.

The problem arise to serious when I wanted to repeat Apex installation with apxrtins.sql script (again I'm in "apex" dir where 4.2 version is placed), I was told that 4.2 version is installed!

I.    O R A C L E   S Y S   I N S T A L L   P R O C E S S
...create flows user
create user APEX_040200 identified by "0MOYPMY31JG4XJBXNVODAWNCI7REFX" password expire account lock default tablespace APEX42 temporary tablespace TEMP
            *
ERROR at line 1:
ORA-01920: user name 'APEX_040200' conflicts with another user or role name 

The whole installation log file from this failed attempt may be downloaded from here

But if I remove the whole APEX_040200 schema, as advised, I'll loose all mine installed workspaces with mine applications and have to install them manually again after.

This was not a bright idea for me in this moment and wanted to avoid it if anyhow possible.

The Solution

When I analyzed install script I find that there are few places where previous installation is checked. So editing them help me a lot. Here are scripts that you should TEMPORARY modify.

Edit apxprereq.sql

Edit 104 line that exception is not raised. So change (showing the whole block for easy reference):

begin
    if c_install_type not in ('ADD_DEV') then
        for c1 in (
            select null from sys.dba_users where username = upper('^APPUN')
        ) loop
            dbms_output.put_line('Error: This version of Application Express is already installed (^APPUN).');
            execute immediate 'bogus statement to force exit';
        end loop;
    end if;
end;
/

to

begin
    if c_install_type not in ('ADD_DEV') then
        for c1 in (
            select null from sys.dba_users where username = upper('^APPUN')
        ) loop
            dbms_output.put_line('Error: This version of Application Express is already installed (^APPUN).');
-- comment this line            execute immediate 'bogus statement to force exit';
        end loop;
    end if;
end;
/

Edit coreins.sql

Edit 956 line that create user is not performed. So change (showing the whole block for easy reference):

create user ^APPUN identified by "^ADM_PWD" password expire account lock default tablespace ^DATTS temporary tablespace ^TEMPTBL;

to

-- comment this line create user ^APPUN identified by "^ADM_PWD" password expire account lock default tablespace ^DATTS temporary tablespace ^TEMPTBL;

You might commented instead line 954
whenever sqlerror exit
... but I prefer mine way, which are in this case the same.

Premature finishing on 10g

On 10g databases I found out that if you run install in thgis moment with your modified installation, it would probably end like:

Application Express Packaged and Sample Applications installation data

...Remove existing packaged applications from SGID11 workspace

begin
*
ERROR at line 1:
ORA-04091: table APEX_040200.WWV_FLOWS is mutating, trigger/function may not
see it
ORA-06512: at "APEX_040200.WWV_FLOW_LOAD_TABLES_T1", line 21
ORA-04088: error during execution of trigger
'APEX_040200.WWV_FLOW_LOAD_TABLES_T1'
ORA-06512: at "APEX_040200.WWV_FLOW_API", line 1400
ORA-06512: at line 11

Interesting, this step is not known on 11g databases (have tested personally) but only on 10g. Workaround is to modify core\trigger.sql script (lines 769-781):

...
    --
    -- last updated
    --
   if not wwv_flow.g_import_in_progress then
        :new.last_updated_on := sysdate;
        :new.last_updated_by := nvl(wwv_flow.g_user,sys_context('USERENV','CURRENT_USER'));
        if inserting then
            :new.created_on := sysdate;
            :new.created_by := nvl(wwv_flow.g_user,sys_context('USERENV','CURRENT_USER'));
        end if;
        update wwv_flows set
           last_updated_on = sysdate,
           last_updated_by = nvl(wwv_flow.g_user,sys_context('USERENV','CURRENT_USER'))
        where
           id = :new.flow_id and
           security_group_id = :new.security_group_id;
    end if;
end;
/

and comment part of trigger which was causing error:

    --
    -- last updated
    --
--   if not wwv_flow.g_import_in_progress then
--        :new.last_updated_on := sysdate;
--        :new.last_updated_by := nvl(wwv_flow.g_user,sys_context('USERENV','CURRENT_USER'));
--        if inserting then
--            :new.created_on := sysdate;
--            :new.created_by := nvl(wwv_flow.g_user,sys_context('USERENV','CURRENT_USER'));
--        end if;
--        update wwv_flows set
--           last_updated_on = sysdate,
--           last_updated_by = nvl(wwv_flow.g_user,sys_context('USERENV','CURRENT_USER'))
--        where
--           id = :new.flow_id and
--           security_group_id = :new.security_group_id;
--    end if;
end;
/

Repeat Apex installation

Now you are able to repeat Apex installation. This installation will make a lot of errors on creation of objects, but this is not a problem because all of them are the same from previous successful upgrade. As an example here is mine 11g installation log file from such a installation. And here is 10g installation log file for comparasion.
When installation finished with end like:

PL/SQL procedure successfully completed.

timing for: Validate Installation
Elapsed: 00:01:51.74
old   1: alter session set current_schema = ^APPUN
new   1: alter session set current_schema = APEX_040200

Session altered.

timing for: Complete Installation
Elapsed: 00:16:31.99

PL/SQL procedure successfully completed.

do not forget to edit previously modified wwv_flow_load_tables_t1 trigger and un comment previously commented part.

SQL> alter session set current_schema = APEX_040200;

Session altered.

SQL> CREATE OR REPLACE TRIGGER APEX_040200.wwv_flow_load_tables_t1
  2      before insert or update ON APEX_040200.WWV_FLOW_LOAD_TABLES
  3      for each row
  4  begin
  5      if inserting and :new.id is null then
  6          :new.id := wwv_flow_id.next_val;
  7      end if;
  8      --
  9      -- vpd
 10      --
 11      if :new.security_group_id is null then
 12         :new.security_group_id := nvl(wwv_flow_security.g_security_group_id,0);
 13      end if;
 14      --
 15      -- last updated
 16      --
 17      --vadas
 18      if not wwv_flow.g_import_in_progress then
 19          :new.last_updated_on := sysdate;
 20          :new.last_updated_by := nvl(wwv_flow.g_user,sys_context('USERENV','CURRENT_USER'));
 21          if inserting then
 22              :new.created_on := sysdate;
 23              :new.created_by := nvl(wwv_flow.g_user,sys_context('USERENV','CURRENT_USER'));
 24          end if;
 25          update wwv_flows set
 26             last_updated_on = sysdate,
 27             last_updated_by = nvl(wwv_flow.g_user,sys_context('USERENV','CURRENT_USER'))
 28          where
 29             id = :new.flow_id and
 30             security_group_id = :new.security_group_id;
 31      end if;
 32  end;
 33  /

Trigger created.

SQL> show errors;
No errors.
SQL> 

Configure the embedded PL/SQL gateway

In mine case I was using embedded PL/SQL gateway, so this step should be repeated as well.

SQL> @apex_epg_config.sql c:\oracle\product\11.2.0\dbhome_2

PL/SQL procedure successfully completed.


PL/SQL procedure successfully completed.

old   1: create directory APEX_IMAGES as '&1/apex/images'
new   1: create directory APEX_IMAGES as 'c:\oracle\product\11.2.0\dbhome_2/apex/images'

Directory created.

old  47:     if '&IMGUPG' != '' then
new  47:     if '' != '' then
old  48:         l_mv_folder := '&IMGUPG';
new  48:         l_mv_folder := '';

PL/SQL procedure successfully completed.


Commit complete.


PL/SQL procedure successfully completed.


PL/SQL procedure successfully completed.

timing for: Load Images
Elapsed: 00:01:23.64

Session altered.


PL/SQL procedure successfully completed.


Commit complete.


Session altered.


Directory dropped.

If you use standalone WEB server you might skip this test because all images should be already placed in appropriate folder on WEB server.
Start Oracle XMLDB HTTP server again:

SQL> EXEC DBMS_XDB.SETHTTPPORT(8080);

PL/SQL procedure successfully completed.

SQL> commit;

Commit complete.

SQL>

And, even it is not required, run recompile database with more then known utlrp.sql script, to ensure that all objects are compiled properly.

The End

Testing WEB page shows that Apex is again safe and sound and running in all shine!
Do not forget to return changes in scripts.
Even thought this method is run on latest Apex, should be able to perform on any version (haven't tested!)-but lines and changes in script may vary. Test by yourself mine idea and place some responds if find appropriate.

Hope this helps someone.
Cheers!