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