SQL> SELECT name, state, type, total_mb, free_mb FROM v$asm_diskgroup;NAME STATE TYPE TOTAL_MB FREE_MB
—————————— ———– —— ———- ———-
DATA_DG MOUNTED NORMAL 40960 35840
LOG_DG MOUNTED NORMAL 20480 20480
ARCH_DG MOUNTED EXTERN 61440 61440

SQL> SELECT group_number, disk_number, mount_status, header_status, state, total_mb, free_mb, name FROM v$asm_disk;GROUP_NUMBER DISK_NUMBER MOUNT_S HEADER_STATU STATE TOTAL_MB FREE_MB NAME
———— ———– ——- ———— ——– ———- ———- ——————————
1 0 CACHED MEMBER NORMAL 20480 17920 DGDATA_0000
1 1 CACHED MEMBER NORMAL 20480 17920 DGDATA_0001
2 0 CACHED MEMBER NORMAL 10240 10240 LOG_DG_0000
2 1 CACHED MEMBER NORMAL 10240 10240 LOG_DG_0001
3 0 CACHED MEMBER NORMAL 30720 30720 ARCH_DG_0000
3 1 CACHED MEMBER NORMAL 30720 30720 ARCH_DG_0001

— 1. 设置ASM实例参数
ALTER SYSTEM SET asm_power_limit = 10 SCOPE=BOTH;ALTER SYSTEM SET asm_diskstring = ‘/dev/sd*’ SCOPE=SPFILE;– 2. 调整磁盘组属性
ALTER DISKGROUP data_dg SET ATTRIBUTE ‘au_size’ = ‘4M’;ALTER DISKGROUP data_dg SET ATTRIBUTE ‘compatible.asm’ = ‘19.0.0’;ALTER DISKGROUP data_dg SET ATTRIBUTE ‘compatible.rdbms’ = ‘19.0.0’;– 3. 优化重平衡功率
ALTER DISKGROUP data_dg REBALANCE POWER 11;– 4. 调整ASM后台进程
ALTER SYSTEM SET asm_bg_processes = 4 SCOPE=SPFILE;– 5. 配置ASM PGA和SGA
ALTER SYSTEM SET sga_target = 2G SCOPE=SPFILE;ALTER SYSTEM SET pga_aggregate_target = 1G SCOPE=SPFILE;– 6. 优化磁盘组条带化
CREATE DISKGROUP data_dg NORMAL REDUNDANCY
FAILGROUP fg1 DISK ‘/dev/sdb1’, ‘/dev/sdc1’
FAILGROUP fg2 DISK ‘/dev/sdd1’, ‘/dev/sde1’
ATTRIBUTE ‘au_size’ = ‘4M’, ‘compatible.asm’ = ‘19.0.0’, ‘compatible.rdbms’ = ‘19.0.0’;– 7. 分离不同类型的文件到不同磁盘组
CREATE TABLESPACE user_data
DATAFILE ‘+dgdata/fgedudb/datafile/user_data01.dbf’ SIZE 1000M;ALTER DATABASE ADD LOGFILE
GROUP 4 (‘+log_dg/fgedudb/onlinelog/redo04a.log’, ‘+log_dg/fgedudb/onlinelog/redo04b.log’) SIZE 100M;ALTER SYSTEM SET db_recovery_file_dest = ‘+arch_dg’ SCOPE=SPFILE;

— 1. 增加磁盘数量，提高I/O并行度
ALTER DISKGROUP data_dg ADD DISK ‘/dev/sdf1’, ‘/dev/sdg1’;– 2. 调整重平衡功率，优化重平衡性能
ALTER DISKGROUP data_dg REBALANCE POWER 11;– 3. 监控重平衡状态
SELECT group_number, operation, state, power, sofar, est_work, est_rate, est_minutes FROM v$asm_operation;– 4. 优化磁盘组布局，确保负载均衡
ALTER DISKGROUP data_dg ADD FAILGROUP fg3 DISK ‘/dev/sdh1’, ‘/dev/sdi1’;– 5. 清理不必要的文件，释放空间
DELETE ARCHIVELOG ALL COMPLETED BEFORE ‘SYSDATE-7’;– 6. 备份ASM配置，确保故障恢复
ALTER DISKGROUP data_dg BACKUP DISK TO ‘/backup/asm_config.bkp’;– 7. 使用ASMCMD工具管理ASM
$ asmcmd
ASMCMD> ls -l
ASMCMD> lsdg
ASMCMD> lsod
ASMCMD> md_backup /backup/asm_config_backup.txt

— 8. 监控ASM性能指标
SELECT * FROM v$asm_perfstat;SELECT * FROM v$asm_disk_io_stat;SELECT * FROM v$asm_file_io_stat;

— 案例：为企业应用系统优化ASM性能

