Durham Oracle: February 2018

SAP HANA / Oracle

In the ERP world I have always been in the Oracle E-Business camp. Nothing against SAP just never had any reason to work with it but know a few lads who do. Obviously, Oracle 'E-Biz' has an Oracle database at the back of it and most SAP sites use Oracle too (as far as I can tell) even though SAP is database independent. I feel SAP and E-Biz is a bit like the Solaris or Linux, X-Box or PS4 debate. It really depends on what you want.

However HANA which is a in memory "vertical" database and is supposed to be very quick can be used with SAP and is prob one of the reasons why you might go down the SAP road. Oracle have had "TimesTen" around for a long time but I have never worked anywhere that would pay for it or had a need to use it.

There is a lot of debate on the web but if anyone has any real world experience of both please let me know. Really quick in-memory databases are great but for a lot of sites they want a Ford Transit for load not a Ferrari for speed.

http://www.oracle.com/technetwork/database/options/dbim-vs-sap-hana-2215625.pdf

Grid Infrastructure Removal / ASM disks Oracle VM box

These notes from Oracle Base can be used to remove a failed Grid Infrastructure install but I have added the piece below as I hit a problem with ASM disks on Oracle Virtual box.

https://oracle-base.com/articles/rac/clean-up-a-failed-grid-infrastructure-installation

Following a failed RAC attack install, tried to delete and then add the asm disks.

However, Oracle Virtaul box holds onto the disk information and won't let you add a disk with the same name. The solution is to use vboxmanage from the cmd prompt.

In the example below, removes the 5gig ASM disk.

C:\Program Files\Oracle\VirtualBox>vboxmanage list hdds

UUID: a35460c0-eef6-4bc5-a706-66dbddce04d7
Parent UUID: base
State: inaccessible
Type: shareable
Location: C:\racattack12c\asm1.vdi
Storage format: VDI
Capacity: 5120 MBytes
Encryption: disabled

UUID: 266df39c-8e4b-40cf-8c34-500d69ee1ab9
Parent UUID: base
State: created
Type: normal (base)
Location: C:\racattack12c\collabn2.vdi
Storage format: VDI
Capacity: 30720 MBytes
Encryption: disabled

UUID: cfec563f-a1a2-489c-922c-808fbaa3e984
Parent UUID: base
State: created
Type: normal (base)
Location: C:\racattack12c\collabn1.vdi
Storage format: VDI
Capacity: 30720 MBytes
Encryption: disabled

C:\Program Files\Oracle\VirtualBox>vboxmanage closemedium a35460c0-eef6-4bc5-a706-66dbddce04d7 --delete

0%...10%...20%...30%...40%...50%...60%...70%...80%...90%...100%

C:\Program Files\Oracle\VirtualBox>vboxmanage list hdds

UUID: 266df39c-8e4b-40cf-8c34-500d69ee1ab9
Parent UUID: base
State: created
Type: normal (base)
Location: C:\racattack12c\collabn2.vdi
Storage format: VDI
Capacity: 30720 MBytes
Encryption: disabled

UUID: cfec563f-a1a2-489c-922c-808fbaa3e984
Parent UUID: base
State: created
Type: normal (base)
Location: C:\racattack12c\collabn1.vdi
Storage format: VDI
Capacity: 30720 MBytes
Encryption: disabled

--Syntax to resize disk

C:\Program Files\Oracle\VirtualBox>.\VBoxManage.exe modifyhd "C:\Users\206545\VirtualBox VMs\collabn1\collabn1.vdi" --resize 20480

0%...10%...20%...30%...40%...50%...60%...70%...80%...90%...100%

Headless

In the good old days, most Unix boxes needed to have a keyboard, video and mouse plugged in or they got most upset. One solution is to use KVM over IP from the likes of Avocent. You plug a dongle into the back of the server and use the network to access the box remotely.

Another option is to go headless (needs some config changes) by enabling ssh, VNC, etc then disconnecting the monitor, keyboard and mouse. Power up a second computer on the same network, start the ssh client and log on to the headless server. You will find little difference between working from the console and the remote access connection. I only mention this as it a term a lot of IT people
never come across. In the Windows world there is something called "Server Core" installation and I think that is the closest you can get to a headless server. If anyone with a Windows background would like to enlighten us please feel free.

One thing I would advise is to fit your racks with switched rack power distribution Units (PDUs) to provide advanced load monitoring combined with remote on/off switching control of individual outlets for power cycling, delayed power sequencing, and outlet use management. At least then
you can try the switch it back off and back on again approach (handy on Sunday mornings or 3am call outs) but be aware this is last resort, always shutdown gracefully if you can.

