天萃荷净
分析关于ORA-01052: required destination LOG_ARCHIVE_DUPLEX_DEST is not specified产生原因
1.Oracle SCN说明
1、Oracle的SCN在每秒16384次commit的情况下可以维持534年,每秒16384次commit是Oracle早先认为的任何系统的极限commit强度;
2、Oracle里SCN的起点是1988年1月1日;
3、_minimum_giga_scn=n的含义是把SCN往前推进到nG,但请注意,只有在SCN小于nG的时候才会用到这个隐含参数,反之则Oracle会置这个隐含参数于不顾。
求模拟_minimum_giga_scn值
这里通过时间差,大概的模拟_minimum_giga_scn小于当前时间和1988年1月1日的scn最大值(300>290)
SQL> select to_char(sysdate,'yyyy-mm-dd hh24:mi:ss')from dual;
TO_CHAR(SYSDATE,'YYYY-MM-DDHH24:MI:SS'
--------------------------------------
2012-03-18 04:27:50
SQL> select months_between(sysdate,to_date('19880101','yyyymmdd')) from dual;
MONTHS_BETWEEN(SYSDATE,TO_DATE('19880101','YYYYMMDD'))
------------------------------------------------------
290.55443
SQL> select 16384*60*60*24*31*300/(1024*1024*1024) SCN from dual;
SCN
----------
12260.7422
2.启动Oracle数据库
SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
--------------------------------
*._minimum_giga_scn=12260
--------------------------------
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
分析ORA-01052
SQL> !oerr ora 1052
01052, 00000, "required destination LOG_ARCHIVE_DUPLEX_DEST is not specified"
// *Cause: A valid destination for parameter LOG_ARCHIVE_DUPLEX_DEST was not
// specified when parameter LOG_ARCHIVE_MIN_SUCCEED_DEST was set to
// two.
// *Action: Either specify a value for parameter LOG_ARCHIVE_DUPLEX_DEST, or
// reduce the value for parameter LOG_ARCHIVE_MIN_SUCCEED_DEST to one.
SQL> show parameter LOG_ARCHIVE_DUPLEX_DEST;
NAME TYPE VALUE
------------------------------------ ---------- ------------------------------
log_archive_duplex_dest string
SQL> show parameter LOG_ARCHIVE_MIN_SUCCEED_DEST
NAME TYPE VALUE
------------------------------------ ---------- ------------------------------
log_archive_min_succeed_dest integer 1
这里可以看出,不是以为我上面的两个参数设置错误导致ORA-01052,而是因为推进scn过大导致ORA-01052的错误
减小_minimum_giga_scn测试
这里选择_minimum_giga_scn小于当前时间和1988年1月1日的scn最大值(280<290)
SQL> select 16384*60*60*24*31*280/(1024*1024*1024) SCN from dual;
SCN
———-
11443.3594
SQL> shutdown immediate
ORA-01109: database not open
Database dismounted.
ORACLE instance shut down.
—————————–
*._minimum_giga_scn=11443
—————————–
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.
Database opened.
SQL> select to_char(dbms_flashback.get_system_change_number(),
2 ’9999999999999999′) from dual;
TO_CHAR(DBMS_FLASHBACK.GET_SYSTEM_
———————————-
12286827692251
SQL> select dbms_flashback.get_system_change_number()/(1024*1024*1024) from dual;
DBMS_FLASHBACK.GET_SYSTEM_CHANGE_NUMBER()/(1024*1024*1024)
———————————————————-
11443
通过试验可以发现,在我们的数据库遇到异常,需要恢复通过推进scn来恢复的时候,不是推进的越大越好;如果推进的太大可能导致ORA-01052错误,一般建议是比当前不一致的scn稍微大一点
--------------------------------------ORACLE-DBA----------------------------------------
最权威、专业的Oracle案例资源汇总之案例:Oracle报错ORA-01052 required destinatio 分析ora错误产生原因