Oracle 靜態監聽註冊詳解

網上有很多關於oracle 監聽靜態註冊的文章，但大多都是簡單說說，並沒有詳細的例子，這裡，將結合linux as4 下的oracle 10gR2.0.1 舉一個具體的例子
1、在 $ORACLE_HOME/network/admin/listener.ora 檔案中加入一個靜態註冊的節點

[oracle@prudent oracle]$ cd $ORACLE_HOME/network/admin
[oracle@prudent admin]$ vi listener.ora
# listener.ora Network Configuration File: /mydatafile2/app/oracle/oracle/product/11.2.0/db_1/network/admin/listener.ora
# Generated by Oracle configuration tools.
SID_LIST_LISTENER =
(SID_LIST =
    (SID_DESC =
      (SID_NAME = PLSExtProc)
      (ORACLE_HOME = /mydatafile2/app/oracle/oracle/product/11.2.0/db_1)
      (PROGRAM = extproc)
    )

    (SID_DESC =
      (SID_NAME = ORCL)
      (ORACLE_HOME = /mydatafile2/app/oracle/oracle/product/11.2.0/db_1)
      (GLOBAL_DBNAME=WOO.COM)
    )
)

LISTENER =
(DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1))
      (ADDRESS = (PROTOCOL = TCP)(HOST = prudent)(PORT = 1521))
    )
)

注意這裡的GLOBAL_DBNAME=WOO.COM
SID_NAME=ORCL
這個SID_NAME 應與你對外提供服務的 $ORACLE_SID 一致

[oracle@prudent admin]$ echo $ORACLE_SID
ORCL

2、配置對應的tnsnames.ora 中的節點

[oracle@prudent admin]$ vi tnsnames.ora

# tnsnames.ora Network Configuration File: /mydatafile2/app/oracle/oracle/product/11.2.0/db_1/network/admin/tnsnames.ora
# Generated by Oracle configuration tools.
ORCL=
(DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = prudent)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = ORCL)
    )
)

WOOORCL=
(DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = prudent)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = WOO.COM)
    )
)

tnsname WOOORCL 中的 SERVICE_NAME=WOO.COM

這裡的服務名為 WOO.COM 而不是通常的 ORCL，因為在 listener.ora 中已經註冊了 WOO.COM,lsnrctl 啟動時會監聽 WOO.COM ，並對應到 SID_NAME=ORCL 上。

3、啟動監聽和服務

[oracle@prudent oracle]$ cat dbstart
lsnrctl start
sqlplus /nolog < connect /as sysdba
startup
EOF
[oracle@prudent oracle]$ ./dbstart

LSNRCTL for Linux: Version 11.2.0.1.0 - Production on 13-FEB-2011 20:11:15

Copyright (c) 1991, 2005, Oracle. All rights reserved.

Starting /mydatafile2/app/oracle/oracle/product/11.2.0/db_1/bin/tnslsnr: please wait...

TNSLSNR for Linux: Version 11.2.0.1.0 - Production
System parameter file is /mydatafile2/app/oracle/oracle/product/11.2.0/db_1/network/admin/listener.ora
Log messages written to /mydatafile2/app/oracle/oracle/product/11.2.0/db_1/network/log/listener.log
Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1)))
Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=prudent)(PORT=1521)))

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC1)))
STATUS of the LISTENER
------------------------
Alias                     LISTENER
Version                   TNSLSNR for Linux: Version 11.2.0.1.0 - Production
Start Date                13-FEB-2011 20:11:15
Uptime                    0 days 0 hr. 0 min. 0 sec
Trace Level               off
Security                  ON: Local OS Authentication
SNMP                      OFF
Listener Parameter File   /mydatafile2/app/oracle/oracle/product/11.2.0/db_1/network/admin/listener.ora
Listener Log File         /mydatafile2/app/oracle/oracle/product/11.2.0/db_1/network/log/listener.log
Listening Endpoints Summary...
(DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1)))
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=prudent)(PORT=1521)))
Services Summary...
Service "WOO.COM" has 1 instance(s).
Instance "ORCL", status UNKNOWN, has 1 handler(s) for this service...
Service "ORCL" has 1 instance(s).
Instance "ORCL", status UNKNOWN, has 1 handler(s) for this service...
Service "PLSExtProc" has 1 instance(s).
Instance "PLSExtProc", status UNKNOWN, has 1 handler(s) for this service...
The command completed successfully

SQL*Plus: Release 11.2.0.1.0 - Production on Sun Feb 13 20:11:16 2011

Copyright (c) 1982, 2005, Oracle. All rights reserved.

SQL> Connected to an idle instance.
SQL> ORA-32004: obsolete and/or deprecated parameter(s) specified
ORACLE instance started.

Total System Global Area 461373440 bytes
Fixed Size                  1220000 bytes
Variable Size              75498080 bytes
Database Buffers          381681664 bytes
Redo Buffers                2973696 bytes
Database mounted.
Database opened.
SQL> Disconnected from Oracle Database 10g Enterprise Edition Release 11.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options

可以看到
Service "WOO.COM" has 1 instance(s).
Instance "ORCL", status UNKNOWN, has 1 handler(s) for this service...
正在被監聽。

4、驗證該服務可以到達

[oracle@prudent oracle]$ tnsping WOOORCL

TNS Ping Utility for Linux: Version 11.2.0.1.0 - Production on 13-FEB-2011 20:14:59

Copyright (c) 1997, 2005, Oracle. All rights reserved.

Used parameter files:
/mydatafile2/app/oracle/oracle/product/11.2.0/db_1/network/admin/sqlnet.ora

Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = prudent)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = WOO.COM)))
OK (10 msec)

5、利用靜態註冊的服務登入oracle

[oracle@prudent oracle]$ sqlplus system/
SQL*Plus: Release 11.2.0.1.0 - Production on Sun Feb 13 20:17:27 2011

Copyright (c) 1982, 2005, Oracle. All rights reserved.

Connected to:
Oracle Database 10g Enterprise Edition Release 11.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options

SQL> select count(*) from date_log;

COUNT(*)
----------

SQL>

至此：已驗證該靜態註冊可以成功的被解析，監聽，連線。

Oracle 靜態監聽註冊詳解

相關文章