oracle归档命令整理

问题背景：

一般处理数据库问题中，归档问题遇到比较频繁，如归档满了是否可以清理，是否进行了备份，问题是否会复发，如何诊断

处理方案：

查看数据库备份情况，如有备份则可清理，若无备份可及时备份进行处理

col START_TIME for a30
col END_TIME for a30
col status for a10
select SESSION_KEY,INPUT_TYPE,STATUS,to_char(START_TIME, 'yyyy-mm-dd hh24:mi') START_TIME,to_char(END_TIME, 'yyyy-mm-dd hh24:mi') END_TIME,ELAPSED_SECONDS / 3600 from v$rman_backup_job_details where to_char(START_TIME, 'yyyy-mm-dd hh24:mi') > to_char(sysdate-12,'yyyy-mm-dd hh24:mi') order by SESSION_KEY;

查看归档保留情况，可确定大致归档的保留日期，可清理区间

col name for a80
set lines 200
select name,first_time from v$archived_log where name is  not null  and  name!='dbstd' order by first_time;

查看归档生成量，可用于诊断归档生成信息，是否存在激增情况，是否需要及时扩容，调整归档保留时间

select trunc(FIRST_TIME,'dd'),sum(BLOCKS*BLOCK_SIZE)/1024/1024/1024 from v$archived_log group by trunc(FIRST_TIME,'dd') order by 1;

rman清理命令（本地或asmcmd也可直接删除，不考虑备库是否应用）

指定天数清理归档

delete archivelog all completed before 'sysdate-1';

根据序列号删除

delete archivelog until sequence 9310 thread 1;

根据时间清理归档

delete noprompt archivelog until time "to_date('2023-05-23 18:00:00','YYYY-MM-DD hh24:mi:ss')";

强制参数删除归档

delete force noprompt archivelog until time "to_date('2023-05-23 23:12:22','yyyy-mm-dd hh24:mi:ss')";

清理完成后

crosscheck archivelog all;

若误删除归档，可查看归档号

select THREAD# ,sequence#,completion_time from v$archived_log

从备份恢复归档

rman target /

run

{

Allocate channel t1 type 'sbt_tape' parms 'ENV=(TDPO_OPTFILE=/xxx/tdpo.opt)';

restore archivelog sequence between XXX and xxx thread 1;

restore archivelog sequence between xxx and xxx thread 2;

Release channel t1;

}