Introduction

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

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 table spaces (along with the control files 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;’

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.

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;

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.

Comments/Suggestions are invited. Happy coding......!