90 lines
2.4 KiB
Plaintext
90 lines
2.4 KiB
Plaintext
|
|
|
|
----------------------------------------
|
|
-- Add logfile members
|
|
----------------------------------------
|
|
set serveroutput on size unlimited
|
|
BEGIN
|
|
FOR i in (
|
|
SELECT 'alter database add logfile member ''+DATA_DM02_EXT'' TO GROUP ' || l.group# sqltext
|
|
, l.status
|
|
FROM v$log l
|
|
JOIN v$logfile lf ON lf.group# = l.group#
|
|
WHERE l.members = 1
|
|
group by l.group#, l.status
|
|
ORDER BY l.group#
|
|
)
|
|
LOOP
|
|
dbms_output.put_line(i.sqltext || ';');
|
|
-- execute immediate i.sqltext;
|
|
END LOOP;
|
|
END;
|
|
/
|
|
|
|
|
|
|
|
--------------------------------------------------
|
|
-- Switch logfiles for newly added log members
|
|
--------------------------------------------------
|
|
set serveroutput on size unlimited
|
|
BEGIN
|
|
FOR i in (
|
|
SELECT 'alter system archive log instance ''' || i.instance_name || ''' current' sqltext
|
|
, lf.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 lf.status = 'INVALID'
|
|
ORDER BY l.group#, i.instance_name
|
|
)
|
|
LOOP
|
|
dbms_output.put_line(i.sqltext || ';');
|
|
-- execute immediate i.sqltext;
|
|
END LOOP;
|
|
END;
|
|
/
|
|
|
|
|
|
|
|
--------------------------------------------------
|
|
-- Drop logfiles
|
|
--------------------------------------------------
|
|
set serveroutput on size unlimited
|
|
BEGIN
|
|
FOR i in (
|
|
SELECT 'alter database drop logfile member ''' || 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')
|
|
and lf.member like '+RECO_DM02_EXT%'
|
|
ORDER BY l.group#
|
|
)
|
|
LOOP
|
|
dbms_output.put_line(i.sqltext || ';');
|
|
-- execute immediate i.sqltext;
|
|
END LOOP;
|
|
|
|
FOR i in (
|
|
SELECT 'alter database drop logfile member ''' || 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 lf.member like '+RECO_DM02_EXT%'
|
|
and l.status = 'CURRENT'
|
|
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;
|
|
/
|
|
|