Saturday, July 28, 2012

Finding Sector Size of a Disk

Sector size of a disk could be found out with the blockdev command in linux
blockdev
Usage:
  blockdev -V
  blockdev --report [devices]
  blockdev [-v|-q] commands devices
Available commands:
        --getsz (get size in 512-byte sectors)
        --setro (set read-only)
        --setrw (set read-write)
        --getro (get read-only)
        --getss (get sectorsize)
        --getbsz        (get blocksize)
        --setbsz BLOCKSIZE      (set blocksize)
        --getsize       (get 32-bit sector count)
        --getsize64     (get size in bytes)
        --setra READAHEAD       (set readahead)
        --getra (get readahead)
        --flushbufs     (flush buffers)
        --rereadpt      (reread partition table)
        --rmpart PARTNO (disable partition)
        --rmparts       (disable all partitions)
To get the sector size use getss option with the device
/sbin/blockdev --getss /dev/sdd1
512
Many options could be combined together as well such as finding out block size as well as the sector size
/sbin/blockdev --getss --getbsz /dev/sdd1
512
2048
Knowing the sector size may be useful when dealing with new features of ASM.

In windows sector size could be found out with
wmic DISKDRIVE get bytespersector, caption
BytesPerSector  Caption
512             TOSHIBA MK1216GSY

Friday, July 6, 2012

11gR2 Standalone Data Guard (with ASM and Role Separation)

Post list the steps to configure a data guard environment with 11gR2 (11.2.0.3) standalone server using ASM and role separation. Following table list the names and conventions used during the setup.

Item On Primary On Standby
Host namerac1rac2
Database Nameent11g2ent11g2s
Diskgroup NamesDATA
FLASH
APPS
DG_DATA
DG_FLASH
APPS


1. Create the relevant *dump directories in the standby server
cd $ORACLE_BASE/admin
mkdir ent11g2s
cd ent11g2s
mkdir adump  dpdump  hdump  pfile
2. It is assumed primary db is in archive log mode if not put the db in archive log mode. This is required since duplication is done using active database.

3. Enable force logging on the primary database
alter database force logging;
4. Create standby log files for each thread on primary db. These should be same size as the online redo log. There should be at least one more redo log group per thread than the online redo log groups.
SQL> alter database add standby logfile thread 1 size 52428800;
5. Add static listener entries on standby and primary $GI_HOME's listener.ora file. This must be done as grid user. On primary add the following static listener entry to listener.ora
SID_LIST_LISTENER =
(SID_LIST =
        (SID_DESC =
                (GLOBAL_DBNAME = ent11g2.domain.net)
                (SID_NAME = ent11g2)
                (ORACLE_HOME = /opt/app/oracle/product/11.2.0/dbhome_1)
        )
)
On standby
SID_LIST_LISTENER =
(SID_LIST =
        (SID_DESC =
                (GLOBAL_DBNAME = ent11g2s.domain.net)
                (SID_NAME = ent11g2s)
                (ORACLE_HOME = /opt/app/oracle/product/11.2.0/dbhome_1)
        )
)
Restart both primary and standby listeners for static listener entries to take effect. After the listener restart the listener status would show on primary
...
Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=rac1)(PORT=1521)))
Services Summary...
Service "+ASM" has 1 instance(s).
  Instance "+ASM", status READY, has 1 handler(s) for this service...
Service "ent11g2.domain.net" has 2 instance(s).
  Instance "ent11g2", status UNKNOWN, has 1 handler(s) for this service...
  Instance "ent11g2", status READY, has 1 handler(s) for this service...
Service "ent11g2XDB.domain.net" has 1 instance(s).
  Instance "ent11g2", status READY, has 1 handler(s) for this service...
The command completed successfully
On standby
...
Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=rac2)(PORT=1521)))
Services Summary...
Service "+ASM" has 1 instance(s).
  Instance "+ASM", status READY, has 1 handler(s) for this service...
Service "ent11g2s.domain.net" has 1 instance(s).
  Instance "ent11g2s", status UNKNOWN, has 1 handler(s) for this service...
The command completed successfully
6. As oracle user in ORACLE_HOME'S create tns entries. On primary server's oracle home create a tns entry pointing to the standby instance
ENT11G2STNS =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = rac2)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = ent11g2s.domain.net)
    )
  )
On standby's tnsnames.ora file add an entry pointing to the primary instance
ENT11G2TNS =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = rac1)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = ent11g2.domain.net)
    )
  )
7. Create a backup pfile from the primary spfile
SQL> create pfile='/home/oracle/pfile.ora' from spfile;
8. Modify the initialization parameters on the primary db. Remote archive dest state is set to defer, enable it before running the standby duplication. LGWR with ASYNC and NOAFFIRM is used as the redo transport this could be changed to suite the protection mode.
alter system set log_archive_config='dg_config=(ent11g2,ent11g2s)' scope=both ;
alter system set log_archive_dest_1='location=use_db_recovery_file_dest valid_for=(all_logfiles,all_roles) db_unique_name=ent11g2' scope=both;
alter system set log_archive_dest_2='service=ENT11G2STNS LGWR ASYNC NOAFFIRM max_failure=10 max_connections=5 reopen=180 valid_for=(online_logfiles,primary_role) db_unique_name=ent11g2s' scope=both;
alter system set log_archive_dest_state_2='defer' scope=both;
alter system set log_archive_dest_state_1='enable' scope=both;
alter system set fal_server='ENT11G2STNS' scope=both;
alter system set fal_client='ENT11G2TNS' scope=both ;
alter system set log_archive_max_processes=10 scope=both;
alter system set db_file_name_convert='+DG_DATA/ent11g2s','+DATA/ent11g2','+APPS/ent11g2s','+APPS/ent11g2' scope=spfile;
alter system set log_file_name_convert='+DG_FLASH/ent11g2s','+FLASH/ent11g2','+DG_DATA/ent11g2s','+DATA/ent11g2' scope=spfile;
alter system set standby_file_management='AUTO' scope=both;
Restart the database server for the changes to take effect
srvctl stop database -d ent11g2
srvctl start database -d ent11g2
9. Copy password to standby and rename with the instance name
scp orapwent11g2 rac2:/opt/app/oracle/product/11.2.0/dbhome_1/dbs/orapwent11g2s
10. Create pfile on standby with db_name parameter only
export ORACLE_SID=ent11g2s
cat initent11g2s.ora
db_name=ent11g2s
10. Start the standby instance in nomount mode
SQL> startup nomount
ORACLE instance started.

Total System Global Area  238034944 bytes
Fixed Size                  2227136 bytes
Variable Size             180356160 bytes
Database Buffers           50331648 bytes
Redo Buffers                5120000 bytes
11. Enable the remote log archive destination on primary
alter system set log_archive_dest_state_2='enable' SCOPE=both sid='*';
12. Run the duplicate from active database command from primary. Local listner is set automatically and will be reset during duplication process.
duplicate target database for standby from active database
spfile
parameter_value_convert 'ent11g2','ent11g2s','ENT11G2','ENT11G2S'
set db_unique_name='ent11g2s'
set db_file_name_convert='+DATA/ent11g2','+DG_DATA/ent11g2s','+APPS/ent11g2','+APPS/ent11g2s'
set log_file_name_convert='+FLASH/ent11g2','+DG_FLASH/ent11g2s','+DATA/ent11g2','+DG_DATA/ent11g2s'
set control_files='+DG_DATA','+DG_FLASH'
set db_create_file_dest='+DG_DATA'
set db_recovery_file_dest='+DG_FLASH'
set log_archive_max_processes='10'
set fal_client='ENT11G2STNS'
set fal_server='ENT11G2TNS'
reset local_listener
set log_archive_dest_2='service=ENT11G2TNS LGWR ASYNC NOAFFIRM max_failure=10 max_connections=5 reopen=180 valid_for=(online_logfiles,primary_role) db_unique_name=ent11g2'
set log_archive_dest_1='location=use_db_recovery_file_dest valid_for=(all_logfiles,all_roles) db_unique_name=ent11g2s';
The complete output of the duplication process is given below
rman target / auxiliary sys/ent11g2db@ent11g2stns

Recovery Manager: Release 11.2.0.3.0 - Production on Wed Jul 4 16:56:36 2012

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

connected to target database: ENT11G2 (DBID=2557133120)
connected to auxiliary database: ENT11G2S (not mounted)

