Upgrading ODB 23.26.1 to 23.26.2 and adding TCPS

Last week I was troubleshooting an Oracle AI 26 Database unified audit log project that required upgrading from 23.26.1 to 23.26.2  I had to iterate through the setup multiple times to make sure that I had the flow just right.  I did not know that stand alone Oracle AI 26 Databases use an out-of-place Gold Image release updates (RUs)  method rather than the traditional "opatch apply".  Given that this was new to me, I thought that I would helpful to write up my notes from that journey for my own edification but hopefully help others as well.  In addition, since this project was oriented around Entra ID integration with TCPS/TLS connections required been database client and server, those steps are included as well.

1. Make sure /etc/hosts entry is preceded by TLS cert FQDN:

grep hrdb /etc/hosts
10.0.0.33 hrdb.dbauthdemo.com hrdb.sub10241351260.odswest.oraclevcn.com hrdb

2. Make sure hostname is fully qualified using the TLS cert FQDN:

sudo hostnamectl set-hostname $(hostname -f)

3. Upload Oracle AI Database 23.26.1 (V1054592-01.zip from https://edelivery.oracle.com) and 23.26.2 (p39099680_230000_Linux-x86-64.zip  from https://updates.oracle.com/download/39099680.html) to /u01/bits:

ls -1 /u01/bits
p39099680_230000_Linux-x86-64.zip (23.26.2)
V1054592-01.zip (23.26.1)

4.  Extract and install software and setup 19c database server:

Note: manage_db.sh in OUD POC Kit at https://github.com/oudlabs/oudpockit

/u01/manage_db.sh setup --dbv 26ai

5. Set LOCAL_LISTENER because I’m running 26ai on default port and 19c on alternate port in my lab.

$ORACLE_HOME/bin/sqlplus / as sysdba
SHOW PARAMETER LOCAL_LISTENER;

ALTER SYSTEM SET LOCAL_LISTENER='(ADDRESS=(PROTOCOL=TCP)(HOST=hrdb.dbauthdemo.com)(PORT=1521))' SCOPE=BOTH;

ALTER SYSTEM REGISTER;

6. Update 26 AI database to most recent release update (23.26.2) using the out-of-place Gold Image release update method found in the patch README file:

unzip -qo -d /u01/app/oracle/26ai/dbhome_2 /u01/bits/p39099680_230000_Linux-x86-64.zip

/u01/app/oracle/26ai/dbhome_2/runInstaller -silent -setupDBHomeAs /u01/app/oracle/26ai/dbhome_1

sudo /u01/app/oracle/26ai/dbhome_2/root.sh

/u01/app/oracle/26ai/dbhome_2/bin/dbca -silent -moveDatabase -sourceDB hrdb

7. Alter db26ai.env to change dbhome_1 (23.26.1 home) to dbhome_2 (23.26.2 home):

cat /u01/cfg/db26ai.env 

export ORACLE_BASE="/u01/app/oracle/26ai"
export ORACLE_HOME="/u01/app/oracle/26ai/dbhome_2"
export TNS_ADMIN="/u01/app/oracle/26ai/dbhome_2/network/admin"
export ORACLE_SID="hrdb"
export ORACLE_INSTANCE="/u01/app/oracle/26ai/dbhome_2"
export ORACLE_UNQNAME="hrdb"
export WALLET_ROOT="/u01/app/oracle/26ai/wallet_root"

8. Re-load the db26ai env and restart database:

. /u01/cfg/db26ai.env
/u01/manage_db.sh stop --dbv 26ai
/u01/manage_db.sh start --dbv 26ai

9. Set wallet_root and then lookup PDB1 GUID:

. /u01/cfg/db26ai.env
$ORACLE_HOME/bin/sqlplus / as sysdba
show parameter wallet_root;

alter system set wallet_root='/u01/app/oracle/26ai/wallet_root' scope=spfile;

select guid from v$containers where name = 'PDB1';

GUID
--------------------------------

512D5CFBAB67AA47E0632100000A0A24

10. Make tls directories (replace PDB GUID with value you retrieve from previous step):

mkdir -p $WALLET_ROOT/tls $WALLET_ROOT/<PDB_GUID>/tls

11. Extract wallet_root from backup with:

unzip -qo -d "$WALLET_ROOT/.." /u01/hrdb_wallet_root.zip

12. Copy wallet to PDB1 tls directory (replace PDB GUID with retrieved PDB GUID value):

rsync -Hav $WALLET_ROOT/tls/. $WALLET_ROOT/<PDB_GUID>/tls/.

13. Display wallet root contents to make sure they are valid:

$ORACLE_HOME/bin/orapki wallet display -nologo -complete  -wallet $WALLET_ROOT/tls

14. Create or update sqlnet.ora:

cat $TNS_ADMIN/sqlnet.ora
SQLNET.AUTHENTICATION_SERVICES = (TCPS,NTS,BEQ)

SSL_CLIENT_AUTHENTICATION = FALSE

15. Create or update listener.ora:

cat $TNS_ADMIN/listener.ora
SSL_CLIENT_AUTHENTICATION = FALSE


LISTENER =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = TCP)(HOST = hrdb.dbauthdemo.com)(PORT = 1521))
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
      (ADDRESS = (PROTOCOL = TCPS)(HOST = hrdb.dbauthdemo.com)(PORT = 2484))
      (SECURITY=(WALLET_LOCATION=/u01/app/oracle/26ai/wallet_root/tls))
    )
  )

