Return-Path: X-Original-To: oracle-l@orafaq.com Delivered-To: oracle-l@orafaq.com Received: from smtp-aa.freelists.org (smtp-aa.freelists.org [23.23.80.81]) by malta2546.startdedicated.com (Postfix) with ESMTPS id 3EBF7100343D18 for ; Wed, 20 Oct 2021 02:12:15 +0200 (CEST) Received: from turing.freelists.org (ip-10-0-0-164.ec2.internal [10.0.0.164]) (using TLSv1.3 with cipher TLS_AES_256_GCM_SHA384 (256/256 bits) key-exchange X25519 server-signature RSA-PSS (2048 bits)) (No client certificate requested) by smtp-aa.freelists.org (Avenir Technologies Mail Multiplex) with ESMTPS id 28CD242A53; Wed, 20 Oct 2021 00:12:14 +0000 (UTC) Received: from localhost (localhost [127.0.0.1]) by turing.freelists.org (Postfix) with ESMTP id 14BDC3F7EA; Wed, 20 Oct 2021 00:12:14 +0000 (UTC) DKIM-Signature: v=1; a=rsa-sha256; c=simple/simple; d=freelists.org; s=turing; t=1634688734; bh=TlUOe25g4CrgijAoDf5X7yUBCUDwQCJdCZH7Sx+CtG4=; h=From:Sender:Sender:From; b=tI18ERFkfylDmcMI++fT86v3878aozDOBG7bh65pSQGfqsUlT7r/LljmDkaZzDI9m y5gtS27pqlWGlVmk9Nf7a/rARGrcA9qHw+aHngvRV9d5sr/rlEda0YY/96uCJKX6uR uDXg5wyJ21Z509ObsU6qJpba2CI2wpdCO2UrUSq4= X-Virus-Scanned: by FreeLists at turing.freelists.org Received: from turing.freelists.org ([127.0.0.1]) by localhost (turing.freelists.org [127.0.0.1]) (amavisd-new, port 10024) with ESMTP id uGC_oT-Pr98p; Wed, 20 Oct 2021 00:12:14 +0000 (UTC) Received: from turing.freelists.org (localhost [127.0.0.1]) by turing.freelists.org (Postfix) with ESMTP id 9AE9A3F7ED; Wed, 20 Oct 2021 00:12:10 +0000 (UTC) DKIM-Signature: v=1; a=rsa-sha256; c=simple/simple; d=freelists.org; s=turing; t=1634688732; bh=TlUOe25g4CrgijAoDf5X7yUBCUDwQCJdCZH7Sx+CtG4=; h=From:Sender:Sender:From; b=fQ9YIzZryQXYzyR79EfmRSHAxZx4UClevn80y6aVVSfN8dw7LQjo/vRvG9T79n0ZR RmX19WdzO7RaV+jz824ztM1wLk6Q5vGNWAhWTA8yJRGqimvHnVdxgmYtd+fYib1xrG qxHifojX+e38pbkq66NYEF/GkFikUEnEzkdk+flk= Received: with ECARTIS (v1.0.0; list oracle-l); Wed, 20 Oct 2021 00:12:09 +0000 (UTC) Received: from localhost (localhost [127.0.0.1]) by turing.freelists.org (Postfix) with ESMTP id F2FC53F7B9 for ; Wed, 20 Oct 2021 00:12:08 +0000 (UTC) Authentication-Results: turing.freelists.org; dkim=pass (2048-bit key; unprotected) header.d=gmail.com header.i=@gmail.com header.a=rsa-sha256 header.s=20210112 header.b=AdXitL51; dkim-atps=neutral Received: from turing.freelists.org ([127.0.0.1]) by localhost (turing.freelists.org [127.0.0.1]) (amavisd-new, port 10024) with ESMTP id w3XJyqbntNdg for ; Wed, 20 Oct 2021 00:12:08 +0000 (UTC) Received: from mail-lj1-f169.google.com (mail-lj1-f169.google.com [209.85.208.169]) (using TLSv1.3 with cipher TLS_AES_128_GCM_SHA256 (128/128 bits) key-exchange X25519 server-signature RSA-PSS (2048 bits) server-digest SHA256) (No client certificate requested) by turing.freelists.org (Postfix) with ESMTPS id CD8B93F5F2 for ; Wed, 20 Oct 2021 00:12:08 +0000 (UTC) Received: by mail-lj1-f169.google.com with SMTP id o11so9211073ljg.10 for ; Tue, 19 Oct 2021 17:12:08 -0700 (PDT) X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20210112; h=x-gm-message-state:mime-version:references:in-reply-to:from:date :message-id:subject:to:cc; bh=Z7bs6Dj9osIhHtULCGXPj0KLizI0kxxXYL6un/hnZaw=; b=Q5wVxQr0dKDImG4QpoMy/9jFTz9oN1rAAlRYL8NPqA2k3xqaVP+G+1bBpjiirYy3Mz EWVqIEGtWpJn9dOylfhRbJ+dntb+JcuKQbItiRS7/Y/xRLqRTjtka0EN1ij6AMILJ9LP loALcljnSqp77PQUxgMDx2Du8gNIoC0MP0BhyX/FokWbTpqzya+VSSrv3jXkOQJYOOlf N+vQsbh/rWYLPy0i+MpQhuzbBinijtZhUTegEk9PKOSWfO7Bai8eU4dm/ZOpyYu6iK0Q kUwqZ5n9JhfDGH4poHjol9v3ri2aChKXWG1htAbw1S0utpk5lGeXcMz9kl85jua2E9vj AUzA== X-Gm-Message-State: AOAM533odaz5yVbMIDt6+dBqNbgTZPQ75qvkjukxpXkceYwV+XHSS82l A38asfx0mUJbWWENP7jjnAFFS4MiD0MBICbiQYC+INTNNQA= X-Google-Smtp-Source: ABdhPJwfs/3+4ebmEp1/jSKWySBnuL1BoH3IH1D7e0QXRFJFZF3Kg0Po1+CExbwW5jLnONqwGiix7EppVAj+ldouh1E= X-Received: by 2002:a05:651c:115:: with SMTP id a21mr9757115ljb.6.1634688727555; Tue, 19 Oct 2021 17:12:07 -0700 (PDT) MIME-Version: 1.0 References: In-Reply-To: From: Ruan Linehan Date: Wed, 20 Oct 2021 01:11:56 +0100 Message-ID: Subject: Re: ORDS Restart requirement To: kris rice Cc: tim@oracle-base.com, Oracle-L oracle-l Content-Type: multipart/alternative; boundary="000000000000eca33f05cebda081" X-archive-position: 81194 X-ecartis-version: Ecartis v1.0.0 Sender: oracle-l-bounce@freelists.org Errors-to: oracle-l-bounce@freelists.org X-original-sender: ruandav@gmail.com Precedence: normal Reply-To: ruandav@gmail.com List-Help: List-Unsubscribe: List-software: Ecartis version 1.0.0 List-Id: oracle-l X-List-ID: oracle-l List-Subscribe: List-Owner: List-post: List-Archive: X-list: oracle-l --000000000000eca33f05cebda081 Content-Type: text/plain; charset="UTF-8" Hi Kris, We are using Oracle Rest Data Services version 20.3.0. I've scanned through the Release Notes for subsequent versions, but I've not yet been able to identify an obvious match to what you describe below within the issues fixed or changes. Regards, Ruan On Tue, Oct 19, 2021 at 3:43 PM kris rice wrote: > Ruan, > There was an issue a while back that if not available at startup it was > flagged and never revisited. That was changed but I can not recall what > version. There were basically 2 issues that got addressed. 1) what you see > in that a down db is bad listed and 2) connecting to every defined db at > startup. What version are you using? > > -kris > > On Mon, Oct 18, 2021 at 7:24 PM Ruan Linehan wrote: > >> Hi Kris/Tim, >> >> After quite a few various scenario based attempts at "breaking" the >> connectivity between ORDS and the DB endpoints, I think I have observed >> what is really going on. Trying to recreate my original suggested symptom >> by creating a service availability disconnect, even for long periods of >> time, was unsuccessful; insofar as ORDS always then successfully resumed >> work once the DB connection was re-established (Which is good). So >> apologies for mis-representing the issue in my original mail. >> >> I investigated some of the older historical APEX logs. What is more >> likely happening in our situation, is that restarts of the ORDS services >> (i.e. Which we do frequently to introduce new endpoint configs) on the load >> balanced VMs is sometimes crossing over with periodic maintenance window >> periods of individual pluggable databases. >> >> Therefore, sometimes it will happen that for 1 in 100 endpoints, there >> will be an ORDS complaint with respect to the initial startup validation of >> the pool config. e.g. "WARNING: The pool named: |apex|pu| is missing and >> will be ignored: The database service named: |apex|pu| does not exist." >> These startup errors were not being properly trapped / flagged as part of >> our processes, so I can easily fix that. >> >> Testing of this scenario with an ORDS startup, whilst a PDB database >> service is currently down, does result in the connection never establishing >> once the PDB service is eventually started. So I believe this is actually >> what is occurring for us; Not a disconnect of the database from ORDS, but >> it is the initial startup verification which if unsuccessful for a >> particular endpoint, means that the pool entry is literally "ignored" from >> then on. >> >> I assume this is the expected behaviour and if so, is there any work >> around beyond a restart of ORDS at that point? >> >> Kind regards, >> Ruan >> >> On Tue, Oct 12, 2021 at 9:58 PM Ruan Linehan wrote: >> >>> *"and it never requires a restart. The pools should reestablish >>> themselves as needed"* >>> >>> Thanks Tim and Kris for taking the time to reply. >>> >>> Well, that is quite puzzling to me that our 'broken' connection issue >>> seems unexpected to you both; but it also makes me hopeful that this is >>> some mis-config or error in implementation on our part. I'm certainly not >>> proficient in configuring ORDS as I'm usually investigating from the other >>> side of the (database) fence. I'm intrigued now, as you mentioned Tim, that >>> maybe we have some sequence of events or triggers leading to our particular >>> issue. Yes, we have multiple ORDS installs on separate VMs behind a >>> load-balancer. >>> >>> I've just "broken" a non-production environment ORDS web services pool >>> connection in the last hour, by stopping the PDB services and killing >>> existing ORDS sessions to test. I'm going to leave this down now for a few >>> different periods of hours to see what happens and will reply back here >>> with some specifics. >>> >>> Kind regards >>> Ruan >>> >>> On Tue, Oct 12, 2021 at 1:57 PM kris rice wrote: >>> >>>> I'd suggest, as always, upgrade. We have ords nodes on some databases >>>> that go up/down, active/readonlny,... and it never requires a restart. The >>>> pools should reestablish themselves as needed. For example, I have to >>>> manage all the ords nodes in Autonomous DB and those things come and go on >>>> the whim of customers kicking tires or shutting down to save money when not >>>> in use. These ords nodes run somewhere around 2k connection pools each and >>>> never need a restart, even when the pdb is relocated out from under us to >>>> another CDB. >>>> >>>> Happy to jump on a zoom or medium of choice and chat more if you'd like. >>>> >>>> -kris >>>> >>>> On Tue, Oct 12, 2021 at 7:43 AM Tim Hall wrote: >>>> >>>>> Hi. >>>>> >>>>> That's interesting.I can't ever remember having to restart ORDS as a >>>>> result of a database outage. Even prolonged ones. We install in the PDB, >>>>> not the CDB and have one or more ORDS instances in Docker containers for >>>>> each PDB. As a result, a problem with one instance doesn't affect >>>>> everything else. Even so, these are basic connection issues you are having, >>>>> so I don't think the topology differences can be that relevant. I think >>>>> this may be a job for the ORDS team. They could certainly tell you what the >>>>> expected behaviour is. >>>>> >>>>> Do you have a non-prod/test setup where you can test some failure >>>>> scenarios? I wonder if there are specific patterns that cause the issue, >>>>> rather than a general overarching issue. >>>>> >>>>> I guess in the interim I would consider a mitigation. I assume you >>>>> have multiple ORDS installations behind a load balancer to support this. If >>>>> so, you could script a restart of all ORDS instances (one at a time of >>>>> course), and call that at the end of every piece of scheduled maintenance. >>>>> It would minimise the apparent outage. >>>>> >>>>> I'll see if I can get someone from the ORDS team to look at this >>>>> thread. >>>>> >>>>> Cheers >>>>> >>>>> Tim... >>>>> >>>>> On Tue, Oct 12, 2021 at 9:22 AM Ruan Linehan >>>>> wrote: >>>>> >>>>>> Hi all, >>>>>> >>>>>> I've researched elsewhere but not been able to identify a suitable >>>>>> solution, so I'm asking here in the hopes that an ORDS aficionado might >>>>>> provide some direction. >>>>>> >>>>>> My issue is around the perception of a restart of ORDS being a >>>>>> requirement to re-establishing a connection to an endpoint which may have >>>>>> been unavailable for a period of time. >>>>>> >>>>>> We run ORDS v20 on a Linux VM as part of a solution accompanying an >>>>>> Exadata multitenant environment. ORDS is made available to all PDBs, >>>>>> installed in the CDB. Within the 'conf' directory of ORDS - we stage all >>>>>> the associated apex_aa.xml, apex_ab.xml, apex_ac.xml etc configuration >>>>>> mapping files. Periodically, one of the PDB environments may be made >>>>>> unavailable (i.e Closed or else RAC services stopped, or someone >>>>>> inadvertently locks the ORDS_PUBLIC_USER account etc) for maintenance, for >>>>>> a day or weekend etc. When this takes place, the pluggables >>>>>> ORDS_PUBLIC_USER database sessions are terminated and the ORDS connection >>>>>> cannot be re-established for a period of time to that PDB. So far so good. >>>>>> >>>>>> Once the maintenance is complete, and the PDB is re-opened once >>>>>> again, RAC services restarted, ORDS does not automatically re-establish a >>>>>> database connection to that same PDB. >>>>>> >>>>>> If I need to get the ORDS_PUBLIC_USER connections re-established once >>>>>> more for that specific PDB, then I need to stop ORDS processes for all >>>>>> clients and restart. >>>>>> i.e. This reads the url mapping xml and validates the associated >>>>>> apex_aa.xml files etc., and eventually successfully re-establishes ALL the >>>>>> database connection and all is good. >>>>>> >>>>>> The difficulty is though, that we have literally hundreds of these >>>>>> PDBs in a CDB, and literally hundreds of accompanying ORDS endpoints. So, >>>>>> if one of these environments is impacted by a "maintenance" of some sort, >>>>>> and the database connection is severed for a time, then it requires a full >>>>>> restart of ORDS for ALL to get it back, which is rather painful. >>>>>> >>>>>> There must be something I am missing right? I understand XML config >>>>>> changes require a restart of ORDS to be picked up, but I find it troubling >>>>>> that a full restart is also required when just one client endpoint >>>>>> connection out of a hundred is impacted? >>>>>> Is there any way I can force ORDS to 'reinit' and re-read the conf >>>>>> files to re-establish a single broken connection with restarting? >>>>>> >>>>>> Kind regards, >>>>>> Ruan >>>>>> >>>>> --000000000000eca33f05cebda081 Content-Type: text/html; charset="UTF-8" Content-Transfer-Encoding: quoted-printable
Hi Kris,

