oracle共享伺服器配置彙總
oracle共享伺服器配置彙總
對於共享伺服器的配置進行了彙總，也發現一些隱藏的錯誤
方式一，多種監聽（專用連線有專有連線的監聽，共享服務有共享服務的監聽）
方式二，一種監聽，專有和共享的都設定在一個監聽上
以下，ｌｉｓｔｅ１是專有伺服器連線方式，
ｌｉｓｔｅ２是專門的共享伺服器連線方式
ｌｉｓｔｅ３是專有服務和共享服務混合的方式
方式二，一種監聽，專有和共享的都設定在一個監聽上
以下，ｌｉｓｔｅ１是專有伺服器連線方式，
ｌｉｓｔｅ２是專門的共享伺服器連線方式
ｌｉｓｔｅ３是專有服務和共享服務混合的方式
listener.ora 配置如下。
LISTE1 =
(ADDRESS_LIST=
(ADDRESS=(PROTOCOL=tcp)(HOST=oel1.oracle.com)(PORT=1521))
)
LISTE1 =
(ADDRESS_LIST=
(ADDRESS=(PROTOCOL=tcp)(HOST=oel1.oracle.com)(PORT=1521))
)
SID_LIST_LISTE1=
(SID_LIST=
(SID_DESC=
(GLOBAL_DBNAME=PROD.oracle.com)
(SID_NAME=PROD)
(ORACLE_HOME=/u01/app/oracle/product/10.2.0/db_1)
)
)
(SID_LIST=
(SID_DESC=
(GLOBAL_DBNAME=PROD.oracle.com)
(SID_NAME=PROD)
(ORACLE_HOME=/u01/app/oracle/product/10.2.0/db_1)
)
)
LISTE2 =
(ADDRESS_LIST=
(ADDRESS=(PROTOCOL=tcp)(HOST=oel1.oracle.com)(PORT=1529))
)
SID_LIST_LISTE2=
(SID_LIST=
(SID_DESC=
(GLOBAL_DBNAME=PROD.oracle.com)
(SID_NAME=PROD)
(ORACLE_HOME=/u01/app/oracle/product/10.2.0/db_1)
)
)
(ADDRESS_LIST=
(ADDRESS=(PROTOCOL=tcp)(HOST=oel1.oracle.com)(PORT=1529))
)
SID_LIST_LISTE2=
(SID_LIST=
(SID_DESC=
(GLOBAL_DBNAME=PROD.oracle.com)
(SID_NAME=PROD)
(ORACLE_HOME=/u01/app/oracle/product/10.2.0/db_1)
)
)
LISTE3 =
(ADDRESS_LIST=
(ADDRESS=(PROTOCOL=tcp)(HOST=oel1.oracle.com)(PORT=1520))
(ADDRESS=(PROTOCOL=tcp)(HOST=oel1.oracle.com)(PORT=1530))
)
(ADDRESS_LIST=
(ADDRESS=(PROTOCOL=tcp)(HOST=oel1.oracle.com)(PORT=1520))
(ADDRESS=(PROTOCOL=tcp)(HOST=oel1.oracle.com)(PORT=1530))
)
SID_LIST_LISTE3=
(SID_LIST=
(SID_DESC=
(GLOBAL_DBNAME=PROD.oracle.com)
(SID_NAME=PROD)
(ORACLE_HOME=/u01/app/oracle/product/10.2.0/db_1)
)
)
(SID_LIST=
(SID_DESC=
(GLOBAL_DBNAME=PROD.oracle.com)
(SID_NAME=PROD)
(ORACLE_HOME=/u01/app/oracle/product/10.2.0/db_1)
)
)
tnsnames.ora 檔案配置如下
－－配置了多個埠的專用服務連線
PROD =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = tcp)(HOST = oel1.oracle.com)(PORT = 1520))
(ADDRESS = (PROTOCOL = tcp)(HOST = oel1.oracle.com)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = PROD.oracle.com)
)
)
－－配置了共享服務連線
SPROD =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = tcp)(HOST = oel1.oracle.com)(PORT = 1529))
)
(CONNECT_DATA =
(SERVICE_NAME = PROD.oracle.com)
(SERVER=SHARED)
)
)
－－配置共享服務連線，驗證可以有多個共享伺服器並存
XPROD =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = tcp)(HOST = oel1.oracle.com)(PORT = 1530))
)
(CONNECT_DATA =
(SERVICE_NAME = PROD.oracle.com)
(SERVER=SHARED)
)
)
－－配置了多個埠的專用服務連線
PROD =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = tcp)(HOST = oel1.oracle.com)(PORT = 1520))
(ADDRESS = (PROTOCOL = tcp)(HOST = oel1.oracle.com)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = PROD.oracle.com)
)
)
－－配置了共享服務連線
SPROD =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = tcp)(HOST = oel1.oracle.com)(PORT = 1529))
)
(CONNECT_DATA =
(SERVICE_NAME = PROD.oracle.com)
(SERVER=SHARED)
)
)
－－配置共享服務連線，驗證可以有多個共享伺服器並存
XPROD =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = tcp)(HOST = oel1.oracle.com)(PORT = 1530))
)
(CONNECT_DATA =
(SERVICE_NAME = PROD.oracle.com)
(SERVER=SHARED)
)
)
－－配置ｄｉｓｐａｔｃｈｅｒ
SQL> show parameter dispatch
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
dispatchers string (protocol=tcp)(service=PROD.or
acle.com)(dispatchers=2)
max_dispatchers integer 2
－－根據需要來配置共享服務的引數。當然ｓｅｓｓｉｏｎ，ｐｒｏｃｅｓｓｅｓ也需要相應的設定，如果值太小，共享服務連線就會很慢。
------------------------------------ ----------- ------------------------------
dispatchers string (protocol=tcp)(service=PROD.or
acle.com)(dispatchers=2)
max_dispatchers integer 2
－－根據需要來配置共享服務的引數。當然ｓｅｓｓｉｏｎ，ｐｒｏｃｅｓｓｅｓ也需要相應的設定，如果值太小，共享服務連線就會很慢。
SQL> show parameter shared
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
hi_shared_memory_address integer 0
max_shared_servers integer 2
shared_memory_address integer 0
shared_pool_reserved_size big integer 4404019
shared_pool_size big integer 0
shared_server_sessions integer 50
shared_servers integer 1
------------------------------------ ----------- ------------------------------
hi_shared_memory_address integer 0
max_shared_servers integer 2
shared_memory_address integer 0
shared_pool_reserved_size big integer 4404019
shared_pool_size big integer 0
shared_server_sessions integer 50
shared_servers integer 1
－－配置本地監聽
！！！如果配置的共享服務時非預設監聽Listeners，預設埠１５２１，則需要在ｌｏｃａｌ ｌｉｓｔｅｎｅｒ中註冊。
因為我建了兩個監聽ｌｉｓｔｅ２，ｌｉｓｔｅ３，所以把它們的埠資訊都註冊進去
alter system set local_listener='(address_list=(address=(protocol=tcp)(host=oel1.oracle.com)(port=1529))(address=(protocol=tcp)(host=oel1.oracle.com)(port=1530)))';
！！！如果配置的共享服務時非預設監聽Listeners，預設埠１５２１，則需要在ｌｏｃａｌ ｌｉｓｔｅｎｅｒ中註冊。
因為我建了兩個監聽ｌｉｓｔｅ２，ｌｉｓｔｅ３，所以把它們的埠資訊都註冊進去
alter system set local_listener='(address_list=(address=(protocol=tcp)(host=oel1.oracle.com)(port=1529))(address=(protocol=tcp)(host=oel1.oracle.com)(port=1530)))';
SQL> show parameter local_lis
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
local_listener string (address_list=(address=(protoc
l=tcp)(host=oel1.oracle.com)(
port=1529))(address=(protocol=
tcp)(host=oel1.oracle.com)(por
t=1530)))
－啟動監聽
LSNRCTL> start liste1
------------------------------------ ----------- ------------------------------
local_listener string (address_list=(address=(protoc
l=tcp)(host=oel1.oracle.com)(
port=1529))(address=(protocol=
tcp)(host=oel1.oracle.com)(por
t=1530)))
－啟動監聽
LSNRCTL> start liste1
LSNRCTL> start liste2
LSNRCTL> start liste3
－－檢視共享服務是否配置正常。加粗的地方因為起了２個ｄｉｓｐａｔｃｈｅｒ，所以有２個Ｄ開頭的ｈａｎｄｌｅｒｓ．
LSNRCTL> service liste3
Connecting to (ADDRESS=(PROTOCOL=tcp)(HOST=oel1.oracle.com)(PORT=1520))
Services Summary...
Service "PROD.oracle.com" has 2 instance(s).
Instance "PROD", status UNKNOWN, has 1 handler(s) for this service...
Handler(s):
"DEDICATED" established:0 refused:0
LOCAL SERVER
Instance "PROD", status READY, has 3 handler(s) for this service...
Handler(s):
"D001" established:0 refused:0 current:0 max:972 state:ready
DISPATCHER
(ADDRESS=(PROTOCOL=tcp)(HOST=oel1.oracle.com)(PORT=46966))
"D000" established:0 refused:0 current:0 max:972 state:ready
DISPATCHER
(ADDRESS=(PROTOCOL=tcp)(HOST=oel1.oracle.com)(PORT=57952))
"DEDICATED" established:0 refused:0 state:ready
LOCAL SERVER
Service "PROD_XPT.oracle.com" has 1 instance(s).
Instance "PROD", status READY, has 1 handler(s) for this service...
Handler(s):
"DEDICATED" established:0 refused:0 state:ready
LOCAL SERVER
The command completed successfully
LSNRCTL> service liste3
Connecting to (ADDRESS=(PROTOCOL=tcp)(HOST=oel1.oracle.com)(PORT=1520))
Services Summary...
Service "PROD.oracle.com" has 2 instance(s).
Instance "PROD", status UNKNOWN, has 1 handler(s) for this service...
Handler(s):
"DEDICATED" established:0 refused:0
LOCAL SERVER
Instance "PROD", status READY, has 3 handler(s) for this service...
Handler(s):
"D001" established:0 refused:0 current:0 max:972 state:ready
DISPATCHER
(ADDRESS=(PROTOCOL=tcp)(HOST=oel1.oracle.com)(PORT=46966))
"D000" established:0 refused:0 current:0 max:972 state:ready
DISPATCHER
(ADDRESS=(PROTOCOL=tcp)(HOST=oel1.oracle.com)(PORT=57952))
"DEDICATED" established:0 refused:0 state:ready
LOCAL SERVER
Service "PROD_XPT.oracle.com" has 1 instance(s).
Instance "PROD", status READY, has 1 handler(s) for this service...
Handler(s):
"DEDICATED" established:0 refused:0 state:ready
LOCAL SERVER
The command completed successfully
－－測試一把
[oracle@oel1 admin]$ sqlplus as sysdba
Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options
SQL>
--判斷連線ｓｅｓｓｉｏｎ是否接入了共享服務
[oracle@oel1 admin]$ sqlplus as sysdba
[oracle@oel1 admin]$ sqlplus as sysdba
SQL*Plus: Release 10.2.0.1.0 - Production on Fri Oct 26 20:37:34 2012
Copyright (c) 1982, 2005, Oracle. All rights reserved.
Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options
SQL> select sid,server from v$session where sid=(select sid from v$mystat where rownum<=1);
SID SERVER
---------- ---------
160 SHARED
---------- ---------
160 SHARED
--使用埠連線來確認Liste1，liste3的兩個埠都可以正常訪問
[oracle@oel1 admin]$ sqlplus as sysdba
SQL*Plus: Release 10.2.0.1.0 - Production on Fri Oct 26 20:44:38 2012
Copyright (c) 1982, 2005, Oracle. All rights reserved.
Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options
[oracle@oel1 admin]$ sqlplus as sysdba
SQL*Plus: Release 10.2.0.1.0 - Production on Fri Oct 26 20:45:12 2012
Copyright (c) 1982, 2005, Oracle. All rights reserved.
Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options
SQL>
--共享伺服器關閉失敗，所以在做data guard等場景時，不適合用共享伺服器模式
SQL> shut immediate
ORA-00106: cannot startup/shutdown database when connected to a dispatcher
SQL>
SQL> shut immediate
ORA-00106: cannot startup/shutdown database when connected to a dispatcher
SQL>
--復現共享服務繫結監聽錯誤的情況
--把本地監聽設定為空
SQL> alter system set local_listener='';
--把本地監聽設定為空
SQL> alter system set local_listener='';
System altered.
LSNRCTL> service liste1
Connecting to (ADDRESS=(PROTOCOL=tcp)(HOST=oel1.oracle.com)(PORT=1521))
Services Summary...
Service "PROD.oracle.com" has 2 instance(s).
Instance "PROD", status UNKNOWN, has 1 handler(s) for this service...
Handler(s):
"DEDICATED" established:8 refused:0
LOCAL SERVER
Instance "PROD", status READY, has 3 handler(s) for this service...
Handler(s):
"D001" established:0 refused:0 current:0 max:972 state:ready
DISPATCHER
(ADDRESS=(PROTOCOL=tcp)(HOST=oel1.oracle.com)(PORT=46966))
"D000" established:0 refused:0 current:0 max:972 state:ready
DISPATCHER
(ADDRESS=(PROTOCOL=tcp)(HOST=oel1.oracle.com)(PORT=57952))
"DEDICATED" established:0 refused:0 state:ready
LOCAL SERVER
Service "PROD_XPT.oracle.com" has 1 instance(s).
Instance "PROD", status READY, has 1 handler(s) for this service...
Handler(s):
"DEDICATED" established:0 refused:0 state:ready
LOCAL SERVER
The command completed successfully
LSNRCTL> service liste2
Connecting to (ADDRESS=(PROTOCOL=tcp)(HOST=oel1.oracle.com)(PORT=1529))
Services Summary...
Service "PROD.oracle.com" has 1 instance(s).
Instance "PROD", status UNKNOWN, has 1 handler(s) for this service...
Handler(s):
"DEDICATED" established:0 refused:0
LOCAL SERVER
The command completed successfully
--看起來好像也影響不大，後臺的共享服務都起來了。但是連庫連不上去。
[oracle@oel1 admin]$ sqlplus as sysdba
SQL*Plus: Release 10.2.0.1.0 - Production on Fri Oct 26 20:34:44 2012
Copyright (c) 1982, 2005, Oracle. All rights reserved.
ERROR:
ORA-12523: TNS:listener could not find instance appropriate for the client
connection
所以本地監聽的配置是必須的。
ORA-12523: TNS:listener could not find instance appropriate for the client
connection
所以本地監聽的配置是必須的。
來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/29320885/viewspace-1269924/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- oracle dataguard 配置錯誤彙總Oracle
- Oracle RAC修改網路配置知識點彙總Oracle
- Oracle故障彙總Oracle
- Oracle 架構彙總Oracle架構
- Oracle函式彙總Oracle函式
- Oracle 常用方法彙總Oracle
- oracle異常彙總Oracle
- Oracle ----調節共享伺服器Oracle伺服器
- oracle 常用語句彙總Oracle
- oracle常用函式彙總Oracle函式
- 【轉】oracle X表彙總Oracle
- Oracle當機案例彙總(一)Oracle
- ORACLE DBA培訓命令彙總Oracle
- UDEV方式配置Oracle RAC ASM共享磁碟devOracleASM
- Oracle共享伺服器的連線模式Oracle伺服器模式
- rman configure配置彙總及原理
- Oracle索引塊分裂split資訊彙總Oracle索引
- oracle日常操作彙總:日誌篇Oracle
- Vagrant box 命令彙總彙總
- 小白必看 | 代理ip伺服器知識彙總伺服器
- oracle 10g在共享伺服器模式shared server如何配置大池large poolOracle 10g伺服器模式Server
- Oracle 統計資訊相關命令彙總Oracle
- oracle DG 11g新特性彙總Oracle
- Oracle index索引塊分裂split資訊彙總OracleIndex索引
- ORACLE 10G中閃回彙總Oracle 10g
- Oracle中的臨時表用法彙總Oracle
- Sendmail relay規則及配置檔案用法彙總 sendmail配置 (轉)AI
- Kafka 配置引數彙總及相關說明Kafka
- 新手雲伺服器租用常見問題彙總伺服器
- Oracle goldengate 11g 錯誤彙總OracleGo
- AIX安裝oracle遇到的問題彙總AIOracle
- Jdbc連線Oracle常見錯誤彙總JDBCOracle
- 使用ADO訪問Oracle問題彙總Oracle
- Oracle網路配置之共享模式和專有模式Oracle模式
- jquery彙總jQuery
- 排序彙總排序
- Oracle資料庫啟動問題彙總(一)Oracle資料庫
- Oracle 高水位查詢和處理方法彙總Oracle