Oracle RAC集群日常运维与管理

-- 检查Oracle RAC集群的健康状况
SELECT inst_id, instance_name, status, host_name
FROM gv$instance
ORDER BY inst_id;
 
-- 查看所有节点的数据库日志文件大小
SELECT node_name, name, bytes/1024/1024 AS size_mb
FROM gv$datafile
JOIN gv$asm_diskgroup USING (group_number)
WHERE type = 'DATAFILE'
ORDER BY node_name, size_mb DESC;
 
-- 查看集群中各节点的会话数和进程数
SELECT node_name, COUNT(*) AS sessions_count, COUNT(DISTINCT spid) AS processes_count
FROM gv$session
GROUP BY node_name
ORDER BY node_name;
 
-- 查看集群中各节点的表空间使用情况
SELECT node_name,
       tablespace_name,
       ROUND(SUM(bytes) / 1024 / 1024, 2) AS total_mb,
       ROUND(SUM(maxbytes) / 1024 / 1024, 2) AS max_mb,
       ROUND(SUM(bytes) / SUM(maxbytes) * 100, 2) AS usage_pct
FROM gv$datafile
JOIN gv$tablespace USING (ts#)
GROUP BY node_name, tablespace_name
ORDER BY node_name, usage_pct DESC;

这些SQL语句提供了检查Oracle RAC集群健康状况、查看数据文件大小、统计会话和进程数、以及表空间使用情况的有效方法。这些查询可以帮助数据库管理员及时发现潜在问题，并采取相应的维护和优化措施。