分类目录归档：数据库

如何判断数据文件是否处于begin backup状态

发表于 2025 年 2 月 10 日 由 惜分飞

在数据库恢复中,经常会遇到由于某种原因对数据库执行了begin backup,但是没有执行end backup,然后导致库无法启动的例子,那么怎么判断当前的库是存在这种情况呢？有两种方法可以对其进行判断:
1. 通过查询v$backup表来确认

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

SQL> select * from v$backup;

     FILE# STATUS                CHANGE# TIME
---------- ------------------ ---------- ------------
         1 NOT ACTIVE         1.1210E+12 09-FEB-25
         2 NOT ACTIVE         1.1210E+12 04-JAN-25
         3 NOT ACTIVE         1.1210E+12 09-FEB-25
         4 NOT ACTIVE         1.1210E+12 09-FEB-25

SQL> alter tablespace users begin backup;

Tablespace altered.

SQL>  select * from v$backup;

     FILE# STATUS                CHANGE# TIME
---------- ------------------ ---------- ------------
         1 NOT ACTIVE         1.1210E+12 09-FEB-25
         2 NOT ACTIVE         1.1210E+12 04-JAN-25
         3 NOT ACTIVE         1.1210E+12 09-FEB-25
         4 ACTIVE             1.1210E+12 09-FEB-25

SQL>  alter tablespace users end backup;

Tablespace altered.

SQL> select * from v$backup;

     FILE# STATUS                CHANGE# TIME
---------- ------------------ ---------- ------------
         1 NOT ACTIVE         1.1210E+12 09-FEB-25
         2 NOT ACTIVE         1.1210E+12 04-JAN-25
         3 NOT ACTIVE         1.1210E+12 09-FEB-25
         4 NOT ACTIVE         1.1210E+12 09-FEB-25

v$backup.status=’ACTIVE’表示该文件处于begin backup状态

2. 通过bbed查看kcvfh.kcvfhsta值
确认所有数据文件都没有处于begin backup状态

SQL> select * from v$backup;

     FILE# STATUS                CHANGE# TIME
---------- ------------------ ---------- ------------
         1 NOT ACTIVE         1.1210E+12 09-FEB-25
         2 NOT ACTIVE         1.1210E+12 04-JAN-25
         3 NOT ACTIVE         1.1210E+12 09-FEB-25
         4 NOT ACTIVE         1.1210E+12 09-FEB-25

list内容列表

[oracle@iZbp11c0qyuuo1gr7j98upZ ~]$ cat /home/oracle/list.txt 
         1 /u01/app/oracle/oradata/xifenfei/system01.dbf
         2 /u01/app/oracle/oradata/xifenfei/sysaux01.dbf
         3 /u01/app/oracle/oradata/xifenfei/undotbs01.dbf
         4 /u01/app/oracle/oradata/xifenfei/users01.dbf

bbed查看kcvfh.kcvfhsta值

[oracle@iZbp11c0qyuuo1gr7j98upZ ~]$ bbed listfile=/home/oracle/list.txt
Password: 

BBED: Release 2.0.0.0.0 - Limited Production on Sun Feb 9 21:20:15 2025

Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.

************* !!! For Oracle Internal Use only !!! ***************

BBED> set file 1
        FILE#           1

BBED> p kcvfh.kcvfhsta
ub2 kcvfhsta                                @138      0x2004 (KCVFHOFZ)

BBED> set file 2
        FILE#           2

BBED> p kcvfh.kcvfhsta
ub2 kcvfhsta                                @138      0x0004 (KCVFHOFZ)

BBED> set file 3
        FILE#           3

BBED> p kcvfh.kcvfhsta
ub2 kcvfhsta                                @138      0x0004 (KCVFHOFZ)

BBED> set file 4
        FILE#           4

BBED> p kcvfh.kcvfhsta
ub2 kcvfhsta                                @138      0x0004 (KCVFHOFZ)

执行database begin backup

SQL> alter database begin backup;

Database altered.

SQL> select * from v$backup;

     FILE# STATUS                CHANGE# TIME
---------- ------------------ ---------- ------------
         1 ACTIVE             1.1210E+12 09-FEB-25
         2 ACTIVE             1.1210E+12 09-FEB-25
         3 ACTIVE             1.1210E+12 09-FEB-25
         4 ACTIVE             1.1210E+12 09-FEB-25

再次bbed查看kcvfh.kcvfhsta值

