硬件故障恢复出文件之后数据库故障处理

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

标题:硬件故障恢复出文件之后数据库故障处理

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

客户那边硬件故障(raid损坏磁盘超过了极限,导致raid offline),通过硬件恢复出来数据文件,然后尝试自行恢复,我接手的时候大量数据文件resetlogs scn异常.
wrong_resetlogs


重建控制文件报错

WARNING: Default Temporary Tablespace not specified in CREATE DATABASE command
Default Temporary Tablespace will be necessary for a locally managed database in future release
Errors in file /home/oracle/app/diag/rdbms/orcl/orcl/trace/orcl_ora_5949.trc:
ORA-01189: file is from a different RESETLOGS than previous files
ORA-01110: data file 153: '/home/oracle/oracledata/orcl/sysaux02.dbf'
ORA-1503 signalled during: CREATE CONTROLFILE REUSE DATABASE "ORCL" NORESETLOGS  ARCHIVELOG

通过修改文件头然后重建控制文件,可以通过bbed,或者我的小工具Oracle Recovery Tools
bbed解决ORA-01190
Oracle Recovery Tools 解决ORA-01190 ORA-01248等故障
重建control遗漏数据文件,reseltogs报ORA-1555错误处理
然后继续重建ctl发现以下错误

WARNING: Default Temporary Tablespace not specified in CREATE DATABASE command
Default Temporary Tablespace will be necessary for a locally managed database in future release
Errors in file /home/oracle/app/diag/rdbms/orcl/orcl/trace/orcl_ora_34075.trc:
ORA-01200: actual file size of 2015415 is smaller than correct size of 2944000 blocks
ORA-01110: data file 178: '/home/oracle/oracledata/orcl/xifenfei20_10.dbf'
ORA-1503 signalled during: CREATE CONTROLFILE REUSE DATABASE "ORCL" NORESETLOGS  NOARCHIVELOG

通过对比发现是由于客户上传恢复文件异常导致
20230713002257


重新上传文件,然后修改文件头,该问题解决,重建ctl成功,提个醒:对于这种硬件恢复之后文件上次到服务器上进行恢复的,一定要确认上传文件和原文件一致,不然做无用功或者恢复效果差很多
尝试open数据库报ORA-600 2662错误

SQL> alter database open resetlogs;
alter database open resetlogs
*
ERROR at line 1:
ORA-00603: ORACLE server session terminated by fatal error
ORA-00600: internal error code, arguments: [2662], [5], [1653389530], [5],
[1653496702], [12583040], [], [], [], [], [], []
ORA-00600: internal error code, arguments: [2662], [5], [1653389529], [5],
[1653496702], [12583040], [], [], [], [], [], []
ORA-01092: ORACLE instance terminated. Disconnection forced
ORA-00600: internal error code, arguments: [2662], [5], [1653389527], [5],
[1653496702], [12583040], [], [], [], [], [], []
Process ID: 4710
Session ID: 1847 Serial number: 3

这个错误比较简单,一般是scn问题,有过大量的处理经验案例:
使用bbed解决ORA-00600[2662]
硬件故障导致ORA-600 2662错误处理
Patch SCN工具快速解决ORA-600 2662问题
解决好该问题之后,数据库open成功,实现了最大限度抢救数据.

dul支持arm版本Oracle数据库恢复

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

标题:dul支持arm版本Oracle数据库恢复

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

确认数据库名字,DBID,运行在ARM平台

SQL> select name,dbid,PLATFORM_ID,PLATFORM_NAME from v$database;

NAME            DBID PLATFORM_ID
--------- ---------- -----------
PLATFORM_NAME
--------------------------------------------------------------------------------
ARMDB     1195886419          23
Linux OS (AARCH64)
dul恢复ARM平台数据库
[oracle@xifenfei dul]$ ./dul

Data UnLoader: 12.2.0.2.5 - Internal Only - on Sun Jul  9 22:05:51 2023
with 64-bit io functions and the decompression option

Copyright (c) 1994 2023 Bernard van Duijnen All rights reserved.

 Strictly Oracle Internal Use Only


DUL: Warning: ulimit process stack size is only 33554432
Found db_id = 1195886419
Found db_name = ARMDB
DUL> bootstrap;
Probing file = 1, block = 520
. unloading table                BOOTSTRAP$
DUL: Warning: block number is non zero but marked deferred trying to process it anyhow
      60 rows unloaded
Reading BOOTSTRAP.dat 60 entries loaded
Parsing Bootstrap$ contents
Generating dict.ddl for version 12
 OBJ$: segobjno 18, file 1 block 240
 TAB$: segobjno 2, tabno 1, file 1  block 144
 COL$: segobjno 2, tabno 5, file 1  block 144
 USER$: segobjno 10, tabno 1, file 1  block 208
Running generated file "@dict.ddl" to unload the dictionary tables
. unloading table                      OBJ$   23092 rows unloaded
. unloading table                      TAB$    1794 rows unloaded
. unloading table                      COL$  118438 rows unloaded
. unloading table                     USER$      85 rows unloaded
Reading USER.dat 85 entries loaded
Reading OBJ.dat 23092 entries loaded and sorted 23092 entries
Reading TAB.dat 1794 entries loaded
Reading COL.dat 118438 entries loaded and sorted 118438 entries
Reading BOOTSTRAP.dat 60 entries loaded

