标签云
asm恢复 bbed bootstrap$ dul In Memory kcbzib_kcrsds_1 kccpb_sanity_check_2 kfed MySQL恢复 ORA-00312 ORA-00607 ORA-00704 ORA-01110 ORA-01555 ORA-01578 ORA-08103 ORA-600 2131 ORA-600 2662 ORA-600 2663 ORA-600 3020 ORA-600 4000 ORA-600 4137 ORA-600 4193 ORA-600 4194 ORA-600 16703 ORA-600 kcbzib_kcrsds_1 ORA-600 KCLCHKBLK_4 ORA-15042 ORA-15196 ORACLE 12C oracle dul ORACLE PATCH Oracle Recovery Tools oracle加密恢复 oracle勒索 oracle勒索恢复 oracle异常恢复 Oracle 恢复 ORACLE恢复 ORACLE数据库恢复 oracle 比特币 OSD-04016 YOUR FILES ARE ENCRYPTED 勒索恢复 比特币加密文章分类
- Others (2)
- 中间件 (2)
- WebLogic (2)
- 操作系统 (102)
- 数据库 (1,671)
- DB2 (22)
- MySQL (73)
- Oracle (1,533)
- Data Guard (52)
- EXADATA (8)
- GoldenGate (21)
- ORA-xxxxx (159)
- ORACLE 12C (72)
- ORACLE 18C (6)
- ORACLE 19C (14)
- ORACLE 21C (3)
- Oracle 23ai (7)
- Oracle ASM (65)
- Oracle Bug (8)
- Oracle RAC (52)
- Oracle 安全 (6)
- Oracle 开发 (28)
- Oracle 监听 (28)
- Oracle备份恢复 (560)
- Oracle安装升级 (92)
- Oracle性能优化 (62)
- 专题索引 (5)
- 勒索恢复 (78)
- PostgreSQL (18)
- PostgreSQL恢复 (6)
- SQL Server (27)
- SQL Server恢复 (8)
- TimesTen (7)
- 达梦数据库 (2)
- 生活娱乐 (2)
- 至理名言 (11)
- 虚拟化 (2)
- VMware (2)
- 软件开发 (37)
- Asp.Net (9)
- JavaScript (12)
- PHP (2)
- 小工具 (20)
-
最近发表
- Kylin Linux 安装19c
- ORA-600 krse_arc_complete.4
- Oracle 19c 202410补丁(RUs+OJVM)
- ntfs MFT损坏(ntfs文件系统故障)导致oracle异常恢复
- .mkp扩展名oracle数据文件加密恢复
- 清空redo,导致ORA-27048: skgfifi: file header information is invalid
- A_H_README_TO_RECOVER勒索恢复
- 通过alert日志分析客户自行对一个数据库恢复的来龙去脉和点评
- ORA-12514: TNS: 监听进程不能解析在连接描述符中给出的SERVICE_NAME
- ORA-01092 ORA-00604 ORA-01558故障处理
- ORA-65088: database open should be retried
- Oracle 19c异常恢复—ORA-01209/ORA-65088
- ORA-600 16703故障再现
- 数据库启动报ORA-27102 OSD-00026 O/S-Error: (OS 1455)
- .[metro777@cock.li].Elbie勒索病毒加密数据库恢复
- 应用连接错误,初始化mysql数据库恢复
- RAC默认服务配置优先节点
- Oracle 19c RAC 替换私网操作
- 监听报TNS-12541 TNS-12560 TNS-00511错误
- drop tablespace xxx including contents恢复
分类目录归档:ORACLE 12C
ORA-00700: soft internal error, arguments: [kskvmstatact: excessive swapping observed]
有一朋友数据库经常crash,让我帮忙分析和解决该问题
数据库版本
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production PL/SQL Release 12.1.0.2.0 - Production CORE 12.1.0.2.0 Production TNS for Linux: Version 12.1.0.2.0 - Production NLSRTL Version 12.1.0.2.0 - Production
alert日志报错信息
Mon Apr 23 02:14:18 2018 Process 0x0x10f33262f8 appears to be hung while dumping Current time = 464149508, process death time = 464089392 interval = 60000 Called from location UNKNOWN:UNKNOWN Attempting to kill process 0x0x10f33262f8 with OS pid = 30813 OSD kill succeeded for process 0x10f33262f8 Instance Critical Process (pid: 9, ospid: 30813, DBRM) died unexpectedly Mon Apr 23 02:14:21 2018 System state dump requested by (instance=1, osid=30789 (PMON)), summary=[abnormal instance termination]. System State dumped to trace file /u01/app/oracle/diag/rdbms/orcl/orcl/trace/orcl_diag_30809_20180423021421.trc Mon Apr 23 02:14:22 2018 PMON (ospid: 30789): terminating the instance due to error 56710 Mon Apr 23 02:14:22 2018 opiodr aborting process unknown ospid (27086) as a result of ORA-1092 Mon Apr 23 02:14:28 2018 Instance terminated by PMON, pid = 30789
而在类似报错之前,一般有swap不足的报错
Mon Apr 23 02:03:54 2018 WARNING: Heavy swapping observed on system in last 5 mins. pct of memory swapped in [2.01%] pct of memory swapped out [0.51%]. Please make sure there is no memory pressure and the SGA and PGA are configured correctly. Look at DBRM trace file for more details. Errors in file /u01/app/oracle/diag/rdbms/orcl/orcl/trace/orcl_dbrm_30813.trc (incident=854536): ORA-00700: soft internal error, arguments: [kskvmstatact: excessive swapping observed], [], [], [], [], [], [] Incident details in: /u01/app/oracle/diag/rdbms/orcl/orcl/incident/incdir_854536/orcl_dbrm_30813_i854536.trc Mon Apr 23 02:04:02 2018 Dumping diagnostic data in directory=[cdmp_20180423020402], requested by (instance=1, osid=30813 (DBRM))
从这里报错看,由于系统内存不足,导致大量使用swap,从而引起oracle进程被kill
分析系统内存使用情况
[www.xifenfei.com@Oracle ~]$ more /proc/meminfo MemTotal: 66109924 kB MemFree: 359848 kB Buffers: 9308 kB Cached: 1848504 kB SwapCached: 172800 kB Active: 1060368 kB Inactive: 1156100 kB Active(anon): 999208 kB Inactive(anon): 1104860 kB Active(file): 61160 kB Inactive(file): 51240 kB Unevictable: 0 kB Mlocked: 0 kB SwapTotal: 33554428 kB SwapFree: 30516280 kB Dirty: 68 kB Writeback: 0 kB AnonPages: 190936 kB Mapped: 1152196 kB Shmem: 1745380 kB Slab: 70900 kB SReclaimable: 25640 kB SUnreclaim: 45260 kB KernelStack: 5728 kB PageTables: 92488 kB NFS_Unstable: 0 kB Bounce: 0 kB WritebackTmp: 0 kB CommitLimit: 35152108 kB Committed_AS: 70923356 kB VmallocTotal: 34359738367 kB VmallocUsed: 267468 kB VmallocChunk: 34359442996 kB HardwareCorrupted: 0 kB AnonHugePages: 18432 kB HugePages_Total: 30720 HugePages_Free: 30720 HugePages_Rsvd: 0 HugePages_Surp: 0 Hugepagesize: 2048 kB DirectMap4k: 8192 kB DirectMap2M: 2088960 kB DirectMap1G: 65011712 kB [www.xifenfei.com@Oracle ~]$ free -m total used free shared buffers cached Mem: 64560 64200 359 1682 9 1801 -/+ buffers/cache: 62389 2170 Swap: 32767 2977 29790
比较明显系统总共内存64G,配置了60G大页,但是数据库没有使用该大页
数据库使用内存情况
SQL> show sga; Total System Global Area 7.1672E+10 bytes Fixed Size 3719544 bytes Variable Size 2684358280 bytes Database Buffers 6.8719E+10 bytes Redo Buffers 264712192 bytes
比较明显按照上述配置,一共就只有4G的空闲内存,但是oracle sga占用7G,出现大量换页是必然.错误也明显想让数据库使用大页,但是由于配置不当导致数据库无法使用大页而使用系统除大页之外的内存,从而引起系统异常.
这里也说明12c的提示有明显的改善,通过alert的错误提示基本上就可以确定是swap不足导致.
18c新特性:alter system cancel sql
根据18c官方描述cancel sql功能是在18c中引起,但是实测发现在oracle 12.2中已经有了cancel sql功能,可以实现终止掉某个sql的当前sql正在执行的sql语句,而不是传统的直接kill某个会话.ALTER SYSTEM CANCEL SQL语句有四个参数分别为:
--会话1 SQL> set lines 150 SQL> select * from v$version; 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> select sid, serial# from v$session where sid in 2 (select sid from v$mystat where rownum=1); SID SERIAL# ---------- ---------- 278 4019 SQL> create table t_xifenfei tablespace users as select * from dba_source; Table created. SQL> insert into t_xifenfei select * from t_xifenfei; 274132 rows created. <<===没有提交 SQL> select count(*)from t_xifenfei; COUNT(*) ---------- 548264 SQL> insert into t_xifenfei select * from t_xifenfei; 548264 rows created. <<===没有提交 SQL> select count(*)from t_xifenfei; COUNT(*) ---------- 1096528 SQL> insert into t_xifenfei select * from t_xifenfei; --会话2 SQL> select count(*)from t_xifenfei; COUNT(*) ---------- 274132 SQL> alter system cancel sql '278,4019'; System altered. SQL> select count(*)from t_xifenfei; COUNT(*) ---------- 274132 --会话1 SQL> insert into t_xifenfei select * from t_xifenfei; insert into t_xifenfei select * from t_xifenfei * ERROR at line 1: ORA-01013: user requested cancel of current operation SQL> select count(*)from t_xifenfei; COUNT(*) ---------- 1096528
这里可以看到会话1的最后一个insert被cancel,但是前面两个没有提交的insert没有被回滚/提交,看到了cancel sql的功能的实现.