清理无用的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;
舒爽了。