| Copy data from one oracle server to another [message #186363] |
Mon, 07 August 2006 13:08  |
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 
|
|
|
|
|
|
|
|
| Re: Copy data from one oracle server to another [message #186388 is a reply to message #186363] |
Mon, 07 August 2006 15:01  |
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 #186398 is a reply to message #186363] |
Mon, 07 August 2006 15:56 |
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 |
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 |
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
|
|
|
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
