Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It only takes a minute to sign up.
Sign up to join this community
I'm researching the connection methods for an Oracle 12c database and I understand the difference between an SID and Service Name, but I cannot figure out where the service name is defined or how to change it.
Presumably it was named the same as the SID I typed in during installation, but can it be changed post-installation? Is there a configuration file where that is done?
Thanks in advance!
Understanding Database Services
The service name defaults to the global database name, a name comprising the database name (DB_NAME initialization parameter) and domain name (DB_DOMAIN initialization parameter).
You can find service information in ALL_SERVICES.
You can manage services with DBMS_SERVICE.
In an Oracle Restart or clustered environment, you can manage services with srvctl, which uses DBMS_SERVICE in the background.
No, you should not use the service_names parameter. That should be left to maintained by the above.
The default services should not be used by clients, and you should always create your own service(s) with the required properties.
Your Oracle RAC database includes an Oracle database service identified by DB_UNIQUE_NAME, if set, or DB_NAME or PDB_NAME, if not. This default service is always available on all instances in an Oracle RAC environment, unless an instance is in restricted mode. You cannot alter this service or its properties. Additionally, the database supports the following two internal services:
SYS$BACKGROUND is used by the background processes only
SYS$USERS is the default service for user sessions that are not associated with any application service
All of these services are used for internal management. You cannot stop or disable any of these internal services to do planned outages or to failover to Oracle Data Guard. Do not use these services for client connections.
The above is about RAC from the RAC documentation, but it is true for single instance environments as well.
service_names can do that. You will be able to analyze performance separately for clients using different services, and you can use it with Resource Manager to prioritize workload based on services. But if you want to create a role based service in a Data Guard environment, or use database editions for zero downtime application patching, or use the high availability features of a service, then service_names parameter alone is not enough.
Jan 31, 2019 at 21:32