查看归档目录位置
SQL> show parameters db_re
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_recovery_file_dest string D:\app\Administrator\fast_recovery_area
db_recovery_file_dest_size big integer 150G
db_recycle_cache_size big integer 0
SQL>
修改归档日志目录大小:
alter system set db_recovery_file_dest_size=200G scope=spfile;
查看归档目录使用率:PERCENT_SPACE_USED
set linesize 300
col FILE_TYPE for a40
col PERCENT_SPACE_USED for 99999
col PERCENT_SPACE_RECLAIMABLE for 99999
col NUMBER_OF_FILES for 99999
select * from v$flash_recovery_area_usage;
FILE_TYPE PERCENT_SPACE_USED PERCENT_SPACE_RECLAIMABLE NUMBER_OF_FILES
---------------------------------------- ------------------ ------------------------- ---------------
CONTROL FILE 0 0 0
REDO LOG 0 0 0
ARCHIVED LOG 13 0 431
BACKUP PIECE 0 0 0
IMAGE COPY 0 0 0
FLASHBACK LOG 0 0 0
FOREIGN ARCHIVED LOG 0 0 0
删除归档:用rman删除
rman target sys/oralce@orcl_his
crosscheck archivelog all;//验证
delete noprompt archivelog until time 'sysdate-30'; 删除30天之前的归档
delete noprompt archivelog until time 'sysdate'; 删除当前时刻之前的归档
delete noprompt archivelog until time 'sysdate-1/24'; 删除一个小时之前的归档
删除归档定时任务:
archivelog_delete.bat
D:\app\Administrator\product\11.2.0\dbhome_1\BIN\rman <<EOF
target sys/oralce@orcl_his
crosscheck archivelog all;
rem 删除一天之前的归档
delete noprompt archivelog until time 'sysdate-1;
exit;
EOF
delete noprompt archivelog until time 'sysdate'; 删除当前时刻之前的归档
delete noprompt archivelog until time 'sysdate-1/48'; 删除半小时之前的归档
delete noprompt archivelog until time 'sysdate-1'; 删除一天之前的归档