oracle最佳連接方式之service簡介及創(chuàng)建（上）

IT那活兒發(fā)布于2023-01-11 13:19 / 2732人閱讀

點擊上方“IT那活兒”公眾號，關(guān)注后了解更多內(nèi)容，不管IT什么活兒，干就完了?。?！

數(shù)據(jù)庫service概要

service是訪問數(shù)據(jù)庫的一種方式。可以通過service將不同的連接區(qū)分開來。service可以當做是一組客戶端或者應(yīng)用的邏輯組合；每個多帶帶的service 可以設(shè)定其自己的屬性，以滿足不同的需要。

例如，為不同的應(yīng)用、業(yè)務(wù)、PDB、instance創(chuàng)建不同的service，service的配置要在參數(shù) service_names中設(shè)定，同時如果service 要在監(jiān)聽中完成注冊，那么一個新建是service 才能夠正常使用從而可以將連接映射到不同的服務(wù)器上（集群環(huán)境）以進行區(qū)分。service具有高可用性和負載均衡性。

說明：只是service 漂移，并沒有vip漂移這樣一旦出現(xiàn)問題才能路由到新實例上新的sql 動作才能觸發(fā)漂移動作，否則服務(wù)器端是無法知道和那些和client 進行連接的。觸發(fā)連接遷移是客戶端有操作觸發(fā)才行。也就是重新執(zhí)行SQL等操作。

傳統(tǒng)的TFA實現(xiàn)方式存在如下問題：

連接串通過VIP的順序配置優(yōu)先連接RAC的節(jié)點。
連接串通常不會配置備庫的TNS，主備切換后需要調(diào)整TNS連接配置。
需要人工維護配置文件tnsnames.ora，客戶端多的話，不利于維護。

簡單理解就是原始的TFA(Transparent Application Failover)是在客戶端的tnsnames.ora文件中配置，而數(shù)據(jù)庫的service是在服務(wù)端已經(jīng)配置好了，客戶端無需維護tnsnames.ora文件。

service參數(shù)說明

RAC使用oracle用戶通過srvctl命令來創(chuàng)建數(shù)據(jù)庫的service，可以通過servctl add service –h 獲取參數(shù)幫助信息。

11G 版本 srvctl add service Options 常見參數(shù)：

