ASM DISKGROUP SHOWS USABLE_FILE_MB VALUE IN NEGATIVE

A negative value for USABLE_FILE_MB means that you do not have sufficient free space to tolerate a disk failure. If a disk were to fail, the subsequent rebalance would run out of space before full redundancy could be restored to all files.

The box i was working on was exadata server eighth rack, so it had 3 storage server. Each storage server on an exadata server has 12 cell disk. Grid disk are created within Cell Disks.In a simple configuration, One Grid Disk can be created per Cell Disk and Grid disks are what the storage cell presents to db servers. So basically

GRID DISK = ASM DISK.

When creating disk groups, ASM automatically puts all grid disks from the same storage cell into the same failgroup. The failgroup is then named after the storage cell.

[oracle@test~]$ 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  NORMAL  N         512   4096  4194304  40697856  8464936         13565952        -2550508              0             N  DATA1/
MOUNTED  NORMAL  N         512   4096  4194304    415296   367220           138432          114394              0             Y  DBFS_DG/
MOUNTED  NORMAL  N         512   4096  4194304  10176480  9018276          3392160         2813058              0             N  RECO1/

compute sum Label total_FG of total_mb on FAILGROUP
compute sum Label total of total_mb on report
col diskgroup for a20
col failgroup for a30
col name for a30
select g.name diskgroup, d.failgroup,  d.name, d.total_mb from v$asm_disk d, v$asm_diskgroup g where g.name = 'DATA1' and d.GROUP_NUMBER = g.GROUP_NUMBER order by g.name, d.failgroup;

DISKGROUP            FAILGROUP                      NAME                                   TOTAL_MB
-------------------- ------------------------------ ------------------------------ ----------------
DATA1               CELL01                         DATA1_CD_00_CELL01             2260992
DATA1                                              DATA1_CD_05_CELL01             2260992
DATA1                                              DATA1_CD_03_CELL01             2260992
DATA1                                              DATA1_CD_04_CELL01             2260992
DATA1                                              DATA1_CD_01_CELL01             2260992
DATA1                                              DATA1_CD_02_CELL01             2260992
                     ******************************                                ----------------
                     total_FG                                                              13565952
DATA1               CELL02                         DATA1_CD_01_CELL02             2260992
DATA1                                              DATA1_CD_05_CELL02             2260992
DATA1                                              DATA1_CD_02_CELL02             2260992
DATA1                                              DATA1_CD_03_CELL02             2260992
DATA1                                              DATA1_CD_00_CELL02             2260992
DATA1                                              DATA1_CD_04_CELL02             2260992
                     ******************************                                ----------------
                     total_FG                                                              13565952
DATA1               CELL03                         DATA1_CD_02_CELL03             2260992
DATA1                                              DATA1_CD_05_CELL03             2260992
DATA1                                              DATA1_CD_01_CELL03             2260992
DATA1                                              DATA1_CD_04_CELL03             2260992
DATA1                                              DATA1_CD_03_CELL03             2260992
DATA1                                              DATA1_CD_00_CELL03             2260992
                     ******************************                                ----------------
                     total_FG                                                              13565952
                                                                                   ----------------
total                                                                                      40697856

For DATA1 diskgroup the USABLE_FILE_MB shows value in Negative (-2550508 MB).

SQL> select name, state, type, total_mb, free_mb, required_mirror_free_mb req_free,  usable_file_mb use_mb from v$asm_diskgroup where name = 'DATA1';

NAME                      STATE       TYPE     TOTAL_MB    FREE_MB   REQ_FREE     USE_MB
------------------------- ----------- ------ ---------- ---------- ---------- ----------
DATA1                      MOUNTED     NORMAL   40697856    8464936   13565952   -2550508
                                                                                                              ----------
total                                                                                                           40697856

TOTAL_MB:- Refers to total capacity of the diskgroup
FREE_MB :- Refers to raw free space available in diskgroup in MB.

FREE_MB = (TOTAL_MB – (HOT_USED_MB + COLD_USED_MB))

