|
NOTES:
1.
From the new ORACLE_HOME/rdbams/admin copy the utlu102i.sql
to a directory and run it as sys
More information
about this subject (
Pre-Upgrade checks/ tasks for the upgrade from 9.2.0.6 to 10.2.0.3 ) you can get from
www.in-oracle.com
2.
Check if pfile or spfile is used
SELECT decode(value, NULL, 'PFILE', 'SPFILE') "Init File Type"
FROM sys.v$parameter
WHERE name ='spfile';
If
a pfile is used the initSID.ora must be modified with the new parameter
and the Obsolete/Deprecated Parameters must be removed.
If
a spfile is used I suggest:
-
modifying the spfile with "alter system" command (ex: alter
system set SGA_MAX_SIZE=300M scope=file; )
-
create the pfile with create pfile from spfile;
-
renaming the spfile and restart the database to use the pfile during the
upgrade (after the upgrade a spfile could be created and the database
could be restarted in order to use the spfile).
3.
Run the $ORACLE_HOME/rdbms/admin/utltzuv2.sql (as sys)
to see if there is the new timezone will impact the upgrade.
Please run this script from the patch# 5746835.
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> select count(*) from sys.sys_tzuv2_temptab;
COUNT(*)
----------
0
SQL> spool off
So,
nothing is to do for the upgrade related to the new timezone.
4.
To identify which users and roles in your database are granted the
CONNECT role, use the following query:
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');
These
users must receive the following privileges after the upgrade:
CREATE VIEW
CREATE TABLE
ALTER SESSION
CREATE CLUSTER
CREATE SESSION
CREATE SYNONYM
CREATE SEQUENCE
CREATE DATABASE LINK
5.
Downgrade the database and the DBlinks
During
the upgrade to 10gR2, any passwords in database links will be encrypted.
To downgrade back to the original release, all of the database links
with encrypted passwords must be dropped prior to the downgrade.
Consequently, the database links will not exist in the downgraded
database.
To
recreate the DBlinks, please run the following select in order to create
the DBlink creation:
SELECT
'create '||DECODE(U.NAME,'PUBLIC','public ')||'database link '||CHR(10)
||DECODE(U.NAME,'PUBLIC',Null, U.NAME||'.')|| L.NAME||chr(10)
||'connect to ' || L.USERID || ' identified by '''
||L.PASSWORD||''' using ''' || L.host || ''''
||chr(10)||';' TEXT
FROM sys.link$ L,sys.user$ U
WHERE L.OWNER# = U.USER# ;
6.
Backup the existing statistics as follows
SQL>
exec DBMS_STATS.GATHER_SCHEMA_STATS;
$ sqlplus '/as sysdba'
SQL>spool sdict
SQL>grant analyze any to sys;
SQL>exec dbms_stats.create_stat_table('SYS','dictstattab');
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
7.
Gather new statistics
$ sqlplus '/as sysdba'
SQL>spool gdict
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
8.
Check for invalid objects in the database
sqlplus
'/as sysdba'
SQL> @?/rdbms/admin/utlrp.sql
SQL>
spool invalid_pre.lst
SQL> 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';
SQL> spool off
9.
Check for corruption in the dictionary
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
sqlplus
'/as sysdba'
SQL> @$ORACLE_HOME/rdbms/admin/utlvalid.sql
SQL> @analyze.sql
This script (analyze.sql) should not return any errors.
10.
Note down where all control files are located
select * from v$controlfile;
11.
Check for XDB.MIGR9202STATUS table
If
the table exists drop it before the upgrade:
DROP
TABLE XDB.MIGR9202STATUS;
|