Oracle升级_oracle 10g版本由 10.2.0.1.0升级为10.2.0.4.0（即CPU升级）

***********************************************声明************************************************ 

原创作品，出自 “深蓝的blog” 博客，欢迎转载，转载时请务必注明出处（http://blog.csdn.net/huangyanlong）。

表述有错误之处，请您留言，不胜感激。

提醒：点击目录，更有助于您的查看。

*****************************************************************************************************

实验环境：

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

//开启告警日志文件

[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: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太小错误

[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：组建名

//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）。

表述有错误之处，请您留言，不胜感激。

提醒：点击目录，更有助于您的查看。

*****************************************************************************************************