srvctl add service -d db_unique_name -s service_name {-r "preferred_list"
[-a "available_list"] [-P {BASIC | NONE | PRECONNECT}] | -g server_pool
[-c {UNIFORM | SINGLETON]} [-k network_number]
[-l [PRIMARY | PHYSICAL_STANDBY | LOGICAL_STANDBY | SNAPSHOT_STANDBY]
[-y {AUTOMATIC | MANUAL}] [-q {TRUE | FALSE}] [-x {TRUE | FALSE}]
[-j {SHORT | LONG}][-B {NONE | SERVICE_TIME | THROUGHPUT}]
[-e {NONE | SESSION | SELECT}] [-m {NONE | BASIC}] [-z failover_retries]
[-w failover_delay]

19C 版本部分參數(shù)稍有改動：

srvctl add service -database db_unique_name -service service_name_list
[-pdb pluggable_database] [-eval]
[-preferred preferred_list] [-available available_list] [-failback {YES | NO}]
[-netnum network_number] [-tafpolicy {BASIC | NONE}]
[-edition edition_name]
[-role "[PRIMARY][,PHYSICAL_STANDBY][,LOGICAL_STANDBY][,SNAPSHOT_STANDBY]"
[-policy {AUTOMATIC | MANUAL}] [-notification {TRUE | FALSE}]
[-clbgoal {SHORT | LONG}] [-failovertype {NONE|SESSION|SELECT|TRANSACTION|AUTO}]
[-rlbgoal {NONE | SERVICE_TIME | THROUGHPUT}] [-dtp {TRUE | FALSE}]
[-failovermethod {NONE | BASIC}] [-failoverretry failover_retries]
[-drain_timeout timeout] [-stopoption {NONE|IMMEDIATE|TRANSACTIONAL}]
[-failover_restore {NONE|LEVEL1|AUTO}] [-failoverdelay failover_delay]
[-sql_translation_profile sql_translation_profile]
[-global {TRUE | FALSE}] [-maxlag max_lag_time] [-commit_outcome {TRUE|FALSE}]
[-retention retention_time] [-replay_init_time replay_initiation_time]
[-session_state {STATIC|DYNAMIC|AUTO}] [-force] [-verbose]

service 創(chuàng)建模版 DB11G MAA

注：備庫的service需要在主庫先創(chuàng)建,然后數(shù)據(jù)字典同步過去。

3.1 主庫創(chuàng)建備庫角色的兩個service

Create the service resource in primary cluster, for example, add service1 and service2

$ $ORACLE_HOME/bin/srvctl add service -d  -s 
 -l physical_standby -r ","

$ $ORACLE_HOME/bin/srvctl add service -d  -s 
 -l physical_standby -r ","

在主庫執(zhí)行：
RAC DB_PRIMARY 是主庫的db_unique_name。

srvctl add service -d EMREP -s EMREP_RD_S1 -l physical_standby -r EMREP1 -a EMREP2
srvctl add service -d EMREP -s EMREP_RD_S2 -l physical_standby -r EMREP2 -a EMREP1

檢查服務(wù)狀態(tài)：

[oracle@emrep01 dbs]$ srvctl status service -d EMREP
Service EMREP_RD_S1 is not running.
Service EMREP_RD_S2 is not running.

3.2 主庫啟動service并注冊到數(shù)據(jù)庫

主庫啟動service并注冊到數(shù)據(jù)庫通過日志傳輸給備庫：

$ $ORACLE_HOME/bin/srvctl start service -d

這時候觀察主庫兩個節(jié)點的alert日志發(fā)現(xiàn)如下語句：

主庫節(jié)點1 alert日志：

ALTER SYSTEM SET service_names=EMREP_RD_S1 SCOPE=MEMORY SID=EMREP1;

主庫節(jié)點2 alert日志：

ALTER SYSTEM SET service_names=EMREP_RD_S2 SCOPE=MEMORY SID=EMREP2;

3.3 主庫停止service

$ $ORACLE_HOME/bin/srvctl stop service -d 
$ $ORACLE_HOME/bin/srvctl status service -d 

Service service1 is not running.
Service service2 is not running.

srvctl stop service -d EMREP

這時候觀察主庫兩個節(jié)點的alert日志發(fā)現(xiàn)如下語句：

ALTER SYSTEM SET service_names=EMREP SCOPE=MEMORY SID=EMREP1;
ALTER SYSTEM SET service_names=EMREP SCOPE=MEMORY SID=EMREP2;

即使使用srvctl start database 重新啟動數(shù)據(jù)庫，它也不會啟動這個service服務(wù)，除非顯示的手動使用srvctl start service啟動！

3.4 確保備庫已經(jīng)同步

set lines 400 pages 1000
select thread#, max(sequence#) as "last_applied_log"
from v$log_history
group by thread#;

3.5 在備庫創(chuàng)建服務(wù)

建議在MRP所在的啟動service。

$ $ORACLE_HOME/bin/srvctl add service -d  -s  -l physical_standby -r ","
$ $ORACLE_HOME/bin/srvctl add service -d  -s  -l physical_standby -r ","

# 備庫創(chuàng)建,注意db_unique_name是備庫的
srvctl add service -d DGEMREP -s EMREP_RD_S1 -l physical_standby -r EMREP1 -a EMREP2
srvctl add service -d DGEMREP -s EMREP_RD_S2 -l physical_standby -r EMREP2 -a EMREP1

3.6 檢查service狀態(tài)

ora.dgEMREP.EMREP_rd_s1.svc
1   OFFLINE OFFLINE
ora.dgEMREP.EMREP_rd_s2.svc
1   OFFLINE OFFLINE

資源是OFFLINE狀態(tài)，因為還沒啟動。

3.7 備庫啟動service

備庫啟動service之后，會在備庫的監(jiān)聽器中注冊該service。

$ $ORACLE_HOME/bin/srvctl start service -d 

$ $ORACLE_HOME/bin/srvctl status service -d 

Service  is running on instance(s) ,
Service  is running on instance(s) ,

啟動備庫的service：

srvctl start service -d dgEMREP

這時候觀察主庫兩個節(jié)點的alert日志發(fā)現(xiàn)如下語句：

ALTER SYSTEM SET service_names=EMREP_RD_S1 SCOPE=MEMORY SID=EMREP1;
ALTER SYSTEM SET service_names=EMREP_RD_S2 SCOPE=MEMORY SID=EMREP2;

檢查服務(wù)狀態(tài)：

ora.dgEMREP.EMREP_rd_s1.svc
1  ONLINE  ONLINE       emrep01
ora.dgEMREP.EMREP_rd_s2.svc
1  ONLINE  ONLINE       emrep02

檢查service狀態(tài)：

[oracle@emrep02 ~]$ srvctl status service -d dgEMREP
Service EMREP_RD_S1 is running on instance(s) EMREP1
Service EMREP_RD_S2 is running on instance(s) EMREP2

查看備庫兩個節(jié)點的監(jiān)聽：

節(jié)點1

[grid@emrep01 ~]$ lsnrctl status

LSNRCTL for Linux: Version 11.2.0.4.0 - Production on 07-APR-2022 22:02:28

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

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=LISTENER)))
STATUS of the LISTENER
------------------------
Alias LISTENER
Version TNSLSNR for Linux: Version 11.2.0.4.0 - Production
Start Date                07-APR-2022 20:33:54
Uptime 0 days 1 hr. 28 min. 34 sec
Trace Level               off
Security                  ON: Local OS Authentication
SNMP OFF
Listener Parameter File   /u01/11.2.0/grid/network/admin/listener.ora
Listener Log File         /u01/11.2.0/grid/log/diag/tnslsnr/emrep01/listener/alert/log.xml
Listening Endpoints Summary...
(DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=LISTENER)))
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=10.25.140.184)(PORT=1521)))
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=10.25.140.186)(PORT=1521)))
Services Summary...
Service "+ASM" has 1 instance(s).
Instance "+ASM1", status READY, has 1 handler(s) for this service...
Service "EMREP_RD_S1" has 1 instance(s).
Instance "EMREP1", status READY, has 1 handler(s) for this service...
Service "dgEMREP" has 1 instance(s).
Instance "EMREP1", status READY, has 1 handler(s) for this service...
Service "EMREPXDB" has 1 instance(s).
Instance "EMREP1", status READY, has 1 handler(s) for this service...
The command completed successfully

