安装PSU

安装10.2.0.4.5及以上PSU补丁需要先安装10.2.0.4.4的psu补丁。

一、安装10.2.0.4.4的psu补丁
1.升级OPatch：
必须使用Opatch 10.2.0.4.7及以上版本，如果不满足，需要下载6880880补丁替换
$ORACLE_HOME/OPatch/opatch version

下载p6880880_102000_.zip
unzip p6880880_102000_.zip 解压到$ORACLE_HOME目录下，覆盖之前的OPatch目录

[oracle@node2 ~]$ $ORACLE_HOME/OPatch/opatch version
Invoking OPatch 10.2.0.4.2

OPatch Version: 10.2.0.4.2

OPatch succeeded.

unzip  p6880880_102000_Linux-x86-64.zip
cp -r OPatch/ $ORACLE_HOME/

[oracle@node2 ~]$ $ORACLE_HOME/OPatch/opatch version
Invoking OPatch 10.2.0.5.1

OPatch Version: 10.2.0.5.1

OPatch succeeded.

2.前期环境检查

export PATH=$PATH:/usr/ccs/bin:$ORACLE_HOME/OPatch

unzip p9352164_10204_.zip
opatch prereq CheckConflictAgainstOHWithDetail -phBaseDir ./9352164

关闭所有实例和监听
如果是RAC，关闭CRS

3.安装补丁，先安装10.2.0.4.4的psu。
如果是RAC模式，轮流在2个节点上安装补丁。
unzip p9352164_10204_.zip
cd 9352164
opatch apply

过程如下：
[oracle@node2 9352164]$ opatch apply
Invoking OPatch 10.2.0.5.1

Oracle Interim Patch Installer version 10.2.0.5.1
Copyright (c) 2010, Oracle Corporation.  All rights reserved.

Oracle Home       : /opt/ora10g/product/10.2.0/db_1
Central Inventory : /opt/ora10g/oraInventory
   from           : /etc/oraInst.loc
OPatch version    : 10.2.0.5.1
OUI version       : 10.2.0.4.0
OUI location      : /opt/ora10g/product/10.2.0/db_1/oui
Log file location : /opt/ora10g/product/10.2.0/db_1/cfgtoollogs/opatch/opatch2013-08-16_10-10-00AM.log

Patch history file: /opt/ora10g/product/10.2.0/db_1/cfgtoollogs/opatch/opatch_history.txt

ApplySession applying interim patch '9352164' to OH '/opt/ora10g/product/10.2.0/db_1'

Running prerequisite checks...
Provide your email address to be informed of security issues, install and
initiate Oracle Configuration Manager. Easier for you if you use your My
Oracle Support Email address/User Name.
Visit for details.
Email address/User Name:   --直接回车

You have not provided an email address for notification of security issues.
Do you wish to remain uninformed of security issues ([Y]es, [N]o) [N]:  Y  --输入Y

OPatch detected non-cluster Oracle Home from the inventory and will patch the local system only.

Please shutdown Oracle instances running out of this ORACLE_HOME on the local system.
(Oracle Home = '/opt/ora10g/product/10.2.0/db_1')

Is the local system ready for patching? [y|n]
y  --输入y
User Responded with: Y
Backing up files and inventory (not for auto-rollback) for the Oracle Home
Backing up files affected by the patch '9352164' for restore. This might take a while...
Backing up files affected by the patch '9352164' for rollback. This might take a while...
Execution of 'sh /home/oracle/9352164/custom/scripts/pre -apply 9352164 ':

Return Code = 0

......

The local system has been patched and can be restarted.

OPatch succeeded.

4.装载修改后的SQL文件到数据库中
cd $ORACLE_HOME/rdbms/admin
sqlplus /nolog
SQL> CONNECT / AS SYSDBA
SQL> STARTUP
SQL> @catbundle.sql psu apply
SQL> -- Execute the next statement only if this is the first 10.2.0.4 PSU applied in the Oracle home.
SQL> @utlrp.sql
SQL> QUIT

执行catbundle.sql脚本后，会产生apply和rollback脚本
SQL> @catbundle.sql psu apply

PL/SQL procedure successfully completed.

PL/SQL procedure successfully completed.

Generating apply and rollback scripts...
Check the following file for errors:
/opt/ora10g/product/10.2.0/db_1/cfgtoollogs/catbundle/catbundle_PSU_VSGPDB_GENERATE_2013Aug16_13_49_09.log

......

 

 

