Performing a 9i to 10g upgrade using DBUA

helmetpastoralΛογισμικό & κατασκευή λογ/κού

13 Δεκ 2013 (πριν από 3 χρόνια και 8 μήνες)

210 εμφανίσεις

ORACLE


9i to 10g database Upgrade using DBUA
.



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



10
g

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 uni
x command
unset TNS_ADMIN


export ORACLE_HOME=/u01/
finprod
/
finprod
db/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
L
D_LIBRARY_PATH=$ORACLE_HOME/lib:/usr/dt/lib:/usr/openwin/lib:$ORACLE_HOME/ctx/lib

export
ORA_NLS10=/u01/
finprod
/
finprod
db/10.2/db_1/nls/data/9idata

unset TNS_ADMIN


To m
ake 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 f
iles under $ORACLE_HOME/nls/data and


$ORACLE_HOME/nls/data/old to directory $ORACLE_HOME/nls/data/9idata


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
/
finprod
db/10.2/db_1/nls/data/9idata ...

Copying files to /u01/
finprod
/
finprod
db/10.2/db_1/nls/data/9idata...

Copy finished.


Please reset environm
ent variable ORA_NLS10 to
/u01/
finprod
/
finprod
db/10.2/db_1/nls/data/9idata


export ORA_NLS10=/u01/
finprod
/
finprod
db/10.2/db_1/nls/data/9idata


oraprod
(DATABASE)@nbrbwdev5:echo $ORA_NLS10

/u01/
finprod
/
finprod
db/10.2/db_1/nls/data/9idata


Now configure liste
ner for 10g home



First unset TNS_ADMIN

so that the old 9i environment is unset.


u
nset 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:









Use same listener name as in old machine….
FINPROD

was used here and not
LISTENER as above.








Use same port number as currently used

by
the current dat
abase
-
152
6







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/
fin
prod
/
finprod
db/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


l
snrctl 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> star
tup;

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 mounte
d.

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.



1.

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.


c
d /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



T
hen, 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).





Databas
e:


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 databas
e 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 adju
stments 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 nee
d 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
O
racle 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 t
o 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.


Misc
ellaneous 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 10
g 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');


s
pool off


Run
@grant_connect1.sql

and make sure any bb staff I’d are in quotes.


Ex grant connect1 to
“bb16872.1”;


Scripts fo
r 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
enc
rypted. To downgrade back to the original release, all of the
database links with encrypted passwords must be dropped prior to the
downgrade.

C
onsequently, 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.


Sp
ool 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,
s
ys.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 SYSDB
A

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_s
chema_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.A
UTO_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);


e
xec 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 =>
DB
MS_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=>'GA
THER',
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 COL
UMNS 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 => 'F
OR ALL COLUMNS SIZE AUTO',
cascade => TRUE);


spool off


The above steps take
1.5


hours max.


Run /u02/oradata/software/analy
z
e.sql



Get a count of all INVALID OBJECTS PRIOR TO UPGRADE:


s
pool 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_typ
e, owner;

spool off;


LOGOFF FROM MACHINE.




LOGIN AGAIN AND INVOKE 10g ENVIRONMENT



UPGRADE STEPS


Make sure environment in 10
g 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/d
b_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:/u
01/finprod/finproddb/10.2/db_1/ctx/lib

$export
LIBPATH=
/u01/finprod/f
inproddb/10.2/db_1/lib:/usr/dt/lib:/usr/openwin/lib:/u01/finprod/fi
nproddb/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/fin
proddb/10.2/db_1/bin:/usr/ccs/bin:/usr/sbin:/bin:/u01/finprod/finproddb/10.2/db_1/Apach
e/perl/bin:/usr/bin:/usr/bin:/u01/finprod/finproddb/10.2/db_1/bin:/usr/ccs/bin:/usr/sbin:/bi
n:/usr/bin:/etc:/usr/
sbin:/usr/ucb:/usr/bin/x11:/sbin:/u01/oraprod:/u01/oraprod/bin:/bin:/u
01/oraprod/scripts:/usr/bin:/etc:/usr/sbin:/usr/ucb:/usr/bin/x11:/sbin:/u01/oraprod:/u01/or
aprod/bin:/bin:/usr/local/bin:/u01/oracle/prod/scripts:/u01/local/bwa/prod/scripts:/u01/loc
al/b
wa/prod/c/bin:/u01/local/pvcs:/usr/ccs/bin:/usr/openwin/bin:.:/u01/local/bwa/prod/scri
pts:/u01/local/sas8:/u01/finprod/finproddb/10.2/db_1/OPatch

$
export ORA_NLS10=/u01/
finprod
/
finprod
db/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/n
etwork/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 liste
ner.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 T
NS_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/fi
nprod/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 follow
ing PATCHES are not applied.


The patch numbers for AIX5L are


1.

5755471

2.

5892355

3.

5871314

4.


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 ig
nored...


Error similar to this…


ld: 0711
-
783 WARNING: TOC overflow. TOC size: 67040 Maximum size: 65536

Extra instructions are being generated for each reference to a TOC

symbol if the symbol is in the TOC overflow area.

ld: 0711
-
783 WARNING: TOC overflo
w. TOC size: 67040 Maximum size: 65536

Extra instructions are being generated for each reference to a TOC

symbol 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_HO
ME/bin


e
xport DISPLAY=10.254.101.25:0.0
-

This is the I.P. address of your machine/laptop.


Should be in the 10g HOME.


./dbua










CLICK YES






CLICK NEXT




BACKUP already taken so continue





No need of any OEM now.












Takes a

long time to go from 1
1

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 compilatio
n 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 ru
n this command:


SELECT COUNT(*) FROM obj$ WHERE status IN (4, 5, 6);


The coun
t should reduce.






Check the failed steps. Ignore if step is for Real Application Clusters as it is not used
in this environment.








DATABASE UPGRADE COMPLETE.