AIX系统 -- 为Oracle扩大表空间
题记：今天做系统检查时，发现一套数据库中的一表空间使用率超过了90%，可见，如果不扩大这个表空间，那么数据库很快将被撑挂，并且顺便将本次的操作过程记录下来，分享给大家，谢谢！使用脚本检查：SELECT d.status "Status", d.tablespace_name "Name", &...显示全部
题记：今天做系统检查时，发现一套数据库中的一表空间使用率超过了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';收起
使用脚本检查：
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';收起