asm disk 磁盘部分被清空恢复

Posted on 2022 年 03 月 31 日 by 惜分飞

由于未知原因导致磁盘组的一个磁盘前面1G被置空(data磁盘组一共5个1T磁盘,第一个盘未知原因置空了1G数据)

[root@oradb1 ~]#kfed read /dev/mapper/asm-disk1
kfbh.endian:                          0 ; 0x000: 0x00
kfbh.hard:                            0 ; 0x001: 0x00
kfbh.type:                            0 ; 0x002: KFBTYP_INVALID
kfbh.datfmt:                          0 ; 0x003: 0x00
kfbh.block.blk:                       0 ; 0x004: blk=0
kfbh.block.obj:                       0 ; 0x008: file=0
kfbh.check:                           0 ; 0x00c: 0x00000000
kfbh.fcn.base:                        0 ; 0x010: 0x00000000
kfbh.fcn.wrap:                        0 ; 0x014: 0x00000000
kfbh.spare1:                          0 ; 0x018: 0x00000000
kfbh.spare2:                          0 ; 0x01c: 0x00000000
7F539088C400 00000000 00000000 00000000 00000000  [................]
  Repeat 255 times
KFED-00322: Invalid content encountered during block traversal: [kfbtTraverseBlock][Invalid OSM block type][][0]
…………
[root@oradb1 ~]#kfed read /dev/mapper/asm-disk1 aun=999
kfbh.endian:                          0 ; 0x000: 0x00
kfbh.hard:                            0 ; 0x001: 0x00
kfbh.type:                            0 ; 0x002: KFBTYP_INVALID
kfbh.datfmt:                          0 ; 0x003: 0x00
kfbh.block.blk:                       0 ; 0x004: blk=0
kfbh.block.obj:                       0 ; 0x008: file=0
kfbh.check:                           0 ; 0x00c: 0x00000000
kfbh.fcn.base:                        0 ; 0x010: 0x00000000
kfbh.fcn.wrap:                        0 ; 0x014: 0x00000000
kfbh.spare1:                          0 ; 0x018: 0x00000000
kfbh.spare2:                          0 ; 0x01c: 0x00000000
7F8FEB795400 00000000 00000000 00000000 00000000  [................]
  Repeat 255 times
KFED-00322: Invalid content encountered during block traversal: [kfbtTraverseBlock][Invalid OSM block type][][0]

通过分析disk 1和disk 2的前面1001MB数据,以及asm的数据分布特性,可以确认丢失的1000MB数据为39号文件的数据(和客户当时这个库是通过rman还原过来的操作有关系)

对于这样的情况,通过底层扫描,可以很好的恢复数据,参考以前类似文章
asm disk被加入vg恢复
asm disk header 彻底损坏恢复
asm磁盘dd破坏恢复
通过底层恢复,恢复结果如下

dbv检查数据文件

然后把数据库恢复出来数据文件中的数据恢复到新库中,至此完成该case恢复

File #xxx found in data dictionary but not in controlfile. Creating OFFLINE file ‘MISSING00XXX’ in the controlfile

Posted on 2022 年 03 月 25 日 by 惜分飞

联系：手机/微信(+86 17813235971) QQ(107644445)

标题：File #xxx found in data dictionary but not in controlfile. Creating OFFLINE file ‘MISSING00XXX’ in the controlfile

接手客户的库,已经被强制resetlogs 报ORA-600 2662错误

