首页 > 代码库 > 做dg时遇到的log_archive_dest、log_archive_dest_1、db_recovery_file_dest之间互相影响

做dg时遇到的log_archive_dest、log_archive_dest_1、db_recovery_file_dest之间互相影响

2024-07-08 00:51:52   222人阅读

前提：归档开启，默认不指定归档目录。

今晚遇到客户那里设置了闪回区和log_archive_dest，不停库做DG时，无法指定log_archive_dest_n参数，巨坑。。实验了下，结论如下：

数据库版本:ORACLE10.2.0.1

1.未设置闪回区时，可以动态配置log_archive_dest或log_archive_dest_1
2.配置闪回区时，不可以动态配置log_archive_dest
3.配置了闪回区和log_archive_dest后，不可动态配置log_archive_dest或log_archive_dest_1


1.未设置闪回区时，可以动态配置log_archive_dest或log_archive_dest_1

SQL> alter database archivelog;
Database altered.
SQL> alter database open;
Database altered.
SQL> show parameter log_archive
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
log_archive_config                   string
log_archive_dest                     string      /home/oracle/
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

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
log_archive_dest_9                   string
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

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
log_archive_duplex_dest              string
log_archive_format                   string      %t_%s_%r.dbf
log_archive_local_first              boolean     TRUE
log_archive_max_processes            integer     2
log_archive_min_succeed_dest         integer     1
log_archive_start                    boolean     FALSE
log_archive_trace                    integer     0
SQL> alter system set log_archive_dest=‘‘;
System altered.
SQL> show parameter recover
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
db_recovery_file_dest                string
db_recovery_file_dest_size           big integer 0
recovery_parallelism                 integer     0

2.配置闪回区时，不可以动态配置log_archive_dest

SQL> show parameter log_archive
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
log_archive_config                   string
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
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
log_archive_duplex_dest              string
log_archive_format                   string      %t_%s_%r.dbf
log_archive_local_first              boolean     TRUE
log_archive_max_processes            integer     2
log_archive_min_succeed_dest         integer     1
log_archive_start                    boolean     FALSE
log_archive_trace                    integer     0
SQL> alter system set log_archive_dest=‘/home/oracle/‘;   ----不可动态修改
alter system set log_archive_dest=‘/home/oracle/‘
*
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 log_archive_dest_1=‘location=/home/oracle/‘;   -----可动态修改
System altered.
SQL> alter system set log_archive_dest_1=‘‘;
System altered.
SQL> alter system set log_archive_dest=‘/home/oracle/‘ scope=spfile;


3.配置了闪回区和log_archive_dest后，不可动态配置log_archive_dest或log_archive_dest_1

SQL> startup
ORACLE instance started.
Total System Global Area  524288000 bytes
Fixed Size                  1220360 bytes
Variable Size             150995192 bytes
Database Buffers          369098752 bytes
Redo Buffers                2973696 bytes
Database mounted.
Database opened.
SQL> show parameter log_archive_dest
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
log_archive_dest                     string      /home/oracle/
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
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> alter system set log_archive_dest_1=‘location=/home/oracle/‘;    ------不可动态修改
alter system set log_archive_dest_1=‘location=/home/oracle/‘
*
ERROR at line 1:
ORA-02097: parameter cannot be modified because specified value is invalid
ORA-16019: cannot use LOG_ARCHIVE_DEST_1 with LOG_ARCHIVE_DEST or
LOG_ARCHIVE_DUPLEX_DEST


SQL>  alter system set log_archive_dest=‘‘;    ------不可动态修改
 alter system set log_archive_dest=‘‘
*
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


联系
我们