ORA-44786
There're several error patterns about ORA-44786 when we operate on a service in a pluggable database (PDB).
A. Stop Service of a PDB
Tried to stop a service in a PDB, and got an error stack like this.
SQL> exec dbms_service.stop_service('ERPAPP2', DBMS_SERVICE.ALL_INSTANCES);
BEGIN dbms_service.stop_service('ERPAPP2', DBMS_SERVICE.ALL_INSTANCES); END;
*
ERROR at line 1:
ORA-44786: Service operation cannot be completed.
ORA-06512: at "SYS.DBMS_SERVICE_ERR", line 91
ORA-06512: at "SYS.DBMS_SERVICE", line 519
ORA-06512: at line 1
The root causes may be:
- You're in the wrong container, or
- You used a wrong service name.
1. Incorrect Container
Being in the wrong container can be corrected like this:
SQL> alter session set container=CRM;
Session altered.
SQL> show con_name
CON_NAME
------------------------------
CRM
Or using the correct TNS name to reconnect to the database.
2. Incorrect Service Name
Using a wrong service name can be easily fixed by using the correct one in the statement.
SQL> exec dbms_service.stop_service('CRMAPP2', DBMS_SERVICE.ALL_INSTANCES);
PL/SQL procedure successfully completed.
B. Start Service of a PDB
A cloned PDB may not start the cloned service name successfully. Eventually, it failed with ORA-44786.
SQL> exec dbms_service.start_service('ERPAPP2', DBMS_SERVICE.ALL_INSTANCES);
BEGIN dbms_service.start_service('ERPAPP2', DBMS_SERVICE.ALL_INSTANCES); END;
*
ERROR at line 1:
ORA-44786: Service operation cannot be completed.
ORA-06512: at "SYS.DBMS_SERVICE", line 76
ORA-06512: at "SYS.DBMS_SERVICE", line 483
ORA-06512: at line 1
ORA-44786 means that starting a relocated service name is stopped and affected by some factors of previous service in the way to complete.
We take the following steps to solve ORA-44786.
1. Stop Service in Source PDB
If the source PDB is in the same database server, please make sure the service has been stopped in the source PDB. Furthermore, you may consider to delete the service name from the PDB if it's no longer used.
Restarting the source PDB may be a safer way to the following steps.
2. Recreate Service in Clone PDB
We deleted then created the same service name in the cloned PDB.
SQL> exec dbms_service.delete_service('ERPAPP2');
PL/SQL procedure successfully completed.
SQL> begin
2 dbms_service.create_service(
3 service_name => 'ERPAPP2',
4 network_name => 'ERPAPP2',
5 failover_method => DBMS_SERVICE.FAILOVER_METHOD_BASIC,
6 failover_type => DBMS_SERVICE.FAILOVER_TYPE_SELECT,
7 failover_retries => 180,
8 failover_delay => 5
9 );
10 end;
11 /
PL/SQL procedure successfully completed.
As you can see, we create a TAF service name for the RAC database as the same name as used to be. Of course, you can also add a normal service to the PDB.
3. Start Service in Clone PDB
We can start the service in the cloned PDB on all nodes.
SQL> exec dbms_service.start_service('ERPAPP2', DBMS_SERVICE.ALL_INSTANCES);
PL/SQL procedure successfully completed.
We solved it.