2 Replies Latest reply: Dec 9, 2013 7:38 AM by marcusafs RSS

    Migrating Designer 10g and Oracle Database 11g EE Data Dictionary to SDDM 4.0

    marcusafs

      I have been working with SDDM 4.0 and it looks like the best candidate to replace our current Oracle Designer Repository  We are a small shop with 2 DBA's and only internal customers.  I have developed a couple of small designs from scratch as a proof of concept. 

       

      Now I need to load all of our current designs into SDDM and synchronize them with the existing data dictionaries.. We are not using versioning in Designer but plan on versioning in SDDM in lieu of a repository and to maintain development and production branches.

       

      The forum thread Advice/Recommendation please? How to best handle big models with various schemas? was a big help and I plan to implement most of that approach but I need advice on merging and versioning everything.  I initially worked with Subversion with SDDM 3.3 about a year ago but decided to defer Subversion until I had time to work with the tool without that extra complication.  Now I need to bite the subversion bullet and I am hoping to avoid obvious dead ends with the help of the community.

       

      Current State Of Designer

      1 work area

      13 Application Systems for custom applications

      1 Implementation Application System for deployment from DB Admin tab

      4 Oracle Implementation Databse (11.2.0.3) pairs (DEV/PROD)

      1 MS SQLServer Implementation Database (2008 R2) (only one database and 4 tables so this is not critical and can be manually synched)

       

      Current State of the database

      Designer Repository is not completely in synch with the Oracle data dictionaries. 

      There are development modifications in Designer *not* implemented in any database.

      There are development modifications in Designer  implemented in any database.

      There are development modifications in Designer implemented in the development database

      Development database has differences that are not in the repository (Not all modifications made in the database are in Designer).

      Production database has differences that are not in the repository (Not all modifications made in the database are in Designer).

      Development and Production databases also have differences

       

      What should be the order of importing and how do I synchronize Designer, Development DB and Production DB?  What is possible? At what stages should I merge and check into Subversion?  I am trying not to get wrapped around the axle.  I have a month or so to get this done. 

      A) Import one, merge it, import the next and merge it and import the last and merge that into one Relational, version then engineer to logical? 

      B) Import them into different Relational Models, Engineer to one Logical, Engineer Logical to a new Relational and version

      C) Some other approach?

        • 1. Re: Migrating Designer 10g and Oracle Database 11g EE Data Dictionary to SDDM 4.0
          Philip Stoyanov-Oracle

          Hi Marcus,

           

          at the end decision is yours, I'll  put some points here:

          1) Start with import from designer repository

          2) Put design into SVN repository, do not leave uncommitted changes before each import/sync step - at least you'll have clear history and can start again from point back in time

          13 Application Systems for custom applications

          3) If Data Modeler can handle those systems in one relational model then follow that path (increase amount of memory dedicated to DM) - subview will be created for each system (folder) and that subview can be used as filter for set of objects you need to include into generated DDL

          4 Oracle Implementation Databse (11.2.0.3) pairs (DEV/PROD)

          4) I don't know what are differences in the scope of one pair and between 4 implementations of pairs - you can create 8 RDBMS sites Oracle 11g and map them to 8 databases you have or you can maintain smaller number of physical models

          and use name substitution during DDL generation

           

          5) Differences

          Use DDL generation configurations - for each target save DDL configuration which represents settings (including name substitution) and selected objects to be included in DDL for that target. Use that configuration when generate DDL again for that target.

          DDL configurations can be used when synchronize (compare) to database or another model - in Compare models dialog - "Options>Compare options" - check the flag "Use Generate in DDL setting to filter out objects" and refresh the tree - then you'll see only objects

          included in that DDL configuration (you can load it there) - basically you can filter out tables, PK and UK constraints, indexes, foreign keys.

          And of course you can use tree view and tabular view to see changes (report can be generated in tabular view) and set what to be included into DDL or merged into design.

          6) You can import each application system in separate relational model but then you need to restore manually references between systems.

          7) Engineering to Logical model - you need to decide whether you need that. If you have ERD in designer repository then you should import entities and tables together in one run and then you'll get mapping information between them.

           

          Philip

          • 2. Re: Migrating Designer 10g and Oracle Database 11g EE Data Dictionary to SDDM 4.0
            marcusafs

            Thanks for the helpful direction.

            4) I don't know what are differences in the scope of one pair and between 4 implementations of pairs

            The pairs are development and production of operations and personnel databases. Just the normal promotion model of two different databases.