oracle10g归档的设置讨论

本文讨论涉及的参数有：

 

log_archive_dest

log_archive_dest_n

log_archive_start

db_recovery_file_dest  

 

软件环境:HP-UNIX11.31  IA64

硬件环境:RX6600 

Oracle版本：

 

SQL> select * from v$version;

 

BANNER

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

Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - Prod

PL/SQL Release 10.2.0.4.0 - Production

CORE    10.2.0.4.0      Production

TNS for Solaris: Version 10.2.0.4.0 - Production

NLSRTL Version 10.2.0.4.0 – Production

 

Oracle为10g的企业版

 

SQL> show parameter spfile

 

NAME   TYPE                   VALUE

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

spfile  string     /export/home/oracledb/oracle/p roduct/10.2.0/dbs/spfileorcl.ora

 

使用的是spfile参数。

                                                      

SQL> alter system set log_archive_dest_1='location=/export/home/arch';

System altered.

 

SQL> alter system set log_archive_dest_1=''; #置为空的时候, 没有location字样.

System altered.

 

SQL> alter system set log_archive_dest='/export/home/arch';

alter system set log_archive_dest='/export/home/arch'

*

ERROR at line 1:

ORA-02097: parameter cannot be modified because specified value is invalid ORA-16018: cannot use LOG_ARCHIVE_DEST with LOG_ARCHIVE_DEST_n orDB_RECOVERY_FILE_DEST

 

报错，报错信息是LOG_ARCHIVE_DEST 和LOG_ARCHIVE_DEST_n不能同时使用，但是，现在log_archive_dest_N已经为空了，为何还不能使用log_archive_dest呢，检查如下设置：

 

SQL> show parameter LOG_ARCHIVE_DEST

 

NAME                                 TYPE                   VALUE

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

log_archive_dest                     string

log_archive_dest_1                   string

log_archive_dest_10                  string

log_archive_dest_2                   string

log_archive_dest_3                   string

log_archive_dest_4                   string

log_archive_dest_5                   string

log_archive_dest_6                   string

log_archive_dest_7                   string

log_archive_dest_8                   string

log_archive_dest_9                   string

 

NAME                                 TYPE                   VALUE

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

log_archive_dest_state_1             string                 enable

log_archive_dest_state_10            string                 enable

log_archive_dest_state_2             string                 enable

log_archive_dest_state_3             string                 enable

log_archive_dest_state_4             string                 enable

log_archive_dest_state_5             string                 enable

log_archive_dest_state_6             string                 enable

log_archive_dest_state_7             string                 enable

log_archive_dest_state_8             string                 enable

log_archive_dest_state_9             string                 enable

SQL>

 

log_archive_dest和log_archive_dest_1都为空了，但是DB_RECOVERY_FILE_DEST值却不为空：

 

SQL> show parameter DB_RECOVERY_FILE_DEST

 

NAME             TYPE                   VALUE

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

db_recovery_file_dest string        /export/home/oracledb/oracle/flash_recovery_area

db_recovery_file_dest_size           big integer            2G

 

原因就在这里，置空DB_RECOVERY_FILE_DEST

 

SQL> alter system set db_recovery_file_dest='';

 

System altered.

 

SQL> show parameter DB_RECOVERY_FILE_DEST

 

NAME                                 TYPE                   VALUE

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

db_recovery_file_dest                string

db_recovery_file_dest_size           big integer            2G

 

重新执行log_archive_dest设置

 

SQL> alter system set log_archive_dest='/export/home/arch';

 

System altered.

 

可以顺利执行了

 

另外一个参数，log_archive_start在oracle10g已经废弃，但是oracle并没有抛弃这个参数，还是可以查到：

SQL> show parameter log_archive_start

 

NAME                                 TYPE                   VALUE

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

log_archive_start                    boolean                FALSE

SQL>

 

默认值为FALSE，并且不能修改， oracle10g在启用归档后自动归档。不用设置自动归档，因此无需理会此值。

 

总结：

log_archive_dest和log_archive_des_n不能同时使用，10g默认归档路径log_archive_dest_10 为 flash _recovery_area，即DB_RECOVERY_FILE_DEST，因此log_archive_dest也不能于DB_RECOVERY_FILE_DEST一起使用。

 

换句话说：LOG_ARCHIVE_DEST和 LOG_ARCHIVE_DUPLEX_DES是一组
LOG_ARCHIVE_DEST_1。。。10，又是一组，你只能选择一组归档。

 

从以上,我们可以看到这个两个参数可以互换的, 也就是说原来的是log_archive_dest,现在可以用log_archive_dest_1.反之亦然.但是不能够同时使用.

 

