What DALL-E thinks “Oracle Database Release Notes Documentation Bug” looks like
Update: In Feb 2024 Oracle updated the release notes to fix most of the below! They still have the un-needed export of the CV_ASSUME_DISTID, but they did add the steps about patching with the 19.22 patch during install and updating opatch. Thanks!
In January 2024, Oracle released a new version of the Oracle 19c release notes. They also released the 19.22 patchset for Oracle Database. The great news is that with the 19.22 release, Oracle has finally got the Oracle Database on-premises install on Oracle Linux 9 stuff knocked out. It works ‘out of the box’ now. However, if you look at the release notes and navigate to the section entitled “Known Issues and Bugs for Oracle Linux 9 and Red Hat Enterprise Linux 9“, and then navigate to the 19.22 subsection, you’ll see this:
And, well… It’s not really that simple. If you didn’t have 35 years of experience reading Oracle release notes, you might take that statement at face value. Things won’t go well for you if you did. Instead, you have to peer up at the 19.21 section to see the following steps (but of course you are installing 19.22, not 19.21, so you don’t need to pay attention to that section, right?):
Single-instance Oracle Database (19.21):
Set the environment variable CV_ASSUME_DISTID to OL8 ($export CV_ASSUME_DISTID=OL8).
Unzip the 19.3.0.0.0 Oracle Database gold image.
Copy the OPatch utility version 12.2.0.1.40 or later from My Oracle Support patch 6880880 by selecting the 19.0.0.0.0 release.
That’s quite a bit different than the 19.22 section that says “No additional patches are required for installing Oracle Database 19c Release 19.22 on Oracle Linux 9 or Red Hat Enterprise Linux 9“.
Having just done a lot of testing of this on Oracle Linux 9, here’s what (in my opinion) the release notes should actually say in the Single-instance Oracle Database (19.22) section:
Single-instance Oracle Database (19.22):
Unzip the 19.3.0.0.0 Oracle Database gold image to your ORACLE_HOME location (for example /u01/app/oracle/product/19.0.0.0/dbhome_1).
Download the OPatch utility version 12.2.0.1.40 or later from My Oracle Support patch 6880880 by selecting the 19.0.0.0.0 release. $ cd /u01/app/oracle/product/19.0.0.0/dbhome_1 $ rm -rf OPatch $ unzip -q /usr/local/src/oracle/patch_downloads/p6880880_122010_Linux-x86-64.zip
With 19.22 you don’t need to modify the $ORACLE_HOME/cv/admin/cvu_config file or export the CV_ASSUME_DISTID environment variable to get the install to work correctly.
Even though you can now select the 19.0.0.0.0 “Release” of OPatch, you’ll actually get a version that is 12.X (see image below).
The OJVM patch is optional, but I like to see my opatch lspatches command look very clean (see below).
On Tuesdays Cary Millsap runs a meeting for Method-R customers to chat about Oracle stuff. Often he has something prepared, but this Tuesday he didn’t. Doug Gault decided to share an image that finally helped him get his head around the ANSI SQL syntax. Doug has been around the Oracle world for a long time but he’s always been able to work with Oracle proprietary SQL so he never really learned the ANSI SQL syntax. Recently he got assigned to a project where ANSI SQL is mandated so he had to get everything straight in his head. He shared an image that he had created from some training and we all took a look at it. Me, being me, I immediately jumped in with what I thought would be improvements to the image. I was challenged to come up with a better image, and so, I created the below.
My hope is that this will help some folks move away from the horrible (in my opinion) Oracle propriety SQL syntax to the totally awesome ANSI SQL syntax. I think the Oracle syntax is horrible because where clauses in queries end up doing two things; joining table AND filtering rows. With the ANSI syntax, join clauses join tables and where clauses only filter rows.
A note on the above: I used the preferred USING syntax to join tables for the ANSI queries:
join using (deptno)
instead of the ON syntax to join tables
join on e.deptno = d.deptno
I believe this is easier to read and understand and, in general, less code is better code, and this is smaller. If you use the USING syntax just note that you no longer associate the column with one table or another in the other clauses (like SELECT or WHERE) but instead leave it unconstrained. For example:
select deptno, e.ename, d.dname from emp e join dept d using (deptno) where deptno > 20;
If you were to qualify the DEPTNO column in either the select clause or the where clause (d.deptno for example) you’d get an ORA-25154: column part of USING clause cannot have qualifier message.
I’ve been working on an upgrade of Oracle Database on Windows. Despite working with Oracle Database for over 30 years, I really never spent a whole lot of time working on a Windows server. Unix, Solaris, Linux, heck even AIX, oh yeah. Windows, not so much.
While attempting to patch a brand new software-only install of Oracle 19c from the original 19.3 up to 19.21 I kept on getting UtilSession failed: Prerequisite check “CheckActiveFilesAndExecutables” failed during my opatch apply. It appeared that the JDK home in my ORACLE_HOME was in use. Of course, this didn’t make any sense since there wasn’t anything running out of this home.
Here’s what I was seeing:
Following active files/executables/libs are used by ORACLE_HOME: c:\app\oracle\product\19.0.0.0\dbhome_1
c:\app\oracle\product\19.0.0.0\dbhome_1\jdk\bin\java.exe
c:\app\oracle\product\19.0.0.0\dbhome_1\jdk\jre\bin\java.dll
c:\app\oracle\product\19.0.0.0\dbhome_1\jdk\jre\bin\management.dll
c:\app\oracle\product\19.0.0.0\dbhome_1\jdk\jre\bin\msvcr100.dll
c:\app\oracle\product\19.0.0.0\dbhome_1\jdk\jre\bin\net.dll
c:\app\oracle\product\19.0.0.0\dbhome_1\jdk\jre\bin\nio.dll
c:\app\oracle\product\19.0.0.0\dbhome_1\jdk\jre\bin\server\jvm.dll
c:\app\oracle\product\19.0.0.0\dbhome_1\jdk\jre\bin\verify.dll
c:\app\oracle\product\19.0.0.0\dbhome_1\jdk\jre\bin\zip.dll
c:\app\oracle\product\19.0.0.0\dbhome_1\jdk\jre\lib\ext\cldrdata.jar
c:\app\oracle\product\19.0.0.0\dbhome_1\jdk\jre\lib\ext\localedata.jar
c:\app\oracle\product\19.0.0.0\dbhome_1\jdk\jre\lib\ext\zipfs.jar
c:\app\oracle\product\19.0.0.0\dbhome_1\jdk\jre\lib\jsse.jar
c:\app\oracle\product\19.0.0.0\dbhome_1\jdk\jre\lib\rt.jar
c:\app\oracle\product\19.0.0.0\dbhome_1\jdk\bin\java.exe
c:\app\oracle\product\19.0.0.0\dbhome_1\jdk\jre\bin\java.dll
c:\app\oracle\product\19.0.0.0\dbhome_1\jdk\jre\bin\management.dll
c:\app\oracle\product\19.0.0.0\dbhome_1\jdk\jre\bin\net.dll
c:\app\oracle\product\19.0.0.0\dbhome_1\jdk\jre\bin\nio.dll
c:\app\oracle\product\19.0.0.0\dbhome_1\jdk\jre\bin\verify.dll
c:\app\oracle\product\19.0.0.0\dbhome_1\jdk\jre\bin\zip.dll
c:\app\oracle\product\19.0.0.0\dbhome_1\jdk\jre\bin\server\jvm.dll
c:\app\oracle\product\19.0.0.0\dbhome_1\jdk\jre\lib\jsse.jar
c:\app\oracle\product\19.0.0.0\dbhome_1\jdk\jre\lib\rt.jar
c:\app\oracle\product\19.0.0.0\dbhome_1\jdk\jre\lib\ext\cldrdata.jar
c:\app\oracle\product\19.0.0.0\dbhome_1\jdk\jre\lib\ext\localedata.jar
c:\app\oracle\product\19.0.0.0\dbhome_1\jdk\jre\lib\ext\zipfs.jar
Why were they in use? Why were they listed twice? What Windows process had a lock on them? I couldn’t figure it out.
Time for drastic measures. I downloaded IObit’s Unlocker program (use at your own risk) and used it to look at the files. It was showing no locks on any of those files, but I went ahead and unlocked them anyway. Of course this was it. This had to fix it… Nope! Still broken.
In desperation, I reached out to Oracle Support. And that’s when it finally happened for me. Support generated this:
My Oracle Support has performed a Knowledge search using your Service Request details (Summary, Error codes, Product) and located the following documents which may be related to your issue.
Search Results
====================================================================================
99% - Doc ID 2507120.1 Opatch 11.2.0.3.20 : Applying PSU/Windows BP fails with:'To run in silent mode, OPatch requires a response file for Oracle Configuration Manager (OCM)', 'error code = 73'
99% - Doc ID 1475147.1 OPatch - Failed to load the patch object. Possible causes are: OPatch failed with error code = 73 LsInventorySession failed:
99% - Doc ID 2950970.1 opatch apply fails with Error:" Prerequisite check "CheckActiveFilesAndExecutables" failed" on $oracle_home/jdk files
99% - Doc ID 1472242.1 Applying Patch Fails as Copy Failed for crsctl.bin as EM dbconsole was not Stopped
99% - Doc ID 2978449.1 "opatch util verify" reports OUI-67073:UtilSession failed: Files are not updated completely & OUI-67124:Files check failed: Some files under oracle_home are not patched , after applying RU Patches
Wait… That third Doc ID looks exactly like my error. I took a look and started reading and while it didn’t match my situation, it did mention the problem. Someone had downloaded opatch for the wrong platform. Could I really have done that? You betcha! (been watching a lot of Fargo with my wife, and “Minnesotan” is very catchy).
I got a new version of OPatch, this time for Windows instead of for Linux and you know what? Everything worked just fine.
In over 30 years of using Oracle Support, this is the first time I’ve ever had the “Oracle Support did a search and we found these documents” actually have the solution to my problem!
It appears that the physical contents of the cwallet.sso and ewallet.p12 files changed enough between Oracle 11 and Oracle 19 that the Oracle 19.15 (19.21 was also tested) binaries no longer liked the physical contents of the TDE wallet files even though the logical contents were correct. Overwriting the existing contents of the old “Oracle 11 style” wallet files with the Oracle 19 binaries with the exact same logical contents allowed the database to work as expected. This happened somewhere between Oracle 19.8 (old Oracle 11 style wallet works fine) and Oracle 19.15.
The Problem
Recently we had an interesting situation where a database that had been upgraded over the years, from Oracle 10 to eventually Oracle 19.8, wouldn’t upgrade to 19.15 cleanly. Once the ORACLE_HOME binaries had been updated to 19.15 (datapatch had yet to be applied) the database would only open in restricted mode and the following message would appear in the alert logs:
ALTER DATABASE OPEN detects that an encrypted tablespace has been restored but the database key has not been activated, or the database has been flashback'ed prior to first set key of the master key (pdb 0). Database is open in RESTRICTED MODE only. Please select the latest master key from V$ENCRYPTION_KEYS and execute ADMINISTER KEY MANAGEMENT USE KEY command, and restart the database
We could force the database to open in read write, and then run datapatch and everything would appear to work correctly, but this seemed kind of buggy because any bounce of the instance would still cause the database to open in restricted mode with the same error message in the alert log and we’d have to repeat the force to read write mode again.
This behavior was very strange because there was only a single TDE (Transparent Data Encryption) key in the wallet and it was obviously working just fine with the Oracle 19.8 binaries. However, if you looked into the data dictionary, you’d notice that the creation date and activation date in the old wallet were both NULL and there was also a NO value for masterkey_activated in the data dictionary even though the master key was very obviously activated since we could read and write to encrypted tablespaces just fine.
Some Details
The database name is going to be orcl, and it will be a standalone database, not a pluggable database in a container database.
The database was created a long time ago as an Oracle 10 database, upgraded to Oracle 11 at which point the transparent data encryption wallet was added and an encrypted tablespace was created using Oracle 11.1 binaries (which was when TDE for tablespaces was first introduced).
The database has an encrypted tablespace with a bunch of tables in it.
The database has a TDE wallet (located here: /u01/app/oracle/admin/orcl/tde_wallet) with the two important files: ewallet.p12 which, of course, contains the actual TDE key and is secured with a password (oracle_4U which, of course, is a bad TDE wallet password because you want your TDE keys file to be pretty secure), and the cwallet.sso file which contains an encrypted version of the oracle_4U password needed to read the ewallet.p12 file). The important detail for these two files is that they were created with Oracle 11.1 binaries which appears to have less information in them than if they were created with the Oracle 19 binaries.
The database is open and the wallet is open with a wallet_type of AUTOLOGIN.
All SQL commands were entered using a bequeath connection on the database server (sqlplus / as sysdba).
Almost certainly this bug exists because the v$encryption_keys view has a NULL for activation_time and the v$database_key_info view has NO as the value for masterkey_activated. These values appear to be read directly from the ewallet.p12 file and it appears that the Oracle 11.1 binaries never set those values in the ewallet.p12 file.
The Fix
For some operations with a TDE wallet, it appears you need to have a password based wallet type and not an auto_login based wallet type. Since our database is currently open with an auto login wallet, we’ll close the wallet and re-open it as a password based wallet. You may not need to move the old cwallet.sso file, but we moved the cwallet.sso file to old.cwallet.sso before we did the below command. The DBA had backed out the 19.15 binaries, so all of the below commands were done with the 19.8 binaries.
SQL> administer key management set keystore close;
We didn’t need to supply a password when closing the wallet because it is currently an autologin wallet.
Next, we’ll open the wallet using a password.
SQL> administer key management set keystore open identified by oracle_4U;
A query against v$encryption_wallet will now show a status of OPEN and a wallet_type of PASSWORD. We can now modify the wallet to contain the exact same logical contents that it currently contains. We’ll first find the key that is currently in use. In our case there was only a single key since the key had never been rotated. If you’ve rotated keys in the past, make sure to choose the currently activated key which is the one in v$encryption_keys with the highest creation_time or activation_time. Interestingly with the old 11.1 wallet, both these values were NULL, which is almost certainly why this bug exists in the first place (apparently, even Oracle can make mistakes with NULLs!).
SQL> select key_id, creation_time, activation_time from v$encryption_keys;
Now, using the key from above, we’re going to ‘update’ the wallet to use the exact same key that it’s already using. Note that you’ll enclose the key in ticks since it’s a literal value.
SQL> administer key management use key 'TheKey_IDValueFromTheQueryAbove' identified by oracle_4U with backup;
The ‘with backup’ clause automatically saves the previous version of the ewallet.p12 file and renamed it to include the current date and time. It was at this point that we noticed that the new ewallet.p12 file which contained the exact same key as the older ewallet.p12 file had grown in size. On our system, we went from an old file size of 1573 bytes to new file size of 2987 bytes.
Additionally, a query against v$encryption_keys showed that our previously NULL activation_time was now set to the time the previous command was run. And v$database_key_info now had the correct value of YES for masterkey_activated.
Our next step was to recreate the cwallet.sso file using the newly created ewallet.p12 file.
SQL> administer key management create auto_login keystore from keystore '/u01/app/oracle/admin/orcl/tde_wallet' identified by oracle_4U;
If you check the file size of the new cwallet.sso file (remember, we had moved our old one before so we could open the wallet with a password) against the old file you should notice that the new file is larger than the old one even though, again, it contains the exact same logical contents as the previous file (the encrypted oracle_4U password). On our system the old cwallet.sso file was 1883 bytes and the new cwallet.sso file was 3032 bytes.
Now a query against v$encryption_wallet still shows us that the wallet type is PASSWORD. Interestingly we can change this to AUTOLOGIN while keeping the wallet open with the following command.
SQL> administer key management set keystore close identifed by oracle_4U;
One would sort of expect this to actually close the wallet, but instead, it doesn’t close the wallet, it just updates the wallet_type column of v$encryption_wallet from PASSWORD to AUTOLOGIN.
At this point, one of the DBAs noticed that the fully_backed_up column in v$encryption_wallet was still NO. This is because although we had backed up our previous wallet key file, we had yet to back up the current version of the wallet key file (remember, this is the ewallet.p12 file). Just for fun (or was it just to be pedantic?) we did that next.
SQL> administer key management backup keystore force keystore identified by oracle_4U;
While logically our wallet file contents hadn’t really changed at all (one key in the ewallet.p12 and one password in the cwallet.sso), the updated wallet files created with the 19.8 binaries allowed our upgrade from 19.8 to 19.15 to go exactly as expected and we didn’t get a repeat of the database opening in restricted mode, almost certainly because the activation_time in the ewallet.p12 file for the key was no longer NULL.
One of my students at ACC suggested that I put this document up for sale on Amazon.
Instead I’ve decided to do the following:
Please consider opening an account on Kiva.Kiva is a non-profit organization with a mission to connect people through lending to alleviate poverty. Leveraging the internet and a worldwide network of microfinance institutions, Kiva lets individuals lend as little as $25 to help create opportunity around the world.If you use the below link you can get $25 to lend to others to get you started. I’ll also get $25 to lend to others. Technically the $25 to lend is a limited time offer, so you and I may not get this ‘bonus’ money to lend.http://www.kiva.org/invitedby/richard4068
If you’d really like to send along a direct donation, PayPal can be used to send direct donations to installguide@iinformation.com.
I made this document because I’ve seen folks struggle with some of the concepts in the document. Of course this document wasn’t created in a vacuum. Various blogs, the Oracle forums, a ton of Google searches and a lot of trial and error went into this document. To those that helped me in some way with the content in this document, thanks!
Select Devices -> Install Guest Additions… from the VirtualBox menu. You’ll have to use the right Ctrl key to release the mouse to do so.
Click Cancel.
Right-click the desktop and select Open in Terminal.
Type the following commands, pressing Enter after each.
su -
root (at the Password: prompt)
export KERN_DIR=/usr/src/kernels/`uname –r`
cd /media/V [TAB]
./VBoxLinuxAdditions.run
Note:
Those are back ticks which are probably next to the 1 key on your keyboard, not forward ticks in the export command. The Tab key will autocomplete commands for you. When we type cd /media/V the shell will complete the name of the Virtualbox Guest Additions CD for you. It’s much easier than typing it. If you’d like to use the Tab key to autocomplete the second command, you’ll have to type up to the L in Linux before it will work because there are other commands in that directory that start with VBox. You can always hit the Tab key twice to see what the shell will be able to autocomplete based on your search path.
You should see output with all green OKs.
Note:
If you get a failed message it’s most probably because your kernel doesn’t match the kernel sources which usually means that your yum update kernel-uek-devel command from earlier didn’t work correctly. Recheck your commands from Step 9 to make sure you did everything correctly.
Select System -> Log out oracle… from the menu.
Click Log Out.
Press Enter and type oracle in the Password field.
Right click the Guest Additions CD and select Eject.
Your window should now resize to fill the whole screen!
Note:
We could actually do this step a lot earlier and get the nice widescreen if we want. It was just a bit harder to do screen captures for me so I stuck this at the end. This must be done AFTER the reboot in step 18 though…
Cars and Code 