3 Replies Latest reply on Apr 29, 2014 5:16 PM by Adam

    How can one clone an Application Express Instance without Cloning the Entire DB


      Hi there,


      This may be answered elsewhere but I have not been able to find it.  I have multiple Application Express Environments.  For purposes of this discussion lets just consider two environments.  One is a "production" environment where ApEx is the only thing the DB is used for.  The other is a "Development" environment which shares it's DB with another homegrown application.


      Periodically a refresh from production will be requested on this database.  How can I export the entire application express environment from the Prod Instance and replace that environment on the Dev Instance?  Are there specific schemas to export/import with Datapump?  Is there a documented procedure somewhere? 


      Please let me know how to do this...


        • 1. Re: How can one clone an Application Express Instance without Cloning the Entire DB
          Mike Kutz

          I don't understand what you mean by "the entire APEX environment".


          "APEX Environment" (as I see it) contains many components

          1. APEX itself is just some code that is stored in the APEX_{version} schema of the database.
          2. The applications that you build is stored in your Workspace .. which, itself, is just a schema in the database.
          3. For some, we move our PL/SQL Processes into Packages which are stored in a different schema
          4. Then, there is the data. (I store my data in multiple "data schemas")


          For some people, the "parsing schema" and "data schema" are the same schema.

          For others, they are different and are kept that way for security reasons. (this is how I do it)


          #1 should be identical on both servers

          #2 -- (depending on the number of apps) you may be better off doing export/import of the applications

          expdp/impdp of the workspace schema might work...but, it might just fubar stuff..


          I do not consider Data(#4) and Code(#3) as part of the APEX environment because APEX is just one application that uses them.

          Others applications written in C,c++,c#,java,cobol,fortran,ada,perl,python,ruby,R,P9,<place neatest new language here> need to access them too.


          So, when you say "the entire APEX environment", which one of those components do you really mean to 'refresh'?



          • 2. Re: How can one clone an Application Express Instance without Cloning the Entire DB

            Hi Adam,


            Your applications in production are really made up of two major elements:


            1)  The APEX applications and associated artifacts (e.g., images, CSS files - but hopefully in production, these would be on a Web server file system somewhere)


            2)  The application data structures and data.


            To replicate the underlying database objects, you'll want to do a normal DataPump export/import.  For the APEX applications themselves, you'll want to use the command-line utility APEXExport to export the workspace definitions and APEX application definitions, and then import these into dev.




            P.S.  You may think you can simply do a DataPump export of the APEX schema (e.g., APEX_040200), but that would not work when you tried to import it.  There are system privileges, grants on SYS objects, and a handful of objects in the SYS schema which would not come across when you attempted to do this.  Your best bet, as stated, is to use APEXExport of the workspaces and applications.

            • 3. Re: How can one clone an Application Express Instance without Cloning the Entire DB

              I think the workspace export it what I wanted but I had two different versions of ApEx.  Now that I upgraded the one that comes with Oracle Express to match my Enterprise Edition Instance of ApEx we seem to be ok.