oracle归档设置

查看是否归档
SQL> archive log list ;
Database log mode        No Archive Mode
Automatic archival        Disabled
Archive destination        USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence     165
Current log sequence        167

SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
不存在初始化文件pfile，可以根据spfile生成的初始化文件创建他
create pfile='/opt/oracle/product/10.2.1/db_1/dbs/initquanmai.ora' from spfile='/opt/oracle/product/10.2.1/db_1/dbs/spfilequanmai.ora'

编辑初始化文件：
[oracle@oracle oracle]$ vi /opt/oracle/product/10.2.1/db_1/dbs/initquanmai.ora
LOG_ARCHIVE_FORMAT='log%t_%s_%r.arc'
log_archive_start=true
（添加log_archive_start=true，oracle会自己启动归档进程）

重建spfile文件
create spfile='/opt/oracle/product/10.2.1/db_1/dbs/spfile_xin_quanmai.ora' from pfile='/opt/oracle/product/10.2.1/db_1/dbs/initquanmai.ora'

设置归档路径
[oracle@oracle oracle]$ mkdir arch2
SQL> alter system set LOG_ARCHIVE_DEST_1='LOCATION=/opt/oracle/arch1' SCOPE=both;
SQL> alter system set LOG_ARCHIVE_DEST_2='LOCATION=/opt/oracle/arch2' SCOPE=both;
SQL> alter database archivelog; （切换到归档模式）
（查看是否归档模式运行）
SQL> archive log list;     
Database log mode        Archive Mode
Automatic archival        Enabled
Archive destination        /opt/oracle/arch2
Oldest online log sequence     165
Next log sequence to archive   167
Current log sequence        167
或者
SQL> select name,log_mode from v$database;   

NAME   LOG_MODE
--------- ------------
QUANMAI   ARCHIVELOG  
（若LOG_MODE=ARCHIVELOG  则在说明运行在归档模式）

SQL> show parameter log_archive_dest;  （归档模式是否设置正确）
NAME         TYPE  VALUE
------------------------------------ ----------- ------------------------------
log_archive_dest       string
log_archive_dest_1       string  LOCATION=/opt/oracle/arch1
log_archive_dest_10       string
log_archive_dest_2       string  LOCATION=/opt/oracle/arch2
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> alter database open

手动启动归档进程：
alter system archive log start;
查看启动的归档进程：
SQL> select * from v$bgprocess where paddr<>'00' and name like '%ARC%';

定时查出2天前的归档日志
find /opt/oracle/arch1 ! -mtime -2 -exec rm -f '{}' ';'

修改归档路径
SQL> alter system set LOG_ARCHIVE_DEST_2='LOCATION=/mnt/arch3' SCOPE=both;

手工关闭归档进程：
alter system archive log stop;

查看数据库中有哪些归档日志文件：
select Name,Sequence#,RESETLOGS_TIME from v$archived_Log Order By Sequence# Desc

查看进程：
SQL> select * from v$bgprocess where paddr<>'00' and name like '%ARC%';

PADDR    PSERIAL# NAME
-------- ---------- -----
DESCRIPTION             ERROR
---------------------------------------------------------------- ----------
5262360C   1 ARC0
Archival Process 0       ##########

52623BC8   1 ARC1
Archival Process 1       ##########

[oracle@oracle dbs]$ ps aux|grep arc         
oracle    8450  0.0  0.9 943140 27244 ?        Ss   17:25   0:00 ora_arc0_quanmai
oracle    8452  0.0  0.9 943140 27184 ?        Ss   17:25   0:00 ora_arc1_quanmai
oracle    8492  0.0  0.0   1844   488 pts/0    S+   17:32   0:00 grep arc

归档路径查看：(状态)
a.SQL> archive log list ;
b.SQL> show parameter log_archive_dest;
c.SQL> select dest_name,destination,status from v$archive_dest;

 
 查看归档日志数量
Select count(*) from v$archived_log where deleted<>'YES';

删除归档日志文件
[oracle@oracle ~]$ rman
Recovery Manager: Release 10.2.0.4.0 - Production on Fri Mar 30 09:13:39 2012

Copyright (c) 1982, 2007, Oracle.  All rights reserved.

RMAN> connect target sys
target database Password:
connected to target database: QUANMAI (DBID=796606958)
using target database control file instead of recovery catalog

RMAN> run {allocate channel t1 type disk; delete force noprompt archivelog all; release channel t1;}

allocated channel: t1
channel t1: sid=159 devtype=DISK

List of Archived Log Copies
Key     Thrd Seq     S Low Time  Name
------- ---- ------- - --------- ----
2       1    167     A 29-MAR-12 /opt/oracle/arch2/1_167_777890481.dbf
1       1    167     A 29-MAR-12 /opt/oracle/arch1/1_167_777890481.dbf
deleted archive log
archive log filename=/opt/oracle/arch2/1_167_777890481.dbf recid=2 stamp=779241614
deleted archive log
archive log filename=/opt/oracle/arch1/1_167_777890481.dbf recid=1 stamp=779241614
Deleted 2 objects
released channel: t1

删除2009年9月15号23:12:22' 的归档日志
RMAN> run {allocate channel t1 type disk; delete force noprompt archivelog until time "to_date('2009-9-15 23:12:22' , 'yyyy-mm-dd hh24:mi:ss')";release channel t1;}

没有设置归档路径，oracle默认会把归档路径放到闪回去
闪回区路径查询：
SQL> show parameter db_recovery_file_dest
查看闪回区使用情况
SQL> select * from v$flash_recovery_area_usage;
若归档日志没有设置
1.增加闪回去的空间
SQL> alter system set db_recovery_file_dest_size = 1G scope=both SID='*';
2.编辑初始化文件参数，增加归档日志路径，然后重启oracle
[oracle@oracle oracle]$ vi /opt/oracle/product/10.2.1/db_1/dbs/initquanmai.ora
LOG_ARCHIVE_DEST_1='LOCATION=/mnt/arch4'
shutdown immediate
 startup file='/opt/oracle/product/10.2.1/db_1/dbs/initquanmai.ora'

查看最少成功归档路径的数量：
SQL> show parameter log_archive_min_succeed_dest
NAME         TYPE  VALUE
------------------------------------ ----------- ------------------------------
log_archive_min_succeed_dest      integer  1

开启归档日志
shutdown immediate;
startup mount;
alter database archivelog;
alter database open;

关闭归档日志
shutdown immediate;
startup mount;
alter datebase noarchivelog;
alter datebase open;