Learn X$KFFXP - fixed table for ASM

I heard about the X$ fixed table (X$KFFXP). It keeps position of striped and mirrored extents... and etc. If You connect to ASM Instance, You can check and retrieve data from it.

$ sqlplus / as sysasm

SQL> desc X$KFFXP

Name Null? Type
----------------------------------------------------------------------------------- -------- ----------
ADDR RAW(8)
INDX NUMBER
INST_ID NUMBER
GROUP_KFFXP NUMBER
NUMBER_KFFXP NUMBER
COMPOUND_KFFXP NUMBER
INCARN_KFFXP NUMBER
PXN_KFFXP NUMBER
XNUM_KFFXP NUMBER
LXN_KFFXP NUMBER
DISK_KFFXP NUMBER
AU_KFFXP NUMBER
FLAGS_KFFXP NUMBER
CHK_KFFXP NUMBER
SIZE_KFFXP NUMBER

What do you learn?
Some columns in X$KFFXP table:
GROUP_KFFXP = V$ASM_DISKGROUP.GROUP_NUMBER
NUMBER_KFFXP = V$ASM_FILE.FILE_NUMBER
COMPOUND_KFFXP = V$ASM_FILE.COMPOUND_INDEX
INCARN_KFFXP = V$ASM_FILE.INCARNATION
XNUM_KFFXP = Extent Number
LXN_KFFXP = Logical Extent Number (0 = primary, 1 = mirrored copy, 2 = mirrored copy[High redundancy])
DISK_KFFXP = V$ASM_DISK.DISK_NUMBER
AU_KFFXP = V$ASM_DISKGROUP.ALLOCATION_UNIT_SIZE

So, I used X$KFFXP table for testing (just tested)

select dg.name, x.NUMBER_KFFXP "FILE NUMBER", x.XNUM_KFFXP "EXTENT NUMBER", decode(x.LXN_KFFXP,0,'P',1,'M','MM') "EXTENT TYPE", d.path from x$kffxp x, v$asm_disk d, v$asm_diskgroup dg where x.GROUP_KFFXP = d.GROUP_NUMBER and x.DISK_KFFXP = d.DISK_NUMBER and x.GROUP_KFFXP=dg.GROUP_NUMBER and x.NUMBER_KFFXP in (select file_number from v$asm_alias where name like 'USERS%') order by x.NUMBER_KFFXP, x.XNUM_KFFXP, x.LXN_KFFXP
/

P = primary
M = mirrored copy
MM = mirrored copy[High redundancy]

- On ASM, that uses External redundancy

SQL> select dg.name, x.NUMBER_KFFXP "FILE NUMBER", x.XNUM_KFFXP "EXTENT NUMBER", decode(x.LXN_KFFXP,0,'P',1,'M','MM') "EXTENT TYPE", d.path from x$kffxp x, v$asm_disk d, v$asm_diskgroup dg where x.GROUP_KFFXP = d.GROUP_NUMBER and x.DISK_KFFXP = d.DISK_NUMBER and x.GROUP_KFFXP=dg.GROUP_NUMBER and x.NUMBER_KFFXP in (select file_number from v$asm_alias where name like 'USERS%') order by x.NUMBER_KFFXP, x.XNUM_KFFXP, x.LXN_KFFXP
/

NAME FILE NUMBER EXTENT NUMBER EXTENT TYPE PATH
----- ----------- ------------- --------------- ---------------
DATA 316 0 P /dev/sdb
DATA 316 1 P /dev/sdb
DATA 316 2 P /dev/sdb
DATA 316 3 P /dev/sdb
DATA 316 4 P /dev/sdb
DATA 316 5 P /dev/sdb

- On ASM, that uses Normal redundancy

SQL> select dg.name, x.NUMBER_KFFXP "FILE NUMBER", x.XNUM_KFFXP "EXTENT NUMBER", decode(x.LXN_KFFXP,0,'P',1,'M','MM') "EXTENT TYPE", d.path from x$kffxp x, v$asm_disk d, v$asm_diskgroup dg where x.GROUP_KFFXP = d.GROUP_NUMBER and x.DISK_KFFXP = d.DISK_NUMBER and x.GROUP_KFFXP=dg.GROUP_NUMBER and x.NUMBER_KFFXP in (select file_number from v$asm_alias where name like 'USERS%') order by x.NUMBER_KFFXP, x.XNUM_KFFXP, x.LXN_KFFXP
/

NAME FILE NUMBER EXTENT NUMBER EXTENT TYPE PATH
------------------------------ ----------- ------------- --------------- ------------------------------
DATA 261 0 P ORCL:DATA10
DATA 261 0 M ORCL:DATA02
DATA 261 1 P ORCL:DATA03
DATA 261 1 M ORCL:DATA07
DATA 261 2 P ORCL:DATA09
DATA 261 2 M ORCL:DATA05
DATA 261 3 P ORCL:DATA05
DATA 261 3 M ORCL:DATA07
DATA 261 4 P ORCL:DATA08
DATA 261 4 M ORCL:DATA04
DATA 261 5 P ORCL:DATA04
DATA 261 5 M ORCL:DATA06

- On ASM, that uses High redundancy

SQL> select dg.name, x.NUMBER_KFFXP "FILE NUMBER", x.XNUM_KFFXP "EXTENT NUMBER", decode(x.LXN_KFFXP,0,'P',1,'M','MM') "EXTENT TYPE", d.path from x$kffxp x, v$asm_disk d, v$asm_diskgroup dg where x.GROUP_KFFXP = d.GROUP_NUMBER and x.DISK_KFFXP = d.DISK_NUMBER and x.GROUP_KFFXP=dg.GROUP_NUMBER and x.NUMBER_KFFXP in (select file_number from v$asm_alias where name like 'USERS%') order by x.NUMBER_KFFXP, x.XNUM_KFFXP, x.LXN_KFFXP
/

NAME FILE NUMBER EXTENT NUMBER EXTENT TYPE PATH
------------------------------ ----------- ------------- --------------- --------------------------------------------------
DATA 917 0 P o/192.168.99.6/DATA_EXA_CD_11_exacel02
DATA 917 0 M o/192.168.99.11/DATA_EXA_CD_02_exacel07
DATA 917 0 MM o/192.168.99.7/DATA_EXA_CD_00_exacel03
DATA 917 1 P o/192.168.99.9/DATA_EXA_CD_01_exacel05
DATA 917 1 M o/192.168.99.10/DATA_EXA_CD_11_exacel06
DATA 917 1 MM o/192.168.99.7/DATA_EXA_CD_06_exacel03
DATA 917 2 P o/192.168.99.9/DATA_EXA_CD_11_exacel05
DATA 917 2 M o/192.168.99.10/DATA_EXA_CD_01_exacel06
DATA 917 2 MM o/192.168.99.8/DATA_EXA_CD_02_exacel04
DATA 917 3 P o/192.168.99.6/DATA_EXA_CD_04_exacel02
DATA 917 3 M o/192.168.99.5/DATA_EXA_CD_10_exacel01
DATA 917 3 MM o/192.168.99.11/DATA_EXA_CD_10_exacel07
DATA 917 4 P o/192.168.99.8/DATA_EXA_CD_09_exacel04
DATA 917 4 M o/192.168.99.10/DATA_EXA_CD_09_exacel06
DATA 917 4 MM o/192.168.99.9/DATA_EXA_CD_03_exacel05
DATA 917 5 P o/192.168.99.6/DATA_EXA_CD_06_exacel02
DATA 917 5 M o/192.168.99.7/DATA_EXA_CD_10_exacel03
DATA 917 5 MM o/192.168.99.8/DATA_EXA_CD_07_exacel04

That was example to use it. However, you can use X$KFFXP to join with other views(V$ASM_*)

CLSU-00100: Operating System function: open failed failed with error data: 13

Nothing special. I removed some old folder in ASM DiskGroup (after I dropped database but not clean) by using asmcmd command-line. but error.

$ env |grep ORACLE
ORACLE_SID=+ASM1
ORACLE_HOME=/u01/app/11.2.0/grid

$ asmcmd
ASMCMD>
.
.
.
ASMCMD> rm -rf ARCHIVELOG
Can't open '/u01/app/11.2.0/grid/log/diag/asmcmd/user_root/exadb01/alert/alert.log' for append
CLSU-00100: Operating System function: open failed failed with error data: 13
CLSU-00101: Operating System error message: Permission denied
CLSU-00103: error location: SlfFopen1

Error "CLSU-00100: Operating System function: open failed failed with error data: 13", and then checked.

$ cat /u01/app/11.2.0/grid/log/diag/asmcmd/user_root/exadb01/alert/alert.log
25-May-11 17:51 Connected to an idle instance.
25-May-11 17:51 ASMCMD-08102: no connection to ASM; command requires ASM to run

Check both $ORACLE_HOME and $ORACLE_SID environments again -) So... Grant sysasm to sys.

