11 Replies Latest reply: Jun 21, 2011 8:41 AM by 791487 RSS

    Move APEX Installation from one database to another one

    518602
      My task is to move an APEX installation to a new one.
      Exact: The customer wants to downgrade from Enterprise Edition to Standard Edition.
      Database version is 9.2.0.8 (both), APEX version is 2.2.1

      What I've done:
      - Full database export (from EE)
      - Delete EE-software, reboot server
      - Install. SE-software, reboot server, create db
      - Full database import
      around 40 db objects are invalid after recompile
      :-(
      --------------------------------------------------------------------------------------------------------
      2. try:
      - create db (SE)
      - Install. of APEX 2.2.1
      - Import of the schemas flows_020200, flows_files and the APEX user-schemas
      - recompile
      all db objects are valid, but the APEX applications belongs to a workspace "unknown" so no workspace have access to its applications
      :-(

      Has anybody an idea or a recipe for me?

      Many thanks
      Ralph
        • 1. Re: Move APEX Installation from one database to another one
          60437
          Ralph,

          I'd go back to method 1 and examine the 40 invalid objects. That might be easy to fix. Can you show a list?

          Scott
          • 2. Re: Move APEX Installation from one database to another one
            518602
            Scott,

            thanks for your reply!

            Okay, I'd tried method 1 again. Now I used the script from Metalink note 97902.1 and now are "only" two objects invalid:

            OBJECT_NAME//OBJECT_TYPE//OWNER
            WWV_FLOW_CUSTOM_AUTH_STD//PACKAGE BODY//FLOWS_020200
            WWV_FLOW_SECURITY//PACKAGE BODY//FLOWS_020200

            I've no idea how to get them valid. Also I'm not able to call the login-page of APEX.
            • 3. Re: Move APEX Installation from one database to another one
              Jes
              Hi Ralph,

              What does the Apache error log say?

              John
              • 4. Re: Move APEX Installation from one database to another one
                438381
                Ralph:

                From SqlPlus, logged in as the 'FLOWS_020200' user issue the query below

                select name,text from user_errors
                where name in ('WWV_FLOW_CUSTOM_AUTH_STD','WWV_FLOW_SECURITY');

                Please post the output of this query here.

                Varad
                • 5. Re: Move APEX Installation from one database to another one
                  518602
                  I've got it.

                  Here is the complete recipe:

                  - Full database export
                  - On the source db, as user system, run this script:

                  set hea off
                  set pagesize 0
                  set feedback off
                  spool objs_upg.sql
                  select 'grant '||privilege||' on '||owner||'.'||
                  table_name||' to '||grantee||' '||
                  decode(grantable,'YES','WITH Grant option')||';'
                  from dba_tab_privs
                  where owner = 'SYS'
                  /
                  select 'grant '||privilege||' ('||column_name||') '||
                  ' on '||owner||'.'||table_name||' to '||grantee||' '||
                  decode(grantable,'YES','WITH Grant option')||';'
                  from dba_col_privs
                  where owner = 'SYS'
                  /

                  - Create the new db (target), with all the needed tablespaces, but leave it empty (no APEX installation)
                  - Full database import

                  On the target db, as user sys:
                  - recompile with ORACLE_HOME\rdbms\admin\utlrp.sql
                  - run the above created script objs_upg.sql
                  - recompile with ORACLE_HOME\rdbms\admin\utlrp.sql
                  - from the APEX installation disk: run the scripts wwv_flow_val.sql and wwv_flow_val.plb
                  - recompile with ORACLE_HOME\rdbms\admin\utlrp.sql
                  - unlock the account flows_020200

                  On the target db, as user flows_020200:
                  - from the APEX installation disk: run the scripts custom_auth_std.sql and custom_auth_std.plb

                  On the target db, as user sys:
                  - recompile with ORACLE_HOME\rdbms\admin\utlrp.sql
                  - lock the account flows_020200

                  Now everything should be fine.

                  Maybe this recipe also is usable to copy a full APEX installation from SE to SE (or EE to EE).


                  Thank you all for your help!!!

                  Regards
                  Ralph

                  Message was edited by:
                  RalphM
                  • 6. Re: Move APEX Installation from one database to another one
                    JP Kreps
                    Hi, All --

                    I’ve performed database migrations before, but I’m about to perform my first one involving Apex applications. We're relatively new to Apex at our shop, so I’m uncertain how to handle the complications that Apex introduces to a DB migration.

                    MY SCENARIO:

                    On our source server we have Oracle DB 10.2.0.2 (with HTTP Server) and Apex version 3.0.1.00.07. Call it “SOURCEDB”.

                    On our target server we have Oracle DB 10.2.0.3 (with HTTP Server). Call it “TARGETDB”.

                    We want to migrate all the SOURCEDB Apex apps to TARGETDB and upgrade them to Apex 3.1.

                    I’ve tried RalphM's excellent suggestions in this thread. I have some observations and questions that I want to run by the Apex experts in this forum before I proceed with my own migration. Here they are:

                    OBSERVATIONS:

                    Given the original scenario of this thread (migrate to installation with same Oracle DB/Apex version), RalphM's suggestions should work. But things get trickier if you migrate to a target installation with newer Oracle DB/Apex version.

                    A full database import on TARGETDB will bring over the objects originally created by the Apex install on SOURCEDB, thus making it unnecessary to run the script “apexins.sql” to install Apex on TARGETDB.

                    However, when migrating to an installation with a newer version of the Oracle DB, a full database import is perilous because it will corrupt the newer versions of the Oracle-generated schemas/users on TARGETDB with objects from the older version’s Oracle-generated schemas/users on SOURCEDB. You must run a script that recreates the non-Oracle-generated schemas/users with their SYS grants on TARGETDB, and then run a user-to-user import. But by doing this user-to-user import, you can't import all the objects in the Oracle-generated schemas created or modified by the Apex installation process. So they must be created or modified on TARGETDB by running the "apexins.sql" script.

                    By the term "Oracle-generated schemas", I’m referring to those schemas which are created by installing or patching Oracle Database software and can themselves be altered by subsequent patches.

                    Also, I’ve found that to use this technique to migrate Apex apps from SOURCEDB to TARGETDB with a newer Apex version, you must first migrate your Apex apps to TARGETDB with the same version of Apex, and then upgrade the TARGETDB Apex installation to the later version.

                    PROPOSED PLAN:

                    Assuming my observations are correct, I start with a freshly installed Oracle database version 10.2.0.3 (TARGETDB). Then, on TARGETDB, I do the following:

                    1) Run the “apexins.sql” script to install the same version of Apex that exists on SOURCEDB (in my case, Apex 3.0.1).

                    2) Drop the newly created FLOWS_XXXXXX users.

                    3) Run a script that recreates on TARGETDB all non-Oracle-generated schemas/users that exist on SOURCEDB along with their SYS grants. This creates empty FLOWS_XXXXXX schemas corresponding to the ones dropped in Step #2.

                    4) Run a user-to-user import from SOURCEDB. This brings in the objects from the original FLOWS_XXXXXX schemas on SOURCEDB into their corresponding schemas on TARGETDB.

                    5) As SYS@TARGETDB run “utlrp.sql” to recompile all database objects.

                    6) As SYS@TARGETDB run scripts “wwv_flow_val.sql”, “wwv_flow_val.plb”.

                    7) As SYS@TARGETDB run “utlrp.sql” to recompile all database objects.

                    8) As FLOWS_030000@TARGETDB run scripts “custom_auth_std.sql” and “custom_auth_std.plb”.

                    9) As SYS@TARGETDB run “utlrp.sql” to recompile all database objects. Now the SOURCEDB Apex apps exist in TARGETDB version 10.2.0.3 and Apex 3.01.

                    10) As SYS@TARGETDB run the Apex 3.1 script “apexins.sql” to upgrade the TARGETDB Apex installation from version 3.01 to version 3.1.

                    11) As SYS@TARGETDB run “utlrp.sql” to recompile all database objects. Now the SOURCEDB Apex apps exist in TARGETDB version 10.2.0.3 and Apex version 3.1.

                    QUESTIONS:

                    But all this is based on the assumption that the functionality of the Apex scripts mentioned in Steps #6 and #8 (“wwv_flow_val.sql”, “wwv_flow_val.plb”, “custom_auth_std.sql”, and “custom_auth_std.plb”) have not been altered as Apex has changed from version 2.2.1 to version 3.01 to version 3.1. Is this assumption correct? What do these scripts do exactly? It’s hard to tell by looking at their listings.

                    I know that I can migrate Apex apps by using the Apex Admin Web utility to manually export/import Workspaces between two Apex installations. But the plan that I describe above seems more efficient, especially when dealing with a large number of Apex apps/workspaces. Would you consider it to be a practical alternative?

                    If you notice any fundamental misunderstanding on my part or have any other suggestions, corrections, or clarifications, please let me know! I'd appreciate your help. Thanks!

                    -- JP
                    • 7. Re: Move APEX Installation from one database to another one
                      60437
                      JP,

                      I can't certify your plan. It sounds like RalphM and you have had some success with his outline but I never fully understood it, especially about wwv_flow_custom_auth_std. I recommend that you install Application Express 3.0.1 in the targetdb, export all the workspaces, applications, and related stuff from each workspace, then import install them into the targetdb. Test everything you need to to be satisfied nothing's broken, then upgrade the targetdb to 3.1.

                      There are differences between 10.2.0.2 and 10.2.0.3 in which Application Express objects get compiled and when you get to 3.1, lots of stuff changes with respect to what's in the SYS schema, and so on.

                      So that's what I recommend.

                      Scott
                      • 8. Re: Move APEX Installation from one database to another one
                        JP Kreps
                        Hi, Scott –

                        Thanks for your post on this thread last Friday evening, May 2. I posted my message on Wednesday April 30, and after receiving no response after two days, we decided to go ahead with my planned migration on Friday afternoon. We completed it shortly after 5:00 PM (just six hours before you posted your response). Everything seemed to go well, but based on your expert opinion, we've decided to scrap the TARGETDB installation and redo the migration according to your recommendations.

                        It seems that exporting each Apex application individually from the SOURCEDB and importing it individually to the TARGETDB would be a laborious and lengthy process, but if Oracle provides no faster and more convenient alternative, then I guess this is something we’ll have to do.

                        However, this has sparked more questions….

                        Like I said in my previous post, we’re relatively new to Apex. When I mentioned exporting/importing Apex workspaces, I was imprecise in my terminology. I know you can export Apex applications from the SOURCEDB Apex installation and import them into the TARGETDB Apex installation using the Web based Apex Admin tool. But can/should you do the same with workspaces? Or do you recreate corresponding empty Apex workspaces on the TARGETDB and then import the appropriate Apex applications into them?

                        What would you suggest to be the best way to do this?

                        Thanks again for your help!

                        --JP                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                           
                        • 9. Re: Move APEX Installation from one database to another one
                          60437
                          It's easiest to export the workspaces using the Service Administration application (apex_admin). If you don't export workspaces but simply create new ones in the target db, you won't have any user accounts in the workspaces -- not ideal. These files can then be imported/installed using the same tool on the target db or you can connect as flows_xxxxxx and run them in SQL*Plus. This method preserves the workspace IDs also so that application export files can be easily run using SQL*Plus as well (allowing you to not use the web interface to import/install applications).

                          Scott
                          • 10. Re: Move APEX Installation from one database to another one
                            JP Kreps
                            Hi, Scott --

                            Thanks for the quick response. I'm scrapping the TARGETDB today and rebuilding it so that we can migrate the Apex apps in the approved way.

                            I appreciate your help, Scott. Thanks!

                            -- JP
                            • 11. Re: Move APEX Installation from one database to another one
                              791487
                              Hi,

                              I have got a scenario:

                              I have a database running on unix server A having database D1 (10.2.0.3) and with the APEX version 3.1.2.00.02.

                              I have to migrate this APEX applicaiton from server A to server B (unix) having database D2 (newly created 11.2.0.2) and APEX version 3.2.1.00.12 (which is installed by default while creating the database D2).

                              Kindly let me know, how to proceed on this ?


                              Your advice is highly appreciated.

                              Thanks and Regards,

                              Jacob