April | 2017 | Spot on Oracle

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 the new 12.2 release.

That’s what I’ve stumbled upon:

DBCA parameter "-variables" is not processed correctly. Oracle has acknowledge this as bug 25353173. Workaround: put name=value list in a file and use "-variablesFile <path-to-file>"
Controlfiles are not multiplexed to different paths as specified. First given path is used for all controlfile members (SR is "Work In Progress")
Specifying <initParam name="os_authent_prefix" value=""/> results in parameter to be set to "ops$". In 12.2 you have to specify the single quotes '' like this: <initParam name="os_authent_prefix" value="''"/>. If you want an empty value for a parameter you better set it in ''. (*)
Maybe not a bug but still annoying: specify DB_UNIQUE_NAME different from DB_NAME and your file paths will be appended with it.

* 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.

Anyway, enough with the rambling. I will show now how I setup my lab systems.

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.

NEW_ORACLE_SID=DEV1
NEW_DB_NAME=DEV1
NEW_DB_DOMAIN=localdomain
NEW_DB_CHARSET=AL32UTF8
NEW_SYS_PWD=manager
NEW_SYSTEM_PWD=manager
NEW_REDO_SIZE_MB=256

INIT_CMPA=DIAGNOSTIC+TUNING
INIT_COMPATIBLE=12.2.0.0
INIT_OFE=12.2.0.1
INIT_PGA_AGG_TGT_MB=512
INIT_PROCESSES=200
INIT_SGA_TGT_MB=4096
INIT_CPU_COUNT=4

NEW_DATA_PATH1=/u02/oradata/${NEW_DB_NAME}
NEW_REDO_PATH1=/u02/oradata/${NEW_DB_NAME}
NEW_REDO_PATH2=/u03/oradata/${NEW_DB_NAME}
NEW_ARCH_PATH1=/u04/oradata/${NEW_DB_NAME}

NEW_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.

You don’t have to modify following variables as they are “calculated” values.

NEW_DB_UNIQUE_NAME=${NEW_DB_NAME}
NEW_GLOBAL_DBNAME=${NEW_DB_NAME}.${NEW_DB_DOMAIN}
INIT_PGA_AGG_TGT=$((${INIT_PGA_AGG_TGT_MB} * 1024 * 1024))
INIT_SGA_TGT=$((${INIT_SGA_TGT_MB} * 1024 * 1024))
INIT_LISTENER_REG=LISTENER_${NEW_DB_NAME}

Just make sure you have a listener running with the name specified in INIT_LISTENER_REG.

Now, because of bug 25353173 we must put what we’d normally feed to "-variables" into a file:

echo "DATA_PATH1=${NEW_DATA_PATH1}" >  /tmp/dbca-vars.txt
echo "REDO_PATH1=${NEW_REDO_PATH1}" >> /tmp/dbca-vars.txt
echo "REDO_PATH2=${NEW_REDO_PATH2}" >> /tmp/dbca-vars.txt
echo "ARCH_PATH1=${NEW_ARCH_PATH1}" >> /tmp/dbca-vars.txt

Before 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.
I know it’s awfully long but you can skip reading the template if you just want to go ahead and create the database.
Save this as /tmp/dbca-newdb-linux.dbt

