Jul 09

Creating a 19c database with a lower COMPATIBLE parameter

Those days, I was trying to create a 19c database with a lower COMPATIBLE, so I could have a fallback plan after plugin a PDB from a lower release.

However, when trying to create it using DBCA command:

dbca -silent -createDatabase \
-gdbName cdb19c \
-templateName General_Purpose.dbc \
-characterSet AL32UTF8 \
-createAsContainerDatabase true \
-numberOfPDBs 1 \
-pdbAdminPassword "Rodrigo.123" \
-pdbName PDB01 \
-useLocalUndoForPDBs false \
-databaseConfigType SINGLE \
-databaseType MULTIPURPOSE \
-datafileDestination /u01/app/oradata \
-dvConfiguration false \
-emConfiguration NONE \
-enableArchive false \
-memoryMgmtType AUTO_SGA \
-totalMemory 4000 \
-nationalCharacterSet AL16UTF16 \
-olsConfiguration false \
-recoveryAreaDestination /u01/app/fra \
-sampleSchema false \
-sid cdb19c \
-storageType FS \
-useOMF false \
-sysPassword "Rodrigo.123" \
-systemPassword "Rodrigo.123" \
-initParams compatible=12.1.0.2.0

I got the following error:

Prepare for db operation
8% complete
Copying database files
9% complete
[WARNING] ORA-00201: control file version 19.0.0.0.0 incompatible with ORACLE version 12.1.0.2.0
ORA-00202: control file: '/u01/app/oracle/cfgtoollogs/dbca/orcl/tempControl.ctl'

[WARNING] ORA-01507: database not mounted
ORA-06512: at "SYS.X$DBMS_BACKUP_RESTORE", line 8418
ORA-06512: at "SYS.X$DBMS_BACKUP_RESTORE", line 8412
ORA-06512: at line 1

10% complete
[WARNING] ORA-01507: database not mounted
ORA-06512: at "SYS.X$DBMS_BACKUP_RESTORE", line 8418
ORA-06512: at "SYS.X$DBMS_BACKUP_RESTORE", line 8412
ORA-06512: at line 1

[WARNING] ORA-01507: database not mounted

[WARNING] ORA-01507: database not mounted

[WARNING] ORA-01507: database not mounted

[WARNING] ORA-01507: database not mounted

31% complete
Creating and starting Oracle instance
[FATAL] ORA-01503: CREATE CONTROLFILE failed
ORA-01565: error in identifying file '/u01/app/oradata/ORCL/sysaux01.dbf'
ORA-27037: unable to obtain file status

46% complete
100% complete
[FATAL] ORA-01503: CREATE CONTROLFILE failed
ORA-01565: error in identifying file '/u01/app/oradata/ORCL/sysaux01.dbf'
ORA-27037: unable to obtain file status

31% complete
8% complete
0% complete
Look at the log file "/u01/app/oracle/cfgtoollogs/dbca/orcl/orcl2.log" for further details.

So, the issue is that when I've selected "General Purpose" template in DBCA, it will actually restore the control files and data files from a pre-built database template, not create it from scratch.  And the control file version there is 19.0.0.0, so there is no chance to select a template with pre-built data files and control files and also define the COMPATIBLE parameter.

To fix the issue, you have to use the DBCA GUI and select the "Custom Database" option. Later, on the last screen where you can check and alter the init file, change the COMPATIBLE parameter to the desired version:

