【archive_dest】归档的路径问题

初始环境：

查看归档模式：

SYS@PROD> archive log list;

Database log mode              Archive Mode

Automatic archival             Enabled

Archive destination            /u01/app/oracle/product/10.2.0/db_1/dbs/arch

Oldest online log sequence     2

Next log sequence to archive   5

Current log sequence           5

 

查看参数log_archive_dest：

SYS@PROD> show parameter log_archive_dest;

 

NAME                                 TYPE        VALUE

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

log_archive_dest                     string

 

查看闪回状态:

SYS@PROD> select flashback_on from v$database;

 

FLASHBACK_ON

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

NO

闪回未开启。

==============================================================================

 

闪回未开启时的归档路径测试：

 

进行归档，并查看归档路径：

SYS@PROD> alter system switch logfile;

 

System altered.

SYS@PROD> select name from v$archived_log;

 

NAME

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

/u01/app/oracle/product/10.2.0/db_1/dbs/arch1_5_809806951.dbf

/u01/app/oracle/product/10.2.0/db_1/dbs/ar_test1_6_809806951.dbf

/u01/app/oracle/product/10.2.0/db_1/dbs/ar_test1_7_809806951.dbf

3条记录归档在了Archive destination   /u01/app/oracle/product/10.2.0/db_1/dbs/arch

路径下。

 

此时想修改归档的路径，设置参数log_archive_dest

 

SYS@PROD> alter system set log_archive_dest='/home/oracle/ar_test';

System altered.

 

SYS@PROD> show parameter log_archive_dest;

NAME                                 TYPE        VALUE

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

log_archive_dest                     string      /home/oracle/ar_test

 

SYS@PROD> archive log list;

Database log mode              Archive Mode

Automatic archival             Enabled

Archive destination            /home/oracle/ar_test

Oldest online log sequence     4

Next log sequence to archive   8

Current log sequence           8

 

此时再次实验查看归档路径：

 

SYS@PROD> alter system switch logfile;

 

System altered.

 

SYS@PROD> select name from v$archived_log;

 

NAME

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

/u01/app/oracle/product/10.2.0/db_1/dbs/arch1_5_809806951.dbf

/u01/app/oracle/product/10.2.0/db_1/dbs/ar_test1_6_809806951.dbf

/u01/app/oracle/product/10.2.0/db_1/dbs/ar_test1_7_809806951.dbf

/home/oracle/ar_test/1_8_809806951.dbf

 

多出第4条归档结果的存放路径发生了变化

 

上述的操作，未开启闪回。接下来看看闪回开启后归档路径会发生什么变化。

闪回开启：

 

SYS@PROD> alter system set db_recovery_file_dest_size=2g;

 

System altered.

 

SYS@PROD> alter system set db_recovery_file_dest='/home/oracle/flash';

alter system set db_recovery_file_dest='/home/oracle/flash'

*

ERROR at line 1:

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

ORA-16019: cannot use db_recovery_file_dest with LOG_ARCHIVE_DEST or

LOG_ARCHIVE_DUPLEX_DEST

 

因为设置了参数LOG_ARCHIVE_DEST，所以无法再设置闪回恢复区的目录，也就无法开启归档。

 

只好取消LOG_ARCHIVE_DEST的设置

SYS@PROD> alter system set LOG_ARCHIVE_DEST='';

 

System altered.

 

SYS@PROD> archive log list;

Database log mode              Archive Mode

Automatic archival             Enabled

Archive destination            ?/dbs/arch

Oldest online log sequence     5

Next log sequence to archive   9

Current log sequence           9

 

如果此时如果在进行归档：

SYS@PROD> alter system switch logfile;

 

System altered.

 

SYS@PROD> select name from v$archived_log;

 

NAME

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

/u01/app/oracle/product/10.2.0/db_1/dbs/arch1_5_809806951.dbf

/u01/app/oracle/product/10.2.0/db_1/dbs/ar_test1_6_809806951.dbf

/u01/app/oracle/product/10.2.0/db_1/dbs/ar_test1_7_809806951.dbf

/home/oracle/ar_test/1_8_809806951.dbf

 

你将无法找到新生成的归档，直到我们再次设置了新的归档路径，此次消失的归档文件将和下一次新产生的归档文件同时出现

 

SYS@PROD> alter system set log_archive_dest='/home/oracle/ar_test';

 

System altered.

 

SYS@PROD> select name from v$archived_log;

 

NAME

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

/u01/app/oracle/product/10.2.0/db_1/dbs/arch1_5_809806951.dbf

/u01/app/oracle/product/10.2.0/db_1/dbs/ar_test1_6_809806951.dbf

