Oracle

    之前系统是用的ORACLE数据库,这几天出了点小错误码老出现ORA-12535 TNS  operation time out 监听器连接超时的错误,搞了一两天才知道原来自己的IP是自动获取的,每次IP的变化导致了ORACLE监听器客户端(开始我的是以IP定位的)出现了错误,结果我将IP定位改为计算机名(hostname)就好OK。以下是监听器client与server端的实例配置:

 

    Listener.ora、tnsnames.ora这两个文件常常因为格式问题而不好用,我平时都是配置好了留个备份,以后都是拷贝过去改改就好了!嘿嘿~~~
  因为平时使用linux的时候较多,所以有时还会遇到sqlnet.ora这个文件有问题,直接给它删了就好了~~
  有时在windows下装的oracle的listener服务就启动不了,遇到这种情况,直接去oracle的bin目录下面找到lsnrctl.exe那个文件,直接给它运行,弹出来的窗口不要关闭,就可以了。
  Oracle Server端Listener.ora文件(一个instance):
  LISTENER =
  (DESCRIPTION_LIST =
  (DESCRIPTION =
  (ADDRESS_LIST =
  (ADDRESS = (PROTOCOL=TCP)(HOST=hostname)(PORT=1521)) //主机名或IP及端口设置
  )
  )
  )
  SID_LIST_LISTENER =
  (SID_LIST =
  (SID_DESC =
  (SID_NAME = database1) // database1为数据库instance名
  (ORACLE_HOME = /opt/oracle/product/10.1.0) //oracle安装目录,根据安装情况更改
  (PROGRAM = extproc)
  )
  )
  Oracle Server端Listener.ora文件(两个instance):
  LISTENER =
  (DESCRIPTION_LIST =
  (DESCRIPTION =
  (ADDRESS_LIST =
  (ADDRESS = (PROTOCOL=TCP)(HOST=hostname)(PORT=1521))
  )
  )
  )
  SID_LIST_LISTENER =
  (SID_LIST =
  (SID_DESC =
  (SID_NAME = database1) // database1为数据库instance1名
  (ORACLE_HOME = /opt/oracle/product/10.1.0)
  (PROGRAM = extproc)
  )
  (SID_DESC =
  (SID_NAME = database2) // database2为数据库instance2名
  (ORACLE_HOME = /opt/oracle/product/10.1.0)
  (PROGRAM = extproc)
  )
  )
  Oracle Server端监听器启动、停止、查看状态命令:
  $ lsnrctl start
  $ lsnrctl stop
  $ lsnrctl status
  Oracle Client端Tnsnames.ora文件:
  ORALOCAL = //别名oralocal
  (DESCRIPTION =
  (ADDRESS_LIST =
  (ADDRESS = (PROTOCOL = TCP)(HOST = hostname)(PORT = 1521))
  )
  (CONNECT_DATA =
  (SERVICE_NAME = database1) //database1为SID名
  )
  )

Oracle Client端检查监听器连接状态用命令:
  $ tnsping 别名
  例如:用oracle用户执行命令 $ tnsping oralocal

 

LISTENER = (
  DESCRIPTION_LIST =
  (DESCRIPTION = (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1))
  (ADDRESS = (PROTOCOL = TCP)(HOST = 0.0.0.0)(PORT = 1521)) )
  )
  执行lsnrctl reload使Listener生效:
  su – oracle
  lsnrctl reload
  LSNRCTL for Linux: Version 10.2.0.4.0 - Production on 09-FEB-2009 13:59:38 Copyright (c) 1991, 2007, Oracle.
  All rights reserved. Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC1)))
  The command completed successfully
  lsnrctl status
  LSNRCTL for Linux: Version 10.2.0.4.0 - Production on 12-FEB-2009 08:56:00
  Copyright (c) 1991, 2007, Oracle. All rights reserved.
  Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC1)))
  STATUS of the LISTENER
  ------------------------
  Alias LISTENER
  Version TNSLSNR for Linux: Version 10.2.0.4.0 - Production
  Start Date 03-JAN-2009 03:47:39
  Uptime 40 days 5 hr. 8 min. 20 sec
  Trace Level off
  Security ON: Local OS Authentication
  SNMP OFF
  Listener Parameter File /ora10g/network/admin/listener.ora
  Listener Log File /ora10g/network/log/listener.log
  Listening Endpoints Summary……
  (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=0.0.0.0)(PORT=1521)))
  Services Summary……
  Service "PLSExtProc" has 1 instance(s)。
  Instance "PLSExtProc", status UNKNOWN, has 1 handler(s) for this service……
  Service "test" has 1 instance(s)。
  Instance "test", status UNKNOWN, has 1 handler(s) for this service……
  The command completed successfully
  6, 编辑Oracle所在计算机中的tnsnames.ora文件,便于建立dblink.注意,此tnsnames的配置可以支持tnsping,但是不能支持sqlplus登录,只用于dblink:
  vi /ora10g/network/admin/tnsnames.ora
  test =
  (DESCRIPTION =
  (ADDRESS = (PROTOCOL = TCP)(HOST = 127.0.0.1)(PORT = 1521))
  (CONNECT_DATA =
  (SID = test)
  )
  (HS = OK))
  7, 在Oracle Database建立dblink:
  create public database link MYSQL
  connect to "mysql username" identified by "mysql pwd"
  using '(DESCRIPTION =
  (ADDRESS = (PROTOCOL = TCP) (HOST = 127.0.0.1) (PORT =1521) )
  (CONNECT_DATA = (SID= test) )
  (HS=OK)
  )';
  要注意用户名和密码处需要用双引号,否则Oracle所传输的都是大写字母,可能无法登录进入MySQL.
  8, 由于MySQL中的表名的大小写敏感,因此需要在进行SQL查询时对表名用双引号扩起来
  select * from "tablename"@test

  • 0
    点赞
  • 0
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值