It’s that time of year again, our 2023 quarter 1 family of products are starting to trickle out, with ORDS 23.1 available today (Downloads Page.)
Upgrading, same as before
Run the ‘install’ command, and run through your pools as desired, then start ORDS back up.
When we’re done, you’ll should see a confirmation of version –
And now starting it back up with ‘serve’ –
So what’s new?
APEX Management endpoints in the Database API
Inspired by this blog post from our friend and co-worker Carsten, and developed alongside the APEX team 🙂
Simplified/Nicer Looking Sign-Ins
Most of the time you’ll see this are for authenticating to SQL Developer Web. What used to be two-step series of prompts and dialogs, is now a single dialog.
What if you don’t have a ‘simple’ scenario?
Examples of something not simple, combinatations of:
- multiple pools configured in your ORDS server
- REST Enabled schemas with aliases
Then you’ll use the Advanced panel to sort that.
Let’s say I have two pools, with a pool mapped to a database called ‘orcl.’ And in that database, I have a schema called JEFE, REST Enabled as ‘boss.’
BEGIN ORDS.ENABLE_SCHEMA(p_enabled => TRUE, p_schema => 'JEFE', p_url_mapping_type => 'BASE_PATH', p_url_mapping_pattern => 'boss', p_auto_rest_auth => FALSE); COMMIT; END; /
I would in the advanced panel, supply the ORDS mapping pattern to my pool, that would be ‘orcl’, then I would supply the alias for JEFE, which would be ‘boss’, and separate the two with a ‘/’
Note that this isn’t a common deployment for customers, so this wouldn’t be required except in ‘Advanced’ situations. And later this year we hope to have it so that ORDS can automatically ‘sort’ aliased schemas automatically, so it should get even easier.
But let’s login with that exact scenario –
Which logs me in, and I see this –
Even more Connections improvements
We automatically support your browser stored credentials/auto-fill.
Chrome goes, oh I know this – if I select one of those, it will pre-fill the form AND submit it, so it’s actually a single-click operation.
And finally, when you sign out, it immediately takes you back to the sign-in page.
Note if you’re in that small case of ORDS deployments with multiple pools, you’ll need to use the advanced panel to set the pool mapping as desired for your new session.
REST Workshop Export Schema
Exactly what it sounds like. Instead of simply exporting 1 module at a time, I can export…all of them!
Now, my export script is big…too big for all of it to be shown in the slider that pops out when clicking the Export button, so I’ll need to use the ‘Download’ button vs simply copying the PL/SQL to my clipboard.
Is that all?
Nope! I’ll have much more to show/demo in a week or three, stay tuned!
What about SQL Developer, Data Modeler, and SQLcl?
Versions 23.1 of those should be out next week. They’re just going through their final shake-downs and certifications.
41 Comments
Hi Jeff,
This question is about connectivity. Kindly, share your experience.
We are planning to migrate on-premise oracle database with 150GB. In OCI,we have a DB system(Oracle Base Database Service). Not an Autonomous Database. We have a question regarding connectivity. We dont have VPN or Fast Connect. How can we connect to DB system in private subnet to perform the migration. Please suggest. We want to go for offline migration.
The migration steps consist of:
1) Invoking Data Pump export on-premise database to generate the dump files that include the data and metadata.
2) Securely copy the dump files to the OCI database system.
3) On the OCI database system, invoking Data Pump again to import the exported data into a prescribed database service and simply validate the imported data.
Is it possible to perform step 2 & 3 without VPN or Fast Connect?
Thank You
My experience here is limited, but in general I would look at either moving a RMAN backup or Data Pump job’s DMP files to the object store, and once those are there, moving the data or creating a new db based on the backup, should be straightforward.
Thanks for the update jeff.
moving a RMAN backup or Data Pump job’s DMP files to the object store. Inorder to move the backup to object storage,do we need vpn connection or fast connect,is there any alternative. Kindly,please suggest
Kindly read the docs, or kindly reach out to an OCI engineer.
Hi Jeff,
Please kindly advice on the configuration in OCI PAAS
Here, we need your expertise.
We will have PROD and UAT environments.
We have taken Oracle Base Database Service and one Compute.
We have only one load balancer.
If we use Load balancer for PROD and Nginx for UAT, Do you think will there be any challenges during testing etc?
We have to use either Nginx for both prod and uat
OR
use Load balancer for PROD and Nginx for UAT
Please kindly suggest.
Thanks,
SG
Why not use same stack for both environments? And why a LB for only one ORDS (compute) vs say…3?
Hi Jeff,
why a LB for only one ORDS (compute) ?
We want to use our custom domain for ORDS. We planned to place ORDS in private subnet. Not sure if it makes sense.
Can you suggest some good plan according the items we have.
Database Service – Enterprise Edition OCPU – 3 for PROD
Database Service – Enterprise Edition OCPU – 2 for UAT/DEV
Compute Standard X9 OCPU – 2 for PROD
Compute Standard X9 OCPU – 2 for UAT
Please kindly share from your experience.
Thanks,
SG
you could if you wanted use the same compute, and a single ords to handle both prod and uat/dev
see our best practices doc for ords, we recommend 3 nodes, not 1
Hello Jeff,
We are hosting Apex environment in OCI. We have one question with respect to Oracle Base Database Service – Enterprise Edition OCPU.
Does this service come with its own VM?
Do we have to pay for the VM on which database gets hosted?
We also have one compute standard X9 OCPU.
Thanks,
SG
Base service has several options, one of which is being virtual machine-based.
Cloud Licensing isn’t my Forte, I suggest you read up on the Base service pricing.
Hi jeff,
We have Apex with ords in different servers. Using ORACLE Database.
We have developed few rest web services and shared to third party.
1) Does this webservices use the connection pool in ords.
We are consuming few web services shared by third party.
2) Does these use connection pool in ords.
Thank you
Yes to what you developed
No to what you’re consuming
Hello,
In Oracle Base Database Service – Enterprise-> Will we have the option to create 2 pluggable databases on the Oracle Base Database Service – Enterprise VM.
Does the database comes with multi-tenant option.
I think it is preconfigured with single PDB. Please correct me.
It will be of great help
Diagram as per shows Multi-tenant is not listed in Oracle Base Database Service – Enterprise
“https://medium.com/@hiteshgondalia/oci-basedb-dbcs-cloud-dba-role-and-responsibility-a73222ae7c67”
Thanks,
Naresh
Multitenant option is different that being able to create a PDB.
You can have up to 3 PDBs on a instance before needing to license the multitenant option.
However, I don’t like answering licensing questions, you need to check the terms of your service to see what you can and should or should not do with it.
Hi jeff,
Oracle Base Database Service – Enterprise Edition OCPU —
1) Does Apex and ORDS come with default installation with above base database service or do we have to install Apex and ords on it.
2)Does it come with pluggable database or do we have to create from our end.
Thanks for all the support you provide.
.
Thank You,
SG
1. no you will have to install it
2. the service generally prompts you to create a PDB when you create your base service
Thank you for the update.
What I understand, We have to perform the ords and Apex installation on the base database service and we are going to create the base service.
What about operating system.Who will take care of operating system related work,like upgrading the oracle linux etc..Will Oracle handle it.
Thanks,
SG
Start here.
Hi Jeff,
If we go for autonomous database in OCI,will we get the backend access to develop Custom
application in Apex.
Thanks,
Satish
What does ‘backend access’ mean?
In an Autonomous Database service, you have everything you need to build, deploy, and run custom Apex apps.
Hi Dimitri,
We need your help.
This is regarding OCI PASS – Apex
Oracle Base Database Service – Enterprise Edition OCPU
Virtual Machine(VM.Standard3.Flex) – Compute Standard-X9 OCPU
I think the compute has given by oracle to install ORDS. Please correct me if wrong
and Who is going to perform database activities? Is it customer or oracle.
Database Activities like Installing/Upgrading the Oracle Database
Thanks,
Satish
We don’t give people compute resources – you pay for those.
These are generally customer managed activities/resources – unless you’re on Autonomous, where we (Oracle) handles that for you.
Thanks for the update.
1)
Can we use our custom domain for our Apex application with configuration having Apex+ORDS+Base Database on same vm without having any compute.
2)
Is there any challenges with having Apex+ORDS+Base Database on same vm when compared to having ORDS in compute.
I have referred few blogs but not getting clarify. I hope you will definitely help us in this regard.
Thanks,
SG
No you won’t run ords on the same machine as your base service
Hey Jeff, I’m trying to enable ORDS 23 / APEX 23 and I can only get half way there. I am able to log in to SDW after setting up a REST-enabled user, but whenever I hit the http://server:port/ords/apex URL, I get a 404 error. I have seen a few MOS notes about this, but none of the suggestions have worked in my environment. Incidentally, I am fully in OCI using a Linux 7.6 VM’s for both App and DB servers. ORDS is running standalone with the jetty access log enabled and I can’t seem to get the config right for APEX.
Is there a way to discern what ORDS is looking for when using the …/ords/apex URL?
Thanks
It’s looking for a valid database/connection pool where APEX is installed and the ORDS_PUBLIC_USER has privs to proxy into the APEX accounts to make APEX work.
What do you see when ORDS comes up?
Have you tried the repair command? That will check the users/proxy privs and fix if necessary.
When I hit the /apex url, I get the pretty 404 error screen. I will try running the repair script. I have done connectivity tests to make sure both from a DB and network perspective that my app server can connect to the DB. I all the log files I have trie to enable / view (jetty / db alert log / ORDS screen), there is no indication what the issue might be.
I’ll post after running the repair process.
Thanks
Ok, so I just ran the repar process : ords –config install repair –interactive
There is one piece of the output that concerns me. See below:
[*** script: ords_schema_mapping.sql]
INFO: Configuring ORDS_PUBLIC_USER to map APEX Workspaces and ORDS schemas
Session altered.
Configuring APEX and ORDS schemas for url mapping
Made APEX_PUBLIC_USER proxiable from ORDS_PUBLIC_USER
Made APEX_REST_PUBLIC_USER proxiable from ORDS_PUBLIC_USER
APEX is not installed, or could not be detected, stubbing out <—————–
ORDS_METADATA.APEX_WWV_FLOW_POOL_CONFIG
PL/SQL procedure successfully completed.
I added the arrow on the line above. I am pretty sure that APEX 23 is installed in my database. There could be a problem with the installation process, but all the users are there and I can log in and see them.
Is this a false alarm?
If ORDS can’t detect APEX, we have a big problem. Suggest you open a SR with MOS.
Ok, so I just ran the repar process : ords –config install repair –interactive
There is one piece of the output that concerns me. See below:
[*** script: ords_schema_mapping.sql]
INFO: Configuring ORDS_PUBLIC_USER to map APEX Workspaces and ORDS schemas
Session altered.
Configuring APEX and ORDS schemas for url mapping
Made APEX_PUBLIC_USER proxiable from ORDS_PUBLIC_USER
Made APEX_REST_PUBLIC_USER proxiable from ORDS_PUBLIC_USER
APEX is not installed, or could not be detected, stubbing out <—————–
ORDS_METADATA.APEX_WWV_FLOW_POOL_CONFIG
PL/SQL procedure successfully completed.
I added the arrow on the line above. I am pretty sure that APEX 23 is installed in my database. There could be a problem with the installation process, but all the users are there and I can log in and see them.
Is this a false alarm?
Got one open already. That's kind of why I'm here. After looking at EVERY line of the APEX installation and finding only one error on an object called APEX_230100.WWV_DG_BI_CARS_T1, I assumed that APEX23 was good. After reviewing the info above, I looked at the object called ORDS_METADATA.APEX_WWV_FLOW_POOL_CONFIG and there are no rows in it. I'll try to direct the MOS teams attention that way. Everything I've been asked to do with ORDS worked or did not change the situation.
Is the apex public synonym there in the db?
How about results from
select version_no
from apex_release
1 select version_no
2* from apex_230100.apex_release
SYS@cdb01> /
VERSION_NO
——————————————————————————————
23.1.5
And the synonym?
I apologize, but I don’t know the specific public synonym name you’re referring to.
In doing some research on this situation, I discovered in the apex_verify.html that APEX23 is installed as a Runtime environment (so I just did a ful reinstall of APEX23). I ran a script described in a MOS note as having the capability of showing the status, Runtime or Development, of the APEX install and got the following:
SYS@cdb01> select count(*) from apex_230100.wwv_flows where id=4000;
COUNT(*)
———-
0
In fact, there are no rows in this table. Seems verified, from what I can tell, that the apexins.sql script for APEX23 installs a Runtime Environment. I could be doing it wrong, but I created a new tablespace and ran it like this:
apexins APEX APEX TEMP /i/
So, in a last ditch effort to hopefully fix my ORDS problem, I ran the apxdvins.sql script. It did populate the wwv_flows table, but ORDS standalone with the http://host:port/ords/apex still throws the 404 error and the ORDS landing page show APEX is not available.
I really hope I’m just missing something or doing something wrong. Thanks for your help.
The APEX Synonym is there, now. I dont think there were any synonyms with APEX in the name before I performed the steps described below – could be wrong about the that so please see below.
OWNER SYNONYM_NA TABLE_OWNER TABLE_NAME DB_LINK ORIGIN_CON_ID
——————– ———- ——————– —————————— ———- ————-
PUBLIC APEX APEX_230100 APEX 4
I think I have found that the APEX23 insatllation is possibly flawed in one way. When I finish the standard install process (apexins apex apex temp /i/), the apex_verify.sql script say that it is a RUNTIME environment. There are no records in wwv_flows. It is not until I run apxdvins.sql that the install type changes to DEVELOPMENT. Following that two step process, I run apex_rest_config and then a fresh ORDS install. At that point, when I hit the /ords/ url, I get a good landing page with the 3 cards and the APEX button is lit up. When I click it, I get this error:
APEX.UNHANDLED_ERROR
APEX.CONTACT_ADMIN
RETURN_TO_APPLICATION
So, while I have been focusing on ORDS, this feels more like an APEX installation issue. I have a new SR open on that subject.
after login showing Please clear your browser’s temporary files and try again
Hi Jeff.
I understand that config changes will require a restart. However I was wondering if there is a way to make ORDS wait until the current requests are processed before shutting down i.e. using Jetty’s “graceful” shutdown.
https://stackoverflow.com/questions/25011249/embedded-jetty-and-graceful-shutdown
We have two ORDS instances running, so there is no problem shutting one of them down – the issue is that when we stop an instance the *current* requests get immediately killed (and thus get an error in the browser). What I was hoping is that there is a way to tell ORDS “shutdown once you have processed your current requests” which seems to be what “standalone.stop.timeout” is supposed to do.
Have you any idea what that parameter actually does by any chance?
You can configure Jetty within ORDS, like the 2nd answer has
That SHOULD work.
Hi Jeff.
Do you know of anywhere which explains how ORDS handles “graceful” restarts in any way?
With Apache, for example, if you run a “httpd -k” restart it picks up new config without causing issues with existing requests (they run to completion) whereas ORDS seems to require a “hard” restart (i.e. existing requests would get kicked off) to pick up new config.
There is a parameter “standalone.stop.timeout” which says it “Specifies the period for Standalone Mode to wait until it is gracefully shutdown” but it doesn’t seem to do anything when I’ve tried it.
We’ve stuck an Apache Reverse Proxy in front of a pair of ORDS instances with the intention of being able to upgrade without downtime (take one offline and the other one works whilst we upgrade the first) but when we shut down the primary, all ongoing requests get killed – I was hoping the existing requests would get handled but new requests would be rejected.
If you know of anywhere in the documentation which covers this, that would be amazing
There is no way, to have ORDS uptake changes from config files will require a restart.
For ORDS 23.2, we’ll offer ability (via ORDS CLI) to have the connection pools on a given instance (via process ID) destroyed, so they can be recreated. So if you had a configuration change for a pool, then it would get picked up instead of requiring a restart.