ASM disk space insufficiency-ORA-15041: disgroup data space exhausted (production database case)

ASM disk space insufficiency-ORA-15041: disgroup data space exhausted (production database case)

Recently, I have handled a problem caused by insufficient space in the ASM disk.

A simple record is as follows:

First. Problem feedback

Feedback from resident engineers:

The resident engineer informed the problem by email and the emergency response to the problem.

Probably like this description: told the inspection found a picture of the tablespace is full, expansion of its operation, error: ORA-15041: DISGROUP "DATA" space exhausted. The customer needs to assess the last month's data at the beginning of the month and upload some photos. This issue is urgent and needs to be resolved immediately.

The attachment contains the following query information:
SQL> select group_number, name, total_mb, free_mb from v $ ASM_DISKGROUP;

GROUP_NUMBER NAME TOTAL_MB FREE_MB
--------------------------------------------------------------
1 ARCH 860159 405817
2 CRS 30717 29791
3. DATA 1638394 238

SQL> select name, group_number, state, redundancy, total_mb, free_mb, path from v $ asm_disk;

NAME GROUP_NUMBER state redunda TOTAL_MB
-------------------------------------------------------------------
FREE_MB
----------
PATH
--------------------------------------------------------------------------------
ARCH_0000 1 normal unknown 860159
405817
/Dev/oracleasm/disks/ARCH

CRS_0002 2 normal unknown 10239
9931
/Dev/oracleasm/disks/VOTE_CRS3

NAME GROUP_NUMBER state redunda TOTAL_MB
-------------------------------------------------------------------
FREE_MB
----------
PATH
--------------------------------------------------------------------------------

CRS_0001 2 normal unknown 10239
9930
/Dev/oracleasm/disks/VOTE_CRS2

DATA_0001 3 normal unknown 819197
112

NAME GROUP_NUMBER state redunda TOTAL_MB
-------------------------------------------------------------------
FREE_MB
----------
PATH
--------------------------------------------------------------------------------
/Dev/oracleasm/disks/DATA2

DATA_0000 3 normal unknown 819197
126
/Dev/oracleasm/disks/DATA1

CRS_0000 2 normal unknown 10239

NAME GROUP_NUMBER state redunda TOTAL_MB
-------------------------------------------------------------------
FREE_MB
----------
PATH
--------------------------------------------------------------------------------
9930
/Dev/oracleasm/disks/VOTE_CRS1

6 rows selected.

Second. Urgent Handling

When the database is connected to the generative database, the asm space is insufficient.
ASMCMD> lsdg

State Type Rebal Sector Block AU Total_MB Free_MB Req_mir_free_MB Usable_file_MB Offline_disks Voting_files Name
Mounted extern n 512 4096 1048576 860159 0 405780 0 n arch/
Mounted normal n 512 4096 1048576 30717 29791 10239 0 y crs/
Mounted extern n 512 4096 1048576 1638394 0 238 0 n data/

In order to quickly solve the problem and let the application run, we decided to start with how to solve the problem that cannot expand the tablespace.
The thought is to reduce the table space with low utilization.

View the table space usage:
1. undo and temp tablespaces are greatly resized and can be reduced;
2. Some low-utilization tablespaces are found, such as GB-level tables that only store a few MB of data, which can be reduced;

Therefore, the following commands are used consecutively:

ALTER DATABASE
TEMPFILE '+ DATA/xcky/xckytmp04.dbf'
RESIZE 1024 M;

It is used to reduce the size of the table space that can be reduced.

After some space reduction, the system queries the space usage again to meet the needs of table space expansion, and completes the expansion of the photo tablespace stored in the business. The application system returns to normal.

Third. Periodic feedback

Feedback to the resident engineers for problem solving quickly.

The cause is: the space of the ASM disk is insufficient.

1. The temporary method is to reduce the size of other tablespaces and release space for the/DATA Directory (reduce the size of undo tablespaces, temp tablespaces, and other tablespaces with low space utilization ).

