UDI-12154 ORA-12154问题解决

问题背景最近新建环境impdp导入数据库数据的时候发现该问题，带上了SID后就报了这个错。[oracle@POC-ORACLE ~]$ impdp dcps/dcps@upbs directory=dump_dir dumpfile=dcps20210630.dmpImport: Release 19.0.0.0.0 - Production on Mon Jul 5 17:26:56 2021V

忙碌的菠萝

10006人浏览 · 2021-07-05 17:48:24

忙碌的菠萝 · 2021-07-05 17:48:24 发布

问题背景

最近新建环境impdp导入数据库数据的时候发现该问题，带上了SID后就报了这个错。

[oracle@POC-ORACLE ~]$ impdp dcps/dcps@upbs directory=dump_dir dumpfile=dcps20210630.dmp

Import: Release 19.0.0.0.0 - Production on Mon Jul 5 17:26:56 2021
Version 19.3.0.0.0

Copyright (c) 1982, 2019, Oracle and/or its affiliates.  All rights reserved.

UDI-12154: operation generated ORACLE error 12154
ORA-12154: TNS:could not resolve the connect identifier specified

UDI-12154: 操作产生了 ORACLE 错误 12154
ORA-12154: TNS: 无法解析指定的连接标识符

问题解决

原因

tns报错，检查发现tnsnames.ora中数据库连接标识符在换了数据库后没有更改，还是orcl导致该错误

[oracle@POC-ORACLE u01]$ vi ./database/network/admin/tnsnames.ora

LISTENER_ORCL =
  (ADDRESS = (PROTOCOL = TCP)(HOST = POC-ORACLE)(PORT = 1521))


ORCL =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = POC-ORACLE)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = orcl)
    )
  )

解决方法

方式一：修改tns标识符为SID

LISTENER_UPBS =
  (ADDRESS = (PROTOCOL = TCP)(HOST = POC-ORACLE)(PORT = 1521))


UPBS =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = POC-ORACLE)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = upbs)
    )
  )

修改完后可以直接导入

[oracle@POC-ORACLE ~]$ impdp dcps/dcps@upbs directory=dump_dir dumpfile=dcps20210630.dmp

Import: Release 19.0.0.0.0 - Production on Mon Jul 5 17:42:16 2021
Version 19.3.0.0.0

Copyright (c) 1982, 2019, Oracle and/or its affiliates.  All rights reserved.

Connected to: Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Master table "DCPS"."SYS_IMPORT_FULL_01" successfully loaded/unloaded
Starting "DCPS"."SYS_IMPORT_FULL_01":  dcps/********@upbs directory=dump_dir dumpfile=dcps20210630.dmp 
Processing object type SCHEMA_EXPORT/SYSTEM_GRANT

方式二：导入时去掉SID即可

[oracle@POC-ORACLE ~]$ impdp dcps/dcps directory=dump_dir dumpfile=dcps20210630.dmp

Import: Release 19.0.0.0.0 - Production on Mon Jul 5 17:29:16 2021
Version 19.3.0.0.0

Copyright (c) 1982, 2019, Oracle and/or its affiliates.  All rights reserved.

Connected to: Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Master table "DCPS"."SYS_IMPORT_FULL_01" successfully loaded/unloaded
Starting "DCPS"."SYS_IMPORT_FULL_01":  dcps/******** directory=dump_dir dumpfile=dcps20210630.dmp