In Oracle : Oracle Database Pre-upgrade Checks (from 9.2.0.6 to 10.2.0.3)

Oracle Applications EBS 11i, R12      Oracle Database      Data Warehouse & BI      Oracle Middleware      PL/SQL      Linux ...

Oracle Applications EBS

Oracle Apps 11i DBA (I) Oracle Apps 11i DBA (II) Oracle Apps 11i DBA (III) Oracle Apps 11i Sys Admin UPGRADE 11i Apps to R12 Oracle Apps R12 DBA

The last articles in the site (HOT)

The most visited articles in the site

In-Oracle.com  -> Oracle Applications (apps)  -> R12

-> Oracle Database Pre-upgrade Checks (from 9.2.0.6 to 10.2.0.3)

 

 Oracle Database Pre-upgrade Checks

 

 

NOTES: 

  • For more details please read the Metalink Note 316889.1: Complete Checklist for Manual Upgrades to 10gR2

  • This step prepare the database for the upgrade

1. From the new ORACLE_HOME/rdbams/admin copy the utlu102i.sql to a directory and run it as sys

 

SQL> @utlu102i.sql;
Oracle Database 10.2 Upgrade Information Utility 05-04-2008 10:01:59 

********************************************************************** 
Database: 
********************************************************************** 
--> name: VIS 
--> version: 9.2.0.6.0 
--> compatible: 9.2.0 
--> blocksize: 8192 

********************************************************************** 
Logfiles: [make adjustments in the current environment] 
********************************************************************** 
--> The existing log files are adequate. No changes are required. 

 


********************************************************************** 
Tablespaces: [make adjustments in the current environment] 
********************************************************************** 
--> SYSTEM tablespace is adequate for the upgrade. 
.... minimum required size: 8053 MB 
--> APPS_UNDOTS1 tablespace is adequate for the upgrade. 
.... minimum required size: 111 MB 
--> TEMP tablespace is adequate for the upgrade. 
.... minimum required size: 58 MB 
--> APPS_TS_QUEUES tablespace is adequate for the upgrade. 
.... minimum required size: 657 MB 
--> APPS_TS_TX_DATA tablespace is adequate for the upgrade. 
.... minimum required size: 10868 MB 
--> ODM tablespace is adequate for the upgrade. 
.... minimum required size: 15 MB 
--> OLAP tablespace is adequate for the upgrade. 
.... minimum required size: 32 MB 


********************************************************************** 
Update Parameters: [Update Oracle Database 10.2 init.ora or spfile] 
********************************************************************** 
WARNING: --> "streams_pool_size" is not currently defined and needs a value of 
at least 50331648 
WARNING: --> "large_pool_size" needs to be increased to at least 8388608 
WARNING: --> "session_max_open_files" needs to be increased to at least 20 


********************************************************************** 
Renamed Parameters: [Update Oracle Database 10.2 init.ora or spfile] 
********************************************************************** 
-- No renamed parameters found. No changes are required. 

********************************************************************** 
Obsolete/Deprecated Parameters: [Update Oracle Database 10.2 init.ora or spfile]
********************************************************************** 
--> "optimizer_max_permutations" 
--> "row_locking" 
--> "undo_suppress_errors" 
--> "max_enabled_roles" 
--> "enqueue_resources" 
--> "sql_trace"
 


********************************************************************** 
Components: [The following database components will be upgraded or installed] 
********************************************************************** 
--> Oracle Catalog Views [upgrade] VALID 
--> Oracle Packages and Types [upgrade] VALID 
--> JServer JAVA Virtual Machine [upgrade] VALID 
...The 'JServer JAVA Virtual Machine' JAccelerator (NCOMP) 
...is required to be installed from the 10g Companion CD. 

--> Oracle XDK for Java [upgrade] VALID 
--> Oracle Java Packages [upgrade] VALID 
--> Oracle Text [upgrade] VALID 
--> Oracle XML Database [install] 
--> Real Application Clusters [upgrade] INVALID 
--> Oracle Data Mining [upgrade] VALID 
--> OLAP Analytic Workspace [upgrade] UPGRADED 
--> OLAP Catalog [upgrade] VALID 
--> Oracle OLAP API [upgrade] UPGRADED 
--> Oracle interMedia [upgrade] VALID 
...The 'Oracle interMedia Image Accelerator' is 
...required to be installed from the 10g Companion CD. 

--> Spatial [upgrade] VALID 

********************************************************************** 
Miscellaneous Warnings 
********************************************************************** 
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 
.... ODM 
.... OLAPSYS 
.... MDSYS 
WARNING: --> Database contains INVALID objects prior to upgrade. 
.... USER APPS has 1 INVALID objects. 


********************************************************************** 
SYSAUX Tablespace: 
[Create tablespace in the Oracle Database 10.2 environment] 
********************************************************************** 
--> New "SYSAUX" tablespace 
.... minimum required size for database upgrade: 500 MB 


PL/SQL procedure successfully completed.

SQL> spool off

  

Before starting the upgrade all required pre-upgrade tasks must be accomplished. I marked in blue & bold the items we have to pay attention in this particular case. For more details please read the Metalink Note 316889.1

 

KNOWN ISSUES:

 

1)   STREAMS_POOL_SIZE, was introduced in release 10gR1. While adding the parameter, Oracle gives streams_pool_size as invalid parameter during the upgrade. This message may be ignored for database version 9iR2 or less

2)   One of the customer has reported an error on keeping the shared_pool_size at 150 MB. Please set the shared_pool_size at 200M.

3)    While upgrade following error was encountered.
create or replace
*
ERROR at line 1:
ORA-06553: PLS-213: package STANDARD not accessible.
ORA-00955: name is already used by an existing object

Please make sure to set the following init parameters as below in the spfile/init file or comment them out to their default values, at the time of upgrading the database.

PLSQL_V2_COMPATIBILITY = FALSE
PLSQL_CODE_TYPE = INTERPRETED # Only applicable to 10gR1
PLSQL_NATIVE_LIBRARY_DIR = ""
PLSQL_NATIVE_LIBRARY_SUBDIR_COUNT = 0

 

 

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(*) 
---------- 

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;

 

 

In-Oracle.com  -> Oracle Applications (apps)  -> R12

-> Oracle Database Pre-upgrade Checks (from 9.2.0.6 to 10.2.0.3)

Oracle Applications EBS 11i, R12      Oracle Database      Data Warehouse & BI      Oracle Middleware      PL/SQL      Linux ...

 

     Copyright (c) 2011-2015  www.in-oracle.com  |  Disclaimer: The views expressed on this web site are my own and do not reflect the views of Oracle Corporation. You may use the information from this site only at your risk.