The vendor Nutanix provides a “Database as a Service” solution called ERA which enables to provision databases together with VMs over a GUI, CLI or API. Since some months I am working with this nice hyperconverged infrastructure and I believe it will become the future standard of on-premise IT. As part of my daily job the target was to provision Oracle databases with ERA and add the company related db standards including security etc.
ERA allows to create parameter profiles and allows to define things like block size, size of undo/temp tablespace, number of processes SGA and PGA. This is a very good start and majority of end-users are fine with this. However I want to modify more things.
I also identified that by default ERA is installing all available DB options. We made some bad experiences in terms of patching when for example also the JAVA-option is installed. On top the db footprint is increased because of multiple users and additional storage usage at SYSTEM and SYSAUX tablespace. So we want also that not all db options (in total 15) get installed by default.
SQL> select COMP_NAME, VERSION, STATUS from dba_registry order by 1;
COMP_NAME VERSION STATUS
---------------------------------------- ------------------------------ ------
Oracle Database Catalog Views 19.0.0.0.0 VALID
Oracle Database Packages and Types 19.0.0.0.0 VALID
Oracle Real Application Clusters 19.0.0.0.0 VALID
JServer JAVA Virtual Machine 19.0.0.0.0 VALID
Oracle XDK 19.0.0.0.0 VALID
Oracle Database Java Packages 19.0.0.0.0 VALID
OLAP Analytic Workspace 19.0.0.0.0 VALID
Oracle XML Database 19.0.0.0.0 VALID
Oracle Workspace Manager 19.0.0.0.0 VALID
Oracle Text 19.0.0.0.0 VALID
Oracle Multimedia 19.0.0.0.0 VALID
Spatial 19.0.0.0.0 VALID
Oracle OLAP API 19.0.0.0.0 VALID
Oracle Label Security 19.0.0.0.0 VALID
Oracle Database Vault 19.0.0.0.0 VALID
15 rows selected.
Code-Sprache: CSS (css)You have to know that ERA is performing a DBCA silent installation in the background and then apply parameter profiles. DBCA should be very known if you are dealing with DB installation. The good thing here is that it uses templates in the format of .dbc – Database Clone or .dbt – Template Database. It is fetching the template General_Purpose.dbc and that’s the point we have to invent!
#If template name is passed , use it
templatestr= -templateName General_Purpose.dbc
if [ $db_block_size -ne 8 ]; then
templatestr= -templateName New_Database.dbt
fi
Code-Sprache: Bash (bash)Go to your golden image VM where you have created the software profile. Then create your own dbt-file at location $ORACLE_HOME/assistants/dbca/templates and rename it to General_Purpose.dbc with the level of customization you need.
oracle@myserver:/app/oracle/product/19c/assistants/dbca/templates [DBNAME]# ll
total 371008
-rw-r----- 1 oracle oinstall 4888 Apr 17 2019 Data_Warehouse.dbc
-rw-r----- 1 oracle oinstall 14094 Jan 7 13:42 General_Purpose.dbc
-rw-r----- 1 oracle oinstall 13755 Jan 5 11:09 General_Purpose.dbc.old
-rw-r----- 1 oracle oinstall 10772 Apr 5 2019 New_Database.dbt
-rw-r----- 1 oracle oinstall 86548480 Apr 17 2019 pdbseed.dfb
-rw-r----- 1 oracle oinstall 6611 Apr 17 2019 pdbseed.xml
-rw-r----- 1 oracle oinstall 18726912 Apr 17 2019 Seed_Database.ctl
-rw-r----- 1 oracle oinstall 274554880 Apr 17 2019 Seed_Database.dfb
Code-Sprache: PHP (php)For instance my file looks like this:
- I’ve removed nearly all db options
- I’ve added a lot of other parameters
- Adjusted redo log size from 200 MB to 512 MB and multiplexing to 4 groups
- Adjusting Tablespace size and type (smallfile vs bigfile)
- Adding customer tabelspacs by default
<?xml version = '1.0' encoding = 'UTF-8'?>
<DatabaseTemplate name="Test" description="" version="19.0.0.0.0">
<CommonAttributes>
<option name="JSERVER" value="false" includeInPDBs="false"/>
<option name="ORACLE_TEXT" value="false" includeInPDBs="false">
<tablespace id="SYSAUX"/>
</option>
<option name="IMEDIA" value="false" includeInPDBs="false"/>
<option name="CWMLITE" value="false" includeInPDBs="false">
<tablespace id="SYSAUX"/>
</option>
<option name="SPATIAL" value="false" includeInPDBs="false"/>
<option name="OMS" value="false" includeInPDBs="false"/>
<option name="SAMPLE_SCHEMA" value="false" includeInPDBs="false"/>
<option name="APEX" value="false" includeInPDBs="false"/>
<option name="DV" value="false" includeInPDBs="false"/>
</CommonAttributes>
<Variables/>
<CustomScripts Execute="false"/>
<InitParamAttributes>
<InitParams>
<initParam name="compatible" value="19.0.0"/>
<initParam name="undo_tablespace" value="UNDOTBS1"/>
<initParam name="undo_retention" value="3600"/>
<initParam name="dispatchers" value="(PROTOCOL=TCP) (SERVICE=XDB)"/>
<initParam name="db_name" value=""/>
<initParam name="sga_target" value="2042" unit="MB"/>
<initParam name="diagnostic_dest" value="{ORACLE_BASE}"/>
<initParam name="audit_file_dest" value="{ORACLE_BASE}/admin/{DB_UNIQUE_NAME}/adump"/>
<initParam name="open_cursors" value="300"/>
<initParam name="processes" value="600"/>
<initParam name="job_queue_processes" value="10"/>
<initParam name="recyclebin" value="on"/>
<initParam name="use_large_pages" value="only"/>
<initParam name="pga_aggregate_target" value="681" unit="MB"/>
<initParam name="nls_language" value="AMERICAN"/>
<initParam name="db_block_size" value="8192" unit="BYTES"/>
<initParam name="nls_territory" value="AMERICA"/>
<initParam name="control_files" value="("{ORACLE_BASE}/oradata/{DB_UNIQUE_NAME}/control01.ctl", "{ORACLE_BASE}/oradata/{DB_UNIQUE_NAME}/control02.ctl")"/>
<initParam name="audit_trail" value="db, extended"/>
<initParam name="remote_login_passwordfile" value="EXCLUSIVE"/>
<initParam name="db_recovery_file_dest_size" value="51200" unit="MB"/>
<initParam name="awr_pdb_autoflush_enabled" value="true"/>
</InitParams>
<MiscParams>
<databaseType>MULTIPURPOSE</databaseType>
<maxUserConn>20</maxUserConn>
<percentageMemTOSGA>40</percentageMemTOSGA>
<customSGA>false</customSGA>
<dataVaultEnabled>false</dataVaultEnabled>
<characterSet>AL32UTF8</characterSet>
<nationalCharacterSet>AL16UTF16</nationalCharacterSet>
<archiveLogMode>false</archiveLogMode>
<initParamFileName>{ORACLE_BASE}/admin/{DB_UNIQUE_NAME}/pfile/init.ora</initParamFileName>
</MiscParams>
<SPfile useSPFile="true">{ORACLE_HOME}/dbs/spfile{SID}.ora</SPfile>
</InitParamAttributes>
<StorageAttributes>
<ControlfileAttributes id="Controlfile">
<maxDatafiles>128</maxDatafiles>
<maxLogfiles>32</maxLogfiles>
<maxLogMembers>3</maxLogMembers>
<maxLogHistory>1</maxLogHistory>
<maxInstances>8</maxInstances>
<image name="control01.ctl" filepath="{ORACLE_BASE}/oradata/{DB_UNIQUE_NAME}/"/>
<image name="control02.ctl" filepath="{ORACLE_BASE}/oradata/{DB_UNIQUE_NAME}/"/>
</ControlfileAttributes>
<DatafileAttributes id="{ORACLE_BASE}/oradata/{DB_UNIQUE_NAME}/sysaux01.dbf" con_id="1">
<tablespace>SYSAUX</tablespace>
<temporary>false</temporary>
<online>true</online>
<status>0</status>
<size unit="MB">4096</size>
<reuse>true</reuse>
<autoExtend>false</autoExtend>
<increment unit="KB">10240</increment>
<maxSize unit="MB">-1</maxSize>
</DatafileAttributes>
<DatafileAttributes id="{ORACLE_BASE}/oradata/{DB_UNIQUE_NAME}/system01.dbf" con_id="1">
<tablespace>SYSTEM</tablespace>
<temporary>false</temporary>
<online>true</online>
<status>0</status>
<size unit="MB">2048</size>
<reuse>true</reuse>
<autoExtend>false</autoExtend>
<increment unit="KB">10240</increment>
<maxSize unit="MB">-1</maxSize>
</DatafileAttributes>
<DatafileAttributes id="{ORACLE_BASE}/oradata/{DB_UNIQUE_NAME}/temp01.dbf" con_id="1">
<tablespace>TEMP</tablespace>
<temporary>false</temporary>
<online>true</online>
<status>0</status>
<size unit="MB">8192</size>
<reuse>true</reuse>
<autoExtend>false</autoExtend>
<increment unit="KB">640</increment>
<maxSize unit="MB">-1</maxSize>
</DatafileAttributes>
<DatafileAttributes id="{ORACLE_BASE}/oradata/{DB_UNIQUE_NAME}/undotbs01.dbf" con_id="1">
<tablespace>UNDOTBS1</tablespace>
<temporary>false</temporary>
<online>true</online>
<status>0</status>
<size unit="MB">2048</size>
<reuse>true</reuse>
<autoExtend>false</autoExtend>
<increment unit="KB">5120</increment>
<maxSize unit="MB">-1</maxSize>
</DatafileAttributes>
<DatafileAttributes id="{ORACLE_BASE}/oradata/{DB_UNIQUE_NAME}/users01.dbf" con_id="1">
<tablespace>USERS</tablespace>
<temporary>false</temporary>
<online>true</online>
<status>0</status>
<size unit="MB">1536</size>
<reuse>true</reuse>
<autoExtend>false</autoExtend>
<increment unit="KB">1280</increment>
<maxSize unit="MB">-1</maxSize>
</DatafileAttributes>
<DatafileAttributes id="{ORACLE_BASE}/oradata/{DB_UNIQUE_NAME}/tools.dbf" con_id="1">
<tablespace>TOOLS</tablespace>
<temporary>false</temporary>
<online>true</online>
<status>0</status>
<size unit="MB">1536</size>
<reuse>false</reuse>
<autoExtend>false</autoExtend>
<increment unit="KB">0</increment>
<maxSize unit="MB">-1</maxSize>
</DatafileAttributes>
<TablespaceAttributes id="USERS" con_id="1">
<temporary>false</temporary>
<defaultTemp>false</defaultTemp>
<undo>false</undo>
<local>true</local>
<blockSize unit="BYTES">-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>true</bigfile>
<datafilesList>
<TablespaceDatafileAttributes id="{ORACLE_BASE}/oradata/{DB_UNIQUE_NAME}/users01.dbf"/>
</datafilesList>
</TablespaceAttributes>
<TablespaceAttributes id="SYSTEM" con_id="1">
<temporary>false</temporary>
<defaultTemp>false</defaultTemp>
<undo>false</undo>
<local>true</local>
<blockSize unit="BYTES">-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>-1</maxExtends>
<minExtendsSize unit="KB">64</minExtendsSize>
<logging>true</logging>
<recoverable>false</recoverable>
<maxFreeSpace>0</maxFreeSpace>
<bigfile>false</bigfile>
<datafilesList>
<TablespaceDatafileAttributes id="{ORACLE_BASE}/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 unit="BYTES">-1</blockSize>
<allocation>2</allocation>
<uniAllocSize unit="MB">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>
<bigfile>true</bigfile>
<datafilesList>
<TablespaceDatafileAttributes id="{ORACLE_BASE}/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 unit="BYTES">-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>true</bigfile>
<datafilesList>
<TablespaceDatafileAttributes id="{ORACLE_BASE}/oradata/{DB_UNIQUE_NAME}/undotbs01.dbf"/>
</datafilesList>
</TablespaceAttributes>
<TablespaceAttributes id="TOOLS" con_id="1">
<temporary>false</temporary>
<defaultTemp>false</defaultTemp>
<undo>false</undo>
<local>true</local>
<blockSize unit="BYTES">-1</blockSize>
<allocation>1</allocation>
<uniAllocSize unit="KB">-1</uniAllocSize>
<initSize unit="KB">128</initSize>
<increment unit="MB">-1</increment>
<incrementPercent>0</incrementPercent>
<minExtends>1</minExtends>
<maxExtends>4096</maxExtends>
<minExtendsSize unit="KB">128</minExtendsSize>
<logging>false</logging>
<recoverable>false</recoverable>
<maxFreeSpace>0</maxFreeSpace>
<bigfile>true</bigfile>
<datafilesList>
<TablespaceDatafileAttributes id="{ORACLE_BASE}/oradata/{DB_UNIQUE_NAME}/tools.dbf"/>
</datafilesList>
</TablespaceAttributes>
<TablespaceAttributes id="SYSAUX" con_id="1">
<temporary>false</temporary>
<defaultTemp>false</defaultTemp>
<undo>false</undo>
<local>true</local>
<blockSize unit="BYTES">-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="{ORACLE_BASE}/oradata/{DB_UNIQUE_NAME}/sysaux01.dbf"/>
</datafilesList>
</TablespaceAttributes>
<RedoLogGroupAttributes id="1">
<reuse>false</reuse>
<fileSize unit="MB">512</fileSize>
<Thread>1</Thread>
<member ordinal="0" memberName="redo01.log" filepath="{ORACLE_BASE}/oradata/{DB_UNIQUE_NAME}/"/>
</RedoLogGroupAttributes>
<RedoLogGroupAttributes id="2">
<reuse>false</reuse>
<fileSize unit="MB">512</fileSize>
<Thread>1</Thread>
<member ordinal="0" memberName="redo02.log" filepath="{ORACLE_BASE}/oradata/{DB_UNIQUE_NAME}/"/>
</RedoLogGroupAttributes>
<RedoLogGroupAttributes id="3">
<reuse>false</reuse>
<fileSize unit="MB">512</fileSize>
<Thread>1</Thread>
<member ordinal="0" memberName="redo03.log" filepath="{ORACLE_BASE}/oradata/{DB_UNIQUE_NAME}/"/>
</RedoLogGroupAttributes>
<RedoLogGroupAttributes id="4">
<reuse>false</reuse>
<fileSize unit="MB">512</fileSize>
<Thread>1</Thread>
<member ordinal="0" memberName="redo04.log" filepath="{ORACLE_BASE}/oradata/{DB_UNIQUE_NAME}/"/>
</RedoLogGroupAttributes>
<RedoLogGroupAttributes id="5">
<reuse>false</reuse>
<fileSize unit="MB">512</fileSize>
<Thread>1</Thread>
<member ordinal="0" memberName="redo05.log" filepath="{ORACLE_BASE}/oradata/{DB_UNIQUE_NAME}/"/>
</RedoLogGroupAttributes>
<RedoLogGroupAttributes id="6">
<reuse>false</reuse>
<fileSize unit="MB">512</fileSize>
<Thread>1</Thread>
<member ordinal="0" memberName="redo06.log" filepath="{ORACLE_BASE}/oradata/{DB_UNIQUE_NAME}/"/>
</RedoLogGroupAttributes>
<RedoLogGroupAttributes id="7">
<reuse>false</reuse>
<fileSize unit="MB">512</fileSize>
<Thread>1</Thread>
<member ordinal="0" memberName="redo07.log" filepath="{ORACLE_BASE}/oradata/{DB_UNIQUE_NAME}/"/>
</RedoLogGroupAttributes>
<RedoLogGroupAttributes id="8">
<reuse>false</reuse>
<fileSize unit="MB">512</fileSize>
<Thread>1</Thread>
<member ordinal="0" memberName="redo08.log" filepath="{ORACLE_BASE}/oradata/{DB_UNIQUE_NAME}/"/>
</RedoLogGroupAttributes>
</StorageAttributes>
</DatabaseTemplate>
Code-Sprache: HTML, XML (xml)After creation of new software profile and db creation et voila. DB got created with less options (6 remains) and all what I have declared in the dbc-file. Mission completed.
SQL> select COMP_NAME, VERSION, STATUS from dba_registry order by 1;
COMP_NAME VERSION STATUS
------------------------------------------ ------------------------------ --------------------------------------------
Oracle Database Catalog Views 19.0.0.0.0 VALID
Oracle Database Packages and Types 19.0.0.0.0 VALID
Oracle Real Application Clusters 19.0.0.0.0 OPTION OFF
Oracle Text 19.0.0.0.0 VALID
Oracle Workspace Manager 19.0.0.0.0 VALID
Oracle XML Database 19.0.0.0.0 VALID
6 rows selected.Code-Sprache: SQL (Structured Query Language) (sql)