Solved

Any way to track the changes to the DB from one point of time?

Posted on 2013-11-26
7
467 Views
Last Modified: 2013-12-04
Hi,

I have done so many changes to my dev. DB. Now I have to move those changes to another DB.

I want to know whatever changes I did from a specific point of time, any way to get this?
0
Comment
Question by:sakthikumar
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
7 Comments
 
LVL 23

Expert Comment

by:David
ID: 39681524
A broad question, let's try to focus a bit.  Assuming a current version of the product -- a database has several features to track changes:  auditing access, or who's made content changes, can be tracked -- there are simple ways, and complex ways.

In like manner, the actual content change (DML) is easily tracked if the database logs such changes (in archivelog mode).  Those logs are commonly copied to other hosts, and applied to update the database copy on those backup databases.  Oracle DataGuard is the utility used.

Oracle Flashback is also available, by which one can report the changes made to the data over time, for as long as the log copies are available.  Again, very high level mention.

But, there's no convenient way to report what, when, or by whom a data definition (DDL) was changed -- say a person with admin rights changed one column in one table in production from num(5) to num(6).  Comparison can be made, but that's not a feature commonly done in a production environment.

So, reword your question with specifics please.
0
 
LVL 77

Expert Comment

by:slightwv (䄆 Netminder)
ID: 39682255
If you are running in archive log mode, Oracle's Logminer should be able to show you what was executed and when.

If you are licensed for it the Change Management Pack for Enterprise Manager can compare two different databases and show you what is different.  Never used it but remember reading it can do it.

You could also generate the DDL for the schema with dbms_metadata for development and production then do a compare, preferably side by side, to locate the differences.


Guess this is a learning moment for you?  You should ALWAYS track changes in development...

I take it to the extreme but it has saved me soooo many times:  I have a master SQL script that can rebuild ALL my database objects any time I want.

Whenever I'm doing development, I create a new SQL script for that patch/release/whatever.  If a change needs to be made, I modify the dev script.  Then when it comes time to move it into production it is as simple as running a script.

Then I merge the dev script into the master production script.

If you are using a software versioning/tracking tool, it is even better.  You can check in the database script with the application code for any specific release.
0
 
LVL 37

Accepted Solution

by:
Geert Gruwez earned 250 total points
ID: 39682999
whatever point in time ... that's a rather big problem
toad has a database/schema comparison which can list all the differences between the 2 and provide a script to sync them both

http://www.quest.com/toad-for-oracle/
0
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 13

Expert Comment

by:Alexander Eßer [Alex140181]
ID: 39683003
You may want to give this one a try:

http://www.liquibase.org/

It's free (open source) and looked quite suitable for me (seen on DOAG 2013 in Germany)...
0
 
LVL 29

Assisted Solution

by:MikeOM_DBA
MikeOM_DBA earned 250 total points
ID: 39693534
*
1) You can start by querying the LAST_DDL_TIME column from DBA_OBJECTS table to get recent changes on objects of the affected schema.

2) Using SQL Developer, you can then compare these changed objects between the source and target database.

3) Not sure if SQL Developer will generate the code to apply the changes (I use TOAD).

4) Apply changes to the target schema

5) Then you can worry about the data (if at all).

Good luck!
;)
0
 
LVL 23

Expert Comment

by:David
ID: 39693703
Mike, I follow your logic about DBA_OBJECTS, but w/o the asker getting back on we're all shooting in the dark.  I don't believe that this particular view would help me know if a table column was added, for example.  And as you can appreciate, his/her system could have hundreds or thousands of transactions per minute -- manual comparison would become labor intensive and error-prone.

dvz
0
 
LVL 29

Expert Comment

by:MikeOM_DBA
ID: 39693944
Hi dvz,

My take on the requirement is that the OP is asking only about differences in schema objects which he/she needs to migrate (apply) to another db and not "transactions" (data).

Therefore the missing column would be supplied from the comparison of objects between the source and target db's.
 
DBA_OBJECTS just provides the list of "suspect" objects that were either created or modified after the required date.

But you are right, without more clear requirements, we're all shooting in the dark.

:)
0

Featured Post

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Have you ever had to make fundamental changes to a table in Oracle, but haven't been able to get any downtime?  I'm talking things like: * Dropping columns * Shrinking allocated space * Removing chained blocks and restoring the PCTFREE * Re-or…
Cursors in Oracle: A cursor is used to process individual rows returned by database system for a query. In oracle every SQL statement executed by the oracle server has a private area. This area contains information about the SQL statement and the…
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
Video by: Steve
Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…

691 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question