Miguel Anjo

ORA-64307 when creating compressed table and /home

This entry was posted in Engineer Systems ExaCC Exadata Oracle 19c on by

My customer running on ExaCC (Exadata Cloud@Customer) was getting “ORA-64307: Exadata Hybrid Columnar Compression is not supported for tablespaces on this storage type” on one of his test databases.

I did test connecting to SYS and no problem. Then I try to do using his tablespace and indeed, I get the error:

Quite going around, to check what was different on the user tablespace than on others. I test a self created tablespace and it works.

Strange. Until I found that… some datafiles were not in ASM!

Seems the ASM Diskgroup is almost full and the client DBA just put the datafiles somewhere else!

JumpHost Matryoshka

This entry was posted in Other on by

My client just added an extra jumphost before arriving to the server. So now I’ve to connect, to connect, to connect and then open the connection. 🙂

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…)

Solve “OGG-08224 Error: CONTAINER option was specified though the database does not support containers” error

This entry was posted in GoldenGate Oracle 19c on by

Quick post to add info about the following Goldengate error:

OGG (http://localhost:9300 test1 as ogg_pdb1@CDB2) 10> REGISTER EXTRACT E_TEST1 DATABASE CONTAINER (pdb1)

2024-12-08T17:16:58Z ERROR OGG-08224 Error: CONTAINER option was specified though the database does not support containers.

This means that you are connected directly to the PDB, and not to CDB$ROOT.

To register Goldengate 21 extracts you need to connect to the Root container with a common user.

OGG (http://localhost:9300 test1 as ogg_pdb1@CDB2) 12> DBLOGIN USERIDALIAS ogg_cdb2
Successfully logged into database CDB$ROOT.

OGG (http://localhost:9300 test1 as ogg_cdb2@CDB2/CDB$ROOT) 13> REGISTER EXTRACT E_TEST1 DATABASE CONTAINER (pdb1)
2024-12-08T17:20:36Z  INFO    OGG-02003  Extract group E_TEST1 successfully registered with database at SCN 8039188.

Well, in the future this is not anymore true, as new version from Goldengate and DBs will work only at PDB level.

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…)

Oracle postpones release of 23ai on-premises to 2H CY2024

This entry was posted in Exadata Oracle 23ai on by

Oracle just updated the Release Schedule of Current Database Releases (Doc ID 742060.1) and changed the release date of database version 23ai on-premises to next half-year. Lets see how many months and bug fixing that means. 🙂

Update on 20.06.2024 – “Added new release dates for Oracle Autonomous Database – Dedicated Exadata Infrastructure, Autonomous Database on Exadata Cloud@Customer, ODA, Exadata and Linux-x86 64”

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.

Change language and prompt in SQLcl for VS Code

This entry was posted in SQLcl VSCode on by

Windows and language settings is a common problem for users outside English speaking countries. The windows language on my laptop is English, but time to time I use another laptop in Portugal with the same Microsoft account as login. This laptop is configured in Portuguese. My work laptop’s keyboard is Swiss, locale also set for Switzerland. Windows just gets confused and shows some applications in English, others in German, others in Portuguese.

Today I was testing SQLcl inside VS Code. I tried the help function and get answers in German, plus with the öäü signs wrong.

So I needed to change it to English. Based on SQLcl documentation it reads a file startup.sql once when it starts. And on VS Code, to know where it is currently running, you just have to run the ‘pwd’ command:

SQL> pwd
C:\Users\migue\

Then, on this path, you can create a startup.sql file and change the prompt, and language. My startup.sql starts by changing the display language, then the SQLcl layout and the prompt:

set feedb off termout off
-- changes the display language
alter session set nls_language=american;

-- Changes SQLcl to have word highlight and statusbar
set highlighting on
set highlighting keyword foreground green
set highlighting identifier background blue
set highlighting string foreground yellow
set highlighting number foreground cyan
set highlighting comment foreground white
set statusbar on
set statusbar add editmode
set statusbar add txn
set statusbar add timing

-- Changes prompt to show the instance and pdb name
DEFINE prompt="SQL"
COLUMN col_prompt NEW_VALUE prompt
SELECT UPPER(USER || '@' || SYS_CONTEXT('userenv', 'instance_name')) ||'.'|| SYS_CONTEXT('userenv', 'con_name') col_prompt FROM dual;
set sqlprompt "&prompt> " ;
set termout on feed on

At the ends it looks like this:

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.