[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

Any Oracle schema Synchronization tool ?

Posted on 2011-10-06
13
Medium Priority
?
331 Views
Last Modified: 2012-05-12

- We have Schema A and  B

- They both are same schemas (kind of clones when we started)

- There are some changes done to schema A from past few months and none to B

- If we wanted to fresh schema B with all the changes done from A

  we can take export A and then drop B and then recreate B user and import all the data
  back.

 Other than above procedure is there any other utility which can do schema comparsion
  and run some scripts online and make sure both schemas A & B are synchronized.
0
Comment
Question by:OCUBE
  • 5
  • 4
  • 2
  • +2
13 Comments
 
LVL 74

Accepted Solution

by:
sdstuber earned 252 total points
ID: 36928284
Toad can do schema comparisons and generate a script of differences.

You could also extract objects with dbms_metadata from both sides and use any textbased diff tool.

As for the synching itself,  you could use datapump   either expdp/impdp  or the pl/sql package  dbms_datapump.
0
 
LVL 23

Assisted Solution

by:OP_Zaharin
OP_Zaharin earned 999 total points
ID: 36928301
- you can also do a comparison by script however you need to create a dblink between the 2 databases. eg as follows:

> comparing tables between databaseA and databaseB:
select * from user_tables
MINUS
select * from user_tables@databaseB;

> comparing columns between 2 tables:
select  column_name, data_type, data_length  from user_tab_columns
       where table_name = 'ABC'
MINUS
select  column_name, data_type, data_length  from user_tab_columns@databaseB
       where table_name = 'ABC'
0
 
LVL 23

Assisted Solution

by:OP_Zaharin
OP_Zaharin earned 999 total points
ID: 36928311
- you can also use 3rd party tools such as TOYS and DBDIFF:

compare and synchronize oracle database schemas: http://www.impacttoys.com/

compare 2 oracle databases: http://www.dkgas.com/oradbdiff.htm

OP
0
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 

Author Comment

by:OCUBE
ID: 36928328


  Both the schemas are in the same database ( on same physical server).
0
 
LVL 23

Assisted Solution

by:OP_Zaharin
OP_Zaharin earned 999 total points
ID: 36928342
- you can then query to all_tables and specify the schema name:
select * from all_tables where owner = 'schema1'
MINUS
select * from all_tables where owner = 'schema2'

- and query to all_tab_columns for column comparison:
select  column_name, data_type, data_length  from all_tab_columns
       where table_name = 'ABC' and owner = 'schema1'
MINUS
select  column_name, data_type, data_length  from all_tab_columns
       where table_name = 'ABC' and owner = schema2'

0
 
LVL 2

Expert Comment

by:akramryk
ID: 36929205
Please your need to maintain two similar scemas. You may also use Materilized views replication or Oracle streams to keep both scemas sync in real time or periodically.
0
 
LVL 7

Assisted Solution

by:Jacobfw
Jacobfw earned 249 total points
ID: 36930951
Two other tools that could be options for you:

http://www.dbtools.com.br/EN/dbmanagerpro/index.php
and
http://www.softtreetech.com/ (DB Tools for Oracle)
0
 

Author Comment

by:OCUBE
ID: 36939799

  Do these tools Synchronize DATA as well ?
0
 
LVL 74

Expert Comment

by:sdstuber
ID: 36939818
Toad, datapump  

or just merge sql statement over db link
0
 

Author Comment

by:OCUBE
ID: 36939852

 How about the other 3rd party tools recommended by experts here ?

 Don't they sync the data as well ?
0
 
LVL 23

Expert Comment

by:OP_Zaharin
ID: 36940053
hi ocube,
- you can go thru all the link suggested by the experts and see if any of those meets you requirement. as for sync the data, look into my suggestion in ID: 36928311.
0
 
LVL 23

Assisted Solution

by:OP_Zaharin
OP_Zaharin earned 999 total points
ID: 36940064
- sorry to fast, my suggested link only sync the schema. as for the data you can do dblink between the databases as suggested by the expert earlier and do an insert such as - INSERT INTO tableA b WHERE NOT EXISTS (SELECT * FROM tableA@dblinkname WHERE b.id = id)  

- or use the legacy EXP then IMP to load the data.
0
 

Author Closing Comment

by:OCUBE
ID: 36950229
Thanks
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

Background In several of the companies I have worked for, I noticed that corporate reporting is off loaded from the production database and done mainly on a clone database which needs to be kept up to date daily by various means, be it a logical…
Using SQL Scripts we can save all the SQL queries as files that we use very frequently on our database later point of time. This is one of the feature present under SQL Workshop in Oracle Application Express.
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.
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
Suggested Courses

873 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