rman delete for Archiver error
set oracle_sid=eg03prd rman target / crosscheck archivelog all; list expired archivelog all; delete noprompt expired archivelog all; RMAN> report obsolete; RMAN> delete obsolete; RMAN> crosscheck archivelog all; DELETE force archivelog ALL completed before 'SYSDATE-1'; DELETE noprompt ARCHIVELOG ALL COMPLETED BEFORE 'SYSDATE-1/24'; list archivelog all completed between '2015-08-13' and '2015-08-13 18:00:00'; list archivelog time between '2015-08-13' and '2015-08-13 18:00:00'; delete archivelog time between "2016-01-15 14:00:00" and "2016-01-15 15:00:00"; configure configuration policy to redundancy 1; configure retention policy to recovery window of 7 days; 保存7天 如果设置了这个 就可以将归档删除策略设置为 configure archivelog deletion policy to backed;
查询Flash Recovery Area和归档信息
SQL> show parameter db_recovery_file_dest NAME VALUE ---------------------- -------------------------- db_recovery_file_dest E:\oracle\product\10.2.0\flash_recovery_area db_recovery_file_dest_size 2GSQL> archive log list; Database log mode Archive Mode Automatic archival Enabled Archive destination USE_DB_RECOVERY_FILE_DEST Oldest online log sequence 174 Next log sequence to archive 176 Current log sequence 176 |
查询FRA剩余空间
SQL>select file_type, percent_space_used as used,percent_space_reclaimable as reclaimable, number_of_files as "number" from v$flash_recovery_area_usage; FILE_TYPE USED RECLAIMABLE number ------------ ---------- ----------- ---------- CONTROLFILE 0 0 0 ONLINELOG 0 0 0 ARCHIVELOG 89.94 0 53 BACKUPPIECE 9.51 0 11 IMAGECOPY 0 0 0 FLASHBACKLOG 0 0 0 |
修改db_recovery_file_dest_size,打开数据库
SQL>Alter system set db_recovery_file_dest_size=40G scope=both;
System altered.
SQL> alter database open;
Database altered.
[root@node1 archivelog]# find ./ -mtime +1| xargs rm -rf
[root@node1 archivelog]# ll
五、补充说明
1.使用os命令删除fast_recovery_area内容后，需要使用crosscheck检测(如:archivelog all,backup等)。
2.然后使用 DELETE EXPIRED命令删除(archivelog all,backup等)
3.fast_recovery_area设置合适大小+合适的策略
4.resetlogs打开数据库后，做好备份
5.fast_recovery_area无剩余空间处理思路
5.1)如果数据库不能登录：重启至mount，增大fast_recovery_area，open数据库，然后使用rman删除历史垃圾数据(备份集,日志,闪回日志等)
5.2)如果数据库可以使用sys登录，增大fast_recovery_area(使其数据库可以正常工作)，然后使用rman处理垃圾数据
1 sql> archive log list; #查看是不是归档方式
2 sql> alter system set log_archive_start=true scope=spfile; #启用主动归档
sql> alter system set log_archive_dest=''location=/oracle/ora9/oradata/arch'' scope=spfile;
#设置归档路径
sql> alter system set log_archive_dest_1=''location=/oracle/ora9/oradata/arch1'' scope=spfile;
sql> alter system set log_archive_dest_2=''location=/oracle/ora9/oradata/arch2'' scope=spfile;
#如果归档到两个位置，则可以通过上边方法实现
sql> alter system set log_archive_format=''arch_%d_%t_%r_%s.log'' #设置归档日记款式
3 sql> shutdown immediate;
4 sql> startup mount; #打开控制文件，不打开数据文件
5 sql> alter database archivelog; #将数据库切换为归档模式
6 sql> alter database open; #将数据文件打开
7 sql> archive log list; #查看此时是否处于归档模式
8 查询以确定数据库位于archivelog模式中且归档过程正在运行
sql> select log_mode from v$database;
sql> select archiver from v$instance;
9 日志切换
sql> alter system switch logfile;
10 这次日志切换将归档写到两个目标地,
1，即第二步的/oracle/ora9/oradata/arch1和/oracle/ora9/oradata/arch1，要是要对目录确认
在oracle情况中运行如下查询：
sql> select name from v$archived_log;
而后在操作系统中确认查询所列出的文件
1 sql> archive log list; #查看是否是归档方式
2 sql> alter system set log_archive_start=false scope=spfile; #禁用自动归档
3 sql> shutdown immediate;
4 sql> startup mount; #打开控制文件，不打开数据文件
5 sql> alter database noarchivelog; #将数据库切换为非归档模式
6 sql> alter database open; #将数据文件打开
7 sql> archive log list; #查看此时便处于非归档模式
对于上述的三种方式又可以配合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; --->同上一命令
3、清除归档日志简单的shell脚本
[python]
#对于RAC环境或者ASM需要清除archive，使用shell脚本调用RMAN是比较妥当的方式
#其次，如果你的archive位于闪回区，制定合理的保留策略，也可以让Oracle自动老化无用的归档日志
robin@SZDB:~/dba_scripts/custom/bin> more clean_arch.sh
# +-------------------------------------------------------+
# + Clean archived log as specified time |
# + Author : Robinson |
# + Blog : http://blog.csdn.net/robinson_0612 |
# + Usage : |
# + clean_arch.sh $ORACLE_SID |
# +-------------------------------------------------------+
#
#!/bin/bash
# --------------------
# Define variable
# --------------------
if [ -f ~/.bash_profile ]; then
. ~/.bash_profile
fi
if [ -z "${1}" ];then
echo "Usage: "
echo " `basename $0` ORACLE_SID"
exit 1
fi
ORACLE_SID=$1; export ORACLE_SID
$ORACLE_HOME/bin/rman log=/users/robin/log/rman.log <<EOF
connect target /
run{
crosscheck archivelog all;
delete noprompt expired archivelog all;
delete noprompt archivelog all completed before 'sysdate - 1';
}
exit;
EOF
exit
All for u