/u01/app/oracle/product/10.2.0/db_1/dbs/ar_test1_7_809806951.dbf

/home/oracle/ar_test/1_8_809806951.dbf

还是4条记录，我们在归档一次：

 

SYS@PROD> alter system switch logfile;

 

System altered.

 

SYS@PROD> select name from v$archived_log;

 

NAME

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

/u01/app/oracle/product/10.2.0/db_1/dbs/arch1_5_809806951.dbf

/u01/app/oracle/product/10.2.0/db_1/dbs/ar_test1_6_809806951.dbf

/u01/app/oracle/product/10.2.0/db_1/dbs/ar_test1_7_809806951.dbf

/home/oracle/ar_test/1_8_809806951.dbf

/home/oracle/ar_test/1_9_809806951.dbf

/home/oracle/ar_test/1_10_809806951.dbf

 

6 rows selected.

6条记录出现了。

 

如果，我们设置了参数log_archive_dest，闪回是无法开启的。

取消该参数的设置：

SYS@PROD> alter system set log_archive_dest='';

 

System altered.

 

设置db_recovery_file_dest

SYS@PROD> alter system set db_recovery_file_dest='/home/oracle/flash';

 

System altered.

 

SYS@PROD> show parameter db_recovery_file_dest;

 

NAME                                 TYPE        VALUE

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

db_recovery_file_dest                string      /home/oracle/flash

db_recovery_file_dest_size           big integer 2G

 

取消db_recovery_file_dest参数后就可以了。虽然此时闪回还没有打开，但此时新的归档路径已经变成db_recovery_file_dest=’/home/oracle/flash’

 

SYS@PROD> alter system switch logfile;

 

System altered.

 

SYS@PROD> archive log list;

Database log mode              Archive Mode

Automatic archival             Enabled

Archive destination            USE_DB_RECOVERY_FILE_DEST

Oldest online log sequence     8

Next log sequence to archive   12

Current log sequence           12

SYS@PROD> select name from v$archived_log;

 

NAME

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

/u01/app/oracle/product/10.2.0/db_1/dbs/arch1_5_809806951.dbf

/u01/app/oracle/product/10.2.0/db_1/dbs/ar_test1_6_809806951.dbf

/u01/app/oracle/product/10.2.0/db_1/dbs/ar_test1_7_809806951.dbf

/home/oracle/ar_test/1_8_809806951.dbf

/home/oracle/ar_test/1_9_809806951.dbf

/home/oracle/ar_test/1_10_809806951.dbf

/home/oracle/flash/PROD/archivelog/2013_03_13/o1_mf_1_11_8n106whc_.arc

 

7 rows selected.

我们清楚地看到第七条结果了

 

接下来，我们正式开始闪回

SYS@PROD> select flashback_on from v$database;

 

FLASHBACK_ON

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

YES

在来重复上面的归档操作看看：

SYS@PROD> archive log list;

Database log mode              Archive Mode

Automatic archival             Enabled

Archive destination            USE_DB_RECOVERY_FILE_DEST

Oldest online log sequence     8

Next log sequence to archive   12

Current log sequence           12

 

SYS@PROD> alter system switch logfile;

 

System altered.

SYS@PROD> select name from v$archived_log;

 

NAME

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

/u01/app/oracle/product/10.2.0/db_1/dbs/arch1_5_809806951.dbf

/u01/app/oracle/product/10.2.0/db_1/dbs/ar_test1_6_809806951.dbf

/u01/app/oracle/product/10.2.0/db_1/dbs/ar_test1_7_809806951.dbf

/home/oracle/ar_test/1_8_809806951.dbf

/home/oracle/ar_test/1_9_809806951.dbf

/home/oracle/ar_test/1_10_809806951.dbf

/home/oracle/flash/PROD/archivelog/2013_03_13/o1_mf_1_11_8n106whc_.arc

/home/oracle/flash/PROD/archivelog/2013_03_13/o1_mf_1_12_8n10qpfm_.arc

 

8 rows selected.

8条记录了，新生成的归档在闪回恢复区中。

我们似乎看到，其实不论我们是否执行了：

Alter  database  flashback  on命令

来真正的开启闪回，当我们设置了参数db_recovery_file_dest时，归档的路径就已经确定，而且无法再使用参数log_archive_dest。

 

如果，此时想形成多路径归档日志，可以通过设置参数log_archive_dest_n

这里设置log_archive_dest_1

SYS@PROD> alter system set log_archive_dest_1='location=/home/oracle/ar_test';

 

System altered.

 

SYS@PROD> archive log list;

Database log mode              Archive Mode

