Rollback a Database Upgrade - 11.2.0.4 to 11.2.0.3

Database rollbacks usually follow a similar sequence of steps. From time to time, the rollback varies ever so slightly but on the most part, you can be pretty sure there is a significant overlap in database rollback plans. I've rolled back database in Linux and now in Windows.

The following post contains my notes on how to rollback a database from 11.2.0.4 to 11.2.0.3 in Windows. To raise the stakes, the rollback is done on Windows and in a RAC environment. Please note that if you are doing an Oracle Downgrade in windows from 11.2.0.4 to 11.2.0.3 you will likely invalidate your Oracle Multimedia component and you cannot work your way out of it without the assistance of MOS (Doc ID 2022064.1) - So read the note if you're doing this downgrade in windows.
The notes are an in depth adaptation of the Oracle Guide:
Downgrading Oracle Database to an Earlier Release
https://docs.oracle.com/cd/E11882_01/server.112/e23633/downgrade.htm
The database I am downgrading is fairly uncomplicated. You may want to have a look at the above document to see if there are any special circumstances that apply to your database setup.

Here is a brief guideline of the process:

1. Stop the database resource across both nodes using svrctl on the primary node
2. Create a pfile from the spfile
3. Adjust the pfile and set the cluster parameters to false
4. Overwrite the spfile using the new pfile
5. Start the database up using sqlplus in downgrade mode.
6. Run the cat downgrade script
7. Stop and delete the OracleService<INSTANCENAME> on the primary and secondary node
8. Using ORADIM, recreate the service on the primary node ONLY using the pfile from step 3 and
    using the old home.
9. Change directory to the old Oracle home admin directory
10. Startup the database in upgrade mode
11. Reload the catalogue.
12. Shutdown and startup the database followed by a recompile
13. Downgrade the database cluster resource with srvctl
14. Stop the database using srvctl.
15. Create a pfile with CLUSTER ENABLED
16. Stop and delete the OracleService<INSTANCENAME> on the primary and secondary node, recreate the services
17. Startup the database using srvctl

Lets begin with an overview.

Overview and preparation

Oracle home is where your database RDBMS software is held. Set it:
SET ORACLE_HOME=E:\Oracle\product\11.2.0.4\dbhome_1

Consider the following points:
Most of this work will take place in command prompt.
You are not downgrading any clusterware, just the database.
You are going to start the database up in downgrade mode within the latest release.
The catalogue will be downgraded from 11.2.0.4 to 11.2.0.3 using 11.2.0.4 downgrade script
The oracle home will be adjusted at some point and set back to 11.2.0.3
The entire catalogue will be recompiled using the older releases catalogue scripts.
The duration of this process is that of a database upgrade with some extra – I say 90 minutes.

Unless specified, you can assume all instructions apply to NODE1 – the primary node.
If an instruction must be executed on the secondary node, it will be stated explicitly

Remote desktop into the primary cluster node

Open up an administrator command window on both nodes in the cluster.

Set your ORACLE_HOME to the 11.2.0.4 directory:
SET ORACLE_HOME=E:\Oracle\product\11.2.0.4\dbhome_1

Set your ORACLE_SID to the database instance on the primary node:
SET ORACLE_SID=INSTANCE1

Adjust your %PATH% variable to ensure your 11.2.0.4 %ORACLE_HOME% appears first in the list before any 11.2.0.3 directory. To test this type “where sqlplus”
To see your path – type “path”

For trouble shooting purposes, it is recommended that you use baretail.exe (linux tail command for windows) which can be found here: https://www.baremetalsoft.com/baretail/

Use tail to follow the database logs which usually resides in
%ORACLE_BASE%\diag\rdbms\<databasename>\
<instancename>\trace\alert_<instancename>.log

1. Stop the database resource across both nodes using svrctl on the primary node

srvctl stop database -d <DATABASENAME>

Either watch the alert log or check the cluster resource ora.<DATABASENAME>.db to see when the database is finally shutdown.

2. Create a pfile from the spfile

You are going to create a pfile using the current spfile that is within the +ASM diskgroup.
Make sure the following folder exists on NODE1 and NODE2 so you can modify the pfile file you’re about to create:
E:\Oracle\scripts\downgrade\

In your administration command window on the primary node, create the pfile:
sqlplus /nolog
conn / as sysdba
create pfile='E:\Oracle\scripts\downgrade\<DATABASENAME>_clusterdisable.ora' from spfile='+DATA/<DATABASENAME>/spfile<DATABASENAME>.ora';

3. Adjust the pfile and set the cluster parameters to false

Set the CLUSTER_DATABASE parameter in the pfile to false and SAVE the file

4. Overwrite the spfile using the new pfileIn your administration command window on the primary node, overwrite the spfile using the adjusted pfile:

sqlplus /nolog
conn / as sysdba
create spfile='+DATA/<DATABASENAME>/spfile<DATABASENAME>.ora' from pfile='E:\Oracle\scripts\downgrade\<DATABASENAME>_clusterdisable.ora';