DUL: Warning: Recreating file "dict.ddl"
Generating dict.ddl for version 12
 OBJ$: segobjno 18, file 1 block 240
 TAB$: segobjno 2, tabno 1, file 1  block 144
 COL$: segobjno 2, tabno 5, file 1  block 144
 USER$: segobjno 10, tabno 1, file 1  block 208
 TABPART$: segobjno 822, file 1 block 5496
 INDPART$: segobjno 827, file 1 block 5536
 TABCOMPART$: segobjno 844, file 1 block 5672
 INDCOMPART$: segobjno 849, file 1 block 5712
 TABSUBPART$: segobjno 834, file 1 block 5592
 INDSUBPART$: segobjno 839, file 1 block 5632
 IND$: segobjno 2, tabno 3, file 1  block 144
 ICOL$: segobjno 2, tabno 4, file 1  block 144
 LOB$: segobjno 2, tabno 6, file 1  block 144
 COLTYPE$: segobjno 2, tabno 7, file 1  block 144
 TYPE$: segobjno 748, tabno 1, file 1  block 4960
 COLLECTION$: segobjno 748, tabno 2, file 1  block 4960
 ATTRIBUTE$: segobjno 748, tabno 3, file 1  block 4960
 LOBFRAG$: segobjno 855, file 1 block 5768
 LOBCOMPPART$: segobjno 858, file 1 block 5792
 UNDO$: segobjno 15, file 1 block 224
 TS$: segobjno 6, tabno 2, file 1  block 176
 PROPS$: segobjno 127, file 1 block 1320
Running generated file "@dict.ddl" to unload the dictionary tables
. unloading table                      OBJ$
DUL: Warning: Recreating file "OBJ.ctl"
   23092 rows unloaded
. unloading table                      TAB$
DUL: Warning: Recreating file "TAB.ctl"
    1794 rows unloaded
. unloading table                      COL$
DUL: Warning: Recreating file "COL.ctl"
  118438 rows unloaded
. unloading table                     USER$
DUL: Warning: Recreating file "USER.ctl"
      85 rows unloaded
. unloading table                  TABPART$     320 rows unloaded
. unloading table                  INDPART$     186 rows unloaded
. unloading table               TABCOMPART$       1 row  unloaded
. unloading table               INDCOMPART$       0 rows unloaded
. unloading table               TABSUBPART$      32 rows unloaded
. unloading table               INDSUBPART$       0 rows unloaded
. unloading table                      IND$    2273 rows unloaded
. unloading table                     ICOL$    4155 rows unloaded
. unloading table                      LOB$     566 rows unloaded
. unloading table                  COLTYPE$    2794 rows unloaded
. unloading table                     TYPE$    4381 rows unloaded
. unloading table               COLLECTION$     983 rows unloaded
. unloading table                ATTRIBUTE$   11584 rows unloaded
. unloading table                  LOBFRAG$       8 rows unloaded
. unloading table              LOBCOMPPART$       0 rows unloaded
. unloading table                     UNDO$      11 rows unloaded
. unloading table                       TS$       5 rows unloaded
. unloading table                    PROPS$      39 rows unloaded
Reading USER.dat 85 entries loaded
Reading OBJ.dat 23092 entries loaded and sorted 23092 entries
Reading TAB.dat 1794 entries loaded
Reading COL.dat 118438 entries loaded and sorted 118438 entries
Reading TABPART.dat 320 entries loaded and sorted 320 entries
Reading TABCOMPART.dat 1 entries loaded and sorted 1 entries
Reading TABSUBPART.dat 32 entries loaded and sorted 32 entries
Reading INDPART.dat 186 entries loaded and sorted 186 entries
Reading INDCOMPART.dat 0 entries loaded and sorted 0 entries
Reading INDSUBPART.dat 0 entries loaded and sorted 0 entries
Reading IND.dat 2273 entries loaded
Reading LOB.dat 566 entries loaded
Reading ICOL.dat 4155 entries loaded
Reading COLTYPE.dat 2794 entries loaded
Reading TYPE.dat
DUL: Notice: Increased the size of DC_TYPES from 4096 to 32768 entries
 4381 entries loaded
Reading ATTRIBUTE.dat 11584 entries loaded
Reading COLLECTION.dat 983 entries loaded
Reading BOOTSTRAP.dat 60 entries loaded
Reading LOBFRAG.dat 8 entries loaded and sorted 8 entries
Reading LOBCOMPPART.dat 0 entries loaded and sorted 0 entries
Reading UNDO.dat 11 entries loaded
Reading TS.dat 5 entries loaded
Reading PROPS.dat 39 entries loaded
Database character set is AL32UTF8
Database national character set is AL16UTF16
DUL> unload table sys.obj$;
. unloading table                      OBJ$   23092 rows unloaded
DUL> 

通过上述测试,证明dul支持arm版本Oracle数据库恢复

