Solution to [FATAL] [DBT-05509] Failed to connect to the specified database (cdb) in dbca

I was attempting to create a container database using dbca like this:

dbca -createDatabase -responsefile /home/oracle/scripts/cdb.rsp -silent

The following error kept coming up:

[FATAL] [DBT-05509] Failed to connect to the specified database (cdb).
   CAUSE: OS Authentication might be disabled for this database (cdb).
   ACTION: Specify a valid sysdba user name and password to connect to the database.

Solution:

Same solution as in my previous post "My solution to ORA-12701: CREATE DATABASE character set is not known":

If there are no *.nlb files in the default location $ORACLE_HOME/nls/data, then set the ORA_NLS10 parameter to the place where these files actually resides. In my case, they were found in the sub directory 9idata instead:

export ORA_NLS10=$ORACLE_HOME/nls/data/9idata

Otherwise Oracle won't be able to find the language files it needs to create the database.

Script snipplet to scan the dbca logs for errors

Here is a way to scan the logs in the directory where dbca creates its logfiles, by default under your $ORACLE_BASE/cfgtoollogs/dbca/$ORACLE_SID.

If it happens to be a multitenant database, dbca will create subdirectories for the PDB(s).

If you want to skip scanning these, exclude them from your grep command, like below:

cd /orasw/oracle/cfgtoollogs/dbca/cdb
export PDBDIR=`ls -d */ | awk -F '/' '{ print $1 }'`
export ignorelist='ORA_IGNORE_CVU_ERRORS|exectask.sh|utlrp0.log|RMANUtil\$RMANUtilErrorListener.handleError:1386|RMANUtil\$RMANUtilErrorListener.handleError:1383|RMANEngine.readSqlOutput:1046'
grep ERROR --exclude-dir=${PDBDIR} * | egrep -v $ignorelist

The errors I have excluded are based on my own observations on what's important or not during dbca usage.

[DBT-50000] Unable to check available system memory when running dbca

dbca returned the following error upon launch:

[FATAL] [DBT-50000] Unable to check available system memory.
   CAUSE: An exception occured while checking kernel parameter.
*ADDITIONAL INFORMATION:*
Exception details
 - The default location for framework home is not available. It must be specified

Cause:

The TMP and/or TMPDIR directory doesn't exist.

Solution:

Make sure the .bash_profile contains the correct values for the TMP and TMPDIR directories. It must be an accessible and writable directory.

In my case:

mkdir -p /u01/ora19c/tmp

Open .bash_profile, and add the following

OWNER=ora19c;export OWNER
TMP=/u01/$OWNER/tmp;export TMP
TMPDIR=/u01/$OWNER/tmp;export TMPDIR

Launch dbca again. It should now proceed as expected.

This article from IBM pointed me in the right direction.

Doc ID 2534894.1 "How To Change Default TEMP Location When Creating Database By DBCA?" is also onto the same solution, but here oracle show you how to append the -j flag to your dbca command, e.g

 dbca -J-Djava.io.tmpdir=/home/oracle/tmp

How to create a template that includes a seed database using dbca

This is how you can create a new template which will include the database "mydb01" as a seed database for future installations:

dbca -silent -createCloneTemplate -sourceDB mydb01 -templateName minimal_nonmt_seed -maintainFileLocations true -sysDBAPassword mysecretpassword -sysDBAUserName sys -rmanParallelism 2 -dataFileBackup true

Using the dbca General Purpose template and the resulting database

If you execute the dbca GUI and choose the simple configuration, you will be using the "General Purpose" template, which gives you an out-of-the-box database based on a seed database. There is not much you can do to adjust the resulting database to your needs when you select this option.

But, you do have the option to save the response file before the dbca GUI starts the actual creation process. Using the response file instead of the GUI, you have a couple of options to override the use of OMF and the naming of the subdirectories used for data files:

The reponse file, after peeling off the comments, contains the following:

