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.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
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.

1
2
3
4
5
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:

1
2
3
4
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

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
266
267
268
269
270
271
272
273
274
275
276
277
278
279
280
281
282
283
284
285
286
<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:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
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.