Creating an Oracle 11.2-Compatible Database in Oracle 12.2.0.1

Question

I am using an Oracle Docker image of 12.2.0.1 in my development environment:

https://github.com/oracle/docker-images/tree/master/OracleDatabase/SingleInstance/dockerfiles/12.2.0.1

There does not seem to be an equivalent of this for 11.2.0.4. The database where my code will be deployed into production is 11.2.0.4, so I need to work with compatible=11.2.0.4.

I have tried many times and many ways to set compatible=11.2.0.4 but I cannot get it to work. It seems that, no matter what, due to the built in templates or whatever, I get an error that 12.2 control files are not compatible with 11.2.0.4.

The first method I tried was to set info.ora parameters prior to the first call to dbca and to use dbca responses (in an rsp file) to include compatible=11.2.0.4. This resulted in an error because the dbc template has control files that are incompatible (12.1).

I tried using a dbt template instead, so that control files would not be copied, but the dbca command seems to have created 12.2 control files even though I was requesting 11.2 compatibility.

Is it possible to create a CDB or PDB with 11.2.0.4 compatibility using 12.2.0.1? Currently the question is whether or not it's even possible. If it is possible, some high-level guidance as to how to go about it would be much appreciated.

Unfortunately I did not save records from yesterday's 10-hour session. For now, knowing whether or not it is even possible to create an older-versioned database will suffice.

Here's what detail I can provide...

The most frequently encountered error was

ORA-00201: control file version 12.2.0.0.0 incompatible with ORACLE version 11.2.0.4.0 ORA-00202: control file: '/opt/oracle/cfgtoollogs/dbca/SIDGNRIC/tempControl.ctl'

This error occurs when my dbca responses included: responseFileVersion=/oracle/assistants/rspfmt_dbca_response_schema_v11.2.0 initParams=audit_trail=none,audit_sys_operations=false,compatible=11.2.0.4 templateName=General_Purpose.dbc

I also tried changing to templateName=New_Database.dbt with the same result.

Answer 1

Long story short:

If you want a 11.2.0.4 database, then use 11.2.0.4. Yes, I know, downloading 11.2.0.4 requires a valid support contract which most visitors here do not have, but you can still download a virtual machine with that version at Pre-built Virtual Machine for Oracle Data Integrator 12c Getting Started Guide.

And here is how to create a 12.2 database with compatible set to 11.2.0.4 (no, I did not even bother with DBCA):

[oracle@o71 ~]$ cat ${ORACLE_HOME}/dbs/init${ORACLE_SID}.ora
*.audit_file_dest='/u01/app/oracle/admin/EX/adump'
*.audit_trail='db'
*.compatible='11.2.0.4.0'
*.control_files='/oradata/EX/control01.ctl','/oradata/EX/control02.ctl'
*.db_block_size=8192
*.db_create_file_dest='/oradata/EX'
*.db_name='EX'
*.diagnostic_dest='/u01/app/oracle'
*.dispatchers='(PROTOCOL=TCP) (SERVICE=EXXDB)'
*.filesystemio_options='setall'
*.nls_language='AMERICAN'
*.nls_territory='AMERICA'
*.open_cursors=300
*.pga_aggregate_target=256m
*.processes=320
*.remote_login_passwordfile='EXCLUSIVE'
*.sga_target=1024m
[oracle@o71 ~]$ echo ${ORACLE_SID}
EX

[oracle@o71 ~]$ sqlplus / as sysdba

SQL*Plus: Release 12.2.0.1.0 Production on Mon Dec 3 20:06:47 2018

Copyright (c) 1982, 2016, Oracle.  All rights reserved.

Connected to an idle instance.

SQL> create spfile from pfile;

File created.

SQL> startup nomount
ORACLE instance started.

Total System Global Area 1073741824 bytes
Fixed Size                  8628936 bytes
Variable Size             394265912 bytes
Database Buffers          662700032 bytes
Redo Buffers                8146944 bytes
SQL> create database;

Database created.

The above creates the database without any dictionary views and packages. I performed the next steps in UPGRADE mode, because without that, my database ended up with several invalid objects that could not be compiled at all.

SQL> shu immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup upgrade
ORACLE instance started.

Total System Global Area 1073741824 bytes
Fixed Size                  8628936 bytes
Variable Size             394265912 bytes
Database Buffers          662700032 bytes
Redo Buffers                8146944 bytes
Database mounted.
Database opened.

SQL> @?/rdbms/admin/catalog.sql
...
SQL> @?/rdbms/admin/catproc.sql
...
SQL> @?/rdbms/admin/utlrp.sql
...
SQL> alter user system identified by oracle;

User altered.

SQL> shu immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup
ORACLE instance started.

Total System Global Area 1073741824 bytes
Fixed Size                  8628936 bytes
Variable Size             394265912 bytes
Database Buffers          662700032 bytes
Redo Buffers                8146944 bytes
Database mounted.
Database opened.

SQL> @?/sqlplus/admin/pupbld.sql
...

And done.

SQL> exit
Disconnected from Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production
[oracle@o71 ~]$ sqlplus / as sysdba

SQL*Plus: Release 12.2.0.1.0 Production on Mon Dec 3 21:14:45 2018

Copyright (c) 1982, 2016, Oracle.  All rights reserved.


Connected to:
Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production

SQL> show parameter compat

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
compatible                           string      11.2.0.4.0
noncdb_compatible                    boolean     FALSE
plsql_v2_compatibility               boolean     FALSE

SQL> select comp_name, version, status from dba_registry;

COMP_NAME                                VERSION                        STATUS
---------------------------------------- ------------------------------ -----------
Oracle Database Catalog Views            12.2.0.1.0                     VALID
Oracle Database Packages and Types       12.2.0.1.0                     VALID
Oracle XML Database                      12.2.0.1.0                     VALID

SQL> select count(*) from dba_invalid_objects;

  COUNT(*)
----------
         0

SQL> create table t1 (id number generated always as identity);
create table t1 (id number generated always as identity)
*
ERROR at line 1:
ORA-00406: COMPATIBLE parameter needs to be 12.0.0.0.0 or greater
ORA-00722: Feature "SQL identity columns"

Steps are from Creating a Database with the CREATE DATABASE Statement

I have seen people using 12.1.0.2 production data warehouses intentionally with optimizer_features_enable=9.2.0.8, but this was new...

Answer 2

You cannot set compatible=11 on a database that already has compatible=12 set. Like the error states, your control files have already been changed to the 12 format once you set compatible=12.

Your best bet would be to create an 11 database, upgrade it to 12, and then leave compatible=11 in the upgraded database.

You can check https://docs.oracle.com/database/122/UPGRD/toc.htm for steps on how to perform an upgrade. Somewhere in there it clearly states that once you change your compatible to 12, you cannot go back.