Upgrading To 10g Database

Database Upgrade 10gR2

Manually

The steps for a manual upgrade to the Oracle Database 10g database version are briefly listed here, 1) Create a spool file. 2) Log in as a user with the SYSDBA privilege, and run the PreUpgrade Information Tool. Make any changes it recommends. 3) Backup the database you're going to upgrade. 4) Copy the current init.ora file to the new Oracle Database 10g init.ora file location. 5) Shutdown the database and restart it under the new Oracle 10g home in the STARTUP UPGRADE mode. 6) Create the required Sysaux tablespace. 7) Run the catupgrd.sql upgrade script. 8) Check to see if any objects became invalidated during the database upgrade. 9) Run the utlrp.sql script to recompile any objects that become invalid during the database upgrade. 10)Run the Post-Upgrade Status Tool. 11)End the spool file. 12)Shutdown and startup the new database Step 1 :

Create a Spool file

Create a spool file to record the upgrade process so that you can review it later. Step

2 :

Starting the Pre-Upgrade Information Tool

To start the Pre-Upgrade Information Tool, run the following : SQL > @$ORACLE_HOME\rdbms\admin\utlu102i.sql SQL> spool off Check out the upgrade.log spool file to see if you meet all upgrade requirements. Step 3 :

Backup the Database

Backup the database you are upgrading, either by using RMAN or by using your own backup techniques

Step 4

:

Copy your init.ora File

Copy your current init.ora file to its default location in the new Oracle Database 10g Release 2 home ($ORACLE_HOME/dbs). You should also make the changes that the PreUpgrade information tool recommended. Remove all obsolete and deprecated parameters and add the new parameters, such as SGA_TARGET, which automates shared memory management. Step

5 :

Starting up the New Database

Shut down the current database if it's running, and start it up again with the updated init.ora parameter file under the new Oracle Database 10g home. You must use the new STARTUP UPGRADE command to start up your database under the Oracle 10.2 version, which tells Oracle to modify those initialization parameters that would otherwise cause errors during the upgrade( for example the new startup mode will set the JOB_QUE_PROCESSES parameter to 0 ). The startup upgrade mode starts a restricted session and prepares the environment for the upgrade. Step 6 : Creating the Sysaux Tablespace Once you have brought up the database instance,you can create the Sysaux tablespace. SQL>

CREATE TABLESPACE sysaux DATAFILE '/U01/ORADATA/PROD/SYSAUX01.DBF' SIZE 500M REUSE EXTENT MANAGEMENT LOCAL SEGMENT SPACE MANAGEMENT AUTO;

The database is now technically converted into an Oracle Database 10g version, as shown by the following query. SQL> SELECT * FROM V$VERSION; In the next step, you actually upgrade the current database to the 10.2 version. Step 7 :

Running the Upgrade Script

Although the database is opened and a query to V$VERSION reveals

that your database is now an Oracle Database 10g database, you will have to run the upgrade script (catupgrd.sql) . The upgrade script (catupgrd.sql) automatically runs the appropriate upgrade script for the database version you're upgrading and uses procedures from the DBMS_REGISTRY package to execute various component upgrades. Log in as a user with SYSDBA privileges & run the upgrade script. SQL> @$ORACLE_HOME/rdbms/admin/catupgrd.sql

Step 8

:

Checking for Invalid Objects

Oracle will create, drop , and alter some database objects as the upgrade process progresses, thus invalidating some internal Oracle packages and procedures. After the upgrade script has finished you need to check for invalid objects: SQL> Step 9

SELECT count(*) FROM DBA_OBJECTS WHERE STATUS='INVALID';

:

Recompiling and Validating Invalidated objects

By running the Oracle provided utlrp.sql script, you can do a recompilation and validation of all the objects invalidated during the upgrade process. During this process, utlrp.sql calls utlprp.sql ( a wrapper based on the UTL_RECOMP package), Note that Oracle will dynamically compile each of the invalidated objects when they are accessed if you don't do it now. However , this runtime compilation of invalidated objects could slow down your database's performance. You can recompile all invalidated Oracle database objects using the utlrp.sql script: SQL> @$ORACLE_HOME/rdbms/admin/utlrp.sql Once it has validated all the invalid objects, the utlrp.sql script validates each individual component in the database and updates the DBMS_SERVER_REGISTRY view. Note : You can revert to the older database as long as you have a backup of the database made before starting the upgrade process. It's vital to have a backup, since the

upgrade process may fail before it's completed, leaving you with a database that won't be functional under the pre- or post-upgrade version of Oracle.

Step 10 :

Running the Post-Upgrade Status Tool

After the upgrade process completes, you must run the Post-upgrade Status Tool, using the following script: SQL>@utlu102s.sql The Post-Upgrade Status Tool determines the upgrade status of each database component by querying the DBA_SERVER_REGISTRY view. SQL> SELECT comp_id,comp_name,version,status FROM DBA_SERVER_REGISTRY; Output from the Post-Upgrade Status Tool Pointing out a problem with a component Caution Don't start the newly upgraded database under the old Oracle home – this corrupts your database. Step 11 :

Ending the Spool File

After the upgrade script had finished. You can turn off the spooling of the upgrade process. SQL> SPOOL OFF Step 12 :

Shutting Down and Starting Up the New Database

You can now shutdown and restart the instance so you're ready for normal database operations. SQL> SHUTDOWN IMMEDIATE SQL> STARTUP

After the Upgrade After the upgrade, you have a brand-new Oracle Database 10g (10.2.0.0) instance. Of course, at this point, all your old application code continues to remain at the Oracle 8i, Oracle 9i, or Oracle 10.1 version level. You may want to test the new features as well as your old applications, to see that they run in the upgraded database without problems. It's a good idea to promptly back up the newly database. You must also change the passwords for the Oracle-supplied user accounts if you manually upgraded the database. You must also need to modify the

listener.ora file, as well as migrate to the SPFILE from your init.ora file. Several of Oracle Database 10g's new feature expect you to use locally managed tablespaces. You need to migrate all your dictionarymanaged tablespaces to locally managed tablespaces by using the DBMS_SPACE_ADMIN package. You need to migrate database jobs to the new Oracle Scheduler and check out the Automatic Jobs feature, the Automatic Database Diagnostic Monitor and many other goodies available in the new Oracle Database 10g Release 2 database.