Purpose of this document is to show a complete 9i to 10g upgrade of an Oracle database on
AIX 5L using DBUA ( Database Uprgrade Assistant)
3.2 TECHNICAL SOLUTION
10g upgrade from 9.2.06 to 10.2.0.3 for any database -:
Check the init.ora parameter file in the 9I ORACLE_HOME/dbs
Make sure there is no “If event=”30084 trace name context forever, level1”
defined in pfile of spfile.
LOGIN TO NEW PSERIES MACHINE AS oraprod user.
Important : Issue unix command unset TNS_ADMIN
export ORACLE_HOME=/u01/finprod/finproddb/10.2/db_1
export PATH=$ORACLE_HOME/bin:$ORACLE_HOME/perl/bin:/usr/sbin:/usr/bin/
export PERL5LIB=$ORACLE_HOME/perl/lib/5.8.3:$ORACLE_HOME/perl/lib/site_pearl/5.8.3
export LIBPATH=$ORACLE_HOME/lib:/usr/dt/lib:/usr/openwin/lib:$ORACLE_HOME/lib32:$ORACLE_HOME/ctx/lib
export LD_LIBRARY_PATH=$ORACLE_HOME/lib:/usr/dt/lib:/usr/openwin/lib:$ORACLE_HOME/ctx/lib
export ORA_NLS10=/u01/finprod/finproddb/10.2/db_1/nls/data/9idata
unset TNS_ADMIN
To make sure run the following to ascertain correct paths below.
echo $ORACLE_HOME
echo $PATH
echo $PERL5LIB
echo $LIBPATH
echo $LD_LIBRARY_PATH
echo $NLS_010
Firstly create the /NLS/DATA/9i data directory.
# DESCRIPTION
The script copies every nlb files under $ORACLE_HOME/nls/data and
for customer needs to revert back to 9i locale behavior.
Make sure perl utility being used points to the 10g oracle environment.
Make sure there is no reference to the old 9i environment in the $PATH or this command will
fail as the old version of perl is picked up.
export PATH=$ORACLE_HOME/bin:$ORACLE_HOME/perl/bin:/usr/sbin:/usr/bin/
$ which perl
/u01/finprod/finproddb/10.2/db_1/perl/bin/perl
$
oraprod(DATABASE)@nbrbwdev5:perl $ORACLE_HOME/nls/data/old/cr9idata.pl
Creating directory /u01/finprod/finproddb/10.2/db_1/nls/data/9idata …
Copying files to /u01/finprod/finproddb/10.2/db_1/nls/data/9idata…
Copy finished.
Please reset environment variable ORA_NLS10 to /u01/finprod/finproddb/10.2/db_1/nls/data/9idata
export ORA_NLS10=/u01/finprod/finproddb/10.2/db_1/nls/data/9idata
oraprod(DATABASE)@nbrbwdev5:echo $ORA_NLS10
/u01/finprod/finproddb/10.2/db_1/nls/data/9idata
Now configure listener for 10g home
First unset TNS_ADMIN so that the old 9i environment is unset.
unset TNS_ADMIN
oraprod(DATABASE)@nbrbwdev5:cd $ORACLE_HOME/bin (10g ORACLE_HOME)
oraprod(DATABASE)@nbrbwdev5:export DISPLAY=10.254.101.25:0.0
oraprod(DATABASE)@nbrbwdev5:ls netca
netca
oraprod(DATABASE)@nbrbwdev5:./netca
Oracle Net Services Configuration:
$ORACLE_HOME/nls/data/old to directory $ORACLE_HOME/nls/data/9idata
Use same listener name as in old machine….FINPROD was used here and not LISTENER as above.
Message on screen :
Oracle Net Services Configuration:
Configuring Listener:FINPROD
Default local naming configuration complete.
Listener configuration complete.
Oracle Net Listener Startup:
Running Listener Control:
/u01/finprod/finproddb/10.2/db_1/bin/lsnrctl start FINPROD
Listener Control complete.
Listener started successfully.
Oracle Net Services configuration successful. The exit code is 0
SHUTDOWN LISTENER
lsnrctl stop FINPROD ( Database name )
Now start the PRE-UPGRADE STEPS:
Important : START THE DATABASE in the 9i environment and not from the new 10g environment.
If you haven’t changed your .profile and are still using the 9i .profile , just log into a new session so that the old 9i environment is picked up.
oraprod(DATABASE)@nbrbwdev5:sqlplus “sys as sysdba”
SQL*Plus: Release 9.2.0.6.0 – Production on Thu Feb 21 11:57:55 2008
Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.
Enter password:
Connected to an idle instance.
SQL> startup;
ORACLE instance started.
Total System Global Area 2149552280 bytes
Fixed Size 745624 bytes
Variable Size 1879048192 bytes
Database Buffers 268435456 bytes
Redo Buffers 1323008 bytes
Database mounted.
Database opened.
SQL>
STEPS FOR UPGRADING THE DATABASE TO 10G RELEASE 2
=================================================
Preparing to Upgrade
——————–
In this section all the steps need to be performed to the previous version of Oracle.
Please note that the database must be running in normal mode in the old release- i.e 9i environment.
PRE UPGRADE DATABASE HEALTH CHECK:
Log in to the system as the owner of the new 10gR2 ORACLE_HOME and 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:
cp $ORACLE_HOME/rdbms/admin/utlu102i.sql /tmp—have to be in the 10g environment to do this.
Make a note of the new location of these files.
Change to the temporary directory that you copied files to in Step 1.
cd /tmp
Start SQL*Plus and connect to the database instance as a user with SYSDBA privileges.
Then run and spool the utlu102i.sql file.
sqlplus ‘/as sysdba’ —In 9i environment.
SQL> spool Database_Info.log
SQL> @utlu102i.sql
SQL> spool off
Then, check the spool file and examine the output of the upgrade information tool. The sections which follow, describe the output of the Upgrade Information Tool (utlu102i.sql).
Database:
This section displays global database information about the current database such
as the database name, release number, and compatibility level. A warning is displayed
if the COMPATIBLE initialization parameter needs to be adjusted before the database is
upgraded.
Logfiles:
This section displays a list of redo log files in the current database whose size is less than 4 MB. For each log file, the file name, group number, and recommended size is displayed.
New files of at least 4 MB (preferably 10 MB) need to be created in the current database. Any redo log files less than 4 MB must be dropped before the database is upgraded.
Tablespaces:
This section displays a list of tablespaces in the current database. For each tablespace,the tablespace name and minimum required size is displayed. In addition, a message is displayed if the tablespace is adequate for the upgrade.
If the tablespace does not have enough free space, then space must be added to the tablespace in the current database.
Tablespace adjustments need to be made before the database is upgraded.
Update Parameters:
This section displays a list of initialization parameters in the parameter file of the current database that must be adjusted before the database is upgraded. The adjustments need to be made to the parameter file after it is copied to the new Oracle Database 10g
release.
Deprecated Parameters:
This section displays a list of initialization parameters in the parameter file of the current database that are deprecated in the new Oracle Database 10g release.
Obsolete Parameters:
This section displays a list of initialization parameters in the parameter file of the
current database that are obsolete in the new Oracle Database 10g release. Obsolete
initialization parameters need to be removed from the parameter file before the database is upgraded.
Components:
This section displays a list of database components in the new Oracle Database 10g release that will be upgraded or installed when the current database is upgraded.
Miscellaneous Warnings:
This section provides warnings about specific situations that may require attention before
and/or after the upgrade.
SYSAUX Tablespace:
This section displays the minimum required size for the SYSAUX tablespace, which is required in Oracle Database 10g.
The SYSAUX tablespace must be created after the new Oracle Database 10g release is started and BEFORE the upgrade scripts are invoked.
However since we are using DBUA to upgrade, the assistant will prompt you for the creation of the SYSAUX tablespace so there is no need to create it manually.
2. CHECK FOR CONNECT ROLE PRIVILEGE CHANGES
The CONNECT role in 10g Release 2 has only create session privilege.
Therefore create a new role with the usual privileges which CONNECT role had in 9i and grant it to the users who are not generic database users.
CREATE ROLE CONNECT1;
Grant all privs usually associated with the CONNECT role in previous versions.
grant create view to connect1;
grant create table to connect1;
grant create cluster to connect1;
grant create session to connect1;
grant create synonym to connect1;
grant create sequence to connect1;
grant create database link to connect1;
grant alter session to connect1;
spool grant_connect1.sql
Select ‘grant connect1 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’);
spool off
Run @grant_connect1.sql and make sure any bb staff I’d are in quotes.
Ex grant connect1 to “bb16872.1”;
Scripts for the above already in place :
Just run create_connect1.sql and grant_connect1.sql from /u02/oradata/software
3. GATHER SCRIPT TO RECREATE DATABASE LINKS IN CASE OF DOWNGRADE:
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.
If you anticipate a requirement to be able to downgrade back to your original release, then save the information about affected database links from the SYS.LINK$ table, so that you can recreate the database links after the downgrade.
Following script can be used to construct the dblink.
Spool db_links.txt
Set head off
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# ;
Spool off
Spool file above already in place in /u02/oradata/software
4. GATHER STATISTICS BEFORE UPGRADE:
To run this script, connect to the database AS SYSDBA using SQL*Plus.
spool gdict
grant analyze any to sys;
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);
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);
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);
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);
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);
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);
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);
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);
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);
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);
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);
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);
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);
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);
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);
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);
spool off
The above steps take 1.5 hours max.
Run /u02/oradata/software/analyze.sql
Get a count of all INVALID OBJECTS PRIOR TO UPGRADE:
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 <>’VALID’;
select count(*),object_type,owner from dba_objects where status <>’VALID’
group by object_type, owner;
spool off;
LOGOFF FROM MACHINE.
LOGIN AGAIN AND INVOKE 10g ENVIRONMENT
UPGRADE STEPS
Make sure environment in 10g and all env variables defined –
export ORACLE_HOME=/u01/finprod/finproddb/10.2/db_1
$export PERL5LIB=/u01/finprod/finproddb/10.2/db_1/perl/lib/5.8.3:/u01/finprod/finproddb/10.2/db_1/perl/lib/site_pearl/5.8.3
$export LD_LIBRARY_PATH=/u01/finprod/finproddb/10.2/db_1/lib:/usr/dt/lib:/usr/openwin/lib:/u01/finprod/finproddb/10.2/db_1/ctx/lib
$export LIBPATH=/u01/finprod/finproddb/10.2/db_1/lib:/usr/dt/lib:/usr/openwin/lib:/u01/finprod/finproddb/10.2/db_1/lib32:/u01/finprod/finproddb/10.2/db_1/ctx/lib
$export PATH=/u01/finprod/finproddb/10.2/db_1/Apache/perl/bin:/u01/finprod/finproddb/10.2/db_1/Apache/perl/bin:/usr/bin:/u01/finprod/finprodora/iAS/Apache/perl/bin:/u01/finprod/finproddb/10.2/db_1/bin:/usr/ccs/bin:/usr/sbin:/bin:/u01/finprod/finproddb/10.2/db_1/Apache/perl/bin:/usr/bin:/usr/bin:/u01/finprod/finproddb/10.2/db_1/bin:/usr/ccs/bin:/usr/sbin:/bin:/usr/bin:/etc:/usr/sbin:/usr/ucb:/usr/bin/x11:/sbin:/u01/oraprod:/u01/oraprod/bin:/bin:/u01/oraprod/scripts:/usr/bin:/etc:/usr/sbin:/usr/ucb:/usr/bin/x11:/sbin:/u01/oraprod:/u01/oraprod/bin:/bin:/usr/local/bin:/u01/oracle/prod/scripts:/u01/local/bwa/prod/scripts:/u01/local/bwa/prod/c/bin:/u01/local/pvcs:/usr/ccs/bin:/usr/openwin/bin:.:/u01/local/bwa/prod/scripts:/u01/local/sas8:/u01/finprod/finproddb/10.2/db_1/OPatch
$export ORA_NLS10=/u01/finprod/finproddb/10.2/db_1/nls/data/9idata
export DISPLAY=10.254.101.25:0.0
unset TNS_ADMIN
unset ORACLE_BASE
echo all the env variables above and then proceed.
To avoid a local_listener error after launching DBUA since port 1521 is in use, copy the following files from the 9i ORACLE_HOME/network/admin/finprod_frmbwds03 to ORACLE_HOME/network/admin
Refer note 431437.1 in metalink…..synopsis below
Cause
Apparently if there are no sqlnet config files in the default location of the 9.2 ORACLE_HOME/network/admin directory then DBUA fails.? The sqlnet config files are:
listener.ora
tnsnames.ora
sqlnet.ora
The TNS_ADMIN environment variable is not set in 10g. “Installation Guide for Solaris Operating System (SPARC 64-Bit)” wants it to be unset during software installation/database upgrade. During DBUA’s “Pre Upgrade” check, it runs database from 9.2 home. But?in an?Oracle E-business Suite environment?the sqlnet config files?are not in the default location of $ORACLE_HOME?/network/admin, therefore?DBUA cannot resolve the local_listener.
Solution
1. Make sure that the sqlnet config files listener.ora, tnsnames.ora, sqlnet.ora are in the default location of $ORACLE_HOME/network/admin.?
This should be the 9.2 ORACLE_HOME????2. If they are not there then find out where the TNS_ADMIN variable points to.????3. Copy the sqlnet config files from TNS_ADMIN location to $ORACLE_HOME/network/admin.
This?should be the 9.2 ORACLE_HOME
4. Rerun DBUA
Steps below :
cd /u01/finprod/finproddb/92/network/admin/finprod_frmbwds03
cp listener.ora /u01/finprod/finproddb/92/network/admin
cp tnsnames.ora /u01/finprod/finproddb/92/network/admin
cp sqlnet_ifile.ora /u01/finprod/finproddb/92/network/admin
cd /u01/finprod/finproddb/92/network/admin
$ ls -lrt
-rw-r–r– 1 oraprod dba 1315 Apr 24 11:26 listener.ora
-rw-r–r– 1 oraprod dba 2292 Apr 24 11:26 tnsnames.ora
-rw-r–r– 1 oraprod dba 627 Apr 24 11:26 sqlnet_ifile.ora
$
Before upgrading to 10.2.0.3 apply the following patches using opatch to avoid the upgrade failing half way.
The upgrade will fail if the following PATCHES are not applied.
The patch numbers for AIX5L are
5755471
5892355
5871314
Download the patches and transfer to any directory.
Unzip the files and go to directory created on unzip
For example patch number 5755471
Go to /5755471 directory
opatch lsinventory –invPtrLoc $ORACLE_HOME/oraInst.loc
Make sure right inventory is picked up – the new 10g patched environment.
opatch apply –invPtrLoc $ORACLE_HOME/oraInst.loc
For two of the patches you will see a few relinking errors which can be ignored…
Error similar to this…
ld: 0711-783 WARNING: TOC overflow. TOC size: 67040 Maximum size: 65536Extra instructions are being generated for each reference to a TOCsymbol if the symbol is in the TOC overflow area.ld: 0711-783 WARNING: TOC overflow. TOC size: 67040 Maximum size: 65536Extra instructions are being generated for each reference to a TOCsymbol if the symbol is in the TOC overflow area.
Now proceed with UPGRADE of database.
Now launch DBUA from the 10g ORACLE_HOME/bin
cd $ORACLE_HOME/bin
export DISPLAY=10.254.101.25:0.0- This is the I.P. address of your machine/laptop.
Should be in the 10g HOME.
./dbua
Takes a long time to go from 11 percent to 13 percent.
From 32 percent the JServer JAVA Virtual Machine upgrade starts.
Post upgrade starts at 58 percent.
Takes a long time at 33,39,73 percent.
Takes about 7 hours at 82 percent -running the compilation job of all objects as part of the POST UPGRADE…as screenshot above shows.
To find out how much has completed run this command:
SELECT COUNT(*) FROM UTL_RECOMP_COMPILED;
The count should increase .
To find out how many objects remain to be compiled run this command:
SELECT COUNT(*) FROM obj$ WHERE status IN (4, 5, 6);
The count should reduce.
