环境信息

1台windows server上跑了2个版本的oracle数据库，数据文件都放在同一个磁盘上，因断电2套数据库都起不来，也没归档没备份
服务器信息：windows server 2019
数据库1：单机19c容器库
数据库2：单机11g

11g恢复过程

当前redo文件损坏

Started redo scan
Aborting crash recovery due to error 742
Errors in file E:\APP\ADMINISTRATOR\diag\rdbms\orcl\orcl\trace\orcl_ora_11384.trc:
ORA-00742: 日志读取在线程 %d 序列 %d 块 %d 中检测到写入丢失情况
ORA-00312: 联机日志 5 线程 1: 'E:\APP\ADMINISTRATOR\ORADATA\REDO5.DAT'

--尝试clear日志文件和不完全恢复都不成功
alter database clear unarchived logfile group 5;

尝试设置隐含参数

_allow_error_simulation=TRUE
_allow_resetlogs_corruption=TRUE

数据库open报错ORA-01555

SQL> alter database open;
alter database open
*
第 1 行出现错误:
ORA-01092: ORACLE instance terminated. Disconnection forced
ORA-00704: bootstrap process failure
ORA-00704: bootstrap process failure
ORA-00604: error occurred at recursive SQL level 1
ORA-01555: snapshot too old: rollback segment number 9 with name
"_SYSSMU9_4587213$" too small
进程 ID: 18988
会话 ID: 409 序列号: 1

alert日志中显示报错的sql为：select ctime, mtime, stime from obj$ where obj# = :1

--这里屏蔽回滚段没有用，需要推进scn

推进scn

SQL> select checkpoint_change# from v$database;
CHECKPOINT_CHANGE#
------------------
        2485027051

SQL> select unique checkpoint_change# from v$datafile_header;
CHECKPOINT_CHANGE#
------------------
        2485027051

--scn推进一个级别100w并转化为16进制
SQL> select to_char(2485027051+1000000,'XXXXXXXXXXXXX') as scn from dual;
SCN
----------------------------
      942DC32B

SQL> oradebug setmypid
Statement processed.
SQL> oradebug DUMPvar SGA kcsgscn_
kcslf kcsgscn_ [149876FA0, 149876FD0) = 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 49876C30 00000001

SQL> oradebug poke 0x149876FA0 4 0x942DC32B
BEFORE: [149876FA0, 149876FA8) = 00000000 00000000
AFTER:  [149876FA0, 149876FA8) = 942DC32B 00000000

SQL> alter database open;
数据库已更改。

逻辑导出数据

expdp导出数据

19c恢复过程

mount阶段报错

2024-12-14T16:38:10.219874+08:00
alter database mount
2024-12-14T16:38:14.407373+08:00
Errors in file E:\ORACLE\ORACLE19\diag\rdbms\orcl19\orcl19\trace\orcl19_ora_6128.trc  (incident=409965) (PDBNAME=CDB$ROOT):
ORA-00600: ??????, ??: [2131], [9], [8], [], [], [], [], [], [], [], [], []
Incident details in: E:\ORACLE\ORACLE19\diag\rdbms\orcl19\orcl19\incident\incdir_409965\orcl19_ora_6128_i409965.trc

重建控制文件

--处理方法参照 Doc ID 2183374.1

--一共2个控制文件，尝试了都不行

--重建控制文件
----控制文件模版获取
其他19c的机器，导出一个模版使用
alter database backup controlfile to trace as 'E:\oracle\oradata\ctl.sql';

----字符集获取
EmEditor以二进制格式打开system文件，搜索NLS_CHARACTERSET关键字

--重建控制文件（注意不要漏数据文件、cdb、pdb、pdbseed）
create controlfile reuse database "orcl19" NORESETLOGS NOARCHIVELOG
MAXINSTANCES 8
MAXLOGHISTORY 292
MAXLOGFILES 16
MAXLOGMEMBERS 3
MAXDATAFILES 1024
LOGFILE 
GROUP 1 ('E:\oracle\oradata\ORCL19\redo01.log') SIZE 200M BLOCKSIZE 512,
GROUP 2 ('E:\oracle\oradata\ORCL19\redo02.log') SIZE 200M BLOCKSIZE 512,
GROUP 3 ('E:\oracle\oradata\ORCL19\redo03.log') SIZE 200M BLOCKSIZE 512
Datafile
--cdb
'E:\ORACLE\ORADATA\ORCL19\SYSAUX01.DBF',
'E:\ORACLE\ORADATA\ORCL19\SYSTEM01.DBF',
'E:\ORACLE\ORADATA\ORCL19\UNDOTBS01.DBF',
'E:\ORACLE\ORADATA\ORCL19\USERS01.DBF',
--pdbseed
'E:\ORACLE\ORADATA\ORCL19\PDBSEED\SYSAUX01.DBF',
'E:\ORACLE\ORADATA\ORCL19\PDBSEED\SYSTEM01.DBF',
'E:\ORACLE\ORADATA\ORCL19\PDBSEED\UNDOTBS01.DBF',
--pdb
'E:\ORACLE\ORADATA\ORCL19\ORCLPDB\SYSAUX01.DBF',
'E:\ORACLE\ORADATA\ORCL19\ORCLPDB\SYSTEM01.DBF',
'E:\ORACLE\ORADATA\ORCL19\ORCLPDB\UNDOTBS01.DBF',
'E:\ORACLE\ORADATA\ORCL19\ORCLPDB\USERS01.DBF',
...省略...
CHARACTER SET AL32UTF8
;

recover database;

alter database open;

open报错，ORA-00600/4193

2024-12-15T11:24:37.618790+08:00
Errors in file E:\ORACLE\ORACLE19\diag\rdbms\orcl19\orcl19\trace\orcl19_smon_11096.trc  (incident=655665) (PDBNAME=CDB$ROOT):
ORA-00600: ??????, ??: [4193], [952], [956], [], [], [], [], [], [], [], [], []
Incident details in: E:\ORACLE\ORACLE19\diag\rdbms\orcl19\orcl19\incident\incdir_655665\orcl19_smon_11096_i655665.trc

#处理undo即可

#开库后处理下临时表空间

逻辑导出数据

#开库后，alert日志中还有报错没有继续处理
2024-12-15T11:42:50.820228+08:00
Errors in file E:\ORACLE\ORACLE19\diag\rdbms\orcl19\orcl19\trace\orcl19_mmon_2172.trc  (incident=737675) (PDBNAME=CDB$ROOT):
ORA-00600: 内部错误代码, 参数: [kdsgrp1], [], [], [], [], [], [], [], [], [], [], []
Incident details in: E:\ORACLE\ORACLE19\diag\rdbms\orcl19\orcl19\incident\incdir_737675\orcl19_mmon_2172_i737675.trc
Use ADRCI or Support Workbench to package the incident.
See Note 411.1 at My Oracle Support for error and packaging details.

#expdp导出报错，exp可以导出，于是用exp按用户导出数据