Sat Mar 19 20:07:48 2022
alter database open resetlogs
RESETLOGS is being done without consistancy checks. This may result
in a corrupted database. The database should be recreated.
RESETLOGS after incomplete recovery UNTIL CHANGE 26228222518
Clearing online redo logfile 1 /u2/oradb/oradata/xff/redo01.log
Clearing online log 1 of thread 1 sequence number 0
Clearing online redo logfile 1 complete
Online log /u2/oradb/oradata/xff/redo01.log: Thread 1 Group 1 was previously cleared
Online log /u2/oradb/oradata/xff/redo02.log: Thread 1 Group 2 was previously cleared
Online log /u2/oradb/oradata/xff/redo03.log: Thread 1 Group 3 was previously cleared
Sat Mar 19 20:08:02 2022
Setting recovery target incarnation to 2
Sat Mar 19 20:08:07 2022
Assigning activation ID 2327373166 (0x8ab8e56e)
Thread 1 opened at log sequence 1
  Current log# 1 seq# 1 mem# 0: /u2/oradb/oradata/xff/redo01.log
Successful open of redo thread 1
Sat Mar 19 20:08:14 2022
MTTR advisory is disabled because FAST_START_MTTR_TARGET is not set
Sat Mar 19 20:08:14 2022
SMON: enabling cache recovery
ORA-00600: internal error code, arguments: [2662], [6], [458447781], [6], [458448180], [12583056]
ORA-00600: internal error code, arguments: [2662], [6], [458447780], [6], [458448180], [12583056]
ORA-01092: ORACLE instance terminated. Disconnection forced
ORA-00600: internal error code, arguments: [2662], [6], [458447778], [6], [458448180], [12583056]
Incident details in: /u2/oradb/diag/rdbms/xifenfei/xifenfei/incident/incdir_1181122/xifenfei_ora_19893_i1181122.trc
Errors in file /u2/oradb/diag/rdbms/xifenfei/xifenfei/incident/incdir_1181122/xifenfei_ora_19893_i1181122.trc:
ORA-00603: ORACLE server session terminated by fatal error
ORA-00600: internal error code, arguments: [2662], [6], [458447781], [6], [458448180], [12583056]
ORA-00600: internal error code, arguments: [2662], [6], [458447780], [6], [458448180], [12583056]
ORA-01092: ORACLE instance terminated. Disconnection forced
ORA-00600: internal error code, arguments: [2662], [6], [458447778], [6], [458448180], [12583056]

接手之后尝试获取创建控制文件脚本,报ORA-16433

Mon Mar 21 15:20:37 2022
alter database backup controlfile to trace as '/tmp/ctl'
ORA-16433 signalled during: alter database backup controlfile to trace as '/tmp/ctl'...

经过一些处理之后resetlogs 成功,但是悲剧产生了(客户之前自己重建过ctl,遗漏大量数据文件,然后我参照客户的ctl进行处理)使得新的ctl中遗漏的很多数据文件,库被resetlogs打开，导致部分文件的resetlog scn不一致,另外数据库还有ORA-600 4137错误需要处理

Mon Mar 21 15:35:01 2022
alter database open resetlogs
RESETLOGS is being done without consistancy checks. This may result
in a corrupted database. The database should be recreated.
RESETLOGS after incomplete recovery UNTIL CHANGE 26228222522
Clearing online redo logfile 1 /u2/oradb/oradata/xff/redo01.log
Clearing online redo logfile 1 complete
Resetting resetlogs activation ID 2327373166 (0x8ab8e56e)
Errors in file /u2/oradb/diag/rdbms/xifenfei/xifenfei/trace/xifenfei_ora_4029.trc:
ORA-00367: checksum error in log file header
ORA-00322: log 1 of thread 1 is not current copy
ORA-00312: online log 1 thread 1: '/u2/oradb/oradata/xff/redo01.log'
Errors in file /u2/oradb/diag/rdbms/xifenfei/xifenfei/trace/xifenfei_ora_4029.trc:
ORA-00367: checksum error in log file header
ORA-00322: log 2 of thread 1 is not current copy
ORA-00312: online log 2 thread 1: '/u2/oradb/oradata/xff/redo02.log'
Errors in file /u2/oradb/diag/rdbms/xifenfei/xifenfei/trace/xifenfei_ora_4029.trc:
ORA-00367: checksum error in log file header
ORA-00322: log 3 of thread 1 is not current copy
ORA-00312: online log 3 thread 1: '/u2/oradb/oradata/xff/redo03.log'
Mon Mar 21 15:35:16 2022
Setting recovery target incarnation to 2
Mon Mar 21 15:35:23 2022
Assigning activation ID 2327514749 (0x8abb0e7d)
Thread 1 opened at log sequence 1
  Current log# 1 seq# 1 mem# 0: /u2/oradb/oradata/xff/redo01.log
