Oracle 归档相关知识

归档量查询

select trunc(completion_time) as "Data",
    (sum(blocks*block_size))/1024/1024 as "MB"
from v$archived_log
group by trunc(completion_time)
order by 1;

select trunc(completion_time) as "Data",
    (sum(blocks*block_size))/1024/1024/1024 as "GB"
from v$archived_log
group by trunc(completion_time)
order by 1;

v$archived_log

select thread#,sequence#,deleted from v$archived_log order by 2;

查看归档路径和时间

alter session set nls_date_format='yyyy-mm-dd hh24:mi:ss';
set linesize 200 pages 40
col name for a80
select name, FIRST_TIME  , NEXT_TIME , DELETED  from v$archived_log  where name is not null order by 2 desc, 3 desc;
select name, FIRST_TIME  , NEXT_TIME , DELETED  from v$archived_log  where name is not null order by 2 , 3 ;

归档路径更换

archive log list;
alter system set log_archive_dest_1='location=+ARCHDG';
alter system switch logfile;
archive log list;

注册归档

使用场景：asm里面有归档，但是数据库里面查不到

export ORACLE_SID=实例名
rman target /
catalog start with '+ASM1/xxx/xxx';

删除全部归档

ASM层面删除归档

su - grid
asmcmd 
cd 归档路径
rm -ef 2022_02_*

su - oracle
export ORACLE_SID=xxx
rman target /
crosscheck archivelog all;
delete noprompt expired archivelog all;

export ORACLE_SID=xxx
rman target /
delete noprompt archivelog all ;
delete force noprompt archivellog until time 'sysdate';

删除归档保留时间

delete force noprompt archivelog until time 'sysdate-1'; --保留1天

delete force noprompt archivelog until time 'sysdate-1/24'; --保留1个小时

delete force archivelog all completed before 'sysdate-2021/2/28/'; --不好使

delete force noprompt archivelog until time "to_date('2021-02-28 18:00:00','YYYY-MM-DD hh24:mi:ss')"; --2021年3月份之前的都不要了