Original work, from the "Deep Blue Blog" blog,Deep blue blog:/huangyanlong/article/details/47277715
Recently, a problem caused by insufficient ASM disk group space was dealt with.
The simple record is as follows:
1. Feedback on the problem
Feedback from the resident engineer:
The resident engineer informed the problem in the form of an email and the urgency of solving the problem.
A brief description: When I informed me that the photo table space was full during the inspection, I found that the photo table space was full, and I expanded it. An error was reported: ORA-15041: DISGROUP "DATA" space exhausted. Since the data of last month need to be assessed at the beginning of the month, customers uploaded some photos. This matter is more urgent and needs to be resolved immediately.
In the attachment, some query information is included, as follows:
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.
2. Emergency handling
Connecting to the generation library, the query is indeed seriously insufficient asm space.
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 405780 0 405780 0 N ARCH/
MOUNTED NORMAL N 512 4096 1048576 30717 29791 10239 9776 0 Y CRS/
MOUNTED EXTERN N 512 4096 1048576 1638394 238 0 238 0 N DATA/
In order to quickly solve the problem and make the application run, we decided to start with how to solve the problem that cannot be expanded.
What comes to mind is to reduce the table space that reduces utilization.
So check the usage of the tablespace:
1. It is found that the undo table space and temp table space have been expanded very much, so it can be reduced;
2. Some table spaces with low utilization rates have been found, such as GB-level data volumes that only have a few M stored, so you can consider reducing them;
So use commands like the following continuously:
ALTER DATABASE
TEMPFILE '+DATA/xcky/'
RESIZE 1024M;
Used to reduce the size of the reduced tablespace.
After some space reduction, the space usage rate was checked again to meet the requirements of expanding the table space, and the expansion of the table space stored in the business was completed. The application system is restored to normal use.
3. Phase feedback
Quickly give back to the problem solving of the resident engineers.
The cause of the problem is: insufficient ASM disk group space.
1. The temporary method is to reduce the size of other tablespaces and free up space for the /DATA directory (reduce the size of undo tablespace, temp tablespace, and other tablespaces with low space utilization).
In addition, a new 10G table space for automatically expanding and storing photos has been created, named photo_info47.dbf.
2. But follow-up suggestions:
(1) Expand storage capacity.
According to the ASM planning strategy of this environment, /DATA in the ASM disk group has used about 1.4T (the total size is about 1.5T), and the current available space is about 50G.
(2) Or re-plan the asm storage and consider temporarily expanding the table space on /ARCH (currently 400G is available), but this /ARCH is used to store archive files. It is not recommended to do so. If the archive increases dramatically in the future, it may cause the database to be suspended.
4. Follow-up solution of essential problems
Connect the production library again to check if there is a good way to further solve the problem.
Let’s first check 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/ARCH 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/DATA1 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 404777 0 404777 0 N ARCH/
MOUNTED NORMAL N 512 4096 1048576 30717 29791 10239 9776 0 Y CRS/
MOUNTED EXTERN N 512 4096 1048576 1638394 49590 0 49590 0 N DATA/
Check the disk group situation
SQL> select name,state from v$asm_diskgroup;
NAME STATE
------------------------------ -----------
ARCH CONNECTED
CRS MOUNTED
DATA CONNECTED
Check the system's disk usage and sent a good message.
For some reason, there is actually a disk in the storage that is not used. That's great, you can share it with ASM.
So, let’s query this disk first.
[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
Through the above information, the locked /dev/emcpoweree device is not divided and used.
Partition the device disk
[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 could in certain setups cause problems with:
1) software that runs at boot time (., old versions of LILO)
2) booting and partitioning software from other OSs
(., 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.
The above is completed partitioning of the disk. It is divided into four main partitions, three 500G, and the last part remains one area.
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 ]
On another node, scan the newly added disk
[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
In Node 1, use the sysasm user to log in to the instance
[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, 2011, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Real Application Clusters and Automatic Storage Management options
Check 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.
Batch expansion ASM disk group
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 the query v$asm_operation has no data, it means that the IO automatic balance has been completed
SQL> select * from v$asm_operation;
no rows selected
Check the disk group 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 404170 0 404170 0 N ARCH/
MOUNTED NORMAL N 512 4096 1048576 30717 29791 10239 9776 0 Y CRS/
MOUNTED EXTERN Y 512 4096 1048576 3686390 2097561 0 2097561 0 N DATA/
DATA/ has been expanded and has nearly 2T of remaining space, which can meet the needs of business for a period of time.
5. Final feedback
message:
Yesterday, the problem of "insufficient ASM disk group space" in Guizhou. Later, I found that the "disk array" had unused space, about 2T, which has been added for ASM.
It can meet the disk space needs for a period of time.
The resident engineer expressed his gratitude.
At this point, the task record has been completed.
************************************* Blue's Growth Record Series***************************************************************
Original work, from the "Deep Blue Blog" blog, welcome to reprint, please be sure to indicate the source when reprinting (/huangyanlong）。
Blue's Growth Story - Chasing DBA (1): Running on the road and advancing to Shandong
Blue's Growth Story - Chasing DBA (2): Install! Install! The long-lost memories have caused me to re-understand DBA
Blue's Growth Story - Chasing DBA (3): Operation on antiques, data import and export become a problem
Blue's Growth Story - Chasing DBA (4): Recalling the sadness of a young man, and then exploring the installation of oracle (10g, 11g under Linux)
Blue's Growth Record - Chasing DBA (5): Not talking about technology and business, annoying application systems
Blue's Growth Story - Chasing DBA (6): Doing things and being a person: Small skills, big people
Blue's Growth Story - Chasing DBA (7): Basic Command, Foundation Stone
Blue's Growth Record - Chasing DBA (8): Retrieve the SP report and recall the STATSPACK experiment of oracle
Blue's Growth Story - Chasing DBA (9): National Day is coming, chasing DBA, new plans, new start
Blue's Growth Record - Chasing DBA (10): Flying Knife Self-defense, Familiarity rather than expertise: Flirting with Middleware Websphere
Lan's Growth Story - Chasing DBA (11): The comfort after returning home, I woke up in a daze
Blue's Growth Story - Chasing DBA (12): SQL from Seven Days and Seven Days to Seven Days
Blue's Growth Story - Chasing DBA (13): Coordinating hardware manufacturers, six stories: "Servers, Storage, Switches..."
Blue's Growth Story - Chasing DBA (14): Unforgettable "cloud" end, the starting hadoop deployment
Blue's Growth Story - Chasing DBA (15): I thought FTP was "simple", but who would have thought it was full of twists and turns
Blue's Growth Story - Chasing DBA (16): DBA also drinks and is beaten
Blue's Growth Story - Chasing DBA (17): Is it sharing or consumption, and learn to grow in the post-IOE era
******************************************************************************************************************
************************************************ Football and oracle series **********************************************************************
Original work, from the "Deep Blue Blog" blog, welcome to reprint, please be sure to indicate the source when reprinting (/huangyanlong）。
Football and Oracle Series (1): The 32 princes are pointing troops, oracle32 process alliance Group A of Brazil's SMON process overall view
Football and Oracle Series (2): Brazil's opening match preview, oracle architecture talk
Football and Oracle Series (3): Oracle Process Ranking, the second round of the World Cup is about to end!
Football and Oracle Series (4): From Brazil's defeat to Germany, thinking about the difference in RAC topology comparison!
Football and Oracle Series (5): The missing directX library in the fifa14 game is compared to the rpm package of oracle!
Football and Oracle Series (6): Asian Cup with Jianku - Come on the Chinese team
******************************************************************************************************************