initParams get ignored

[hohwille]

Today I cloned the latest main of your docker-images git repo.
I have customized the initParams locally here:

docker-images/OracleDatabase/SingleInstance/dockerfiles/21.3.0/dbca.rsp.tmpl

Line 173 in 65bc131

initParams=audit_trail=none,audit_sys_operations=false

Then I have build the docker image for XE 21 with this command:

./buildContainerImage.sh -x -v 21.3.0

After that has completed, I started a container using that image:

docker run -p 1521:1521 --name OracleXE --restart=always -e ORACLE_PDB=XE -e ORACLE_PWD=«password» -v «datafiles»:/opt/oracle/oradata -v «dumpfiles»:/opt/migrate -v «tablespaces»:/opt/tsfiles oracle/database:21.3.0-xe

Finally, I connected to my new Oracle XE in docker using SQL-Developer as sys and tested some of the init parameters:
My custom init param that I configured as not set. Nor any of the ones pre-configured in your git repo:
E.g.

show parameter audit_trail

gave me

NAME        TYPE   VALUE 
----------- ------ ----- 
audit_trail string DB    

I would have expected none as result value.

So how can I configure initParams for OracleXE 21 via your docker-images build scripts?

[hohwille]

Even if I hack the createDb.sh script here:

docker-images/OracleDatabase/SingleInstance/dockerfiles/21.3.0/createDB.sh

Line 283 in 65bc131

# If both INIT_SGA_SIZE & INIT_PGA_SIZE aren't provided by user

And add this:

if [ -z "${INIT_PARAMS}"]; then
  INIT_PARAMS=audit_trail=none,audit_sys_operations=false,compatible=19.0.0
fi
sed -i -e "s|initParams=.*|initParams=${INIT_PARAMS}|g" "$ORACLE_BASE"/dbca.rsp

and rebuild the image and recreate the container, I still get the defaults and no init parameter is changed as expected (verified with show parameter).
Also when I log into the container in a bash and do

cat /opt/oracle/product/21c/dbhomeXE/assistants/dbca/dbca.rsp

I can see that all parameters are empty.
In case I change them in bash from there and verify that they are changed and restart the container they also have no effect.

Something is totally broken and messed up here.
p.s.: Please also note that it is very frustrating to retry this 20 times as the docker build always downloads the 2GB XE package from scratch and the entire process takes 20-30 minutes per retry. It would be much smarter if the downloaded file would be stored on the disc and if present and recent enough it could be reused without downloading again...

[oraclesean]

The Oracle 21c Express Edition installs differently than other database editions/versions. It's an rpm-based installation, with configuration managed via /etc/sysconfig/oracle-xe-21c.conf. You could add something to createDB.sh to overwrite the defaults or replace the file entirely, but I believe there are limits on what parameters are available for modification on XE. For instance, you can't change the SID. I suspect you may be unable to change compatible, either.

For more information, see page 21 of the 21c Express Edition Installation Guide.

To avoid the download with each build, download the rpm to the 21.3.0 subdirectory, then try the following edits to dockerfile.xe:

• Change the installation file to a local file:

  INSTALL_FILE_1="https://download.oracle.com/otn-pub/otn_software/db-express/oracle-database-xe-21c-1.0-1.ol7.x86_64.rpm" \
  to:
  INSTALL_FILE_1="oracle-database-xe-21c-1.0-1.ol7.x86_64.rpm" \

• Add the local copy of the file to the COPY command, placing it in the installation directory:

  COPY $CHECK_SPACE_FILE $CONF_FILE $SETUP_LINUX_FILE $RUN_FILE $PWD_FILE $CHECK_DB_FILE $CREATE_DB_FILE $USER_SCRIPTS_FILE $CONFIG_TCPS_FILE $INSTALL_DIR/
  to:
  COPY $CHECK_SPACE_FILE $CONF_FILE $SETUP_LINUX_FILE $RUN_FILE $PWD_FILE $CHECK_DB_FILE $CREATE_DB_FILE $USER_SCRIPTS_FILE $CONFIG_TCPS_FILE $INSTALL_FILE_1 $INSTALL_DIR/

• Perform a yum localinstall:

      unbuffer yum -y install $INSTALL_FILE_1 && \
  to:
      unbuffer yum -y localinstall $INSTALL_DIR/$INSTALL_FILE_1 && \

[hohwille]

