How to make a backup from (PL /) SQL over the network?

Question

Oracle

Task : It is necessary to make a backup of the database data (without a schema). The source database cannot be changed, i.e. this is a ridonly backup.

Context : Orakla data backup to an arbitrary database over the network. Now I am writing the integration of Oracle with Postgres.

Resources : only a connection string, and the ability to connect to the base with maximum rights. Those. There is no ssh to the server, you can’t read the backup file via ssh from a hard file, and in general, Venda can suddenly stand on the server.

Question : Is there any fast proven way to pull data under such conditions? Maybe someone has a ready-made open-source solution lying around? %)

The word “fast” is present here because simply selecting pieces of N rows (via rownum or row_number()) and forwarding to the target base is unnecessarily slow.

If someone has a desire to discuss a more global topic - copying from bazka to bazka over the network with runtime optimization, wellcome.

Thanks

Answer 1

impdp,expdp - religion does not allow using ???

Answer 2

In this case, for backup exp.
And for transfer to postgre sulukta through odbc/jdbc. There is little you can think of here.

Answer 3

As already mentioned, your choice is Data Pump. And, in your case, there are two different ways to use it. You can set up a DB Link between databases and use Data Pump in Network Export/Import mode (with some restrictions on column types). Or you can run expdp(.exe) on the recipient base and connect to the source base by specifying a TNS-Alias. Also note that for a selective backup, Data Pump supports flexible configuration options - you can export only certain objects (tables in your case), or you can also export certain sets of table rows (although, in fairness, the old exp (.exe) supported the Where condition) . Well, the sweetest thing - because. the question you were looking for was “How to make a backup from (PL /) SQL over the network?” - Data Pump can perform all its functions (and a bunch of other things, which cannot be done through the expdp/impdp utilities) via the API. Everything is in the documentationofficial and "squeeze" ). I will only note that you can implement real business logic on Data Pump, in this sense it has gone very far from an ordinary database export tool. Of the minuses, however, there is an insane amount of bugs, it may take quite a long time to make it work as it should. In addition, if there is no access to Metalink, finding specific patches for it will be a big problem.