注意以上log_archive_dest 和log_archive_dest_1都置为空的时候, 系统会自动设置归档的目的地. -->?/dbs/arch.

 

 

 

Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Production

With the Partitioning, OLAP, Data Mining and Real Application Testing options

$ sqlplus '/as sysdba'

 

SQL*Plus: Release 10.2.0.4.0 - Production on Wed Apr 1 15:09:55 2009

 

Copyright (c) 1982, 2007, Oracle.  All Rights Reserved.

 

Connected to an idle instance.

 

SQL> startup

ORACLE instance started.

 

Total System Global Area 3925868544 bytes

Fixed Size                  2060936 bytes

Variable Size             754978168 bytes

Database Buffers         3154116608 bytes

Redo Buffers               14712832 bytes

Database mounted.

Database opened.

SQL> shutdown immedaite

SP2-0717: illegal SHUTDOWN option

SQL> shutdown immediate

Database closed.

Database dismounted.

ORACLE instance shut down.

SQL> startup mount

ORACLE instance started.

 

Total System Global Area 3925868544 bytes

Fixed Size                  2060936 bytes

Variable Size             754978168 bytes

Database Buffers         3154116608 bytes

Redo Buffers               14712832 bytes

Database mounted.

SQL> alter system set log_archive_dest_1='location=/archivelog';

 

System altered.

 

SQL> show parameter log_archive_dest

 

NAME                                 TYPE        VALUE

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

log_archive_dest                     string

log_archive_dest_1                   string      location=/archivelog

log_archive_dest_10                  string

log_archive_dest_2                   string

log_archive_dest_3                   string

log_archive_dest_4                   string

log_archive_dest_5                   string

log_archive_dest_6                   string

log_archive_dest_7                   string

log_archive_dest_8                   string

log_archive_dest_9                   string

 

NAME                                 TYPE        VALUE

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

log_archive_dest_state_1             string      enable

log_archive_dest_state_10            string      enable

log_archive_dest_state_2             string      enable

log_archive_dest_state_3             string      enable

log_archive_dest_state_4             string      enable

log_archive_dest_state_5             string      enable

log_archive_dest_state_6             string      enable

log_archive_dest_state_7             string      enable

log_archive_dest_state_8             string      enable

log_archive_dest_state_9             string      enable

SQL> show parameter db_recovery_dest

SQL> ;

  1* alter system set log_archive_dest_1='location=/archivelog'

SQL> show parameter db_recovery_file_dest;

 

NAME                                 TYPE        VALUE

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

db_recovery_file_dest                string      /oracle/flash_recovery_area

db_recovery_file_dest_size           big integer 2G

SQL> alter database open; 

 

Database altered.

 

SQL> shutdown immediate

Database closed.

Database dismounted.

ORACLE instance shut down.

SQL> startup mount

ORACLE instance started.

 

Total System Global Area 3925868544 bytes

Fixed Size                  2060936 bytes

Variable Size             754978168 bytes

Database Buffers         3154116608 bytes

Redo Buffers               14712832 bytes

Database mounted.

SQL> alter database archivelog;

 

Database altered.

 

SQL> alter database flashback on;

 

Database altered.

 

SQL> archive log list

Database log mode              Archive Mode

Automatic archival             Enabled

Archive destination            USE_DB_RECOVERY_FILE_DEST

Oldest online log sequence     339

Next log sequence to archive   341

Current log sequence           341

 

SQL> alter database open;

 

Database altered.

 

SQL>

 

 

 

 

 

 

 

SQL> startup mount;

ORACLE instance started.

 

Total System Global Area 3925868544 bytes

Fixed Size                  2060936 bytes

Variable Size             754978168 bytes

Database Buffers         3154116608 bytes

Redo Buffers               14712832 bytes

Database mounted.

SQL> alter system set log_archive_dest='/archivelog';

alter system set log_archive_dest='/archivelog'

*

ERROR at line 1:

ORA-02097: parameter cannot be modified because specified value is invalid

ORA-16018: cannot use LOG_ARCHIVE_DEST with LOG_ARCHIVE_DEST_n or

DB_RECOVERY_FILE_DEST

 

 

 

SQL> alter system set db_recovery_file_dest='';

 

System altered.

 

 

SQL> alter system set log_archive_dest='/archivelog';

 

System altered.

 

SQL> alter database open;

 

Database altered.

 

 

SQL> archive log list;

Database log mode              Archive Mode

Automatic archival             Enabled

Archive destination            /archivelog

Oldest online log sequence     357

Next log sequence to archive   359

Current log sequence           359

 

最简便的方法：直接修改$ORACLE_HOME/dbs/initSID.ora文件，注释掉下面两行

db_recovery_file_dest

db_recovery_file_dest_size

增加log_archive_dest=/archivelog(目的归档位置)