Guest Additions - Oracle VM Box Linux

Notes for Guest Additions after installing Linux on Oracle VM Box.

Downloaded the .iso image from here :

http://download.virtualbox.org/virtualbox/5.2.6/

" Guest Additions are designed to be installed inside a virtual machine after the guest operating system has been installed. They consist of device drivers and system applications that optimize the guest operating system for better performance and usability."

Check your version of virtualbox to match the guest additions i.e. for me 5.2.6

Rather than create a virtual cdrom drive for the .iso file used WinSCP to copy onto
Linux machine. From that directory used the following :

[root@collabn1 guest]# mount -o loop VBoxGuestAdditions_5.2.6.iso /guest_additions
[root@collabn1 guest]# cd /guest_additions
[root@collabn1 guest_additions]# ls -la
total 49579
dr-xr-xr-x 6 root root 2226 Jan 15 13:59 .
dr-xr-xr-x. 27 root root 4096 Feb 25 14:05 ..
dr-xr-xr-x 2 root root 480 Jan 15 13:59 32Bit
dr-xr-xr-x 2 root root 480 Jan 15 13:59 64Bit
etc....
-r-xr-xr-x 1 root root 10275944 Jan 15 13:53 VBoxWindowsAdditions-x86.exe
[root@collabn1 guest_additions]#

You can run the guest additions from there.

DevOps / Agile

"DevOps-tear-down-that-wall"

If like me you don't wan't to be sitting watching videos when you would rather be working -
the podcasts from Command Line Heroes could be for you.

Just put your headphones on and learn loads of things that you probably did not know.
I especially liked the podcast on Agile.

"Agile-revolution"

Put some magic into your database.

Click below for some magic - not really but with a title like that how could I resist.

Going back a long time but still does a job.

Oracle Tip: Put some magic into your database Discover how to check the magic value of a BLOB and return a MIME type and encoding information by directly examining the data.

ZFS Administration

Really handy facility on Solaris is the ability to use ZFS and take snapshot prior to do any work.

Brilliant if you have a training box and want to roll the course back to the start of the week once the student have wrecked the data.

http://docs.oracle.com/cd/E23823_01/html/819-5461/index.html

+++++++++++++++++++++++++++++++++

rootpool/export/home 20G 18G 156M 100% /export/home

# zfs list

NAME USED AVAIL REFER MOUNTPOINT

rootpool 58.1G 216G 98K /rootpool

rootpool@20130611 18K - 95K -

rootpool/ROOT 17.9G 216G 18K legacy

rootpool/ROOT@20130611 0 - 18K -

rootpool/ROOT/s10s_u6wos_07b 87.4M 216G 13.3G /

rootpool/ROOT/s10s_u6wos_07b@20130611 0 - 13.3G -

rootpool/ROOT/sol10u10 17.8G 216G 12.0G /

rootpool/ROOT/sol10u10@sol10u10 5.20G - 13.3G -

rootpool/ROOT/sol10u10@20130611 234M - 11.6G -

rootpool/export 20.0G 216G 22K /export

rootpool/export@20130611 1K - 20K -

rootpool/export@20130621 1K - 20K -

rootpool/export@20140223 18K - 22K -

rootpool/export@20140430 18K - 22K -

rootpool/export/home 20.0G 0 18.2G /export/home

rootpool/export/home@20130611 5.35M - 6.10G -

rootpool/export/home@20130621 5.33M - 6.10G -

rootpool/export/home@20140223 102M - 17.2G -

rootpool/export/home@20140430 8.27M - 18.7G -

rootpool/test 100M 216G 100M /rootpool/test

rootpool/test@20130611 16K - 100M -

rootpool/zonenfs 20.0G 216G 20.0G /rootpool/zonenfs

safefmepool 4.06G 15.5G 31K /safefmepool

safefmepool/safefme 4.06G 15.5G 4.06G /SAFEFME

# zfs destroy -r rootpool/export/home@20130611

# zfs destroy -r rootpool/export/home@20130621

Etc…

Now

rootpool/export/home 20G 18G 1.8G 91% /export/home

DO NOT RUN ZFS DESTROY WITHOUT THE SNAPSHOT NAME I.E. USING THE @ BIT – THIS IS VERY BAD!!!

If you have zfs snapshots running for very long periods of time they record changes so they can go back to the time they were created.

I reckon we could not remove the files or do anything as the zfs snapshot could “not keep up

+++++++++++++++++++++++++++++++++++++++++++++=

