Downgrading a failed 11g Release 2 Upgrade - the easy way

One of the best practices before performing any database upgrade is to take a full backup before we start the upgrade process just in case the upgrade fails and we want to then restore the previous version.

In many cases owing to the size of the database, we are constrained both by time as well as disk space when it comes to taking a full backup of the database.

So can we just get away by taking a backup of a much smaller subset of the database and not worry about backing up all our data and index tablespaces? – just the controlfiles, redo log files and the tablespaces – SYSTEM, SYSAUX, UNDOTBS1 (or whatever you call your undo tablespace) and TEMP (or whatever you call your temporary tablespace/tablespaces).

 

Let us see a test case where we start by performing a manual upgrade of a 10.2.0.4 database to 11g Release 2 and midway through the upgrade we simulate a failure by aborting the upgrade process. When we try to then start the database in the original 10g environment we see that our data dictionary has been corrupted (we see the ORA-00600 error) and then to perform a downgrade we just restore the backup of the SYSTEM,SYSAUX, UNDOTBS1 and TEMP tablespaces (along with the controlfiles and redo log files) which we took prior to performing an upgrade. So we do not have to restore any full database backup and the rollback is thus very fast.

 

Generate a script to make all data tablespaces READ ONLY

SQL> select ‘alter tablespace ‘ ||tablespace_name || ‘ read only;’
2 from dba_tablespaces where tablespace_name not in (‘SYSTEM’,'SYSAUX’,'UNDOTBS1′,’TEMP’);

‘ALTERTABLESPACE’||TABLESPACE_NAME||’READONLY;’
———————————————————-
alter tablespace USERS read only;
alter tablespace STATSARCHIVE read only;
alter tablespace EXAMPLE read only;
alter tablespace GOLDENGATE read only;

 

Make the tablespaces READ ONLY before performing a backup

SQL> alter tablespace USERS read only;
alter tablespace STATSARCHIVE read only;
alter tablespace EXAMPLE read only;
alter tablespace GOLDENGATE read only;

Tablespace altered.

SQL>
Tablespace altered.

SQL>
Tablespace altered.

SQL>
Tablespace altered.

 

SHUTDOWN IMMEDIATE the database and then take a backup (Note – just a few files and not full database backup)

sunos01:/u05/oradata/testdb $ cp *.log /u01/stage
sunos01:/u05/oradata/testdb $ cp *.ctl /u01/stage

sunos01:/u05/oradata/testdb $ cp sysaux01.dbf /u01/stage
sunos01:/u05/oradata/testdb $ cp system01.dbf /u01/stage
sunos01:/u05/oradata/testdb $ cp temp01.dbf /u01/stage
sunos01:/u05/oradata/testdb $ cp undotbs01.dbf /u01/stage

 

Start the 11g Release 2 upgrade by running the catupgrd.sql script

While the upgrade is in progress, from another session kill the PMON process which will abort the upgrade process.

We will see an error like this in the session which was performing the upgrade

ORA-03114: not connected to ORACLE
ERROR:
ERROR:
ORA-03114: not connected to ORACLE
Segmentation Fault(coredump)

We now try and start the database in the old 10g environment and see that the database crashes with an ORA-00600 error since the data dictionary has got corrupted due to the failed upgrade process.

SQL> startup
ORACLE instance started.

Total System Global Area 1073741824 bytes
Fixed Size 2046056 bytes
Variable Size 490735512 bytes
Database Buffers 574619648 bytes
Redo Buffers 6340608 bytes
Database mounted.
Database opened.

SQL> select comp_name,version,status
from dba_registry 2 ;
from dba_registry
*
ERROR at line 2:
ORA-04045: errors during recompilation/revalidation of SYS.DBA_REGISTRY
ORA-00600: internal error code, arguments: [17069], [0x3BF8AE2F8], [], [], [],
[], [], []

 

Shutdown the database and restore the backup we took prior to the upgrade

sunos01:/u01/stage $ mv * /u05/oradata/testdb
sunos01:/u01/stage $ cd /u05/oradata/testdb
sunos01:/u05/oradata/testdb $ cp control03.ctl /u03/oradata/testdb/control01.ctl
sunos01:/u05/oradata/testdb $ cp control03.ctl /u04/oradata/testdb/control02.ctl

Now we can start the 10g database and see that we have rolled back the unsuccessful 11g upgrade and things are back to normal now – we have done a very quick downgrade from 11g Release 2 to 10.2.0.4!

By not having to take a full backup before performing the 11g upgrade we have reduced the amount of downtime required for the upgrade and thus may have contributed to saving the business from loss of additional revenue

SQL> select comp_name,version,status
 from dba_registry;
COMP_NAME                                          VERSION              STATUS
-------------------------------------------------- -------------------- -----------
Oracle Ultra Search                                10.2.0.4.0           VALID
Spatial                                                       10.2.0.4.0           VALID
Oracle interMedia                                    10.2.0.4.0           VALID
OLAP Catalog                                          10.2.0.4.0           VALID
Oracle Enterprise Manager                   10.2.0.4.0           VALID
Oracle XML Database                             10.2.0.4.0           VALID
Oracle Text                                                10.2.0.4.0           VALID
Oracle Expression Filter                         10.2.0.4.0           VALID
Oracle Rules Manager                            10.2.0.4.0           VALID
Oracle Workspace Manager                   10.2.0.4.3           VALID
Oracle Data Mining                                   10.2.0.4.0           VALID
Oracle Database Catalog Views            10.2.0.4.0           VALID
Oracle Database Packages and Types                 10.2.0.4.0           VALID
JServer JAVA Virtual Machine                   10.2.0.4.0           VALID
Oracle XDK                                                  10.2.0.4.0           VALID
Oracle Database Java Packages            10.2.0.4.0           VALID
OLAP Analytic Workspace                         10.2.0.4.0           VALID
Oracle OLAP API                                         10.2.0.4.0           VALID

18 rows selected.

Set the tablespaces back to READ WRITE

alter tablespace USERS read write;
alter tablespace STATSARCHIVE read write;
alter tablespace EXAMPLE read write;
alter tablespace GOLDENGATE read write;

SQL>
Tablespace altered.

SQL>
Tablespace altered.

SQL>
Tablespace altered.

SQL>
Tablespace altered.