$ sqlplus / as sysasm
SQL*Plus: Release 11.2.0.2.0 Production on Wed May 25 18:10:02 2011
Copyright (c) 1982, 2010, Oracle. All rights reserved.

Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production
With the Real Application Clusters and Automatic Storage Management options

SQL> grant sysasm to sys;

Grant succeeded.

Then I removed again.

$ asmcmd
ASMCMD>
.
.
.
ASMCMD> rm -rf ARCHIVELOG
< no error and done >

I can remove directories and files in ASM DiskGroup.

ORA-15067 - When drop disks in ASM DiskGroup Normal redundancy

I have ASM Diskgroup and redundancy type is Normal ans tested to drop all disks from one Cell Server:

SQL> select NAME, TOTAL_MB, FREE_MB, STATE, TYPE, REQUIRED_MIRROR_FREE_MB, USABLE_FILE_MB from v$asm_diskgroup where name='TEST_DG';

NAME TOTAL_MB FREE_MB STATE TYPE REQUIRED_MIRROR_FREE_MB USABLE_FILE_MB
---------------------------------------- ---------- ---------- ----------- ------ ----------------------- --------------
TEST_DG 32768 18008 MOUNTED NORMAL 2978 7515

SQL> select path, header_status, os_mb,free_mb, failgroup from v$asm_disk where group_number = (select group_number from v$asm_diskgroup where name='TEST_DG') order by failgroup;

PATH HEADER_STATU OS_MB FREE_MB FAILGROUP
---------------------------------------- ------------ ---------- ---------- ----------
o/192.168.99.5/TEST_DG_FD_03_EXACEL01 MEMBER 1024 564 EXACEL01
.
.
o/192.168.99.6/TEST_DG_FD_12_EXACEL02 MEMBER 1024 552 EXACEL02
.
.

SQL> select * from gv$asm_operation;

no rows selected

SQL> alter diskgroup TEST_DG drop disks in failgroup EXACEL01 rebalance power 11 NOWAIT;
alter diskgroup TEST_DG drop disks in failgroup EXACEL01 rebalance power 11 NOWAIT
*
ERROR at line 1:
ORA-15067: command or option incompatible with diskgroup redundancy

Found ORA-15067 and found out it.

ORA-15067: command or option incompatible with diskgroup redundancy
Cause: An attempt was made to use a feature which is not permitted by the diskgroup's redundancy policy. Common examples are forcibly dropping a disk from an EXTERNAL REDUNDANCY diskgroup, using the FAILGROUP clauses with an EXTERNAL REDUNDANCY diskgroup, or using invalid template attributes.
Action: Omit the option from the command

Not sure about error and idea to test then. So, I just added new ASM Disk and tried to drop again.

SQL> alter diskgroup TEST_DG add disk 'o/*/TEST_DG*EXACEL03' rebalance power 11 NOWAIT;

Diskgroup altered.

SQL> select * from gv$asm_operation;

INST_ID GROUP_NUMBER OPERA STAT POWER ACTUAL SOFAR EST_WORK EST_RATE EST_MINUTES ERROR_CODE
---------- ------------ ----- ---- ---------- ---------- ---------- ---------- ---------- ----------- -----------
1 4 REBAL RUN 11 11 1931 2051 8646 0

SQL> select * from gv$asm_operation;

no rows selected

SQL> select NAME, TOTAL_MB, FREE_MB, STATE, TYPE, REQUIRED_MIRROR_FREE_MB, USABLE_FILE_MB from v$asm_diskgroup where name='TEST_DG';

NAME TOTAL_MB FREE_MB STATE TYPE REQUIRED_MIRROR_FREE_MB USABLE_FILE_MB
---------------------------------------- ---------- ---------- ----------- ------ ----------------------- --------------
TEST_DG 49152 34184 MOUNTED NORMAL 5004 14590

SQL> select path, header_status, os_mb,free_mb, failgroup from v$asm_disk where group_number = (select group_number from v$asm_diskgroup where name='TEST_DG') order by failgroup;

PATH HEADER_STATU OS_MB FREE_MB FAILGROUP
---------------------------------------- ------------ ---------- ---------- ----------
o/192.168.99.5/TEST_DG_FD_14_EXACEL01 MEMBER 1024 696 EXACEL01
.
.
o/192.168.99.6/TEST_DG_FD_12_EXACEL02 MEMBER 1024 708 EXACEL02
.
.
o/192.168.99.7/TEST_DG_FD_10_EXACEL03 MEMBER 1024 712 EXACEL03
.
.

SQL> alter diskgroup TEST_DG drop disks in failgroup EXACEL01 rebalance power 11 NOWAIT;

Diskgroup altered.

SQL> select * from gv$asm_operation;

no rows selected

SQL> select path, header_status, os_mb,free_mb, failgroup from v$asm_disk where group_number = (select group_number from v$asm_diskgroup where name='TEST_DG') order by failgroup;

PATH HEADER_STATU OS_MB FREE_MB FAILGROUP
---------------------------------------- ------------ ---------- ---------- ----------
o/192.168.99.6/TEST_DG_FD_12_EXACEL02 MEMBER 1024 616 EXACEL02
.
.
o/192.168.99.7/TEST_DG_FD_09_EXACEL03 MEMBER 1024 584 EXACEL03
.
.

SQL> select path, header_status, os_mb,free_mb, failgroup, mount_status from v$asm_disk where path like '%TEST_DG%' and failgroup='EXACEL01';

PATH HEADER_STATU OS_MB FREE_MB FAILGROUP MOUNT_S
---------------------------------------- ------------ ---------- ---------- ---------- -------
o/192.168.99.5/TEST_DG_FD_10_EXACEL01 FORMER 1024 0 EXACEL01 CLOSED
.
.
.

MOUNT_STATUS should not be “CACHED” for DROPPED Disks.

I dropped all Disks from one cell server. I'm curious. My ASM DiskGroup is Normal redundancy type, could not drop!!! had to add disks and tired again.

ORA-15041 - drop disks in ASM diskgroup

Test to drop disk in ASM Disk Group and found error: ORA-15041

SQL> alter diskgroup TEST_DG drop disks in failgroup EXACEL02 rebalance power 11 NOWAIT;

Diskgroup altered.

SQL> select * from gv$asm_operation;

INST_ID GROUP_NUMBER OPERA STAT POWER ACTUAL SOFAR EST_WORK EST_RATE EST_MINUTES ERROR_CODE
---------- ------------ ----- ---- ---------- ---------- ---------- ---------- ---------- ----------- ----------
1 4 REBAL ERRS 11 ORA-15041

SQL> select path, header_status, os_mb,free_mb, failgroup, mount_status from v$asm_disk where path like '%TEST_DG%' and failgroup='EXACEL02';

PATH HEADER_STATU OS_MB FREE_MB FAILGROUP MOUNT_S
---------------------------------------- ------------ ---------- ---------- ---------- -------
.
.
.
o/192.168.99.6/TEST_DG_FD_03_EXACEL02 FORMER 1024 0 EXACEL02 CLOSED
o/192.168.99.6/TEST_DG_FD_00_EXACEL02 MEMBER 1024 1012 EXACEL02 CACHED

MOUNT_STATUS should not be “CACHED” for DROPPED Disks.

ORA-15041: diskgroup space exhausted
Cause: The diskgroup ran out of space.
Action: Add more disks to the diskgroup, or delete some existing files.

Idea: Have to remove some files in this ASM disk group or add new disk to this ASM disk group, (Actually we can check "required_mirror_free_mb" in V$ASM_DISKGROUP before drop Disk). After we fixed it (this issue: I resized some database files):

SQL> select * from gv$asm_operation;

no rows selected

SQL> select path, header_status, os_mb,free_mb, failgroup, mount_status from v$asm_disk where path like '%TEST_DG%' and failgroup='EXACEL02';

PATH HEADER_STATU OS_MB FREE_MB FAILGROUP MOUNT_S
---------------------------------------- ------------ ---------- ---------- ---------- -------
.
.
.
o/192.168.99.6/TEST_DG_FD_03_EXACEL02 FORMER 1024 0 EXACEL02 CLOSED
o/192.168.99.6/TEST_DG_FD_15_EXACEL02 FORMER 1024 0 EXACEL02 CLOSED

Other Post: ORA-15041 - ASM Disk Group Unbalanced

How to upgrade ASM after Install Grid Infrastructure Software Only?

I posted "struggle to Upgrade Oracle Database 11.2.0.1 to 11.2.0.2" topic, and someone comment me "Upgrade Grid Infrastructure to ASM after Installed Grid Infrastructure Software Only"
but I have ASM on 10.2 and need to test:

SQL> select instance_name from v$instance;
INSTANCE_NAME
----------------
+ASM

SQL> select name, state from v$asm_diskgroup;
NAME STATE
------------------------------ -----------
ASM_DG01 MOUNTED