Zfs swapfile

# swap -l

swapfile dev swaplo blocks free

/dev/zvol/dsk/rpool/swap 181,1 8 2097144 2054536

zfs create –V 3G rpool/swap3

swap -a /dev/zvol/dsk/rpool/swap3

+++++++++++++++++++++++++++++++++

root@whisky > zfs snapshot -r STORAGE/whisky01@jan19th2011

Removing a snapshot:

zfs destroy -r diskpool/niagra03@nov8th

Make SURE the @snapshot_name is specified or this will be BAD.

Adding a swap file to a zfs filesystem

zfs create -V 1gb rpool/extraswap

find

This will create a swap file called extraswap in the zpool rpool. You will need to identify where

this file has actually been created as it will be a link. Once you find the actual file you can add it with the following command :

swap -a /dev/zvol/dsk/rpool/extraswap

++++++++++++++++++++++++++++++++++++++++++++

ZPOOL CREATION AND MICELLANEOUS

-------------------------------

zpool create tank c1t0d0 c1t1d0

zpool create tank mirror c1t0d0 c1t1d0

zpool create tank raidz c1t0d0 c2t0d0 c3t0d0 c4t0d0 c5t0d0

(can be done with slices but they have to be created first).

zpool create -n tank mirror c1t0d0 c2t0d0 (practice create)

zpool create -m /export/zfs home c1t0d0 (specify the mount point)

zpool add [-n] tank mirror c4t0d0 c5t0d0 (add extra space)

zpool attach tank c1t1d0 c2t0d0 (add extra space to existing stuff)

zpool detach tank c1t1d0

zpool offline [-t] tank c1t0d0 to replace a disk or make unavailable

the state is persistant across reboots)

zpool online tank c1t0d0

zpool clear tank (clear error messages in pool)

zpool clear tank c1t0d0 (clear error messages in disk)

zpool replace tank c1t0d0 c2d0s0 (replace 1st with 2nd HS)

zfs create tank/home/zfs set mountpoint=/export/zfs tank/home

zfs set sharenfs=on tank/home

zfs set comporession=on tank/home

get all tank/home

zfs create tank/home/psco0508

zfs set quota=5G tank/home/psco0508

root@irene.> zfs create diskpool/december01

root@irene > zfs list

NAME USED AVAIL REFER MOUNTPOINT

diskpool 372G 722G 4.88G /diskpool

diskpool/december01 31K 722G 31K /diskpool/december01

root@irene > zfs set mountpoint=/december01 diskpool/december01

Basic ZPOOL

zpool list

zpool list tank

zpool list -o name,size

zpool list -Ho name,size no headings and tabbed fields good for scripting)

I/O ZPOOL

zpool iostat (not very acurate)

zpool iostat tank 2 (every 2 seconds indefinately)

zpool iostat tank 2 3 (every 2 secs for 3 iterations)

zpool iostat -v (virtual devices as well as I/O)

Health ZPOOL

zpool status -x short output

zpool status -v tank

zpool destroy [-f] tank (BE CAREFUL)

zfs list

ZILs Adding and Removal

-----------------------

zpool status (to check what there)

zpool add diskpool log|cache c1t2d0 c1t3d0 (add a ZIL)

zpool remove diskpool c1t2d0 c1t3d0 (remove disks from a ZIL)

Migration

---------

zpool export [-f] tank export the pool (no longer visable and unmounts FS in pool)

zpool import get a list by default from /dev/dsk

zpool import -d /file look for exports in other directories

zpool import tank if several have the same name use the number to specify.

zpool import 6223921996255991199

zpool import dozer dozernew import and rename pool

DESTROYING and RECREATING

-------------------------

zpool destroy tank

zpool import -Df tank without -f it doesn't actually come back.

zpool status tank

It is possible to bring back a pool if a device is missing, so long as its not critical to the operation.

ZPOOL UPGRADE

-------------

zfs upgrade or zfs upgrade -v

If using RAID-Z then generally keep the number of disks in each group in single figures a this works better.

HOT SPARE SECTION

-----------------

zpool add disk_pool spare c1t14d0 add a disk to a pool as a hot_spare

zpool remove disk_pool c1t14d0 remove a disk from a pool as a hot_spare

++++++++++++++++++++++++++++++++++++++++++++

Recreate error when pool fills up :

# zfs list

NAME USED AVAIL REFER MOUNTPOINT

rpool 21.6G 9.65G 42.5K /rpool

rpool/ROOT 7.21G 9.65G 31K legacy

