Validating databases after migration oracle 8i 10g

A blog about Database Administration, Exadata, DBA tutorials, Database troubleshooting and new Development in Database area.

The views expressed are my own and not necessarily those of Oracle and its affiliates.

Database Upgrade are common but risky task for a DBA if not done properly.

validating databases after migration oracle 8i 10g-39

Minimum Version of the Oracle database software that can be directly upgraded to Oracle 11g Release 2, So before upgrade remote DBA needs to check this.

Source Database Target Database 9.2.0.8 or higher 11.2.x 10.1.0.5 or higher 11.2.x 10.2.0.2 or higher 11.2.x 11.1.0.6 or higher 11.2.x The following database software version will require an indirect upgrade path.

In this case DBA needs to do double effort, because two upgrades are needed.

Source Database --- Log in to the system as the owner of the Oracle Database 11g Release 2 (11.2) Oracle home directory.

Copy the Pre-Upgrade Information Tool (utlu112i.sql) and utltzuv2from the Oracle Database 11g Release 2 (11.2) ORACLE_HOME/rdbms/admin directory to a directory outside of the Oracle home, such as the temporary directory on your system.

$ORACLE_HOME/rdbms/admin/utlu112Should be change to the directory where utlu112had been copied in the previous step.

Start SQL*Plus and connect to the database instance as a user with SYSDBA privileges. Please note that the database should be started using the Source Oracle Home.

$ sqlplus '/ as sysdba' SQLIf the script reports any invalid objects, run $ORACLE_HOME/rdbms/admin/(multiple times) to validate the invalid objects in the database, until there is no change in the number of invalid objects.

When upgrading to Oracle Database 11g Release 2 (11.2), 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, but statistics gathering only occurs for those tables that lack statistics or are significantly changed during the upgrade If your operating system is UNIX then complete this step, else skip to next Step. Make sure the following environment variables point to the Oracle database software 11g Release directories: - ORACLE_BASE - ORACLE_HOME - PATH Note : After /etc/oratab is updated to have sid and Oracle Home (11.2), you can execute oraenv (/usr/local/bin/oraenv) and set the environment.

The input has to be the sid which is entered in /etc/oratab against 11g home.

Tags: , ,