ARM上的oracle数据库存储字节序分析—小字节序

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

标题:ARM上的oracle数据库存储字节序分析—小字节序

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

对在ARM CPU上运行的oracle数据库进行分析,判断大小字节序问题
从系统上lscpu看是小字节序

[root@xifenfei ~]# lscpu
Architecture:                    aarch64
CPU op-mode(s):                  32-bit, 64-bit
Byte Order:                      Little Endian
CPU(s):                          8
On-line CPU(s) list:             0-7
Thread(s) per core:              1
Core(s) per socket:              8
Socket(s):                       1
NUMA node(s):                    1
Vendor ID:                       ARM
Model:                           1
Model name:                      Cortex-A76
Stepping:                        r4p1
BogoMIPS:                        125.00
NUMA node0 CPU(s):               0-7
…………
[root@xifenfei ~]# 

从dbid分析字节序问题
数据库中查看dbid值

SQL> select name,dbid,PLATFORM_ID,PLATFORM_NAME from v$database;

NAME            DBID PLATFORM_ID
--------- ---------- -----------
PLATFORM_NAME
--------------------------------------------------------------------------------
ARMDB     1195886419          23
Linux OS (AARCH64)

bbed查看dbid值

BBED> p kcvfh.kcvfhhdr.kccfhdbi
ub4 kccfhdbi                                @28       0x4747c753

BBED> set count 16
        COUNT           16

BBED> d
 File: /tmp/system01.dbf (0)
 Block: 1                Offsets:   28 to   43           Dba:0x00000000
------------------------------------------------------------------------
 53c74747 41524d44 42000000 03040000 

 <32 bytes per line>

从这里可以看出来dbid(1195886419)是倒序存储的,也就证明了arm linux上面运行的oracle 是小字节序的.

ARM Linux(麒麟操作系统)安装Oracle数据库

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

标题:ARM Linux(麒麟操作系统)安装Oracle数据库

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

Oracle在6月底发布了支持ARM cpu的oracle数据库版本19.9,下载页面,选择Oracle Database 19c for LINUX ARM (aarch64)
oracle-arm


安装技术文档参考:database-installation-guide-linux,其中描述目前for arm版本oracle只是认证了操作系统oracle linux(for arm) 8.6+
arm-liunx

在信创平台中没有oracle linux选项,为了让oracle数据库尽可能的运行在信创的硬件和系统上,我选择的麒麟V10版本进行测试安装测试(该版本未被oracle认证,仅供测试),在安装过程中遇到的几个主要坑分享下:
1. 执行runInstaller报错

[oracle@www.xifenfei.com db_1]$ ./runInstaller 
/u01/app/oracle/product/19c/db_1/perl/bin/perl: error while loading shared libraries: 
libnsl.so.1: cannot open shared object file: No such file or directory
[oracle@www.xifenfei.com db_1]$ perl -version

This is perl 5, version 28, subversion 3 (v5.28.3) built for aarch64-linux-thread-multi

Copyright 1987-2020, Larry Wall

Perl may be copied only under the terms of either the Artistic License or the
GNU General Public License, which may be found in the Perl 5 source kit.

Complete documentation for Perl, including FAQ lists, should be found on
this system using "man perl" or "perldoc perl".  If you have access to the
Internet, point your browser at http://www.perl.org/, the Perl Home Page.

这个是由于oracle自带的perl版本依赖包操作系统中不具备,对数据库软件中的perl进行降级即可
2. 安装图形化界面报PRVG-0282 : failed to retrieve the operating system distribution ID错误
PRVG-0282


由于在该版本的cvu_prereq.xml文件中只是写了oel支持,现在的操作系统是Kylin Linux Advanced Server,不在他的列表里面,因此提示该错误,解决方案参考:19.x:Database software installation failed with ” PRVG-0282 : failed to retrieve the operating system distribution ID ” (Doc ID 2894095.1),设置CV_ASSUME_DISTID解决该问题

3. 编译报错类似:
Error in invoking target ‘clean rat_on part_on dm_on olap_on sdo_on rac_off dnfs_off’
Error in invoking target ‘mkldflags ntcontab.o nnfgt.o’
通过查看日志发现是类似以下错误
ar

确认是由于缺少了/opt/rh/devtoolset-8/root/usr/bin/ar程序导致,对其进行安装然后重试编译(出现错误类似一个个分析处理)
经过上述一系列处理,数据库软件终于顺利安装
20230708211250

4. dbca无法正常启动,静默方式直接退出,选择命令方式创建库

