A-Different-View by Joerg Sobottka

Install RLWRAP on Oracle Database Appliance without internet connection

If you need to install RLWRAP on an Oracle Database Appliance (ODA) with actual releases (19.26-19.28 ) you need to install the following rpms. As we use rlwrap a lot also in our Robotron DBA tool set, we install it nearly everywhere.

If you install rlwrap from the fedoraproject und you get an error "GPG check FAILED" you need to download the public key RPM-GPG-KEY-EPEL-8 from https://dl.fedoraproject.org/pub/epel/RPM-GPG-KEY-EPEL-8, save it, e.g. as RPM-GPG-KEY-EPEL-8.txt and import it with "rpm --import RPM-GPG-KEY-EPEL-8.txt" before installing the rpm.

If you patch an ODA you might get an error at the prepatchreport for the server. If so, uninstall automake, autoconf and m4 with dnf remove <package> and re-install them after you finished the ODA patch (server and storage).

If you download everything locally and upload/put all rpms into the /tmp folder of the ODA, you can run dnf install /tmp/<name-of-the-rpm> and install them one after the other.

My Oracle Support portal changed to worse. How to find at least some DocIDs you still have...

Well, we all looked forward to a new support portal starting at Dec. 7th, enabling us to use AI and to work better and faster than before. What really happend? Links are broken, all bookmarks gone (in my case roundabout 100 sorted for different technical products and problems, patches, white papers), the patch search does not work like it did before, and so on, and so on... At the end, it is not better than before - it is a lot more worse.

At least for all the links in my blog I hope I have found a solution. Typically, all blogs do not tell you the document title, but you can see the DocID.
As the DocID has changed into a "KB" with a number, they still seem to be used in the background. If you do know the DocID which consists of a number, followed by a "Dot" and a second number, you may get access by using the following procedure.

First is, login to your support.oracle.com environment. Then, put the old DocID without the "Dot" and everything following into this URL, replacing <SHORT_DOC_ID> with the value you have.

https://support.oracle.com/support/?anchorId=&kmContentId=<SHORT_DOC_ID>&page=sptemplate&sptemplate=km-article

One of my favorite notes is 888.1, the title is something with "Oracle Database", but if you search for "Oracle Database primary note", well, you don't find it in the search results.

E.g. to get the DocID 888.1 you can use

https://support.oracle.com/support/?anchorId=&kmContentId=888&page=sptemplate&sptemplate=km-article

The title by the way is now "(KB106822) Primary Note for Database Quarterly Release Updates". If you look at the first lines, you can see the following text

"This was MOS Document ID: 888.1 in Legacy MOS. Post migration, we will be moving to KB888 (Date TBA)"

What the f*...

Ok, back to another note I really need a lot - the last version of OPatch was at DocID 293369.1. Again, changing the link to

https://support.oracle.com/support/?anchorId=&kmContentId=293369&page=sptemplate&sptemplate=km-article

leads to the Primary Note for OPatch.

I'm still mourning the lost bookmarks, but at least for the most documents I find at the internet, I may have found a solution to bring them back without struggling with the new search functionality. Unfortunately it seems to not work with all DocIDs, some do show a "404" error, but most do work.

Please comment if this works for the things you need or if you still struggle.

Oracle database - if (not) exists clause is backported to 19c

You may have read my blog post about the new feature of the if (not) exists clause available in database 23ai HERE, where a detailed description can be found. This is one of the most wanted features for a lot of (old fashioned) developers waiting for 23ai on-premises (which is still not available yet).

The good news, this feature was now back-ported to 19c!
After the installation of the Oracle Database Release Update 19.28 you are able to use this feature in 19c.

You don't need to change the compatible parameter of your database, it can still stays on 19.0.0.

You can see at the SQL logon message that my database is running on 19.28 and I am able to use the new if (not) exists syntax:

Like at 23ai, the table jso_test is first created with a column with a length of 10. The second - new - create table would create the table with a column with a length of 20. As "if not exists" is specified, it does not throw an error, BUT the length of the column is still 10. As before, one need to add an alter table to modify this column to a length of 20 characters.

The third attempt is like it was until 19.27 where the if (not) exists clause did not exists. It throws an error.

Now you can start changing your database scripts to use the if (not) exits clause, but don't forget to alter the columns to reflect schema changes. And you don't have to wait for 23ai on-premises!

Very good news for all database developers scripting the schema changes.

An underestimated feature: Using Oracle Remote Listener with Standard Edition 2 Licenses - Part 4 - Security through "Hiding the database"

In the fourth part of the series "Remote Listener for Standard Edition 2 Databases", I highlight some security features that can be gained by using Remote Listener. These features can also be used with standard listeners, but they are particularly useful when running remote listeners.

First of all, the remote listener is “only” a listener, and therefore the same recommendations and restrictions apply - from a security perspective - as for local listeners. Nevertheless, especially running with an Oracle Standard Edition 2 license, these featurs can be used to provide additional security for the your database connection. This blog post provides an overview of the two features which allow to hide your database server and your database itself by using a remote listener.

1. Hide the database node(s)

By using the remote listener node in a TNS entry instead of the database node(s), end users can be prevented from seeing which database node the database is currently running on. Isn't it better to have the already known application server node, on which the remote listener is running, exposed in your tnsnames.ora (or jdbc connection string)?

If the database node(s) is/are not displayed in the application somewhere or cannot be evaluated in a query (e.g., by selecting v$instance) - because end users do not have SQL access, end users will not know anything about the database server.

Furthermore, a potential attacker who gains access to a client machine cannot easily find out which database server or servers are involved – they would need access to the application server (to execute an lsnrctl status) or do network tracing. The screenshot is an example of the TNS entry for a database service where only the application server name is specified as the host, but not the names of the two database servers on which this database service is started (depending on the role, standby/primary). Just looks like a single-server entry.

2. Hide the PDB or (Non-CDB) Database Service

Often, there is only one database service that is actually intended to be used by a remote listener—an application database service (please do not use a default service, but all administrators should be aware of this by now). This is because application servers are typically divided into production and test servers, which means that only one dedicated database service needs to be accessed from each dedicated application server.

What nearly nobody know. This service can be configured in the listener.ora file for the remote listener.

A parameter exists which is admittedly relatively rarely used: DEFAULT_SERVICE_<Listener-Name>, i.e., normally DEFAULT_SERVICE_LISTENER.

The 19c database documentation states the following about this parameter:

After reading through the section marked in yellow, one might assume that this parameter does not work in a container database, as the client must EXPLICITLY specify the service name. However, the parameter is still present in 23ai, and there is no longer a classic architecture here. In addition, the parameter is called DEFAULT_SERVICE_<listener> and not DEFAULT_SID_<listener>. So it's time to find out whether the parameter is actually effective.

For testing purposes, Default_SERVICE_LISTENER is set as follows in the listener.ora file of the remote listener:

Now the TNS entry is adjusted and the value for the service is removed.

Will the connection be established even without an explicit service definition in TNSNames.ora?

Yes, it works.

Conclusion:

By combining the hiding of the database servers and the database service, it is possible to ensure - with relatively simple effort - that as little information as possible about the database and its underlying server(s) is available on the client PCs.