Wednesday, September 27, 2023

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.

No comments:

Post a Comment