=>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
