12C数据库报ORA-600 kcbzib_kcrsds_1故障处理

联系:手机/微信(+86 17813235971) QQ(107644445)QQ咨询惜分飞

标题:12C数据库报ORA-600 kcbzib_kcrsds_1故障处理

作者:惜分飞©版权所有[未经本人同意,不得以任何形式转载,否则有进一步追究法律责任的权利.]

有朋友由于主机断电,导致数据库无法启动,需要我们提供恢复支持.通过Oracle数据库异常恢复检查脚本(Oracle Database Recovery Check)检查分析,发现存放在一个磁盘的数据文件scn过小,数据库无法正常open,需要历史redo(已经被覆盖,而且数据库未归档).
20201130202509
20201130202545


对于这种情况,选择强制拉库,然后数据库报ORA-600 kcbzib_kcrsds_1错误
20201130203023
20201130203204

对于此类故障参考以前处理过类似案例:ORA-600 kcbzib_kcrsds_1报错,对数据库scn进行修改,数据库顺利open
20201130203529

再次遇到ORA-600 kokasgi1故障恢复

联系:手机/微信(+86 17813235971) QQ(107644445)QQ咨询惜分飞

标题:再次遇到ORA-600 kokasgi1故障恢复

作者:惜分飞©版权所有[未经本人同意,不得以任何形式转载,否则有进一步追究法律责任的权利.]

接到一个朋友数据库无法启动,报错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$信息
20201129175609


确认sys和system被分别被修改为sysa和systema,由于这个调整导致数据库无法正常启动.把sysa修改为sys,systema修改为system,数据库正常启动,业务测试正常.
20201129175803

sysaux表空间不足—WRH$_ACTIVE_SESSION_HISTORY

联系:手机/微信(+86 17813235971) QQ(107644445)QQ咨询惜分飞

标题:sysaux表空间不足—WRH$_ACTIVE_SESSION_HISTORY

作者:惜分飞©版权所有[未经本人同意,不得以任何形式转载,否则有进一步追究法律责任的权利.]

一个老生常谈的问题遇到oracle sysaux表空间不足,通过awrinfo 分析,初步判断很可能是ash相关表没有正常清理分区导致
20201128205515


进一步分析确认异常对象
20201128205618

20201128205704

通过分析基本上可以确认是由于WRH$_ACTIVE_SESSION_HISTORY没有创建新分析,数据未被及时清理.根据官方WRH$_ACTIVE_SESSION_HISTORY Does Not Get Purged (Doc ID 387914.1)处理建议
alter session set “_swrf_test_action” = 72;
增加新分区,过一段时间之后,自动清理WRH$_ACTIVE_SESSION_HISTORY表历史数据.
20201128210106

对于sysaux表空间不足的情况,还遇到过类似情况:
sysaux中HEATMAP 对象较大
WRI$_ADV_OBJECTS表过大,导致SYSAUX表空间不足

sql server 删除数据库恢复

联系:手机/微信(+86 17813235971) QQ(107644445)QQ咨询惜分飞

标题:sql server 删除数据库恢复

作者:惜分飞©版权所有[未经本人同意,不得以任何形式转载,否则有进一步追究法律责任的权利.]

有客户通过sql server控制台,直接把一个生产库给删除了.删除之后客户没有做任何操作,联系到我们,通过工具反删除恢复,未找到正确文件(根据经验有时候就算恢复出来文件也大量坏块).只能采用底层技术进行恢复,使用sql扫描磁盘发现效果良好
20201121221315


恢复出来mdf文件,但是没有ldf文件,无法直接附加数据库,只能通过强制发放进行附加尝试
20201121222950

20201121222901

20201121222754

20201121222725

运气不错,强制附加成功,数据库可以查询,如果无法附加成功,或者查询表报错,可以通过mdf文件单独处理,最大限度恢复数据
20201121223109

如果你不小心删除了sql server的数据库,请第一时间保护现场,联系我们,可以最大限度恢复数据,减少损失,请参考:专业Sql Server数据库恢复服务,如果需要联系我们:
电话/微信:17813235971    Q Q:107644445QQ咨询惜分飞    E-Mail:dba@xifenfei.com

