How to create a database with non-default blocksize using 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

1. create a response file called $ORACLE_SID.rsp, in this case, tstdwh1.rsp:

responseFileVersion=/oracle/assistants/rspfmt_dbca_response_schema_v12.2.0
gdbName=tstdwh1.oric.no
sid=tstdwh1
databaseConfigType=SI
policyManaged=false
createServerPool=false
force=false
createAsContainerDatabase=false
templateName=/u01/oracle/product/19c/assistants/dbca/templates/New_Database.dbt
sysPassword=mysecreatpassword
systemPassword=mysecreatpassword
datafileJarLocation={ORACLE_HOME}/assistants/dbca/templates/
datafileDestination=/data02/oradata/{DB_UNIQUE_NAME}/
recoveryAreaDestination=/data04/fra/{DB_UNIQUE_NAME}
storageType=FS
characterSet=AL32UTF8
nationalCharacterSet=AL16UTF16
registerWithDirService=false
listeners=LISTENER
skipListenerRegistration=false
variables=ORACLE_BASE_HOME=/u01/oracle/product/19c,DB_UNIQUE_NAME=tstdwh1,ORACLE_BASE=/u01/oracle,PDB_NAME=,DB_NAME=tstdwh1,ORACLE_HOME=/u01/oracle/product/19c,SID=tstdwh1
initParams=undo_tablespace=UNDOTBS1,sga_target=4710MB,db_block_size=32768BYTES,nls_language=NORWEGIAN,dispatchers=(PROTOCOL=TCP) (SERVICE=tstdwh1XDB),diagnostic_dest={ORACLE_BASE},control_files=("{ORACLE_BASE}/oradata/{DB_UNIQUE_NAME}/control01.ctl", "/u04/fra/{DB_UNIQUE_NAME}/control02.ctl"),remote_login_passwordfile=EXCLUSIVE,audit_file_dest={ORACLE_BASE}/admin/{DB_UNIQUE_NAME}/adump,processes=400,pga_aggregate_target=1570MB,nls_territory=NORWAY,local_listener=LISTENER_TSTDWH1,db_recovery_file_dest_size=7851MB,open_cursors=300,log_archive_format=%t_%s_%r.dbf,db_domain=oric.no,compatible=19.0.0,db_name=tstdwh1,db_recovery_file_dest=/u04/fra/{DB_UNIQUE_NAME},audit_trail=db
sampleSchema=false
memoryPercentage=40
automaticMemoryManagement=false

Notice the parameter db_block_size=32768BYTES in the "variables" directive, and the fact that I am using the New_Database.dbt template. This template is more flexible than the others delivered by Oracle, but makes the whole database creation process take more time, as they do not include any databaes files.

Also notice the absence of the directive "databaseType" - it is only meaningful if you let DBCA choose a built-in template based on that type (like OLTP or Data Warehouse).

2. execute the dbca directly in the shell:

dbca -createDatabase -responsefile ./${ORACLE_SID}.rsp -silent 

Or, create a wrapper around the command, make it executable and execute it in the background:

#!/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

chmod 755 run_dbca.sh
nohup ./run_dbca.sh &