Creating an Oracle 11.2-Compatible Database in Oracle 12.2.0.1

oracleoracle-12c

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.

Best Answer

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...

Related Solutions

Oracle upgrade, v$version what is it actually doing

I bet your database files are not upgraded to 8.1.7.0.

I can't get how you could open the DB from one or another release without errors. I wonder that since this is a patch release upgrade (http://docs.oracle.com/cd/A84870_01/doc/server.816/a76957/migintro.htm#10138), and the COMPATIBLE parameter in init file its in 8.1.5, when you start the DB from 8.1.7 it builds compatible memory and disk structures with 8.1.5 version and "works" (at least, it opens. But I wouldnt go with this even in TEST environment)

To check the DB version You can also use the DBMS_UTILITY.DB_VERSION procedure. I don't know if it will return the instance or the database files version. You can run something like this in sqlplus:

SQL> set serveroutput on
SQL> DECLARE
       v_version varchar2(10);
       v_comp varchar2(10);
     BEGIN
       DBMS_UTILITY.DB_VERSION ( v_version, v_comp);
       DBMS_OUTPUT.PUT_LINE ('DB Version: ' || v_version);
       DBMS_OUTPUT.PUT_LINE ('Compatibility: ' || v_comp);
     END;
     /

Another option is the hardway. In linux if I use strings with my SYSTEM TABLESPACE datafile I can grep the release version from them. But ... I cannot claim this is a reliable method :\

$ strings o1_mf_system_91ck66x1_.dbf | grep "[0-9]\{1,2\}\.[0-9]\{1\}" | grep -i "Catalog Views" | head
 Oracle Database Catalog Views Re Oracle XML Database Version 11.2
COracle Database Catalog Views Release 11.2.0.3.0 - 64bit Production"DBMS_REGISTRY_SYS.VALIDATE_CATALOG
COracle Database Catalog Views Release 11.2.0.3.0 - 64bit Production"DBMS_REGISTRY_SYS.VALIDATE_CATALOG
COracle Database Catalog Views Release 11.2.0.3.0 - 64bit Production"DBMS_REGISTRY_SYS.VALIDATE_CATALOG

Anyway, In a mess like this I'll go for export-import migration, and forget about what has been done in the past. You said you can perform export with no problems, right? If the database its now open with 8.1.5, and the application has been hard tested with no errors, let's assume it's on 8.1.5 and migrate. Perform a new fresh 8.1.7 install, stop the application, export every application related schema from the production DB, and import into the new 8.1.7. Done.

Now, me and everyone will tell you that this is ridiculous. Why this? I'll try to migrate to a new server and DB version at any cost precisely because the elderly nature of your environment will make it only harder and harder in the future, not to mention the lack of security and new features of having a 13 years old software release running in a server powerless that your cellphone.

I cant imagine a good reason to not migrate that server entirely, even more when (based on what you said) no one knows what kind of mess did in the past. Fix the problem, not the symptom =)

Regards

How to recreate or restore Corrupted Data Files in Oracle 11g

If you don't have any backups. Well... You're just flat down.

Nothing to be done.

You might as well drop the entire database and recreate.

Reasons follow:

1.- Corrupt blocks can only be repaired by getting the original block from a full backup (at least of the datafile that has the corrupted blocks) and applying the archivelogs after the original block has been restored to the original location.

2.- No backups, No archivelogs and no exports means you lost your data. There is no way to get it back as you cannot find out the actual storage order of each row once it got corrupted.

3.- If the undo tablespace got corupted as well, you can't even recover a datafile/block because the RDBMS won't find the undo data needed to make it consistent. In the best of cases you could apply forward changes on the redo logs, but you couln't rollback unfinished transactions before the crash.

Next time, as soon as you have a running database, get it to work in archivelog mode, make a full back every once in a while and, to be on the safe side, export the most important data you have there..

Best Answer

Related Solutions

Oracle upgrade, v$version what is it actually doing

How to recreate or restore Corrupted Data Files in Oracle 11g

Related Question