联系：手机/微信(+86 17813235971) QQ(107644445)
标题：12C数据库报ORA-600 kcbzib_kcrsds_1故障处理
作者：惜分飞©版权所有[未经本人同意,不得以任何形式转载,否则有进一步追究法律责任的权利.]
有朋友由于主机断电,导致数据库无法启动,需要我们提供恢复支持.通过Oracle数据库异常恢复检查脚本(Oracle Database Recovery Check)检查分析,发现存放在一个磁盘的数据文件scn过小,数据库无法正常open,需要历史redo(已经被覆盖,而且数据库未归档).
联系：手机/微信(+86 17813235971) QQ(107644445)
作者：惜分飞©版权所有[未经本人同意,不得以任何形式转载,否则有进一步追究法律责任的权利.]
接到一个朋友数据库无法启动,报错ORA-00600: 内部错误代码, 参数: [kokasgi1]错误:
Sun Nov 29 14:23:15 2020 Thread 1 advanced to log sequence 28572 (thread open) Thread 1 opened at log sequence 28572 Current log# 3 seq# 28572 mem# 0: /u01/app/oracle/oradata/xifenfei/redo03.log Successful open of redo thread 1 MTTR advisory is disabled because FAST_START_MTTR_TARGET is not set Sun Nov 29 14:23:15 2020 SMON: enabling cache recovery [3505] Successfully onlined Undo Tablespace 2. Undo initialization finished serial:0 start:135724 end:135754 diff:30 (0 seconds) Verifying file header compatibility for 11g tablespace encryption.. Verifying 11g file header compatibility for tablespace encryption completed SMON: enabling tx recovery Database Characterset is ZHS16GBK ORA-00600: 内部错误代码, 参数: [kokasgi1], [], [], [], [], [], [], [], [], [], [], [] Use ADRCI or Support Workbench to package the incident. See Note 411.1 at My Oracle Support for error and packaging details. Errors in file /u01/app/oracle/diag/rdbms/xifenfei/xifenfei/trace/xifenfei_ora_3505.trc: ORA-00600: 内部错误代码, 参数: [kokasgi1], [], [], [], [], [], [], [], [], [], [], [] Errors in file /u01/app/oracle/diag/rdbms/xifenfei/xifenfei/trace/xifenfei_ora_3505.trc: ORA-00600: 内部错误代码, 参数: [kokasgi1], [], [], [], [], [], [], [], [], [], [], [] Error 600 happened during db open, shutting down database USER (ospid: 3505): terminating the instance due to error 600 Instance terminated by USER, pid = 3505 ORA-1092 signalled during: alter database open... opiodr aborting process unknown ospid (3505) as a result of ORA-1092
对于这个错误,以前处理过一些类似故障,参考:
ORA-600 kokasgi1故障恢复
重命名sys用户引起数据库启动报ORA-01092 ORA-00600 kokasgi1错误
通过技术手段实现数据库open,查询user$信息
联系：手机/微信(+86 17813235971) QQ(107644445)
标题：sysaux表空间不足—WRH$_ACTIVE_SESSION_HISTORY
作者：惜分飞©版权所有[未经本人同意,不得以任何形式转载,否则有进一步追究法律责任的权利.]
一个老生常谈的问题遇到oracle sysaux表空间不足,通过awrinfo 分析,初步判断很可能是ash相关表没有正常清理分区导致
联系：手机/微信(+86 17813235971) QQ(107644445)
作者：惜分飞©版权所有[未经本人同意,不得以任何形式转载,否则有进一步追究法律责任的权利.]
对于19c在pdb情况下三种常见故障进行了模拟测试:
模拟19c数据库redo异常恢复
模拟19c数据库pdb undo异常恢复
模拟19c数据库root pdb undo异常恢复
模拟oracle 19c cdb模式下root pdb中undo丢失故障恢复
会话1,pdb中插入大量数据,未提交
SQL> alter session set container=pdb; Session altered. SQL> alter database open; Database altered. SQL> create user xff identified by oracle default tablespace users; grant dba to xff; conn xff/oracle@127.0.0.1/pdb create table t_xifenfei as select * from dba_objects; insert into t_xifenfei select * from t_xifenfei; insert into t_xifenfei select * from t_xifenfei; insert into t_xifenfei select * from t_xifenfei; insert into t_xifenfei select * from t_xifenfei; insert into t_xifenfei select * from t_xifenfei; User created. SQL> Grant succeeded. SQL> Connected. SQL> Table created. SQL> 72351 rows created. SQL> 144702 rows created. SQL> 289404 rows created. SQL> 578808 rows created. SQL> 1157616 rows created. SQL> SQL> SQL>
会话2中root pdb模拟事务
[oracle@localhost ~]$ ss SQL*Plus: Release 19.0.0.0.0 - Production on Mon Nov 16 16:56:01 2020 Version 19.5.0.0.0 Copyright (c) 1982, 2019, Oracle. All rights reserved. Connected to: Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production Version 19.5.0.0.0 SQL> SQL> SQL> conn system/oracle Connected. SQL> create table t_xifenfei tablespace users as select * from dba_objects; insert into t_xifenfei select * from t_xifenfei; insert into t_xifenfei select * from t_xifenfei; insert into t_xifenfei select * from t_xifenfei; insert into t_xifenfei select * from t_xifenfei; insert into t_xifenfei select * from t_xifenfei; Table created. SQL> 72380 rows created. SQL> 144760 rows created. SQL> 289520 rows created. SQL> 579040 rows created. SQL> 1158080 rows created. SQL> SQL>
会话3 abort库并删除root pdb中undo文件
[oracle@localhost ~]$ ss SQL*Plus: Release 19.0.0.0.0 - Production on Mon Nov 16 16:56:55 2020 Version 19.5.0.0.0 Copyright (c) 1982, 2019, Oracle. All rights reserved. Connected to: Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production Version 19.5.0.0.0 SQL> shutdown abort; ORACLE instance shut down. SQL> [oracle@localhost oradata]$ cd ORA19C [oracle@localhost ORA19C]$ ls control01.ctl control02.ctl pdb pdbseed redo01.log redo02.log redo03.log sysaux01.dbf system01.dbf temp01.dbf undotbs01.dbf users01.dbf [oracle@localhost ORA19C]$ rm -rf undotbs01.dbf
启动数据库报ORA-01157 ORA-01110错误
SQL> alter database datafile 4 offline drop;
Database altered.
SQL> alter database open;
Database altered.
SQL> show pdbs;
CON_ID CON_NAME OPEN MODE RESTRICTED
---------- ------------------------------ ---------- ----------
2 PDB$SEED READ ONLY NO
3 PDB MOUNTED
SQL> alter session set container=pdb;
Session altered.
SQL> alter database open;
Database altered.
SQL> conn / as sysdba
Connected.
SQL> show pdbs;
CON_ID CON_NAME OPEN MODE RESTRICTED
---------- ------------------------------ ---------- ----------
2 PDB$SEED READ ONLY NO
3 PDB READ WRITE NO
SQL> SQL> select tablespace_name,segment_name,status from dba_rollback_segs;
TABLESPACE_NAME SEGMENT_NAME STATUS
------------------------------ ------------------------------ --------------------------------
SYSTEM SYSTEM ONLINE
UNDOTBS1 _SYSSMU1_1261223759$ NEEDS RECOVERY
UNDOTBS1 _SYSSMU2_27624015$ NEEDS RECOVERY
UNDOTBS1 _SYSSMU3_2421748942$ NEEDS RECOVERY
UNDOTBS1 _SYSSMU4_625702278$ NEEDS RECOVERY
UNDOTBS1 _SYSSMU5_2101348960$ NEEDS RECOVERY
UNDOTBS1 _SYSSMU6_813816332$ NEEDS RECOVERY
UNDOTBS1 _SYSSMU7_2329891355$ NEEDS RECOVERY
UNDOTBS1 _SYSSMU8_399776867$ NEEDS RECOVERY
UNDOTBS1 _SYSSMU9_1692468413$ NEEDS RECOVERY
UNDOTBS1 _SYSSMU10_930580995$ NEEDS RECOVERY
本次测试比较幸运,虽然undo段状态为NEEDS RECOVERY,但是数据库直接open成功.实际生产情况,可能比这个要复杂很多
联系：手机/微信(+86 17813235971) QQ(107644445)
作者：惜分飞©版权所有[未经本人同意,不得以任何形式转载,否则有进一步追究法律责任的权利.]
对于19c在pdb情况下三种常见故障进行了模拟测试:
模拟19c数据库redo异常恢复
模拟19c数据库pdb undo异常恢复
模拟19c数据库root pdb undo异常恢复
测试在有事务的情况下,删除pdb中的undo数据库异常情况测试
会话1在root pdb中删除表记录,不提交
SQL> show pdbs;
CON_ID CON_NAME OPEN MODE RESTRICTED
---------- ------------------------------ ---------- ----------
2 PDB$SEED READ ONLY NO
3 PDB READ WRITE NO
SQL> delete from system.t_xifenfei;
2316160 rows deleted.
会话2在pdb中删除表记录,不提交
SQL> conn xff/oracle@127.0.0.1/pdb Connected. SQL> delete from xff.t_xifenfei; 72351 rows deleted.
会话3 直接abort库
SQL> shutdown abort; ORACLE instance shut down.
删除pdb中undo文件
[oracle@localhost pdb]$ rm -rf undotbs01.dbf [oracle@localhost pdb]$
启动数据库
SQL> startup ORACLE instance started. Total System Global Area 4999609088 bytes Fixed Size 9145088 bytes Variable Size 905969664 bytes Database Buffers 4076863488 bytes Redo Buffers 7630848 bytes Database mounted. ORA-01157: cannot identify/lock data file 11 - see DBWR trace file ORA-01110: data file 11: '/u01/app/oracle/oradata/ORA19C/pdb/undotbs01.dbf'
offline异常文件,再open库
SQL> alter database datafile 11 offline drop;
alter database datafile 11 offline drop
*
ERROR at line 1:
ORA-01516: nonexistent log file, data file, or temporary file "11" in the current container
SQL> show pdbs;
CON_ID CON_NAME OPEN MODE RESTRICTED
---------- ------------------------------ ---------- ----------
2 PDB$SEED MOUNTED
3 PDB MOUNTED
SQL> alter session set container=pdb;
Session altered.
SQL> alter database datafile 11 offline drop;
Database altered.
SQL> conn / as sysdba
Connected.
SQL> startup
ORACLE instance started.
Total System Global Area 4999609088 bytes
Fixed Size 9145088 bytes
Variable Size 905969664 bytes
Database Buffers 4076863488 bytes
Redo Buffers 7630848 bytes
Database mounted.
Database opened.
open pdb
SQL> alter session set container=pdb;
Session altered.
SQL>
SQL> alter database open;
Database altered.
SQL> conn / as sysdba
Connected.
SQL> show pdbs;
CON_ID CON_NAME OPEN MODE RESTRICTED
---------- ------------------------------ ---------- ----------
2 PDB$SEED READ ONLY NO
3 PDB READ WRITE NO
测试中库open比较简单,后续只要对异常undo进行处理即可
SQL> create undo tablespace undotbs2 datafile '/u01/app/oracle/oradata/ORA19C/pdb/undotbs02.dbf' size 128M autoextend on; Tablespace created. SQL> alter system set undo_tablespace=undotbs2; System altered. SQL> select tablespace_name,segment_name,status from dba_rollback_segs; TABLESPACE_NAME SEGMENT_NAME STATUS ------------------------------ ------------------------------ -------------------------------- SYSTEM SYSTEM ONLINE UNDOTBS1 _SYSSMU1_3588498444$ NEEDS RECOVERY UNDOTBS1 _SYSSMU2_2971032042$ NEEDS RECOVERY UNDOTBS1 _SYSSMU3_3657342154$ NEEDS RECOVERY UNDOTBS1 _SYSSMU4_811969446$ NEEDS RECOVERY UNDOTBS1 _SYSSMU5_3018429039$ NEEDS RECOVERY UNDOTBS1 _SYSSMU6_442110264$ NEEDS RECOVERY UNDOTBS1 _SYSSMU7_2728255665$ NEEDS RECOVERY UNDOTBS1 _SYSSMU8_801938064$ NEEDS RECOVERY UNDOTBS1 _SYSSMU9_647420285$ NEEDS RECOVERY UNDOTBS1 _SYSSMU10_2262159254$ NEEDS RECOVERY SQL> drop tablespace undotbs1 including contents and datafiles; Tablespace dropped.
在测试中,undo有事务的情况下,数据库可以正常open,而且运行了一段时间未crash,在这个方面确实比11g及其以前版本有很大改进.当然由于测试环境本身比较单一,可能实际生产中会此类故障处理比较复杂
 |
|