FAQ
We have 13 development databases, 8 parallel development tracks, and 2 concurrent sustainment releases all developing at the same time. We are having alot of trouble with change management. I am looking for an easy way to do the following:
take a snapshot of the metadata of a database at a point in time.
compare it at a later point in time or to another database and see the differences.
What we have tried

1. Designer and change manager(OEM) are really slow.
2. Toad does not appear to be complete
3. Writing code with dbms_metadata is a major task.
4. export won't work, because the order of the objects in the file could be different in different databases, do to different releases applied at different times.

Anything easier? Any good tools? Does RMAN have any functionality for this? I didn't see anything...

Search Discussions

  • Jesse, Rich at Dec 9, 2004 at 4:23 pm
    What isn't in TOAD? What version of TOAD? What DB version(s) are you using?

    If you'd like direct access to the development team of TOAD, join the TOAD group at http://groups.yahoo.com V8.0 is out, 8.1 is in Beta right now and is currently slated for a Q1 '05 release, AFAIK.

    Just a thought...

    Rich

    -----Original Message-----
    Sent: Thursday, December 09, 2004 4:01 PM
    Subject: looking for a good way to change manage oracle

    We have 13 development databases, 8 parallel development tracks, and 2 concurrent sustainment releases all developing at the same time. We are having alot of trouble with change management. I am looking for an easy way to do the following:
    take a snapshot of the metadata of a database at a point in time.
    compare it at a later point in time or to another database and see the differences.
    What we have tried

    1. Designer and change manager(OEM) are really slow.
    2. Toad does not appear to be complete
    3. Writing code with dbms_metadata is a major task.
    4. export won't work, because the order of the objects in the file could be different in different databases, do to different releases applied at different times.

    Anything easier? Any good tools? Does RMAN have any functionality for this? I didn't see anything...
    --
    http://www.freelists.org/webpage/oracle-l

    --
    http://www.freelists.org/webpage/oracle-l
  • Stephen booth at Dec 9, 2004 at 4:49 pm

    On Thu, 09 Dec 2004 22:00:57 +0000, ryan_gaffuri_at_comcast.net wrote:
    We have 13 development databases, 8 parallel development tracks, and 2 concurrent sustainment releases all developing at the same time. We are having alot of trouble with change management. I am looking for an easy way to do the following:
    take a snapshot of the metadata of a database at a point in time.
    compare it at a later point in time or to another database and see the differences.
    I've always approached it from the other direction. Developers don't
    have the ability to modify objects. If they want to create make a
    change to the structure of an object they have to write it as a script
    (for new objects) or ammend the existing script and give it to the DBA
    who will run it. Scripts are stored in SCCS which handles versioning.
    Seeing the changes can be a simple as doing a diff of the scripts at
    two different versions.

    There are plenty of scripts out there to extract the meta data to a
    file. It shouldn't be too difficult to adapt one to first identify
    allt he objects to extract then extract each object to a uniquely
    named file (e.g. cre_[object_type]_object_name.sql) to avoid the
    problem of objects being in different orders in the file.

    There's a tool called DDL Wizard that you can download (I don't
    remeber the URL but Google should be able to find it for you) for free
    that will extractthe DDL and output as either a set of HTML pages or
    the SQL scripts to recreate the objects. If you create the HTML files
    you could do a diff on them. It's also handy for generating some
    quick and dirty documentation for your database structure.

    Stephen
  • Post, Ethan at Dec 9, 2004 at 5:03 pm
    I am managing something like that at the moment here also. Our basic
    operation is...

    Code is checked into source safe folders, different folders have
    different rules.
    Builds grab current source, deploy changes based on file checksum to
    various databases, compile etc...the source code is saved off and I can
    also trigger database backups with version numbers. This process also
    packages the builds so other can re-run them in environments I do not
    manage.
    Full builds are run every day and results DB compares are run against
    multiple qa/testing environments to ensure they are staying in sync.
    Scripts are home grown. I have a basic shell script that can a)log into
    database, b) create db link to remote db, c) run a file and compare to
    schemas.
    multiple files can be run in step 3, some compare schema, some
    compare seed data
    I have separate scripts which can compare code in db's, fastest way I
    found for this is to pull code from OS using shell and run diff on the
    files, about 20 minutes to compare two database. The schema compare
    script works very well for the most part and runs in about 1 minute,
    last time I tried Oracle schema compare it took forever!

    If I want to compare versions of the product I just restore the right
    database or apply a particular build to a database and run a compare.

    Most of the databases also have a DDL audit trigger that throws DDL
    events into the alert log incase I need to dig something up and can not
    find it.

    Another script constantly monitors the gets from VSS and rdist's it to
    another directory on the file system, this way I can easily use the
    output from rdist to recognize when a file has changed, which triggers a
    "diff" between the new file and old before replacing it. This diff file
    is stored on a web server for engineers to easily see what has changed
    recently and what the change was.

    Hopefully that gives you a few ideas.

    One last thought, if you write your own compare script using data
    dictionary tables you can easily grab and store those in a schema and
    version them, then just point to the right set of tables for a compare.

    -----Original Message-----
    From: oracle-l-bounce_at_freelists.org
    On Behalf Of
    ryan_gaffuri_at_comcast.net
    Sent: Thursday, December 09, 2004 4:01 PM
    To: oracle-l_at_freelists.org
    Subject: looking for a good way to change manage oracle

    We have 13 development databases, 8 parallel development tracks, and 2
    concurrent sustainment releases all developing at the same time. We are
    having alot of trouble with change management. I am looking for an easy
    way to do the following:
    take a snapshot of the metadata of a database at a point in time.=20
    compare it at a later point in time or to another database and see the
    differences.
    What we have tried

    1. Designer and change manager(OEM) are really slow.=20
    2. Toad does not appear to be complete
    3. Writing code with dbms_metadata is a major task.
    4. export won't work, because the order of the objects in the file could

    be different in different databases, do to different releases applied at
    different times. =20
    Anything easier? Any good tools? Does RMAN have any functionality for
    this? I didn't see anything...=20
    --
    http://www.freelists.org/webpage/oracle-l
    --
    http://www.freelists.org/webpage/oracle-l
  • Mercadante, Thomas F at Dec 10, 2004 at 7:05 am
    Ryan,

    It seems that your approach is always playing catch-up. Here, we developed
    a very simple Database Request system. Developers cannot make any changes
    anyplace - they have to request a change be made via the request system.
    The application tracks all changes and what level of the database they have
    been applied to (Dev, Staging or Production). Simple reports can be
    generated showing what changes are in Dev, but not in staging yet.

    I'm really talking about managing your change requests a little better.
    If you manage your requests better, then you don't need a schema compare
    functionality.

    Hope this helps.

    Tom

    -----Original Message-----
    From: ryan_gaffuri_at_comcast.net
    Sent: Thursday, December 09, 2004 5:01 PM
    To: oracle-l_at_freelists.org
    Subject: looking for a good way to change manage oracle

    We have 13 development databases, 8 parallel development tracks, and 2
    concurrent sustainment releases all developing at the same time. We are
    having alot of trouble with change management. I am looking for an easy way
    to do the following:
    take a snapshot of the metadata of a database at a point in time.
    compare it at a later point in time or to another database and see the
    differences.
    What we have tried

    1. Designer and change manager(OEM) are really slow.
    2. Toad does not appear to be complete
    3. Writing code with dbms_metadata is a major task.
    4. export won't work, because the order of the objects in the file could be

    different in different databases, do to different releases applied at
    different times.
    Anything easier? Any good tools? Does RMAN have any functionality for this?
    I didn't see anything...
    --
    http://www.freelists.org/webpage/oracle-l
    --
    http://www.freelists.org/webpage/oracle-l
  • Loughmiller, Greg at Dec 10, 2004 at 7:21 am
    Well,
    To compliment what Tom mentions about the formal Change Request effort. How
    about a data model driven approach as well? There are a few tools that will
    "merge the data models" based on the concurrency that you mention and gen
    SQL/DDL/DML based on a "project basis". Thus, you can also ensure that the
    logical/physical aspect of the schemas stay aligned with a Formal SDLC for
    the Database changes that Tom has mentioned.

    This may seem like a "little over the top". But it saved my teams' "hide" a
    few times in previous jobs..

    greg

    -----Original Message-----
    From: Mercadante, Thomas F
    Sent: Friday, December 10, 2004 8:05 AM
    To: 'ryan_gaffuri_at_comcast.net'; oracle-l_at_freelists.org
    Subject: RE: looking for a good way to change manage oracle

    Ryan,

    It seems that your approach is always playing catch-up. Here, we developed
    a very simple Database Request system. Developers cannot make any changes
    anyplace - they have to request a change be made via the request system.
    The application tracks all changes and what level of the database they have
    been applied to (Dev, Staging or Production). Simple reports can be
    generated showing what changes are in Dev, but not in staging yet.

    I'm really talking about managing your change requests a little better.
    If you manage your requests better, then you don't need a schema compare
    functionality.

    Hope this helps.

    Tom

    -----Original Message-----
    From: ryan_gaffuri_at_comcast.net
    Sent: Thursday, December 09, 2004 5:01 PM
    To: oracle-l_at_freelists.org
    Subject: looking for a good way to change manage oracle

    We have 13 development databases, 8 parallel development tracks, and 2
    concurrent sustainment releases all developing at the same time. We are
    having alot of trouble with change management. I am looking for an easy way
    to do the following:
    take a snapshot of the metadata of a database at a point in time.
    compare it at a later point in time or to another database and see the
    differences.
    What we have tried

    1. Designer and change manager(OEM) are really slow.
    2. Toad does not appear to be complete
    3. Writing code with dbms_metadata is a major task.
    4. export won't work, because the order of the objects in the file could be

    different in different databases, do to different releases applied at
    different times.
    Anything easier? Any good tools? Does RMAN have any functionality for this?
    I didn't see anything...
    --
    http://www.freelists.org/webpage/oracle-l
    --
    http://www.freelists.org/webpage/oracle-l

    --
    http://www.freelists.org/webpage/oracle-l
  • Niall Litchfield at Dec 10, 2004 at 8:07 am
    If as I presume this is a US based organisation sarbanes-oxley may
    have the odd thing to say about finding out what changes have happened
    *after* rather than before the fact.

    In general though change request/change management is good practice,
    and post change auditing is just a supplementary control.
  • Powell, Mark D at Dec 10, 2004 at 8:04 am
    I think Tom has a good point; however, the request tracking works best if
    the environments are all in sync when the process is started otherwise you
    do not have an explanation for why differences exist. I think Ryan's first
    problem is to identify and re-sync those elements that should be the same.
    As this is done then change tracking must be implemented to maintain a
    record of the changes.

    Ryan, one idea that might be of use if your application is basically limited
    to using the basic objects: tables, views, indexes, synonyms, etc... might
    be to extract the owner, object_name, and object_type from dba_objects along
    with a database identifier and load this into one database. Then a simple
    pl/sql script could identify objects in one database environment but not
    others. Using dba_tab_columns you could identify differences in tables and
    views. If you do not worry about identifying the exact differences in code
    but just identify the objects that require review the code is pretty simple.

    Obviously this is not a purely automated technique but the coding is simple,
    the cost is cheap, and it may be good enough. I used the above for
    comparing tables/views between our test environments and production so that
    we could rebuild test tables where the column list/order did not match
    production.

    Mark D Powell --

    -----Original Message-----
    From: oracle-l-bounce_at_freelists.org
    On Behalf Of Mercadante, Thomas F
    Sent: Friday, December 10, 2004 8:05 AM
    To: 'ryan_gaffuri_at_comcast.net'; oracle-l_at_freelists.org
    Subject: RE: looking for a good way to change manage oracle

    Ryan,

    It seems that your approach is always playing catch-up. Here, we developed
    a very simple Database Request system. Developers cannot make any changes
    anyplace - they have to request a change be made via the request system.
    The application tracks all changes and what level of the database they have
    been applied to (Dev, Staging or Production). Simple reports can be
    generated showing what changes are in Dev, but not in staging yet.

    I'm really talking about managing your change requests a little better.
    If you manage your requests better, then you don't need a schema compare
    functionality.

    Hope this helps.

    Tom

    -----Original Message-----
    From: ryan_gaffuri_at_comcast.net
    Sent: Thursday, December 09, 2004 5:01 PM
    To: oracle-l_at_freelists.org
    Subject: looking for a good way to change manage oracle

    We have 13 development databases, 8 parallel development tracks, and 2
    concurrent sustainment releases all developing at the same time. We are
    having alot of trouble with change management. I am looking for an easy way
    to do the following:
    take a snapshot of the metadata of a database at a point in time.
    compare it at a later point in time or to another database and see the
    differences.
    What we have tried

    1. Designer and change manager(OEM) are really slow.
    2. Toad does not appear to be complete
    3. Writing code with dbms_metadata is a major task.
    4. export won't work, because the order of the objects in the file could be

    different in different databases, do to different releases applied at
    different times.
    Anything easier? Any good tools? Does RMAN have any functionality for this?
    I didn't see anything...
    --
    http://www.freelists.org/webpage/oracle-l
    --
    http://www.freelists.org/webpage/oracle-l
    --
    http://www.freelists.org/webpage/oracle-l
  • Ryan_gaffuri_at_comcast.net at Dec 10, 2004 at 11:09 am
    the developers cannot make any modifcations to structure. This is not a simple system. We have hundreds of objects that include everything from partitions, to IOTs, to objects. Multiple users, multiple tablespaces, and multiple datafiles.

    Its large. There are 300 people on the project. I really am hoping for an automated tool to help us. I am really hoping we can get an automated process. We are even having release schedules flipped. Release A,B,C. Well it becomes C, B,A and then changes again after we test it. This alone screws up column ordering. We are using an interface that requires the column order to be the same or the system crashes(long story, cant change it, have to deal with it.. system is too big to just 'get rid of it' The government has already spent $500 million developing this thing).

    Thanks for the help guys.
    -------------- Original message --------------
    I think Tom has a good point; however, the request tracking works best if
    the environments are all in sync when the process is started otherwise you
    do not have an explanation for why differences exist. I think Ryan's first
    problem is to identify and re-sync those elements that should be the same.
    --
    http://www.freelists.org/webpage/oracle-l
  • Mhthomas at Dec 11, 2004 at 10:20 am
    Hi Ryan,

    On Fri, 10 Dec 2004 17:07:37 +0000, ryan_gaffuri_at_comcast.net
    wrote:
    the developers cannot make any modifcations to structure. This is not a simple system. We have hundreds of objects that include everything from partitions, to IOTs, to objects. Multiple users, multiple tablespaces, and multiple datafiles.

    Its large. There are 300 people on the project. I really am hoping for an automated tool to help us. I am really hoping we can get an automated process. We are even having
    I'm not aware of a tool that does all you ask, but many tools get part
    of the info and may help. I think its an additional requirement (on
    top of schema compare) to manage developers contributions to "objects
    in the database".

    We built our own system, because we had strange requirements. Probably
    the first time you get a really complete looking tool, someone will
    throw in a v7 or v80 Oracle instance, or some other wierdness. :-)
    BTW, when we started discussing this stuff at an enterprise level then
    we were bumping into enterprise architects and their favorite tools
    and processes. Pretty soon you have many 'non-functional' requirements
    (pun intended).

    I think you have two major tasks, and if you manage these you will get
    close to your goal. The first task to automate is a 'simple inventory'
    of objects in the database. You don't need much detail to make a good
    comparison. The 'simple' idea is a big list of objects, and maybe a
    few details like creation time and permissions. The second task is to
    automate comparison of object details, drilling down to your required
    level of detail.

    Break it into two tasks because the first task can usually be done
    very quickly, especially if you manage it with metadata. Many times
    the first task will also identify the critical problems.

    The object details (2nd task) are much more time consuming to compare
    and resolve in my experience. And, there are usually many ways to
    manage these problems. Its up to you to decide.

    HTH

    Regards,

    Mike Thomas
  • Chazhoor, Vincent at Dec 10, 2004 at 11:52 am
    My current project has six releases moving in parallel. The company is
    followig the RUP methodology. A few of the releases are in development and
    we have the same challenge of managing version conrol. We are using
    clearcase and a custom tool to manage this. The links below may be helpful.

    http://www.vsj.co.uk/articles/display.asp?id=393

    You could download a freeware from http://www.ddlwizard.com/
    I found a perl script in archieves of comp.databases and it was useful.
    There is a PL/SQL script at asktom.oracle.com which will create most of the
    object types. You could modify this to add the additional object types
    required.
    http://asktom.oracle.com/pls/ask/f?p=4950:8:12251516314420919508::NO::F4950_
    P8_DISPLAYID,F4950_P8_CRITERIA:1464804639878

    -----Original Message-----
    From: ryan_gaffuri_at_comcast.net
    Sent: Friday, December 10, 2004 12:08 PM
    To: mark.powell_at_eds.com; oracle-l_at_freelists.org
    Cc: Powell, Mark D
    Subject: RE: looking for a good way to change manage oracle

    the developers cannot make any modifcations to structure. This is not a
    simple system. We have hundreds of objects that include everything from
    partitions, to IOTs, to objects. Multiple users, multiple tablespaces, and
    multiple datafiles.

    Its large. There are 300 people on the project. I really am hoping for an
    automated tool to help us. I am really hoping we can get an automated
    process. We are even having release schedules flipped. Release A,B,C. Well
    it becomes C, B,A and then changes again after we test it. This alone screws
    up column ordering. We are using an interface that requires the column order
    to be the same or the system crashes(long story, cant change it, have to
    deal with it.. system is too big to just 'get rid of it' The government has
    already spent $500 million developing this thing).

    Thanks for the help guys.
    -------------- Original message --------------
    I think Tom has a good point; however, the request tracking works best if
    the environments are all in sync when the process is started otherwise you
    do not have an explanation for why differences exist. I think Ryan's first
    problem is to identify and re-sync those elements that should be the same.
    --
    http://www.freelists.org/webpage/oracle-l
    --
    http://www.freelists.org/webpage/oracle-l
  • DENNIS WILLIAMS at Dec 12, 2004 at 11:15 am
    Agree totally with the use of scripts to make database changes.
    One idea is to base your development on a 3-database model. Production,
    staging, and test (or development). Update the test and staging as required
    by cloning production. This way you are guaranteed to have an exact copy of
    production.

    Ideally you clone by recovering a backup. That way you frequently test
    your backups.

    Staging acts as a buffer between test and production. Often you can't
    refresh the test database because of various developer activities in
    progress, so staging is where you test the changes before they hit
    production.

    This method is from ITIL. The only way to combat a massive force like SOX
    is to pit it against another massive institution.

    Dennis Williams
    DBA

    Lifetouch, Inc.

    -----Original Message-----
    From: oracle-l-bounce_at_freelists.org
    On Behalf Of Powell, Mark D
    Sent: Friday, December 10, 2004 7:54 AM
    To: oracle-l_at_freelists.org
    Subject: RE: looking for a good way to change manage oracle

    I think Tom has a good point; however, the request tracking works best if
    the environments are all in sync when the process is started otherwise you
    do not have an explanation for why differences exist. I think Ryan's first
    problem is to identify and re-sync those elements that should be the same.
    As this is done then change tracking must be implemented to maintain a
    record of the changes.

    Ryan, one idea that might be of use if your application is basically limited
    to using the basic objects: tables, views, indexes, synonyms, etc... might
    be to extract the owner, object_name, and object_type from dba_objects along
    with a database identifier and load this into one database. Then a simple
    pl/sql script could identify objects in one database environment but not
    others. Using dba_tab_columns you could identify differences in tables and
    views. If you do not worry about identifying the exact differences in code
    but just identify the objects that require review the code is pretty simple.

    Obviously this is not a purely automated technique but the coding is simple,
    the cost is cheap, and it may be good enough. I used the above for
    comparing tables/views between our test environments and production so that
    we could rebuild test tables where the column list/order did not match
    production.

    Mark D Powell --

    -----Original Message-----
    From: oracle-l-bounce_at_freelists.org
    On Behalf Of Mercadante, Thomas F
    Sent: Friday, December 10, 2004 8:05 AM
    To: 'ryan_gaffuri_at_comcast.net'; oracle-l_at_freelists.org
    Subject: RE: looking for a good way to change manage oracle

    Ryan,

    It seems that your approach is always playing catch-up. Here, we developed
    a very simple Database Request system. Developers cannot make any changes
    anyplace - they have to request a change be made via the request system.
    The application tracks all changes and what level of the database they have
    been applied to (Dev, Staging or Production). Simple reports can be
    generated showing what changes are in Dev, but not in staging yet.

    I'm really talking about managing your change requests a little better.
    If you manage your requests better, then you don't need a schema compare
    functionality.

    Hope this helps.

    Tom

    -----Original Message-----
    From: ryan_gaffuri_at_comcast.net
    Sent: Thursday, December 09, 2004 5:01 PM
    To: oracle-l_at_freelists.org
    Subject: looking for a good way to change manage oracle

    We have 13 development databases, 8 parallel development tracks, and 2
    concurrent sustainment releases all developing at the same time. We are
    having alot of trouble with change management. I am looking for an easy way
    to do the following:
    take a snapshot of the metadata of a database at a point in time.
    compare it at a later point in time or to another database and see the
    differences.
    What we have tried

    1. Designer and change manager(OEM) are really slow.
    2. Toad does not appear to be complete
    3. Writing code with dbms_metadata is a major task.
    4. export won't work, because the order of the objects in the file could be

    different in different databases, do to different releases applied at
    different times.
    Anything easier? Any good tools? Does RMAN have any functionality for this?
    I didn't see anything...
    --
    http://www.freelists.org/webpage/oracle-l
    --
    http://www.freelists.org/webpage/oracle-l
    --
    http://www.freelists.org/webpage/oracle-l
    --
    http://www.freelists.org/webpage/oracle-l
  • Alexander Gorbachev at Dec 12, 2004 at 1:31 pm
    We are not a US based company but approach is similar. Real
    development environment is out of our scope - we don't maintain it. We
    maintain test cycle databases (normally 3 DBs for acceptance by
    different groups), several pre-production DBs (normally 3-4 DBs again
    for other groups), one production and one production look-alike (kind
    of post-production). Depending on functional requirements they are
    refreshed from prodution backup, functional refresh, or production
    based TTS.
    Changes are coming with new software versions and going through our
    record tracking system.

    --
    Best regards,
    Alex Gorbachev

    On Sun, 12 Dec 2004 11:18:32 -0600, DENNIS WILLIAMS
    wrote:
    Agree totally with the use of scripts to make database changes.
    One idea is to base your development on a 3-database model. Production,
    staging, and test (or development). Update the test and staging as required
    by cloning production. This way you are guaranteed to have an exact copy of
    production.
    Ideally you clone by recovering a backup. That way you frequently test
    your backups.
    Staging acts as a buffer between test and production. Often you can't
    refresh the test database because of various developer activities in
    progress, so staging is where you test the changes before they hit
    production.
    This method is from ITIL. The only way to combat a massive force like SOX
    is to pit it against another massive institution.

    Dennis Williams
    DBA
    Lifetouch, Inc.
    --
    http://www.freelists.org/webpage/oracle-l

Related Discussions

Discussion Navigation
viewthread | post
Discussion Overview
grouporacle-l @
categoriesoracle
postedDec 9, '04 at 4:01p
activeDec 12, '04 at 1:31p
posts13
users12
websiteoracle.com

People

Translate

site design / logo © 2022 Grokbase