Home » RDBMS Server » Server Utilities » Copy data from one oracle server to another
Copy data from one oracle server to another [message #186363] Mon, 07 August 2006 13:08 Go to next message
dazormiq
Messages: 4
Registered: August 2006
Junior Member
I know I saw a lot of data copy stuff in here already but none quite fits my problem.

I have a very strict DBA. I am trying to run coldfusion to pull and display some data. I am in no way allowed to write anything to my RDBMS box, but most of my scripts need to create a little temp table to compile and sort all my queries.

I installed the newest oracle on a separate server hoping to be able to on command pull data from one server, store it on my new local oracle and do my stuff from there. It didn't work out so well. Then I asked if I could create a link between the databases (my RDBMS user has select permissions only) and have my server pull data that way. That got shot down very quickly.

Does anyone know of another way I can dynamically pull data from my production RDBMS box to my local oracle without using a link?

Any help is greatly appreciated. It would also help save what little hair I have left Smile
Re: Copy data from one oracle server to another [message #186378 is a reply to message #186363] Mon, 07 August 2006 14:13 Go to previous messageGo to next message
Mahesh Rajendran
Messages: 10672
Registered: March 2002
Location: oracleDocoVille
Senior Member
Account Moderator
You can export (from source) and import (to the new target database).
search the forum/google for export and import
Re: Copy data from one oracle server to another [message #186379 is a reply to message #186363] Mon, 07 August 2006 14:17 Go to previous messageGo to next message
Littlefoot
Messages: 20901
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
If you can't
a) create objects in your "read only" schema (but you desperately need them),
b) establish a link to the production database,

I guess the only way to dynamically (real time? Mahesh's export-import doesn't help here, right?) query the database is to steal the server.

Or fire your employer and get another one.
Re: Copy data from one oracle server to another [message #186388 is a reply to message #186363] Mon, 07 August 2006 15:01 Go to previous messageGo to next message
dazormiq
Messages: 4
Registered: August 2006
Junior Member
Ya, I didn't want to do the export/import. This data needs to be totally up to date and I will have multiple people trying to pull different data based on their unique criteria.


I was really hoping I would get allowed to do a link, but oh no....don't let me do stuff that will help the 300 people that would use this.

I guess I could do a huge export/import every day and just let users know that all data is valid 24 hours ago.
Re: Copy data from one oracle server to another [message #186397 is a reply to message #186388] Mon, 07 August 2006 15:53 Go to previous messageGo to next message
Littlefoot
Messages: 20901
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
But that doesn't make sense; doing export/import will take much time and consume resources; don't forget network traffic. Can't your boss talk to DBA?
Re: Copy data from one oracle server to another [message #186398 is a reply to message #186363] Mon, 07 August 2006 15:56 Go to previous messageGo to next message
wagnerch
Messages: 58
Registered: July 2006
Member
Perhaps you should look at eliminating the temp tables. Are these temp tables created on the fly when a user runs a query from ColdFusion? What are they used for?

I can't see creating another database as a good solution, sorry. Now you have another point of failure in the architecture, and one that probably wouldn't be maintained by the DBA. Before you know it you have one thing in there, then two, then 40, and then the business is dependent on it, and then it crashes and burns and you didn't have a backup strategy.

[Updated on: Mon, 07 August 2006 15:58]

Report message to a moderator

Re: Copy data from one oracle server to another [message #186401 is a reply to message #186363] Mon, 07 August 2006 16:15 Go to previous messageGo to next message
dazormiq
Messages: 4
Registered: August 2006
Junior Member
I don't want to do an export/import cause that would be horrible on me.


My boss and the DBA are one and the same. We also have to abide by guidelines from our parent organization.

The temp tables are used to compile a lot of data from a bunch of different tables and sort it out. All my scripts create little tables to store that data and select against the data in them. I tried taking out the temp tables, but there is so much data from dozens of different tables that stuff kept getting lost. If the script will be run alot and will show the same data over and over, I would put it in a more permanent table to save on processing power.

The coldfusion part database would be purely for coldfusion to display data. If it dies, no big deal to recreate the tables and let them be recompiled since all the data comes directly from the RDBMS box anyways.

[Updated on: Mon, 07 August 2006 16:16]

Report message to a moderator

Re: Copy data from one oracle server to another [message #186406 is a reply to message #186401] Mon, 07 August 2006 17:23 Go to previous messageGo to next message
wagnerch
Messages: 58
Registered: July 2006
Member
If the temp tables are used to compile alot of data from different tables then how is that different then a SQL join?

Is this source database a data warehouse or a OLTP configuration?

I think you missed my point on the "ColdFusion" database, but whatever -- build it if you must.


In any event, you may want to look at the data warehousing guide. Specifically the "Extraction" sections may be of interest. It covers several mechanisms for data extraction, it sounds like you may want to leverage one of the Change Data Capture mechanisms which apply archived redo to a staging database asynchronously (called Asynchronous AutoLog Mode).

http://download-east.oracle.com/docs/cd/B19306_01/server.102/b14223/cdc.htm#i1028295
Re: Copy data from one oracle server to another [message #186407 is a reply to message #186363] Mon, 07 August 2006 17:31 Go to previous messageGo to next message
BlackSwan
Messages: 25050
Registered: January 2009
Location: SoCal
Senior Member
From the fine Concepts Manual...
Overview of Views

A view is a tailored presentation of the data contained in one or more tables or other views. A view takes the output of a query and treats it as a table. Therefore, a view can be thought of as a stored query or a virtual table. You can use views in most places where a table can be used.
Re: Copy data from one oracle server to another [message #186432 is a reply to message #186407] Mon, 07 August 2006 23:55 Go to previous message
Littlefoot
Messages: 20901
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
True, but - as I've understood it - the OP can't create absolutely anything in his schema (including views).
Previous Topic: passing value
Next Topic: Insert/Update facillity in loader
Goto Forum:
  


Current Time: Sat Dec 10 09:19:48 CST 2016

Total time taken to generate the page: 0.04087 seconds