金沙国际平台登录-金沙国际会员登录

热门关键词: 金沙国际平台登录,金沙国际会员登录

--//利用共享服务器模式.,tnsname.ora文件配置都正

配置好rac后,两个节点用tnsping都可以ping通,但用sqlplus连接都出现以下错误,不知道哪里出了问题[oracle@rac2 admin]$ lsnrctl statusLSNRCTL for Linux: Version 9.2.0.4.0 - Production on 04-MAR-2008 08:32:26Copyright (c) 1991, 2002, Oracle Corporation. All rights reserved.Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=rac2)(PORT=1521)))STATUS of the LISTENER------------------------Alias LISTENERVersion TNSLSNR for Linux: Version 9.2.0.4.0 - ProductionStart Date 04-MAR-2008 08:29:03Uptime 0 days 0 hr. 3 min. 22 secTrace Level offSecurity OFFSNMP OFFListener Parameter File /home/oracle/9.2.0.4/network/admin/listener.oraListener Log File /home/oracle/9.2.0.4/network/log/listener.logListening Endpoints Summary... (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=rac2)(PORT=1521)))Services Summary...Service "PLSExtProc" has 1 instance(s). Instance "PLSExtProc", status UNKNOWN, has 1 handler(s) for this service...Service "esal" has 2 instance(s). Instance "rac1", status READY, has 1 handler(s) for this service... Instance "rac2", status READY, has 1 handler(s) for this service...The command completed successfully[oracle@rac2 admin]$ lsnrctl serviceLSNRCTL for Linux: Version 9.2.0.4.0 - Production on 04-MAR-2008 08:32:32Copyright (c) 1991, 2002, Oracle Corporation. All rights reserved.Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=rac2)(PORT=1521)))Services Summary...Service "PLSExtProc" has 1 instance(s). Instance "PLSExtProc", status UNKNOWN, has 1 handler(s) for this service... Handler(s): "DEDICATED" established:0 refused:0 LOCAL SERVERService "esal" has 2 instance(s). Instance "rac1", status READY, has 1 handler(s) for this service... Handler(s): "DEDICATED" established:0 refused:0 state:ready REMOTE SERVER (address=(protocol=tcp)(host=rac1)(port=1521)) Instance "rac2", status READY, has 1 handler(s) for this service... Handler(s): "DEDICATED" established:0 refused:0 state:ready LOCAL SERVERThe command completed successfully[oracle@rac2 admin]$ sqlplus cy/zh@esalSQL*Plus: Release 9.2.0.4.0 - Production on Tue Mar 4 08:32:45 2008Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.ERROR:ORA-12154: TNS:could not resolve service name

今天做Linux下DG配置的时候,遇到一个现象,tnsname.ora文件配置都正常,tnsping也正常,监听也正常,但是仍然报ORA-12514错误:

[20170825]不启动监听远程能连接数据库吗2.txt

Enter user-name: ERROR:ORA-01017: invalid username/password; logon denied

 

--//曾经写过一篇不启动监听连接数据库的帖子:
--//利用共享服务器模式.
--//昨天测试备库启动drcp,突然想到这个跟共享模式有一些类似.自己也测试看看.

Enter user-name: cy/zh@rac1ERROR:ORA-12154: TNS:could not resolve service name

SQL> set lin 130 pages 130 

1.环境:
SYS@book> @ &r/ver1
PORT_STRING                    VERSION        BANNER

SP2-0157: unable to CONNECT to ORACLE after 3 attempts, exiting SQL*Plus[oracle@rac2 admin]$ sqlplus cy/zh@rac2SQL*Plus: Release 9.2.0.4.0 - Production on Tue Mar 4 08:32:59 2008Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.ERROR:ORA-12154: TNS:could not resolve service name

SQL> select dest_id,error from v$archive_dest;


Enter user-name:

 


按照如下过程,一般都能解决:在虚拟机内开cmd,用lsnrctl status查看监听器监听的服务 如看到监听服务“orcl”,那在本机的tns配置中(SERVICE_NAME = orcl)必须写orcl;如果想要自定义,则在虚拟机内的listener配置中就要加入自定义服务名,记得要重启监听服务:lsnrctl reload 最后形成的配置文件如下 虚拟机listener:

   DEST_ID ERROR

x86_64/Linux 2.4.xx            11.2.0.4.0     Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production