SQL> select * from v$version;
BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.5.0
PL/SQL Release 10.2.0.5.0 - Production
CORE 10.2.0.5.0 Production
TNS for Linux: Version 10.2.0.5.0 - Production
NLSRTL Version 10.2.0.5.0 - Production

then installed Grid Infrastructure(runInstaller) and chose "Installed Grid Infrastructure Software Only"!!! after that - need to run "root.sh" script.

$ su - root
# /u01/app/oracle/product/11.2.0/grid/root.sh
Running Oracle 11g root script...

The following environment variables are set as:
ORACLE_OWNER= oracle
ORACLE_HOME= /u01/app/oracle/product/11.2.0/grid

Enter the full pathname of the local bin directory: [/usr/local/bin]:
The contents of "dbhome" have not changed. No need to overwrite.
The contents of "oraenv" have not changed. No need to overwrite.
The contents of "coraenv" have not changed. No need to overwrite.

Entries will be added to the /etc/oratab file as needed by
Database Configuration Assistant when a database is created
Finished running generic part of root script.
Now product-specific root actions will be performed.

To configure Grid Infrastructure for a Stand-Alone Server run the following command as the root user:
/u01/app/oracle/product/11.2.0/grid/perl/bin/perl -I/u01/app/oracle/product/11.2.0/grid/perl/lib -I/u01/app/oracle/product/11.2.0/grid/crs/install /u01/app/oracle/product/11.2.0/grid/crs/install/roothas.pl

To configure Grid Infrastructure for a Cluster execute the following command:
/u01/app/oracle/product/11.2.0/grid/crs/config/config.sh
This command launches the Grid Infrastructure Configuration Wizard. The wizard also supports silent operation, and the parameters can be passed through the response file that is available in the installation media.

My some answer for question "Upgrade Grid Infrastructure to ASM after Installed Grid Infrastructure Software Only", that show above!!!
My Test is "Grid Infrastructure for a Stand-Alone Server", then

# /u01/app/oracle/product/11.2.0/grid/perl/bin/perl -I/u01/app/oracle/product/11.2.0/grid/perl/lib -I/u01/app/oracle/product/11.2.0/grid/crs/install /u01/app/oracle/product/11.2.0/grid/crs/install/roothas.pl

Using configuration parameter file: /u01/app/oracle/product/11.2.0/grid/crs/install/crsconfig_params
Creating trace directory
LOCAL ADD MODE
Creating OCR keys for user 'oracle', privgrp 'oinstall'..
Operation successful.
CSS appears healthy
Stopping CSSD.
Shutting down CSS daemon.
Shutdown request successfully issued.
Shutdown has begun. The daemons should exit soon.
CRS-4664: Node mytest successfully pinned.
Adding daemon to inittab
ACFS-9459: ADVM/ACFS is not supported on this OS version: '2.6.32-100.0.19.el5'
mytest 2011/01/12 00:49:30 /u01/app/oracle/product/11.2.0/grid/cdata/mytest/backup_20110112_004930.olr
Successfully configured Oracle Grid Infrastructure for a Standalone Server

then check and upgrade ASM (reference)

$ export ORACLE_HOME=/u01/app/oracle/product/11.2.0/grid
$ export PATH=$ORACLE_HOME/bin:$PATH
$ crsctl query has releaseversion
Oracle High Availability Services release version on the local node is [11.2.0.2.0]

$ crsctl query has softwareversion
Oracle High Availability Services version on the local node is [11.2.0.2.0]

$ export ASMCA_ROLLING_UPGRADE=true
$ asmca

but found error:
So, start LISTENER and then "asmca" again but found
try "asmca" again
ASM upgraded, then check from "sqlplus":

SQL> select instance_name from v$instance;
INSTANCE_NAME
----------------
+ASM

SQL> select name, state from v$asm_diskgroup;
NAME STATE
------------------------------ -----------
ASM_DG01 MOUNTED

SQL> select * from v$version;
BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.2.0
PL/SQL Release 11.2.0.2.0 - Production
CORE 11.2.0.2.0 Production
TNS for Linux: Version 11.2.0.2.0 - Production
NLSRTL Version 11.2.0.2.0 - Production

However, ASM Disk Groups compatibility not change:

NAME COMPATIBILITY DATABASE_COMPATIBILITY
------------------------------ ------------------------------ ------------------------------
ASM_DG01 10.1.0.0.0 10.1.0.0.0

OK... this only shows answer, what we should do after "Installed Grid Infrastructure Software Only"

To configure Grid Infrastructure for a Stand-Alone Server run the following command as the root user:
/u01/app/oracle/product/11.2.0/grid/perl/bin/perl -I/u01/app/oracle/product/11.2.0/grid/perl/lib -I/u01/app/oracle/product/11.2.0/grid/crs/install /u01/app/oracle/product/11.2.0/grid/crs/install/roothas.pl

To configure Grid Infrastructure for a Cluster execute the following command:
/u01/app/oracle/product/11.2.0/grid/crs/config/config.sh

!!!

ORA-27154: post/wait create failed ORA-27300: OS system dependent operation:semget failed with status: 28

I started ASM instance, it crashed and found error.

ORA-27154: post/wait create failed
ORA-27300: OS system dependent operation:semget failed with status: 28
ORA-27301: OS failure message: No space left on device
ORA-27302: failure occurred at: sskgpsemsper

if check from OS

# df -h
Filesystem Size Used Avail Use% Mounted on
/dev/sda6 129G 27G 103G 21% /oradata
/dev/sda2 49G 33G 14G 72% /oracle

then check about Semaphore Limits.

# ipcs -la
------ Semaphore Limits --------
max number of arrays = 200
max semaphores per array = 250
max semaphores system wide = 100
max ops per semop call = 100
semaphore max value = 32767

my value wrong, check Document and then modify /etc/sysctl.conf file.

kernel.sem = 250 32000 100 200

run sysctl and check.

# sysctl -p
# sysctl kernel.sem
kernel.sem = 250 32000 100 128
# ipcs -la
------ Semaphore Limits --------
max number of arrays = 200
max semaphores per array = 250
max semaphores system wide = 32000
max ops per semop call = 100
semaphore max value = 32767

and then startup ASM instance again. (ASM instance started)

struggle to Upgrade Oracle Database 11.2.0.1 to 11.2.0.2

After I wrote about Oracle Database 11gR2 (11.2.0.2) is available on Linux x86-64, x86. It's time to test for upgrading. On my test system, I install Oracle Grid Infrastructure for Standalone Server (ASM) and Database 11.2.0.1, then test to upgrade 11.2.0.1 => 11.2.0.2:

SQL> select * from v$version;
BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
PL/SQL Release 11.2.0.1.0 - Production
CORE 11.2.0.1.0 Production
TNS for Linux: Version 11.2.0.1.0 - Production
NLSRTL Version 11.2.0.1.0 - Production

I use ASM instance, So I have to upgrade Oracle Grid Infrastructure before, check and stop HAS

# /oracle/product/11.2.0/grid/bin/crsctl stat res
NAME=ora.DATA.dg
TYPE=ora.diskgroup.type
TARGET=ONLINE
STATE=ONLINE on oel

NAME=ora.LISTENER.lsnr
TYPE=ora.listener.type
TARGET=ONLINE
STATE=ONLINE on oel

NAME=ora.asm
TYPE=ora.asm.type
TARGET=ONLINE
STATE=ONLINE on oel

NAME=ora.cssd
TYPE=ora.cssd.type
TARGET=ONLINE
STATE=ONLINE on oel

NAME=ora.diskmon
TYPE=ora.diskmon.type
TARGET=ONLINE
STATE=ONLINE on oel

NAME=ora.orcl.db
TYPE=ora.database.type
TARGET=ONLINE
STATE=ONLINE on oel

# /oracle/product/11.2.0/grid/bin/crsctl stop has
CRS-2791: Starting shutdown of Oracle High Availability Services-managed resources on 'oel'
.
.
.
CRS-2793: Shutdown of Oracle High Availability Services-managed resources on 'oel' has completed
CRS-4133: Oracle High Availability Services has been stopped.

Grid infrastructure Upgrading uses p10098816_112020_Linux-x86-64_3of7.zip file and database use p10098816_112020_Linux-x86-64_1of7.zip, p10098816_112020_Linux-x86-64_2of7.zip files. On step, use "runInstaller" to install (choose Upgrade)
Find warning!!! I can not install to replace old Grid Home.
I have to use new Home.
then press Install, find step to run "rootupgrade.sh" script.

# /oracle/product/11.2.0/grid_new/rootupgrade.sh
Running Oracle 11g root script...
The following environment variables are set as:

ORACLE_OWNER= oracle
ORACLE_HOME= /oracle/product/11.2.0/grid_new

