I’m a bit of a database pureist. I believe that if you are building a system that will capture information for a business or organization, and that information matters to the business, then you should build the tables that will support the insert, update, and delete of information in third normal form (at least, there are additional normal forms) and the best way to figure out how those tables should be built is to work directly with the business by building an Entity Relationship Diagram (note: a Table Diagram is NOT an ERD!) and getting business by-in before you start creating tables.
A frequent business requirement is “We’d like to know by who and when information was created or updated.” Traditionally, we have added four columns to our tables to do this: CREATED, CREATED_BY, UPDATED, and UPDATED_BY. As an aside (I have a feeling there will be a lot of them in this post), I despise when folks do CREATED_DATE, and UPDATED_DATE. You don’t do NAME_VARCHAR2!
Oracle Quick SQL, built into Oracle APEX, builds these same traditional columns on your tables if you ask it to, and then they build a BUI (before update or insert) trigger on the table. Note, I think that a BUI trigger is “wrong” since we’ve had compound triggers in the database since Oracle 11g, and that every table that has normal triggers (basically, if you are not using Edition Based Redefinition) should only have a single trigger, and it should be a compound trigger.
That said, quick SQL builds a trigger that looks like this:
create or replace trigger employee_biu
before insert or update
on employee
for each row
begin
if inserting then
:new.created := sysdate;
:new.created_by := coalesce(sys_context('APEX$SESSION','APP_USER'),user);
end if;
:new.updated := sysdate;
:new.updated_by := coalesce(sys_context('APEX$SESSION','APP_USER'),user);
end employee_biu;
/
As a purist, I have always hated that the trigger always populates the UPDATED and UPDATED_BY columns even on the first insert. To me, those columns should only be populated if the row was actually updated, since that is the name of the columns. By populating them all the time, if you want to answer the question “How many rows have actually been updated?”, you will need to compare (let’s use a table with a hundred million rows) a hundred million values:
select count(*)
from sometable
where created != updated;
In addition, you are going to store extra stuff that you don’t really need. Instead, I believed (notice the past tense!), those columns should be nullable and should only be populated if you actually update the column. A hundred million row table with only seven updated rows and an index on updated would be worlds faster than a table that stores all these needless UPDATED and UPDATED_BY by values.
Now, because we have a nullable column, we do introduce the “issues” that come along with a nullable column. You’ll need to use NVL or, even better, COALESCE every time you look at the column, and if you forget, you can end up with bugs. The number of bugs that nulls give us in systems is pretty large. But, because you did things “right” (you didn’t needlessly populate a column that didn’t need to be populated), you would be aware of this, and you wouldn’t fall prey to those bugs.
However, a recent conversation with Anton Nielsen convinced me to update my perspective.
At first, Anton tried to make the argument that every insert is an update too. I wasn’t buying that argument at all. He, of course, brought up all the issues with a nullable column and indexes on virtual columns (Why not add a virtual column that is a COALESCE(UPDATED,CREATED) as LAST_TOUCHED?), etc. But the thing that really convinced me was this:
“In all my years of building applications the business people always ask ‘Who touched this row last?’, they never ask ‘How many rows were touched after they were created?’.”
Anton Nielsen
Maybe those columns should have been called LAST_TOUCHED and LAST_TOUCHED_BY (and, indeed, if you are going to populate them on insert, they really should be), but we can fix this by adding a comment to the UPDATED and UPDATED_BY columns:
comment on column employee.updated is 'Populated during row creation and whenever the row is updated. Effectively that makes this column the LAST_TOUCHED column.';
comment on column employee.updated_by is 'Populated during row creation and whenever the row is updated. Effectively that makes this column the LAST_TOUCHED_BY column. This is set to the current APEX user or the database user depending on the context.';
Indexes are now owned by the table owner rather than the DBA running the script.
All missing indexes are created in a single tablespace named “missing_foreign_key_indexes”. Obviously, this tablespace needs to exist for the statements to work. You might want to change this clause to use “your user’s index tablespaces”, and you should drop this if you are on Oracle Autonomous on Oracle Cloud, since you only get one tablespace for your stuff and everything will automatically go to it.
There are suggestions for making shorter index names if needed/wanted.
The “local” clause was added for partitioned tables.
You can optionally require validated foreign keys, valid indexes, and visible indexes. Today’s invalid or invisible index can turn into tomorrow’s index, so I left the default to show all indexes.
We are now sorting numerically.
Change “select *” into “select username” for the not in clause that eliminates users.
Added a where clause to find missing indexes by default.
Again, here’s the formatted code that doesn’t look great, but if you copy it, it should be formatted as it is in the above picture.
-- Created by Rich Soule of Talan's Oracle Group in collaboration with Lance Eaton.
--
-- Notes:
-- * Can ignore unusable/invisible/bitmap indexes; prefers NORMAL (and function-based NORMAL) b-trees
-- * Emits LOCAL for partitioned child tables
-- * change 'missing_foreign_key_indexes' tablespace to taste (or drop on Autonomous)
-- * if you want to shorten the index names, you can replace the two lines with comments below with something like: 'missing_fk_index'||rownum
-- * designed to be run by a DBA with access to the DBA views, but can also be run by a regular user by replacing the dba_ views with
-- all_ views or user_ views (search and replace dba_ with all_ or user_)
with owner_exclusion_list as ( select username from dba_users where oracle_maintained = 'Y'
union all select 'ORDS_METADATA' from dual
union all select 'ORDS_PUBLIC_USER' from dual )
, constraint_column_list as ( select owner
, table_name
, constraint_name
, listagg(column_name, ', ') within group (order by position) as constraint_column_list
from dba_cons_columns
join dba_constraints using (owner, table_name, constraint_name)
where constraint_type = 'R'
and status = 'ENABLED'
-- and validated = 'VALIDATED' -- uncomment to require validated fks
and owner not in (select username from owner_exclusion_list)
group by owner, table_name, constraint_name )
, index_column_list as ( select di.owner
, di.table_name
, di.index_name
, listagg(dic.column_name, ', ') within group (order by dic.column_position) as index_column_list
from dba_indexes di
join dba_ind_columns dic on (dic.index_owner = di.owner and dic.index_name = di.index_name)
where di.owner not in (select username from owner_exclusion_list)
-- and di.status = 'VALID' -- uncomment to require valid indexes
-- and di.visibility = 'VISIBLE' -- uncomment to require visible indexes
and di.index_type in ('NORMAL','FUNCTION-BASED NORMAL')
group by di.owner, di.table_name, di.index_name )
, foreign_key_index_query as (select decode(icl.table_name, null, 'Missing', 'Exists') as index_existence
, dt.num_rows as last_analyzed_row_count_number
, to_char(dt.num_rows, '999,999,999,999,999') as last_analyzed_row_count
, dt.last_analyzed
, ccl.owner as table_owner
, ccl.table_name
, ccl.constraint_name as foreign_key_name
, ccl.constraint_column_list as foreign_key_column_list
, coalesce(icl.index_name, '*** Missing Index ***') as index_name
, coalesce(icl.index_column_list, '*** Missing Index ***') as index_column_list
, decode(icl.table_name, null,'create index "'||ccl.owner||'".'||
lower(ccl.table_name||'_foreign_key_index_on_'|| -- Shorten these two lines to have
replace(replace(ccl.constraint_column_list,',','_'),' '))|| -- smaller index names
' on "'||ccl.owner||'"."'||ccl.table_name||'"('||
replace(replace(ccl.constraint_column_list,',','","'),' ')||')'||
decode(dt.partitioned, 'YES', ' local', '')||
' tablespace missing_foreign_key_indexes;'
,'*** supporting index already exists ***') as create_index_ddl
from constraint_column_list ccl
join dba_tables dt on (dt.owner = ccl.owner and dt.table_name = ccl.table_name)
left join index_column_list icl on ( icl.owner = ccl.owner and icl.table_name = ccl.table_name
and icl.index_column_list like ccl.constraint_column_list || '%' ))
select index_existence
, last_analyzed_row_count
, last_analyzed
, table_owner
, table_name
, foreign_key_name
, foreign_key_column_list
, index_name
, index_column_list
, create_index_ddl
from foreign_key_index_query
where index_existence = 'Missing' -- comment to see both Exists & Missing
order by last_analyzed_row_count_number desc nulls last, table_owner, table_name, foreign_key_column_list;
TLDR: I’ve been running into an issue where my Oracle Base Database on Oracle Cloud running Oracle 23ai appears to be ‘automatically locking accounts at random times’. To potentially prevent one of these random locks from stopping APEX from working, try this unsupported but working adjustment to your APEX_PUBLIC_USER account: alter user apex_public_user account unlock no authentication;
The background: My database is what is currently called on Oracle Cloud, an “Oracle Base Database”. Unlike the Oracle Autonomous Database, where you get a pluggable database in a container database that someone else manages, here you get full access to the database file system and full access to everything about the database (root container, full sys user access, etc.). I say “currently called” because we actually put this database on Oracle Cloud way back in Sept of 2021. That’s when this database was migrated from an on-premises Oracle Database Appliance to Oracle Cloud.
Oracle Cloud has changed a bunch since then, but overall, I couldn’t be happier with the migration. With Oracle Base Database, you “let” Oracle manage the software locations and database locations (Oracle uses Automatic Storage Management for the database and fast recovery area storage). Patches and upgrades (we started with 19c, but are now on 23ai) are straightforward and controlled at your own pace, implemented by simple choices in the Oracle Cloud UI.
For many years, this database “just worked”. The business ran its processes, and the APEX application we built for them just did its thing. On July 22nd, I got a call from the business saying “APEX isn’t working”. When I went and looked, the APEX_PUBLIC_USER account was locked. This is strange because there wasn’t a reason for the account to be locked. Nobody did anything. The database profile for the APEX_PUBLIC_USER has a password life time of unlimited, so it wasn’t a profile thing. I unlocked the account, APEX started working again, and life was good. An investigation into the unified audit trail didn’t show anything. This was a “mystery”. Anyone in tech would agree that a mystery isn’t good.
On August 11th, I got the same call. Again, the APEX_PUBLIC_USER account was locked. I again unlocked it. This time I did a bigger investigation with a coworker. He’s been struggling with the same random locking behavior for the APEX_PUBLIC_USER in his DEV, TEST, and PROD environments for the last 4 months (he’s had many Oracle SRs open and closed on this while he’s been bounced around various teams within Oracle, and his random locks have happened much more frequently than mine). As we looked at things, we realized that there is an amount of correlation between database patches being applied and accounts getting locked. It’s not exact, but here are some of the queries that we looked at:
select cdb$name as container -- Awesome hidden colum on CDB_ views!
, target_build_description
, action_time
from cdb_registry_sqlpatch
order by action_time desc;
select username
, cdb$name as container
, lock_date
, last_login
, created
, cu.*
from cdb_users cu
order by cu.lock_date desc nulls last;
select cdb$name as container
, cp.*
from cdb_profiles cp
where resource_name = 'INACTIVE_ACCOUNT_TIME';
Obviously, if you don’t have access to the root container, you can change the above queries to use the DBA views in your own pluggable (or non-container) database if you eliminate the pdb_name column.
Something very interesting was that there were a LOT of accounts getting locked at the “same time”, but that time was different for different pluggable databases in the same container database.
I’ve got two “opportunities for future enhancement” logged against the APEX product and APEX documentation. This is the current slide in my latest (award-winning!) APEX & ORDS for DBAs and System Admins presentation (an earlier version of this can be found on YouTube).
A while back, I had shared that with my coworker, and he had implemented it in his dev and test environment:
alter user apex_public_user account unlock no authentication;
Since implementing this, he has not had the locking issue for the APEX_PUBLIC_USER his 23ai environments.
I went ahead and implemented this in DEV, TEST, and PROD. We’ll see what happens, and if any of the SRs my coworker has filed with Oracle Support get an actual resolution, I’ll update this post!
I help a team manage a bunch of Oracle servers that use an OFA layout with the Oracle software and their database on /u01 and the fast recovery area on /u02.
These are all Linux servers, so we’ve installed rlwap so that we can reverse grep (CTRL-r) through our command history in the command line tools and use the up arrow to cycle through previous commands.
With the following in my .bashrc file, our lives as DBAs are much easier.
# User specific aliases and functions
# rlwrap for Oracle command line tools
alias adrci='rlwrap adrci'
alias asmcmd='rlwrap asmcmd'
alias expdp='rlwrap expdp'
alias impdp='rlwrap impdp'
alias rman='rlwrap rman'
alias sqlplus='rlwrap sqlplus'
# Quick Navigation
alias home='cd $ORACLE_HOME'
alias audit='cd $ORACLE_BASE/admin/$ORACLE_SID/adump'
alias alert='cd $ORACLE_BASE/diag/rdbms/$ORACLE_SID/$ORACLE_SID/trace'
alias trace='cd $ORACLE_BASE/diag/rdbms/$ORACLE_SID/$ORACLE_SID/trace'
alias log='cd $ORACLE_BASE/diag/rdbms/$ORACLE_SID/$ORACLE_SID/trace'
alias dbs='cd $ORACLE_BASE/dbs'
alias network='cd $(orabasehome)/network/admin'
alias admin='cd $ORACLE_BASE/admin'
alias diag='cd $ORACLE_BASE/diag'
alias oradata='cd $ORACLE_BASE/oradata'
alias fra='cd /u02/app/oracle/fast_recovery_area'
# Commands
alias opatch='$ORACLE_HOME/OPatch/opatch'
alias sql='/u01/app/oracle/product/19.0.0.0/dbhome_1/sqlcl/bin/sql'
alias pmon='ps -ef | grep pmon | grep -v grep'
alias oratab='cat /etc/oratab'
alias rmanc='rlwrap rman target / catalog /@rcat'
Some notes:
As I mentioned, the fast recovery area is on /u02 so the “fra” alias is hard coded to that location.
The “rmanc” (connect to the local database and the remote recovery catalog in one command) alias uses a SEPS wallet with the recovery catalog username and password aliased to “rcat”. A SEPS wallet allows bequeath connections to remote databases which means that you don’t have to put passwords into your scripts.
Why are there three aliases for the same location (“alert”,”trace”,”log”)? Different team members liked different aliases and why not?
The “network” alias is designed to work with both an old style read/write Oracle Software Home and the new Read Only Software Home.
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!
Cars and Code 