查看是否归档
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;
![oracle归档设置 - herb - herb oracle归档设置 - herb - herb](http://img0.ph.126.net/stMWtGMDPPI9mEW1D3abFw==/2652901655515293850.jpg)
查看归档日志数量
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;