oralce关于密码研究与疑问.doc

关于密码文件研究与疑问?平台： server端；SUSE9+oracle db 。 client端： windows xp + oracle client 。 下面的tns_connect_string是指windows端的tnsnames.ora文件中配置的连接串。过程1. 确保suse上没有密码文件。在suse端启动实例和listener后。在windows端用sqlplus sys/oracletns_connect_string as sysdba 连接。 结果登录失败。报“insufficient privileges”。说明能够连接，只是没有密码文件而无法认证。 2. 在suse上的oracle 创建密码文件（含sys用户）。确认remote_login_passwordfile=EXCLUSIVE后重新启动实例（listener也启动）。这是再在windows上用sqlplus sys/oracletns_connect_string as sysdba 连接。 结果登录成功吗，并且可以用sysdba的权限进行各种操作，包括shutdown。 说明在instance和listener启动时，且密码文件存在时，可以远程以sysdba身份连接到数据库（并由密码文件认证而不是数据库内部）。 3. 在suse端停止instance而不停listener。再在windows上用sqlplus sys/oracletns_connect_string as sysdba 连接。结果无法连接，报“ORA-12514: TNS: 监听程序当前无法识别连接描述符中请求的服务”。 问题出来了： 难道用密码文件方式认证需要先启动instance？ 4. 查阅资料，根据oracle副总写的那个oracle编程艺术上讲的，应该server端未启动instance时，密码文件也可以认证。 我个人从理论分析也觉得oracle副总说的是对的，因为密码文件的存在的价值就是为了解决在数据库没有open前，远程操作数据的用户登录认证问题。假如需要在server端先启动instance，密码文件才能认证，那密码文件存在的价值就大大的打折了。但是我从我前面的基本操作看，为什么不行呢？请大家指点？这个问题也很好重现，按照上面的做一下就可以了。我之前也有遇到这样的问题，我的问题是出在监听的名称上，当数据库shutdown以后查看监听的status 。LSNRCTL statusConnecting to (ADDRESS=(PROTOCOL=tcp)(HOST=)(PORT=1521)STATUS of the LISTENER-Alias LISTENERVersion TNSLSNR for Linux: Version .0 - ProductionStart Date 13-MAY-2008 00:43:24Uptime 0 days 0 hr. 1 min. 20 secTrace Level offSecurity ON: Local OS AuthenticationSNMP OFFListener Parameter File /u01/app/oracle/product/10.2.0/db_1/network/admin/listener.oraListener Log File /u01/app/oracle/product/10.2.0/db_1/network/log/listener.logListening Endpoints Summary.(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=)(PORT=1521)The listener supports no servicesThe command completed successfully这里可以看到，监听不支持任何的服务The listener supports no services 。所以这个时候外界是连接不上server的 。如果启动了数据库，情况就有所不同LSNRCTL statusConnecting to (ADDRESS=(PROTOCOL=tcp)(HOST=)(PORT=1521)STATUS of the LISTENER-Alias LISTENERVersion TNSLSNR for Linux: Version .0 - ProductionStart Date 13-MAY-2008 00:43:24Uptime 0 days 0 hr. 3 min. 29 secTrace Level offSecurity ON: Local OS AuthenticationSNMP OFFListener Parameter File /u01/app/oracle/product/10.2.0/db_1/network/admin/listener.oraListener Log File /u01/app/oracle/product/10.2.0/db_1/network/log/listener.logListening Endpoints Summary.(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=)(PORT=1521)Services Summary.Service orcl has 1 instance(s).Instance orcl, status READY, has 1 handler(s) for this service.Service orcl_XPT has 1 instance(s).Instance orcl, status READY, has 1 handler(s) for this service.The command completed successfully 我当时也是花了很长的时间才找到原因。我的监听的名称LISTENER01 ，而我们用lsnrctl start启动监听时它默认去找名为LISTENER的监听。LSNRCTL statusConnecting to (ADDRESS=(PROTOCOL=tcp)(HOST=)(PORT=1521)STATUS of the LISTENER-Alias LISTENER # 从这里可以看出 。所以解决的办法是更改监听名称，或者lsnrctl start时指定监听的名称。lsnrctl start LISTENER01在suse端停止instance而不停listener。再在windows上用sqlplus sys/oracletns_connect_string as sysdba 连接。结果无法连接，报“ORA-12514: TNS: 监听程序当前无法识别连接描述符中请求的服务”。 问题出来了： 难道用密码文件方式认证需要先启动instance？-你的数据库是动态注册的，数据库停掉了，自然在监听器上的注册信息就没了，跟你的口令文件无关，只要配置静态注册就可以感谢并进一步讨论同意 “ora-600”的说法，我改成了静态注册就OK了！ 设置如下：（同时发现，静态注册后，只要实例启动，它同时也会动态向监听器注册）. (SID_DESC = (GLOBAL_NAME = 数据库名.域名) (ORACLE_HOME = /opt/oracle/product/10.2.0/db_1) (SID_NAME = 实例名) ).但进一步分析： 我启动listener后，发现其service的名字为“实例名”。（这个service名字即是tns_conn_string中需要指定的service_name参数）。 但我看文档和oracle副总说：listener的service名字应该是由listener.ora中的GLOBAL_NAME决定（即两者相同）。 但为什么我这里的listener的service名字与SID_NAME 相同？ 请大家指点？listener启动后对外提供的服务名到底由什么决定？ 有显示的参数设置吗？ （并且发现似乎GLOBAL_NAME 配置任意字母都可以） 附：oracle副总关于此问题的解释，它是在9iR2上做的。/pls/ask . N_ID:35644258225012-sqlplus sys/passworddbname as sysdbashow parameter service_nametnsname.ora 里面的service_name对应上面查询出的service_name。tns-12514这个错很经典，之前遇到过，也许此文对你有意义/2008/05/22/ora-12514/ORA-12514By huojia on May 22, 2008 in 数据库技术and tagged oracle今天碰到一个错，很典型。1. 错误 ORA-12514C:Documents and SettingsAdministratorsqlplus icmadmin/fvt6eiprmdbSQL*Plus: Release .0 - Production on Fri May 23 01:53:46 2008Copyright (c) 1982, 2006, Oracle. All Rights Reserved.ERROR:ORA-12514: TNS:listener does not currently know of service requested in connectdescriptorEnter user-name:tnsname.oraRMDB =(DESCRIPTION =(ADDRESS = (PROTOCOL = TCP)(HOST = )(PORT = 1521)(CONNECT_DATA =(SERVER = DEDICATED)(SERVICE_NAME = )2.TNSPing检查C:Documents and SettingsAdministratortnsping rmdbTNS Ping Utility for 32-bit Windows: Version .0 - Production on 23-MAY-2008 01:54:45Copyright (c) 1997, 2006, Oracle. All rights reserved.Used parameter files:C:oracleproduct10.2.0db_1networkadminsqlnet.oraUsed TNSNAMES adapter to resolve the aliasAttempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = )(PORT = 1521) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = )OK (10 msec)3.Oracle官方解释： ORA-12514: TNS:listener does not currently know of service requested in connect descriptorCause: The listener received a request to establish a connection to a database or other service. The connect descriptor received by the listener specified a service name for a service (usually a database service) that either has not yet dynamically registered with the listener or has not been statically configured for the listener. This may be a temporary condition such as after the listener has started, but before the database instance has registered with the listener.Action:- Wait a moment and try to connect a second time.- Check which services are currently known by the listener by executing: lsnrctl services - Check that the SERVICE_NAME parameter in the connect descriptor of the net service name used specifies a service known by the listener.- If an easy connect naming connect identifier was used, check that the service name specified is a service known by the listener.- Check for an event in the listener.log file.4.检查ServiceNameSQL show parameter service_name;NAME TYPE VALUE service_names string rmdb果然，rmdb的servicename不是期望的5.更改Service NameSQL shutdown immediatelySP2-0717: illegal SHUTDOWN optionSQL shutdownDatabase closed.Database dismounted.ORACLE instance shut down.SQL startupORACLE instance started.Total System Global Area 364904448 bytesFixed Size 1290640 bytesVariable Size 306187888 bytesDatabase Buffers 54525952 bytesRedo Buffers 2899968 bytesDatabase mounted.Database opened.SQL show parameter service_name;NAME TYPE VALUE service_names string 6.连接成功！SQL conn icmadmin/fvt6eiprmdbConnected.SQL-EOF-You Asked In the listener.ora file, we can define the value of GLOBAL_DBNAME in SID_DESC section. for example:(SID_DESC = (GLOBAL_DBNAME = orachh) (ORACLE_HOME = D:oracleora92) (SID_NAME = orachh)I have found in many docs which said the value of GLOBAL_DBNAME must equal to the value of service_name.During my experiments i conclude that we can assign a random value to GLOBAL_DBNAME such as abcde and in this situation the listener can still start successfully,it seems the global_name was only an identifier and has not any important function:$lsnrctl statusService abcde has 1 instance(s). Instance orachh,status UNKNOWN, has 1 handler(s) for this service.So i want to know Whats the use of Global_dbname and in which circumstance should we use it.Thanks in advance. and we said.the global dbname in there will be the service name:ora9ir2localhost admin$ cat listener.ora# LISTENER.ORA Network Configuration File: /home/ora9ir2/network/admin/listener.ora# Generated by Oracle configuration tools. LISTENER = (DESCRIPTION_LIST = (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC) ) (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = localhost.localdomain)(PORT = 1521) ) ) ) SID_LIST_LISTENER = (SID_LIST = (SID_DESC = (SID_NAME = PLSExtProc) (ORACLE_HOME = /home/ora9ir2) (PROGRAM = extproc) ) (SID_DESC = (GLOBAL_DBNAME = WRONG) (ORACLE_HOME = /home/ora9ir2) (SID_NAME = ora9ir2) ) )when I ask that listener what services do you provide:ora9ir2localhost admin$ lsnrctl services LSNRCTL for Linux: Version .0 - Production on 27-FEB-2005 09:08:05 Copyright (c) 1991, 2002, Oracle Corporation. All rights reserved. Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC)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 WRONG has 1 instance(s). Instance ora9ir2, status UNKNOWN, has 1 handler(s) for this service. Handler(s): DEDICATED established:0 refused:0 LOCAL SERVERThe command completed successfullyora9ir2localhost admin$ora9ir2localhost admin$ sh -vx t.shsqlplus scott/tiger(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=localhost.localdomain)(PORT=1521)(CONNECT_DATA=(SERVICE_NAME=ora9ir2)+ sqlplus scott/tiger(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=localhost.localdomain)(PORT=1521)(CONNECT_DATA=(SERVICE_NAME=ora9ir2) SQL*Plus: Release .0 - Production on Sun Feb 27 09:10:04 2005 Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved. ERROR:ORA-12514: TNS:listener could not resolve SERVICE_NAME given in connectdescriptor Enter user-name:ERROR:ORA-01017: invalid username/password; logon denied Enter user-name:ERROR:ORA-01017: invalid username/password; logon denied SP2-0157: unable to CONNECT to ORACLE after 3 attempts, exiting SQL*Plussqlplus scott/tiger(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=localhost.localdomain)(PORT=1521)(CONNECT_DATA=(SERVICE_NAME=WRONG)+ sqlplus scott/tiger(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=localhost.localdomain)(PORT=1521)(CONNECT_DATA=(SERVICE_NAME=WRONG) SQL*Plus: Release .0 - Production on Sun Feb 27 09:10:07 2005 Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved. Connected to:Oracle9i Enterprise Edition Release .0 - ProductionWith the Partitioning, OLAP and Oracle Data Mining optionsJServer Release .0 - Production SQLso, the global dbname is the service name - it is just a way to keep it all straight, in a multi-database environment, you might think about using the global dbnames (and using the right ones, not the wrong ones) - as service names so you know what the service names are implicitly (global database names) and have a reasonable expectation of what database you are connecting to. Hi Tom,I didnt get exactly your explanation. If we define GLOBAL_DBNAME to contain correct DB name, does it mean that we can put anything in ORACLE_SID and connection will be successful? What about SERVICE_NAME in that case?Sorry for the questions that can seem stupid, but for me, these area has always been one of the most obscure sections in Oracle configuration. Followup February 27, 2005 - 10am US/Eastern: (SID_DESC = (GLOBAL_DBNAME = WRONG) (SERVICE_NAME = WRONGER) (ORACLE_HOME = /home/ora9ir2) (SID_NAME = INVALID) )ora9ir2localhost admin$ lsnrctl services LSNRCTL for Linux: Version .0 - Production on 27-FEB-2005 09:47:56 Copyright (c) 1991, 2002, Oracle Corporation. All rights reserved. Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC)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 WRONG has 1 instance(s). Instance INVALID, status UNKNOWN, has 1 handler(s) for this service. Handler(s): DEDICATED established:0 refused:0 LOCAL SERVERThe command completed successfullyora9ir2localhost admin$bascially, the global dbname is the service name, the oracle sid is the instance (a single service can point to many instances - RAC for example, you might have 4 nodes - 4 instances - the service would/could point to any of the 4 and load balance)Given I put in an invalid SID to get to the instance, itll never work:sqlplus scott/tiger(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=localhost.localdomain)(PORT=1521)(CONNECT_DATA=(SERVICE_NAME=WRONG) SQL*Plus: Release .0 - Production on Sun Feb 27 09:49:28 2005 Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved. ERROR:ORA-01034: ORACLE not availableORA-27101: shared memory realm does not existLinux Error: 2: No such file or directory Enter user-name:the service_name isnt really used in the listener.ora. You use global_dbname for static registration, you use service_name in the database init.ora to use dynamic service registration (no listener.ora setup, but no remote AS SYSDBA connections then either, you need a static en