REQUIRED_MIRROR_FREE_MB :- Indicates how much free space is required in an ASM disk group to restore redundancy after the failure of an ASM disk or ASM failure group.In exadata it is the disk capacity of one failure group.

USABLE_FILE_MB :- Indicates how much space is available in an ASM disk group considering the redundancy level of the disk group.

Its calculated as :-

USABLE_FILE_MB=(FREE_MB – REQUIRED_MIRROR_FREE_MB ) / 2 –> For Normal Redundancy
USABLE_FILE_MB=(FREE_MB – REQUIRED_MIRROR_FREE_MB ) / 3 –> For High Redundancy

Also to note here is ASM diskgroup do not set aside the space based on reuqired_mirror_free_mb. Its merely calculated and used to derive usable_file_mb.

While reading Mos Doc Id 1551288.1 i came across some interesting terms and script which i wanted to share to everyone (atleast some of you who might not have been familiar)

Failure coverage refers to the amount of space in a disk group that will be used to re-mirror data in the event of some storage failure.

1. Disk Failure Coverage :- Refers to having enough free space to allow data to be re-mirrored (rebalanced) after a single disk failure in Normal redundancy.

2. Cell Failure Coverage :- Refers to having enough free space to allow data to be re-mirrored after loss of One entire Cell Disk.

Reserving space in the disk group means that you monitor the disk group to ensure that FREE_MB never goes below minimum amount needed for disk or cell failure coverage.

I ran the script provided in Mos Docid 1551288 and below was the output :-

Description of Derived Values:
One Cell Required Mirror Free MB : Required Mirror Free MB to permit successful rebalance after losing largest CELL regardless of redundancy type
Disk Required Mirror Free MB     : Space needed to rebalance after loss of single or double disk failure (for normal or high redundancy)
Disk Usable File MB              : Usable space available after reserving space for disk failure and accounting for mirroring
Cell Usable File MB              : Usable space available after reserving space for SINGLE cell failure and accounting for mirroring
.  .  .
ASM Version: 11.2.0.4
.  .  .
----------------------------------------------------------------------------------------------------------------------------------------------------
|          |         |     |          |            |            |            |Cell Req'd  |Disk Req'd  |            |            |    |    |       |
|          |DG       |Num  |Disk Size |DG Total    |DG Used     |DG Free     |Mirror Free |Mirror Free |Disk Usable |Cell Usable |    |    |PCT    |
|DG Name   |Type     |Disks|MB        |MB          |MB          |MB          |MB          |MB          |File MB     |File MB     |DFC |CFC |Util   |
----------------------------------------------------------------------------------------------------------------------------------------------------
|DATA1    |NORMAL   |   18| 2,260,992|  40,697,856|  32,233,944|   8,463,912|  14,922,547|   2,761,008|   2,851,452|  -3,229,318|PASS|FAIL|  79.2%|
|DBFS_DG  |NORMAL   |   12|    34,608|     415,296|      48,076|     367,220|     152,275|      59,425|     153,898|     107,472|PASS|PASS|  11.6%|
|RECO1    |NORMAL   |   18|   565,360|  10,176,480|   1,171,220|   9,005,260|   3,731,376|     703,460|   4,150,900|   2,636,942|PASS|PASS|  11.5%|
----------------------------------------------------------------------------------------------------------------------------------------------------
Cell Failure Coverage Freespace Failures Detected. Warning Message Follows.
Enough Free Space to Rebalance after loss of ONE cell: WARNING (However, cell failure is very rare)
.  .  .
Script completed.

https://aprakash.wordpress.com/2014/09/17/asm-diskgroup-shows-usable_file_mb-value-in-negative/

Changing the Hostname and Gateway in Linux Environment

Add IP to the Linux Machine.

Verify the IP current settings

[root@xxx] $route -n

Add IP to the
[root@xxx] $sudo ifconfig eth0 172.16.74.10 netmask 255.255.255.0

Add Default Gateway
[root@xxx] $sudo route add default gw 172.16.74.1 eth0

