Oracle ASMLib

This blog post is more of a note for myself on configuring ASMLib.

ASMLib is an optional utility that can be used on Linux systems to manage Oracle ASM devices.

ASMLib consists of the following components:

An open source (GPL) kernel module package: kmod-oracleasm
An open source (GPL) utilities package: oracleasm-support
A closed source (proprietary) library package: oracleasmlib

On my Oracle VMBox, I performed the below steps –

1. Check the installed packages related to oracleasm

[root@oel67-12c dev]# rpm -qa | grep oracleasm
[root@oel67-12c dev]#

2. As no rpm were installed, I started installing the packages

[root@oel67-12c dev]# yum install oracleasm-support
Loaded plugins: refresh-packagekit, security, ulninfo
Setting up Install Process
Resolving Dependencies
--> Running transaction check
---> Package oracleasm-support.x86_64 0:2.1.8-1.el6 will be installed
--> Finished Dependency Resolution
..........................
Installed:
  oracleasm-support.x86_64 0:2.1.8-1.el6

Complete!
[root@oel67-12c dev]#
[root@oel67-12c dev]# yum install kmod-oracleasm
Loaded plugins: refresh-packagekit, security, ulninfo
Setting up Install Process
Resolving Dependencies
--> Running transaction check
---> Package kmod-oracleasm.x86_64 0:2.0.8-13.el6_8 will be installed
--> Processing Dependency: kernel >= 2.6.32-642.el6 for package: kmod-oracleasm-2.0.8-13.el6_8.x86_64

...........................
Is this ok [y/N]: y
Downloading Packages:
(1/2): kernel-2.6.32-642.el6.x86_64.rpm
(2/2): kmod-oracleasm-2.0.8-13.el6_8.x86_64.rpm   

...........................

Installed:
  kmod-oracleasm.x86_64 0:2.0.8-13.el6_8

Dependency Installed:
  kernel.x86_64 0:2.6.32-642.el6

Complete!
[root@oel67-12c dev]#

3. Download the oracleasmlib rpm from http://www.oracle.com/technetwork/server-storage/linux/asmlib/rhel6-1940776.html and install it. Without this RPM, you will not have the oracleasm-discover executable


[root@oel67-12c dev]# ls -lrt /media/sf_12cR1/oracleasmlib*
-rwxrwx--- 1 root vboxsf    13336 May 18 21:44 oracleasmlib-2.0.12-1.el6.x86_64.rpm

[root@oel67-12c sf_12cR1]# rpm -ihv oracleasmlib-2.0.12-1.el6.x86_64.rpm
Preparing...                ########################################### [100%]
   1:oracleasmlib           ########################################### [100%]
[root@oel67-12c sf_12cR1]#

4. Verify all the components are installed

[root@oel67-12c sf_12cR1]# rpm -qa | grep oracleasm
oracleasm-support-2.1.8-1.el6.x86_64
oracleasmlib-2.0.12-1.el6.x86_64
kmod-oracleasm-2.0.8-13.el6_8.x86_64
[root@oel67-12c sf_12cR1]# cd 
[root@oel67-12c ~]# which oracleasm
/usr/sbin/oracleasm
[root@oel67-12c ~]#

5. Configure oracleasm — will configure the on-boot properties of the Oracle ASM library driver as well as set the appropriate file permissions.

[root@oel67-12c dev]# oracleasm configure -i
Configuring the Oracle ASM library driver.

This will configure the on-boot properties of the Oracle ASM library
driver.  The following questions will determine whether the driver is
loaded on boot and what permissions it will have.  The current values
will be shown in brackets ('[]').  Hitting  without typing an
answer will keep that current value.  Ctrl-C will abort.

Default user to own the driver interface []: oracle
Default group to own the driver interface []: oinstall
Start Oracle ASM library driver on boot (y/n) [n]: y
Scan for Oracle ASM disks on boot (y/n) [y]: y
Writing Oracle ASM library driver configuration: done
[root@oel67-12c dev]#

The configuration setting done above, can be checked in /etc/sysconfig/oracleasm

[root@oel67-12c dev]# ls -lrt /etc/sysconfig/oracleasm
lrwxrwxrwx 1 root root 24 May 18 22:02 /etc/sysconfig/oracleasm -> oracleasm-_dev_oracleasm

6. Check the status

[root@oel67-12c ~]# oracleasm status
Checking if ASM is loaded: no
Checking if /dev/oracleasm is mounted: no
[root@oel67-12c ~]#

7. Load oracleasm module with the init command

