How can you transfer Oracle 11g to a new server with Oracle 11g?

To transfer the Oracle 11g database to a new server with the Oracle 11g database, use the ORACLE database export and import tools.

To export the database:

1. After starting the sql client, e.g .:

  • sqlplus / nolog
  • connect userid = system / pass as sysdba;

check the directories created for the ORA engine to which the database can save files in the operating system:

<SQL> SELECT directory_name,directory_path FROM dba_directories; (by default, the backup will be performed to the directory pointed to by “DATA_PUMP_DIR”)

2. To export the database, create a new directory to save the database dump or use the existing DATA_PUMP_DIR:

  • creating a new directory in the Oracle database to which export will be made:

<SQL>CREATE DIRECTORY asistbackup AS ‚C:\oracle_backup’;

<SQL>grant read,write on directory asistbackup to asist5;

  • database export from the Windows command line:

<CMD>expdp asist5/pass DIRECTORY=asistbackup DUMPFILE=asist.dmp  LOGFILE=asist_export.log

The data of the exported user should be saved in the required directory (here, for example, asistbackup).

To import the database:

1. For the new base:

– check the ORA engine catalogs and their paths,

– create the desired DIR.

2. Import the file exported using the previous export procedure.

The file to be imported should be placed in the appropriate directory indicated by DATA_PUMP_DIR or in the newly created folder:

  • creating a new directory in the Oracle database to be imported:

<SQL> CREATE DIRECTORY asistbackup AS ‚C:\oracle_backup’;

  • database import from the Windows command line:

<CMD> impdp system/pass DIRECTORY=asistbackup logfile=import.log dumpfile=asist.dmp

By default, the schema will be imported under the same name as in the source database, if you want to change it, you can use the additional switches:

remap_schema – is equivalent to the syntax: fromuser – touser

remap_tablespace –  as above to be used when changing the target user space

Link to helpful article (import syntax a bit simpler):

http://orafaq.com/wiki/Datapump

Attention!!!

Depending on your version of the target ORACLE 11 server and licensing mode, there is a possibility of errors during import:

1. Error caused by enabling space saving features for empty data structures in the ORA engine configuration.

You should then:

– execute the command on the target ORACLE instance:

  • <SQL> ALTER SYSTEM SET DEFERRED_SEGMENT_CREATION=FALSE;

– retry import.

2. Error related to the lack of support for the above-mentioned database engine function

You should then:

– import by enforcing backward compatibility with the previous version of server import, for example:

  • <CMD> impdp system/pass Directory=asistkopia dumpfile=asist_kopia_export.dmp schemas=asist5 version=11.1 logfile=asist_kopia_import.log