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 prepare 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

[s.kumar]

Hello Erman,

I've some questions reg. the same. In our DEV env. I was about to install a patch and the adop session failed in the PREPARE phase itself as follows (excerpts from the adop.log) :

****************************************
[EVENT]     preparing the seed tables needed for autoconfig execution.
    [PROCEDURE] [START 2023/04/21 12:50:13] Preparing autoconfig related seed data tables
    [STATEMENT] Sourcing Patch edition environment <APPL_TOP_path>/APPSERPDEV_<hostname>.env
    [ERROR]     Cannot connect to database
    [ERROR]     Error Message: ORA-12521: TNS:listener does not currently know of instance requested in connect descriptor (DBD ERROR: OCIServerAttach)
****************************************

When I did the tnsping to the tns entry for this <DEVDB_ebs_patch> service and when I tried to connect to the DB using this ens entry (apps/<apps_password>@<DEVDB_path> - which connects to the service name <DEVDB_ebs_patch>) after setting the patch edition, I'm getting the same error.

I checked on the DB nodes (4 node RAC / 12c multi-tenant) and found that, the service <DEVDB_ebs_patch> wasn't started. And the service_name parameter is also not set with this value as well. But I see an entry in dba_services and in the tnsnames.ora for <DEVDB_ebs_patch>

To confirm, I cross checked with the UAT env. where, I see the service <UATDB_ebs_patch> up and running. Also there's an entry for <UATDB_ebs_patch> in tnsnames.ora. But here too, the service_names parameter is not set to <UATDB_ebs_patch>

From the above 2, I'm not sure if the service_names parameter plays a role here (If not, then how the ebs_patch service is started and up in UAT but not in DEV instance)

So, is "ORA-12521" due to the service <DEVDB_ebs_patch> not up and running ?

EBS maintenance guide status thus about the prepare phase :

"Checks to see if the patch service has been created. adop requires that a special database service exists for the purpose of connecting to the patch edition. This service is created automatically, but its continued existence is validated on each prepare"

I hope it talks about this <DEVDB_ebs_patch>" service only. Does this mean that this ebs_patch service will be started automatically by adop prepare phase, if it's not already up ? Or, it'll only be created by adop prepare phase, if it doesn't exist, but should be started manually and will not be started automatically, if it exists already ?

Thanks for your inputs in advance.
Kumar.

[ErmanArslansOracleBlog]

ORA-12521 is about not having the instance (that is given in your connect descriptor) registered in the listener that you connect to.

Just some background info about that:

---
Cause: There are not any available and appropriate database instances registered with the listener, that are part of the service identified by SERVICE_NAME given in the connect descriptor and that have the specified INSTANCE_ROLE (and INSTANCE_NAME, if specified).
Action: Check to make sure that the INSTANCE_ROLE specified is correct. Run "lsnrctl services" to ensure that the instance(s) have registered with the listener and that they are ready to accept connections.
---

*It may due to the TNS_ADMIN configuration of the patch filesystem..

*Just check the following MOS Note ->
Adop Prepare Failure With Error 'ORA-12521' (Doc ID 1998916.1)