rpool/ROOT/s10_1-13 7.21G 9.65G 7.21G /

rpool/dump 1.00G 9.65G 1.00G -

rpool/export 844M 9.65G 32K /export

rpool/export/home 844M 9.65G 844M /export/home

rpool/oracle 8.41G 9.65G 8.41G /oracle

rpool/swap 1.06G 9.71G 1.00G -

rpool/swap3 3.09G 9.74G 3.00G -

i.e. 9881.6 Meg

From the /export/home area

# mkfile 9600m bigfile

# zfs snapshot -r rpool/export/home@may23rd

Then fill up what is left

# mkfile 100m smallfile

# mkfile 100m smallfile2

# mkfile 77m smallfile3

# df -h

Filesystem size used avail capacity Mounted on

rpool/export/home 31G 10G 0K 100% /export/home

Nothing left

Remove all the files - zfs does not care about the 3 files after the snapshot but does need 9600m from before the snapshot

So we free up approx. 276M but we have removed 9.65Gig

# zfs list

NAME USED AVAIL REFER MOUNTPOINT

rpool 31.0G 276M 42.5K /rpool

rpool/ROOT 7.21G 276M 31K legacy

rpool/ROOT/s10_1-13 7.21G 276M 7.21G /

rpool/dump 1.00G 278M 1.00G -

rpool/export 10.2G 276M 32K /export

rpool/export/home 10.2G 276M 844M /export/home

rpool/export/home@may23rd 9.38G - 10.2G -

rpool/oracle 8.41G 276M 8.41G /oracle

rpool/swap 1.06G 340M 1.00G -

rpool/swap3 3.09G 373M 3.00G -

Get rid of the snapshot

# zfs destroy -r rpool/export/home@may23rd

et voilla...

# zfs list

NAME USED AVAIL REFER MOUNTPOINT

rpool 21.6G 9.65G 42.5K /rpool

rpool/ROOT 7.21G 9.65G 31K legacy

rpool/ROOT/s10_1-13 7.21G 9.65G 7.21G /

rpool/dump 1.00G 9.65G 1.00G -

rpool/export 844M 9.65G 32K /export

rpool/export/home 844M 9.65G 844M /export/home

rpool/oracle 8.41G 9.65G 8.41G /oracle

rpool/swap 1.06G 9.71G 1.00G -

rpool/swap3 3.09G 9.74G 3.00G -

How to add disks to ASM

Introduction

This post details the steps carried to add three disks to a DATA diskgroup in ASM.

The three disks were 500Gb each in size (approx).

After the RFC / CAB was raised, ‘presented’ the disks to the two database servers.

The next steps were to add the new storage to ASM, thereby making them
available to Oracle.

IMPORTANT:

Most of the steps here can be carried out by logging onto the database server as ‘oracle’,
followed by ‘sqlplus / as sysdba’

However, the step of actually adding the disks needs to be done using
‘sqlplus / as sysasm’.

Therefore – for consistency, and to keep things simple – it is advisable to carry out all of
the steps in this document by logging onto the DB server as the ‘grid’ user, and then onto
the ASM instance using sqlplus / as sysasm

Step 1 – check the space currently in ASM

Log onto DB server (either one) as grid.

sqlplus / as sysasm

set lines 120

col name format a25

col group_number format 999 heading GROUP

col name format a20

select GROUP_NUMBER

,NAME

,ROUND(TOTAL_MB/1024,1) TOTAL_GB

,ROUND(((TOTAL_MB - FREE_MB)/1024),1) USED_GB

,ROUND(FREE_MB/1024,1) FREE_GB

,ROUND((100-(FREE_MB/TOTAL_MB)*100),2) "% Used"

from V$ASM_DISKGROUP

order by group_number

e.g.

SQL> /

GROUP NAME TOTAL_GB USED_GB FREE_GB % Used

----- -------------------- ---------- ---------- ---------- ----------

1 DATA 3366.7 2679.4 687.3 79.59

2 FRA 959.8 473.3 486.5 49.32

SQL>

Step 2 – Check that the disks have been presented to the OS:

db01 /dev/asmdisks > pwd

/dev/asmdisks

cdepld2prsodb01 /dev/asmdisks (rpap1) > ls -l

total 0

brw-rw----. 1 grid asmadmin 253, 8 Mar 11 14:43 asm1

brw-rw----. 1 grid asmadmin 253, 6 Mar 11 14:43 asm10

brw-rw----. 1 grid asmadmin 253, 32 Mar 11 14:43 asm11

brw-rw----. 1 grid asmadmin 253, 31 Mar 11 14:39 asm12

