Create new ASM diskgroup for voting disk

1) Create new diskgroup for ASM diskgroup

CREATE DISKGROUP VOTEVPLEX EXTERNAL REDUNDANCY DISK 'ORCL:VOTEVPLEX' ATTRIBUTE 'au_size'='4M';

alter diskgroup VOTEVPLEX set attribute 'compatible.asm'='11.2;

2) Migrate voting file to new ASM diskgroup

[root@rac1 ~]# su - oragrid

+ASM1_rac1 $ sqlplus / as sysasm

SQL> CREATE DISKGROUP VOTEVPLEX EXTERNAL REDUNDANCY DISK 'ORCL:VOTEVPLEX' ATTRIBUTE 'au_size'='4M';

SQL> alter diskgroup VOTEVPLEX set attribute 'compatible.asm'='11.2';

Diskgroup altered.


Diskgroup created.

+ASM1_rac1 $ ocrcheck
Status of Oracle Cluster Registry is as follows :
         Version                  :          3
         Total space (kbytes)     :     262120
         Used space (kbytes)      :       4384
         Available space (kbytes) :     257736
         ID                       :   70121363
         Device/File Name         :     +OCRDG
                                    Device/File integrity check succeeded

                                    Device/File not configured

                                    Device/File not configured

                                    Device/File not configured

                                    Device/File not configured

         Cluster registry integrity check succeeded

         Logical corruption check bypassed due to non-privileged user


+ASM1_rac1 $ crsctl   query css votedisk
##  STATE    File Universal Id                File Name Disk group
--  -----    -----------------                --------- ---------
 1. ONLINE   12e224acaa594fcfbf245fd6486e1ed0 (ORCL:VOTEDC1) [VOTEDG]
 2. ONLINE   355415a275c64fabbf0159a43d7bac62 (ORCL:VOTEDC2) [VOTEDG]
Located 2 voting disk(s).

3) Check results

+ASM1_rac1 $ crsctl  replace votedisk +VOTEVPLEX
Successful addition of voting disk 0d588bf13f684f32bfb3d45688c28826.
Successful deletion of voting disk 12e224acaa594fcfbf245fd6486e1ed0.
Successful deletion of voting disk 355415a275c64fabbf0159a43d7bac62.
Successfully replaced voting disk group with +VOTEVPLEX.
CRS-4266: Voting file(s) successfully replaced

+ASM1_rac1 $ crsctl   query css votedisk
##  STATE    File Universal Id                File Name Disk group
--  -----    -----------------                --------- ---------
 1. ONLINE   0d588bf13f684f32bfb3d45688c28826 (ORCL:VOTEVPLEX) [VOTEVPLEX]

Get multiple Oracle SIDs into variable without using oratab

Here's the script for the lazy ones. After script I have explained how the script actually works. 

This script requires connection as sqlplus / as sysdba without giving password

#
# 1.0   17.09.2015      Mika Heino
#
# List Oracle SIDs in server in human readable output
# and connect SQL plus as sysdba
# 

db=`ps -ef | grep [p]mon  | awk {'print $8'} | sed -e 's,ora_pmon_,,' | sed -e s,asm_pmon_,,`
for i in $db ; do
  if [ "$i" == "sed" ] || [ "$i" == *ASM* ]
  then
     continue
  else
         export ORAENV_ASK=NO
         export ORACLE_SID=$i
        . oraenv
        export ORAENV_ASK=YES
   sqlplus -S / as sysdba @your_sql_script
  fi
done

What it actually does? 

ps -ef | grep [p]mon -- List all active pmon processes. Use [] to avoid grep coming into the result list

awk {'print $8'} -- print from output only the 8th column, which is the process name

sed -e 's,ora_pmon_,,' -- remove 'ora_pmon_' text from output

sed -e s,asm_pmon_,, -- remove 'asm_pmon_' text from output

Final output looks like this. It will still output 'sed' text two times, but this will be removed in the clumsy if -clause

[oracle@server ~]$ ps -ef | grep [p]mon  | awk {'print $8'} | sed -e 's,ora_pmon_,,' | sed -e s,asm_pmon_,,

+ASM1

INSTANCE2

INSTANCE3

INSTANCE4

sed

sed

This output is then inserted into variable $db. 

