Solved

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

Posted on 2013-11-26
7
460 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
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
Industry Leaders: 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: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering 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

Suggested Solutions

Title # Comments Views Activity
PL SQL Developer 7 67
pivot rows to columns 1 44
Oracle Query to Find number of process consumed by a session 15 52
error in oracle form 11 19
Working with Network Access Control Lists in Oracle 11g (part 1) Part 2: http://www.e-e.com/A_9074.html So, you upgraded to a shiny new 11g database and all of a sudden every program that used UTL_MAIL, UTL_SMTP, UTL_TCP, UTL_HTTP or any oth…
How to Create User-Defined Aggregates in Oracle Before we begin creating these things, what are user-defined aggregates?  They are a feature introduced in Oracle 9i that allows a developer to create his or her own functions like "SUM", "AVG", and…
This video shows information on the Oracle Data Dictionary, starting with the Oracle documentation, explaining the different types of Data Dictionary views available by group and permissions as well as giving examples on how to retrieve data from th…
This video shows how to Export data from an Oracle database using the Datapump Export Utility.  The corresponding Datapump Import utility is also discussed and demonstrated.

685 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