brw-rw----. 1 grid asmadmin 253, 34 Mar 11 14:39 asm13

brw-rw----. 1 grid asmadmin 253, 30 Mar 11 14:39 asm14

brw-rw----. 1 grid asmadmin 253, 9 Mar 11 14:43 asm2

brw-rw----. 1 grid asmadmin 253, 7 Mar 11 14:43 asm3

brw-rw----. 1 grid asmadmin 253, 11 Mar 11 14:43 asm4

brw-rw----. 1 grid asmadmin 253, 10 Mar 11 14:43 asm5

brw-rw----. 1 grid asmadmin 253, 12 Mar 11 14:43 asm6

brw-rw----. 1 grid asmadmin 253, 3 Mar 11 14:43 asm7

brw-rw----. 1 grid asmadmin 253, 5 Mar 11 14:43 asm8

brw-rw----. 1 grid asmadmin 253, 4 Mar 11 14:43 asm9

cdepld2prsodb01 /dev/asmdisks (rpap1) >

Make sure the new ones have the same ownership and permissions as the existing ones.

→there used to be 11, now there are14.

Step 3 – Check which disks ASM can actually see

Set lines 200 pages 999

col disk_group format a15

col DISK_NAME format a15

col path format a50

select g.name disk_group

,d.DISK_NUMBER

,d.name disk_name

,d.PATH

,d.TOTAL_MB

,d.header_status

from v$asm_disk d,

v$asm_diskgroup g

where d.GROUP_NUMBER = g.GROUP_NUMBER (+)

order by g.name, d.disk_number

1 select g.name disk_group

2 ,d.DISK_NUMBER

3 ,d.name disk_name

4 ,d.PATH

5 ,d.TOTAL_MB

6 ,d.header_status

7 from v$asm_disk d,

8 v$asm_diskgroup g

9 where d.GROUP_NUMBER = g.GROUP_NUMBER (+)

10* order by g.name, d.disk_number

SQL>

SQL> /

DISK_GROUP DISK_NUMBER DISK_NAME PATH TOTAL_MB HEADER_STATU

--------------- ----------- --------------- ------------------------- ---------- ------------

DATA 0 DATA_0000 /dev/asmdisks/asm1 492500 MEMBER

DATA 1 DATA_0001 /dev/asmdisks/asm2 492500 MEMBER

DATA 2 DATA_0002 /dev/asmdisks/asm3 492500 MEMBER

DATA 3 DATA_0003 /dev/asmdisks/asm4 492500 MEMBER

DATA 4 DATA_0004 /dev/asmdisks/asm5 492500 MEMBER

DATA 5 DATA_0005 /dev/asmdisks/asm6 492500 MEMBER

DATA 6 DATA_0006 /dev/asmdisks/asm11 492500 MEMBER

FRA 0 FRA_0000 /dev/asmdisks/asm10 245700 MEMBER

FRA 1 FRA_0001 /dev/asmdisks/asm7 245700 MEMBER

FRA 2 FRA_0002 /dev/asmdisks/asm8 245700 MEMBER

FRA 3 FRA_0003 /dev/asmdisks/asm9 245700 MEMBER

0 /dev/asmdisks/asm12 0 CANDIDATE

1 /dev/asmdisks/asm14 0 CANDIDATE

2 /dev/asmdisks/asm13 0 CANDIDATE

14 rows selected.

SQL>

→the highlighted ones are the new ones that have not been added to a diskgroup yet.

Step 4 – Add the first disk

SQL> l

1 ALTER DISKGROUP DATA

2 ADD DISK '/dev/asmdisks/asm12'

3* NAME DATA_0007 rebalance power 0

SQL> /

Diskgroup altered.

SQL>

Step 5 – Run the two queries again:

SQL> /

GROUP NAME TOTAL_GB USED_GB FREE_GB % Used

----- -------------------- ---------- ---------- ---------- ----------

1 DATA 3847.7 2680.8 1166.9 69.67

2 FRA 959.8 475.1 484.7 49.5

SQL>

And:

SQL> /

DISK_GROUP DISK_NUMBER DISK_NAME PATH TOTAL_MB HEADER_STATU

--------------- ----------- --------------- ------------------------- ---------- ------------

DATA 0 DATA_0000 /dev/asmdisks/asm1 492500 MEMBER

DATA 1 DATA_0001 /dev/asmdisks/asm2 492500 MEMBER

DATA 2 DATA_0002 /dev/asmdisks/asm3 492500 MEMBER

