WRH$_ACTIVE_SESSION_HISTORY打补丁14084247实现自动分区
环境:
OS:Centos 7
DB:11.2.0.4
在11.2.0.4版本中WRH$_ACTIVE_SESSION_HISTORY不会自动分区,需要手工执行语句(alter session set "_swrf_test_action" = 72)
才会创建一个新的分区,保留时间过期后系统会自动清理该分区的数据,这里是一个Bug,需要打补丁14084247
不会自动分区的信息如下:
SQL> connect / as sysdba
SQL> set linesize 1000;
SQL> SELECT owner,
2 segment_name,
3 partition_name,
4 segment_type,
5 bytes/1024/1024/1024 Size_GB
6 FROM dba_segments
7 WHERE segment_name='WRH$_ACTIVE_SESSION_HISTORY';
OWNER SEGMENT_NAME PARTITION_NAME SEGMENT_TYPE SIZE_GB
----------------------------------------------------------------------------------------------------
SYS WRH$_ACTIVE_SESSION_HISTORY WRH$_ACTIVE_2510643529_0 TABLE PARTITION 8.921875
SYS WRH$_ACTIVE_SESSION_HISTORY WRH$_ACTIVE_SES_MXDB_MXSN TABLE PARTITION .000061035
1.下载补丁
https://support.oracle.com/epmos/faces/PatchSearchResults?_adf.ctrl-state=mibbkwijr_4&_afrLoop=91369577477188
下载地址,这里需要mos账号才能下载
根据相应的数据库版本和操作系统选择对应的版本下载,我这里下载的是p14084247_112040_Linux-x86-64.zip
2.将软件包上传到数据库服务器并修改权限
[root@dbmaster soft]# mkdir /soft/patch
将软件包上传到该目录下,然后将该目录权限授予oracle账号
[root@dbmaster soft]# chown -R oracle:oinstall /soft/patch
3.切换到oracle账号下解压
[oracle@dbmaster patch]$ unzip p14084247_112040_Linux-x86-64.zip
4.阅读README.txt
[oracle@dbmaster 14084247]$ more README.txt
5.检查是否有冲突
README.txt里的脚本
$ cd <PATCH_TOP_DIR>/14084247
$ opatch prereq CheckConflictAgainstOHWithDetail -ph ./
实际的脚本
[oracle@dbmaster 14084247]$ cd /soft/patch/14084247
[oracle@dbmaster 14084247]$ /u01/app/oracle/product/11.2.0.4/db_1/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.4/db_1
Central Inventory : /u01/app/oraInventory
from : /u01/app/oracle/product/11.2.0.4/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.4/db_1/cfgtoollogs/opatch/opatch2021-08-30_02-30-08AM_1.log
Invoking prereq "checkconflictagainstohwithdetail"
Prereq "checkConflictAgainstOHWithDetail" passed.
OPatch succeeded.
6.查看当前系统已经打的补丁
[oracle@dbmaster 14084247]$ /u01/app/oracle/product/11.2.0.4/db_1/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/oracle/product/11.2.0.4/db_1
Central Inventory : /u01/app/oraInventory
from : /u01/app/oracle/product/11.2.0.4/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.4/db_1/cfgtoollogs/opatch/opatch2021-08-30_02-42-48AM_1.log
Lsinventory Output file location : /u01/app/oracle/product/11.2.0.4/db_1/cfgtoollogs/opatch/lsinv/lsinventory2021-08-30_02-42-48AM.txt
--------------------------------------------------------------------------------
Installed Top-level Products (1):
Oracle Database 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.
--------------------------------------------------------------------------------
OPatch succeeded.
7.打补丁
该补丁可以Offline Mode和Online Mode,我们这里选择Online Mode模式安装
(2.1) Installing in Offline Mode
-----------------------------------
To install the patch, follow these steps:
1. Set your current directory to the directory where the patch is located and then run the OPatch utility by entering the followi
ng commands:
$ cd <PATCH_TOP_DIR>/14084247
$ opatch apply
2. Verify whether the patch has been successfully installed by running the following command:
$ opatch lsinventory
3. Start the services from the Oracle home.
(2.2) Installing in Online Mode
-----------------------------------
To install the patch in online mode, follow these steps:
1. Set your current directory to the directory where the patch is located and then run the OPatch utility by entering the followi
ng commands:
$ cd <PATCH_TOP_DIR>/14084247
2. Install the patch by running the following command:
- For Non-RAC Environments (Standalone Databases):
$ opatch apply online -connectString <SID>:<USERNAME>:<PASSWORD>:
- For RAC Environments:
$ opatch apply online -connectString <SID_Node1>:<Username_Node1>:<Password_Node1>:<Node1_Name>,<SID_Node2>:<Username_
Node2>:<Password_Node2>:<Node2_Name>,<SID_NodeN>:<Username_NodeN>:<Password_NodeN>:<NodeN_Name>
我这里是单机模式,使用的语法为:
$ opatch apply online -connectString <SID>:<USERNAME>:<PASSWORD>:
RAC环境语法:
For RAC Environments:
$opatch apply online -connectString slnngk1:sys:oracle:rac01,slnngk2:sys:oracle:rac02
[oracle@dbmaster 14084247]$ cd /soft/patch/14084247
[oracle@dbmaster 14084247]$ /u01/app/oracle/product/11.2.0.4/db_1/OPatch/opatch apply online -connectString slnngk:sys:oracle
Oracle Interim Patch Installer version 11.2.0.3.4
Copyright (c) 2012, Oracle Corporation. All rights reserved.
Oracle Home : /u01/app/oracle/product/11.2.0.4/db_1
Central Inventory : /u01/app/oraInventory
from : /u01/app/oracle/product/11.2.0.4/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.4/db_1/cfgtoollogs/opatch/14084247_Aug_30_2021_02_43_45/apply2021-08-30_02-43-44AM_1.log
The patch should be applied/rolled back in '-all_nodes' mode only.
Converting the RAC mode to '-all_nodes' mode.
Applying interim patch '14084247' to OH '/u01/app/oracle/product/11.2.0.4/db_1'
Verifying environment and performing prerequisite checks...
All checks passed.
Backing up files...
Patching component oracle.rdbms, 11.2.0.4.0...
Installing and enabling the online patch 'bug14084247.pch', on database 'slnngk'.
Verifying the update...
Patch 14084247 successfully applied
Log file location: /u01/app/oracle/product/11.2.0.4/db_1/cfgtoollogs/opatch/14084247_Aug_30_2021_02_43_45/apply2021-08-30_02-43-44AM_1.log
OPatch succeeded.
8.再次查看打补丁情况
[oracle@dbmaster 14084247]$ /u01/app/oracle/product/11.2.0.4/db_1/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/oracle/product/11.2.0.4/db_1
Central Inventory : /u01/app/oraInventory
from : /u01/app/oracle/product/11.2.0.4/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.4/db_1/cfgtoollogs/opatch/opatch2021-08-30_02-44-32AM_1.log
Lsinventory Output file location : /u01/app/oracle/product/11.2.0.4/db_1/cfgtoollogs/opatch/lsinv/lsinventory2021-08-30_02-44-32AM.txt
--------------------------------------------------------------------------------
Installed Top-level Products (1):
Oracle Database 11g 11.2.0.4.0
There are 1 products installed in this Oracle Home.
Interim patches (1) :
Patch (online) 14084247: applied on Mon Aug 30 02:43:51 EDT 2021
Unique Patch ID: 17159649
Created on 5 Feb 2014, 05:35:15 hrs
Bugs fixed:
14084247
--------------------------------------------------------------------------------
OPatch succeeded.
[oracle@dbmaster 14084247]$ /u01/app/oracle/product/11.2.0.4/db_1/OPatch/opatch lspatches
14084247;
【推荐】国内首个AI IDE，深度理解中文开发场景，立即下载体验Trae
【推荐】编程新体验，更懂你的AI，立即体验豆包MarsCode编程助手
【推荐】抖音旗下AI助手豆包，你的智能百科全书，全免费不限次数
【推荐】轻量又高性能的 SSH 工具 IShell：AI 加持，快人一步
· AI与.NET技术实操系列（二）：开始使用ML.NET
· 记一次.NET内存居高不下排查解决与启示
· 探究高空视频全景AR技术的实现原理
· 理解Rust引用及其生命周期标识（上）
· 浏览器原生「磁吸」效果！Anchor Positioning 锚点定位神器解析
· DeepSeek 开源周回顾「GitHub 热点速览」
· 物流快递公司核心技术能力-地址解析分单基础技术分享
· .NET 10首个预览版发布：重大改进与新特性概览！
· AI与.NET技术实操系列（二）：开始使用ML.NET
· 单线程的Redis速度为什么快？