mysql启动错误1.69,12c ORA-01113 ORA-01110 恢复

最新推荐文章于 2024-04-15 11:44:47 发布

贼仙呐

最新推荐文章于 2024-04-15 11:44:47 发布

阅读量311

点赞数 1

文章标签： mysql启动错误1.69

数据库启动报错

Completed: ALTER DATABASE MOUNT

Thu Aug 17 12:34:52 2017

alter database open

Thu Aug 17 12:34:52 2017

Ping without log force is disabled

Thu Aug 17 12:34:52 2017

Errors in file D:\APP\ORACLE\diag\rdbms\XIFENFEI\XIFENFEI\trace\XIFENFEI_ora_5960.trc:

ORA-01113: 文件 5 需要介质恢复

ORA-01110: 数据文件 5: 'D:\APP\ORACLE\ORADATA\XIFENFEI\DATAFILE\O1_MF_UNDOTBS1_DN9MQJFK_.DBF'

ORA-1113 signalled during: alter database open

客户尝试恢复

SQL> startup mount;

ORACLE 例程已经启动。

Total System Global Area 1.0301E+10 bytes

Fixed Size 3842760 bytes

Variable Size 1778388280 bytes

Database Buffers 8489271296 bytes

Redo Buffers 29708288 bytes

数据库装载完毕。

SQL> alter database open;

alter database open

第 1 行出现错误:

ORA-01113: 文件 5 需要介质恢复

ORA-01110: 数据文件 5: 'D:\APP\ORACLE\ORADATA\XIFENFEI\DATAFILE\O1_MF_UNDOTBS1_DN9MQJFK_.DBF'

SQL> alter database datafile 5 offline drop;

数据库已更改。

SQL> alter database open;

alter database open

第 1 行出现错误:

ORA-01113: 文件 6 需要介质恢复

ORA-01110: 数据文件 6: 'D:\APP\ORACLE\ORADATA\XIFENFEI\DATAFILE\O1_MF_USERS_DN9MQH75_.DBF'

SQL> recover datafile 6;

ORA-00283: 恢复会话因错误而取消

ORA-00322: 日志 3 (用于线程 1) 不是最新副本

ORA-00312: 联机日志 3 线程 1: 'D:\APP\ORACLE\FAST_RECOVERY_AREA\XIFENFEI\ONLINELOG\O1_MF_3_DN9MV6DW_.LOG'

ORA-00322: 日志 3 (用于线程 1) 不是最新副本

ORA-00312: 联机日志 3 线程 1: 'D:\APP\ORACLE\ORADATA\XIFENFEI\ONLINELOG\O1_MF_3_DN9MV6BX_.LOG'

使用Oracle Database Recovery Check检测结果

尝试恢复

SQL> recover database;

ORA-00283: recovery session canceled due to errors

ORA-00322: log 3 of thread 1 is not current copy

ORA-00312: online log 3 thread 1:

'D:\APP\ORACLE\FAST_RECOVERY_AREA\XIFENFEI\ONLINELOG\O1_MF_3_DN9MV6DW_.LOG'

ORA-00322: log 3 of thread 1 is not current copy

ORA-00312: online log 3 thread 1:

'D:\APP\ORACLE\ORADATA\XIFENFEI\ONLINELOG\O1_MF_3_DN9MV6BX_.LOG'

SQL> recover database until cancel

ORA-00279: 更改 9843709 (在 08/17/2017 07:04:02 生成) 对于线程 1 是必需的

ORA-00289: 建议:

D:\APP\ORACLE\FAST_RECOVERY_AREA\XIFENFEI\ARCHIVELOG\2017_08_17\O1_MF_1_717_%U_.ARC

ORA-00280: 更改 9843709 (用于线程 1) 在序列 #717 中

指定日志: {=suggested | filename | AUTO | CANCEL}

D:\APP\ORACLE\FAST_RECOVERY_AREA\XIFENFEI\ONLINELOG\O1_MF_2_DN9MV69G_.LOG

ORA-00310: archived log contains sequence 716; sequence 717 required

ORA-00334: archived log:

'D:\APP\ORACLE\FAST_RECOVERY_AREA\XIFENFEI\ONLINELOG\O1_MF_2_DN9MV69G_.LOG'

ORA-01547: warning: RECOVER succeeded but OPEN RESETLOGS would get error below

ORA-01194: file 1 needs more recovery to be consistent

ORA-01110: data file 1:

'D:\APP\ORACLE\ORADATA\XIFENFEI\DATAFILE\O1_MF_SYSTEM_DN9MN5OT_.DBF'