DATA 3 DATA_0003 /dev/asmdisks/asm4 492500 MEMBER

DATA 4 DATA_0004 /dev/asmdisks/asm5 492500 MEMBER

DATA 5 DATA_0005 /dev/asmdisks/asm6 492500 MEMBER

DATA 6 DATA_0006 /dev/asmdisks/asm11 492500 MEMBER

DATA 7 DATA_0007 /dev/asmdisks/asm12 492500 MEMBER

FRA 0 FRA_0000 /dev/asmdisks/asm10 245700 MEMBER

FRA 1 FRA_0001 /dev/asmdisks/asm7 245700 MEMBER

FRA 2 FRA_0002 /dev/asmdisks/asm8 245700 MEMBER

FRA 3 FRA_0003 /dev/asmdisks/asm9 245700 MEMBER

0 /dev/asmdisks/asm14 0 CANDIDATE

1 /dev/asmdisks/asm13 0 CANDIDATE

14 rows selected.

SQL>

Step 6 – Check ASM alert log

/u01/app/grid/diag/asm/+asm/+ASM1/trace/alert_+ASM1.log

Check no errors.

e.g.

Wed Mar 11 14:57:30 2015

SQL> ALTER DISKGROUP DATA

ADD DISK '/dev/asmdisks/asm12'

NAME DATA_0007 rebalance power 0

Wed Mar 11 14:57:30 2015

NOTE: GroupBlock outside rolling migration privileged region

NOTE: Assigning number (1,7) to disk (/dev/asmdisks/asm12)

NOTE: requesting all-instance membership refresh for group=1

NOTE: Disk 7 in group 1 is assigned fgnum=8

NOTE: initializing header (replicated) on grp 1 disk DATA_0007

NOTE: initializing header on grp 1 disk DATA_0007

NOTE: requesting all-instance disk validation for group=1

Wed Mar 11 14:57:32 2015

NOTE: skipping rediscovery for group 1/0xc6785ea9 (DATA) on local instance.

Wed Mar 11 14:57:32 2015

NOTE: requesting all-instance disk validation for group=1

Wed Mar 11 14:57:32 2015

NOTE: skipping rediscovery for group 1/0xc6785ea9 (DATA) on local instance.

Wed Mar 11 14:57:32 2015

GMON updating for reconfiguration, group 1 at 12 for pid 39, osid 6241

Wed Mar 11 14:57:32 2015

NOTE: group 1 PST updated.

Wed Mar 11 14:57:32 2015

NOTE: membership refresh pending for group 1/0xc6785ea9 (DATA)

Wed Mar 11 14:57:32 2015

GMON querying group 1 at 13 for pid 21, osid 7028

NOTE: cache opening disk 7 of grp 1: DATA_0007 path:/dev/asmdisks/asm12

GMON querying group 1 at 14 for pid 21, osid 7028

Wed Mar 11 14:57:34 2015

SUCCESS: refreshed membership for 1/0xc6785ea9 (DATA)

Wed Mar 11 14:57:34 2015

SUCCESS: ALTER DISKGROUP DATA

ADD DISK '/dev/asmdisks/asm12'

NAME DATA_0007 rebalance power 0

NOTE: Attempting voting file refresh on diskgroup DATA

Step 7 – check DB alert log

Again, check for any errors:

e.g.

Wed Mar 11 14:57:34 2015

NOTE: Assigning number (1,7) to disk (/dev/asmdisks/asm12)

SUCCESS: disk DATA_0007 (7.3915951733) added to diskgroup DATA

Wed Mar 11 14:58:23 2015

Step 8 – check if rebalancing is taking place:

SQL> select * from v$asm_operation ;

GROUP OPERA PASS STAT POWER ACTUAL SOFAR EST_WORK
EST_RATE EST_MINUTES

----- ----- --------- ---- ---------- ---------- ---------- ---------- ---------- -----------

ERROR_CODE CON_ID

-------------------------------------------- ----------

1 REBAL REBALANCE WAIT 0

1 REBAL COMPACT WAIT 0

SQL>

→the above is correct, since we have ‘disabled’ automatic rebalancing by using the ‘power 0’ syntax.

Step 9 – add the remaining disks

SQL> l

1 ALTER DISKGROUP DATA

2 ADD DISK '/dev/asmdisks/asm13'

3* NAME DATA_0008 rebalance power 0

SQL> /

Diskgroup altered.

SQL>

SQL> l

1 ALTER DISKGROUP DATA

2 ADD DISK '/dev/asmdisks/asm14'

3* NAME DATA_0009 rebalance power 0

SQL> /

