date:20140525
auth:Jin
platform :windows
一、服务端启动服务和创建账号
# su - oracle
$ lsnrctl start
$ sqlplus / as sysdba
SQL> startup
SQL> alter user sys identified by admin; #修改sys密码
User altered.
SQL> create user jin identified by com123; #创建用户
SQL> grant connect to jin; #授予用户权限
SQL> conn jin/com123
Connected.
SQL> show user
USER is "JIN
连接测试
$ sqlplus jin/com123@edrsr12p1.us.oracle.com/orcl
服务端监听配置的位置
$ cat /u01/app/oracle/product/11.2.0/dbhome_1/network/admin/listener.ora
# listener.ora Network Configuration File: /u01/app/oracle/product/11.2.0/dbhome_1/network/admin/listener.ora
# Generated by Oracle configuration tool
SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(SID_NAME = orcl)
(ORACLE_HOME = /u01/app/oracle/product/11.2.0/dbhome_1)
(PROGRAM = extproc)
)
(SID_DESC =
(GOLBAL_DBNAME= orcl)
(ORACLE_HOME = /u01/app/oracle/product/11.2.0/dbhome_1)
(SID_NAME=orcl)
)
)
ADR_BASE_LISTENER = /u01/app/oracl
服务端命名服务的位置
$ cat /u01/app/oracle/product/11.2.0/dbhome_1/network/admin/tnsnames.ora
# tnsnames.ora Network Configuration File: /u01/app/oracle/product/11.2.0/dbhome_1/network/admin/tnsnames.ora
# Generated by Oracle configuration tools.
ORCL =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = edrsr12p1.us.oracle.com)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = orcl.us.oracle.com)
)
)
二、客户端配置
1.解压win32_11gR2_client.zip
2.默认安装
3.测试连通性
D:appjinproduct11.2.0client_4BINsqlplus.exe jin/com123@edrsr12p1.us.oracle.com/orcl
SQL> show user
USER 为 "SYS"
D:appjinproduct11.2.0client_4BINsqlplus.exe sys/admin@edrsr12p1.us.oracle.com/orcl as sysdba
SQL> show user
USER 为 "SYS"
4.使用网络配置助手配置或者Net Manger
client只需配置服务命名
配置保存在
D:appjinproduct11.2.0client_4NETWORKADMIN nsnames.ora
MYORACLE =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.234.128)(PORT = 1521))
)
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = myoracle)
(INSTANCE_NAME = orcl)
)
)
5.安装PLSQL
tool-perference
D:appjinproduct11.2.0client_4BIN
D:appjinproduct11.2.0client_4BINoci.dll
6.连接
报错提示：ora 12514 TNS 监听程序当前无法识别连接描述符中请求的服务
问题分析sqlplus可以登录,LSQL无法登陆.说明服务端没有问题，还是client配置问题
仔细对比配置
服务端
(SERVICE_NAME = orcl.us.oracle.com)
客户端
(SERVICE_NAME = myoracle)
修改client中 MYORACLE的SERVICE_NAME为orcl.us.oracle.com保存
重新连接，正常了
总结：
搞了一天，很郁闷，中间看来几篇文章，都配置监听了。感觉oracle满烦的。
配置参考文章不要看多了，1篇即可，最多不超过两篇。
涉及的包尽量使用官方的。
对oracle不了解造成的，思路完全和mysql/sql server不一样。有些东西有点面向对象的意思。
处理问题，要坚持，不要放弃。只有坚持才能解决问题。