Re: Datapump and read only database

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@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@xxxxxxxxxxxxxxxxxxx
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
.

Relevant Pages

  • Re: Connecting to a remote sbs from a website?
    ... Your solution only works if your web app doesn't need to write data to the database. ... SQL server has always supported SQL authentication which does NOT require an SBS username/password. ... And if you aren't, somebody will find a way to break you, regardless of how well you think you've secured your master database. ...
    (microsoft.public.windows.server.sbs)
  • Re: SQL 2005 Express Connection Problem
    ... sql2005 support auto attaching a database in the connect string. ... The remote site is not a company ... An error has occurred while establishing a connection to the server. ... When connecting to SQL Server 2005, this failure may be caused by the ...
    (microsoft.public.dotnet.framework.aspnet)
  • Challenging ADO.NET situation (long)
    ... need to import those records into the local database. ... depending on a "type" column in the master (ie some records ... independantly, but data is always transfered from remote to local, not the ...
    (microsoft.public.dotnet.framework.adonet)
  • RE: Challenging ADO.NET situation (long)
    ... need to import those records into the local database. ... depending on a "type" column in the master (ie some records ... independantly, but data is always transfered from remote to local, not the ...
    (microsoft.public.dotnet.framework.adonet)
  • Re: fc3 & mysql clustering
    ... > fault tolerance/redundancy is not just an issue of multiple mysql databases. ... > machine is the slave being tied to the master, ... The real trick is redundant database servers. ... > server goes down the other server takes over it. ...
    (Fedora)