In addition, a new 10 Gb table space for automatic scaling and photo storage has been created, named photo_info47.dbf.

2. But follow-up suggestions:

(1) expand storage capacity.
According to the ASM planning policy in the current environment, the/DATA in the ASM disk group currently uses about 1.4 Tb (the total size is about 1.5 TB), and the current available space in/DATA is about 50 GB.

(2) or re-plan the asm storage, and consider temporarily expanding the tablespace on/ARCH (currently the remaining GB is available). However, this/ARCH is used to store archive files. We do not recommend that you do this, in the future, if there is a sharp increase in archiving, there may be the possibility of hang stopping the database.

Fourth. Solving Essential Problems in the future

 

Connect to the production database again to check whether there are any good solutions to the problem.
First, query the general usage of the current space.
SQL> conn sys/oracle as sysdba
Connected.
SQL> show user
USER is "SYS"
SQL> select path, total_mb, free_mb from v $ asm_disk_stat;

PATH TOTAL_MB FREE_MB
----------------------------------------------------------------------
/Dev/oracleasm/disks/Archy 860159 405780
/Dev/oracleasm/disks/VOTE_CRS3 10239 9931
/Dev/oracleasm/disks/VOTE_CRS2 10239 9930
/Dev/oracleasm/disks/DATA2 819197 25466
/Dev/oracleasm/disks/data_1 819197 25480
/Dev/oracleasm/disks/VOTE_CRS1 10239 9930

6 rows selected.

ASMCMD> lsdg
State Type Rebal Sector Block AU Total_MB Free_MB Req_mir_free_MB Usable_file_MB Offline_disks Voting_files Name
Mounted extern n 512 4096 1048576 860159 0 404777 0 n arch/
Mounted normal n 512 4096 1048576 30717 29791 10239 0 y crs/
Mounted extern n 512 4096 1048576 1638394 0 49590 0 n data/

Check the disk group information.
SQL> select name, state from v $ asm_diskgroup;

NAME STATE
-----------------------------------------
ARCH CONNECTED
CRS MOUNTED
DATA CONNECTED

Check the disk usage of the system and send a good message.
I don't know why, a disk is not used in storage. That's great. You can give it to ASM.
Therefore, we will first query this disk.

[Root @ gzxkdb1 ~] # Fdisk-l
Disk/dev/emcpoweree: 2147.4 GB, 2147483648000 bytes
255 heads, 63 sectors/track, 261083 cylinders
Units = cylinders of 16065*512 = 8225280 bytes

Disk/dev/emcpoweree doesn't contain a valid partition table
With the above information, the/dev/emcpoweree device is not divided for use.

Partition the disk of the specified device.
[Root @ gzxkdb1 ~] # Fdisk/dev/emcpoweree
Device contains neither a valid DOS partition table, nor Sun, SGI or OSF disklabel
Building a new DOS disklabel. Changes will remain in memory only,
Until you decide to write them. After that, of course, the previous
Content won't be recoverable.

The number of cylinders for this disk is set to 261083.
There is nothing wrong with that, but this is larger than 1024,
And coshould in certain setups cause problems:
1) software that runs at boot time (e.g., old versions of LILO)
2) booting and partitioning software from other OSs
(E.g., dos fdisk, OS/2 FDISK)
Warning: invalid flag 0x0000 of partition table 4 will be corrected by w (rite)

Command (m for help): n
Command action
E extended
P primary partition (1-4)
P
Partition number (1-4): 1
First cylinder (1-261083, default 1 ):
Using default value 1
Last cylinder or + size or + sizeM or + sizeK (1-261083, default 261083): + 500G

Command (m for help): p

Disk/dev/emcpoweree: 2147.4 GB, 2147483648000 bytes
255 heads, 63 sectors/track, 261083 cylinders
Units = cylinders of 16065*512 = 8225280 bytes

