AIX系统 -- 为Oracle扩大表空间

MyOracleFei 2011-10-25

题记:今天做系统检查时,发现一套数据库中的一表空间使用率超过了90%,可见,如果不扩大这个表空间,那么数据库很快将被撑挂,并且顺便将本次的操作过程记录下来,分享给大家,谢谢!

使用脚本检查:

SELECT d.status "Status",
       d.tablespace_name "Name",
       d.contents "Type",
       d.extent_management "Extent Management",
       to_char(nvl(a.bytes / 1024 / 1024, 0), '99999999.999') "Total Size (M)",
       to_char(nvl(a.bytes - nvl(f.bytes, 0), 0) / 1024 / 1024,
               '99999999.999') "Used (M)",
       to_char(nvl(nvl(f.bytes, 0), 0) / 1024 / 1024, '99999999.999') "Free (M)",
       to_char(nvl((a.bytes - nvl(f.bytes, 0)) / a.bytes * 100, 0),
               '990.00') "Used %"
  FROM sys.dba_tablespaces d,
       (SELECT tablespace_name, SUM(bytes) bytes
          FROM dba_data_files
         GROUP BY tablespace_name) a,
       (SELECT tablespace_name, SUM(bytes) bytes
          FROM dba_free_space
         GROUP BY tablespace_name) f
 WHERE d.tablespace_name = a.tablespace_name(+)
   AND d.tablespace_name = f.tablespace_name(+)
   AND NOT
        (d.extent_management LIKE 'LOCAL' AND d.contents LIKE 'TEMPORARY')
UNION ALL
SELECT d.status "Status",
       d.tablespace_name "Name",
       d.contents "Type",
       d.extent_management "Extent Management",
       to_char(nvl(a.bytes / 1024 / 1024, 0), '99999990.900') "Size (M)",
       to_char(nvl(t.bytes, 0) / 1024 / 1024, '99999999.999') "Used (M)",
       to_char((nvl(a.bytes / 1024 / 1024, 0)) -
               (nvl(t.bytes, 0) / 1024 / 1024),
               '99999999.999') "Free (M)",
       to_char(nvl(t.bytes / a.bytes * 100, 0), '990.00') "Used %"
  FROM sys.dba_tablespaces d,
       (SELECT tablespace_name, SUM(bytes) bytes
          FROM dba_temp_files
         GROUP BY tablespace_name) a,
       (SELECT tablespace_name, SUM(bytes_cached) bytes
          FROM v$temp_extent_pool
         GROUP BY tablespace_name) t
 WHERE d.tablespace_name = a.tablespace_name(+)
   AND d.tablespace_name = t.tablespace_name(+)
   AND d.extent_management LIKE 'LOCAL'
   AND d.contents LIKE 'TEMPORARY'
 ORDER BY "Used %" DESC;


SQL> select file_name,tablespace_name,bytes/1024/1024 from dba_data_files where tablespace_name='BILLING_DATA2';

FILE_NAME                                TABLESPACE_NAME                BYTES/1024/1024
---------------------------------------- ------------------------------ ---------------
/dev/rlvsm_data2                         BILLING_DATA2                            20000
/dev/rlvsm_data3                         BILLING_DATA2                            24500


确定lvsm_data2、lvsm_data3属于哪一个VG:
GD_HYWG_cManager2_A:/>lslv -L lvsm_data2
LOGICAL VOLUME:     lvsm_data2             VOLUME GROUP:   datavg
LV IDENTIFIER:      00062d670000d6000000011aaec5d738.40 PERMISSION:     read/write
VG STATE:           active/complete        LV STATE:       opened/syncd
TYPE:               raw                    WRITE VERIFY:   off
MAX LPs:            512                    PP SIZE:        128 megabyte(s)
COPIES:             1                      SCHED POLICY:   parallel
LPs:                240                    PPs:            240
STALE PPs:          0                      BB POLICY:      relocatable
INTER-POLICY:       minimum                RELOCATABLE:    yes
INTRA-POLICY:       middle                 UPPER BOUND:    1024
MOUNT POINT:        N/A                    LABEL:          None
MIRROR WRITE CONSISTENCY: on/ACTIVE                             
EACH LP COPY ON A SEPARATE PV ?: yes                                   
Serialize IO ?:     NO                                    
DEVICESUBTYPE : DS_LVZ
                                       
GD_HYWG_cManager2_A:/>lslv -L lvsm_data3
LOGICAL VOLUME:     lvsm_data3             VOLUME GROUP:   datavg
LV IDENTIFIER:      00062d670000d6000000011aaec5d738.45 PERMISSION:     read/write
VG STATE:           active/complete        LV STATE:       opened/syncd
TYPE:               raw                    WRITE VERIFY:   off
MAX LPs:            512                    PP SIZE:        128 megabyte(s)
COPIES:             1                      SCHED POLICY:   parallel
LPs:                200                    PPs:            200
STALE PPs:          0                      BB POLICY:      relocatable
INTER-POLICY:       minimum                RELOCATABLE:    yes
INTRA-POLICY:       middle                 UPPER BOUND:    1024
MOUNT POINT:        N/A                    LABEL:          None
MIRROR WRITE CONSISTENCY: on/ACTIVE                             
EACH LP COPY ON A SEPARATE PV ?: yes                                   
Serialize IO ?:     NO                                    
DEVICESUBTYPE : DS_LVZ
由上面可见,lvsm_data2、lvsm_data3这两个LV均属于datavg:
GD_HYWG_cManager2_A:/dev>lsvg -l datavg | grep lvsm_data
lvsm_data1          raw        240     240     1    open/syncd    N/A
lvsm_data2          raw        240     240     1    open/syncd    N/A
lvsm_data3          raw        200     200     1    open/syncd    N/A

那么接下来就确认datavg是否还有剩余可用空间:
GD_HYWG_cManager2_A:/dev>lspv
hdisk0          0001e6b91e911b61                    rootvg          active
hdisk1          0001f369e182ea0e                    rootvg          active
hdisk2          00062d67aec5d1eb                    datavg          active
hdisk3          00062d67aec5d3bb                    datavg          active
hdisk4          0001e6b99995a385                    billingbakvg    active
hdisk5          0001e6b99995b755                    billingarchvg   active
hdisk6          0001e6b9020606ed                    billingvg       active

GD_HYWG_cManager2_A:/dev>lsvg datavg
VOLUME GROUP:       datavg                   VG IDENTIFIER:  00062d670000d6000000011aaec5d738
VG STATE:           active                   PP SIZE:        128 megabyte(s)
VG PERMISSION:      read/write               TOTAL PPs:      7806 (999168 megabytes)
MAX LVs:            256                      FREE PPs:       740 (94720 megabytes)
LVs:                45                       USED PPs:       7066 (904448 megabytes)
OPEN LVs:           41                       QUORUM:         2 (Enabled)
TOTAL PVs:          2                        VG DESCRIPTORS: 3
STALE PVs:          0                        STALE PPs:      0
ACTIVE PVs:         2                        AUTO ON:        no
MAX PPs per VG:     32768                    MAX PVs:        1024
LTG size (Dynamic): 1024 kilobyte(s)         AUTO SYNC:      no
HOT SPARE:          no                       BB POLICY:      relocatable
由上面可知,datavg还有740个FREE PPs,于是我们可以从这个VG中划分出几个LV,用于扩大数据库的表空间。

相关推荐