Oracle升级_oracle10g版本由10.2.0.1.0升级为10.2.0.4.0（即CPU升级）
CPU升级_ oracle 10g版本由 10.2.0.1.0升级为10.2.0.4.0
***********************************************声明************************************************
原创作品，出自 “深蓝的blog” 博客，欢迎转载，转载时请务必注明出处（http://blog.csdn.net/huangyanlong）。
表述有错误之处，请您留言，不胜感激。
提醒：点击目录，更有助于您的查看。
*****************************************************************************************************
实验环境：
|
虚拟机软件 |
Vbox 4.2.12 |
|
实验系统 |
Linux RedHat 5.5 |
|
数据库 |
Oracle10g 10.2.0.1.0 |
|
注意项点：实验系统为镜像导入，存留其余无关数据库，在下面的实验中会删除，望阅者周知。 |
|
1、 升级准备
（1）确认hosts设置
【补充】：修改Linux的IP地址
[root@rh55 ~]# vi /etc/hosts
[root@rh55 ~]# su - oracle
[oracle@rh55 ~]$ cd $ORACLE_HOME/dbs
[oracle@rh55 dbs]$ ls
（2）检查磁盘空间
[oracle@rh55 dbs]$ df -h
//u01下只有4g较少，之后升级到11g时需要至少5g以上空间，需给u01增加空间
为了给/u01增加可用空间，将本机内无用数据库卸载，如下：
[oracle@rh55 dbs]$ vi /etc/oratab //为卸载catdb、test1数据库做准备
[oracle@rh55 dbs]$ dbca
//使用dbca时报错，需要执行export命令，并注销后用oracle用户登陆操作系统
注销系统，使用oracle用户登陆：
$dbca
选择删除数据库，如下图：
先删除catdb库，如下图：
卸载数据库，如下图所示：
接下来，同样的方法卸载test1数据库，如下图：
[oracle@rh55 dbs]$ cd /u01/app/oracle/oradata/
[oracle@rh55 oradata]$ ls
[oracle@rh55 oradata]$ rm -rf catdb
[oracle@rh55 oradata]$ rm -rf test1
[oracle@rh55 oradata]$ cd /u01/app/oracle/admin/
[oracle@rh55 admin]$ ls
[oracle@rh55 admin]$ rm -rf catdb
[oracle@rh55 admin]$ rm -rf test1
[oracle@rh55 admin]$ df -h //u01已经变成6.5g，满足了后续安装oracle11g的工作
[oracle@rh55 admin]$ cd /disk1
[oracle@rh55 disk1]$ ls
[oracle@rh55 disk1]$ cd oradata
[oracle@rh55 oradata]$ ls
[oracle@rh55 oradata]$ rm -rf test1
[oracle@rh55 oradata]$ !sql
16:04:45 SYS@ prod>startup
[oracle@rh55 ~]$ tail -f/u01/app/oracle/admin/prod/bdump/alert_prod.log
//开启告警日志文件
16:06:11SYS@ prod>select* from v$version;
[root@rh55 oradata]# cd /disk2/oradata
[root@rh55 oradata]# ls
prod test1
[root@rh55 oradata]# rm -rf test1
16:09:27 SYS@ prod>show parameter archive
//查看一下归档信息，为了删除无用的归档目录
[root@rh55 oradata]# cd /
[root@rh55 /]# cd disk1
[root@rh55 disk1]# ls
arch1 arch2 backup lost+found oradata prod recovery rman
[root@rh55 disk1]# rm -rf arch2
//因为之前查看到了prod库的归档为arch1，因此推断出arch2为其它库的文件，之前已经将库删除了所以可以将此目录删除
[root@rh55 disk1]# cd backup
[root@rh55 backup]# ls
catdb prod
[root@rh55 backup]# rm -rf catdb
//至此，已经将除prod以外的数据库清理干净了
16:13:03 SYS@ prod>archive log list //查看是否开启归档
[root@rh55 backup]# su - oracle
至此，多余的数据库卸载完毕。
（3）关闭无用服务
[root@rh55 ~]# chkconfig sendmail off
[root@rh55 ~]# chkconfig --list |grep scsi
iscsi 0:off 1:off 2:off 3:on 4:on 5:on 6:off
iscsid 0:off 1:off 2:off 3:on 4:on 5:on 6:off
[root@rh55 ~]# chkconfig iscsi off
[root@rh55 ~]# chkconfig iscsid off
[root@rh55 ~]# chkconfig --list |grep scsi
iscsi 0:off 1:off 2:off 3:off 4:off 5:off 6:off
iscsid 0:off 1:off 2:off 3:off 4:off 5:off 6:off
（4）上传升级包
方式一：使用samba服务器
[root@rh55 ~]# service smb start //报错，没有smb服务
smb: unrecognized service
//接下来安装samba服务器
[root@rh55 ~]# mount /dev/cdrom /media/
mount: block device /dev/cdrom iswrite-protected, mounting read-only
[root@rh55 ~]# yum install -y samba-*
Loaded plugins: rhnplugin, security
This system is not registered with RHN.
RHN support will be disabled.
base | 1.3 kB 00:00
Setting up Install Process
Package samba-common-3.0.33-3.28.el5.i386 alreadyinstalled and latest version
Package samba-client-3.0.33-3.28.el5.i386 alreadyinstalled and latest version
Resolving Dependencies
--> Running transaction check
---> Package samba.i386 0:3.0.33-3.28.el5 setto be updated
--> Processing Dependency: perl(Convert::ASN1)for package: samba
---> Package samba-swat.i386 0:3.0.33-3.28.el5set to be updated
--> Running transaction check
---> Package perl-Convert-ASN1.noarch0:0.20-1.1 set to be updated
--> Finished Dependency Resolution
Dependencies Resolved
================================================================================
Package Arch Version Repository Size
================================================================================
Installing:
samba i386 3.0.33-3.28.el5 base 16 M
samba-swat i386 3.0.33-3.28.el5 base 8.2 M
Installing for dependencies:
perl-Convert-ASN1 noarch 0.20-1.1 base 41 k
Transaction Summary
================================================================================
Install 3 Package(s)
Upgrade 0 Package(s)
Total download size: 24 M
Downloading Packages:
--------------------------------------------------------------------------------
Total 8.1GB/s | 24 MB 00:00
Running rpm_check_debug
Running Transaction Test
Finished Transaction Test
Transaction Test Succeeded
Running Transaction
Installing :perl-Convert-ASN1 1/3
error: failed to stat /media/RHEL_5.5 i386 DVD:No such file or directory
Installing : samba 2/3
Installing : samba-swat 3/3
Installed:
samba.i386 0:3.0.33-3.28.el5 samba-swat.i386 0:3.0.33-3.28.el5
Dependency Installed:
perl-Convert-ASN1.noarch 0:0.20-1.1
Complete!
[root@rh55 ~]# service smb start
Starting SMB services: [ OK ]
Starting NMB services: [ OK ]
[root@rh55 ~]# smbpasswd -a oracle
New SMB password:
Retype new SMB password:
Added user oracle.
将补丁包拷贝到共享的oracle目录下，如下所示：
方式二：使用ssh软件
使用SecureFX上传软件包，如下图界面：
//与虚拟机建立连接（注意使用oracle用户），将windows下对应的文件拖拽到虚拟机相应目录下
方式三：使用X-manager shell软件
//至此，补丁包上传完毕（使用以上任意一种方式即可完成）。
安装包解压：
[root@rh55 ~]# su - oracle
[oracle@rh55 ~]$ ls
[oracle@rh55 ~]$ unzip p6810189_10204_Linux-x86.zip //对第一个补丁包解压
（5）做一次冷备
[oracle@rh55 ~]$ rman target /
RMAN> list backup;
RMAN> list backup of database; //旧的备份信息
RMAN> list copy;
RMAN> delete backup;
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=154 devtype=DISK
List of Backup Pieces
BP Key BSKey Pc# Cp# Status Device Type Piece Name
------- ------- --- --- ----------- ----------- ----------
333 333 1 1 AVAILABLE DISK /disk1/rman/PROD_366.bak
334 334 1 1 AVAILABLE DISK /disk1/rman/PROD_365.bak
335 335 1 1 AVAILABLE DISK /disk1/recovery/PROD/autobackup/2012_10_26/o1_mf_s_797680992_88mw2mnf_.bkp
339 339 1 1 AVAILABLE DISK /disk1/recovery/PROD/autobackup/2012_10_26/o1_mf_s_797682372_88mx65co_.bkp
340 340 1 1 AVAILABLE DISK /disk1/recovery/PROD/autobackup/2012_10_26/o1_mf_s_797694184_88n8qcjf_.bkp
341 341 1 1 AVAILABLE DISK /disk1/recovery/PROD/autobackup/2012_10_26/o1_mf_s_797694788_88n9b64r_.bkp
342 342 1 1 AVAILABLE DISK /disk1/recovery/PROD/autobackup/2012_10_26/o1_mf_s_797695808_88nbb1lm_.bkp
Do you really want to delete the above objects(enter YES or NO)?yes
deleted backup piece
backup piece handle=/disk1/rman/PROD_366.bakrecid=333 stamp=797681138
deleted backup piece
backup piece handle=/disk1/rman/PROD_365.bakrecid=334 stamp=797681139
deleted backup piece
backup piecehandle=/disk1/recovery/PROD/autobackup/2012_10_26/o1_mf_s_797680992_88mw2mnf_.bkprecid=335 stamp=797681235
deleted backup piece
backup piecehandle=/disk1/recovery/PROD/autobackup/2012_10_26/o1_mf_s_797682372_88mx65co_.bkprecid=339 stamp=797682373
deleted backup piece
backup piecehandle=/disk1/recovery/PROD/autobackup/2012_10_26/o1_mf_s_797694184_88n8qcjf_.bkprecid=340 stamp=797694187
deleted backup piece
backup piecehandle=/disk1/recovery/PROD/autobackup/2012_10_26/o1_mf_s_797694788_88n9b64r_.bkprecid=341 stamp=797694790
deleted backup piece
backup piecehandle=/disk1/recovery/PROD/autobackup/2012_10_26/o1_mf_s_797695808_88nbb1lm_.bkprecid=342 stamp=797695809
Deleted 7 objects
RMAN> list copy;
RMAN> delete copy;
Do you really want to delete the above objects(enter YES or NO)?yes
deleted archive log
archive logfilename=/disk1/arch1/arch_1_4_797613178.log recid=853 stamp=797680899
deleted archive log
archive logfilename=/disk1/arch1/arch_1_5_797613178.log recid=854 stamp=797680902
deleted archive log
archive logfilename=/disk1/arch1/arch_1_6_797613178.log recid=855 stamp=797680911
deleted archive log
archive logfilename=/disk1/arch1/arch_1_7_797613178.log recid=856 stamp=797686101
deleted archive log
archive logfilename=/disk1/arch1/arch_1_8_797613178.log recid=857 stamp=797765967
deleted archive log
archive logfilename=/disk1/arch1/arch_1_9_797613178.log recid=858 stamp=797769330
deleted archive log
archive logfilename=/disk1/arch1/arch_1_10_797613178.log recid=859 stamp=797779367
deleted archive log
archive logfilename=/disk1/arch1/arch_1_11_797613178.log recid=860 stamp=797781258
deleted archive log
archive logfilename=/disk1/arch1/arch_1_12_797613178.log recid=861 stamp=797782326
deleted archive log
archive logfilename=/disk1/arch1/arch_1_13_797613178.log recid=862 stamp=797785426
deleted archive log
archive logfilename=/disk1/arch1/arch_1_14_797613178.log recid=863 stamp=797786840
deleted archive log
archive log filename=/disk1/arch1/arch_1_15_797613178.logrecid=864 stamp=797852200
deleted archive log
archive logfilename=/disk1/arch1/arch_1_16_797613178.log recid=865 stamp=798037031
deleted archive log
archive logfilename=/disk1/arch1/arch_1_17_797613178.log recid=866 stamp=798242772
deleted archive log
archive logfilename=/disk1/arch1/arch_1_18_797613178.log recid=867 stamp=847641900
Deleted 15 objects
RMAN> list copy;
specification does not match any archive log inthe recovery catalog
RMAN> sql'alter system switch logfile';
sql statement: alter system switch logfile
RMAN> sql'alter system switch logfile';
sql statement: alter system switch logfile
//接下来，做一个冷备
RMAN> run{
2> shutdown immediate;
3> startup mount;
4> backup database format '/disk2/backup/%d_%s.bak';
5> alter database open;
6> }
RMAN> quit
[oracle@rh55 ~]$ mkdir -p /disk2/backup
[oracle@rh55 ~]$ rman target /
Recovery Manager: Release 10.2.0.1.0 - Productionon Thu May 15 16:51:57 2014
Copyright (c) 1982, 2005, Oracle. All rights reserved.
connected to target database: PROD(DBID=199802235, not open)
注意：先不要按下面执行，先看看后边的“更正”！
RMAN> run{
2> shutdown immediate;
3> startup mount;
4> backup database format '/disk2/backup/%d_%s.bak';
5> alter database open;
6> }
using target database control file instead ofrecovery catalog
database dismounted
Oracle instance shut down
connected to target database (not started)
Oracle instance started
database mounted
Total System Global Area 272629760 bytes
Fixed Size 1218944 bytes
Variable Size 71304832 bytes
Database Buffers 197132288 bytes
Redo Buffers 2973696 bytes
Starting backup at 15-MAY-14
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=157 devtype=DISK
channel ORA_DISK_1: starting full datafilebackupset
channel ORA_DISK_1: specifying datafile(s) inbackupset
input datafile fno=00001name=/u01/app/oracle/oradata/prod/system01.dbf
input datafile fno=00003name=/u01/app/oracle/oradata/prod/sysaux01.dbf
input datafile fno=00006name=/u01/app/oracle/oradata/prod/test1.dbf
input datafile fno=00002name=/u01/app/oracle/oradata/prod/undotbs01.dbf
input datafile fno=00005name=/u01/app/oracle/oradata/prod/example01.dbf
input datafile fno=00007 name=/u01/app/oracle/oradata/prod/tbs1.dbf
input datafile fno=00008name=/u01/app/oracle/oradata/prod/test3.dbf
input datafile fno=00004name=/u01/app/oracle/oradata/prod/users01.dbf
channel ORA_DISK_1: starting piece 1 at 15-MAY-14
channel ORA_DISK_1: finished piece 1 at 15-MAY-14
piece handle=/disk2/backup/PROD_378.baktag=TAG20140515T165417 comment=NONE
channel ORA_DISK_1: backup set complete, elapsedtime: 00:00:34
Finished backup at 15-MAY-14
Starting Control File and SPFILE Autobackup at15-MAY-14
piece handle=/disk1/recovery/PROD/autobackup/2014_05_15/o1_mf_s_847644081_9q906yj6_.bkpcomment=NONE
Finished Control File and SPFILE Autobackup at15-MAY-14
database opened
更正：由于之前已经处于mount状态，直接执行备份语句即可，如下所示：
RMAN> backup database format '/disk2/backup/%d_%s.bak';
database closed
database dismounted
Oracle instance shut down
connected to target database (not started)
Oracle instance started
database mounted
Total System Global Area 272629760 bytes
Fixed Size 1218944 bytes
Variable Size 71304832 bytes
Database Buffers 197132288 bytes
Redo Buffers 2973696 bytes
Starting backup at 15-MAY-14
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=157 devtype=DISK
channel ORA_DISK_1: starting full datafile backupset
channel ORA_DISK_1: specifying datafile(s) inbackupset
input datafile fno=00001name=/u01/app/oracle/oradata/prod/system01.dbf
input datafile fno=00003name=/u01/app/oracle/oradata/prod/sysaux01.dbf
input datafile fno=00006 name=/u01/app/oracle/oradata/prod/test1.dbf
input datafile fno=00002name=/u01/app/oracle/oradata/prod/undotbs01.dbf
input datafile fno=00005name=/u01/app/oracle/oradata/prod/example01.dbf
input datafile fno=00007name=/u01/app/oracle/oradata/prod/tbs1.dbf
input datafile fno=00008name=/u01/app/oracle/oradata/prod/test3.dbf
input datafile fno=00004name=/u01/app/oracle/oradata/prod/users01.dbf
channel ORA_DISK_1: starting piece 1 at 15-MAY-14
channel ORA_DISK_1: finished piece 1 at 15-MAY-14
piece handle=/disk2/backup/PROD_380.baktag=TAG20140515T170725 comment=NONE
channel ORA_DISK_1: backup set complete, elapsedtime: 00:00:34
Finished backup at 15-MAY-14
Starting Control File and SPFILE Autobackup at15-MAY-14
piecehandle=/disk1/recovery/PROD/autobackup/2014_05_15/o1_mf_s_847645628_9q90zk5q_.bkpcomment=NONE
Finished Control File and SPFILE Autobackup at15-MAY-14
database opened
RMAN> list backup;
RMAN> list copy;
RMAN> alter database open; //开库
RMAN>quit
（6）调整内存及数据文件大小
[oracle@rh55 ~]$ sqlplus '/as sysdba'
SQL*Plus: Release 10.2.0.1.0 - Production on ThuMay 15 17:38:44 2014
Copyright (c) 1982, 2005, Oracle. All rights reserved.
Connected to:
Oracle Database 10g Enterprise Edition Release10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Miningoptions
17:38:45 SYS@ prod>select tablespace_name,sum(bytes/1024/1024)free_M from dba_free_space group by tablespace_name; //查看各表空间剩余量
17:43:37 SYS@ prod>col name for a50
17:40:13 SYS@ prod>select file#,name from v$datafile; //查看数据文件号
17:43:48 SYS@ prod>select file#,name,bytes/1024/1024 fromv$datafile;
//查看数据文件大小，根据上面查询出的表空间剩余空间对其进行相应调整
17:46:04 SYS@ prod>alter database datafile 1 resize 700m; //给system01.dbf扩容
17:47:09 SYS@ prod>alter database datafile 3 resize 400m; //给sysaux01.dbf扩容
17:47:44 SYS@ prod>alter database datafile 4 resize 50m; //给users01.dbf扩容
17:49:28 SYS@ prod>select file#,name,bytes/1024/1024 fromv$datafile;
//再次查看数据文件大小，这里因为undo启动了自动扩展功能，所以未对其进行扩容
17:50:22SYS@ prod>show parameter sga //查看sga设置情况
17:51:54 SYS@ prod>alter system set sga_max_size=600mscope=spfile; //修改sga最大值
17:53:40 SYS@ prod>alter system set sga_target=600m scope=spfile; //修改sga目标值
17:54:55 SYS@ prod>show parameter shared //查看各共享池尺寸
17:55:05 SYS@ prod>alter system set shared_pool_size=200mscope=spfile;//调整共享池尺寸
17:55:41 SYS@ prod>alter system set java_pool_size=300mscope=spfile;
//调整java池尺寸，此操作非必须，这么做是为了之后在执行数据字典升级脚本时加快速度
【补充】：报sga_target太小错误
17:56:14 SYS@prod>startupforce nomount;
//由于刚刚对sga进行了扩展，致使大小已经超出初始化文件中设定的sga大小，所以报此错误
[oracle@rh55 ~]$ cd $ORACLE_HOME/dbs
[oracle@rh55 dbs]$ ls
alert_prod.log c-199802235-20120822-04 init.ora orapwprod
alert_test1.log hc_catdb.dat initprod.ora snapcf_prod.f
c-199802235-20120812-1d hc_prod.dat lkCATDB spfileprod.ora
c-199802235-20120812-20 hc_test1.dat lkPROD
c-199802235-20120812-22 initdw.ora lkTEST1
[oracle@rh55 dbs]$ vi initprod.ora
补充完毕
[oracle@rh55 dbs]$ !sql
18:06:34 SYS@ prod>create spfile from pfile;
18:08:41 SYS@ prod>startup force nomount;
18:08:54 SYS@ prod>show parameter sga;
18:09:21 SYS@ prod>show parameter cache;
18:09:47 SYS@ prod>show parameter disp；
18:11:16 SYS@ prod>alter system set dispatchers='';
18:11:51 SYS@ prod>alter system set shared_servers=0;
18:12:17 SYS@ prod>shutdown;
18:12:47 SYS@ prod>exit
（7）查看监听状态
确保监听处于停止状态
[oracle@rh55 dbs]$ lsnrctl status //查看监听状态
//查看所有相关端口已经关闭
[oracle@rh55 dbs]$ netstat -an |grep 1521
[oracle@rh55 dbs]$ netstat -an |grep 1158
[oracle@rh55 dbs]$ netstat -an |grep 5500
2、 升级oracle软件
运行补丁包中的./runInstaller文件，参考如下图所示：
下面报错了，如下图：
重启虚拟机，再次执行以上操作，不在弹出错误提示，进入如下界面：
[root@rh55 ~]# /u01/app/oracle/product/10.2.0/db_1/root.sh //运行第一个脚本
Running Oracle10 root.sh script...
The following environment variables are set as:
ORACLE_OWNER= oracle
ORACLE_HOME= /u01/app/oracle/product/10.2.0/db_1
Enter the full pathname of the local bindirectory: [/usr/local/bin]:
The file "dbhome" already exists in/usr/local/bin. Overwrite it? (y/n)
[n]: y
Copyingdbhome to /usr/local/bin ...
The file "oraenv" already exists in/usr/local/bin. Overwrite it? (y/n)
[n]: y
Copyingoraenv to /usr/local/bin ...
The file "coraenv" already exists in/usr/local/bin. Overwrite it? (y/n)
[n]: y
Copyingcoraenv to /usr/local/bin ...
Entries will be added to the /etc/oratab file asneeded by
Database Configuration Assistant when a databaseis created
Finished running generic part of root.sh script.
Now product-specific root actions will beperformed.
至此，数据库软件升级完成。
3、 升级数据库
[root@rh55 ~]# su - oracle
[oracle@rh55 ~]$ !sql
sqlplus '/as sysdba'
SQL*Plus: Release 10.2.0.4.0 - Production on FriMay 16 09:57:40 2014
Copyright (c) 1982, 2007, Oracle. All Rights Reserved.
Connected to an idle instance.
SQL> startup upgrade; //以upgrade状态启库
ORACLE instance started.
Total System Global Area 729808896 bytes
Fixed Size 1269640 bytes
Variable Size 71303288 bytes
Database Buffers 654311424 bytes
Redo Buffers 2924544 bytes
Database mounted.
Database opened.
（1）升级数据字典
SQL> spool /home/oracle/cpu_up.log //把脚本信息录入到一个日志文件中
SQL> @?/rdbms/admin/catupgrd.sql //开始跑脚本，升级数据字典，约10分钟
//如上图，数据库字典升级脚本运行完毕
//根据提示信息，下一步关库，之后正常启库，之后运行utlrp.sql脚本
SQL> spool off; //结束日志文件录入
SQL> shutdown immediate; //关库
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup //正常启动
ORACLE instance started.
Total System Global Area 729808896 bytes
Fixed Size 1269640 bytes
Variable Size 130023544 bytes
Database Buffers 595591168 bytes
Redo Buffers 2924544 bytes
Database mounted.
Database opened.
SQL> @?/rdbms/admin/utlrp.sql
//若在没有应用的情况下，运行这个脚本会比较快，约2分钟
//如上图，没有任何错误，数据库字典升级结束
（2）查看版本升级情况
SQL> select * from utl_recomp_errors; //检测组件升级情况
SQL> col comp_name for a30
SQL> set linesize 200
SQL> select comp_name,version,status from dba_registry;
//comp_name：组建名
SQL>show parametercomp
//compatible参数一旦被修改，数据库将无法进行回退，修改之前，一定确认所有应用在新版本上运行没有问题再去修改此参数
（3）修改兼容性参数
SQL> alter system set compatible='10.2.0.4.0' scope=spfile;
//确认应用运行无误后，修改参数compatible
至此，数据库已经由10.2.0.1.0升级为10.2.0.4.0。
SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
***********************************************声明************************************************
原创作品，出自 “深蓝的blog” 博客，欢迎转载，转载时请务必注明出处（http://blog.csdn.net/huangyanlong）。
表述有错误之处，请您留言，不胜感激。
提醒：点击目录，更有助于您的查看。
*****************************************************************************************************