Diskgroup altered.

SQL>

SQL> /

GROUP NAME TOTAL_GB USED_GB FREE_GB % Used

----- -------------------- ---------- ---------- ---------- ----------

1 DATA 4809.6 2680.8 2128.8 55.74

2 FRA 959.8 475.9 483.8 49.59

SQL>

SQL> /

DISK_GROUP DISK_NUMBER DISK_NAME PATH TOTAL_MB HEADER_STATU

--------------- ----------- --------------- ------------------------- ---------- ------------

DATA 0 DATA_0000 /dev/asmdisks/asm1 492500 MEMBER

DATA 1 DATA_0001 /dev/asmdisks/asm2 492500 MEMBER

DATA 2 DATA_0002 /dev/asmdisks/asm3 492500 MEMBER

DATA 3 DATA_0003 /dev/asmdisks/asm4 492500 MEMBER

DATA 4 DATA_0004 /dev/asmdisks/asm5 492500 MEMBER

DATA 5 DATA_0005 /dev/asmdisks/asm6 492500 MEMBER

DATA 6 DATA_0006 /dev/asmdisks/asm11 492500 MEMBER

DATA 7 DATA_0007 /dev/asmdisks/asm12 492500 MEMBER

DATA 8 DATA_0008 /dev/asmdisks/asm13 492500 MEMBER

DATA 9 DATA_0009 /dev/asmdisks/asm14 492500 MEMBER

FRA 0 FRA_0000 /dev/asmdisks/asm10 245700 MEMBER

FRA 1 FRA_0001 /dev/asmdisks/asm7 245700 MEMBER

FRA 2 FRA_0002 /dev/asmdisks/asm8 245700 MEMBER

FRA 3 FRA_0003 /dev/asmdisks/asm9 245700 MEMBER

14 rows selected.

SQL>

→all looks good. ASM alert log:

No errors:

DB Alert log:

Also no errors:

Wed Mar 11 15:07:33 2015

NOTE: Assigning number (1,8) to disk (/dev/asmdisks/asm13)

SUCCESS: disk DATA_0008 (8.3915951736) added to diskgroup DATA

Wed Mar 11 15:08:53 2015

NOTE: Assigning number (1,9) to disk (/dev/asmdisks/asm14)

SUCCESS: disk DATA_0009 (9.3915951738) added to diskgroup DATA

Wed Mar 11 15:10:03 2015

Step 10 – Run queries on Node 2

Should return the same results as on Node 1:

SQL> !uname -a

Linux db02.scc.local 2.6.32-431.11.2.el6.x86_64 #1 SMP Mon Mar 3 13:32:45 EST 2014 x86_64 x86_64 x86_64 GNU/Linux

SQL> /

GROUP NAME TOTAL_GB USED_GB FREE_GB % Used

----- -------------------- ---------- ---------- ---------- ----------

1 DATA 4809.6 2681.3 2128.3 55.75

2 FRA 959.8 476.5 483.2 49.65

SQL>

And:

SQL> !uname -a

Linux db02.scc.local 2.6.32-431.11.2.el6.x86_64 #1 SMP Mon Mar 3 13:32:45 EST 2014 x86_64 x86_64 x86_64 GNU/Linux

SQL> /

DISK_GROUP DISK_NUMBER DISK_NAME PATH TOTAL_MB HEADER_STATU

--------------- ----------- --------------- ------------------------- ---------- ------------

DATA 0 DATA_0000 /dev/asmdisks/asm1 492500 MEMBER

DATA 1 DATA_0001 /dev/asmdisks/asm2 492500 MEMBER

DATA 2 DATA_0002 /dev/asmdisks/asm3 492500 MEMBER

DATA 3 DATA_0003 /dev/asmdisks/asm4 492500 MEMBER

DATA 4 DATA_0004 /dev/asmdisks/asm5 492500 MEMBER

DATA 5 DATA_0005 /dev/asmdisks/asm6 492500 MEMBER

DATA 6 DATA_0006 /dev/asmdisks/asm11 492500 MEMBER

DATA 7 DATA_0007 /dev/asmdisks/asm12 492500 MEMBER

DATA 8 DATA_0008 /dev/asmdisks/asm13 492500 MEMBER

DATA 9 DATA_0009 /dev/asmdisks/asm14 492500 MEMBER

FRA 0 FRA_0000 /dev/asmdisks/asm10 245700 MEMBER

FRA 1 FRA_0001 /dev/asmdisks/asm7 245700 MEMBER

FRA 2 FRA_0002 /dev/asmdisks/asm8 245700 MEMBER