responseFileVersion=/oracle/assistants/rspfmt_dbca_response_schema_v12.2.0
gdbName=mydb.skead.no
sid=mydb
databaseConfigType=SI
policyManaged=false
createServerPool=false
force=false
createAsContainerDatabase=false
numberOfPDBs=0
useLocalUndoForPDBs=true
templateName=/u01/oracle/product/19c/assistants/dbca/templates/General_Purpose.dbc
sysPassword=
systemPassword=
runCVUChecks=FALSE
dvConfiguration=false
olsConfiguration=false
datafileJarLocation={ORACLE_HOME}/assistants/dbca/templates/
datafileDestination=/u01/oradata/{DB_UNIQUE_NAME}/
recoveryAreaDestination=/u04/flash_recovery_area/{DB_UNIQUE_NAME}
storageType=FS
characterSet=AL32UTF8
registerWithDirService=false
listeners=LISTENER
skipListenerRegistration=false
variables=ORACLE_BASE_HOME=/u01/oracle/product/19c,DB_UNIQUE_NAME=mydb,ORACLE_BASE=/u01/oracle,PDB_NAME=,DB_NAME=mydb,ORACLE_HOME=/u01/oracle/product/19c,SID=mydb
initParams=undo_tablespace=UNDOTBS1,sga_target=4718MB,db_block_size=8KB,dispatchers=(PROTOCOL=TCP) (SERVICE={SID}XDB),diagnostic_dest={ORACLE_BASE},remote_login_passwordfile=EXCLUSIVE,db_create_file_dest=/data2/oradata/{DB_UNIQUE_NAME}/,audit_file_dest={ORACLE_BASE}/admin/{DB_UNIQUE_NAME}/adump,processes=320,pga_aggregate_target=1573MB,local_listener=LISTENER_MYDB,db_recovery_file_dest_size=8256MB,open_cursors=300,db_domain=skead.no,compatible=19.0.0,db_name=mydb,db_recovery_file_dest=/data4/flash_recovery_area/{DB_UNIQUE_NAME},audit_trail=db
sampleSchema=false
memoryPercentage=40
databaseType=MULTIPURPOSE
automaticMemoryManagement=false
totalMemory=0

Notice how the dbca withholds the value you supply for SYS and SYSTEM passwords, while it prints out the value for datafileDestination and recoveryAreaDestination in the response file for you.

This response file can be used to execute dbca silently, like this:

dbca -silent -createDatabase -responsefile youresponsefilename.rsp

This will create the database MYDB with OMF naming:

select file_name from dba_data_files:

/data2/oradata/MYDB/datafile/o1_mf_users__vbctspxs_.dbf
/data2/oradata/MYDB/datafile/o1_mf_undotbs1__vbcsmzql_.dbf
/data2/oradata/MYDB/datafile/o1_mf_system__vbb9h6kc_.dbf
/data2/oradata/MYDB/datafile/o1_mf_sysaux__vbcc2o9j_.dbf

select member from v$logfile

MEMBER
--------------------------------------------------
/data2/oradata/MYDB/onlinelog/o1_mf_3__vbdwcyb1_.log
/data4/fra/MYDB/onlinelog/o1_mf_3__vbdwxrdx_.log
/data2/oradata/MYDB/onlinelog/o1_mf_2__vbdwbqv8_.log
/data4/fra/MYDB/onlinelog/o1_mf_2__vbdwxb5f_.log
/data2/oradata/MYDB/onlinelog/o1_mf_1__vbdw9lqy_.log
/data4/fra/MYDB/onlinelog/o1_mf_1__vbdwtx1r_.log

This is because the response file contains the value

initParams=db_create_file_dest=/data2/oradata/{DB_UNIQUE_NAME}/

If dbca is run again without this value, the files will not be of type OMF anymore:

SYS@mydb>select member from v$logfile;

MEMBER
----------------------------------------
/u01/oradata/MYDB/redo03.log
/u01/oradata/MYDB/redo02.log
/u01/oradata/MYDB/redo01.log

