Upgradation from 9.2.0.8 to 10.2.0.3

=>Prerequisites and recommendations :

Ø Install Oracle 10g Release-2 / Release-3 in a new Oracle Home. Kindly refer to the 10g installation guide for the same

Ø Install the 10g Companion CD for the base ORACLE 10g version.

Ø Compatibility Matrix

a) Minimum Version of the database that can be directly upgraded 10gR2/10gr3

8.1.7.4	-> 10.2.X.X.X
9.0.1.4 or 9.0.1.5	-> 10.2.X.X.X
9.2.0.4 or higher	-> 10.2.X.X.X
10.1.0.2 or higher	-> 10.2.X.X.X

b) The following database version will require an indirect upgrade path.

7.3.3 (or lower)	> 7.3.4	> 8.1.7	> 8.1.7.4	> 10.2.X.X.X
7.3.4		> 8.1.7	> 8.1.7.4	> 10.2.X.X.X
8.0.n		> 8.1.7	> 8.1.7.4	> 10.2.X.X.X
8.1.n		> 8.1.7	> 8.1.7.4	> 10.2.X.X.X

NOTE : BEFORE UPGRADATION ACTIVITY TAKE THE COLD BACKUP OF DATABASE AND ORACLE_HOME

============ Upgradation Activity ==============================

Step 1 : Run the “utlu102i.sql”

Copy the following files from the 10gR2 ORACLE_HOME/rdbms/admin directory to a directory outside of the Oracle home, such as the /tmp directory on your system

sqlplus '/as sysdba'

SQL> spool Database_Info.log
SQL> @utlu102i.sql
SQL> spool off

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

WARNING: --> DRSYS tablespace is not large enough for the upgrade

alter tablespace

size of DRSYS tablespace need to be increased

---> atleast to 50 MB

====>

WARNING: --> "streams_pool_size" is not currently defined and needs a value of

at least 50331648

WARNING: --> "session_max_open_files" needs to be increased to at least 20

streams_pool_size=50331648

session_max_open_files=20

=======>

--> "hash_join_enabled"

--> "log_archive_start"

Remove this parameter from the pfile of 9i

===>

...The 'JServer JAVA Virtual Machine' JAccelerator (NCOMP) is required to be installed from the 10g Companion CD.

...The 'Oracle interMedia Image Accelerator' is required to be installed from the 10g Companion CD

====>

WARNING: --> Passwords exist in some database links.

.... Passwords will be encrypted during the upgrade.

.... Downgrade of database links with passwords is not supported.

WARNING: --> Deprecated CONNECT role granted to some user/roles.

.... CONNECT role after upgrade has only CREATE SESSION privilege.

WARNING: --> Database contains stale optimizer statistics.

.... Refer to the 10g Upgrade Guide for instructions to update

.... statistics prior to upgrading the database.

.... Component Schemas with stale statistics:

.... SYS

.... XDB

.... OLAPSYS

.... MDSYS

WARNING: --> Database contains INVALID objects prior to upgrade.

.... USER ORAEQUS has 8 INVALID objects.

===>

--> New "SYSAUX" tablespace

....minimum required size for database upgrade: 500 MB

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

#########################################################################################

Step 2 : Take the backup sys.link$ and create a script of all the bd links .

===> DB_links password will be encrypted in ORACLE10g , So ,in case of downgrade password will no be available . So, Please take the backup of database link

Create table db_link_backup as select * from sys.link$;

select 'create database link '||name||'connect to'||userid||'identified by '||password||';' from sys.link$;

##########################################################################################

Step 3 : Check for the deprecated CONNECT Role

User that contains CONNECT -->

SELECT grantee FROM dba_role_privs

WHERE granted_role = 'CONNECT' and

grantee NOT IN (

'SYS', 'OUTLN', 'SYSTEM', 'CTXSYS', 'DBSNMP',

'LOGSTDBY_ADMINISTRATOR', 'ORDSYS',

'ORDPLUGINS', 'OEM_MONITOR', 'WKSYS', 'WKPROXY',

'WK_TEST', 'WKUSER', 'MDSYS', 'LBACSYS', 'DMSYS',

'WMSYS', 'OLAPDBA', 'OLAPSVR', 'OLAP_USER',

'OLAPSYS', 'EXFSYS', 'SYSMAN', 'MDDATA',

'SI_INFORMTN_SCHEMA', 'XDB', 'ODM');

