File ADZDPREP.sql In Online Patching with ORA-44303: service name exists


Error:

While running the patch 13543062, Following error encountered,

begin
*
ERROR at line 1:
ORA-44303: service name exists
ORA-06512: at "SYS.DBMS_SERVICE_ERR", line 21
ORA-06512: at "SYS.DBMS_SERVICE", line 218
ORA-06512: at "APPS.AD_ZD_PREP", line 346
ORA-06512: at line 4
 

SQL> begin
  ad_zd.log('ad.sql.adzdprep', 'STATEMENT', 'Create patch service: begin');
  dbms_application_info.set_module('AD_ZD_PREP', 'Create patch service');
  ad_zd_prep.create_patch_service;
end;
/
 

begin
*
ERROR at line 1:
ORA-44303: service name exists
ORA-06512: at "SYS.DBMS_SERVICE_ERR", line 21
ORA-06512: at "SYS.DBMS_SERVICE", line 218
ORA-06512: at "APPS.AD_ZD_PREP", line 346
ORA-06512: at line 4
 

Reason:

Found to have ebs_patch already in all_services

SQL> select SERVICE_ID,NAME from all_services;

SERVICE_ID NAME
---------- ------------------------------------------------------------
  1 SYS$BACKGROUND
  2 SYS$USERS
  5 ebs_patch

Online Enable patch will try to create the ebs_patch service in the all_services table. If the service is already present in all_services table, then the patch worker will fail while executing ADZDPREP.sql script with below error.

sqlplus -s APPS/***** @<BASE_DIRECTORY>/fs1/EBSapps/appl/ad/12.0.0/patch/115/sql/adsqlwrapper.sql '<BASE_DIRECTORY>/fs1/EBSapps/appl/ad/12.0.0/patch/115/sql/ADZDPREP.sql &un_apps &pw_apps &systempwd'
Connected.

PL/SQL procedure successfully completed.

Connected.

Session altered.

PL/SQL procedure successfully completed.


Commit complete.

begin
*
ERROR at line 1:
ORA-44303: service name exists
ORA-06512: at "SYS.DBMS_SERVICE_ERR", line 21
ORA-06512: at "SYS.DBMS_SERVICE", line 218
ORA-06512: at "APPS.AD_ZD_PREP", line 346
ORA-06512: at line 4
 

SOLUTION

1) Check the output of Below query

select SERVICE_ID,NAME from dba_services;

select c.name from cdb_services c, service$ s where s.deletion_date is null and s.name = c.name;

2) If ebs_patch exists then Please drop the service.

begin

  DBMS_SERVICE.STOP_SERVICE(service_name =>'ebs_patch');
  DBMS_SERVICE.DELETE_SERVICE(service_name =>'ebs_patch');
end;
/

3) Please check below query and make sure that "ebs_patch" does not exist.

select SERVICE_ID,NAME from dba_services;

If no entry found then commit the session.

Then, restart the patch using adctrl



I have checked all above but in my case I found the service name in CDB root as it was 19c database.

Solution

1) Source CDB environment file.
sqlplus '/as sysdba'

2) Run Below Queries and we can find the service name ebs_patch

alter session set container=cdb$root;
select name, con_id# from cdb_service$;
select service_id, name, pdb, deletion_date from service$;

3) Delete the service

exec dbms_service.delete_service('ebs_patch');

4) Restart the failed worker for patching




Please do like and subscribe to my youtube channel: https://www.youtube.com/@foalabs If you like this post please follow,share and comment