Thursday, February 16, 2012

ORA-31655,ORA-39154 while Datapump Import


Today while I’m performing SYSFM schema import using MYCORP user on my test server

$ cat /etc/redhat-release
Red Hat Enterprise Linux Server release 5.4 (Tikanga)

I already have the Dump files from prod server and ready to do import in test server with my parfile.

Vi  sysfm_impdp.par

DIRECTORY=DPUMP_OMEGA_DIR1
DUMPFILE=DPUMP_OMEGA_DIR1:SYSFM_%U.dmp
LOGFILE=LOGFILE_OMEGA_dir1:sysfm_impdp.log
PARALLEL=10
SCHEMAS=SYSFM
JOB_NAME=sysfm_Import


Impdp  mycorp/*****  parfile=sysfm_impdp.par
Import: Release 11.2.0.2.0 - Production on Thu Feb 16 15:45:25 2012

Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.
Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production
With the Partitioning, Real Application Clusters and Automatic Storage Management options
ORA-31655: no data or metadata objects selected for job
ORA-39154: Objects from foreign schemas have been removed from import
Master table "MYCORP"."SYSFM_IMPORT”successfully loaded/unloaded
Starting "MYCORP"."SYSFM_IMPORT":  mycorp/********  parfile=sysfm_impdp.par
Job "MYCORP"."SYSFM_IMPORT" successfully completed at 15:47:11

I checked the database and found no schema has imported. Then after struggling for some time I came to know the solution for above error as the "user have no privileges" to perform on the another user then I granted ‘IMP_FULL_DATABASE’ to the MYCORP user from which I’m performing datapump schema import operation.

SQL> select  banner  from  v$version;
BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production
PL/SQL Release 11.2.0.2.0 - Production
CORE    11.2.0.2.0      Production
TNS for Linux: Version 11.2.0.2.0 - Production
NLSRTL Version 11.2.0.2.0 – Production

SQL> grant IMP_FULL_database to  MYCORP;
Grant succeeded.

And started import again, Now the Import operation has running successfully.
Check dba_datapump_jobs  to confirm import  job is running

SQL> select OWNER_NAME,JOB_NAME,STATE from dba_datapump_jobs;

OWNER_NAME          JOB_NAME                STATE
--------------------       --------------------    --------------------
MYCORP                     SYSFM_IMPORT         EXECUTING


0 comments:

Post a Comment

Auto Scroll Stop Scroll