分类目录归档：数据库

Oracle JDBC Thin Driver Formats

发表于 2011 年 11 月 12 日 由 惜分飞

虽然自己对java不熟悉，但是做为一个dba，经常会遇到java和数据库连接的配置文件，参考朋友网站，记录下来以便以后查找

格式一:  Oracle JDBC Thin using a ServiceName
jdbc:oracle:thin:@//<host>:<port>/<service_name> 
Example: jdbc:oracle:thin:@//192.168.2.1:1521/xifenfei
 
格式二: Oracle JDBC Thin using an SID
jdbc:oracle:thin:@<host>:<port>:<SID> 
Example: jdbc:oracle:thin:192.168.2.1:1521:xff
--注意这里的格式，@后面有//, 这是与使用SID的主要区别。

格式三：Oracle JDBC Thin using a TNSName
jdbc:oracle:thin:@<TNSName> 
Example: jdbc:oracle:thin:@GL 
--Support for TNSNames was added in the driver release 10.2.0.1

来源:Java JDBC Thin Driver 连接 Oracle 三种方法说明

发表在 Oracle | 评论关闭

Linux/Win中ORACLE_SID错误时表现

发表于 2011 年 11 月 11 日 由 惜分飞

Linux系统中

[oracle@report ~]$ echo $ORACLE_SID
wlwjj
[oracle@report ~]$ export ORACLE_SID=xifenfei
[oracle@report ~]$ sqlplus / as sysdba

SQL*Plus: Release 10.2.0.4.0 - Production on Fri Nov 11 23:36:27 2011

Copyright (c) 1982, 2007, Oracle.  All Rights Reserved.

Connected to an idle instance.

SQL> exit
Disconnected
[oracle@report ~]$  sqlplus abc/abc

SQL*Plus: Release 10.2.0.4.0 - Production on Fri Nov 11 23:42:32 2011

Copyright (c) 1982, 2007, Oracle.  All Rights Reserved.

ERROR:
ORA-01034: ORACLE not available
ORA-27101: shared memory realm does not exist
Linux-x86_64 Error: 2: No such file or directory

Win系统

E:\oracle\11_2_0\NETWORK\ADMIN>ECHO %ORACLE_SID%
xff

E:\oracle\11_2_0\NETWORK\ADMIN>SET ORACLE_SID=xifenfei

E:\oracle\11_2_0\NETWORK\ADMIN>sqlplus / as sysdba

SQL*Plus: Release 11.2.0.1.0 Production on 星期五 11月 11 23:31:14 2011

Copyright (c) 1982, 2010, Oracle.  All rights reserved.

ERROR:
ORA-12560: TNS: 协议适配器错误


请输入用户名:

E:\oracle\11_2_0\NETWORK\ADMIN>sqlplus chf/xifenfei

SQL*Plus: Release 11.2.0.1.0 Production on 星期五 11月 11 23:34:01 2011

Copyright (c) 1982, 2010, Oracle.  All rights reserved.

ERROR:
ORA-12560: TNS: 协议适配器错误


请输入用户名:
发表在 Oracle | 评论关闭

SQLNET.AUTHENTICATION_SERVICES参数说明

发表于 2011 年 11 月 11 日 由 惜分飞

一、官方文档说明

作用
Use the parameter SQLNET.AUTHENTICATION_SERVICES to enable one or more authentication services. 
If authentication has been installed, 
it is recommended that this parameter be set to either none or to one of the authentication methods.

默认值
None

一般可选值
NONE for no authentication methods. A valid username and password can be used to access the database.
ALL for all authentication methods
NTS for Windows NT native authentication(An authentication method that enables 
a client single login access to a Windows NT server and a database running on the server)

为了加深对这几个参数的理解，通过实验证明，这几个参数在不同的系统中的作用

二、win系统

Microsoft Windows [版本 6.1.7601]
版权所有 (c) 2009 Microsoft Corporation。保留所有权利。

C:\Windows\system32>e:

E:\>cd E:\oracle\11_2_0\NETWORK\ADMIN

#sqlnet.ora文件不存在情况
E:\oracle\11_2_0\NETWORK\ADMIN>dir sqlnet.ora
 驱动器 E 中的卷没有标签。
 卷的序列号是 38D0-2A35

 E:\oracle\11_2_0\NETWORK\ADMIN 的目录