[root@oel67-12c ~]# oracleasm init
Creating /dev/oracleasm mount point: /dev/oracleasm
Loading module "oracleasm": oracleasm
Configuring "oracleasm" to use device physical block size
Mounting ASMlib driver filesystem: /dev/oracleasm
[root@oel67-12c ~]#

8. Verify the oracleasm configuration

a. Perform ‘df –ha’, this should show you that /dev/oracleasmfs is mounted.


[root@oel67-12c ~]# df -ha
Filesystem            Size  Used Avail Use% Mounted on
/dev/mapper/vg_oel6712c-lv_root
                       45G  7.4G   36G  18% /
....................
oracleasmfs              0     0     0    - /dev/oracleasm

b. Execute the ‘lsmod’ command, to show the loaded oracleasm module.

[root@oel67-12c ~]# lsmod | grep oracleasm
Module                  Size  Used by
oracleasm              53591  1

9. Create the partition on disk , if not already done

[root@oel67-12c oracleasm]# lsblk
NAME                           MAJ:MIN RM  SIZE RO TYPE MOUNTPOINT
sda                              8:0    0   50G  0 disk
├─sda1                           8:1    0  500M  0 part /boot
└─sda2                           8:2    0 49.5G  0 part
  ├─vg_oel6712c-lv_root (dm-0) 252:0    0 45.6G  0 lvm  /
  └─vg_oel6712c-lv_swap (dm-1) 252:1    0  3.9G  0 lvm  [SWAP]
sdb                              8:16   0   10G  0 disk
sdc                              8:32   0   12G  0 disk
sdd                              8:48   0   12G  0 disk
sde                              8:64   0   10G  0 disk
sdf                              8:80   0   12G  0 disk
sr0                             11:0    1 55.5M  0 rom  /media/VBOXADDITIONS_5.0.20_106931


[root@oel67-12c disks]# fdisk /dev/sdb
Device contains neither a valid DOS partition table, nor Sun, SGI or OSF disklabel
Building a new DOS disklabel with disk identifier 0x50e6a869.
Changes will remain in memory only, until you decide to write them.
After that, of course, the previous content won't be recoverable.

Warning: invalid flag 0x0000 of partition table 4 will be corrected by w(rite)

WARNING: DOS-compatible mode is deprecated. It's strongly recommended to
         switch off the mode (command 'c') and change display units to
         sectors (command 'u').

Command (m for help): n
Command action
   e   extended
   p   primary partition (1-4)
p
Partition number (1-4):
Value out of range.
Partition number (1-4): 1
First cylinder (1-1305, default 1):
Using default value 1
Last cylinder, +cylinders or +size{K,M,G} (1-1305, default 1305):
Using default value 1305

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

Calling ioctl() to re-read partition table.
Syncing disks.
[root@oel67-12c disks]#
[root@oel67-12c disks]#
[root@oel67-12c disks]#
[root@oel67-12c disks]# lsblk
NAME                           MAJ:MIN RM  SIZE RO TYPE MOUNTPOINT
sda                              8:0    0   50G  0 disk
├─sda1                           8:1    0  500M  0 part /boot
└─sda2                           8:2    0 49.5G  0 part
  ├─vg_oel6712c-lv_root (dm-0) 252:0    0 45.6G  0 lvm  /
  └─vg_oel6712c-lv_swap (dm-1) 252:1    0  3.9G  0 lvm  [SWAP]
sdb                              8:16   0   10G  0 disk
└─sdb1                           8:17   0   10G  0 part
sdc                              8:32   0   12G  0 disk
sdd                              8:48   0   12G  0 disk
sde                              8:64   0   10G  0 disk
sdf                              8:80   0   12G  0 disk
sr0                             11:0    1 55.5M  0 rom /media/VBOXADDITIONS_5.0.20_106931

Perform the same above steps for remaining disk.

10. Once the disk are created, label the disks as ASM disks and give then an ASM name.


[root@oel67-12c disks]# oracleasm createdisk data0101 /dev/sdb1
Writing disk header: done
Instantiating disk: done
[root@oel67-12c disks]# oracleasm createdisk data0102 /dev/sde1
Writing disk header: done
Instantiating disk: done
[root@oel67-12c disks]# ls -lrt
total 0
brw-rw---- 1 oracle oinstall 8, 17 May 18 22:49 DATA0101
brw-rw---- 1 oracle oinstall 8, 65 May 18 22:50 DATA0102

11. Check the configured disk using listdisks

[root@oel67-12c disks]# oracleasm listdisks
DATA0101