--准备好pfile文件,启动库到nomount
CREATE DATABASE armdb
USER SYS IDENTIFIED BY oracle
USER SYSTEM IDENTIFIED BY oracle
LOGFILE GROUP 1 
('/u01/app/oracle/oradata/armdb/redo01a.log') SIZE 200M BLOCKSIZE 512,
GROUP 2 ('/u01/app/oracle/oradata/armdb/redo02a.log') SIZE 200M BLOCKSIZE 512,
GROUP 3 ('/u01/app/oracle/oradata/armdb/redo03a.log') SIZE 200M BLOCKSIZE 512
MAXLOGHISTORY 1
MAXLOGFILES 16
MAXLOGMEMBERS 3
MAXDATAFILES 8192
CHARACTER SET AL32UTF8
NATIONAL CHARACTER SET AL16UTF16
EXTENT MANAGEMENT LOCAL
DATAFILE '/u01/app/oracle/oradata/armdb/system01.dbf'
SIZE 700M REUSE AUTOEXTEND ON NEXT 10240K MAXSIZE UNLIMITED
SYSAUX DATAFILE '/u01/app/oracle/oradata/armdb/sysaux01.dbf'
SIZE 550M REUSE AUTOEXTEND ON NEXT 10240K MAXSIZE UNLIMITED
DEFAULT TABLESPACE users
DATAFILE '/u01/app/oracle/oradata/armdb/users01.dbf'
SIZE 5M REUSE AUTOEXTEND ON MAXSIZE UNLIMITED
DEFAULT TEMPORARY TABLESPACE temp
TEMPFILE '/u01/app/oracle/oradata/armdb/temp01.dbf'
SIZE 20M REUSE AUTOEXTEND ON NEXT 640K MAXSIZE UNLIMITED
UNDO TABLESPACE undotbs1
DATAFILE '/u01/app/oracle/oradata/armdb/undotbs01.dbf'
SIZE 200M REUSE AUTOEXTEND ON NEXT 5120K MAXSIZE UNLIMITED;


--执行以下脚本
@?/rdbms/admin/catalog.sql
@?/rdbms/admin/catproc.sql
@?/rdbms/admin/utlrp.sql
@?/sqlplus/admin/pupbld.sql

上述操作之后,在ARM平台的麒麟V10上安装oracle 数据库的事情基本上完成

[oracle@www.xifenfei.com ~]$ uname -a
Linux www.xifenfei.com.localdomain 4.19.90-24.4.v2101.ky10.aarch64 
#1 SMP Mon May 24 14:45:37 CST 2021 aarch64 aarch64 aarch64 GNU/Linux
[oracle@www.xifenfei.com ~]$ cat /etc/os-release 
NAME="Kylin Linux Advanced Server"
VERSION="V10 (Sword)"
ID="kylin"
VERSION_ID="V10"
PRETTY_NAME="Kylin Linux Advanced Server V10 (Sword)"
ANSI_COLOR="0;31"

[oracle@www.xifenfei.com ~]$ sqlplus / as sysdba

SQL*Plus: Release 19.0.0.0.0 - Production on Sat Jul 8 23:46:42 2023
Version 19.19.0.0.0

Copyright (c) 1982, 2023, Oracle.  All rights reserved.


Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.19.0.0.0

SQL> select name,created ,PLATFORM_NAME from v$database;

NAME      CREATED
--------- ------------------
PLATFORM_NAME
--------------------------------------------------------------------------------
ARMDB     08-JUL-23
Linux OS (AARCH64)

本次测试是基于Oracle官方没有认证的麒麟V10进行,如果希望安装的顺利一些,稳定一些,建议选择Oracle linux(for ARM)8.6+版本

win系统删除oracle数据文件恢复

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

标题:win系统删除oracle数据文件恢复

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

有客户联系我们,说win平台下的数据库,在由于空间紧张,在关闭数据库的情况下删除的两个数据文件,导致数据库无法正常访问很多业务表,需要对其进行恢复,查看alert日志发现大概操作,删除文件之后,启动数据库失败

Completed: alter database mount exclusive
alter database open
Errors in file d:\app\administrator\diag\rdbms\orcl\orcl\trace\orcl_dbw0_4060.trc:
ORA-01157: cannot identify/lock data file 6 - see DBWR trace file
ORA-01110: data file 6: 'D:\DATASPACE\XXXXX.DBF'
ORA-27041: unable to open file
OSD-04002: 无法打开文件
O/S-Error: (OS 2) 系统找不到指定的文件。
Errors in file d:\app\administrator\diag\rdbms\orcl\orcl\trace\orcl_dbw0_4060.trc:
ORA-01157: cannot identify/lock data file 38 - see DBWR trace file
ORA-01110: data file 38: 'D:\DATASPACE\XXXXX24.DBF'
ORA-27041: unable to open file
OSD-04002: 无法打开文件
O/S-Error: (OS 2) 系统找不到指定的文件。
Tue Jun 27 14:50:28 2023
Checker run found 2 new persistent data failures

人工创建被删除文件,启动库报ORA-27047,OSD-04006等错误

Tue Jun 27 16:45:10 2023
ALTER DATABASE OPEN
Errors in file d:\app\administrator\diag\rdbms\orcl\orcl\trace\orcl_dbw0_5456.trc:
ORA-01157: cannot identify/lock data file 6 - see DBWR trace file
ORA-01110: data file 6: 'D:\DATASPACE\XXXXX.DBF'
ORA-27047: unable to read the header block of file
OSD-04006: ReadFile() 失败, 无法读取文件
O/S-Error: (OS 38) 已到文件结尾。

offline相关数据文件,启动库成功,但是job开始报错