節(jié)點2

[grid@emrep02 ~]$ lsnrctl status

LSNRCTL for Linux: Version 11.2.0.4.0 - Production on 07-APR-2022 22:07:01

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

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=LISTENER)))
STATUS of the LISTENER
------------------------
Alias LISTENER
Version TNSLSNR for Linux: Version 11.2.0.4.0 - Production
Start Date                07-APR-2022 20:34:14
Uptime 0 days 1 hr. 32 min. 47 sec
Trace Level               off
Security                  ON: Local OS Authentication
SNMP OFF
Listener Parameter File   /u01/11.2.0/grid/network/admin/listener.ora
Listener Log File         /u01/11.2.0/grid/log/diag/tnslsnr/emrep02/listener/alert/log.xml
Listening Endpoints Summary...
(DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=LISTENER)))
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=10.25.140.185)(PORT=1521)))
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=10.25.140.187)(PORT=1521)))
Services Summary...
Service "+ASM" has 1 instance(s).
Instance "+ASM2", status READY, has 1 handler(s) for this service...
Service "EMREP_RD_S2" has 1 instance(s).
Instance "EMREP2", status READY, has 1 handler(s) for this service...
Service "dgEMREP" has 1 instance(s).
Instance "EMREP2", status READY, has 1 handler(s) for this service...
Service "EMREPXDB" has 1 instance(s).
Instance "EMREP2", status READY, has 1 handler(s) for this service...
The command completed successfully

