Oracle 归档路径设置

这是我参与8月更文挑战的第8天，活动详情查看：8月更文挑战

Oracle 归档日志的默认存储路径为闪回区，默认大小为2G，可利用闪回区做归档日志的冗余。

前提

Oracle 数据库已处于归档模式。

使用闪回区需先设置其大小和路径

alter system set db_recovery_file_dest_size=10G scope=both;
alter system set db_recovery_file_dest='/archivelog';

设置归档路径和闪回区同时保留归档日志

alter system set log_archive_dest_1='location=/arch';
alter system set log_archive_dest_10='LOCATION=USE_DB_RECOVERY_FILE_DEST';

设置归档路径保留归档日志，闪回区不保留

alter system set log_archive_dest_1='location=/arch';
alter system set log_archive_dest_10='';

设置归档路径不保留归档日志，闪回区保留

alter system set log_archive_dest_1='';
alter system set log_archive_dest_10='LOCATION=USE_DB_RECOVERY_FILE_DEST';

不可设置归档路径和闪回区都不保留归档日志

alter system set log_archive_dest_1='';
alter system set log_archive_dest_10='';

试验机测试结果为切切几次归档后，卡住切不了归档：

SQL> alter system switch logfile;
System altered.
SQL> /
System altered.
SQL> /
日志报错为：
ALTER SYSTEM SET log_archive_dest_1='' SCOPE=BOTH;
ALTER SYSTEM SET log_archive_dest_10='' SCOPE=BOTH;
Fri Nov 09 07:45:54 2018
Thread 1 advanced to log sequence 20 (LGWR switch)
  Current log# 4 seq# 20 mem# 0: /u01/app/oracle/oradata/syd/redo04.log
Fri Nov 09 07:45:54 2018
ARCH: Archival stopped, error occurred. Will continue retrying
ORACLE Instance syd - Archival Error
ORA-16014: log 3 sequence# 19 not archived, no available destinations
ORA-00312: online log 3 thread 1: '/u01/app/oracle/oradata/syd/redo03.log'
Thread 1 advanced to log sequence 21 (LGWR switch)
  Current log# 1 seq# 21 mem# 0: /u01/app/oracle/oradata/syd/redo01.log
Fri Nov 09 07:46:02 2018
Archiver process freed from errors. No longer stopped
Fri Nov 09 07:46:34 2018
Thread 1 cannot allocate new log, sequence 22
Checkpoint not complete
  Current log# 1 seq# 21 mem# 0: /u01/app/oracle/oradata/syd/redo01.log
Thread 1 advanced to log sequence 22 (LGWR switch)
  Current log# 2 seq# 22 mem# 0: /u01/app/oracle/oradata/syd/redo02.log
Fri Nov 09 07:46:44 2018
ORACLE Instance syd - Cannot allocate log, archival required
Thread 1 cannot allocate new log, sequence 23
All online logs need archiving
Examine archive trace files for archiving errors
  Current log# 2 seq# 22 mem# 0: /u01/app/oracle/oradata/syd/redo02.log
Fri Nov 09 07:51:11 2018
ARCH: Archival stopped, error occurred. Will continue retrying
ORACLE Instance syd - Archival Error
ORA-16014: log 3 sequence# 19 not archived, no available destinations
ORA-00312: online log 3 thread 1: '/u01/app/oracle/oradata/syd/redo03.log'
重启实例后恢复正常，此时归档被改回保留在闪回区：
SQL> shu immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup
ORACLE instance started.
Total System Global Area 1068937216 bytes
Fixed Size                  2260088 bytes
Variable Size             884999048 bytes
Database Buffers          176160768 bytes
Redo Buffers                5517312 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     19
Next log sequence to archive   22
Current log sequence           22
SQL> alter system switch logfile;
System altered.
SQL> /
System altered.
SQL> /
System altered.
SQL> /
System altered.