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.
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/
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/
%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';
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.
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
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.
Change the cluster database parameter to enable the cluster
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
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 downgrade6. 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
Shutdown the database
sqlplus /nolog
conn / as sysdba
shutdown immediateIn 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.startup upgrade
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
Recompile the invalid database objects:
sqlplus /nolog
conn / as sysdba
shutdown immediate
startup
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)
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
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
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
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.
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>
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.
