ACD = Active Change Directory
ACFS = ASM Cluster File System
ADDM = Automatic Database Diagnostic Monitor
ADR = Automatic Diagnostic Repository
ADVM = ASM Dynamic Volume Manager
AIO = Asynchronous I/O
AMDU = ASM Metadata Dump Utility
AMM = Automatic Memory Management
ARC = Archive process
ASH = Active Session History
ASM = Automatic Storage Management
ASMB = ASM Background process
ASMCA = ASM Configuration Assistant
ASMCMD = ASM CoMmanD line utility
ASMLIB = ASM LIBrary tool
ASMM = Automatic Shared Memory Management
ASMSNMP = ASM Simple Network Management Protocol
AT = Allocation Table
ATA = Advanced Technology Attachment
AU = Allocation Unit
AWR = Automatic Workload Repository
BH = Block Header
BMC = Baseboard Management Controller
BS = Block Size
CBO = Cost-Based Optimizer
CF = Control File
CFS = Cluster FileSystem
CHM = Cluster Health Monitor
CIO = Concurrent I/O
CKPT = ChecKPoinT process
CLUVFY = CLUster VeriFy utility
COD = Continuing Operation Directory
CPU = Central Processing Unit or Critical Patch Update
CRM = Customer Relationship Management
CRS = Cluster Ready Services
CSS = Cluster Synchronization Services
CSV = Comma-Separated Values
CVM = Cluster Volume Manager
DB = DataBase
DBCA = DataBase Configuration Assistant
DBFS = DataBase File System
DBM = DataBase Machine
DBMS = DataBase Management Systems
DBPERF = DataBase PERFormance
DBV = DataBase Verification tool
DBW = DataBase Writer process
DD = Disk Directory or Data Description tool
DES = Database Excelleration Systems Inc.
DG = DiskGroup
DH = Disk Header
DIO = Direct I/O
DISM = Dynamic Intimate Shared Memory
DM = Device Mapper
DNFS = Direct Network File System
DRAM = Dynamic Random Access Memory
DSS = Decision Support System
DUL = Data UnLoader
DW = Data Warehouse
EIDE = Enhanced Integrated Drive Electronics
ERP = Enterprise Resource Planning
ETA = Estimated Time of Arrival
ETL = Extract Transform Load
FD = File Directory
FG = FailGroup
FRA = Flash Recovery Area or Fast Recovery Area
FS = FileSystem
FST = Free Segments Table
FTS = Full Table Scan
GC = Grid Control
GI = Grid Infrastructure
GUI = Graphical User Interface
HA = High Availability
HARD = Hardware Assisted Resilient Data
HB = Heart Beat
HBA = Host Bus Adapter
IDE = Integrated Drive Electronics
IIS = Internet Information Services
INST = INSTance
IO = Input/Output
IOPS = IO Per Second
IOT = Index Organized Table
IP = Internet Protocol
IPMI = Intelligent Platform Management Interface
JET = Joint Escalation Team
JFS = Journaled FileSystem
KB = KiloByte
KFED = Kernel Files metadata EDitor
KFOD = Kernel Files Osm disk[group] Discovery
LGWR = redo LoG WRiter process
LLT = Veritas Low Latency Transport protocol
LSNR = LiSteNeR
LUN = Logical Unit Number
LVM = Logical Volume Manager
MAA = Maximum Availability Architecture
MB = MegaByte
NAS = Network Attached Storage
NetApp = Network Appliance
NETCA = NETwork Configuration Assistant
NFS = Network FileSystem
NIC = Network Interface Controller
OCFS = Oracle Cluster FileSystem
OCR = Oracle Cluster Registry
ODM = Oracle Disk Manager
ODS = Operational Data Store
OEL = Oracle Enterprise Linux
OEM = Oracle Enterprise Manager
OID = Oracle Internet Directory
OLAP = OnLine Analytical Processing
OLTP = OnLine Transaction Processing
OMS = Oracle Management Service
OPATCH = Oracle PATCHing utility
OS = Operating System
OSCP = Oracle Storage Compatibility Program
OSW = OS Watcher
OSWFW = OS Watcher For Windows
OTN = Oracle Technology Network
OUI = Oracle Universal Installer
PB = PetaByte
PFILE = Parameter FILE
PGA = Program Global Area
PID = Proces ID
PL/SQL = Procedural Language/Structured Query Language
POC = Proof Of Concept
PROCWATCHER = PROCess WATCHER
PST = Partnership Status Table
PSU = Patch Set Update
PX = Parrallel eXecution
RAC = Real Application Cluster
RAID = Redundant Array of Independent Disks
RAM = Random Access Memory
RBAL = ReBALance process
RCA = Root Cause Analysis
RDA = Remote Diagnostic Agent
RDBMS = Relational DataBase Management System
RHEL = RedHat Enterprise Linux
RM = Resource Manager
RMAN = Recovery MANager
RPM = Resource Package Manager
SAN = Storage Area Network
SAS = Serial Attached SCSI
SATA = Serial Advanced Technology Attachment
SCAN = Single Client Access Name
SCSI = Small Computer System Interface
SGA = System Global Area
SLA = Service Level Agreement
SMF = Service Management Facility
SPFILE = Server Parameter FILE
SQL = Structured Query Language
SRDF = EMC Symmetrix Remote Data Facility
SSD = Solid State Disk
SVCTM = average SerViCe TiMe
SVM = Solaris Volume Manager
TB = TeraByte
TCP = Transmission Control Protocol
TDE = Transparent Data Encryption
TKPROF = Transient Kernel PROFile
TNS = Transparent Network Substrate
UDEV = Unix DEVice manager
UDP = User Datagram Protocol
UFG = Umbilicus ForeGround process
UFS = User FileSystem
VBG = Volume BackGround process
VCS = Veritas Cluster Server
VD = Voting Disk
VDBG = Volume Driver BackGround process
VIP = Virtual Internet Portocol
VLDB = Very Large DataBase
VM = Virtual Machine
VMB = Volume Membership BackGround process
VxFS = Veritas File System
VxVM = Veritas Volume Manager
XDB = XML DataBase
DBMS_STATS收集子分区表导致library cache lock等待
有客户反馈一个问题,业务中有一张表无论是查询还是dml操作都非常慢,让我们介入分析
数据库版本
打上了170814比较新的psu
[oracle@xffdb1 ~]$ $ORACLE_HOME/OPatch/opatch lsinventory Oracle 中间补丁程序安装程序版本 11.2.0.3.15 版权所有 (c) 2018, Oracle Corporation。保留所有权利。 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.15 OUI version : 11.2.0.4.0 Log file location : /u01/app/oracle/product/11.2.0/db_1/cfgtoollogs/opatch/opatch2018-01-06_16-20-22下午_1.log -------------------------------------------------------------------------------- Local Machine Information:: Hostname: xffdb1 ARU platform id: 226 ARU platform description:: Linux x86-64 已安装的顶级产品 (1): Oracle Database 11g 11.2.0.4.0 此 Oracle 主目录中已安装 1 个产品。 中间补丁程序 (2) : Patch 26609929 : applied on Fri Sep 15 14:28:51 CST 2017 Unique Patch ID: 21482966 Patch description: "OCW Patch Set Update : 11.2.0.4.170814 (26609929)" Patch 26609445 : applied on Fri Sep 15 14:28:04 CST 2017 Unique Patch ID: 21482382 Patch description: "Database Patch Set Update : 11.2.0.4.170814 (26609445)"
awr数据结果
这里显示大量的library cache lock等待
做systemstate分析
Resource Holder State
LOCK: Handle=0x325d938e08 74: 74: is waiting for PIN: Handle=0x325d938e08
Enq TX-00E0001B-000004D3 488: 488: is waiting for 74:
Mutex 1e7fbf6c 322: 322: is waiting for 74:
Enq TX-02850009-00000086 630: 630: is waiting for 74:
Enq TX-02BC000A-0000009E ??? Blocker
Enq TX-0075000E-000010BE ??? Blocker
PIN: Handle=0x325d938e08 ??? Blocker
Enq TX-049A000C-00000002 ??? Blocker
Enq TX-034C001D-00000038 ??? Blocker
Enq TX-052C0012-00000002 669: 669: is waiting for 74:
Enq TX-04620004-00000003 ??? Blocker
Enq TX-009B0020-00000DA6 ??? Blocker
Mutex af4db5a8 242: 242: is waiting for 74:
Enq TX-041E0002-00000005 ??? Blocker
Mutex 663b253d 727: 727: is waiting for 74:
Enq TX-04FA0008-00000003 124: 124: is waiting for 69:
Enq TX-031D0010-0000005B ??? Blocker
Enq TX-02BB0004-000000A2 ??? Blocker
Enq TX-0248001A-000000CD 69: 69: is waiting for Enq TX-02BC000A-0000009E
Enq TX-03D70008-00000002 ??? Blocker
Enq TX-02B1001D-00000081 ??? Blocker
Enq TX-0423001A-00000003 ??? Blocker
Enq TX-051A0007-00000003 506: 506: is waiting for 74:
Mutex 2aceb8e9 602: 602: is waiting for 74:
Mutex c6b2e0f4 196: 196: is waiting for 74:
Enq TX-00A30020-0000055A ??? Blocker
Enq TX-00D70015-00000315 ??? Blocker
Enq TX-03B30006-00000012 ??? Blocker
Enq TX-008C0003-00001005 ??? Blocker
Enq TX-05470014-00000006 219: 219: is waiting for 74:
Enq TX-054E0018-00000006 673: 673: is waiting for 74:
Mutex f28c06f8 279: 279: is waiting for 74:
Enq TX-03D30012-00000002 ??? Blocker
Enq TX-055C001B-00000005 333: 333: is waiting for Enq TX-01CF000B-00000B2F
Mutex 2ff03da9 276: 276: is waiting for 74:
Enq TX-030A000A-000000B6 ??? Blocker
Enq TX-00530004-000023DF 212: 212: is waiting for 74:
Enq TX-05550017-00000002 469: 469: is waiting for 74:
Mutex 724ba5e3 177: 177: is waiting for 74:
Enq TX-03C10007-00000015 ??? Blocker
Enq TX-02E30006-00000079 ??? Blocker
IPC 6 6 Blocker
Enq TX-0289000B-0000005F 449: 449: is waiting for 74:
Enq TX-021E0002-000001DA ??? Blocker
Mutex a9fcc7b8 131: 131: is waiting for 74:
Enq TX-0483001F-00000009 ??? Blocker
Enq TX-00800010-00000BA0 ??? Blocker
Mutex c0af249e 666: 666: is waiting for 74:
Mutex 94389ed4 78: 78: is waiting for 74:
Mutex 4ac40611 143: 143: is waiting for 74:
Mutex 24c1c387 168: 168: is waiting for 74:
Mutex 930636c9 752: 752: is waiting for 74:
Enq TX-04660001-00000003 ??? Blocker
Enq TX-01B6000A-00000022 ??? Blocker
Enq TX-0166000F-000002C7 ??? Blocker
Enq TX-02FF0008-000000A1 ??? Blocker
Enq TX-01E10001-00000023 ??? Blocker
Enq TX-0327000E-0000009E ??? Blocker
Enq TX-05120016-00000002 615: 615: is waiting for 74:
Enq TX-039F001D-00000011 ??? Blocker
Enq TX-01CF000B-00000B2F ??? Blocker
Enq TX-01130020-000001B6 ??? Blocker
Enq TX-052A0021-00000002 635: 635: is waiting for 74:
Mutex ebf68fee 579: 579: is waiting for 74:
Enq TX-02350017-000000D6 ??? Blocker
Enq TX-00BB0000-00000598 ??? Blocker
Enq TX-0443000E-00000003 ??? Blocker
Mutex e7bae014 344: 344: is waiting for 74:
Mutex fe251793 616: 616: is waiting for 74:
Mutex 750494ae 180: 180: is waiting for 74:
Enq TX-01230010-000001C0 ??? Blocker
Enq TX-0543001C-00000003 119: 119: is waiting for 124:
Enq TX-04E60014-00000005 ??? Blocker
Enq TX-00F1000A-000002AB ??? Blocker
Mutex aa83fbd7 651: 651: is waiting for 74:
Enq TX-058A0002-00000003 ??? Blocker
Enq TX-03B00016-0000000F ??? Blocker
Mutex 26e065a4 150: 150: is waiting for 74:
Enq TX-0219001B-000001BE ??? Blocker
Enq TX-00B9001D-00000069 ??? Blocker
Enq TX-01110020-00000198 ??? Blocker
Enq TX-04F3001B-00000002 ??? Blocker
Enq TX-04A60015-00000002 ??? Blocker
Enq TX-02D30015-0000008A ??? Blocker
Enq TX-01540018-000001B7 ??? Blocker
Enq TX-02240019-0000001C ??? Blocker
Mutex e595002d 502: 502: is waiting for 74:
Mutex 1661a4cb 732: 732: is waiting for 74:
Enq TX-00320010-00003A12 ??? Blocker
Enq TX-020F001F-00000027 ??? Blocker
Enq TX-0387000A-0000004D 515: 515: is waiting for 74:
Enq TX-01260014-0000012D ??? Blocker
Enq TX-02EE001D-0000005F ??? Blocker
Enq TX-004A0001-00000882 603: 603: is waiting for Enq TX-02240019-0000001C
Mutex 4a18a781 258: 258: is waiting for 74:
LOCK: Handle=0x325aa4c428 643: 643: is waiting for 74:
Enq TX-037A0002-0000006E 118: 118: is waiting for 74:
Enq TX-02460020-0000006D 493: 493: is waiting for Enq TX-00F1000A-000002AB
PROCESS 74: J000
----------------------------------------
SO: 0x31a2b2e440, type: 2, owner: (nil), flag: INIT/-/-/0x00 if: 0x3 c: 0x3
proc=0x31a2b2e440, name=process, file=ksu.h LINE:12721, pg=0
(process) Oracle pid:74, ser:153, calls cur/top: 0x305d24c378/0x305f3f5eb0
flags : (0x0) -
flags2: (0x10), flags3: (0x10)
intr error: 0, call error: 0, sess error: 0, txn error 0
intr queue: empty
ksudlp FALSE at location: 0
(post info) last post received: 0 0 80
last post received-location: kji.h LINE:3691 ID:kjata: wake up enqueue owner
last process to post me: 0x3182a6fca0 1 6
last post sent: 0 0 26
last post sent-location: ksa2.h LINE:285 ID:ksasnd
last process posted by me: 0x3182a75038 2 6
(latch info) wait_event=0 bits=0x0
Process Group: DEFAULT, pseudo proc: 0x3162ddbe90
O/S info: user: oracle, term: UNKNOWN, ospid: 103036
OSD pid info: Unix process pid: 103036, image: oracle@xffdb1 (J000)
Short stack dump:
ksedsts()+465<-ksdxfstk()+32<-ksdxcb()+1927<-sspuser()+112<-__sighandler()<-semtimedop()+10
<-skgpwwait()+178<-ksliwat()+2046<-kslwaitctx()+163<-kjusuc()+3400
<-ksipgetctxi()+1759<-kqlmPin()+2943<-kqlmClusterLock()+237<-kglpnal()+4072
<-kglpin()+1381<-qostobkglcrt1()+640<-qostobkglcrt()+255<-qospsts()+1639
<-spefcmpa()+196<-spefmccallstd()+235<-pextproc()+41<-peftrusted()+150
<-psdexsp()+255<-rpiswu2()+1776<-psdextp()+700<-pefccal()+726<-pefcal()+224
<-pevm_FCAL()+169<-pfrinstr_FCAL()+75<-pfrrun_no_tool()+63<-pfrrun()+627
<-plsql_run()+649<-peidxr_run()+263<-peidxexe()+79<-kkxdexe()+338
<-kkxmpexe()+241<-kgmexwi()+605<-kgmexec()+2193<-evapls()+813
<-evaopn2()+808<-kkxmexcs()+133<-opiexe()+20880<-kpoal8()+2380
<-opiodr()+917<-kpoodr()+1401<-upirtrc()+2436<-kpurcsc()+98
<-kpuexec()+10790<-OCIStmtExecute()+39<-jslvec_execcb1()+672
<-jslvswu()+56<-jslve_execute0()+2257<-jslve_execute()+332
<-rpiswu2()+1776<-kkjex1e()+379<-kkjsexe()+711<-kkjrdp()+694
<-opirip()+958<-opidrv()+603<-sou2o()+103<-opimai_real()+250
<-ssthrdmain()+265<-main()+201<-__libc_start_main()+253
SO: 0x31c382f010, type: 4, owner: 0x31a2b2e440, flag: INIT/-/-/0x00 if: 0x3 c: 0x3
proc=0x31a2b2e440, name=session, file=ksu.h LINE:12729, pg=0
(session) sid: 6001 ser: 679 trans: 0x311e36fa28, creator: 0x31a2b2e440
flags: (0x8010041) USR/- flags_idl: (0x1) BSY/-/-/-/-/-
flags2: (0x40009) -/-/INC
DID: , short-term DID:
txn branch: (nil)
edition#: 100 oct: 170, prv: 0, sql: 0x323de178e8, psql: 0x32b5d0fc98, user: 0/SYS
ksuxds FALSE at location: 0
service name: SYS$USERS
client details:
O/S info: user: oracle, term: UNKNOWN, ospid: 103036
machine: xffdb1 program: oracle@xffdb1 (J000)
application name: DBMS_SCHEDULER, hash value=2478762354
action name: ORA$AT_OS_OPT_SY_1594, hash value=1524069073
Current Wait Stack:
0: waiting for 'library cache pin'
handle address=0x325d938e08, pin address=0x325baec3e0, 100*mode+namespace=0x850fe00010003
wait_id=46488 seq_num=46502 snap_id=1
wait times: snap=6 min 2 sec, exc=6 min 2 sec, total=6 min 2 sec
wait times: max=15 min 0 sec, heur=6 min 2 sec
wait counts: calls=727 os=727
in_wait=1 iflags=0x15a2
There are 2136 sessions blocked by this session.
发现主要是74号进程阻塞了其他的
做hanganalyze分析
-------------------------------------------------------------------------------
Chain 1:
-------------------------------------------------------------------------------
Oracle session identified by:
{
instance: 1 (xffdb.xffdbsv1)
os id: 82310
process id: 224, oracle@xffdb1
session id: 4
session serial #: 12237
}
is waiting for 'enq: TX - row lock contention' with wait info:
{
p1: 'name|mode'=0x54580006
p2: 'usn<<16 | slot'=0x21e0002
p3: 'sequence'=0x1da
time in wait: 3 min 48 sec
timeout after: never
wait id: 1260
blocking: 0 sessions
current sql: UPDATE t_xifenfei subpartition(p201801_s32)
SET INDICATORCODE = :1,
LOWERLIMIT = :2,
UPPERLIMIT = :3,
AVGUNITPRICE = :4,
TRADEITEMNAME = :5,
short stack: ………………
wait history:
* time between current wait and wait #1: 0.000220 sec
1. event: 'gc cr block 2-way'
time waited: 0.000341 sec
wait id: 1259 p1: ''=0x6
p2: ''=0x3590
p3: ''=0x44b
* time between wait #1 and #2: 0.000284 sec
2. event: 'gc current block 3-way'
time waited: 0.000663 sec
wait id: 1258 p1: ''=0xa
p2: ''=0x3ad02
p3: ''=0x2000001
* time between wait #2 and #3: 0.000383 sec
3. event: 'gc cr block 2-way'
time waited: 0.000288 sec
wait id: 1257 p1: ''=0x5
p2: ''=0x2e20
p3: ''=0x957
}
and is blocked by
=> Oracle session identified by:
{
instance: 3 (xffdb.xffdbsv3)
os id: 39472
process id: 587, oracle@xffdb3
session id: 2215
session serial #: 8213
}
which is waiting for 'library cache lock' with wait info:
{
p1: 'handle address'=0x327f5ecea0
p2: 'lock address'=0x327a5ab660
p3: '100*mode+namespace'=0x850fe00010002
time in wait: 3 min 43 sec
timeout after: 11 min 16 sec
wait id: 2343
blocking: 3 sessions
current sql: SELECT COUNT(*) COUNT
FROM t_xifenfei subpartition(p201801_s32)
WHERE TRADEITEMID = '679EA6DE428F414D014B3D5EC8DE5E32'
short stack: ………………
wait history:
* time between current wait and wait #1: 0.000908 sec
1. event: 'SQL*Net message from client'
time waited: 0.001074 sec
wait id: 2342 p1: 'driver id'=0x54435000
p2: '#bytes'=0x1
* time between wait #1 and #2: 0.000033 sec
2. event: 'SQL*Net message to client'
time waited: 0.000003 sec
wait id: 2341 p1: 'driver id'=0x54435000
p2: '#bytes'=0x1
* time between wait #2 and #3: 0.000069 sec
3. event: 'SQL*Net message from client'
time waited: 0.001232 sec
wait id: 2340 p1: 'driver id'=0x54435000
p2: '#bytes'=0x1
}
and is blocked by
=> Oracle session identified by:
{
instance: 1 (xffdb.xffdbsv1)
os id: 103036
process id: 74, oracle@xffdb1 (J000)
session id: 6001
session serial #: 679
}
which is waiting for 'library cache pin' with wait info:
{
p1: 'handle address'=0x325d938e08
p2: 'pin address'=0x325baec3e0
p3: '100*mode+namespace'=0x850fe00010003
time in wait: 3 min 47 sec
timeout after: 11 min 12 sec
wait id: 46488
blocking: 2099 sessions
current sql: call dbms_stats.gather_database_stats_job_proc ( )
short stack: …………
wait history:
* time between current wait and wait #1: 0.000038 sec
1. event: 'library cache lock'
time waited: 0.012353 sec
wait id: 46487 p1: 'handle address'=0x325d938e08
p2: 'lock address'=0x3259aee680
p3: '100*mode+namespace'=0x850fe00010003
* time between wait #1 and #2: 0.002509 sec
2. event: 'enq: IV - contention'
time waited: 0.001079 sec
wait id: 46486 p1: 'type|mode'=0x49560005
p2: 'id1'=0x53594e43
p3: 'id2'=0x15
* time between wait #2 and #3: 0.000085 sec
3. event: 'enq: IV - contention'
time waited: 0.001817 sec
wait id: 46485 p1: 'type|mode'=0x49560005
p2: 'id1'=0x4c4f434b
p3: 'id2'=0x15
}
Chain 1 Signature: 'library cache pin'<='library cache lock'<='enq: TX - row lock contention'
Chain 1 Signature Hash: 0xa08ff7bf
到这里基本上可以确定是由于自动任务收集统计信息导致系统出现大量的library cache lock和library cache pin,另外可以确定大部分被阻塞在library cache 相关的select和dml语句都集中在t_xifenfei这个子分区表中,通过查询mos,发现相关bug:Bug 19790972 – “library cache lock” waits due to DBMS_STATS gather of stats for a subpartition
osw配置私网监控(oswprvtnet)
OS Watcher Black Box (OSWbb) 是一种 UNIX shell 脚本的集合，主要用于收集和归档操作系统和网络的度量，以便为诊断性能问题提供支持。OSWbb 作为服务器上的一组后台进程来运行，定期收集OS 数据，其调用 Unix 实用程序，如 vmstat、netstat 和 iostat 等。在最近的gi psu中已经把osw包含在tfa中了，但是默认不启用私网的监控，如果需要可以进行人工配置
OSWbb 使用 traceroute 命令获取这些私有网络的状态。每个操作系统对 traceroute 命令使用的参数都略有不同。基本 oswbb 目录中的示例Exampleprivate.net 文件中包含每个操作系统使用的语法。因此，这将导致在 UNIX 平台中的输出显示有所不同。OSWbb 按指定间隔运行 private.net 文件，并在归档目录下的 oswprvtnet 子目录中存储数据。数据存储在每小时归档的文件中。文件中的每个条目都包含一个时间戳，前缀为 ***，嵌入在顶部输出中。
tfa中含osw
[root@xifenfei01 ~]# ps -ef|grep osw grid 17673 1 0 Jan30 ? 00:00:07 /bin/sh ./OSWatcher.sh 30 48 NONE /u01/app/grid/tfa/repository/suptools/xifenfei01/oswbb/grid/archive grid 17841 17673 0 Jan30 ? 00:00:01 /bin/sh ./OSWatcherFM.sh 48 /u01/app/grid/tfa/repository/suptools/xifenfei01/oswbb/grid/archive root 64159 64120 0 00:30 pts/0 00:00:00 grep osw
测试prvtnet.sh
[root@xifenfei01 oswbb]# cd /u01/app/grid/tfa/repository/suptools/xifenfei01/oswbb/grid/oswbb [root@xifenfei01 oswbb]# more prvtnet.sh echo "zzz ***"`date` traceroute -r -F xifenfei01-priv traceroute -r -F xifenfei02-priv ###################################################################### # DO NOT DELETE THE FOLLOWING LINE!!!!!!!!!!!!!!!!!!!!! ###################################################################### rm locks/lock.file [root@xifenfei01 oswbb]# ./prvtnet.sh zzz ***Tue Jan 30 18:02:49 CST 2018 traceroute to xifenfei01-priv (192.168.102.11), 30 hops max, 60 byte packets 1 xifenfei01-priv (192.168.102.11) 0.010 ms 0.003 ms 0.002 ms traceroute to xifenfei02-priv (192.168.102.14), 30 hops max, 60 byte packets 1 xifenfei02-priv (192.168.102.14) 0.081 ms 0.076 ms 0.087 ms rm: cannot remove `locks/lock.file': No such file or directory
配置private.net正式收集私网信息
[root@xifenfei01 oswbb]# find . -name OSWatcher.sh | xargs grep -i "private"
# Run traceroute for private networks if file private.net exists
if [ -x private.net ]; then
./private.net >> $OSWBB_ARCHIVE_DEST/oswprvtnet/${hostn}_prvtnet_$hour 2>&1 &
这里重要的是：
./OSWatcher.sh:# Run traceroute for private networks if file private.net exists
表示，在OSWatcher.sh中调用的文件名称是private.net
将prvtnet.sh复制为供OSWatcher.sh脚本调用的文件名称
[root@xifenfei01 oswbb]# cp prvtnet.sh private.net [root@xifenfei01 oswbb]# chown grid:oinstall prvtnet.net [root@xifenfei01 oswbb]# chmod +x prvtnet.net
关注内容
示例 1： 接口处于启动和响应状态：
traceroute to X.X.X.X, (X.X.X.X) 30 hops max, 1492 byte packets
1 X.X.X.X 1.015 ms 0.766 ms 0.755 ms
示例 2： 目标接口不是在直接连接的网络上，所以需要验证地址
是否正确或其插入的交换机是否位于相同的 VLAN 上（或其他问题）：
traceroute to X.X.X.X, (X.X.X.X) 30 hops max, 40 byte packets
traceroute: host X.X.X.X is not on a directly-attached network
示例 3： 网络无法访问：
traceroute to X.X.X.X, (X.X.X.X) 30 hops max, 40 byte packets
Network is unreachable
示例 4： 网络无法访问：
xifenfei01_prvtnet_18.01.30.2000.dat-zzz ***Tue Jan 30 20:27:43 CST 2018
xifenfei01_prvtnet_18.01.30.2000.dat-traceroute to xifenfei01-priv (192.168.102.11), 30 hops max, 60 byte packets
xifenfei01_prvtnet_18.01.30.2000.dat- 1 xifenfei01-priv (192.168.102.11) 0.014 ms 0.004 ms 0.005 ms
xifenfei01_prvtnet_18.01.30.2000.dat-traceroute to xifenfei02-priv (192.168.102.14), 30 hops max, 60 byte packets
xifenfei01_prvtnet_18.01.30.2000.dat- 1 * * *
xifenfei01_prvtnet_18.01.30.2000.dat- 2 * * *
xifenfei01_prvtnet_18.01.30.2000.dat- 3 * * *
xifenfei01_prvtnet_18.01.30.2000.dat- 4 * * *
xifenfei01_prvtnet_18.01.30.2000.dat- 5 * * *
xifenfei01_prvtnet_18.01.30.2000.dat- 6 * * *
xifenfei01_prvtnet_18.01.30.2000.dat- 7 * * *
xifenfei01_prvtnet_18.01.30.2000.dat- 8 * * *
xifenfei01_prvtnet_18.01.30.2000.dat- 9 * * *
xifenfei01_prvtnet_18.01.30.2000.dat-10 * * *
xifenfei01_prvtnet_18.01.30.2000.dat-11 * * *
xifenfei01_prvtnet_18.01.30.2000.dat-12 * * *
xifenfei01_prvtnet_18.01.30.2000.dat-13 * * *
xifenfei01_prvtnet_18.01.30.2000.dat-14 * * *
xifenfei01_prvtnet_18.01.30.2000.dat-15 * * *
xifenfei01_prvtnet_18.01.30.2000.dat-16 * * *
xifenfei01_prvtnet_18.01.30.2000.dat-17 * * *
xifenfei01_prvtnet_18.01.30.2000.dat-18 * * *
xifenfei01_prvtnet_18.01.30.2000.dat-19 * * *
xifenfei01_prvtnet_18.01.30.2000.dat-20 * * *
xifenfei01_prvtnet_18.01.30.2000.dat-21 * * *
xifenfei01_prvtnet_18.01.30.2000.dat-22 * * *
xifenfei01_prvtnet_18.01.30.2000.dat-23 * * *
xifenfei01_prvtnet_18.01.30.2000.dat-24 * * *
xifenfei01_prvtnet_18.01.30.2000.dat-25 * * *
xifenfei01_prvtnet_18.01.30.2000.dat-26 * * *
xifenfei01_prvtnet_18.01.30.2000.dat-27 * * *
xifenfei01_prvtnet_18.01.30.2000.dat-28 * * *
xifenfei01_prvtnet_18.01.30.2000.dat-29 * * *
xifenfei01_prvtnet_18.01.30.2000.dat-30 * * *
xifenfei01_prvtnet_18.01.30.2000.dat:zzz ***Warning. Traceroute response is spanning snapshot intervals.
reliable message等待
在一次给客户的rac poc测试中，发现大量reliable message等待，严重影响性能
数据库版本
11.2.0.4+最新psu(180116)
SYS@xffdb1>select * from v$version; BANNER -------------------------------------------------------------------------------- Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production PL/SQL Release 11.2.0.4.0 - Production CORE 11.2.0.4.0 Production TNS for Linux: Version 11.2.0.4.0 - Production NLSRTL Version 11.2.0.4.0 - Production Elapsed: 00:00:00.00 SYS@xffdb1>exit Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP, Data Mining and Real Application Testing options [oracle@xifenfei01 ~]$ opatch lspatches 26609929;OCW Patch Set Update : 11.2.0.4.170814 (26609929) 26925576;Database Patch Set Update : 11.2.0.4.180116 (26925576) OPatch succeeded.
监控em发现大量other等待
查询发现大量reliable message等待
SYS@xffdb1>@event
INST_ID USERNAME EVENT COUNT(*)
---------------- ------------------------------ -------------------------------------------------- ----------------
1 SOE reliable message 16
1 SOE gc current request 4
1 SOE gc cr request 5
1 SOE db file sequential read 17
1 SYS db file scattered read 1
2 SOE library cache: mutex X 2
2 SOE latch: ges resource hash list 2
2 SOE gc current request 1
2 SOE gc cr request 4
2 SOE db file sequential read 12
2 db file parallel write 1
2 SOE SQL*Net message to client 1
12 rows selected.
进一步查询发现大量Result Cache: Channel
SYS@xffdb1>SELECT CHANNEL, 2 SUM(wait_count) sum_wait_count 3 FROM GV$CHANNEL_WAITS 4 GROUP BY CHANNEL 5 ORDER BY SUM(wait_count) DESC; CHANNEL SUM_WAIT_COUNT ---------------------------------------------------------------- ---------------- Result Cache: Channel 379367698 RBR channel 128968 kxfp control signal channel 115091 MMON remote action broadcast channel 4646 obj broadcast channel 395 LCK0 ksbxic channel 17 parameters to cluster db instances - broadcast channel 4 service operations - broadcast channel 3 kill job broadcast - broadcast channel 2
查看result_cache_max_size参数
SYS@xffdb1>show parameter result_cache_max_size; NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ result_cache_max_size big integer 335552K
调整result_cache_max_size参数
SYS@xffdb1>alter system set result_cache_max_size=0; System altered. SYS@xffdb1>show parameter result_cache_max_size; NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ result_cache_max_size big integer 0
重启数据库，再次测试，发现相关等待消失，系统也更加稳定
SYS@xffdb1>@event
INST_ID USERNAME EVENT COUNT(*)
---------------- ------------------------------ -------------------------------------------------- ----------------
1 SOE library cache: mutex X 1
1 SOE gc current request 3
1 DBSNMP gc cr request 1
1 SOE gc cr request 7
1 SOE db file sequential read 18
1 db file parallel write 1
2 SOE library cache: mutex X 2
2 SOE latch free 1
2 SOE gc current request 4
2 SOE gc cr request 4
2 SOE db file sequential read 12
2 db file parallel write 3
12 rows selected.
参考：Very High Waits for ‘reliable message’ After Upgrade to 11.2.0.4 When Using Result Cache (Doc ID 1951729.1)
重建oraInventory解决ORA-20001
数据库启动报ORA-20001: Latest xml inventory is not loaded into table错误
Completed: ALTER DATABASE OPEN 2018-01-23T23:46:27.924841+08:00 CJQ0 started with pid=54, OS id=6653 2018-01-23T23:46:31.705550+08:00 Unable to obtain current patch information due to error: 20001, ORA-20001: Latest xml inventory is not loaded into table ORA-06512: at "SYS.DBMS_QOPATCH", line 777 ORA-06512: at "SYS.DBMS_QOPATCH", line 864 ORA-06512: at "SYS.DBMS_QOPATCH", line 2222 ORA-06512: at "SYS.DBMS_QOPATCH", line 740 ORA-06512: at "SYS.DBMS_QOPATCH", line 2247 =========================================================== Dumping current patch information =========================================================== Unable to obtain current patch information due to error: 20001 ===========================================================
查询相关sql报错
SYS@xffdb>select * from OPATCH_XML_INV ; ERROR: ORA-29913: error in executing ODCIEXTTABLEFETCH callout ORA-29400: data cartridge error KUP-04004: error while reading file /u03/app/oracle/product/12.2.0.1/dbhome/QOpatch/qopiprep.bat no rows selected Elapsed: 00:00:00.58 SYS@xffdb>select xmltransform(dbms_qopatch.get_opatch_lsinventory(), dbms_qopatch.GET_OPATCH_XSLT()) from dual ; ERROR: ORA-20001: Latest xml inventory is not loaded into table ORA-06512: at "SYS.DBMS_QOPATCH", line 777 ORA-06512: at "SYS.DBMS_QOPATCH", line 864 ORA-06512: at "SYS.DBMS_QOPATCH", line 2222 ORA-06512: at "SYS.DBMS_QOPATCH", line 740 ORA-06512: at "SYS.DBMS_QOPATCH", line 2247 no rows selected Elapsed: 00:00:00.63
datapatch -prereq报错
[oracle@xifenfei ~]$ $ORACLE_HOME/OPatch/datapatch -prereq
SQL Patching tool version 12.2.0.1.0 Production on Tue Jan 23 18:11:32 2018
Copyright (c) 2012, 2017, Oracle. All rights reserved.
Connecting to database...OK
Note: Datapatch will only apply or rollback SQL fixes for PDBs
that are in an open state, no patches will be applied to closed PDBs.
Please refer to Note: Datapatch: Database 12c Post Patch SQL Automation
(Doc ID 1585822.1)
Queryable inventory could not determine the current opatch status.
Execute 'select dbms_sqlpatch.verify_queryable_inventory from dual'
and/or check the invocation log
/u03/app/oracle/cfgtoollogs/sqlpatch/sqlpatch_4909_2018_01_23_18_11_32/sqlpatch_invocation.log
for the complete error.
Prereq check failed, exiting without installing any patches.
Please refer to MOS Note 1609718.1 and/or the invocation log
/u03/app/oracle/cfgtoollogs/sqlpatch/sqlpatch_4909_2018_01_23_18_11_32/sqlpatch_invocation.log
for information on how to resolve the above errors.
SQL Patching tool complete on Tue Jan 23 18:11:45 2018
分析qopiprep.bat文件
cd $ORACLE_HOME
PATH=/bin:/usr/bin
export PATH
# sed tried to convert from one encoding to other in presence of LC_ALL
# or LANG settings. Since opatch returning UTF-8 based encoding we do not
# need such a conversion. So safely skip it
LANG=en_US.UTF-8
export LANG
LC_ALL=''
export LC_ALL
# Option: "-retry 0" avoids retries in case of locked inventory.
# Option: "-invPtrLoc" is required for non-central-inventory
# locations. $OPATCH_PREP_LSINV_OPTS which may set by users
# in the environment to configure special OPatch options
# ("-jdk" is another good candidate that may require configuration!).
# Option: "-all" gives information on all Oracle Homes
# installed in the central inventory. With that information, the
# patches of non-RDBMS homes could be fetched.
DBSID=$ORACLE_SID
ORABASE=`$ORACLE_HOME/bin/orabasehome`
rm -rf $ORABASE/rdbms/log/xml_file_$DBSID.xml
$ORACLE_HOME/OPatch/opatch lsinventory -xml $ORABASE/rdbms/log/xml_file_$DBSID.xml -retry 0 -invPtrLoc $ORACLE_HOME/oraInst.loc >> $ORABASE/rdbms/log/stout_$DBSID.txt
cat $ORABASE/rdbms/log/xml_file_$DBSID.xml | sed 's/^ *//' | tr '\n' ' '
echo "UIJSVTBOEIZBEFFQBL"
rm $ORABASE/rdbms/log/xml_file_$DBSID.xml
rm $ORABASE/rdbms/log/stout_$DBSID.txt
这里主要是$ORACLE_HOME/OPatch/opatch lsinventory可能异常，测试该功能
qopatch_log日志
[oracle@xifenfei ~]$ tail -f /u03/app/oracle/product/12.2.0.1/dbhome/QOpatch/qopatch_log.log
LOG file opened at 01/23/18 18:48:55
KUP-05007: Warning: Intra source concurrency disabled because the preprocessor option is being used.
Field Definitions for table OPATCH_XML_INV
Record format DELIMITED BY NEWLINE
Data in file has same endianness as the platform
Reject rows with all null fields
Fields in Data Source:
XML_INVENTORY CHAR (100000000)
Terminated by "UIJSVTBOEIZBEFFQBL"
Trim whitespace same as SQL Loader
KUP-04004: error while reading file /u03/app/oracle/product/12.2.0.1/dbhome/QOpatch/qopiprep.bat
KUP-04017: OS message: Error 0
KUP-04017: OS message: LsInventorySession failed: RawInventory gets null OracleHomeInfo
cat: /u03/app/oracle/product/12.2.0.1/dbhome/rdbms/log/xml_file_xffdb.xml: No such file or direc
KUP-04118: operation "pipe read", location "skudmir:2"
opatch lsinventory验证
[oracle@xifenfei ~]$ /u03/app/oracle/product/12.2.0.1/dbhome/OPatch/opatch lsinventory Oracle Interim Patch Installer version 12.2.0.1.6 Copyright (c) 2018, Oracle Corporation. All rights reserved. Oracle Home : /u03/app/oracle/product/12.2.0.1/dbhome Central Inventory : /u01/app/oraInventory from : /u03/app/oracle/product/12.2.0.1/dbhome/oraInst.loc OPatch version : 12.2.0.1.6 OUI version : 12.2.0.1.4 Log file location : /u03/app/oracle/product/12.2.0.1/dbhome/cfgtoollogs/opatch/opatch2018-01-23_23-50-29PM_1.log List of Homes on this system: Home name= OraDB12Home1, Location= "/u01/app/oracle/product/12.2.0/dbhome_1" LsInventorySession failed: RawInventory gets null OracleHomeInfo OPatch failed with error code 73
现在到这一步，可以确定判断opatch lsinventory运行异常，导致DBMS_QOPATCH无法正常工作，而引起opatch异常的原因是由于RawInventory gets null OracleHomeInfo
分析inventory.xml 文件
[oracle@xifenfei ContentsXML]$ cat inventory.xml <?xml version="1.0" standalone="yes" ?> <!-- Copyright (c) 1999, 2016, Oracle and/or its affiliates. All rights reserved. --> <!-- Do not modify the contents of this file by hand. --> <INVENTORY> <VERSION_INFO> <SAVED_WITH>12.2.0.1.0</SAVED_WITH> <MINIMUM_VER>2.1.0.6.0</MINIMUM_VER> </VERSION_INFO> <HOME_LIST> <HOME NAME="OraDB12Home1" LOC="/u01/app/oracle/product/12.2.0/dbhome_1" TYPE="O" IDX="1"/> </HOME_LIST> <COMPOSITEHOME_LIST> </COMPOSITEHOME_LIST> </INVENTORY>
因为该机器上安装过三个版本的oracle，12.2 beta，11.2.0.4,12.2.0.1，现在oracle home只有第一个beta的，因此这个部分肯定异常，导致后面的12.2正式版无法获取到oraclehome
重建oraInventory
[oracle@xifenfei app]$ cd $ORACLE_HOME/oui/bin [oracle@xifenfei bin]$ ./runInstaller -silent -ignoreSysPrereqs -attachHome ORACLE_HOME="/u01/app/oracle/product/12.2.0/dbhome_1" ORACLE_HOME_NAME="OraDB12betaHome1" Starting Oracle Universal Installer... Checking swap space: must be greater than 500 MB. Actual 3935 MB Passed The inventory pointer is located at /etc/oraInst.loc 'AttachHome' was successful. [oracle@xifenfei bin]$ ./runInstaller -silent -ignoreSysPrereqs -attachHome ORACLE_HOME="/u02/app/oracle/product/11.2.0.4/dbhome" ORACLE_HOME_NAME="OraDb11g_home1" Starting Oracle Universal Installer... Checking swap space: must be greater than 500 MB. Actual 3935 MB Passed The inventory pointer is located at /etc/oraInst.loc 'AttachHome' was successful. [oracle@xifenfei bin]$ ./runInstaller -silent -ignoreSysPrereqs -attachHome ORACLE_HOME="/u03/app/oracle/product/12.2.0.1/dbhome" ORACLE_HOME_NAME="OraDb122g_home1" Starting Oracle Universal Installer... Checking swap space: must be greater than 500 MB. Actual 3935 MB Passed The inventory pointer is located at /etc/oraInst.loc 'AttachHome' was successful. --验证inventory.xml 文件 [oracle@xifenfei ContentsXML]$ cat inventory.xml <?xml version="1.0" standalone="yes" ?> <!-- Copyright (c) 1999, 2018, Oracle and/or its affiliates. All rights reserved. --> <!-- Do not modify the contents of this file by hand. --> <INVENTORY> <VERSION_INFO> <SAVED_WITH>12.2.0.1.4</SAVED_WITH> <MINIMUM_VER>2.1.0.6.0</MINIMUM_VER> </VERSION_INFO> <HOME_LIST> <HOME NAME="OraDB12betaHome1" LOC="/u01/app/oracle/product/12.2.0/dbhome_1" TYPE="O" IDX="1"/> <HOME NAME="OraDb11g_home1" LOC="/u02/app/oracle/product/11.2.0.4/dbhome" TYPE="O" IDX="2"/> <HOME NAME="OraDb122g_home1" LOC="/u03/app/oracle/product/12.2.0.1/dbhome" TYPE="O" IDX="3"/> </HOME_LIST> <COMPOSITEHOME_LIST> </COMPOSITEHOME_LIST> </INVENTORY>
验证opatch lsinventory
[oracle@xifenfei bin]$ opatch lsinv Oracle Interim Patch Installer version 12.2.0.1.6 Copyright (c) 2018, Oracle Corporation. All rights reserved. Oracle Home : /u03/app/oracle/product/12.2.0.1/dbhome Central Inventory : /u01/app/oraInventory from : /u03/app/oracle/product/12.2.0.1/dbhome/oraInst.loc OPatch version : 12.2.0.1.6 OUI version : 12.2.0.1.4 Log file location : /u03/app/oracle/product/12.2.0.1/dbhome/cfgtoollogs/opatch/opatch2018-01-24_00-19-55AM_1.log Lsinventory Output file location : /u03/app/oracle/product/12.2.0.1/dbhome/cfgtoollogs/opatch/lsinv/lsinventory2018-01-24_00-19-55AM.txt -------------------------------------------------------------------------------- Local Machine Information:: Hostname: xifenfei ARU platform id: 226 ARU platform description:: Linux x86-64 Installed Top-level Products (1): Oracle Database 12c 12.2.0.1.0 There are 1 products installed in this Oracle Home. There are no Interim patches installed in this Oracle Home. -------------------------------------------------------------------------------- OPatch succeeded.
验证dbms_qopatch工作正常
[oracle@xifenfei ContentsXML]$ $ORACLE_HOME/OPatch/datapatch -prereq
SQL Patching tool version 12.2.0.1.0 Production on Wed Jan 24 00:21:48 2018
Copyright (c) 2012, 2017, Oracle. All rights reserved.
Connecting to database...OK
Note: Datapatch will only apply or rollback SQL fixes for PDBs
that are in an open state, no patches will be applied to closed PDBs.
Please refer to Note: Datapatch: Database 12c Post Patch SQL Automation
(Doc ID 1585822.1)
Determining current state...done
Adding patches to installation queue and performing prereq checks...done
Installation queue:
For the following PDBs: CDB$ROOT PDB$SEED
Nothing to roll back
Nothing to apply
SQL Patching tool complete on Wed Jan 24 00:21:55 2018
SYS@xffdb>select dbms_sqlpatch.verify_queryable_inventory from dual;
VERIFY_QUERYABLE_INVENTORY
--------------------------------------------------------------------------------------------------
OK
Elapsed: 00:00:01.03
SYS@xffdb>select xmltransform(dbms_qopatch.get_opatch_lsinventory(), dbms_qopatch.GET_OPATCH_XSLT()) from dual ;
XMLTRANSFORM(DBMS_QOPATCH.GET_OPATCH_LSINVENTORY(),DBMS_QOPATCH.GET_OPATCH_XSLT())
----------------------------------------------------------------------------------------------------------
Oracle Querayable Patch Interface 1.0
-----------------------------------------
Elapsed: 00:00:01.09
SYS@xffdb>
通过修复错误的oraInventory解决ORA-20001问题
18c新特性：Scalable Sequences(自适应序列)
作为18c的新特性,其实在oracle 12.2 引入了Scalable Sequences作为一个隐藏特性，为了改善在高并发系统中，特别是使用seq作为index，大量插入记录导致index的争用，oracle自己实现了以前我们需要人工去自定义seq的方式(instance+sid+seq的类似算法方式）,该功能将在oracle 18c中正式推出,我这里带领大家先体会下
SCALE/NOSCALE
When SCALE is specified, a numeric offset is affixed to the beginning of the sequence. This offset if of the form iii||sss||, where,
iii denotes a three digit instance offset given by (instance_id % 100) + 100,
sss denotes a three digits session offset given by (session_id % 1000), and
|| is the concatenation operator
EXTEND/NOEXTEND
When EXTEND is specified with the SCALE keyword, the generated sequence values are all of length (x+y), where x is the length of the scalable offset (default 6), and y is the maximum number of digits in the sequence maxvalue/minvalue. Thus, for an ascending sequence with maxvalue 100 and SCALABLE EXTEND specified, the generated sequence values are of the form iii||sss||001, iii||sss||002, …,iii||sss||100
The default setting for the SCALE clause is NOEXTEND. With the NOEXTEND setting, the generated sequence values are at most as wide as the maximum number of digits in the sequence maxvalue/minvalue. This setting is useful for integration with existing applications where sequences are used to populate fixed width columns. On invocation of NEXTVAL on a sequence with SCALABLE NOEXTEND specified, a user error is thrown if the generated value requires more digits of representation than the sequence’s maxvalue/minvalue.
SCALE的算法就是(instance_id % 100)(_kqdsn_instance_digits) + 100||(session_id % 1000)(_kqdsn_cpu_digits)+seq(EXTEND/NOEXTEND确定是否固定宽度）
Scalable Sequences语法
CREATE | ALTER SEQUENCE [ schema. ]sequence
[ { INCREMENT BY | START WITH } integer
| { MAXVALUE integer | NOMAXVALUE }
| { MINVALUE integer | NOMINVALUE }
| { CYCLE | NOCYCLE }
| { CACHE integer | NOCACHE }
| { ORDER | NOORDER }
| { SCALE {EXTEND | NOEXTEND} | NOSCALE}
]
Scalable Sequences测试
[oracle@xifenfei ~]$ sqlplus / as sysdba
SQL*Plus: Release 12.2.0.1.0 Production on Tue Jan 23 02:33:49 2018
Copyright (c) 1982, 2016, Oracle. All rights reserved.
Connected to:
Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production
BANNER CON_ID
-------------------------------------------------------------------------------- ----------
Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production 0
PL/SQL Release 12.2.0.1.0 - Production 0
CORE 12.2.0.1.0 Production 0
TNS for Linux: Version 12.2.0.1.0 - Production 0
NLSRTL Version 12.2.0.1.0 - Production 0
SQL> set line 150
SQL> col "Paramete" for a30
SQL> col "Session Value" for a20
SQL> col "Instance Value" for a20
SQL> col "Is Default?" for a20
SQL> SELECT a.ksppinm "Parameter", b.ksppstvl "Session Value", c.ksppstvl "Instance Value",c.ksppstdf "Default?"
2 FROM x$ksppi a, x$ksppcv b, x$ksppsv c WHERE a.indx = b.indx AND a.indx = c.indx AND lower(a.ksppinm)
3 in ('_kqdsn_instance_digits', '_kqdsn_cpu_digits');
Parameter Session Value Instance Value Default?
-------------------------------------------------------- -------------------- -------------------- ---------
_kqdsn_instance_digits 2 2 TRUE
_kqdsn_cpu_digits 3 3 TRUE
SQL> select instance_number from v$instance;
INSTANCE_NUMBER
---------------
1
SQL> select sys_context('userenv','sid') from dual;
SYS_CONTEXT('USERENV','SID')
----------------------------------------------------------------------------
275
SQL> create sequence seq_xff start with 1 increment by 1 minvalue 1 maxvalue 100 scale extend;
Sequence created.
SQL> select seq_xff.nextval from dual;
NEXTVAL
----------
101275001
验证效果
--另外一个会话
[oracle@xifenfei ~]$ sqlplus / as sysdba
SQL*Plus: Release 12.2.0.1.0 Production on Tue Jan 23 02:45:14 2018
Copyright (c) 1982, 2016, Oracle. All rights reserved.
Connected to:
Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production
SQL> select sys_context('userenv','sid') from dual;
SYS_CONTEXT('USERENV','SID')
---------------------------------------------------------
4
SQL> select instance_number from v$instance;
INSTANCE_NUMBER
---------------
1
SQL> select seq_xff.nextval from dual;
NEXTVAL
----------
101004002
这里可以看出来seq的值是固定长度的.而且随着sid或者inst_id 不同而不同,从而实现减少大量数据集中在一个block而引起的各种争用
测试scale noextend
SQL> create sequence seq_xifenfei start with 1 increment by 1 minvalue 1 maxvalue 100 scale noextend;
Sequence created.
SQL> select seq_xifenfei.nextval from dual;
select seq_xifenfei.nextval from dual
*
ERROR at line 1:
ORA-64603: NEXTVAL cannot be instantiated for seq_xifenfei. Widen the sequence
by 4 digits or alter sequence with SCALE EXTEND.
SQL> drop sequence seq_xifenfei;
Sequence dropped.
SQL> create sequence seq_xifenfei start with 1 increment by 1 minvalue 1 maxvalue 1000000 scale noextend;
Sequence created.
SQL> select seq_xifenfei.nextval from dual;
NEXTVAL
----------
1010041
这里可以看出来scale noextend的长度是随着seq值的改变而改变,而且max值不能小于seq本身长度.
ORA-20001: Latest xml inventory is not loaded into table
12.2数据库启动报ORA-20001错
2018-01-22T04:51:19.574350-05:00 Unable to obtain current patch information due to error: 20001, ORA-20001: Latest xml inventory is not loaded into table ORA-06512: at "SYS.DBMS_QOPATCH", line 777 ORA-06512: at "SYS.DBMS_QOPATCH", line 864 ORA-06512: at "SYS.DBMS_QOPATCH", line 2222 ORA-06512: at "SYS.DBMS_QOPATCH", line 740 ORA-06512: at "SYS.DBMS_QOPATCH", line 2247 =========================================================== Dumping current patch information =========================================================== Unable to obtain current patch information due to error: 20001 ===========================================================
patch日志
KUP-05007: Warning: Intra source concurrency disabled because the preprocessor option is being used.
Field Definitions for table OPATCH_XML_INV
Record format DELIMITED BY NEWLINE
Data in file has same endianness as the platform
Reject rows with all null fields
Fields in Data Source:
XML_INVENTORY CHAR (100000000)
Terminated by "UIJSVTBOEIZBEFFQBL"
Trim whitespace same as SQL Loader
DBMS_QOPATCH包是提供PLSQL/SQL接口方式访问数据库补丁安装情况，oracle在启动的时候会去调用这个包查询patch情况，由于ORA-20001 Latest XML inventory is not loaded into table.导致失败，从而出现上述启动错误。
主要相关的sql
CREATE OR REPLACE DIRECTORY "OPATCH_LOG_DIR" AS '/u01/app/oracle/product/11.2.0/dbhome_1/QOpatch';
CREATE OR REPLACE DIRECTORY "OPATCH_SCRIPT_DIR" AS '/u01/app/oracle/product/11.2.0/dbhome_1/QOpatch';
CREATE TABLE "SYS"."OPATCH_XML_INV"
( "XML_INVENTORY" CLOB
)
ORGANIZATION EXTERNAL
( TYPE ORACLE_LOADER
DEFAULT DIRECTORY "OPATCH_SCRIPT_DIR"
ACCESS PARAMETERS
( RECORDS DELIMITED BY NEWLINE CHARACTERSET UTF8
DISABLE_DIRECTORY_LINK_CHECK
READSIZE 8388608
preprocessor opatch_script_dir:'qopiprep.bat'
BADFILE opatch_script_dir:'qopatch_bad.bad'
LOGFILE opatch_log_dir:'qopatch_log.log'
FIELDS TERMINATED BY 'UIJSVTBOEIZBEFFQBL'
MISSING FIELD VALUES ARE NULL
REJECT ROWS WITH ALL NULL FIELDS
(
xml_inventory CHAR(100000000)
)
)
LOCATION
( "OPATCH_SCRIPT_DIR":'qopiprep.bat'
)
)
REJECT LIMIT UNLIMITED;
解决方案
---方案1 SQL> alter system set "_disable_directory_link_check"=true scope=spfile; System altered. SQL> shutdown immediate; Database closed. Database dismounted. ORACLE instance shut down. SQL> startup ORACLE instance started. Total System Global Area 2432696320 bytes Fixed Size 8795664 bytes Variable Size 654313968 bytes Database Buffers 1761607680 bytes Redo Buffers 7979008 bytes Database mounted. Database opened. SQL> SELECT a.ksppinm "Parameter", 2 b.ksppstvl "Session Value", 3 c.ksppstvl "Instance Value" 4 FROM x$ksppi a, 5 x$ksppcv b, 6 x$ksppsv c 7 WHERE a.indx = b.indx 8 AND a.indx = c.indx 9 AND a.ksppinm LIKE '/_disable_direc%' escape '/' ; Parameter -------------------------------------------------------------------------------- Session Value -------------------------------------------------------------------------------- Instance Value -------------------------------------------------------------------------------- _disable_directory_link_check TRUE TRUE SQL> exit $ORACLE_HOME/sqlpatch/datapatch ---方案2 SQL> create or replace directory OPATCH_LOG_DIR as '<complete path to QOpatch>'; Directory created. SQL> create or replace directory OPATCH_SCRIPT_DIR as '<complete path to QOpatch>'; Directory created. ---方案3 cd $ORACLE_HOME/sqlpatch ./datapatch -verbose ---方案4 chmod 775 -r $ORACLE_HOME/rdbms/log $ORACLE_HOME/OPatch/datapatch -prereq
参考MOS
Queryable Patch Inventory – Issues/Solutions for ORA-20001: Latest xml inventory is not loaded into table (Doc ID 1602089.1)
12.2:DB Alert.log shows ORA-20001: Latest xml inventory,ORA-06512: at “SYS.DBMS_QOPATCH”,”KUP-04004: error while reading file” (Doc ID 2323937.1)
Assistant: Download Reference for Oracle Database/GI PSU, SPU(CPU), Bundle Patches, Patchsets and Base Releases—201801
|
Patchsets |
|
|
12.1.0.2 (12.1.0.2.0 PATCH SET FOR ORACLE DATABASE SERVER) |
|
|
11.2.0.4 (11.2.0.4.0 PATCH SET FOR ORACLE DATABASE SERVER) |
|
|
11.2.0.3 (11.2.0.3.0 PATCH SET FOR ORACLE DATABASE SERVER) |
|
|
11.2.0.2 (11.2.0.2.0 PATCH SET FOR ORACLE DATABASE SERVER) |
|
|
11.1.0.7 (11.1.0.7.0 PATCH SET FOR ORACLE DATABASE SERVER) |
|
|
10.2.0.5 (10.2.0.5 PATCH SET FOR ORACLE DATABASE SERVER) |
|
|
A10.2.0.4 (10.2.0.4.0 PATCH SET FOR ORACLE DATABASE SERVER) |
|
|
B10.2.0.3 (10.2.0.3 PATCH SET FOR ORACLE DATABASE SERVER) |
|
|
10.2.0.2 (10.2.0.2 PATCH SET FOR ORACLE DATABASE SERVER) |
|
|
10.1.0.5 (10.1.0.5 PATCH SET FOR ORACLE DATABASE SERVER) |
|
|
10.1.0.4 (10.1.0.4 PATCH SET FOR ORACLE DATABASE SERVER) |
|
|
10.1.0.3 (10.1.0.3 PATCH SET FOR ORACLE DATABASE SERVER) |
|
|
9.2.0.8 (9.2.0.8 PATCH SET FOR ORACLE DATABASE SERVER) |
|
|
9.2.0.7 (9.2.0.7 PATCH SET FOR ORACLE DATABASE SERVER) |
|
|
9.2.0.6 (9.2.0.6 PATCH SET FOR ORACLE DATABASE SERVER) |
|
|
9.2.0.5 (ORACLE 9I DATABASE SERVER RELEASE 2 – PATCH SET 4 VERSION 9.2.0.5.0) |
|
|
9.2.0.4 (9.2.0.4 PATCH SET FOR ORACLE DATABASE SERVER) |
|
|
9.2.0.3 (9.2.0.3 PATCH SET FOR ORACLE DATABASE SERVER) |
|
|
9.2.0.2 (9.2.0.2 PATCH SET FOR ORACLE DATABASE SERVER) |
|
|
9.0.1.5 (9.0.1.5 PATCHSET) |
|
|
9.0.1.4 (9.0.1.4 PATCH SET FOR ORACLE DATABASE SERVER) |
|
|
9.0.1.3 (9.0.1.3. PATCH SET FOR ORACLE DATA SERVER) |
|
|
8.1.7.4 (8.1.7.4 PATCH SET FOR ORACLE DATA SERVER) |
|
|
8.1.7.3 (8.1.7.3 PATCH SET FOR ORACLE DATA SERVER) |
|
|
8.1.7.2 (8.1.7.2.1 PATCH SET FOR ORACLE DATA SERVER) |
|
|
12.2.0.1 |
|||
| Description | Database RU | GI RU | Windows Bundle Patch |
| JAN2018 (12.2.0.1.180116) | 27105253 | 27100009 | 27162931 |
| NOV2017 (12.2.0.1.171121) | 27010638 | ||
| OCT2017 (12.2.0.1.171017) | 26710464 | 26737266 | 26758841 |
| AUG2017 (12.2.0.1.170814) | 26609817 | 26610291 | 26204214 |
| JUL2017 (12.2.0.1.170718) | 26123830 | 26133434 | 26204212 |
|
12.1.0.2 |
||||
| Description | PSU | GI PSU | Proactive Bundle Patch | Bundle Patch(Windows 32bit & 64bit) |
| JAN2017 (12.1.0.2.180116) | 26925311 | 27010872 | 27010930 | 27162953 |
| OCT2017 (12.1.0.2.171017) | 26713565 | 26635815 | 26635880 | 26720785 |
| AUG2017(12.1.0.2.170814) | 26609783 | 26610308 | 26610322 | 26161726 |
| JUL2017 (12.1.0.2.170718) | 25755742 | 25901062 | 26022196 | 26161724 |
| APR2017 (12.1.0.2.170418) | 25171037 | 25434003 | 25433352 | 25632533 |
| JAN2017 (12.1.0.2.170117) | 24732082 | 24917825 | 24968615 | 25115951 |
| OCT2016 (12.1.0.2.161018) | 24006101 | 24412235 | 24448103 | 24591642 |
| JUL2016 (12.1.0.2.160719) | 23054246 | 23273629 | 23273686 | 23530387 |
| APR2016 (12.1.0.2.160419) | 22291127 | 22646084 | 22899531 | 22809813 |
| JAN2016 (12.1.0.2.160119) | 21948354 | 22191349 | 22243551 | 22310559 |
| OCT2015 | 21359755(12.1.0.2.5) | 21523234(12.1.0.2.5) | 21744410(12.1.0.2.13) | 21821214(12.1.0.2.10) |
| JUL2015 | 20831110(12.1.0.2.4) | 20996835(12.1.0.2.4) | 21188742(12.1.0.2.10) | 21126814(12.1.0.2.7) |
| APR2015 | 20299023(12.1.0.2.3) | 20485724(12.1.0.2.3) | 20698050(12.1.0.2.7) | 20684004(12.1.0.2.4) |
| JAN2015 | 19769480(12.1.0.2.2) | 19954978(12.1.0.2.2) | 20141343(12.1.0.2.4) | 19720843(12.1.0.2.1) |
| OCT2014 | 19303936(12.1.0.2.1) | 19392646(12.1.0.2.1) | 19404326(12.1.0.2.1) | N/A |
|
12.1.0.1 |
|||||
|
Description |
PSU |
GI PSU |
Bundle Patch |
||
|
A |
B |
Windows 64 bit |
Windows 32 bit |
||
|
JUL2016 (12.1.0.1.160719) |
23273935 | ||||
|
APR2016 (12.1.0.1.160419) |
|||||
|
JAN2016 (12.1.0.1.160119) |
|||||
|
OCT2015 |
21352619(12.1.0.1.9) |
21551666(12.1.0.1.9) |
21551685(12.1.0.1.9) |
21744907 (12.1.0.1.21) |
|
|
JUL2015 |
20831107(12.1.0.1.8) |
20996901(12.1.0.1.8) |
20996911(12.1.0.1.8) |
21076681 (12.1.0.1.20) |
|
|
APR2015 |
20299016(12.1.0.1.7) |
20485762(12.1.0.1.7) |
19971331(12.1.0.1.7) |
20558101 (12.1.0.1.18) |
|
|
JAN2015 |
19769486(12.1.0.1.6) |
19971324(12.1.0.1.6) |
19971331(12.1.0.1.6) |
20160748 (12.1.0.1.16) |
|
|
OCT2014 |
19121550(12.1.0.1.5) |
19392372(12.1.0.1.5) |
19392451(12.1.0.1.5) |
19542943 (12.1.0.1.14) |
|
|
JUL2014 |
18522516(12.1.0.1.4) |
18705901(12.1.0.1.4) |
18705972(12.1.0.1.4) |
19062327 (12.1.0.1.11) |
|
|
APR2014 |
18031528(12.1.0.1.3) |
18139660(12.1.0.1.3) |
18413105(12.1.0.1.3) |
18448604 (12.1.0.1.7) |
|
|
JAN2014 |
17552800(12.1.0.1.2) |
17735306 (12.1.0.1.2) |
17977915 (12.1.0.1.3) |
||
|
OCT2013 |
17027533(12.1.0.1.1) |
17272829 (12.1.0.1.1) |
17363796(12.1.0.1.1) |
17363795(12.1.0.1.1) |
|
|
11.2.0.4 |
||||
| Description | PSU | SPU(CPU) | GI PSU | Bundle Patch (Windows 32bit & 64bit) |
| JAN2018 (11.2.0.4.180116) | 26925576 | N/A | 27107360 | 27162965 |
| OCT2017 (11.2.0.4.171017) | 26392168 | 26474853 | 26635745 | 26581376 |
| AUG2017 (11.2.0.4.170814) | 26609445 | N/A | 26610246 | 26194138 |
| JUL2017 (11.2.0.4.170718) | 25869727 | 25879656 | 26030799 | 26194136 |
| APR2017 (11.2.0.4.170418) | 24732075 | 25369547 | 25476126 | 25632525 |
| JAN2017 | N/A | N/A | N/A | N/A |
| OCT2016 (11.2.0.4.161018) | 24006111 | 24433711 | 24436338 | 24591646 |
| JUL2016 (11.2.0.4.160719) | 23054359 | 23177648 | 23274134 | 23530402 |
| APR2016 (11.2.0.4.160419) | 22502456 | 22502493 | 22646198 | 22839608 |
| JAN2016 (11.2.0.4.160119) | 21948347 | 21972320 | 22191577 | 22310544 |
| OCT2015 | 21352635 (11.2.0.4.8) | 21352646 | 21523375 (11.2.0.4.8) | 21821802 (11.2.0.4.20) |
| JUL2015 | 20760982 (11.2.0.4.7) | 20803583 | 20996923 (11.2.0.4.7) | 21469106 (11.2.0.4.18) |
| APR2015 | 20299013 (11.2.0.4.6) | 20299015 | 20485808 (11.2.0.4.6) | 20544696 (11.2.0.4.15) |
| JAN2015 | 19769489 (11.2.0.4.5) | 19854503 | 19955028 (11.2.0.4.5) | 20127071 (11.2.0.4.12) |
| OCT2014 | 19121551 (11.2.0.4.4) | 19271443 | 19380115 (11.2.0.4.4) | 19651773 (11.2.0.4.10) |
| JUL2014 | 18522509 (11.2.0.4.3) | 18681862 | 18706472 (11.2.0.4.3) | 18842982 (11.2.0.4.7) |
| APR2014 | 18031668 (11.2.0.4.2) | 18139690 | 18139609 (11.2.0.4.2) | 18296644 (11.2.0.4.4) |
| JAN2014 | 17478514 (11.2.0.4.1) | 17551709 | N/A | 17987366 (11.2.0.4.1) |
|
11.2.0.3 |
|||||
|
Description |
PSU |
SPU(CPU) |
GI PSU |
Bundle Patch (Windows 64bit) |
Bundle Patch (Windows 32bit) |
|
JUL2015 |
20760997 (11.2.0.3.15) |
20996944 (11.2.0.3.15) |
|||
|
APR2015 |
20299017 (11.2.0.3.14) |
20485830 (11.2.0.3.14) |
|||
|
JAN2015 |
19769496 (11.2.0.3.13) |
19971343 (11.2.0.3.13) |
|||
|
OCT2014 |
19121548 (11.2.0.3.12) |
19440385 (11.2.0.3.12) |
|||
|
JUL2014 |
18522512 (11.2.0.3.11) |
18706488 (11.2.0.3.11) |
|||
|
APR2014 |
18031683 (11.2.0.3.10) |
18139678 (11.2.0.3.10) |
|||
|
JAN2014 |
17540582 (11.2.0.3.9) |
17735354 (11.2.0.3.9) |
|||
|
OCT2013 |
16902043 (11.2.0.3.8) |
17272731 (11.2.0.3.8) |
|||
|
JUL2013 |
16619892 (11.2.0.3.7) |
16742216 (11.2.0.3.7) |
|||
|
APR2013 |
16056266 (11.2.0.3.6) |
16083653 (11.2.0.3.6) |
|||
|
JAN2013 |
14727310 (11.2.0.3.5) |
14727347 (11.2.0.3.5) |
|||
|
OCT2012 |
14275605 (11.2.0.3.4) |
14275572 (11.2.0.3.4) |
|||
|
JUL2012 |
13923374 (11.2.0.3.3) |
13919095 (11.2.0.3.3) |
|||
|
APR2012 |
13696216 (11.2.0.3.2) |
13696251 (11.2.0.3.2) |
|||
|
JAN2012 |
13343438 (11.2.0.3.1) |
13348650 (11.2.0.3.1) |
|||
|
11.2.0.2 |
|||||
|
Description |
PSU |
SPU(CPU) |
GI PSU |
Bundle Patch (Windows 64bit) |
Bundle Patch (Windows 32bit) |
|
OCT2013 |
17082367 (11.2.0.2.12) |
17272753 (11.2.0.2.12) |
|||
|
JUL2013 |
16619893 (11.2.0.2.11) |
16742320 (11.2.0.2.11) |
|||
|
APR2013 |
16056267 (11.2.0.2.10) |
16166868 (11.2.0.2.10) |
|||
|
JAN2013 |
14727315 (11.2.0.2.9) |
14841385 (11.2.0.2.9) |
|||
|
OCT2012 |
14275621 (11.2.0.2.8) |
14390437 (11.2.0.2.8) |
|||
|
JUL2012 |
13923804 (11.2.0.2.7) |
14192201 (11.2.0.2.7) |
|||
|
APR2012 |
13696224 (11.2.0.2.6) |
13696242 (11.2.0.2.6) |
|||
|
JAN2012 |
13343424 (11.2.0.2.5) |
13653086 (11.2.0.2.5) |
|||
|
OCT2011 |
12827726 (11.2.0.2.4) |
12827731 (11.2.0.2.4) |
|||
|
JUL2011 |
12419331 (11.2.0.2.3) |
12419353 (11.2.0.2.3) |
|||
|
APR2011 |
11724916 (11.2.0.2.2) |
12311357 (11.2.0.2.2) |
|||
|
JAN2011 |
10248523 (11.2.0.2.1) |
N/A |
N/A |
||
|
11.2.0.1 |
||||
|
Description |
PSU |
CPU |
Bundle Patch (Windows 64bit) |
Bundle Patch (Windows 32bit) |
|
JUL2011 |
12419378 (11.2.0.1.6) |
|||
|
APR2011 |
11724930 (11.2.0.1.5) |
|||
|
JAN2011 |
10248516 (11.2.0.1.4) |
|||
|
OCT2010 |
9952216 (11.2.0.1.3) |
|||
|
JUL2010 |
9654983 (11.2.0.1.2) |
|||
|
APR2010 |
9352237 (11.2.0.1.1) |
N/A |
N/A |
|
|
10.2.0.5 |
|||||
|
Description |
PSU |
SPU(CPU) |
Bundle Patch (Windows 64bit) |
Bundle Patch (Windows 32bit) |
Bundle Patch (Windows Itanium) |
|
JUL2015 |
20299014(10.2.0.5.19) |
N/A |
|||
|
APR2015 |
N/A |
N/A |
N/A |
N/A |
N/A |
|
JAN2015 |
19769505(10.2.0.5.18) |
N/A |
|||
|
OCT2014 |
19274523(10.2.0.5.17) |
N/A |
|||
|
JUL2014 |
18522511(10.2.0.5.16) |
N/A |
|||
|
APR2014 |
18031728(10.2.0.5.15) |
N/A |
|||
|
JAN2014 |
17465584(10.2.0.5.14) |
N/A |
|||
|
OCT2013 |
17082365(10.2.0.5.13) |
N/A |
N/A |
||
|
JUL2013 |
16619894(10.2.0.5.12) |
||||
|
APR2013 |
16056270(10.2.0.5.11) |
||||
|
JAN2013 |
14727319(10.2.0.5.10) |
||||
|
OCT2012 |
14275629(10.2.0.5.9) |
||||
|
JUL2012 |
13923855(10.2.0.5.8) |
||||
|
APR2012 |
13632743(10.2.0.5.7) |
||||
|
JAN2012 |
13343471(10.2.0.5.6) |
N/A |
|||
|
OCT2011 |
12827745(10.2.0.5.5) |
N/A |
N/A |
||
|
JUL2011 |
12419392(10.2.0.5.4) |
N/A |
|||
|
APR2011 |
11724962(10.2.0.5.3) |
N/A |
|||
|
JAN2011 |
10248542(10.2.0.5.2) |
N/A |
|||
|
OCT2010 |
9952230(10.2.0.5.1) |
N/A |
|||
|
10.2.0.4 |
|||||
|
Description |
PSU |
SPU(CPU) |
Bundle Patch (Windows 32bit) |
Bundle Patch (Windows 64bit) |
Bundle Patch (Windows Itanium) |
|
JUL2013 |
16619897 (10.2.0.4.17) |
N/A |
N/A |
N/A |
|
|
APR2013 |
16056269 (10.2.0.4.16) |
N/A |
N/A |
N/A |
|
|
JAN2013 |
14736542 (10.2.0.4.15) |
N/A |
N/A |
N/A |
|
|
OCT2012 |
14275630 (10.2.0.4.14) |
N/A |
N/A |
N/A |
|
|
JUL2012 |
13923851 (10.2.0.4.13) |
N/A |
N/A |
N/A |
|
|
APR2012 |
12879933 (10.2.0.4.12) |
N/A |
|||
|
JAN2012 |
12879929 (10.2.0.4.11) |
N/A |
N/A |
||
|
OCT2011 |
12827778 (10.2.0.4.10) |
||||
|
JUL2011 |
12419397 (10.2.0.4.9) |
||||
|
APR2011 |
11724977 (10.2.0.4.8) |
||||
|
JAN2011 |
10248636 (10.2.0.4.7) |
||||
|
OCT2010 |
9952234 (10.2.0.4.6) |
||||
|
JUL2010 |
9654991 (10.2.0.4.5) |
||||
|
APR2010 |
9352164 (10.2.0.4.4) |
||||
|
JAN2010 |
9119284 (10.2.0.4.3) |
||||
|
OCT2009 |
8833280 (10.2.0.4.2) |
||||
|
JUL2009 |
8576156 (10.2.0.4.1) |
||||
|
APR2009 |
N/A |
||||
|
JAN2009 |
N/A |
N/A |
|||
|
OCT2008 |
N/A |
N/A |
|||
|
JUL2008 |
N/A |
N/A |
|||
|
10.2.0.3 |
||||
|
Description |
CPU (Unix/Linux) |
Bundle Patch (Windows 32bit) |
Bundle Patch (Windows Itanium) |
Bundle Patch (Windows 64bit) |
|
JAN2009 |
||||
|
OCT2008 |
||||
|
JUL2008 |
||||
|
APR2008 |
||||
|
JAN2008 |
||||
|
OCT2007 |
||||
|
JUL2007 |
||||
|
APR2007 |
||||
|
JAN2007 |
||||
|
10.2.0.2 |
||||
|
Description |
CPU (Unix/Linux) |
Bundle Patch (Windows 32bit) |
Bundle Patch (Windows 64bit) |
Bundle Patch (Windows Itanium) |
|
JAN2009 |
N/A |
N/A |
N/A |
|
|
OCT2008 |
N/A |
N/A |
N/A |
|
|
JUL2008 |
N/A |
N/A |
N/A |
|
|
APR2008 |
N/A |
N/A |
N/A |
|
|
JAN2008 |
N/A |
N/A |
N/A |
|
|
OCT2007 |
||||
|
JUL2007 |
||||
|
APR2007 |
||||
|
JAN2007 |
||||
|
OCT2006 |
||||
|
JUL2006 |
||||
|
APR2006 |
||||
|
10.2.0.1 |
||||
|
Description |
CPU (Unix/Linux) |
Bundle Patch (Windows 32bit) |
Bundle Patch (Windows 64bit) |
Bundle Patch (Windows Itanium) |
|
APR2007 |
N/A |
N/A |
N/A |
|
|
JAN2007 |
||||
|
OCT2006 |
||||
|
JUL2006 |
||||
|
APR2006 |
||||
|
JAN2006 |
||||
|
10.1.0.5 |
|||
|
Description |
CPU (Unix/Linux) |
Bundle Patch (Windows 32bit) |
Bundle Patch (Windows Itanium) |
|
JAN2012 |
|||
|
OCT2011 |
|||
|
JUL2011 |
|||
|
APR2011 |
|||
|
JAN2011 |
N/A |
N/A |
N/A |
|
OCT2010 |
|||
|
JUL2010 |
|||
|
APR2010 |
|||
|
JAN2010 |
|||
|
OCT2009 |
|||
|
JUL2009 |
|||
|
APR2009 |
|||
|
JAN2009 |
|||
|
OCT2008 |
|||
|
JUL2008 |
|||
|
APR2008 |
|||
|
JAN2008 |
|||
|
OCT2007 |
|||
|
JUL2007 |
|||
|
APR2007 |
|||
|
JAN2007 |
|||
|
OCT2006 |
|||
|
JUL2006 |
|||
|
APR2006 |
|||
|
JAN2006 |
|||
|
10.1.0.4 |
|||
|
Description |
CPU (Unix/Linux) |
Bundle Patch (Windows 32bit) |
Bundle Patch (Windows Itanium) |
|
APR2007 |
|||
|
JAN2007 |
|||
|
OCT2006 |
|||
|
JUL2006 |
|||
|
APR2006 |
|||
|
JAN2006 |
|||
|
OCT2005 |
|||
|
JUL2005 |
|||
|
APR2005 |
|||
|
10.1.0.3 |
|||
|
Description |
CPU (Unix/Linux) |
Bundle Patch (Windows 32bit) |
Bundle Patch (Windows Itanium) |
|
JAN2007 |
N/A |
N/A |
|
|
OCT2006 |
N/A |
N/A |
|
|
JUL2006 |
N/A |
N/A |
|
|
APR2006 |
N/A |
N/A |
|
|
JAN2006 |
|||
|
OCT2005 |
|||
|
JUL2005 |
|||
|
APR2005 |
|||
|
JAN2005 |
|||
|
10.1.0.2 |
|||
|
Description |
CPU (Unix/Linux) |
Bundle Patch (Windows 32bit) |
Bundle Patch (Windows Itanium) |
|
APR2005 |
|||
|
JUL2005 |
|||
|
JAN2005 |
|||
|
9.2.0.8 |
|||
|
Description |
CPU (Unix/Linux) |
Bundle Patch (Windows 32bit) |
Bundle Patch (Windows Itanium) |
|
JUL2010 |
|||
|
APR2010 |
N/A |
||
|
JAN2010 |
N/A |
||
|
OCT2009 |
|||
|
JUL2009 |
|||
|
APR2009 |
|||
|
JAN2009 |
|||
|
OCT2008 |
|||
|
JUL2008 |
|||
|
APR2008 |
|||
|
JAN2008 |
|||
|
OCT2007 |
|||
|
JUL2007 |
|||
|
APR2007 |
|||
|
JAN2007 |
N/A |
N/A |
N/A |
|
OCT2006 |
|||
|
9.2.0.7 |
|||
|
Description |
CPU (Unix/Linux) |
Bundle Patch (Windows 32bit) |
Bundle Patch (Windows Itanium) |
|
JUL2007 |
|||
|
APR2007 |
|||
|
JAN2007 |
|||
|
OCT2006 |
|||
|
JUL2006 |
|||
|
APR2006 |
|||
|
JAN2006 |
|||
|
OCT2005 |
|||
|
JUL2005 |
N/A |
N/A |
|
|
9.2.0.6 |
|||
|
Description |
CPU (Unix/Linux) |
Bundle Patch (Windows 32bit) |
Bundle Patch (Windows Itanium) |
|
OCT2006 |
|||
|
JUL2006 |
|||
|
APR2006 |
|||
|
JAN2006 |
|||
|
OCT2005 |
|||
|
JUL2005 |
|||
|
APR2005 |
|||
|
9.2.0.5 |
|||
|
Description |
CPU (Unix/Linux) |
Bundle Patch (Windows 32bit) |
Bundle Patch (Windows Itanium) |
|
OCT2006 |
N/A |
N/A |
|
|
JUL2006 |
N/A |
N/A |
|
|
APR2006 |
N/A |
N/A |
|
|
OCT2005 |
N/A |
N/A |
|
|
JUL2005 |
|||
|
APR2005 |
|||
|
JAN2005 |
|||
|
9.2.0.4 |
|||
|
Description |
CPU (Unix/Linux) |
Bundle Patch (Windows 32bit) |
Bundle Patch (Windows Itanium) |
|
JAN2005 |
|||
|
8.1.7.4 |
||
|
Description |
CPU (Unix/Linux) |
Bundle Patch (Windows 32bit) |
|
JAN2007 |
||
|
OCT2006 |
||
|
JUL2006 |
||
|
APR2006 |
||
|
JAN2006 |
||
|
OCT2005 |
||
|
JUL2005 |
||
|
APR2005 |
||
|
JAN2005 |
||
参考:Assistant: Download Reference for Oracle Database/GI PSU, SPU(CPU), Bundle Patches, Patchsets and Base Releases (文档 ID 2118136.2)
文件read only导致数据库无法open
在一次客户的恢复请求中,系统被黑客入侵,数据库无法启动.虽然system文件可以拷贝,但是无论拷贝到哪里,都无法dbv和open数据库,报O/S-Error: (OS 5)和DBV-00600错误.最后检查发现是文件设置了只读
数据库启动报错
ORACLE 例程已经启动。 Total System Global Area 6881345536 bytes Fixed Size 2130160 bytes Variable Size 4026535696 bytes Database Buffers 2818572288 bytes Redo Buffers 34107392 bytes 数据库装载完毕。 ORA-01113: 文件 1 需要介质恢复 ORA-01110: 数据文件 1: 'D:\APP\ADMINISTRATOR\ORADATA\xifenfei\SYSTEM01.DBF' SQL> recover datafile 1; ORA-00283: 恢复会话因错误而取消 ORA-01114: 将块写入文件 1 时出现 IO 错误 (块 # 1) ORA-01110: 数据文件 1: 'D:\APP\ADMINISTRATOR\ORADATA\xifenfei\SYSTEM01.DBF' ORA-27091: 无法将 I/O 排队 ORA-27041: 无法打开文件 OSD-04002: 无法打开文件 O/S-Error: (OS 5) 拒绝访问。 SQL> recover database until cancel using backup controlfile; ORA-00283: 恢复会话因错误而取消 ORA-01201: 文件 1 标头无法正确写入
alert日志报错
Completed: ALTER DATABASE MOUNT Sun Dec 10 12:03:20 2017 alter database open Errors in file d:\app\administrator\diag\rdbms\xifenfei\xifenfei\trace\xifenfei_ora_6860.trc: ORA-01110: 数据文件 1: 'D:\APP\ADMINISTRATOR\ORADATA\xifenfei\SYSTEM01.DBF' ORA-01114: 将块写入文件 1 时出现 IO 错误 (块 # 1) ORA-27041: 无法打开文件 OSD-04002: 无法打开文件 O/S-Error: (OS 5) 拒绝访问。 ORA-01201: 文件 1 标头无法正确写入 ORA-1113 signalled during: alter database open... Sun Dec 10 12:03:33 2017 ALTER DATABASE RECOVER datafile 1 Media Recovery Start Fast Parallel Media Recovery NOT enabled Media Recovery failed with error 1114 ORA-283 signalled during: ALTER DATABASE RECOVER datafile 1 ...
dbv检查文件
D:\APP\ADMINISTRATOR\ORADATA>dbv file=system01.dbf DBVERIFY: Release 11.1.0.7.0 - Production on 星期日 12月 10 19:06:55 2017 Copyright (c) 1982, 2007, Oracle. All rights reserved. DBV-00600: 致命错误 - [22] [27041] [0] [0]
这里很明显,文件不能写操作的原因,就是由于该属性导致
redhat和oracle linux kernel对应关系
|
Red Hat Enterprise Linux Version / Update |
Red Hat Enterprise Linux – Kernel version / redhat-release string |
Oracle Linux – Kernel version / release strings |
|
Red Hat Enterprise Linux 7 |
|
|
|
Red Hat Enterprise Linux 7 Update 6 |
3.10.0-957.el7.x86_64 |
4.14.35-1818.3.3.el7uek.x86_64 ^ * (x86_64 only) |
|
Red Hat Enterprise Linux 7 Update 5 |
3.10.0-862.el7.x86_64 |
4.1.12-112.16.4.el7uek ^ * (x86_64 only) |
|
Red Hat Enterprise Linux 7 Update 4 |
3.10.0-693.el7.x86_64 |
4.1.12-94.3.9.el7uek ^ * (x86_64 only) |
|
Red Hat Enterprise Linux 7 Update 3 |
3.10.0-514.el7.x86_64 |
4.1.12-61.1.18.el7uek ^ * (x86_64 only) |
|
Red Hat Enterprise Linux 7 Update 2 |
3.10.0-327.el7.x86_64 |
3.8.13-98.6.1.el7uek ^ * (x86_64 only) |
|
Red Hat Enterprise Linux 7 Update 1 |
3.10.0-229.el7.x86_64 |
3.8.13-55.1.6.el7uek ^ * (x86_64 only) |
|
Red Hat Enterprise Linux 7 GA |
3.10.0-123.el7.x86_64 |
3.8.13-35.3.1.el7uek ^ * (x86_64 only) |
|
Red Hat Enterprise Linux 6 |
|
|
|
Red Hat Enterprise Linux Server 6 Update 10 |
2.6.32-754.el6 |
4.1.12-124.16.4.el6uek ^ * (x86_64 only) |
|
Red Hat Enterprise Linux Server 6 Update 9 |
2.6.32-696.el6 |
4.1.12-61.1.28.el6uek ^ * (x86_64 only) |
|
Red Hat Enterprise Linux Server 6 Update 8 |
2.6.32-642.el6 |
4.1.12-37.3.1.el6uek ^ * (x86_64 only) |
|
Red Hat Enterprise Linux Server 6 Update 7 |
2.6.32-573.el6 |
3.8.13-68.3.4.el6uek ^ * (x86_64 only) |
|
Red Hat Enterprise Linux Server 6 Update 6 |
2.6.32-504.el6 |
3.8.13-44.1.1.el6uek ^ * (x86_64 only) |
|
Red Hat Enterprise Linux Server 6 Update 5 |
2.6.32-431.el6 |
3.8.13-16.121.el6uek ^ * (x86_64 only) |
|
Red Hat Enterprise Linux Server 6 Update 4 |
2.6.32-358.el6 |
2.6.39-400.17.1.el6uek ^ * |
|
Red Hat Enterprise Linux Server 6 Update 3 |
2.6.32-279.el6 |
2.6.39-200.24.1.el6uek ^ * |
|
Red Hat Enterprise Linux Server 6 Update 2 |
2.6.32-220.el6 |
2.6.32-100.34.1.el6uek ^ * |
|
Red Hat Enterprise Linux Server 6 Update 1 |
2.6.32-131.el6 |
2.6.32-100.34.1.el6uek ^ * |
|
Red Hat Enterprise Linux Server 6 GA |
2.6.32-71.el6 |
2.6.32-100.28.5.el6uek ^ * |
|
Red Hat Enterprise Linux 5 |
|
|
|
Red Hat Enterprise Linux Server 5 Update 11 |
2.6.18-398.el5 |
2.6.39-400.215.10.el5uek ^ * (x86, x86_64) |
|
Red Hat Enterprise Linux Server 5 Update 10 |
2.6.18-371.el5
|
2.6.39-400.209.1.el5uek ^ * (x86, x86_64) |
|
Red Hat Enterprise Linux Server 5 Update 9 |
2.6.18-348.el5 |
2.6.39-300.26.1.el5uek ^ * (x86, x86_64) |
|
Red Hat Enterprise Linux Server 5 Update 8 |
2.6.18-308.el5 |
2.6.32-300.10.1.el5uek ^ * (x86, x86_64) |
|
Red Hat Enterprise Linux Server 5 Update 7 |
2.6.18-274.el5 |
2.6.32-200.13.1.el5uek ^ * (x86, x86_64) |
|
Red Hat Enterprise Linux Server 5 Update 6 |
2.6.18-238.el5 |
2.6.32-100.26.2.el5uek ^ * (x86_64 only) |
|
Red Hat Enterprise Linux Server 5 Update 5 |
2.6.18-194.el5 |
2.6.18-194.el5 ^ * (x86, x86_64, ia64) |
|
Red Hat Enterprise Linux Server 5 Update 4 |
2.6.18-164.el5 |
2.6.18-164.el5 ^ * (x86, x86_64, ia64) |
|
Red Hat Enterprise Linux Server 5 Update 3 |
2.6.18-128.el5 |
2.6.18-128.el5 ^ * (x86, x86_64, ia64) |
|
Red Hat Enterprise Linux Server 5 Update 2 |
2.6.18-92.el5 |
2.6.18-92.el5 ^ * (x86, x86_64, ia64) |
|
Red Hat Enterprise Linux Server 5 Update 1 |
2.6.18-53.el5 |
2.6.18-53.el5 ^ * (x86, x86_64, ia64) |
|
Red Hat Enterprise Linux Server 5 GA |
2.6.18-8.el5 |
2.6.18-8.el5 ^ * (x86, x86_64, ia64) |
|
Red Hat Enterprise Linux 4 |
|
|
|
Red Hat Enterprise Linux 4 Update 9 |
2.6.9-100.EL |
2.6.9-100.0.0.0.1.EL ^ * (x86, x86_64, ia64) |
|
Red Hat Enterprise Linux 4 Update 8 |
2.6.9-89.EL |
2.6.9-89.0.0.0.1.EL ^ * (x86, x86_64, ia64) |
|
Red Hat Enterprise Linux 4 Update 7 |
2.6.9-78.EL |
2.6.9-78.0.0.0.1.EL ^ * (x86, x86_64, ia64) |
|
Red Hat Enterprise Linux 4 Update 6 |
2.6.9-67.EL |
2.6.9-67.0.0.0.1.EL ^ * (x86, x86_64, ia64) |
|
Red Hat Enterprise Linux 4 Update 5 |
2.6.9-55.EL |
2.6.9-55.0.0.0.2.EL ^ * (x86, x86_64) |
|
Red Hat Enterprise Linux 4 Update 4 |
2.6.9-42.EL |
2.6.9-42.0.0.0.1.EL ^ * (x86, x86_64) |
|
Red Hat Enterprise Linux 4 Update 3 |
2.6.9-34.EL |
No corresponding version |
|
Red Hat Enterprise Linux 4 Update 2 |
2.6.9-22.EL |
No corresponding version |
|
Red Hat Enterprise Linux 4 Update 1 |
2.6.9-11.EL |
No corresponding version |
|
Red Hat Enterprise Linux 4 GA |
2.6.9-5.EL |
No corresponding version |
参考：Comparison of Red Hat and Oracle Linux kernel versions and release strings (文档 ID 560992.1)