Oracle 归档日志

开启、关闭归档

开启

1
2


关闭

1
2
3
4
5
6
-- 关闭数据库
SQL> shutdown immediate;
-- 开启数据库至mount状态
SQL> startup mount;
-- 修改数据库模式
SQL> alter datebase noarchivelog;

查询归档模式是否开启

1
2
3
4
5
6
SQL> archive log list;
Database log mode No Archive Mode
Automatic archival Disabled
Archive destination USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence 10107
Current log sequence 10109

问题

归档日志空间占满

ORA-00257: archiver error. Connect internal only, until freed

查看归档

1
2
SQL> select * from V$FLASH_RECOVERY_AREA_USAGE;
SQL> quit

调整归档日志大小

1
2
3
4
5
6
-- 查看归档日志信息
SQL> show parameter db_recovery;
-- 查看归档日志大小
SQL> show parameter db_recovery_file_dest_size;
-- 调整归档日志大小
SQL> alter system set db_recovery_file_dest_size=400g;

Select to_char(completion_time,’yyyy-mm-dd’) as date1,count(0) as cnt,round(sum((blocks*block_size)/1024/1024)) as mb from v$archived_log group by to_char(completion_time,’yyyy-mm-dd’) order by date1 desc;

–CNT为每天归档次数,MB为每天的归档量

/data/app/oracle/fast_recovery_area/ORCL/archivelog/

删除归档日志

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
$ rman
$ connect target sys/oracle

RMAN> crosscheck archivelog all;
RMAN> delete expired archivelog all;
-- 注:删除过期的归档
-- 这样就把归档文件删除了。再进入sqlplus 查看ARCHIVELOG日志使用率!

-- 以上处理方法是当遇到出现日志写满报错时的处理,建议最好做个任务,定时删除日志,如下:
-- 删除七天前的归档
RMAN> DELETE ARCHIVELOG ALL COMPLETED BEFORE 'SYSDATE-7';
-- 删除七天到现在的归档
RMAN> DELETE ARCHIVELOG FROM TIME 'SYSDATE-7';

DELETE ARCHIVELOG FROM TIME 'SYSDATE-1';

del_archivelog.sh

1
2
3
4
5
6
7
8
9
10
11
12
13
14
#!/bin/bash
echo "--------------------------`date`--------------------------"
source ~/.bash_profile
mkdir -p /data/del_archivelog
LOG_DIR=/data/del_archivelog/
DATEL=`date '+%Y-%m-%d'`
LOG_NAME=${LOG_DIR}${DATEL}".log"

rman log=$LOG_NAME target / <<EOF
crosscheck archivelog all;
delete noprompt archivelog all completed before 'sysdate-2';
EOF
echo -e "\n"
echo "--------------------------finished--------------------------"

每天6点执行脚本 del_archivelog.sh,并将执行结果输出到 task.log

1
0 6 * * * sh ~/del_archivelog.sh >> ~/del_archivelog/task.log 2>&1 &
  • 本文作者: forever杨
  • 本文链接: https://blog.yl-online.top/posts/e34c2a97.html
  • 版权声明: 本博客所有文章除特别声明外,均采用 BY-NC-SA 许可协议。如果文章内容对你有用,请记录到你的笔记中。本博客站点随时会停止服务,请不要收藏、转载!