@oraclesean Thank you so much for your kind, helpful and quick response.
The download issue works like a charm after following your instructions.
Also I was able to tweak the post setup by adding some custom SQL by adding:

    echo -e "\nALTER PROFILE DEFAULT LIMIT PASSWORD_LIFE_TIME UNLIMITED;\n" >> $ORACLE_HOME/assistants/dbca/postdb_creation.sql && \

Also I changed the initParams in dbca.rsp with this line:

    sed -i -e "s|initParams=.*|initParams=audit_trail=none,audit_sys_operations=false,compatible=19.0.0|g" "$ORACLE_HOME"/assistants/dbca/dbca.rsp && \

My changes worked and the post SQL was executed and also the dbca.rsp is modified as planned inside the image.
However, the latter has no effect on the actual initParams when the DB is created.
So is dbca.rsp simply ignored by the DB creation of XE 21?
Any hint how to archive my goal would be highly appreciated.

BTW: For XE21 no createDB.sh is used at all. It is copied into the image from the Dockerfile.xe but is not used at all. I found there is a dbSetup.pl pearl script doing the setup, but I am not a pearl programmer and could not find the code it is delegating to.

With manual tweaking of local XE 21 installation it seems to be somehow possible:
https://connor-mcdonald.com/2021/10/11/19c-express-edition-joelkallmanday/

Is there another place, where I could configure initParams and with dbca.rsp I am again on a wrong trail?

[oraclesean]

@hohwille so happy that worked for you and glad I could help speed up your troubleshooting effort!

rpm-based installations work a bit differently than those in a normal container environment because they run as root through an init process, rather than as the oracle user. That means they also bypass some of the automation and customization present in images for "regular" editions. If you only needed to change the audit* parameters, you could edit the contents of $ORACLE_HOME/assistants/dbca/templates/XE_Database.dbc and add/modify lines there.

Changing compatible can't be done that way. When the DBCA runs, it's not really creating a new database; rather, it restores a database from the XE_Seed_Database.[ctl|dfb] (controlfile + database) files in that same templates directory. The template control file is generated from a 21c database. You can't make it "less" compatible than the current version.

The post you mentioned talks about creating a fresh database from scratch, but Connor only showed how to do that through the GUI installer. I figured out how to get this working through a -silent install. Here's the quick, dirty method:

• Create a file in the dockerfiles/21.3.0 directory called XE_Database.dbc. I included the parameters you wanted to set in the initParams section. I also updated the control_files parameter to generate a single control file rather than putting one copy under the oradata volume and another in a container directory. (Normally, you'd multiplex the control files under different directories. For this purpose, it's not beneficial and would prevent you from using the contents of the oradata directory as a gold image.):