db=`ps -ef | grep [p]mon  | awk {'print $8'} | sed -e 's,ora_pmon_,,' | sed -e s,asm_pmon_,,`

Do for loop for all values within the variable 

for i in $db ; do

Skip and continue if the value is 'sed' or 'ASM' 

  if [ "$i" == "sed" ] || [ "$i" == *ASM* ]
  then
     continue
  else

Otherwise export the Oracle environment, use . oraenv and execute the desired SQL script. 

         export ORAENV_ASK=NO
         export ORACLE_SID=$i
        . oraenv
        export ORAENV_ASK=YES

If your environment has passwords, use them right here using variables.

   sqlplus -S / as sysdba @your_sql_script
  fi
done

How to delete spfile entries in Oracle 11gr2

Do not use '' or 'NULL' values. Use instead following reset -value

alter system reset parameter scope sid='*';

e.g.

alter system reset db_file_name_convert scope=spfile sid='*';

How to move disks inside ASM diskgroups

Simple commands (without any details go as follow). Modify the datafile names to suit your own needs. These files do not use OMF and hence full filename needs to said during RMAN commands.

Locate file which needs to be moved;
SQL> SELECT file_name FROM dba_data_files;


Take the file offline;
SQL> ALTER DATABASE DATAFILE '+DATA/database/datafile/db_name_data01.dbf' OFFLINE;


Log on to RMAN
OS> rman target / 


Copy the datafile to new location
RMAN> COPY DATAFILE '+DATA/database/datafile/db_name_data01.dbf' TO '+DATA_DISK_GROUP_1/database/datafile/db_name_data01.dbf';


Log on to SQL to rename the file again
SQL> ALTER DATABASE RENAME FILE '+DATA/database/datafile/db_name_data01.dbf' TO '+DATA_DISK_GROUP_1/database/datafile/db_name_data01.dbf';


Log on to RMAN
OS> rman target /

Switch datafile
RMAN> SWITCH DATAFILE '+DATA_DISK_GROUP_1/database/datafile/db_name_data01.dbf' TO COPY;

Log on to SQL  to recover and put the file online again.

SQL> RECOVER DATAFILE '+DATA_DISK_GROUP_1/database/datafile/db_name_data01.dbf';
SQL> ALTER DATABASE DATAFILE '+DATA_DISK_GROUP_1/database/datafile/db_name_data01.dbf' ONLINE;

Done.

Adding ASM disks through SQL*Plus

List ASM diskgroup and make note of diskgroup name

SQL> !cat list_asm_diskgroups.sql
set lines 200
column GROUP_NUMBER format a30
column NAME format a20
column SECTOR_SIZE format a20
column STATE format a10
column TYPE format a10
SELECT GROUP_NUMBER as NO, NAME,SECTOR_SIZE as SS ,BLOCK_SIZE,ALLOCATION_UNIT_SIZE,STATE,TYPE,TOTAL_MB,FREE_MB FROM V$ASM_DISKGROUP;

SQL> @list_asm_diskgroups.sql

        NO NAME                         SS BLOCK_SIZE ALLOCATION_UNIT_SIZE STATE      TYPE         TOTAL_MB    FREE_MB

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

         1 GRID                        512       4096              1048576 MOUNTED    NORMAL          30720      29794

         2 XXPROD_DATA                 512       4096              4194304 MOUNTED    EXTERN        2048000    1202260

         3 XXPROD_FRA                  512       4096              4194304 MOUNTED    EXTERN         102400      88680

List possible candidate disks

SQL> !cat show_asm_candidates.sql

column name format a20

column header_status format a10

column path format a30

SELECT name, header_status, path FROM V$ASM_DISK

WHERE HEADER_STATUS='CANDIDATE';

SQL> @show_asm_candidates.sql

NAME                 HEADER_STA PATH

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

                     CANDIDATE  /dev/rhdisk56

                     CANDIDATE  /dev/rhdisk55

Add candidate ASM disks by issuing following commands

SQL> ALTER DISKGROUP XXPROD_DATA ADD DISK '/dev/rhdisk55' NAME XXPROD_FRA_0001 SIZE 102400 M REBALANCE POWER 11;

Diskgroup altered.

SQL> ALTER DISKGROUP XXPROD_DATA ADD DISK '/dev/rhdisk56' NAME XXPROD_FRA_0002 SIZE 102400 M REBALANCE POWER 11;

Done.