11 September 2017

Create a 12.2 database with dbca silent - with minimal components


We recently worked on an upgrade from 11.2 to 12.2. This made us think about the environment and the options we needed - or not needed. So we tried to setup the environment with a minimum amount of options. Why? simply because we don't need these components and will never use them in the future. And, as Mike Dietrich states - and I agree on him, future upgrades will run faster.

Default, we get a whole bunch of options installed. But the following gave us a small environment, with XML and JAVA only.

 $ORACLE_HOME/bin/dbca -silent -createDatabase -responseFile SMALLDB.rsp   

This SMALLDB.rsp responsefile looks like (no, these are not my passwords or db name).

 responseFileVersion=/oracle/assistants/rspfmt_dbca_response_schema_v12.2.0  
 gdbName=SMALLDB01  
 databaseConfigType=SI  
 createAsContainerDatabase=false  
 templateName=SMALLDB.dbt  
 sysPassword=PassWord#123  
 systemPassword=PassWord#123  
 dvConfiguration=false  
 olsConfiguration=false  
 dbOptions=JSERVER:true,ORACLE_TEXT:false,IMEDIA:false,CWMLITE:false,SPATIAL:false,OMS:false,APEX:false,DV:false  
 datafileDestination=/data/oradata/  
 recoveryAreaDestination=/data/fra/  
 storageType=FS  
 characterSet=AL32UTF8  
 redoLogFileSize=400  
 listeners=LISTENER  
 initParams=db_recovery_file_dest_size=200G,processes=1400,sga_max_size=8G,sga_target=2G  