<DatabaseTemplate name="XE Database" description=" " version="21.0.0.0.0">
   <CommonAttributes>
      <option name="OMS" value="true" includeInPDBs="true"/>
      <option name="JSERVER" value="true" includeInPDBs="true"/>
      <option name="SPATIAL" value="true" includeInPDBs="true"/>
      <option name="IMEDIA" value="true" includeInPDBs="true"/>
      <option name="ORACLE_TEXT" value="true" includeInPDBs="true">
         <tablespace id="SYSAUX"/>
      </option>
      <option name="SAMPLE_SCHEMA" value="false" includeInPDBs="false"/>
      <option name="CWMLITE" value="true" includeInPDBs="true">
         <tablespace id="SYSAUX"/>
      </option>
      <option name="DV" value="true" includeInPDBs="true"/>
   </CommonAttributes>
   <Variables/>
   <CustomScripts Execute="false"/>
   <InitParamAttributes>
      <InitParams>
         <initParam name="audit_file_dest" value="{ORACLE_BASE}/admin/{DB_UNIQUE_NAME}/adump"/>
         <initParam name="audit_sys_operations" value="false"/>
         <initParam name="audit_trail" value="none"/>
         <initParam name="compatible" value="19.0.0"/>
         <initParam name="control_files" value="{ORACLE_BASE}/oradata/{DB_UNIQUE_NAME}/control01.ctl"/>
         <initParam name="db_block_size" value="8" unit="KB"/>
         <initParam name="db_name" value=""/>
         <initParam name="diagnostic_dest" value="{ORACLE_BASE}"/>
         <initParam name="dispatchers" value="(PROTOCOL=TCP) (SERVICE={SID}XDB)"/>
         <initParam name="open_cursors" value="300"/>
         <initParam name="pga_aggregate_target" value="512" unit="MB"/>
         <initParam name="remote_login_passwordfile" value="EXCLUSIVE"/>
         <initParam name="sga_target" value="1536" unit="MB"/>
         <initParam name="undo_tablespace" value="UNDO"/>
      </InitParams>
      <MiscParams>
         <databaseType>MULTIPURPOSE</databaseType>
         <maxUserConn>20</maxUserConn>
         <percentageMemTOSGA>0</percentageMemTOSGA>
         <customSGA>false</customSGA>
         <dataVaultEnabled>false</dataVaultEnabled>
         <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>100</maxDatafiles>
         <maxLogfiles>16</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/fast_recovery_area/{DB_UNIQUE_NAME}/"/>
      </ControlfileAttributes>
      <DatafileAttributes id="{ORACLE_BASE}/oradata/{DB_UNIQUE_NAME}/sysaux01.dbf">
         <tablespace>SYSAUX</tablespace>
         <temporary>false</temporary>
         <online>true</online>
         <status>0</status>
         <size unit="MB">460</size>
         <reuse>true</reuse>
         <autoExtend>true</autoExtend>
         <increment unit="KB">10240</increment>
         <maxSize unit="MB">-1</maxSize>
      </DatafileAttributes>
      <DatafileAttributes id="{ORACLE_BASE}/oradata/{DB_UNIQUE_NAME}/system01.dbf">
         <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="{ORACLE_BASE}/oradata/{DB_UNIQUE_NAME}/undotbs01.dbf">
         <tablespace>UNDO</tablespace>
         <temporary>false</temporary>
         <online>true</online>
         <status>0</status>
         <size unit="MB">25</size>
         <reuse>true</reuse>
         <autoExtend>true</autoExtend>
         <increment unit="KB">5120</increment>
         <maxSize unit="MB">-1</maxSize>
      </DatafileAttributes>
      <DatafileAttributes id="{ORACLE_BASE}/oradata/{DB_UNIQUE_NAME}/users01.dbf">
         <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>
      <DatafileAttributes id="{ORACLE_BASE}/oradata/{DB_UNIQUE_NAME}/temp01.dbf">
         <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>
      <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="{ORACLE_BASE}/oradata/{DB_UNIQUE_NAME}/sysaux01.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="{ORACLE_BASE}/oradata/{DB_UNIQUE_NAME}/system01.dbf"/>
         </datafilesList>
      </TablespaceAttributes>
      <TablespaceAttributes id="UNDO">
         <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="{ORACLE_BASE}/oradata/{DB_UNIQUE_NAME}/undotbs01.dbf"/>
         </datafilesList>
      </TablespaceAttributes>
      <TablespaceAttributes id="USERS">
         <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="{ORACLE_BASE}/oradata/{DB_UNIQUE_NAME}/users01.dbf"/>
         </datafilesList>
      </TablespaceAttributes>
      <TablespaceAttributes id="TEMP">
         <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">-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>false</bigfile>
         <datafilesList>
            <TablespaceDatafileAttributes id="{ORACLE_BASE}/oradata/{DB_UNIQUE_NAME}/temp01.dbf"/>
         </datafilesList>
      </TablespaceAttributes>
      <RedoLogGroupAttributes id="1">
         <reuse>false</reuse>
         <fileSize unit="KB">204800</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="KB">204800</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="KB">204800</fileSize>
         <Thread>1</Thread>
         <member ordinal="0" memberName="redo03.log" filepath="{ORACLE_BASE}/oradata/{DB_UNIQUE_NAME}/"/>
      </RedoLogGroupAttributes>
   </StorageAttributes>
</DatabaseTemplate>

• In Dockerfile.xe, update the ENV command to include the name of the template:

ENV ORACLE_BASE=/opt/oracle \
    ORACLE_HOME=/opt/oracle/product/21c/dbhomeXE \
    ORACLE_BASE_HOME=/opt/oracle/homes/OraDBHome21cXE \
    ORACLE_SID=XE \
    INSTALL_FILE_1="https://download.oracle.com/otn-pub/otn_software/db-express/oracle-database-xe-21c-1.0-1.ol7.x86_64.rpm" \
    RUN_FILE="runOracle.sh" \
    PWD_FILE="setPassword.sh" \
    CREATE_DB_FILE="createDB.sh" \
    USER_SCRIPTS_FILE="runUserScripts.sh" \
    CONF_FILE="oracle-xe-21c.conf" \
    CHECK_SPACE_FILE="checkSpace.sh" \
    CHECK_DB_FILE="checkDBStatus.sh" \
    SETUP_LINUX_FILE="setupLinuxEnv.sh" \
    CONFIG_TCPS_FILE="configTcps.sh" \
    TEMPLATE_FILE="XE_Database.dbc" \
    INSTALL_DIR="$HOME/install" \
    ORACLE_DOCKER_INSTALL="true" \
    CHECKPOINT_FILE_EXTN=".created"

