Minimize Downtime During Upgrade 

Solution:


1. Migration in NOARCHIVELOG mode will reduce the database upgrade time and hence the downtime.

NOTE: NOARCHIVELOG should NOT be used if any log-based replication technology is used. Log-based technologies include Active Data Guard, Oracle GoldenGate, XStream, Oracle Streams, Data Guard (Redo Apply and SQL Apply), Asynchronous Change Data Capture(CDC) and Audit Vault redo collector.


2. As the main function of a database upgrade is to create a new data dictionary, the upgrade can be tested for most of the upgrade functionality by using a copy of the database's SYSTEM tablespace and ROLLBACK SEGMENT tablespace and marking all other tablespaces OFFLINE. This allows realistic timings to be obtained without having to copy an entire database.


3. Make all tablespaces OFFLINE NORMAL or make READ ONLY  except for SYSTEM, SYSAUX (When migrating to 10gR2 or higher) and those containing rollback segments prior to upgrade. This way if upgrade fails, only the SYSTEM and rollback datafiles need to be restored rather than the entire database.

Note: You must OFFLINE the TABLESPACE as upgrade does not allow OFFLINE files in an ONLINE tablespace.

4. When upgrading to Oracle Database 10g, optimizer statistics are collected for dictionary tables that lack statistics. This statistics collection can be time consuming for databases with a large number of dictionary tables that lack statistics or are significantly changed during the upgrade.

To decrease the amount of downtime incurred when collecting statistics during upgrade, you must gather statistics prior to performing the actual database upgrade. DBMS_STATS.GATHER_SCHEMA_STATS is the procedure to be used to gather statistics for your database.

To verify the schema with stale statistics, refer Doc ID 560336.1 : Script to Check Schemas with Stale Statistics


5. (Applicable for DBUA only)

DBUA has an option to use flashback database feature for restoring and recovering the Database in case of upgrade failure. This can be done by selecting the option "Use Flashback and Guaranteed Restore Point".

In DBUA silent mode, below option/flag needs to be chosen :

-createGRP - When true, specifies that DBUA creates a guaranteed restore point when the database is in archive log mode and flashback mode



Note : Please test before upgrading production database by upgrading a test database.


Reference metalink Doc ID 455744.1