Oracle

Configuring Hybrid Dataguard and have standby on OCI without the need of advanced security license

This entry was posted in Cloud DataGuard OCI Oracle Security and tagged OCI tde on by

Hybrid Dataguard is a relatively new 19c feature that allows to have one side of a Dataguard configuration not encrypted. This is particularly useful when having a primary on premises and standby on a OCI, where tablespaces need to be encrypted. The main point is that it allows to spare a Advanced Security licenses on premises site.

Usages of Hybrid Dataguard can be during migration to the cloud or just as a high availability solution, when we have a small on-premises data center.

In the tutorial below I assume to have a primary database on premises without TDE, a we will create a standby in OCI with encryption enabled. The connection between on-premises and OCI is already configured and not explained.

(more…)

Oracle TDE – the basic information you need to know when encrypting the whole database

This entry was posted in Cloud Engineering Systems ExaCC Exadata OCI Oracle Oracle 19c and tagged OCI oracle 19c tde on by

TDE – Transparent Data Encryption – is the Oracle solution for protecting data at rest. This refers, protecting data that is stored in one file or one disk. This data will be encrypted. Only after the database is open and the wallet password is given, you can query the data and see it.

Recently I’ve been working more with ExaCC and migrating databases from on-premises to the Cloud. Took quite some days to get into TDE and sometimes confusing terms used in the documentation and on the web.

This post summarizes all the concepts to have in mind when working with Oracle encrypted databases, which is the default in the Cloud and Exadata systems.

(more…)

Commvault backup timeout and too many objects in Oracle recycle bin

This entry was posted in Oracle on by

Since a few weeks there was an error during backups of one of my clients databases. They use Commvault to trigger the backups.

In the background, for some reason, Commvault asks the primary instance for some information about the free space in the tablespaces, using the following query:

