3 Replies Latest reply: Oct 6, 2012 7:18 AM by Balazs Papp RSS

    error in IMPDP

    $phinx19
      Hi All,

      OS: AIX
      DB:10G

      I am doing a schema level import following is the command that i am using:
      impdp system/##### dumpfile=PostEOD.dmp logfile=PreEOD_IMP_06102012_new.log directory=DMPDIR remap_schema=usr_1:usr_2
      after this iam getiing the follwing error:

      Processing object type SCHEMA_EXPORT/FUNCTION/ALTER_FUNCTION
      ORA-39082: Object type ALTER_FUNCTION:"DPPMG"."FN_EOD" created with compilation warnings
      Processing object type SCHEMA_EXPORT/PROCEDURE/ALTER_PROCEDURE
      ORA-39083: Object type ALTER_PROCEDURE failed to create with error:
      ORA-04052: error occurred when looking up remote object ETLINK.FWD_CONTRACT_VIEW@ETLINK
      ORA-00604: error occurred at recursive SQL level 3
      ORA-12154: TNS:could not resolve the connect identifier specified
      Failing sql is:

      ALTER PROCEDURE "DPPMG"."SP_PULLETFWDCONTRACT" COMPILE PLSQL_OPTIMIZE_LEVEL= 2 PLSQL_CODE_TYPE= INTERPRETED PLSQL_DEBUG= FALSE PLSCOPE_SETTINGS= 'IDENTIFIERS:ALL' REUSE SETTINGS TIMESTAMP '2012-09-12 1
      ORA-39083: Object type ALTER_PROCEDURE failed to create with error:
      ORA-04052: error occurred when looking up remote object ETLINK.POPULATE_UTIL_DTLS@ETLINK
      ORA-00604: error occurred at recursive SQL level 3
      ORA-12154: TNS:could not resolve the connect identifier specified
      Failing sql is:

      ALTER PROCEDURE "DPPMG"."SP_PUSH_UTILIZATIONDTL" COMPILE PLSQL_OPTIMIZE_LEVEL= 2 PLSQL_CODE_TYPE= INTERPRETED PLSQL_DEBUG= FALSE PLSCOPE_SETTINGS= 'IDENTIFIERS:ALL' REUSE SETTINGS TIMESTAMP '2012-08-0
      ORA-39083: Object type ALTER_PROCEDURE failed to create with error:
      ORA-04052: error occurred when looking up remote object ETLINK.UPDATE_UTIL_DTLS@ETLINK
      ORA-00604: error occurred at recursive SQL level 3
      ORA-12154: TNS:could not resolve the connect identifier specified
      Failing sql is:

      ALTER PROCEDURE "DPPMG"."SP_UPDATE_UTILIZATIONDTL" COMPILE PLSQL_OPTIMIZE_LEVEL= 2 PLSQL_CODE_TYPE= INTERPRETED PLSQL_DEBUG= FALSE PLSCOPE_SETTINGS= 'IDENTIFIERS:ALL' REUSE SETTINGS TIMESTAMP '2012-08-0
      Processing object type SCHEMA_EXPORT/VIEW/VIEW
      Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/REF_CONSTRAINT
      Processing object type SCHEMA_EXPORT/TABLE/TRIGGER
      Processing object type SCHEMA_EXPORT/TABLE/INDEX/FUNCTIONAL_AND_BITMAP/INDEX
      Processing object type SCHEMA_EXPORT/TABLE/INDEX/STATISTICS/FUNCTIONAL_AND_BITMAP/INDEX_STATISTICS
      Processing object type SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
      Job "DPSMG"."SYS_IMPORT_FULL_01" completed with 5 error(s) at 12:26:41


      I am logged in the server and the first half of the import was proper with no errors, i have just pasted the errr part.

      Neeed your inputs

      Regards
      Sphinx
        • 1. Re: error in IMPDP
          phaeus
          Hello,
          its look like you are missing a database link.
          ORA-04052: error occurred when looking up remote object ETLINK.UPDATE_UTIL_DTLS@ETLINK

          Try to create this database link or maybe you need to set you global database name to etlink according to the source database.

          regards
          Peter
          • 2. Re: error in IMPDP
            javeedkaleem dba
            expdp copied the ddl of db link

            db link connects to xyz machine whose tns details is updated in this machine and the db link owner or user who use the db link has access to use the db link
            which work perfectly.

            after import.(IMPDP)

            db link imported but tns details may not exists.
            either this user does not have access for those table connecting using db link

            normally for this kind of tasks first backup the current db links ddl and then start refreshing schema
            once import is completed then remove all db links and recreate the dblinks which you backed up.

            Thanks & Regads
            Kaleem.
            • 3. Re: error in IMPDP
              Balazs Papp
              "ORA-12154: TNS:could not resolve the connect identifier specified"

              this is the root cause, dblink uses non-existent TNS entry