Oracle SQL Developer copy database step by step

Question

I'm having big trouble in copying an Oracle DB to the same server but with another name, to use as a development DB.

I'm used to SQL Server, I'm new to Oracle (11g).

I wanted to use the 'Database copy' from SQL Developer, but I'm getting errors all the way. First it was about missing tablespaces. Then when I manually created them in my new empty DB the errors were about missing users. I wanted to create the users manually, but then I first needed to create missing roles. When all that was done, it failed on missing indexes...

How do I copy everything I need with 'Database copy'?

Any advice is greatly appreciated!

Solution 1

SQL Developer copy will only copy objects between schemas. Do you want to just make a copy of a schema? Or a whole new database, including all schemas?

Judging by your question, I'm assuming the latter. If so, RMAN "database duplication" may help you.

See http://www.oracle-base.com/articles/11g/duplicate-database-using-rman-11gr2.php at Tim Hall's excellent site.

Solution 2

The best way for you is to create a new user :

Launch MSDOS Shell Connect to your database using system manager account sqlplus / as sysdba

Then write these sequences:

CREATE USER user2 IDENTIFIED BY user2password;

GRANT ALL PRIVILEGE TO user2 WITH ADMIN OPTION;

GRANT CONNECT TO user2;

GRANT DBA TO user2;

exit oracle prompt

In MSDOS Shell again, export your current user1 like this :

exp user1/password

or exp user1/password@connectString

if you have a connection string specified in tnsnames.ora Answer all the questions by default, give a name to your export file, and specify that you want to export only the user user1

Then proceed to the importation of the dump in your new user2 like this :

imp user2/password2 fromuser=user1 touser=user2

Answer all the questions by default, give the name to your export file (if you don't change the default folder of CmdShell you will not have to specify the complete folder)

Comments

[Colin 't Hart]

This just creates another schema with the same contents, not a new database.

[Colin 't Hart]

And why grant every privilege under the sun AND DBA to the owner of the second schema?

[SonOfGrey]

Indeed...I need a whole new DB, identical to the source DB. I will check out your link...at first sight it seems easier to follow than some other's I've seen. I'll let you know if it worked (tomorrow). Thanks!

[SonOfGrey]

The instructions are for cloning the DB to another server. I found instructions for cloning to the same server with RMAN, but it's so much more complicated than it should be. I'm really amazed at the horrible procedure you have to go through to just copy a DB. We're almost 2013 you know...one would expect a wizard and a few clicks, like with SQL Server. I think I'm going to have to find someone to do it for me, because I'm wasting precious time on this. Thanks for trying though...

[Colin 't Hart]

Yeah, Amen to that. Oracle really is 1980's technology when it comes to filesystem layout and hoops you have to jump through for what should be common use cases.