Just pay attention to avoid compatible issues, like:

  • Selecting CDB model and <12.1 COMPATIBLE (containers didn't even exist before 12c). If you try it, you will get:

[WARNING] ORA-00406: COMPATIBLE parameter needs to be 12.0.0.0.0 or greater
ORA-00722: Feature "Consolidated Database"

  • Selecting Local Undo and <12.2 COMPATIBLE.

Actually, I was surprised to see that I could create it. I will blog about it in another post.

I don't have DISPLAY on my server!

You can also do it using DBCA with silent mode, however, you will need to create a customized template before starting. To build a template, it's much easier with the UI. Deadlock detected.

So, to make your life easier, just use the one I have below. It was created from the "Custom Template". Don't forget to replace the entries (dbname, paths, etc) using case sensitivity!

cat > $ORACLE_HOME/assistants/dbca/templates/My_Template.dbt << 'EOF'
<DatabaseTemplate name="My Template" description="" version="19.0.0.0.0">
   <CommonAttributes>
      <option name="OMS" value="false" includeInPDBs="false"/>
      <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="APEX" value="false" includeInPDBs="false"/>
      <option name="DV" value="false" includeInPDBs="false"/>
   </CommonAttributes>
   <Variables/>
   <CustomScripts Execute="false"/>
   <PluggableDatabases>
      <PluggableDatabase pdb_name="PDB$SEED" con_id="2" pdb_dbid="0"/>
   </PluggableDatabases>
   <InitParamAttributes>
      <InitParams>
         <initParam name="db_name" value="cdb19c"/>
         <initParam name="dispatchers" value="(PROTOCOL=TCP) (SERVICE=orclXDB)"/>
         <initParam name="audit_file_dest" value="/u01/app/oracle/admin/cdb19c/adump"/>
         <initParam name="compatible" value="11.2.0.4.0"/>
         <initParam name="remote_login_passwordfile" value="EXCLUSIVE"/>
         <initParam name="sga_target" value="3000" unit="MB"/>
         <initParam name="processes" value="300"/>
         <initParam name="undo_tablespace" value="UNDOTBS1"/>
         <initParam name="control_files" value="(&quot;/u01/app/oracle/oradata/CDB19C/control01.ctl&quot;, &quot;/u01/app/oracle/fra/CDB19C/control02.ctl&quot;)"/>
         <initParam name="diagnostic_dest" value="/u01/app/oracle"/>
         <initParam name="enable_pluggable_database" value="true"/>
         <initParam name="db_recovery_file_dest" value="/u01/app/oracle/fra"/>
         <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="5000" unit="MB"/>
         <initParam name="pga_aggregate_target" value="1000" unit="MB"/>
      </InitParams>
      <MiscParams>
         <databaseType>MULTIPURPOSE</databaseType>
         <maxUserConn>20</maxUserConn>
         <percentageMemTOSGA>40</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">{ORACLE_HOME}/dbs/spfile{SID}.ora</SPfile>
   </InitParamAttributes>
   <StorageAttributes>
      <ControlfileAttributes id="Controlfile">
         <maxDatafiles>1024</maxDatafiles>
         <maxLogfiles>16</maxLogfiles>
         <maxLogMembers>3</maxLogMembers>
         <maxLogHistory>1</maxLogHistory>
         <maxInstances>8</maxInstances>
         <image name="control01.ctl" filepath="/u01/app/oracle/oradata/CDB19C/"/>
         <image name="control02.ctl" filepath="/u01/app/oracle/fra/CDB19C/"/>
      </ControlfileAttributes>
      <DatafileAttributes id="/u01/app/oracle/oradata/CDB19C/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="/u01/app/oracle/oradata/CDB19C/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="/u01/app/oracle/oradata/CDB19C/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="/u01/app/oracle/oradata/CDB19C/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="/u01/app/oracle/oradata/CDB19C/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="/u01/app/oracle/oradata/CDB19C/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="/u01/app/oracle/oradata/CDB19C/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="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>
         <autoSegmentMgmt>true</autoSegmentMgmt>
         <bigfile>false</bigfile>
         <datafilesList>
            <TablespaceDatafileAttributes id="/u01/app/oracle/oradata/CDB19C/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="/u01/app/oracle/oradata/CDB19C/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="/u01/app/oracle/oradata/CDB19C/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="/u01/app/oracle/oradata/CDB19C/"/>
      </RedoLogGroupAttributes>
      <RedoLogGroupAttributes id="2">
         <reuse>false</reuse>
         <fileSize unit="KB">204800</fileSize>
         <Thread>1</Thread>
         <member ordinal="0" memberName="redo02.log" filepath="/u01/app/oracle/oradata/CDB19C/"/>
      </RedoLogGroupAttributes>
      <RedoLogGroupAttributes id="3">
         <reuse>false</reuse>
         <fileSize unit="KB">204800</fileSize>
         <Thread>1</Thread>
         <member ordinal="0" memberName="redo03.log" filepath="/u01/app/oracle/oradata/CDB19C/"/>
      </RedoLogGroupAttributes>
   </StorageAttributes>
</DatabaseTemplate>
EOF

And now you can create it with the following command:

dbca -silent -createDatabase \
-gdbName cdb19c \
-templateName My_Template.dbt \
-characterSet AL32UTF8 \
-createAsContainerDatabase true \
-numberOfPDBs 1 \
-pdbAdminPassword "Rodrigo.123" \
-pdbName PDB01 \
-useLocalUndoForPDBs true \
-databaseConfigType SINGLE \
-databaseType MULTIPURPOSE \
-datafileDestination /u01/app/oracle/oradata \
-dvConfiguration false \
-emConfiguration NONE \
-enableArchive false \
-memoryMgmtType AUTO_SGA \
-totalMemory 4000 \
-nationalCharacterSet AL16UTF16 \
-olsConfiguration false \
-recoveryAreaDestination /u01/app/oracle/fra \
-sampleSchema false \
-sid cdb19c \
-storageType FS \
-useOMF false \
-sysPassword "Rodrigo.123" \
-systemPassword "Rodrigo.123" \
-initParams compatible=12.1.0.2.0
Have you enjoyed? Please leave a comment or give a 👍!

2 comments

    • Marco on July 13, 2021 at 11:35
    • #
    • Reply

    Hello Rodrigo,

    You are verifying and executing the steps that voice is recommending not in your blog, referring to the lower compatible parameter.

    So I have a dubious one, when I have a bank as a higher parameter (example: 19.3.0.0.0) is it possible to reduce the level for a lower version (example: 19.0.0.0.0) ?!

    I read the Oracle documentation, a Note item informing that it is possible to lower the value of the compatible parameter.

    Best Regards,
    Marco

    1. Hi Marco, it's not possible to lower the value of COMPATIBLE parameter. However, when you create a 19c database (no matter the RU), the COMPATIBLE will default to '19.0.0'. This allows you to move from 19.X to 19.Y, for any possible X and Y.

      Regards,
      RJ

Leave a Reply

Your email address will not be published.