Import/Export of Databases

Information

• For the import/export of databases (i.e. for Oracle more specifically: user schemas as part of an Oracle database), the data pump tools expdp or impdp should be used. Here, you will find the documentation:
  • Overview: http://docs.oracle.com/cd/E11882_01/server.112/e22490/dp_overview.htm#SUTIL100
  • Export: http://docs.oracle.com/cd/E11882_01/server.112/e22490/dp_export.htm#SUTIL200
  • Import: http://docs.oracle.com/cd/E11882_01/server.112/e22490/dp_import.htm#SUTIL300

Note

• Unless there are valid circumstances leaving you with no alternative, you should avoid using exp or imp: for the following reasons.
  • As of Oracle DB version 11.1, exp is outdated and is not supported anymore. Only imp is still supported for dump imports from older systems. As a result, no empty tables are exported via exp anymore so that they are missing when importing again later.
    • See also: Ask Tom
  • imp does not change the MView-Owner for materialized views (FROMUSER/TOUSER has no influence here). Therefore, the access from PLANTA fails since the view does not belong to the PLANTA DB user but another user who perhaps does not exist on the target system.
    • This can be corrected by SYSDBA rights afterwards, see also here.

Examples for "secure" import/export Commands for Dragging or Loading a Dump

Note

• To proceed as follows, the corresponding roles are required.

Loading a Database with =impdp

• If an old schema is to be overwritten, it is recommended to empty it first by means of the drop_schema.sql script.
• You have to set several parameters to successfully load a dump (Oracle Import Parameter see link above).
• Example:

impdp <DB_User>/<Password>@<SID> DIRECTORY=<Data_Pump_Dir> DUMPFILE=<Dump Filename>.dmp PARALLEL=4 SCHEMAS=<Source_User> REMAP_SCHEMA=<Source_User>:<DB_User> REMAP_TABLESPACE=<Source_TABLESPACE>:<DB_TABLESPACE> LOGFILE=<Log Filename>.log TABLE_EXISTS_ACTION=REPLACE TRANSFORM=SEGMENT_ATTRIBUTES:N

• Placeholders enclosed by greater/less than sign (< or >) are to be replaced by the appropriate values.

Explanation of parameters and values

• Connect string: Who is connecting to which database
  • <DB_User>
    • Here, the PLANTA DB user should be used. Therefore, the import is restricted to the schemas to which the user has the required rights. As a result, nothing is imported that is not part of it, see also SCHEMAS parameter.
  • <Password>
    • Password of <DB_User>
  • <SID>
    • The Oracle system ID of the corresponding database

Parameter	Value
DIRECTORY	DB directory object installed on DB server The dump file is loaded from the file system associated with DIRECTORY, e.g.: AUDIT_DIR points to /tmp directory; to be looked up via View dba_directories The dump file must have been transferred to this directory (on DB server) before loading.
DUMPFILE	Dump file name
PARALLEL	Specification of the maximum number of parallel processes to accelerate exporting, e.g. 4, depending on the number of server processor cores (only available in the Oracle database 11g enterprise edition)
SCHEMAS	Specification of several schemas to be imported (divided by comma), as they are available in the dump, i.e. usually the name of the source user Multiple schemas are imported so they all have to be considered in the remap command as well.
REMAP_SCHEMA	The source user from which the dump was created, has to be remapped to the user to which you wish to import.
REMAP_TABLESPACE	The table space used in the source system has to be remapped to the table space of the current database For PLANTA this is usually DATA
LOGFILE	Name of the logfiles Naming analog dump files, impdp put first, extension .log
TABLE_EXISTS_ACTION	REPLACE: in case a table already exists, it is deleted and replaced by the file in the dump
TRANSFORM	SEGMENT_ATTRIBUTES:N: the segment attributes are generally not adopted so that only the settings of the target database instance are applied.

Command example

impdp Neuer_Benutzer/ppms@Meine_Datenbank PARALLEL=4 DIRECTORY=AUDIT_DIR DUMPFILE=10_2_Mein_System.dmp LOGFILE=impdp_Mein_System.log SCHEMAS=Exportierter_Benutzer REMAP_SCHEMA=Exportierter_Benutzer:Neuer_Benutzer REMAP_TABLESPACE=Tablespace_des_exportierten_Benutzers:Zu_benutzender_Tablespace TABLE_EXISTS_ACTION=REPLACE TRANSFORM=SEGMENT_ATTRIBUTES:N

Exporting Data with =expdp

• In order to successfully create a dump that can be worked with, some important parameters have to be set.
• Example

expdp <DB_User>/<Password>@<SID> DIRECTORY=<Data_Pump_Dir> DUMPFILE=<Dump Filename>.dmp PARALLEL=4 SCHEMAS=<DB_User> CONTENT=ALL EXCLUDE=STATISTICS VERSION=10.2 LOGFILE=<Log Filename>.log

• Placeholders enclosed by greater/less than sign (< or >) are to be replaced by the appropriate values.

Explanation of parameters and values

• Connect string: Who is connecting to which database
  • <DB_User>
    • Here, the PLANTA DB user should be used. Hence, the export is restricted to the PLANAT DB user schema. As a result, nothing is exported that is not part of it, see also SCHEMAS parameter.
  • <Password>
    • Password of <DB_User>
  • <SID>
    • The Oracle system ID of the corresponding database

Parameter	Value
DIRECTORY	DB directory object installed on the DB server the dump file is dropped in the file system directory associated with DIRECTORY, e.g. AUDIT_DIR or DATA_PUMP_DIR; to be looked up via View dba_directories
DUMPFILE	Name of the dump file Example for name convention expdp_<DB-Version>_<customer_license>_<DB_user>_<PLANTA/PPMS version>.dmp
PARALLEL	Specification of the maximum number of parallel processes to accelerate exporting, e.g. 4, depending on the number of server processor cores (only available in the Oracle database 11g enterprise edition)
SCHEMAS	Specification of one or more schemas to be exported for which you need to have access rights
CONTENT	ALL is the default value Metadata as well as data are to be exported
EXCLUDE	STATISTICS - transferring statistics may cause problems. They are generated again automatically on the target system. Therefore they are excluded.
VERSION	10.2 - Specification of the Oracle DB version up to which the dump is supposed to be compatible. I.e., if 10.2 is specified, the dump created can be loaded into every Oracle DBMS from version 10.2.
LOGFILE	Name of the logfile Name convention analog dumpfile, extension .log

Command example

expdp PLANTA_PROD/Geheim.124@ORADB DIRECTORY=AUDIT_DIR DUMPFILE=expdp_10.2_444_PLANTA_PROD_3904.dmp PARALLEL=4 SCHEMAS=PLANTA_PROD CONTENT=all EXCLUDE=statistics VERSION=10.2 LOGFILE=expdp_10.2_444_PLANTA_PROD_3904.log

Note

• • The log file has to be sent to PLANTA along with the dump.