两键清理垃圾redo

清理无用的redo，用于恢复了adg环境后

set lin 200
col name for a60
col member for a50
set pages 200
select name from v$controlfile union all
select name from v$dbfile union all
select member from v$logfile;


set serverout on
declare
  -- 1 声明游标
  cursor cur_redo1 is
    select distinct a.group# from v$logFILE a where a.type='STANDBY';
  v_log_info cur_redo1%rowtype;
  v1 varchar2(100);
begin
  -- 2 开启游标
  open cur_redo1;

  --dbms_output.put_line('--------------');
  loop
  -- 3 获取数据（一次获取一行）
  fetch cur_redo1
    into v_log_info;
  exit when cur_redo1%notfound;

  for x in ( select member from v$logfile where group#=v_log_info.group#) loop
   dbms_output.put_line('rm -f '||x.member);
  end loop;


  execute immediate 'alter database drop standby logfile group '||v_log_info.group#;
  end loop;

  -- 4 关闭游标
  close cur_redo1;
end;
/


set serverout on

declare

  -- 1 声明游标

  cursor cur_redo is
--  set lin 200
--  col member for a50
    select a.group#,a.status, a.thread# from v$log a where a.status not in ('ACTIVE','CURRENT') order by group# desc;

  v_log_info cur_redo%rowtype;
begin
  -- 2 开启游标
  open cur_redo;

  dbms_output.put_line('--');
  loop
  -- 3 获取数据（一次获取一行）
  fetch cur_redo
    into v_log_info;
  exit when cur_redo%notfound;

  for x in ( select member from v$logfile where group#=v_log_info.group#) loop
   dbms_output.put_line('rm -f '||x.member);
  end loop;

  --dbms_output.put_line(v_log_info.group# || ' : rm -f ' || v_log_info.member);
  execute immediate 'alter database drop logfile group '||v_log_info.group#;
  end loop;

  -- 4 关闭游标
  close cur_redo;
end;
/



select 'alter tablespace '||t.name||' drop tempfile '''||c.name||''';'
from v$tempfile c, v$tablespace t where c.ts#=t.ts# and c.name not like '%t11_%';


select 'rm -f '||substr(name,1,instr(name,'/',-1) )||'Archi*/*.dbf' c from v$dbfile where rownum<2;
select 'create pfile='''||substr(name,1,instr(name,'/',-1) )||'init'||(select instance_name from v$instance)||'.ora'' from spfile;' c from v$dbfile where rownum<2;

舒爽了。