ORA-01052: required destination LOG_ARCHIVE_DUPLEX_DEST is not specified

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

启动数据库测试

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稍微大一点
参考:http://www.dbsnake.net/two-scn-internal-points.html