I have been told that, there are lot of blogs on How to manually upgrade a database from 11g to 19c but there are very few blogs which has action plan about how to downgrade a database from 19c to 11g. I would like create one for you. Along with that I have seen many are looking for a rollback plan. Well it’s all good when you have not released the system and you have a flashback option available with you. But Flashback option doesn’t work when application team faced an issue. And they are asking to roll back the system after 10 days.
My 19c home had OCT PSU 2020 Applied on it.
Object Comparisons
create table myschema.19c_objects as select * from dba_objects;
create table myschema.19c_registry as select * from dba_registry;
Check the Timezone file details
select * from v$timezone_file;
Apply the Downgrade Patch on 19c
cd 31171631/
opatch lsinventory > lsinventory_04062021.log ;
opatch lspatches > lspatches_04062021.log
opatch lsinventory -details > lsinventory_detail_04062021.log
opatch prereq CheckConflictAgainstOHWithDetail -ph ./
opatch apply
opatch lspatches
31171631;19C TO 11G DOWNGRADE UPDATED PER_PDB TO NULL
31771877;Database Release Update : 19.9.0.0.201020 (31771877)
29585399;OCW RELEASE UPDATE 19.3.0.0.0 (29585399)
OPatch succeeded.
Apply the Patch on 11g home – 20348910 and 20898997
cd 20898997;
opatch lsinventory > lsinventory_04062021.log ;
opatch lspatches > lspatches_04062021.log
opatch lsinventory -details > lsinventory_detail_04062021.log
opatch prereq CheckConflictAgainstOHWithDetail -ph ./
opatch apply
and
cd 20348910;
opatch lsinventory > lsinventory_04062021.log ;
opatch lspatches > lspatches_04062021.log
opatch lsinventory -details > lsinventory_detail_04062021.log
opatch prereq CheckConflictAgainstOHWithDetail -ph ./
opatch apply
opatch lspatches
20898997;
20348910;
31537677;Database Patch Set Update : 11.2.0.4.201020 (31537677)
29938455;OCW Patch Set Update : 11.2.0.4.191015 (30070097)
OPatch succeeded.
Create a restore point on database
create restore point BEFORE_DOWNGRADE_11G guarantee flashback database;
Shutdown the database ( if it’s rac shutdown both the instances.)
Remove all the underscore parameter from 19c home pfile. and start the instance ( only one instance if it is rac database.) This was one of the issue we faced where while running cat-downgrade we see one object didn’t get altered. So as a pre-requisite we added in our prechecks.
select count(1) from WRH$_SQL_PLAN;
create table WRH_SQL_PLAN_04062021 as select * from WRH$_SQL_PLAN;
select * from WRH$_SQL_PLAN where length (object_name)>30;
delete from WRH$_SQL_PLAN where length (object_name)>30;
select * from WRH$_SQL_PLAN where length (object_name)>30;
select count(1) from WRH$_SQL_PLAN;
commit;
shut immediate;
Catdowngrade from 19c home
cd $ORACLE_HOME/rdbms/admin/
sqlplus / as sysdba
startup downgrade;
set trimspool on
SPOOL downgrade_04062021.log
@catdwgrd.sql
SPOOL OFF
Please go through spool file generated in downgrade_04062021.log and check for ORA- errors. (grepping ORA- errors along with wc -l will not work, it already has so many bug fixes for ORA- 😉 Just an heads up ) Once you are sure there are no errors
Shutdown the instance from 19c home.
Update Timezone files on 11g home
Here you need to apply the timezone patch. You need to apply based on your timezone level. For me it was default one ie 32 so I had to apply patch 28125601 on 11g home so that $ORACLE_HOME/oracore/zoneinfo has 32 dat file in it.
Catreload.sql from 11g home
Make sure db is started in exclusive mode, and from 11g home having no underscore parameters
startup upgrade
SPOOL reload.log
@$ORACLE_HOME/rdbms/admin/catrelod.sql
SPOOL OFF
Refer the reload.log file and make sure everything is error free.
Once everything is completed, run UTLRP.sql and check the status of all of db components.
We faced the issue, where catproc component was invalid, and when we tried to manually compile the sys objects that didn’t work either for us. Due to the fact that creation itself had issue (3 objects where not created when I checked the reload.log )
If select status from dba_registry; return any invalid ---> ( From 11g home off-course )
set time on timing on
trimspool on
spool workaround.log
shutdown immediate;
startup upgrade;
@$ORACLE_HOME/dbhome_1/rdbms/admin/catalog.sql
@$ORACLE_HOME/dbhome_1/rdbms/admin/catproc.sql
@$ORACLE_HOME/dbhome_1/rdbms/admin/utlrp.sql
@$ORACLE_HOME/dbhome_1/rdbms/admin/utlrp.sql
@$ORACLE_HOME/dbhome_1/rdbms/admin/utlrp.sql
spool off
exit
if select status from dba_registry; return all valid
shut immediate;
Startup the database with 11g home
Compare the object status with for application schemas and release the system
Known Issues
DBMS_AUDIT_MGMT invalid post downgrade from 19c to 11g.
Thanks for reading!!!