- oracle 10g中db_recovery_file_dest和log_archive_dest参数的关系
我要投稿-
在10g中，如果指定了db_recovery_file_dest 参数，那么归档日志将存放到这个参数指向的目录下，如果同时指定了log_archive_desc，则将同时往这两个参数指定的目录中归档。
1.只设置db_recovery_file_desc参数，log_archive_desc设置为空SQL> show parameter db_recovery
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_recovery_file_dest string /ora10g/flash_recovery_area
db_recovery_file_dest_size big integer 1G
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> select name,completion_time from v$archived_log;
no rows selected
SQL> alter system switch logfile;
System altered.
SQL> select name,completion_time from v$archived_log;
NAME
--------------------------------------------------------------------------------
COMPLETION_TIME
-----------------
/ora10g/flash_recovery_area/EBANK/archivelog/2008_12_18/o1_mf_1_1_4no7w6tg_.arc
20081218 14:07:50
SQL> col name for a100
SQL> set lines 132
SQL> /
NAME COMPLETION_TIME
---------------------------------------------------------------------------------------------------- -----------------
/ora10g/flash_recovery_area/EBANK/archivelog/2008_12_18/o1_mf_1_1_4no7w6tg_.arc 20081218 14:07:502.设置log_archive_desc参数，同时db_recovery_file_desc参数被设置为空。
SQL> alter system set db_recovery_file_dest='' scope=spfile;
System altered.
SQL> alter system set log_archive_dest='/ora10g/arch' scope=spfile;
System altered.
SQL> startup force;
ORACLE instance started.
Total System Global Area 209715200 bytes
Fixed Size 1218580 bytes
Variable Size 104859628 bytes
Database Buffers 100663296 bytes
Redo Buffers 2973696 bytes
Database mounted.
Database opened.
SQL> alter session set nls_date_format='yyyymmdd hh24:mi:ss';
Session altered.
SQL> col name for a100
SQL> set lines 132
SQL> alter system switch logfile;
System altered.
SQL> select name,completion_time from v$archived_log;
NAME COMPLETION_TIME
---------------------------------------------------------------------------------------------------- -----------------
/ora10g/flash_recovery_area/EBANK/archivelog/2008_12_18/o1_mf_1_1_4no7w6tg_.arc 20081218 14:07:50
/ora10g/arch/1_2_673797803.dbf 20081218 14:21:01
/ora10g/flash_recovery_area/EBANK/archivelog/2008_12_18/o1_mf_1_2_4no8nwq9_.arc 20081218 14:21:01
/ora10g/arch/1_3_673797803.dbf 20081218 14:22:15
/ora10g/arch/1_4_673797803.dbf 20081218 14:23:03
SQL> alter system switch logfile;
System altered.
SQL> select name,completion_time from v$archived_log;
NAME COMPLETION_TIME
---------------------------------------------------------------------------------------------------- -----------------
/ora10g/flash_recovery_area/EBANK/archivelog/2008_12_18/o1_mf_1_1_4no7w6tg_.arc 20081218 14:07:50
/ora10g/arch/1_2_673797803.dbf 20081218 14:21:01
/ora10g/flash_recovery_area/EBANK/archivelog/2008_12_18/o1_mf_1_2_4no8nwq9_.arc 20081218 14:21:01
/ora10g/arch/1_3_673797803.dbf 20081218 14:22:15
/ora10g/arch/1_4_673797803.dbf 20081218 14:23:03
/ora10g/arch/1_5_673797803.dbf 20081218 14:23:36
6 rows selected.3.log_archive_desc和db_recovery_file_desc参数被同时设置
SQL> alter system switch logfile;
System altered.
SQL> /
System altered.
SQL> /
System altered.
SQL> alter system switch logfile;
System altered.
SQL> select name,completion_time from v$archived_log;
NAME COMPLETIO
---------------------------------------------------------------------------------------------------- ---------
/ora10g/flash_recovery_area/EBANK/archivelog/2008_12_18/o1_mf_1_1_4no7w6tg_.arc 18-DEC-08
/ora10g/arch/1_2_673797803.dbf 18-DEC-08
/ora10g/flash_recovery_area/EBANK/archivelog/2008_12_18/o1_mf_1_2_4no8nwq9_.arc 18-DEC-08
/ora10g/arch/1_3_673797803.dbf 18-DEC-08
/ora10g/arch/1_4_673797803.dbf 18-DEC-08
/ora10g/arch/1_5_673797803.dbf 18-DEC-08
/ora10g/arch/1_6_673797803.dbf 18-DEC-08
/ora10g/flash_recovery_area/EBANK/archivelog/2008_12_18/o1_mf_1_6_4no8xl3j_.arc 18-DEC-08
/ora10g/arch/1_7_673797803.dbf 18-DEC-08
/ora10g/flash_recovery_area/EBANK/archivelog/2008_12_18/o1_mf_1_7_4no8y3qy_.arc 18-DEC-08
/ora10g/arch/1_8_673797803.dbf 18-DEC-08
NAME COMPLETIO
---------------------------------------------------------------------------------------------------- ---------
/ora10g/flash_recovery_area/EBANK/archivelog/2008_12_18/o1_mf_1_8_4no8ys0k_.arc 18-DEC-08
/ora10g/arch/1_9_673797803.dbf 18-DEC-08
/ora10g/flash_recovery_area/EBANK/archivelog/2008_12_18/o1_mf_1_9_4no8zdk2_.arc 18-DEC-08
/ora10g/arch/1_10_673797803.dbf 18-DEC-08
/ora10g/flash_recovery_area/EBANK/archivelog/2008_12_18/o1_mf_1_10_4no8zfjg_.arc 18-DEC-08
/ora10g/arch/1_11_673797803.dbf 18-DEC-08
/ora10g/flash_recovery_area/EBANK/archivelog/2008_12_18/o1_mf_1_11_4no8zg7s_.arc 18-DEC-08
/ora10g/arch/1_12_673797803.dbf 18-DEC-08
/ora10g/flash_recovery_area/EBANK/archivelog/2008_12_18/o1_mf_1_12_4no8zl4s_.arc 18-DEC-08
20 rows selected.========================================================================================================================================================================
DB_RECOVERY_FILE_DEST参数是默认的flash recovery area的路径，里面存放有归档日志、闪回日志以及rman的备份文件等文件。LOG_ARCHIVE_DEST_n参数是存放归档日志的路径，n表示1~10的一个整数，由于归档日志在recovery的时候担当了重要的角色，所以我们可以设置多个归档目录（最多可以设置10个）。这种情况下，联机日志在归档时，会在不同的目录下都会生成有一个相同的归档文件，通过冗余的来保证归档日志的安全。
系统默认的情况是以DB_RECOVERY_FILE_DEST定义的目录来存放归档日志。但是，课上演示的时候，老师已经设置了log_archive_dest_1、log_archive_dest_2和log_archive_dest_3，三个参数。我现在将这三个参数修改回去，让系统使用默认的DB_RECOVERY_FILE_DEST目录来存放归档日志。
经过下面的操作，发现提示找不到归档的目录。
SQL> alter system set log_archive_dest_1='';
SQL> alter system set log_archive_dest_2='';
SQL> alter system set log_archive_dest_3='';
SQL> alter system archive log current;
alter system archive log current
*
ERROR at line 1:
ORA-16014: log 2 sequence# 50 not archived, no available destinations
ORA-00312: online log 2 thread 1: '/opt/ora10g/oradata/orcl/redo02.log'
SQL> archive log list;
Database log mode Archive Mode
Automatic archival Enabled
Archive destination ?/dbs/arch
Oldest online log sequence 49
Next log sequence to archive 50
Current log sequence 51
解决办法，我们暂时先指定一个目录来存放归档日志。然后再修改三个log_archive_dest_n的系统参数时，指定好scope为spfile，重启实例即可。
SQL> alter system set log_archive_dest_1='location=/logs/log_1';
SQL> alter system archive log current;
System altered.
SQL> alter system set log_archive_dest_1='' scope=spfile;
SQL> alter system set log_archive_dest_2='' scope=spfile;
SQL> alter system set log_archive_dest_3='' scope=spfile;
SQL> startup force
ORACLE instance started.
Total System Global Area 285212672 bytes
Fixed Size 1218992 bytes
Variable Size 104859216 bytes
Database Buffers 176160768 bytes
Redo Buffers 2973696 bytes
Database mounted.
Database opened.
查看一下现在使用的归档的目录，已经成功改回为了DB_RECOVERY_FILE_DEST。
SQL> archive log list;
Database log mode Archive Mode
Automatic archival Enabled
Archive destination USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence 51
Next log sequence to archive 53
Current log sequence 53
最后再查看一下log_archive_dest_n参数，这些参数都没有被设置了，均为空：
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
小结：log_archive_dest_n与DB_RECOVERY_FILE_DEST参数都可以用来存放归档日志，但二者的关系是：不设置log_archive_dest_n时，使用DB_RECOVERY_FILE_DEST来存文件；当设置了log_archive_dest_n时，则使用log_archive_dest_n来存放归档日志。
下面我们，再对log_archive_dest_n进行设置，让DB_RECOVERY_FILE_DEST“失效”。
SQL>alter system set log_archive_dest_1='location=/logs/log_1' scope=spfile;
SQL>alter system set log_archive_dest_2='location=/logs/log_2' scope=spfile;
SQL>alter system set log_archive_dest_3='location=/logs/log_3' scope=spfile;
SQL> startup force
ORACLE instance started.
Total System Global Area 285212672 bytes
Fixed Size 1218992 bytes
Variable Size 109053520 bytes
Database Buffers 171966464 bytes
Redo Buffers 2973696 bytes
Database mounted.
Database opened.
我们进行下手动归档
SQL> alter system archive log current;
查看下系统时间，发现三个目录下，都已经有了最新的归档日志：
[root@localhost logs]$ date
Wed Sep 1 10:48:39 CST 2010
[root@localhost logs]# ll -t log_1
-rw-r----- 1 oracle oinstall 280576 Sep 1 10:48 1_54_723037014.dbf
[root@localhost logs]# ll -t log_2
-rw-r----- 1 oracle oinstall 280576 Sep 1 10:48 1_54_723037014.dbf
[root@localhost logs]# ll -t log_3
-rw-r----- 1 oracle oinstall 280576 Sep 1 10:48 1_54_723037014.dbf
而DB_RECOVERY_FILE_DEST目录下，已经不再会存放新的归档日志了：
[oracle@localhost archivelog]$ ll 2010_09_01/
作者“FJXSUNMIT”
1205
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