SELECT 'D,' || c.NAME || ':' || t.NAME || ':' || c.dbId  || ',' || d.STATUS || ',' || d.ENABLED || ',' || TO_CHAR(d.BYTES) || ',' || TO_CHAR(d.BYTES - NVL(ff.fbytes,0)) || ',' || TRIM(' ' FROM d.NAME) || ',' || TRIM(' ' FROM d.FILE#) AS PDB_TS_DF 
FROM v$datafile d, v$tablespace t, v$CONTAINERS c, (SELECT f.CON_ID CON_ID, f.file_id file_id, SUM(f.bytes) fbytes FROM CDB_FREE_SPACE f GROUP BY f.file_id,f.CON_ID) ff 
WHERE c.CON_ID = d.CON_ID and d.CON_ID = t.CON_ID and d.TS#=t.TS# AND ff.file_id (+)= d.FILE#  
ORDER BY PDB_TS_DF;

The important part is this one:

(more…)

New mandatory unified audit policy on 19.26

This entry was posted in Audit Oracle Oracle 19c Oracle 23ai on by

This feature was just backported from Oracle 23ai. The new ORA$MANDATORY audit policy was added with the Oracle 19.26 RU. This policy is not visible at UNIFIED_AUDIT_POLICIES or AUDIT_UNIFIED_ENABLED_POLICIES.

After patching the database to 19.26, then you see entries on UNIFIED_AUDIT_TRAIL:

SYS@CDB2.CDB$ROOT> select EVENT_TIMESTMAP, SYSTEM_PRIVILEGE_USED, ACTION_NAME 
from UNIFIED_AUDIT_TRAIL 
where UNIFIED_AUDIT_POLICIES='ORA$MANDATORY' 
order by EVENT_TIMESTMAP;

                  EVENT_TIMESTAMP     SYSTEM_PRIVILEGE_USED       ACTION_NAME
_________________________________ _________________________ _________________
02-FEB-2025 21:54:56.192982000    SYSDBA                    LOGON
02-FEB-2025 21:54:56.216549000    SYSDBA                    SELECT
02-FEB-2025 21:55:00.381577000    SYSDBA, ALTER DATABASE    ALTER DATABASE
02-FEB-2025 21:55:00.393882000    SYSDBA                    LOGOFF
...

The actions that are audited by ORA$MANDATORY policy are described on Oracle 23ai documentation.

What I find interesting, is that the “ALTER DATABASE MOUNT” during startup is audited, so we can have a good history of database startups.

(more…)

Warning: OPatchauto ignores disabled components – possible licensing issues

This entry was posted in OPatch Oracle Oracle 19c Patchset Update PSU on by

Since many years at my customer I’m using “opatchauto” to perform a out-of-place patching of Oracle Restart (GI+RDBMS).

My customer is concerned about database users using not licensed options, like partitioning. To avoid it, at the installation time the partitioning option is disabled using chopt, like described at Doc ID 948061.1.

Today during a check we noticed that Partitioning option was activated everywhere, which is not the client standard! We found out the origin of the problem was the out-of-place patching with “opatchauto”.

The big advantage of using “opatchauto” is that it allows easily either a single-step or a two-step Out-of-Place patching. We just write in a properties file the name of the new Oracle Homes and it does:

  • Clone current GI + RDBMS homes to new Homes (prepare clone)
  • Patches the new homes (prepare clone)
  • Stops GI and DBs (switch clone)
  • Switches GI and DBs from current homes to new Homes (switch clone)
  • Restart everything (switch clone)
  • Runs Datapatch on DBs if not standby (switch clone)

This allows to decrease the patching downtime without RAC to about 10 minutes, with the two-step (prepare clone + switch clone) operation.

Here the steps to reproduce de bug:

(more…)

Using AI to confirm a wrongly cabled Exadata switch – or how to fix verify_roce_cables.py script for Python3.

This entry was posted in Engineer Systems Exadata Oracle on by

One of the preparation steps when installing an Exadata X10M is to verify that the cabling of the RoCE switches is correctly done. The next step is to upgrade the Cisco switches with the latest firmware. During my intervention for Tradeware at the customer, the first didn’t work as the provided script is not compatible with Python3 and the latter complained about wrong cabling.

Here I show how studied the wrong cabling of the X10M switches and how I use Claude.ai (ChatGPT and other AI tools probably also work) to quickly fix the Python script provided by Oracle.

(more…)

The DBT-16051 when creating a standby database using DBCA is still around. 7 years after.

This entry was posted in DataGuard Oracle Oracle 19c on by

Sometimes I ask myself why some bugs are not solved. When looking for DBT-16071 we find a blog post from Frank Pachot from more than 7 years ago. He shows that with Oracle 12.2 you can “create” standby databases directly with dbca. But that the script does only a duplicate for standby and nothing more.

I decided to try with 19.22 to see how the situation evolved. It didn’t.

The first thing I got was a DBT-16051 error:

$ dbca -createDuplicateDB -gdbName anjodb01 -primaryDBConnectionString "anjovm01.local.wsl/anjodb01_s1.local.wsl" -sid anjodb01 -createAsStandby -dbUniqueName anjodb01_s2 -silent
Enter SYS user password:
*****
[FATAL] [DBT-16051] Archive log mode is not enabled in the primary database.
   ACTION: Primary database should be configured with archive log mode for creating a duplicate or standby database.

Quick check shows the primary is correctly in archivelog mode. The problem is the Easy Connect string. The string I gave “anjovm1.local.wsl/anjodb1_s1.local.wsl” works well on sqlplus, but not with dbca. There you need to specify the port, also when you are just using the default one:

$ dbca -createDuplicateDB -gdbName anjodb01 -primaryDBConnectionString "anjovm01.local.wsl:1521/anjodb01_s1.local.wsl" -sid anjodb01 -createAsStandby -dbUniqueName anjodb01_s2 -silent
Enter SYS user password:
*****
[WARNING] [DBT-10331] Specified SID Name (anjodb01) may have a potential conflict with an already existing database on the system.
   CAUSE: The specified SID Name without the trailing numeric characters ({2}) may have a potential conflict with an already existing database on the system.
   ACTION: Specify a different SID Name that does not conflict with existing databases on the system.
Prepare for db operation
22% complete
Listener config step
44% complete
Auxiliary instance creation
67% complete
RMAN duplicate
89% complete
Post duplicate database operations
100% complete

The warning DBT-10331 appears because I’ve a “anjodb02” in the same VM, and this could create a problem, as they share the prefix “anjodb”. I don’t expect on a single instance environment that to be a problem though.

And it starts the new standby in ‘read only’ mode, which requires adequate licenses. 

SQL> select name, db_unique_name, database_role, open_mode, dataguard_broker from v$database;

NAME      DB_UNIQUE_NAME                 DATABASE_ROLE    OPEN_MODE            DATAGUAR
--------- ------------------------------ ---------------- -------------------- --------
ANJODB01 ANJODB02_S2                  PHYSICAL STANDBY READ ONLY            DISABLED

For the moment, I’ll stay with my set of scripts which do the operations in the right way.

How to use Zabbix to monitor Oracle DB – a quick test run

This entry was posted in Oracle on by

Currently I’m testing different solutions for monitoring (mostly) Oracle databases. One solution in the shortlist is Zabbix.

Zabbix is open-source and currently has a quite active community helping out.

On Zabbix website there is a nice quick download and setup instructions:

I’ll use a Oracle Linux 9 VM under Windows WSL2 for installing Zabbix. For the exercise, I’ll configure it with PostgreSQL. The database installation step is missing. So, here are all the steps I’ve done.

(more…)

DBCA doesn’t like personal SQL*Plus prompt

This entry was posted in Oracle on by

The database creation (19c) using DBCA was hanging at 33%:

[ 2024-04-24 10:31:41.770 CEST ] Prepare for db operation
DBCA_PROGRESS : 8%
[ 2024-04-24 10:31:41.808 CEST ] Creating and starting Oracle instance
DBCA_PROGRESS : 11%
DBCA_PROGRESS : 12%
DBCA_PROGRESS : 17%
[ 2024-04-24 10:32:11.412 CEST ] Creating database files
DBCA_PROGRESS : 18%
DBCA_PROGRESS : 25%
[ 2024-04-24 10:32:11.702 CEST ] Creating data dictionary views
DBCA_PROGRESS : 28%
DBCA_PROGRESS : 33%

The catproc0.log shows many connection errors:

SQL*Plus: Release 19.0.0.0.0 - Production on Wed Apr 24 11:36:32 2024
Version 19.21.0.0.0

Copyright (c) 1982, 2022, Oracle.  All rights reserved.

connect SYS/SP2-0640: Not connected
connect SYS/  2  SP2-0640: Not connected
connect SYS/SP2-0640: Not connected
connect SYS/connect SYS/  2  SP2-0640: Not connected
connect SYS/connect SYS/  2  SP2-0640: Not connected
connect SYS/connect SYS/
connect SYS/connect SYS/  2  SP2-0640: Not connected
connect SYS/connect SYS/SP2-0640: Not connected
connect SYS/SP2-0640: Not connected
connect SYS/  2  SP2-0640: Not connected
connect SYS/connect SYS/connect SYS/connect SYS/connect SYS/connect SYS/connect SYS/connect SYS/connect SYS/connect SYS/connect SYS/connect SYS/connect SYS/connect SYS/SQL> SQL> SQL> SQL> SQL> SQL>   2  SP2-0640: Not connected
...

The generated trace file shows at the end:

[progressPage.flowWorker] [ 2024-04-24 10:32:17.334 CEST ] [PerlEngine.execute:220]  /u01/app/oracle/product/19.0.0/dbhome_1/rdbms/admin/catproc.sql
[progressPage.flowWorker] [ 2024-04-24 10:32:17.334 CEST ] [PerlEngine.execute:232]  Execing PERL process...
[progressPage.flowWorker] [ 2024-04-24 10:32:17.334 CEST ] [PerlEngine.execute:274]  m_bReaderStarted: false
[progressPage.flowWorker] [ 2024-04-24 10:32:17.334 CEST ] [PerlEngine.execute:278]  Starting Reader Thread... 
[progressPage.flowWorker] [ 2024-04-24 10:32:17.334 CEST ] [PerlEngine.execute:332]  Inputing secret arg: 0
[progressPage.flowWorker] [ 2024-04-24 10:32:17.334 CEST ] [PerlEngine.execute:332]  Inputing secret arg: 1
[Thread-233] [ 2024-04-24 10:32:17.379 CEST ] [PerlEngine.readPerlOutput:597]  perl engine output:Enter Password: 
[Thread-233] [ 2024-04-24 10:32:17.379 CEST ] [PerlEngine.readPerlOutput:597]  perl engine output:Enter Password:

This was part of an automation script I wrote, which was started during the VM installation using ansible. It was working fine until not long ago. What might have happened?

I end up opening an SR and searching a bit more, until I found out that the culprit was that in the previous steps there are a ORACLE_PATH variable added to .bash_profile . And recently I changed the login.sql to show the name of DB and PDB where the user is connected to. However I forgot to test for the cases when the database is down, on which it was simply showing:

$ sqlplus / as sysdba

SQL*Plus: Release 19.0.0.0.0 - Production on Wed Apr 24 11:36:32 2024
Version 19.21.0.0.0

Copyright (c) 1982, 2022, Oracle.  All rights reserved.

>

for some reason dbca did not like it. Ok, partly was my fault, lack of tests. Solution was to make sure that even with the database down (or without database), the sqlplus shows a meaningful prompt.

In a next blog post I’ll show how to personalize SQL*Plus and SQLcl prompt, which is compatible to dbca.