RMAN> duplicate target database for standby from active database
2> spfile
3> parameter_value_convert 'ent11g2','ent11g2s','ENT11G2','ENT11G2S'
4> set db_unique_name='ent11g2s'
5> set db_file_name_convert='+DATA/ent11g2','+DG_DATA/ent11g2s','+APPS/ent11g2','+APPS/ent11g2s'
6> set log_file_name_convert='+FLASH/ent11g2','+DG_FLASH/ent11g2s','+DATA/ent11g2','+DG_DATA/ent11g2s'
7> set control_files='+DG_DATA','+DG_FLASH'
8> set db_create_file_dest='+DG_DATA'
9> set db_recovery_file_dest='+DG_FLASH'
10> set log_archive_max_processes='10'
11> set fal_client='ENT11G2STNS'
12> set fal_server='ENT11G2TNS'
13> reset local_listener
14> set log_archive_dest_2='service=ENT11G2TNS LGWR ASYNC NOAFFIRM max_failure=10 max_connections=5 reopen=180 valid_for=(online_logfiles,primary_role) db_unique_name=ent11g2'
15> set log_archive_dest_1='location=use_db_recovery_file_dest valid_for=(all_logfiles,all_roles) db_unique_name=ent11g2s';

Starting Duplicate Db at 04-JUL-12
using target database control file instead of recovery catalog
allocated channel: ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: SID=10 device type=DISK

contents of Memory Script:
{
   backup as copy reuse
   targetfile  '/opt/app/oracle/product/11.2.0/dbhome_1/dbs/orapwent11g2' auxiliary format
 '/opt/app/oracle/product/11.2.0/dbhome_1/dbs/orapwent11g2s'   targetfile
 '+DATA/ent11g2/spfileent11g2.ora' auxiliary format
 '/opt/app/oracle/product/11.2.0/dbhome_1/dbs/spfileent11g2s.ora'   ;
   sql clone "alter system set spfile= ''/opt/app/oracle/product/11.2.0/dbhome_1/dbs/spfileent11g2s.ora''";
}
executing Memory Script

Starting backup at 04-JUL-12
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=18 device type=DISK
Finished backup at 04-JUL-12

sql statement: alter system set spfile= ''/opt/app/oracle/product/11.2.0/dbhome_1/dbs/spfileent11g2s.ora''