Enter the full pathname of the local bin directory: [/usr/local/bin]:
The contents of "dbhome" have not changed. No need to overwrite.
The file "oraenv" already exists in /usr/local/bin. Overwrite it? (y/n)
[n]: y
Copying oraenv to /usr/local/bin ...
The file "coraenv" already exists in /usr/local/bin. Overwrite it? (y/n)
[n]: y
Copying coraenv to /usr/local/bin ...

Entries will be added to the /etc/oratab file as needed by
Database Configuration Assistant when a database is created
Finished running generic part of root script.
Now product-specific root actions will be performed.
Using configuration parameter file: /oracle/product/11.2.0/grid_new/crs/install/crsconfig_params
Creating trace directory

Error while detecting Oracle Grid Infrastructure. ASMCA needs Oracle Grid Infrastructure to configure ASM.

ASM upgrade failed, see logs for details
Failed to upgrade ASM for Oracle Restart configuration at /oracle/product/11.2.0/grid_new/crs/install/crsconfig_lib.pm line 12653.
/oracle/product/11.2.0/grid_new/perl/bin/perl -I/oracle/product/11.2.0/grid_new/perl/lib -I/oracle/product/11.2.0/grid_new/crs/install /oracle/product/11.2.0/grid_new/crs/install/roothas.pl execution failed

So, run "asmca" but find error.

One idea, copy orapw+ASM file from old grid home :

# cp -a /oracle/product/11.2.0/grid/dbs/orapw+ASM /oracle/product/11.2.0/grid_new/dbs/

then run "rootupgrade.sh" again.

# /oracle/product/11.2.0/grid_new/rootupgrade.sh
Running Oracle 11g root script...

The following environment variables are set as:
ORACLE_OWNER= oracle
ORACLE_HOME= /oracle/product/11.2.0/grid_new

Enter the full pathname of the local bin directory: [/usr/local/bin]:
The contents of "dbhome" have not changed. No need to overwrite.
The file "oraenv" already exists in /usr/local/bin. Overwrite it? (y/n)
[n]: y
Copying oraenv to /usr/local/bin ...
The file "coraenv" already exists in /usr/local/bin. Overwrite it? (y/n)
[n]: y
Copying coraenv to /usr/local/bin ...

Entries will be added to the /etc/oratab file as needed by
Database Configuration Assistant when a database is created
Finished running generic part of root script.
Now product-specific root actions will be performed.
Using configuration parameter file: /oracle/product/11.2.0/grid_new/crs/install/crsconfig_params
Creating trace directory

Error while detecting Oracle Grid Infrastructure. ASMCA needs Oracle Grid Infrastructure to configure ASM.

ASM upgrade failed, see logs for details
Failed to upgrade ASM for Oracle Restart configuration at /oracle/product/11.2.0/grid_new/crs/install/crsconfig_lib.pm line 12653.
/oracle/product/11.2.0/grid_new/perl/bin/perl -I/oracle/product/11.2.0/grid_new/perl/lib -I/oracle/product/11.2.0/grid_new/crs/install /oracle/product/11.2.0/grid_new/crs/install/roothas.pl execution failed

but can not solve, try to start ASM instance to check:

$ export ORACLE_HOME=/oracle/product/11.2.0/grid_new
$ export ORACLE_SID=+ASM
$ /oracle/product/11.2.0/grid_new/bin/sqlplus / as sysasm
SQL*Plus: Release 11.2.0.2.0 Production on Thu Sep 16 03:04:04 2010
Copyright (c) 1982, 2010, Oracle. All rights reserved.
Connected to an idle instance.
SQL> startup
ORA-01078: failure in processing system parameters ORA-29701: unable to connect to Cluster Synchronization Service

From error, I have to start CSS before, then idea "run rootupgrade.sh again"-> fixed by modified /etc/oracle/olr.loc and run "rootupgrade.sh" again:

# vi /etc/oracle/olr.loc

Old:
crs_home=/oracle/product/11.2.0/grid
New:
crs_home=/oracle/product/11.2.0/grid_new

# /oracle/product/11.2.0/grid_new/rootupgrade.sh
Running Oracle 11g root script...

The following environment variables are set as:
ORACLE_OWNER= oracle
ORACLE_HOME= /oracle/product/11.2.0/grid_new

Enter the full pathname of the local bin directory: [/usr/local/bin]:
The contents of "dbhome" have not changed. No need to overwrite.
The contents of "oraenv" have not changed. No need to overwrite.
The contents of "coraenv" have not changed. No need to overwrite.

Entries will be added to the /etc/oratab file as needed by
Database Configuration Assistant when a database is created
Finished running generic part of root script.
Now product-specific root actions will be performed.
Using configuration parameter file: /oracle/product/11.2.0/grid_new/crs/install/crsconfig_params
Creating OCR keys for user 'oracle', privgrp 'oinstall'..
Operation successful.
LOCAL ONLY MODE
Successfully accumulated necessary OCR keys.
Creating OCR keys for user 'root', privgrp 'root'..
Operation successful.
CRS-4664: Node oel successfully pinned.
Adding daemon to inittab
PRKO-3226 : Upgrade from version 11.2.0.1.0 to version 11.2.0.1.0 using srvctl upgrade model command of version 11.2.0.2.0 is not supported
srvctl upgrade model -first ... failed
PRKO-3226 : Upgrade from version 11.2.0.1.0 to version 11.2.0.1.0 using srvctl upgrade model command of version 11.2.0.2.0 is not supported
srvctl upgrade model -last ... failed
ACFS-9459: ADVM/ACFS is not supported on this OS version: '2.6.35.4'
oel 2010/09/16 03:17:37 /oracle/product/11.2.0/grid_new/cdata/oel/backup_20100916_031737.olr
oel 2010/09/16 01:00:46 /oracle/product/11.2.0/grid/cdata/oel/backup_20100916_010046.olr
Successfully configured Oracle Grid Infrastructure for a Standalone Server

Everything look ok... and press install to continue...

after upgraded Grid Infrastructure, check

# /oracle/product/11.2.0/grid_new/bin/crsctl stat res
NAME=ora.DATA.dg
TYPE=ora.diskgroup.type
TARGET=ONLINE
STATE=ONLINE on oel

NAME=ora.LISTENER.lsnr
TYPE=ora.listener.type
TARGET=ONLINE
STATE=ONLINE on oel

NAME=ora.asm
TYPE=ora.asm.type
TARGET=ONLINE
STATE=ONLINE on oel

NAME=ora.cssd
TYPE=ora.cssd.type
TARGET=ONLINE
STATE=ONLINE on oel

NAME=ora.diskmon
TYPE=ora.diskmon.type
TARGET=ONLINE
STATE=ONLINE on oel

NAME=ora.evmd
TYPE=ora.evm.type
TARGET=ONLINE
STATE=ONLINE on oel

NAME=ora.ons
TYPE=ora.ons.type
TARGET=OFFLINE
STATE=OFFLINE

NAME=ora.orcl.db
TYPE=ora.database.type
TARGET=ONLINE
STATE=ONLINE on oel

Note: error from rootupgrade.sh, I fixed by 1. copy password ASM file from old home to new home(for start ASM instance ) 2. modified /etc/oracle/olr.loc file (crs_home) after I used "ps -aef" and other idea to check.

Then install Oracle Database 11.2.0.2 by "runInstaller" (use Upgrade)
and found warning(oracle recommends to install out-of-place)
install new home
and then upgrade database.

After upgraded, check

SQL> select * from v$version;
BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production
PL/SQL Release 11.2.0.2.0 - Production
CORE 11.2.0.2.0 Production
TNS for Linux: Version 11.2.0.2.0 - Production
NLSRTL Version 11.2.0.2.0 - Production

but ohasd, init.ohasd files still wrong about CRS_HOME.

# cd /etc/init.d/
# cp ohasd ohasd.org
# cp init.ohasd init.ohasd.org

Change /oracle/product/11.2.0/grid to /oracle/product/11.2.0/grid_new in ohasd, init.ohasd files.

We may find error from "rootupgrade.sh", we should solve it before (try to use "rootupgrade.sh" with no error). If find "The current version of Oracle Grid Infrastructure is 11.2.0.1.0... " while upgrade database(dbua), make sure step rootupgrade.sh done.

ORA-29786: SIHA attribute GET failed with error [Attribute 'SPFILE' sts[200] lsts[0]]

When we created spfile in ASM instance and found ORA-29786.

SQL> create spfile='+DATA' from memory;
create spfile='+DATA' from pfile
*
ERROR at line 1:
ORA-29786: SIHA attribute GET failed with error [Attribute 'SPFILE' sts[200]
lsts[0]]

If we check oracle support 976075.1:

11gR2, ASM instance is a resoure in CRS repository also in single instance installations. Hence, it must be registered to OCR before.

then, check and solve:

$ crsctl stat res | grep NAME\= |grep asm

I had no asm in my OCR.