SID_LIST_LISTENER = (SID_LIST = (SID_DESC = (GLOBAL_DBNAME = test) 自定义服务名 (ORACLE_HOME = E:oracleproduct10.2.0db_1) db_home (SID_NAME = test) ) (SID_DESC = (PROGRAM = extproc) (SID_NAME = PLSExtProc) (ORACLE_HOME = E:oracleproduct10.2.0db_1) ##这个服务保证你可以使用外部过程,如C程序 ) )

--//启动drcp.
SYS@book> exec dbms_connection_pool.start_pool()
PL/SQL procedure successfully completed.

本机tns:


$ lsnrctl service
LSNRCTL for Linux: Version 11.2.0.4.0 - Production on 25-AUG-2017 16:40:36
Copyright (c) 1991, 2013, Oracle.  All rights reserved.
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=127.0.0.1)(PORT=1521)))
Services Summary...
Service "book" has 2 instance(s).
  Instance "book", status UNKNOWN, has 1 handler(s) for this service...
    Handler(s):
      "DEDICATED" established:23 refused:0
         LOCAL SERVER
  Instance "book", status READY, has 3 handler(s) for this service...
    Handler(s):
      "DEDICATED" established:31 refused:0 state:ready
         LOCAL SERVER
      "D000" established:0 refused:0 current:0 max:1022 state:ready
         DISPATCHER <machine: xxxxx, pid: 22244>
         (ADDRESS=(PROTOCOL=tcp)(HOST=xxxxx)(PORT=33994))
      "N000" established:0 refused:0 current:0 max:40000 state:ready
         CMON <machine: xxxxx, pid: 34251>
         (ADDRESS=(PROTOCOL=tcp)(HOST=xxxxx)(PORT=34401))
          ~~~~~~~~~~~~~
Service "book123" has 1 instance(s).
  Instance "book", status UNKNOWN, has 1 handler(s) for this service...
    Handler(s):
      "DEDICATED" established:0 refused:0
         LOCAL SERVER
Service "bookXDB" has 1 instance(s).
  Instance "book", status READY, has 1 handler(s) for this service...
    Handler(s):
      "D000" established:0 refused:0 current:0 max:1022 state:ready
         DISPATCHER <machine: xxxxx, pid: 22244>
         (ADDRESS=(PROTOCOL=tcp)(HOST=xxxxx)(PORT=33994))
Service "orcl" has 1 instance(s).
  Instance "orcl", status READY, has 1 handler(s) for this service...
    Handler(s):
      "DEDICATED" established:0 refused:0 state:ready
         LOCAL SERVER
Service "orclXDB" has 1 instance(s).
  Instance "orcl", status READY, has 1 handler(s) for this service...
    Handler(s):
      "D000" established:0 refused:0 current:0 max:1022 state:ready
         DISPATCHER <machine: xxxxx, pid: 53522>
         (ADDRESS=(PROTOCOL=tcp)(HOST=xxxxx)(PORT=64784))
The command completed successfully

ORCL_XNJ = (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = 182.12.15.232)(PORT = 1521)) ) (CONNECT_DATA = (SERVICE_NAME = test) 与自定义服务名对应 ) )

         1

--//注意看下划线的端口号34401.
# lsof -i -P -n | grep 34401
oracle    34251  oracle   11u  IPv6 32813593      0t0  TCP *:34401 (LISTEN)

再次连接,问题解决!记住,一定从最简单的原因找起,不要一上去就看到网上的什么改process数、注册表之类的,要想想本机能连为什么通过网络就不行,还是定位问题。VS2008连接oracle 出现错误 ora-12154 tns 无法解析指定的连接标示符

         2 ORA-12514: TNS:listener does not currently know of service

# ps -ef | grep  3425[1]
oracle   34251     1  0 16:40 ?        00:00:00 ora_n000_book
--//对应进程是34251.进程名为ora_n000_book.

在保证你的代码没有错误的前提下:检查OracleOraDb10g_home1TNSListener 服务有没有启动,即监听服务检查tnsnames.ora是否添加连接串,其路径一般是:%Oracle_home%product10.2.0db_1NETWORKADMINtnsnames.ora

           requested in connect descriptor

$ lsnrctl stop
LSNRCTL for Linux: Version 11.2.0.4.0 - Production on 25-AUG-2017 16:45:56
Copyright (c) 1991, 2013, Oracle.  All rights reserved.
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=127.0.0.1)(PORT=1521)))
The command completed successfully

