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

题记：今天做系统检查时，发现一套数据库中的一表空间使用率超过了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;

结果发现：

Status    Name                           Type      Extent Man Total Size (M Used (M)      Free (M)      Used %

--------- ------------------------------ --------- ---------- ------------- ------------- ------------- -------

ONLINE    BILLING_DATA2                  PERMANENT LOCAL          44500.000     41558.480       2941.520  93.39

 

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，用于扩大数据库的表空间。

小技巧：

我们知道，在创建LV时，都是以PP为单位进行分配的，这样会给很初学者带来不便，比如我要划分一个20G的LV，那么该给多少个PP呢？

以这里的datavg为例：

首先：lsvg datavg --> PP的大小为128M，那么20G需要的PP数为：20*1024/128=160。

GD_HYWG_cManager2_A:/dev>mklv -t jfs2 -y ora10g_data3 datavg 160  -->20G

GD_HYWG_cManager2_A:/dev>mklv -t jfs2 -y ora10g_data4 datavg 240  -->30G

GD_HYWG_cManager2_A:/dev>mklv -t jfs2 -y ora10g_data5 datavg 240  -->30G

修改权限：

GD_HYWG_cManager2_A:/dev>chown oracle:dba rora10g_data[3-5]

GD_HYWG_cManager2_A:/dev>chown oracle:dba ora10g_data[3-5]

 

$ sqlplus / as sysdba

SQL*Plus: Release 10.2.0.1.0 - Production on Mon Oct 24 15:50:48 2011

Copyright (c) 1982, 2005, Oracle.  All rights reserved.

Connected to:

Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - 64bit Production

With the Partitioning, OLAP and Data Mining options

SQL> alter tablespace BILLING_DATA2  add datafile '/dev/rora10g_data3' size  20470M autoextend off;

SQL> alter tablespace BILLING_DATA2  add datafile '/dev/rora10g_data4' size  30710M autoextend off;

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

 

转载：http://www.2cto.com/database/201110/108854.html