• In Dockerfile.xe, add that file to the COPY command that's placing files in the $INSTALL_DIR:

COPY $CHECK_SPACE_FILE $CONF_FILE $SETUP_LINUX_FILE $RUN_FILE $PWD_FILE $CHECK_DB_FILE $CREATE_DB_FILE $USER_SCRIPTS_FILE $CONFIG_TCPS_FILE $TEMPLATE_FILE $INSTALL_DIR/

• In Dockerfile.xe, update the RUN command to read:

RUN cd $INSTALL_DIR && \
    mkdir -p $ORACLE_BASE && \
    mv $RUN_FILE $PWD_FILE $CHECK_DB_FILE $CREATE_DB_FILE $USER_SCRIPTS_FILE $CONFIG_TCPS_FILE $ORACLE_BASE/ && \
    chmod ug+x *.sh && \
    sync && \
    ./$CHECK_SPACE_FILE && \
    yum -y install expect && \
    ./$SETUP_LINUX_FILE && \
    chown -R oracle:oinstall $ORACLE_BASE /home/oracle && \
    sed -i -r 's/(^session\s+include\s+system-auth)/# \1/' /etc/pam.d/su && \
    unbuffer yum -y install $INSTALL_FILE_1 && \
    yum -y remove expect && \
    rm -rf /var/cache/yum && \
    rm -rf /var/tmp/yum-* && \
    mv $CONF_FILE /etc/sysconfig/ && \
    mv $TEMPLATE_FILE $ORACLE_HOME/assistants/dbca/templates/ && \
    chown oracle:install $ORACLE_HOME/assistants/dbca/templates/$TEMPLATE_FILE && \
    cd $HOME && \
    rm -rf $INSTALL_DIR && \
    $ORACLE_BASE/oraInventory/orainstRoot.sh && \
    $ORACLE_HOME/root.sh && \
    echo 'export ORACLE_SID=XE' > .bashrc

Then, rebuild the image and then run it normally. The downside of this is that you're no longer using the pre-built seed database files, so database creation takes a longer. If you don't need to change compatible, you can get away with modifying the initParams section of the default XE_Database.dbc template.

I realize this doesn't lend the same flexibility as passing an environment variable, but I sense this is more of a specific/one-off need than something you're mobilizing to a build pipeline. LMK if that's not the case and I'll pull together something more flexible for you.

[hohwille]

@oraclesean thanks for your great support. Without you I would have given up and usually I do not give up easily.
For completeness:

chown oracle:install $ORACLE_HOME/assistants/dbca/templates/$TEMPLATE_FILE && \

There was an o missing so I fixed it to:

chown oracle:oinstall $ORACLE_HOME/assistants/dbca/templates/$TEMPLATE_FILE && \

Further to save some hundred MBs of image size, from your comment that the DB then does not get created from the seeds, I deleted those by adding:

    rm $ORACLE_HOME/assistants/dbca/templates/XE_Seed_Database.* && \
    rm $ORACLE_HOME/assistants/dbca/templates/pdbseed.* && \

And the most important part: You also have to change createDB.sh (I was wrong earlier saying that it is not used, only the code tweaking the dbca.rsp was all pointless for XE 21). So at the end of the script there is a line starting with the dbca command that I changed to this:

dbca -silent -createDatabase -gdbName XE -templateName XE_Database.dbc -characterSet AL32UTF8 -createAsContainerDatabase true -numberOfPDBs 1 -pdbName XEPDB1 -sid $ORACLE_SID -customScripts $ORACLE_HOME/assistants/dbca/postdb_creation.sql -oui_internal -systemPassword $ORACLE_PWD -sysPassword $ORACLE_PWD -pdbAdminPassword $ORACLE_PWD ||

You could even hardcode $ORACLE_SID as XE since there is no other value allowed...

I finally archived my goals and therefore close this issue. Thanks mate - I owe you a drink!

[oraclesean]

@hohwille awesome! I'm so glad you could navigate around my oversights (and the autocorrect of oinstall to install) and get it working!