3.8 檢查備庫service是否隨備庫啟動而啟動

$ $ORACLE_HOME/bin/srvctl stop database -d 
$ $ORACLE_HOME/bin/srvctl start database -d 
$ $ORACLE_HOME/bin/srvctl status service -d 
Service  is running on instance(s) ,
Service  is running on instance(s) ,

啟動數(shù)據(jù)庫：

srvctl start database -d dgEMREP

檢查資源狀態(tài)：

ora.dgEMREP.EMREP_rd_s1.svc
1  ONLINE  ONLINE       emrep01
ora.dgEMREP.EMREP_rd_s2.svc
1  ONLINE  ONLINE       emrep01

隨著數(shù)據(jù)庫啟動而啟動了。

3.9 創(chuàng)建主庫的service

主庫寫服務(wù),在主庫執(zhí)行：

srvctl add service -d EMREP -s EMREP_R_S1 -l PRIMARY -r EMREP1 -a EMREP2
srvctl add service -d EMREP -s EMREP_R_S2 -l PRIMARY -r EMREP2 -a EMREP1

觀察主庫兩個節(jié)點的alert日志并沒有同步給備庫。

檢查資源狀態(tài)：

ora.EMREP.EMREP_r_s1.svc
     1 OFFLINE OFFLINE                                                  
ora.EMREP.EMREP_r_s2.svc
     1 OFFLINE OFFLINE                                                  
ora.EMREP.EMREP_rd_s1.svc
     1 OFFLINE OFFLINE                                                  
ora.EMREP.EMREP_rd_s2.svc
     1 OFFLINE OFFLINE

說明：此時資源的狀態(tài)是OFFLINE，因為還沒啟動。

注意千萬別把備庫的服務(wù)在主庫啟動了！

3.10 啟動主庫的service

注意:不要使用servctl start service –d xxx來啟動，這回把備庫的service啟動起來，需要制定-s參數(shù)，來啟動特定的service。

只啟動主庫的service：

srvctl start service -d EMREP -s EMREP_r_s1
srvctl start service -d EMREP -s EMREP_r_s2

檢查狀態(tài)：

ora.EMREP.EMREP_r_s1.svc
1 ONLINE  ONLINE       emrep01
ora.EMREP.EMREP_r_s2.svc
1 ONLINE  ONLINE       emrep02
ora.EMREP.EMREP_rd_s1.svc
1 OFFLINE OFFLINE
ora.EMREP.EMREP_rd_s2.svc
1 OFFLINE OFFLINE

檢查service狀態(tài)：

srvctl config service -d EMREP -s EMREP_r_s1
Service name: EMREP_R_S1
Service is enabled
Server pool: EMREP_EMREP_R_S1
Cardinality: 1
Disconnect: false
Service role: PRIMARY
Management policy: AUTOMATIC
DTP transaction: false
AQ HA notifications: false
Failover type: NONE
Failover method: NONE
TAF failover retries: 0
TAF failover delay: 0
Connection Load Balancing Goal: LONG
Runtime Load Balancing Goal: NONE
TAF policy specification: NONE
Edition:
Preferred instances: EMREP1
Available instances: EMREP2

srvctl config service -d EMREP -s EMREP_r_s2
Service name: EMREP_R_S2
Service is enabled
Server pool: EMREP_EMREP_R_S2
Cardinality: 1
Disconnect: false
Service role: PRIMARY
Management policy: AUTOMATIC
DTP transaction: false
AQ HA notifications: false
Failover type: NONE
Failover method: NONE
TAF failover retries: 0
TAF failover delay: 0
Connection Load Balancing Goal: LONG
Runtime Load Balancing Goal: NONE
TAF policy specification: NONE
Edition:
Preferred instances: EMREP2
Available instances: EMREP1

