[20190306]静态监听配置sid大小写问题.txt

[20190306]静态监听配置sid大小写问题.txt --//有网友按照链接http://blog.itpub.net/267265/viewspace-2558389/测试,通过配置静态监听,无法通过远程启动数据库. --//我看了配置,问题出在sid_name大小写问题.通过例子说明: 1.环境: SYS@book> @ ver1 PORT_STRING                    VERSION        BANNER ------------------------------ -------------- -------------------------------------------------------------------------------- x86_64/Linux 2.4.xx            11.2.0.4.0     Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production SYS@book> shutdown  immediate ; Database closed. Database dismounted. ORACLE instance shut down. 2.监听配置如下: $ grep -v "#" listener.ora SID_LIST_LISTENER =    (SID_LIST =     (SID_DESC =       (SDU=32767)       (GLOBAL_DBNAME = booK) ~~~~~~~~~~~~=>这个不区分大小写^_^.         (ARGV0=myapp0)       (ORACLE_HOME = /u01/app/oracle/product/11.2.0.4/dbhome_1)       (SID_NAME=BOOK) ~~~~~~~~~~~~~~~~~~~~~~         )     ) LISTENER =   (DESCRIPTION_LIST =     (DESCRIPTION =       (ADDRESS = (PROTOCOL = TCP)(ARGV0=LLLLLL)(HOST = 0.0.0.0)(PORT = 1521)(ARGV0=KKKKK))       (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))   )  ) SECURE_REGISTER_LISTENER = (TCP) ADR_BASE_LISTENER = /u01/app/oracle DIAG_ADR_ENABLED_LISTENER=OFF USE_NS_PROBES_FOR_DCD=true INBOUND_CONNECT_TIMEOUT_LISTENER=2 --//注意下划线的配置. $ lsnrctl stop;sleep 1;lsnrctl start ... $ lsnrctl status LSNRCTL for Linux: Version 11.2.0.4.0 - Production on 06-MAR-2019 15:08:09 Copyright (c) 1991, 2013, Oracle.  All rights reserved. Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(ARGV0=LLLLLL)(HOST=0.0.0.0)(PORT=1521)(ARGV0=KKKKK))) STATUS of the LISTENER ------------------------ Alias                     LISTENER Version                   TNSLSNR for Linux: Version 11.2.0.4.0 - Production Start Date                06-MAR-2019 15:08:07 Uptime                    0 days 0 hr. 0 min. 2 sec Trace Level               off Security                  ON: Local OS Authentication SNMP                      OFF Listener Parameter File   /u01/app/oracle/product/11.2.0.4/dbhome_1/network/admin/listener.ora Listener Log File         /u01/app/oracle/product/11.2.0.4/dbhome_1/network/log/listener.log Listening Endpoints Summary...   (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=0.0.0.0)(PORT=1521)))   (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521))) Services Summary... Service "booK" has 1 instance(s).   Instance "BOOK", status UNKNOWN, has 1 handler(s) for this service...   ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ The command completed successfully --//注意看下划线. 3.测试: d:\tools> sqlplus -l sys/oracle@192.168.100.78:1521/book as sysdba SQL*Plus: Release 12.2.0.1.0 Production on Wed Mar 6 15:12:43 2019 Copyright (c) 1982, 2016, Oracle.  All rights reserved. ERROR: ORA-01017: invalid username/password; logon denied SP2-0751: Unable to connect to Oracle.  Exiting SQL*Plus d:\tools> sqlplus -l sys/oracle@192.168.100.78:1521/BOOK SQL*Plus: Release 12.2.0.1.0 Production on Wed Mar 6 15:06:45 2019 Copyright (c) 1982, 2016, Oracle.  All rights reserved. ERROR: ORA-01034: ORACLE not available ORA-27101: shared memory realm does not exist Linux-x86_64 Error: 2: No such file or directory Process ID: 0 Session ID: 0 Serial number: 0 SP2-0751: Unable to connect to Oracle.  Exiting SQL*Plus --//linux下sid_name严格区分大小写.修改为监听配置SID_NAME=book小写后正常. d:\tools> sqlplus -l sys/oracle@192.168.100.78:1521/book as sysdba SQL*Plus: Release 12.2.0.1.0 Production on Wed Mar 6 15:14:10 2019 Copyright (c) 1982, 2016, Oracle.  All rights reserved. Connected to an idle instance. SYS@192.168.100.78:1521/book> startup ORACLE instance started. Total System Global Area  643084288 bytes Fixed Size                  2255872 bytes Variable Size             205521920 bytes Database Buffers          427819008 bytes Redo Buffers                7487488 bytes Database mounted. Database opened. --//启动成功!!oracle启动数据库,共享内存段建立的句柄由ORACLE_HOME_SID_NAME组成.对应linux,严格区分大小写. --//另外11.2.0.4环境变量ORACLE_HOME结尾是否有斜线不影响的handle组成,但是后面的部分还是SID_NAME. --//不知道windows是否有问题,感觉经常使用windows的人喜欢大写. $ sysresv IPC Resources for ORACLE_SID "book" : Shared Memory: ID              KEY 343080971       0x00000000 343113740       0x00000000 343146509       0xe8a8ec10 Semaphores: ID              KEY 301662208       0x6aa88594 Oracle Instance alive for sid "book" 4.许多人经常混淆数据库实例与数据库等概念,参数里面许多name,实际上我自己有时候也混淆.再做一个例子: --//关闭数据库重新测试.修改监听配置里面的静态监听配置SID_NAME=booK. $ lsnrctl stop;sleep 1;lsnrctl start ... $ lsnrctl status LSNRCTL for Linux: Version 11.2.0.4.0 - Production on 06-MAR-2019 15:28:03 Copyright (c) 1991, 2013, Oracle.  All rights reserved. Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(ARGV0=LLLLLL)(HOST=0.0.0.0)(PORT=1521)(ARGV0=KKKKK))) STATUS of the LISTENER ------------------------ Alias                     LISTENER Version                   TNSLSNR for Linux: Version 11.2.0.4.0 - Production Start Date                06-MAR-2019 15:27:38 Uptime                    0 days 0 hr. 0 min. 25 sec Trace Level               off Security                  ON: Local OS Authentication SNMP                      OFF Listener Parameter File   /u01/app/oracle/product/11.2.0.4/dbhome_1/network/admin/listener.ora Listener Log File         /u01/app/oracle/product/11.2.0.4/dbhome_1/network/log/listener.log Listening Endpoints Summary...   (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=0.0.0.0)(PORT=1521)))   (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521))) Services Summary... Service "booK" has 1 instance(s).   Instance "booK", status UNKNOWN, has 1 handler(s) for this service... The command completed successfully --//修改SID_NAME=booK. $ cd /u01/app/oracle/product/11.2.0.4/dbhome_1/dbs $ cp orapwbook orapwbooK d:\tools> sqlplus -l sys/oracle@192.168.100.78:1521/book as sysdba SQL*Plus: Release 12.2.0.1.0 Production on Wed Mar 6 15:29:32 2019 Copyright (c) 1982, 2016, Oracle.  All rights reserved. Connected to an idle instance. SYS@192.168.100.78:1521/book> --//能访问口令文件,就可以到Connected to an idle instance. SYS@192.168.100.78:1521/book> startup ORA-01078: failure in processing system parameters LRM-00109: could not open parameter file '/u01/app/oracle/product/11.2.0.4/dbhome_1/dbs/initbooK.ora' --//可以发现找不到参数文件. $ cp spfilebook.ora spfilebooK.ora SYS@192.168.100.78:1521/book> startup ORACLE instance started. Total System Global Area  643084288 bytes Fixed Size                  2255872 bytes Variable Size             205521920 bytes Database Buffers          427819008 bytes Redo Buffers                7487488 bytes Database mounted. Database opened. --//现在一样启动数据库. $ sysresv IPC Resources for ORACLE_SID "book" : Shared Memory ID              KEY No shared memory segments used Semaphores: ID              KEY No semaphore resources used Oracle Instance not alive for sid "book" --//ORACLE_SID=book,sysresv看不到共享内存段. $ export ORACLE_SID=booK $ sysresv IPC Resources for ORACLE_SID "booK" : Shared Memory: ID              KEY 343212043       0x00000000 343244812       0x00000000 343277581       0x280f14d4 Semaphores: ID              KEY 301826048       0x5aa88594 Oracle Instance alive for sid "booK" ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ --//注意下划线的内容. 5.收尾: SYS@192.168.100.78:1521/book> shutdown immediate; Database closed. Database dismounted. ORACLE instance shut down. --//修改监听参数配置,重启监听.略. $ lsnrctl stop;sleep 1;lsnrctl start $ cd /u01/app/oracle/product/11.2.0.4/dbhome_1/dbs $ rm orapwbooK spfilebooK.ora --//$ rm -rf /u01/app/oracle/diag/rdbms/book/booK