Errors in file d:\app\administrator\diag\rdbms\orcl\orcl\trace\orcl_dbw0_5456.trc:
ORA-01157: cannot identify/lock data file 38 - see DBWR trace file
ORA-01110: data file 38: 'D:\DATASPACE\XXXXX24.DBF'
ORA-27041: unable to open file
OSD-04002: 无法打开文件
O/S-Error: (OS 2) 系统找不到指定的文件。
Errors in file d:\app\administrator\diag\rdbms\orcl\orcl\trace\orcl_ora_648.trc:
ORA-01157: 无法标识/锁定数据文件 6 - 请参阅 DBWR 跟踪文件
ORA-01110: 数据文件 6: 'D:\DATASPACE\XXXXX.DBF'
ORA-1157 signalled during: ALTER DATABASE OPEN...
Tue Jun 27 16:48:43 2023
alter database datafile 'D:\DATASPACE\XXXXX.DBF' offline drop
Completed: alter database datafile 'D:\DATASPACE\XXXXX.DBF' offline drop
Tue Jun 27 16:49:08 2023
alter database open
Tue Jun 27 16:49:08 2023
Errors in file d:\app\administrator\diag\rdbms\orcl\orcl\trace\orcl_dbw0_5456.trc:
ORA-01157: cannot identify/lock data file 38 - see DBWR trace file
ORA-01110: data file 38: 'D:\DATASPACE\XXXXX24.DBF'
ORA-27041: unable to open file
OSD-04002: 无法打开文件
O/S-Error: (OS 2) 系统找不到指定的文件。
Errors in file d:\app\administrator\diag\rdbms\orcl\orcl\trace\orcl_ora_3976.trc:
ORA-01157: 无法标识/锁定数据文件 38 - 请参阅 DBWR 跟踪文件
ORA-01110: 数据文件 38: 'D:\DATASPACE\XXXXX24.DBF'
ORA-1157 signalled during: alter database open...
Tue Jun 27 16:49:08 2023
Checker run found 1 new persistent data failures
Tue Jun 27 16:49:28 2023
alter database datafile 'D:\DATASPACE\XXXXX24.DBF' offline drop
Completed: alter database datafile 'D:\DATASPACE\XXXXX24.DBF' offline drop
alter database open
Tue Jun 27 16:49:37 2023
Thread 1 opened at log sequence 145929
  Current log# 3 seq# 145929 mem# 0: D:\APP\ADMINISTRATOR\ORADATA\ORCL\REDO03.LOG
Successful open of redo thread 1
MTTR advisory is disabled because FAST_START_MTTR_TARGET is not set
Tue Jun 27 16:49:37 2023
SMON: enabling cache recovery
Successfully onlined Undo Tablespace 2.
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
No Resource Manager plan active
Tue Jun 27 16:49:39 2023
replication_dependency_tracking turned off (no async multimaster replication found)
Starting background process QMNC
Tue Jun 27 16:49:40 2023
QMNC started with pid=21, OS id=6096 
Completed: alter database open
Tue Jun 27 16:49:43 2023
db_recovery_file_dest_size of 4096 MB is 0.00% used. This is a
user-specified limit on the amount of space that will be used by this
database for recovery-related files, and does not reflect the amount of
space available in the underlying filesystem or ASM diskgroup.
Tue Jun 27 16:49:44 2023
Starting background process CJQ0
Tue Jun 27 16:49:44 2023
CJQ0 started with pid=142, OS id=6036 
Tue Jun 27 16:49:48 2023
Errors in file d:\app\administrator\diag\rdbms\orcl\orcl\trace\orcl_j007_5184.trc:
ORA-12012: 自动执行作业 64 出错
ORA-00376: 此时无法读取文件 6
ORA-01110: 数据文件 6: 'D:\DATASPACE\XXXXX.DBF'
ORA-06512: 在 "XIFENFEI.XXXXXXXX", line 2897
ORA-06512: 在 line 1
Tue Jun 27 16:51:52 2023
Errors in file d:\app\administrator\diag\rdbms\orcl\orcl\trace\orcl_j000_2548.trc:
ORA-12012: 自动执行作业 64 出错
ORA-00376: 此时无法读取文件 6
ORA-01110: 数据文件 6: 'D:\DATASPACE\XXXXX.DBF'
ORA-06512: 在 "XIFENFEI.XXXXXXXX", line 2897
ORA-06512: 在 line 1
Tue Jun 27 16:54:44 2023
Starting background process SMCO
Tue Jun 27 16:54:44 2023
SMCO started with pid=42, OS id=908 
Tue Jun 27 16:55:52 2023

接手现场之后,关闭数据库,使用操作系统层面反删除工具进行扫描恢复,发现其中一个文件(另外一个文件os层面无法恢复)
20230707132040


通过工具检测恢复出来的数据文件,损坏的几个block是文件头部不涉及业务数据,运气不错
20230707135054

另外一个数据文件,从os层面无法恢复,对于这种情况,只能基于底层的block层面进行恢复(恢复没有覆盖的block)
20230707150912
参考类似恢复案例:
win文件系统损坏oracle恢复
Oracle 数据文件大小为0kb或者文件丢失恢复
分享运气超级好的一次drop tablespace 数据恢复
恢复出来的两个数据文件,结合该编辑的其他数据文件通过dul工具恢复其中数据,最大程度抢救客户数据,减少损失.