Device Boot Start End Blocks Id System
/Dev/emcpoweree1 1 60789 488287611 83 Linux

Command (m for help): n
Command action
E extended
P primary partition (1-4)
P
Partition number (1-4): 2
First cylinder (60790-261083, default 60790 ):
Using default value 60790
Last cylinder or + size or + sizeM or + sizeK (60790-261083, default 261083): + 500G

Command (m for help): p

Disk/dev/emcpoweree: 2147.4 GB, 2147483648000 bytes
255 heads, 63 sectors/track, 261083 cylinders
Units = cylinders of 16065*512 = 8225280 bytes

Device Boot Start End Blocks Id System
/Dev/emcpoweree1 1 60789 488287611 83 Linux
/Dev/emcpoweree2 60790 121578 488287642 + 83 Linux

Command (m for help): m
Command action
A toggle a bootable flag
B edit bsd disklabel
C toggle the dos compatibility flag
D delete a partition
L list known partition types
M print this menu
N add a new partition
O create a new empty DOS partition table
P print the partition table
Q quit without saving changes
S create a new empty Sun disklabel
T change a partition's system id
U change display/entry units
V verify the partition table
W write table to disk and exit
X extra functionality (experts only)

Command (m for help): n
Command action
E extended
P primary partition (1-4)
P
Partition number (1-4): 3
First cylinder (121579-261083, default 121579 ):
Using default value 121579
Last cylinder or + size or + sizeM or + sizeK (121579-261083, default 261083): + 500G

Command (m for help): p

Disk/dev/emcpoweree: 2147.4 GB, 2147483648000 bytes
255 heads, 63 sectors/track, 261083 cylinders
Units = cylinders of 16065*512 = 8225280 bytes

Device Boot Start End Blocks Id System
/Dev/emcpoweree1 1 60789 488287611 83 Linux
/Dev/emcpoweree2 60790 121578 488287642 + 83 Linux
/Dev/emcpoweree3 121579 182367 488287642 + 83 Linux

Command (m for help): n
Command action
E extended
P primary partition (1-4)
P
Selected partition 4
First cylinder (182368-261083, default 182368 ):
Using default value 182368
Last cylinder or + size or + sizeM or + sizeK (182368-261083, default 261083 ):
Using default value 261083

Command (m for help): p

Disk/dev/emcpoweree: 2147.4 GB, 2147483648000 bytes
255 heads, 63 sectors/track, 261083 cylinders
Units = cylinders of 16065*512 = 8225280 bytes

Device Boot Start End Blocks Id System
/Dev/emcpoweree1 1 60789 488287611 83 Linux
/Dev/emcpoweree2 60790 121578 488287642 + 83 Linux
/Dev/emcpoweree3 121579 182367 488287642 + 83 Linux
/Dev/emcpoweree4 182368 261083 632286270 83 Linux

Command (m for help): w
The partition table has been altered!

Calling ioctl () to re-read partition table.
Syncing disks.

Above, the disk is partitioned. It is divided into four primary partitions, three 500 GB, and the remaining last partition.

View the asm disk list

[Root @ gzxkdb1 ~] # Service oracleasm listdisks
ARCH
DATA1
DATA2
VOTE_CRS1
VOTE_CRS2
VOTE_CRS3
Create an asm Disk
[Root @ gzxkdb1 ~] # Service oracleasm createdisk DATA3/dev/emcpoweree1
Marking disk "DATA3" as an ASM disk: [OK]
[Root @ gzxkdb1 ~] # Service oracleasm createdisk DATA4/dev/emcpoweree2
Marking disk "DAT43" as an ASM disk: [OK]
[Root @ gzxkdb1 ~] # Service oracleasm createdisk DATA5/dev/emcpoweree3
Marking disk "DATA5" as an ASM disk: [OK]
[Root @ gzxkdb1 ~] # Service oracleasm createdisk DATA6/dev/emcpoweree4
Marking disk "DATA6" as an ASM disk: [OK]

