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
Post a Comment
Post a Comment