[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

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/267265/viewspace-2637706/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/267265/viewspace-2637706/