$ srvctl add asm -l LISTENER -p '+DATA' -d +DATA
PRCR-1001 : Resource ora.LISTENER.lsnr does not exist
$ srvctl add listener -l LISTENER -s -o /u01/app/oracle/product/11.2.0/grid_1
$ srvctl add asm -l LISTENER -p '+DATA' -d +DATA
$ crsctl stat res | grep NAME\= |grep asm
NAME=ora.asm

then try to create spfile again:

SQL> show parameter spfile;
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
spfile string

SQL> create spfile='+DATA' from memory;
File created.

SQL> shutdown

this case, I used "srvctl" to help (start)

$ srvctl start asm

check spfile in ASM instance.

SQL> show parameter spfile
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
spfile string +DATA/asm/asmparameterfile/registry.253.729779795

So, ORA-29786 error, we can solve to register ASM in OCR.

ORA-15477: cannot communicate with the volume driver

ORA-15477: cannot communicate with the volume driver
when add volume...

SQL> alter diskgroup data add volume 'asm_vol1' size 5G;
alter diskgroup data add volume 'asm_vol1' size 5G
*
ERROR at line 1:
ORA-15032: not all alterations performed
ORA-15477: cannot communicate with the volume driver

SQL> alter diskgroup data set attribute 'compatible.advm'='11.2';
alter diskgroup data set attribute 'compatible.advm'='11.2'
*
ERROR at line 1:
ORA-15032: not all alterations performed
ORA-15242: could not set attribute compatible.advm
ORA-15238: 11.2 is not a valid value for attribute compatible.advm
ORA-15477: cannot communicate with the volume driver

# acfsutil registry -l
acfsutil registry: CLSU-00100: Operating System function: open64 (/dev/ofsctl) failed with error data: 2
acfsutil registry: CLSU-00101: Operating System error message: No such file or directory
acfsutil registry: CLSU-00103: error location: OOF_1
acfsutil registry: ACFS-00502: Failed to communicate with the ACFS driver. Verify the ACFS driver has been loaded.

then load ACFS.

# /u01/app/grid/bin/acfsload start -s
ACFS-9327: Verifying ADVM/ACFS devices.
ACFS-9322: done.

Try again.

SQL> alter diskgroup data set attribute 'compatible.advm'='11.2';

Diskgroup altered.

SQL> alter diskgroup data add volume 'asm_vol1' size 5G;

Diskgroup altered.

SQL> !ls -l /dev/asm/asm_v*
brwxrwx--- 1 root oinstall 252, 86529 Mar 9 00:11 /dev/asm/asm_vol1-169

ORA-15041 - ASM Disk Group Unbalanced

Use ASM Disk Group NORMAL redundancy (11gR2) and found error while create tablespace.

SQL> create tablespace test datafile '+DATA' size 5G;
create tablespace test datafile '+DATA' size 5G
*
ERROR at line 1:
ORA-01119: error in creating database file '+DATA'
ORA-17502: ksfdcre:4 Failed to create file +DATA
ORA-15041: diskgroup "DATA" space exhausted

What Wrong??? - I have free size on "DATA", Check ASM Disk Group.

SQL> select name, total_mb, free_mb, required_mirror_free_mb, usable_file_mb ,type from v$asm_diskgroup where name='DATA';

NAME TOTAL_MB FREE_MB REQUIRED_MIRROR_FREE_MB USABLE_FILE_MB TYPE
------------------------------ ---------- ---------- ----------------------- -------------- ------
DATA 257807 112233 23437 44398 NORMAL

Check (ASM) alert log file.

kfdpDumpBg()
kfdpDumpBg() - Done

Check on ASM Instance and Investigate the problem.

SQL> select path, free_mb,total_mb,state from v$asm_disk where group_number in (select group_number from v$asm_diskgroup where name='DATA');

PATH FREE_MB TOTAL_MB STATE
------------------- ---------- ---------- --------
/dev/sdl 1017 23437 NORMAL
/dev/sdb 7867 23437 NORMAL
/dev/sdk 7988 23437 NORMAL
/dev/sdq 23211 23437 NORMAL
/dev/sdf 7864 23437 NORMAL
/dev/sdg 14819 23437 NORMAL
/dev/sdc 13827 23437 NORMAL
/dev/sdi 6971 23437 NORMAL
/dev/sde 14829 23437 NORMAL
/dev/sdj 1 23437 NORMAL
/dev/sdd 13839 23437 NORMAL

Disk Group UNBALANCE, then

SQL> alter diskgroup data rebalance power 11;

Diskgroup altered.

Check.

SQL> select path, free_mb,total_mb,state from v$asm_disk where group_number in (select group_number from v$asm_diskgroup where name='DATA');

PATH FREE_MB TOTAL_MB STATE
------------------- ---------- ---------- --------
/dev/sdl 1018 23437 NORMAL
/dev/sdb 7867 23437 NORMAL
/dev/sdk 7988 23437 NORMAL
/dev/sdq 23209 23437 NORMAL
/dev/sdf 7864 23437 NORMAL
/dev/sdg 14820 23437 NORMAL
/dev/sdc 13827 23437 NORMAL
/dev/sdi 6971 23437 NORMAL
/dev/sde 14830 23437 NORMAL
/dev/sdj 0 23437 NORMAL
/dev/sdd 13839 23437 NORMAL

Nothing to resolve... Check on v$operation.

SQL> select group_number, operation, state, error_code from v$asm_operation;

GROUP_NUMBER OPERA STAT ERROR_CODE
------------ ----- ---- --------------------------------------------
1 REBAL ERRS ORA-15041

Try to check & repair ASM Disk Group.

SQL> alter diskgroup data check all repair;

Diskgroup altered.

SQL> select group_number, operation, state, error_code from v$asm_operation;

GROUP_NUMBER OPERA STAT ERROR_CODE
------------ ----- ---- --------------------------------------------
1 REBAL ERRS ORA-15041

SQL> select path, free_mb,total_mb,state from v$asm_disk where group_number in (select group_number from v$asm_diskgroup where name='DATA');

PATH FREE_MB TOTAL_MB STATE
------------------- ---------- ---------- --------
/dev/sdl 1018 23437 NORMAL
/dev/sdb 7867 23437 NORMAL
/dev/sdk 7988 23437 NORMAL
/dev/sdq 23209 23437 NORMAL
/dev/sdf 7864 23437 NORMAL
/dev/sdg 14820 23437 NORMAL
/dev/sdc 13827 23437 NORMAL
/dev/sdi 6971 23437 NORMAL
/dev/sde 14830 23437 NORMAL
/dev/sdj 0 23437 NORMAL
/dev/sdd 13839 23437 NORMAL

Nothing to resolve... - "DATA" ASM Disk Group still unbalance, and v$asm_operation show error.
think & make Idea: Move some files or resize some files on "DATA" Disk Group, so login database and make something.
Example:

SQL> alter database tempfile '+DATA/orcl/tempfile/temp.263.712603677' resize 1G;

Database altered.

Check on ASM again.

SQL> select path, free_mb,total_mb,state from v$asm_disk where group_number in (select group_number from v$asm_diskgroup where name='DATA');

PATH FREE_MB TOTAL_MB STATE
------------------- ---------- ---------- --------
/dev/sdl 1685 23437 NORMAL
/dev/sdb 8322 23437 NORMAL
/dev/sdk 8357 23437 NORMAL
/dev/sdq 22855 23437 NORMAL
/dev/sdf 8324 23437 NORMAL
/dev/sdg 15013 23437 NORMAL
/dev/sdc 14043 23437 NORMAL
/dev/sdi 7386 23437 NORMAL
/dev/sde 15011 23437 NORMAL
/dev/sdj 711 23437 NORMAL
/dev/sdd 14054 23437 NORMAL

Check v$asm_operation.

SQL> select group_number, operation, state, power, error_code from v$asm_operation;

GROUP_NUMBER OPERA STAT ERROR_CODE
------------ ----- ---- --------------------------------------------
1 REBAL RUN

ASM instance... "DATA" Disk Group rebalancing, So Wait... and check

SQL> select group_number, operation, state, power, error_code from v$asm_operation;

no rows selected

SQL> select path, free_mb,total_mb,state from v$asm_disk where group_number in (select group_number from v$asm_diskgroup where name='DATA');

PATH FREE_MB TOTAL_MB STATE
------------------- ---------- ---------- --------
/dev/sdl 10524 23437 NORMAL
/dev/sdb 10524 23437 NORMAL
/dev/sdk 10528 23437 NORMAL
/dev/sdq 10532 23437 NORMAL
/dev/sdf 10521 23437 NORMAL
/dev/sdg 10523 23437 NORMAL
/dev/sdc 10522 23437 NORMAL
/dev/sdi 10526 23437 NORMAL
/dev/sde 10522 23437 NORMAL
/dev/sdj 10522 23437 NORMAL
/dev/sdd 10523 23437 NORMAL

SQL> alter diskgroup data rebalance power 1;

Diskgroup altered

