联系：手机/微信(+86 17813235971) QQ(107644445)
标题：ORA-01052: required destination LOG_ARCHIVE_DUPLEX_DEST is not specified 恢复思路
作者：惜分飞©版权所有[未经本人同意,不得以任何形式转载,否则有进一步追究法律责任的权利.]
今天一网友找到我,说数据库恢复在推scn的过程中遇到了ORA-01052: required destination LOG_ARCHIVE_DUPLEX_DEST is not specified错误无法解决,让我给予支持.这不禁让我想起,现在由于数据库bug和dblink原因导致了很多数据库scn很大,距离天花板非常近,从而使得数据库恢复过程中无法直接简单的推scn,这里正好结合该例子,简单说明下ORA-01052故障的处理.类似文档以前也写过:ORA-01052发生原因的类似文章
由于坏块导致数据库进行实例恢复无法进行
Beginning crash recovery of 1 threads Started redo scan Completed redo scan read 1901 KB redo, 276 data blocks need recovery Started redo application at Thread 1: logseq 1004, block 172771 Recovery of Online Redo Log: Thread 1 Group 2 Seq 1004 Reading mem 0 Mem# 0: F:\APP\ADMINISTRATOR\ORADATA\XFF\REDO02.LOG Fri May 29 10:59:56 2015 RECOVERY OF THREAD 1 STUCK AT BLOCK 439938 OF FILE 19 Fri May 29 11:00:00 2015 Exception [type: ACCESS_VIOLATION, UNABLE_TO_READ] [ADDR:0x2048] [PC:0x6215134, __intel_new_memcpy()+260] Fri May 29 11:00:12 2015 Trace dumping is performing id=[cdmp_20150529110012] Fri May 29 11:00:12 2015 Slave exiting with ORA-1172 exception Errors in file f:\app\administrator\diag\rdbms\XFF\XFF\trace\XFF_p007_1612.trc: ORA-01172: 线程 1 的恢复停止在块 439938 (在文件 19 中) ORA-01151: 如果需要, 请使用介质恢复以恢复块和还原备份 Fri May 29 11:00:27 2015 ORA-01578: ORACLE 数据块损坏 (文件号 19, 块号 450245) ORA-01110: 数据文件 19: 'F:\APP\ADMINISTRATOR\ORADATA\XFF\PSTORE_02.DBF' ORA-10564: tablespace PSTORE ORA-01110: 数据文件 19: 'F:\APP\ADMINISTRATOR\ORADATA\XFF\PSTORE_02.DBF' ORA-10561: block type 'TRANSACTION MANAGED INDEX BLOCK', data object# 91642 ORA-00607: 当更改数据块时出现内部错误 ORA-00602: 内部编程异常错误 ORA-07445: 出现异常错误: 核心转储 [_intel_new_memcpy()+260] [ACCESS_VIOLATION] [ADDR:0x2048] [PC:0x6215134] [UNABLE_TO_READ] [] Fri May 29 11:00:27 2015 Aborting crash recovery due to slave death, attempting serial crash recovery RECOVERY OF THREAD 1 STUCK AT BLOCK 439938 OF FILE 19 Fri May 29 11:00:45 2015 Trace dumping is performing id=[cdmp_20150529110045] Aborting crash recovery due to error 1172 ORA-1172 signalled during: alter database open...
设置_allow_resetlogs_corruption并resetlogs尝试打开数据库
Assigning activation ID 4272042346 (0xfea2316a) Thread 1 opened at log sequence 1 Current log# 1 seq# 1 mem# 0: F:\APP\ADMINISTRATOR\ORADATA\XFF\REDO01.LOG Successful open of redo thread 1 MTTR advisory is disabled because FAST_START_MTTR_TARGET is not set Fri May 29 11:30:47 2015 SMON: enabling cache recovery Fri May 29 11:30:47 2015 Errors in file f:\app\administrator\diag\rdbms\XFF\XFF\trace\XFF_ora_3004.trc (incident=181236): ORA-00600: ??????, ??: [2662], [3360], [2233437186], [3360], [2235447064], [4194545], [], [], [], [], [], [] Incident details in: f:\app\administrator\diag\rdbms\XFF\XFF\incident\incdir_181236\XFF_ora_3004_i181236.trc Errors in file f:\app\administrator\diag\rdbms\XFF\XFF\trace\XFF_ora_3004.trc: ORA-00704: ???????? ORA-00704: ???????? ORA-00600: ??????, ??: [2662], [3360], [2233437186], [3360], [2235447064], [4194545], [], [], [], [], [], [] Errors in file f:\app\administrator\diag\rdbms\XFF\XFF\trace\XFF_ora_3004.trc: ORA-00704: ???????? ORA-00704: ???????? ORA-00600: ??????, ??: [2662], [3360], [2233437186], [3360], [2235447064], [4194545], [], [], [], [], [], [] Error 704 happened during db open, shutting down database USER (ospid: 3004): terminating the instance due to error 704 Instance terminated by USER, pid = 3004 ORA-1092 signalled during: alter database open resetlogs... opiodr aborting process unknown ospid (3004) as a result of ORA-1092
这里可以看到数据库通过设置_allow_resetlogs_corruption参数，进行不完全恢复,跳过数据库启动的实例恢复,然后强制拉库,然后遭遇大家熟悉的ORA-600[2662]错误,使得恢复失败,根据经验,通过推scn来绕过该错误
使用_minimum_giga_scn尝试推SCN
SQL> shutdown immediate Database closed. Database dismounted. ORACLE instance shut down. -------------------------------- *._minimum_giga_scn=13443 -------------------------------- SQL> startup pfile='/tmp/pfile' ORACLE instance started. Total System Global Area 236000356 bytes Fixed Size 451684 bytes Variable Size 201326592 bytes Database Buffers 33554432 bytes Redo Buffers 667648 bytes Database mounted. ORA-01052: required destination LOG_ARCHIVE_DUPLEX_DEST is not specified
通过运行Oracle Database Recovery Check检查发现数据库的scn已经非常大,距离天花板较近
这里最大允许的推进的scn为13442.7,但是常规的最小的推scn的方法最小值为1024*1024*1024的倍数,因此这里遇到麻烦.
这里数据库遭遇了ORA-01052错误,导致推scn不成功,数据库无法正常启动.出现这类情况,由于scn可以增加的空间非常小,因此可以使用使用oradebug修改数据库scn或者直接修改控制文件scn的方式来精确控制推scn的值(可以实现任何值的scn增加,只要不超过天花板),也可以通过方法修改数据库scn距离天花板的距离,从而实现大幅度使用_minimum_giga_scn来推scn.另外还有一种解决方法:由于ORA-01052是由于scn过大导致(超过了数据库现在的天花板scn),因此出现了ORA-01052.所以另外一种变通的方法,就是通过调整数据库的天花板scn,从而使得_minimum_giga_scn可以继续推scn.在本次恢复中使用最为简单的增加天花板scn的方式来恢复(不过该方法恢复之后需要重建库,其实已经使用了隐含参数屏蔽redo恢复,本身就建议重建库保证数据字典一致性)