12. You can query the disk to check if its a valid ASM Disk

[root@oel67-12c disks]# oracleasm querydisk -d DATA0101
Disk "DATA0101" is a valid ASM disk on device [8,17]

[root@oel67-12c disks]# more /proc/partitions
major minor  #blocks  name
   8    17   10482381 sdb1

[root@oel67-12c sbin]# blkid|grep asm
/dev/sdb1: LABEL="DATA0101" TYPE="oracleasm"
/dev/sde1: LABEL="DATA0102" TYPE="oracleasm"
[root@oel67-12c sbin]#

The [8,17] is the major,minor number of the device with this partition. The value comes from /proc/partitions

major — The major number of the device with this partition. The major number in the /proc/partitions, (3), corresponds with the block device ide0, in /proc/devices.

minor — The minor number of the device with this partition. This serves to separate the partitions into different physical devices and relates to the number at the end of the name of the partition.

#blocks — Lists the number of physical disk blocks contained in a particular partition.

name — The name of the partition.

13. oracleasm-discover


[root@oel67-12c sbin]# oracleasm-discover
-bash: /usr/sbin/oracleasm-discover: /lib/ld-linux.so.2: bad ELF interpreter: No such file or directory

For the above error install/lib/ld-linux.so.2

[root@oel67-12c ~]# yum install /lib/ld-linux.so.2
Loaded plugins: refresh-packagekit, security, ulninfo
Setting up Install Process
public_ol6_latest/filelists                                                                                                                                                             |  61 MB     00:43
Resolving Dependencies
--> Running transaction check
---> Package glibc.i686 0:2.12-1.192.el6 will be installed
Dependencies Resolved

..................
Complete!
[root@oel67-12c ~]#

[root@oel67-12c ~]# ls -lrt /lib/ld-linux.so.2
lrwxrwxrwx 1 root root 10 May 18 23:25 /lib/ld-linux.so.2 -> ld-2.12.so
[root@oel67-12c ~]#
[root@oel67-12c ~]# oracleasm-discover
Using ASMLib from /opt/oracle/extapi/32/asm/orcl/1/libasm.so
[ASM Library - Generic Linux, version 2.0.4 (KABI_V2)]
Discovered disk: ORCL:DATA0101 [20964762 blocks (10733958144 bytes), maxio 512]
Discovered disk: ORCL:DATA0102 [20964762 blocks (10733958144 bytes), maxio 512]
[root@oel67-12c ~]#
Advertisements

PRCA-1057 : Failed to retrieve the password file location used by ASM

After the Oracle Restart (Grid Infrastructure) upgrade from 11.2. to 12.1.0.2 , you might encounter the below lines in the ASM alert log file

Tue Jun 23 01:16:01 2015
WARNING: unknown state for ASM password file location resource, Return Value: 3
WARNING: unknown state for ASM password file location resource, Return Value: 3

After the upgrade, below command can get the error

[grid@oracle1 ~]$ srvctl config asm
ASM home: <crs home=''>
PRCA-1057 : Failed to retrieve the password file location used by ASM asm
PRCR-1097 : Resource attribute not found: PWFILE
[grid@oracle1 ~]$

This issue is unpublished bug 19449701.From 12.1 onwards ASM instance has a new attribute called PWFILE.

So , the solution is