===> grant them all priviliges , other than "CONNECT"

select 'CREATE VIEW, CREATE TABLE, ALTER SESSION,CREATE CLUSTER,CREATE SESSION,CREATE SYNONYM,CREATE SEQUENCE,CREATE DATABASE LINK

to '||grantee||';' from dba_role_privs

WHERE granted_role = 'CONNECT' and

grantee NOT IN (

'SYS', 'OUTLN', 'SYSTEM', 'CTXSYS', 'DBSNMP',

'LOGSTDBY_ADMINISTRATOR', 'ORDSYS',

'ORDPLUGINS', 'OEM_MONITOR', 'WKSYS', 'WKPROXY',

'WK_TEST', 'WKUSER', 'MDSYS', 'LBACSYS', 'DMSYS',

'WMSYS', 'OLAPDBA', 'OLAPSVR', 'OLAP_USER',

'OLAPSYS', 'EXFSYS', 'SYSMAN', 'MDDATA',

'SI_INFORMTN_SCHEMA', 'XDB', 'ODM');

##########################################################################################

( There is no table on our site , that required this step )

Step 4 : Data of table with "TIMESTAMP WITH TIMEZONE" can be affected . Thus to avoid this ..

Copy the files , to the temp location .. Then the scripts need to be run

$ sqlplus '/as sysdba'

SQL> spool TimeZone_Info.log

SQL> @utltzuv2.sql

SQL> spool off

Scripts mentioned below will show you the column with "TIMESTAMP WITH TIMEZONE" .then back up the data in character format before you upgrade the database. After the upgrade, you must update the tables to ensure that the data is stored based on the new rules

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

SQL> @utltzuv2.sql

DROP TABLE sys.sys_tzuv2_temptab

*

ERROR at line 1:

ORA-00942: table or view does not exist

Table created.

Query sys.sys_tzuv2_temptab Table to see if any TIMEZONE data is affected by

version 2 transition rules

PL/SQL procedure successfully completed.

Commit complete.

SQL> spool off

[ilomsdb01:ora9equs] /tmp> sysdba

SQL*Plus: Release 9.2.0.8.0 - Production on Wed Jun 25 10:34:04 2008

Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.

Connected to:

Oracle9i Enterprise Edition Release 9.2.0.8.0 - 64bit Production

With the Partitioning, OLAP and Oracle Data Mining options

JServer Release 9.2.0.8.0 - Production

SQL> select * from sys.sys_tzuv2_temptab;

no rows selected

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

OR :

Suppose if there is any table

SQL> desc test_table

x number primary key

y timestamp with time zone

Now ,take the backup of this table

SQL> create table test_table_backup as select * from test_table where 1=2;

SQL> insert into test_table_backup select x,to_char(y, 'YYYY-MM-DD HH24.MI.SSXFF TZR') from test_table;

After the upgrade , run the update command to update the data according to new transition rule

update test_table t set t.y = (select to_timestamp_tz(t1.y,'YYYY-MM-DD HH24.MI.SSXFF TZR') from test_table_backup t1 where t.x=t1.x);

##########################################################################################

Step 5 : Starting in Oracle 9i the National Characterset (NLS_NCHAR_CHARACTERSET) will be limited to UTF8 and AL16UTF16. Any other NLS_NCHAR_CHARACTERSET will no longer be supported. Basicaly N-datatype used this NLS-Characterset conversion

NOTE: If you are upgrading from Oracle9i to 10g, Then no NLS Characterset conversion is required

---------------- Internal Use ------------------------

SQL> select distinct OWNER, TABLE_NAME from DBA_TAB_COLUMNS where DATA_TYPE in ('NCHAR','NVARCHAR2', 'NCLOB') and OWNER not in ('SYS','SYSTEM','XDB');

OWNER TABLE_NAME

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

OE BOMBAY_INVENTORY

OE PRODUCTS

