Oracle 从10.2.0.1升级到10.2.0.5
一、环境介绍
[oracle@SimpleLinux ~]$ uname -r
2.6.32-431.el6.i686
SQL> select * from v$version;
BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Prod
PL/SQL Release 10.2.0.1.0 - Production
CORE 10.2.0.1.0 Production
TNS for Linux: Version 10.2.0.1.0 - Production
NLSRTL Version 10.2.0.1.0 – Production
当前操做系统用户oracle关键环境变量以下：
[oracle@SimpleLinux ~]$ env | grep ORA
ORACLE_SID=ora11g
ORACLE_BASE=/u01/app/oracle
ORACLE_HOME=/u01/app/oracle/product/10.2.0/db_1
操做系统存储和内存资源相对充足。
[oracle@SimpleLinux ~]$ df -h
Filesystem Size Used Avail Use% Mounted on
/dev/mapper/vg_simplelinux-LogVol00 29G 8.7G 19G 32% /
tmpfs 448M 0 448M 0% /dev/shm
/dev/sda1 485M 33M 427M 8% /boot
[oracle@SimpleLinux ~]$ free -m
total used free shared buffers cached
Mem: 894 332 561 0 20 241
-/+ buffers/cache: 70 824
Swap: 1999 0 1999
二、备份环境和补丁包准备
首先，咱们须要将数据库、监听程序等相关对象进行关闭。根据不一样的状况，ASM、DB Control也须要关闭。
[oracle@SimpleLinux ~]$ lsnrctl stop
LSNRCTL for Linux: Version 10.2.0.1.0 - Production on 13-MAY-2014 11:19:04
Copyright (c) 1991, 2005, Oracle. All rights reserved.
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=localhost)(PORT=1521)))
The command completed successfully
SQL> conn / as sysdba
Connected.
SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
下面须要备份环境。注意：升级过程是有风险的，特别是在生产环境下。两个步骤是下降升级风险的重要策略：屡次测试和数据备份。屡次测试（在系统测试环境）能够帮助咱们事先发现升级方案问题，准备应对策略，验证方案。数据备份能够最大程度减小咱们数据损失风险。
具体备份方法针对各自状况不一样，能够选择RMAN、冷备份、exp/expdp等。此处笔者选择直接将目录拷贝的彻底方法。
[root@SimpleLinux upload]# mkdir /u02
[root@SimpleLinux upload]# cd /u01
[root@SimpleLinux u01]# cp -r * /u02
[root@SimpleLinux u01]#
正式进行动做前，要从新规划肯定升级步骤。本次升级要实现两个补丁包，一个是10.2.0.5升级包，另外一个是在10.2.0.5上运行的补丁包。在MOS上针对特定版本下载升级安装包，而且上传到服务器上。
[root@SimpleLinux ~]# cd /upload/
[root@SimpleLinux upload]# ls -l
total 1082304
-rw-r--r-- 1 root root 4278863 May 13 11:27 p11724962_10205_LINUX.zip
-rw-r--r-- 1 root root 1103992900 May 13 11:29 p8202632_10205_LINUX.zip –升级10.2.0.5包
注意：咱们升级数据库是有两个大的步骤，Oracle软件升级和Oracle数据库升级。能够回想一下咱们在安装数据库的时候，是先安装数据库软件（包括Grid、Clusterware），以后建立dbca数据库。升级也是同样，咱们须要先把数据库软件更新为最新版本，以后清理已经存在的数据库对象，使之升级为最新版本。
三、Oracle软件升级
咱们首先进行软件升级动做，解压安装包。
[root@SimpleLinux upload]# ls -l
total 1082472
drwxr-xr-x 5 root root 4096 Jul 23 2010 Disk1
-rw-r--r-- 1 root root 4278863 May 13 11:27 p11724962_10205_LINUX.zip
-rw-r--r-- 1 root root 1103992900 May 13 11:29 p8202632_10205_LINUX.zip
-rwxrwxrwx 1 root root 165290 Jul 20 2010 README.html
root中给oracle用户受权。
[root@SimpleLinux upload]# cd /
[root@SimpleLinux /]# chown -p oracle:oinstall upload/
chown: invalid option -- 'p'
Try `chown --help' for more information.
[root@SimpleLinux /]# chown -R oracle:oinstall upload/
[root@SimpleLinux /]#
升级软件是在图形化界面中进行，所须要配置XWindows或者vnc界面工具。执行目录中的.runInstall脚本，就能够启动界面。若是环境变量配置正常，自动Path都会带入到界面中。
错误：
[oracle@orcl Disk1]$ ./runInstaller
Starting Oracle Universal Installer...
Checking installer requirements...
Checking operating system version: must be redhat-3, SuSE-9, SuSE-10, redhat-4, redhat-5, redhat-6, UnitedLinux-1.0, asianux-1, asianux-2, asianux-3, enterprise-4, enterprise-5 or SuSE-11
Failed <<<<
须要：
[root@localhost ~]# vi /etc/redhat-release
将其内容修改成：
Red Hat Enterprise Linux Server release 4.4 (Tikanga)
大部分操做，都是点击Next确认过程。
下面是验证操做系统环境信息，是否知足安装条件。
最后须要以root用户手工执行脚本。
执行root.sh中，有一些覆盖的确认，建议覆盖。
[root@SimpleLinux ~]# cd /u01/app/oracle/product/10.2.0/db_1/
[root@SimpleLinux db_1]# ./root.sh
Running Oracle 10g 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 bin directory: [/usr/local/bin]:
The file "dbhome" already exists in /usr/local/bin. Overwrite it? (y/n)
[n]: y
（篇幅缘由，有省略……）
Now product-specific root actions will be performed.
若是没有明确的报错信息，就说明Oracle软件部分的安装成功。
SQL> alter database open;
ERROR at line 1:
ORA-01092: ORACLE instance terminated. Disconnection forced
第一步，经过startup upgrade启动实例
SQL> startup upgrade;
ORACLE instance started.
Total System Global Area
322961408 bytes
Fixed Size
2095992 bytes
Variable Size
100664456 bytes
Database Buffers
213909504 bytes
Redo Buffers
6291456 bytes
Database mounted.
Database opened.
第二步，升级数据字典和实例等
。
SQL>@?/rdbms/admin/catupgrd.sql
整个过程须要15分钟至30分钟左右
升级完毕显示以下：
Oracle Database 10.2 Upgrade Status Utility
09-11-2014 21:48:21
Component
Status Version HH:MM:SS
Oracle Database Server
VALID 10.2.0.5.0 00:15:41
JServer JAVA Virtual Machine
VALID 10.2.0.5.0 00:06:46
Oracle XDK
VALID 10.2.0.5.0 00:00:51
Oracle Database Java Packages
VALID 10.2.0.5.0 00:00:45
Oracle Text
VALID 10.2.0.5.0 00:01:07
Oracle XML Database
VALID 10.2.0.5.0 00:03:26
Oracle Workspace Manager
VALID 10.2.0.5.0 00:01:21
Oracle Data Mining
VALID 10.2.0.5.0 00:00:39
OLAP Analytic Workspace
VALID 10.2.0.5.0 00:00:48
OLAP Catalog
VALID 10.2.0.5.0 00:02:01
Oracle OLAP API
VALID 10.2.0.5.0 00:02:01
Oracle interMedia
VALID 10.2.0.5.0 00:06:35
Spatial
VALID 10.2.0.5.0 00:04:50
Oracle Expression Filter
VALID 10.2.0.5.0 00:00:30
Oracle Enterprise Manager
VALID 10.2.0.5.0 00:02:59
Oracle Rule Manager
VALID 10.2.0.5.0 00:00:18
.
Total Upgrade Time: 00:53:47
DOC>#######################################################################
DOC>#######################################################################
DOC>
DOC>
The above PL/SQL lists the SERVER components in the upgraded
DOC>
database, along with their current version and status.
DOC>
DOC>
Please review the status and version columns and look for
DOC>
any errors in the spool log file. If there are errors in the spool
DOC>
file, or any components are not VALID or not the current version,
DOC>
consult the Oracle Database Upgrade Guide for troubleshooting
DOC>
recommendations.
DOC>
DOC>
Next shutdown immediate, restart for normal operation, and then
DOC>
run utlrp.sql to recompile any invalid application objects.
DOC>
DOC>#######################################################################
DOC>#######################################################################
DOC>#
第三步，shutdown 实例。Startup实例
第四步，再次编译无效的应用对象
SQL> @?/rdbms/admin/utlrp.sql
TIMESTAMP
--------------------------------------------------------------------------------
COMP_TIMESTAMP UTLRP_BGN
2014-09-11 21:52:02
DOC>
The following PL/SQL block invokes UTL_RECOMP to recompile invalid
DOC>
objects in the database. Recompilation time is proportional to the
DOC>
number of invalid objects in the database, so this command may take
DOC>
a long time to execute on a database with a large number of invalid
DOC>
objects.
DOC>
DOC>
Use the following queries to track recompilation progress:
DOC>
DOC>
1. Query returning the number of invalid objects remaining. This
DOC>
number should decrease with time.
DOC>
SELECT COUNT(*) FROM obj$ WHERE status IN (4, 5, 6);
DOC>
DOC>
2. Query returning the number of objects compiled so far. This number
DOC>
should increase with time.
DOC>
SELECT COUNT(*) FROM UTL_RECOMP_COMPILED;
DOC>
DOC>
This script automatically chooses serial or parallel recompilation
DOC>
based on the number of CPUs available (parameter cpu_count) multiplied
DOC>
by the number of threads per CPU (parameter parallel_threads_per_cpu).
DOC>
On RAC, this number is added across all RAC nodes.
DOC>
DOC>
UTL_RECOMP uses DBMS_SCHEDULER to create jobs for parallel
DOC>
recompilation. Jobs are created without instance affinity so that they
DOC>
can migrate across RAC nodes. Use the following queries to verify
DOC>
whether UTL_RECOMP jobs are being created and run correctly:
DOC>
DOC>
1. Query showing jobs created by UTL_RECOMP
DOC>
SELECT job_name FROM dba_scheduler_jobs
DOC>
WHERE job_name like 'UTL_RECOMP_SLAVE_%';
DOC>
DOC>
2. Query showing UTL_RECOMP jobs that are running
DOC>
SELECT job_name FROM dba_scheduler_running_jobs
DOC>
WHERE job_name like 'UTL_RECOMP_SLAVE_%';
DOC>#
TIMESTAMP
--------------------------------------------------------------------------------
COMP_TIMESTAMP UTLRP_END
2014-09-11 21:53:40
DOC> The following query reports the number of objects that have compiled
DOC> with errors (objects that compile with errors have status set to 3 in
DOC> obj$). If the number is higher than expected, please examine the error
DOC> messages reported with each object (using SHOW ERRORS) to see if they
DOC> point to system misconfiguration or resource constraints that must be
DOC> fixed before attempting to recompile these objects.
DOC>#
OBJECTS WITH ERRORS
-------------------
0
DOC> The following query reports the number of errors caught during
DOC> recompilation. If this number is non-zero, please query the error
DOC> messages in the table UTL_RECOMP_ERRORS to see if any of these errors
DOC> are due to misconfiguration or resource constraints that must be
DOC> fixed before objects can compile successfully.
DOC>#
ERRORS DURING RECOMPILATION
---------------------------
0
SQL>