BBED> set file 1
        FILE#           1

BBED> p kcvfh.kcvfhsta
ub2 kcvfhsta                                @138      0x2001 (KCVFHHBP)

BBED> set file 2
        FILE#           2

BBED>  p kcvfh.kcvfhsta
ub2 kcvfhsta                                @138      0x0001 (KCVFHHBP)

BBED> set file 3
        FILE#           3

BBED>  p kcvfh.kcvfhsta
ub2 kcvfhsta                                @138      0x0001 (KCVFHHBP)

BBED> set file 4
        FILE#           4

BBED>  p kcvfh.kcvfhsta
ub2 kcvfhsta                                @138      0x0001 (KCVFHHBP)

对于非system文件kcvfh.kcvfhsta=0×0001表示begin backup状态
对于system文件kcvfh.kcvfhsta=0×2001表示begin backup状态

发表在 Oracle | 标签为 , , | 评论关闭

CDM备份缺少归档打开数据库报ORA-600 kcbzib_kcrsds_1故障处理

发表于 2025 年 2 月 9 日 由 惜分飞

有客户联系我们,说一个19c的库,由于产生归档较多在备份过程中把部分归档删除而导致没有备份成功,从而使得他们那边一个误操作需要恢复使用备份做不完全恢复,备份厂商进行恢复并尝试强制打开库报ORA-600 kcbzib_kcrsds_1错误
ORA-600-kcbzibz_kcrsds_1

由于客户那边使用的是CDM方式备份,可以较快的准备出来一个新环境,观察客户在应用日志过程中,文件头的scn一直不变,怀疑文件头由于begin backup冻结,对其进行dump,发现确实做了hot backup操作,而且没有end backup
scn
begin-backup
由于缺少归档,不完全恢复没有成功,begin backup 也无法正常结束,对于这种情况,先尝试调整到文件头最大的scn值,尝试打开库

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: 3949615
Session ID: 5111 Serial number: 30040


--重启到mount状态
SQL> set numw 16
col CHECKPOINT_TIME for a40
set lines 150
set pages 1000
SELECT status,
to_char(checkpoint_time,'yyyy-mm-dd hh24:mi:ss') checkpoint_time,FUZZY,checkpoint_change#,
count(*) ROW_NUM
FROM v$datafile_header
GROUP BY status, checkpoint_change#, to_char(checkpoint_time,'yyyy-mm-dd hh24:mi:ss'),fuzzy
ORDER BY status, checkpoint_change#, checkpoint_time;SQL> SQL> SQL> SQL>   2    3    4    5    6  

STATUS  CHECKPOINT_TIME                          FUZ CHECKPOINT_CHANGE#          ROW_NUM
------- ---------------------------------------- --- ------------------ ----------------
ONLINE  2025-02-08 22:43:01                      YES     15626238353558               56

打开库失败,只能找出来数据库最大的block中最大scn,然后调整文件头scn的值,实现数据库open

SQL> oradebug setmypid
Statement processed.
SQL> oradebug DUMPvar SGA kcsgscn_
SQL> kscn8 kcsgscn_ [060017E98, 060017EA0) = 00000000 00000000
SQL> oradebug DUMPvar SGA kcsgscn_
kscn8 kcsgscn_ [060017E98, 060017EA0) = 8CD9C896 00000E4D
SQL> 
SQL> 
SQL> 
SQL> alter database open ;
alter database open 
*
ERROR at line 1:
ORA-01113: file 1 needs media recovery
ORA-01110: data file 1: '/cdmbak/db/xifenfei/ob_data_D-XIFENFEI-SYSTEM_FNO-1_t13930nd'

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

Database altered.

SQL>

使用exp导出客户需要的表,完成本次恢复任务
对于ORA-600 kcbzib_kcrsds_1恢复的情况,以前有过大量恢复案例,修改数据库scn即可
kcbzib_kcrsds_1报错汇总
12C数据库报ORA-600 kcbzib_kcrsds_1故障处理
存储故障,强制拉库报ORA-600 kcbzib_kcrsds_1处理
Patch SCN工具一键恢复ORA-600 kcbzib_kcrsds_1
此类故障处理太多,不一一列举,解决这个错误之后,数据库open成功,然后安排逻辑迁移即可

发表在 Oracle备份恢复 | 标签为 , , | 评论关闭

ORA-07445: exception encountered: core dump [expgod()+43] [IN_PAGE_ERROR]