SYS@mydb>select member from v$logfile;

MEMBER
----------------------------------------
/u01/oradata/MYDB/redo03.log
/u01/oradata/MYDB/redo02.log
/u01/oradata/MYDB/redo01.log

If you alter the directions

datafileDestination=/u01/oradata/{DB_UNIQUE_NAME}/
recoveryAreaDestination=/data4/flash_recovery_area/{DB_UNIQUE_NAME}

to

datafileDestination=/u01/oradata/mydb
recoveryAreaDestination=/data4/flash_recovery_area/mydb

you will also be able to override the fact that dbca creates the subdirectories in capital letters by default.

What are dbca templates and how do they work?

What are dbca templates? 

Database Configuration Assistant (DBCA) templates are XML files that contain information required to create a database.

What are templates used for? 

Templates are used in DBCA to create new databases and duplicate existing databases. 

What information does a template contain?

The information in templates includes database options, initialization parameters, and storage attributes (for data files, tablespaces, control files, and online redo log files). 

Are there different types of templates?

Yes:

Type	File extension	Includes Data Files	Database Structure	Configurable?
Seed	.dbc	Yes	contains both the structure and the physical data files of an existing database, referred to as the seed database. The data files for the seed database are stored in compressed RMAN backup format in a file with a .dfb extension. The seed database control file is stored in a file with .ctl extension The .dbc file contains the location of the seed database data files and contains the source database name used to mount the control file.	No
nonseed	.dbt	No	This type of template is used to create a new database. It contains the characteristics of the database to be created. Nonseed templates are more flexible than their seed counterparts because all data files and online redo log files are created to your specification, and names, sizes, and other attributes can be changed as required.	Yes

Why use templates? 

Templates can be used just like scripts, but they are more powerful than scripts because you have the option of duplicating a database. Duplication saves time because you copy the files of an existing database, referred to as a seed database, to the correct locations.

When using templates, beware of the following:

1. Database options (for example, Data Vault or Spatial) can be configured or modified only for a custom database or nonseed template (.dbt file).
2. You cannot modify database options for a seed template (includes data file backups).
3. Using a seed template is a much quicker operation, than using a non-seed template, but less flexible.

What templates are delivered with a standard Oracle 19c software installation?

Upon completion of an Oracle 19c software installation, you will find the following files in the directory $ORACLE_HOME/assistants/dbca/templates:

New_Database.dbt

Seed_Database.dfb

Seed_Database.ctl

Data_Warehouse.dbc

General_Purpose.dbc

pdbseed.xml

They are used by the different templates:

Template	Workload	Files used
Data warehouse	Users perform numerous, complex queries that process large volumes of data.	Seed_Database.dbf
General Purpose or Transaction processing	Many concurrent users perform numerous transactions that require rapid access to data.	Seed_Database.dbf
Custom database	Allows for maximum flexibility in defining a database; you can change any of the settings for the database being created.	New_Database.dbt

Both the Data_Warehouse.dbc and the General_Purpose.dbc are pointing to the Seed_Database.dbf when they are used, which means they will be create a database for you quickly but you will have to accept the options and standard given by the template


Documentation:

2.3.5 Managing Templates with DBCA

Solution for [WARNING] ORA-01081: cannot start already-running ORACLE - shut it down first when running dbca

I received the following error multiple times, when executing dbca like below:

dbca -createDatabase -responsefile ./cdb.rsp -silent
 2023-09-14 15:05:45.435 CEST ] Prepare for db operation
DBCA_PROGRESS : 10%
[ 2023-09-14 15:05:45.505 CEST ] Copying database files
DBCA_PROGRESS : 12%
[ 2023-09-14 15:05:47.250 CEST ] [WARNING] ORA-01081: cannot start already-running ORACLE - shut it down first

