upgrade oracle version 9i to 10g

從9.2.0.1升級到10.2.0.4的過程，同事寫的，記錄一下。

[@more@]

1. Environment Setting：

Solaris 10 + Oracle 9.2.0.1/9.2.0.4(SPARC 64-bit)
2. DB Version：
SQL>select version from v$instance;
>=9.2.0.4 direct upgrade to 10.2.0.4.0；
<9.2.0.4 of 9iR2, upgrade to 9.2.0.8 first, then upgrade to 10.2.0.4.0
3. Upgrade to 9.2.0.8：
A). Preparation:
a).Check pool size of SHARED_POOL_SIZE & JAVA_POOL_SIZE to minimum size 150M each;
If not, verify it pfile;
b). Shutdown DB and Take a full cold backup
c). Shutdown all Oracle processes, including listener
d). Copy 9208 patch to /u01/9i_patch and unzip it, change owner to Oracle
e). Check group oinstall and dba exist or not
$ id –ap oracle
f).Modify response file and patch with silent mode:
Copy one as backup before you modify the response file allocated in
/u01/9i_patch/response/patchset.rsp
$ sh /u01/9i_patch/runInstaller –silent –responseFile /u01/9i_patch/response/patchset_new.rsp
Notes:
I). Get ORACLE_HOME_NAME from installation log(…/…/oraInventory/logs/):
9i: ORACLE_HOME
10g: OraDbHome1 (Default value, Huailin)
ORACLE_HOME_10g (Jiagang)
II). Execute $ORACLE_HOME/root.sh as root
g). If using DBUA or other binary java command, it may fail because of Oracle’s bug that
there is no execution permission on “loadjava”. To fix this bug to follow blow steps:
$ cd $ORACLE_HOME/jdk/jre/bin
$ ln –s java jre
$ cd $ORACLE_HOME/jdk/jre/bin/sparc/native_threads
Ln –s java jre
$ chmod 755 $ORACLE_HOME/bin/loadjava
B).Upgrade DB:
$ cd $ORACLE_HOME/rdbms/admin
SQL> sqlplus / as sysdba
SQL> startup migrate
SQL>spool /u01/9i_upgrade.log
SQL>@catpatch.sql
SQL>spool off
Notes: If you find any error in the log, you can rerun the script “catpatch.sql” after you correcting any problem.
SQL>shutdown
SQL>startup
To compile all invalid objects:
SQL> @ utlrp.sql

