文档课题：oracle 11.2.0.4安装201020补丁
系统：rhel 7.9
主机名：主库 OEL791 备库 OEL792
1、升级前备份数据库
RMAN> run {
allocate channel d1 type disk;
allocate channel d2 type disk;
allocate channel d3 type disk;
allocate channel d4 type disk;
backup as compressed backupset database format '/home/oracle/rman/data_%d_%T_%s.bak' plus archivelog format '/home/oracle/rman/arch_%d_%T_%s.bak';
release channel d1;
release channel d2;
release channel d3;
release channel d4;
}
2、升级包
说明：主备库均上传
sftp> cd /soft
sftp> lcd F:\BaiduNetdiskDownload\2110最新补丁集-文档2118136.2\11GR2-201020\OPatch
sftp> put p6880880_112000_Linux-x86-64.zip
[root@OEL791 /]# chown -R oracle:oinstall /soft
[root@OEL791 soft]# su - oracle -c "unzip -q -o /soft/p6880880_112000_Linux-x86-64.zip -d /u01/app/oracle/product/11.2.0/db_1/"
[oracle@OEL791 OPatch]$ opatch version
OPatch Version: 11.2.0.3.31
OPatch succeeded.
[root@OEL791 soft]# su - oracle -c "unzip -q -o /soft/p31537677_112040_Linux-x86-64.zip -d /soft"
3、检查补丁
[oracle@OEL791 31537677]$ $ORACLE_HOME/OPatch/opatch prereq CheckConflictAgainstOHWithDetail -ph ./
Oracle Interim Patch Installer version 11.2.0.3.4
Copyright (c) 2012, Oracle Corporation. All rights reserved.
PREREQ session
Oracle Home : /u01/app/oracle/product/11.2.0/db_1
Central Inventory : /u01/app/oraInventory
from : /u01/app/oracle/product/11.2.0/db_1/oraInst.loc
OPatch version : 11.2.0.3.4
OUI version : 11.2.0.4.0
Log file location : /u01/app/oracle/product/11.2.0/db_1/cfgtoollogs/opatch/opatch2022-03-28_13-42-03PM_1.log
Invoking prereq "checkconflictagainstohwithdetail"
Prereq "checkConflictAgainstOHWithDetail"not executed
Unable to create Patch Object.
Exception occured : Version field of the component "delete" in actions file cannot be <null> or empty. Please check patch metadata.
OPatch succeeded.
[oracle@OEL792 31537677]$ $ORACLE_HOME/OPatch/opatch prereq CheckConflictAgainstOHWithDetail -ph ./
Oracle Interim Patch Installer version 11.2.0.3.4
Copyright (c) 2012, Oracle Corporation. All rights reserved.
PREREQ session
Oracle Home : /u01/app/oracle/product/11.2.0/db_1
Central Inventory : /u01/app/oraInventory
from : /u01/app/oracle/product/11.2.0/db_1/oraInst.loc
OPatch version : 11.2.0.3.4
OUI version : 11.2.0.4.0
Log file location : /u01/app/oracle/product/11.2.0/db_1/cfgtoollogs/opatch/opatch2022-03-28_13-43-19PM_1.log
Invoking prereq "checkconflictagainstohwithdetail"
Prereq "checkConflictAgainstOHWithDetail"not executed
Unable to create Patch Object.
Exception occured : Version field of the component "delete" in actions file cannot be <null> or empty. Please check patch metadata.
OPatch succeeded.
4、升级
说明：升级前关闭数据库、监听。
先打备库补丁，再打主库，打完后在主库跑脚本，改变数据库结构，数据自然会传到备库。
主库执行：
SQL> alter system set log_archive_dest_state_2=defer scope=both sid='*';
4.1、备库应用opatch
备库执行：
SQL> select open_mode from v$database;
OPEN_MODE
----------------------------------------
READ ONLY WITH APPLY
SQL> alter database recover managed standby database cancel;
Database altered.
SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
[oracle@OEL792 db_1]$ lsnrctl stop
LSNRCTL for Linux: Version 11.2.0.4.0 - Production on 29-MAR-2022 13:41:14
Copyright (c) 1991, 2013, Oracle. All rights reserved.
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=192.168.133.151)(PORT=1521)))
The command completed successfully
[oracle@OEL792 OPatch]$ ./opatch apply /soft/31537677
[oracle@OEL792 OPatch]$ lsnrctl start
LSNRCTL for Linux: Version 11.2.0.4.0 - Production on 29-MAR-2022 15:10:08
Copyright (c) 1991, 2013, Oracle. All rights reserved.
Starting /u01/app/oracle/product/11.2.0/db_1/bin/tnslsnr: please wait...
TNSLSNR for Linux: Version 11.2.0.4.0 - Production
System parameter file is /u01/app/oracle/product/11.2.0/db_1/network/admin/listener.ora
Log messages written to /u01/app/oracle/diag/tnslsnr/OEL792/listener/alert/log.xml
Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=192.168.133.151)(PORT=1521)))
Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521)))
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=192.168.133.151)(PORT=1521)))
STATUS of the LISTENER
------------------------
Alias LISTENER
Version TNSLSNR for Linux: Version 11.2.0.4.0 - Production
Start Date 29-MAR-2022 15:10:28
Uptime 0 days 0 hr. 0 min. 40 sec
Trace Level off
Security ON: Local OS Authentication
SNMP OFF
Listener Parameter File /u01/app/oracle/product/11.2.0/db_1/network/admin/listener.ora
Listener Log File /u01/app/oracle/diag/tnslsnr/OEL792/listener/alert/log.xml
Listening Endpoints Summary...
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=192.168.133.151)(PORT=1521)))
(DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521)))
Services Summary...
Service "orcl151" has 1 instance(s).
Instance "orcl151", status UNKNOWN, has 1 handler(s) for this service...
The command completed successfully
[oracle@OEL792 OPatch]$ sqlplus / as sysdba
SQL*Plus: Release 11.2.0.4.0 Production on Tue Mar 29 15:11:04 2022
Copyright (c) 1982, 2013, Oracle. All rights reserved.
Connected to an idle instance.
SQL> startup mount
ORACLE instance started.
Total System Global Area 3206836224 bytes
Fixed Size 2257520 bytes
Variable Size 1811942800 bytes
Database Buffers 1375731712 bytes
Redo Buffers 16904192 bytes
Database mounted.
注意：如果使用了DG broker，需通过其修改state=APPLY-OFF,以避免DG broker自动启动MRP.
4.2、主库应用Patch
[oracle@OEL791 ~]$ lsnrctl stop
LSNRCTL for Linux: Version 11.2.0.4.0 - Production on 29-MAR-2022 15:16:50
Copyright (c) 1991, 2013, Oracle. All rights reserved.
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=192.168.133.150)(PORT=1521)))
The command completed successfully
[oracle@OEL791 ~]$ sqlplus / as sysdba
SQL*Plus: Release 11.2.0.4.0 Production on Tue Mar 29 15:17:14 2022
Copyright (c) 1982, 2013, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
[oracle@OEL791 ~]$ opatch version
OPatch Version: 11.2.0.3.31
OPatch succeeded.
[oracle@OEL791 soft]$ cd $ORACLE_HOME/OPatch
[oracle@OEL791 OPatch]$ ./opatch apply /soft/31537677
Oracle Interim Patch Installer version 11.2.0.3.31
Copyright (c) 2022, Oracle Corporation. All rights reserved.
Oracle Home : /u01/app/oracle/product/11.2.0/db_1
Central Inventory : /u01/app/oraInventory
from : /u01/app/oracle/product/11.2.0/db_1/oraInst.loc
OPatch version : 11.2.0.3.31
OUI version : 11.2.0.4.0
Log file location : /u01/app/oracle/product/11.2.0/db_1/cfgtoollogs/opatch/opatch2022-03-29_15-19-50PM_1.log
Verifying environment and performing prerequisite checks...
Do you want to proceed? [y|n]
y
User Responded with: Y
All checks passed.
Please shutdown Oracle instances running out of this ORACLE_HOME on the local system.
(Oracle Home = '/u01/app/oracle/product/11.2.0/db_1')
Is the local system ready for patching? [y|n]
y
User Responded with: Y
Backing up files...
/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.
/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.
/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/db_1/cfgtoollogs/opatch/opatch2022-03-29_15-19-50PM_1.log
OPatch completed with warnings.
4.3、升级/修补RDBMS及字典对象
[oracle@OEL791 admin]$ cd $ORACLE_HOME/rdbms/admin
[oracle@OEL791 admin]$ sqlplus / as sysdba
SQL*Plus: Release 11.2.0.4.0 Production on Tue Mar 29 15:27:08 2022
Copyright (c) 1982, 2013, Oracle. All rights reserved.
Connected to an idle instance.
SQL> startup restrict;
ORACLE instance started.
Total System Global Area 3206836224 bytes
Fixed Size 2257520 bytes
Variable Size 1811942800 bytes
Database Buffers 1375731712 bytes
Redo Buffers 16904192 bytes
Database mounted.
Database opened.
SQL> @catbundle psu apply
…
25 end;
26 commit;
27 end;
28 END LOOP;
29 close query_crs;
30 end;
31 /
PL/SQL procedure successfully completed.
SQL>
SQL>
SQL> ALTER SESSION SET current_schema = SYS;
Session altered.
SQL> PROMPT Updating registry...
Updating registry...
SQL> INSERT INTO registry$history
2 (action_time, action,
3 namespace, version, id,
4 bundle_series, comments)
5 VALUES
6 (SYSTIMESTAMP, 'APPLY',
7 SYS_CONTEXT('REGISTRY$CTX','NAMESPACE'),
8 '11.2.0.4',
9 201020,
10 'PSU',
11 'PSU 11.2.0.4.201020');
1 row created.
SQL> COMMIT;
Commit complete.
SQL> SPOOL off
SQL> SET echo off
Check the following log file for errors:
/u01/app/oracle/cfgtoollogs/catbundle/catbundle_PSU_ORCL150_APPLY_2022Mar29_15_28_06.log
4.4、主库重建 DG环境
[oracle@OEL791 admin]$ lsnrctl start
LSNRCTL for Linux: Version 11.2.0.4.0 - Production on 29-MAR-2022 15:38:23
Copyright (c) 1991, 2013, Oracle. All rights reserved.
Starting /u01/app/oracle/product/11.2.0/db_1/bin/tnslsnr: please wait...
TNSLSNR for Linux: Version 11.2.0.4.0 - Production
System parameter file is /u01/app/oracle/product/11.2.0/db_1/network/admin/listener.ora
Log messages written to /u01/app/oracle/diag/tnslsnr/OEL791/listener/alert/log.xml
Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=192.168.133.150)(PORT=1521)))
Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521)))
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=192.168.133.150)(PORT=1521)))
STATUS of the LISTENER
------------------------
Alias LISTENER
Version TNSLSNR for Linux: Version 11.2.0.4.0 - Production
Start Date 29-MAR-2022 15:38:23
Uptime 0 days 0 hr. 0 min. 0 sec
Trace Level off
Security ON: Local OS Authentication
SNMP OFF
Listener Parameter File /u01/app/oracle/product/11.2.0/db_1/network/admin/listener.ora
Listener Log File /u01/app/oracle/diag/tnslsnr/OEL791/listener/alert/log.xml
Listening Endpoints Summary...
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=192.168.133.150)(PORT=1521)))
(DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521)))
Services Summary...
Service "orcl150" has 1 instance(s).
Instance "orcl150", status UNKNOWN, has 1 handler(s) for this service...
The command completed successfully
强制主节点向监听注册服务
SQL> alter system register;
System altered.
在单个实例（非 RAC）中禁用受限会话以允许终端连接
SQL> alter system disable restricted session;
System altered.
SQL> exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
重新启用到备库的日志传送
SQL> alter system set log_archive_dest_state_2=enable scope=both sid='*';
System altered.
备库执行
SQL> alter database open;
Database altered.
SQL> select open_mode from v$database;
OPEN_MODE
----------------------------------------
READ ONLY
SQL> alter database recover managed standby database using current logfile disconnect from session;
Database altered.
SQL> select open_mode from v$database;
OPEN_MODE
----------------------------------------
READ ONLY WITH APPLY
SQL> select max(sequence#) from v$archived_log;
MAX(SEQUENCE#)
--------------
299
SQL> r
1* select sequence#,to_char(first_time,'yyyy-mm-dd hh24:mi:ss'),to_char(next_time,'yyyy-mm-dd hh24:mi:ss'),applied from v$archived_log order by 1
SEQUENCE# TO_CHAR(FIRST_TIME,'YYYY-MM-DDHH24:MI: TO_CHAR(NEXT_TIME,'YYYY-MM-DDHH24:MI:S APPLIED
---------- -------------------------------------- -------------------------------------- ------------------
289 2022-03-29 10:26:02 2022-03-29 11:21:12 YES
290 2022-03-29 11:21:12 2022-03-29 11:21:44 YES
291 2022-03-29 11:21:44 2022-03-29 11:22:21 YES
292 2022-03-29 11:22:21 2022-03-29 11:23:29 YES
293 2022-03-29 11:23:29 2022-03-29 13:22:53 YES
294 2022-03-29 13:22:53 2022-03-29 13:23:00 YES
295 2022-03-29 13:23:00 2022-03-29 15:27:23 YES
296 2022-03-29 15:27:23 2022-03-29 15:27:28 YES
297 2022-03-29 15:27:28 2022-03-29 15:28:13 YES
298 2022-03-29 15:28:13 2022-03-29 15:28:34 YES
299 2022-03-29 15:28:34 2022-03-29 15:45:41 IN-MEMORY
11 rows selected.
主库执行：
SQL> select max(sequence#) from v$archived_log;
MAX(SEQUENCE#)
--------------
299
5、主备库验证补丁是否成功应用
主库
[oracle@OEL791 ~]$ opatch lspatches
31537677;Database Patch Set Update : 11.2.0.4.201020 (31537677)
OPatch succeeded.
备库
[oracle@OEL792 OPatch]$ opatch lspatches
31537677;Database Patch Set Update : 11.2.0.4.201020 (31537677)
OPatch succeeded.
检查注册表历史升级记录
SQL> r
1* select action_time,action,namespace,version,bundle_series,id from registry$history
ACTION_TIME ACTION NAMESPACE VERSION BUNDLE_SERIES ID
------------------------------ -------------------- -------------------- -------------------- -------------------- ----------
24-AUG-13 12.03.45.119862 PM APPLY SERVER 11.2.0.4 PSU 0
07-OCT-21 11.25.40.878982 AM APPLY SERVER 11.2.0.4 PSU 0
29-MAR-22 03.28.39.351894 PM APPLY SERVER 11.2.0.4 PSU 201020