5.如果是第一次安装PSU补丁，需要重新编译一下数据库视图，该步骤在一个数据库上永远只需要执行一次，是为了完成在2008年1月份第一次发布CPU补丁时的后续工作，如果在安装以前的PSU或者CPU时执行过这个步骤那么就可以无需再次执行
先检查一下需要重新编译的视图的最大数量
cd $ORACLE_HOME/cpu/view_recompile
sqlplus /nolog
SQL> CONNECT / AS SYSDBA
SQL> @recompile_precheck_jan2008cpu.sql
SQL> QUIT

例如，第一次安装PSU补丁之后，有大量的对象需要重编译，检查结果建议按README中的说明运行脚本。
SQL> @recompile_precheck_jan2008cpu.sql

Running precheck.sql...

Number of views to be recompiled :2071
-----------------------------------------------------------------------

Number of objects to be recompiled :4170
Please follow the README.txt instructions for running viewrecomp.sql

PL/SQL procedure successfully completed.

5.1.非RAC模式下运行
cd $ORACLE_HOME/cpu/view_recompile
sqlplus /nolog
SQL> CONNECT / AS SYSDBA
SQL> SHUTDOWN IMMEDIATE
SQL> STARTUP UPGRADE
SQL> @view_recompile_jan2008cpu.sql
SQL> SHUTDOWN;
SQL> STARTUP;
SQL> QUIT

示例：
SQL> @view_recompile_jan2008cpu.sql

PL/SQL procedure successfully completed.

PL/SQL procedure successfully completed.

PL/SQL procedure successfully completed.

1 row created.

Commit complete.

No. of Invalid Objects is :250
Please refer to README.html to for instructions on validating these objects

PL/SQL procedure successfully completed.

Logfile for the current viewrecomp.sql session is : vcomp_VSGPDB_16Aug2013_10_46_07.log
......

5.2.如果是RAC模式，需要按以下内容运行
cd $ORACLE_HOME/cpu/view_recompile
sqlplus /nolog
SQL> CONNECT / AS SYSDBA
SQL> STARTUP NOMOUNT
SQL> ALTER SYSTEM SET CLUSTER_DATABASE=FALSE SCOPE=spfile;
SQL> SHUTDOWN
SQL> STARTUP UPGRADE
SQL> @view_recompile_jan2008cpu.sql
SQL> SHUTDOWN;
SQL> STARTUP NOMOUNT;

Set the CLUSTER_DATABASE initialization parameter to TRUE:

SQL> ALTER SYSTEM SET CLUSTER_DATABASE=TRUE SCOPE=spfile;

Restart the database:

SQL> QUIT
cd $CRS_HOME/bin
srvctl start database -d

 

6.最后再重新编译一下无效对象
cd $ORACLE_HOME/rdbms/admin
sqlplus /nolog
SQL> CONNECT / AS SYSDBA
SQL> @utlrp.sql

7.回退补丁：
关闭所有实例和监听
opatch rollback -id 9352164

8.执行rollback脚本
cd $ORACLE_HOME/rdbms/admin
sqlplus /nolog
SQL> CONNECT / AS SYSDBA
SQL> STARTUP
SQL> @catbundle_PSU__ROLLBACK.sql
SQL> QUIT

 

二、安装10.2.0.4.11的psu补丁
10.2.0.4.11的psu补丁包含了从10.2.0.4.5以来的补丁内容

1.前期环境检查
由于之前升级过了OPatch，
export PATH=$PATH:/usr/ccs/bin:$ORACLE_HOME/OPatch

unzip p12879929_102044_.zip
opatch prereq CheckConflictAgainstOHWithDetail -phBaseDir ./12879929

2.关闭数据库实例及监听
shutdown immediate
lsnrctl stop

3.安装补丁
unzip p12879929_102044_.zip
cd 12879929
opatch apply

4.装载修改后的SQL文件到数据库中
cd $ORACLE_HOME/rdbms/admin
sqlplus /nolog
SQL> CONNECT / AS SYSDBA
SQL> STARTUP
SQL> @catbundle.sql opsu apply
SQL> -- Execute the next statement only if this is the first PSU applied for 10.2.0.4 or this is the first PSU applied since 10.2.0.4.8.
SQL> @utlrp.sql
SQL> QUIT

5.检查2008年1月的CPU补丁是否生效，是否需要重编译视图。
由于之前安装了10.2.0.4.4补丁，已经执行过这步骤，这里可以不再执行。

6.最后再重新编译一下无效对象
cd $ORACLE_HOME/rdbms/admin
sqlplus /nolog
SQL> CONNECT / AS SYSDBA
SQL> @utlrp.sql

