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;