Disks rebalanced, Try to create tablespace again.

SQL> select name, total_mb, free_mb, required_mirror_free_mb, usable_file_mb ,type from v$asm_diskgroup where name='DATA';

NAME TOTAL_MB FREE_MB REQUIRED_MIRROR_FREE_MB USABLE_FILE_MB TYPE
------------------------------ ---------- ---------- ----------------------- -------------- ------
DATA 257807 115767 12916 51425 NORMAL

SQL> create tablespace test datafile '+DATA' size 30G;

Tablespace created.

SQL> select path, free_mb,total_mb,state from v$asm_disk where group_number in (select group_number from v$asm_diskgroup where name='DATA');

PATH FREE_MB TOTAL_MB STATE
------------------- ---------- ---------- --------
/dev/sdl 4936 23437 NORMAL
/dev/sdb 4931 23437 NORMAL
/dev/sdk 4931 23437 NORMAL
/dev/sdq 4932 23437 NORMAL
/dev/sdf 4922 23437 NORMAL
/dev/sdg 4933 23437 NORMAL
/dev/sdc 4936 23437 NORMAL
/dev/sdi 4940 23437 NORMAL
/dev/sde 4933 23437 NORMAL
/dev/sdj 4928 23437 NORMAL
/dev/sdd 4938 23437 NORMAL

What this told me? Make Sure ASM Disk Group no problem (REBALANCE).

Creating Database 11gR1 on ASM 11gR2

On Forums - show 10g database creating with 11gR2 ASM failed.
If we check on oracle support, we can use [ID 949073.1] ... That's helpful issue to fix.

To run older databases on 11gR2 ASM and Clusterware we need to create a persistent configuration for the nodes(pinning a node). Because Cluster configuration in 11gR2 is dynamic. This dynamic configuration is incompatible with older database releases.

On Oracle Document about "Pinning Cluster Nodes for Oracle Database Release 10.x or 11.x".

Time To Test It... (Creating Database 11gR1 on ASM 11gR2) and error.

ORA-19624: operation failed, retry possible
ORA-19870: error while restoring backup piece /oracle/product/11.1/db/assistants/dbca/templates/Seed_Database.dfb
ORA-19504: failed to create file "+DISK02"
ORA-17502: ksfdcre:4 Failed to create file +DISK02
ORA-15001: diskgroup "DISK02" does not exist or is not mounted
ORA-15077: could not locate ASM instance serving a required diskgroup
ORA-29701: unable to connect to Cluster Manager
ORA-06512: at "SYS.X$DBMS_BACKUP_RESTORE", line 5662
ORA-06512: at line 40

Check ASM... about database compatibility.

SQL> select name, compatibility, database_compatibility, state from v$asm_diskgroup_stat;

NAME COMPATIBILITY DATABASE_COMPAT STATE
------------------------------ --------------- --------------- -----------
DISK00 11.2.0.0.0 10.1.0.0.0 MOUNTED
DISK01 11.1.0.0.0 11.1.0.0.0 MOUNTED
DISK02 11.1.0.0.0 11.1.0.0.0 MOUNTED
DISK03 11.2.0.0.0 11.2.0.0.0 MOUNTED

then pin node, by crsctl pin css

# cd /oracle/grid2/
# cd bin/
# ./olsnodes -t -n
rhel5-test 1 Unpinned

# ./crsctl pin css -n rhel5-test
CRS-4664: Node rhel5-test successfully pinned.

# ./olsnodes -t -n
rhel5-test 1 Pinned

then create database again... and no problem.

then register my database(11gR1) in Grid.

$ srvctl add database -d orcl11gr1 -o /oracle/product/11.1/db -p +DISK02/orcl11gr1/spfileorcl11gr1.ora -y AUTOMATIC
$ srvctl add instance -d orcl11gr1 -i orcl11gr1 -n rhel5-test

$ srvctl start database -d orcl11gr1
$ srvctl status database -d orcl11gr1
Instance orcl11gr1 is running on node rhel5-test

$ ./crsstat ora.orcl11gr1
HA Resource Target State (Host)
----------- ------ -----
ora.orcl11gr1.db ONLINE ONLINE on rhel5-test

How to register ASM diskgroup in Grid Infrastructure

It's a cool today, Someone are working, Someone are on vacation.
By the way. Have good holidays ;)

I read/tested Relocating Grid Infrastructure. After I relocated my Grid Infrastructure. I'd like to register ASM diskgroup else.

$ ./crsstat -word dg
HA Resource Type Target State (Host)
----------- ------ ------ -----
ora.DISK00.dg ora.diskgroup.type ONLINE ONLINE on rhel5-test

Some disk groups... not registered in Grid. I had to register by manual, then I checked srvctl command-line, but no "add" diskgroup.

$ srvctl -h | grep diskgroup | grep dg_name
Usage: srvctl start diskgroup -g <dg_name> [-n "<node_list>"]
Usage: srvctl stop diskgroup -g <dg_name> [-n "<node_list>"] [-f]
Usage: srvctl status diskgroup -g <dg_name> [-n "<node_list>"] [-a]
Usage: srvctl enable diskgroup -g <dg_name> [-n "<node_list>"]
Usage: srvctl disable diskgroup -g <dg_name> [-n "<node_list>"]
Usage: srvctl remove diskgroup -g <dg_name> [-f]

Actually, ASM disk groups are registered in Grid by Automatic, After we mounted them ???

$ sqlplus / as sysasm

SYS> alter diskgroup DISK01 mount;

Diskgroup altered.

SYS> alter diskgroup DISK02 mount;

Diskgroup altered.

SYS> alter diskgroup DISK03 mount;

Diskgroup altered.

$ ./crsstat -word dg
HA Resource Type Target State (Host)
----------- ------ ------ -----
ora.DISK00.dg ora.diskgroup.type ONLINE ONLINE on rhel5-test
ora.DISK01.dg ora.diskgroup.type ONLINE ONLINE on rhel5-test
ora.DISK02.dg ora.diskgroup.type ONLINE ONLINE on rhel5-test
ora.DISK03.dg ora.diskgroup.type ONLINE ONLINE on rhel5-test

By the way, It's just fun today. Nothing special.

Reference:
#!/bin/bash
# crsstat v. 0.01
# By surachart
if [ $# -gt 4 ]
then
echo "Please Check arguments."
echo
echo "./crsstat [-word|--word] resource_word [-crshome|--crshome] CRS_HOME "
echo
echo " ./crsstat"
echo " ./crsstat -word orcl"
echo ' ./crsstat -word orcl -crshome $CRS_HOME'
exit 0
fi

while [ $# -gt 0 ]; do
case "$1" in
-h|--h|-help|--help)
echo "./crsstat [-word|--word] resource_word [-crshome|--crshome] CRS_HOME "
echo
echo " ./crsstat"
echo " ./crsstat -word orcl"
echo ' ./crsstat -word orcl -crshome $CRS_HOME'
exit 0
;;
-word|--word)
GREP_KEY=$2
shift
;;
-crshome|--crshome)
ORA_CRS_HOME=$2
shift
;;
esac
shift
done

if [ -z $ORA_CRS_HOME ]
then
ORA_CRS_HOME=$CRS_HOME
if [ ! -d $ORA_CRS_HOME ]
then
echo "Please Check CRS_HOME Environment ($ORA_CRS_HOME)"
exit 1
fi

elif [ ! -d $ORA_CRS_HOME ]
then
ORA_CRS_HOME=$CRS_HOME
if [ ! -d $ORA_CRS_HOME ]
then
echo "Please Check CRS_HOME Environment ($ORA_CRS_HOME)"
exit 1
fi
fi

CRS_STAT=$ORA_CRS_HOME/bin/crs_stat

if [ ! -x $CRS_STAT ]
then
echo "Please Check CRS_HOME Environment ($ORA_CRS_HOME)"
exit 2
fi

AWK=/usr/bin/awk

if [ ! -x $AWK ]
then
AWK=/bin/awk
fi

$AWK \
'BEGIN {printf "%-40s %-25s %-10s %-12s\n", "HA Resource", "Type", "Target", "State (Host)"; printf "%-40s %-25s %-10s %-12s\n", "-----------", "------", "------", "-----";}'
$CRS_STAT | $AWK \
'BEGIN { FS="="; state = 0; }
$1~/NAME/ && $2~/'$GREP_KEY'/ {appname = $2; state=1;}
state == 0 {next;}
$1~/TYPE/ && state == 1 {apptype = $2; state=2;}
$1~/TARGET/ && state == 2 {apptarget = $2; state=3;}
$1~/STATE/ && state == 3 {appstate = $2; state=4;}
state == 4 {printf "%-40s %-25s %-10s %-12s\n", appname,apptype, apptarget, appstate;state=0}'

MOVING DATAFILE IN ASM BY ASMCMD(cp)

