|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 |
<span class="kwd">SET</span><span class="pln"> FEEDBACK </span><span class="kwd">OFF</span><span class="pln"> </span><span class="kwd">SET</span><span class="pln"> SERVEROUTPUT </span><span class="kwd">ON</span><span class="pln"> </span><span class="kwd">DECLARE</span><span class="pln"> </span><span class="kwd">CURSOR</span><span class="pln"> cur_thread </span><span class="kwd">IS</span><span class="pln"> </span><span class="kwd">SELECT</span><span class="pln"> THREAD</span><span class="pun">#</span><span class="pln"> THREAD</span><span class="pun">,</span><span class="pln"> COUNT</span><span class="pun">(*)</span><span class="pln"> COUNT </span><span class="kwd">FROM</span><span class="pln"> V</span><span class="pun">$</span><span class="pln">LOG </span><span class="kwd">GROUP</span><span class="pln"> </span><span class="kwd">BY</span><span class="pln"> THREAD</span><span class="pun">#;</span><span class="pln"> standbylog_count NUMBER </span><span class="pun">:=</span><span class="pln"> </span><span class="lit">0</span><span class="pun">;</span><span class="pln"> standbylog_group NUMBER </span><span class="pun">:=</span><span class="pln"> </span><span class="lit">0</span><span class="pun">;</span><span class="pln"> standbylog_size NUMBER </span><span class="pun">:=</span><span class="pln"> </span><span class="lit">0</span><span class="pun">;</span><span class="pln"> last_group_number NUMBER</span><span class="pun">;</span><span class="pln"> sqlstmt VARCHAR2</span><span class="pun">(</span><span class="lit">200</span><span class="pun">);</span><span class="pln"> </span><span class="kwd">BEGIN</span><span class="pln"> </span><span class="kwd">SELECT</span><span class="pln"> MAX</span><span class="pun">(</span><span class="kwd">GROUP</span><span class="pun">#),</span><span class="pln"> MAX</span><span class="pun">(</span><span class="pln">BYTES</span><span class="pun">)</span><span class="pln"> </span><span class="kwd">INTO</span><span class="pln"> last_group_number</span><span class="pun">,</span><span class="pln"> standbylog_size </span><span class="kwd">FROM</span><span class="pln"> V</span><span class="pun">$</span><span class="pln">LOG</span><span class="pun">;</span><span class="pln"> standbylog_group </span><span class="pun">:=</span><span class="pln"> last_group_number </span><span class="pun">+</span><span class="pln"> </span><span class="lit">1</span><span class="pun">;</span><span class="pln"> </span><span class="kwd">FOR</span><span class="pln"> rec_thread </span><span class="kwd">IN</span><span class="pln"> cur_thread LOOP standbylog_count </span><span class="pun">:=</span><span class="pln"> rec_thread</span><span class="pun">.</span><span class="pln">count </span><span class="pun">+</span><span class="pln"> </span><span class="lit">1</span><span class="pun">;</span><span class="pln"> </span><span class="kwd">FOR</span><span class="pln"> i </span><span class="kwd">IN</span><span class="pln"> </span><span class="lit">1.</span><span class="pun">.</span><span class="pln">standbylog_count LOOP sqlstmt </span><span class="pun">:=</span><span class="pln"> </span><span class="str">'ALTER DATABASE ADD STANDBY LOGFILE THREAD '</span><span class="pln"> </span><span class="pun">||</span><span class="pln"> rec_thread</span><span class="pun">.</span><span class="pln">thread </span><span class="pun">||</span><span class="pln"> </span><span class="str">' GROUP '</span><span class="pln"> </span><span class="pun">||</span><span class="pln"> TO_CHAR</span><span class="pun">(</span><span class="pln">standbylog_group</span><span class="pun">)</span><span class="pln"> </span><span class="pun">||</span><span class="pln"> </span><span class="str">' SIZE '</span><span class="pln"> </span><span class="pun">||</span><span class="pln"> standbylog_size </span><span class="pun">||</span><span class="pln"> </span><span class="str">';'</span><span class="pun">;</span><span class="pln"> DBMS_OUTPUT</span><span class="pun">.</span><span class="pln">PUT_LINE</span><span class="pun">(</span><span class="pln"> sqlstmt</span><span class="pun">);</span><span class="pln"> standbylog_group </span><span class="pun">:=</span><span class="pln"> standbylog_group </span><span class="pun">+</span><span class="pln"> </span><span class="lit">1</span><span class="pun">;</span><span class="pln"> </span><span class="kwd">END</span><span class="pln"> LOOP</span><span class="pun">;</span><span class="pln"> </span><span class="kwd">END</span><span class="pln"> LOOP</span><span class="pun">;</span><span class="pln"> </span><span class="kwd">END</span><span class="pun">;</span><span class="pln"> </span><span class="pun">/</span><span class="pln"> </span><span class="kwd">SET</span><span class="pln"> FEEDBACK </span><span class="kwd">ON</span><span class="pln"> -----------------------------------------------</span> |
・REDO確認
set lines 120
set pages 100
set term off
clear col
col GROUP# format 90
col STATUS format a7
col TYPE format a6
col MEMBER format a50
select
group#,
status,
type,
member
from
v$logfile
order by
group#,member
/
select
group#,
members,
bytes/(1024*1024)
from v$log
order by
group#,members
/
|
1 |
<span class="pln"></span> |