Have need for custom default tablespaces

[enlightenalpha]

We have a need to define custom settings for some default tablespaces. For example:

tablespaces: - { name: sysaux, size: 10M, bigfile: True, autoextend: true , next: 10M, maxsize: 2G, content: permanent, state: present } - { name: system, size: 10M, bigfile: True, autoextend: true , next: 10M, maxsize: 2G, content: permanent, state: present } - { name: temp, size: 10M, bigfile: True, autoextend: true , next: 10M, maxsize: 1000G, content: temp, state: present } - { name: undo_t1, size: 10M, bigfile: True, autoextend: true , next: 10M, maxsize: 1000G, content: undo, state: present } - { name: users, size: 10M, bigfile: True, autoextend: true , next: 10M, maxsize: 4000G, content: permanent, state: present }

We want to enable bigfile and since the system and sysaux tablespaces are created by default during DB install and cannot be changed to bigfile after the fact, we need a way to override defaults used during install. Is this currently supported?

[majdfr]

I had a similar requirement, and was able to resolve the issue using a DBCA custom template

    oracle_databases:                                       # Dictionary describing the databases to be installed
        - home: 18300-base                                  # 'Last' directory in ORACLE_HOME path 
          state: present
           ...
          dbca_templatename: cc_dbca_standalone_18300.dbt
          ...

create a dbca template in the ansible-oracle/roles/oradb-manage-db/templates folder you can create one using dbca, and then customize it

   <StorageAttributes>
      <DataFiles>
         <Location>{ORACLE_HOME}/assistants/dbca/templates/Seed_Database.dfb</Location>
         <SourceDBName cdb="true">seeddata</SourceDBName>
         <Name id="3" Tablespace="SYSAUX" Contents="PERMANENT" Size="400" autoextend="false" blocksize="8192" con_id="1">{ORACLE_BASE}/oradata/{DB_UNIQUE_NAME}/sysaux01.dbf</Name>
         <Name id="1" Tablespace="SYSTEM" Contents="PERMANENT" Size="830" autoextend="false" blocksize="8192" con_id="1">{ORACLE_BASE}/oradata/{DB_UNIQUE_NAME}/system01.dbf</Name>
         <Name id="4" Tablespace="UNDOTBS1" Contents="UNDO" Size="25" autoextend="false" blocksize="8192" con_id="1">{ORACLE_BASE}/oradata/{DB_UNIQUE_NAME}/undotbs01.dbf</Name>
         <Name id="7" Tablespace="USERS" Contents="PERMANENT" Size="5" autoextend="false" blocksize="8192" con_id="1">{ORACLE_BASE}/oradata/{DB_UNIQUE_NAME}/users01.dbf</Name>
      </DataFiles>

please note that the dbca templates within ansible-oracle are jinja compatible. You can use your ansible variables :

   <InitParamAttributes>
      <InitParams>
         <initParam name="db_create_file_dest" value="{{ dbh.datafile_dest }}"/>
         <initParam name="db_name" value="{{ dbh.oracle_db_name }}"/>
         <initParam name="db_domain" value="local"/>

it works like a charm :)

Cheers

[Rendanic]

As @majdfr explained. You could use DBCA-Templates in ansible-oracle to reach this goal.

I am not a big fan of BIGFILE Tablespaces in Oracle. They created to much performance issues in the data dictionary with dba_free_space in the past - hopefully the develeoper will fix them completly.