SQL> recover database until cancel

ORA-00279: 更改 9843709 (在 08/17/2017 07:04:02 生成) 对于线程 1 是必需的

ORA-00289: 建议:

D:\APP\ORACLE\FAST_RECOVERY_AREA\XIFENFEI\ARCHIVELOG\2017_08_17\O1_MF_1_717_%U_.AR

ORA-00280: 更改 9843709 (用于线程 1) 在序列 #717 中

指定日志: {=suggested | filename | AUTO | CANCEL}

D:\APP\ORACLE\FAST_RECOVERY_AREA\XIFENFEI\ONLINELOG\O1_MF_1_DN9MV661_.LOG

ORA-00310: archived log contains sequence 715; sequence 717 required

ORA-00334: archived log:

'D:\APP\ORACLE\FAST_RECOVERY_AREA\XIFENFEI\ONLINELOG\O1_MF_1_DN9MV661_.LOG'

ORA-01547: warning: RECOVER succeeded but OPEN RESETLOGS would get error below

ORA-01194: file 1 needs more recovery to be consistent

ORA-01110: data file 1:

'D:\APP\ORACLE\ORADATA\XIFENFEI\DATAFILE\O1_MF_SYSTEM_DN9MN5OT_.DBF'

SQL> recover database until cancel

ORA-00279: 更改 9843709 (在 08/17/2017 07:04:02 生成) 对于线程 1 是必需的

ORA-00289: 建议:

D:\APP\ORACLE\FAST_RECOVERY_AREA\XIFENFEI\ARCHIVELOG\2017_08_17\O1_MF_1_717_%U_.ARC

ORA-00280: 更改 9843709 (用于线程 1) 在序列 #717 中

指定日志: {=suggested | filename | AUTO | CANCEL}

D:\APP\ORACLE\FAST_RECOVERY_AREA\XIFENFEI\ONLINELOG\O1_MF_3_DN9MV6DW_.LOG

已应用的日志。

完成介质恢复。

SQL> alter database datafile 5 online;

数据库已更改。

SQL> recover database until cancel;

ORA-00279: 更改 9843709 (在 08/17/2017 07:04:02 生成) 对于线程 1 是必需的

ORA-00289: 建议:

D:\APP\ORACLE\FAST_RECOVERY_AREA\XIFENFEI\ARCHIVELOG\2017_08_17\O1_MF_1_717_%U_.AR

ORA-00280: 更改 9843709 (用于线程 1) 在序列 #717 中

指定日志: {=suggested | filename | AUTO | CANCEL}

D:\APP\ORACLE\FAST_RECOVERY_AREA\XIFENFEI\ONLINELOG\O1_MF_3_DN9MV6DW_.LOG

ORA-01547: warning: RECOVER succeeded but OPEN RESETLOGS would get error below

ORA-01152: file 1 was not restored from a sufficiently old backup

ORA-01110: data file 1:

'D:\APP\ORACLE\ORADATA\XIFENFEI\DATAFILE\O1_MF_SYSTEM_DN9MN5OT_.DBF'

ORA-01112: 未启动介质恢复

SQL> recover datafile 5;

ORA-00283: 恢复会话因错误而取消

ORA-00264: 不要求恢复

SQL> SELECT status,

2 checkpoint_change#,

3 checkpoint_time,FUZZY,

4 count(*) ROW_NUM

5 FROM v$datafile_header

6 GROUP BY status, checkpoint_change#, checkpoint_time,fuzzy

7 ORDER BY status, checkpoint_change#, checkpoint_time;

STATUS CHECKPOINT_CHANGE# CHECKPOINT_TIME FUZ ROW_NUM

------- ------------------ ------------------- --- ----------------

ONLINE 9850826 2017-08-17 08:15:45 NO 1

ONLINE 9857411 2017-08-17 08:15:45 NO 7

ONLINE 9857411 2017-08-17 09:00:48 NO 2

SQL> set numw 16

SQL> SELECT status,

2 checkpoint_change#,

3 checkpoint_time,last_change#,

4 count(*) ROW_NUM

5 FROM v$datafile

6 GROUP BY status, checkpoint_change#, checkpoint_time,last_change#

7 ORDER BY status, checkpoint_change#, checkpoint_time;

STATUS CHECKPOINT_CHANGE# CHECKPOINT_TIME LAST_CHANGE# ROW_NUM

------- ------------------ ------------------- ---------------- ----------------

ONLINE 9857411 2017-08-17 09:00:48 9850826 1

