分类目录归档：数据库

客户有一个数据库由于异常断电之后无法正常启动,自行尝试恢复之后但是没有open成功,让可以通过Oracle数据库异常恢复检查脚本(Oracle Database Recovery Check)脚本收集信息进行评估,发现两个问题:
1. 根据查询信息确认users01.dbf（file# 4）文件处于offline状态,而且checkpoint scn明显小于其他文件

Mon Mar 30 06:26:30 2026
Starting ORACLE instance (normal)
Mon Mar 30 06:27:00 2026
ALTER DATABASE DATAFILE 4 OFFLINE DROP
Completed: ALTER DATABASE DATAFILE 4 OFFLINE DROP
Mon Mar 30 06:27:26 2026
ALTER DATABASE OPEN
Beginning crash recovery of 1 threads
 parallel recovery started with 7 processes
Started redo scan
Completed redo scan
 read 88 KB redo, 103 data blocks need recovery
Started redo application at
 Thread 1: logseq 3, block 3
Recovery of Online Redo Log: Thread 1 Group 3 Seq 3 Reading mem 0
  Mem# 0: /home/oracle/app/oracle/oradata/orcl/redo03.log
Completed redo application of 0.07MB
Completed crash recovery at
 Thread 1: logseq 3, block 180, scn 415466134
 103 data blocks read, 103 data blocks written, 88 redo k-bytes read
Mon Mar 30 06:27:28 2026
Thread 1 advanced to log sequence 4 (thread open)
Thread 1 opened at log sequence 4
  Current log# 1 seq# 4 mem# 0: /home/oracle/app/oracle/oradata/orcl/redo01.log
Successful open of redo thread 1
MTTR advisory is disabled because FAST_START_MTTR_TARGET is not set
Mon Mar 30 06:27:28 2026
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
Errors in file /home/oracle/app/oracle/diag/rdbms/orcl/orcl/trace/orcl_smon_17628.trc(incident=186839):
ORA-00600: internal error code, arguments: [4194], [], [], [], [], [], [], [], [], [], [], []
Incident details in: /home/oracle/app/oracle/diag/rdbms/orcl/orcl/orcl_smon_17628_i186839.trc
Exception [type: SIGBUS, Non-existent physical address] [ADDR:0x6C0C4B62] [PC:0x2297750, kgegpa()+40]
Exception [type: SIGBUS, Non-existent physical address] [ADDR:0x6C0C4B62] [PC:0x229597B, kgebse()+279]
Mon Mar 30 06:27:28 2026
PMON (ospid: 17604): terminating the instance due to error 397
Errors in file /home/oracle/app/oracle/diag/rdbms/orcl/orcl/trace/orcl_smon_17628.trc:
ORA-00328: archived log ends at change 415466135, need later change 415466136
ORA-00334: archived log: '/home/oracle/app/oracle/oradata/orcl/redo03.log'
ORA-00600: internal error code, arguments: [4194], [], [], [], [], [], [], [], [], [], [], []
Instance terminated by PMON, pid = 17604

接手这个故障之后,由于数据库是非归档模式,而且已经被屏蔽一致性强制打开过(open过程没成功),redo已经被clear过,因此基于这样的情况,直接上obet工具(Oracle Block Editor Tool修改file# 4的文件头状态
obet修复之前文件状态

STATUS	CHECKPOINT_TIME 			 FUZ CHECKPOINT_CHANGE# 	 ROW_NUM
------- ---------------------------------------- --- ------------------ ----------------
OFFLINE 2026-03-30 06:24:41			 YES	      415446014 	       1
ONLINE	2026-03-30 06:28:26			 YES	      415486184 	       7

obet修改文件头操作

OBET> open listfile.txt
Loaded 8 files from  datafile list 'listfile.txt'.

OBET> info

Loaded files (2 total):
----------------------------------------
Number  Path
----------------------------------------
     1  /home/oracle/app/oracle/oradata/orcl/system01.dbf
     4  /opt/oradata/orcl/users01.dbf
----------------------------------------

OBET> set mode edit
mode set to: edit


OBET> set file 4
filename set to: /opt/oradata/orcl/users01.dbf (file#4)

OBET> backup block 1
Created backup directory: backup_blk
Successfully backed up block 1 from current file to /tmp/backup_blk/users01.dbf_1.20260331092333


OBET> copy chkscn file 1 to file 4

Confirm Modify chkscn:
Source: file#1 (/home/oracle/app/oracle/oradata/orcl/system01.dbf)
Target: file#4 (/opt/oradata/orcl/users01.dbf)
Proceed? (Y/YES to confirm): y
Successfully copied checkpoint SCN information from file#1 to file#4.

OBET> exit
Exiting OBET.

再次查询文件头scn信息

STATUS	CHECKPOINT_TIME 			 FUZ CHECKPOINT_CHANGE# 	 ROW_NUM
------- ---------------------------------------- --- ------------------ ----------------
OFFLINE 2026-03-30 06:28:26			 NO	      415486184 	       1
ONLINE	2026-03-30 06:28:26			 YES	      415486184 	       7

尝试online文件,并open库成功

SQL> recover datafile 4;
Media recovery complete.
SQL> alter database datafile 4 online;

Database altered.

SQL> alter database open;

Database altered.

然后expdp导出数据成功,基本完成本次数据恢复工作