Backup with exp and imp

Although best practice is to use RMAN, Oracle’s backup utility, you can also use imp and exp export and import a schema in Oracle.

Installing Redcell Synergy creates two new user schemas: netview is the owner for the database (or tablespace) owportal01, synadmin is the owner for the database owsynergy01, the default password for these two user is: dorado

Installation asks for an a oracle user, and this example selects redcell. This selection also appears in the installed.properties file.

User redcell is the owner for the database owdata01 (loaddb creates tablespace owdata01 under user redcell) as follows:

loaddb -u system -w dorado

Executing this command creates owportal01 and owsynergy01

loaddb -u system -w dorado -s

so the complete backup/restore should includes

In this example dorado is the database administrator’s password, and tmdb60 is the SID:

Backup

exp system/dorado@tmdb60 owner=redcell file=redcell.dmp

exp system/dorado@tmdb60 owner=netview file=netview.dmp

exp system/dorado@tmdb60 owner=synadmin file=synadmin.dmp

Restore

imp system/dorado@tmdb60 fromuser=redcell touser=redcell ignore=y file=redcell.dmp

imp system/dorado@tmdb60 fromuser=netview touser=netview ignore=y file=netview.dmp

imp system/dorado@tmdb60 fromuser=synadmin touser=synadmin ignore=y file=synadmin.dmp

You may encounter an ORACLE 2291 error when using command line imp.(Best practice is to use Oracle RMAN.)

For example:

. importing table "RCC_TASK_USAGE_ENTITY"

IMP-00019: row rejected due to ORACLE error 2291

IMP-00003: ORACLE error 2291 encountered

ORA-02291: integrity constraint (REDCELL.FKE609020E14863754) violated - parent key not found

The workaround for this is to find the foreign key reference table and import the parent table first then re-import the problematic table. For example:

imp system/d0rad0 fromuser=redcell touser=redcell ignore=y constraints=n

file=redcell_17012013.dmp tables=rcc_task_usage_entity

There is no substitute for having a DBA. Such an administrator could tell you Oracle has improved on its previous import/export utility with RMAN and Data Pump. Oracle’s manuals explain the use of these utilities.