ORA-01122 ORA-01200故障处理

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

标题:ORA-01122 ORA-01200故障处理

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

由于某种原因客户的数据库启动报ORA-01122 ORA-01200错误
ORA-01200


让客户把system01.dbf文件发给我进行分析,发现system01.dbf文件大于32G(在8k的blocksize库中,默认情况system01.dbf文件不会超过32G),这个明显异常
system01.dbf

检测坏块情况发现4096000之后的block全部为全0块
20230704165111

通过bbed分析文件头记录文件大小
20230704165343

通过bbed修改合适的值,并且把文件截取到适当大小,提供system文件给客户,直接启动库成功,实现数据库完美恢复
20230704165533

通过设置文件头大小和截断合适大小实现本次数据库恢复,以前有过类似恢复:
bbed处理ORA-01200故障

RMAN-06214: Archivelog错误

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

标题:RMAN-06214: Archivelog错误

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

有一个客户他是linux到win环境dg,alert日志报清除fra中日志失败

un Jun 25 10:50:14 2023
Media Recovery Waiting for thread 1 sequence 196437 (in transit)
Sun Jun 25 10:50:26 2023
WARNING: Cannot delete Oracle managed file /u01/oracle/fast_recovery_area/XFFDG/archivelog/2023_05_28/o1_mf_1_192078_l74s4m2l_.arc
Errors in file D:\APP\ADMINISTRATOR\diag\rdbms\XFFwin\XFF\trace\XFF_rfs_1100.trc:
ORA-01265: 无法删除 ARCHIVED LOG /u01/oracle/fast_recovery_area/XFFDG/archivelog/2023_05_28/o1_mf_1_192078_l74s4m2l_.arc
ORA-27056: 无法删除文件
OSD-04029: 无法获取文件属性
O/S-Error: (OS 3) 系统找不到指定的路径。

尝试人工rman删除日志,报RMAN-06214错误

RMAN-06214: Archivelog      /u01/oracle/fast_recovery_area/XFFDG/archivelog/2023_05_29/o1_mf_1_192575_l78zobv0_.arc
RMAN-06214: Archivelog      /u01/oracle/fast_recovery_area/XFFDG/archivelog/2023_05_29/o1_mf_1_192576_l791fo3j_.arc
RMAN-06214: Archivelog      /u01/oracle/fast_recovery_area/XFFDG/archivelog/2023_05_29/o1_mf_1_192577_l7935w3d_.arc
RMAN-06214: Archivelog      /u01/oracle/fast_recovery_area/XFFDG/archivelog/2023_05_29/o1_mf_1_192578_l794y5bc_.arc
RMAN-06214: Archivelog      /u01/oracle/fast_recovery_area/XFFDG/archivelog/2023_05_29/o1_mf_1_192579_l795cngq_.arc
RMAN-06214: Archivelog      /u01/oracle/fast_recovery_area/XFFDG/archivelog/2023_05_29/o1_mf_1_192580_l795con4_.arc
RMAN-06214: Archivelog      /u01/oracle/fast_recovery_area/XFFDG/archivelog/2023_05_29/o1_mf_1_192581_l795jtxk_.arc
RMAN-06214: Archivelog      /u01/oracle/fast_recovery_area/XFFDG/archivelog/2023_05_29/o1_mf_1_192582_l795k97z_.arc
RMAN-06214: Archivelog      /u01/oracle/fast_recovery_area/XFFDG/archivelog/2023_05_29/o1_mf_1_192583_l795noy1_.arc
RMAN-06214: Archivelog      /u01/oracle/fast_recovery_area/XFFDG/archivelog/2023_05_29/o1_mf_1_192584_l795vvjg_.arc
RMAN-06214: Archivelog      /u01/oracle/fast_recovery_area/XFFDG/archivelog/2023_05_29/o1_mf_1_192585_l796y9o2_.arc
RMAN-06214: Archivelog      /u01/oracle/fast_recovery_area/XFFDG/archivelog/2023_05_29/o1_mf_1_192586_l798pk99_.arc
RMAN-06214: Archivelog      /u01/oracle/fast_recovery_area/XFFDG/archivelog/2023_05_29/o1_mf_1_192587_l79bgx33_.arc
RMAN-06214: Archivelog      /u01/oracle/fast_recovery_area/XFFDG/archivelog/2023_05_29/o1_mf_1_192588_l79bm1wf_.arc
RMAN-06214: Archivelog      /u01/oracle/fast_recovery_area/XFFDG/archivelog/2023_05_29/o1_mf_1_192589_l79bm2tn_.arc

crosscheck报ORA-19633错

RMAN> CROSSCHECK ARCHIVELOG ALL;

