- Have successfully installed Apex 4.1
- renamed "apex" directory to "apex_old"
- Put new installation in apex directory (good praxis to have production version always in same dir)
- Successfully upgraded to Apex 4.2 from "apex" directory
- Tested through WEB that new Apex 4.2 is running. All looks nice and all old applications were converted to new version.
The problem
Deletion action I perform in next steps:- Shutdown Oracle XMLDB HTTP server, that was upp and running
EXEC DBMS_XDB.SETHTTPPORT(0); - 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.
- 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.
- Started Oracle XMLDB HTTP server
EXEC DBMS_XDB.SETHTTPPORT(8080);
gave me information that Apex WEB page is not running.
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 11Interesting, 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!
