Downgrade 19c to 12c

Not something I expected to do but was asked to download some 19c dbs after the dev lot hit an issue with 19 and wanted to go back to 12!

Some quick notes  as I don't expect ever to do this again - maybe 😉

[oracle@ora02 dbs]$ . oraenv

Pick the correct 19c databases

The Oracle base remains unchanged with value /u01/app/oracle

[oracle@ora02 dbs]$ sqlplus / as sysdba

SQL*Plus: Release 19.0.0.0.0 - Production on Fri Jun 26 09:06:05 2020

Version 19.3.0.0.0

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

Connected to:

Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production

Version 19.3.0.0.0

SQL> create pfile='$ORACLE_HOME/dbs/initcms.ora' from spfile ** change as approp.

2 /

File created.

Shutdown immediate and change init file removing the __unified pga line


Copy init’dbname’.ora to 12c home/dbs/init’dbname’.ora - 12c will need this to startup

e.g.

SQL> shutdown immediate;

Database closed.

Database dismounted.

ORACLE instance shut down.

cp $ORACLE_HOME/dbs/initdb1.ora /u01/app/oracle/product/12.2.0/dbhome_1/dbs/.

Then

SQL> startup downgrade;

ORACLE instance started.

Total System Global Area 2147481656 bytes

Fixed Size 8898616 bytes

Variable Size 553648128 bytes

Database Buffers 1577058304 bytes

Redo Buffers 7876608 bytes

Database mounted.

Database opened.


SQL> set termout on serverout on echo on timing on

SQL> spool /home/oracle/downgrade_db1.log

Run the grant admin and the clean audit or you will see error when running the


ERROR at line 1:

ORA-20001: Downgrade cannot proceed - Unified Audit Trail data exists.Please

clean up the data first using DBMS_AUDIT_MGMT.CLEAN_AUDIT_TRAIL.

ORA-06512: at line 75


SQL> grant ADMINISTER DATABASE TRIGGER to SYSTEM;

Grant succeeded.

SQL> exec DBMS_AUDIT_MGMT.CLEAN_AUDIT_TRAIL (audit_trail_type => DBMS_AUDIT_MGMT.AUDIT_TRAIL_UNIFIED, use_last_arch_timestamp => FALSE);

PL/SQL procedure successfully completed.

SQL> @?/rdbms/admin/catdwgrd.sql

Approx 5 mins…

…….

SQL>

SQL> Rem ***********************************************************************

SQL> Rem END catdwgrd.sql

SQL> Rem ***********************************************************************

SQL>

SQL>shutdown immediate;

SQL> exit


When migrating to 19c the timezone had been upgraded so had to do this to downgrade or hit issues.

export ORA_TZFILE=/u01/app/oracle/product/12.2.0/dbhome_1/oracore/zoneinfo/timezone_26.dat

set environment to Oracle 12 and change sid to the correct database i.e. db1

oracle@ora02 dbs]$ export

ORA_TZFILE=/u01/app/oracle/product/12.2.0/dbhome_1/oracore/zoneinfo/timezone_26.dat

[oracle@usze2qmbtora02 dbs]$ sqlplus / as sysdba

exit

SQL*Plus: Release 12.2.0.1.0 Production on Fri Jun 26 09:51:18 2020

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

Connected to an idle instance.

SQL> startup upgrade;

Remember you need to use the 19c init.file copied earlier with the __unified parameter removed or you will hit

ORA-01078: failure in processing system parameters

LRM-00109: could not open parameter file '/u01/app/oracle/product/12.2.0/dbhome_1/dbs/initdb1.ora'

SQL> startup upgrade pfile='/u99/media/init_db1.ora'

LRM-00101: unknown parameter name '__unified_pga_pool_size'

ORA-01078: failure in processing system parameters

SQL> startup upgrade -> need init file copied earlier

ORACLE instance started.

Total System Global Area 2147483648 bytes

Fixed Size 8622776 bytes

Variable Size 654314824 bytes

Database Buffers 1476395008 bytes

Redo Buffers 8151040 bytes

Database mounted.

Database opened.

SQL>

SQL> set termout on echo on timing on

SQL>@?/rdbms/admin/catrelod.sql

Check that this correctly

@?/rdbms/admin/utlrp.sql


SELECT comp_name, status, substr(version,1,10) as version

from dba_server_registry order by modified


COMP_NAME STATUS
----------------------------------- ----------------------------------------

VERSION
----------------------------------------

Oracle Real Application Clusters OPTION OFF

12.2.0.1.0

Oracle Database Catalog Views VALID

12.2.0.1.0

Oracle Database Packages and Types VALID

12.2.0.1.0

Oracle XML Database VALID

12.2.0.1.0

Oracle Workspace Manager VALID

12.2.0.1.0

JServer JAVA Virtual Machine VALID

12.2.0.1.0

Oracle Database Java Packages VALID

12.2.0.1.0

Oracle Text VALID

12.2.0.1.0

Oracle Multimedia VALID

12.2.0.1.0

Oracle XDK VALID

12.2.0.1.0

OLAP Analytic Workspace VALID

12.2.0.1.0

Spatial VALID

12.2.0.1.0

Oracle OLAP API VALID

12.2.0.1.0

Oracle Label Security VALID

12.2.0.1.0

Oracle Database Vault VALID

12.2.0.1.0


15 rows selected.


Shutdown - (change oratab to v12)

Set environment to grid 19c

. oraenv

+ASM

Remove database from srvctl

[oracle@usze2qmbtora02 ~]$ srvctl remove database -d ora01 

This will remove it from the /etc/oratab so manually put it back as 12

Now we need add the database back as 12c and start it as 12c

Still using the grid 19c environment (19c grid to 12 for another day!)

[oracle@usze2qmbtora02 ~]$ /u01/app/oracle/product/12.2.0/dbhome_1/bin/srvctl add database -d oradb1 -o /u01/app/oracle/product/12.2.0/dbhome_1

[oracle@usze2qmbtora02 ~]$ /u01/app/oracle/product/12.2.0/dbhome_1/bin/srvctl start database -db oradb1 

Run “crsctl stat res -t” and check the db is up and running – shows as 12c but under a 19c grid control - bit odd but hey ho. 

After some research found this :

https://www.youtube.com/watch?v=Jm7g_RAoa4Y