Oracle 归档日志

开启、关闭归档

开启

-- 关闭数据库
shutdown immediate;
-- 打开数据库
startup mount;
-- 开启归档日志
alter database archivelog;
-- 开启数据库
alter database open;
-- 查看归档日志是否开启
archive log list;

关闭

-- 关闭数据库
SQL> shutdown immediate；
-- 开启数据库至mount状态
SQL> startup mount;
-- 修改数据库模式
SQL> alter datebase noarchivelog;

查询归档模式是否开启

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

查看归档

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

查询归档日志文件大小

Select to_char(completion_time,'yyyy-mm-dd') as date1,
    -- CNT 为每天归档次数
    count(0) as cnt,
    -- MB 为每天的归档量
    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;

调整归档日志大小

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

一般日志文件路径：/data/app/oracle/fast_recovery_area/ORCL/archivelog/

删除归档日志

$ 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

#!/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

0 6 * * * sh ~/del_archivelog.sh >> ~/del_archivelog/task.log 2>&1 &