After I wrote about Moving Datafile(system) from one ASM Diskgroup to Another, From example I used RMAN to move datafile. I read ocpdba's comment and curious Moving Datafile in ASM by asmcmd(cp). then start to test..

SQL> select name from v$asm_diskgroup;

NAME
------------------------------
DISK01
DISK02

$ rman target /

Recovery Manager: Release 11.2.0.1.0 - Production
connected to target database: ORCL (DBID=1224745511)

RMAN> REPORT SCHEMA;

using target database control file instead of recovery catalog
Report of database schema for database with db_unique_name ORCL

List of Permanent Datafiles
===========================
File Size(MB) Tablespace RB segs Datafile Name
---- -------- -------------------- ------- ------------------------
4 5 USERS *** +DISK02/orcl/datafile/users.336.701196307

This test, I want to move datafile from +DISK02 to +DISK01
.. Bring datafile offline

RMAN> SQL 'ALTER DATABASE DATAFILE 4 OFFLINE';

sql statement: ALTER DATABASE DATAFILE 4 OFFLINE

After bring DATAFILE offline... then copy datafile by asmcmd

$ asmcmd -p
ASMCMD [+] > cp +DISK02/orcl/datafile/users.336.701196307 +DISK01/orcl/datafile/
copying +DISK02/orcl/datafile/users.336.701196307 -> +DISK01/orcl/datafile/users.336.701196307
ASMCMD-08016: copy source->'+DISK02/orcl/datafile/users.336.701196307' and target->'+DISK01/orcl/datafile/users.336.701196307' failed
ORA-19505: failed to identify file "+DISK01/orcl/datafile/users.336.701196307"
ORA-17502: ksfdcre:4 Failed to create file +DISK01/orcl/datafile/users.336.701196307
ORA-15046: ASM file name '+DISK01/orcl/datafile/users.336.701196307' is not in single-file creation form
ORA-06512: at "SYS.X$DBMS_DISKGROUP", line 397
ORA-06512: at line 3 (DBD ERROR: OCIStmtExecute)

Can't copy, so copy to new file name

ASMCMD [+] > cp +DISK02/orcl/datafile/users.336.701196307 +DISK01/orcl/datafile/users01.dbf
copying +DISK02/orcl/datafile/users.336.701196307 -> +DISK01/orcl/datafile/users01.dbf


ASMCMD [+] > ls -la +DISK02/orcl/datafile/users.336.701196307
Type Redund Striped Time Sys Name
DATAFILE UNPROT COARSE OCT 25 17:00:00 Y none => users.336.701196307

ASMCMD [+] > ls -la +DISK01/orcl/datafile/users01.dbf
Type Redund Striped Time Sys Name
N users01.dbf => +DISK01/ASM/DATAFILE/users01.dbf.264.701198411

ASMCMD [+] > ls -la +DISK01/ASM/DATAFILE/users01.dbf.264.701198411
Type Redund Striped Time Sys Name
DATAFILE MIRROR COARSE OCT 25 17:00:00 Y +DISK01/ORCL/DATAFILE/users01.dbf => users01.dbf.264.701198411

After copied datafile... then rename file in database

SQL> ALTER DATABASE RENAME FILE '+DISK02/orcl/datafile/users.336.701196307' TO '+DISK01/ORCL/DATAFILE/users01.dbf';

Database altered.

Check in alert log...
Sun Oct 25 17:24:55 2009
ALTER DATABASE RENAME FILE '+DISK02/orcl/datafile/users.336.701196307' TO '+DISK01/ORCL/DATAFILE/users01.dbf'
Deleted Oracle managed file +DISK02/orcl/datafile/users.336.701196307
Completed: ALTER DATABASE RENAME FILE '+DISK02/orcl/datafile/users.336.701196307' TO '+DISK01/ORCL/DATAFILE/users01.dbf'

"ALTER DATABASE RENAME FILE" with ASM(OMF), that delete old datafile.

After renamed datafile in Database, then check + recover + bring datafile online.

RMAN> REPORT SCHEMA;

Report of database schema for database with db_unique_name ORCL

List of Permanent Datafiles
===========================
File Size(MB) Tablespace RB segs Datafile Name
---- -------- -------------------- ------- ------------------------
4 5 USERS *** +DISK01/orcl/datafile/users01.dbf

RMAN> RECOVER DATAFILE 4;

Starting recover at 25-OCT-09
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=157 instance=orcl1 device type=DISK

starting media recovery
media recovery complete, elapsed time: 00:00:02

Finished recover at 25-OCT-09

RMAN> SQL 'ALTER DATABASE DATAFILE 4 ONLINE';

sql statement: ALTER DATABASE DATAFILE 4 ONLINE

RMAN> REPORT SCHEMA;

Report of database schema for database with db_unique_name ORCL

List of Permanent Datafiles
===========================
File Size(MB) Tablespace RB segs Datafile Name
---- -------- -------------------- ------- ------------------------
4 5 USERS *** +DISK01/orcl/datafile/users01.dbf

That's a good way... but I still interested about file (name, type) in ASM, after copied ;)

Moving datafile (system) from one ASM Diskgroup to Another

We have to move database file to NEW diskgroup... In case, we have to move system + sysaux database files as well.

With Oracle ASM, when we need to move database file from one diskgroup to another. we have to use "rman" to help.

About moving system + sysaux database files, we need database (mount)

SQL> select file_name, FILE_ID from dba_data_files where file_name like '%sys%';

FILE_NAME FILE_ID

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

+OLD/db/datafile/sysaux.260.699468081 3

+OLD/db/datafile/system.259.699468079 1

SQL> SELECT name FROM v$asm_diskgroup;

NAME

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

OLD

NEW

We need to move database files to NEW diskgroup

SQL> shutdown immediate;

SQL> startup mount;

Connect target database by "rman" command-line :

$ rman target /

connected to target database: DB (DBID=1043389676)

RMAN> REPORT SCHEMA;

File Size(MB) Tablespace RB segs Datafile Name

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

1 560 SYSTEM *** +OLD/db/datafile/system.259.699468079

3 500 SYSAUX *** +OLD/db/datafile/sysaux.260.699468081

Backup AS COPY "database files" to NEW diskgroup, then SWITCH DATAFILE to use NEW diskgroup

RMAN> run {

BACKUP AS COPY DATAFILE 1 FORMAT "+NEW";

BACKUP AS COPY DATAFILE 3 FORMAT "+NEW";

SWITCH DATAFILE "+OLD/db/datafile/sysaux.260.699468081" TO COPY;

SWITCH DATAFILE "+OLD/db/datafile/system.259.699468079" TO COPY;

}

Open database...

SQL> alter database open;

SQL> select file_name, FILE_ID from dba_data_files where file_name like '%sys%';

FILE_NAME FILE_ID

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

+NEW/db/datafile/sysaux.291.700906921 3

+NEW/db/datafile/system.294.700906903 1

RMAN> REPORT SCHEMA;

File Size(MB) Tablespace RB segs Datafile Name

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

1 560 SYSTEM *** +NEW/db/datafile/system.294.700906903

3 500 SYSAUX *** +NEW/db/datafile/sysaux.291.700906921

After make sure...(no error and database files used in NEW diskgroup), then DELETE old datafilecopy(original)...

RMAN> run {

DELETE DATAFILECOPY "+OLD/db/datafile/sysaux.260.699468081";

DELETE DATAFILECOPY "+OLD/db/datafile/system.259.699468079";

}

reference: http://download.oracle.com/docs/cd/B28359_01/backup.111/b28270/rcmasmmi.htm

If not "SYSTEM" database file. we can move database file while database open...

SQL> select file_name, FILE_ID from dba_data_files where file_name like '%users%';

FILE_NAME FILE_ID

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

+OLD/db/datafile/users.258.699468081 4

SQL> SELECT name FROM v$asm_diskgroup;

NAME

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

OLD

NEW

We need to move database file (4) to NEW diskgroup

Connect target database by "rman" command-line

$ rman target /

connected to target database: DB (DBID=1043389676)

Check...

RMAN> REPORT SCHEMA;

File Size(MB) Tablespace RB segs Datafile Name

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

4 5 USERS *** +OLD/db/datafile/users.258.699468081

Bring database file to offline

RMAN> SQL "ALTER DATABASE DATAFILE ' ' +OLD/db/datafile/users.258.699468081 ' ' OFFLINE";

or

RMAN> SQL 'ALTER DATABASE DATAFILE 4 OFFLINE';

Backup as copy database file to NEW diskgroup

RMAN> BACKUP AS COPY DATAFILE "+OLD/db/datafile/users.258.699468081" FORMAT "+NEW";

or

RMAN> BACKUP AS COPY DATAFILE 4 FORMAT "+NEW";

Switch database file to NEW diskgroup

RMAN> SWITCH DATAFILE "+OLD/db/datafile/users.258.699468081" TO COPY;

and we need "RECOVER"...

RMAN> RECOVER DATAFILE 4;