Successful open of redo thread 1
MTTR advisory is disabled because FAST_START_MTTR_TARGET is not set
Mon Mar 21 15:35:30 2022
SMON: enabling cache recovery
Undo initialization finished serial:0 start:191905344 end:191905754 diff:410 (4 seconds)
Dictionary check beginning
Tablespace 'TEMP' #3 found in data dictionary,
but not in the controlfile. Adding to controlfile.
Tablespace 'DS_POS' #9 found in data dictionary,
but not in the controlfile. Adding to controlfile.
Mon Mar 21 15:36:00 2022
File #498 found in data dictionary but not in controlfile.
Creating OFFLINE file 'MISSING00498' in the controlfile.
…………
File #567 found in data dictionary but not in controlfile.
Creating OFFLINE file 'MISSING00567' in the controlfile.
This file can no longer be recovered so it must be dropped.
Dictionary check complete
Verifying file header compatibility for 11g tablespace encryption..
Verifying 11g file header compatibility for tablespace encryption completed
Mon Mar 21 15:36:08 2022
SMON: enabling tx recovery
*********************************************************************
WARNING: The following temporary tablespaces contain no files.
         This condition can occur when a backup controlfile has
         been restored.  It may be necessary to add files to these
         tablespaces.  That can be done using the SQL statement:
 
         ALTER TABLESPACE <tablespace_name> ADD TEMPFILE
 
         Alternatively, if these temporary tablespaces are no longer
         needed, then they can be dropped.
           Empty temporary tablespace: TEMP
*********************************************************************
Database Characterset is AL32UTF8
No Resource Manager plan active
Errors in file /u2/oradb/diag/rdbms/xifenfei/xifenfei/trace/xifenfei_smon_4017.trc  (incident=1325274):
ORA-00600: internal error code, arguments: [4137], [28.27.4413199], [0], [0], [], [], [], [], [], [], [], []
Incident details in: /u2/oradb/diag/rdbms/xifenfei/xifenfei/incident/incdir_1325274/xifenfei_smon_4017_i1325274.trc
Use ADRCI or Support Workbench to package the incident.
Mon Mar 21 15:36:10 2022
db_recovery_file_dest_size of 49770 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.
Mon Mar 21 15:36:10 2022
Starting background process CJQ0
Mon Mar 21 15:36:10 2022
CJQ0 started with pid=27, OS id=4155 
Mon Mar 21 15:36:10 2022
Errors in file /u2/oradb/diag/rdbms/xifenfei/xifenfei/trace/xifenfei_smon_4017.trc  (incident=1325275):
ORA-00600: internal error code, arguments: [4137], [36.20.1072031], [0], [0], [], [], [], [], [], [], [], []
Incident details in: /u2/oradb/diag/rdbms/xifenfei/xifenfei/incident/incdir_1325275/xifenfei_smon_4017_i1325275.trc
Use ADRCI or Support Workbench to package the incident.
See Note 411.1 at My Oracle Support for error and packaging details.
Mon Mar 21 15:36:10 2022
Completed: alter database open resetlogs

