[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

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

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

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

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

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

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

[20190306]靜態監聽配置sid大小寫問題.txt

相關文章