16. Create or update tnsnames.ora:

cat $TNS_ADMIN/tnsnames.ora
HRDB =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = hrdb.dbauthdemo.com)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = hrdb)
    )
  )

HRDB_SSL =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCPS)(HOST = hrdb.dbauthdemo.com)(PORT = 2484))
    (SECURITY=(WALLET_LOCATION=/u01/app/oracle/26ai/wallet_root/tls))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = hrdb)
    )
  )

pdb1 =
  (DESCRIPTION=
    (ADDRESS=(PROTOCOL=TCP)(HOST=hrdb.dbauthdemo.com)(PORT=1521))
    (CONNECT_DATA=
      (SERVER = DEDICATED)
      (SERVICE_NAME=pdb1)
    )
  )

pdb1_ssl =
  (DESCRIPTION=
    (ADDRESS=(PROTOCOL=TCPS)(HOST=hrdb.dbauthdemo.com)(PORT=2484))
    (SECURITY=(WALLET_LOCATION=/u01/app/oracle/26ai/wallet_root/tls))
    (CONNECT_DATA=
      (SERVER=dedicated)
      (SERVICE_NAME=pdb1)
    )
  )

17. Update /etc/oratab hrdb entry because I’m running 19c and 26ai on two separate home’s on the same host:

From:

hrdb:/u01/app/oracle/26ai/dbhome_1:N

To:

hrdb26ai:/u01/app/oracle/26ai/dbhome_2:N

18. Restart database server:

/u01/manage_db.sh stop --dbv 26ai
/u01/manage_db.sh start --dbv 26ai

19. Test that you can login to each alias in tnsnames.ora:

$ORACLE_HOME/bin/sqlplus system/Oracle123@hrdb

$ORACLE_HOME/bin/sqlplus system/Oracle123@hrdb_ssl

$ORACLE_HOME/bin/sqlplus system/Oracle123@pdb1

$ORACLE_HOME/bin/sqlplus system/Oracle123@pdb1_ssl

20. Configure PDB1 for Entra ID integration and add necessary users and roles:

$ORACLE_HOME/bin/sqlplus system/Oracle123@pdb1_ssl

ALTER SYSTEM SET IDENTITY_PROVIDER_TYPE=AZURE_AD SCOPE=BOTH;
SHOW PARAMETER IDENTITY_PROVIDER_TYPE;
ALTER SYSTEM SET IDENTITY_PROVIDER_CONFIG =
'{ 
   "application_id_uri" : "https://dbauthdemo.com/16736175-ca41-8f33-af0d-4616ade17621",
   "tenant_id" : "7f4c6e3e-a1e0-43fe-14c5-c2f051a0a3a1",
   "app_id" : "16736175-ca41-8f33-af0d-4616ade17621"
}' SCOPE=BOTH;
SHOW PARAMETER IDENTITY_PROVIDER_CONFIG;