檢查監(jiān)聽狀態(tài)：

節(jié)點1

Listening Endpoints Summary...
(DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=LISTENER)))
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=10.25.140.180)(PORT=1521)))
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=10.25.140.166)(PORT=1521)))
Services Summary...
Service "+ASM" has 1 instance(s).
Instance "+ASM1", status READY, has 1 handler(s) for this service...
Service "EMREP_R_S1" has 1 instance(s).
Instance "EMREP1", status READY, has 1 handler(s) for this service...
Service "EMREP" has 1 instance(s).
Instance "EMREP1", status READY, has 1 handler(s) for this service...
Service "EMREPXDB" has 1 instance(s).
Instance "EMREP1", status READY, has 1 handler(s) for this service...
The command completed successfully

節(jié)點2

Listening Endpoints Summary...
(DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=LISTENER)))
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=10.25.140.181)(PORT=1521)))
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=10.25.140.167)(PORT=1521)))
Services Summary...
Service "+ASM" has 1 instance(s).
Instance "+ASM2", status READY, has 1 handler(s) for this service...
Service "EMREP_R_S2" has 1 instance(s).
Instance "EMREP2", status READY, has 1 handler(s) for this service...
Service "EMREP" has 1 instance(s).
Instance "EMREP2", status READY, has 1 handler(s) for this service...
Service "EMREPXDB" has 1 instance(s).
Instance "EMREP2", status READY, has 1 handler(s) for this service...
The command completed successfully

3.11 備庫添加主庫的service

--備庫添加主庫的service

備庫上執(zhí)行：

srvctl add service -d DGEMREP -s EMREP_R_S1 -l PRIMARY -r EMREP1 -a EMREP2
srvctl add service -d DGEMREP -s EMREP_R_S2 -l PRIMARY -r EMREP2 -a EMREP1

主備切換service變化，切換前：

主庫service

備庫service

主備切換service變化，切換后：

新主庫service

新備庫service

主備switch over之后，需要在主備庫啟動相應(yīng)的service即可！

新主庫關(guān)閉之前備庫的service，啟用主庫的service，新備庫啟用備庫的service。

建議：切換之前可以考慮把主備庫的service先關(guān)閉，切換完成后再打開，不然原先連接備庫的service可能連到了主庫上面。

service 創(chuàng)建模版 DB19C MAA

4.1 主庫創(chuàng)建備庫的service

srvctl add service -db HBCMDB -pdb ORCL -service ORCL_RD_S1 
-role physical_standby -preferred orcl1 -available orcl2

srvctl add service -db HBCMDB -pdb ORCL -service ORCL_RD_S2 
-role physical_standby -preferred orcl2 -available orcl1

4.2 主庫啟動service并注冊到數(shù)據(jù)庫

主庫啟動service并注冊到數(shù)據(jù)庫通過日志傳輸給備庫。

srvctl start service -d HBCMDB

4.3 主庫關(guān)閉service

srvctl stop service -d HBCMDB

4.4 確保備庫已經(jīng)同步

set lines 400 pages 1000
select thread#, max(sequence#) as "last_applied_log"
from v$log_history
group by thread#;

4.5 在備庫中創(chuàng)建service

srvctl add service -db DGORCL -pdb ORCL -service ORCL_RD_S1 
-role physical_standby -preferred orcl1 -available orcl2
srvctl add service -db DGORCL -pdb ORCL -service ORCL_RD_S2 
-role physical_standby -preferred orcl1 -available orcl2

4.6 備庫啟動service

srvctl start service -d DGORCL
或者
srvctl start service -d DGORCL -s ORCL_rd_s1
srvctl start service -d DGORCL -s ORCL_rd_s2

4.7 備庫檢查service狀態(tài)

