Tell me more ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.
up vote 3 down vote favorite

Basically, I am a MS-SQL developer,and relatively new to Oracle.

Even after great deal of research, I was unable to find any way of copying entire database from one server instance to another.

In MS-SQL this is usually done through "backup database" and "restore database" sql query. We can even use external tools such as Management studio to create scripts.

But, How this can be done in Oracle 11g express? Any ideas??

share|improve this question
1  
Lookup imp/exp or starting from Oracle 10, impdp/expdp – Benoit Jan 3 '12 at 12:52
I don't know if they are still available (many years since I worked with Oracle databases), but I migrated databases between servers and/or upgrades between different Oracle versions using the command-line commands exp and imp. See this FAQ for information about exporting/importing. – Joachim Pileborg Jan 3 '12 at 12:55

migrated from stackoverflow.com Jan 3 '12 at 13:02

5 Answers

up vote 3 down vote accepted

You do not state which version you use, so I assume you use version 10.x or 11.x.

Do a full export of your database using Oracle Data Pump, existing since Oracle 10: expdp (export dump) and then re-import it into another database using impdb (import dump).

Use expdp help=y to get the full set of parameters. Parameters are given in the form param=value, not --param=value as one would expect.

share|improve this answer
expdp command is not recognized in SQL-Plus prompt. I am using Oracle 11g Express edition. – Amit Mittal Jan 3 '12 at 13:29
1  
@AmitMittal: expdb is a separate tool (similar to sqlplus). It is not a SQL command (which is clearly explained in the manual that arnep linked to) – a_horse_with_no_name Jan 3 '12 at 15:10
Thanks for the reply. Although I did read the manual that arnep linked, I might have overlooked it. – Amit Mittal Jan 3 '12 at 16:42
up vote 3 down vote

If you are interested in copying all database files bit-by-bit, then use Recovery Manager (RMAN).

RMAN is used for backups and, much more importantly, restores. But it can also copy database to another server (or to another instance on the same server for that matter) - google for "auxiliary".

It operates on entire database, and not on the level of schema (where expdp/impdp is the tool of choice - it is slower but simpler to use).

share|improve this answer
+1 for RMAN not exp[dp] – Jack Douglas♦ Jan 3 '12 at 13:32
Does Oracle Express support RMAN? – a_horse_with_no_name Jan 3 '12 at 15:09
It seems to me like RMAN is implicitly included in XE license, isn't it? In terms of support, there is none for XE. – kubanczyk Jan 3 '12 at 17:02
up vote 1 down vote

One way, using Oracle SQL Developer Database Export. It creates sql statements file, you can use to recreate your database. Note that, this way is most similar to mysql developers but it can take a lot of time compared to other methods if your database is big.

0ToolsDatabaseExport 1SourceDestinationEmpty 1SourceDestinationFilled 2TypesToExport 3SpecifiyObjects1 3SpecifiyObjects2 3SpecifiyObjects3 4SpecifiyData1 4SpecifiyData2 4SpecifiyData3 5ExportSummary 6ExportRunning 7ExportingFinished

share|improve this answer
up vote 0 down vote

on unix i use to do this. for creating the dump:

exp <connection string> file=mydb.dmp

take this dump to the server you want to paste.

imp <connection string> file=mydb.dmp

Note:Both should be similar databases.i mean both should have same table,views,cursors....etc

share|improve this answer
up vote 0 down vote

Easiest option I've found is just to use the Enterprise Manager (EM), logged in as SYS. Navigate to Data Movement > Clone Database. It will use rman to copy the database files either from a running database or from backups.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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