DBCA_PROGRESS : 40%
DBCA_PROGRESS : 100%
[ 2023-09-14 15:05:47.616 CEST ] [FATAL] Recovery Manager failed to restore datafiles. Refer logs for details.
DBCA_PROGRESS : 10%
DBCA_PROGRESS : 0%

At first I thought it was because I had forgotten to terminate some processes owned by oracle. And I also made sure that /etc/oratab was empty before I started.

Reason:

Turns out, there were old memory segments left behind that dbca considers a part of a running instance. Even if

ps -fu oracle

gave no processes in the list.

Solution:

su - oracle
sqlplus / as sysdba
shutdown abort

Given that you've set ORACLE_SID to the same value you used in your response file, this will properly shut down your instance, and clean up the memory segments left behind from the previous run.

How to delete a pluggable database with dbca using the command line

dbca -silent -deletePluggableDatabase \
-sourceDB cdb \
-pdbName pdb3 \

How to create a pluggable database with dbca using the command line

In the following code snippet, I am creating the pluggable database "pdb3" as a copy of the already existing pluggable database "pdb1":

dbca -silent -createPluggableDatabase \
-sourceDB cdb.skead.no \
-pdbName pdb3 \
-sourcePDB pdb1 \
-createPDBFrom PDB \
-pdbDatafileDestination "/dbfiles/oradata/cdb/pdb3/" \
-pdbAdminPassword mysecretpassword1 \
-sysPassword mysecretpassword2 \
-systemPassword mysecretpassword3 \

If you want to use the SEED database as source instead of an already existing database, remove the directive

-sourcePDB pdb1 \

and use this directive instead:

-createPDBFrom DEFAULT \

Source: Oracle 19c documentation

Script for creating a multitenant database with dbca

Prerequisites:

You have a valid Oracle 19c installation in a server

You have set your Oracle environment already, so that your PATH, ORACLE_SID etc already exists when you execute your script

The script called run_dbca.sh:

#!/bin/bash
start=$(date +%s.%N)
export GLOGFILE=run_dbca.log
touch ${GLOGFILE}
chmod 666 ${GLOGFILE}
exec 1> ${GLOGFILE} 2>&1
echo "Now running run_dbca.sh"
echo "Resetting oratab..."
echo '' > /etc/oratab
if [ $? -ne 0 ]; then
 echo "Could not erase oratab. Exit."
 exit 1
else
 echo "oratab erased. Continuing..."
fi
export instanceAlive=`ps -ef| grep pmon | grep -v grep |awk '{ print $8}' | cut -f 3 -d"_"`

if [ ! -z ${instanceAlive} ]; then
 echo "Instance for database $ORACLE_SID is already running. Shut it down first"
 exit 1
fi
dbca -createDatabase -responsefile ./${ORACLE_SID}.rsp -silent
echo "Finished running run_dbca.sh"
dur=$(echo "$(date +%s.%N) - $start" | bc)
printf "Total execution time for run_dbca.sh: %.6f seconds\n" $dur
exit 0

The response file can be seen below. I saved it in a file called cdb.rsp and put it in the same directory.

I commented out most instructions, as they are not needed.

The original template db_install.rsp is delivered by default with your oracle installation and resides in your $ORACLE_HOME/install/response.

#-------------------------------------------------------------------------------
# Do not change the responseFileVersion diretive!
#-------------------------------------------------------------------------------
responseFileVersion=/oracle/assistants/rspfmt_dbca_response_schema_v19.0.0
gdbName=cdb.oric.no
sid=cdb
createAsContainerDatabase=true
numberOfPDBs=1
pdbName=pdb1
useLocalUndoForPDBs=true
pdbAdminPassword=mysecretpassword
templateName=New_Database.dbt
sysPassword=mysyspassword
systemPassword=mysystempassword
datafileDestination=/data01/oradata/cdb
recoveryAreaDestination=/data02/fra
characterSet=AL32UTF8
initParams=max_pdbs=3,db_create_file_dest_size=1024GB
memoryPercentage=75
automaticMemoryManagement=false
enableArchive=true
redoLogFileSize=2048
dbOptions=JSERVER:false,DV:false,ORACLE_TEXT:false,IMEDIA:false,CWMLITE:false,SPATIAL:false,OMS:false,APEX:false