OE PRODUCT_DESCRIPTIONS

OE SYDNEY_INVENTORY

OE TORONTO_INVENTORY

PM PRINT_MEDIA

6 rows selected.

SQL> select * from nls_database_parameters where parameter ='NLS_NCHAR_CHARACTERSET';

PARAMETER VALUE

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

NLS_NCHAR_CHARACTERSET AL16UTF16

= = = == == === == == == === For 8i to 10g upgradaion = = = = = = == == == == === == == = = =

select distinct OWNER, TABLE_NAME from DBA_TAB_COLUMNS where DATA_TYPE in ('NCHAR','NVARCHAR2', 'NCLOB') and OWNER not in ('SYS','SYSTEM','XDB');

If no rows are returned it should mean that the database is not using N-type columns for user data, so simply go to the next step.

SQL> select * from nls_database_parameters where parameter ='NLS_NCHAR_CHARACTERSET';

If you are using N-type columns AND your National Characterset is UTF8 or is in the following list:

JA16SJISFIXED , JA16EUCFIXED , JA16DBCSFIXED , ZHT32TRISFIXED

KO16KSC5601FIXED , KO16DBCSFIXED , US16TSTFIXED , ZHS16CGB231280FIXED

ZHS16GBKFIXED , ZHS16DBCSFIXED , ZHT16DBCSFIXED , ZHT16BIG5FIXED

ZHT32EUCFIXED

then also simply go to point next step.

Otherwise --:

* change the tables to use CHAR, VARCHAR2 or CLOB instead the N-type

or

* use export/import the table(s) containing N-type column and truncate those tables before migrating to 10g

The recommended NLS_LANG during export is simply the NLS_CHARACTERSET, not the NLS_NCHAR_CHARACTERSET

========= ========= ============== NLS Conversion for 8i ====== ====== ====== = = = = = === ===========

#########################################################################################

Step 6 : Stats Gathering

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, but statistics gathering only occurs for those tables that lack statistics or are significantly changed during the upgrade.

( A) --> First take the backup of existing stats ....

$ sqlplus '/as sysdba'

SQL>spool sdict

SQL>grant analyze any to sys;

SQL>exec dbms_stats.create_stat_table('SYS','dictstattab'); --- It'll create a new stat table

SQL>exec dbms_stats.export_schema_stats('WMSYS','dictstattab',statown => 'SYS');

SQL>exec dbms_stats.export_schema_stats('MDSYS','dictstattab',statown => 'SYS');

SQL>exec dbms_stats.export_schema_stats('CTXSYS','dictstattab',statown => 'SYS');

SQL>exec dbms_stats.export_schema_stats('XDB','dictstattab',statown => 'SYS');

SQL>exec dbms_stats.export_schema_stats('WKSYS','dictstattab',statown => 'SYS');

SQL>exec dbms_stats.export_schema_stats('LBACSYS','dictstattab',statown => 'SYS');

SQL>exec dbms_stats.export_schema_stats('OLAPSYS','dictstattab',statown => 'SYS');

SQL>exec dbms_stats.export_schema_stats('DMSYS','dictstattab',statown => 'SYS');

SQL>exec dbms_stats.export_schema_stats('ODM','dictstattab',statown => 'SYS');

SQL>exec dbms_stats.export_schema_stats('ORDSYS','dictstattab',statown => 'SYS');

SQL>exec dbms_stats.export_schema_stats('ORDPLUGINS','dictstattab',statown => 'SYS');

SQL>exec dbms_stats.export_schema_stats('SI_INFORMTN_SCHEMA','dictstattab',statown => 'SYS');

SQL>exec dbms_stats.export_schema_stats('OUTLN','dictstattab',statown => 'SYS');

SQL>exec dbms_stats.export_schema_stats('DBSNMP','dictstattab',statown => 'SYS');

SQL>exec dbms_stats.export_schema_stats('SYSTEM','dictstattab',statown => 'SYS');

SQL>exec dbms_stats.export_schema_stats('SYS','dictstattab',statown => 'SYS');

SQL>spool off

OR .., Run the sql "stats_exp_backup.sql"

