ebs_patch or <INSTANCE>_ebs_patch services

[Harin]

Hi Erman,


Hope you are doing good. I have a question regarding patch services : ebs_patch or <INSTANCE>_ebs_patch.

How does database determine that all connection from these service will connect to patch edition only
? I mean we can create any service say x and connect with that to database (and shows database as open).
But if we do so using these it says : "ebs_patch service ORA-00604: error occurred at recursive SQL level 1 ORA-20099: E-Business Suite Patch Edition does not exist. ORA-06512: at line 29".

I understand that patch service is  used for connecting to patch edition when patch cycle is open (please correct me if wrong) but how does it map a service to an edition?

Regards
Harin

[Harin]

I think its ebs_ebs_login trigger

Your post:
>>>> it is an  after logon trigger. It is an online patching related thing.
You can always view its code using;

SQL> show long;
long 80
SQL> set long 20000
SQL> select dbms_metadata.get_ddl('TRIGGER','EBS_LOGON','SYSTEM') from dual;

Anyways;

It gets the service name that you are connecting to using sys_context userenv.
If you connect to the patch env. I mean if you are connecting to the database using patch service name, then the trigger checks if there is a E-Business Suite Patch Edition exist in the database. If not it gives error.
it reads the service name that you are connection to and the db_domain from your session env.
IF the service name is ebs_patch;
then;
it gets the patch edition name
it sets the current edtition to patch edition.
it alters your session so that pathing actions will wait for locks if needed
It gives error if E-Business Suite Patch Edition does not exist

[Harin]

Adding more information:

As of Oracle Database 11g Release 2 (11.2.0.2), if you do not specify your session edition at connection time, then:

If you use a database service to connect to the database, and an initial session edition was specified for that service, then the initial session edition for the service is your initial session edition.

Otherwise, your initial session edition is the database default edition.

As of Release 11.2.0.2, when you create or modify a database service, you can specify its initial session edition.

To create or modify a database service, Oracle recommends using the srvctl add service or srvctl modify service command. To specify the default initial session edition of the service, use the -t option.

Alternatively, you can create or modify a database service with the DBMS_SERVICE.CREATE_SERVICE or DBMS_SERVICE.MODIFY_SERVICE procedure, and specify the default initial session edition of the service with the EDITION attribute.

so, I assume whenever new patch cycle is open, new edition is created and would be assigned to the service as mentioned above
and this might get populated too
select SERVICE_ID,NAME,EDITION from DBA_SERVICES;
we need to validate this when we open a patch cycle
This statement is whats giving that idea "To create or modify a database service, Oracle recommends using the srvctl add service or srvctl modify service command. To specify the default initial session edition of the service, use the -t option."

[ErmanArslansOracleBlog]

hi Harin,

You are in a good way :) answering your questions yourself..
Thanks for sharing your researches as well..

So your inputs are true. patch edition is created when you run adop prepere phase.
However, the service name named ebs_patch should already be there in the listener.
So anytime(even when there is no online patching cycle), when you check it with lsnrctl status command, you should see the ebs_patch service name there.

so, ebs_patch service name should always be there, but the trigger should allow or disallow you to connect to the ebs_patch edition according to the existence of the database patch edition. (in other words; existence of the online patching cycle)

Check the things explained above, and update pls.

[Harin]

Thanks Erman.

Checked the status if the listener and code of trigger as well.

All things discussed fall in place and make sense.

You can close this thread.

Regards,
Harin