对于类似：File #567 found in data dictionary but not in controlfile.Creating OFFLINE file ‘MISSING00567′ in the controlfile.
错误处理方法:(参照bbed解决ORA-01190
1. 从操作系统中找出来所有遗漏的文件
2. 通过bbed修改文件头信息
3. 重建ctl
4. 重新打开库

Mon Mar 21 16:06:39 2022
alter database open resetlogs
RESETLOGS after complete recovery through change 28991030095
Clearing online redo logfile 1 /u2/oradb/oradata/xff/redo01.log
Clearing online log 1 of thread 1 sequence number 0
Clearing online redo logfile 1 complete
Resetting resetlogs activation ID 2327514749 (0x8abb0e7d)
Online log /u2/oradb/oradata/xff/redo01.log: Thread 1 Group 1 was previously cleared
Online log /u2/oradb/oradata/xff/redo02.log: Thread 1 Group 2 was previously cleared
Online log /u2/oradb/oradata/xff/redo03.log: Thread 1 Group 3 was previously cleared
Mon Mar 21 16:06:53 2022
Setting recovery target incarnation to 2
Mon Mar 21 16:07:00 2022
Assigning activation ID 2327541328 (0x8abb7650)
Thread 1 opened at log sequence 1
  Current log# 1 seq# 1 mem# 0: /u2/oradb/oradata/xff/redo01.log
Successful open of redo thread 1
Mon Mar 21 16:07:07 2022
MTTR advisory is disabled because FAST_START_MTTR_TARGET is not set
Mon Mar 21 16:07:07 2022
SMON: enabling cache recovery
Undo initialization finished serial:0 start:193802264 end:193802294 diff:30 (0 seconds)
Dictionary check beginning
Tablespace 'TEMP' #3 found in data dictionary,
but not in the controlfile. Adding to controlfile.
Mon Mar 21 16:07:38 2022
Dictionary check complete
Verifying file header compatibility for 11g tablespace encryption..
Verifying 11g file header compatibility for tablespace encryption completed
Mon Mar 21 16:07:38 2022
SMON: enabling tx recovery
*********************************************************************
WARNING: The following temporary tablespaces contain no files.
         This condition can occur when a backup controlfile has
         been restored.  It may be necessary to add files to these
         tablespaces.  That can be done using the SQL statement:
 
         ALTER TABLESPACE <tablespace_name> ADD TEMPFILE
 
         Alternatively, if these temporary tablespaces are no longer
         needed, then they can be dropped.
           Empty temporary tablespace: TEMP
*********************************************************************
Database Characterset is AL32UTF8
No Resource Manager plan active
**********************************************************
WARNING: Files may exists in db_recovery_file_dest
that are not known to the database. Use the RMAN command
CATALOG RECOVERY AREA to re-catalog any such files.
If files cannot be cataloged, then manually delete them
using OS command.
One of the following events caused this:
1. A backup controlfile was restored.
2. A standby controlfile was restored.
3. The controlfile was re-created.
4. db_recovery_file_dest had previously been enabled and
   then disabled.
**********************************************************
replication_dependency_tracking turned off (no async multimaster replication found)
Starting background process QMNC
Mon Mar 21 16:07:40 2022
QMNC started with pid=23, OS id=5476 
LOGSTDBY: Validating controlfile with logical metadata
LOGSTDBY: Validation complete
Mon Mar 21 16:07:41 2022
db_recovery_file_dest_size of 49770 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.
Completed: alter database open resetlogs

至此数据库open成功,增加temp文件,然后逻辑迁移库

commit_wait和commit_logging设置不当导致数据库无法正常启动

Posted on 2022 年 03 月 25 日 by 惜分飞

联系：手机/微信(+86 17813235971) QQ(107644445)

标题：commit_wait和commit_logging设置不当导致数据库无法正常启动

客户数据库设置以下参数，突然掉电之后，数据库无法正常启动

  commit_wait              = "NOWAIT"
  commit_logging           = "BATCH"

数据库open报错

alter database open
Block change tracking file is current.
Beginning crash recovery of 1 threads
 parallel recovery started with 31 processes
Started redo scan
Errors in file /media/oracle/diag/rdbms/orcl/orcl/trace/orcl_ora_28711.trc  (incident=8002955):
ORA-00353: log corruption near block 3 change 17372812227460 time 03/20/2022 00:11:51
ORA-00312: online log 12315  thread 1: '/media/oracle/redolog/redo05.log'
Aborting crash recovery due to error 399
Errors in file /media/oracle/diag/rdbms/orcl/orcl/trace/orcl_ora_28711.trc:
ORA-00399: corrupt change description in redo log
ORA-00353: log corruption near block 3 change 17372812227460 time 03/20/2022 00:11:51
ORA-00312: online log 12315 thread 1: '/media/oracle/redolog/redo05.log'
Errors in file /media/oracle/diag/rdbms/orcl/orcl/trace/orcl_ora_28711.trc:
ORA-00399: corrupt change description in redo log
ORA-00353: log corruption near block 3 change 17372812227460 time 03/20/2022 00:11:51
ORA-00312: online log 12315  thread 1: '/media/oracle/redolog/redo05.log'
ORA-399 signalled during: alter database open...

报错信息比较明显是由于redo损坏导致,尝试强制open库

Sun Mar 20 18:32:35 2022
alter database open resetlogs
RESETLOGS is being done without consistancy checks. This may result
in a corrupted database. The database should be recreated.
RESETLOGS after incomplete recovery UNTIL CHANGE 17372812227456
Resetting resetlogs activation ID 1627598093 (0x61032d0d)
Sun Mar 20 18:34:08 2022
Setting recovery target incarnation to 2
Sun Mar 20 18:34:08 2022
Initializing SCN for created control file
Database SCN compatibility initialized to 3
Warning - High Database SCN: Current SCN value is 17372812227459, threshold SCN value is 0
If you have not previously reported this warning on this database, 
please notify Oracle Support so that additional diagnosis can be performed.
Sun Mar 20 18:34:08 2022
Assigning activation ID 1627615603 (0x61037173)
Thread 1 opened at log sequence 1
  Current log# 2 seq# 1 mem# 0: /media/oracle/redolog/redo02.log
Successful open of redo thread 1
MTTR advisory is disabled because FAST_START_MTTR_TARGET is not set
Sun Mar 20 18:34:08 2022
SMON: enabling cache recovery
Errors in file /media/oracle/diag/rdbms/orcl/orcl/trace/orcl_ora_14369.trc  (incident=8003142):
ORA-00600: internal error code, arguments: [2662], [4044], [3964482439], [4044], [3964488833], [12669344], 
Incident details in: /media/oracle/diag/rdbms/orcl/orcl/incident/incdir_8003142/orcl_ora_14369_i8003142.trc
Errors in file /media/oracle/diag/rdbms/orcl/orcl/trace/orcl_ora_14369.trc  (incident=8003143):
ORA-00353: log corruption near block 3 change 17372812227462 time 03/20/2022 18:34:10
ORA-00312: online log 2 thread 1: '/media/oracle/redolog/redo02.log'
ORA-00600: internal error code, arguments: [2662], [4044], [3964482439], [4044], [3964488833], [12669344], 
Incident details in: /media/oracle/diag/rdbms/orcl/orcl/incident/incdir_8003143/orcl_ora_14369_i8003143.trc
Errors in file /media/oracle/diag/rdbms/orcl/orcl/incident/incdir_8003142/orcl_ora_14369_i8003142.trc:
ORA-00399: corrupt change description in redo log
ORA-00353: log corruption near block 3 change 17372812227462 time 03/20/2022 18:34:10
ORA-00312: online log 2 thread 1: '/media/oracle/redolog/redo02.log'
ORA-00600: internal error code, arguments: [2662], [4044], [3964482439], [4044], [3964488833], [12669344], 
Errors in file /media/oracle/diag/rdbms/orcl/orcl/trace/orcl_ora_14369.trc  (incident=8003144):
ORA-00353: log corruption near block 3 change 17372812227462 time 03/20/2022 18:34:10
ORA-00334: archived log: '/media/oracle/redolog/redo02.log'
ORA-00600: internal error code, arguments: [2662], [4044], [3964482439], [4044], [3964488833], [12669344], 
Incident details in: /media/oracle/diag/rdbms/orcl/orcl/incident/incdir_8003144/orcl_ora_14369_i8003144.trc
Sun Mar 20 18:34:11 2022
ORA-00603: ORACLE server session terminated by fatal error
ORA-00600: internal error code, arguments: [2662], [4044], [3964482444], [4044], [3964488833], [12669344], 
ORA-00600: internal error code, arguments: [2662], [4044], [3964482443], [4044], [3964488833], [12669344], 
ORA-01092: ORACLE instance terminated. Disconnection forced
ORA-00600: internal error code, arguments: [2662], [4044], [3964482439], [4044], [3964488833], [12669344],

强制open数据库报ora-600 2662错误,比较常见,通过修改scn再尝试open库

Sun Mar 20 18:38:43 2022
alter database open resetlogs
RESETLOGS is being done without consistancy checks. This may result
in a corrupted database. The database should be recreated.
RESETLOGS after incomplete recovery UNTIL CHANGE 17372812227460
Resetting resetlogs activation ID 1627615603 (0x61037173)
Sun Mar 20 18:40:02 2022
Setting recovery target incarnation to 2
Sun Mar 20 18:40:02 2022
Initializing SCN for created control file
Database SCN compatibility initialized to 3
Warning - High Database SCN: Current SCN value is 17372812227463, threshold SCN value is 0
If you have not previously reported this warning on this database,
please notify Oracle Support so that additional diagnosis can be performed.
Sun Mar 20 18:40:02 2022
Assigning activation ID 1627669665 (0x610444a1)
Thread 1 opened at log sequence 1
Current log# 2 seq# 1 mem# 0: /media/oracle/redolog/redo02.log
Successful open of redo thread 1
MTTR advisory is disabled because FAST_START_MTTR_TARGET is not set
Sun Mar 20 18:40:02 2022
SMON: enabling cache recovery
Undo initialization finished serial:0 start:779809538 end:779809788 diff:250 (2 seconds)
Dictionary check beginning
Tablespace 'TEMP' #3 found in data dictionary,
but not in the controlfile. Adding to controlfile.
Dictionary check complete
Verifying file header compatibility for 11g tablespace encryption..
Verifying 11g file header compatibility for tablespace encryption completed
SMON: enabling tx recovery
*********************************************************************
WARNING: The following temporary tablespaces contain no files.
This condition can occur when a backup controlfile has
been restored. It may be necessary to add files to these
tablespaces. That can be done using the SQL statement:

ALTER TABLESPACE <tablespace_name> ADD TEMPFILE

Alternatively, if these temporary tablespaces are no longer
needed, then they can be dropped.
Empty temporary tablespace: TEMP
*********************************************************************
Database Characterset is AL32UTF8
No Resource Manager plan active
**********************************************************
WARNING: Files may exists in db_recovery_file_dest
that are not known to the database. Use the RMAN command
CATALOG RECOVERY AREA to re-catalog any such files.
If files cannot be cataloged, then manually delete them
using OS command.
One of the following events caused this:
1. A backup controlfile was restored.
2. A standby controlfile was restored.
3. The controlfile was re-created.
4. db_recovery_file_dest had previously been enabled and
then disabled.
**********************************************************
replication_dependency_tracking turned off (no async multimaster replication found)
Starting background process QMNC
Sun Mar 20 18:40:04 2022
QMNC started with pid=55, OS id=16232
LOGSTDBY: Validating controlfile with logical metadata
LOGSTDBY: Validation complete
Sun Mar 20 18:40:05 2022
db_recovery_file_dest_size of 3882 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.
Sun Mar 20 18:40:05 2022
Starting background process CJQ0
Sun Mar 20 18:40:05 2022
CJQ0 started with pid=58, OS id=16251
Completed: alter database open resetlogs

后续增加temp,导出数据到新库,恢复完成