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