Automatic archival             Enabled

Archive destination            /home/oracle/ar_test

Oldest online log sequence     9

Next log sequence to archive   13

Current log sequence           13

 

SYS@PROD> alter system switch logfile;

 

System altered.

 

SYS@PROD> select name from v$archived_log;

 

NAME

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

/u01/app/oracle/product/10.2.0/db_1/dbs/arch1_5_809806951.dbf

/u01/app/oracle/product/10.2.0/db_1/dbs/ar_test1_6_809806951.dbf

/u01/app/oracle/product/10.2.0/db_1/dbs/ar_test1_7_809806951.dbf

/home/oracle/ar_test/1_8_809806951.dbf

/home/oracle/ar_test/1_9_809806951.dbf

/home/oracle/ar_test/1_10_809806951.dbf

/home/oracle/flash/PROD/archivelog/2013_03_13/o1_mf_1_11_8n106whc_.arc

/home/oracle/flash/PROD/archivelog/2013_03_13/o1_mf_1_12_8n10qpfm_.arc

/home/oracle/ar_test/1_13_809806951.dbf

 

9 rows selected.

log_archive_dest_1参数设置后，形成了新的归档路径，且不在闪回恢复区中进行归档。

 

仅接着再来设置一个参数log_archive_dest_2：

SYS@PROD> alter system set log_archive_dest_2='location=/home/oracle/ar_test1';

 

System altered.

 

SYS@PROD> show parameter log_archive_dest;

 

NAME                                 TYPE        VALUE

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

log_archive_dest                     string

log_archive_dest_1                   string      location=/home/oracle/ar_test

log_archive_dest_10                  string

log_archive_dest_2                   string      location=/home/oracle/ar_test1

log_archive_dest_3                   string

 

SYS@PROD> archive log list;

Database log mode              Archive Mode

Automatic archival             Enabled

Archive destination            /home/oracle/ar_test1

Oldest online log sequence     11

Next log sequence to archive   15

Current log sequence           15

 

进行归档：

SYS@PROD> alter system switch logfile;

 

System altered.

 

SYS@PROD> select name from v$archived_log;

 

NAME

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

/u01/app/oracle/product/10.2.0/db_1/dbs/arch1_5_809806951.dbf

/u01/app/oracle/product/10.2.0/db_1/dbs/ar_test1_6_809806951.dbf

/u01/app/oracle/product/10.2.0/db_1/dbs/ar_test1_7_809806951.dbf

/home/oracle/ar_test/1_8_809806951.dbf

/home/oracle/ar_test/1_9_809806951.dbf

/home/oracle/ar_test/1_10_809806951.dbf

/home/oracle/flash/PROD/archivelog/2013_03_13/o1_mf_1_11_8n106whc_.arc

/home/oracle/flash/PROD/archivelog/2013_03_13/o1_mf_1_12_8n10qpfm_.arc

/home/oracle/ar_test/1_13_809806951.dbf

/home/oracle/ar_test/1_14_809806951.dbf

/home/oracle/ar_test1/1_14_809806951.dbf

 

11 rows selected.

此时有两个归档记录生成，且两个文件的名字一样，只是存放的路径不同。

 

结论：

（1）       刚开启归档时，将有一个默认的归档路径：

/u01/app/oracle/product/10.2.0/db_1/dbs/arch，可以通过设置参数log_archive_dest来设定新的归档路径

（2）       如果设置了参数log_archive_dest，将无法开启设置参数db_recovery_file_dest来配置闪回恢复区，也就无法开启闪回。

（3）       log_archive_dest无法和参数log_archive_dest_n, db_recovery_file_dest同时存在

（4）       log_archive_dest_n设置后将做为新的归档路径，设置几个参数就会有几个几路归档，同时也不会再在闪回恢复区中进行归档。

（5）       “曾看到资料说，db_recovery_file_dest设置后，oracle会隐式的设置log_archive_dest

_10为db_recovery_file_dest的内容”这里无从验证。

======================================================================================

其实，到这里，经朋友的提示，发现了一个蛋疼的问题：设置后log_archive_dest后，我们可以关闭数据库在pfile里设置pfiledb_recovery_file_dest的值，然后生成spfile启动数据库，此时，这几个参数将同时有效的存在，归档将会在这两个参数下的路径下进行2路归档。但是此时你将无法在数据库开启状态下对这几个参数通过spfile进行任何修改，除非你在去pfile里进行修改。至于非要这样修改的目的是什么，不知道。暂时只是知道有这么一回事！

ballontt

2013/3/13

---The End---

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/27425054/viewspace-756096/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/27425054/viewspace-756096/