Bring database file to online:

RMAN> SQL 'ALTER DATABASE DATAFILE 4 ONLINE';

Check...

RMAN> REPORT SCHEMA;

File Size(MB) Tablespace RB segs Datafile Name

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

4 5 USERS *** +NEW/db/datafile/users.257.700906939

SQL> select file_name, FILE_ID from dba_data_files where file_name like '%users%';

FILE_NAME FILE_ID

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

+NEW/db/datafile/users.257.700906939 4

After Make sure... DELETE old datafilecopy(original):

RMAN> DELETE DATAFILECOPY "+OLD/db/datafile/users.258.699468081";

...

_disable_interface_checking

sqlplus / as sysasm

SQL> startup

ORA-27504: IPC error creating OSD context

ORA-27300: OS system dependent operation:check if cable failed with status: 0

ORA-27301: OS failure message: Error 0

ORA-27302: failure occurred at: skgxpcini1

ORA-27303: additional information: requested interface eth1 interface not running set _disable_interface_checking = TRUE to disable this check for single instance cluster. Check output from if

"_disable_interface_checking" supports on Oracle Database since 10.2.0 (FALSE)

SQL> show parameter "_disable_interface_checking"

...Nothing...

When Private Interconnect is down in RAC, that make ASM doesn't start.

If need to start ASM(some node) anyway... To bypass by set: _disable_interface_checking = true

SQL> show parameter "_disable_interface_checking"

NAME TYPE VALUE

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

_disable_interface_checking boolean TRUE

ASM (DVM) & ACFS by command-lines

11gR2 feature... ASM Dynamic Volume Manager (ADVM) and ASM Clustered File System (ACFS), that make me excite, So I spent much time to get idea...

We can create ACFS by using asmca (link). and we can use command-lines(sql*plus, asmcmd) as well.

- Create a volume from an ASM diskgroup

Connect sql*plus with sysasm:
$ sqlplus / as sysasm

Add volume by "alter diskgroup":

SQL> alter diskgroup DISK03 add volume 'asm_vol1' size 5G;

Diskgroup altered.

Check :

SQL> SELECT dg.name AS diskgroup, v.volume_name, v.bytes_read, v.bytes_written FROM V$ASM_DISKGROUP dg, V$ASM_VOLUME_STAT v WHERE dg.group_number = v.group_number;

DISKGROUP VOLUME_NAME BYTES_READ BYTES_WRITTEN
------------------------------ ------------------------------ ---------- -------------
DISK03 ASM_VOL1 0 0

SQL> SELECT dg.name AS diskgroup, v.volume_name, v.volume_device, v.mountpath,v.state FROM V$ASM_DISKGROUP dg, V$ASM_VOLUME v WHERE dg.group_number = v.group_number;

DISKGROUP VOLUME_NAM VOLUME_DEVICE MOUNTPATH STATE
---------- ---------- ------------------------------ ------------------------------ --------
DISK03 ASM_VOL1 /dev/asm/asm_vol1-15 DISABLED

Or... use "asmcmd"

ASMCMD> volcreate -G DISK03 -s 5G asm_vol1

ASMCMD> volstat

DISKGROUP NUMBER / NAME: 3 / DISK03
---------------------------------------
VOLUME_NAME
READS BYTES_READ READ_TIME READ_ERRS
WRITES BYTES_WRITTEN WRITE_TIME WRITE_ERRS
-------------------------------------------------------------
ASM_VOL1
0 0 0 0
0 0 0 0

ASMCMD> volinfo -G DISK03 asm_vol1
Diskgroup Name: DISK03

Volume Name: ASM_VOL1
Volume Device: /dev/asm/asm_vol1-15
State: DISABLED
Size (MB): 5120
Resize Unit (MB): 256
Redundancy: UNPROT
Stripe Columns: 4
Stripe Width (K): 128
Usage:
Mountpath:

- Enable "asm_vol1' volume

SQL> !ls -l /dev/asm/asm_vol1-15
ls: /dev/asm/asm_vol1-15: No such file or directory

SQL> alter diskgroup DISK03 enable volume 'asm_vol1';

Diskgroup altered.

SQL> !ls -l /dev/asm/asm_vol1-15
brwxrwx--- 1 root osasm 252, 7681 Sep 4 11:26 /dev/asm/asm_vol1-15

SQL> SELECT dg.name AS diskgroup, v.volume_name, v.volume_device, v.mountpath,v.state FROM V$ASM_DISKGROUP dg, V$ASM_VOLUME v WHERE dg.group_number = v.group_number;

DISKGROUP VOLUME_NAM VOLUME_DEVICE MOUNTPATH STATE
---------- ---------- ------------------------------ ------------------------------ --------
DISK03 ASM_VOL1 /dev/asm/asm_vol1-15 ENABLED

Or... use "asmcmd"

$ ls -l /dev/asm/asm_vol1-15
ls: /dev/asm/asm_vol1-15: No such file or directory

ASMCMD> volenable -G DISK03 asm_vol1

ASMCMD> volinfo -G DISK03 asm_vol1
Diskgroup Name: DISK03

Volume Name: ASM_VOL1
Volume Device: /dev/asm/asm_vol1-15
State: ENABLED
Size (MB): 5120
Resize Unit (MB): 256
Redundancy: UNPROT
Stripe Columns: 4
Stripe Width (K): 128
Usage:
Mountpath:

$ ls -l /dev/asm/asm_vol1-15
brwxrwx--- 1 root osasm 252, 7681 Sep 4 11:43 /dev/asm/asm_vol1-15

- Register mount point

$ mkdir -p /oracle/product/acfsmounts/disk03_asm_vol1

$ su - root

# acfsutil registry -a -f /dev/asm/asm_vol1-15 /oracle/product/acfsmounts/disk03_asm_vol1
acfsutil registry: mount point /oracle/product/acfsmounts/disk03_asm_vol1 successfully added to Oracle Registry

# mount.acfs -o all
mount.acfs: ACFS-00591: error found in volume disk header
mount.acfs: ACFS-02037: File system not created on a Linux system. Cannot mount.

found error... so(make acfs)

# mkfs.acfs -f /dev/asm/asm_vol1-15
mkfs.acfs: version = 11.2.0.1.0.0
mkfs.acfs: on-disk version = 39.0
mkfs.acfs: volume = /dev/asm/asm_vol1-15
mkfs.acfs: volume size = 5368709120
mkfs.acfs: Format complete.

And mount:

# mount.acfs -o all

Or mount.acfs /dev/asm/asm_vol1-15 /oracle/product/acfsmounts/disk03_asm_vol1/

Check...

# df
Filesystem 1K-blocks Used Available Use% Mounted on

/dev/asm/asm_vol1-15 5242880 47512 5195368 1% /oracle/product/acfsmounts/disk03_asm_vol1

- Test create file

# cd /oracle/product/acfsmounts/disk03_asm_vol1
# ls -la
drwxr-xr-x 5 root root 4096 Sep 4 11:54 .ACFS

# touch file.txt
# ls file.txt
file.txt

- Check Again

SQL> SELECT dg.name AS diskgroup, v.volume_name, v.volume_device, v.mountpath,v.state FROM V$ASM_DISKGROUP dg, V$ASM_VOLUME v WHERE dg.group_number = v.group_number;

DISKGROUP VOLUME_NAM VOLUME_DEVICE MOUNTPATH STATE
---------- ---------- ------------------------------ ------------------------------------------ --------
DISK03 ASM_VOL1 /dev/asm/asm_vol1-15 /oracle/product/acfsmounts/disk03_asm_vol1 ENABLED

SQL> SELECT dg.name AS diskgroup, v.volume_name, v.bytes_read, v.bytes_written FROM V$ASM_DISKGROUP dg, V$ASM_VOLUME_STAT v WHERE dg.group_number = v.group_number;

DISKGROUP VOLUME_NAM BYTES_READ BYTES_WRITTEN
---------- ---------- ---------- -------------
DISK03 ASM_VOL1 372736 11745280

Or... use"asmcmd"

ASMCMD> volinfo -G DISK03 asm_vol1
Diskgroup Name: DISK03

Volume Name: ASM_VOL1
Volume Device: /dev/asm/asm_vol1-15
State: ENABLED
Size (MB): 5120
Resize Unit (MB): 256
Redundancy: UNPROT
Stripe Columns: 4
Stripe Width (K): 128
Usage: ACFS
Mountpath: /oracle/product/acfsmounts/disk03_asm_vol1

ASMCMD> volstat

DISKGROUP NUMBER / NAME: 3 / DISK03
---------------------------------------
VOLUME_NAME
READS BYTES_READ READ_TIME READ_ERRS
WRITES BYTES_WRITTEN WRITE_TIME WRITE_ERRS
-------------------------------------------------------------
ASM_VOL1
416 372736 6 0
2941 11745280 242 0

that's step to test it... and thank good blog help idea.