<DatabaseTemplate name="New Database" description="" version="12.2.0.1.0">
   <CommonAttributes>
      <option name="OMS" value="false"/>
      <option name="JSERVER" value="false"/>
      <option name="SPATIAL" value="false"/>
      <option name="IMEDIA" value="false"/>
      <option name="ORACLE_TEXT" value="false">
         <tablespace id="SYSAUX"/>
      </option>
      <option name="CWMLITE" value="false">
         <tablespace id="SYSAUX"/>
      </option>
      <option name="SAMPLE_SCHEMA" value="false"/>
      <option name="APEX" value="false"/>
      <option name="DV" value="false"/>
   </CommonAttributes>
   <Variables/>
   <CustomScripts Execute="false"/>
   <InitParamAttributes>
      <InitParams>
         <initParam name="autotask_max_active_pdbs" value="1"/>
         <initParam name="awr_pdb_autoflush_enabled" value="true"/>
         <initParam name="awr_snapshot_time_offset" value="1000000"/>
         <initParam name="commit_logging" value="IMMEDIATE"/>
         <initParam name="commit_wait" value="WAIT"/>
         <initParam name="containers_parallel_degree" value="2"/>
         <initParam name="control_files" value="(&quot;{DATA_PATH1}/ctrl-001-{DB_NAME}.dbf&quot;, &quot;{REDO_PATH1}/ctrl-002-{DB_NAME}.dbf&quot;, &quot;{REDO_PATH2}/ctrl-003-{DB_NAME}.dbf&quot;)"/>
         <initParam name="control_file_record_keep_time" value="35"/>
         <initParam name="cursor_invalidation" value="DEFERRED"/>
         <initParam name="db_block_checking" value="MEDIUM"/>
         <initParam name="db_block_checksum" value="FULL"/>
         <initParam name="db_block_size" value="8" unit="KB"/>
         <initParam name="db_files" value="100"/>
         <initParam name="db_flashback_retention_target" value="1440"/>  
         <initParam name="db_securefile" value="ALWAYS"/>
         <initParam name="deferred_segment_creation" value="FALSE"/>
         <initParam name="dg_broker_config_file1" value="{ORACLE_BASE}/admin/{DB_NAME}/instance/dr-001-{DB_NAME}.dat"/>
         <initParam name="dg_broker_config_file2" value="{ORACLE_BASE}/admin/{DB_NAME}/instance/dr-002-{DB_NAME}.dat"/>
         <initParam name="diagnostic_dest" value="{ORACLE_BASE}/adr"/>
         <initParam name="dispatchers" value="''"/>
         <initParam name="enable_pluggable_database" value="TRUE"/>
         <initParam name="filesystemio_options" value="SETALL"/>
         <initParam name="inmemory_adg_enabled" value="FALSE"/>
         <initParam name="inmemory_force" value="OFF"/>
         <initParam name="inmemory_query" value="DISABLE"/>
         <initParam name="inmemory_size" value="0"/>
         <initParam name="log_archive_dest_1" value="LOCATION={ARCH_PATH1}"/>
         <initParam name="log_archive_dest_state_1" value="ENABLE"/>
         <initParam name="log_archive_format" value="{DB_NAME}-%t-%s-%r.arc"/>
         <initParam name="max_pdbs" value="1"/>
         <initParam name="nls_language" value="AMERICAN"/>
         <initParam name="nls_nchar_conv_excp" value="TRUE"/>
         <initParam name="nls_territory" value="AMERICA"/>
         <initParam name="open_cursors" value="300"/>
         <initParam name="os_authent_prefix" value="''"/>
         <initParam name="parallel_min_servers" value="0"/>
         <initParam name="remote_listener" value="''"/>
         <initParam name="shared_servers" value="0"/>
         <initParam name="temp_undo_enabled" value="TRUE"/>
         <initParam name="undo_retention" value="1800"/>
         <initParam name="undo_tablespace" value="UNDOTS"/>
         <initParam name="uniform_log_timestamp_format" value="TRUE"/>
      </InitParams>
      <MiscParams>
         <databaseType>MULTIPURPOSE</databaseType>
         <maxUserConn>20</maxUserConn>
         <percentageMemTOSGA>40</percentageMemTOSGA>
         <archiveLogMode>true</archiveLogMode>
         <initParamFileName>{ORACLE_BASE}/admin/{DB_NAME}/create/init.ora</initParamFileName>
      </MiscParams>
      <SPfile useSPFile="true">{ORACLE_BASE}/admin/{DB_NAME}/instance/spfile{SID}.ora</SPfile>
   </InitParamAttributes>
   <StorageAttributes>
      <ControlfileAttributes id="Controlfile">
         <maxDatafiles>100</maxDatafiles>
         <maxLogfiles>16</maxLogfiles>
         <maxLogMembers>3</maxLogMembers>
         <maxLogHistory>1</maxLogHistory>
         <maxInstances>8</maxInstances>
         <image name="ctrl-001-{DB_NAME}.dbf" filepath="{DATA_PATH1}/"/>
         <image name="ctrl-002-{DB_NAME}.dbf" filepath="{REDO_PATH1}/"/>
         <image name="ctrl-003-{DB_NAME}.dbf" filepath="{REDO_PATH2}/"/>
      </ControlfileAttributes>
      <DatafileAttributes id="{DATA_PATH1}/sysaux-001-{DB_NAME}.dbf">
         <tablespace>SYSAUX</tablespace>
         <temporary>false</temporary>
         <online>true</online>
         <status>0</status>
         <size unit="MB">256</size>
         <reuse>true</reuse>
         <autoExtend>true</autoExtend>
         <increment unit="KB">8192</increment>
         <maxSize unit="MB">4096</maxSize>
      </DatafileAttributes>
      <DatafileAttributes id="{DATA_PATH1}/toolts-001-{DB_NAME}.dbf">
         <tablespace>TOOLTS</tablespace>
         <temporary>false</temporary>
         <online>true</online>
         <status>0</status>
         <size unit="MB">56</size>
         <reuse>true</reuse>
         <autoExtend>true</autoExtend>
         <increment unit="KB">4096</increment>
         <maxSize unit="MB">2048</maxSize>
      </DatafileAttributes>
      <DatafileAttributes id="{DATA_PATH1}/system-001-{DB_NAME}.dbf">
         <tablespace>SYSTEM</tablespace>
         <temporary>false</temporary>
         <online>true</online>
         <status>0</status>
         <size unit="MB">1024</size>
         <reuse>true</reuse>
         <autoExtend>true</autoExtend>
         <increment unit="KB">8192</increment>
         <maxSize unit="MB">4096</maxSize>
      </DatafileAttributes>
      <DatafileAttributes id="{DATA_PATH1}/tempts-001-{DB_NAME}.dbf">
         <tablespace>TEMPTS</tablespace>
         <temporary>false</temporary>
         <online>true</online>
         <status>0</status>
         <size unit="MB">128</size>
         <reuse>true</reuse>
         <autoExtend>true</autoExtend>
         <increment unit="KB">2048</increment>
         <maxSize unit="MB">8192</maxSize>
      </DatafileAttributes>
      <DatafileAttributes id="{DATA_PATH1}/undots-001-{DB_NAME}.dbf">
         <tablespace>UNDOTBS1</tablespace>
         <temporary>false</temporary>
         <online>true</online>
         <status>0</status>
         <size unit="MB">512</size>
         <reuse>true</reuse>
         <autoExtend>true</autoExtend>
         <increment unit="KB">2028</increment>
         <maxSize unit="MB">8192</maxSize>
      </DatafileAttributes>
      <TablespaceAttributes id="SYSAUX">
         <online>true</online>
         <offlineMode>1</offlineMode>
         <readOnly>false</readOnly>
         <temporary>false</temporary>
         <defaultTemp>false</defaultTemp>
         <undo>false</undo>
         <local>true</local>
         <blockSize>-1</blockSize>
         <allocation>1</allocation>
         <uniAllocSize unit="KB">-1</uniAllocSize>
         <initSize unit="KB">64</initSize>
         <increment unit="KB">64</increment>
         <incrementPercent>50</incrementPercent>
         <minExtends>1</minExtends>
         <maxExtends>4096</maxExtends>
         <minExtendsSize unit="KB">64</minExtendsSize>
         <logging>true</logging>
         <recoverable>false</recoverable>
         <maxFreeSpace>0</maxFreeSpace>
         <bigfile>false</bigfile>
         <datafilesList>
            <TablespaceDatafileAttributes id="{DATA_PATH1}/sysaux-001-{DB_NAME}.dbf"/>
         </datafilesList>
      </TablespaceAttributes>
      <TablespaceAttributes id="TOOLTS">
         <online>true</online>
         <offlineMode>1</offlineMode>
         <readOnly>false</readOnly>
         <temporary>false</temporary>
         <defaultTemp>false</defaultTemp>
         <undo>false</undo>
         <local>true</local>
         <blockSize>-1</blockSize>
         <allocation>1</allocation>
         <uniAllocSize unit="KB">-1</uniAllocSize>
         <initSize unit="KB">128</initSize>
         <increment unit="KB">128</increment>
         <incrementPercent>0</incrementPercent>
         <minExtends>1</minExtends>
         <maxExtends>4096</maxExtends>
         <minExtendsSize unit="KB">128</minExtendsSize>
         <logging>true</logging>
         <recoverable>false</recoverable>
         <maxFreeSpace>0</maxFreeSpace>
         <bigfile>false</bigfile>
         <datafilesList>
            <TablespaceDatafileAttributes id="{DATA_PATH1}/toolts-001-{DB_NAME}.dbf"/>
         </datafilesList>
      </TablespaceAttributes>
      <TablespaceAttributes id="SYSTEM">
         <online>true</online>
         <offlineMode>1</offlineMode>
         <readOnly>false</readOnly>
         <temporary>false</temporary>
         <defaultTemp>false</defaultTemp>
         <undo>false</undo>
         <local>true</local>
         <blockSize>-1</blockSize>
         <allocation>3</allocation>
         <uniAllocSize unit="KB">-1</uniAllocSize>
         <initSize unit="KB">64</initSize>
         <increment unit="KB">64</increment>
         <incrementPercent>50</incrementPercent>
         <minExtends>1</minExtends>
         <maxExtends>-1</maxExtends>
         <minExtendsSize unit="KB">64</minExtendsSize>
         <logging>true</logging>
         <recoverable>false</recoverable>
         <maxFreeSpace>0</maxFreeSpace>
         <bigfile>false</bigfile>
         <datafilesList>
            <TablespaceDatafileAttributes id="{DATA_PATH1}/system-001-{DB_NAME}.dbf"/>
         </datafilesList>
      </TablespaceAttributes>
      <TablespaceAttributes id="TEMPTS">
         <online>true</online>
         <offlineMode>1</offlineMode>
         <readOnly>false</readOnly>
         <temporary>true</temporary>
         <defaultTemp>true</defaultTemp>
         <undo>false</undo>
         <local>true</local>
         <blockSize>-1</blockSize>
         <allocation>1</allocation>
         <uniAllocSize unit="KB">1012</uniAllocSize>
         <initSize unit="KB">64</initSize>
         <increment unit="KB">64</increment>
         <incrementPercent>0</incrementPercent>
         <minExtends>1</minExtends>
         <maxExtends>0</maxExtends>
         <minExtendsSize unit="KB">64</minExtendsSize>
         <logging>true</logging>
         <recoverable>false</recoverable>
         <maxFreeSpace>0</maxFreeSpace>
         <bigfile>false</bigfile>
         <datafilesList>
            <TablespaceDatafileAttributes id="{DATA_PATH1}/tempts-001-{DB_NAME}.dbf"/>
         </datafilesList>
      </TablespaceAttributes>
      <TablespaceAttributes id="UNDOTS">
         <online>true</online>
         <offlineMode>1</offlineMode>
         <readOnly>false</readOnly>
         <temporary>false</temporary>
         <defaultTemp>false</defaultTemp>
         <undo>true</undo>
         <local>true</local>
         <blockSize>-1</blockSize>
         <allocation>1</allocation>
         <uniAllocSize unit="KB">-1</uniAllocSize>
         <initSize unit="KB">512</initSize>
         <increment unit="KB">512</increment>
         <incrementPercent>50</incrementPercent>
         <minExtends>8</minExtends>
         <maxExtends>4096</maxExtends>
         <minExtendsSize unit="KB">512</minExtendsSize>
         <logging>true</logging>
         <recoverable>false</recoverable>
         <maxFreeSpace>0</maxFreeSpace>
         <bigfile>false</bigfile>
         <datafilesList>
            <TablespaceDatafileAttributes id="{DATA_PATH1}/undots-001-{DB_NAME}.dbf"/>
         </datafilesList>
      </TablespaceAttributes>
      <RedoLogGroupAttributes id="1">
         <reuse>false</reuse>
         <fileSize unit="KB">204800</fileSize>
         <Thread>1</Thread>
         <member ordinal="0" memberName="redo-g1-m1-{DB_NAME}.dbf" filepath="{REDO_PATH1}/"/>
         <member ordinal="1" memberName="redo-g1-m2-{DB_NAME}.log" filepath="{REDO_PATH2}/"/>
      </RedoLogGroupAttributes>
      <RedoLogGroupAttributes id="2">
         <reuse>false</reuse>
         <fileSize unit="KB">204800</fileSize>
         <Thread>1</Thread>
         <member ordinal="0" memberName="redo-g2-m1-{DB_NAME}.dbf" filepath="{REDO_PATH1}/"/>
         <member ordinal="1" memberName="redo-g2-m2-{DB_NAME}.log" filepath="{REDO_PATH2}/"/>
      </RedoLogGroupAttributes>
      <RedoLogGroupAttributes id="3">
         <reuse>false</reuse>
         <fileSize unit="KB">204800</fileSize>
         <Thread>1</Thread>
         <member ordinal="0" memberName="redo-g3-m1-{DB_NAME}.dbf" filepath="{REDO_PATH1}/"/>
         <member ordinal="1" memberName="redo-g3-m2-{DB_NAME}.log" filepath="{REDO_PATH2}/"/>
      </RedoLogGroupAttributes>
   </StorageAttributes>
