Oracle Database Service
資料庫服務(Database Services)
資料庫服務是表示一個或多個資料庫例項的命名方式。服務能讓你組合資料庫工作負載並將一個特定的工作請求分發到一個合適的例項。一個資料庫服務代表了一個單獨的資料庫。這個資料庫可以是單例項資料庫或者RAC資料庫。一個全域性資料庫服務透過資料複製的多資料庫同步機制來提供服務。
資料庫服務將單資料庫的工作負載分成多個互不相交的工作組。每個資料庫服務使用常見的屬性，服務級別閾值與優先順序來代表一個工作負載。分組是基於工作屬性的，它可能包含被使用的應用功能，執行應用功能的優先順序，被管理的job型別或者應用功能中使用的資料範圍或job型別。
資料庫連線請求可以包含一個資料庫服務名。因此中間層應用與C/S應用可以透過在TNS連線資料中指定資料庫服務作為連線的一部分來使用服務。如果沒有包含資料為服務名並且Net服務檔案listener.ora指定了一個預設資料庫服務，那麼連線將會使用這個預設的資料庫服務。
資料庫服務可以對單個資料庫配置工作負載，管理，啟用與禁用，並且可以作為單個實體來檢測工作負載。可以使用標準工具比如DBCA,NETCA和Cloud Control來進行操作。Cloud Control支援檢視與操作服務。
在RAC環境中，資料庫服務可以跨一個或多個例項並且基於事務效能來達到工作負載平衡。這種功能提供了無人值守的恢復，回滾與完全的位置透明。RAC也能讓你使用Cloud Control，DBCA與SRVCLT來管理多個資料庫服務功能。
資料庫服務描述了應用程式，應用程式功能和功能服務或資料依賴服務的資料範圍。功能服務通常對映到工作負載。會話使用特定功能被分組在一起。相反，資料領帶根據資料鍵值將會話路由到資料服務。工作請求對映會在應用程式服務與TP監控的物件關係對映層中出現。例如，在RAC中，因為資料庫是共享的，這些範圍可能基於需要動態的完成。
另外資料庫服務可以透過應用程式來使用，Oracle資料庫也支援兩種內部資料庫服務:SYS$BACKGROUND只能由後臺程式使用，SYS$USERS是使用者會話的預設資料庫服務，它不與服務相關聯。
使用資料庫服務請求不需要改變應用等程式程式碼。客戶端可以連線到一個命名的資料服務進行操作。服務端，比如Oracle排程，並行執行與Oracle資料庫高階佇列，將資料庫服務名設定為工作負載定義的一部分。使用資料庫服務執行的工作請求繼承了服務的效能閾值與作為服務一部分的測量。
資料庫服務與效能
資料庫服務在效能調整中也提供了一種額外的維度。在大多數系統中所有會話都是匿名與共享的，可以使用"服務與SQL"調整來替代"會話與SQL"調整。使用資料庫服務，工作負載可見且可以被檢測。透過應用程式的資源消耗與等待屬性來實現。另外，指定到資料庫服務的資源當負載增加或減少時可以擴充套件。動態資源分配對於滿足你出現的需求是最具成本效益的解決方案。例如，資料庫服務會自動被
檢測並與服務級別閾值進行比較。效能問題會報告給Cloun Control，並執行自動或排程解決方案。
使用資料庫服務的資料庫功能
有些Oracle資料庫功能支援資料庫服務。AWR管理服務的效能。AWR記錄了資料庫服務的效能，包含執行時間，等待型別與服務的資源消耗。當資料庫服務響應時間超過了閾值AWR會發出警告。動態效能檢視使用一小時歷史資料來報告當前服務的效能度量。每個資料庫服務對於響應時間與CPU消耗都有服務質量閾值。
資料庫資源客理器可以將資料庫服務對映到使用者組。因此，可以自動管理資料庫服務的優先順序。可以使用使用者組來定義相對優先順序或資源消耗。
可以為資料庫服務指定一個編輯屬性。編輯可以使資料庫中的相同物件有兩個或多個版本。當你對資料庫服務指定編輯屬性時，所有後續的連線將使用這個編輯屬性來初始化會話。
對資料庫服務指定一個編輯屬性可以更容易的管理資源使用。例如，使用編輯屬性的資料庫服務可以被置於RAC環境中的單獨例項中，資料庫資源管理器透過使用不同編輯屬性的相關資料庫服務所關聯的資源計劃來管理資源的使用。
對於Oracle排程，可以選擇在建立job型別時指定資料庫服務。在執行時間，job被指派到job型別，並且job型別可以使用資料庫服務來執行。指定job型別的資料庫服務可以確保透過job排程的執行被工作負載管理所識別與執行調整。
對於並行查詢與並行DML，查詢協調者連線到資料庫服務就像其它客戶端連線資料一樣。對於執行時間並行查詢程式繼承資料庫服務。在查詢執行結束後，並行執行程式會歸還給預設的資料庫服務。
建立資料庫服務
依賴於資料庫的配置有幾種建立資料庫服務的方式
1.如果是單例項資料庫且由Oracle Restart管理，可以使用srvctl工具來建立資料庫服務
srvctl add service -db db_unique_name -service service_name
[oracle@oracle12c admin]$ srvctl add service -db jycs -service jycs_service
檢查服務狀態
[oracle@oracle12c admin]$ srvctl status service -db jycs Service jycs_service is not running.
檢視服務配置資訊
[oracle@oracle12c admin]$ srvctl config service -db jycs Service name: jycs_service Cardinality: SINGLETON Disconnect: false Service role: PRIMARY Management policy: AUTOMATIC DTP transaction: false AQ HA notifications: false Global: false Commit Outcome: false Failover type: Failover method: TAF failover retries: TAF failover delay: Connection Load Balancing Goal: LONG Runtime Load Balancing Goal: NONE TAF policy specification: NONE Edition: Pluggable database name: Maximum lag time: ANY SQL Translation Profile: Retention: 86400 seconds Replay Initiation Time: 300 seconds Session State Consistency: GSM Flags: 0 Service is enabled
啟動服務
[oracle@oracle12c admin]$ srvctl start service -db jycs -service jycs_service
檢視監聽是否註冊了服務
[grid@oracle12c ~]$ lsnrctl status LSNRCTL for Linux: Version 12.1.0.2.0 - Production on 05-MAY-2016 11:56:23 Copyright (c) 1991, 2014, Oracle. All rights reserved. Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=oracle12c)(PORT=1521))) STATUS of the LISTENER ------------------------ Alias LISTENER Version TNSLSNR for Linux: Version 12.1.0.2.0 - Production Start Date 05-MAY-2016 08:41:25 Uptime 0 days 3 hr. 14 min. 58 sec Trace Level off Security ON: Local OS Authentication SNMP OFF Listener Parameter File /u01/app/oracle/product/12.1/grid/network/admin/listener.ora Listener Log File /u01/app/grid/diag/tnslsnr/oracle12c/listener/alert/log.xml Listening Endpoints Summary... (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=oracle12c)(PORT=1521))) (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521))) (DESCRIPTION=(ADDRESS=(PROTOCOL=tcps)(HOST=oracle12c)(PORT=5500))(Security=(my_wallet_directory=/u01/app/oracle/product/12.1/db/admin/jycs/xdb_wallet))(Presentation=HTTP)(Session=RAW)) Services Summary... Service "jycs" has 1 instance(s). Instance "jycs", status READY, has 1 handler(s) for this service... Service "jycsXDB" has 1 instance(s). Instance "jycs", status READY, has 1 handler(s) for this service... Service "jycs_service" has 1 instance(s). Instance "jycs", status READY, has 1 handler(s) for this service... Service "jycsp1" has 1 instance(s). Instance "jycs", status READY, has 1 handler(s) for this service...
可以看到jycs_service服務已經被監聽註冊，在客戶端配置tns並驗證透過服務jycs_service能否登入資料庫
C:\Users\Administrator>tnsping 12c_jycs_service TNS Ping Utility for 64-bit Windows: Version 11.2.0.4.0 - Production on 05-5月 - 2016 13:21:38 Copyright (c) 1997, 2013, Oracle. All rights reserved. 已使用的引數檔案: D:\oracle\app\Administrator\product\11.2.0\dbhome_1\network\admin\sqlnet.ora 已使用 TNSNAMES 介面卡來解析別名 嘗試連線 (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.56.241)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = jycs_service))) OK (20 毫秒)
C:\Users\Administrator>sqlplus /nolog SQL*Plus: Release 11.2.0.4.0 Production on 星期四 5月 5 14:44:06 2016 Copyright (c) 1982, 2013, Oracle. All rights reserved. SQL> conn sys/system@12c_jycs_service as sysdba 已連線。 SQL>
從執行命令的結果來看透過服務jycs_service可以登入資料庫
2.如果是單例項且沒有使用Oracle Restart來管理資料庫，可以透過以下一種方式來建立資料庫服務
SQL> show parameter service_names NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ service_names string jycs
[grid@oracle12c ~]$ lsnrctl status LSNRCTL for Linux: Version 12.1.0.2.0 - Production on 05-MAY-2016 14:59:06 Copyright (c) 1991, 2014, Oracle. All rights reserved. Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=oracle12c)(PORT=1521))) STATUS of the LISTENER ------------------------ Alias LISTENER Version TNSLSNR for Linux: Version 12.1.0.2.0 - Production Start Date 05-MAY-2016 14:57:53 Uptime 0 days 0 hr. 1 min. 13 sec Trace Level off Security ON: Local OS Authentication SNMP OFF Listener Parameter File /u01/app/oracle/product/12.1/grid/network/admin/listener.ora Listener Log File /u01/app/grid/diag/tnslsnr/oracle12c/listener/alert/log.xml Listening Endpoints Summary... (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=oracle12c)(PORT=1521))) (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521))) (DESCRIPTION=(ADDRESS=(PROTOCOL=tcps)(HOST=oracle12c)(PORT=5500))(Security=(my_wallet_directory=/u01/app/oracle/product/12.1/db/admin/jycs/xdb_wallet))(Presentation=HTTP)(Session=RAW)) Services Summary... Service "jycs" has 1 instance(s). Instance "jycs", status READY, has 1 handler(s) for this service... Service "jycsXDB" has 1 instance(s). Instance "jycs", status READY, has 1 handler(s) for this service... Service "jycs_service" has 1 instance(s). Instance "jycs", status READY, has 1 handler(s) for this service... Service "jycsp1" has 1 instance(s). Instance "jycs", status READY, has 1 handler(s) for this service... Service "jycsp10" has 1 instance(s). Instance "jycs", status READY, has 1 handler(s) for this service... Service "jycsp2" has 1 instance(s). Instance "jycs", status READY, has 1 handler(s) for this service... Service "jycsp3" has 1 instance(s). Instance "jycs", status READY, has 1 handler(s) for this service... Service "jycsp4" has 1 instance(s). Instance "jycs", status READY, has 1 handler(s) for this service... Service "jycsp5" has 1 instance(s). Instance "jycs", status READY, has 1 handler(s) for this service... Service "jycsp6" has 1 instance(s). Instance "jycs", status READY, has 1 handler(s) for this service... Service "jycsp7" has 1 instance(s). Instance "jycs", status READY, has 1 handler(s) for this service... Service "jycsp8" has 1 instance(s). Instance "jycs", status READY, has 1 handler(s) for this service... Service "jycsp9" has 1 instance(s). Instance "jycs", status READY, has 1 handler(s) for this service...
增加一個名為jycs_service_2的服務名
SQL> alter system set service_names='jycs,jycs_service_2' scope=both; System altered.
檢查監聽是否註冊了服務jycs_service_2
[grid@oracle12c ~]$ lsnrctl status LSNRCTL for Linux: Version 12.1.0.2.0 - Production on 05-MAY-2016 15:02:08 Copyright (c) 1991, 2014, Oracle. All rights reserved. Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=oracle12c)(PORT=1521))) STATUS of the LISTENER ------------------------ Alias LISTENER Version TNSLSNR for Linux: Version 12.1.0.2.0 - Production Start Date 05-MAY-2016 14:57:53 Uptime 0 days 0 hr. 4 min. 15 sec Trace Level off Security ON: Local OS Authentication SNMP OFF Listener Parameter File /u01/app/oracle/product/12.1/grid/network/admin/listener.ora Listener Log File /u01/app/grid/diag/tnslsnr/oracle12c/listener/alert/log.xml Listening Endpoints Summary... (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=oracle12c)(PORT=1521))) (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521))) (DESCRIPTION=(ADDRESS=(PROTOCOL=tcps)(HOST=oracle12c)(PORT=5500))(Security=(my_wallet_directory=/u01/app/oracle/product/12.1/db/admin/jycs/xdb_wallet))(Presentation=HTTP)(Session=RAW)) Services Summary... Service "jycs" has 1 instance(s). Instance "jycs", status READY, has 1 handler(s) for this service... Service "jycsXDB" has 1 instance(s). Instance "jycs", status READY, has 1 handler(s) for this service... Service "jycs_service_2" has 1 instance(s). Instance "jycs", status READY, has 1 handler(s) for this service... The command completed successfully
從這裡可以看到jycs_service_2服務已被監聽所註冊,但是透過這種方式建立服務後，原來PDB的服務從監聽中消失了，這可能是12.1.0.2的bug（猜測)，如是重啟資料庫就可以解決這個問題。
SQL> shutdown immediate Database closed. Database dismounted. ORACLE instance shut down. SQL> startup ORACLE instance started. Total System Global Area 1073741824 bytes Fixed Size 2932632 bytes Variable Size 377487464 bytes Database Buffers 687865856 bytes Redo Buffers 5455872 bytes Database mounted. Database opened.
[grid@oracle12c ~]$ lsnrctl status LSNRCTL for Linux: Version 12.1.0.2.0 - Production on 05-MAY-2016 15:02:58 Copyright (c) 1991, 2014, Oracle. All rights reserved. Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=oracle12c)(PORT=1521))) STATUS of the LISTENER ------------------------ Alias LISTENER Version TNSLSNR for Linux: Version 12.1.0.2.0 - Production Start Date 05-MAY-2016 14:57:53 Uptime 0 days 0 hr. 5 min. 5 sec Trace Level off Security ON: Local OS Authentication SNMP OFF Listener Parameter File /u01/app/oracle/product/12.1/grid/network/admin/listener.ora Listener Log File /u01/app/grid/diag/tnslsnr/oracle12c/listener/alert/log.xml Listening Endpoints Summary... (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=oracle12c)(PORT=1521))) (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521))) (DESCRIPTION=(ADDRESS=(PROTOCOL=tcps)(HOST=oracle12c)(PORT=5500))(Security=(my_wallet_directory=/u01/app/oracle/admin/jycs/xdb_wallet))(Presentation=HTTP)(Session=RAW)) Services Summary... Service "jycs" has 1 instance(s). Instance "jycs", status READY, has 1 handler(s) for this service... Service "jycsXDB" has 1 instance(s). Instance "jycs", status READY, has 1 handler(s) for this service... Service "jycs_service" has 1 instance(s). Instance "jycs", status READY, has 1 handler(s) for this service... Service "jycs_service_2" has 1 instance(s). Instance "jycs", status READY, has 1 handler(s) for this service... Service "jycsp1" has 1 instance(s). Instance "jycs", status READY, has 1 handler(s) for this service... Service "jycsp10" has 1 instance(s). Instance "jycs", status READY, has 1 handler(s) for this service... Service "jycsp2" has 1 instance(s). Instance "jycs", status READY, has 1 handler(s) for this service... Service "jycsp3" has 1 instance(s). Instance "jycs", status READY, has 1 handler(s) for this service... Service "jycsp4" has 1 instance(s). Instance "jycs", status READY, has 1 handler(s) for this service... Service "jycsp5" has 1 instance(s). Instance "jycs", status READY, has 1 handler(s) for this service... Service "jycsp6" has 1 instance(s). Instance "jycs", status READY, has 1 handler(s) for this service... Service "jycsp7" has 1 instance(s). Instance "jycs", status READY, has 1 handler(s) for this service... Service "jycsp8" has 1 instance(s). Instance "jycs", status READY, has 1 handler(s) for this service... Service "jycsp9" has 1 instance(s). Instance "jycs", status READY, has 1 handler(s) for this service... The command completed successfully
3.執行dbms_service.create_service過程來建立資料庫服務名
SQL>dbms_service.create_service(service_name => 'jycs_service_3',network_name => 'jycs_service_3');
執行dbms_service.create_service過程來建立資料庫服務名jycs_service_3後，在v$services檢視中是沒有該服務的行記錄，但在dba_services檢視中有該服務的行記錄,需要調整資料庫引數service_names，並重啟。
SQL> select * from v$services;
SERVICE_ID NAME NAME_HASH NETWORK_NAME CREATION_DATE CREATION_DATE_HASH GOAL DTP AQ_HA_NOTIFICATION CLB_GOAL COMMIT_OUTCOME RETENTION_TIME REPLAY_INITIATION_TIMEOUT SESSION_STATE_CONSISTENCY GLOBAL PDB SQL_TRANSLATION_PROFILE MAX_LAG_TIME CON_ID
---------- -------------------------- ---------- --------------- ------------- ------------------ ------------ --- ------------------ -------- -------------- -------------- ------------------------- ------------------------------ ------ ------------ ------------------------ -------------- ----------
0 jycsp10 2786476993 jycsp10 0 NONE N NO SHORT NO 86400 300 NO JYCSP10 12
0 jycsp9 3094752551 jycsp9 0 NONE N NO SHORT NO 86400 300 NO JYCSP9 11
0 jycsp8 2804702749 jycsp8 0 NONE N NO SHORT NO 86400 300 NO JYCSP8 10
0 jycsp7 651053443 jycsp7 0 NONE N NO SHORT NO 86400 300 NO JYCSP7 9
0 jycsp6 2502944067 jycsp6 0 NONE N NO SHORT NO 86400 300 NO JYCSP6 8
0 jycsp5 1822500990 jycsp5 0 NONE N NO SHORT NO 86400 300 NO JYCSP5 7
0 jycsp4 1917126355 jycsp4 0 NONE N NO SHORT NO 86400 300 NO JYCSP4 6
0 jycsp3 2193443928 jycsp3 0 NONE N NO SHORT NO 86400 300 NO JYCSP3 5
0 jycsp2 3609153374 jycsp2 0 NONE N NO SHORT NO 86400 300 NO JYCSP2 4
0 jycsp1 1271175711 jycsp1 0 NONE N NO SHORT NO 86400 300 NO JYCSP1 3
3 jycs_service 1423956612 jycs_service 2016/5/5 11:5 668160606 NONE N NO LONG NO 86400 300 DYNAMIC NO CDB$ROOT ANY 1
7 jycs_service_2 2320947470 jycs_service_2 2016/5/5 15:0 4274618966 NONE N NO LONG NO 86400 300 NO CDB$ROOT 1
5 jycsXDB 1180545090 jycsXDB 2016/3/31 20: 3827618340 NONE N NO LONG NO 86400 300 NO CDB$ROOT 1
6 jycs 1105513663 jycs 2016/3/31 20: 3827618340 NONE N NO LONG NO 86400 300 NO CDB$ROOT 1
1 SYS$BACKGROUND 165959219 2014/7/7 5:39 977152970 NONE N NO SHORT NO 86400 300 NO CDB$ROOT 1
2 SYS$USERS 3427055676 2014/7/7 5:39 977152970 NONE N NO SHORT NO 86400 300 NO CDB$ROOT 1
16 rows selected
SQL> select * from dba_services;
SERVICE_ID NAME NAME_HASH NETWORK_NAME CREATION_DATE CREATION_DATE_HASH FAILOVER_METHOD FAILOVER_TYPE FAILOVER_RETRIES FAILOVER_DELAY MIN_CARDINALITY MAX_CARDINALITY GOAL DTP ENABLED AQ_HA_NOTIFICATIONS CLB_GOAL EDITION COMMIT_OUTCOME RETENTION_TIMEOUT REPLAY_INITIATION_TIMEOUT SESSION_STATE_CONSISTENCY GLOBAL_SERVICE PDB SQL_TRANSLATION_PROFILE MAX_LAG_TIME GSM_FLAGS
---------- -------------------------- ---------- --------------- ------------- ------------------ ------------------ ---------------- ---------------- -------------- --------------- --------------- ------------ --- ------- ------------------- -------- ----------- -------------- ----------------- ------------------------- ----------------------------- -------------- ----------- ------------------------- -------------- ----------
1 SYS$BACKGROUND 165959219 2014/7/7 5:39 977152970 N NO NO LONG NO NO CDB$ROOT
2 SYS$USERS 3427055676 2014/7/7 5:39 977152970 N NO NO LONG NO NO CDB$ROOT
3 jycs_service 1423956612 jycs_service 2016/5/5 11:5 668160606 0 0 NONE N NO NO LONG NO 86400 300 DYNAMIC NO CDB$ROOT ANY 0
4 jycs_service1 3627910471 jycs_service1 2016/5/5 14:5 153848850 N NO NO LONG NO NO CDB$ROOT
5 jycsXDB 1180545090 jycsXDB 2016/3/31 20: 3827618340 N NO NO LONG NO NO CDB$ROOT
6 jycs 1105513663 jycs 2016/3/31 20: 3827618340 N NO NO LONG NO NO CDB$ROOT
7 jycs_service_2 2320947470 jycs_service_2 2016/5/5 15:0 4274618966 N NO NO LONG NO NO CDB$ROOT
8 jycs_service_3 1197864246 jycs_service_3 2016/5/5 15:0 2019662977 NONE N NO NO LONG NO 86400 300 DYNAMIC NO CDB$ROOT ANY 0
8 rows selected
[grid@oracle12c ~]$ lsnrctl status LSNRCTL for Linux: Version 12.1.0.2.0 - Production on 05-MAY-2016 15:18:05 Copyright (c) 1991, 2014, Oracle. All rights reserved. Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=oracle12c)(PORT=1521))) STATUS of the LISTENER ------------------------ Alias LISTENER Version TNSLSNR for Linux: Version 12.1.0.2.0 - Production Start Date 05-MAY-2016 14:57:53 Uptime 0 days 0 hr. 20 min. 12 sec Trace Level off Security ON: Local OS Authentication SNMP OFF Listener Parameter File /u01/app/oracle/product/12.1/grid/network/admin/listener.ora Listener Log File /u01/app/grid/diag/tnslsnr/oracle12c/listener/alert/log.xml Listening Endpoints Summary... (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=oracle12c)(PORT=1521))) (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521))) (DESCRIPTION=(ADDRESS=(PROTOCOL=tcps)(HOST=oracle12c)(PORT=5500))(Security=(my_wallet_directory=/u01/app/oracle/admin/jycs/xdb_wallet))(Presentation=HTTP)(Session=RAW)) Services Summary... Service "jycs" has 1 instance(s). Instance "jycs", status READY, has 1 handler(s) for this service... Service "jycsXDB" has 1 instance(s). Instance "jycs", status READY, has 1 handler(s) for this service... Service "jycs_service" has 1 instance(s). Instance "jycs", status READY, has 1 handler(s) for this service... Service "jycs_service_2" has 1 instance(s). Instance "jycs", status READY, has 1 handler(s) for this service... Service "jycsp1" has 1 instance(s). Instance "jycs", status READY, has 1 handler(s) for this service... Service "jycsp10" has 1 instance(s). Instance "jycs", status READY, has 1 handler(s) for this service... Service "jycsp2" has 1 instance(s). Instance "jycs", status READY, has 1 handler(s) for this service... Service "jycsp3" has 1 instance(s). Instance "jycs", status READY, has 1 handler(s) for this service... Service "jycsp4" has 1 instance(s). Instance "jycs", status READY, has 1 handler(s) for this service... Service "jycsp5" has 1 instance(s). Instance "jycs", status READY, has 1 handler(s) for this service... Service "jycsp6" has 1 instance(s). Instance "jycs", status READY, has 1 handler(s) for this service... Service "jycsp7" has 1 instance(s). Instance "jycs", status READY, has 1 handler(s) for this service... Service "jycsp8" has 1 instance(s). Instance "jycs", status READY, has 1 handler(s) for this service... Service "jycsp9" has 1 instance(s). Instance "jycs", status READY, has 1 handler(s) for this service... The command completed successfully
設定引數service_names並重啟資料庫
SQL> alter system set service_names='jycs,jycs_service_2,jycs_service_3' scope=both; System altered. SQL> shutdown immediate Database closed. Database dismounted. ORACLE instance shut down. SQL> startup ORACLE instance started. Total System Global Area 1073741824 bytes Fixed Size 2932632 bytes Variable Size 377487464 bytes Database Buffers 687865856 bytes Redo Buffers 5455872 bytes Database mounted. Database opened.
SQL> select * from v$services;
SERVICE_ID NAME NAME_HASH NETWORK_NAME CREATION_DATE CREATION_DATE_HASH GOAL DTP AQ_HA_NOTIFICATION CLB_GOAL COMMIT_OUTCOME RETENTION_TIME REPLAY_INITIATION_TIMEOUT SESSION_STATE_CONSISTENCY GLOBAL PDB SQL_TRANSLATION_PROFILE MAX_LAG_TIME CON_ID
---------- ------------------- ---------- --------------- ------------- ------------------ ------------ --- ------------------ -------- -------------- -------------- ------------------------- ------------------------------ ------ ------------- ------------------------- ------------- ----------
3 jycs_service 1423956612 jycs_service 2016/5/5 11:5 668160606 NONE N NO LONG NO 86400 300 DYNAMIC NO CDB$ROOT ANY 1
0 jycsp10 2786476993 jycsp10 0 NONE N NO SHORT NO 86400 300 NO JYCSP10 12
0 jycsp9 3094752551 jycsp9 0 NONE N NO SHORT NO 86400 300 NO JYCSP9 11
0 jycsp8 2804702749 jycsp8 0 NONE N NO SHORT NO 86400 300 NO JYCSP8 10
0 jycsp7 651053443 jycsp7 0 NONE N NO SHORT NO 86400 300 NO JYCSP7 9
0 jycsp6 2502944067 jycsp6 0 NONE N NO SHORT NO 86400 300 NO JYCSP6 8
0 jycsp5 1822500990 jycsp5 0 NONE N NO SHORT NO 86400 300 NO JYCSP5 7
0 jycsp4 1917126355 jycsp4 0 NONE N NO SHORT NO 86400 300 NO JYCSP4 6
0 jycsp3 2193443928 jycsp3 0 NONE N NO SHORT NO 86400 300 NO JYCSP3 5
0 jycsp2 3609153374 jycsp2 0 NONE N NO SHORT NO 86400 300 NO JYCSP2 4
0 jycsp1 1271175711 jycsp1 0 NONE N NO SHORT NO 86400 300 NO JYCSP1 3
8 jycs_service_3 1197864246 jycs_service_3 2016/5/5 15:0 2019662977 NONE N NO LONG NO 86400 300 NO CDB$ROOT ANY 1
7 jycs_service_2 2320947470 jycs_service_2 2016/5/5 15:0 4274618966 NONE N NO LONG NO 86400 300 NO CDB$ROOT 1
5 jycsXDB 1180545090 jycsXDB 2016/3/31 20: 3827618340 NONE N NO LONG NO 86400 300 NO CDB$ROOT 1
6 jycs 1105513663 jycs 2016/3/31 20: 3827618340 NONE N NO LONG NO 86400 300 NO CDB$ROOT 1
1 SYS$BACKGROUND 165959219 2014/7/7 5:39 977152970 NONE N NO SHORT NO 86400 300 NO CDB$ROOT 1
2 SYS$USERS 3427055676 2014/7/7 5:39 977152970 NONE N NO SHORT NO 86400 300 NO CDB$ROOT 1
檢視監聽是否註冊了jycs_service_3，可以看到在設定service_names引數並重啟資料庫後監聽註冊了該服務
[grid@oracle12c ~]$ lsnrctl status LSNRCTL for Linux: Version 12.1.0.2.0 - Production on 05-MAY-2016 15:21:10 Copyright (c) 1991, 2014, Oracle. All rights reserved. Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=oracle12c)(PORT=1521))) STATUS of the LISTENER ------------------------ Alias LISTENER Version TNSLSNR for Linux: Version 12.1.0.2.0 - Production Start Date 05-MAY-2016 14:57:53 Uptime 0 days 0 hr. 23 min. 17 sec Trace Level off Security ON: Local OS Authentication SNMP OFF Listener Parameter File /u01/app/oracle/product/12.1/grid/network/admin/listener.ora Listener Log File /u01/app/grid/diag/tnslsnr/oracle12c/listener/alert/log.xml Listening Endpoints Summary... (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=oracle12c)(PORT=1521))) (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521))) (DESCRIPTION=(ADDRESS=(PROTOCOL=tcps)(HOST=oracle12c)(PORT=5500))(Security=(my_wallet_directory=/u01/app/oracle/admin/jycs/xdb_wallet))(Presentation=HTTP)(Session=RAW)) Services Summary... Service "jycs" has 1 instance(s). Instance "jycs", status READY, has 1 handler(s) for this service... Service "jycsXDB" has 1 instance(s). Instance "jycs", status READY, has 1 handler(s) for this service... Service "jycs_service" has 1 instance(s). Instance "jycs", status READY, has 1 handler(s) for this service... Service "jycs_service_2" has 1 instance(s). Instance "jycs", status READY, has 1 handler(s) for this service... Service "jycs_service_3" has 1 instance(s). Instance "jycs", status READY, has 1 handler(s) for this service... Service "jycsp1" has 1 instance(s). Instance "jycs", status READY, has 1 handler(s) for this service... Service "jycsp10" has 1 instance(s). Instance "jycs", status READY, has 1 handler(s) for this service... Service "jycsp2" has 1 instance(s). Instance "jycs", status READY, has 1 handler(s) for this service... Service "jycsp3" has 1 instance(s). Instance "jycs", status READY, has 1 handler(s) for this service... Service "jycsp4" has 1 instance(s). Instance "jycs", status READY, has 1 handler(s) for this service... Service "jycsp5" has 1 instance(s). Instance "jycs", status READY, has 1 handler(s) for this service... Service "jycsp6" has 1 instance(s). Instance "jycs", status READY, has 1 handler(s) for this service... Service "jycsp7" has 1 instance(s). Instance "jycs", status READY, has 1 handler(s) for this service... Service "jycsp8" has 1 instance(s). Instance "jycs", status READY, has 1 handler(s) for this service... Service "jycsp9" has 1 instance(s). Instance "jycs", status READY, has 1 handler(s) for this service... The command completed successfully
來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/29734436/viewspace-2104256/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- Oracle Database Cloud - Database as a Service Quick StartOracleDatabaseCloudUI
- database service registationDatabase
- Guide to Database as a Service (DBaaS)GUIIDEDatabase
- Fatal Error: TXK Install Service，Cannot install Oracle Database HomeErrorOracleDatabase
- RMAN 6217 not connected to auxiliary database with a net service nameUXDatabase
- In Oracle,What Is a Service?Oracle
- How to create and relocate an 11gr2 RAC DATABASE SERVICEDatabase
- Types of Oracle Database Users : Database Users (6)OracleDatabase
- Oracle clone databaseOracleDatabase
- ORACLE database vaultOracleDatabase
- Oracle Database HistoryOracleDatabase
- Oracle Database ReplayOracleDatabase
- alter database in OracleDatabaseOracle
- Oracle Database ScriptOracleDatabase
- 雲關係型資料庫（Relational Database Service，RDS）資料庫Database
- Oracle Physical Database LimitsOracleDatabaseMIT
- Oracle sharding databaseOracleDatabase
- Oracle Database Resource ManagerOracleDatabase
- Oracle Database Scheduler整理OracleDatabase
- oracle full database backupOracleDatabase
- Oracle Active database duplicationOracleDatabase
- Oracle Database Memory StructuresOracleDatabaseStruct
- Oracle database buffer cacheOracleDatabase
- Oracle Database In-MemoryOracleDatabase
- oracle rat database replayOracleDatabase
- Oracle database instanceOracleDatabase
- Oracle Database Internals FAQOracleDatabase
- HP ORACLE DATABASE MACHINEOracleDatabaseMac
- oracle database linkOracleDatabase
- oracle 重建EM databaseOracleDatabase
- float datatype in Oracle databaseOracleDatabase
- 在 Google Kubernetes Cluster 上使用 HANA Expression Database ServiceGoExpressDatabase
- ENTERPRISE MANAGER 12C DATABASE AS A SERVICE (DBaaS) SNAP CLONEDatabase
- Types of Oracle Database Users : Database Administrators (1)OracleDatabase
- 【Oracle】service_name和service_names的關係Oracle
- Oracle Database Cardinality FeedbackOracleDatabase
- Secrets of the Oracle Database筆記OracleDatabase筆記
- 初識 "Oracle Database Vault"OracleDatabase