- 论坛徽章:
- 0
|
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;
复制代码 www.linuxidc.com结果发现:- 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';
复制代码 |
|