We are using = Oracle Rest Data Services version 20.3.0.
I've scanned throug= h the Release Notes for subsequent versions, but I've not yet been able= to identify an obvious match to what you describe below within the issues = fixed or changes.

Regards,
Ruan


On Tue, Oct 19, 2021 at 3:43 PM kris rice <kris.rice@jokr.net> wrote:
Ruan,
=C2=A0 Th= ere was an issue a while back that if not available at startup it was flagg= ed and never revisited. That was changed but I can not recall what version.= There were basically 2 issues that got addressed. 1) what you see in that = a down db is bad listed and 2) connecting to every defined db at startup.= =C2=A0 What version are you=C2=A0using?

-kris

On Mon, Oct 18, 2021 at 7:24 PM Ruan Linehan <ruandav@gmail.com> wrote:
Hi Kris/Tim= ,

After quite a few various scenario based attempts at "breakin= g" the connectivity between ORDS and the DB endpoints, I think I have = observed what is really going on. Trying to recreate my original suggested = symptom by creating a service availability disconnect, even for long period= s of time, was unsuccessful; insofar as ORDS always then successfully resum= ed work once the DB connection was re-established (Which is good). So apolo= gies for mis-representing the issue in my original mail.

I investig= ated some of the older historical APEX logs. What is more likely happening = in our situation, is that restarts of the ORDS services (i.e. Which we do f= requently to introduce new endpoint configs) on the load balanced VMs is so= metimes crossing over with periodic maintenance window periods of individua= l pluggable databases.