ONLINE 9857411 2017-08-17 09:00:48 9857411 8

SYSTEM 9857411 2017-08-17 09:00:48 9857411 1

SQL> alter database open resetlogs;

alter database open resetlogs

第 1 行出现错误:

ORA-01152: 文件 1 没有从过旧的备份中还原

ORA-01110: 数据文件 1:

'D:\APP\ORACLE\ORADATA\XIFENFEI\DATAFILE\O1_MF_SYSTEM_DN9MN5OT_.DBF'

[/shell]

这里比较明显，由于controlfile的scn 大于db的scn，从而出现了ORA-01152的错误,重试重建控制文件

重建控制文件

SQL> alter database backup controlfile to trace as 'd:\app\ctl.txt';

数据库已更改。

SQL> startup nomount;

ORACLE 例程已经启动。

Total System Global Area 10301210624 bytes

Fixed Size 3842760 bytes

Variable Size 1778388280 bytes

Database Buffers 8489271296 bytes

Redo Buffers 29708288 bytes

SQL> CREATE CONTROLFILE REUSE DATABASE "XIFENFEI" NORESETLOGS NOARCHIVELOG

2 MAXLOGFILES 16

3 MAXLOGMEMBERS 3

4 MAXDATAFILES 100

5 MAXINSTANCES 8

6 MAXLOGHISTORY 292

7 LOGFILE

8 GROUP 1 (

9 'D:\APP\ORACLE\ORADATA\XIFENFEI\ONLINELOG\O1_MF_1_DN9MV652_.LOG',

10 'D:\APP\ORACLE\FAST_RECOVERY_AREA\XIFENFEI\ONLINELOG\O1_MF_1_DN9MV661_.LO

11 ) SIZE 50M BLOCKSIZE 512,

12 GROUP 2 (

13 'D:\APP\ORACLE\ORADATA\XIFENFEI\ONLINELOG\O1_MF_2_DN9MV68H_.LOG',

14 'D:\APP\ORACLE\FAST_RECOVERY_AREA\XIFENFEI\ONLINELOG\O1_MF_2_DN9MV69G_.LO

15 ) SIZE 50M BLOCKSIZE 512,

16 GROUP 3 (

17 'D:\APP\ORACLE\ORADATA\XIFENFEI\ONLINELOG\O1_MF_3_DN9MV6BX_.LOG',

18 'D:\APP\ORACLE\FAST_RECOVERY_AREA\XIFENFEI\ONLINELOG\O1_MF_3_DN9MV6DW_.LO

19 ) SIZE 50M BLOCKSIZE 512

20 DATAFILE

21 'D:\APP\ORACLE\ORADATA\XIFENFEI\DATAFILE\O1_MF_SYSTEM_DN9MN5OT_.DBF',

22 'D:\APP\ORACLE\ORADATA\XIFENFEI\DATAFILE\O1_MF_SYSAUX_DN9MK6B3_.DBF',

23 'D:\APP\ORACLE\ORADATA\XIFENFEI\DATAFILE\O1_MF_UNDOTBS1_DN9MQJFK_.DBF',

24 'D:\APP\ORACLE\ORADATA\XIFENFEI\DATAFILE\O1_MF_USERS_DN9MQH75_.DBF',

25 'D:\APP\ORACLE\PRODUCT\12.1.0\DBHOME_1\DATABASE\XIFENFEIFILE06',

26 'D:\APP\ORACLE\PRODUCT\12.1.0\DBHOME_1\DATABASE\XIFENFEIFILE05',

27 'D:\APP\ORACLE\PRODUCT\12.1.0\DBHOME_1\DATABASE\XIFENFEIFILE04',

28 'D:\APP\ORACLE\PRODUCT\12.1.0\DBHOME_1\DATABASE\XIFENFEIFILE03',

29 'D:\APP\ORACLE\PRODUCT\12.1.0\DBHOME_1\DATABASE\XIFENFEIFILE02',

30 'D:\APP\ORACLE\PRODUCT\12.1.0\DBHOME_1\DATABASE\XIFENFEIFILE01'

31 CHARACTER SET ZHS16GBK

32 ;

控制文件已创建。

尝试恢复

SQL> recover database;

完成介质恢复。

SQL> alter database open;

alter database open

第 1 行出现错误:

ORA-01092: ORACLE instance terminated. Disconnection forced

ORA-00600: internal error code, arguments: [4194], [41], [36], [], [], [], [],

[], [], [], [], []

进程 ID: 2864

会话 ID: 62 序列号: 54236

