2

I'm used to working with SQL Server and when I want to copy a DB there, I just need a handful of clicks in the wizard and voila...a complete copy of the DB, without taking the source DB offline.

We now also have an Oracle 11g because some machines require it, and I want to make a copy of the database. Just a copy on the same server, to use as a test DB for my software development.

All instructions that I find are pages full of steps, using RMAN or not, you have to write scripts, use command line stuff...I'm amazed at how inefficient such a common task is when using Oracle.

Aren't there any easy ways of copying a DB? Maybe just exporting everything to a SQL file, then editing it to use another DB name, and then executing it again?

I see that in SQL Developer you can choose 'Database Copy...' from the Tools menu, but it asks a destination connection. How can I select a destination when creating the destination DB is the whole point of running the wizard? Or is a connection not the same as a DB?

Thanks for helping me out here!

Improve this question

1 Answer 1

Reset to default
1

You're generally going to need a new database to copy the data to, and the data could be copied with datapump export/import. There aren't many ways of getting around that I'm afraid, but one option that you might consider is to make more use of VM's such as Oracle's own VirtualBox, as they can be cloned very easily with an absolute certainty of byte-by-byte fidelity.

Incidentally, one problem in making logical copies (via export/import) of a database is that it's easy to end up with a different physical pattern to the table and indexes, which can lead to unexpected differences in query optimisation.

Improve this answer
Sign up to request clarification or add additional context in comments.

3 Comments

I just created a new empty DB. If I understand it correctly I now need to make a new service name for it on the server, then make a new connection to the service name, and finally I can use SQL Developer > Tools > Database Copy...right? Or did I forget something? Thanks again!
When you start the database instance it should register itself with the listener automatically. I haven't used the SQL Developer COPY functionality, but maybe you need to create a new connection to the new database first? How you do so depends on whether you want to reference a tnsnames.ora file or use the "Basic" connection specification in which you name the host (or IP), port number (if not the 1521 default) and service name.
Ok I managed to make a connection to the empty sample DB and registered a net service name. I can now access it in my SQL Developer under a new connection. Tried Database Copy, but I get several namespace errors and nothing seems to have happened...I'll try and Google about this functionality a bit further...

Your Answer

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

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.