释放的通道: ORA_DISK_1
释放的通道: ORA_DISK_2
释放的通道: ORA_DISK_3
释放的通道: ORA_DISK_4
分配的通道: ORA_DISK_1
通道 ORA_DISK_1: SID=1717 设备类型=DISK
分配的通道: ORA_DISK_2
通道 ORA_DISK_2: SID=13 设备类型=DISK
分配的通道: ORA_DISK_3
通道 ORA_DISK_3: SID=579 设备类型=DISK
分配的通道: ORA_DISK_4
通道 ORA_DISK_4: SID=1148 设备类型=DISK
对归档日志的验证成功
归档日志文件名=D:\APP\ADMINISTRATOR\FAST_RECOVERY_AREA\XFFWIN\ARCHIVELOG\2023_06_25\O1_MF_1_196451_L9HC90MS_.ARC REC
ID=35113 STAMP=1140433431
对归档日志的验证成功
归档日志文件名=D:\APP\ADMINISTRATOR\FAST_RECOVERY_AREA\XFFWIN\ARCHIVELOG\2023_06_25\O1_MF_1_196452_L9HC9271_.ARC REC
ID=35112 STAMP=1140433425
已交叉检验的 2 对象

RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03009: crosscheck 命令 (ORA_DISK_4 通道上, 在 06/25/2023 11:04:30 上) 失败
ORA-19633: 控制文件记录 30322 与恢复目录不同步

常规方法无法删除归档日志,只能通过dbms包强制删除归档日志

C:\Users\Administrator>sqlplus / as sysdba

SQL*Plus: Release 11.2.0.4.0 Production on 星期日 6月 25 11:05:15 2023

Copyright (c) 1982, 2013, Oracle.  All rights reserved.


连接到:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> execute sys.dbms_backup_restore.resetCfileSection( 11);

PL/SQL 过程已成功完成。

asm磁盘加入vg恢复

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

标题:asm磁盘加入vg恢复

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

又一个客户把asm disk做成pv,加到vg中,并且对lv进行了扩展(ext4的文件系统)
asm-disk-pv


这个客户做了上述操作之后,没有对lv进行写入其他数据,所以破坏较少(主要的破坏就是ext4的每个一段就会置空一部分block预留给文件系统写入元数据使用),通过winhex查看被破坏磁盘发现lvm信息
lvm

对于这种情况,通过对文件头进行修复,结合工具直接拷贝出来数据文件(个别文件元数据损坏通过基于block的方式恢复dbf)
asm-dbf

然后直接恢复dbf中数据文件(对于异常的主要是segment header被置空的tab使用dul单独扫描处理),实现客户数据的最大限度恢复
以前类似文章:
asm disk被加入vg恢复
asm disk被分区,格式化为ext4恢复
pvcreate asm disk导致asm磁盘组异常恢复
再一起asm disk被格式化成ext3文件系统故障恢复
一次完美的asm disk被格式化ntfs恢复
oracle asm disk格式化恢复—格式化为ext4文件系统

ORA-600 kcbr_apply_change_11

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

标题:ORA-600 kcbr_apply_change_11

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

客户active dataguard应用日志报ORA-600 kcbr_apply_change_11错误

Sun Jun 25 10:31:25 2023
ALTER DATABASE RECOVER MANAGED STANDBY DATABASE USING CURRENT  LOGFILE DISCONNECT FROM SESSION
Sun Jun 25 10:31:25 2023
MRP0 started with pid=32, OS id=6380 
 started logmerger process
