开启/关闭归档，增加归档空间删除归档（归档满了）
1. 开启归档
先关闭数据库 shutdown immediate
开启到挂载状态 startup mount
改到归档模式 alter database archivelog;
查看结果 archive log list
启动数据库 alter database open 启动数据库（此处startup open不行）
2.关闭归档
1、登录数据库：执行alter system set cluster_database = false scope=spfile;
2、关闭node1和node2数据库：shutdown immediate;
3、启动node1至mount状态：startup mount;
4、执行关闭归档：alter database noarchivelog;
5、开启数据库：alter database open;
6、在node1执行：alter system set cluster_database=true scope=spfile;
7、关闭数据库：shutdown immediate;
8、启动node1数据库：startup
9、启动node2数据库：startup
3. 增加归档日志空间
1、查看归档情况
sql>archive log list;
SYS@DBDJ1> set linesize 1000; SYS@DBDJ1> set pagesize 1000; SYS@DBDJ1> show parameter db_recovery_file_dest; NAME TYPE VALUE ------------------------------------ ---------------------- ------------------------------ db_recovery_file_dest string /u01/app/oracle/fast_recovery_ area db_recovery_file_dest_size big integer 4122M
2. 增加到50G
SQL>ALTER SYSTEM SET db_recovery_file_dest_size=50g scope=both;
3. 重启数据库
SQL>shutdown immediate;
SQL>startup
4. 查看测试结果
SQL>show parameter db_recovery_file_dest --50G SQL>sqlplus zz_**/password@10.126.29.241/jw
4.定时删除归档日志
建议使用RMAN提供的命令来搞定比较妥当。因为rm，find方式删除了实际的归档日志也释放了空间，但对应的存储在控制文件中的归档信息并没有彻底清除。
1、清除归档日志的方式
a、手动删除
使用rm 或者find方式来删除，通过该方式删除之后，在RMAN下可以通过 crosscheck archivelog all 校验归档是否失效，如下面的操作:
rm -rf arch_816906485_1_10.arc
find /u02/database/GOBO1/archive/ -ctime +0 -delete
RMAN> crosscheck archivelog all
RMAN清除方式会自动清除磁盘上的归档日志文件，同时会释放控制文件中对应的归档日志的归档信息。
可以基于不同的条件来清除归档日志，如基于SCN，基于SEQUENCE，基于TIME等方式。
对于上述的三种方式又可以配合from, until, between .. and .. 等等子句来限定范围，方式灵活多变。
下面的命令用于校验归档日志的有效性，列出无效的归档日志，以及以何种方式清除归档日志，列出几种常用的:
crosscheck archivelog all; --->校验日志的可用性
list expired archivelog all; --->列出所有失效的归档日志
delete archivelog until sequence 16; --->删除log sequence为16及16之前的所有归档日志
delete archivelog all completed before 'sysdate-7'; --->删除系统时间7天以前的归档日志，不会删除闪回区有效的归档日志
delete archivelog all completed before 'sysdate - 1'; --->同上，1天以前的
delete archivelog from time 'sysdate-1'; --->注意这个命令，删除系统时间1天以内到现在的归档日志
delete noprompt archivelog all completed before 'sysdate'; --->该命令清除所有的归档日志
delete noprompt archivelog all; --->同上一命令
2、演练使用RMAN清除归档日志
robin@SZDB:~> export ORACLE_SID=GOBO1 robin@SZDB:~> rman target / Recovery Manager: Release 10.2.0.3.0 - Production on Thu Jul 11 17:07:00 2013 Copyright (c) 1982, 2005, Oracle. All rights reserved. connected to target database: GOBO1 (DBID=733951103) RMAN> host; robin@SZDB:~> cd /u02/database/GOBO1/archive/ robin@SZDB:/u02/database/GOBO1/archive> ls arch_816906485_1_10.arc arch_816906485_1_12.arc arch_816906485_1_11.arc arch_816906485_1_13.arc ............ robin@SZDB:/u02/database/GOBO1/archive> rm -rf arch_816906485_1_10.arc arch_816906485_1_11.arc arch_816906485_1_12.arc robin@SZDB:/u02/database/GOBO1/archive> exit; exit host command complete RMAN> crosscheck archivelog all; released channel: ORA_DISK_1 allocated channel: ORA_DISK_1 channel ORA_DISK_1: sid=1075 devtype=DISK archive log filename=/u02/database/GOBO1/archive/arch_816906485_1_9.arc recid=2085 stamp=817211151 validation failed for archived log archive log filename=/u02/database/GOBO1/archive/arch_816906485_1_10.arc recid=2086 stamp=817250793 .............. validation succeeded for archived log archive log filename=/u02/database/GOBO1/archive/arch_816906485_1_90.arc recid=2166 stamp=820458049 Crosschecked 83 objects RMAN> list expired archivelog all; List of Archived Log Copies Key Thrd Seq S Low Time Name ------- ---- ------- - ----------------- ---- 2086 1 10 X 20130604 11:05:51 /u02/database/GOBO1/archive/arch_816906485_1_10.arc 2087 1 11 X 20130604 22:06:17 /u02/database/GOBO1/archive/arch_816906485_1_11.arc 2088 1 12 X 20130605 19:30:53 /u02/database/GOBO1/archive/arch_816906485_1_12.arc RMAN> delete archivelog until sequence 16; released channel: ORA_DISK_1 allocated channel: ORA_DISK_1 channel ORA_DISK_1: sid=1075 devtype=DISK List of Archived Log Copies Key Thrd Seq S Low Time Name ------- ---- ------- - ----------------- ---- 2084 1 8 A 20130604 09:53:17 /u02/database/GOBO1/archive/arch_816906485_1_8.arc ................. 2092 1 16 A 20130607 22:03:23 /u02/database/GOBO1/archive/arch_816906485_1_16.arc Do you really want to delete the above objects (enter YES or NO)? yes ............... deleted archive log archive log filename=/u02/database/GOBO1/archive/arch_816906485_1_16.arc recid=2092 stamp=817516861 Deleted 9 objects RMAN> delete archivelog all completed before 'sysdate-7'; released channel: ORA_DISK_1 allocated channel: ORA_DISK_1 channel ORA_DISK_1: sid=1075 devtype=DISK List of Archived Log Copies Key Thrd Seq S Low Time Name ------- ---- ------- - ----------------- ---- 2093 1 17 A 20130608 00:01:00 /u02/database/GOBO1/archive/arch_816906485_1_17.arc 2094 1 18 A 20130608 18:00:17 /u02/database/GOBO1/archive/arch_816906485_1_18.arc ........... deleted archive log archive log filename=/u02/database/GOBO1/archive/arch_816906485_1_72.arc recid=2148 stamp=819847035 Deleted 56 objects RMAN> list copy of database archivelog all; List of Archived Log Copies Key Thrd Seq S Low Time Name ------- ---- ------- - ----------------- ---- 2149 1 73 A 20130703 23:17:13 /u02/database/GOBO1/archive/arch_816906485_1_73.arc 2150 1 74 A 20130704 22:00:19 /u02/database/GOBO1/archive/arch_816906485_1_74.arc 2151 1 75 A 20130704 22:04:40 /u02/database/GOBO1/archive/arch_816906485_1_75.arc ............... 2164 1 88 A 20130709 23:19:34 /u02/database/GOBO1/archive/arch_816906485_1_88.arc 2165 1 89 A 20130710 13:00:34 /u02/database/GOBO1/archive/arch_816906485_1_89.arc 2166 1 90 A 20130710 22:02:44 /u02/database/GOBO1/archive/arch_816906485_1_90.arc RMAN> delete archivelog from time 'sysdate-1'; released channel: ORA_DISK_1 allocated channel: ORA_DISK_1 channel ORA_DISK_1: sid=1075 devtype=DISK List of Archived Log Copies Key Thrd Seq S Low Time Name ------- ---- ------- - ----------------- ---- 2165 1 89 A 20130710 13:00:34 /u02/database/GOBO1/archive/arch_816906485_1_89.arc 2166 1 90 A 20130710 22:02:44 /u02/database/GOBO1/archive/arch_816906485_1_90.arc Do you really want to delete the above objects (enter YES or NO)? yes deleted archive log archive log filename=/u02/database/GOBO1/archive/arch_816906485_1_89.arc recid=2165 stamp=820447373 deleted archive log archive log filename=/u02/database/GOBO1/archive/arch_816906485_1_90.arc recid=2166 stamp=820458049 Deleted 2 objects RMAN> delete archivelog all completed before 'sysdate - 1'; released channel: ORA_DISK_1 allocated channel: ORA_DISK_1 channel ORA_DISK_1: sid=1075 devtype=DISK List of Archived Log Copies Key Thrd Seq S Low Time Name ------- ---- ------- - ----------------- ---- 2149 1 73 A 20130703 23:17:13 /u02/database/GOBO1/archive/arch_816906485_1_73.arc ....................... 2164 1 88 A 20130709 23:19:34 /u02/database/GOBO1/archive/arch_816906485_1_88.arc Do you really want to delete the above objects (enter YES or NO)? yes ................ archive log filename=/u02/database/GOBO1/archive/arch_816906485_1_88.arc recid=2164 stamp=820414835 Deleted 16 objects RMAN> sql " alter system archive log current"; sql statement: alter system archive log current RMAN> list copy of archivelog all; List of Archived Log Copies Key Thrd Seq S Low Time Name ------- ---- ------- - ----------------- ---- 2167 1 91 A 20130711 01:00:48 /u02/database/GOBO1/archive/arch_816906485_1_91.arc RMAN> delete noprompt archivelog all completed before 'sysdate'; released channel: ORA_DISK_1 allocated channel: ORA_DISK_1 channel ORA_DISK_1: sid=1075 devtype=DISK List of Archived Log Copies Key Thrd Seq S Low Time Name ------- ---- ------- - ----------------- ---- 2167 1 91 A 20130711 01:00:48 /u02/database/GOBO1/archive/arch_816906485_1_91.arc deleted archive log archive log filename=/u02/database/GOBO1/archive/arch_816906485_1_91.arc recid=2167 stamp=820517964 Deleted 1 objects
3、清除归档日志简单的shell脚本
1 #对于RAC环境或者ASM需要清除archive，使用shell脚本调用RMAN是比较妥当的方式 2 #其次，如果你的archive位于闪回区，制定合理的保留策略，也可以让Oracle自动老化无用的归档日志 3 robin@SZDB:~/dba_scripts/custom/bin> more clean_arch.sh 4 # +-------------------------------------------------------+ 5 # + Clean archived log as specified time | 6 # + Author : Robinson | 7 # + Blog : http://blog.csdn.net/robinson_0612 | 8 # + Usage : | 9 # + clean_arch.sh $ORACLE_SID | 10 # +-------------------------------------------------------+ 11 # 12 #!/bin/bash 13 # -------------------- 14 # Define variable 15 # -------------------- 16 17 if [ -f ~/.bash_profile ]; then 18 . ~/.bash_profile 19 fi 20 21 if [ -z "${1}" ];then 22 echo "Usage: " 23 echo " `basename $0` ORACLE_SID" 24 exit 1 25 fi 26 27 ORACLE_SID=$1; export ORACLE_SID 28 $ORACLE_HOME/bin/rman log=/users/robin/log/rman.log <<EOF 29 connect target / 30 run{ 31 crosscheck archivelog all; 32 delete noprompt expired archivelog all; 33 delete noprompt archivelog all completed before 'sysdate - 1'; 34 } 35 exit; 36 EOF 37 exit 38
4、小结
a、归档日志清除的方法最好是在RMAN方式下完成，这样子是最彻底的清除方式
b、对于生产环境应考虑在RMAN备份的时候清除归档日志，如backup archivelog all时使用delete input与delete all input清除归档日志
c、如果备份期间不清除归档日志则arch会很大，造成归档磁盘满而导致归档失败。建议还是删除或考虑存放到闪回区
d、如果清除大部分又想保留最近的，则使用delete noprompt archivelog all completed before 'sysdate - n'方式
e、详细的清除归档日志语法: http://docs.oracle.com/cd/B19306_01/backup.102/b14194/rcmsynta008.htm#RCMRF106