[grid@oracle1 ~]$
[grid@oracle1 ~]$ crsctl stop resource -all
[grid@oracle1 ~]$ crsctl stat res -t
[grid@oracle1 ~]$ srvctl remove asm -force
[grid@oracle1 ~]$ srvctl add asm -listener LISTENER -spfile "+DATA/asm/asmparameterfile/registry.201.883099876" -pwfile "/oracle/grid/product/12.1/dbs/orapw+ASM" -diskstring "/dev/mapper/*p1"
[grid@oracle1 ~]$ crsctl start resource -all
[grid@oracle1 ~]$ srvctl config asm
ASM home: 
Password file: /oracle/grid/product/12.1/dbs/orapw+ASM
ASM listener: LISTENER
Spfile: +DATA/asm/asmparameterfile/registry.201.883099876
ASM diskgroup discovery string: /dev/mapper/*p1

Reference :- Oracle Restart: WARNING: unknown state for ASM password file location resource, Return Value: 3 (Doc ID 1935891.1)

Primary on FileSystem and Standby on ASM

For one of the client, standby server went down. We had another standby server which was kept down for more than a month. Decision was taken to start the server and apply incremental SCN based backup on the standby database.

The standby was on ASM and the Primary on filesystem.Incremental backup was started from the SCN reported by below query

select min(fhscn) from x$kcvfh;

Once the backup completed, it was transferred to standby, standby was mounted (using the old controlfile), backups were cataloged and recovery performed using ‘recover database noredo’.

The recovery was going on, and was handed over to me. After the recovery completed I restored the latest controlfile from Primary and mounted the standby. At this point the controlfile was with the information of the filesystem as they were on the Primary side. The next step was to register everything we had on Standby side:

[oracle@oracle3:~ (db)]$ rman target /

Recovery Manager: Release 11.2.0.3.0 - Production on Thu Jan 8 04:57:01 2015

Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.

connected to target database: ABCD (DBID=1463580380, not open)

RMAN> catalog  start with '+DATA/adbc_oracle3/DATAFILE/';

searching for all files that match the pattern +DATA/adbc_oracle3/DATAFILE/

List of Files Unknown to the Database
=====================================
File Name: +data/adbc_oracle3/DATAFILE/TEST.256.844395985
File Name: +data/adbc_oracle3/DATAFILE/TEST.257.844397067
.................
.................
File Name: +data/adbc_oracle3/DATAFILE/TEST.416.865953683

Do you really want to catalog the above files (enter YES or NO)? YES
cataloging files...
cataloging done

List of Cataloged Files
=======================
File Name: +data/adbc_oracle3/DATAFILE/TEST.256.844395985
File Name: +data/adbc_oracle3/DATAFILE/TEST.257.844397067
................
...............
File Name: +data/adbc_oracle3/DATAFILE/TEST.416.865953683

RMAN> report schema;

RMAN-06139: WARNING: control file is not current for REPORT SCHEMA
Report of database schema for database with db_unique_name ABCD_ORACLE3

List of Permanent Datafiles
===========================
File Size(MB) Tablespace           RB segs Datafile Name
---- -------- -------------------- ------- ------------------------
1    0        SYSTEM               ***     /oradata/abcd_oracle2/datafile/o1_mf_system_825xkscr_.dbf
2    0        SYSAUX               ***     /oradata/abcd_oracle2/datafile/o1_mf_sysaux_825y451r_.dbf
3    0        TEST                 ***     /oradata/abcd_oracle2/datafile/o1_mf_test_825s84mw_.dbf
4    0        TEST                 ***     /oradata2/abcd_oracle2/datafile/o1_mf_test_8dr1v332_.dbf
................
................
................
147  0        TEST                   ***     /oradata4/abcd_oracle2/datafile/o1_mf_test_b8k8hcrh_.dbf
148  0        TEST                   ***     /oradata4/abcd_oracle2/datafile/o1_mf_test_b8k8hdhf_.dbf
149  0        TEST                   ***     /oradata4/abcd_oracle2/datafile/o1_mf_test_b8k8hf6o_.dbf
150  0        TEST                   ***     /oradata4/abcd_oracle2/datafile/o1_mf_test_b8k8hg1j_.dbf
151  0        TEST                   ***     /oradata4/abcd_oracle2/datafile/o1_mf_test_bb318bhs_.dbf
152  0        TEST                   ***     /oradata4/abcd_oracle2/datafile/o1_mf_test_bb318cff_.dbf
153  0        TEST_INDEX             ***     /oradata4/abcd_oracle2/datafile/o1_mf_test_index_bb318pmy_.dbf
154  0        TEST_NOLOGGING         ***     /oradata3/abcd_oracle2/datafile/o1_mf_test_nolog_bbm2s7vk_.dbf
155  0        TESTINDEX             ***     /oradata3/abcd_oracle2/datafile/o1_mf_test_index_bbm2z7nv_.dbf
156  0        PERFSTAT             ***     /oradata3/abcd_oracle2/datafile/o1_mf_perfstat_bbm312pf_.dbf

List of Temporary Files
=======================
File Size(MB) Tablespace           Maxsize(MB) Tempfile Name
---- -------- -------------------- ----------- --------------------
3    15104    TEMP                 32767       /oradata4/abcd_oracle2/datafile/o1_mf_temp_b633ppbr_.tmp
4    25600    TEMP                 32767       /oradata4/abcd_oracle2/datafile/o1_mf_temp_b633ppcf_.tmp

After catalog completed, it was time to switch database to copy and it failed with below error

RMAN> switch database to copy;

RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of switch to copy command at 01/08/2015 05:00:25
RMAN-06571: datafile 151 does not have recoverable copy

After some analysis found, it was due to missing datafile on standby. The datafile was created on Primary after the standby was down and the recovery using the incremental backup was done with the older controlfile which had no information about the new datafiles.

Datafile # 151 – 156 were missing, so cataloged the backup pieces again as the controlfile was restored, and started restoring the datafile

RMAN> restore datafile 151;

Starting restore at 08-JAN-15
using channel ORA_DISK_1

channel ORA_DISK_1: starting datafile backup set restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_DISK_1: restoring datafile 00151 to /oradata4/ABCD_ORACLE2/datafile/o1_mf_ct_bb318bhs_.dbf
channel ORA_DISK_1: reading from backup piece +BACKUP/ABCD_ORACLE3/backupset/restore/incr_standby_5sps58rd_1_1

channel ORA_DISK_1: piece handle=+BACKUP/ABCD_ORACLE3/backupset/restore/incr_standby_5sps58rd_1_1 tag=TAG20150107T192825
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:19:05
Finished restore at 08-JAN-15

RMAN>

After the restoration completed, report schema showed

RMAN> report schema;

RMAN-06139: WARNING: control file is not current for REPORT SCHEMA
Report of database schema for database with db_unique_name adbc_oracle3

List of Permanent Datafiles
===========================
File Size(MB) Tablespace           RB segs Datafile Name
---- -------- -------------------- ------- ------------------------
1    0        SYSTEM               ***     /oradata/ABCD_ORACLE2/datafile/o1_mf_system_825xkscr_.dbf
2    0        SYSAUX               ***     /oradata/ABCD_ORACLE2/datafile/o1_mf_sysaux_825y451r_.dbf
.................
151  30720    TEST                  ***     +DATA/adbc_oracle3/datafile/test.417.868424659 
152  30720    TEST                  ***     +DATA/adbc_oracle3/datafile/test.418.868424659 
................

Tried to perform “switch database to copy” which again failed with the same error “RMAN-06571: datafile 151 does not have recoverable copy” . At this point I though to use “switch datafile to copy” for which generated dynamic sql from primary and ran in to standby.

Generated switch command sql from Primary :-

SQL> select 'switch datafile '||file#||' to copy;' from v$datafile;

[oracle@oracle3:~ (db)]$ vi swtch_copy.rman
[oracle@oracle3:~ (db)]$ rman target /

Recovery Manager: Release 11.2.0.3.0 - Production on Thu Jan 8 06:09:46 2015

Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.

connected to target database: ABCD (DBID=1463580380, not open)

RMAN> @swtch_copy.rman

RMAN> switch datafile 2 to copy;
using target database control file instead of recovery catalog
datafile 2 switched to datafile copy "+DATA/adbc_oracle3/datafile/sysaux.277.844418721"

............................
...........................
...........................
RMAN> switch datafile 149 to copy;
datafile 149 switched to datafile copy "+DATA/adbc_oracle3/datafile/ct.415.865953681"

RMAN> switch datafile 156 to copy;
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of switch to copy command at 01/08/2015 06:28:54
RMAN-06571: datafile 156 does not have recoverable copy

RMAN>
RMAN> **end-of-file**

Performed ‘recover database noredo’ again and this time it was pretty quick and then tried recover standby database

[oracle@oracle3:~/working/anand (abcd)]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.3.0 Production on Thu Jan 8 06:31:38 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 Partitioning, Automatic Storage Management, OLAP, Data Mining
and Real Application Testing options

SQL> recover standby database;
ORA-00279: change 38328244436 generated at 01/07/2015 19:28:32 needed for thread 1
ORA-00289: suggestion : +FRA
ORA-00280: change 38328244436 for thread 1 is in sequence #98501


Specify log: {=suggested | filename | AUTO | CANCEL}
^C
ORA-01547: warning: RECOVER succeeded but OPEN RESETLOGS would get error below
ORA-01152: file 1 was not restored from a sufficiently old backup
ORA-01110: data file 1: '+DATA/adbc_oracle3/datafile/system.357.844454911'


Did few changes in DGMGRL parameters and enabled standby configuration. After few hours, standby was insync with Primary. Stopped the MRP , dropped the standby
redo logfiles as they showed filesystem, and created on ASM. Opened the standby in read only mode and started the MRP.

ASM Diskgroup shows USABLE_FILE_MB value in Negative

Today while working on ASM diskgroup i noticed Negative value for USABLE_FILE_MB. I was little surprised as it has been pretty long that i worked on ASM. So i started looking around for blogs and mos docs and found few really nice one around.

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.

I would really recommend reading :-

http://prutser.wordpress.com/2013/01/03/demystifying-asm-required_mirror_free_mb-and-usable_file_mb/

The box i was working on was exadata server quarter 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.

So here i am good with one disk failure but not with One celldisk failure. Basically i need to either add disk to the disk group or free up some space.

This post is more of a note for myself to refer back. Hope it useful for some of you too 🙂

DBCA failing with “Diskgroup XXX is not compatible for database usage”

Today a friend of mine pinged me an error while he was trying to create database using DBCA from 11.2.0.3 RDBMS home on an exadata box.Screenshot of the error is below

dbca_error

The error was easy to understand as compatible parameter set in the database initialization parameter was lower than the compatible.rdbms set for the diskgroup in ASM. The db compatible parameter was set to 11.2.0.0.0 whereas for the diskgroup compatible.rdbms was set to 11.2.0.2.0.

So checking the ASM instance showed

SYS@+ASM7 > col COMPATIBILITY form a10
SYS@+ASM7 > col DATABASE_COMPATIBILITY form a10
SYS@+ASM7 > col NAME form a20
SYS@+ASM7 > select group_number, name, compatibility, database_compatibility from v$asm_diskgroup;

GROUP_NUMBER NAME               COMPATIBIL DATABASE_C
------------ ------------------ ---------- ----------
           1 DATA_O1            11.2.0.2.0 11.2.0.2.0
           2 RECO_O1            11.2.0.2.0 11.2.0.2.0

The compatible.asm diskgroup attribute controls the format of the ASM diskgroup metadata. Only ASM instances with a software version equal to or greater than compatible.asm can mount the diskgroup.

The compatible.rdbms diskgroup attribute determines the format of ASM files themselves. The diskgroup can be accessed by any database instance with a compatible init.ora parameter set equal to or higher than the compatible.rdbms attribute.

DBCA (choose General Purpose) doesn’t provide any screen wherein we can change the parameter value. But as we want to create the database using dbca, we need to change the parameter value in the template stored in ORACLE_HOME/assistants/dbca/templates


xxxxxx: (test1) /u01/oraadmin/test/admin> cd $ORACLE_HOME
xxxxxx: (test1) /u01/app/oracle/product/11.2.0.3/dbhome_1> cd assistants/dbca/templates/
xxxxxx: (test1) /u01/app/oracle/product/11.2.0.3/dbhome_1/assistants/dbca/templates> ls -lrt
total 292272
-rwxrw-r-- 1 oracle oinstall        83 Oct  4 07:25 create_bct.sql
-rwxrw-r-- 1 oracle oinstall       718 Oct  4 07:25 crt_cluster_interconnect.sql
-rwxrw-r-- 1 oracle oinstall      5104 Oct  4 07:25 Data_Warehouse.dbc
-rwxrw-r-- 1 oracle oinstall       122 Oct  4 07:25 drop_cluster_interconnect.sql
-rwxrw-r-- 1 oracle oinstall     13756 Oct  4 07:25 dw_x2_2.dbt
-rwxrw-r-- 1 oracle oinstall       178 Oct  4 07:25 exadata_miscellaneous.sql
-rwxrw-r-- 1 oracle oinstall   1507328 Oct  4 07:25 example.dmp
-rwxrw-r-- 1 oracle oinstall  21889024 Oct  4 07:25 example01.dfb
-rwxrw-r-- 1 oracle oinstall      4984 Oct  4 07:25 General_Purpose.dbc
-rwxrw-r-- 1 oracle oinstall       803 Oct  4 07:25 logs.sql
-rwxrw-r-- 1 oracle oinstall      4104 Oct  4 07:25 logs.wk1
-rwxrw-r-- 1 oracle oinstall       320 Oct  4 07:25 logs_to_add.lst
-rwxrw-r-- 1 oracle oinstall       311 Oct  4 07:25 logs_to_add.sql
-rwxrw-r-- 1 oracle oinstall      8208 Oct  4 07:25 logs_to_add.wk1
-rwxrw-r-- 1 oracle oinstall     11489 Oct  4 07:25 New_Database.dbt
-rwxrw-r-- 1 oracle oinstall     13558 Oct  4 07:25 oltp_x2_2.dbt
-rwxrw-r-- 1 oracle oinstall       369 Oct  4 07:25 recreate_temp.sql
-rwxrw-r-- 1 oracle oinstall   9748480 Oct  4 07:26 Seed_Database.ctl
-rwxrw-r-- 1 oracle oinstall 265691136 Oct  4 07:26 Seed_Database.dfb
-rwxrw-r-- 1 oracle oinstall       761 Oct  4 07:26 set_cluster_interconnect.sql
-rwxrw-r-- 1 oracle oinstall        18 Oct  4 07:26 set_fra_size.lst
-rwxrw-r-- 1 oracle oinstall       806 Oct  4 07:26 set_fra_size.sql
-rwxrw-r-- 1 oracle oinstall       513 Oct  4 07:26 set_fra_size.wk1
-rwxrw-r-- 1 oracle oinstall       199 Oct  4 07:26 set_use_large_pages_false.sql
xxxxxx: (test1) /u01/app/oracle/product/11.2.0.3/dbhome_1/assistants/dbca/templates>

As we choose the General Purpose template, we need to edit the value in General_Purpose.dbc and search of compatible parameter and you would see

..............
  initParam name="compatible" value="11.2.0.0.0"/
..............
..............

Edit the value to either equal to compatible.rdbms of diskgroup or higher. In our case, we set it “11.2.0.3” and dbca didn’t threw error the next time 🙂

To know more on ASM diskgroup compatibility read

http://www.pythian.com/news/1078/oracle-11g-asm-diskgroup-compatibility/

ORA-15027: active use of diskgroup “DATA” precludes its dismount

As i wanted to test few thing related to asm diskgroup on my test box, had to drop the diskgroup. Before dropping the diskgroup had dropped the database using RMAN.

A S M    D I S K G R O U P    S P A C E   U S A G E   R E P O R T
                                                                                                                    Required
                                                           AU                                          Hot     Cold     Free   Usable
                                   ASM        RDBMS      Size  Offline    Total     Free     Used     Used     Used   Mirror     Free
DiskGroup       STATE       Type   Compat     Compat     (MB)    Disks     (GB)     (GB)     (GB)     (GB)     (GB)     (GB)     (GB)
--------------- ----------- ------ ---------- ---------- ---- -------- -------- -------- -------- -------- -------- -------- --------
DATA02          MOUNTED     EXTERN 10.1.0.0.0 10.1.0.0.0    1        0        2        0        2        0        2        0        0
DATA            MOUNTED     EXTERN 11.2.0.0.0 10.1.0.0.0    1        0        4        3        1        0        1        0        3
                                                                       -------- -------- -------- -------- -------- -------- --------
Total                                                                         6        3        3        0        3        0        3

SQL>  drop diskgroup DATA including contents;
 drop diskgroup DATA including contents
*
ERROR at line 1:
ORA-15039: diskgroup not dropped
ORA-15027: active use of diskgroup "DATA" precludes its dismount

Using v$asm_client checked if any database client is still on

SQL> select * from v$asm_client;

no rows selected

Checked for the spfile location of the ASM instance and it resided in +DATA diskgroup, leading to ORA-15027

SQL> show parameter spfile

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
spfile                               string      +DATA/asm/asmparameterfile/reg
                                                 istry.253.749745881
SQL>                                                                                         

To resolve and drop the diskgroup did the following

1. create pfile in another location –

SQL> create pfile='$ORACLE_HOME/dbs/init+ASM.ora' from spfile='+DATA/asm/asmparameterfile/registry.253.749745881';

File created.

2. Shutdown and start the asm instance using PFILE

SQL> startup pfile=$ORACLE_HOME/dbs/init+ASM.ora
ASM instance started

Total System Global Area  284565504 bytes
Fixed Size                  1343692 bytes
Variable Size             258055988 bytes
ASM Cache                  25165824 bytes
ASM diskgroups mounted
SQL> show parameter disk

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
asm_diskgroups                       string      DATA02, DATA

3. drop the diskgroup

A S M    D I S K G R O U P    S P A C E   U S A G E   R E P O R T
                                                                                                                    Required
                                                           AU                                          Hot     Cold     Free   Usable
                                   ASM        RDBMS      Size  Offline    Total     Free     Used     Used     Used   Mirror     Free
DiskGroup       STATE       Type   Compat     Compat     (MB)    Disks     (GB)     (GB)     (GB)     (GB)     (GB)     (GB)     (GB)
--------------- ----------- ------ ---------- ---------- ---- -------- -------- -------- -------- -------- -------- -------- --------
DATA02          MOUNTED     EXTERN 10.1.0.0.0 10.1.0.0.0    1        0        2        2        0        0        0        0        2
DATA            MOUNTED     EXTERN 11.2.0.0.0 10.1.0.0.0    1        0        4        4        0        0        0        0        4
                                                                       -------- -------- -------- -------- -------- -------- --------
Total                                                                         6        6        0        0        0        0        6

SQL> drop diskgroup DATA including contents;

Diskgroup dropped.

The other way of dropping the diskgroup is –

A S M    D I S K G R O U P    S P A C E   U S A G E   R E P O R T
                                                                                                                    Required
                                                           AU                                          Hot     Cold     Free   Usable
                                   ASM        RDBMS      Size  Offline    Total     Free     Used     Used     Used   Mirror     Free
DiskGroup       STATE       Type   Compat     Compat     (MB)    Disks     (GB)     (GB)     (GB)     (GB)     (GB)     (GB)     (GB)
--------------- ----------- ------ ---------- ---------- ---- -------- -------- -------- -------- -------- -------- -------- --------
DATA02          MOUNTED     EXTERN 10.1.0.0.0 10.1.0.0.0    1        0        2        2        0        0        0        0        2
                                                                       -------- -------- -------- -------- -------- -------- --------
Total                                                                         2        2        0        0        0        0        2

SQL> create spfile='+DATA02' from pfile='$ORACLE_HOME/dbs/init+ASM.ora';
create spfile='+DATA02' from pfile='$ORACLE_HOME/dbs/init+ASM.ora'
*
ERROR at line 1:
ORA-17502: ksfdcre:4 Failed to create file +DATA02
ORA-15221: ASM operation requires compatible.asm of 11.2.0.0.0 or higher

SQL> alter diskgroup DATA02 set attribute 'compatible.asm'='11.2.0.0.0';

Diskgroup altered.

SQL> create spfile='+DATA02' from pfile='$ORACLE_HOME/dbs/init+ASM.ora';

File created.

Shutdown and started the ASM instance using the SPFILE which resides on +DATA02 now and is the only asm diskgroup associated with the instance. When we try to drop the diskgroup we get the same ORA-15027

SQL> drop diskgroup DATA02 including contents;
drop diskgroup DATA02 including contents
*
ERROR at line 1:
ORA-15039: diskgroup not dropped
ORA-15027: active use of diskgroup "DATA02" precludes its dismount

SQL> alter diskgroup DATA02  dismount force;

Diskgroup altered.


A S M    D I S K G R O U P    S P A C E   U S A G E   R E P O R T
                                                                                                                    Required
                                                           AU                                          Hot     Cold     Free   Usable
                                   ASM        RDBMS      Size  Offline    Total     Free     Used     Used     Used   Mirror     Free
DiskGroup       STATE       Type   Compat     Compat     (MB)    Disks     (GB)     (GB)     (GB)     (GB)     (GB)     (GB)     (GB)
--------------- ----------- ------ ---------- ---------- ---- -------- -------- -------- -------- -------- -------- -------- --------
DATA02          DISMOUNTED         0.0.0.0.0  0.0.0.0.0     0        0        0        0        0        0        0        0        0
                                                                       -------- -------- -------- -------- -------- -------- --------
Total                                                                         0        0        0        0        0        0        0

SQL> drop diskgroup DATA02 force including contents;

Diskgroup dropped.

ERROR: No checkpoint table specified for ADD REPLICAT – Oracle GoldenGate

This is just a quick note for myself to remember and reference for in case someone comes across this error while setting up Oracle GoldenGate.

While configuring the online synchronization replication, a default checkpoint table is created.The table’s name is mentioned in file named GLOBALS.

GGSCI (anand-lab) 29> EDIT PARAMS ./GLOBALS

GGSCHEMA GG_OWNER
CHECKPOINTTABLE GG_OWNER.CKPTAB

GGSCI (anand-lab) 31> DBLOGIN USERID gg_owner,PASSWORD gg123
Successfully logged into database.

GGSCI (anand-lab) 33> ADD CHECKPOINTTABLE CKPTAB

Successfully created checkpoint table CKPTAB.

Now, when you try to create the Replicat group, the ADD REPLICAT command might produces an error

GGSCI (anand-lab) 41> ADD REPLICAT rep1, EXTTRAIL /media/sf_database/gg/dirdat/rt
ERROR: No checkpoint table specified for ADD REPLICAT.

The solution is simply to exit that GGSCI session and then start another one before issuing ADD REPLICAT.The ADD REPLICATE command fails if issued from the session where the GLOBALS file using the GGSCI command “EDIT PARAMS ./GLOBALS” was created.This is because the name of the checkpoint table is read from GLOBALS by GGSCI. The session in which the GLOBALS was created cannot read the file.

Reference – MOS 965256.1