预防headroom过小

1. 在使用DBLINK连接远程DB之前，确认是否安全，防止SCN大量跳跃，导致headroom过小。

点击(此处)折叠或打开

1. select
   
2. ((((
   
3. ((to_number(to_char(cur_date,'YYYY'))-1988)*12*31*24*60*60) +
   
4. ((to_number(to_char(cur_date,'MM'))-1)*31*24*60*60) +
   
5. (((to_number(to_char(cur_date,'DD'))-1))*24*60*60) +
   
6. (to_number(to_char(cur_date,'HH24'))*60*60) +
   
7. (to_number(to_char(cur_date,'MI'))*60) +
   
8. (to_number(to_char(cur_date,'SS')))
   
9. ) * (16*1024)) - &target_db_scn) --跳跃目标值的10进制，看看跳跃到这个数还能剩多少天
   
10. / (16*1024*60*60*24)
    
11. ) headroom
    
12. from (select sysdate cur_date from dual);

2.查询headroom

点击(此处)折叠或打开

1. SELECT SYSDATE sdate,
   
2.        'Headroom',
   
3.        ROUND ( (chk16kscn - gscn) / 3600 / 16 / 1024, 1) AS headroom_value
   
4.   FROM (SELECT tim,
   
5.                gscn,
   
6.                rate,
   
7.                delta,
   
8.                ( ( ( (TO_NUMBER (TO_CHAR (tim, 'YYYY')) - 1988)
   
9.                       * 12
   
10.                       * 31
    
11.                       * 24
    
12.                       * 60
    
13.                       * 60)
    
14.                    + ( (TO_NUMBER (TO_CHAR (tim, 'MM')) - 1)
    
15.                       * 31
    
16.                       * 24
    
17.                       * 60
    
18.                       * 60)
    
19.                    + ( ( (TO_NUMBER (TO_CHAR (tim, 'DD')) - 1))
    
20.                       * 24
    
21.                       * 60
    
22.                       * 60)
    
23.                    + (TO_NUMBER (TO_CHAR (tim, 'HH24')) * 60 * 60)
    
24.                    + (TO_NUMBER (TO_CHAR (tim, 'MI')) * 60)
    
25.                    + (TO_NUMBER (TO_CHAR (tim, 'SS'))))
    
26.                 * (16 * 1024))
    
27.                   chk16kscn
    
28.           FROM (SELECT FIRST_TIME tim,
    
29.                        (next_time - first_time) * 24 * 60 * 60 delta,
    
30.                        FIRST_CHANGE# gscn,
    
31.                        ( (NEXT_CHANGE# - FIRST_CHANGE#)
    
32.                         / ( (NEXT_TIME - FIRST_TIME) * 24 * 60 * 60))
    
33.                           rate
    
34.                   FROM v$archived_log
    
35.                    WHERE (next_time > first_time))
    
36.         ORDER BY 1 DESC)
    
37.  WHERE TO_CHAR (tim, 'YYYY/MM/DD HH24') >
    
38.               TO_CHAR (SYSDATE - 1, 'YYYY/MM/DD HH24')
    
39.        AND ROWNUM < 2

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/15412087/viewspace-2157232/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/15412087/viewspace-2157232/