.eight加密数据库恢复

联系:手机/微信(+86 17813235971) QQ(107644445)QQ咨询惜分飞

标题:.eight加密数据库恢复

作者:惜分飞©版权所有[未经本人同意,不得以任何形式转载,否则有进一步追究法律责任的权利.]

有数据库被加密为扩展名:.id[766CBC00-2815].[tsai.shen@mailfence.com].eight,通过分析该扩展名病毒的mdf文件
20201121173408


该文件压缩前后大小一样,根据经验此文件为全文加密,无法恢复,对于被加密的bak文件,运气不错,可以实现恢复
20201121172527

通过底层处理实现绝大部分业务数据恢复,满足客户的对核心表恢复的需求
20201121173638

如果此类的Sql Server/Oracle等数据库被加密,需要专业恢复技术支持,请联系我们:
电话/微信:17813235971    Q Q:107644445QQ咨询惜分飞    E-Mail:dba@xifenfei.com

xiaolinghelper@firemail.cc加密数据库恢复

联系:手机/微信(+86 17813235971) QQ(107644445)QQ咨询惜分飞

标题:xiaolinghelper@firemail.cc加密数据库恢复

作者:惜分飞©版权所有[未经本人同意,不得以任何形式转载,否则有进一步追究法律责任的权利.]

有客户oracle数据库所在系统被加密,上面的oracle数据文件也被加密,扩展名为:.id[3C525F2A-3031].[xiaolinghelper@firemail.cc].eking
20201121171051


通过对其坏块检查发现每个文件被加密192个block左右,绝大部分数据可以实现完美恢复,甚至数据库可以直接open,实现数据直接导出,业务直接使用
20201121171437

如果您遇到此类加密情况,无法解决请联系我们,提供专业ORACLE数据库恢复技术支持
Phone:17813235971    Q Q:107644445QQ咨询惜分飞    E-Mail:dba@xifenfei.com

加密.CC4H扩展名数据库恢复支持

联系:手机/微信(+86 17813235971) QQ(107644445)QQ咨询惜分飞

标题:加密.CC4H扩展名数据库恢复支持

作者:惜分飞©版权所有[未经本人同意,不得以任何形式转载,否则有进一步追究法律责任的权利.]

接到客户请求,服务器中所有文件被加密为后缀名为.CC4H的病毒,其中包含oracle dmp文件,经过分析,确认可以效果很好的恢复出里面数据(因为加密只是破坏了一点数据)
20201121165014


这里故障比较好处理,通过工具跳过头部损坏恢复剩余数据,工具参考:expdp dmp被加密破坏恢复
20201121165616

如果你有oracle expdp dmp被加密或者破坏,无法正常导入数据库,可以联系我们对其进行恢复处理:提供(ORACLE数据库恢复技术支持):
Phone:17813235971    Q Q:107644445    E-Mail:dba@xifenfei.com
如果你的oracle dmp是exp方式导出,也可以联系我们对其进行处理,参见:
exp dmp文件损坏恢复
oracle dmp被加密恢复
通过百度发现该病毒还有一些变种比如:.CC1H,.CC2H,.CC3H,.CC4H,.CC5H 如果您有其他数据库被此类病毒加密,也可以联系我们进行分析和恢复

模拟19c数据库root pdb undo异常恢复

联系:手机/微信(+86 17813235971) QQ(107644445)QQ咨询惜分飞

标题:模拟19c数据库root pdb undo异常恢复

作者:惜分飞©版权所有[未经本人同意,不得以任何形式转载,否则有进一步追究法律责任的权利.]

对于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成功.实际生产情况,可能比这个要复杂很多

模拟19c数据库pdb undo异常恢复

联系:手机/微信(+86 17813235971) QQ(107644445)QQ咨询惜分飞

标题:模拟19c数据库pdb undo异常恢复

作者:惜分飞©版权所有[未经本人同意,不得以任何形式转载,否则有进一步追究法律责任的权利.]