其它网友的补充:

 

$ lsnrctl status
LSNRCTL for Linux: Version 11.2.0.4.0 - Production on 25-AUG-2017 16:46:54
Copyright (c) 1991, 2013, Oracle.  All rights reserved.
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=127.0.0.1)(PORT=1521)))
TNS-12541: TNS:no listener
 TNS-12560: TNS:protocol adapter error
  TNS-00511: No listener
   Linux Error: 111: Connection refused
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=192.168.100.78)(PORT=1521)))
TNS-12541: TNS:no listener
 TNS-12560: TNS:protocol adapter error
  TNS-00511: No listener
   Linux Error: 111: Connection refused
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC1521)))
TNS-12541: TNS:no listener
 TNS-12560: TNS:protocol adapter error
  TNS-00511: No listener
   Linux Error: 2: No such file or directory
--//可以发现监听已经关闭.

今天装oracle安装都完成了,但出现了“ORA-12154 TNS”的错误,最开始以为是oracle安装处了问题,但后来发现并不是,因为用SQL Plus可以登录,只是用plsql登录时才出现这错,用360完全卸载了qlsql后,从新装了次,还是那错,郁闷。。。后来又把数据库删掉重新建立,还是不行,整了半天,最后自己都整晕了,算了,重头再来吧。。。百度下很多安装图解,按着来就成

         3

3.测试连接:
--//采用ezconnect模式,这样快捷一些.
$ rlsql scott/book@192.168.100.78:34401/book:POOLED
SQL*Plus: Release 11.2.0.4.0 Production on Fri Aug 25 16:50:41 2017
Copyright (c) 1982, 2013, Oracle.  All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

oracle服务端安装图解//客户端安装图解//

         4

SCOTT@192.168.100.78:34401/book:POOLED> select sysdate from dual ;

oracle完全卸载//或者直接在注册表中按F3搜索oracle,删除注册文件,不过貌似文件有点多,操作有点慢,建议用上边链接的方法

         5

SYSDATE

2017-08-25 16:50:52

SCOTT@192.168.100.78:34401/book:POOLED> select sid, serial#,server from v$session where sid = ( select sid from v$mystat where rownum=1);
       SID    SERIAL# SERVER


        31         57 POOLED

SCOTT@192.168.100.78:34401/book:POOLED> @ &r/spid
       SID    SERIAL# SPID       PID  P_SERIAL# C50



        31         57 34257       26         29 alter system kill session '31,57' immediate;

$ ps -ef | grep 3425[7]
oracle   34257     1  0 16:40 ?        00:00:00 ora_l002_book

--//同样对于共享模式也一样,前面知道端口号33994.

$ rlsql scott/book@192.168.100.78:33994/book:shared
SQL*Plus: Release 11.2.0.4.0 Production on Fri Aug 25 16:57:06 2017
Copyright (c) 1982, 2013, Oracle.  All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SCOTT@192.168.100.78:33994/book:shared> select sid, serial#,server from v$session where sid = ( select sid from v$mystat where rownum=1);

       SID    SERIAL# SERVER


       263          9 SHARED

总结:
--//再次用一句安全的术语讲"苍蝇不叮无缝的蛋"。^_^.
--//只要知道drcp的端口号(当然要启动这个服务)或者共享模式的端口号,不启动监听一样能连接数据库.
--//补充netstat -tnlp | grep ora的输出.
# netstat -tnlp | egrep 'Active|Proto|ora'
Active Internet connections (only servers)
Proto Recv-Q Send-Q Local Address               Foreign Address             State       PID/Program name
tcp        0      0 :::34401                    :::*                        LISTEN      34251/ora_n000_book
tcp        0      0 :::33994                    :::*                        LISTEN      22244/ora_d000_book

总结了下要注意的几点

         6