</DatabaseTemplate>

We’re now ready to create the database by calling DBCA:

export ORACLE_BASE=/u01/app/oracle
export ORACLE_HOME=${ORACLE_BASE}/product/ora12201
export PATH=${ORACLE_HOME}/bin:${PATH}

${ORACLE_HOME}/bin/dbca -silent -createDatabase \
  -templateName "/tmp/dbca-newdb-linux.dbt" \
  -gdbName "${NEW_GLOBAL_DBNAME}" \
  -characterSet "${NEW_DB_CHARSET}" \
  -createAsContainerDatabase "true" \
  -databaseConfigType SINGLE \
  -dbOptions "ORACLE_TEXT:true" \
  -databaseType MULTIPURPOSE \
  -dvConfiguration "false" \
  -emConfiguration "NONE" \
  -enableArchive "true" \
  -archiveLogDest "${NEW_ARCH_PATH1}" \
  -listeners "${INIT_LISTENER_REG}" \
  -nationalCharacterSet "AL16UTF16" \
  -olsConfiguration "false" \
  -recoveryAreaDestination "NONE" \
  -redoLogFileSize "${NEW_REDO_SIZE_MB}" \
  -registerWithDirService "false" \
  -sampleSchema "false" \
  -sid "${NEW_ORACLE_SID}" \
  -sysPassword "${NEW_SYS_PWD}" \
  -systemPassword "${NEW_SYSTEM_PWD}" \
  -useOMF "false" \
  -variablesFile /tmp/dbca-vars.txt \
  -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}"

Couple of things to note:

This will create an empty CDB
The template specifies not to install all the options. In this example I’ve chosen I want to install Oracle Text
The database will be in archivelog mode
Because of a bug, the controlfiles are not be multiplexed. You have to correct this manually after DBCA finishes
The DBCA can be extremely flexible by using variables (placeholders in template)
A lot can be configured via DBCA but not everything. E.g. I like most DB’s running in FORCE LOGGING mode
It never hurts to run "dbca -silent -<command> -h" to see what it can do

Spot on Oracle

Hopefully enlightening

Monthly Archives: April 2017

Sh! – silent installation (DBCA)