Embedded Files
Check
Check
To be used a Service exists at a database level and, to accept connections from outside the database server, is registered to a Listener.
You can see all configured Services at the database level using...
SET LINESIZE 200
SET PAGESIZE 40
COLUMN network_name FORMAT a40
SELECT name, network_name, creation_date FROM dba_services;
You can see running Services at the database level using...
SET LINESIZE 200
SET PAGESIZE 40
COLUMN network_name FORMAT a40
SELECT name, network_name, creation_date FROM v$active_services;
You can see running Services at the listener level using...
lsnrctl stat
or, for more detail
lsnrctl services
Further Information
Further Information
Where you see a Service Instance with a status of UNKNOWN in the output from lsrnctl, this generally means that the Service exists because it was defined in the LISTENER.ORA file (a 'Static Service') rather than being registered from the database instance (a 'Dynamic Service').
For example...
Service "ORCL1" has 2 instance(s).
Instance "ORCL1", status UNKNOWN, has 1 handler(s) for this service...
Instance "ORCL1", status READY, has 1 handler(s) for this service...
The first Instance, with the status of UNKNOWN is there because of this SID_DESC stanza in the LISTENER.ORA...
(SID_DESC =
(SID_NAME = ORCL1)
(ORACLE_HOME = /u01/app/oracle/product/122010ORCL1)
)
The second Instance, with the status of READY is there because of the parameter SERVICE_NAMES which is set as...
service_names string ORCL1
If DB_DOMAIN was not null then the Service Name showing in lsnrctl would include the domainThe lsnrctl stat command may show some other Services...
There may be a Service to support the Oracle Messaging Gateway (e.g. mgwextproc)
There may be Services to support the Data Guard Broker (e.g. ORCL1_DGB and ORCL1_DGMGRL)
There may be some manually created Services
User-Defined Services
User-Defined Services
Create Service
Create Service
exec DBMS_SERVICE.CREATE_SERVICE(service_name => 'SERVICE', network_name => 'SERVICE' );
Start Service
Start Service
exec DBMS_SERVICE.START_SERVICE('SERVICE');
Stop Service
Stop Service
exec DBMS_SERVICE.STOP_SERVICE('SERVICE');
Delete Service
Delete Service
exec DBMS_SERVICE.DELETE_SERVICE(service_name => 'SERVICE');
Autostart Service
Autostart Service
If you create and start a Service using DBMS_SERVICE you will need to manually start the Service after every instance restart. There are several ways to get around this...
SERVICE_NAMES
SERVICE_NAMES
If you add a service name to the SERVICE_NAMES parameter then all Services listed will be started and registered at database startup.
ALTER SYSTEM SET service_names = 'ORCL1, ORCL2, ORCL3...' SCOPE=both;
AFTER STARTUP ON DATABASE Trigger
AFTER STARTUP ON DATABASE Trigger
This method is useful if you only want to start the Service under certain conditions (e.g. only if the database is a Physical Standby)...
CREATE OR REPLACE TRIGGER mySERVICE_service_tr
AFTER STARTUP ON DATABASE
DECLARE
v_role VARCHAR2(30);
BEGIN
SELECT database_role INTO v_role FROM V$DATABASE;
IF v_role = 'PRIMARY' THEN
DBMS_SERVICE.START_SERVICE('mySERVICE');
ELSE
DBMS_SERVICE.STOP_SERVICE('mySERVICE');
END IF;
END;
/
NOTE that this Trigger only runs on database startup, so actions that don't cause the database to restart will not cause the Trigger to fire. This could be a problem during a switchover/failover. Use a DB_ROLE_CHANGE Trigger instead. See below...AFTER DB_ROLE_CHANGE ON DATABASE Trigger
AFTER DB_ROLE_CHANGE ON DATABASE Trigger
This method is useful if you only want the Service to run under certain conditions (e.g. only if the database is the Primary)...
CREATE OR REPLACE TRIGGER mySERVICE_service_tr
AFTER DB_ROLE_CHANGE ON DATABASE
DECLARE
v_role VARCHAR2(30);
BEGIN
SELECT DATABASE_ROLE INTO v_role FROM V$DATABASE;
IF v_role = 'PRIMARY' THEN
DBMS_SERVICE.START_SERVICE('mySERVICE');
ELSE
DBMS_SERVICE.STOP_SERVICE('mySERVICE');
END IF;
END;
/
Use a Pluggable Database
Use a Pluggable Database
You can save the state of a pluggable database which will include the state of all Services...
ALTER PLUGGABLE DATABASE ALL SAVE STATE;
Data Guard Broker / SRVCTL
Data Guard Broker / SRVCTL
This achieves the same goal as the trigger method:
The service will only run on the Primary database
It will automatically move during switchover/failover
No manual intervention or custom logic is required
The key difference is that this approach uses Oracle’s built-in HA tooling rather than custom triggers, making it:
Simpler to manage
More consistent with the platform
In line with Oracle best practice
srvctl add service -db myDB -service mySERVICE -role PRIMARY
srvctl start service -db myDB -service mySERVICE
Disconnect Sessions
Disconnect Sessions
exec DBMS_SERVICE.disconnect_session(service_name => 'SERVICE', disconnect_option => DICONNECT_OPTION);
DISCONNECT_OPTION:DBMS.SERVICE.POST_TRANSACTION : Sessions disconnect once their current transaction ends with a commit or rollback. This is the default value (0).DBMS.SERVICE.IMMEDIATE : Sessions disconnect immediately. Value (1).DBMS.SERVICE.NOREPLAY : Sessions disconnect immediately, and are flagged not to be replayed by application continuity. Value (2).
Bibliography
Bibliography
https://docs.oracle.com/database/121/REFRN/GUID-0091C6CB-6852-4253-8FE7-7F0FF053C652.htm#REFRN20465 (12.1 ALL_SERVICES view)https://docs.oracle.com/en/database/oracle/oracle-database/12.2/arpls/DBMS_SERVICE.html https://oracle-base.com/articles/10g/dbms_service https://www.morganslibrary.org/hci/hci012.html http://psoug.org/reference/dbms_service.html http://www.bigdatalyn.com/2018/01/10/Oracle_CreatePDBService/ https://minimalistic-oracle.blogspot.com/2016/02/how-to-create-and-start-service-with.html https://www.aemcorp.com/managedservices/blog/part-2-pdb-save-state-tips-and-tricks-for-your-12.2-oracle-database-upgrade
Page updated
Google Sites
Report abuse