Execute the script like this:

./run_dbca.sh

Follow the progression of the creation in the log run_dbca.log:

tail -f run_dbca.log

Sources:

Oracle 19c documentation: About creating a database with dbca

Oracle 19c documentation: Managing templates with dbca

Oracle-Base.com: Multitenant : Create and Configure a Pluggable Database (PDB) in Oracle Database 12c Release 1 (12.1) - still a relevant and good source.

Oracle-base.com: a dbca template

Oracle-base.com: Database Configuration Assistant (DBCA) : Creating Databases in Silent Mode

Solution for [DBT-10317] Specified SID Name (SID) already exists when running dbca

When running dbca, for example like this:

dbca -createDatabase -responsefile ./cdb.rsp -silent
[FATAL] [DBT-10317] Specified SID Name (cdb) already exists.
   ACTION: Specify a different SID Name that does not already exist.

There are two potential reasons for the error:

1. you have a /etc/oratab which has an entry, for example:

cdb:/installedsw1/oracle/product/Ora19c:Y

2. your instance is running.

Solution to case 1: remove entry

Solution to case 2: shutdown the instance

Rerun dbca and it will now proceed as expected.

Cause and solution to ORA-00058: DB_BLOCK_SIZE must be 8192 to mount this database (not 16384)

You attempt to execute dbca silently, using a response file, as shown below:

dbca -createDatabase -responsefile mydb01.rsp -silent

But it errors out with the error:

[WARNING] ORA-00058: DB_BLOCK_SIZE must be 8192 to mount this database (not 16384)

and the instance is left running, with an unmounted database.

Cause:

This means you are using a pre-defined template for dbca that includes datafiles.

When you do this, you cannot change the db_block_size. It is set to 8K, and carved in stone.

Solution:

Create your own, customized template and refer to that instead.

Remember that templates ending with the extension .dbc contains database files, so they will be significantly faster to use, than custom made templates ending with the extension .dbt - the latter will have to go through the "CREATE DATABASE" statements from scratch.

Templates that do not contain datafiles have the extension .dbt

What you can do in order to have your cake and eat it, too, is to first create a database with a non-confirming db_block_size, then create a dbca template based on this new database, including its datafiles.

I will see if I have time to write a blog post about how to do extactly this.

How to trace the dbca tool

These notes are taken directly from MOS, and jotted down here so I have it readily available for the future.

To trace the DBCA, we need to perform the following.

1. Do a copy of the original dbca file in $ORACLE_HOME/bin. For example:

cp $ORACLE_HOME/bin/dbca $ORACLE_HOME/bin/dbca.ori

2. vi the dbca file in the $ORACLE_HOME/bin directory.

3. At the end of the file, look for the following line:

# Run DBCA
$JRE_DIR/bin/jre -DORACLE_HOME=$OH -DJDBC_PROTOCOL=thin -mx64m -classpath $CLASSPATH oracle.sysman.assistants.dbca.Dbca $ARGUMENTS

4. Add the following just before the -classpath in the '$JRE_DIR' line:

-DTRACING.ENABLED=true -DTRACING.LEVEL=2

5. At the end of the dbca file, the string should now look like this:

# Run DBCA
$JRE_DIR/bin/jre -DORACLE_HOME=$OH -DJDBC_PROTOCOL=thin -mx64m -DTRACING.ENABLED=true -DTRACING.LEVEL=2 -classpath $CLASSPATH oracle.sysman.assistants.dbca.Dbca $ARGUMENTS

6. To trace, run:

[host]/u01/home/usupport> dbca > dbca.out >

The output will be written to the dbca.out file.