$ sqlplus " /as sysdba"

SQL>@stats_exp_backup.sql

( B )

---> Now , Gather the new statistics . This will also reduce the downtime

$ sqlplus '/as sysdba'

SQL>spool gdict_stats.txt

SQL>grant analyze any to sys;

SQL>exec dbms_stats.gather_schema_stats('WMSYS',options=>'GATHER',estimate_percent => DBMS_STATS.AUTO_SAMPLE_SIZE,method_opt => 'FOR ALL COLUMNS SIZE AUTO', cascade => TRUE);

SQL>exec dbms_stats.gather_schema_stats('MDSYS',options=>'GATHER',estimate_percent => DBMS_STATS.AUTO_SAMPLE_SIZE, method_opt => 'FOR ALL COLUMNS SIZE AUTO', cascade => TRUE);

SQL>exec dbms_stats.gather_schema_stats('CTXSYS',options=>'GATHER',estimate_percent => DBMS_STATS.AUTO_SAMPLE_SIZE, method_opt => 'FOR ALL COLUMNS SIZE AUTO', cascade => TRUE);

SQL>exec dbms_stats.gather_schema_stats('XDB',options=>'GATHER',estimate_percent => DBMS_STATS.AUTO_SAMPLE_SIZE, method_opt => 'FOR ALL COLUMNS SIZE AUTO', cascade => TRUE);

SQL>exec dbms_stats.gather_schema_stats('WKSYS',options=>'GATHER',estimate_percent => DBMS_STATS.AUTO_SAMPLE_SIZE,method_opt => 'FOR ALL COLUMNS SIZE AUTO', cascade => TRUE);

SQL>exec dbms_stats.gather_schema_stats('LBACSYS',options=>'GATHER',estimate_percent => DBMS_STATS.AUTO_SAMPLE_SIZE,method_opt => 'FOR ALL COLUMNS SIZE AUTO', cascade => TRUE);

SQL>exec dbms_stats.gather_schema_stats('OLAPSYS',options=>'GATHER',estimate_percent => DBMS_STATS.AUTO_SAMPLE_SIZE,method_opt => 'FOR ALL COLUMNS SIZE AUTO', cascade => TRUE);

SQL>exec dbms_stats.gather_schema_stats('DMSYS',options=>'GATHER',estimate_percent => DBMS_STATS.AUTO_SAMPLE_SIZE,method_opt => 'FOR ALL COLUMNS SIZE AUTO', cascade => TRUE);

SQL>exec dbms_stats.gather_schema_stats('ODM',options=>'GATHER',estimate_percent => DBMS_STATS.AUTO_SAMPLE_SIZE,method_opt => 'FOR ALL COLUMNS SIZE AUTO', cascade => TRUE);

SQL>exec dbms_stats.gather_schema_stats('ORDSYS',options=>'GATHER',estimate_percent => DBMS_STATS.AUTO_SAMPLE_SIZE,method_opt => 'FOR ALL COLUMNS SIZE AUTO', cascade => TRUE);

SQL>exec dbms_stats.gather_schema_stats('ORDPLUGINS',options=>'GATHER',estimate_percent => DBMS_STATS.AUTO_SAMPLE_SIZE,method_opt => 'FOR ALL COLUMNS SIZE AUTO', cascade => TRUE);

SQL>exec dbms_stats.gather_schema_stats('SI_INFORMTN_SCHEMA',options=>'GATHER',estimate_percent => DBMS_STATS.AUTO_SAMPLE_SIZE,method_opt => 'FOR ALL COLUMNS SIZE AUTO', cascade => TRUE);

SQL>exec dbms_stats.gather_schema_stats('OUTLN',options=>'GATHER',estimate_percent => DBMS_STATS.AUTO_SAMPLE_SIZE,method_opt => 'FOR ALL COLUMNS SIZE AUTO', cascade => TRUE);

SQL>exec dbms_stats.gather_schema_stats('DBSNMP',options=>'GATHER',estimate_percent => DBMS_STATS.AUTO_SAMPLE_SIZE,method_opt => 'FOR ALL COLUMNS SIZE AUTO', cascade => TRUE);

