A "how to" guideline for exporting and importing Product Manager Schemas from and to an Oracle 11G.

Informatica Software Development or Support might ask you to provide the data of a certain installation in order to reproduce bugs or performance issues. This guide shall help support, partners and consulting to provide this.

The terms backup/restore which are usually used in the SQL Server world map to export/import in Oracle. The actual backup/restore functionality of Oracle is bound to a specific oracle instance (more or less) and is only meant for error/data loss recovery, but not to transfer the data to a different database instance.

Preparation

The whole export/import process can not be performed by the sys user at all. Therefore we suggest to create a new user for administrative purposes. In the following sections we will refer to this user as hlradmin. The effective permissions a user needs to have differ between the export and import process, thus they are described in the corresponding sections.

Create the hlradmin user

CREATE USER "HLRADMIN" IDENTIFIED BY "heiler" PROFILE "DEFAULT" ACCOUNT UNLOCK;

Directory Object

The directory object in oracle maps to a physical directory path. This directory is used to store the dumpfile(s). The directory object is needed for the export as well as the import.

Create the directory object

CREATE DIRECTORY "HLR_DIR" AS 'c:\heiler\oracle\impexp'

Export

User Roles

The hlradmin user needs the following privileges to export data

resource
connect
select permissions on all objects for the MAIN, MASTER and SUPPLIER users

Grant needed privileges to hlradmin user

GRANT CONNECT  TO "HLRADMIN";
GRANT RESOURCE TO "HLRADMIN";
GRANT EXP_FULL_DATABASE TO "HLRADMIN";

Execute export datapump

Import

User Roles

The hlradmin user needs the following privileges to perform the import:

imp_full_database
create user
create any table
create any index
create any sequence
create any procedure
create tablespace

Grant needed privileges to hlradmin user

GRANT IMP_FULL_DATABASE TO "HLRADMIN"; 
GRANT CREATE USER TO "HLRADMIN";
GRANT CREATE ANY TABLE TO "HLRADMIN";
GRANT CREATE TABLE WITH ADMIN OPTION TO "HLRADMIN";
GRANT CREATE ANY INDEX TO "HLRADMIN";
GRANT CREATE INDEX WITH ADMIN OPTION TO "HLRADMIN";
GRANT CREATE ANY SEQUENCE TO "HLRADMIN";
GRANT CREATE SEQUENCE WITH ADMIN OPTION TO "HLRADMIN";
GRANT CREATE ANY PROCEDURE TO "HLRADMIN";
GRANT CREATE PROCEDURE WITH ADMIN OPTION TO "HLRADMIN";
GRANT CREATE TABLESPACE TO "HLRADMIN";

Tablespaces

To import a dump file which contains the tablespaces of the MAIN, MASTER and SUPPLIER schemas, you need to have corresponding tablespaces in the target database. You can execute the following pl/sql snippet for each needed schema in order to create the tablespaces.

Create empty tablespaces

 -- Temporary tablespace 
CREATE TEMPORARY TABLESPACE "<SCHEMA_NAME>_TEMP"  
   TEMPFILE 'c:/heiler/oracle/impexp/<SCHEMA_NAME>_Temp.dbf'  
   SIZE 1024M REUSE
   AUTOEXTEND ON NEXT 128M  
   MAXSIZE UNLIMITED  
   EXTENT MANAGEMENT LOCAL 
   UNIFORM SIZE 1M;
 -- Data tablespace 
CREATE TABLESPACE "<SCHEMA_NAME>_DATA" 
   DATAFILE 'c:/heiler/oracle/impexp/<SCHEMA_NAME>_Data.dbf'
   SIZE 1024M REUSE 
   AUTOEXTEND ON NEXT 128M 
   MAXSIZE UNLIMITED 
   EXTENT MANAGEMENT LOCAL 
   SEGMENT SPACE MANAGEMENT AUTO;
 -- Separate index tablespace 
CREATE TABLESPACE "<SCHEMA_NAME>_INDEX" 
   DATAFILE 'c:/heiler/oracle/impexp/<SCHEMA_NAME>_Index.dbf' 
   SIZE 1024M REUSE 
   AUTOEXTEND ON NEXT 128M 
   MAXSIZE UNLIMITED 
   EXTENT MANAGEMENT LOCAL 
   SEGMENT SPACE MANAGEMENT AUTO;

Execute Import Datapump

Parameter file

# the directory object
DIRECTORY=HLR_DIR
# the name of the ora dump file, located in the physical file directory mapped to DIRECTORY
DUMPFILE=MYEXPORT01.DMP 
# the schemas to be imported 
# attention: you have to import all PIM - Server schemas (there're dependencies between the schema objects) 
SCHEMAS=CUST_MAIN,CUST_MASTER,CUST_SUPPLIER 
# specify the schema name mappings, necessary if schema names to be imported are different from local schema names 
# schemas are created automatically if they don't exist 
# pattern: 
# schema name in dump file:local schema name REMAP_SCHEMA=CUST_MAIN:HPM_MAIN,CUST_MASTER:HPM_MASTER,CUST_SUPPLIER:HPM_SUPPLIER 
# specify the tablespace name mappings, necessary if tablespace names to be imported are different from local tablespace names 
# tablespace are NOT created automatically, you have to create them before 
# pattern: 
# tablespace name in dump file:local tablespace name (must exist) 
REMAP_TABLESPACE= CUST_MAIN_INDEX:HPM_MAIN_INDEX,CUST_MAIN_DATA:HPM_MAIN_DATA,CUST_MAIN_TEMP:HPM_MAIN_TEMP,CUST_MASTER_INDEX:HPM_MASTER_INDEX,CUST_MASTER_DATA:HPM_MASTER_DATA,CUST_MASTER_TEMP:HPM_MASTER_TEMP, CUST_SUPPLIER_INDEX:HPM_SUPPLIER_INDEX,CUST_SUPPLIER_DATA:HPM_SUPPLIER_DATA,CUST_SUPPLIER_TEMP:HPM_SUPPLIER_TEMP 
# use this option to only create the sql file 
# no import will be executed 
# SQLFILE=SQL_IMPORT.SQL

execute import cmd

impdp USERID=HLRADMIN/heiler parfile="<path to parameter file>" | pause

If you get errors regarding insufficient access rights of user HLRADMIN, try to execute with SYSTEM-User:

impdp USERID=SYSTEM/heiler parfile="<path to parameter file>" | pause

While importing, you might get several Oracle errors regarding the users .._HPM_BULKLOAD and .._HPM_REPSTORE:

ORA-39083: Objekttyp OBJECT_GRANT konnte nicht erstellt werden, Fehler:

ORA-01917: Benutzer oder Funktion KRAMP_HPM_REPSTORE ist nicht vorhanden

Fehlerhafte SQL ist:

GRANT FLASHBACK ON "KRAMP_HPM_MAIN"."User" TO "KRAMP_HPM_REPSTORE"

ORA-39083: Objekttyp OBJECT_GRANT konnte nicht erstellt werden, Fehler:

ORA-01917: Benutzer oder Funktion KRAMP_HPM_BULKLOAD ist nicht vorhanden

Fehlerhafte SQL ist:

GRANT ALTER ON "KRAMP_HPM_MAIN"."UnitSystemUnitMapLang" TO "KRAMP_HPM_BULKLOAD"

They do not have any impact on the import result, so you can ignore them.