0

We're automating the nightly transfer of several databases from production into a "T-1" (or "yesterday") environment. It takes a while currently:

  1. the source server has to dump the stripes,
  2. which then need to be transferred to the target server,
  3. which then needs to load them.

Would rather all three steps ran in parallel -- with the dumps written into a pipe or a socket, from where the target could read them directly...

In both MySQL and PostgreSQL the dumps are sent to the requesting client program (mysqldump and pg_dump respectively), which could save the data into a file and/or push it to stdout:

mysqldump SOURCEDATABSE | mysql TARGET

For Sybase (and thus for MS SQL) there is a way to arrange for same.

Is there something like this for Oracle too -- or must it always be a file (or multiple files) local to the server?

The underlying OS on both source and target is Linux -- maybe, I can somehow specify a host:port combination to Oracle instead of /directory?

Our Oracle servers run version 19.

Update: it looks like the network_link-argument to expdp can be used to collapse steps 1. and 2. into one, which is good. But is there any way to make step 3. happen in parallel too?

Improve this question
6
  • Running datapump over the database (network) link does all three steps at once. Ideally you would write your job as a stored procedure in the target database, with a link back to the source, then use DBMS_SCHEDULER to control execution. That way the whole process is OS-independent.
    – pmdba
    Sep 26, 2022 at 23:34
  • Could you provide a sample command-line in a proper answer? I can only find examples, where the network_link is used to create or load existing dumps. How would I skip the storing them to disk?
    – Mikhail T.
    Sep 27, 2022 at 1:29
  • This is way beyond command-line scripting; you need to go back to the documentation. See the docs for DBMS_DATAPUMP here (docs.oracle.com/en/database/oracle/oracle-database/19/arpls/…) and oracle-base.com (oracle-base.com/articles/misc/data-pump-api) for examples of what I'm talking about.
    – pmdba
    Sep 27, 2022 at 1:56
  • As far as I can tell, the datapump mechanism can be used to combine only the steps 1. and 2. or the steps 2. and 3. Plenty of examples exist for both -- including the one you gave. but I'm looking to combine all three steps to run in parallel -- bypassing the intermediate storing of the dumps on disk altogether...
    – Mikhail T.
    Sep 27, 2022 at 3:41
  • When run as a stored procedure, datapump pulls the data over the link and inserts in one process. When executed in this manner there is no file stored on local disk. Please trust the DBA with 30 years experience. The RMAN duplication suggested below is also a way to go. Depending on your architecture and the needs for the duplicated data, materialized views using a database link could perhaps also work.
    – pmdba
    Sep 27, 2022 at 10:16

2 Answers 2

Reset to default
2

if you need the dump only for your "refresh" of your t-1 environment, why don't use rman duplicate? Oracle 19 rman duplicate

Improve this answer
3
  • The T-1 environment could be locally modified during the day -- before it is reloaded with production again. So it cannot be a refresh, but must be a full reload each time.
    – Mikhail T.
    Sep 27, 2022 at 15:13
  • rman duplicate do a Full Rebuild of the Target Database! And it is much faster as datapump.
    – MarcelS
    Sep 27, 2022 at 18:48
  • Thanks, we'll try both then here!
    – Mikhail T.
    Sep 28, 2022 at 16:30
2

This is simple. In target:

drop user schema_to_be_overwritten cascade;

Then the below command combines all the 3 steps you need:

impdp network_link=db_link_to_source schemas=source_schema \
    remap_schema=source_schema:schema_to_be_overwritten;

It simply copies the whole schema from source over the network directly in the database. Yes, it will use a directory object, but that is needed only for the logfile of this operation.

Depending on the amount of data and objects, other methods may be more efficient.

Improve this answer
5
  • Thanks! This looks promising. What if the from- and the to-schemas have different names?
    – Mikhail T.
    Sep 27, 2022 at 15:29
  • 1
    @MikhailT. Just add the following parameter remap_schema=old_name1:new_name1,old_name2:new_name2,....
    – Balazs Papp
    Sep 27, 2022 at 15:30
  • Also, can the direction be reversed -- to have expdp on the source push data into the target, rather than impdp on the target pull data in? Granting the prod-user access to the lower database will be easier, than the other way around...
    – Mikhail T.
    Sep 27, 2022 at 15:45
  • @MikhailT. No .
    – Balazs Papp
    Sep 27, 2022 at 15:47
  • Thanks, I proposed an edit based on your comment about remap_schema. I think, it makes your answer more general.
    – Mikhail T.
    Sep 27, 2022 at 15:55

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.