Add Hostname
Modify the below file as below.
/etc/sysconfig/network

NETWORKING=yes
HOSTNAME="plain.domainname.com"
GATEWAY="172.16.74.1"
GATEWAYDEV="eth0"
FORWARD_IPV4="yes"

Retest the issue.
Reboot the server

VNCSERVER "Command Not Found" in Solaris 11

root@labsys2:/software# pkg install --accept solaris-desktop
           Packages to install: 322
            Services to change:  12
       Create boot environment:  No
Create backup boot environment: Yes

DOWNLOAD                                PKGS         FILES    XFER (MB)   SPEED
Completed                            322/322   46542/46542  556.9/556.9    0B/s


PHASE                                          ITEMS
Installing new actions                   77190/77190
Updating package state database                 Done
Updating package cache                           0/0
Updating image state                            Done
Creating fast lookup database                working -Dec 27 17:35:40 labsys2 sendmail[22098]: [ID 702911 mail.alert] unable to qualify my own domain name (labsys2) -- using short name
Creating fast lookup database                   Done
Updating package cache                           1/1
You have new mail in /var/mail/root
root@labsys2:/software#
root@labsys2:/software# vncserver

You will require a password to access your desktops.

Password:
Verify:
xauth:  file /root/.Xauthority does not exist

New 'labsys2:1 (root)' desktop is labsys2:1

Creating default startup script /root/.vnc/xstartup
Starting applications specified in /root/.vnc/xstartup
Log file is /root/.vnc/labsys2:1.log

root@labsys2:/software#

Debugging RMAN Utility

rman target xx/xx@xx debug all log=mylog trace=mytrace --with catalog 

execute the backup command of controlfile and crosscheck 

rman target / debug all log=mylog trace=mytrace --without catalog 


execute the backup command of controlfile and crosscheck 

Out Of memory , Not Enough Space Solaris 11

Issue:

SQL> startup nomount pfile='initXXX.ora' ;
ORA-27102: out of memory
SVR4 Error: 12: Not enough space
Additional information: 1671
Additional information: 16106127360
Additional information: 64424509440

The Above error appeared When trying to start Oracle 12c on Solaris 11.3, this is published bug

Bug:20635316 - 12C DATABASE INSTANCE STARTUP FAILS WITH ORA-27102: OUT OF MEMORY

Cause:

Solaris using something called OSM , Optimized Shared memory.

Solution:

To avoid this error as workaround just set the parameter in the pfile and retest the issue.

_use_osm=FALSE

Exception occurred: java.sql.SQLException: ORA-28040: No matching authentication protocol

Issue:

while I was running adcfgclone.pl on appsTier and could not connect my database which was upgraded from 10.2.0.5 to 12.1.0.2 

Workaround:

Add SQLNET.ALLOWED_LOGON_VERSION=8 in sqlnet.ora on the db side. 

Retest the issue.

Found running request 37175313 attached to dead manager process.

Issue:
Internal Concurrent Manager was not starting.

PID not found with ID
An error occured in client-side routine afpsmckp for Service Manager FNDSM.  The routine returned code 1.
Check for preceding errors and as well as the service manager log file for further details."

Found running request 37175313 attached to dead manager process.
 Attempting to restart request.

Found dead process: spid=(11181), cpid=(287127), ORA pid=(49), manager=(0/0)

Workaround Tried:

Tried to run the cmclean.sql but it was hanging while updating the table FND_CONCURRENT_PROCESSES

It was found further that the table was locked for DML Operations, identified the lock using below query.

Run as sys user.

select

   c.owner,

   c.object_name,

   c.object_type,

   b.sid,

   b.serial#,

   b.status,

   b.osuser,

   b.machine

from

   v$locked_object a ,

   v$session b,

   dba_objects c

where

   b.sid = a.session_id

and

   a.object_id = c.object_id;

and a.object_name='FND_CONCURRENT_PROCESSES';

Identified the Sid and Serial# and killed the session,

alter system kill session 'sid, serial#' immediate;

Run the cmclean.sql;

Start the Concurrent Managers.