4 Replies Latest reply: Jan 8, 2009 3:22 PM by 638015 RSS

    import problem ora-12154 please help

    581304
      I have Oracle 10g database. the backup file created by oracle 9i.

      COMMAND:
      imp.exe userid=reyestruser/psw@dbglobal file=D:\backups\LBAKI.dat fromuser=reyestruser touser=BAKIUSER

      I successfuly did it more than 10 times six days ago. Now but I can not. I dont know why?

      ERROR MESSAGE:
      IMP-00058: ORACLE error 12154 encountered
      ORA-12154: TNS:could not resolve service name
      IMP-00000: Import terminated unsuccessfully

      p.s.
      there is one policy violations. I dont know is there a relationship with error and this violation
      Policy Violations
      EXECUTE UTL_FILE privileges to PUBLIC     Security     
      Oracle recommends that you revoke EXECUTE privileges on powerful packages from PUBLIC     1     
      PackageUTL_FILE     Jan 6, 2009 5:23:56 PM     Jan 6, 2009 5:23:44 PM
        • 1. Re: import problem ora-12154 please help
          Dean Gagne
          Here is the information regarding the error that you got. I'm not sure if it helps,but it seems like the tnslistener doesn't understand dbglobal which is part of your login string.

          Dean

          12154, 00000, "TNS:could not resolve the connect identifier specified"
          // *Cause:  A connection to a database or other service was requested using
          // a connect identifier, and the connect identifier specified could not
          // be resolved into a connect descriptor using one of the naming methods
          // configured. For example, if the type of connect identifier used was a
          // net service name then the net service name could not be found in a
          // naming method repository, or the repository could not be
          // located or reached.
          // *Action:
          // - If you are using local naming (TNSNAMES.ORA file):
          // - Make sure that "TNSNAMES" is listed as one of the values of the
          // NAMES.DIRECTORY_PATH parameter in the Oracle Net profile
          // (SQLNET.ORA)
          // - Verify that a TNSNAMES.ORA file exists and is in the proper
          // directory and is accessible.
          // - Check that the net service name used as the connect identifier
          // exists in the TNSNAMES.ORA file.
          // - Make sure there are no syntax errors anywhere in the TNSNAMES.ORA
          // file. Look for unmatched parentheses or stray characters. Errors
          // in a TNSNAMES.ORA file may make it unusable.
          // - If you are using directory naming:
          // - Verify that "LDAP" is listed as one of the values of the
          // NAMES.DIRETORY_PATH parameter in the Oracle Net profile
          // (SQLNET.ORA).
          // - Verify that the LDAP directory server is up and that it is
          // accessible.
          // - Verify that the net service name or database name used as the
          // connect identifier is configured in the directory.
          // - Verify that the default context being used is correct by
          // specifying a fully qualified net service name or a full LDAP DN
          // as the connect identifier
          // - If you are using easy connect naming:
          // - Verify that "EZCONNECT" is listed as one of the values of the
          // NAMES.DIRETORY_PATH parameter in the Oracle Net profile
          // (SQLNET.ORA).
          // - Make sure the host, port and service name specified
          // are correct.
          // - Try enclosing the connect identifier in quote marks.
          //
          // See the Oracle Net Services Administrators Guide or the Oracle
          // operating system specific guide for more information on naming.
          • 2. Re: import problem ora-12154 please help
            581304
            Thanks for answer, but there is not any connection problem. I can connect database, also i can export from this database, but i cannot import.
            • 3. Re: import problem ora-12154 please help
              Nigel C.L. Thomas
              Mehi

              You say there is no connection problem - but obviously there is one; you just have to work out why.

              1) check the connect string; can you use the same string from the same user and directory, and with the same environment, with SQL*Plus (eg use command line sqlplus - starting plus from an icon may set the environment differently
              sqlplus user/password@dbglobal
              2) check the service by using tnsping:
              tnsping dbglobal
              3) Does the failure stop the import immediately?
              Yes: the definition of dbglobal you give may be incorrect - perhaps you are picking up the wrong tnsnames.ora

              No: do some objects get imported successfully? Are there any database links in the export that you are importing? If so, this error message could be generated if the database links used a connect string that wasn't recognised by your Oracle server.

              4) Are you using a local or a remote database? If it is remote, remember that its own tnsnames.ora may be different from yours (that can also happen with a local database, depending on ORACLE_HOME settings etc).

              Try all those and see if that helps

              Regards Nigel
              • 4. Re: import problem ora-12154 please help
                638015
                try this in cmd prompt assuming you are on a windows o/s,
                putting single quotes around the filepath imp instead of imp.exe:
                imp userid=reyestruser/psw@dbglobal file='D:\backups\LBAKI.dat' fromuser=reyestruser touser=BAKIUSER