7.回退补丁：
关闭所有实例和监听
opatch rollback -id 12879929

8.执行rollback脚本
cd $ORACLE_HOME/rdbms/admin
sqlplus /nolog
SQL> CONNECT / AS SYSDBA
SQL> STARTUP
SQL> @catbundle_OPSU__ROLLBACK.sql
SQL> -- Execute the next statement only if this is the first PSU applied for 10.2.0.4 or this is the first PSU applied since 10.2.0.4.8.
SQL> @utlrp.sql
SQL> QUIT

三、检查PSU补丁是否正确安装
无论是从V$VERSION或者DBA_REGISTRY或者PRODUCT_COMPONENT_VERSION视图中，都无法查找到PSU的信息，这些视图中始终显示的是最原始的版本，比如10.2.0.4.0。
最常用的方法是使用opatch命令。在打完最新的PSU 10.2.0.4.8的10.2.0.4数据库中会有以下显示。
在数据库检查时，除了要检查opatch还要检查数据字典，以确定PSU补丁是否正确安装。

[oracle@node2 gg]$ $ORACLE_HOME/OPatch/opatch lsinventory -bugs_fixed | egrep -i 'PSU|DATABASE PATCH SET UPDATE'
9654991    12879929  Fri Aug 16 15:47:17 CST 2013   DATABASE PSU 10.2.0.4.5 (REQUIRES PRE-REQUISITE
9952234    12879929  Fri Aug 16 15:47:17 CST 2013   DATABASE PSU 10.2.0.4.6 (REQUIRES PRE-REQUISITE
10248636   12879929  Fri Aug 16 15:47:17 CST 2013   DATABASE PSU 10.2.0.4.7 (REQUIRES PRE-REQUISITE
11724977   12879929  Fri Aug 16 15:47:17 CST 2013   DATABASE PSU 10.2.0.4.8 (REQUIRES PRE-REQUISITE
12419397   12879929  Fri Aug 16 15:47:17 CST 2013   DATABASE PSU 10.2.0.4.9 (REQUIRES PRE-REQUISITE
12827778   12879929  Fri Aug 16 15:47:17 CST 2013   DATABASE PSU 10.2.0.4.10 (REQUIRES PRE-REQUISITE
12879929   12879929  Fri Aug 16 15:47:17 CST 2013   DATABASE PATCH SET UPDATE 10.2.0.4.11 (PRE-REQ
8576156    9352164   Fri Aug 16 13:47:48 CST 2013   DATABASE PSU 10.2.0.4.1 (INCLUDES CPUJUL2009)
8833280    9352164   Fri Aug 16 13:47:48 CST 2013   DATABASE PSU 10.2.0.4.2 (INCLUDES CPUOCT2009)
9119284    9352164   Fri Aug 16 13:47:48 CST 2013   DATABASE PSU 10.2.0.4.3 (INCLUDES CPUJAN2010)
9352164    9352164   Fri Aug 16 13:47:48 CST 2013   DATABASE PSU 10.2.0.4.4 (INCLUDES CPUAPR2010)

另外的方法是查看registry$history表。

SQL> select action,comments from registry$history;

ACTION                         COMMENTS
------------------------------ -------------------------
APPLY                          PSU 10.2.0.4.4
CPU                            view recompilation
CPU                            view recompilation
APPLY                          PSU 10.2.0.4.4
ROLLBACK                       PSU 10.2.0.4.4
ROLLBACK                       PSU 10.2.0.4.4
APPLY                          PSU 10.2.0.4.4
APPLY                          PSU 10.2.0.4.4
CPU                            view recompilation
APPLY                          PSU 10.2.0.4.11

 

四、配置了Goldengate环境打PSU补丁注意事项

注意，如果数据库配置了GG，在安装补丁，apply psu的时候，会报如下这种错误：
ERROR at line 1:
ORA-00604: error occurred at recursive SQL level 1
ORA-04045: errors during recompilation/revalidation of GGS.DDLREPLICATION
ORA-04064: not executed, invalidated
ORA-04064: not executed, invalidated package body "GGS.DDLREPLICATION"
ORA-06508: PL/SQL: could not find program unit being called:
"GGS.DDLREPLICATION"
ORA-06512: at line 60
ORA-06508: PL/SQL: could not find program unit being called:
"GGS.DDLREPLICATION"
ORA-06512: at line 60

解决方法：关闭ogg的ddl捕获
因为ddl捕获触发器，导致这些操作失败
ddl_disable.sql
升级完成后，开启ddl捕获触发器
ddl_enable.sql