Forum Stats

  • 3,770,705 Users
  • 2,253,155 Discussions


Maintaining data & data schema between Development vs Production?

DannyS-Oracle Member Posts: 165
edited Jun 16, 2017 3:54AM in APEX Discussions

Hi everyone, I am a beginner Apex developer and need some advice on how to sync between my development and production instances. I tried searching on this forum, and this has been asked before here , but there was no feedback.

So my situation is, we are planning to have two different instance of our application:

  1. Development, where we do all experiment and add new features; and
  2. Production, where we put all stable features, production data, and make it accessible for our users.

We are thinking to put these two instances on different workspaces, so we can safely export and import all pages easily and separate development vs production data.

The case where I am concerned is, how to keep the data schema between the development and production workspaces in sync? Let's say I implemented some features in dev environment, where I need to make changes to certain table (e.g. adding/removing columns or triggers in Table A). How to reflect this changes automatically in the production environment? I believe I can't just export the data, replace the data schema with the one from dev, and then re-import the data because some table columns might have changed. Do I have to implement all those changes manually in the production workspace?

Please let me know if you have been in similar situation, or if you know better solution for this. Any feedback will be appreciated! I am using Apex v5.1, Oracle DB v12.



  • TexasApexDeveloper
    TexasApexDeveloper Member Posts: 7,814 Gold Crown
    edited Jun 15, 2017 7:01PM

    You should be using version control for your database objects and if need be you should build scripts to update the objects in beta/prod when you need to migrate the changes.  Remember version control is developers friend, don't develop without it!!

    Thank you,

    Tony Miller

    Los Alamos, NM

  • DannyS-Oracle
    DannyS-Oracle Member Posts: 165
    edited Jun 15, 2017 7:26PM

    Hi Tony, thanks for the reply! I agree that version control is necessary here. I guess I am still trying to figure out the detailed steps - please bear with me

    So far here is the best practice from what I found so far, including questions I still have:

    1. Have 2 separate workspace: Dev and Prod. I heard that both of apps in Dev and Prod should have the same App ID - what is the purpose of this?
    2. Whenever a feature / DB change is ready to release, check-in the Application export script / DDL scripts to the version control. This step is fairly straightforward.
    3. We import the scripts to the prod environment. I have some confusion here:
      1. For application export (pages, shared components), should I delete the previous production application entirely, to replace it with the script? Or is there a way to automatically check diff and merge the differences? I assume it is the former one (delete and replace), because this will make the idea of having same APP IDs between Dev and Prod environments much more understandable.
      2. Same question for DDL script: should I purge the database in prod and then replace the tables, functions, triggers, etc with the ones from script? Is there a way to merge automatically?
      3. How to manage the production data during this rollout process? Is there a way to generate DML script (which allow me to do INSERT data after the database tables reconstructed) in Apex? Also, what if the table columns changed in the new release, how can I merge the production data to this new table structure?

    Any idea will be appreciated!

  • ATK
    ATK Member Posts: 3
    edited Jun 16, 2017 3:54AM

    Hi Danny,

    To create a stable and reliable deployment process for APEX you need more than one Database/APEX instance. With one single database and APEX you cannot have same app ID in two different workspaces. Also it is a risk of updating the wrong table in wrong schema and damage production data.

    Ideally, you need 3 different servers, and Database, (1)Dev, (2)Test and (3)Production. You do all your development on Dev. Create a seperateSQL file contain all table alter and DML.Put all together and create a script to run them on Test. Run this deployment script on Test, do a full test and UAT, then deploy App to production with using this tested script.

    On 12c you can use pluggable databases (PDBs) and install multiple APEX each on separate PDB. Then configure ORDS to connect to each and you find different URL for each environment. It is not a simple newbie task but you can study more here.

    For generate alter scripts, if you use Oracle SQLDeveloper (here), when you do a change in a table, it gives you alter script as well. And you can export all changed packages or procedures. Also, you could use third party tools or Database Diff in Oracle SQL Developer to generate alter scripts by comparing two databases.


This discussion has been closed.