找不到文件

E:\oracle\11_2_0\NETWORK\ADMIN>sqlplus / as sysdba

SQL*Plus: Release 11.2.0.1.0 Production on 星期五 11月 11 22:13:57 2011

Copyright (c) 1982, 2010, Oracle.  All rights reserved.

ERROR:
ORA-01031: 权限不足


请输入用户名:

#NTS情况
E:\oracle\11_2_0\NETWORK\ADMIN>more sqlnet.ora
SQLNET.AUTHENTICATION_SERVICES=(NTS)

E:\oracle\11_2_0\NETWORK\ADMIN>sqlplus / as sysdba

SQL*Plus: Release 11.2.0.1.0 Production on 星期五 11月 11 22:16:20 2011

Copyright (c) 1982, 2010, Oracle.  All rights reserved.


连接到:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
With the Partitioning, Oracle Label Security, OLAP, Data Mining,
Oracle Database Vault and Real Application Testing options

#NONE情况
E:\oracle\11_2_0\NETWORK\ADMIN>more sqlnet.ora
SQLNET.AUTHENTICATION_SERVICES=(NONE)

E:\oracle\11_2_0\NETWORK\ADMIN>sqlplus / as sysdba

SQL*Plus: Release 11.2.0.1.0 Production on 星期五 11月 11 22:17:18 2011

Copyright (c) 1982, 2010, Oracle.  All rights reserved.

ERROR:
ORA-01031: 权限不足


请输入用户名:

#ALL情况
E:\oracle\11_2_0\NETWORK\ADMIN>more sqlnet.ora
SQLNET.AUTHENTICATION_SERVICES=(ALL)

E:\oracle\11_2_0\NETWORK\ADMIN>sqlplus / as sysdba

SQL*Plus: Release 11.2.0.1.0 Production on 星期五 11月 11 22:18:02 2011

Copyright (c) 1982, 2010, Oracle.  All rights reserved.

ERROR:
ORA-12641: 验证服务无法初始化


请输入用户名:

三、linux系统

[oracle@report ~]$ cd /opt/oracle/product/10.2.0/db_1/network/admin/
#NTS情况
[oracle@report admin]$ more sqlnet.ora 
SQLNET.AUTHENTICATION_SERVICES = (NTS)
[oracle@report admin]$ sqlplus / as sysdba

SQL*Plus: Release 10.2.0.4.0 - Production on Fri Nov 11 22:03:51 2011

Copyright (c) 1982, 2007, Oracle.  All Rights Reserved.

ERROR:
ORA-01031: insufficient privileges


Enter user-name: 

#NONE情况
[oracle@report admin]$ more sqlnet.ora
SQLNET.AUTHENTICATION_SERVICES = (NONE)
[oracle@report admin]$ sqlplus / as sysdba

SQL*Plus: Release 10.2.0.4.0 - Production on Fri Nov 11 22:04:31 2011

Copyright (c) 1982, 2007, Oracle.  All Rights Reserved.

ERROR:
ORA-01031: insufficient privileges


Enter user-name: 

#ALL情况
[oracle@report admin]$ more sqlnet.ora
SQLNET.AUTHENTICATION_SERVICES = (ALL)
[oracle@report admin]$ sqlplus / as sysdba

SQL*Plus: Release 10.2.0.4.0 - Production on Fri Nov 11 22:05:07 2011

Copyright (c) 1982, 2007, Oracle.  All Rights Reserved.


Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

#不存在sqlnet.ora文件情况
[oracle@report admin]$ ll sqlnet.ora
ls: sqlnet.ora: No such file or directory
[oracle@report admin]$ sqlplus / as sysdba

SQL*Plus: Release 10.2.0.4.0 - Production on Fri Nov 11 22:05:41 2011

Copyright (c) 1982, 2007, Oracle.  All Rights Reserved.


Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

四、补充说明
1、在win系统中使用all，提示ORA-12641，不是很清楚原因
2、在nts只有在win系统中有用，linux中无用
3、当不存在sqlnet.ora文件时，linux中可以正常登录，win中不能

发表在 Oracle 监听 | 评论关闭