Copying Oracle 11g express Database from one server to another

Question

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??

• Benoit: Lookup imp/exp or starting from Oracle 10, impdp/expdp
• Joachim Pileborg: 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.

Answer 1

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.

• Amit Mittal: expdp command is not recognized in SQL-Plus prompt. I am using Oracle 11g Express edition.
• user1822: @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)
• Amit Mittal: Thanks for the reply. Although I did read the manual that arnep linked, I might have overlooked it.

Answer 2

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).

• user1822: Does Oracle Express support RMAN?
• kubanczyk: It seems to me like RMAN is implicitly included in XE license, isn't it? In terms of support, there is none for XE.

Answer 3

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

Answer 4

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

Answer 5

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.