I’ve been playing with Oracle 12.2 lately, which also means I do lots of silent installations of it :-).
\nI don’t know why I just put a smiley there because there was nothing funny about it! Troubleshooting DBCA errors, combing through logs, and raising service requests with Oracle Support almost sucked the joy out of the new 12.2 release.<\/p>\n
That’s what I’ve stumbled upon:<\/p>\n
* actually, the new way makes more sense to me as it is closer to the way you set empty values in ALTER SYSTEM\/SESSION SET statement.<\/p>\n
Anyway, enough with the rambling. I will show now how I setup my lab systems.<\/p>\n
To be flexible with automation we set a few variables that are later used to feed input to DBCA. You can modify these according to your needs and environment.<\/p>\n
\r\nNEW_ORACLE_SID=DEV1\r\nNEW_DB_NAME=DEV1\r\nNEW_DB_DOMAIN=localdomain\r\nNEW_DB_CHARSET=AL32UTF8\r\nNEW_SYS_PWD=manager\r\nNEW_SYSTEM_PWD=manager\r\nNEW_REDO_SIZE_MB=256\r\n\r\nINIT_CMPA=DIAGNOSTIC+TUNING\r\nINIT_COMPATIBLE=12.2.0.0\r\nINIT_OFE=12.2.0.1\r\nINIT_PGA_AGG_TGT_MB=512\r\nINIT_PROCESSES=200\r\nINIT_SGA_TGT_MB=4096\r\nINIT_CPU_COUNT=4\r\n\r\nNEW_DATA_PATH1=\/u02\/oradata\/${NEW_DB_NAME}\r\nNEW_REDO_PATH1=\/u02\/oradata\/${NEW_DB_NAME}\r\nNEW_REDO_PATH2=\/u03\/oradata\/${NEW_DB_NAME}\r\nNEW_ARCH_PATH1=\/u04\/oradata\/${NEW_DB_NAME}\r\n<\/pre>\nNEW_REDO_PATH1 and NEW_REDO_PATH2 will be used for multiplexing online redo-logs as well as controlfiles. The rest of the variables should be self-explanatory.<\/p>\n
You don’t have to modify following variables as they are “calculated” values.<\/p>\n
\r\nNEW_DB_UNIQUE_NAME=${NEW_DB_NAME}\r\nNEW_GLOBAL_DBNAME=${NEW_DB_NAME}.${NEW_DB_DOMAIN}\r\nINIT_PGA_AGG_TGT=$((${INIT_PGA_AGG_TGT_MB} * 1024 * 1024))\r\nINIT_SGA_TGT=$((${INIT_SGA_TGT_MB} * 1024 * 1024))\r\nINIT_LISTENER_REG=LISTENER_${NEW_DB_NAME}\r\n<\/pre>\nJust make sure you have a listener running with the name specified in INIT_LISTENER_REG.<\/p>\n
Now, because of bug 25353173 we must put what we’d normally feed to "-variables" into a file:<\/p>\n
\r\necho "DATA_PATH1=${NEW_DATA_PATH1}" > \/tmp\/dbca-vars.txt\r\necho "REDO_PATH1=${NEW_REDO_PATH1}" >> \/tmp\/dbca-vars.txt\r\necho "REDO_PATH2=${NEW_REDO_PATH2}" >> \/tmp\/dbca-vars.txt\r\necho "ARCH_PATH1=${NEW_ARCH_PATH1}" >> \/tmp\/dbca-vars.txt\r\n<\/pre>\nBefore we can run DBCA we need to prepare a template. I like to prepare a template that contains placeholders and everything else that I define static. This way I define one single template and can reuse it for all kinds of deployments.
\nI know it’s awfully long but you can skip reading the template if you just want to go ahead and create the database.
\nSave this as \/tmp\/dbca-newdb-linux.dbt<\/p>\n
\r\n<DatabaseTemplate name="New Database" description="" version="12.2.0.1.0">\r\n <CommonAttributes>\r\n <option name="OMS" value="false"\/>\r\n <option name="JSERVER" value="false"\/>\r\n <option name="SPATIAL" value="false"\/>\r\n <option name="IMEDIA" value="false"\/>\r\n <option name="ORACLE_TEXT" value="false">\r\n <tablespace id="SYSAUX"\/>\r\n <\/option>\r\n <option name="CWMLITE" value="false">\r\n <tablespace id="SYSAUX"\/>\r\n <\/option>\r\n <option name="SAMPLE_SCHEMA" value="false"\/>\r\n <option name="APEX" value="false"\/>\r\n <option name="DV" value="false"\/>\r\n <\/CommonAttributes>\r\n <Variables\/>\r\n <CustomScripts Execute="false"\/>\r\n <InitParamAttributes>\r\n <InitParams>\r\n <initParam name="autotask_max_active_pdbs" value="1"\/>\r\n <initParam name="awr_pdb_autoflush_enabled" value="true"\/>\r\n <initParam name="awr_snapshot_time_offset" value="1000000"\/>\r\n <initParam name="commit_logging" value="IMMEDIATE"\/>\r\n <initParam name="commit_wait" value="WAIT"\/>\r\n <initParam name="containers_parallel_degree" value="2"\/>\r\n <initParam name="control_files" value="("{DATA_PATH1}\/ctrl-001-{DB_NAME}.dbf", "{REDO_PATH1}\/ctrl-002-{DB_NAME}.dbf", "{REDO_PATH2}\/ctrl-003-{DB_NAME}.dbf")"\/>\r\n <initParam name="control_file_record_keep_time" value="35"\/>\r\n <initParam name="cursor_invalidation" value="DEFERRED"\/>\r\n <initParam name="db_block_checking" value="MEDIUM"\/>\r\n <initParam name="db_block_checksum" value="FULL"\/>\r\n <initParam name="db_block_size" value="8" unit="KB"\/>\r\n <initParam name="db_files" value="100"\/>\r\n <initParam name="db_flashback_retention_target" value="1440"\/> \r\n <initParam name="db_securefile" value="ALWAYS"\/>\r\n <initParam name="deferred_segment_creation" value="FALSE"\/>\r\n <initParam name="dg_broker_config_file1" value="{ORACLE_BASE}\/admin\/{DB_NAME}\/instance\/dr-001-{DB_NAME}.dat"\/>\r\n <initParam name="dg_broker_config_file2" value="{ORACLE_BASE}\/admin\/{DB_NAME}\/instance\/dr-002-{DB_NAME}.dat"\/>\r\n <initParam name="diagnostic_dest" value="{ORACLE_BASE}\/adr"\/>\r\n <initParam name="dispatchers" value="''"\/>\r\n <initParam name="enable_pluggable_database" value="TRUE"\/>\r\n <initParam name="filesystemio_options" value="SETALL"\/>\r\n <initParam name="inmemory_adg_enabled" value="FALSE"\/>\r\n <initParam name="inmemory_force" value="OFF"\/>\r\n <initParam name="inmemory_query" value="DISABLE"\/>\r\n <initParam name="inmemory_size" value="0"\/>\r\n <initParam name="log_archive_dest_1" value="LOCATION={ARCH_PATH1}"\/>\r\n <initParam name="log_archive_dest_state_1" value="ENABLE"\/>\r\n <initParam name="log_archive_format" value="{DB_NAME}-%t-%s-%r.arc"\/>\r\n <initParam name="max_pdbs" value="1"\/>\r\n <initParam name="nls_language" value="AMERICAN"\/>\r\n <initParam name="nls_nchar_conv_excp" value="TRUE"\/>\r\n <initParam name="nls_territory" value="AMERICA"\/>\r\n <initParam name="open_cursors" value="300"\/>\r\n <initParam name="os_authent_prefix" value="''"\/>\r\n <initParam name="parallel_min_servers" value="0"\/>\r\n <initParam name="remote_listener" value="''"\/>\r\n <initParam name="shared_servers" value="0"\/>\r\n <initParam name="temp_undo_enabled" value="TRUE"\/>\r\n <initParam name="undo_retention" value="1800"\/>\r\n <initParam name="undo_tablespace" value="UNDOTS"\/>\r\n <initParam name="uniform_log_timestamp_format" value="TRUE"\/>\r\n <\/InitParams>\r\n <MiscParams>\r\n <databaseType>MULTIPURPOSE<\/databaseType>\r\n <maxUserConn>20<\/maxUserConn>\r\n <percentageMemTOSGA>40<\/percentageMemTOSGA>\r\n <archiveLogMode>true<\/archiveLogMode>\r\n <initParamFileName>{ORACLE_BASE}\/admin\/{DB_NAME}\/create\/init.ora<\/initParamFileName>\r\n <\/MiscParams>\r\n <SPfile useSPFile="true">{ORACLE_BASE}\/admin\/{DB_NAME}\/instance\/spfile{SID}.ora<\/SPfile>\r\n <\/InitParamAttributes>\r\n <StorageAttributes>\r\n <ControlfileAttributes id="Controlfile">\r\n <maxDatafiles>100<\/maxDatafiles>\r\n <maxLogfiles>16<\/maxLogfiles>\r\n <maxLogMembers>3<\/maxLogMembers>\r\n <maxLogHistory>1<\/maxLogHistory>\r\n <maxInstances>8<\/maxInstances>\r\n <image name="ctrl-001-{DB_NAME}.dbf" filepath="{DATA_PATH1}\/"\/>\r\n <image name="ctrl-002-{DB_NAME}.dbf" filepath="{REDO_PATH1}\/"\/>\r\n <image name="ctrl-003-{DB_NAME}.dbf" filepath="{REDO_PATH2}\/"\/>\r\n <\/ControlfileAttributes>\r\n <DatafileAttributes id="{DATA_PATH1}\/sysaux-001-{DB_NAME}.dbf">\r\n <tablespace>SYSAUX<\/tablespace>\r\n <temporary>false<\/temporary>\r\n <online>true<\/online>\r\n <status>0<\/status>\r\n <size unit="MB">256<\/size>\r\n <reuse>true<\/reuse>\r\n <autoExtend>true<\/autoExtend>\r\n <increment unit="KB">8192<\/increment>\r\n <maxSize unit="MB">4096<\/maxSize>\r\n <\/DatafileAttributes>\r\n <DatafileAttributes id="{DATA_PATH1}\/toolts-001-{DB_NAME}.dbf">\r\n <tablespace>TOOLTS<\/tablespace>\r\n <temporary>false<\/temporary>\r\n <online>true<\/online>\r\n <status>0<\/status>\r\n <size unit="MB">56<\/size>\r\n <reuse>true<\/reuse>\r\n <autoExtend>true<\/autoExtend>\r\n <increment unit="KB">4096<\/increment>\r\n <maxSize unit="MB">2048<\/maxSize>\r\n <\/DatafileAttributes>\r\n <DatafileAttributes id="{DATA_PATH1}\/system-001-{DB_NAME}.dbf">\r\n <tablespace>SYSTEM<\/tablespace>\r\n <temporary>false<\/temporary>\r\n <online>true<\/online>\r\n <status>0<\/status>\r\n <size unit="MB">1024<\/size>\r\n <reuse>true<\/reuse>\r\n <autoExtend>true<\/autoExtend>\r\n <increment unit="KB">8192<\/increment>\r\n <maxSize unit="MB">4096<\/maxSize>\r\n <\/DatafileAttributes>\r\n <DatafileAttributes id="{DATA_PATH1}\/tempts-001-{DB_NAME}.dbf">\r\n <tablespace>TEMPTS<\/tablespace>\r\n <temporary>false<\/temporary>\r\n <online>true<\/online>\r\n <status>0<\/status>\r\n <size unit="MB">128<\/size>\r\n <reuse>true<\/reuse>\r\n <autoExtend>true<\/autoExtend>\r\n <increment unit="KB">2048<\/increment>\r\n <maxSize unit="MB">8192<\/maxSize>\r\n <\/DatafileAttributes>\r\n <DatafileAttributes id="{DATA_PATH1}\/undots-001-{DB_NAME}.dbf">\r\n <tablespace>UNDOTBS1<\/tablespace>\r\n <temporary>false<\/temporary>\r\n <online>true<\/online>\r\n <status>0<\/status>\r\n <size unit="MB">512<\/size>\r\n <reuse>true<\/reuse>\r\n <autoExtend>true<\/autoExtend>\r\n <increment unit="KB">2028<\/increment>\r\n <maxSize unit="MB">8192<\/maxSize>\r\n <\/DatafileAttributes>\r\n <TablespaceAttributes id="SYSAUX">\r\n <online>true<\/online>\r\n <offlineMode>1<\/offlineMode>\r\n <readOnly>false<\/readOnly>\r\n <temporary>false<\/temporary>\r\n <defaultTemp>false<\/defaultTemp>\r\n <undo>false<\/undo>\r\n <local>true<\/local>\r\n <blockSize>-1<\/blockSize>\r\n <allocation>1<\/allocation>\r\n <uniAllocSize unit="KB">-1<\/uniAllocSize>\r\n <initSize unit="KB">64<\/initSize>\r\n <increment unit="KB">64<\/increment>\r\n <incrementPercent>50<\/incrementPercent>\r\n <minExtends>1<\/minExtends>\r\n <maxExtends>4096<\/maxExtends>\r\n <minExtendsSize unit="KB">64<\/minExtendsSize>\r\n <logging>true<\/logging>\r\n <recoverable>false<\/recoverable>\r\n <maxFreeSpace>0<\/maxFreeSpace>\r\n <bigfile>false<\/bigfile>\r\n <datafilesList>\r\n <TablespaceDatafileAttributes id="{DATA_PATH1}\/sysaux-001-{DB_NAME}.dbf"\/>\r\n <\/datafilesList>\r\n <\/TablespaceAttributes>\r\n <TablespaceAttributes id="TOOLTS">\r\n <online>true<\/online>\r\n <offlineMode>1<\/offlineMode>\r\n <readOnly>false<\/readOnly>\r\n <temporary>false<\/temporary>\r\n <defaultTemp>false<\/defaultTemp>\r\n <undo>false<\/undo>\r\n <local>true<\/local>\r\n <blockSize>-1<\/blockSize>\r\n <allocation>1<\/allocation>\r\n <uniAllocSize unit="KB">-1<\/uniAllocSize>\r\n <initSize unit="KB">128<\/initSize>\r\n <increment unit="KB">128<\/increment>\r\n <incrementPercent>0<\/incrementPercent>\r\n <minExtends>1<\/minExtends>\r\n <maxExtends>4096<\/maxExtends>\r\n <minExtendsSize unit="KB">128<\/minExtendsSize>\r\n <logging>true<\/logging>\r\n <recoverable>false<\/recoverable>\r\n <maxFreeSpace>0<\/maxFreeSpace>\r\n <bigfile>false<\/bigfile>\r\n <datafilesList>\r\n <TablespaceDatafileAttributes id="{DATA_PATH1}\/toolts-001-{DB_NAME}.dbf"\/>\r\n <\/datafilesList>\r\n <\/TablespaceAttributes>\r\n <TablespaceAttributes id="SYSTEM">\r\n <online>true<\/online>\r\n <offlineMode>1<\/offlineMode>\r\n <readOnly>false<\/readOnly>\r\n <temporary>false<\/temporary>\r\n <defaultTemp>false<\/defaultTemp>\r\n <undo>false<\/undo>\r\n <local>true<\/local>\r\n <blockSize>-1<\/blockSize>\r\n <allocation>3<\/allocation>\r\n <uniAllocSize unit="KB">-1<\/uniAllocSize>\r\n <initSize unit="KB">64<\/initSize>\r\n <increment unit="KB">64<\/increment>\r\n <incrementPercent>50<\/incrementPercent>\r\n <minExtends>1<\/minExtends>\r\n <maxExtends>-1<\/maxExtends>\r\n <minExtendsSize unit="KB">64<\/minExtendsSize>\r\n <logging>true<\/logging>\r\n <recoverable>false<\/recoverable>\r\n <maxFreeSpace>0<\/maxFreeSpace>\r\n <bigfile>false<\/bigfile>\r\n <datafilesList>\r\n <TablespaceDatafileAttributes id="{DATA_PATH1}\/system-001-{DB_NAME}.dbf"\/>\r\n <\/datafilesList>\r\n <\/TablespaceAttributes>\r\n <TablespaceAttributes id="TEMPTS">\r\n <online>true<\/online>\r\n <offlineMode>1<\/offlineMode>\r\n <readOnly>false<\/readOnly>\r\n <temporary>true<\/temporary>\r\n <defaultTemp>true<\/defaultTemp>\r\n <undo>false<\/undo>\r\n <local>true<\/local>\r\n <blockSize>-1<\/blockSize>\r\n <allocation>1<\/allocation>\r\n <uniAllocSize unit="KB">1012<\/uniAllocSize>\r\n <initSize unit="KB">64<\/initSize>\r\n <increment unit="KB">64<\/increment>\r\n <incrementPercent>0<\/incrementPercent>\r\n <minExtends>1<\/minExtends>\r\n <maxExtends>0<\/maxExtends>\r\n <minExtendsSize unit="KB">64<\/minExtendsSize>\r\n <logging>true<\/logging>\r\n <recoverable>false<\/recoverable>\r\n <maxFreeSpace>0<\/maxFreeSpace>\r\n <bigfile>false<\/bigfile>\r\n <datafilesList>\r\n <TablespaceDatafileAttributes id="{DATA_PATH1}\/tempts-001-{DB_NAME}.dbf"\/>\r\n <\/datafilesList>\r\n <\/TablespaceAttributes>\r\n <TablespaceAttributes id="UNDOTS">\r\n <online>true<\/online>\r\n <offlineMode>1<\/offlineMode>\r\n <readOnly>false<\/readOnly>\r\n <temporary>false<\/temporary>\r\n <defaultTemp>false<\/defaultTemp>\r\n <undo>true<\/undo>\r\n <local>true<\/local>\r\n <blockSize>-1<\/blockSize>\r\n <allocation>1<\/allocation>\r\n <uniAllocSize unit="KB">-1<\/uniAllocSize>\r\n <initSize unit="KB">512<\/initSize>\r\n <increment unit="KB">512<\/increment>\r\n <incrementPercent>50<\/incrementPercent>\r\n <minExtends>8<\/minExtends>\r\n <maxExtends>4096<\/maxExtends>\r\n <minExtendsSize unit="KB">512<\/minExtendsSize>\r\n <logging>true<\/logging>\r\n <recoverable>false<\/recoverable>\r\n <maxFreeSpace>0<\/maxFreeSpace>\r\n <bigfile>false<\/bigfile>\r\n <datafilesList>\r\n <TablespaceDatafileAttributes id="{DATA_PATH1}\/undots-001-{DB_NAME}.dbf"\/>\r\n <\/datafilesList>\r\n <\/TablespaceAttributes>\r\n <RedoLogGroupAttributes id="1">\r\n <reuse>false<\/reuse>\r\n <fileSize unit="KB">204800<\/fileSize>\r\n <Thread>1<\/Thread>\r\n <member ordinal="0" memberName="redo-g1-m1-{DB_NAME}.dbf" filepath="{REDO_PATH1}\/"\/>\r\n <member ordinal="1" memberName="redo-g1-m2-{DB_NAME}.log" filepath="{REDO_PATH2}\/"\/>\r\n <\/RedoLogGroupAttributes>\r\n <RedoLogGroupAttributes id="2">\r\n <reuse>false<\/reuse>\r\n <fileSize unit="KB">204800<\/fileSize>\r\n <Thread>1<\/Thread>\r\n <member ordinal="0" memberName="redo-g2-m1-{DB_NAME}.dbf" filepath="{REDO_PATH1}\/"\/>\r\n <member ordinal="1" memberName="redo-g2-m2-{DB_NAME}.log" filepath="{REDO_PATH2}\/"\/>\r\n <\/RedoLogGroupAttributes>\r\n <RedoLogGroupAttributes id="3">\r\n <reuse>false<\/reuse>\r\n <fileSize unit="KB">204800<\/fileSize>\r\n <Thread>1<\/Thread>\r\n <member ordinal="0" memberName="redo-g3-m1-{DB_NAME}.dbf" filepath="{REDO_PATH1}\/"\/>\r\n <member ordinal="1" memberName="redo-g3-m2-{DB_NAME}.log" filepath="{REDO_PATH2}\/"\/>\r\n <\/RedoLogGroupAttributes>\r\n <\/StorageAttributes>\r\n<\/DatabaseTemplate>\r\n<\/pre>\nWe’re now ready to create the database by calling DBCA:<\/p>\n
\r\nexport ORACLE_BASE=\/u01\/app\/oracle\r\nexport ORACLE_HOME=${ORACLE_BASE}\/product\/ora12201\r\nexport PATH=${ORACLE_HOME}\/bin:${PATH}\r\n\r\n${ORACLE_HOME}\/bin\/dbca -silent -createDatabase \\\r\n -templateName "\/tmp\/dbca-newdb-linux.dbt" \\\r\n -gdbName "${NEW_GLOBAL_DBNAME}" \\\r\n -characterSet "${NEW_DB_CHARSET}" \\\r\n -createAsContainerDatabase "true" \\\r\n -databaseConfigType SINGLE \\\r\n -dbOptions "ORACLE_TEXT:true" \\\r\n -databaseType MULTIPURPOSE \\\r\n -dvConfiguration "false" \\\r\n -emConfiguration "NONE" \\\r\n -enableArchive "true" \\\r\n -archiveLogDest "${NEW_ARCH_PATH1}" \\\r\n -listeners "${INIT_LISTENER_REG}" \\\r\n -nationalCharacterSet "AL16UTF16" \\\r\n -olsConfiguration "false" \\\r\n -recoveryAreaDestination "NONE" \\\r\n -redoLogFileSize "${NEW_REDO_SIZE_MB}" \\\r\n -registerWithDirService "false" \\\r\n -sampleSchema "false" \\\r\n -sid "${NEW_ORACLE_SID}" \\\r\n -sysPassword "${NEW_SYS_PWD}" \\\r\n -systemPassword "${NEW_SYSTEM_PWD}" \\\r\n -useOMF "false" \\\r\n -variablesFile \/tmp\/dbca-vars.txt \\\r\n -initParams "compatible=${INIT_COMPATIBLE},control_management_pack_access=${INIT_CMPA},db_domain=${NEW_DB_DOMAIN},db_name=${NEW_DB_NAME},db_unique_name=${NEW_DB_UNIQUE_NAME},local_listener=${INIT_LISTENER_REG},optimizer_features_enable=${INIT_OFE},pga_aggregate_target=${INIT_PGA_AGG_TGT},processes=${INIT_PROCESSES},sga_target=${INIT_SGA_TGT}"\r\n<\/pre>\nCouple of things to note:<\/p>\n
I’ve been playing with Oracle 12.2 lately, which also means I do lots of silent installations of it :-). I don’t know why I just put a smiley there because there was nothing funny about it! Troubleshooting DBCA errors, combing through logs, and raising service requests with Oracle Support almost sucked the joy out of […]<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[2,14],"tags":[],"class_list":["post-155","post","type-post","status-publish","format-standard","hentry","category-general","category-silent"],"_links":{"self":[{"href":"https:\/\/www.spotonoracle.com\/index.php?rest_route=\/wp\/v2\/posts\/155","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.spotonoracle.com\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.spotonoracle.com\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.spotonoracle.com\/index.php?rest_route=\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/www.spotonoracle.com\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=155"}],"version-history":[{"count":5,"href":"https:\/\/www.spotonoracle.com\/index.php?rest_route=\/wp\/v2\/posts\/155\/revisions"}],"predecessor-version":[{"id":160,"href":"https:\/\/www.spotonoracle.com\/index.php?rest_route=\/wp\/v2\/posts\/155\/revisions\/160"}],"wp:attachment":[{"href":"https:\/\/www.spotonoracle.com\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=155"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.spotonoracle.com\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=155"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.spotonoracle.com\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=155"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}