Oracle远程客户端配置
Oracle安装之前，为了在Oracle主目录有Net Configuration Assist自动正确配置监听等文件，必须删掉因为使用远程客户端而配置的环境变量，否则会通过环境变量在远程客户端的network/admin/下生成配置文件。
Oracle正确安装、创建实例、配置监听之后，会在C:\app\oracle\product\11.1.0\db_2\NETWORK\ADMIN下自动生成三个文件：listener.ora sqlnet.ora tnsnames.ora，各个文件中的内容如下：
listener.ora：
# listener.ora Network Configuration File: C:\app\oracle\product\11.1.0\db_2\network\admin\listener.ora
# Generated by Oracle configuration tools.
LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
(ADDRESS = (PROTOCOL = TCP)(HOST = Qin-PC)(PORT = 1521))
)
)
sqlnet.ora：
# sqlnet.ora Network Configuration File: C:\app\oracle\product\11.1.0\db_2\network\admin\sqlnet.ora
# Generated by Oracle configuration tools.
# This file is actually generated by netca. But if customers choose to
# install "Software Only", this file wont exist and without the native
# authentication, they will not be able to connect to the database on NT.
SQLNET.AUTHENTICATION_SERVICES= (NTS)
NAMES.DIRECTORY_PATH= (TNSNAMES, EZCONNECT)
tnsnames.ora：
# tnsnames.ora Network Configuration File: C:\app\oracle\product\11.1.0\db_2\network\admin\tnsnames.ora
# Generated by Oracle configuration tools.
ORCL =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = Qin-PC)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = orcl)
)
)
如果本机没有安装Oracle数据库，想用客户端连接远程Oracle，步骤如下：
1、下载客户端：
http://www.oracle.com/technetwork/cn/index.html去下载中找instant client
有很多种客户端，建议下载3个，然后解压到一起：basic-jdbc-sqlplus
2、在F:\Java\instantclient_11_2下创建目录、文件\network\admin\tnsnames.ora，在F:\Java\instantclient_11_2\network\admin下有文件tnsnames.ora，文件内容可以从Oracle安装目录中的C:\app\oracle\product\11.1.0\db_2\NETWORK\ADMIN\tnsnames.ora中拷贝。如下：
# tnsnames.ora Network Configuration File: C:\app\oracle\product\11.1.0\db_2\network\admin\tnsnames.ora
# Generated by Oracle configuration tools.
ORCL =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = Qin-PC)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = orcl)
)
)
需要注意的是，红色部分必须 去掉，否则使用PL/SQL Developer连接时会报错：ORA-12154:TNS:could not resolve the connect identifier specified
3、配置环境变量2个：
TNS_ADMIN：F:\Java\instantclient_11_2\network\admin
TNS_LANG：SIMPLIFIED CHINESE_CHINA.ZHS16GBK
注意一点，这里的TNS_LANG的值是远程数据库的ORACLE语言，不知道的可以ssh或者telnet到远程机器,在命令界面输入，用命令行连接到数据库，查询： SELECT * FROM NLS_INSTANCE_PARAMETERS;
4、安装、打开PS/SQL Developer，配置tools->preferences->connection
Oracle Home D:/instantclient_11_2
OCI library D:/instantclient_11_2/oci.dll
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = [ip])(PORT = [port]))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME =[service_name])
)
Oracle正确安装、创建实例、配置监听之后，会在C:\app\oracle\product\11.1.0\db_2\NETWORK\ADMIN下自动生成三个文件：listener.ora sqlnet.ora tnsnames.ora，各个文件中的内容如下：
listener.ora：
# listener.ora Network Configuration File: C:\app\oracle\product\11.1.0\db_2\network\admin\listener.ora
# Generated by Oracle configuration tools.
LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
(ADDRESS = (PROTOCOL = TCP)(HOST = Qin-PC)(PORT = 1521))
)
)
sqlnet.ora：
# sqlnet.ora Network Configuration File: C:\app\oracle\product\11.1.0\db_2\network\admin\sqlnet.ora
# Generated by Oracle configuration tools.
# This file is actually generated by netca. But if customers choose to
# install "Software Only", this file wont exist and without the native
# authentication, they will not be able to connect to the database on NT.
SQLNET.AUTHENTICATION_SERVICES= (NTS)
NAMES.DIRECTORY_PATH= (TNSNAMES, EZCONNECT)
tnsnames.ora：
# tnsnames.ora Network Configuration File: C:\app\oracle\product\11.1.0\db_2\network\admin\tnsnames.ora
# Generated by Oracle configuration tools.
ORCL =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = Qin-PC)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = orcl)
)
)
如果本机没有安装Oracle数据库，想用客户端连接远程Oracle，步骤如下：
1、下载客户端：
http://www.oracle.com/technetwork/cn/index.html去下载中找instant client
有很多种客户端，建议下载3个，然后解压到一起：basic-jdbc-sqlplus
2、在F:\Java\instantclient_11_2下创建目录、文件\network\admin\tnsnames.ora，在F:\Java\instantclient_11_2\network\admin下有文件tnsnames.ora，文件内容可以从Oracle安装目录中的C:\app\oracle\product\11.1.0\db_2\NETWORK\ADMIN\tnsnames.ora中拷贝。如下：
# tnsnames.ora Network Configuration File: C:\app\oracle\product\11.1.0\db_2\network\admin\tnsnames.ora
# Generated by Oracle configuration tools.
ORCL =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = Qin-PC)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = orcl)
)
)
需要注意的是，红色部分必须 去掉，否则使用PL/SQL Developer连接时会报错：ORA-12154:TNS:could not resolve the connect identifier specified
3、配置环境变量2个：
TNS_ADMIN：F:\Java\instantclient_11_2\network\admin
TNS_LANG：SIMPLIFIED CHINESE_CHINA.ZHS16GBK
注意一点，这里的TNS_LANG的值是远程数据库的ORACLE语言，不知道的可以ssh或者telnet到远程机器,在命令界面输入，用命令行连接到数据库，查询： SELECT * FROM NLS_INSTANCE_PARAMETERS;
4、安装、打开PS/SQL Developer，配置tools->preferences->connection
Oracle Home D:/instantclient_11_2
OCI library D:/instantclient_11_2/oci.dll
然后关闭、重启，机名就会出现在PL/SQL Developer的列表里，输入用户名密码，就可以登录远程oracle 11g数据库了。
如果本机安装了Oracle数据库，那么要连接本地数据库，直接安装PL/SQL Developer就好了；如果要连接远程数据库，只需要再在tnsnames.ora配置文件中加上一条远程数据库的配置信息：
[instance_name] =(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = [ip])(PORT = [port]))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME =[service_name])
)
)
（无需另外下载配置远程客户端）
