Oracle集群升级迁移—老Oracle集群磁盘剔除
Oracle升级迁移
目前有两套Oracle采用ADG + RAC架构，其中备库使用的为SUSE12.4目前已EOS，文件系统BFTFS与Oracle兼容性据说也有一定的问题，决定对现有的集群进行升级，升级后服务器统一采用SUSE12.5+EXT4文件系统。
由于没有多余的存储，原Oracle备机的ASM磁盘又够大，为节省成本准备在备机存储上剔除磁盘供新建Oracle服务器使用。
先对剔除磁盘过程进行记录。
剔除磁盘腾出存储LUN
GRID用户登录，查询ASM磁盘
v$asm_diskgroup 查询磁盘组整体概况
v$asm_disk 查询ASM磁盘组具体挂载情况
172530.913: Last login: Sat Oct 7 20:04:28 2023 from 21.1.143.29
172541.398: szn-db-ora1:~ # su - grid
172555.803: grid@szn-db-ora1:~> sqlplus "/as sysdba"
172555.813:
172555.813: SQL*Plus: Release 12.2.0.1.0 Production on Sun Oct 8 17:25:55 2023
172555.813:
172555.813: Copyright (c) 1982, 2016, Oracle. All rights reserved.
172555.813:
172555.828:
172555.833: Connected to:
172555.833: Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production
172555.833:
172701.650: SQL> desc v$asm_diskgroup;
172701.650: Name Null? Type
172701.655: ----------------------------------------- -------- ----------------------------
172701.655: GROUP_NUMBER NUMBER
172701.655: NAME VARCHAR2(30)
172701.655: SECTOR_SIZE NUMBER
172701.655: LOGICAL_SECTOR_SIZE NUMBER
172701.655: BLOCK_SIZE NUMBER
172701.655: ALLOCATION_UNIT_SIZE NUMBER
172701.655: STATE VARCHAR2(11)
172701.655: TYPE VARCHAR2(6)
172701.655: TOTAL_MB NUMBER
172701.655: FREE_MB NUMBER
172701.655: HOT_USED_MB NUMBER
172701.655: COLD_USED_MB NUMBER
172701.655: REQUIRED_MIRROR_FREE_MB NUMBER
172701.655: USABLE_FILE_MB NUMBER
172701.655: OFFLINE_DISKS NUMBER
172701.655: COMPATIBILITY VARCHAR2(60)
172701.655: DATABASE_COMPATIBILITY VARCHAR2(60)
172701.655: VOTING_FILES VARCHAR2(1)
172701.655: CON_ID NUMBER
172701.655:
172711.545: SQL> c/status/state
172711.545: 1* select name,free_mb/1024,total_mb/1024,state from v$asm_diskgroup
172712.590: SQL> /
172712.705:
172712.705: NAME FREE_MB/1024 TOTAL_MB/1024 STATE
172712.705: ------------------------------ ------------ ------------- -----------
172712.705: CDB_REDOA 10.7109375 25 MOUNTED
172712.705: CDB_REDOB 10.7109375 25 MOUNTED
172712.705: DATA 7584.29688 8192 MOUNTED
172712.710: FRA 2033.77734 2048 MOUNTED
172712.710: MGMT 133.554688 200 MOUNTED
172712.710: OCR 14.1523438 15 MOUNTED
172712.710:
172712.710: 6 rows selected.
172712.710:
172732.235: SQL> desc v$asm_disk
172732.240: Name Null? Type
172732.245: ----------------------------------------- -------- ----------------------------
172732.245: GROUP_NUMBER NUMBER
172732.245: DISK_NUMBER NUMBER
172732.245: COMPOUND_INDEX NUMBER
172732.245: INCARNATION NUMBER
172732.245: MOUNT_STATUS VARCHAR2(7)
172732.245: HEADER_STATUS VARCHAR2(12)
172732.245: MODE_STATUS VARCHAR2(7)
172732.245: STATE VARCHAR2(8)
172732.245: REDUNDANCY VARCHAR2(7)
172732.245: LIBRARY VARCHAR2(64)
172732.245: OS_MB NUMBER
172732.245: TOTAL_MB NUMBER
172732.245: FREE_MB NUMBER
172732.245: HOT_USED_MB NUMBER
172732.245: COLD_USED_MB NUMBER
172732.245: NAME VARCHAR2(30)
172732.245: FAILGROUP VARCHAR2(30)
172732.245: LABEL VARCHAR2(31)
172732.245: PATH VARCHAR2(256)
172732.245: UDID VARCHAR2(64)
172732.245: PRODUCT VARCHAR2(32)
172732.245: CREATE_DATE DATE
172732.245: MOUNT_DATE DATE
172732.245: REPAIR_TIMER NUMBER
172732.245: READS NUMBER
172732.245: WRITES NUMBER
172732.280: READ_ERRS NUMBER
172732.280: WRITE_ERRS NUMBER
172732.280: READ_TIMEOUT NUMBER
172732.280: WRITE_TIMEOUT NUMBER
172732.280: READ_TIME NUMBER
172732.280: WRITE_TIME NUMBER
172732.280: BYTES_READ NUMBER
172732.280: BYTES_WRITTEN NUMBER
172732.280: PREFERRED_READ VARCHAR2(1)
172732.280: HASH_VALUE NUMBER
172732.280: HOT_READS NUMBER
172732.285: HOT_WRITES NUMBER
172732.285: HOT_BYTES_READ NUMBER
172732.285: HOT_BYTES_WRITTEN NUMBER
172732.285: COLD_READS NUMBER
172732.285: COLD_WRITES NUMBER
172732.285: COLD_BYTES_READ NUMBER
172732.285: COLD_BYTES_WRITTEN NUMBER
172732.285: VOTING_FILE VARCHAR2(1)
172732.285: SECTOR_SIZE NUMBER
172732.285: LOGICAL_SECTOR_SIZE NUMBER
172732.285: FAILGROUP_TYPE VARCHAR2(7)
172732.285: CON_ID NUMBER
172732.285: THIN_PROVISION_CAPABLE VARCHAR2(1)
172732.285: DATA_INTEGRITY_CAPABLE VARCHAR2(1)
172732.285: SITE_NAME VARCHAR2(30)
172732.285: SITE_GUID VARCHAR2(33)
172732.285: FAILGROUP_LABEL VARCHAR2(30)
172732.285: SITE_LABEL VARCHAR2(30)
172732.285: SITE_STATUS VARCHAR2(11)
172732.285:
SQL> set linesize 200
172842.636: SQL> /
172842.756:
172842.756: NAME FREE_MB/1024 TOTAL_MB/1024 STATE GROUP_NUMBER
172842.756: ------------------------------ ------------ ------------- ----------- ------------
172842.756: CDB_REDOA 10.7109375 25 MOUNTED 1
172842.756: CDB_REDOB 10.7109375 25 MOUNTED 2
172842.756: DATA 7584.29688 8192 MOUNTED 3
172842.756: FRA 2033.77734 2048 MOUNTED 4
172842.756: MGMT 133.554688 200 MOUNTED 5
172842.756: OCR 14.1523438 15 MOUNTED 6
172842.756:
172925.122: SQL> select GROUP_NUMBER,DISK_NUMBER ,NAME,PATH,STATE from v$asm_disk;
SQL> col path for a40
172930.522: SQL> /
172930.652:
172930.652: GROUP_NUMBER DISK_NUMBER NAME PATH STATE
172930.652: ------------ ----------- ------------------------------ ---------------------------------------- --------
172930.652: 3 1 DATA_0001 /dev/mapper/asm-data2 NORMAL
172930.652: 3 0 DATA_0000 /dev/mapper/asm-data1 NORMAL
172930.652: 4 1 FRA_0001 /dev/mapper/asm-fra2 NORMAL
172930.652: 4 0 FRA_0000 /dev/mapper/asm-fra1 NORMAL
172930.657: 5 1 MGMT_0001 /dev/mapper/asm-mgmt2 NORMAL
172930.657: 5 0 MGMT_0000 /dev/mapper/asm-mgmt1 NORMAL
172930.657: 6 2 OCR_0002 /dev/mapper/asm-ocr3 NORMAL
172930.657: 2 4 CDB_REDOB_0004 /dev/mapper/asm-redob5 NORMAL
172930.657: 2 3 CDB_REDOB_0003 /dev/mapper/asm-redob4 NORMAL
172930.657: 2 2 CDB_REDOB_0002 /dev/mapper/asm-redob3 NORMAL
172930.657: 2 1 CDB_REDOB_0001 /dev/mapper/asm-redob2 NORMAL
172930.657:
172930.657: GROUP_NUMBER DISK_NUMBER NAME PATH STATE
172930.657: ------------ ----------- ------------------------------ ---------------------------------------- --------
172930.657: 2 0 CDB_REDOB_0000 /dev/mapper/asm-redob1 NORMAL
172930.657: 1 4 CDB_REDOA_0004 /dev/mapper/asm-redoa5 NORMAL
172930.657: 6 0 OCR_0000 /dev/mapper/asm-ocr2 NORMAL
172930.657: 1 3 CDB_REDOA_0003 /dev/mapper/asm-redoa4 NORMAL
172930.687: 1 2 CDB_REDOA_0002 /dev/mapper/asm-redoa3 NORMAL
172930.687: 1 1 CDB_REDOA_0001 /dev/mapper/asm-redoa2 NORMAL
172930.687: 1 0 CDB_REDOA_0000 /dev/mapper/asm-redoa1 NORMAL
172930.687: 3 7 DATA_0007 /dev/mapper/asm-data8 NORMAL
172930.687: 3 6 DATA_0006 /dev/mapper/asm-data7 NORMAL
172930.687: 3 5 DATA_0005 /dev/mapper/asm-data6 NORMAL
172930.687: 3 4 DATA_0004 /dev/mapper/asm-data5 NORMAL
172930.687:
172930.687: GROUP_NUMBER DISK_NUMBER NAME PATH STATE
172930.687: ------------ ----------- ------------------------------ ---------------------------------------- --------
172930.692: 3 3 DATA_0003 /dev/mapper/asm-data4 NORMAL
172930.692: 3 2 DATA_0002 /dev/mapper/asm-data3 NORMAL
172930.692: 6 1 OCR_0001 /dev/mapper/asm-ocr1 NORMAL
172930.692:
172930.692: 25 rows selected.
剔除磁盘
操作系统登录GRID，SQL登录sysasm进行剔盘
alter diskgroup FRA drop disk 'FRA_0001' 剔除磁盘
select *from v$asm_operation; 查询操作
CDB、MGMT、OCR不动，剔除FRA和DATA的磁盘各腾出一半
172944.392: SQL> select name,free_mb/1024,total_mb/1024,state,GROUP_NUMBER from v$asm_diskgroup;
172944.577:
172944.577: NAME FREE_MB/1024 TOTAL_MB/1024 STATE GROUP_NUMBER
172944.577: ------------------------------ ------------ ------------- ----------- ------------
172944.577: CDB_REDOA 10.7109375 25 MOUNTED 1
172944.577: CDB_REDOB 10.7109375 25 MOUNTED 2
172944.577: DATA 7584.29688 8192 MOUNTED 3
172944.577: FRA 2033.77734 2048 MOUNTED 4
172944.582: MGMT 133.554688 200 MOUNTED 5
172944.582: OCR 14.1523438 15 MOUNTED 6
173035.283: SQL> conn /as sysasm
173035.303: Connected.
173043.318: SQL> alter diskgroup FRA drop disk 'FRA_0001';
173043.403:
173043.403: Diskgroup altered.
173043.403:
173056.033: SQL> select *from v$asm_operatoin;
173056.038: select *from v$asm_operatoin
173056.038: *
173056.038: ERROR at line 1:
173056.038: ORA-01219: database or pluggable database not open: queries allowed on fixed tables or views only
173056.038:
173056.038:
173102.933: SQL> select *from v$asm_operation;
173102.943:
173102.943: GROUP_NUMBER OPERA PASS STAT POWER ACTUAL SOFAR EST_WORK EST_RATE EST_MINUTES ERROR_CODE CON_ID
173102.943: ------------ ----- --------- ---- ---------- ---------- ---------- ---------- ---------- ----------- -------------------------------------------- ----------
173102.943: 4 REBAL COMPACT WAIT 1 1 0 0 0 0 0
173102.943: 4 REBAL REBALANCE RUN 1 1 1354 1821 16394 0 0
173102.943: 4 REBAL REBUILD DONE 1 1 0 0 0 0 0
173102.943:
173109.233: SQL> /
173109.238:
173109.238: GROUP_NUMBER OPERA PASS STAT POWER ACTUAL SOFAR EST_WORK EST_RATE EST_MINUTES ERROR_CODE CON_ID
173109.238: ------------ ----- --------- ---- ---------- ---------- ---------- ---------- ---------- ----------- -------------------------------------------- ----------
173109.238: 4 REBAL COMPACT RUN 1 1 0 0 0 0 0
173109.238: 4 REBAL REBALANCE DONE 1 1 1821 1821 0 0 0
173109.243: 4 REBAL REBUILD DONE 1 1 0 0 0 0 0
173109.243:
173110.178: SQL> /
剔除成功后 $v$asm_disgroup 里的state显示droping
174625.392: SQL> select group_number,disk_number,state,name,path from v$asm_disk where group_number=3;
174626.787:
174626.787: GROUP_NUMBER DISK_NUMBER STATE NAME PATH
174626.787: ------------ ----------- -------- ------------------------------ ----------------------------------------
174626.787: 3 1 NORMAL DATA_0001 /dev/mapper/asm-data2
174626.787: 3 0 NORMAL DATA_0000 /dev/mapper/asm-data1
174626.787: 3 7 DROPPING DATA_0007 /dev/mapper/asm-data8
174626.787: 3 6 DROPPING DATA_0006 /dev/mapper/asm-data7
174626.787: 3 5 DROPPING DATA_0005 /dev/mapper/asm-data6
174626.787: 3 4 DROPPING DATA_0004 /dev/mapper/asm-data5
174626.787: 3 3 NORMAL DATA_0003 /dev/mapper/asm-data4
174626.787: 3 2 NORMAL DATA_0002 /dev/mapper/asm-data3