— 1. 检查ASM实例状态
SELECT instance_name, status FROM v$instance;– 2. 查看磁盘组性能
SELECT name, state, type, total_mb, free_mb, required_mirror_free_mb, usable_file_mb FROM v$asm_diskgroup;– 3. 查看磁盘性能
SELECT group_number, disk_number, mount_status, header_status, state, total_mb, free_mb, name FROM v$asm_disk;– 4. 增加磁盘数量，提高I/O并行度
ALTER DISKGROUP data_dg ADD DISK ‘/dev/sdf1’, ‘/dev/sdg1’;ALTER DISKGROUP log_dg ADD DISK ‘/dev/sdh1’, ‘/dev/sdi1’;– 5. 调整重平衡功率，优化重平衡性能
ALTER DISKGROUP data_dg REBALANCE POWER 11;ALTER DISKGROUP log_dg REBALANCE POWER 11;– 6. 监控重平衡状态
SELECT group_number, operation, state, power, sofar, est_work, est_rate, est_minutes FROM v$asm_operation;– 7. 优化磁盘组属性
ALTER DISKGROUP data_dg SET ATTRIBUTE ‘au_size’ = ‘4M’;ALTER DISKGROUP log_dg SET ATTRIBUTE ‘au_size’ = ‘4M’;– 8. 分离不同类型的文件到不同磁盘组
CREATE TABLESPACE user_data
DATAFILE ‘+dgdata/fgedudb/datafile/user_data01.dbf’ SIZE 1000M;CREATE TABLESPACE index_data
DATAFILE ‘+dgdata/fgedudb/datafile/index_data01.dbf’ SIZE 500M;ALTER DATABASE ADD LOGFILE
GROUP 4 (‘+log_dg/fgedudb/onlinelog/redo04a.log’, ‘+log_dg/fgedudb/onlinelog/redo04b.log’) SIZE 100M;ALTER SYSTEM SET db_recovery_file_dest = ‘+arch_dg’ SCOPE=SPFILE;– 9. 监控ASM性能指标
SELECT * FROM v$asm_perfstat;SELECT * FROM v$asm_disk_io_stat;SELECT * FROM v$asm_file_io_stat;– 10. 备份ASM配置
ALTER DISKGROUP data_dg BACKUP DISK TO ‘/backup/asm_data_config.bkp’;ALTER DISKGROUP log_dg BACKUP DISK TO ‘/backup/asm_log_config.bkp’;ALTER DISKGROUP arch_dg BACKUP DISK TO ‘/backup/asm_arch_config.bkp’;

— 1. ASM性能下降问题
— 查看ASM性能指标
SELECT * FROM v$asm_perfstat;SELECT * FROM v$asm_disk_io_stat;– 解决方案：增加磁盘数量，优化磁盘组配置
ALTER DISKGROUP data_dg ADD DISK ‘/dev/sdf1’, ‘/dev/sdg1’;– 2. 重平衡操作过慢问题
— 查看重平衡状态
SELECT group_number, operation, state, power, sofar, est_work, est_rate, est_minutes FROM v$asm_operation;– 解决方案：调整重平衡功率
ALTER DISKGROUP data_dg REBALANCE POWER 11;– 3. 磁盘I/O性能瓶颈问题
— 查看磁盘I/O统计
SELECT * FROM v$asm_disk_io_stat;– 解决方案：更换性能更好的磁盘，增加磁盘数量
ALTER DISKGROUP data_dg ADD DISK ‘/dev/sdf1’, ‘/dev/sdg1’;– 4. ASM实例内存不足问题
— 查看ASM实例内存使用情况
SELECT * FROM v$sgastat;– 解决方案：增加ASM实例内存
ALTER SYSTEM SET sga_target = 4G SCOPE=SPFILE;– 5. 磁盘组空间不足问题
— 查看磁盘组空间使用情况
SELECT name, total_mb, free_mb, required_mirror_free_mb, usable_file_mb FROM v$asm_diskgroup;– 解决方案：添加磁盘，清理不必要的文件
ALTER DISKGROUP data_dg ADD DISK ‘/dev/sdf1’;DELETE ARCHIVELOG ALL COMPLETED BEFORE ‘SYSDATE-7’;

— 1. ASM性能下降
— 查看ASM性能指标
SELECT * FROM v$asm_perfstat;SELECT * FROM v$asm_disk_io_stat;– 解决方案：增加磁盘数量，优化磁盘组配置
ALTER DISKGROUP data_dg ADD DISK ‘/dev/sdf1’, ‘/dev/sdg1’;– 2. 重平衡操作过慢
— 查看重平衡状态
SELECT group_number, operation, state, power, sofar, est_work, est_rate, est_minutes FROM v$asm_operation;– 解决方案：调整重平衡功率，在业务低峰期进行
ALTER DISKGROUP data_dg REBALANCE POWER 11;– 3. 磁盘I/O性能瓶颈
— 查看磁盘I/O统计
SELECT * FROM v$asm_disk_io_stat;– 解决方案：更换性能更好的磁盘，增加磁盘数量
ALTER DISKGROUP data_dg ADD DISK ‘/dev/sdf1’, ‘/dev/sdg1’;– 4. ASM实例内存不足
— 查看ASM实例内存使用情况
SELECT * FROM v$sgastat;– 解决方案：增加ASM实例内存
ALTER SYSTEM SET sga_target = 4G SCOPE=SPFILE;– 5. 磁盘组空间不足
— 查看磁盘组空间使用情况
SELECT name, total_mb, free_mb, required_mirror_free_mb, usable_file_mb FROM v$asm_diskgroup;– 解决方案：添加磁盘，清理不必要的文件
ALTER DISKGROUP data_dg ADD DISK ‘/dev/sdf1’;DELETE ARCHIVELOG ALL COMPLETED BEFORE ‘SYSDATE-7’;