Oracle表空间扩容步骤（RAC)

问题处理思路：

手机接到表空间不足的短信。首先登录服务确认短信真实性。

1、查看表空间使用率

 SET LINESIZE 500

SET PAGESIZE 1000
col FREE_SPACE(M) for 999999999
col USED_SPACE(M) for 999999999
col TABLESPACE_NAME for a15
SELECT D.TABLESPACE_NAME,SPACE "SUM_SPACE(M)",SPACE - NVL(FREE_SPACE, 0) "USED_SPACE(M)",
ROUND((1 - NVL(FREE_SPACE, 0) / SPACE) * 100, 2) "USED_RATE(%)", FREE_SPACE "FREE_SPACE(M)",
case when FREE_SPACE=REA_FREE_SPACE then null else ROUND((1 - NVL(REA_FREE_SPACE, 0) / SPACE) * 100, 2) end "REA_USED_RATE(%)",
case when FREE_SPACE=REA_FREE_SPACE then null else REA_FREE_SPACE end "REA_FREE_SPACE(M)"
FROM
(SELECT TABLESPACE_NAME, ROUND(SUM(BYTES) / (1024 * 1024), 2) SPACE
FROM DBA_DATA_FILES GROUP BY TABLESPACE_NAME) D,
( SELECT F1.TABLESPACE_NAME, F1.FREE_SPACE-NVL(F2.FREE_SPACE,0) REA_FREE_SPACE,F1.FREE_SPACE
FROM
(SELECT TABLESPACE_NAME, ROUND(SUM(BYTES) / (1024 * 1024), 2) FREE_SPACE
FROM DBA_FREE_SPACE GROUP BY TABLESPACE_NAME
) F1,
(SELECT TS_NAME TABLESPACE_NAME, ROUND(SUM(SPACE)*8/1024,2) FREE_SPACE
FROM DBA_RECYCLEBIN GROUP BY TS_NAME
) F2
WHERE F1.TABLESPACE_NAME=F2.TABLESPACE_NAME(+)
) F
WHERE D.TABLESPACE_NAME = F.TABLESPACE_NAME(+)
ORDER BY ROUND((1 - NVL(FREE_SPACE, 0) / SPACE) * 100, 2) DESC;

发现表空间使用率96.3%，剩余空间8G，情况属实。

2、查看磁盘组大小和数据文件位置
su - grid
asmcmd
lsdg

剩余500G，可以加数据文件

--查询数据文件位置
select file_name,tablespace_name,bytes from dba_data_files;

按照扩容标准依次添加数据文件即可

增加数据文件 alter tablespace ZJGL add datafile '+RAC_DATA/BFSDB/DATAFILE/zjgl06.dbf' size 31G;
增加数据文件 alter tablespace ZJGL add datafile '+RAC_DATA/BFSDB/DATAFILE/zjgl07.dbf' size 31G;

添加成功，表空间使用率下降到75%。

ORACLE DATA GUARD 扩容

增加数据文件 alter tablespace USERS add datafile '/u01/app/oracle/oradata/CCN/users06.dbf' size 31G;