Therefore, sometimes it will happen that for= 1 in 100 endpoints, there will be an ORDS complaint with respect to the in= itial startup validation of the pool config. e.g. "WARNING: The pool n= amed: |apex|pu| is missing and will be ignored: The database service named:= |apex|pu| does not exist."
These startup errors were not being pro= perly trapped / flagged as part of our processes, so I can easily fix that.=

Testing of this scenario with an ORDS startup, whilst a PDB databa= se service is currently down, does result in the connection never establish= ing once the PDB service is eventually started. So I believe this is actual= ly what is occurring for us; Not a disconnect of the database from ORDS, bu= t it is the initial startup verification which if unsuccessful for a partic= ular endpoint, means that the pool entry is literally "ignored" f= rom then on.

I assume this is the expected behaviour and if so, is = there any work around beyond a restart of ORDS at that point?

Kind r= egards,
Ruan

On Tue, Oct 12, 2021 at 9:58 PM Ruan Linehan <ruandav@gmail.com> w= rote:
"and it never requires a restart. The pools should reestab= lish themselves as needed"

Thanks Tim and Kris for taking t= he time to reply.

Well, that=C2=A0is quite puzzling to me that our = 'broken' connection issue seems unexpected to you both; but it also= makes me hopeful that this is some mis-config or error in implementation o= n our part. I'm certainly not proficient in configuring ORDS as I'm= usually=C2=A0investigating from the other side of the (database) fence. I&= #39;m intrigued now, as you mentioned Tim, that maybe we=C2=A0have some seq= uence of events or triggers leading to our particular issue. Yes, we have m= ultiple ORDS installs on separate VMs behind a load-balancer.

