联系：手机/微信(+86 17813235971) QQ(107644445)
作者：惜分飞©版权所有[未经本人同意,不得以任何形式转载,否则有进一步追究法律责任的权利.]
有客户本想truncate业务用下面所有的表,结果连接成SYS用户,并且拼接truncate 批量语句,导致sys用户下面大量表被truncate
联系：手机/微信(+86 17813235971) QQ(107644445)
标题：ORA-00742: 日志读取在线程 %d 序列 %d 块 %d 中检测到写入丢失情况
作者：惜分飞©版权所有[未经本人同意,不得以任何形式转载,否则有进一步追究法律责任的权利.]
由于主机异常断电，导致oracle数据库无法正常启动,数据库启动报错ORA-07445 kdxlin,ORA-01172,ORA-00312,ORA-00742等错误
Fri Nov 25 11:24:53 2022 alter database open Beginning crash recovery of 1 threads parallel recovery started with 15 processes Started redo scan Completed redo scan read 900 KB redo, 386 data blocks need recovery Started redo application at Thread 1: logseq 93214, block 60163 Recovery of Online Redo Log: Thread 1 Group 1 Seq 93214 Reading mem 0 Mem# 0: D:\APP\ADMINISTRATOR\ORADATA\XFF\REDO01.LOG Completed redo application of 0.46MB Fri Nov 25 11:25:02 2022 Hex dump of (file 3, block 208) in trace file D:\APP\ADMINISTRATOR\diag\rdbms\XFF\XFF\trace\XFF_p004_1988.trc Reading datafile 'D:\APP\ADMINISTRATOR\ORADATA\XFF\UNDOTBS01.DBF' for corruption at rdba: 0x00c000d0 (file 3, block 208) Reread (file 3, block 208) found valid data Hex dump of (file 3, block 208) in trace file D:\APP\ADMINISTRATOR\diag\rdbms\XFF\XFF\trace\XFF_p004_1988.trc Repaired corruption at (file 3, block 208) Hex dump of (file 3, block 152) in trace file D:\APP\ADMINISTRATOR\diag\rdbms\XFF\XFF\trace\XFF_p004_1988.trc Reading datafile 'D:\APP\ADMINISTRATOR\ORADATA\XFF\UNDOTBS01.DBF' for corruption at rdba: 0x00c00098 (file 3, block 152) Reread (file 3, block 152) found same corrupt data (logically corrupt) RECOVERY OF THREAD 1 STUCK AT BLOCK 152 OF FILE 3 Fri Nov 25 11:25:02 2022 Hex dump of (file 3, block 6859) in trace file D:\APP\ADMINISTRATOR\diag\rdbms\XFF\XFF\trace\XFF_p001_19268.trc Reading datafile 'D:\APP\ADMINISTRATOR\ORADATA\XFF\UNDOTBS01.DBF' for corruption at rdba: 0x00c01acb (file 3, block 6859) Reread (file 3, block 6859) found same corrupt data (logically corrupt) Fri Nov 25 11:25:13 2022 Errors in file D:\APP\ADMINISTRATOR\diag\rdbms\XFF\XFF\trace\XFF_p010_7024.trc (incident=224379): ORA-07445: 出现异常错误: 核心转储 [kdxlin()+4432] [ACCESS_VIOLATION] [ADDR:0xC] [PC:0x14306B54A] [UNABLE_TO_READ] [] Incident details in: D:\APP\ADMINISTRATOR\diag\rdbms\XFF\XFF\incident\incdir_224379\XFF_p010_7024_i224379.trc Use ADRCI or Support Workbench to package the incident. See Note 411.1 at My Oracle Support for error and packaging details. Fri Nov 25 11:25:13 2022 Errors in file D:\APP\ADMINISTRATOR\diag\rdbms\XFF\XFF\trace\XFF_p005_12036.trc (incident=224343): ORA-07445: 出现异常错误: 核心转储 [kdxlin()+4432] [ACCESS_VIOLATION] [ADDR:0xC] [PC:0x14306B54A] [UNABLE_TO_READ] [] Incident details in: D:\APP\ADMINISTRATOR\diag\rdbms\XFF\XFF\incident\incdir_224343\XFF_p005_12036_i224343.trc Use ADRCI or Support Workbench to package the incident. See Note 411.1 at My Oracle Support for error and packaging details. Fri Nov 25 11:25:18 2022 Sweep [inc][224379]: completed Sweep [inc][224343]: completed Sweep [inc2][224379]: completed Sweep [inc2][224343]: completed RECOVERY OF THREAD 1 STUCK AT BLOCK 6859 OF FILE 3 Fri Nov 25 11:25:33 2022 Slave exiting with ORA-1172 exception Errors in file D:\APP\ADMINISTRATOR\diag\rdbms\XFF\XFF\trace\XFF_p004_1988.trc: ORA-01172: 线程 1 的恢复停止在块 152 (在文件 3 中) ORA-01151: 如果需要, 请使用介质恢复以恢复块和还原备份 Fri Nov 25 11:25:34 2022 Errors in file D:\APP\ADMINISTRATOR\diag\rdbms\XFF\XFF\trace\XFF_p001_19268.trc: ORA-10388: parallel query server interrupt (failure) Errors in file D:\APP\ADMINISTRATOR\diag\rdbms\XFF\XFF\trace\XFF_p001_19268.trc: ORA-10388: parallel query server interrupt (failure) Fri Nov 25 11:25:38 2022 Errors in file D:\APP\ADMINISTRATOR\diag\rdbms\XFF\XFF\trace\XFF_p010_7024.trc: ORA-00742: 日志读取在线程 %d 序列 %d 块 %d 中检测到写入丢失情况 ORA-00312: 联机日志 1 线程 1: 'D:\APP\ADMINISTRATOR\ORADATA\XFF\REDO01.LOG' ORA-00607: 当更改数据块时出现内部错误 ORA-00602: 内部编程异常错误 ORA-07445: 出现异常错误: 核心转储 [kdxlin()+4432] [ACCESS_VIOLATION] [ADDR:0xC] [PC:0x14306B54A] [UNABLE_TO_READ] [] Fri Nov 25 11:25:41 2022 Errors in file D:\APP\ADMINISTRATOR\diag\rdbms\XFF\XFF\trace\XFF_p005_12036.trc (incident=224344): ORA-01578: ORACLE 数据块损坏 (文件号 27, 块号 520567) ORA-01110: 数据文件 27: 'D:\APP\ADMINISTRATOR\PRODUCT\11.2.0\DBHOME_1\DATABASE\POWERMPP.DBF' ORA-10564: tablespace POWERMPP ORA-01110: 数据文件 27: 'D:\APP\ADMINISTRATOR\PRODUCT\11.2.0\DBHOME_1\DATABASE\POWERMPP.DBF' ORA-10561: block type 'TRANSACTION MANAGED INDEX BLOCK', data object# 89776 ORA-00607: 当更改数据块时出现内部错误 ORA-00602: 内部编程异常错误 ORA-07445: 出现异常错误: 核心转储 [kdxlin()+4432] [ACCESS_VIOLATION] [ADDR:0xC] [PC:0x14306B54A] [UNABLE_TO_READ] [] Incident details in: D:\APP\ADMINISTRATOR\diag\rdbms\XFF\XFF\incident\incdir_224344\XFF_p005_12036_i224344.trc Errors in file D:\APP\ADMINISTRATOR\diag\rdbms\XFF\XFF\trace\XFF_p005_12036.trc: ORA-01578: ORACLE 数据块损坏 (文件号 27, 块号 520567) ORA-01110: 数据文件 27: 'D:\APP\ADMINISTRATOR\PRODUCT\11.2.0\DBHOME_1\DATABASE\POWERMPP.DBF' ORA-10564: tablespace POWERMPP ORA-01110: 数据文件 27: 'D:\APP\ADMINISTRATOR\PRODUCT\11.2.0\DBHOME_1\DATABASE\POWERMPP.DBF' ORA-10561: block type 'TRANSACTION MANAGED INDEX BLOCK', data object# 89776 ORA-00607: 当更改数据块时出现内部错误 ORA-00602: 内部编程异常错误 ORA-07445: 出现异常错误: 核心转储 [kdxlin()+4432] [ACCESS_VIOLATION] [ADDR:0xC] [PC:0x14306B54A] [UNABLE_TO_READ] [] Errors in file D:\APP\ADMINISTRATOR\diag\rdbms\XFF\XFF\trace\XFF_p010_7024.trc (incident=224380): ORA-01578: ORACLE 数据块损坏 (文件号 26, 块号 227101) ORA-01110: 数据文件 26: 'D:\APP\ADMINISTRATOR\PRODUCT\11.2.0\DBHOME_1\DATABASE\POWERMSP.DBF' ORA-10564: tablespace POWERMSP ORA-01110: 数据文件 26: 'D:\APP\ADMINISTRATOR\PRODUCT\11.2.0\DBHOME_1\DATABASE\POWERMSP.DBF' ORA-10561: block type 'TRANSACTION MANAGED INDEX BLOCK', data object# 99375 ORA-00607: 当更改数据块时出现内部错误 ORA-00602: 内部编程异常错误 ORA-07445: 出现异常错误: 核心转储 [kdxlin()+4432] [ACCESS_VIOLATION] [ADDR:0xC] [PC:0x14306B54A] [UNABLE_TO_READ] [] Fri Nov 25 11:25:51 2022 Incident details in: D:\APP\ADMINISTRATOR\diag\rdbms\XFF\XFF\incident\incdir_224380\XFF_p010_7024_i224380.trc Errors in file D:\APP\ADMINISTRATOR\diag\rdbms\XFF\XFF\trace\XFF_p010_7024.trc: ORA-01578: ORACLE 数据块损坏 (文件号 26, 块号 227101) ORA-01110: 数据文件 26: 'D:\APP\ADMINISTRATOR\PRODUCT\11.2.0\DBHOME_1\DATABASE\POWERMSP.DBF' ORA-10564: tablespace POWERMSP ORA-01110: 数据文件 26: 'D:\APP\ADMINISTRATOR\PRODUCT\11.2.0\DBHOME_1\DATABASE\POWERMSP.DBF' ORA-10561: block type 'TRANSACTION MANAGED INDEX BLOCK', data object# 99375 ORA-00607: 当更改数据块时出现内部错误 ORA-00602: 内部编程异常错误 ORA-07445: 出现异常错误: 核心转储 [kdxlin()+4432] [ACCESS_VIOLATION] [ADDR:0xC] [PC:0x14306B54A] [UNABLE_TO_READ] [] Fri Nov 25 11:25:54 2022 Aborting crash recovery due to slave death, attempting serial crash recovery Beginning crash recovery of 1 threads Started redo scan Completed redo scan read 900 KB redo, 386 data blocks need recovery Started redo application at Thread 1: logseq 93214, block 60163 Recovery of Online Redo Log: Thread 1 Group 1 Seq 93214 Reading mem 0 Mem# 0: D:\APP\ADMINISTRATOR\ORADATA\XFF\REDO01.LOG Hex dump of (file 3, block 6743) in trace file D:\APP\ADMINISTRATOR\diag\rdbms\XFF\XFF\trace\XFF_ora_4172.trc Reading datafile 'D:\APP\ADMINISTRATOR\ORADATA\XFF\UNDOTBS01.DBF' for corruption at rdba: 0x00c01a57 (file 3, block 6743) Reread (file 3, block 6743) found same corrupt data (logically corrupt) RECOVERY OF THREAD 1 STUCK AT BLOCK 6743 OF FILE 3 Fri Nov 25 11:26:09 2022 Aborting crash recovery due to error 1172 Errors in file D:\APP\ADMINISTRATOR\diag\rdbms\XFF\XFF\trace\XFF_ora_4172.trc: ORA-01172: 线程 1 的恢复停止在块 6743 (在文件 3 中) ORA-01151: 如果需要, 请使用介质恢复以恢复块和还原备份 Errors in file D:\APP\ADMINISTRATOR\diag\rdbms\XFF\XFF\trace\XFF_ora_4172.trc: ORA-01172: 线程 1 的恢复停止在块 6743 (在文件 3 中) ORA-01151: 如果需要, 请使用介质恢复以恢复块和还原备份 ORA-1172 signalled during: alter database open...
尝试人工recover恢复,报ORA-00283 ORA-00742 ORA-00312错误
SQL> recover database; ORA-00283: 恢复会话因错误而取消 ORA-00742: 日志读取在线程 %d 序列 %d 块 %d 中检测到写入丢失情况 ORA-00312: 联机日志 1 线程 1: 'D:\APP\ADMINISTRATOR\ORADATA\XFF\REDO01.LOG'
通过特殊这里之后recover库成功
SQL> recover database until cancel;
ORA-00279: 更改 47073228694 (在 11/25/2022 08:11:15 生成) 对于线程 1 是必需的
ORA-00289: 建议:
D:\APP\ADMINISTRATOR\FAST_RECOVERY_AREA\XFF\ARCHIVELOG\2022_11_25\O1_MF_1_932
14_%U_.ARC
ORA-00280: 更改 47073228694 (用于线程 1) 在序列 #93214 中
指定日志: {<RET>=suggested | filename | AUTO | CANCEL}
D:\APP\ADMINISTRATOR\ORADATA\XFF\REDO01.LOG
已应用的日志。
完成介质恢复。
打开数据库报ORA-600 2662错误
SQL> startup mount pfile='d:/pfile.txt' ORACLE 例程已经启动。 Total System Global Area 1603411968 bytes Fixed Size 2281656 bytes Variable Size 1191186248 bytes Database Buffers 402653184 bytes Redo Buffers 7290880 bytes 数据库装载完毕。 SQL> ALTER DATABASE OPEN; 数据库已更改。
然后逻辑导出数据,导入新库,完成数据迁移工作
联系：手机/微信(+86 17813235971) QQ(107644445)
标题：ora-600 kccpb_sanity_check_2故障处理
作者：惜分飞©版权所有[未经本人同意,不得以任何形式转载,否则有进一步追究法律责任的权利.]
数据库启动报ORA-600 kccpb_sanity_check_2
SQL> startup mount pfile='d:/pfile.txt' ORACLE 例程已经启动。 Total System Global Area 1258291200 bytes Fixed Size 1250548 bytes Variable Size 243272460 bytes Database Buffers 1006632960 bytes Redo Buffers 7135232 bytes ORA-00600: ??????, ??: [kccpb_sanity_check_2], [66014], [66011], [0x0], [], [],[], []
重建控制文件报错ora-600 kccsga_update_amx_1
SQL> CREATE CONTROLFILE REUSE DATABASE "zs" NORESETLOGS NOARCHIVELOG 2 MAXLOGFILES 50 3 MAXLOGMEMBERS 5 4 MAXDATAFILES 100 5 MAXINSTANCES 8 6 MAXLOGHISTORY 226 7 LOGFILE 8 GROUP 1 'd:\zs\redo01.log' SIZE 50M, 9 GROUP 2 'd:\zs\redo02.log' SIZE 50M, 10 GROUP 3 'd:\zs\redo03.log' SIZE 50M 11 DATAFILE 12 'd:\zs\SYSAUX01.DBF', ……………… 22 'd:\zs\SYSTEM01.DBF', 23 'd:\zs\UNDOTBS01.DBF', 24 'd:\zs\USERS01.DBF' 25 CHARACTER SET zhs16gbk 26 ; CREATE CONTROLFILE REUSE DATABASE "zs" NORESETLOGS NOARCHIVELOG * 第 1 行出现错误: ORA-01503: CREATE CONTROLFILE ?? ORA-00600: ??????, ??: [kccsga_update_amx_1], [9], [2920], [292], [], [], [],[]
重启实例，重建ctl成功.尝试恢复库提示需要很久之前的日志，因为有两个数据文件scn异常
联系：手机/微信(+86 17813235971) QQ(107644445)
标题：ORA-600 3417和ORA-600 3005故障处理
作者：惜分飞©版权所有[未经本人同意,不得以任何形式转载,否则有进一步追究法律责任的权利.]
运行中的数据库突然报ORA-600 3417错误,lgwr进程异常数据库crash
Thu Nov 17 03:00:14 2022 Archived Log entry 23860 added for thread 2 sequence 1958 ID 0x6200e2f5 dest 1: Thu Nov 17 03:13:11 2022 Auto-tuning: Shutting down background process GTX1 Thu Nov 17 04:00:02 2022 Errors in file D:\APP\ADMINISTRATOR\diag\rdbms\orcl\orcl2\trace\orcl2_lgwr_1740.trc (incident=672186): ORA-00600: 内部错误代码, 参数: [3417], [2], [0], [0], [0], [1], [2], [], [], [], [], [] Thu Nov 17 04:00:04 2022 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\orcl\orcl2\trace\orcl2_lgwr_1740.trc: ORA-00600: 内部错误代码, 参数: [3417], [2], [0], [0], [0], [1], [2], [], [], [], [], [] LGWR (ospid: 1740): terminating the instance due to error 470
重启之后报ORA-600 3005错误,数据库启动失败
Thu Nov 17 04:03:09 2022
Successful mount of redo thread 2, with mount id 1648753015
Database mounted in Shared Mode (CLUSTER_DATABASE=TRUE)
Lost write protection disabled
Completed: ALTER DATABASE MOUNT /* db agent *//* {0:1:38} */
ALTER DATABASE OPEN /* db agent *//* {0:1:38} */
This instance was first to open
Beginning crash recovery of 1 threads
parallel recovery started with 31 processes
Thu Nov 17 04:03:14 2022
Started redo scan
ORA-00600: ??????, ??: [3005], [1], [706], [10374], [0], [0], [], [], [], [], [], []
Thu Nov 17 04:03:15 2022
Reconfiguration started (old inc 14, new inc 16)
List of instances:
1 2 (myinst: 2)
Global Resource Directory frozen
Thu Nov 17 04:03:15 2022
Communication channels reestablished
Thu Nov 17 04:03:16 2022
* domain 0 valid = 0 according to instance 1
Master broadcasted resource hash value bitmaps
Non-local Process blocks cleaned out
Thu Nov 17 04:03:16 2022
LMS 0: 0 GCS shadows cancelled, 0 closed, 0 Xw survived
Use ADRCI or Support Workbench to package the incident.
See Note 411.1 at My Oracle Support for error and packaging details.
Thu Nov 17 04:03:16 2022
Sweep [inc][688298]: completed
Sweep [inc2][688298]: completed
Thu Nov 17 04:03:16 2022
LMS 1: 0 GCS shadows cancelled, 0 closed, 0 Xw survived
Thu Nov 17 04:03:16 2022
LMS 2: 0 GCS shadows cancelled, 0 closed, 0 Xw survived
Set master node info
Submitted all remote-enqueue requests
Dwn-cvts replayed, VALBLKs dubious
All grantable enqueues granted
Post SMON to start 1st pass IR
Submitted all GCS remote-cache requests
Post SMON to start 1st pass IR
Fix write in gcs resources
Reconfiguration complete
Abort recovery for domain 0
Aborting crash recovery due to error 600
Errors in file D:\APP\ADMINISTRATOR\diag\rdbms\orcl\orcl2\trace\orcl2_ora_15352.trc:
ORA-00600: ??????, ??: [3005], [1], [706], [10374], [0], [0], [], [], [], [], [], []
Abort recovery for domain 0
Errors in file D:\APP\ADMINISTRATOR\diag\rdbms\orcl\orcl2\trace\orcl2_ora_15352.trc:
ORA-00600: ??????, ??: [3005], [1], [706], [10374], [0], [0], [], [], [], [], [], []
ORA-600 signalled during: ALTER DATABASE OPEN /* db agent *//* {0:1:38} */...
尝试人工进行recover恢复库
SQL> recover database;
ORA-00279: 更改 310644203 (在 11/17/2022 01:00:05 生成) 对于线程 2 是必需的
ORA-00289: 建议:
+DATA/orcl/archivelog/2022_11_17/thread_2_seq_1956.22763.1120960801
ORA-00280: 更改 310644203 (用于线程 2) 在序列 #1956 中
指定日志: {<RET>=suggested | filename | AUTO | CANCEL}
auto
ORA-00279: 更改 310663747 (在 11/17/2022 02:00:01 生成) 对于线程 2 是必需的
ORA-00289: 建议:
+DATA/orcl/archivelog/2022_11_17/thread_2_seq_1957.22764.1120962585
ORA-00280: 更改 310663747 (用于线程 2) 在序列 #1957 中
ORA-10877: error signaled in parallel recovery slave
ORA-01112: 未启动介质恢复
通过查看alert日志确认由于ORA-00353错误导致recover database失败
Thu Nov 17 08:07:39 2022 ALTER DATABASE RECOVER database Media Recovery Start started logmerger process Parallel Media Recovery started with 32 slaves Thu Nov 17 08:07:41 2022 Recovery of Online Redo Log: Thread 1 Group 1 Seq 705 Reading mem 0 Mem# 0: +DATA/orcl/onlinelog/group_1.261.1116409583 ORA-279 signalled during: ALTER DATABASE RECOVER database ... Thu Nov 17 08:08:07 2022 ALTER DATABASE RECOVER CONTINUE DEFAULT Media Recovery Log +DATA/orcl/archivelog/2022_11_17/thread_2_seq_1956.22763.1120960801 ORA-279 signalled during: ALTER DATABASE RECOVER CONTINUE DEFAULT ... ALTER DATABASE RECOVER CONTINUE DEFAULT Media Recovery Log +DATA/orcl/archivelog/2022_11_17/thread_2_seq_1957.22764.1120962585 Thu Nov 17 08:08:14 2022 Recovery of Online Redo Log: Thread 2 Group 4 Seq 1958 Reading mem 0 Mem# 0: +DATA/orcl/onlinelog/group_4.266.1116409589 Errors in file D:\APP\ADMINISTRATOR\diag\rdbms\orcl\orcl2\trace\orcl2_pr00_7116.trc (incident=704315): ORA-00353: 日志损坏接近块 20866 更改 310761542 时间 11/17/2022 03:00:04 ORA-00312: 联机日志 1 线程 1: '+DATA/orcl/onlinelog/group_1.261.1116409583' Thu Nov 17 08:08:26 2022 Sweep [inc][704315]: completed Thu Nov 17 08:08:27 2022 Media Recovery failed with error 354 Errors in file D:\APP\ADMINISTRATOR\diag\rdbms\orcl\orcl2\trace\orcl2_pr00_7116.trc: ORA-00283: 恢复会话因错误而取消 ORA-00354: 损坏重做日志块标头 ORA-00353: 日志损坏接近块 20866 更改 310761542 时间 11/17/2022 03:00:04 ORA-00312: 联机日志 1 线程 1: '+DATA/orcl/onlinelog/group_1.261.1116409583' Thu Nov 17 08:08:27 2022 ORA-10877 signalled during: ALTER DATABASE RECOVER CONTINUE DEFAULT ... ALTER DATABASE RECOVER CANCEL ORA-1112 signalled during: ALTER DATABASE RECOVER CANCEL ...
通过对redo进行处理顺利recover成功并完美open库
SQL> recover database; 完成介质恢复。 SQL> alter database open; 数据库已更改。
联系：手机/微信(+86 17813235971) QQ(107644445)
作者：惜分飞©版权所有[未经本人同意,不得以任何形式转载,否则有进一步追究法律责任的权利.]
有一个朋友自己想测试只用system文件open库,闲着没事给他测试了下,顺利open成功（主要还是经验比较多,规避了很多坑）
1. 准备参数文件
*.audit_file_dest='C:\app\XFF\admin\ORCL\adump' *.audit_trail='none' *.compatible='11.2.0.3.0' *.control_files='H:\TEMP\11203\control01.ctl' *.db_block_size=8192 *.db_domain='' *.db_name='DBM' *.diagnostic_dest='C:\app\XFF' *.dispatchers='(PROTOCOL=TCP) (SERVICE=ORCLXDB)' *.nls_language='SIMPLIFIED CHINESE' *.nls_territory='CHINA' *.open_cursors=300 *.pga_aggregate_target=2147483648 *.processes=150 *.remote_login_passwordfile='EXCLUSIVE' *.sessions=170 *.sga_target=6442450944 *.undo_tablespace='UNDOTBS1' undo_management=MANUAL _corrupted_rollback_segments= _allow_resetlogs_corruption=true
2. 准备重建ctl语句
CREATE CONTROLFILE REUSE DATABASE "DBM" RESETLOGS NOARCHIVELOG
MAXLOGFILES 50
MAXLOGMEMBERS 5
MAXDATAFILES 100
MAXINSTANCES 8
MAXLOGHISTORY 226
LOGFILE
GROUP 1 'H:\TEMP\11203\redo01.log' SIZE 50M,
GROUP 2 'H:\TEMP\11203\redo02.log' SIZE 50M,
GROUP 3 'H:\TEMP\11203\redo03.log' SIZE 50M
DATAFILE
'H:\TEMP\11203\system01.dbf'
CHARACTER SET ZHS16GBK
;
3. 重建ctl并且resetogs open库
SQL> recover database using backup controlfile until cancel;
ORA-00279: 更改 40438873410 (在 10/21/2022 14:06:16 生成) 对于线程 1 是必需的
ORA-00289: 建议:
C:\APP\XFF\PRODUCT\11.2.0.3\DBHOME_1\RDBMS\ARC0000000093_1118545292.0001
ORA-00280: 更改 40438873410 (用于线程 1) 在序列 #93 中
指定日志: {<RET>=suggested | filename | AUTO | CANCEL}
cancel
ORA-01547: 警告: RECOVER 成功但 OPEN RESETLOGS 将出现如下错误
ORA-01194: 文件 1 需要更多的恢复来保持一致性
ORA-01110: 数据文件 1: 'H:\TEMP\11203\SYSTEM01.DBF'
ORA-01112: 未启动介质恢复
SQL> alter database open resetlogs;
alter database open resetlogs
*
第 1 行出现错误:
ORA-01092: ORACLE instance terminated. Disconnection forced
ORA-01176: data dictionary has more than the 100 files allowed by the
controlfie
进程 ID: 3952
会话 ID: 14 序列号: 3
MAXDATAFILES值不对修改正确值，重建ctl，open库
SQL> RECOVER DATABASE; 完成介质恢复。 SQL> ALTER DATABASE OPEN; ALTER DATABASE OPEN * 第 1 行出现错误: ORA-01092: ORACLE instance terminated. Disconnection forced ORA-00604: error occurred at recursive SQL level 1 ORA-01555: snapshot too old: rollback segment number with name "" too small 进程 ID: 6916 会话 ID: 14 序列号: 1
alert日志内容
Database Characterset is ZHS16GBK Errors in file C:\APP\XFF\diag\rdbms\dbm\test\trace\test_smon_9384.trc: ORA-00604: 递归 SQL 级别 1 出现错误 ORA-01555: 快照过旧: 回退段号 (名称为 "") 过小 Errors in file C:\APP\XFF\diag\rdbms\dbm\test\trace\test_ora_6916.trc: ORA-00604: 递归 SQL 级别 1 出现错误 ORA-01555: 快照过旧: 回退段号 (名称为 "") 过小 Errors in file C:\APP\XFF\diag\rdbms\dbm\test\trace\test_ora_6916.trc: ORA-00604: 递归 SQL 级别 1 出现错误 ORA-01555: 快照过旧: 回退段号 (名称为 "") 过小 Error 604 happened during db open, shutting down database USER (ospid: 6916): terminating the instance due to error 604 Errors in file C:\APP\XFF\diag\rdbms\dbm\test\trace\test_smon_9384.trc (incident=2521): ORA-00600: 内部错误代码, 参数: [2662], [9], [1784188335], [9], [1784216952], [6019273], [], [], [], [], [], [] Incident details in: C:\APP\XFF\diag\rdbms\dbm\test\incident\incdir_2521\test_smon_9384_i2521.trc Use ADRCI or Support Workbench to package the incident. See Note 411.1 at My Oracle Support for error and packaging details. Non-fatal internal error happenned while SMON was doing temporary segment drop. SMON encountered 1 out of maximum 100 non-fatal internal errors. Tue Nov 01 10:17:49 2022 Instance terminated by USER, pid = 6916 ORA-1092 signalled during: ALTER DATABASE OPEN...
修改文件头scn，并正常open库
SQL> startup nomount pfile='d:/pfile.txt' ORACLE 例程已经启动。 Total System Global Area 6413680640 bytes Fixed Size 2267184 bytes Variable Size 1107298256 bytes Database Buffers 5284823040 bytes Redo Buffers 19292160 bytes SQL> alter database mount; 数据库已更改。 SQL> set numw 16 SQL> col CHECKPOINT_TIME for a40 SQL> set lines 150 SQL> set pages 1000 SQL> SELECT status, 2 to_char(checkpoint_time,'yyyy-mm-dd hh24:mi:ss') checkpoint_time,FUZZY,checkpoint_change#, 3 count(*) ROW_NUM 4 FROM v$datafile_header 5 GROUP BY status, checkpoint_change#, to_char(checkpoint_time,'yyyy-mm-dd hh24:mi:ss'),fuzzy 6 ORDER BY status, checkpoint_change#, checkpoint_time; STATUS CHECKPOINT_TIME FUZ CHECKPOINT_CHANGE# ROW_NUM ------- ---------------------------------------- --- ------------------ ---------------- OFFLINE 0 121 ONLINE 2022-11-01 10:17:44 YES 40438893615 1
SQL> alter database open; 数据库已更改。 SQL> select name from v$datafile; NAME -------------------------------------------------------------------------- H:\TEMP\11203\SYSTEM01.DBF C:\APP\XFF\PRODUCT\11.2.0.3\DBHOME_1\DATABASE\MISSING00002 C:\APP\XFF\PRODUCT\11.2.0.3\DBHOME_1\DATABASE\MISSING00003 C:\APP\XFF\PRODUCT\11.2.0.3\DBHOME_1\DATABASE\MISSING00004 C:\APP\XFF\PRODUCT\11.2.0.3\DBHOME_1\DATABASE\MISSING00005 ……………… C:\APP\XFF\PRODUCT\11.2.0.3\DBHOME_1\DATABASE\MISSING00121 C:\APP\XFF\PRODUCT\11.2.0.3\DBHOME_1\DATABASE\MISSING00122 已选择122行。
恢复完成
 |
|