SQL>exec dbms_stats.gather_schema_stats('SYSTEM',options=>'GATHER',estimate_percent => DBMS_STATS.AUTO_SAMPLE_SIZE,method_opt => 'FOR ALL COLUMNS SIZE AUTO', cascade => TRUE);

SQL>exec dbms_stats.gather_schema_stats('SYS',options=>'GATHER',estimate_percent => DBMS_STATS.AUTO_SAMPLE_SIZE,method_opt => 'FOR ALL COLUMNS SIZE AUTO', cascade => TRUE);

SQL>spool off

OR , Use the sql "gather_stats_9i.sql"

$ sqlplus " /as sysdba"

SQL>@gather_stats_9i.sql

#########################################################################################

Step 7 : Invalid Count and compile the invalid

Take a list of all the invalids object before upgrading

spool invalid_pre.lst

select substr(owner,1,12) owner,substr(object_name,1,30) object,substr(object_type,1,30) type, status from dba_objects where status='INVALID';

spool off

After taking the list of invalids object , Now compile the invalids ..

% sqlplus '/as sysdba'

SQL> @?/rdbms/admin/utlrp.sql

OR

$ sqlplus " /as sysdba"

SQL> exec utl_recomp,parallel(4); -- Something like this . Refer to shift log regarding this

#########################################################################################==

Step 8 : Dictionary Conrruption check

Check for corruption in the dictionary, use the following commands in sqlplus connected as sys:

Set verify off

Set space 0

Set line 120

Set heading off

Set feedback off

Set pages 1000

Spool analyze.sql

Select 'Analyze cluster "'||cluster_name||'" validate structure cascade;' from dba_clusters where owner='SYS'

union

Select 'Analyze table "'||table_name||'" validate structure cascade;' from dba_tables where owner='SYS' and partitioned='NO' and (iot_type='IOT' or iot_type is NULL)

union

Select 'Analyze table "'||table_name||'" validate structure cascade into invalid_rows;' from dba_tables where owner='SYS' and partitioned='YES';

spool off

This creates a script called analyze.sql.

Now execute the following steps.

$ sqlplus '/as sysdba'

SQL> @$ORACLE_HOME/rdbms/admin/utlvalid.sql

SQL> @analyze.sql

This script (analyze.sql) should not return any errors.

#########################################################################################==

Step 9 : Ensure that all Snapshot refreshes are successfully completed, and that replication is stopped.

$ sqlplus '/ as sysdba'

SQL> select distinct(trunc(last_refresh)) from dba_snapshot_refresh_times;

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

SQL> select distinct(trunc(last_refresh)) from dba_snapshot_refresh_times;