对于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及其以前版本有很大改进.当然由于测试环境本身比较单一,可能实际生产中会此类故障处理比较复杂

模拟19c数据库redo异常恢复

联系:手机/微信(+86 17813235971) QQ(107644445)QQ咨询惜分飞

标题:模拟19c数据库redo异常恢复

作者:惜分飞©版权所有[未经本人同意,不得以任何形式转载,否则有进一步追究法律责任的权利.]

对于19c在pdb情况下三种常见故障进行了模拟测试:
模拟19c数据库redo异常恢复
模拟19c数据库pdb undo异常恢复
模拟19c数据库root pdb undo异常恢复
模拟oracle 19c数据库redo丢失的恢复操作,模拟数据库有事务,在没有提交的情况下redo丢失故障

[oracle@localhost oradata]$ ss

SQL*Plus: Release 19.0.0.0.0 - Production on Mon Nov 16 16:11:16 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> conn xff/oracle@127.0.0.1/pdb
Connected.
SQL> create table t_xifenfei as select * from dba_objects;

Table created.

SQL> 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;

72351 rows created.

SQL> 
144702 rows created.

SQL> 
289404 rows created.

SQL> 
578808 rows created.

SQL> 

1157616 rows created.

另外一个会话kill数据库并且删除redo

[root@localhost ~]# ps -ef|grep pmon
oracle    38500      1  0 16:08 ?        00:00:00 ora_pmon_ora19c
root      39030  39009  0 16:11 pts/2    00:00:00 grep --color=auto pmon
[root@localhost ~]# kill -9 38500
[root@localhost ~]# ps -ef|grep pmon
root      39042  39009  0 16:11 pts/2    00:00:00 grep --color=auto pmon
[root@localhost ~]# ls -l /u01/app/oracle/oradata/ORA19C/redo*.log
ls: cannot access /u01/app/oracle/oradata/ORA19C/redo*.log: No such file or directory

启动数据库报错ORA-00313 ORA-00312 ORA-27037

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-00313: open failed for members of log group 3 of thread 1
ORA-00312: online log 3 thread 1: '/u01/app/oracle/oradata/ORA19C/redo03.log'
ORA-27037: unable to obtain file status
Linux-x86_64 Error: 2: No such file or directory
Additional information: 7

因为redo全部丢失只能尝试强制拉库

SQL> startup mount pfile='/tmp/pfile'
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.
SQL>  recover database until cancel;
ORA-00279: change 2335666 generated at 11/16/2020 16:08:42 needed for thread 1
ORA-00289: suggestion :
/u01/app/oracle/product/19.2/db_1/dbs/arch1_12_1056620100.dbf
ORA-00280: change 2335666 for thread 1 is in sequence #12


Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
cancel
ORA-01547: warning: RECOVER succeeded but OPEN RESETLOGS would get error below
ORA-01194: file 1 needs more recovery to be consistent
ORA-01110: data file 1: '/u01/app/oracle/oradata/ORA19C/system01.dbf'


ORA-01112: media recovery not started


SQL> alter database open resetlogs;
alter database open resetlogs
*
ERROR at line 1:
ORA-00603: ORACLE server session terminated by fatal error
ORA-01092: ORACLE instance terminated. Disconnection forced
ORA-00600: internal error code, arguments: [kcbzib_kcrsds_1], [], [], [], [],
[], [], [], [], [], [], []
Process ID: 39588
Session ID: 9 Serial number: 32012

数据库报ORA-600 kcbzib_kcrsds_1错误是由于在强制拉库过程中文件异常导致,通过对异常文件进行处理数据库open成功

SQL> recover database;
Media recovery complete.
SQL> alter database open;

Database altered.

SQL> 

SQL> alter session set container=pdb;

Session altered.

SQL> alter database open;

SQL> show pdbs;

    CON_ID CON_NAME                       OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
         2 PDB$SEED                       READ ONLY  NO
         3 PDB                            READ WRITE NO

这个是模拟redo丢失或者损坏故障,在实际的生产故障中可能要比这个复杂很多.