Daily Archives: 01.05.2024

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.