发表于 2025 年 2 月 8 日 由 惜分飞

数据库在运行过程中报O/S-Error: (OS 23) 数据错误(循环冗余检查)错误

Thu Jan 30 22:00:02 2025
Begin automatic SQL Tuning Advisor run for special tuning task  "SYS_AUTO_SQL_TUNING_TASK"
Thu Jan 30 22:00:04 2025
Errors in file d:\app\administrator\diag\rdbms\orcl\orcl\trace\orcl_j000_12576.trc:
ORA-12012: error on auto execute of job 155962
ORA-01115: IO error reading block from file  (block # )
ORA-01110: data file 1: 'D:\APP\ADMINISTRATOR\ORADATA\ORCL\SYSTEM01.DBF'
ORA-27070: async read/write failed
OSD-04006: ReadFile() 失败, 无法读取文件
O/S-Error: (OS 23) 数据错误(循环冗余检查)。
ORA-06512: at "SYS.DBMS_STATS", line 25836
ORA-06512: at "SYS.DBMS_STATS", line 26171
End automatic SQL Tuning Advisor run for special tuning task  "SYS_AUTO_SQL_TUNING_TASK"
Fri Jan 31 02:00:00 2025
Clearing Resource Manager plan via parameter
Fri Jan 31 08:15:46 2025
Thread 1 advanced to log sequence 4420 (LGWR switch)
  Current log# 1 seq# 4420 mem# 0: D:\APP\ADMINISTRATOR\ORADATA\ORCL\REDO01.LOG
Fri Jan 31 10:53:57 2025
Process J000 died, see its trace file
kkjcre1p: unable to spawn jobq slave process 
Errors in file d:\app\administrator\diag\rdbms\orcl\orcl\trace\orcl_cjq0_1140.trc:
Fri Jan 31 10:53:57 2025
Errors in file d:\app\administrator\diag\rdbms\orcl\orcl\trace\orcl_j000_7916.trc:
ORA-27102: out of memory
OSD-00043: 附加错误信息
O/S-Error: (OS 1455) 页面文件太小，无法完成操作。
Process J000 died, see its trace file
kkjcre1p: unable to spawn jobq slave process 
Errors in file d:\app\administrator\diag\rdbms\orcl\orcl\trace\orcl_cjq0_1140.trc:
Fri Jan 31 10:54:03 2025
Exception [type: ACCESS_VIOLATION, UNABLE_TO_READ] [ADDR:0x18] [PC:0xB778B02, clsdcxini()+90]
ERROR: Unable to normalize symbol name for the following short stack (at offset 199):
dbgexProcessError()+193<-dbgeExecuteForError()+65<-dbgePostErrorKGE()+1726<-dbkePostKGE_kgsf()+75
<-kgeade()+560<-kgerev()+125<-kgerec5()+60<-sss_xcpt_EvalFilterEx()+1869<-sss_xcpt_EvalFilter()+174
<-.1.4_5+59<-00007FFD0245F306<-00007FFD024735AF<-00007FFD023D4AAF<-00007FFD0247231E<-clsdcxini()+90
<-clsdinit()+124<-ksdnfy()+225<-kscnfy()+778<-opirip()+86<-opidrv()+909<-sou2o()+98<-opimai_real()+299
<-opimai()+191<-BackgroundThreadStart()+693<-00007FFD020E7E94<-00007FFD02437AD1
Errors in file d:\app\administrator\diag\rdbms\orcl\orcl\trace\orcl_j000_9920.trc  (incident=39621):
ORA-07445: exception encountered: core dump [clsdcxini()+90][ACCESS_VIOLATION][ADDR:0x18][PC:0xB778B02][UNABLE_TO_READ]
Incident details in: d:\app\administrator\diag\rdbms\orcl\orcl\incident\incdir_39621\orcl_j000_9920_i39621.trc

然后无法正常启动,报Exception [type: IN_PAGE_ERROR, ] [] [PC:0x2C9C015, expgod()+43]错误

Wed Feb 05 09:43:51 2025
Sweep [inc][39621]: completed
Successful mount of redo thread 1, with mount id 1720066005
Database mounted in Exclusive Mode
Lost write protection disabled
Completed: alter database mount exclusive
alter database open
Beginning crash recovery of 1 threads
 parallel recovery started with 3 processes
Started redo scan
Completed redo scan
 read 140 KB redo, 62 data blocks need recovery
Started redo application at
 Thread 1: logseq 4420, block 42375
Wed Feb 05 09:44:00 2025
Recovery of Online Redo Log: Thread 1 Group 1 Seq 4420 Reading mem 0
  Mem# 0: D:\APP\ADMINISTRATOR\ORADATA\ORCL\REDO01.LOG
Completed redo application of 0.09MB
Completed crash recovery at
 Thread 1: logseq 4420, block 42656, scn 94456019
 62 data blocks read, 62 data blocks written, 140 redo k-bytes read
Wed Feb 05 09:44:01 2025
Exception [type: IN_PAGE_ERROR, ] [] [PC:0x2C9C015, expgod()+43]
ERROR: Unable to normalize symbol name for the following short stack (at offset 199):
dbgexProcessError()+193<-dbgeExecuteForError()+65<-dbgePostErrorKGE()+1726
<-dbkePostKGE_kgsf()+75<-kgeade()+560<-kgerev()+125<-kgerec5()+60<-sss_xcpt_EvalFilterEx()+1869
<-sss_xcpt_EvalFilter()+174<-.1.4_5+59<-00007FFD0245F306<-00007FFD024735AF<-00007FFD023D4AAF
<-00007FFD0247231E<-expgod()+43<-xtyopncb()+241<-qctcopn()+613<-qctcopn()+392<-qctcpqb()+290
<-qctcpqbl()+52<-xtydrv()+148<-opitca()+1091<-kksLoadChild()+9008<-kxsGetRuntimeLock()+2320
<-kksfbc()+15225<-kkspsc0()+2117<-kksParseCursor()+181<-opiosq0()+2538<-opiosq()+23<-opiodr()+1662
<-rpidrus()+862<-rpidru()+154<-rpiswu2()+2757<-rpidrv()+6105<-rpisplu()+1607<-kqldFixedTableLoadCols()+345
<-kqldcor()+2534<-kglslod()+352<-kqlslod()+52<-PGOSF455_kqlsublod()+125<-kqllod()+7284<-kglobld()+1354
<-kglobpn()+1900<-kglpim()+336<-qcdlgtd()+260<-qcsfplob()+166<-qcsprfro()+903<-qcsprfro_tree()
+292<-qcsprfro_tree()+373<-qcspafq()+96
Errors in file d:\app\administrator\diag\rdbms\orcl\orcl\trace\orcl_mmon_15468.trc  (incident=39749):
ORA-07445: exception encountered: core dump [expgod()+43] [IN_PAGE_ERROR] [] [PC:0x2C9C015] [] []
Incident details in: d:\app\administrator\diag\rdbms\orcl\orcl\incident\incdir_39749\orcl_mmon_15468_i39749.trc
Wed Feb 05 09:44:02 2025
Thread 1 advanced to log sequence 4421 (thread open)
Thread 1 opened at log sequence 4421
  Current log# 2 seq# 4421 mem# 0: D:\APP\ADMINISTRATOR\ORADATA\ORCL\REDO02.LOG
Successful open of redo thread 1
MTTR advisory is disabled because FAST_START_MTTR_TARGET is not set
Wed Feb 05 09:44:02 2025
SMON: enabling cache recovery
Wed Feb 05 09:44:11 2025
Exception [type: IN_PAGE_ERROR, ] [] [PC:0x2C9C015, expgod()+43]
Errors in file d:\app\administrator\diag\rdbms\orcl\orcl\trace\orcl_ora_9308.trc  (incident=39781):
ORA-07445: ??????: ???? [expgod()+43] [IN_PAGE_ERROR] [] [PC:0x2C9C015] [] []
Incident details in: d:\app\administrator\diag\rdbms\orcl\orcl\incident\incdir_39781\orcl_ora_9308_i39781.trc
Wed Feb 05 09:44:19 2025
PMON (ospid: 12376): terminating the instance due to error 397
Instance terminated by PMON, pid = 12376

基于上述的Exception [type: IN_PAGE_ERROR, ] [] [PC:0x2C9C015, expgod()+43]错误,第一反应就是可能由于底层损坏导致数据块损坏,dbv检查文件是否报错
dbv-system

检查系统日志确认异常
20250208203059
尝试拷贝文件也报错
QQ20250208-203152
已经比较明确由于底层问题,解决给问题之前,需要先对文件系统进行处理,然后再对恢复出来的数据文件恢复数据

发表在 Oracle备份恢复 | 标签为 , , , , , | 评论关闭