1.安装服务端是一般选“基本安装”就行2.使用PLSQL Developer时,“ORA-12154 TNS无法解析指定的连接标识符”的问题安装时报后边的错“warning some oracle net versions cannot connect from a path with parentheses”,这错原来提示了,不能有括号,修改了安装路径就没有问题了相应的上边的“ORA-12154 TNS无法解析指定的连接标识符”错也没啦,有点晕,没整明白,但确实好啦3.“ORA-12154 TNS无法解析指定的连接标识符”按照网上的方法有两种,但我试了,在我这都不好使,下边贴出来,没准儿啥时候可以用1.解决办法,修改PLSQL Developer的选项设置:客户端、服务器端Oracle软件装好之后,用PLSQL Devloper登录时,点击取消,进入PLSQL Developer主界面,菜单Tools-Preferences,在设置窗口中,选择Oracle-Connection,对应设置窗口中,找到Oracle Home,选择OraDb10g_home1,保存设置,重新登录PLSQL Developer即可

         7

2.修改D:oracleproduct10.2.0db_1NETWORKADMIN路径下的tnsnames.ora文件###### 一般的配置 ##################################

         8

ORALOCAL = (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = 127.0.0.1)(PORT = 1521)) ) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = oralocal) ) )

         9

以上答案供参考

        10

 

--查看主库监听

SQL> !lsnrctl status

 

LSNRCTL for Linux: Version 10.2.0.5.0 - Production on 08-OCT-2014 12:31:46

 

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

 

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=prd)(PORT=1521)))

STATUS of the LISTENER


Alias                     LISTENER

Version                   TNSLSNR for Linux: Version 10.2.0.5.0 - Production

Start Date                08-OCT-2014 10:34:51

Uptime                    0 days 1 hr. 56 min. 54 sec

Trace Level               off

Security                  ON: Local OS Authentication

SNMP                      OFF

Listener Parameter File   /u01/app/oracle/product/10.2.0/db_1/network/admin/listener.ora

Listener Log File         /u01/app/oracle/product/10.2.0/db_1/network/log/listener.log

Listening Endpoints Summary...

  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=prd)(PORT=1521)))

  (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC0)))

Services Summary...

Service "PLSExtProc" has 1 instance(s).

  Instance "PLSExtProc", status UNKNOWN, has 1 handler(s) for this service...

Service "prd" has 1 instance(s).

  Instance "prd", status READY, has 1 handler(s) for this service...

Service "prdXDB" has 1 instance(s).

  Instance "prd", status READY, has 1 handler(s) for this service...

Service "prd_XPT" has 1 instance(s).

  Instance "prd", status READY, has 1 handler(s) for this service...

The command completed successfully

 

--tnsnames.ora文件内容

# tnsnames.ora Network Configuration File: /u01/app/oracle/product/10.2.0/db_1/network/admin/tnsnames.ora

# Generated by Oracle configuration tools.

 

EXTPROC_CONNECTION_DATA =

  (DESCRIPTION =

    (ADDRESS_LIST =

      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC0))

    )

    (CONNECT_DATA =

      (SID = PLSExtProc)

      (PRESENTATION = RO)

    )

  )

 

PRD =

  (DESCRIPTION =

    (ADDRESS = (PROTOCOL = TCP)(HOST = prd)(PORT = 1521))

    (CONNECT_DATA =

      (SERVER = DEDICATED)

      (SERVICE_NAME = prd)

    )

  )

 

STD =

  (DESCRIPTION =

    (ADDRESS = (PROTOCOL = TCP)(HOST = std)(PORT = 1521))

    (CONNECT_DATA =

      (SERVER = DEDICATED)

      (SERVICE_NAME = prd)

    )

  )

 

--主库tnsping测试网络服务名是否正常

SQL> !tnsping prd

 

TNS Ping Utility for Linux: Version 10.2.0.5.0 - Production on 08-OCT-2014 12:32:35

 

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

 

Used parameter files:

 

 

Used TNSNAMES adapter to resolve the alias

Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = prd)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = prd)))

OK (10 msec)

 

SQL> !tnsping std

 

TNS Ping Utility for Linux: Version 10.2.0.5.0 - Production on 08-OCT-2014 12:32:43

 

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

 

Used parameter files:

 

 

Used TNSNAMES adapter to resolve the alias

Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = std)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = prd)))

OK (20 msec)

 

--查看备库监听

[oracle@std ~]$ lsnrctl status

 

LSNRCTL for Linux: Version 10.2.0.5.0 - Production on 08-OCT-2014 12:29:52

 

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

 

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=std)(PORT=1521)))

STATUS of the LISTENER


Alias                     LISTENER

本文由金沙国际平台登录发布于金沙国际平台登录,转载请注明出处:--//利用共享服务器模式.,tnsname.ora文件配置都正

您可能还会对下面的文章感兴趣: