oracle11g_开启归档模式和归档路径的设置

–查看当前归档模式是否开启====
SQL> archive log list;
Database log mode No Archive Mode
Automatic archival Disabled
Archive destination USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence 72
Current log sequence 74

–归档模式开启需要在mount状态下进行======
SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup mount;
ORACLE instance started.

Total System Global Area 764121088 bytes
Fixed Size 2257152 bytes
Variable Size 503320320 bytes
Database Buffers 255852544 bytes
Redo Buffers 2691072 bytes
Database mounted.

–=====开启归档模式
SQL> alter database archivelog;

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 72
Next log sequence to archive 74
Current log sequence 74

–=开启数据库=======
SQL> alter database open;

Database altered.

默认的归档日志存储路径为USE_DB_RECOVERY_FILE_DEST，也就是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 72
Next log sequence to archive 74
Current log sequence 74

SQL> show parameter db_recovery_file_dest;

NAME TYPE VALUE

---

db_recovery_file_dest string /app/oracle/fast_recovery_area
db_recovery_file_dest_size big integer 4977M

–=============对log日志进行手动切换，然后验证归档目录中日志。
SQL> alter system switch logfile;

System altered.

[oracle@oracle 2020_07_01]$ pwd
/app/oracle/fast_recovery_area/ORCL/archivelog/2020_07_01
[oracle@oracle 2020_07_01]$ ls
o1_mf_1_74_hhrrc21w_.arc

–====修改归档日志的路径=

–===1、DB_RECOVERY_FILE_DEST 和LOG_ARCHIVE_DEST、LOG_ARCHIVE_DUPLEX_DEST.不能同时用,修改了spfile中的log_archive_dest的路径，数据库无法启动，需要将log_archive_dest再次设置为空才可以启动。

SQL> alter system set log_archive_dest=‘location=/app/oracle/archivelog’;
alter system set log_archive_dest=‘location=/app/oracle/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

[oracle@oracle oracle]$ oerr ora 16018
16018, 00000, “cannot use %s with LOG_ARCHIVE_DEST_n or DB_RECOVERY_FILE_DEST”
// *Cause: One of the following events caused an incompatibility:
// 1) Parameter LOG_ARCHIVE_DEST or LOG_ARCHIVE_DUPLEX_DEST
// was in use when a LOG_ARCHIVE_DEST_n (n = 1…31) parameter
// was encountered while fetching initialization parameters.
// 2) An ALTER SYSTEM ARCHIVE LOG START TO command was in effect when
// a LOG_ARCHIVE_DEST_n parameter was encountered while fetching
// initialization parameters.
// 3) A LOG_ARCHIVE_DEST_n parameter was in use when an ALTER SYSTEM
// command was used to define a value for either the LOG_ARCHIVE_DEST
// or LOG_ARCHIVE_DUPLEX_DEST parameter.
// 4) Parameter DB_RECOVERY_FILE_DEST was in use when an attempt
// was made to use an ALTER SYSTEM or ALTER SESSION command to
// define a value for LOG_ARCHIVE_DEST or LOG_ARCHIVE_DUPLEX_DEST.
// *Action: Eliminate any incompatible parameter definitions.

SQL> alter system set log_archive_dest=‘location=/app/oracle/archivelog’ scope=spfile;

System altered.

SQL> shutdown immediate; //直接修改Spfile,所以进行了重启验证，但再次启动时却无法启动。

SQL> startup;
ORA-16032: parameter LOG_ARCHIVE_DEST destination string cannot be translated
ORA-07286: sksagdi: cannot obtain device information.
Linux-x86_64 Error: 2: No such file or directory
SQL> ! ls -ld /app/oracle/archivelog
drwxr-xr-x. 2 oracle oinstall 32 Jul 1 20:04 /app/oracle/archivelog

SQL> create pfile from spfile; //生成pfile将LOG_ARCHIVE_DEST设置为空

File created.

SQL> create spfile from pfile;

File created.

SQL> startup; //再次启动数据库可以启动
ORACLE instance started.

Total System Global Area 764121088 bytes
Fixed Size 2257152 bytes
Variable Size 503320320 bytes
Database Buffers 255852544 bytes
Redo Buffers 2691072 bytes
Database mounted.
Database opened.
SQL> archive log list;
Database log mode Archive Mode
Automatic archival Enabled
Archive destination USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence 73
Next log sequence to archive 75
Current log sequence 75

SQL> alter system switch logfile;

System altered.

[oracle@oracle archivelog]$ pwd
/app/oracle/archivelog
[oracle@oracle archivelog]$ ls

[oracle@oracle 2020_07_01]$ pwd
/app/oracle/fast_recovery_area/ORCL/archivelog/2020_07_01
[oracle@oracle 2020_07_01]$ ll
total 32524
-rw-r-----. 1 oracle oinstall 32974848 Jul 1 18:11 o1_mf_1_74_hhrrc21w_.arc
-rw-r-----. 1 oracle oinstall 326144 Jul 1 19:31 o1_mf_1_75_hhrwzv4j_.arc

–====修改log_archive_dest_n 归档路径，修改后db_recovery_file_dest则不起作用。=
SQL> alter system set log_archive_dest_1=‘location=/app/oracle/archivelog’;

System altered.

SQL> archive log list;
Database log mode Archive Mode
Automatic archival Enabled
Archive destination /app/oracle/archivelog
Oldest online log sequence 74
Next log sequence to archive 76
Current log sequence 76

SQL> alter system switch logfile;

System altered.

[oracle@oracle ~]$ cd /app/oracle/fast_recovery_area/ORCL/archivelog/2020_07_01
[oracle@oracle 2020_07_01]$ ll
total 32524
-rw-r-----. 1 oracle oinstall 32974848 Jul 1 18:11 o1_mf_1_74_hhrrc21w_.arc
-rw-r-----. 1 oracle oinstall 326144 Jul 1 19:31 o1_mf_1_75_hhrwzv4j_.arc
[oracle@oracle 2020_07_01]$ cd /app/oracle/archivelog
[oracle@oracle archivelog]$ ll
total 1372
-rw-r-----. 1 oracle oinstall 1404928 Jul 1 20:04 1_76_1044329988.dbf

–=将DB_RECOVERY_FILE_DEST 设置为空后，LOG_ARCHIVE_DEST可以设置。==========
测试的前提是先恢复数据库默认设置，将LOG_ARCHIVE_DEST_n设置为空，DB_RECOVERY_FILE_DEST为/app/oracle/fast_recovery_area ，然后再进行修改

SQL> alter system set db_recovery_file_dest=’’;

System altered.

SQL> archive log list;
Database log mode Archive Mode
Automatic archival Enabled
Archive destination /app/oracle/product/11.2.4/db_1/dbs/arch
Oldest online log sequence 75
Next log sequence to archive 77
Current log sequence 77

SQL> alter system set log_archive_dest=’/app/oracle/archivelog’;

System altered.

SQL> archive log list;
Database log mode Archive Mode
Automatic archival Enabled
Archive destination /app/oracle/archivelog
Oldest online log sequence 75
Next log sequence to archive 77
Current log sequence 77

SQL> alter system switch logfile;

System altered.
[oracle@oracle ~]$ ll /app/oracle/archivelog/
total 2620
-rw-r-----. 1 oracle oinstall 1404928 Jul 1 20:04 1_76_1044329988.dbf
-rw-r-----. 1 oracle oinstall 1274368 Jul 1 20:44 1_77_1044329988.dbf

结论：

1. 指定了db_recovery_file_dest快速恢复区作为归档路径，则不能使用LOG_ARCHIVE_DEST，不兼容；
   而db_recovery_file_dest和log_archive_dest_n兼容，且未指定log_archive_dest_n参数时归档默认指向db_recovery_file_dest快速恢复区，而当log_archive_dest_n参数设定值时则归档目录指定为log_archive_dest_n参数所指向的目录。

2. log_archive_dest参数：使用log_archive_dest参数最多可设置2个归档路径，通过log_archive_dest设置一个主归档路径，通过LOG_ARCHIVE_DUPLEX_DEST 参数设置一个从归档路径。所有的路径必须是本地的
   设置log_archive_dest参数时，db_recovery_file_dest和LOG_ARCHIVE_DEST_n都必须先设置为空。

3.LOG_ARCHIVE_DEST_n, LOG_ARCHIVE_DEST_n 参数可以设置最多10个不同的归档路径，通过设置关键词location或service，该参数指向的路径可以是本地或远程的。

4.可见log_archive_dest和LOG_ARCHIVE_DEST_n这两个参数都可以设置归档路径，不同的是后者可以设置远程归档到standby端，而前者只能归档到本地，且最多同时归档到2个路径下