5. Start the database up using sqlplus in downgrade mode. In command, change directory to the administration folder of the 11.2.0.4 release:
e:
cd E:\Oracle\product\11.2.0.4\dbhome_1\RDBMS\ADMIN

Now log into sqlplus and start the database up in downgrade mode
sqlplus /nolog

conn / as sysdba

startup downgrade

6. Run the cat downgrade script

Here you are downgrading the catalogue to the previous database release. Make use of the new releases downgrade script.

spool e:\oracle\scripts\downgrade\downgade.log
@catdwgrd.sql
spool off

If you encounter serious errors that abort the script – correct the issues with the assistance of the my oracle support website and rerun the script again and again. Don’t worry about Oracle Multimedia components, they can be ignored until later.

7. Stop and delete the OracleService<INSTANCENAME> on the primary and secondary node

Shutdown the database
sqlplus /nolog

conn / as sysdba

shutdown immediate

In command, the service must be deleted

NODE1
NET STOP OracleService<INSTANCENAME>
oradim -DELETE -SID <INSTANCENAME>

NODE2
NET STOP OracleService<INSTANCENAME>
oradim -DELETE -SID <INSTANCENAME>

*Usually in a cluster, instance name is typically your databasename suffixed with a number to indicate the node it resides on.

8. Using ORADIM, recreate the service on the primary node ONLY using the pfile from step 3 and using the old home.

Change homes in command:
set ORACLE_HOME=E:\Oracle\product\11.2.0.3\dbhome_1

Now recreate the service using the PFILE from step 3.
oradim -new -SID <INSTANCENAME> -startmode manual -pfile 'E:\Oracle\scripts\downgrade\<INSTANCENAME>_clusterdisable.ora


9. Change directory to the old Oracle home admin directory

In command, change directories to your old oracle home:
e:
cd E:\Oracle\product\11.2.0.3\dbhome_1\RDBMS\ADMIN

10. Startup the database in upgrade modeNow is the time to reload the 11.2.0.3 catalogue back into the database. The following is similar to an upgrade:

sqlplus /nolog

conn / as sysdba
startup upgrade

11. Reload the catalogue.

Reload the catalogue:
spool e:\oracle\scripts\downgrade\reload.log
@catrelod.sql
spool off

Check the spool file for any errors.
If you encounter any issues that terminate the script, use MOS to resolve the issue.
The script should run its course in an hour. Once complete, it will kick you out of your console.

12. Shutdown and startup the database followed by a recompile

Recompile the invalid database objects:
sqlplus /nolog

conn / as sysdba
shutdown immediate
startup

@utlrp.sql

If Oracle Multimedia is in an invalid state, execute
exec validate_ordim;

NOTE: What would a major rebuild be without some bugs. There is a bug when downgrading from Oracle 11.2.0.4 to 11.2.0.3 in WINDOWS – no matter what you do, your Oracle Multimedia will remain invalid. To correct this issue, see note (Doc ID 2022064.1)

13. Downgrade the database cluster resource with srvctl

Downgrade the cluster resource on the primary node.
From command, execute the following.

NODE 1:
set ORACLE_HOME=E:\Oracle\product\11.2.0.4\dbhome_1

srvctl downgrade database -d <DATABASENAME> -o E:\Oracle\product\11.2.0.3\dbhome_1 -t 11.2.0.3.0


14. Stop the database using srvctl.

NODE 1:
srvctl stop database -d <DATABASENAME>

15. Create a pfile with CLUSTER ENABLED

Duplicate the <DATABASENAME>_clusterdisable.ora file to a new file <DATABASENAME>_clusterenable.ora

Change the cluster database parameter to enable the cluster

Copy this file to E:\Oracle\scripts\downgrade\ on NODE1 and NODE2

16. Stop and delete the OracleService<INSTANCENAME> on the primary and secondary node, recreate the services

NODE1
NET STOP OracleService<INSTANCENAME>
oradim -DELETE -SID <INSTANCENAME>
Change homes in command:
set ORACLE_HOME=E:\Oracle\product\11.2.0.3\dbhome_1

Now recreate the service using the PFILE from step 3.
oradim -new -SID <INSTANCENAME> -startmode manual -pfile 'E:\Oracle\scripts\downgrade\<DATABASENAME>_clusterenable.ora

NODE2

NET STOP OracleService<INSTANCENAME>
oradim -DELETE -SID <INSTANCENAME>

Change homes in command:
set ORACLE_HOME=E:\Oracle\product\11.2.0.3\dbhome_1

Now recreate the service using the PFILE from step 3.
oradim -new -SID <INSTANCENAME> -startmode manual -pfile 'E:\Oracle\scripts\downgrade\<DATABASENAME>_clusterenable.ora*Usually in a cluster, instance name is typically your databasename suffixed with a number to indicate the node it resides on.

17. Startup the database using srvctl

NODE1
srvctl start database -d <DATABASENAME>

Check that the database is accessible on both nodes, restart the cluster if accessibility is an issue on both nodes.