[oracle@server01 admin]$ srvctl config service -d DGORCL
Service name: ORCL_R_S1
Server pool:
Cardinality: 1
Service role: PRIMARY
Management policy: AUTOMATIC
DTP transaction: false
AQ HA notifications: false
Global: false
Commit Outcome: false
Failover type:
Failover method:
Failover retries:
Failover delay:
Failover restore: NONE
Connection Load Balancing Goal: LONG
Runtime Load Balancing Goal: NONE
TAF policy specification: NONE
Edition:
Pluggable database name: ORCL
Hub service:
Maximum lag time: ANY
SQL Translation Profile:
Retention: 86400 seconds
Replay Initiation Time: 300 seconds
Drain timeout:
Stop option:
Session State Consistency: DYNAMIC
GSM Flags: 0
Service is enabled
Preferred instances: orcl1
Available instances: orcl2
CSS critical: no


Service name: ORCL_R_S2
Server pool:
Cardinality: 1
Service role: PRIMARY
Management policy: AUTOMATIC
DTP transaction: false
AQ HA notifications: false
Global: false
Commit Outcome: false
Failover type:
Failover method:
Failover retries:
Failover delay:
Failover restore: NONE
Connection Load Balancing Goal: LONG
Runtime Load Balancing Goal: NONE
TAF policy specification: NONE
Edition:
Pluggable database name: ORCL
Hub service:
Maximum lag time: ANY
SQL Translation Profile:
Retention: 86400 seconds
Replay Initiation Time: 300 seconds
Drain timeout:
Stop option:
Session State Consistency: DYNAMIC
GSM Flags: 0
Service is enabled
Preferred instances: orcl2
Available instances: orcl1
CSS critical: no

Service name: ORCL_RD_S1
Server pool:
Cardinality: 1
Service role: PHYSICAL_STANDBY
Management policy: AUTOMATIC
DTP transaction: false
AQ HA notifications: false
Global: false
Commit Outcome: false
Failover type:
Failover method:
Failover retries:
Failover delay:
Failover restore: NONE
Connection Load Balancing Goal: LONG
Runtime Load Balancing Goal: NONE
TAF policy specification: NONE
Edition:
Pluggable database name: ORCL
Hub service:
Maximum lag time: ANY
SQL Translation Profile:
Retention: 86400 seconds
Replay Initiation Time: 300 seconds
Drain timeout:
Stop option:
Session State Consistency: DYNAMIC
GSM Flags: 0
Service is enabled
Preferred instances: orcl1
Available instances: orcl2
CSS critical: no
Service uses Java: false

Service name: ORCL_RD_S2
Server pool:
Cardinality: 1
Service role: PHYSICAL_STANDBY
Management policy: AUTOMATIC
DTP transaction: false
AQ HA notifications: false
Global: false
Commit Outcome: false
Failover type:
Failover method:
Failover retries:
Failover delay:
Failover restore: NONE
Connection Load Balancing Goal: LONG
Runtime Load Balancing Goal: NONE
TAF policy specification: NONE
Edition:
Pluggable database name: ORCL
Hub service:
Maximum lag time: ANY
SQL Translation Profile:
Retention: 86400 seconds
Replay Initiation Time: 300 seconds
Drain timeout:
Stop option:
Session State Consistency: DYNAMIC
GSM Flags: 0
Service is enabled
Preferred instances: orcl1
Available instances: orcl2
CSS critical: no
Service uses Java: false

4.8 主庫創(chuàng)建主庫的service

srvctl add service -db HBCMDB -pdb ORCL -service ORCL_R_S1 -
role primary -preferred orcl1 -available orcl2
srvctl add service -db HBCMDB -pdb ORCL -service ORCL_R_S2 -
role primary -preferred orcl2 -available orcl1

4.9 啟動主庫的service

srvctl start service -d HBCMDB -s ORCL_R_S1
srvctl start service -d HBCMDB -s ORCL_R_S2

4.10 備庫添加主庫的service

srvctl add service -db DGORCL -pdb ORCL -service ORCL_R_S1 -
role primary -preferred orcl1 -available orcl2
srvctl add service -db DGORCL -pdb ORCL -service ORCL_R_S2 -
role primary -preferred orcl2 -available orcl1