(TRUNC(LA

---------

13-MAY-02

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

#########################################################################################==

Step 10 : Stop the listener and confirm that , there should be no file in recovery/backup mode

Stop the listener for the database:

$ lsnrctl

LSNRCTL> stop

Ensure no files need media recovery:

$ sqlplus '/ as sysdba'

SQL> select * from v$recover_file; ---- Ensure no files are in revovery mode:

This should return no rows.

SQL> select * from v$backup where status!='NOT ACTIVE'; --Ensure no files are in backup mode:

This should return no row

#########################################################################################==

Step 11 :Check for Distributed transaction : Resolve any outstanding unresolved distributed transaction

SQL> select * from dba_2pc_pending;

If this returns rows you should do the following:

SQL> select local_tran_id from dba_2pc_pending;

SQL> execute dbms_transaction.purge_lost_db_entry('');

SQL> commit;

====>> Now disable , all the batch and cron jobs.

Crontab –l > cronbackup

Now , Hash all the program in the crontab

#########################################################################################

Step 12: sys , system and table "aud$" should be there in system tablespace : Otherwise change the default tablespace to system ..

SQL> select username, default_tablespace from dba_users where username in ('SYS','SYSTEM');

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

SQL> select tablespace_name from dba_tables where table_name='AUD$';

#########################################################################################

Step 13:

===> Drop table XDB.MIGR9202STATUS if it exist in the databse

#########################################################################################

Step 14: Note down where all control files are located.

SQL> select * from v$controlfile;

#########################################################################################

Step 15: NOW SHUTDOWN the database and take a full cold backup including ORACLE_HOME ( for security purpose ) OR take the RMAN online full backup

[ No need to take the backup , As already taken earlier .. ]

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

Issue the following RMAN commands:

RUN

{

ALLOCATE CHANNEL chan_name TYPE DISK;

BACKUP DATABASE FORMAT 'some_backup_directory%U' TAG before_upgrade;

BACKUP CURRENT CONTROLFILE TO 'save_controlfile_location';

}

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

$ sqlplus '/as sysdba'

SQL> shutdown immediate;

======================================================================================

==================== PHASE 2 ================================

=============== Upgrading to the New Oracle Database 10g Release 2 =================

======================================================================================

#########################################################################################

Step 16: Copy the init file to the new ORACLE_HOME

=====> Changes in INIT.ORA file

* Comment out obsoleted parameters like hash_join_enabled , ( Please chk it againg also )

* Change all deprecated parameters like “log_archive_start”

* Change the value of "COMPATIBLE" parameter :

If upgradaing from 8i , Then set the value to 9.2.0

If upgrading from 9.2.0 or 10.1.0 then leave the COMPATIBLE parameter set to it's current

value until the upgrade has been completed successfully .This will avoid any unnecessary

ORA-942 errors from being reported in SMON trace files during the upgrade (because the upgrade is looking for 10.2 objects that have not yet been created)

Note : In out case there is no need to change the value of COMPATIBLE parameter

* If you have the parameter NLS_LENGTH_SEMANTICS currently set to CHAR, change the value

to BYTE during the upgrade .

Note : In our case there is no need to change the value as this bug has been resolved in 10.2.0.3

* Verify that the parameter DB_DOMAIN is set properly.

* PGA_AGGREGATE_TARGET >= 24MB

* SHARED_POOL_SIZE and the LARGE_POOL_SIZE should be greater than 150MB

* JAVA_POOL_SIZE should be greater than 150MB

* Ensure there is a value for DB_BLOCK_SIZE

* Change the value of BDUMP ,CDUMP ,UDUMP

* Comment AQ_TM_PROCESSES and JOB_QUEUE_PROCESSES parameter

Now add ..,

AQ_TM_PROCESSES=0

JOB_QUEUE_PROCESSES=0 ( This is required for upgrade purpose , That's why hashed above )

* Set the parameter UNDO_MANAGEMENT=AUTO

#########################################################################################

Step 17: Ensure the NLS_LANG variable is set correctly:

$ env | grep $NLS_LANG

#########################################################################################

Step 18: Copy the Network Configuration/init.ora/password file from old ORACLE_HOME to new ORACLE_HOME

$ cp $OLD_ORACLE_HOME/network/admin/*.ora $NEW_ORACLE_HOME/network/admin

$ cp $OLD_ORACLE_HOME/dbs/init.ora $NEW_ORACLE_HOME/dbs/

$ cp $OLD_ORACLE_HOME/dbs/orapw $NEW_ORACLE_HOME/dbs/

#########################################################################################

Step 18: Update the enviorement setting according to new 10g enviorment

---> Update the oratab entry, to set the new ORACLE_HOME and disable automatic startup:

SID:ORACLE_HOME:N

---> Update the environment variables like ORACLE_HOME and PATH

$. oraenv

---> Make sure the following environment variables point to the new release (10g) directories:

- ORACLE_HOME

- PATH

- ORA_NLS10

- ORACLE_BASE

- LD_LIBRARY_PATH

- LIBPATH (AIX only)

- ORACLE_PATH

$ env | grep ORACLE_HOME

$ env | grep PATH

$ env | grep ORA_NLS10

$ env | grep ORACLE_BASE

$ env | grep LD_LIBRARY_PATH

$ env | grep ORACLE_PATH

$ env | grep LIBPATH

---> Unset ORA_NLS33 and set ORA_NLS10 to point to $ORACLE_HOME/nls/data

#########################################################################################

Step 19: Datafile movement need to be done

As in our case, we have created the different mount points for the database also. Thus file movement

is also required .

You can skip this step if you’re not changing the location of your database

Step 1 : Copy all the file to the desire location .

Step 2 : Then use the " alter database rename file '.....' to '...''; ( taking database in mount mode )

#########################################################################################

Step 20: Now , start the upgradation of database

$ cd $ORACLE_HOME/rdbms/admin

$ sqlplus / as sysdba

Use Startup with the UPGRADE option:

SQL> startup upgrade

After this create the SYSAUX tablespace

SQL > CREATE TABLESPACE sysaux DATAFILE '/sp04/equs10g/oradata/sysaux01.dbf'

SIZE 1000M REUSE

EXTENT MANAGEMENT LOCAL

SEGMENT SPACE MANAGEMENT AUTO

ONLINE;

Now , run the "catupgrd.sql" script and also spooled the output ..

SQL> spool upgrade.log

SQL> @catupgrd.sql

SQL> SPOOL OFF

Note : This script determines which upgrade scripts need to be run and then runs each necessary script. The upgrade script creates and alters certain data dictionary tables . Check the spool file and verify .If there is any error , then you can run the errored out scripts again

Now , Run the "@utlu102s.sql" to check the upgrade status tool

SQL> @utlu102s.sql TEXT

This is the Post-upgrade Status Tool displays the status of the database components in the upgraded database. The Upgrade Status Tool displays output similar to the following:

#########################################################################################

Step 21: Restart the database and do the following

SQL> shutdown immediate (DO NOT use "shutdown abort" !!!)

SQL> startup restrict

Run olstrig.sql to re-create DML triggers

SQL> @olstrig.sql

Above script will give error. IF [Oracle Label Security VALID 10.2.0.1.0 00:00:14 ] is

not installed . Thus you can skip this script

Run utlrp.sql and compiled all the invalids object

SQL> @utlrp.sql

Take the list of invalid now and compare it with old list

spool invalid_post_new.lst

Select substr(owner,1,12) owner,substr(object_name,1,30) object,substr(object_type,1,30) type, status from dba_objects where status <>'VALID';

spool off

NOTE: If you have upgraded from version 9.2 to version 10.2 and find that the following views are invalid , the views can be safely ignored (or dropped):

SYS.V_$KQRPD,SYS.V_$KQRSD,SYS.GV_$KQRPD,SYS.GV_$KQRSD

Again restat the database :-

% sqlplus '/as sysdba'

SQL> shutdown

SQL> startup

#########################################################################################

Step 22: If you changed the value for NLS_LENGTH_SEMANTICS from "CHAR" to "BYTE" prior to the upgrade set it back to "CHAR"

Now revert the init parameters

AQ_TM_PROCESSES , JOB_QUEUE_PROCESSES to their old value

Now , start the database and create spfile ..

SQL> startup

SQL> create spfile from pfile;

This will create a spfile as a copy of the init.ora file located in the $ORACLE_HOME/dbs directory.

Now ,modify the listener.ora according to the new ORACLE_HOME stttings and then ..

$ lsnrctl

LSNRCTL> start

#########################################################################################

Step 23: Enable cron and batch jobs

crontab < cronbackup

#########################################################################################

Step 24: For more better plan new stats can be gathered , using dbms_stats() ;

#########################################################################################

Step 25 : If your database contains the rman catalog , Then upgrade that also.

% rman CATALOG rcat/rcat@oemdb

connected to recovery catalog database 

PL/SQL package rcat.DBMS_RCVCAT version 08.00.04 in RCVCAT database is too old

RMAN> UPGRADE CATALOG

recovery catalog owner is rcat 

enter UPGRADE CATALOG command again to confirm catalog upgrade 

RMAN> UPGRADE CATALOG

recovery catalog upgraded to version 10.01.00

DBMS_RCVMAN package upgraded to version 10.01.00

DBMS_RCVCAT package upgraded to version 10.01.00

==========================================================================================

=============== Suggestion on 10g upgrade==========================

If you are applying any CPUs ( Critical Patch Upgrades ) .. then you will have to re-run the catcpu.sql for that particular CPU after upgrading to 10g

Also make sure there are no invalid objects after the 10g upgrade and also after applying the CPU