Re: Datapump and read only database

From: Brian Peasland <dba_at_nospam.peasland.net>
Date: Tue, 29 Aug 2006 19:10:24 GMT
Message-ID: <J4rx9G.I90@igsrsparc2.er.usgs.gov>

>> You can still use exp/imp but there may be a future version of Oracle
>> where these are not available.
>>
>> You can invoke the datapump utilities from a remote server, but the
>> master table is still created on the target server, so that will not
>> solve your problem.

>
> Well, then I have to find another way. I might remember reading somewhere
> on google that datapumping a read only database remotely is possible though.
> I'll check that in the docs. Maybe there is some hidden option. ;-)

I couldn't find it in the docs, but then I did not spend too long on it either....but from OCP Oracle Database 10g: New Features for Administrators Exam Guide by Alapati on Oracle Press, pg 87: "You can't use Data Pump in the normal way to to export data from a read-only database. This is because Data Pump can't create the necessary master table or create external tables on a read only database. Using the network mode, however, you can export data from a read-only database on server A to dump files on server B, where Data Pump is running".

I forgot about the network link option and it looks like it will solve your problem. To me, running DP remotely involves using invoking as follows:

expdp system/manager_at_remote_db .....

The above will still create the master table in the "remote" database, which if it is read-only, will result in an error. But the NETWORK_LINK parameter runs DP on a different server, places the master table a database there, but lets you run this dumping from a read-only database. Please see the docs on that link here:

http://download-east.oracle.com/docs/cd/B19306_01/server.102/b14215/dp_import.htm#sthref319

>> Any reason why you cannot perform your export operation on the primary
>> server and not the standby? What will this dump be used for?
>>

> As I wrote in answer to Steve's posting, the export is part of our
> backup strategy.
> The main reason to export the standby and not the primary database is to keep
> some load away from the primary database.
> Another reason is that an export on the read only standby database is
> always consistent so I do not run into ORA-1555 (hope I'm remembering the
> number for "snapshot too old" coerrectly :-) when running a consistent
> export on a database with havy batch load on it.
>

Since you are on 10g, if someone accidentally drops a table, then the FLASHBACK TABLE TO BEFORE DROP is preferable to importing from a dump file. And if someone accidentally deleted rows from a table, then a Flashback Query might be better to restore those rows. There are many, many more options in 10g for recovering these sorts of things than a dump file. They might bear investigation to determine if they will fit your needs and how they work in your specific environment.

HTH,
Brian

-- 
===================================================================

Brian Peasland
dba_at_nospam.peasland.net
http://www.peasland.net

Remove the "nospam." from the email address to email me.


"I can give it to you cheap, quick, and good.
Now pick two out of the three" - Unknown

Received on Tue Aug 29 2006 - 14:10:24 CDT