sqlplus不能连接本地Oracle数据库-蒲公英云

sqlplus不能连接本地Oracle数据库

  最近本地环境上安装了timesten，设置了一些环境变量，直接导致本地的sqlplus连不上数据库了，做了一些测试：

缺失环境变量:

oracle_sid ora11
NS_ADMIN F:\app\Administrator\product\11.2.0\dbhome_1\NETWORK\ADMIN
C:\Users\Administrator>sqlplus test/test
SQL*Plus: Release 11.2.0.2.0 Production on Tue Feb 14 15:05:58 2017
Copyright (c) 1982, 2010, Oracle. All rights reserved.
ERROR:
ORA-12560: TNS:protocol adapter error
Enter user-name:

C:\Users\Administrator>sqlplus test/test@ora112
SQL*Plus: Release 11.2.0.2.0 Production on Tue Feb 14 15:06:34 2017
Copyright (c) 1982, 2010, Oracle. All rights reserved.
ERROR:
ORA-12154: TNS:could not resolve the connect identifier specified
Enter user-name:

只缺失环境变量：
TNS_ADMIN F:\app\Administrator\product\11.2.0\dbhome_1\NETWORK\ADMIN
C:\Users\Administrator>sqlplus test/test
SQL*Plus: Release 11.2.0.2.0 Production on Tue Feb 14 15:19:42 2017
Copyright (c) 1982, 2010, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL>

C:\Users\Administrator>sqlplus test/test@ora112
SQL*Plus: Release 11.2.0.2.0 Production on Tue Feb 14 15:03:16 2017
Copyright (c) 1982, 2010, Oracle. All rights reserved.
ERROR:
ORA-12154: TNS:could not resolve the connect identifier specified
Enter user-name:

只缺失环境变量:
oracle_sid ora11
C:\Users\Administrator>sqlplus test/test@ora112
SQL*Plus: Release 11.2.0.2.0 Production on Tue Feb 14 15:17:36 2017
Copyright (c) 1982, 2010, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL>

C:\Users\Administrator>sqlplus test/test
SQL*Plus: Release 11.2.0.2.0 Production on Tue Feb 14 15:11:54 2017
Copyright (c) 1982, 2010, Oracle. All rights reserved.
ERROR:
ORA-12560: TNS:protocol adapter error
Enter user-name:

listener.ora中：
SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(SID_NAME = CLRExtProc)
(ORACLE_HOME = F:\app\Administrator\product\11.2.0\dbhome_1)
)
)

C:\Users\Administrator>sqlplus test/test
SQL*Plus: Release 11.2.0.2.0 Production on Tue Feb 14 15:23:04 2017
Copyright (c) 1982, 2010, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL>

C:\Users\Administrator>sqlplus test/test@ora112
SQL*Plus: Release 11.2.0.2.0 Production on Tue Feb 14 15:23:08 2017
Copyright (c) 1982, 2010, Oracle. All rights reserved.
ERROR:
ORA-12514: TNS:listener does not currently know of service requested in connect
descriptor

改SID_NAME为ora11，重启监听
SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(SID_NAME = CLRExtProc)
(ORACLE_HOME = F:\app\Administrator\product\11.2.0\dbhome_1)
)
)
两种方式都可以成功。

总结一下：
缺失TNS_ADMIN，sqlplus test/test可以成功。
缺失oracle_sid，sqlplus test/test@ora112可以成功。
缺失TNS_ADMIN，oracle_sid，两个都不成功。
listener.ora SID_NAME错误，sqlplus test/test可以成功。

说明oracle_sid对sqlplus test/test有直接影响。TNS_ADMIN，listener.ora对sqlplus test/test@ora112有直接影响。

listener.ora中的sid_name要与tnsname.ora中的SERVICE_NAME一致。
listener.ora:
SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(SID_NAME = ora11)
(ORACLE_HOME = F:\app\Administrator\product\11.2.0\dbhome_1)
(PROGRAM = extproc)
(ENVS = “EXTPROC_DLLS=ONLY\app\Administrator\product\11.2.0\dbhome_1\bin\oraclr11.dll”)
)
)

tnsname.ora:
ORA115 =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = ora11)
)
)