在从库上使用exp导出数据时报错

[oracle@pldb02 oracle]$ exp 'powerdesk/xxxxxx"@powerdes' file=/oracle/powerdes-20170921.dmp log=/oracle/powerdes-20170921.log grants=y

Export: Release 11.2.0.4.0 - Production on Thu Sep 21 13:06:51 2017

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


EXP-00056: ORACLE error 12154 encountered
ORA-12154: TNS:could not resolve the connect identifier specified
EXP-00000: Export terminated unsuccessfully

使用tnsping检查:

[oracle@pldb02 oracle]$ tnsping powerdes
TNS Ping Utility for Linux: Version 11.2.0.4.0 - Production on 21-SEP-2017 13:07:11

Copyright (c) 1997, 2013, Oracle.  All rights reserved.

Used parameter files:
/oracle/app/oracle/product/11.2.0.4/dbhome_1/network/admin/sqlnet.ora

TNS-03505: Failed to resolve name

果然是配置文件里面写的名字和oracle里面的实例名不一致
检查配置:

[oracle@pldb02 powerdes]$ echo $ORACLE_HOME
/oracle/app/oracle/product/11.2.0.4/dbhome_1
[oracle@pldb02 powerdes]$ cd /oracle/app/oracle/product/11.2.0.4/dbhome_1/network/admin/
[oracle@pldb02 admin]$ cat tnsnames.ora 
# tnsnames.ora Network Configuration File: /oracle/app/oracle/product/11.2.0/dbhome_1/network/admin/tnsnames.ora
# Generated by Oracle configuration tools.


PD1 =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = 10.251.x.11)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = powerdes)
    )
  )

PD2 =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = 10.251.x.13)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = powerdes)
    )
  )

果然这里的名字是PD2,而不是实例名powerdes,在测

[oracle@pldb02 admin]$ tnsping PD2

TNS Ping Utility for Linux: Version 11.2.0.4.0 - Production on 21-SEP-2017 13:17:49

Copyright (c) 1997, 2013, Oracle.  All rights reserved.

Used parameter files:
/oracle/app/oracle/product/11.2.0.4/dbhome_1/network/admin/sqlnet.ora


Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = 10.251.x.13)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = powerdes)))
OK (30 msec)

现在修改为PD2重新执行exp导出成功,同时要保证数据库状态是OPEN,要么也不能导出·

[oracle@pldb02 oracle]$ exp 'powerdesk/xxxxxx"@PD2' file=/oracle/powerdes-20170921.dmp log=/oracle/powerdes-20170921.log grants=y
Export: Release 11.2.0.4.0 - Production on Thu Sep 21 13:21:24 2017

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


Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Export done in ZHS16GBK character set and AL16UTF16 NCHAR character set

About to export specified users ...
. exporting pre-schema procedural objects and actions
. exporting foreign function library names for user POWERDESK 
. exporting PUBLIC type synonyms
. exporting private type synonyms
. exporting object type definitions for user POWERDESK 
EXP-00097: Object type "POWERDESK"."ARR_TENANT_PRICE" is not in a valid state, type will not be exported
About to export POWERDESK's objects ...
. exporting database links
. exporting sequence numbers
. exporting cluster definitions
. about to export POWERDESK's tables via Conventional Path ...
. . exporting table                ADDR_BOOK_GROUP          1 rows exported
. . exporting table               ADDR_BOOK_MEMBER          0 rows exported
. . exporting table                APP_ATTACH_FILE    3129941 rows exported
. . exporting table         APP_ATTACH_FILE_170616    2927330 rows exported
. . exporting table     APP_ATTACH_FILE_UHIS_TODEL        825 rows exported
. . exporting table                  APP_DICT_DATA       3715 rows exported
. . exporting table                  APP_DICT_TYPE        328 rows exported
带符号 * 的表示必填项