[root@rac2 ~]# fdisk /dev/sdh Welcome to fdisk (util-linux 2.23.2).
Changes will remain in memory only, until you decide to write them. Be careful before using the write command.
Device does not contain a recognized partition table Building a new DOS disklabel with disk identifier 0x853b6083.
Command (m for help): n Partition type: p primary (0 primary, 0 extended, 4 free) e extended Select (default p): p Partition number (1-4, default 1): First sector (2048-2097151, default 2048): Using default value 2048 Last sector, +sectors or +size{K,M,G} (2048-2097151, default 2097151): Using default value 2097151 Partition 1 of type Linux and of size 1023 MiB is set
Command (m for help): w The partition table has been altered!
Calling ioctl() to re-read partition table. Syncing disks. .
-- RAC 모든 노드에서 실행1,2 [root@rac1 ~]# oracleasm scandisks Reloading disk partitions: done Cleaning any stale ASM disks... Scanning system for ASM disks... Instantiating disk "IMSI3" Instantiating disk "IMSI2" Instantiating disk "IMSI1" [root@rac2 ~]# oracleasm listdisks CRS1 CRS2 CRS3 DATA DATA2 FRA IMSI1 IMSI2 IMSI3
[root@rac2 ~]# kfod ds=true disks=all group=diskgroup -------------------------------------------------------------------------------- Disk Size Path Disk Group User Group ================================================================================ 1: 1023 MB /dev/oracleasm/disks/CRS1 CRS oracle asmadmin 2: 1023 MB /dev/oracleasm/disks/CRS2 CRS oracle asmadmin 3: 1023 MB /dev/oracleasm/disks/CRS3 CRS oracle asmadmin 4: 30719 MB /dev/oracleasm/disks/DATA DATA oracle asmadmin 5: 50999 MB /dev/oracleasm/disks/DATA2 DATA oracle asmadmin 6: 20479 MB /dev/oracleasm/disks/FRA FRA oracle asmadmin 7: 1023 MB /dev/oracleasm/disks/IMSI1 # oracle asmadmin 8: 1023 MB /dev/oracleasm/disks/IMSI2 # oracle asmadmin 9: 1023 MB /dev/oracleasm/disks/IMSI3 # oracle asmadmin KFOD returned no data 디스크그룹 생성
/dev/oracleasm/disks/IMSI1 , /dev/oracleasm/disks/IMSI2 2개 추가 SQL> create diskgroup IMSI external redundancy disk '/dev/oracleasm/disks/IMSI1','/dev/oracleasm/disks/IMSI2'; Diskgroup created.
SQL> set linesize 200 SQL> set pages 100 SQL> col disk_group_name for a15 SQL> col disk_path for a40 SQL> col disk_name for a20 SQL> col disk_fail_group for a20 SQL> SQL> SELECT 2 NVL(a.name, '[CANDIDATE]') disk_group_name , 3 b.path disk_path , 4 b.name disk_name , 5 b.failgroup disk_fail_group , 6 b.os_mb 7 FROM 8 v$asm_diskgroup a, 9 v$asm_disk b 10 WHERE 11 a.group_number(+) = b.group_number 12 ORDER BY 13 a.name, 14 b.name, 15 b.path;
SQL> alter diskgroup IMSI add disk '/dev/oracleasm/disks/IMSI3' rebalance power 1024; SQL> set linesize 200 SQL> set pages 100 SQL> col disk_group_name for a15 SQL> col disk_path for a40 SQL> col disk_name for a20 SQL> col disk_fail_group for a20 SQL> SQL> SELECT 2 NVL(a.name, '[CANDIDATE]') disk_group_name , 3 b.path disk_path , 4 b.name disk_name , 5 b.failgroup disk_fail_group , 6 b.os_mb 7 FROM 8 v$asm_diskgroup a, 9 v$asm_disk b 10 WHERE 11 a.group_number(+) = b.group_number 12 ORDER BY 13 a.name, 14 b.name, 15 b.path;
1. 사용자 삭제 select 'drop user '||username||' cascade;' from dba_users where default_tablespace in ('TBS_NAME');
ex) drop user test cascade;
2. 대상 테이블스페이스 삭제 select 'drop tablespace '||tablespace_name||' including contents and datafiles;' from (select tablespace_name from dba_data_files where file_name like '%FILE_NAME%');
3. 삭제 대상 디스크 그룹 UNMOUNT;
rac 의 경우 양 서버에서 모두 dismount
리스타트의 경우 한 번만 dismount $sqlplus / as sysasm SQL> alter diskgroup ARTEST dismount;
4. 대상 디스크 그룹 삭제 $sqlplus / as sysasm SQL> drop diskgroup ARTEST force including contents;
SQL> drop diskgroup data3 force including contents; drop diskgroup data3 force including contents * ERROR at line 1: ORA-15039: diskgroup not dropped ORA-15073: diskgroup DATA3 is mounted by another ASM instance
-- RAC 이면 모든 노드에서 디스 마운트 해줘야 된다.
5. CRS 리소스 제거( crsctl stat res -t 에서 삭제 안되면 아래 명령어 실행) $ srvctl remove diskgroup -g ARTEST -f
[root@arac1 dev]# crsctl stat res -t -------------------------------------------------------------------------------- Name Target State Server State details -------------------------------------------------------------------------------- Local Resources -------------------------------------------------------------------------------- ora.ARTEST.dg ONLINE ONLINE arac1 STABLE ora.ASMNET1LSNR_ASM.lsnr ONLINE ONLINE arac1 STABLE ora.CRS.dg ONLINE ONLINE arac1 STABLE ora.DATA.dg ONLINE ONLINE arac1 STABLE ora.FRA.dg ONLINE ONLINE arac1 STABLE ora.LISTENER.lsnr ONLINE ONLINE arac1 STABLE ora.MGMT.dg ONLINE ONLINE arac1 STABLE ora.chad ONLINE ONLINE arac1 STABLE ora.net1.network ONLINE ONLINE arac1 STABLE ora.ons ONLINE ONLINE arac1 STABLE ora.proxy_advm OFFLINE OFFLINE arac1 STABLE -------------------------------------------------------------------------------- Cluster Resources -------------------------------------------------------------------------------- 중 략 --------------------------------------------------------------------------------
디스크 그룹 파괴 [root@arac1 dev]# dd if=/dev/zero of=/dev/ARTEST/ARTESTLV count=1000 1000+0 records in 1000+0 records out 512000 bytes (512 kB) copied, 0.00114053 s, 449 MB/s
확인 - 오프라인 상태로 변경 됨 [+ASM1:/home/grid]> asmcmd ASMCMD> ls ARTEST/ CRS/ DATA/ FRA/ MGMT/ ASMCMD> cd artest ASMCMD> ls
로 그 2024-02-22T11:16:08.730714+09:00 GMON dismounting group 1 at 27 for pid 44, osid 21554 2024-02-22T11:16:08.905072+09:00 System State dumped to trace file /u01/app/oracle/diag/asm/+asm/+ASM1/incident/incdir_214849/+ASM1_ora_21498_i214849.trc 2024-02-22T11:16:08.950506+09:00 ami_comp:dbgrip.c@16254:SQL> alter diskgroup ARTEST check 2024-02-22T11:16:09.156303+09:00 NOTE: cache deleting context for group ARTEST 1/0x47c6226f 2024-02-22T11:16:09.796926+09:00 NOTE: Disk ARTEST_0000 in mode 0x7f marked for de-assignment 2024-02-22T11:16:09.801824+09:00 SUCCESS: diskgroup ARTEST was dismounted 2024-02-22T11:16:09.802800+09:00 SUCCESS: alter diskgroup ARTEST dismount force /* ASM SERVER:1204167279 */ 2024-02-22T11:16:09.805146+09:00 ami_comp:dbgrip.c@16254:ORA-15032: not all alterations performed ami_comp:dbgrip.c@16254:ORA-15001: diskgroup "ARTEST" does not exist or is not mounted ami_comp:dbgrip.c@16254:ORA-15001: diskgroup "1" does not exist or is not mounted ami_comp:dbgrip.c@16254: 2024-02-22T11:16:09.805723+09:00 ami_comp:dbgrip.c@16254:ERROR: alter diskgroup ARTEST check ami_comp:dbgrip.c@16254:Some DDE async actions failed or were cancelled 2024-02-22T11:16:09.813709+09:00 SUCCESS: ASM-initiated MANDATORY DISMOUNT of group ARTEST 2024-02-22T11:16:09.918350+09:00 NOTE: diskgroup resource ora.ARTEST.dg is offline
select group_number, name, state from v$asm_diskgroup; GROUP_NUMBER NAME STATE ------------ ------------------------ --------------------------------- 2 CRS MOUNTED 3 DATA MOUNTED 4 FRA MOUNTED 5 MGMT MOUNTED
삭제 실패 SQL> drop diskgroup ARTEST force including contents 2 ; drop diskgroup ARTEST force including contents * ERROR at line 1: ORA-15039: diskgroup not dropped ORA-15063: ASM discovered an insufficient number of disks for diskgroup "ARTEST"
그냥 crs에서 삭제 한다. 오라클 문서 1478128.1 crsctl delete resource ora.ARTEST.dg
12.2.0.1 기준 아래 방법으로 해야 된다 srvctl remove diskgroup -g ARTEST -f
[+ASM1:/home/grid]> asmcmd ls ASMCMD> CRS/ DATA/ FRA/ MGMT/ ASMCMD> cd data ASMCMD> ls aracdb/ ASMCMD> cd a* ASMCMD> ls CONTROLFILE/ DATAFILE/ ONLINELOG/ PARAMETERFILE/ PASSWORD/ TEMPFILE/ ASMCMD> cd datafile ASMCMD> ls SYSAUX.258.1136915229 SYSAUX.266.1136924577 SYSTEM.257.1136915185 SYSTEM.265.1136924533 UNDOTBS1.259.1136915255 UNDOTBS1.267.1136924603 UNDOTBS2.273.1136924729 USERS.260.1136915255 USERS.268.1136924605
[aracdb1:/home/oracle]> rman target / Recovery Manager: Release 12.2.0.1.0 - Production on Thu Jan 25 11:12:10 2024 Copyright (c) 1982, 2017, Oracle and/or its affiliates. All rights reserved. connected to target database: ARACDB (DBID=2943394426) RMAN> copy datafile 1 to '/home/oracle/system01.dbf' ; Starting backup at 24/01/25 using target database control file instead of recovery catalog allocated channel: ORA_DISK_1 channel ORA_DISK_1: SID=277 instance=aracdb1 device type=DISK channel ORA_DISK_1: starting datafile copy input datafile file number=00001 name=+DATA/ARACDB/DATAFILE/system.265.1136924533 output file name=/home/oracle/system01.dbf tag=TAG20240125T111517 RECID=1 STAMP=1159182926 channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:16 Finished backup at 24/01/25
Starting Control File and SPFILE Autobackup at 24/01/25 piece handle=+FRA/ARACDB/AUTOBACKUP/2024_01_25/s_1159182933.287.1159182935 comment=NONE Finished Control File and SPFILE Autobackup at 24/01/25
세 번째
> Using DBMS package
SQL> desc dbms_file_transfer PROCEDURE COPY_FILE Argument Name Type In/Out Default? ------------------------------ ----------------------- ------ -------- SOURCE_DIRECTORY_OBJECT VARCHAR2 IN SOURCE_FILE_NAME VARCHAR2 IN DESTINATION_DIRECTORY_OBJECT VARCHAR2 IN DESTINATION_FILE_NAME VARCHAR2 IN PROCEDURE COPY_FILE Argument Name Type In/Out Default? ------------------------------ ----------------------- ------ -------- SOURCE_DIRECTORY_OBJECT VARCHAR2 IN SOURCE_FILE_NAME VARCHAR2 IN DESTINATION_DIRECTORY_OBJECT VARCHAR2 IN DESTINATION_FILE_NAME VARCHAR2 IN CREATED_FILE_NAME VARCHAR2 OUT DESTINATION_FILE_TAG VARCHAR2 IN DEFAULT PROCEDURE GET_FILE Argument Name Type In/Out Default? ------------------------------ ----------------------- ------ -------- SOURCE_DIRECTORY_OBJECT VARCHAR2 IN SOURCE_FILE_NAME VARCHAR2 IN SOURCE_DATABASE VARCHAR2 IN DESTINATION_DIRECTORY_OBJECT VARCHAR2 IN DESTINATION_FILE_NAME VARCHAR2 IN PROCEDURE PUT_FILE Argument Name Type In/Out Default? ------------------------------ ----------------------- ------ -------- SOURCE_DIRECTORY_OBJECT VARCHAR2 IN SOURCE_FILE_NAME VARCHAR2 IN DESTINATION_DIRECTORY_OBJECT VARCHAR2 IN DESTINATION_FILE_NAME VARCHAR2 IN DESTINATION_DATABASE VARCHAR2 IN
rman 접속 후 copy RMAN> copy datafile '/u01/app/oracle/product/19c/db_1/dbs/data1' to '+DATA' 2> ;
Starting backup at 23/12/18 using target database control file instead of recovery catalog allocated channel: ORA_DISK_1 channel ORA_DISK_1: SID=214 instance=racdb1 device type=DISK channel ORA_DISK_1: starting datafile copy input datafile file number=00015 name=/u01/app/oracle/product/19c/db_1/dbs/data1 output file name=+DATA/RACDB/DATAFILE/imsi.281.1155917741 tag=TAG20231218T161539 RECID=4 STAMP=1155918015 channel ORA_DISK_1: datafile copy complete, elapsed time: 00:04:46 Finished backup at 23/12/18
Starting Control File and SPFILE Autobackup at 23/12/18 piece handle=/share/backup/rman/db_ctl_c-1091155962-20231218-00 comment=NONE Finished Control File and SPFILE Autobackup at 23/12/18
RMAN> # asm위치로 변경(rename) SQL> alter database rename file '/u01/app/oracle/product/19c/db_1/dbs/data1' to '+DATA/RACDB/DATAFILE/imsi.281.1155917741' ;
Database altered.
SQL> recover datafile '+DATA/RACDB/DATAFILE/imsi.281.1155917741'; Media recovery complete.
[root@rac1 dev]# ls -al sd* brw-rw----. 1 root disk 8, 0 6월 8 16:11 sda brw-rw----. 1 root disk 8, 1 6월 8 16:11 sda1 brw-rw----. 1 root disk 8, 2 6월 8 16:11 sda2 brw-rw----. 1 root disk 8, 16 6월 8 16:11 sdb brw-rw----. 1 root disk 8, 17 6월 8 16:11 sdb1 brw-rw----. 1 root disk 8, 32 6월 8 16:11 sdc brw-rw----. 1 root disk 8, 33 6월 8 16:11 sdc1 brw-rw----. 1 root disk 8, 48 6월 8 16:11 sdd brw-rw----. 1 root disk 8, 49 6월 8 16:11 sdd1 brw-rw----. 1 root disk 8, 64 6월 8 16:11 sde brw-rw----. 1 root disk 8, 65 6월 8 16:11 sde1 brw-rw----. 1 root disk 8, 80 6월 8 16:11 sdf brw-rw----. 1 root disk 8, 81 6월 8 16:11 sdf1 brw-rw----. 1 root disk 8, 96 6월 8 16:11 sdg brw-rw----. 1 root disk 8, 97 6월 8 16:11 sdg1 brw-rw----. 1 root disk 8, 112 6월 8 16:16 sdh brw-rw----. 1 root disk 8, 113 6월 8 17:19 sdh1 brw-rw----. 1 root disk 8, 128 6월 8 16:11 sdi brw-rw----. 1 root disk 8, 144 6월 8 16:11 sdj brw-rw----. 1 root disk 8, 160 6월 8 16:11 sdk brw-rw----. 1 root disk 8, 176 6월 8 16:11 sdl brw-rw----. 1 root disk 8, 192 6월 8 16:11 sdm
-- 파티션 생성 [root@rac1 dev]# fdisk /dev/sdi Welcome to fdisk (util-linux 2.23.2).
Changes will remain in memory only, until you decide to write them. Be careful before using the write command.
Device does not contain a recognized partition table Building a new DOS disklabel with disk identifier 0x1763f317.
Command (m for help): n Partition type: p primary (0 primary, 0 extended, 4 free) e extended Select (default p): p Partition number (1-4, default 1): First sector (2048-204799, default 2048): Using default value 2048 Last sector, +sectors or +size{K,M,G} (2048-204799, default 204799): Using default value 204799 Partition 1 of type Linux and of size 99 MiB is set
Command (m for help): w The partition table has been altered!
Calling ioctl() to re-read partition table. Syncing disks. [root@rac1 dev]# fdisk /dev/sdj Welcome to fdisk (util-linux 2.23.2).
Changes will remain in memory only, until you decide to write them. Be careful before using the write command.
Device does not contain a recognized partition table Building a new DOS disklabel with disk identifier 0x697410e9.
Command (m for help): n Partition type: p primary (0 primary, 0 extended, 4 free) e extended Select (default p): Using default response p Partition number (1-4, default 1): First sector (2048-204799, default 2048): Using default value 2048 Last sector, +sectors or +size{K,M,G} (2048-204799, default 204799): Using default value 204799 Partition 1 of type Linux and of size 99 MiB is set
Command (m for help): w The partition table has been altered!
Calling ioctl() to re-read partition table. Syncing disks. [root@rac1 dev]# fdisk /dev/sdk Welcome to fdisk (util-linux 2.23.2).
Changes will remain in memory only, until you decide to write them. Be careful before using the write command.
Device does not contain a recognized partition table Building a new DOS disklabel with disk identifier 0x0dde1be5.
Command (m for help): n Partition type: p primary (0 primary, 0 extended, 4 free) e extended Select (default p): Using default response p Partition number (1-4, default 1): First sector (2048-204799, default 2048): Using default value 2048 Last sector, +sectors or +size{K,M,G} (2048-204799, default 204799): Using default value 204799 Partition 1 of type Linux and of size 99 MiB is set
Command (m for help): w The partition table has been altered!
Calling ioctl() to re-read partition table. Syncing disks. [root@rac1 dev]# fdisk /dev/sdl Welcome to fdisk (util-linux 2.23.2).
Changes will remain in memory only, until you decide to write them. Be careful before using the write command.
Device does not contain a recognized partition table Building a new DOS disklabel with disk identifier 0xd45bb1c8.
Command (m for help): n Partition type: p primary (0 primary, 0 extended, 4 free) e extended Select (default p): Using default response p Partition number (1-4, default 1): First sector (2048-204799, default 2048): Using default value 2048 Last sector, +sectors or +size{K,M,G} (2048-204799, default 204799): Using default value 204799 Partition 1 of type Linux and of size 99 MiB is set
Command (m for help): w The partition table has been altered!
Calling ioctl() to re-read partition table. Syncing disks. [root@rac1 dev]# fdisk /dev/sdm Welcome to fdisk (util-linux 2.23.2).
Changes will remain in memory only, until you decide to write them. Be careful before using the write command.
Device does not contain a recognized partition table Building a new DOS disklabel with disk identifier 0x57f6bcbe.
Command (m for help): n Partition type: p primary (0 primary, 0 extended, 4 free) e extended Select (default p): Using default response p Partition number (1-4, default 1): First sector (2048-204799, default 2048): Using default value 2048 Last sector, +sectors or +size{K,M,G} (2048-204799, default 204799): Using default value 204799 Partition 1 of type Linux and of size 99 MiB is set
Command (m for help): w The partition table has been altered!
-- disk group 생성 SQL> create diskgroup asmdata external redundancy disk '/dev/oracleasm/disks/ASM01' ;
Diskgroup created.
-- DISK 추가 SQL> create diskgroup asmdata external redundancy disk '/dev/oracleasm/disks/ASM01' ;
Diskgroup created.
SQL> alter diskgroup asmdata add disk '/dev/oracleasm/disks/ASM02' REBALANCE POWER 5;
Diskgroup altered.
SQL> alter diskgroup asmdata add disk '/dev/oracleasm/disks/ASM03' REBALANCE POWER 5;
Diskgroup altered.
alter diskgroup asmdata add disk '/dev/oracleasm/disks/ASM02' REBALANCE POWER 5; ※ REBALANCE POWER : 0부터 1024까지 설정 할 수 있으며 숫자가 높을수록 리밸런스 속도가 빨라지지만
I/O 가 늘어남으로 시스템 상태 사용률이 낮거나 업무시간 이후에 높이는 것 추천!!
SQL> col path for a30 SQL> col name for a14 SQL> select group_number, disk_number, name, mount_status, path, total_mb, free_mb 2 from v$asm_disk 3 order by 1,3;
SQL> set line 200 SQL> col path for a50 SQL> select group_number, mount_status, path, total_mb 2 from v$asm_disk where mount_status='CLOSED'; . GROUP_NUMBER MOUNT_STATUS PATH TOTAL_MB ------------ --------------------- -------------------------------------------------- ---------- 0 CLOSED /dev/oracleasm/disks/CRS4 0
SQL> COL LABEL FOR A10 SQL> COL STATE FOR A10 SQL> SELECT A.NAME AS DISK_GROUP, D.NAME "LABEL", A.STATE 2 FROM V$ASM_DISK D, V$ASM_DISKGROUP A 3 WHERE D.GROUP_NUMBER=A.GROUP_NUMBER 4 ORDER BY 2;
DISK_GROUP LABEL STATE ------------------------------------------------------------------------------------------ ---------- ---------- CRS CRS_0000 MOUNTED CRS CRS_0001 MOUNTED CRS CRS_0002 MOUNTED DATA DATA2 MOUNTED DATA DATA_0000 MOUNTED FRA FRA_0000 MOUNTED
6 rows selected.
SQL> col path for a30 SQL> col name for a14 SQL> select group_number, disk_number, name, mount_status, path, total_mb, free_mb 2 from v$asm_disk 3 order by 1,3;
SQL> ALTER DISKGROUP CRS ADD DISK '/dev/oracleasm/disks/CRS4' REBALANCE POWER 5; ALTER DISKGROUP CRS ADD DISK '/dev/oracleasm/disks/CRS4' REBALANCE POWER 5 * ERROR at line 1: ORA-15032: not all alterations performed ORA-15260: permission denied on ASM disk group
SQL> exit Disconnected from Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production Version 19.3.0.0.0 [+ASM1:/home/oracle]> sqlplus / as sysasm
SQL*Plus: Release 19.0.0.0.0 - Production on Thu Jun 8 17:22:00 2023 Version 19.3.0.0.0
Copyright (c) 1982, 2019, Oracle. All rights reserved.
Connected to: Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production Version 19.3.0.0.0
SQL> ALTER DISKGROUP CRS ADD DISK '/dev/oracleasm/disks/CRS4' REBALANCE POWER 5;
DISK_GROUP LABEL STATE ---------- ---------- ---------- CRS CRS_0000 MOUNTED CRS CRS_0001 MOUNTED CRS CRS_0002 MOUNTED CRS CRS_0003 MOUNTED DATA DATA2 MOUNTED DATA DATA_0000 MOUNTED FRA FRA_0000 MOUNTED
7 rows selected.
-- ASM DISKGROUP 세부 정보 확인 SQL> col path for a30 SQL> col name for a14 SQL> select group_number, disk_number, name, mount_status, path, total_mb, free_mb 2 from v$asm_disk 3 order by 1,3; GROUP_NUMBER DISK_NUMBER NAME MOUNT_STATUS PATH TOTAL_MB FREE_MB ------------ ----------- -------------- --------------------- ------------------------------ ---------- ---------- 1 0 CRS_0000 CACHED /dev/oracleasm/disks/CRS1 1020 760 1 1 CRS_0001 CACHED /dev/oracleasm/disks/CRS2 1020 764 1 2 CRS_0002 CACHED /dev/oracleasm/disks/CRS3 1020 760 1 3 CRS_0003 CACHED /dev/oracleasm/disks/CRS4 1020 816 2 1 DATA2 CACHED /dev/oracleasm/disks/DATA2 50996 47884 2 0 DATA_0000 CACHED /dev/oracleasm/disks/DATA 30716 28820 3 0 FRA_0000 CACHED /dev/oracleasm/disks/FRA 20476 19084
7 rows selected.
-- CRS_003 삭제
SQL> alter diskgroup crs drop disk CRS_0003;
-- ASM DISKGROUP 세부 정보 확인 SQL> col path for a30 SQL> col name for a14 SQL> select group_number, disk_number, name, mount_status, path, total_mb, free_mb 2 from v$asm_disk 3 order by 1,3;
