oracle SQL Developer 连接问题
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/20762447/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
SQL Developer connection issue
提问by user2201747
Can anyone to help me resolve the issue with Oracle connection from SQL Oracle Developer. I've installed jdk 1.6.0_45, Oracle 11g, and can correct connect to The Oracle db using sqlpluswith sqldba credentials, but when I am trying to connect to this db using sql developer I get an error "Listener doe not currently know of SID given in connect descriptor". I try to change "SERVICE_NAME" to "SERVICE_NAME as SYSDBA" and some anather stuff but it does not help. How to make sql developerconnect to theserver?? Here is tnsname.ora file, img with connection setting and init.ora file and cmdprompt for connectiong from sqlplus
任何人都可以帮助我解决来自SQL Oracle Developer 的Oracle 连接问题。我已经安装了jdk 1.6.0_45、Oracle 11g，并且可以使用带有 sqldba 凭据的sqlplus正确连接到 Oracle db ，但是当我尝试使用 sql developer 连接到这个 db 时，我收到一个错误“侦听器目前不知道连接描述符中给出的 SID”。我尝试将“ SERVICE_NAME”更改为“ SERVICE_NAME as SYSDBA”以及一些其他内容，但无济于事。如何让sql developer连接到服务器？？这里是 tnsname.ora 文件，带有连接设置的 img 和 init.ora 文件和来自sqlplus 的连接的cmd提示
Mary Christmas to everyone ;-)
祝大家圣诞快乐 ;-)
# tnsnames.ora Network Configuration File: C:\app\Andriy\product.2.0\dbhome_1\network\admin\tnsnames.ora
# Generated by Oracle configuration tools.
ORACLR_CONNECTION_DATA =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
)
(CONNECT_DATA =
(SID = CLRExtProc)
(PRESENTATION = RO)
)
)
ORCL =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME as SYSDBA = orcl.adobe.com)
)
)
#
# $Header: rdbms/admin/init.ora /main/23 2009/05/15 13:35:38 ysarig Exp $
#
# Copyright (c) 1991, 1997, 1998 by Oracle Corporation
# NAME
# init.ora
# FUNCTION
# NOTES
# MODIFIED
# ysarig 05/14/09 - Updating compatible to 11.2
# ysarig 08/13/07 - Fixing the sample for 11g
# atsukerm 08/06/98 - fix for 8.1.
# hpiao 06/05/97 - fix for 803
# glavash 05/12/97 - add oracle_trace_enable comment
# hpiao 04/22/97 - remove ifile=, events=, etc.
# alingelb 09/19/94 - remove vms-specific stuff
# dpawson 07/07/93 - add more comments regarded archive start
# maporter 10/29/92 - Add vms_sga_use_gblpagfile=TRUE
# jloaiza 03/07/92 - change ALPHA to BETA
# danderso 02/26/92 - change db_block_cache_protect to _db_block_cache_p
# ghallmar 02/03/92 - db_directory -> db_domain
# maporter 01/12/92 - merge changes from branch 1.8.308.1
# maporter 12/21/91 - bug 76493: Add control_files parameter
# wbridge 12/03/91 - use of %c in archive format is discouraged
# ghallmar 12/02/91 - add global_names=true, db_directory=us.acme.com
# thayes 11/27/91 - Change default for cache_clone
# jloaiza 08/13/91 - merge changes from branch 1.7.100.1
# jloaiza 07/31/91 - add debug stuff
# rlim 04/29/91 - removal of char_is_varchar2
# Bridge 03/12/91 - log_allocation no longer exists
# Wijaya 02/05/91 - remove obsolete parameters
#
##############################################################################
# Example INIT.ORA file
#
# This file is provided by Oracle Corporation to help you start by providing
# a starting point to customize your RDBMS installation for your site.
#
# NOTE: The values that are used in this file are only intended to be used
# as a starting point. You may want to adjust/tune those values to your
# specific hardware and needs. You may also consider using Database
# Configuration Assistant tool (DBCA) to create INIT file and to size your
# initial set of tablespaces based on the user input.
###############################################################################
# Change '<ORACLE_BASE>' to point to the oracle base (the one you specify at
# install time)
db_name='ORCL'
memory_target=1G
processes = 150
audit_file_dest='<ORACLE_BASE>/admin/orcl/adump'
audit_trail ='db'
db_block_size=8192
db_domain=''
db_recovery_file_dest='<ORACLE_BASE>/flash_recovery_area'
db_recovery_file_dest_size=2G
diagnostic_dest='<ORACLE_BASE>'
dispatchers='(PROTOCOL=TCP) (SERVICE=ORCLXDB)'
open_cursors=300
remote_login_passwordfile='EXCLUSIVE'
undo_tablespace='UNDOTBS1'
# You may want to ensure that control files are created on separate physical
# devices
control_files = (ora_control1, ora_control2)
compatible ='11.2.0'
C:\>sqlplus
SQL*Plus: Release 11.2.0.1.0 Production on ┬?. ├Ё? 24 16:26:28 2013
Copyright (c) 1982, 2010, Oracle. All rights reserved.
Enter user-name: sys as sysdba
Enter password:
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL>
C:\>lsnrctl status
LSNRCTL for 32-bit Windows: Version 11.2.0.1.0 - Production on 24-├╨╙-2013 17:00:25
Copyright (c) 1991, 2010, Oracle. All rights reserved.
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC1521)))
STATUS of the LISTENER
------------------------
Alias LISTENER
Version TNSLSNR for 32-bit Windows: Version 11.2.0.1.0 - Production
Start Date 24-├╨╙-2013 09:24:44
Uptime 0 days 7 hr. 35 min. 43 sec
Trace Level off
Security ON: Local OS Authentication
SNMP OFF
Listener Parameter File C:\app\Andriy\product.2.0\dbhome_1\network\admin\listener.ora
Listener Log File c:\app\andriy\diag\tnslsnr\andriypc\listener\alert\log.xml
Listening Endpoints Summary...
(DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(PIPENAME=\.\pipe\EXTPROC1521ipc)))
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=127.0.0.1)(PORT=1521)))
Services Summary...
Service "CLRExtProc" has 1 instance(s).
Instance "CLRExtProc", status UNKNOWN, has 1 handler(s) for this service...
The command completed successfully
回答by Alex Poole
Your listener is configured to only listen on localhost(127.0.0.1). By default the database will attempt to register against the server's external host name (the default when local_listeneris blank), so registration seems to be failing.
您的侦听器配置为仅侦听localhost( 127.0.0.1)。默认情况下，数据库将尝试针对服务器的外部主机名进行注册（默认local_listener为空白时），因此注册似乎失败了。
The listener.oracan be modified to listen on the external address instead:
该listener.ora可以进行修改，以外部地址，而不是听：
LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
(ADDRESS = (PROTOCOL = TCP)(HOST = ANDRIYPC)(PORT = 1521))
)
)
... or the IP address rather than the host name if that isn't resolvable, e.g. 192.168.1.134; but if you are using DHCP to get your IP address that will break when you're given a different IP. If you're using a static address then using that IP will be OK.
... 或 IP 地址而不是主机名，如果无法解析，例如192.168.1.134；但是如果您使用 DHCP 来获取您的 IP 地址，当您获得不同的 IP 时，该地址会中断。如果您使用的是静态地址，那么使用该 IP 就可以了。
Alternatively, if you'll only ever access this DB from this PC and don't need it to be visible on the network, you can leave the listener on localhostand tell the DB to register there:
或者，如果您只从这台 PC 访问这个数据库并且不需要它在网络上可见，您可以保持监听器打开localhost并告诉数据库在那里注册：
alter system set local_listener = '127.0.0.1:1521' scope=both;
alter system register;
Either way, lsnrctl servicesshould now show orcl.adobe.com. When connecting from SQL Developer you can choose the 'Service name' radio button instead of SID, and put orcl.adobe.comin there as well.
无论哪种方式，lsnrctl services现在都应该显示orcl.adobe.com. 当从 SQL Developer 连接时，您可以选择“服务名称”单选按钮而不是 SID，并将其放在orcl.adobe.com那里。
If you're connecting as SYSyou'll need to pick the SYSDBArole from the drop-down; but the first thing you should do really is create a new user for yourself and then only use that. Only use SYS(and SYSTEM, and other built-in accounts) for actual DBA tasks, not for creating your own tables etc.
如果您正在连接，则SYS需要SYSDBA从下拉列表中选择角色；但是您真正应该做的第一件事是为自己创建一个新用户，然后仅使用该用户。仅将SYS（和SYSTEM、以及其他内置帐户）用于实际的 DBA 任务，而不用于创建您自己的表等。
