Oracle可以将联机日志文件保存到多个不同的位置，将联机日志转换为归档日志的过程称之为归档。相应的日志被称为归档日志。
（1）非归档模式和归档模式的切换方法：
1.非归档到归档模式
a.一致性关闭数据库(shutdown [immediate | transactional |normal])
b.启动到mount阶段(startup mount)
c.切换到归档模式(alter database archivelog [manual])
d.切换到open阶段(alter database open)
e.对数据做一个完整备份(full backup)
2.归档模式切换到非归档模式
a.一致性关闭数据库(shutdown [immediate | transactional |normal])
b.启动到mount阶段(startup mount)
c.切换到归档模式(alter database noarchivelog )
d.切换到open阶段(alter database open)
e.对数据做一个完整备份(full backup)（2）归档进程的调整
通过设置LOG_ARCHIVE_MAX_PROCESSES 参数，可以指定数据库规定所需初始化的进程数，缺省的归档进程数为2，一般情况下不需要修改该参数，Oracle会根据归档情况自动启动额外的归档进程。也可以动态的增加或减少归档进程数，比如有些业务在月底需要进行大量归档，平时则归档较少，对此可以动态修改，修改方法如下：
ALTER SYSTEM SET LOG_ARCHIVE_MAX_PROCESSES=3;（3）归档位置的配置方法
在ORACLE10g和11g版本，ORACLE默认的日志归档路径为闪回恢复区（$ORACLE_BASE/flash_recovery_area）。对于这个路径，ORACLE有一个限制，就是默认只有2G的空间，而且不只是归档日志的默认路径，也是备份文件和闪回日志的默认地址，这样的话归档日志所使用的空间就达不到2G，在没有设置好这个路径大小的情况下，很多系统都遇到过归档日志满而无法归档导致数据库夯住的问题，可以使用下面的SQL语句去查看归档信息。
SQL> ARCHIVE LOG LIST; --查看日志是否处于归档状态
Database log mode Archive Mode
Automatic archival Enabled
Archive destination USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence 24
Next log sequence to archive 26
Current log sequence 26查看归档路径是否已经设置
查看归档路径是否已设置，为空值表示未设置。
SHOW PARAMETER ARCHIVE
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
archive_lag_target integer 0
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设置归档路径位置的配置方法
a.归档到本机且小于等于两个归档位置
设置LOG_ARCHIVE_DEST和LOG_ARCHIVE_DUPLEX_DEST参数
alter system set log_archive_dest = '/u01/app/oracle/archivelog1' scope = spfile;
alter system set log_archive_duplex_dest = '/u01/app/oracle/archivelog2' scope = spfile;
b.归档到本机或远程主机
设置LOG_ARCHIVE_DEST_n 参数
alter system set log_archive_dest_1 = 'LOCATION=/u01/app/oracle/archivelog3';
alter system set log_archive_dest_2 = 'SERVICE=standby1';
对于远程归档位置,SERVICE选项需要指定远程数据库的网络服务名(在tnsnames.ora文件中配置)
LOG_ARCHIVE_DEST_n的两个常用选项
MANADATORY | OPTIONAL:
MANADATORY表示归档必须成功复制到目的路径之后，联机重做日志才能被复用，OPTIONAL正好相反
REOPEN：
指定归档失败后指定间隔多少时间重试归档，缺省的为300秒。
示例：
log_archive_dest_1 = 'LOCATION=/u01/app/oracle/archivelog3 manadatory reopen = 600'
c.两种归档方法异同(前者log_archive_dest、log_archive_duplex_dest ，后者log_archive_dest_n)
前者最多配置两个归档位置，后者可以配置多个归档位置；
两者互不兼容，要么使用前者，要么使用后者；
使用后者归档到本地时需要增加LOCATION选项，备份到远程主机需要使用SERVICE选项；
但上述两种方式未指定路径时，归档日志位于db_recovery_file_dest 缺省为$ORACLE_BASE/flash_recovey_area
d.归档日志命名格式
设置LOG_ARCHIVE_FORMAT参数，Unix下的缺省设置为LOG_ARCHIVE_FORMAT=%t_%s_%r.dbf
%s: 日志序列号:
%S: 日志序列号(带有前导)
%t: 重做线程编号.
%T: 重做线程编号(带有前导)
%a: 活动ID号
%d: 数据库ID号
%r RESETLOGS的ID值.
alter system set log_archive_format = 'arch_%t_%s_%r.arc';
e.控制归档的可用性(使用参数log_archive_dest_state_n)
alter system set log_archive_dest_state_1 = enable | defer
enable -->缺省状态，可以在该归档位置进行归档
defer -->禁用该归档位置归档路径配置及查看归档进程，归档位置状态案例如下：
查看日志是否处于归档状态
SQL> ARCHIVE LOG LIST;
Database log mode Archive Mode
Automatic archival Enabled
Archive destination USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence 24
Next log sequence to archive 26
Current log sequence 26
查看归档进程是否已启动
SQL> ho ps -ef | grep ora_arc
oracle 4062 1 0 11:43 ? 00:00:00 ora_arc0_orcl
oracle 4064 1 0 11:43 ? 00:00:00 ora_arc1_orcl
查看归档路径是否已设置，为空值表示未设置
SQL> SHOW PARAMETER ARCHIVE
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
archive_lag_target integer 0
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
设置归档路径（2个）
SQL> alter system set log_archive_dest = '/u01/app/oracle/archivelog1' scope = spfile;
System altered.
SQL> alter system set log_archive_duplex_dest= '/u01/app/oracle/archivelog2' scope=spfile;
System altered.
SQL> alter system set log_archive_format = 'arch_%t_%s_%r.arc' scope = spfile;
静态参数，需要重新启动数据库才生效，设定完毕后需要重新启动实例。
启动实例后，手动切换一个日志，查看归档日志的信息
alter system switch logfile;
在指定的文件夹archivelog1查看产生的归档日志
SQL> ho ls -l /u01/app/oracle/archivelog1
total 18224
-rw------- 1 oracle oinstall 18636800 Jul 25 20:39 arch_1_25_724652763.arc
创建新的归档路径
SQL> ho mkdir /u01/app/oracle/archivelog3
重新配置归档路径的位置
SQL> alter system set log_archive_dest_1 = 'location=/u01/app/oracle/archivelog3' scope = spfile;
关闭实例
SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
启动实例后，收到错误提示表明两种归档方式不兼容
SQL> startup
ORA-16019: cannot use LOG_ARCHIVE_DEST_1 with LOG_ARCHIVE_DEST or LOG_ARCHIVE_DUPLEX_DEST
正确的处理办法，应当先将一种归档方式路径置空，此处是将log_archive_dest置空
SQL> alter system set log_archive_dest = '' scope = spfile;
System altered.
此处是将log_archive_duplex_dest置空
SQL> alter system set log_archive_duplex_dest = '' scope = spfile;
System altered.
接下来再设定log_archive_dest_1
SQL> alter system set log_archive_dest_1 = 'location=/u01/app/oracle/archivelog3' scope = spfile;
System altered.
设定完毕后需要重新启动实例，此时系统能正常启动。
查看归档目的地的相关状态信息
set line 300
col dest_name format a20
col destination format a30
select dest_name,status,archiver,destination,log_sequence,reopen_secs,transmit_mode,process from v$archive_dest where status='VALID';
归档日志相关视图
v$archived_log -->从控制文件中获得归档的相关信息
v$archive_dest -->归档路径及状态
v$log_history -->控制文件中日志的历史信息
v$database -->查看数据库是否处于归档状态
v$archive_processes -->归档相关的后台进程信息
set line 500
col name format a80
Select name,sequence#,registrar,standby_dest,archived,status from v$archived_log;（4）查看每天归档日志的大小和个数等情况
set line 200 pagesize 9999 long 5000
column thread# format 999999 heading 'Thread#'
column day format a10 heading 'Day/Time'
column H00 format 999 heading '00'
column H01 format 999 heading '01'
column H02 format 999 heading '02'
column H03 format 999 heading '03'
column H04 format 999 heading '04'
column H05 format 999 heading '05'
column H06 format 999 heading '06'
column H07 format 999 heading '07'
column H08 format 999 heading '08'
column H09 format 999 heading '09'
column H10 format 999 heading '10'
column H11 format 999 heading '11'
column H12 format 999 heading '12'
column H13 format 999 heading '13'
column H14 format 999 heading '14'
column H15 format 999 heading '15'
column H16 format 999 heading '16'
column H17 format 999 heading '17'
column H18 format 999 heading '18'
column H19 format 999 heading '19'
column H20 format 999 heading '20'
column H21 format 999 heading '21'
column H22 format 999 heading '22'
column H23 format 999 heading '23'
column total format 999,999 heading 'Total'
select thread#,to_char (first_time, 'YYYY-MM-DD') day,
sum (decode (to_char (first_time, 'HH24'), '00', 1, 0)) H00,
sum (decode (to_char (first_time, 'HH24'), '01', 1, 0)) H01,
sum (decode (to_char (first_time, 'HH24'), '02', 1, 0)) H02,
sum (decode (to_char (first_time, 'HH24'), '03', 1, 0)) H03,
sum (decode (to_char (first_time, 'HH24'), '04', 1, 0)) H04,
sum (decode (to_char (first_time, 'HH24'), '05', 1, 0)) H05,
sum (decode (to_char (first_time, 'HH24'), '06', 1, 0)) H06,
sum (decode (to_char (first_time, 'HH24'), '07', 1, 0)) H07,
sum (decode (to_char (first_time, 'HH24'), '08', 1, 0)) H08,
sum (decode (to_char (first_time, 'HH24'), '09', 1, 0)) H09,
sum (decode (to_char (first_time, 'HH24'), '10', 1, 0)) H10,
sum (decode (to_char (first_time, 'HH24'), '11', 1, 0)) H11,
sum (decode (to_char (first_time, 'HH24'), '12', 1, 0)) H12,
sum (decode (to_char (first_time, 'HH24'), '13', 1, 0)) H13,
sum (decode (to_char (first_time, 'HH24'), '14', 1, 0)) H14,
sum (decode (to_char (first_time, 'HH24'), '15', 1, 0)) H15,
sum (decode (to_char (first_time, 'HH24'), '16', 1, 0)) H16,
sum (decode (to_char (first_time, 'HH24'), '17', 1, 0)) H17,
sum (decode (to_char (first_time, 'HH24'), '18', 1, 0)) H18,
sum (decode (to_char (first_time, 'HH24'), '19', 1, 0)) H19,
sum (decode (to_char (first_time, 'HH24'), '20', 1, 0)) H20,
sum (decode (to_char (first_time, 'HH24'), '21', 1, 0)) H21,
sum (decode (to_char (first_time, 'HH24'), '22', 1, 0)) H22,
sum (decode (to_char (first_time, 'HH24'), '23', 1, 0)) H23,
count (*) total
from v$log_history a
where first_time >= sysdate - 30
group by thread#,to_char (first_time, 'YYYY-MM-DD')
order by thread#,to_char (first_time, 'YYYY-MM-DD');（5）查看备份集中的日志文件信息
select bs.recid bs_key,
decode (backup_type,'L', 'Archived Redo Logs','D', 'Datafile Full Backup','I', 'Incremental Backup') backup_type,
device_type,
decode (bs.controlfile_included, 'NO', '-', bs.controlfile_included) controlfile_included,
nvl (sp.spfile_included, '-') spfile_included,
bs.incremental_level,
bs.pieces,
to_char (bs.start_time, 'YYYY-MM-DD HH24:MI:SS') start_time,
to_char (bs.completion_time, 'YYYY-MM-DD HH24:MI:SS') completion_time,
bs.elapsed_seconds,
bp.tag,
bs.block_size,
bs.keep,
to_char (bs.keep_until, 'YYYY-MM-DD HH24:MI:SS') keep_until,
bs.keep_options
from v$backup_set bs,
(select distinct set_stamp,
set_count,
tag,
device_type
from v$backup_piece
where status in ('A', 'X')) bp,
(select distinct set_stamp, set_count, 'YES' spfile_included
from v$backup_spfile) sp
where bs.set_stamp = bp.set_stamp
and bs.set_count = bp.set_count
and bs.set_stamp = sp.set_stamp(+)
and bs.set_count = sp.set_count(+)
and sysdate - bs.start_time <= 30
order by bs.recid;（6）归档日志的删除
查看归档日志列表
list archivelog all;
列出所有失效的归档日志
list expired archivelog all;
校验日志的可用性
crosscheck archivelog all;
删除log sequence20480之前的所有归档日志
delete noprompt archivelog until sequence 20480;
删除一天前的所有归档日志文件
delete noprompt archivelog all completed before 'sysdate-2';
删除系统时间7天以前的归档日志
delete noprompt archivelog all completed before 'sysdate-7';
删除所有的归档日志
delete noprompt archivelog all;（7）data guard环境下归档日志删除可能出现的问题和解决方法
如果是dg 主备，在主库上定期删除归档会报如下错误：
RMAN-08120: WARNING: archived log not deleted, not yet applied by standby
第一，查看主库和备库现在应用日志的情况，得到应用的日志序列号
查看主库应用日志信息：
SYS@testdb> select GROUP# ,THREAD# , SEQUENCE# from v$log;
GROUP# THREAD# SEQUENCE#
---------- ---------- ----------
1 1 20507
2 1 20508
3 1 20509
5 1 20510
6 1 20511
查看备库的应用情况：
select THREAD# , SEQUENCE#,APPLIED from v$archived_log;
第二，修改主库的rman配置参数：
默认的参数如下：
CONFIGURE ARCHIVELOG DELETION POLICY TO APPLIED ON ALL STANDBY;
修改为：
CONFIGURE ARCHIVELOG DELETION POLICY TO NONE;
第三：执行删除归档文件命令：
校验日志的可用性
crosscheck archivelog all;
删除log sequence33535之前的所有归档日志；
序列号可以由上面查询到的数据减去8到10个左右
delete noprompt archivelog until sequence 33535;
如果加上force参数，不需要前面的第一二步骤的设置即可执行删除归档的操作。
delete force noprompt archivelog until sequence 33535;
或者指定线程号进行归档日志的删除
delete force noprompt archivelog until sequence 33535thread 1;
本文章为转载内容，我们尊重原作者对文章享有的著作权。如有内容错误或侵权问题，欢迎原作者联系我们进行内容更正或删除文章。