4. Upgrade to 10.2.0.4.0:
A). Source Installation-10.2.0.1.0:
a). Copy source to /u01/10g_source/ and unzip it:
$gunzip solaris……
$chown oracle:oinstall solaris……
$ cpio –idcvm < solaris……
b). Modify environment parameters:
$ su – root
$ cd /export/home/oracle
$ cp .profile profile_9i
Modify .profile to 10g ORACLE_HOME
$ cp .profile profile_10g
c). Modify kernel parameter:
$ cp /etc/system /etc/system.bak
$ vi /etc/system
set shmsys:shminfo_shmmax=13958643712
set shmsys:shminfo_shmmin=1
set shmsys:shminfo_shmmni=2048
set shmsys:shminfo_shmseg=32
set semsys:seminfo_semmns=14421
set semsys:seminfo_semmni=4096
set semsys:seminfo_semmsl=1250
set semsys:seminfo_semopm=100
set semsys:seminfo_semvmx=32767
set semsys:seminfo_semmap=4096
set semsys:seminfo_semmnu=4096
set semsys:seminfo_semume=64
set msgsys:msginfo_msgmap=258
set msgsys:msginfo_msgmni=256
set msgsys:msginfo_msgssz=16
set msgsys:msginfo_msgtql=1024
set msgsys:msginfo_msgseg=32768
set noexec_user_stack=1
d). Reboot server to take effect
e). Package Check:
# pkginfo -i SUNWarc SUNWbtool SUNWhea SUNWlibm SUNWlibms SUNWsprot
SUNWsprox SUNWtoo SUNWi1of SUNWi1cs SUNWi15cs SUNWxwfnt
Package installation: root> pkgadd –d .SUNWarc
f).Copy response file for backup and modify it:
# cp /u01/10g_source/response/custom.rsp /u01/10g_source/response/custom_new.rsp
g).Install in silent mode:
$ sh /u01/10g_source/runInstaller –silent –responseFile
/u01/10g_source/response/custom_new.rsp
B).Patch installation -10.2.0.4.0:
a). Copy source to /u01/10g_patch and unzip the patch
b). Copy the response file for backup and modify the response file in /u01/10g_patch/response/patchset.rsp
c). Install patch with silent mode:
$ sh /u01/10g_patch/runInstaller –silent –responseFile /u01/10g_patch/response/patchset_new.rsp
d).Copy all parameter files from ORACLE_9I_HOME(/u01/app/oracle/product/9201) to
ORACLE_10G_HOME(/u01/app/oracle/product/10201)
e).mark obsolete or conflict parameters in initorcl.ora:
#hash_join_enabled=TRUE
#log_archive_start = false
#MAX_ENABLED_ROLES=70
#PARALLEL_AUTOMATIC_TUNING=TRUE
#fast_start_mttr_target=300
f).Add new parameters:
aq_tm_processes=0
audit_file_dest=/u01/app/oracle/admin/orcl/adump
db_recovery_file_dest_size=21474836480
db_recovery_file_dest=/u01/app/oracle/admin/orcl/flash_recovery_area
resource_manager_plan=""
C).Upgrade to 10.2.0.4.0:
$ cd $ORACLE_HOME/rdbms/admin
$sqlplus / as sysdba
SQL>startup upgrade
SQL>create tablespace sysaux datafile ‘/u01/oradata/orcl/sysaux01.dbf’
Size 500m reuse extent management local segment space management auto online;
SQL>spool /u01/10g_upgrade.log
SQL>@catupgrd.sql
SQL>spool off
If errors in upgrade log file, you can rerun script “catupgrd.sql”after correcting any problem.
SQL>@utlu102s.sql TEXT
(Check which component has been updated)
SQL>shutdown immediate
SQL>startup restrict
SQL>@utlrp.sql
SQL>shutdown
SQL>startup
Notes: sometimes it will be terminated during the upgrade for the JVM error. If this error encountered, we need to reload the JVM (remove first and install again). Here is the procedure to reload the JVM:
a). execute the script of upgrade patch, encounter error to be terminated.
b). reload JVM:
Use this SQL statement to check the installed component, if JVM invalid, please follow blow procedure to solve it.
I) remove:
SQL>startup upgrade
SQL>alter system set "_system_trig_enabled"=false scope=memory;
SQL>alter system enable restricted session;
SQL>@ $ORACLE_HOME/rdbms/admin/catnoexf.sql
SQL>@ $ORACLE_HOME/rdbms/admin/catnojav.sql
SQL>@ $ORACLE_HOME/xdk/admin/rmxml.sql
SQL>@ $ORACLE_HOME/javavm/install/rmjvm.sql
SQL>truncate table java$jvm$status;
SQL>delete from obj$ where obj#=0 and type#=0;
SQL>commit;
SQL>select owner,count(*) from all_objects where object_type like '%JAVA%' GROUP BY OWNER;
SQL>SELECT OBJ#,NAME FROM OBJ$ WHERE TYPE#=28 OR TYPE#=29 OR TYPE#=30
OR NAMESPACE=32;
SQL>SELECT O1.NAME FROM OBJ$ O1, OBJ$ O2 WHERE O1.TYPE#=5 AND O1.OWNER#=1
AND O1.NAME=O2.NAME AND O2.TYPE#=29;
SQL>shutdown immediate
II): reinstall:
SQL>startup upgrade
SQL>alter system set "_system_trig_enabled"=false scope=memory;
SQL>alter system enable restricted session;
SQL>@ $ORACLE_HOME/javavm/install/initjvm.sql
SQL>@ $ORACLE_HOME/xdk/admin/initxml.sql
SQL>@ $ORACLE_HOME/xdk/admin/xmlja.sql
SQL>@ $ORACLE_HOME/rdbms/admin/catjava.sql
SQL>@ $ORACLE_HOME/rdbms/admin/catexf.sql
SQL>shutdown immediate
c).execute the script again to complete the upgrade

5. Modifications:
A). Modify initial parameter file and mark some obsolete or deprecated parameters.
Restart the database with new initial file
B). Modify the listener and tnsnames.ora , restart the listener to make sure it works
C).Create OEM package:
Chown some system default users’password
SQL>alter user sys identified by ;
SQL> alter user system identified by ;
SQL> alter user dbsnmp identified by ;
$ emca –config dbcontrol db –repos create

6. Test DB with applications and backup whole database if possible