Sun Jun 25 10:31:30 2023
Managed Standby Recovery starting Real Time Apply
Parallel Media Recovery started with 4 slaves
Waiting for all non-current ORLs to be archived...
All non-current ORLs have been archived.
Media Recovery Log D:\APP\ADMINISTRATOR\FAST_RECOVERY_AREA\xffWIN\ARCHIVELOG\2023_06_23\O1_MF_1_196319_L99NQQSN_.ARC
Sun Jun 25 10:31:31 2023
Errors in file D:\APP\ADMINISTRATOR\diag\rdbms\xffwin\xff\trace\xff_pr03_1540.trc  (incident=180300):
ORA-00600: 内部错误代码, 参数: [kcbr_apply_change_11], [], [], [], [], [], [], [], [], [], [], []
Incident details in: D:\APP\ADMINISTRATOR\diag\rdbms\xffwin\xff\incident\incdir_180300\xff_pr03_1540_i180300.trc
Use ADRCI or Support Workbench to package the incident.
See Note 411.1 at My Oracle Support for error and packaging details.
Completed: ALTER DATABASE RECOVER MANAGED STANDBY DATABASE USING CURRENT  LOGFILE DISCONNECT FROM SESSION
Sun Jun 25 10:31:32 2023
Errors in file D:\APP\ADMINISTRATOR\diag\rdbms\xffwin\xff\trace\xff_pr02_752.trc  (incident=180292):
ORA-00600: 内部错误代码, 参数: [kcbr_apply_change_11], [], [], [], [], [], [], [], [], [], [], []
Incident details in: D:\APP\ADMINISTRATOR\diag\rdbms\xffwin\xff\incident\incdir_180292\xff_pr02_752_i180292.trc
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 D:\APP\ADMINISTRATOR\diag\rdbms\xffwin\xff\trace\xff_rfs_6032.trc:
ORA-01265: 无法删除 ARCHIVED LOG /u01/oracle/fast_recovery_area/xffDG/archivelog/2023_05_28/o1_mf_1_192064_l74rj3jz_.arc
ORA-27056: 无法删除文件
OSD-04029: 无法获取文件属性
O/S-Error: (OS 3) 系统找不到指定的路径。
Archived Log entry 35084 added for thread 1 sequence 196424 rlc 1013082900 ID 0xe513780f dest 3:
RFS[7]: Opened log for thread 1 sequence 196430 dbid -451738353 branch 1013082900
Slave exiting with ORA-600 exception
Errors in file D:\APP\ADMINISTRATOR\diag\rdbms\xffwin\xff\trace\xff_pr02_752.trc:
ORA-00600: 内部错误代码, 参数: [kcbr_apply_change_11], [], [], [], [], [], [], [], [], [], [], []
Sun Jun 25 10:31:41 2023
Errors in file D:\APP\ADMINISTRATOR\diag\rdbms\xffwin\xff\trace\xff_pr04_3696.trc  (incident=180308):
ORA-00600: 内部错误代码, 参数: [kcbr_apply_change_11], [], [], [], [], [], [], [], [], [], [], []
Incident details in: D:\APP\ADMINISTRATOR\diag\rdbms\xffwin\xff\incident\incdir_180308\xff_pr04_3696_i180308.trc
Use ADRCI or Support Workbench to package the incident.
See Note 411.1 at My Oracle Support for error and packaging details.
Sun Jun 25 10:31:41 2023
Errors in file D:\APP\ADMINISTRATOR\diag\rdbms\xffwin\xff\trace\xff_mrp0_6380.trc  (incident=180268):
ORA-00600: 内部错误代码, 参数: [kcbr_apply_change_11], [], [], [], [], [], [], [], [], [], [], []
Incident details in: D:\APP\ADMINISTRATOR\diag\rdbms\xffwin\xff\incident\incdir_180268\xff_mrp0_6380_i180268.trc
Use ADRCI or Support Workbench to package the incident.
See Note 411.1 at My Oracle Support for error and packaging details.
Recovery Slave PR02 previously exited with exception 600
Sun Jun 25 10:31:41 2023
MRP0: Background Media Recovery terminated with error 448
Errors in file D:\APP\ADMINISTRATOR\diag\rdbms\xffwin\xff\trace\xff_pr00_7812.trc:
ORA-00448: 后台进程正常结束
Managed Standby Recovery not using Real Time Apply
Sun Jun 25 10:31:41 2023
Slave exiting with ORA-600 exception
Errors in file D:\APP\ADMINISTRATOR\diag\rdbms\xffwin\xff\trace\xff_pr03_1540.trc:
ORA-00600: 内部错误代码, 参数: [kcbr_apply_change_11], [], [], [], [], [], [], [], [], [], [], []
Sun Jun 25 10:31:43 2023
Sweep [inc][180308]: completed
Sweep [inc][180300]: completed
Sweep [inc][180292]: completed
Sweep [inc][180268]: completed
Sweep [inc2][180300]: completed
Sweep [inc2][180292]: completed
Sweep [inc2][180268]: completed
Errors in file D:\APP\ADMINISTRATOR\diag\rdbms\xffwin\xff\trace\xff_pr04_3696.trc:
ORA-00448: 后台进程正常结束
ORA-00600: 内部错误代码, 参数: [kcbr_apply_change_11], [], [], [], [], [], [], [], [], [], [], []
Errors in file D:\APP\ADMINISTRATOR\diag\rdbms\xffwin\xff\trace\xff_pr04_3696.trc:
ORA-00339: 归档日志未包含任何重做
ORA-00334: 归档日志: 'D:\APP\ADMINISTRATOR\ORADATA\xff\REDO02.LOG'
ORA-00600: 内部错误代码, 参数: [kcbr_apply_change_11], [], [], [], [], [], [], [], [], [], [], []
Slave exiting with ORA-600 exception
Errors in file D:\APP\ADMINISTRATOR\diag\rdbms\xffwin\xff\trace\xff_pr04_3696.trc:
ORA-00600: 内部错误代码, 参数: [kcbr_apply_change_11], [], [], [], [], [], [], [], [], [], [], []

通过分析可能为:Bug 31104809 – ORA-600: [kcbr_apply_change_11] during adg recovery (Doc ID 31104809.8)
官方WORKAROUND:
In ADG, mount the database (do not open it) and restart media recovery; once
the affected redo log sequence is applied, open the ADG in read only mode.

Oracle Recovery Tools恢复csc higher than block scn

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

标题:Oracle Recovery Tools恢复csc higher than block scn

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

有客户强制关闭数据库,结果有数据块报坏块,dbv检查为:csc higher than block scn问题
20230622151852


该问题主要是由于scn异常导致通过Oracle Recovery工具进行修复
20230622151609
20230622151620

dbv再次验证数据块ok,Oracle Recovery完美代替bbed解决该问题
20230622151915

通过OraRecovery工具快速解决csc higher than block scn故障
软件下载:OraRecovery下载
使用说明:使用说明