= I've just "broken" a non-production environment=C2=A0ORDS web= services pool connection in the last hour, by stopping the PDB services an= d killing existing ORDS sessions to test. I'm going to leave this down = now for a few different periods of hours to see what happens and will reply= back here with some specifics.

Kind regards
Ruan

On Tue, Oct 12, 2021 at 1:57 PM kris rice <kris.rice@jokr.net> wrote:
I'd sugg= est, as always, upgrade.=C2=A0 We have ords nodes on some databases that go= up/down, active/readonlny,... and it never requires a restart. The pools s= hould reestablish themselves as needed. For example, I have to manage all t= he ords nodes in Autonomous DB and those things come and go on the whim of = customers kicking tires or shutting down to save money when not in use.=C2= =A0 These ords nodes run somewhere around 2k connection pools each and neve= r need a restart, even when the pdb is relocated out from under us to anoth= er CDB.

Happy to jump on a zoom or medium of choice and = chat more if you'd like.

-kris
=
On Tue= , Oct 12, 2021 at 7:43 AM Tim Hall <tim@oracle-base.com> wrote:
Hi.

=
That's interesting.I can't ever remember having to restart ORD= S as a result of a database outage. Even prolonged ones. We install in the = PDB, not the CDB and have one or more ORDS instances in Docker containers f= or each PDB. As a result, a problem with one instance doesn't affect ev= erything else. Even so, these are basic connection issues you are having, s= o I don't think the topology differences can be that relevant. I think = this may be a job for the ORDS team. They could certainly tell you what the= expected behaviour is.

