0

I've seen lots of examples using network_link to remote servers, getting rid of the middle man create dump, import dump. I need to do this on the same server.

I have a production database. I have a test database. They are both on the same RAC, same servers.

I want to export the production database and put that data into the test database.

I can use expdp to dump the file on the my Linux server and then import.

What I'd like to do is use datapump network_link or some other parameter that will allow me to simply export and import all at once.

Is datapump the wrong tool?

Can this be done?

johnny
  • 19,272
  • 52
  • 157
  • 259
  • Yes, you just need to create the database link; though linking a test and production database might raise some eyebrows. Why would it matter that they're on the same server? – Alex Poole Feb 20 '14 at 18:12
  • because all the examples have remote server. – johnny Feb 20 '14 at 18:33
  • OK, but it doesn't matter - the DB link can be created back to the same DB, never mind to another DB on the same server. There is nothing checking that the underlying host isn't the same - it would be an artificial restriction, which would be impossible to enforce really, as a server can have multiple name and multiple IP addresses - and no reason it would be a problem otherwise. One DB is connecting to a listener (which happens to be on the same box) and that is handing off to a DB (which may be the same or a different DB). As long as there is a valid network path between them it's fine. – Alex Poole Feb 20 '14 at 18:53
  • What about remap_schema? That sounds better to me. – johnny Feb 20 '14 at 18:59
  • `remap_schema` is for when you have tables under one schema in the DB you're exporting from, and you want to import them under a different schema (e.g. moving from `HR` to `SCOTT`). I don't see how that's relevant... maybe you want to do that too, but you'd still need the link if you don't want to create a dump file. Or... do you actually mean you have two schemas in one RAC database, not that you have two databases? That's a very different thing. – Alex Poole Feb 20 '14 at 19:01
  • I think my terminology might be wrong. I get confused with Oracle. I have one RAC instance. I have many "databases," what you may be calling "schema." All the databases/schemas are in the instance in the RAC. We might have an HR schema, a Payroll schema, Baseball_League schema (just to show these are unrelated.) – johnny Feb 20 '14 at 19:05
  • 1
    Right... then yes, you would want to `remap_schema`, whether you had an intermediate file or not, but you can still do this with a DB link - just make it point to the same DB, logged on as the source schema user. You could also grant permissions on the source schema objects and copy things manually, but data pump will be cleaner overall. – Alex Poole Feb 20 '14 at 19:07

0 Answers0