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...
<strong>How do I copy everything I need with 'Database copy'?</strong>
Any advice is greatly appreciated!Answer1:
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, <em>including all schemas</em>?
Judging by your question, I'm assuming the latter. If so, RMAN "database duplication" may help you.
See <a href="http://www.oracle-base.com/articles/11g/duplicate-database-using-rman-11gr2.php" rel="nofollow">http://www.oracle-base.com/articles/11g/duplicate-database-using-rman-11gr2.php</a> at Tim Hall's excellent site.Answer2:
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 :
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)</blockquote> Answer3:
An interesting link on this from Oracle documentation:
<a href="http://docs.oracle.com/cd/E11882_01/backup.112/e10642/rcmdupdb.htm#BRADV010" rel="nofollow">http://docs.oracle.com/cd/E11882_01/backup.112/e10642/rcmdupdb.htm#BRADV010</a>