upgrade Oracle 10g to 11g using DBUA

Prerequistics

1) oracle 10g version should be above or equal to 10.2.0.4. if not please upgrade it to 10.2.0.4

SQL> select banner from v$version;

2) timezone should be >=4

SELECT CASE COUNT(DISTINCT(tzname))

WHEN 183 then 1

WHEN 355 then 1

WHEN 347 then 1

WHEN 377 then 2

WHEN 186 then case COUNT(tzname) WHEN 636 then 2 WHEN 626 then 3 ELSE 0 end

WHEN 185 then 3

WHEN 386 then 3

WHEN 387 then case COUNT(tzname) WHEN 1438 then 3 ELSE 0 end

WHEN 391 then case COUNT(tzname) WHEN 1457 then 4 ELSE 0 end

WHEN 392 then case COUNT(tzname) WHEN 1458 then 4 ELSE 0 end

WHEN 188 then case COUNT(tzname) WHEN 637 then 4 ELSE 0 end

WHEN 189 then case COUNT(tzname) WHEN 638 then 4 ELSE 0 end

ELSE 0 end VERSION

FROM v$timezone_names;

3) Stop all batch jobs

4) Ensure no files need media recovery

sqlplus "/ as sysdba"

SQL> SELECT * FROM v$recover_file;

This should return no rows.

5) Ensure no files are in backup mode:

SQL> SELECT * FROM v$backup WHERE status!='NOT ACTIVE';

This should return no rows.

6) Resolve any outstanding unresolved distributed transaction:

SQL> select * from dba_2pc_pending;

7) Ensure the users sys and system have 'system' as their default tablespace.

SQL> SELECT username, default_tablespace FROM dba_users WHERE username in ('SYS','SYSTEM');

8) Ensure that the aud$ is in the system tablespace when auditing is enabled.

SQL> SELECT tablespace_name FROM dba_tables WHERE table_name='AUD$';

09) Empty Recyclebin before start upgradation

sqlplus "/ as sysdba"

PURGE DBA_RECYCLEBIN;

select * from recyclebin;

10) Gathers dictionary and schema statistics

sqlplus "/ as sysdba"

EXEC DBMS_STATS.GATHER_DICTIONARY_STATS;

EXEC DBMS_STATS.GATHER_SCHEMA_STATS('SYS');

EXEC DBMS_STATS.GATHER_SCHEMA_STATS('SYSMAN');

EXEC DBMS_STATS.GATHER_SCHEMA_STATS('DPCDSL');

11) check invalid objects and resolve

select OWNER,OBJECT_NAME,OBJECT_TYPE from dba_objects where status='INVALID' ORDER BY OWNER;

-----------COMPILE INVALID OBJECTS

spool c:\temp\invalid.sql ;

select OBJECT_NAME from dba_objects where owner='LDBO' AND STATUS='INVALID'

select

'ALTER ' || OBJECT_TYPE || ' ' ||

OWNER || '.' || OBJECT_NAME || ' COMPILE;'

from

dba_objects

where

status = 'INVALID'

and

object_type in ('PACKAGE','FUNCTION','PROCEDURE','VIEW','TRIGGER')

;

spool out ;

@ c:\temp\invalid.sql ;

-----

12) Perform Cold Backup

sql>shutdown immediate

backup physical file folder to safe location in case of disaster

13) Check for adequate freespace on archive log destination file systems. Note that if your database is in archivelog mode, then it is always desirable and advisable to upgrade the database in noarchivelog mode as that will reduce the time taken to upgrade the database. After the upgrade you can again put the database in the archivelog mode.

sqlplus "/ as sysdba"

SQL> SELECT LOG_MODE FROM SYS.V$DATABASE;

SQL> startup mount;

SQL> alter database noarchivelog;

SQL> alter database open;

14)

Install oracle 11g software without using create database also without upgrade option

15)

Oracle Database 11.1 Pre-Upgrade Information Tool for checking any error

Copy file : utlu112i.sql to C:\temp (any temporary location) from “D:\app\Administrator\product\11.2.0\dbhome_1\RDBMS\ADMIN\utlu112i.sql” .

Connected into Oracle 10g instance while DB is up and running and execute the utlu112i.sql file from SQL prompt.

sqlplus "/ as sysdba"

SQL10g> @C:\temp\utlu112i.sql

SQL10g>select * from registry$sys_inv_objs;

SQL10g>select * from registry$nonsys_inv_objs;

resolve all errors

16)

Final) Now lets perform the Database upgrade steps using : Database Upgrade Assistant(DBUA) a GUI to upgrade the DB and it can be found under Start -) All Programs -) Oracle 11g Home -) Configuration and Migration Tool -) Database Upgrade Assistant

17) Version Validation

SQL11g>select comp_name,version,status from dba_registry;

SELECT * FROM dba_registry_log;

SELECT * FROM sys.registry$error;

SQL11g>select banner from v$version;

18) repeat Oracle Database 11.2 Pre-Upgrade Information Tool until all error resolved

SQL11g>sqlplus "/ as sysdba"

SQL11g> @C:\temp\utlu112i.sql

19) Use utluiobj.sql after the upgrade to identify any new invalid

Oracle Database 11.1 Post-Upgrade Invalid Objects Tool

This tool lists post-upgrade invalid objects that were not invalid prior to upgrade (it ignores pre-existing pre-upgrade invalid objects).

SQL11g>@D:\app\Administrator\product\11.2.0\dbhome_1\RDBMS\ADMIN\utluiobj.sql

---------------------

SQL11g> @D:\app\Administrator\product\11.2.0\dbhome_1\RDBMS\ADMIN\utluiobj.sql

.

Oracle Database 11.1 Post-Upgrade Invalid Objects Tool 12-06-2011 11:38:59

.

This tool lists post-upgrade invalid objects that were not invalid

prior to upgrade (it ignores pre-existing pre-upgrade invalid objects).

.

Owner Object Name Object Type

.

PL/SQL procedure successfully completed.

--------------------------

20) result of upgrade

@D:\oracle\product\10.2.0\db_1\RDBMS\ADMIN\utlu102s.sql

21) if owr is not installed and if require then instal manually

to install oracle workspace manager

select username from dba_users where username ='WMSYS';

ALTER USER WMSYS IDENTIFIED BY WMSYS ACCOUNT UNLOCK;

@D:\app\Administrator\product\11.2.0\dbhome_1\RDBMS\ADMIN\owminst.plb

select comp_name,version,status from dba_registry;

@D:\app\Administrator\product\11.2.0\dbhome_1\RDBMS\ADMIN\utlu112s.sql