I need to create service if we made switchover database prosedure. Many application admin is want to use single SID or they can not use tns entery which has primary&standby server information.

So, We create a new service using the CREATE_SERVICE procedure. The only mandatory parameters are the the SERVICE_NAME and the NETWORK_NAME, which represent the internal name of the service in the data dictionary and the name of the service presented by the listener respectively.

Before create service let us check our current services. You can use below queries

SELECT name,
network_name
FROM dba_services
ORDER BY 1;

SELECT name,
network_name
FROM v$active_services
ORDER BY 1;

If your service is not exist or not active, then you can create services by using below syntax

BEGIN
DBMS_SERVICE.create_service(
service_name => ‘my_new_service’,
network_name => ‘my_new_service’
);
END;
/

Once its run, you can start your service and you can check its active or not by using queries which i already shared.

BEGIN
DBMS_SERVICE.start_service(
service_name => ‘my_new_service’
);
END;
/

If you need to stop service you simply need to run below command

BEGIN
DBMS_SERVICE.stop_service(
service_name => ‘my_new_service’
);
END;
/