FRA 3 FRA_0003 /dev/asmdisks/asm9 245700 MEMBER

14 rows selected.

SQL>

Step 11

Now that we have added all the disks, we can invoke a disk rebalance manually.

Current situation shows that the disks are imbalanced in this diskgroup:

SQL> select DISK_NUMBER

2 ,ROUND(TOTAL_MB/1024,2) TOTAL_GB

3 ,ROUND(FREE_MB/1024,2) FREE_GB

4 ,ROUND((TOTAL_MB - FREE_MB)/1024,2) USED_GB

5 ,ROUND(((TOTAL_MB - FREE_MB)/TOTAL_MB)*100,0) PERCENT_USED

6 from v$asm_disk

7 where GROUP_NUMBER = &1

8 order by 1

SQL>

SQL> /

Enter value for 1: 1

old 7: where GROUP_NUMBER = &1

new 7: where GROUP_NUMBER = 1

DISK_NUMBER TOTAL_GB FREE_GB USED_GB PERCENT_USED

----------- ---------- ---------- ---------- ------------

0 480.96 .16 480.8 100

1 480.96 0 480.96 100

2 480.96 .04 480.91 100

3 480.96 .07 480.89 100

4 480.96 .17 480.79 100

5 480.96 .05 480.91 100

6 480.96 414.49 66.47 14

7 480.96 425.86 55.09 11

8 480.96 425.93 55.03 11

9 480.96 425.95 55 11

10 rows selected.

SQL>

The four highlighted disks are the ones we’ve just added (plus another one I did earlier).

Kick off a manual rebalance:

SQL> alter diskgroup DATA rebalance power 5 ;

Diskgroup altered.

SQL>

We can see that a rebalance is now in progress:

SQL> select * from v$asm_operation;

GROUP_NUMBER OPERA PASS STAT POWER ACTUAL SOFAR EST_WORK EST_RATE EST_MINUTES

------------ ----- --------- ---- ---------- ---------- ---------- ---------- ---------- -----------

ERROR_CODE CON_ID

-------------------------------------------- ----------

1 REBAL REBALANCE RUN 5 5 620 260870 2886 90

1 REBAL COMPACT WAIT 5 5 0 0 0 0

SQL>

When the rebalance has completed, we can see that all of the disks are now
fully balanced:

SQL> select DISK_NUMBER

2 ,ROUND(TOTAL_MB/1024,2) TOTAL_GB

3 ,ROUND(FREE_MB/1024,2) FREE_GB

4 ,ROUND((TOTAL_MB - FREE_MB)/1024,2) USED_GB

5 ,ROUND(((TOTAL_MB - FREE_MB)/TOTAL_MB)*100,0) PERCENT_USED

6 from v$asm_disk

7 where GROUP_NUMBER = &1

order by 1 8

SQL>

SQL> /

Enter value for 1: 1

old 7: where GROUP_NUMBER = &1

new 7: where GROUP_NUMBER = 1

DISK_NUMBER TOTAL_GB FREE_GB USED_GB PERCENT_USED

----------- ---------- ---------- ---------- ------------

0 480.96 167.8 313.16 65

1 480.96 167.82 313.13 65

2 480.96 167.82 313.13 65

3 480.96 167.82 313.14 65

4 480.96 167.83 313.13 65

5 480.96 167.83 313.13 65

6 480.96 167.84 313.12 65

7 480.96 167.84 313.12 65

8 480.96 167.84 313.12 65

9 480.96 167.83 313.13 65

10 rows selected.

SQL>

Notes

This was for a system using 2Tb disks - had to set the compatibility to 12.2 for both asm and rdbms see post on this.

[root@aodw01 ~]# lvmdiskscan

/dev/mapper/NFPSADWGGDATA01S [    <300.04 GiB]
/dev/mapper/NFPSADWORADATA03S [       2.00 TiB]
/dev/mapper/NFPSADWORADATA04S [       2.00 TiB]
/dev/mapper/NFPSADWORADATA02S [       2.00 TiB]
/dev/mapper/NFPSADWORADATA01S [       2.00 TiB]
/dev/mapper/NFPSADWORARECV01S [       2.00 TiB]

Create oracleasm disk for each of these

[root@usnympmtaodw01 disks]# oracleasm createdisk NFPSADWGGDATA /dev/mapper/NFPSADWGGDATA01S
Writing disk header: done
Instantiating disk: failed

Had to manually chown oracle:oinstall on the disk under /dev/oracleasm/disk - need to check why

--- END ---

Main Pages