A service has a stored definition, an active state, and a lifecycle owner. Choosing the correct owner lets you create the service, make it available, verify the application's connection, and remove the disposable exercise cleanly.
Oracle DBA Lesson 38B — Create and Verify an Application Service
The example uses an application service named app_course_v36 in LABPDB. It is separate from the PDB's default service.
Choose the management owner
| Environment | Management method |
|---|---|
| Single-instance database outside Oracle Restart, Clusterware, and Global Data Services management | DBMS_SERVICE in the intended PDB |
| Oracle Restart or Oracle Clusterware | The owning framework's SRVCTL utility |
| Global Data Services | Its supported service-management tooling |
Keep creation, activation, restart behavior, and deletion with that owner. The PL/SQL lifecycle procedures have explicit framework restrictions. The example below applies to the unmanaged row. See DBMS_SERVICE operating procedures.
For a Restart or Clusterware lab, have its authorized owner inspect the service configuration and use release-appropriate SRVCTL commands. Specifying -pdb LABPDB during service creation establishes the intended PDB association. Confirm the correct Oracle home, database unique name, and permissions before applying that separate workflow. See PDB service management.
Lab conditions
Use an authorized, disposable Oracle Database 19c single-instance CDB/PDB on Linux, with SQL*Plus and LABPDB already open READ WRITE. The PDB administrator needs EXECUTE on SYS.DBMS_SERVICE, ALTER SYSTEM, read access to SYS.V_$SESSION, and delegated access to SYS.V_$SERVICES and SYS.V_$ACTIVE_SERVICES. Oracle documents package execution through the DBA role and the additional security requirements. The instructor must confirm the applicable grants in this PDB. The ordinary COURSE_READER account uses its own session context for the client test. See DBMS_SERVICE security model.
Have the instructor reserve the disposable service and network name across the CDB and the listener's service scope. A PDB-local query supplies only the visibility granted to that observer. If a matching service already exists, stop this exercise and obtain a fresh name. Never reuse or delete another team's service. The default PDB service remains in place.
The instructor supplies a working listener endpoint, an existing reader login, and authorized listener inspection. These examples create a service definition and change its availability. They perform no table updates and require no separately licensed management pack. A restart test is a separate planned outage in the disposable lab, with application coordination and restoration instructions.
Create the definition, then activate it
From the authorized administrator's connection to the intended PDB:
SHOW CON_NAME
SELECT name, network_name, pdb
FROM v$services
WHERE name = 'app_course_v36';SHOW CON_NAME is a SQL*Plus command that identifies the current container. Require LABPDB before proceeding. It does not report the open mode. Have the instructor confirm the existing READ WRITE state. Require no matching row, and confirmation of the reserved name, before creation.
BEGIN
DBMS_SERVICE.CREATE_SERVICE(
service_name => 'app_course_v36',
network_name => 'app_course_v36');
END;
/
EXEC DBMS_SERVICE.START_SERVICE(service_name => 'app_course_v36');CREATE_SERVICE stores the definition. service_name is its management identity. network_name is the name used in the client's requested service. START_SERVICE activates it on this single instance. The PL/SQL block is submitted with /. EXEC is SQL*Plus's shorthand for executing a PL/SQL statement. See SQL*Plus EXECUTE and Package procedure reference.
Creating through DBMS_SERVICE associates the service with the current container. Creating in the root would associate it with the root. Verify the PDB column after creation. The package cannot change this association in place. A wrong association requires an owner-approved correction rather than proceeding with the client exercise. See Current-container association.
Collect four different observations
| Observation | What to record |
|---|---|
| Stored definition | Name, network name, and intended PDB association |
| Database activation | Matching active-service row on this instance |
| Listener advertisement | Requested service, expected instance, and usable handler |
| Fresh application connection | This session's actual service and container |
1. Definition
SELECT name, network_name, pdb
FROM v$services
WHERE name = 'app_course_v36';In this design, the intended values are app_course_v36, app_course_v36, and LABPDB. V$SERVICES.PDB identifies the associated PDB. Interpret the result in the observer's container and visibility. See V$SERVICES.
2. Activation
SELECT name, network_name
FROM v$active_services
WHERE name = 'app_course_v36';A matching row reports the active service. Keep this separate from the definition query. A stored definition and current activation answer different questions. See V$ACTIVE_SERVICES.
3. Advertisement
Allow normal dynamic registration. From the instructor-designated host account, inspect the correct listener:
lsnrctl services LISTENERUse the actual listener name if it differs. Find app_course_v36, the expected instance, and its handler information. The command reports registered services and handlers. READY describes an instance that can accept connections. Record the actual handler state. If advertisement is missing, request diagnostics from the authorized listener or database owner. This exercise does not alter listener configuration or root SERVICE_NAMES. Customer use of SERVICE_NAMES is deprecated in 19c. See Listener services inspection and SERVICE_NAMES.
4. Application session
Open a fresh connection using the actual disposable lab endpoint:
sqlplus COURSE_READER@//dbhost:1521/app_course_v36SQL*Plus prompts for the account's password. In that new connection:
SELECT SYS_CONTEXT('USERENV','SERVICE_NAME') AS current_service,
SYS_CONTEXT('USERENV','CON_NAME') AS current_container
FROM dual;The intended pair is app_course_v36 and LABPDB. SERVICE_NAME identifies this session's service. CON_NAME identifies its current container. Compare both observed values with the design before accepting the connection. An unexpected pair calls for checking the connection target and service association. See SYS_CONTEXT.
Plan and test startup behavior
Record the intended startup and shutdown owner. For an unmanaged PDB opening, the documented SERVICES clause defaults to NONE, which starts its default service. Additional services can be selected through an explicit service list, ALL or ALL EXCEPT, or an approved service-start procedure. The instructor must define which startup action this lab will use. See PDB open-state services clause.
For Oracle Restart, inspect the configured service policy. AUTOMATIC supports automatic startup on database restart, subject to the relevant role and configuration. MANUAL has different planned-restart behavior. Use the owner and the documented policy for the environment. See Oracle Restart service policies.
In a separately authorized disposable restart test, record the startup action and repeat all four observations after the PDB becomes available. Keep definition survival, activation, advertisement, and a fresh connection as separate results. An observed successful startup is the acceptance evidence. These notes make no claim that the exercise has passed that test.
Clean up the disposable service
Coordinate the application's work first. Complete or roll back its transactions as agreed, and close only the exercise client connections. For a read-only SQL*Plus exercise, EXIT ROLLBACK explicitly discards any accidental uncommitted changes before disconnecting. See SQL*Plus EXIT.
From the same authorized administrator's PDB connection, after those clients disconnect:
EXEC DBMS_SERVICE.STOP_SERVICE(service_name => 'app_course_v36');
EXEC DBMS_SERVICE.DELETE_SERVICE(service_name => 'app_course_v36');
SELECT name FROM v$services
WHERE name = 'app_course_v36';
SELECT name FROM v$active_services
WHERE name = 'app_course_v36';Require no matching row from both checks, with the same observer visibility. Stopping deactivates the service. Deletion removes the definition. A stop operation by itself does not establish that existing sessions and transactions have drained. Oracle exposes separate stop and disconnect options, so coordinate clients rather than assuming automatic disconnection. Protect the default PDB service and every service outside this exercise. See STOP_SERVICE and DELETE_SERVICE.
Practice and success criterion
- Identify the lab's lifecycle owner, reserved service name, administrator permissions, and existing PDB open mode.
- Check the current container and name availability. Create and start only the disposable application service.
- Capture the four observations and interpret what each establishes.
- Write down the intended startup owner and action. Perform a separately authorized disposable restart test only when its outage and restoration plan are ready. Otherwise leave this result pending.
- Disconnect the exercise clients, stop/delete the service, and verify both rows are gone.
Success means you can explain the owner choice, interpret the service and container pair from a fresh login, and show cleanup of exactly the service you created. Restart acceptance requires observed evidence from the planned test.
Quiz
1. May DBMS_SERVICE independently manage a service owned by Oracle Restart?
2. What determines the PDB association when DBMS_SERVICE.CREATE_SERVICE creates a service?
3. Which observation reports current activation on this instance?
4. Which pair best confirms the destination of the new reader session?
5. What is the appropriate cleanup sequence for this exercise?
The names and expected values in this lesson are documentation-based teaching examples. No database commands were executed to produce these notes. Replace the endpoint with the designated lab endpoint and capture your own observations.
No comments:
Post a Comment