本来到这一步，错误比较明显，undo异常，这类直接对undo进行处理即可，可是运气不太好

异常crash之后redo损坏

SQL> startup pfile='d:/app/pfile.txt'

ORACLE 例程已经启动。

Total System Global Area 1.0301E+10 bytes

Fixed Size 3842760 bytes

Variable Size 1778388280 bytes

Database Buffers 8489271296 bytes

Redo Buffers 29708288 bytes

数据库装载完毕。

ORA-00354: 损坏重做日志块标头

ORA-00353: 日志损坏接近块 60 更改 23924938639111 时间 08/17/2017 21:36:16

ORA-00312: 联机日志 1 线程 1:

'D:\APP\ORACLE\FAST_RECOVERY_AREA\XIFENFEI\ONLINELOG\O1_MF_1_DN9MV661_.LOG'

ORA-00312: 联机日志 1 线程 1:

'D:\APP\ORACLE\ORADATA\XIFENFEI\ONLINELOG\O1_MF_1_DN9MV652_.LOG'

SQL> recover database;

ORA-00283: recovery session canceled due to errors

ORA-00354: corrupt redo log block header

ORA-00353: log corruption near block 60 change 23924938639111 time 08/17/201721:36:16

ORA-00312: online log 1 thread 1:

'D:\APP\ORACLE\FAST_RECOVERY_AREA\XIFENFEI\ONLINELOG\O1_MF_1_DN9MV661_.LOG'

ORA-00312: online log 1 thread 1:

'D:\APP\ORACLE\ORADATA\XIFENFEI\ONLINELOG\O1_MF_1_DN9MV652_.LOG'

SQL> recover database until cancel;

ORA-00279: 更改 10050832 (在 08/17/2017 21:36:13 生成) 对于线程 1 是必需的

ORA-00289: 建议:

D:\APP\ORACLE\FAST_RECOVERY_AREA\XIFENFEI\ARCHIVELOG\2017_08_17\O1_MF_1_718_%U_.ARC

ORA-00280: 更改 10050832 (用于线程 1) 在序列 #718 中

指定日志: {=suggested | filename | AUTO | CANCEL}

D:\APP\ORACLE\FAST_RECOVERY_AREA\XIFENFEI\ONLINELOG\O1_MF_1_DN9MV661_.LOG

ORA-00283: recovery session canceled due to errors

ORA-00354: corrupt redo log block header

ORA-00353: log corruption near block 60 change 23924938639111 time 08/17/2017 21:36:16

ORA-00334: archived log:

'D:\APP\ORACLE\FAST_RECOVERY_AREA\XIFENFEI\ONLINELOG\O1_MF_1_DN9MV661_.LOG'

到这一步，只能通过屏蔽oracle 事务前滚，强制拉库恢复。

SQL> startup pfile='d:/app/pfile.txt' mount;

ORACLE 例程已经启动。

Total System Global Area 1.0301E+10 bytes

Fixed Size 3842760 bytes

Variable Size 1778388280 bytes

Database Buffers 8489271296 bytes

Redo Buffers 29708288 bytes

数据库装载完毕。

SQL> recover database until cancel;

ORA-00279: 更改 10050832 (在 08/17/2017 21:36:13 生成) 对于线程 1 是必需的

ORA-00289: 建议: D:\APP\ORACLE\FAST_RECOVERY_AREA\XIFENFEI\ARCHIVELOG\2017_08_17\O

1_MF_1_718_%U_.ARC

ORA-00280: 更改 10050832 (用于线程 1) 在序列 #718 中

指定日志: {=suggested | filename | AUTO | CANCEL}

cancel

ORA-01547: warning: RECOVER succeeded but OPEN RESETLOGS would get error below

ORA-01194: file 1 needs more recovery to be consistent

ORA-01110: data file 1: 'D:\APP\ORACLE\ORADATA\XIFENFEI\DATAFILE\O1_MF_SYSTEM_DN9M

N5OT_.DBF'

ORA-01112: 未启动介质恢复

SQL> alter database open resetlogs;

数据库已更改。

贼仙呐

关注

1
点赞
踩
1

收藏

觉得还不错? 一键收藏
0
评论
mysql启动错误1.69,12c ORA-01113 ORA-01110 恢复

数据库启动报错Completed: ALTER DATABASE MOUNTThu Aug 17 12:34:52 2017alter database openThu Aug 17 12:34:52 2017Ping without log force is disabled.Thu Aug 17 12:34:52 2017Errors in file D:\APP\ORACLE\diag\...
复制链接

扫一扫