Sh! – silent installation (DBCA)

7 Replies

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

7 thoughts on “Sh! – silent installation (DBCA)”

  1. son Post author

    Bug 25353173 has been fixed and patch can be downloaded from https://support.oracle.com. After applying patch you can use “-variables=…” instead of putting the placeholders in a file and reference it with “-variablesFile=/path/to/file”

    Reply ↓

  2. Pingback: Rant: Does Oracle care anymore? | Spot on Oracle

  3. Pingback: DBCA command line | IT Remote

  4. Felice

    Hello, May I know how you resolve the controlfile multiplex issue?
    Mine is still defaulting to the datafile path even if I specify the controlfile location.

    Reply ↓
    1. son Post author

      Hi,

      My appologies, somehow I missed your comment completely šŸ™

      With 19c it works by specifying DBCA parameter ā€œ-honorControlFileInitParamā€. Of course you need to specify the control_file parameter in the template file or as a parameter for ā€œ-initParamsā€.
      (maybe that would work with 12.2 as well ā€“ I have never tested that).

      Best regards

      Reply ↓
  5. Dan H

    Could you provide more details about the SR you reference concerning
    controlfiles not being multiplexed to different paths?
    I am running into that same problem with v19.3.0.
    My SR with Oracle has been turned into a bug.
    Engineering is saying that it can be done with correct syntax.

    Reply ↓
    1. son Post author

      Hi,

      Unfortunately, I do no longer have access to this SR (change of CSIs), so I can’t give you any more details. Sorry.
      With 19c it works by specifying DBCA parameter “-honorControlFileInitParam”. Of course you need to specify the control_file parameter in the template file or as a parameter for “-initParams”.
      (maybe that would work with 12.2 as well – I have never tested that).

      Hope that helps!

      Reply ↓

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.