Home » Server Options » Replication » Data Transfer From Oracle 10 g to Oracle 9i (Window )
Data Transfer From Oracle 10 g to Oracle 9i [message #494644] Wed, 16 February 2011 04:52 Go to next message
ravianugade
Messages: 15
Registered: February 2011
Location: Pune
Junior Member
Hi Experts,
We need to transfer data from oracle 10g to Oracle 9i in the following condition.

There will be two database server , one is online server where online user fill the form which is generated by java, spring , hibernate and using database 10 g.
at day end i need to execute a process that transferring data from online server to offline server that is in oracle database 9i. This process is scheduled. Some security reason client do not kept this two database on same network.

My challenge is that transfer data from online server to offline server with applying client security norms.

I have option like
1) Using Oracle replication method, creating materialized view on remote server , refreshing it at regular interval. but database connectivity is not contineous, should i go for that ?

2) Write java application on intermediate server where we write process to get the connection of this two database servers. From java application we call the procedure for selecting data from Oracle 10g and insert into oracle 9i database and using flag on both data to identified how many rows are transfered and how many remaining for trasfer.

Please tell me what is best way to acheive this ?
Re: Data Transfer From Oracle 10 g to Oracle 9i [message #494645 is a reply to message #494644] Wed, 16 February 2011 04:54 Go to previous messageGo to next message
Michel Cadot
Messages: 68624
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Are the servers connected? I mean is there a network link between the 2 servers?

Regards
Michel
Re: Data Transfer From Oracle 10 g to Oracle 9i [message #494649 is a reply to message #494645] Wed, 16 February 2011 05:20 Go to previous messageGo to next message
ravianugade
Messages: 15
Registered: February 2011
Location: Pune
Junior Member
First upon thank's for your reply.

There is no direct connectivity but there is indirect connectivity between this two database server.
There is process on intermediate server that open the link on both server for that time period this two servers are in network via intermediate server.
At that specific time i need to create a process ( in any language on java or in oracle it's upto me) to transfer data from one database to another.
There is dialy process. So at once in day for specific time period that server are in network.
Re: Data Transfer From Oracle 10 g to Oracle 9i [message #494650 is a reply to message #494649] Wed, 16 February 2011 05:33 Go to previous messageGo to next message
Michel Cadot
Messages: 68624
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
I think you can from the middle server do an export from the source database and import into the target one. This can be easily scheduled with a cmd script and AT command.

Regards
Michel
Re: Data Transfer From Oracle 10 g to Oracle 9i [message #494652 is a reply to message #494650] Wed, 16 February 2011 05:48 Go to previous messageGo to next message
ravianugade
Messages: 15
Registered: February 2011
Location: Pune
Junior Member
Thanks Michael for the suggestion.
But this middle server is only application server. There is no any database server installed.
This server is only used for opening connection between that two server.
And one more thing is i need to take only those rows from table which not going transferred previously.

e.g.
connect online@server
table abc having 10 rows

connect offline@server
table abc inserted 10 rows

Next day

connect online@server
table abc having 40 rows
selecting 30 rows out of 40 rows because 10 rows is already in offline server database

connect offline@server
insert 30 rows
table abc having 40 rows

.... so on.

Regards,
Ravi
Re: Data Transfer From Oracle 10 g to Oracle 9i [message #494658 is a reply to message #494652] Wed, 16 February 2011 06:28 Go to previous messageGo to next message
Michel Cadot
Messages: 68624
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
But this middle server is only application server. There is no any database server installed.

There is no need to have a database server installed on this machine only an Oracle client.

Quote:
And one more thing is i need to take only those rows from table which not going transferred previously.

That will be more difficult.

You can install an Oracle Connection Manager on the middle server and then you can use standard snapshots in the target database.

Regards
Michel
Re: Data Transfer From Oracle 10 g to Oracle 9i [message #494663 is a reply to message #494658] Wed, 16 February 2011 07:19 Go to previous messageGo to next message
ravianugade
Messages: 15
Registered: February 2011
Location: Pune
Junior Member
Hi Micheal,

Confused
Please see the following :

Step-1> Configured tnsname.ora file on both servers.

Step-2> Create public database link on both server says online_link and offline_link.

Step-3> Create snapshot log on table which going to be transferred from online database.

Step-4> Create materialized view on online database table.
using select * from <table_name>@online_link

This all one time entry.

Now when i start this intermediate server, this two database servers are in network for that time period i going to refresh meterialized views.

Now my question is that working? because whatever data is transferred from online database to offline database is used for only showing reports.
There nothing any dml operation happened in offline server.

Are Materiliazed view are physically stored on database ?
What happens when network failure during refreshing materialized views ?

reply.

Regards,
Ravi


Re: Data Transfer From Oracle 10 g to Oracle 9i [message #494669 is a reply to message #494663] Wed, 16 February 2011 07:50 Go to previous message
Michel Cadot
Messages: 68624
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Yes, this will work and achieve your need if you have a connection manager in the middle server.

Quote:
Are Materiliazed view are physically stored on database ?

Yes (this is the meaning of "materialized").

Quote:
What happens when network failure during refreshing materialized views ?

It depends on the option you gave on the refresh. Either you stay in the previous day or you table will be empty.
I encourage you to read about materialized view in the documentation.
For instance,
Database Concepts
Chapter 5 Schema Objects
Section Overview of Materialized Views
Database Data Warehousing Guide
Chapter 8 Basic Materialized Views

Regards
Michel



Previous Topic: Job Failing Several Times (merged)
Next Topic: Oracle <-> DB2 Bi-Directional Sync, Master-Master DB setup
Goto Forum:
  


Current Time: Thu Mar 28 03:40:18 CDT 2024