Do you have a non-prod/tes= t setup where you can test some failure scenarios? I wonder if there are sp= ecific patterns that cause the issue, rather than a general overarching iss= ue.

I guess in the interim I would consider a miti= gation. I assume you have multiple=C2=A0ORDS installations behind a load ba= lancer to support this. If so, you could=C2=A0script a restart of all ORDS = instances (one at a time of course), and call that at the end of every piec= e of scheduled maintenance. It would minimise the apparent=C2=A0outage.

I'll see if I can get someone from the ORDS team = to look at this thread.

Cheers

Tim...

On Tue, Oct 12, 2021 at 9:22 AM Ruan Linehan <ruandav@gmail.com> w= rote:
Hi all,

I've researched elsewher= e but not been able to identify a suitable solution, so I'm asking here= in the hopes that an ORDS aficionado might provide some direction.
My issue is around the perception of a restart of ORDS being a requirement= to re-establishing a connection to an endpoint which may have been unavail= able for a period of time.
=C2=A0
We run ORDS v20 on a Linux V= M as part of a solution accompanying an Exadata multitenant environment. OR= DS is made available to all PDBs, installed in the CDB. Within the 'con= f' directory of ORDS - we stage all the associated apex_aa.xml, apex_ab= .xml, apex_ac.xml etc configuration mapping files. Periodically, one of the= PDB environments may be made unavailable (i.e Closed or else RAC services = stopped, or someone inadvertently locks the ORDS_PUBLIC_USER account etc) f= or maintenance, for a day or weekend etc. When this takes place, the plugga= bles ORDS_PUBLIC_USER database sessions are terminated and the ORDS connect= ion cannot be re-established for a period of time to that PDB.=C2=A0 So far= so good.=C2=A0

Once the maintenance is complete, and the PDB is re-= opened once again, RAC services restarted, ORDS does not automatically re-e= stablish a database connection to that same PDB.

If I need to get th= e ORDS_PUBLIC_USER connections re-established once more for that specific P= DB, then I need to stop ORDS processes for all clients and restart.
i.e.= This reads the url mapping xml and validates the associated apex_aa.xml fi= les etc., and eventually successfully re-establishes ALL the database conne= ction and all is good.

The difficulty is though, that we have liter= ally hundreds of these PDBs in a CDB, and literally hundreds of accompanyin= g ORDS endpoints. So, if one of these environments is impacted by a "m= aintenance" of some sort, and the database connection is severed for a= time, then it requires a full restart of ORDS for ALL to get it back, whic= h is rather painful.

There must be something I am missing right? I = understand XML config changes require a restart of ORDS to be picked up, bu= t I find it troubling that a full restart is also required when just one cl= ient endpoint connection out of a hundred is impacted?
Is there any way= I can force ORDS to 'reinit' and re-read the conf files to re-esta= blish a single broken connection with restarting?

=
Kind regards,
Ruan
--000000000000eca33f05cebda081-- -- http://www.freelists.org/webpage/oracle-l