Exporting database on oracle

Question

Hi,

I have a DB on oracle on Windows Server 2003. How do I export it with all the data and put it into other Windows server?

Answer 1

You can use Oracle Data Pump to export and import database. Quote from documentation:

Oracle Data Pump is a feature of Oracle Database 11g Release 2 that enables very fast bulk data and metadata movement between Oracle databases.

Procedure is like this:

1. Export existing database using expdp utility
2. Install Oracle database server on new Windows server
3. Import database on new server using impdp utility

Check this link: Oracle Data Pump. There you will find complete documentation and examples how to use this utility.

Answer 2

Use RMAN to take a full backup. Then restore it on the new server.

See Clone using RMAN Article

Answer 3

If you are wanting to create an exact copy of an existing database on a new sever of the same operating system (though not necessarily the same O/S version) and the same Oracle version, the quickest and least problematic method is to just copy the database files. This is often referred to as database cloning, and it is a common method DBAs use to setup development and test databases that are intended to be exact duplicates of production databases.

1. Stop all instances of the database on the existing system. You could login to each instance "as sysdba" using SQLPlus and run the "shutdown immediate" command. You could also stop the Windows Services for the instances. They are named OracleServicesid where "sid" is the instance name. Usually, there is just one instance, but there could be multiple instances to a single database. All instances must be stopped for this procedure.
2. Locate the database files. Look for an "oradata" folder somewhere below the Oracle root folder and then find the folder for the database sid in there. (There could be multiple oradata folders. You need to find the one that has the folder named for the SID of your database.) There are also the files in the Admin folder for the sid as well as the %ORACLE_HOME%/database folder. If DBCA had been used to create the database, then the location of all of these files varies by the Oracle version.
3. Once you have identified all of the files for the database, you can use any method at your disposal to copy these files to the same locations on the new server. (Note: The database files, control files, and redo logs must be placed in the same locations (i.e., file system paths) where they exist on the old server. Otherwise, configuration files must be changed and commands must be run to alter the database's internal file paths.) The parameter file (initSID.ora) and server parameter file (spfileSID.ora) must be placed in the %ORACLE_HOME%/database folder.
4. On the new sever, you must run the oradim utility. (Note: oradim is an Oracle utility that is specific to Windows and is used to create, maintain, and delete instance services.) Here is a sample command:

   oradim -new -sid yourdbsid -startmode automatic

5. Startup the database with SQLPlus, and you should be in business.

This is a general overview of the process, but it should help you get the job done quickly and easily. The problem with other tools is the need to create an empty database on the target server before loading the data by whatever means. If the target server has a different version of Oracle, it will be necessary to run data dictionary scripts to upgrade or downgrade the database. (Note: A downgrade may not always be possible.) If the new server has a different O/S, then the above procedure would require additional steps that would significantly increase its complexity.

It also possible to duplicate a database using RMAN. Google the words "clone oracle database using rman" to get some good sites on how this is done using that tool. If you are not already using RMAN, the procedure I have described above would probably be the way to go.

Kind regards, Opus