TASK: Storage migration for database with asm.Setup Used:
OS : RHEL4 update 4
Database : 10gR2 with two node RAC with ASM.
Extra info: Multipathing
Storage: Currently the storage spaces are from two SAN box. Six TB from one HITACHI-SAN and one-TB from second EMC2-SAN box. And requirement is they have to migrate all the data to EMC2 SAN box to release the space from HITACHI SAN box.
Approach Used in this document can be split into two parts:
Part1: Mark new disk(LUN) as asm disk and assign these disk to existing disks, so that same number of disk are added to disk groups as they exist in current setup to make make sure that there is not any re-balancing issues. Once the balancing operation is complete, old disk can be dropped from disk group and also can be delete from asm disk list.
PART2 : Migrate ocr and voting disk to new storage.
Advantage: This will minimize down-time.
Disadvantage: Performance of live system may go down and asm disk rebalancing will take time depending upon the ASM_POWER_LIMIT init parameter setting.
Current Setup:
Two node RAC, with oracle asm and os multipathing enabled.
Currently database has 7TB storage in 14 luns. ie. each lun of 500GB.
And 6 TB from one Hitachi-SAN, 1 TB from EMC2-SAN this is mirrored to Hitachi.
Finally we have to migrate complete database to new storage (7TB) allocated from EMC2-SAN only.
DB-Servers name/Instance-Name: riddb1/PROD1, riddb2/PROD2
Oracle User os-id: oraprod on both node.
PART1:Step1: login on riddb1 as root:
root@riddb1: ls -ltr /dev/mapper/
output would be like :
brw-rw---- 1 root disk 253, 1 Apr 18 11:57 mpath0
brw-rw---- 1 root disk 253, 4 Apr 18 11:57 mpath1
brw-rw---- 1 root disk 253, 10 Apr 18 11:57 mpath10
brw-rw---- 1 root disk 253, 3 Apr 18 11:57 mpath11
brw-rw---- 1 root disk 253, 12 Apr 18 11:57 mpath12
brw-rw---- 1 root disk 253, 13 Apr 18 11:57 mpath13
brw-rw---- 1 root disk 253, 15 Apr 18 11:57 mpath15
brw-rw---- 1 root disk 253, 6 Apr 18 11:57 mpath16
brw-rw---- 1 root disk 253, 2 Apr 18 11:57 mpath2
brw-rw---- 1 root disk 253, 14 Apr 18 11:57 mpath3
brw-rw---- 1 root disk 253, 11 Apr 18 11:57 mpath4
brw-rw---- 1 root disk 253, 8 Apr 18 11:57 mpath5
brw-rw---- 1 root disk 253, 5 Apr 18 11:57 mpath6
brw-rw---- 1 root disk 253, 9 Apr 18 11:57 mpath7
brw-rw---- 1 root disk 253, 0 Apr 18 11:57 mpath8
brw-rw---- 1 root disk 253, 7 Apr 18 11:57 mpath9
Note : the above are block device.
copy the out put of these.
Step2: Get luns of 500 GB each and now assing these lun to riddb1,ridb2 servers now.
Step3: If require to reboot of servers to luns visible on these server, shutdown instances PROD1 and PROD2 and asm intances +ASM1, +ASM2
Step4: Startup +ASM1, ASM2 and PROD1 and PROD2 instances. And start the application to do the normal production activities.
Step5: Login as root on both servers and issue below command:
root@riddb1: ls -ltr /dev/mapper/
root@riddb2: ls -ltr /dev/mapper/
And you will see that new block devices has been added. This can be differentiated by output of step1 and step 5.
Step6: root@riddb1: cd /etc/init.d/
./oracleasm querydisk ORAVOL1 ORAVOL2 ORAVOL3 ORAVOL4 ORAVOL5 ORAVOL6 ORAVOL7 ORAVOL8 ORAVOL9 ORAVOL10 ORAVOL11 ORAVOL12 ORAVOL13 ORAVOL14 ORAVOL15 ORAVOL16
Disk "ORAVOL1" is a valid ASM disk on device [253, 4]
Disk "ORAVOL2" is a valid ASM disk on device [253, 2]
Disk "ORAVOL3" is a valid ASM disk on device [253, 14]
Disk "ORAVOL4" is a valid ASM disk on device [253, 11]
Disk "ORAVOL5" is a valid ASM disk on device [253, 8]
Disk "ORAVOL6" is a valid ASM disk on device [253, 5]
Disk "ORAVOL7" is a valid ASM disk on device [253, 0]
Disk "ORAVOL8" is a valid ASM disk on device [253, 7]
Disk "ORAVOL9" is a valid ASM disk on device [253, 3]
Disk "ORAVOL10" is a valid ASM disk on device [253, 10]
Disk "ORAVOL11" is a valid ASM disk on device [253, 12]
Disk "ORAVOL12" is a valid ASM disk on device [253, 13]
Unable to open ASM disk "ORAVOL13": No such file or directory
Unable to open ASM disk "ORAVOL14": No such file or directory
Unable to open ASM disk "ORAVOL15": No such file or directory
Unable to open ASM disk "ORAVOL16": No such file or directory
Step7: The last few lines of above result show that, there is no asm disk with name ORAVOL13 onward. So we can create asm disk from ORAVOL13 onward.
Step8: Note down the minor of new devices from out put of step 5: eg: minor of mpath16 is 6 thus dm disk will be dm-6.
Step9: Mark the newly assinged disks as asm disk
[root@riddb1 init.d]# ./oracleasm createdisk ORAVOL13 '/dev/dm-6'
./oracleasm createdisk ORAVOL14 '/dev/dm-xx'
simillarly create other asm disks.
Note: repeat the step 1-9 on riddb2 to mark the new luns on this server as asm disks.
Step10: login as oracle user (oraprod) on riddb1:
oraprod@riddb1$
export ORACLE_SID=+ASM1
sqlplus "/ as sysdba"
Step11: Determine the no. of disks assinged to ORAVG1 and ORAVG2
export ORACLE_SID=+ASM1
sqlplus "/ as sysdba"
select * from v$asm_disk;
select * from v$asm_diskgroup;
So we will add same no. of disk as they exists to both disk groups, so the re-balancing can be done.
alter system set asm_diskstring='ORCL:*' ;
alter diskgroup 'ORAVG1' add disk 'ORCL:ORAVOL13';
alter diskgroup 'ORAVG2' add disk 'ORCL:ORAVOL14';
....
The same way add rest of disks to disk group ORAVG1 and ORAVG2(add same no. of disk in ORAVG1 and ORAVG2 as they exits. But don't repeat this step (step 14 on riddb2.) This will make sure that error free rebalancing.
Step12: To check the rebalancing, run the script asmbalance.sql, this script can be download from metalink. The same script is given below:
====================
/*
* RUN THIS SQL*PLUS SCRIPT WHILE CONNECTED TO AN ASM INSTANCE WITH
* DISKGROUPS MOUNTED. "no rows selected" IF NO DISKGROUPS ARE MOUNTED
*/
set verify off
clear columns
/* THE FOLLOWING ERROR WILL BE REPORTED IF THIS SCRIPT IS RUN ON 10gR1
ORA-01219: database not open: queries allowed on fixed tables/views only
* This script is intended to work with 10gR2 and later. To make it work with
* 10gR1 you must remove the "_stat" in the following two defines by
* uncommenting the redefines that follow. The _stat tables eliminate disk
* header reads, but they were not available in 10.1
*/
define asm_disk = v$asm_disk_stat
define asm_diskgroup = v$asm_diskgroup_stat
/* for 10.1 uncomment the following redefines
define asm_disk = v$asm_disk
define asm_diskgroup = v$asm_diskgroup
*/
/*
* Query to see if any diskgroups are out of balance
*/
column "Diskgroup" format A30
column "Diskgroup" Heading " Columns Described in Script||Diskgroup Name"
/*
* The imbalance measures the difference in space allocated to the fullest and
* emptiest disks in the disk group. Comparision is in percent full since ASM
* tries to keep all disks equally full as a percent of their size. The
* imbalance is relative to the space allocated not the space available. An
* imbalance of a couple percent is reasonable
*/
column "Imbalance" format 99.9 Heading "Percent|Imbalance"
/*
* Percent disk size varience gives the percentage difference in size between
* the largest and smallest disks in the disk group. This will be zero if
* best practices have been followed and all disks are the same size. Small
* differences in size are acceptible. Large differences can result in some
* disks getting much more I/O than others. With normal or high redundancy
* diskgroups a large size varience can make it impossible to reduce the
* percent imbalance to a small value.
*/
column "Varience" format 99.9 Heading "Percent|Disk Size|Varience"
/*
* Minimum percent free gives the amount of free disk space on the fullest
* disk as a percent of the disk size. If the imbalance is zero then this
* represents the total freespace. Since all allocations are done evenly
* across all disks, the minimum free space limits how much space can be
* used. If one disk has only one percent free, then only one percent of the
* space in the diskgroup is really available for allocation, even if the
* rest of the disks are only half full.
*/
column "MinFree" format 99.9 Heading "Minimum|Percent|Free"
/*
* The number of disks in the disk group gives a sense of how widely the
* files can be spread.
*/
column "DiskCnt" format 9999 Heading "Disk|Count"
/*
* External redundancy diskgroups can always be rebalanced to have a small
* percent imbalance. However the failure group configuration of a normal or
* high redundancy diskgroup may make it impossible to make the diskgroup well
* balanced.
*/
column "Type" format A10 Heading "Diskgroup|Redundancy"
select
/*
Name of the diskgroup */
g.name
"Diskgroup",
/*
Percent diskgroup allocation is imbalanced */
100*(max((d.total_mb-d.free_mb)/d.total_mb)-min((d.total_mb-d.free_mb)/d.total_mb))/max((d.total_mb-d.free_mb)/d.total_mb)
"Imbalance",
/*
Percent difference between largest and smallest disk */
100*(max(d.total_mb)-min(d.total_mb))/max(d.total_mb)
"Varience",
/*
The disk with the least free space as a percent of total space */
100*(min(d.free_mb/d.total_mb))
"MinFree",
/*
Number of disks in the diskgroup */
count(*)
"DiskCnt",
/*
Diskgroup redundancy */
g.type
"Type"
from
&asm_disk d ,
&asm_diskgroup g
where
d.group_number = g.group_number and
d.group_number <> 0 and
d.state = 'NORMAL' and
d.mount_status = 'CACHED'
group by
g.name ,
g.type
;
/*
* query to see if there are partner imbalances
*
* This query only returns rows for normal or high redundancy diskgroups.
* You will not get any rows if all your diskgroups are external.
*/
/*
* This query checks how even the partnerships are laid out for the diskgroup.
* If the disks are different sizes or the failure groups are different sizes,
* then the partnerships may not be balanced. Uneven numbers of disks in
* failure groups can result with some disks having fewer partners. Uneven
* disk sizes result in some disks having more partner space than others.
* Primary extents are allocated to a disk based on its size. The secondary
* extents for those primaries are allocated on its partner disks. A disk with
* less partner space will fill up those partners more than a disk with more
* partner space. At the beginning of a rebalance, partnerships may be
* rearranged. The partnerships being dropped are marked as inactive. This
* query only examines active partnerships since it is attempting to
* evaluate problems based on the structure of the diskgroup, not the
* transient state that may exist at the moment. At the completion of a
* successful rebalance the inactive partnerships will be deleted.
*/
/*
* Partner count imbalance gives the difference in the number of partners for
* the disk with the most partners and the disk with the fewest partners. It
* may not be possible for every disk to have the same number of partners
* even when all the failure groups are the same size. However an imbalance
* of more than one indicates that the failure groups are different sizes.
* In any case it maybe impossible to get a percent imbalance of zero when
* some disks have more partners than others.
*/
column "PImbalance" format 99 Heading "Partner|Count|Imbalance"
/*
* Partner space imbalance indicates if some disks have more space in their
* partners than others. The partner space is calculated as a ratio between
* the size of a disk and the sum of the sizes of its active partners. This
* ratio is compared for all the disks. The difference in the highest and
* lowest partner space is reported as a percentage. It is impossible to
* achieve a percent imbalance of zero unless this is zero. An imbalance of
* 10% is acceptible.
* Inactive partners are not considered in this calculation since the
* allocations across an inactive partnership will be relocated by rebalance.
*/
column "SImbalance" format 99.9 Heading "Partner|Space %|Imbalance"
/*
* Failgroup Count reports the number of failure groups. This gives a sense
* of how widely the partners of a disk can be spread across different
* failure groups.
*/
column "FailGrpCnt" format 9999 Heading "Failgroup|Count"
/*
* Inactive Partnership Count reports the number of partnerships which are
* no longer used for allocation. A successful rebalance will eliminate these
* partnerships. If this is not zero then a rebalance is needed or is in
* progress.
*/
column "Inactive" format 9999 Heading "Inactive|Partnership|Count"
select
/*
Name of the diskgroup */
g.name
"Diskgroup",
/*
partner imbalance */
max(p.cnt)-min(p.cnt)
"PImbalance",
/*
total partner space imbalance */
100*(max(p.pspace)-min(p.pspace))/max(p.pspace)
"SImbalance",
/*
Number of failure groups in the diskgroup */
count(distinct p.fgrp)
"FailGrpCnt",
/*
Number of inactive partnerships in the diskgroup */
sum(p.inactive)/2
"Inactive"
from
&asm_diskgroup g ,
( /* One row per disk with count of partners and total space of partners
* as a multiple of the space in this disk. It is possible that all
* partnerships are inactive giving a pspace of 0. To avoid divide by
* zero when this happens for all disks, we return a very small number
* for total partner space. This happens in a diskgroup with only two
* failure groups when one failure group fails. */
select
x.grp grp,
x.disk disk,
sum(x.active) cnt,
greatest(sum(x.total_mb/d.total_mb),0.0001) pspace,
d.failgroup fgrp,
count(*)-sum(x.active) inactive
from
&asm_disk d ,
( /* One row per partner of a disk with partner size. We return
* size of zero for inactive partnerships since they will
* be eliminated by rebalance. */
select
y.grp grp,
y.disk disk,
z.total_mb*y.active_kfdpartner total_mb,
y.active_kfdpartner active
from
x$kfdpartner y,
&asm_disk z
where
y.number_kfdpartner = z.disk_number and
y.grp = z.group_number
) x
where
d.group_number = x.grp and
d.disk_number = x.disk and
d.group_number <> 0 and
d.state = 'NORMAL' and
d.mount_status = 'CACHED'
group by
x.grp, x.disk, d.failgroup
) p
where
g.group_number = p.grp
group by
g.name
;
============================
export ORACLE_SID=+ASM1
sqlplus "/ as sysdba"
sqlplus > @asmbalance.sql
This will show the % of rebalance of disks are pending.
Step12: Now at this time old disks can be dropped from disk group. But these disks (LUN should not be detached from servers riddb1 and riddb2 till rebalancing is complete.)
for this list
oraprod@riddb1:
export ORACLE_SID=+ASM1
sqlplus "/ as sysdba"
alter system set asm_diskstring='ORCL:*' ;
alter diskgroup 'ORAVG1' drop disk 'ORACL:ORAVOL1'.
repeat step this till all old disks of both disk group is dropped.
Step13. Get the asm disk % balance status. Once balancing is complete. The asm old disks can be deleted.
root@riddb1:
cd /etc/init.d
root@riddb1: ./oracleasm listdisks ( to List disks)
and now drop the old asm disk.
./oracleasm deletedisk ORAVOL1 ORAVOL2 ......;
Note: Once the balancing is complete then only delete asm disk. And these LUN can be removed from servers.
PART2:Step14: Now prepare system for replacing OCR and Voting disk.
Identify the newly added LUNs(Block Devices) for OCR and voting disk.
As the LUN size for OCR and Voting disk are 100M+ arround this can be identified from list newly added LUN and size can be varified by fdisk
fdisk -l /dev/dm* |grep -i "Disk /dev/dm-"
Disk /dev/dm-23: 104 MB, 104857600 bytes
Disk /dev/dm-12: 104 MB, 104857600 bytes
ls -ltr /dev/mappers
brw-rw---- 1 oraprod dba 253, 12 Aug 21 17:31 mpath17
brw-rw---- 1 oraprod dba 253, 23 Aug 21 17:31 mpath23
Step14.1: To mark these two new LUNs as raw devices permanently, add entry in
/etc/sysconfig/rawdevices , as follows:
riddb1@root$ cat /etc/sysconfig/rawdevices
/dev/raw/raw1 /dev/mapper/mpath0 #Old raw device
/dev/raw/raw2 /dev/mapper/mpath7 #Old raw device
/dev/raw/raw3 /dev/mapper/mpath17 #New raw device
/dev/raw/raw4 /dev/mapper/mpath23 #New raw device
Note : Once the OCR and voting disk are replace with new raw devices, the old raw devices can be commented in above file, before removal from servers.
To make the effect in this boot,
raw /dev/raw/raw3 /dev/mapper/mpath17
raw /dev/raw/raw4 /dev/mapper/mpath23
Step14.2: format the new raw devices:
dd if=/dev/zero of=/dev/raw/raw3
dd if=/dev/zero of=/dev/raw/raw4
Check the owner and permission on old raw devices and make the same on new raw devices:
Note: Earlier in my case the size of old disks for OCR and voting was 105MB, and newly allocated disk were of size 104MB. So the o
Step14.3: To identify the OCR disk check the '/etc/oracle/ocr.loc' file, which will tell the existing OCR disk.
riddb1@root$ cat /etc/oracle/ocr.loc
ocrconfig_loc=/dev/raw/raw2
The existing raw devices can be verified in: '/etc/sysconfig/rawdevices'
riddb1@roo$ cat /etc/sysconfig/rawdevices
/dev/raw/raw1 /dev/mapper/mpath0
/dev/raw/raw2 /dev/mapper/mpath7
or by using raw -qa
Step 14.4: The existing voting disk can be verified by :
crsctl query css votedisk
As the LUN size for OCR and Voting disk are 100M+ arround this can be identified from list newly added LUN and size can be varified by fdisk
fdisk -l /dev/dm* |grep -i "Disk /dev/dm-"
Disk /dev/dm-23: 104 MB, 104857600 bytes
Disk /dev/dm-12: 104 MB, 104857600 bytes
ls -ltr /dev/mappers
brw-rw---- 1 oraprod dba 253, 12 Aug 21 17:31 mpath17
brw-rw---- 1 oraprod dba 253, 23 Aug 21 17:31 mpath23
14.5. Note: log in as the root user, because root own the OCR files.
Make sure there is a recent copy of the OCR file before making any changes:
ocrconfig –showbackup
If there is not a recent backup copy of the OCR file, an export can be taken for the current OCR file. Use the following command to generate a an export of the online OCR file:
ocrconfig –export
[-s online]
If need to recover using this file, the following command can be used:
ocrconfig -import
To replace OCR device :
ocrconfig -replace ocr /dev/raw/raw3
But this failed as old raw device size and new raw device size are not same. Old one was of 105MB and new one of 104 MB. So it was throwing PROT-1 error.
To overcome this, we have simply imported the backup of ocr to new raw device as follows:
14.6: First replace new raw device in /etc/oracle/ocr.loc to mark this as OCR disk.
[root@riddb1 oracle]# cat ocr.loc
#ocrconfig_loc=/dev/raw/raw2
ocrconfig_loc=/dev/raw/raw4
local_only=FALSE
14.7. Now import backup of OCR disk to new OCR disk (raw4).
ocrconfig -import
If you want to have any OCR mirror:
ocrconfig -replace ocr /dev/raw/rawn
14.8: ADD/DELETE/MOVE Voting Disk
Note: You must be logged in as the oracle user, because oracle own the Voting Disks.
Shutdown the Oracle Clusterware (crsctl stop crs as root) on all nodes before making any modification to the voting disk. Determine the current voting disk location using:
crsctl query css votedisk
Take a backup of all voting disk:
dd if=voting_disk_name of=backup_file_name
The following can be used to restore the voting disk from the backup file created.
dd if=backup_file_name of=voting_disk_name
14.9: To add a Voting Disk, provide the full path including file name.:
crsctl add css votedisk [-force]
14.10: To delete a Voting Disk, provide the full path including file name.:
crsctl delete css votedisk [-force]
14.11: After modifying the voting disk, start the Oracle Clusterware stack on all nodes
crsctl start crs
Verify the voting disk location using
crsctl query css votedisk
14.12. Once the CRS and Voting disks are moved to new raw device, the old can removed.
Step15: Start the database and applications
Note: To make the asm disk re-balaning fast if the load on the server are low, increase the value of asm init parameters 'ASM_POWER_LIMIT' upto number of cpu-1. This improve the asm re-balancing operation performance.
If the the ASM_POWER_LIMIT is set to zero then it will not perform auto re-balancing. In this case to do the re-balancing use:
ALTER DISKGROUP REBALANCE POWER 5;