contents of Memory Script:
{
   sql clone "alter system set  audit_file_dest =
 ''/opt/app/oracle/admin/ent11g2s/adump'' comment=
 '''' scope=spfile";
   sql clone "alter system set  dispatchers =
 ''(PROTOCOL=TCP) (SERVICE=ent11g2sXDB)'' comment=
 '''' scope=spfile";
   sql clone "alter system set  db_unique_name =
 ''ent11g2s'' comment=
 '''' scope=spfile";
   sql clone "alter system set  db_file_name_convert =
 ''+DATA/ent11g2'', ''+DG_DATA/ent11g2s'', ''+APPS/ent11g2'', ''+APPS/ent11g2s'' comment=
 '''' scope=spfile";
   sql clone "alter system set  log_file_name_convert =
 ''+FLASH/ent11g2'', ''+DG_FLASH/ent11g2s'', ''+DATA/ent11g2'', ''+DG_DATA/ent11g2s'' comment=
 '''' scope=spfile";
   sql clone "alter system set  control_files =
 ''+DG_DATA'', ''+DG_FLASH'' comment=
 '''' scope=spfile";
   sql clone "alter system set  db_create_file_dest =
 ''+DG_DATA'' comment=
 '''' scope=spfile";
   sql clone "alter system set  db_recovery_file_dest =
 ''+DG_FLASH'' comment=
 '''' scope=spfile";
   sql clone "alter system set  log_archive_max_processes =
 10 comment=
 '''' scope=spfile";
   sql clone "alter system set  fal_client =
 ''ENT11G2STNS'' comment=
 '''' scope=spfile";
   sql clone "alter system set  fal_server =
 ''ENT11G2TNS'' comment=
 '''' scope=spfile";
   sql clone "alter system set  log_archive_dest_2 =
 ''service=ENT11G2TNS LGWR ASYNC NOAFFIRM max_failure=10 max_connections=5 reopen=180 valid_for=(online_logfiles,primary_role) db_unique_name=ent11g2'' comment=
 '''' scope=spfile";
   sql clone "alter system set  log_archive_dest_1 =
 ''location=use_db_recovery_file_dest valid_for=(all_logfiles,all_roles) db_unique_name=ent11g2s'' comment=
 '''' scope=spfile";
   shutdown clone immediate;
   startup clone nomount;
}
executing Memory Script

sql statement: alter system set  audit_file_dest =  ''/opt/app/oracle/admin/ent11g2s/adump'' comment= '''' scope=spfile

sql statement: alter system set  dispatchers =  ''(PROTOCOL=TCP) (SERVICE=ent11g2sXDB)'' comment= '''' scope=spfile

sql statement: alter system set  db_unique_name =  ''ent11g2s'' comment= '''' scope=spfile

sql statement: alter system set  db_file_name_convert =  ''+DATA/ent11g2'', ''+DG_DATA/ent11g2s'', ''+APPS/ent11g2'', ''+APPS/ent11g2s'' comment= '''' scope=spfile

sql statement: alter system set  log_file_name_convert =  ''+FLASH/ent11g2'', ''+DG_FLASH/ent11g2s'', ''+DATA/ent11g2'', ''+DG_DATA/ent11g2s'' comment= '''' scope=spfile

sql statement: alter system set  control_files =  ''+DG_DATA'', ''+DG_FLASH'' comment= '''' scope=spfile

sql statement: alter system set  db_create_file_dest =  ''+DG_DATA'' comment= '''' scope=spfile

sql statement: alter system set  db_recovery_file_dest =  ''+DG_FLASH'' comment= '''' scope=spfile

sql statement: alter system set  log_archive_max_processes =  10 comment= '''' scope=spfile

sql statement: alter system set  fal_client =  ''ENT11G2STNS'' comment= '''' scope=spfile

sql statement: alter system set  fal_server =  ''ENT11G2TNS'' comment= '''' scope=spfile

sql statement: alter system set  log_archive_dest_2 =  ''service=ENT11G2TNS LGWR ASYNC NOAFFIRM max_failure=10 max_connections=5 reopen=180 valid_for=(online_logfiles,primary_role) db_unique_name=ent11g2'' comment= '''' scope=spfile

sql statement: alter system set  log_archive_dest_1 =  ''location=use_db_recovery_file_dest valid_for=(all_logfiles,all_roles) db_unique_name=ent11g2s'' comment= '''' scope=spfile

Oracle instance shut down

connected to auxiliary database (not started)
Oracle instance started

Total System Global Area     839282688 bytes

Fixed Size                     2233000 bytes
Variable Size                230690136 bytes
Database Buffers             599785472 bytes
Redo Buffers                   6574080 bytes

contents of Memory Script:
{
   sql clone "alter system set  control_files =
  ''+DG_DATA/ent11g2s/controlfile/current.268.787763727'', ''+DG_FLASH/ent11g2s/controlfile/current.259.787763731'' comment=
 ''Set by RMAN'' scope=spfile";
   backup as copy current controlfile for standby auxiliary format  '+DG_DATA/ent11g2s/controlfile/current.267.787763731';
   restore clone controlfile to  '+DG_FLASH/ent11g2s/controlfile/current.258.787763731' from
 '+DG_DATA/ent11g2s/controlfile/current.267.787763731';
   sql clone "alter system set  control_files =
  ''+DG_DATA/ent11g2s/controlfile/current.267.787763731'', ''+DG_FLASH/ent11g2s/controlfile/current.258.787763731'' comment=
 ''Set by RMAN'' scope=spfile";
   shutdown clone immediate;
   startup clone nomount;
}
executing Memory Script

sql statement: alter system set  control_files =   ''+DG_DATA/ent11g2s/controlfile/current.268.787763727'', ''+DG_FLASH/ent11g2s/controlfile/current.259.787763731'' comment= ''Set by RMAN'' scope=spfile

Starting backup at 04-JUL-12
using channel ORA_DISK_1
channel ORA_DISK_1: starting datafile copy
copying standby control file
output file name=/opt/app/oracle/product/11.2.0/dbhome_1/dbs/snapcf_ent11g2.f tag=TAG20120704T143626 RECID=5 STAMP=787761386
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:03
Finished backup at 04-JUL-12

Starting restore at 04-JUL-12
allocated channel: ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: SID=138 device type=DISK

channel ORA_AUX_DISK_1: copied control file copy
Finished restore at 04-JUL-12

sql statement: alter system set  control_files =   ''+DG_DATA/ent11g2s/controlfile/current.267.787763731'', ''+DG_FLASH/ent11g2s/controlfile/current.258.787763731'' comment= ''Set by RMAN'' scope=spfile

Oracle instance shut down

connected to auxiliary database (not started)
Oracle instance started

Total System Global Area     839282688 bytes

Fixed Size                     2233000 bytes
Variable Size                230690136 bytes
Database Buffers             599785472 bytes
Redo Buffers                   6574080 bytes

contents of Memory Script:
{
   sql clone 'alter database mount standby database';
}
executing Memory Script

sql statement: alter database mount standby database
RMAN-05529: WARNING: DB_FILE_NAME_CONVERT resulted in invalid ASM names; names changed to disk group only.

contents of Memory Script:
{
   set newname for tempfile  1 to
 "+dg_data";
   switch clone tempfile all;
   set newname for datafile  1 to
 "+dg_data";
   set newname for datafile  2 to
 "+dg_data";
   set newname for datafile  3 to
 "+dg_data";
   set newname for datafile  4 to
 "+dg_data";
   set newname for datafile  5 to
 "+apps";
   backup as copy reuse
   datafile  1 auxiliary format
 "+dg_data"   datafile
 2 auxiliary format
 "+dg_data"   datafile
 3 auxiliary format
 "+dg_data"   datafile
 4 auxiliary format
 "+dg_data"   datafile
 5 auxiliary format
 "+apps"   ;
   sql 'alter system archive log current';
}
executing Memory Script

executing command: SET NEWNAME

renamed tempfile 1 to +dg_data in control file

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

Starting backup at 04-JUL-12
using channel ORA_DISK_1
channel ORA_DISK_1: starting datafile copy
input datafile file number=00001 name=+DATA/ent11g2/datafile/system.256.787750113
output file name=+DG_DATA/ent11g2s/datafile/system.260.787763751 tag=TAG20120704T143647
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:01:15
channel ORA_DISK_1: starting datafile copy
input datafile file number=00002 name=+DATA/ent11g2/datafile/sysaux.257.787750113
output file name=+DG_DATA/ent11g2s/datafile/sysaux.261.787763827 tag=TAG20120704T143647
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:55
channel ORA_DISK_1: starting datafile copy
input datafile file number=00003 name=+DATA/ent11g2/datafile/undotbs1.258.787750113
output file name=+DG_DATA/ent11g2s/datafile/undotbs1.262.787763883 tag=TAG20120704T143647
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:15
channel ORA_DISK_1: starting datafile copy
input datafile file number=00005 name=+APPS/ent11g2/datafile/myapps.256.787760733
output file name=+APPS/ent11g2s/datafile/myapps.256.787763897 tag=TAG20120704T143647
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:07
channel ORA_DISK_1: starting datafile copy
input datafile file number=00004 name=+DATA/ent11g2/datafile/users.259.787750113
output file name=+DG_DATA/ent11g2s/datafile/users.263.787763905 tag=TAG20120704T143647
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:07
Finished backup at 04-JUL-12

sql statement: alter system archive log current

contents of Memory Script:
{
   switch clone datafile all;
}
executing Memory Script

datafile 1 switched to datafile copy
input datafile copy RECID=5 STAMP=787763913 file name=+DG_DATA/ent11g2s/datafile/system.260.787763751
datafile 2 switched to datafile copy
input datafile copy RECID=6 STAMP=787763913 file name=+DG_DATA/ent11g2s/datafile/sysaux.261.787763827
datafile 3 switched to datafile copy
input datafile copy RECID=7 STAMP=787763913 file name=+DG_DATA/ent11g2s/datafile/undotbs1.262.787763883
datafile 4 switched to datafile copy
input datafile copy RECID=8 STAMP=787763913 file name=+DG_DATA/ent11g2s/datafile/users.263.787763905
datafile 5 switched to datafile copy
input datafile copy RECID=9 STAMP=787763913 file name=+APPS/ent11g2s/datafile/myapps.256.787763897
Finished Duplicate Db at 04-JUL-12
13. Once the duplication for standby is completed start the log apply process on standby
SQL> alter database recover managed standby database using current logfile disconnect;
and monitor the standby instance's alert log for recover process in action
alter database recover managed standby database using current logfile disconnect
Attempt to start background Managed Standby Recovery process (ent11g2s)
Wed Jul 04 17:07:05 2012
MRP0 started with pid=48, OS id=5459
MRP0: Background Managed Standby Recovery process started (ent11g2s)
 started logmerger process
Wed Jul 04 17:07:11 2012
Managed Standby Recovery starting Real Time Apply
Parallel Media Recovery started with 2 slaves
Waiting for all non-current ORLs to be archived...
All non-current ORLs have been archived.
Media Recovery Log +DG_FLASH/ent11g2s/archivelog/2012_07_04/thread_1_seq_36.265.787770401
Media Recovery Log +DG_FLASH/ent11g2s/archivelog/2012_07_04/thread_1_seq_37.264.787770403
Media Recovery Log +DG_FLASH/ent11g2s/archivelog/2012_07_04/thread_1_seq_38.256.787770405
Media Recovery Waiting for thread 1 sequence 39 (in transit)
Recovery of Online Redo Log: Thread 1 Group 4 Seq 39 Reading mem 0
  Mem# 0: +DG_DATA/ent11g2s/onlinelog/group_4.258.787770345
  Mem# 1: +DG_FLASH/ent11g2s/onlinelog/group_4.278.787770345
Completed: alter database recover managed standby database using current logfile disconnect
Do a log switch on primary and see archive log application on standby
QL> select sequence#,thread#,applied from v$archived_log;

 SEQUENCE#    THREAD# APPLIED
---------- ---------- ---------
        36          1 YES
        37          1 YES
        38          1 IN-MEMORY
14. This conclude the data guard configuration. Next step is to bring the standby instance under oracle restart (high availability service) similar to primary db
srvctl config database -d ent11g2
Database unique name: ent11g2
Database name: ent11g2
Oracle home: /opt/app/oracle/product/11.2.0/dbhome_1
Oracle user: oracle
Spfile: +DATA/ent11g2/spfileent11g2.ora
Domain: domain.net
Start options: open
Stop options: immediate
Database role: PRIMARY
Management policy: AUTOMATIC
Database instance: ent11g2
Disk Groups: DATA,FLASH,APPS
Services:
Create a pfile from current spfile used by standby. At the end of the duplication standby's spfile is in the $ORACLE_HOME/dbs, this could be confirmed from the alert log which will have the following entry
Using parameter settings in server-side spfile /opt/app/oracle/product/11.2.0/dbhome_1/dbs/spfileent11g2s.ora
Create the pfile from spfile
SQL> create pfile='/home/oracle/pfileinitent11g2s.ora' from spfile;

File created.
Stop the log apply and shutdown the instance
SQL> alter database recover managed standby database cancel;

Database altered.

SQL> shutdown immediate;
Edit the pfile and remove any references to primary instance. The final pfile would only have standby entries
ent11g2s.__db_cache_size=599785472
ent11g2s.__java_pool_size=4194304
ent11g2s.__large_pool_size=4194304
ent11g2s.__pga_aggregate_target=419430400
ent11g2s.__sga_target=843055104
ent11g2s.__shared_io_pool_size=0
ent11g2s.__shared_pool_size=222298112
ent11g2s.__streams_pool_size=0
*.audit_file_dest='/opt/app/oracle/admin/ent11g2s/adump'
*.audit_trail='db'
*.compatible='11.2.0.0.0'
*.control_files='+DG_DATA/ent11g2s/controlfile/current.274.787770165','+DG_FLASH/ent11g2s/controlfile/current.282.787770167'#Set by RMAN
*.db_block_size=8192
*.db_create_file_dest='+DG_DATA'
*.db_domain='domain.net'
*.db_file_name_convert='+DATA/ent11g2','+DG_DATA/ent11g2s','+APPS/ent11g2','+APPS/ent11g2s'
*.db_name='ent11g2'
*.db_recovery_file_dest_size=9437184000
*.db_recovery_file_dest='+DG_FLASH'
*.db_unique_name='ent11g2s'
*.diagnostic_dest='/opt/app/oracle'
*.dispatchers='(PROTOCOL=TCP) (SERVICE=ent11g2sXDB)'
*.fal_client='ENT11G2STNS'
*.fal_server='ENT11G2TNS'
*.log_archive_config='dg_config=(ent11g2,ent11g2s)'
*.log_archive_dest_1='location=use_db_recovery_file_dest valid_for=(all_logfiles,all_roles) db_unique_name=ent11g2s'
*.log_archive_dest_2='service=ENT11G2TNS LGWR ASYNC NOAFFIRM max_failure=10 max_connections=5 reopen=180 valid_for=(online_logfiles,primary_role) db_u
nique_name=ent11g2'
*.log_archive_dest_state_1='enable'
*.log_archive_dest_state_2='enable'
*.log_archive_format='%t_%s_%r.dbf'
*.log_archive_max_processes=10
*.log_file_name_convert='+FLASH/ent11g2','+DG_FLASH/ent11g2s','+DATA/ent11g2','+DG_DATA/ent11g2s'
*.open_cursors=300
*.pga_aggregate_target=419430400
*.processes=150
*.remote_login_passwordfile='EXCLUSIVE'
*.sga_target=840957952
*.standby_file_management='AUTO'
*.undo_tablespace='UNDOTBS1'
Start the standby instance with the modified pfile
SQL> startup mount pfile='/home/oracle/pfileinitent11g2s.ora';
Create a spfile from the pfile give diskgroup as the spfile location
SQL> create spfile='+DG_DATA/ent11g2s/spfileent11g2s.ora' from pfile='/home/oracle/pfileinitent11g2s.ora';

SQL> shutdown immediate;
in $ORACLE_HOME/dbs create new pfile with following line
vi initent11g2s.ora
spfile='+DG_DATA/ent11g2s/spfileent11g2s.ora'
Remove the existing spfile created part of the duplication. Bring the standby instance under the control of oracle restart by running the following command as the oracle user. Startup mode is set to mount to prevent opening in read only mode which requires active data guard license.
$GI_HOME/bin/srvctl add database -d ent11g2s -o $ORACLE_HOME -m local -p "+DG_DATA/ent11g2s/spfileent11g2s.ora" -r physical_standby -s mount
If the database was running when the above command was executed then stop it using sqlplus and then start with
srvctl start database -d ent11g2s

srvctl config database -d ent11g2s
Database unique name: ent11g2s
Database name:
Oracle home: /opt/app/oracle/product/11.2.0/dbhome_1
Oracle user: oracle
Spfile: +DG_DATA/ent11g2s/spfileent11g2s.ora
Domain: domain.net
Start options: mount
Stop options: immediate
Database role: PHYSICAL_STANDBY
Management policy: AUTOMATIC
Database instance: ent11g2s
Disk Groups: DG_DATA,DG_FLASH,APPS
Services:
This will establish all the dependencies of the database and following lines will disappear from the alert log. Before instance is brought under the oracle restart
SUCCESS: diskgroup DG_FLASH was mounted
ERROR: failed to establish dependency between database ent11g2s and diskgroup resource ora.DG_DATA.dg
ERROR: failed to establish dependency between database ent11g2s and diskgroup resource ora.DG_FLASH.dg
ARCH: STARTING ARCH PROCESSES
After
ALTER DATABASE MOUNT /* db agent *//* {0:0:307} */
NOTE: Loaded library: /opt/oracle/extapi/64/asm/orcl/1/libasm.so
NOTE: Loaded library: System
SUCCESS: diskgroup DG_DATA was mounted
SUCCESS: diskgroup DG_FLASH was mounted
ARCH: STARTING ARCH PROCESSES
Wed Jul 04 17:27:58 2012
ARC0 started with pid=26, OS id=6206
ARC0: Archival started
This conclude the configuration and setup of the standalone role separated ASM data guard configuration.



Alternately a pfile for the standby could be created with all the necessary parameter entries and use that to start the duplicate in nomount state. Then when running the duplicate command and omit the spfile clause. In this scenario pfile created on step 10 would have all the necessary parameters, this could be created by modifying a pfile created from the primary.
more initent11g2s.ora
*.audit_file_dest='/opt/app/oracle/admin/ent11g2s/adump'
*.audit_trail='db'
*.compatible='11.2.0.0.0'
*.control_files='+DG_DATA','+DG_FLASH'
*.db_block_size=8192
*.db_create_file_dest='+DG_DATA'
*.db_domain='domain.net'
*.db_file_name_convert='+DATA/ent11g2','+DG_DATA/ent11g2s','+APPS/ent11g2','+APPS/ent11g2s'
*.db_name='ent11g2'
*.db_unique_name='ent11g2s'
*.db_recovery_file_dest='+DG_FLASH'
*.db_recovery_file_dest_size=9437184000
*.diagnostic_dest='/opt/app/oracle'
*.dispatchers='(PROTOCOL=TCP) (SERVICE=ent11g2sXDB)'
*.fal_client='ENT11G2STNS'
*.fal_server='ENT11G2TNS'
*.log_archive_config='dg_config=(ent11g2,ent11g2s)'
*.log_archive_dest_1='location=use_db_recovery_file_dest valid_for=(all_logfiles,all_roles) db_unique_name=ent11g2s'
*.log_archive_dest_2='service=ENT11G2TNS LGWR ASYNC NOAFFIRM max_failure=10 max_connections=5 reopen=180 valid_for=(online_logfiles,primary_role) db_u
nique_name=ent11g2'
*.log_archive_dest_state_1='enable'
*.log_archive_dest_state_2='enable'
*.log_archive_format='%t_%s_%r.dbf'
*.log_archive_max_processes=10
*.log_file_name_convert='+FLASH/ent11g2','+DG_FLASH/ent11g2s','+DATA/ent11g2','+DG_DATA/ent11g2s'
*.open_cursors=300
*.pga_aggregate_target=419430400
*.processes=150
*.remote_login_passwordfile='EXCLUSIVE'
*.sga_target=840957952
*.standby_file_management='AUTO'
*.undo_tablespace='UNDOTBS1'
Then execute the duplicate command as follows. In this case the auxiliary database name will be same as the primary name unlike in the method mentioned above (step 12)
rman target / auxiliary sys/ent11g2db@ent11g2stns

Recovery Manager: Release 11.2.0.3.0 - Production on Wed Jul 4 15:26:35 2012

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

connected to target database: ENT11G2 (DBID=2557133120)
connected to auxiliary database: ENT11G2 (not mounted)

RMAN> duplicate target database for standby from active database;

Starting Duplicate Db at 04-JUL-12
using target database control file instead of recovery catalog
allocated channel: ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: SID=12 device type=DISK

contents of Memory Script:
{
   backup as copy reuse
   targetfile  '/opt/app/oracle/product/11.2.0/dbhome_1/dbs/orapwent11g2' auxiliary format
 '/opt/app/oracle/product/11.2.0/dbhome_1/dbs/orapwent11g2s'   ;
}
executing Memory Script

Starting backup at 04-JUL-12
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=151 device type=DISK
Finished backup at 04-JUL-12

contents of Memory Script:
{
   backup as copy current controlfile for standby auxiliary format  '+DG_DATA/ent11g2s/controlfile/current.267.787766873';
   restore clone controlfile to  '+DG_FLASH/ent11g2s/controlfile/current.258.787766875' from
 '+DG_DATA/ent11g2s/controlfile/current.267.787766873';
   sql clone "create spfile from memory";
   shutdown clone immediate;
   startup clone nomount;
   sql clone "alter system set  control_files =
  ''+DG_DATA/ent11g2s/controlfile/current.267.787766873'', ''+DG_FLASH/ent11g2s/controlfile/current.258.787766875'' comment=
 ''Set by RMAN'' scope=spfile";
   shutdown clone immediate;
   startup clone nomount;
}
executing Memory Script

Starting backup at 04-JUL-12
using channel ORA_DISK_1
channel ORA_DISK_1: starting datafile copy
copying standby control file
output file name=/opt/app/oracle/product/11.2.0/dbhome_1/dbs/snapcf_ent11g2.f tag=TAG20120704T152853 RECID=6 STAMP=787764533
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:07
Finished backup at 04-JUL-12

Starting restore at 04-JUL-12
using channel ORA_AUX_DISK_1

channel ORA_AUX_DISK_1: copied control file copy
Finished restore at 04-JUL-12

sql statement: create spfile from memory

Oracle instance shut down

connected to auxiliary database (not started)
Oracle instance started

Total System Global Area     839282688 bytes

Fixed Size                     2233000 bytes
Variable Size                234884440 bytes
Database Buffers             595591168 bytes
Redo Buffers                   6574080 bytes

sql statement: alter system set  control_files =   ''+DG_DATA/ent11g2s/controlfile/current.267.787766873'', ''+DG_FLASH/ent11g2s/controlfile/current.258.787766875'' comment= ''Set by RMAN'' scope=spfile

Oracle instance shut down

connected to auxiliary database (not started)
Oracle instance started

Total System Global Area     839282688 bytes

Fixed Size                     2233000 bytes
Variable Size                234884440 bytes
Database Buffers             595591168 bytes
Redo Buffers                   6574080 bytes

contents of Memory Script:
{
   sql clone 'alter database mount standby database';
}
executing Memory Script

sql statement: alter database mount standby database
RMAN-05529: WARNING: DB_FILE_NAME_CONVERT resulted in invalid ASM names; names changed to disk group only.

contents of Memory Script:
{
   set newname for tempfile  1 to
 "+dg_data";
   switch clone tempfile all;
   set newname for datafile  1 to
 "+dg_data";
   set newname for datafile  2 to
 "+dg_data";
   set newname for datafile  3 to
 "+dg_data";
   set newname for datafile  4 to
 "+dg_data";
   set newname for datafile  5 to
 "+apps";
   backup as copy reuse
   datafile  1 auxiliary format
 "+dg_data"   datafile
 2 auxiliary format
 "+dg_data"   datafile
 3 auxiliary format
 "+dg_data"   datafile
 4 auxiliary format
 "+dg_data"   datafile
 5 auxiliary format
 "+apps"   ;
   sql 'alter system archive log current';
}
executing Memory Script

executing command: SET NEWNAME

renamed tempfile 1 to +dg_data in control file

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

Starting backup at 04-JUL-12
using channel ORA_DISK_1
channel ORA_DISK_1: starting datafile copy
input datafile file number=00001 name=+DATA/ent11g2/datafile/system.256.787750113
output file name=+DG_DATA/ent11g2s/datafile/system.263.787766913 tag=TAG20120704T152928
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:01:15
channel ORA_DISK_1: starting datafile copy
input datafile file number=00002 name=+DATA/ent11g2/datafile/sysaux.257.787750113
output file name=+DG_DATA/ent11g2s/datafile/sysaux.262.787766989 tag=TAG20120704T152928
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:55
channel ORA_DISK_1: starting datafile copy
input datafile file number=00003 name=+DATA/ent11g2/datafile/undotbs1.258.787750113
output file name=+DG_DATA/ent11g2s/datafile/undotbs1.261.787767045 tag=TAG20120704T152928
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:15
channel ORA_DISK_1: starting datafile copy
input datafile file number=00005 name=+APPS/ent11g2/datafile/myapps.256.787760733
output file name=+APPS/ent11g2s/datafile/myapps.256.787767061 tag=TAG20120704T152928
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:03
channel ORA_DISK_1: starting datafile copy
input datafile file number=00004 name=+DATA/ent11g2/datafile/users.259.787750113
output file name=+DG_DATA/ent11g2s/datafile/users.260.787767063 tag=TAG20120704T152928
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:03
Finished backup at 04-JUL-12

sql statement: alter system archive log current

contents of Memory Script:
{
   switch clone datafile all;
}
executing Memory Script

datafile 1 switched to datafile copy
input datafile copy RECID=6 STAMP=787767066 file name=+DG_DATA/ent11g2s/datafile/system.263.787766913
datafile 2 switched to datafile copy
input datafile copy RECID=7 STAMP=787767066 file name=+DG_DATA/ent11g2s/datafile/sysaux.262.787766989
datafile 3 switched to datafile copy
input datafile copy RECID=8 STAMP=787767066 file name=+DG_DATA/ent11g2s/datafile/undotbs1.261.787767045
datafile 4 switched to datafile copy
input datafile copy RECID=9 STAMP=787767066 file name=+DG_DATA/ent11g2s/datafile/users.260.787767063
datafile 5 switched to datafile copy
input datafile copy RECID=10 STAMP=787767066 file name=+APPS/ent11g2s/datafile/myapps.256.787767061
Finished Duplicate Db at 04-JUL-12
Reset of the steps are same as above to bring the standby under oracle restart's control.

Some other related notes
When the oracle software is installed on the standby node the permission of the Oracle binary is as follows.
ls -l /opt/app/oracle/product/11.2.0/grid/bin/oracle # GI HOME
-rwsr-s--x 1 grid oinstall 203972955 Jul  4 10:05 /opt/app/oracle/product/11.2.0/grid/bin/oracle
ls -l /opt/app/oracle/product/11.2.0/dbhome_1/bin/oracle # ORACLE HOME
-rwsr-s--x 1 oracle oinstall 232399473 Jul  4 12:29 /opt/app/oracle/product/11.2.0/dbhome_1/bin/oracle
See the post related to oracle binary permissions. On primary the permission of the oracle binary was "oracle:asmadmin". The permission was left as it is (oracle:oinstall) and this did not cause any problem during the standby creation and at the end of the duplication the oracle binary permission was changed to "oracle:asmadmin"
ls -l /opt/app/oracle/product/11.2.0/dbhome_1/bin/oracle
-rwsr-s--x 1 oracle asmadmin 232399473 Jul  4 12:29 /opt/app/oracle/product/11.2.0/dbhome_1/bin/oracle
s -l /opt/app/oracle/product/11.2.0/grid/bin/oracle
-rwsr-s--x 1 grid oinstall 203972955 Jul  4 10:05 /opt/app/oracle/product/11.2.0/grid/bin/oracle
It was confirmed oracle binary permissions are in a smilar state after the installation and that when the database is created with dbca the permission gets changed to "oracle:asmadmin".

It is normal to see message similar to below in the standby alert log when the duplication process and this does not mean a problem
Errors in file /opt/app/oracle/diag/rdbms/ent11g2s/ent11g2s/trace/ent11g2s_lgwr_27303.trc:
ORA-00313: open failed for members of log group 1 of thread 1
ORA-00312: online log 1 thread 1: '+DG_FLASH/ent11g2s/onlinelog/group_1.257.787750219'
ORA-17503: ksfdopn:2 Failed to open file +DG_FLASH/ent11g2s/onlinelog/group_1.257.787750219
ORA-15012: ASM file '+DG_FLASH/ent11g2s/onlinelog/group_1.257.787750219' does not exist
ORA-00312: online log 1 thread 1: '+DG_DATA/ent11g2s/onlinelog/group_1.261.787750215'
ORA-17503: ksfdopn:2 Failed to open file +DG_DATA/ent11g2s/onlinelog/group_1.261.787750215
ORA-15012: ASM file '+DG_DATA/ent11g2s/onlinelog/group_1.261.787750215' does not exist
Errors in file /opt/app/oracle/diag/rdbms/ent11g2s/ent11g2s/trace/ent11g2s_lgwr_27303.trc:
ORA-00313: open failed for members of log group 1 of thread 1
ORA-00312: online log 1 thread 1: '+DG_FLASH/ent11g2s/onlinelog/group_1.257.787750219'
ORA-17503: ksfdopn:2 Failed to open file +DG_FLASH/ent11g2s/onlinelog/group_1.257.787750219
ORA-15012: ASM file '+DG_FLASH/ent11g2s/onlinelog/group_1.257.787750219' does not exist
ORA-00312: online log 1 thread 1: '+DG_DATA/ent11g2s/onlinelog/group_1.261.787750215'
ORA-17503: ksfdopn:2 Failed to open file +DG_DATA/ent11g2s/onlinelog/group_1.261.787750215
ORA-15012: ASM file '+DG_DATA/ent11g2s/onlinelog/group_1.261.787750215' does not exist
The alert logs will be created without an issue.

Useful metalink notes
RMAN Duplicate Database From RAC ASM To RAC ASM [ID 461479.1]
Performing duplicate database with ASM/OMF/RMAN [ID 340848.1]
ORA-7445 [kcrf_val_log_bsz] Creating Physical Standby With Rman Duplicate From Active [ID 1320757.1]
Dataguard DB/LOG FILE NAME CONVERT has been set but files are created in a different directory [ID 1348512.1]
OERR:RMAN 5529 WARNING: DB_FILE_NAME_CONVERT resulted in invalid ASM names; names changed to disk group only [ID 1367794.1]

Related Posts
RAC to Single Instance Physical Standby
11gR2 RAC to RAC Data Guard

Thursday, July 5, 2012

Upgrading OS in 11gR2 RAC Environment

Oracle clusterware 11gR2 requires relinking whenever an OS patch or OS is upgraded. According to GI Installation guide "You must relink the Oracle Clusterware and Oracle ASM binaries every time you apply an operating system patch or after an operating system upgrade. Before relinking executables, you must shut down all executables that run in the Oracle home directory that you are relinking. In addition, shut down applications linked with Oracle shared libraries."
Relink is done with the following steps, again from the GI installation guide
As root:
# cd Grid_home/crs/install
# perl rootcrs.pl -unlock

As the Oracle Grid Infrastructure for a Cluster owner:
$ export ORACLE_HOME=Grid_home
$ Grid_home/bin/relink

As root again:
# cd Grid_home/rdbms/install/
# ./rootadd_rdbms.sh
# cd Grid_home/crs/install
# perl rootcrs.pl -patch
As per (743649.1) Oralce support rolling upgrade of OS when both OS are certified on the database that's running. This is supported only for the duration of the upgrade. But according to (1391807.1) using multiple versions of ASMLib across the cluster may not be compatible. In this case ASM disks were migrated from ASMLib disk to block devices before the upgrade therefore an ASMLib upgrade was not required. But if ASMLib was in use then ASMLib should be upgraded as part of the OS upgrade.
1. Current kernel version
uname -r
2.6.18-194.el5
2. This upgrade is done across the cluster (not a rolling upgrade) therefore stop all the cluster components
crsctl stop cluster -all
and stop disable start of crs
crsctl stop crs
crsctl disable crs
Also comment spawning of high availability processes
#h1:35:respawn:/etc/init.d/init.ohasd run >/dev/null 2>&1 </dev/null

# init q
3. Upgrade OS. New kernel version
uname -r
2.6.18-308.el5
4. Remove the comment of ohas from inittab and enable crs
h1:35:respawn:/etc/init.d/init.ohasd run >/dev/null 2>&1 </dev/null

crsctl enable crs
5. Relink ORACLE_HOME binaries
 which relink
/opt/app/oracle/product/11.2.0/db_2/bin/relink

cd $ORACLE_HOME/bin
$ relink all
writing relink log to: /opt/app/oracle/product/11.2.0/db_2/install/relink.log
Relink ends with following in the log file
test ! -f /opt/app/oracle/product/11.2.0/db_2/bin/oracle ||\
           mv -f /opt/app/oracle/product/11.2.0/db_2/bin/oracle /opt/app/oracle/product/11.2.0/db_2/bin/oracleO
mv /opt/app/oracle/product/11.2.0/db_2/rdbms/lib/oracle /opt/app/oracle/product/11.2.0/db_2/bin/oracle
chmod 6751 /opt/app/oracle/product/11.2.0/db_2/bin/oracle
5. Relink the GI HOME binaries
cd $GI_HOME/crs/install

# perl rootcrs.pl -unlock
Using configuration parameter file: ./crsconfig_params
CRS-4544: Unable to connect to OHAS
CRS-4000: Command Stop failed, or completed with errors.
Successfully unlock /opt/app/11.2.0/grid3

$ export ORACLE_HOME=$GI_HOME
$ export PATH=$ORACLE_HOME/bin:$PATH
$ which relink
/opt/app/11.2.0/grid3/bin/relink

$GI_HOME/bin/relink
Relink ends with the following in the log file
test ! -f /opt/app/11.2.0/grid3/bin/oracle ||\
           mv -f /opt/app/11.2.0/grid3/bin/oracle /opt/app/11.2.0/grid3/bin/oracleO
mv /opt/app/11.2.0/grid3/rdbms/lib/oracle /opt/app/11.2.0/grid3/bin/oracle
chmod 6751 /opt/app/11.2.0/grid3/bin/oracle
Complete the relink process
# cd $GI_HOME/rdbms/install

# ./rootadd_rdbms.sh

# cd $GI_HOME/crs/install
# perl rootcrs.pl -patch

Using configuration parameter file: ./crsconfig_params
CRS-4124: Oracle High Availability Services startup failed.
CRS-4000: Command Start failed, or completed with errors.
Execute
/sbin/init q
to start the cluster stack.

Related Post
Upgrading ASMLib and OS in 11gR1 RAC Environment
Upgrading RHEL 6 OS in a 11gR2 RAC Environment

Useful Metalink notes
Oracle 11gR2 Relink New Feature [ID 883299.1]

Monday, July 2, 2012

Moving ASMLib disk to block devices (Non-ASMLib) in 11gR2 RAC

This post list the steps to migrate from ASMLib to block devices in a 11gR2 RAC environment. There's an earlier post which list the step to migrate from ASMLib to block devices in a 11gR2 standalone system.
The cluster users ASM for the vote disk and OCR and current configuration is
crsctl query css votedisk
##  STATE    File Universal Id                File Name Disk group
--  -----    -----------------                --------- ---------
 1. ONLINE   6d44155fe5054fb5bfd2abd3dee8a5b2 (ORCL:CLUS1) [CLUSTERDG]
 2. ONLINE   05233de65ba64fbebf13238219316963 (ORCL:CLUS2) [CLUSTERDG]
 3. ONLINE   84202daea6964f1ebf0af8c38e5a88f5 (ORCL:CLUS3) [CLUSTERDG]
Located 3 voting disk(s).
All the ASMLib disks in the system are
kfod disk=all
--------------------------------------------------------------------------------
 Disk          Size Path                                     User     Group
================================================================================
   1:       5114 Mb ORCL:CLUS1                                
   2:       5114 Mb ORCL:CLUS2                                
   3:       5114 Mb ORCL:CLUS3                                
   4:      10236 Mb ORCL:DATA                                 
   5:      10236 Mb ORCL:FLASH                                
--------------------------------------------------------------------------------
ORACLE_SID ORACLE_HOME
================================================================================
     +ASM1 /opt/app/11.2.0/grid3
     +ASM2 /opt/app/11.2.0/grid3
ASM instances
SQL> select inst_id,name,path,label from gv$asm_disk order by 1;

   INST_ID NAME       PATH       LABEL
---------- ---------- ---------- ----------
         1 DATA       ORCL:DATA  DATA
         1 CLUS3      ORCL:CLUS3 CLUS3
         1 CLUS2      ORCL:CLUS2 CLUS2
         1 CLUS1      ORCL:CLUS1 CLUS1
         1 FLASH      ORCL:FLASH FLASH
         2 DATA       ORCL:DATA  DATA
         2 CLUS3      ORCL:CLUS3 CLUS3
         2 CLUS2      ORCL:CLUS2 CLUS2
         2 CLUS1      ORCL:CLUS1 CLUS1
         2 FLASH      ORCL:FLASH FLASH
1. The ASMLib to block device migration could be done in a rolling fashion. Before proceeding shutdown the database instance on the node that's being worked on.
srvctl stop instance -d rac11g2 -i rac11g21
2. Unlike in the previous cases it was not possible to test the migration on a one node and one a single disk group.
/etc/init.d/oracleasm querydisk -d FLASH
Disk "FLASH" is a valid ASM disk on device [8, 81]

ls -l /dev/sd*
...
brw-r----- 1 root disk 8, 80 Jun 25 13:03 /dev/sdf
brw-r----- 1 root disk 8, 81 Jun 25 13:04 /dev/sdf1
brw-r----- 1 root disk 8, 96 Jun 25 13:03 /dev/sdg
brw-r----- 1 root disk 8, 97 Jun 25 13:04 /dev/sdg1

chown oracle:asmadmin /dev/sdf1

SQL> alter diskgroup flash dismount;

Diskgroup altered.

SQL> alter system set asm_diskstring='/dev/sdf1','ORCL:CLUS1','ORCL:CLUS2','ORCL:CLUS3','ORCL:DATA' scope=memory sid='+ASM1';

System altered.
It is important to set the parameter change instance level (sid='+ASM1') otherwise following error will be thrown
SQL> alter system set asm_diskstring='ORCL:CLUS*','ORCL:DATA*','/dev/sdf1' scope=memory;
alter system set asm_diskstring='ORCL:CLUS*','ORCL:DATA*','/dev/sdf1' scope=memory
*
ERROR at line 1:
ORA-32008: error while processing parameter update at instance +ASM2
ORA-02097: parameter cannot be modified because specified value is invalid
ORA-15014: path 'ORCL:FLASH' is not in the discovery set
Mounting the diskgroup again results in diskgroup using the ASMLib disk not the value set on the ASM diskstring parameter, which was happening in non-rac 11gR2 and 11gR1 RAC environments.
Mon Jun 25 13:44:38 2012
SQL> alter diskgroup flash dismount
NOTE: cache dismounting (clean) group 3/0x9A384CBC (FLASH)
NOTE: messaging CKPT to quiesce pins Unix process pid: 6170, image: oracle@rac4.code.net (TNS V1-V3)
Mon Jun 25 13:44:38 2012
NOTE: LGWR doing clean dismount of group 3 (FLASH)
NOTE: LGWR closing thread 1 of diskgroup 3 (FLASH) at ABA 58.1895
NOTE: LGWR released thread recovery enqueue
...
...
Mon Jun 25 13:44:38 2012
NOTE: diskgroup resource ora.FLASH.dg is offline
NOTE: diskgroup resource ora.FLASH.dg is updated
ALTER SYSTEM SET asm_diskstring='/dev/sdf1','ORCL:CLUS1','ORCL:CLUS2','ORCL:CLUS3','ORCL:DATA' SCOPE=MEMORY SID='+ASM1';
SQL> alter diskgroup flash mount
NOTE: cache registered group FLASH number=3 incarn=0xa6c84cc2
NOTE: cache began mount (first) of group FLASH number=3 incarn=0xa6c84cc2
NOTE: Assigning number (3,0) to disk (ORCL:FLASH)
Mon Jun 25 13:44:51 2012
NOTE: GMON heartbeating for grp 3
GMON querying group 3 at 50 for pid 27, osid 6170
3. Therefore all the ASMLib disk in the ASM instance were migrated to block devices
# /etc/init.d/oracleasm querydisk -d FLASH
Disk "FLASH" is a valid ASM disk on device [8, 81]
# /etc/init.d/oracleasm querydisk -d data
Disk "DATA" is a valid ASM disk on device [8, 65]
# /etc/init.d/oracleasm querydisk -d clus1
Disk "CLUS1" is a valid ASM disk on device [8, 17]
# /etc/init.d/oracleasm querydisk -d clus2
Disk "CLUS2" is a valid ASM disk on device [8, 33]
# /etc/init.d/oracleasm querydisk -d clus3
Disk "CLUS3" is a valid ASM disk on device [8, 49]

# chown oracle:asmadmin /dev/sdb1
# chown oracle:asmadmin /dev/sdc1
# chown oracle:asmadmin /dev/sdd1
# chown oracle:asmadmin /dev/sde1

# ls -l /dev/sd*
...
brw-r----- 1 root   disk     8, 16 Jun 25 13:03 /dev/sdb
brw-r----- 1 oracle asmadmin 8, 17 Jun 25 13:04 /dev/sdb1
brw-r----- 1 root   disk     8, 32 Jun 25 13:03 /dev/sdc
brw-r----- 1 oracle asmadmin 8, 33 Jun 25 13:04 /dev/sdc1
brw-r----- 1 root   disk     8, 48 Jun 25 13:03 /dev/sdd
brw-r----- 1 oracle asmadmin 8, 49 Jun 25 13:04 /dev/sdd1
brw-r----- 1 root   disk     8, 64 Jun 25 13:03 /dev/sde
brw-r----- 1 oracle asmadmin 8, 65 Jun 25 13:04 /dev/sde1
brw-r----- 1 root   disk     8, 80 Jun 25 13:03 /dev/sdf
brw-r----- 1 oracle asmadmin 8, 81 Jun 25 13:04 /dev/sdf1
brw-r----- 1 root   disk     8, 96 Jun 25 13:03 /dev/sdg
brw-r----- 1 root   disk     8, 97 Jun 25 13:04 /dev/sdg1
4. Change the asm_diskstring on the ASM instance
SQL> alter system set asm_diskstring='/dev/sdb1','/dev/sdc1','/dev/sdd1','/dev/sde1','/dev/sdf1' scope=spfile sid='+ASM1';

System altered.
5. Create udev rules file in the node
# ASM OCR VOTE
KERNEL=="sdb[1]", OWNER="oracle", GROUP="asmadmin", MODE="660"
KERNEL=="sdc[1]", OWNER="oracle", GROUP="asmadmin", MODE="660"
KERNEL=="sdd[1]", OWNER="oracle", GROUP="asmadmin", MODE="660"

# ASM DATA
KERNEL=="sde[1]", OWNER="oracle", GROUP="asmadmin", MODE="660"

# ASM FLASH
KERNEL=="sdf[1]", OWNER="oracle", GROUP="asmadmin", MODE="660"
6. Shutdown the clusterware stack on the node. Unlike in 11gR1 RAC since clusterware is also dependent on ASM diskgroup a database instance shutdown alone is not enough to complete the migration. A full clusterware stack shutdown on the node that's being worked on is required. Other node can remain open.
# crsctl stop crs


7. Unload the oracleasm module
# /sbin/lsmod  | grep oracleasm
oracleasm              84136  1

# /etc/init.d/oracleasm stop
Dropping Oracle ASMLib disks:                              [  OK  ]
Shutting down the Oracle ASMLib driver:                    [  OK  ]

# /etc/init.d/oracleasm disable
Writing Oracle ASM library driver configuration: done
Dropping Oracle ASMLib disks:                              [  OK  ]
Shutting down the Oracle ASMLib driver:                    [  OK  ]

# /sbin/chkconfig oracleasm off
8. Start the crs and verify the new block devices are in use by monitoring the ASM alert log
# crsctl start crs

crsctl query css votedisk
##  STATE    File Universal Id                File Name Disk group
--  -----    -----------------                --------- ---------
 1. ONLINE   6d44155fe5054fb5bfd2abd3dee8a5b2 (/dev/sdb1) [CLUSTERDG]
 2. ONLINE   05233de65ba64fbebf13238219316963 (/dev/sdc1) [CLUSTERDG]
 3. ONLINE   84202daea6964f1ebf0af8c38e5a88f5 (/dev/sdd1) [CLUSTERDG]
Located 3 voting disk(s).

SQL> ALTER DISKGROUP ALL MOUNT /* asm agent call crs *//* {0:0:2} */
NOTE: Diskgroups listed in ASM_DISKGROUPS are
         DATA
         FLASH
NOTE: Diskgroup used for Voting files is:
         CLUSTERDG
Diskgroup with spfile:CLUSTERDG
Diskgroup used for OCR is:CLUSTERDG
NOTE: cache registered group CLUSTERDG number=1 incarn=0xeadcf7b1
NOTE: cache began mount (not first) of group CLUSTERDG number=1 incarn=0xeadcf7b1
NOTE: cache registered group DATA number=2 incarn=0xeaecf7b2
NOTE: cache began mount (not first) of group DATA number=2 incarn=0xeaecf7b2
NOTE: cache registered group FLASH number=3 incarn=0xa6ccf7b3
NOTE: cache began mount (not first) of group FLASH number=3 incarn=0xa6ccf7b3
NOTE: Assigning number (1,0) to disk (/dev/sdb1)
NOTE: Assigning number (1,1) to disk (/dev/sdc1)
NOTE: Assigning number (1,2) to disk (/dev/sdd1)
NOTE: Assigning number (2,0) to disk (/dev/sde1)
NOTE: Assigning number (3,0) to disk (/dev/sdf1)
GMON querying group 1 at 4 for pid 23, osid 7880
NOTE: cache opening disk 0 of grp 1: CLUS1 path:/dev/sdb1
NOTE: F1X0 found on disk 0 au 2 fcn 0.0
NOTE: cache opening disk 1 of grp 1: CLUS2 path:/dev/sdc1
NOTE: F1X0 found on disk 1 au 2 fcn 0.0
NOTE: cache opening disk 2 of grp 1: CLUS3 path:/dev/sdd1
NOTE: F1X0 found on disk 2 au 2 fcn 0.0
NOTE: cache mounting (not first) normal redundancy group 1/0xEADCF7B1 (CLUSTERDG)
...
NOTE: cache mounting group 1/0xEADCF7B1 (CLUSTERDG) succeeded
NOTE: cache ending mount (success) of group CLUSTERDG number=1 incarn=0xeadcf7b1
GMON querying group 2 at 5 for pid 23, osid 7880
NOTE: cache opening disk 0 of grp 2: DATA path:/dev/sde1
NOTE: F1X0 found on disk 0 au 2 fcn 0.0
NOTE: cache mounting (not first) external redundancy group 2/0xEAECF7B2 (DATA)
...
NOTE: cache mounting group 2/0xEAECF7B2 (DATA) succeeded
NOTE: cache ending mount (success) of group DATA number=2 incarn=0xeaecf7b2
GMON querying group 3 at 6 for pid 23, osid 7880
NOTE: cache opening disk 0 of grp 3: FLASH path:/dev/sdf1
NOTE: F1X0 found on disk 0 au 2 fcn 0.0
NOTE: cache mounting (not first) external redundancy group 3/0xA6CCF7B3 (FLASH)
9. From the data views
SQL> select inst_id,name,path,label from gv$asm_disk order by 1;

   INST_ID NAME       PATH       LABEL
---------- ---------- ---------- ----------
         1 DATA       /dev/sde1
         1 CLUS3      /dev/sdd1
         1 CLUS2      /dev/sdc1
         1 CLUS1      /dev/sdb1
         1 FLASH      /dev/sdf1
         2 DATA       ORCL:DATA  DATA
         2 CLUS3      ORCL:CLUS3 CLUS3
         2 CLUS2      ORCL:CLUS2 CLUS2
         2 CLUS1      ORCL:CLUS1 CLUS1
         2 FLASH      ORCL:FLASH FLASH

10 rows selected.
10. Since it's verified that ASMLib to block device migration is working make the changes applicable to all ASM instances in the cluster by removing the instance specific asm_diskstring entry and changing the entry applicable to all instances
SQL> alter system reset asm_diskstring scope=spfile sid='+ASM1';

System altered.

SQL> alter system set asm_diskstring='/dev/sdb1','/dev/sdc1','/dev/sdd1','/dev/sde1','/dev/sdf1' scope=spfile;

System altered.
11. Make udev rules file on all the remaining nodes
# ASM OCR VOTE
KERNEL=="sdb[1]", OWNER="oracle", GROUP="asmadmin", MODE="660"
KERNEL=="sdc[1]", OWNER="oracle", GROUP="asmadmin", MODE="660"
KERNEL=="sdd[1]", OWNER="oracle", GROUP="asmadmin", MODE="660"

# ASM DATA
KERNEL=="sde[1]", OWNER="oracle", GROUP="dba", MODE="660"

# ASM FLASH
KERNEL=="sdf[1]", OWNER="oracle", GROUP="asmadmin", MODE="660"
12. Stop the cluster stack and unload and disable the oracleasm module starting on reboot and start the cluster stack
crsctl stop crs

# /sbin/lsmod  | grep oracleasm
oracleasm              84136  1
# /etc/init.d/oracleasm stop
Dropping Oracle ASMLib disks:                              [  OK  ]
Shutting down the Oracle ASMLib driver:                    [  OK  ]

# /etc/init.d/oracleasm disable
Writing Oracle ASM library driver configuration: done
Dropping Oracle ASMLib disks:                              [  OK  ]
Shutting down the Oracle ASMLib driver:                    [  OK  ]

# /sbin/chkconfig oracleasm off

crsctl start crs
13. Remove oracleasm libraries
# rpm -e oracleasmlib-2.0.4-1.el5
# rpm -e oracleasm-2.6.18-194.el5-2.0.5-1.el5
# rpm -e oracleasm-support-2.1.3-1.el5
This concludes the ASMLib to block device migration.

If the correct permission and ownership setting are not set on the block devices the start of the clusterware stack will fail and following could be observed in the ocssd.log
2012-06-25 14:03:35.314: [    CSSD][1093900608]clssnmReadDiscoveryProfile: voting file discovery string(/dev/sdb1,/dev/sdc1,/dev/sdd1,/dev/sde1,/dev/sdf1)
2012-06-25 14:03:35.314: [    CSSD][1093900608]clssnmvDDiscThread: using discovery string /dev/sdb1,/dev/sdc1,/dev/sdd1,/dev/sde1,/dev/sdf1 for initial discovery
2012-06-25 14:03:35.314: [   SKGFD][1093900608]Discovery with str:/dev/sdb1,/dev/sdc1,/dev/sdd1,/dev/sde1,/dev/sdf1:
2012-06-25 14:03:35.314: [   SKGFD][1093900608]UFS discovery with :/dev/sdb1:
2012-06-25 14:03:35.314: [   SKGFD][1093900608]Fetching UFS disk :/dev/sdb1:
2012-06-25 14:03:35.314: [   SKGFD][1093900608]OSS discovery with :/dev/sdb1:
2012-06-25 14:03:35.314: [   SKGFD][1093900608]Discovery advancing to nxt string :/dev/sdc1:
2012-06-25 14:03:35.314: [   SKGFD][1093900608]UFS discovery with :/dev/sdc1:
2012-06-25 14:03:35.314: [   SKGFD][1093900608]Fetching UFS disk :/dev/sdc1:
2012-06-25 14:03:35.314: [   SKGFD][1093900608]OSS discovery with :/dev/sdc1:
2012-06-25 14:03:35.314: [   SKGFD][1093900608]Discovery advancing to nxt string :/dev/sdd1:
2012-06-25 14:03:35.314: [   SKGFD][1093900608]UFS discovery with :/dev/sdd1:
2012-06-25 14:03:35.314: [   SKGFD][1093900608]Fetching UFS disk :/dev/sdd1:
2012-06-25 14:03:35.314: [   SKGFD][1093900608]OSS discovery with :/dev/sdd1:
2012-06-25 14:03:35.314: [   SKGFD][1093900608]Discovery advancing to nxt string :/dev/sde1:
2012-06-25 14:03:35.314: [   SKGFD][1093900608]UFS discovery with :/dev/sde1:
2012-06-25 14:03:35.314: [   SKGFD][1093900608]Fetching UFS disk :/dev/sde1:
2012-06-25 14:03:35.314: [   SKGFD][1093900608]OSS discovery with :/dev/sde1:
2012-06-25 14:03:35.315: [   SKGFD][1093900608]Discovery advancing to nxt string :/dev/sdf1:
2012-06-25 14:03:35.315: [   SKGFD][1093900608]UFS discovery with :/dev/sdf1:
2012-06-25 14:03:35.315: [   SKGFD][1093900608]Fetching UFS disk :/dev/sdf1:
2012-06-25 14:03:35.315: [   SKGFD][1093900608]OSS discovery with :/dev/sdf1:
2012-06-25 14:03:35.315: [    CSSD][1093900608]clssnmvDiskVerify: Successful discovery of 0 disks
Setting the correct permission and ownership and restarting the clusterware stack will resolve this issue.

Related Posts
Migrating block devices using ASM instance to ASMLib
Moving ASMLib disk to block devices (Non-ASMLib) in 11gR2 Standalone
Moving ASMLib disk to block devices (Non-ASMLib) in 11gR1 RAC

Useful Metalink Notes
How To Migrate ASMLIB Devices To Block Devices (Non-ASMLIB)? [ID 567508.1]

Sunday, July 1, 2012

PRIF-31: Failed to delete the specified network interface because it is the last private interface

Prior to 11.2.0.2 the steps to change the private interconnect subnet, netmask or network interface involved deleting the existing interface related to private interconnect and adding a new one. However from 11.2.0.2 onwards this will fail and it is required to add the new configuration information first before deleting the existing one.
If only the private interconnect IP is changed (192.168.0.10 changed to 192.168.0.22) then shutting down the clusterware stack and changing the IPs on the OS level and starting the clusterware stack backup would suffice. In this case the subnet is changed.
Make a backup (on all the nodes of the cluster) of the profile.xml file located in $GRID_HOME/gpnp/<hostname>/profiles/peer.
Carry out the modification as the grid user (or oracle user if role separation is not used)
Trying to delete the existing cluster interconnect will fail.
$ oifcfg getif
eth0  192.168.0.0  global  public
eth1  192.168.0.0  global  cluster_interconnect

$ oifcfg delif -global eth1
PRIF-31: Failed to delete the specified network interface because it is the last private interface
Add the new configuration
$ oifcfg setif -global eth1/192.168.1.0:cluster_interconnect

$ oifcfg getif
eth0  192.168.0.0  global  public
eth1  192.168.0.0  global  cluster_interconnect
eth1  192.168.1.0  global  cluster_interconnect
Shutdown the clusterware stack and make the changes in the OS level (/etc/hosts file, ifcfg-eth1 file and etc) and start the clusterware stack again

Delete the old configuration by specifying the correct subnet and the interface to delete
$ oifcfg delif -global eth1/192.168.0.0

$ oifcfg getif
eth0  192.168.0.0  global  public
eth1  192.168.1.0  global  cluster_interconnect
Useful Metalink notes
How to Modify Private Network Interface in 11.2 Grid Infrastructure [ID 1073502.1]
How to Modify Public or Private Network Information in Oracle Clusterware [ID 283684.1]

Related Oracle Documentation