drop USER alldbusers;
CREATE USER alldbusers IDENTIFIED GLOBALLY AS 'AZURE_ROLE=pdb.users';
SELECT USERNAME, EXTERNAL_NAME from DBA_USERS where username='ALLDBUSERS';

drop ROLE entra_dba;
CREATE ROLE entra_dba IDENTIFIED GLOBALLY AS 'AZURE_ROLE=dba.role';
GRANT pdb_dba TO entra_dba;
SELECT EXTERNAL_NAME FROM DBA_ROLES WHERE ROLE = 'ENTRA_DBA';

drop ROLE dbsession;
CREATE ROLE dbsession IDENTIFIED GLOBALLY AS 'AZURE_ROLE=session.role';
GRANT CREATE SESSION TO dbsession;
SELECT EXTERNAL_NAME FROM DBA_ROLES WHERE ROLE = 'DBSESSION';

drop USER hrapp;
CREATE USER hrapp IDENTIFIED GLOBALLY AS 'AZURE_ROLE=hr.app';
GRANT CREATE SESSION, CONNECT, SELECT ANY TABLE, INSERT ANY TABLE, UPDATE ANY TABLE, DELETE ANY TABLE to hrapp;
SELECT EXTERNAL_NAME FROM DBA_ROLES WHERE ROLE = 'HRAPP';

21. Create TNS entry for PDB1 over TLS/SSL on database client (23.4 or newer).  Note that WALLET_LOCATION is set to SYSTEM, which means that the database client uses the client operating system's trust store to confirm the authenticity of the TLS certificate because the TLS certificate had been signed by a public certificate authority.

PDB1_26ai_ENTRA =
  (DESCRIPTION=
    (ADDRESS=(PROTOCOL=TCPS)(HOST=hrdb.dbauthdemo.com)(PORT=2484))
    (SECURITY=
      (SSL_SERVER_DN_MATCH=TRUE)
      (SSL_SERVER_CERT_DN="CN=hrdb.dbauthdemo.com")
      (WALLET_LOCATION=SYSTEM)
      (TOKEN_AUTH=AZURE_INTERACTIVE)
      (TENANT_ID=8d2aef2e-f5e0-46fc-86c2-82604520aea9)
      (CLIENT_ID=e5124a85-ac3e-14a4-f2ca-1ad635cf781a)
      (AZURE_DB_APP_ID_URI=https://dbauthdemo.com/16736175-ca41-8f33-af0d-4616ade17621)
    )
    (CONNECT_DATA=
      (SERVER=DEDICATED)
      (SERVICE_NAME=pdb1)
    )
  )

22. Test Entra ID integration authentication to the database. In this example, I use SQLcl 26.1 from my MacOS laptop.

sql -thin /@pdb1_entra

Initiating the connection to the database using the interactive OAuth2 flow pops up a browser window to Entra ID to login.  Here you either enter user email and password or select an existing user.

If you selected an existing user, the next step will be dictated by the Entra ID conditional access policies based on several factors including from where your connecting.  In my case, it just prompts for the user's password.

The default Entra ID tenancy conditional access policy requires a one-time PIN.

Upon successfully authentication, Entra ID returns an access token through the browser's localhost to the client, with which the client makes another call to Entra ID with the authorization token to request an access token.

Upon receipt of the access token, the database client sends the access token over a TLS encrypted connection to the databaase server.  The Oracle database server downloads the current set of keys from Entra ID and confirms the authenticity of the access token and returns the authenticated SQL prompt to the client application.  Here, I show the returned SQL prompt along with a few queries to show what it looks like for an authenticated user of a shared user schema.

I hope you find this helpful.

Blessings!