63 lines
1.6 KiB
SQL
63 lines
1.6 KiB
SQL
|
|
/*
|
|
*
|
|
* Author : Vishal Gupta
|
|
* Purpose : Remove extra online redolog members.
|
|
* Version :
|
|
* Parameters : None
|
|
*
|
|
*
|
|
* Revision History:
|
|
* ===================
|
|
* Date Author Description
|
|
* --------- ------------ -----------------------------------------
|
|
* 17-DEC-15 Vishal Gupta Created
|
|
*
|
|
*
|
|
*/
|
|
|
|
set serveroutput on
|
|
BEGIN
|
|
-- Remove extra redo log member from non-current online redologs
|
|
FOR i in
|
|
(SELECT 'alter database drop logfile member '''
|
|
|| max(lf.member)
|
|
|| '''' sqltext
|
|
, l.status
|
|
FROM v$log l
|
|
JOIN v$logfile lf ON lf.group# = l.group#
|
|
WHERE l.members = 2
|
|
and l.status NOT IN ('CURRENT')
|
|
group by l.group#, l.status
|
|
ORDER BY l.group#
|
|
)
|
|
LOOP
|
|
dbms_output.put_line(i.sqltext || ';');
|
|
execute immediate i.sqltext;
|
|
END LOOP;
|
|
|
|
-- Switch current online redo and remove extra member
|
|
FOR i in
|
|
(SELECT 'alter database drop logfile member '''
|
|
|| max(lf.member)
|
|
|| '''' sqltext
|
|
, 'alter system archive log instance ''' || i.instance_name || ''' current' sqltext2
|
|
, l.status
|
|
FROM v$log l
|
|
JOIN v$logfile lf ON lf.group# = l.group#
|
|
JOIN gv$instance i ON i.inst_id = l.thread#
|
|
WHERE l.members = 2
|
|
and l.status = 'CURRENT'
|
|
group by l.group#, i.instance_name, l.status
|
|
ORDER BY l.group#, i.instance_name
|
|
)
|
|
LOOP
|
|
dbms_output.put_line(i.sqltext2 || ';');
|
|
execute immediate i.sqltext2;
|
|
dbms_output.put_line(i.sqltext || ';');
|
|
execute immediate i.sqltext;
|
|
END LOOP;
|
|
END;
|
|
/
|
|
|