Scan the Newly Added Disk on another node
[Root @ gzxkdb2 ~] # Service oracleasm scandisks// Scan the disk on node 2
[Root @ gzxkdb2 ~] # Service oracleasm listdisks
ARCH
DATA4
DATA1
DATA2
DATA3
DATA5
DATA6
VOTE_CRS1
VOTE_CRS2
VOTE_CRS3

Log on to the instance using sysasm at Node 1.
[Grid @ gzxkdb1 ~] $ Sqlplus '/as sysasm'

SQL * Plus: Release 11.2.0.3.0 Production on Mon Aug 3 17:48:58 2015

Copyright (c) 1982,201 1, Oracle. All rights reserved.

Connected:
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0-64bit Production
With the Real Application Clusters and Automatic Storage Management options

View the asm disk status
SQL> set linesize 200
SQL> set pagesize 200
SQL> col NAME for a30
SQL> col PATH for a50
SQL> r
1 * select name, path, mode_status, state, disk_number, failgroup from v $ asm_disk

Name path MODE_ST STATE DISK_NUMBER FAILGROUP
----------------------------------------------------------------------------------------------------------------------------------------
/Dev/oracleasm/disks/DATA6 online normal 0
/Dev/oracleasm/disks/DATA5 online normal 1
/Dev/oracleasm/disks/DATA4 online normal 2
/Dev/oracleasm/disks/DATA3 online normal 3
ARCH_0000/dev/oracleasm/disks/arch online normal 0 ARCH_0000
CRS_0002/dev/oracleasm/disks/VOTE_CRS3 online normal 2 CRS_0002
CRS_0001/dev/oracleasm/disks/VOTE_CRS2 online normal 1 CRS_0001
DATA_0001/dev/oracleasm/disks/DATA2 online normal 1 DATA_0001
DATA_0000/dev/oracleasm/disks/DATA1 online normal 0 DATA_0000
CRS_0000/dev/oracleasm/disks/VOTE_CRS1 online normal 0 CRS_0000

10 rows selected.

Bulk resizing of ASM disk groups
SQL> alter diskgroup DATA add disk'/dev/oracleasm/disks/DATA3 'rebalance 10;

Diskgroup altered.

SQL> alter diskgroup DATA add disk'/dev/oracleasm/disks/DATA4 'rebalance 10;

Diskgroup altered.

SQL> alter diskgroup DATA add disk'/dev/oracleasm/disks/data5' rebalance 10;

Diskgroup altered.

SQL> alter diskgroup DATA add disk'/dev/oracleasm/disks/data6' rebalance 10;

Diskgroup altered.

 

SQL> select * from v $ asm_operation;

GROUP_NUMBER opera stat power actual sofar EST_WORK EST_RATE EST_MINUTES ERROR_CODE
------------------------------------------------------------------------------------------------------------------------------
3 rebal run 10 10 59949 634963 5143 111

When no data is found in the query v $ asm_operation, the IO automatic balancing is completed.

SQL> select * from v $ asm_operation;
No rows selected

View the disk space again
ASMCMD> lsdg
State Type Rebal Sector Block AU Total_MB Free_MB Req_mir_free_MB Usable_file_MB Offline_disks Voting_files Name
Mounted extern n 512 4096 1048576 860159 0 404170 0 n arch/
Mounted normal n 512 4096 1048576 30717 29791 10239 0 y crs/
Mounted extern y 512 4096 1048576 3686390 0 2097561 0 n data/
DATA/has been resized, And there is nearly 2 TB of space available to meet business needs for a period of time.

Fifth. Final feedback

Message:
Yesterday, the problem of "insufficient space for ASM disk groups" occurred in Guizhou. Later, it was found that there was no space for the disk array, about 2 TB, which has been added for ASM.
It can meet the needs of disk space for a period of time.

The resident engineer expressed thanks.

 

So far, this task record has been completed.