What we need, is to setup the template in $ORACLE_HOME/assistants/dbca/templates/SMALLDB.dbt .
You can do this with a graphical dbca, or copy and change an existing template. Note that the important part is in the 'CommonAttributes'. Where we use dbOptions in the responsefile, these options will be not-activated (v$option shows them as FALSE). But the components get installed. If you want them to not be installed, you will need to put that in the template file as shown below.

 <DatabaseTemplate name="SMALLDB" description="Small install for database" version="12.2.0.1.0">  
   <CommonAttributes>  
    <option name="OMS" value="false"/>  
    <option name="JSERVER" value="true"/>  
    <option name="SPATIAL" value="false"/>  
    <option name="IMEDIA" value="false"/>  
    <option name="ORACLE_TEXT" value="false">  
      <tablespace id="SYSAUX"/>  
    </option>  
    <option name="SAMPLE_SCHEMA" value="false"/>  
    <option name="CWMLITE" value="false">  
      <tablespace id="SYSAUX"/>  
    </option>  
    <option name="APEX" value="false"/>  
    <option name="DV" value="false"/>  
   </CommonAttributes>  
   <Variables/>  
   <CustomScripts Execute="false"/>  
   <InitParamAttributes>  
    <InitParams>  
      <initParam name="db_name" value=""/>  
      <initParam name="dispatchers" value="(PROTOCOL=TCP) (SERVICE=XDB)"/>  
      <initParam name="audit_file_dest" value="{ORACLE_BASE}/admin/{DB_UNIQUE_NAME}/adump"/>  
      <initParam name="compatible" value="12.2.0"/>  
      <initParam name="remote_login_passwordfile" value="EXCLUSIVE"/>  
      <initParam name="processes" value="300"/>  
      <initParam name="undo_tablespace" value="UNDOTBS1"/>  
      <initParam name="control_files" value="(&quot;/data/oradata/{DB_UNIQUE_NAME}/control01.ctl&quot;, &quot;/data/fra/{DB_UNIQUE_NAME}/control02.ctl&quot;)"/>  
      <initParam name="diagnostic_dest" value="{ORACLE_BASE}"/>  
      <initParam name="db_recovery_file_dest" value="/data/fra/{DB_UNIQUE_NAME}"/>  
      <initParam name="audit_trail" value="db"/>  
      <initParam name="nls_territory" value="AMERICA"/>  
      <initParam name="db_block_size" value="8192"/>  
      <initParam name="open_cursors" value="300"/>  
      <initParam name="nls_language" value="AMERICAN"/>  
      <initParam name="db_recovery_file_dest_size" value="200" unit="GB"/>  
    </InitParams>  
    <MiscParams>  
      <databaseType>MULTIPURPOSE</databaseType>  
      <maxUserConn>20</maxUserConn>  
      <percentageMemTOSGA>20</percentageMemTOSGA>  
      <customSGA>false</customSGA>  
      <characterSet>AL32UTF8</characterSet>  
      <nationalCharacterSet>AL16UTF16</nationalCharacterSet>  
      <archiveLogMode>false</archiveLogMode>  
      <initParamFileName>{ORACLE_BASE}/admin/{DB_UNIQUE_NAME}/pfile/init.ora</initParamFileName>  
    </MiscParams>  
    <SPfile useSPFile="true">/opt/oracle/product/12.2.0/dbhome_1/dbs/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="control01.ctl" filepath="/data/oradata/{DB_UNIQUE_NAME}/"/>  
      <image name="control02.ctl" filepath="/data/fra/{DB_UNIQUE_NAME}"/>  
    </ControlfileAttributes>  
    <DatafileAttributes id="/data/oradata/{DB_UNIQUE_NAME}/sysaux01.dbf" con_id="1">  
      <tablespace>SYSAUX</tablespace>  
      <temporary>false</temporary>  
      <online>true</online>  
      <status>0</status>  
      <size unit="MB">550</size>  
      <reuse>true</reuse>  
      <autoExtend>true</autoExtend>  
      <increment unit="KB">10240</increment>  
      <maxSize unit="MB">-1</maxSize>  
    </DatafileAttributes>  
    <DatafileAttributes id="/data/oradata/{DB_UNIQUE_NAME}/system01.dbf" con_id="1">  
      <tablespace>SYSTEM</tablespace>  
      <temporary>false</temporary>  
      <online>true</online>  
      <status>0</status>  
      <size unit="MB">700</size>  
      <reuse>true</reuse>  
      <autoExtend>true</autoExtend>  
      <increment unit="KB">10240</increment>  
      <maxSize unit="MB">-1</maxSize>  
    </DatafileAttributes>  
    <DatafileAttributes id="/data/oradata/{DB_UNIQUE_NAME}/temp01.dbf" con_id="1">  
      <tablespace>TEMP</tablespace>  
      <temporary>false</temporary>  
      <online>true</online>  
      <status>0</status>  
      <size unit="MB">20</size>  
      <reuse>true</reuse>  
      <autoExtend>true</autoExtend>  
      <increment unit="KB">640</increment>  
      <maxSize unit="MB">-1</maxSize>  
    </DatafileAttributes>  
    <DatafileAttributes id="/data/oradata/{DB_UNIQUE_NAME}/undotbs01.dbf" con_id="1">  
      <tablespace>UNDOTBS1</tablespace>  
      <temporary>false</temporary>  
      <online>true</online>  
      <status>0</status>  
      <size unit="MB">200</size>  
      <reuse>true</reuse>  
      <autoExtend>true</autoExtend>  
      <increment unit="KB">5120</increment>  
      <maxSize unit="MB">-1</maxSize>  
    </DatafileAttributes>  
    <DatafileAttributes id="/data/oradata/{DB_UNIQUE_NAME}/users01.dbf" con_id="1">  
      <tablespace>USERS</tablespace>  
      <temporary>false</temporary>  
      <online>true</online>  
      <status>0</status>  
      <size unit="MB">5</size>  
      <reuse>true</reuse>  
      <autoExtend>true</autoExtend>  
      <increment unit="KB">1280</increment>  
      <maxSize unit="MB">-1</maxSize>  
    </DatafileAttributes>  
    <TablespaceAttributes id="SYSAUX" con_id="1">  
      <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>  
      <autoSegmentMgmt>true</autoSegmentMgmt>  
      <bigfile>false</bigfile>  
      <datafilesList>  
       <TablespaceDatafileAttributes id="/data/oradata/{DB_UNIQUE_NAME}/sysaux01.dbf"/>  
      </datafilesList>  
    </TablespaceAttributes>  
    <TablespaceAttributes id="SYSTEM" con_id="1">  
      <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>  
      <autoSegmentMgmt>true</autoSegmentMgmt>  
      <bigfile>false</bigfile>  
      <datafilesList>  
       <TablespaceDatafileAttributes id="/data/oradata/{DB_UNIQUE_NAME}/system01.dbf"/>  
      </datafilesList>  
    </TablespaceAttributes>  
    <TablespaceAttributes id="TEMP" con_id="1">  
      <temporary>true</temporary>  
      <defaultTemp>true</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>0</incrementPercent>  
      <minExtends>1</minExtends>  
      <maxExtends>0</maxExtends>  
      <minExtendsSize unit="KB">64</minExtendsSize>  
      <logging>true</logging>  
      <recoverable>false</recoverable>  
      <maxFreeSpace>0</maxFreeSpace>  
      <autoSegmentMgmt>true</autoSegmentMgmt>  
      <bigfile>false</bigfile>  
      <datafilesList>  
       <TablespaceDatafileAttributes id="/data/oradata/{DB_UNIQUE_NAME}/temp01.dbf"/>  
      </datafilesList>  
    </TablespaceAttributes>  
    <TablespaceAttributes id="UNDOTBS1" con_id="1">  
      <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>  
      <autoSegmentMgmt>true</autoSegmentMgmt>  
      <bigfile>false</bigfile>  
      <datafilesList>  
       <TablespaceDatafileAttributes id="/data/oradata/{DB_UNIQUE_NAME}/undotbs01.dbf"/>  
      </datafilesList>  
    </TablespaceAttributes>  
    <TablespaceAttributes id="USERS" con_id="1">  
      <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>  
      <autoSegmentMgmt>true</autoSegmentMgmt>  
      <bigfile>false</bigfile>  
      <datafilesList>  
       <TablespaceDatafileAttributes id="/data/oradata/{DB_UNIQUE_NAME}/users01.dbf"/>  
      </datafilesList>  
    </TablespaceAttributes>  
    <RedoLogGroupAttributes id="1">  
      <reuse>false</reuse>  
      <fileSize unit="KB">204800</fileSize>  
      <Thread>1</Thread>  
      <member ordinal="0" memberName="redo01.log" filepath="/data/oradata/{DB_UNIQUE_NAME}/"/>  
    </RedoLogGroupAttributes>  
    <RedoLogGroupAttributes id="2">  
      <reuse>false</reuse>  
      <fileSize unit="KB">204800</fileSize>  
      <Thread>1</Thread>  
      <member ordinal="0" memberName="redo02.log" filepath="/data/oradata/{DB_UNIQUE_NAME}/"/>  
    </RedoLogGroupAttributes>  
    <RedoLogGroupAttributes id="3">  
      <reuse>false</reuse>  
      <fileSize unit="KB">204800</fileSize>  
      <Thread>1</Thread>  
      <member ordinal="0" memberName="redo03.log" filepath="/data/oradata/{DB_UNIQUE_NAME}/"/>  
    </RedoLogGroupAttributes>  
   </StorageAttributes>  
 </DatabaseTemplate>  

Let the dbca run, which took me some 20mins. After that, I only manually de-install the Oracle Workspace Manager:

 $ORACLE_HOME/bin/sqlplus -s "/as sysdba" @?/rdbms/admin/owmuinst.plb   

Now we have the following installed, which is just fine for our environment:

 SQL> select comp_id, comp_name from dba_registry;  

 COMP_ID   COMP_NAME  
 --------- ----------------------------------------  
 CATALOG   Oracle Database Catalog Views  
 CATPROC   Oracle Database Packages and Types  
 JAVAVM    JServer JAVA Virtual Machine  
 XML       Oracle XDK  
 CATJAVA   Oracle Database Java Packages  
 XDB       Oracle XML Database  

 6 rows selected.  

If you skip JAVA as well (in your responsefile and template), you will get only CATALOG, CATPROC and XDB (after removing OWS manually as seen above). That is the minimum you can get.

3 comments: