date:20140525
auth:Jin
一、服务端启动服务和创建账号
# 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:\app\jin\product\11.2.0\client_4\BIN\sqlplus.exe
jin/com123@edrsr12p1.us.oracle.com/orcl
SQL> show user
USER 为
"SYS"
D:\app\jin\product\11.2.0\client_4\BIN\sqlplus.exe
sys/admin@edrsr12p1.us.oracle.com/orcl as sysdba
SQL> show user
USER 为
"SYS"
4.使用网络配置助手配置或者Net
Manger
client只需配置服务命名
配置保存在
D:\app\jin\product\11.2.0\client_4\NETWORK\ADMIN\tnsnames.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:\app\jin\product\11.2.0\client_4\BIN
D:\app\jin\product\11.2.0\client_4\BIN\oci.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不一样。有些东西有点面向对象的意思。
处理问题，要坚持，不要放弃。只有坚持才能解决问题。