目录
-
-
-
-
- 0、核心步骤
- 1、检查环境
- 2、下载并上传PSU
- 3、备份`GRID_HOME`、`ORACLE_HOME`
- 4、更新`OPatch`
- 5、备库打补丁
- 6、主库打补丁
- 7、备库重新恢复应用
-
-
-
0、核心步骤
1.主库禁用日志传送到备库
2.关闭备库，应用补丁，但不执行脚本(catpatch.sql等)，启动到mount，不启用日志恢复
3.关闭主库，应用补丁，执行脚本(run catpatch/catbundle/catcpu等)
4.启动主库，重新开启日志传送到备库
5.备库开启日志恢复
6.检查补丁应用情况
1、检查环境
RAC
一节点
[[email protected] ~]$ crsctl status res -t
--------------------------------------------------------------------------------
NAME TARGET STATE SERVER STATE_DETAILS
--------------------------------------------------------------------------------
Local Resources
--------------------------------------------------------------------------------
ora.ARC.dg
ONLINE ONLINE racbj01
ONLINE ONLINE racbj02
ora.DATA.dg
ONLINE ONLINE racbj01
ONLINE ONLINE racbj02
ora.LISTENER.lsnr
ONLINE ONLINE racbj01
ONLINE ONLINE racbj02
ora.OCR.dg
ONLINE ONLINE racbj01
ONLINE ONLINE racbj02
ora.asm
ONLINE ONLINE racbj01 Started
ONLINE ONLINE racbj02 Started
ora.gsd
OFFLINE OFFLINE racbj01
OFFLINE OFFLINE racbj02
ora.net1.network
ONLINE ONLINE racbj01
ONLINE ONLINE racbj02
ora.ons
ONLINE ONLINE racbj01
ONLINE ONLINE racbj02
--------------------------------------------------------------------------------
Cluster Resources
--------------------------------------------------------------------------------
ora.LISTENER_SCAN1.lsnr
1 ONLINE ONLINE racbj02
ora.cvu
1 ONLINE ONLINE racbj02
ora.oc4j
1 ONLINE ONLINE racbj02
ora.racbj01.vip
1 ONLINE ONLINE racbj01
ora.racbj02.vip
1 ONLINE ONLINE racbj02
ora.racdb.db
1 ONLINE ONLINE racbj01 Open
2 ONLINE ONLINE racbj02 Open
ora.scan1.vip
1 ONLINE ONLINE racbj02
[[email protected] ~]$ srvctl status nodeapps
VIP racbj01vip is enabled
VIP racbj01vip is running on node: racbj01
VIP racbj02vip is enabled
VIP racbj02vip is running on node: racbj02
Network is enabled
Network is running on node: racbj01
Network is running on node: racbj02
GSD is disabled
GSD is not running on node: racbj01
GSD is not running on node: racbj02
ONS is enabled
ONS daemon is running on node: racbj01
ONS daemon is running on node: racbj02
[[email protected] ~]$ lsnrctl status
LSNRCTL for Linux: Version 11.2.0.4.0 - Production on 30-MAR-2021 15:47:51
Copyright (c) 1991, 2013, Oracle. All rights reserved.
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=LISTENER)))
STATUS of the LISTENER
------------------------
Alias LISTENER
Version TNSLSNR for Linux: Version 11.2.0.4.0 - Production
Start Date 25-MAR-2021 14:32:08
Uptime 5 days 1 hr. 15 min. 43 sec
Trace Level off
Security ON: Local OS Authentication
SNMP OFF
Listener Parameter File /u01/app/11.2.0/grid/network/admin/listener.ora
Listener Log File /u01/app/grid/diag/tnslsnr/racbj01/listener/alert/log.xml
Listening Endpoints Summary...
(DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=LISTENER)))
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=10.107.174.164)(PORT=1521)))
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=10.107.174.167)(PORT=1521)))
Services Summary...
Service "+ASM" has 1 instance(s).
Instance "+ASM1", status READY, has 1 handler(s) for this service...
Service "racdb" has 1 instance(s).
Instance "racdb1", status READY, has 1 handler(s) for this service...
Service "racdbXDB" has 1 instance(s).
Instance "racdb1", status READY, has 1 handler(s) for this service...
The command completed successfully
备库检查
SQL> select process,client_process,THREAD#,sequence#,status from v$managed_standby;
PROCESS CLIENT_PROCESS THREAD# SEQUENCE# STATUS
--------------------------- ------------------------ ---------- ---------- ------------------------------------
ARCH ARCH 2 65 CLOSING
ARCH ARCH 0 0 CONNECTED
ARCH ARCH 1 141 CLOSING
ARCH ARCH 1 140 CLOSING
ARCH ARCH 1 143 CLOSING
ARCH ARCH 1 142 CLOSING
ARCH ARCH 2 64 CLOSING
ARCH ARCH 2 66 CLOSING
RFS UNKNOWN 0 0 IDLE
RFS UNKNOWN 0 0 IDLE
RFS ARCH 0 0 IDLE
RFS ARCH 0 0 IDLE
RFS LGWR 1 144 IDLE
RFS UNKNOWN 0 0 IDLE
RFS LGWR 2 67 IDLE
MRP0 N/A 2 67 APPLYING_LOG
RFS UNKNOWN 0 0 IDLE
17 rows selected.
SQL> select database_role,open_mode from v$database;
DATABASE_ROLE OPEN_MODE
------------------------------------------------ ------------------------------------------------------------
PHYSICAL STANDBY READ ONLY WITH APPLY
主库建一个测试表，查看是否实时同步
SYS@racdb1> create table t1 (id int,name varchar2(20));
Table created.
SYS@racdb1> insert into t1 values(1,'zhangsan');
1 row created.
SYS@racdb1> commit;
Commit complete.
SYS@racdb1> select * from t1;
ID NAME
---------- ------------------------------------------------------------
1 zhangsan
备库查询
SQL> select * from t1;
ID NAME
---------- ------------------------------------------------------------
1 zhangsan
每个节点的
grid
、
Oracle
执行下面的命令，以下输出就是没有打过补丁，为了节省篇幅，每个节点的输出就不都粘贴出来了
[[email protected] ~]$ $ORACLE_HOME/OPatch/opatch lspatches
There are no Interim patches installed in this Oracle Home.
[[email protected] ~]$ $ORACLE_HOME/OPatch/opatch lsinventory
Oracle Interim Patch Installer version 11.2.0.3.4
Copyright (c) 2012, Oracle Corporation. All rights reserved.
Oracle Home : /u01/app/11.2.0/grid
Central Inventory : /u01/app/oraInventory
from : /u01/app/11.2.0/grid/oraInst.loc
OPatch version : 11.2.0.3.4
OUI version : 11.2.0.4.0
Log file location : /u01/app/11.2.0/grid/cfgtoollogs/opatch/opatch2021-03-30_16-28-06PM_1.log
Lsinventory Output file location : /u01/app/11.2.0/grid/cfgtoollogs/opatch/lsinv/lsinventory2021-03-30_16-28-06PM.txt
--------------------------------------------------------------------------------
Installed Top-level Products (1):
Oracle Grid Infrastructure 11g 11.2.0.4.0
There are 1 products installed in this Oracle Home.
There are no Interim patches installed in this Oracle Home.
Rac system comprising of multiple nodes
Local node = racbj01
Remote node = racbj02
--------------------------------------------------------------------------------
OPatch succeeded.
2、下载并上传PSU
通常情况下
GI PSU
包含
DB PSU
；
DB PSU
包含
CPU
实际情况以
readme
为准
这里我们应用最新的PSU
GI PSU31718723
（当前时间
20210330
）
三个节点分别执行
[[email protected] ~]# mkdir /u01/soft
[[email protected] ~]# chown -R oracle:oinstall /u01/soft
三个节点执行，不都列出了，注意这里的权限是
root
，一会要改成
grid:oinstall
[[email protected] ~]$ cd /u01/soft/
[[email protected] soft]$ ll
total 1406064
-rw-r--r-- 1 root root 1319414278 Mar 30 12:19 p31718723_112040_Linux-x86-64.zip
-rw-r--r-- 1 root root 120388005 Sep 9 2020 p6880880_112000_Linux-x86-64.zip
3、备份 GRID_HOME
ORACLE_HOME
GRID_HOME
ORACLE_HOME
备份之前要先关闭实例和集群（一个一个来，不要影响业务）
srvctl stop instance -d databasename -n hostname
crsctl stop crs
删除
grid
和
oracle
用户下的
audit
文件（审计文件）和
xml
文件（
alert
日志的
xml
格式）
grid
：
cd $ORACLE_HOME/rdbms/audit
find . -mtime +7 -name "*.aud" | xargs rm -rf
cd $ORACLE_BASE/diag/asm/+asm/+ASM1/alert/
find . -mtime +7 -name "*.xml" | xargs rm -rf
oracle
：
cd $ORACLE_HOME/rdbms/audit
find . -mtime +7 -name "*.aud" | xargs rm -rf
cd $ORACLE_BASE/diag/rdbms/racdb/racdb1/alert/
find . -mtime +7 -name "*.xml" | xargs rm -rf
三个节点备份
GRID_HOME
、
ORACLE_HOME
，节省篇幅，只粘贴一个节点的结果，单实例
dg
没有
grid
就不用做相应的操作
备份
gi_home
时间有点长，耐心等待
[[email protected] ~]# cd /u01/app/11.2.0
[[email protected] ~]# tar -pzcvf gihome.tar.gz grid
备份
ora_home
[[email protected] ~]# cd /u01/app/oracle/product/11.2.0
[[email protected] ~]# tar -pzcvf orahome.tar.gz db_1
4、更新 OPatch
OPatch
备份原来的
OPatch
，安装需要的
OPatch
版本，只列一个节点，所有节点都要操作
[[email protected] ~]# mv /u01/app/11.2.0/grid/OPatch/ /u01/app/11.2.0/grid/OPatch_bak
[[email protected] ~]# mv /u01/app/oracle/product/11.2.0/db_1/OPatch/ /u01/app/oracle/product/11.2.0/db_1/OPatch_bak
[[email protected] ~]# chmod 755 /u01/soft/p6880880_112000_Linux-x86-64.zip
[[email protected] ~]# unzip /u01/soft/p6880880_112000_Linux-x86-64.zip -d /u01/app/11.2.0/grid
[[email protected] ~]# unzip /u01/soft/p6880880_112000_Linux-x86-64.zip -d /u01/app/oracle/product/11.2.0/db_1/
[[email protected] ~]# chown -R grid:oinstall /u01/app/11.2.0/grid/OPatch
[[email protected] ~]# chown -R oracle:oinstall /u01/app/oracle/product/11.2.0/db_1/OPatch
验证一下
OPatch
版本
[[email protected] ~]$ /u01/app/11.2.0/grid/OPatch/opatch version
OPatch Version: 11.2.0.3.25
OPatch succeeded.
[[email protected] ~]$ /u01/app/oracle/product/11.2.0/db_1/OPatch/opatch version
OPatch Version: 11.2.0.3.25
OPatch succeeded.
5、备库打补丁
在
RAC+DG
的环境中打
PSU
补丁，首先要在
DG
的备库端进行
PSU
补丁的安装，停止并
shutdown
所有
standby
数据库、停监听、关
EM
，然后打补丁，打完补丁之后不要执行
catbundle.sql psu apply
脚本。这个脚本应该在生产端执行然后通过
redo
传递至灾备端。
在主库上暂停向备库传日志
SYS@racdb2> alter system set log_archive_dest_state_2=defer scope=both sid='*';
System altered.
备库停库停监听
SQL> shu immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
[[email protected] 11.2.0]$ lsnrctl stop
解压
PSU
[[email protected] soft]# chown -R oracle:oinstall p31718723_112040_Linux-x86-64.zip
[[email protected] soft]$ unzip p31718723_112040_Linux-x86-64.zip
检测与当前环境是否冲突
[[email protected] soft]$ cd 31537677/
[[email protected] 31718723]$ $ORACLE_HOME/OPatch/opatch prereq CheckConflictAgainstOHWithDetail -ph ./
Oracle Interim Patch Installer version 11.2.0.3.25
Copyright (c) 2021, Oracle Corporation. All rights reserved.
PREREQ session
Oracle Home : /u01/app/oracle/product/11.2.0/dbhome_1
Central Inventory : /u01/app/oraInventory
from : /u01/app/oracle/product/11.2.0/dbhome_1/oraInst.loc
OPatch version : 11.2.0.3.25
OUI version : 11.2.0.4.0
Log file location : /u01/app/oracle/product/11.2.0/dbhome_1/cfgtoollogs/opatch/opatch2021-03-30_18-13-43PM_1.log
Invoking prereq "checkconflictagainstohwithdetail"
Prereq "checkConflictAgainstOHWithDetail" passed.
OPatch succeeded.
备库手动打补丁
[[email protected] soft]$ $ORACLE_HOME/OPatch/opatch napply -local /u01/soft/31537677/ -oh $ORACLE_HOME
/bin/ld: warning: -z nolazyload ignored.
/bin/ld: warning: -z lazyload ignored.
/bin/ld: warning: -z nolazyload ignored.
/bin/ld: warning: -z lazyload ignored.
/bin/ld: warning: -z nolazyload ignored.
/bin/ld: warning: -z lazyload ignored.
/bin/ld: warning: -z nolazyload ignored.
Composite patch 31537677 successfully applied.
OPatch Session completed with warnings.
Log file location: /u01/app/oracle/product/11.2.0/dbhome_1/cfgtoollogs/opatch/opatch2021-03-30_18-22-56PM_1.log
OPatch completed with warnings.
上面的警告是一个内部bug可以忽略，参考
http://www.sizhxy.com/NewsCenter/NewsDetail-247.html
备库
[[email protected] soft]$ $ORACLE_HOME/OPatch/opatch lspatches
31537677;Database Patch Set Update : 11.2.0.4.201020 (31537677)
OPatch succeeded.
[[email protected] soft]$ $ORACLE_HOME/OPatch/opatch lsinventory
--太长了，粘贴一部分，结果就是应用补丁成功了
22195465, 18440047, 19211433, 21515534, 20331945, 22686674, 18384537
18607546, 17254374, 18315328, 31172207, 23115139, 28790634, 21394225
16360112, 22195477, 17726838, 18510194, 17571306, 24766121, 17302277
21842740, 17551709, 26910644, 17634921, 28566241, 25489607, 16538760
--------------------------------------------------------------------------------
OPatch succeeded.
备库启动数据库到mount状态，启动监听
SQL> startup mount;
ORACLE instance started.
Total System Global Area 8.0973E+10 bytes
Fixed Size 2261968 bytes
Variable Size 1.1006E+10 bytes
Database Buffers 6.9793E+10 bytes
Redo Buffers 171487232 bytes
Database mounted.
如果主库是单实例的话，下面就是停主库，停监听，打补丁，执行脚本了，启动主库监听，启动传日志，然后打开备库的实时应用，验证没问题就可以了
alter system set log_archive_dest_state_X=enable scope=bothsid=’*’
我们这里主要讨论主库是
RAC
RAC
节点一修改权限（其他节点也要执行）
解压
[[email protected] ~]$ unzip /u01/soft/p31718723_112040_Linux-x86-64.zip -d /u01/soft
6、主库打补丁
Oracle 11g 新特性 --Online Patching （Hot Patching 热补丁）说明
http://blog.csdn.net/tianlesoftware/article/details/8214412
这里看一下RAC的Patch。如果是RAC的GI升级，或者是PSR，还是需要停机的。因为GI的升级需要停掉整个CRS。 但对数据库独立patch则可以采取滚动方式来安装。
GRID_HOME
和
ORACLE_HOME
都在本地并且没有配置ACFS就可以gi和
oracle
一起升级
节点一
[[email protected] soft]$ echo $ORACLE_HOME
/u01/app/11.2.0/grid
root
用户执行
[[email protected] ~]# /u01/app/11.2.0/grid/OPatch/opatch auto /u01/soft/31718723/
Executing /u01/app/11.2.0/grid/perl/bin/perl /u01/app/11.2.0/grid/OPatch/crs/patch11203.pl -patchdir /u01/soft -patchn 31718723 -paramfile /u01/app/11.2.0/grid/crs/install/crsconfig_params
This is the main log file: /u01/app/11.2.0/grid/cfgtoollogs/opatchauto2021-03-31_11-12-52.log
This file will show your detected configuration and all the steps that opatchauto attempted to do on your system:
/u01/app/11.2.0/grid/cfgtoollogs/opatchauto2021-03-31_11-12-52.report.log
2021-03-31 11:12:52: Starting Clusterware Patch Setup
Using configuration parameter file: /u01/app/11.2.0/grid/crs/install/crsconfig_params
Stopping RAC /u01/app/oracle/product/11.2.0/db_1 ...
Stopped RAC /u01/app/oracle/product/11.2.0/db_1 successfully
patch /u01/soft/31718723/29938455/custom/server/29938455 apply successful for home /u01/app/oracle/product/11.2.0/db_1
patch /u01/soft/31718723/31537677 apply successful for home /u01/app/oracle/product/11.2.0/db_1
Stopping CRS...
Stopped CRS successfully
patch /u01/soft/31718723/29938455 apply successful for home /u01/app/11.2.0/grid
patch /u01/soft/31718723/31537677 apply successful for home /u01/app/11.2.0/grid
patch /u01/soft/31718723/29509309 apply successful for home /u01/app/11.2.0/grid
Starting CRS...
Installing Trace File Analyzer
CRS-4123: Oracle High Availability Services has been started.
Starting RAC /u01/app/oracle/product/11.2.0/db_1 ...
Started RAC /u01/app/oracle/product/11.2.0/db_1 successfully
opatch auto succeeded.
看一下
log
有没有问题，然后
grid
用户和
Oracle
用户执行
opatch lspatches
、
opatch lsinventory
，没有问题的话
RAC
二节点开始打补丁
RAC
二节点（没有问题，输出跟上面一样，就不粘贴了）
其中一个节点执行注册到数据库就行
[[email protected] ~]# su - oracle
[[email protected] ~]$ cd $ORACLE_HOME/rdbms/admin
[[email protected] admin]$ sqlplus /nolog
@> conn / as sysdba
Connected.
SYS@racdb1> @catbundle.sql psu apply
SYS@racdb1> quit
SELECT *
FROM dba_registry_history
下面没有
invaild
的
SELECT *
FROM dba_registry
编译一下无效对象
SQL> @utlrp.sql
输出很长，前边略.......
DOC>#
ERRORS DURING RECOMPILATION
---------------------------
46
1 row selected.
Function created.
PL/SQL procedure successfully completed.
Function dropped.
PL/SQL procedure successfully completed.
7、备库重新恢复应用
主库（其中一个节点执行就行）
SYS@racdb2> alter system set log_archive_dest_state_2=enable scope=both sid='*';
System altered.
备库
SQL> alter database recover managed standby database using current logfile disconnect from session;
Database altered.
备库
alert
开始了介质恢复
alter database recover managed standby database using current logfile disconnect from session
Attempt to start background Managed Standby Recovery process (racdb)
Wed Mar 31 16:04:10 2021
MRP0 started with pid=39, OS id=31918
MRP0: Background Managed Standby Recovery process started (racdb)
started logmerger process
Wed Mar 31 16:04:15 2021
Managed Standby Recovery starting Real Time Apply
Parallel Media Recovery started with 32 slaves
Waiting for all non-current ORLs to be archived...
All non-current ORLs have been archived.
Media Recovery Log /u01/app/oracle/arch2_69_1067800873.arc
Media Recovery Log /u01/app/oracle/arch1_148_1067800873.arc
Media Recovery Log /u01/app/oracle/arch1_149_1067800873.arc
Completed: alter database recover managed standby database using current logfile disconnect from session
Media Recovery Log /u01/app/oracle/arch1_150_1067800873.arc
Media Recovery Log /u01/app/oracle/arch2_70_1067800873.arc
Media Recovery Log /u01/app/oracle/arch1_151_1067800873.arc
Media Recovery Log /u01/app/oracle/arch2_71_1067800873.arc
Media Recovery Log /u01/app/oracle/arch2_72_1067800873.arc
Media Recovery Log /u01/app/oracle/arch2_73_1067800873.arc
Media Recovery Log /u01/app/oracle/arch1_152_1067800873.arc
Media Recovery Log /u01/app/oracle/arch1_153_1067800873.arc
Media Recovery Log /u01/app/oracle/arch1_154_1067800873.arc
Media Recovery Log /u01/app/oracle/arch1_155_1067800873.arc
Media Recovery Log /u01/app/oracle/arch2_74_1067800873.arc
Media Recovery Log /u01/app/oracle/arch1_156_1067800873.arc
Media Recovery Log /u01/app/oracle/arch1_157_1067800873.arc
Media Recovery Log /u01/app/oracle/arch2_75_1067800873.arc
Media Recovery Log /u01/app/oracle/arch2_76_1067800873.arc
Media Recovery Log /u01/app/oracle/arch2_77_1067800873.arc
Media Recovery Log /u01/app/oracle/arch2_78_1067800873.arc
Wed Mar 31 16:04:25 2021
Media Recovery Log /u01/app/oracle/arch2_79_1067800873.arc
Media Recovery Log /u01/app/oracle/arch1_158_1067800873.arc
Media Recovery Log /u01/app/oracle/arch1_159_1067800873.arc
Media Recovery Log /u01/app/oracle/arch1_160_1067800873.arc
Media Recovery Log /u01/app/oracle/arch2_80_1067800873.arc
Media Recovery Waiting for thread 1 sequence 161 (in transit)
Recovery of Online Redo Log: Thread 1 Group 11 Seq 161 Reading mem 0
Mem# 0: /u01/app/oracle/redo/group_11.320.1068138875
Mem# 1: /u01/app/oracle/oradata/group_11.2952.1068138875
Media Recovery Waiting for thread 2 sequence 81 (in transit)
Recovery of Online Redo Log: Thread 2 Group 14 Seq 81 Reading mem 0
Mem# 0: /u01/app/oracle/redo/group_14.323.1068138891
Mem# 1: /u01/app/oracle/oradata/group_14.2958.1068138891
打开备库
SQL> alter database recover managed standby database cancel;
Database altered.
SQL> alter database open read only;
Database altered.
SQL> alter database recover managed standby database using current logfile disconnect from session;
Database altered.
查看备库状态
SQL> select process,client_process,thread#,sequence#,status from v$managed_standby;
PROCESS CLIENT_PROCESS THREAD# SEQUENCE# STATUS
--------------------------- ------------------------ ---------- ---------- ------------------------------------
ARCH ARCH 2 80 CLOSING
ARCH ARCH 0 0 CONNECTED
ARCH ARCH 1 158 CLOSING
ARCH ARCH 1 148 CLOSING
ARCH ARCH 1 159 CLOSING
ARCH ARCH 2 69 CLOSING
ARCH ARCH 1 160 CLOSING
ARCH ARCH 1 155 CLOSING
RFS ARCH 0 0 IDLE
RFS UNKNOWN 0 0 IDLE
RFS UNKNOWN 0 0 IDLE
RFS LGWR 1 161 IDLE
RFS ARCH 0 0 IDLE
RFS UNKNOWN 0 0 IDLE
RFS LGWR 2 81 IDLE
RFS UNKNOWN 0 0 IDLE
MRP0 N/A 1 161 APPLYING_LOG
RFS UNKNOWN 0 0 IDLE
18 rows selected.