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

Posted on 2013-11-26
Last Modified: 2013-12-04

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?
Question by:sakthikumar
LVL 23

Expert Comment

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.
LVL 76

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.
LVL 37

Accepted Solution

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
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

LVL 13

Expert Comment

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

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

Assisted Solution

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!
LVL 23

Expert Comment

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.

LVL 29

Expert Comment

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.


Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Suggested Solutions

Why doesn't the Oracle optimizer use my index? Querying too much data Most Oracle developers know that an index is useful when you can use it to restrict your result set to a small number of the total rows in a table. So, the obvious side…
Note: this article covers simple compression. Oracle introduced in version 11g release 2 a new feature called Advanced Compression which is not covered here. General principle of Oracle compression Oracle compression is a way of reducing the d…
Via a live example, show how to take different types of Oracle backups using RMAN.
This video shows how to configure and send email from and Oracle database using both UTL_SMTP and UTL_MAIL, as well as comparing UTL_SMTP to a manual SMTP conversation with a mail server.

895 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

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now