Category Archives: asm

Changing attributes on ASM Diskgroups

You can see the attributes set on your ASM diskgroups in 2 ways, via the view  v$asm_attribute

SQL> select a.name, b.name, b.value from v$asm_attribute b, v$asm_diskgroup a where a.group_number=b.group_number and a.name='DATAX6C1' ;

Or via asmcmd and the lsattr

grid@sc7ach00pd00-d1:~$ asmcmd

ASMCMD> cd DATAX6C1
ASMCMD> lsattr -G DATAXC1 -l

Attributes you might want to pay attention to in an Exadata environment are

  • compatible.advm
  • cell.smart_scan_capable
  • appliance.mode
  • compatible.asm
  • compatible.rdbms

If you manually create a diskgroup via asmca these attributes will not normally be set, and so you may want to go manually set them.

SQL> ALTER DISKGROUP DATAX6C1 SET ATTRIBUTE 'appliance.mode'='TRUE';

The attribute is set immediately, but based on my experience, it does not come into effect until the disk group has  been rebalanced.

SQL> alter diskgroup DATAX6C1 rebalance power 2;

Moving a datafile from filesystem to ASM

I had a database where someone had created a datafile outside of ASM and on the Oracle Home filesystem. Whilst this would not cause a problem for backups as they are done via RMAN and it knows where all the files are thanks to the control file, it will have performance implications as my ASM area is on Exadata cells, and the Oracle Home filesystem is on an internal disk.

The method I am going to describe assumes that you can take the tablespace offline to carry out the maintenance.

 

Find out the filename name of your mis-located datafile

SQL> select tablespace_name, file_name from dba_data_files where file_name not like '%DATA%';
TABLESPACE_NAME
------------------------------
FILE_NAME
--------------------------------------------------------------------------------
CMPIND_XX
/u01/app/oracle/product/11.2.0.3/dbhome_1/dbs/.ora

 

Alter the affected tablespace offline so no changes will be made to the datafile while we move it.


SQL> alter tablespace cmpind_xx offline;

 

Use RMAN to copy the file (in bold below).

oracle@mydbhost:~/mel$ rman target /
Recovery Manager: Release 11.2.0.3.0 - Production on Tue Apr 1 08:11:53 2014
Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.
connected to target database: mydb (DBID=3455727309)
RMAN> copy datafile '/u01/app/oracle/product/11.2.0.3/dbhome_1/dbs/.ora' to '+DATA_EXPA';
Starting backup at 01-APR-14
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=856 device type=DISK
allocated channel: ORA_DISK_2
channel ORA_DISK_2: SID=916 device type=DISK
channel ORA_DISK_1: starting datafile copy
input datafile file number=00172 name=/u01/app/oracle/product/11.2.0.3/dbhome_1/dbs/.ora
output file name=+DATA_EXPA/mydb/datafile/cmpind_xx.657.843725535 tag=TAG20140401T081214 RECID=511 STAMP=843725535
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:03
Finished backup at 01-APR-14
Starting Control File and SPFILE Autobackup at 01-APR-14
piece handle=/stage/rman/12Mar14/c-3455727309-20140401-00 comment=NONE
Finished Control File and SPFILE Autobackup at 01-APR-14

Next step is to rename the datafile from the original name to the new OMF name that was generated during the RMAN copy (highlighted in red above) and bring the tablespace back online.

oracle@mydbhost:~/mel$ sqlplus '/ as sysdba'
SQL*Plus: Release 11.2.0.3.0 Production on Tue Apr 1 08:12:46 2014
Copyright (c) 1982, 2011, Oracle.  All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,
Data Mining and Real Application Testing options
SQL> alter database rename file '/u01/app/oracle/product/11.2.0.3/dbhome_1/dbs/.ora' to '+DATA_EXPA/mydb/datafile/cmpind_xx.657.843725535';
Database altered.
SQL> alter tablespace cmpind_xx online;
Tablespace altered.

 

 

Ways of monitoring ASM disk performance

I have the feeling I should call this post ‘part 1’ as I’m writing things as I discover new features and how to use them.

In 11G release 2 ASMCMD has a iostat feature that allows you to list the reads and writes per disk (either as I/O operation or bytes)

iostat [-et][–io] [–suppressheader] [–region] [-G diskgroup] [interval]

-e display error statistics (write/read)

-t display time statistics giving the total I/O time in hundredths of a second (requires TIMED_STATISTICS to be true)

-G diskgroup

interval – repeat the command every X seconds

As with most iostat commands, the first run in an interval is total stats up to now, and subsequent intervals cover the time since previous report.

ASMCMD> iostat -t -G data_upper 5
Group_Name  Dsk_Name                    Reads         Writes        Read_Time     Write_Time
DATA_UPPER  DATA_DM01_CD_00_ED2HCELL12  368823115776  398765133824  15652.064264  115609.999195
DATA_UPPER  DATA_DM01_CD_00_ED2HCELL13  360830513152  399665251328  15293.415546  108496.371997

Group_Name  Dsk_Name                    Reads     Writes    Read_Time  Write_Time

DATA_UPPER  DATA_DM01_CD_00_ED2HCELL12  0.00      0.00      0.00       0.00
DATA_UPPER  DATA_DM01_CD_00_ED2HCELL13  0.00      6553.60   0.00       0.00

This information is extracted from the V$ASM_DISK_IOSTAT dynamic performance view.

 

 

Moving your voting disks

11gr2 provides some good facilities for moving voting disks. http://download.oracle.com/docs/cd/E11882_01/rac.112/e10717/votocr.htm#BGBBIGJH describes the various options for you.

root@c1718-3-50 # ./crsctl query css votedisk
##  STATE    File Universal Id                File Name Disk group
--  -----    -----------------                --------- ---------
 1. ONLINE   23da5a8ec4fe4f7abf87392211a614ab (/dev/rdsk/c20t600A0B8000475BDC0000F5CA4C03684Cd0s6) [DATA1]
Located 1 voting disk(s)

I need to migrate the voting disks from disk group +DATA, which has external redundancy set, (hence only a single voting disk) to disk group +DATA1 which has normal redundancy

root@c1718-3-50 # ./crsctl replace votedisk +DATA1

That gives me the following voting disks…

root@c1718-3-50 # ./crsctl query css votedisk
##  STATE    File Universal Id                File Name Disk group
--  -----    -----------------                --------- ---------
 1. ONLINE   23da5a8ec4fe4f7abf36197331a614ff (/dev/rdsk/c20t600A0B8000475BDC0000F5CA4C063B1Dd0s6) [DATA1]
 2. ONLINE   02e77b8b7af04f6dbfb0bd7af1604eee (/dev/rdsk/c20t600A0B8000475BDC0000F5CD4C063B2Fd0s6) [DATA1]
 3. ONLINE   21a6741b7d0e4fafbf7a81d22c177521 (/dev/rdsk/c20t600A0B8000475BDC0000F5D04C063B43d0s6) [DATA1]
Located 3 voting disk(s).

Changes to ASM and other complicated jobs

This is not an exhaustive description of the process – if you can’t create redologs without my help, really, don’t try this at home.
The initial status:
2 node rac cluster (b-rac1, b-rac2) each have 4 x 5GB logfiles, stored in ASM groups +LOG1 and +LOG2.
To work with an application vendor, we need to move to raw redo logs.
Issues:
1. No spare disks
2. ASM instance parameter file is stored in +LOG1
3. OCR is in both +LOG1 +LOG2
Moving the OCR
Login as root

Run ocrcheck to see where your existing OCR location 

# ocrcheck
Status of Oracle Cluster Registry is as follows :
Version : 3
Total space (kbytes) : 262120
Used space (kbytes) : 2744
Available space (kbytes) : 259376
ID : 277541681
Device/File Name : +DATA
Device/File integrity check succeeded


Device/File Name : +LOG1
Device/File integrity check succeeded

Device/File Name : +LOG2
Device/File integrity check succeeded


Cluster registry integrity check succeeded


Logical corruption check succeeded

So, we just delete the +LOG1 and +LOG2 entries


# ocrconfig -delete +LOG1
# ocrconfig -delete +LOG2

This leaves us with a single copy of the OCR in the +DATA diskgroup.


ASM Parameter file stored in +LOG1

This initially seemed to be the most tricky – there is no $ORACLE_HOME/dbs/initASM.ora to point to an ifile and I couldn’t see how this was configured in the clusterware.

Look at the existing location

$ sqlplus ‘/ as sysasm’
SQL> show parameter spfile

NAME                                 TYPE        VALUE
———————————— ———– ——————————
spfile                               string      +LOG1/b-rac-cluster/ASMPARAMETERFILE/REGISTRY.253.713418645

There are some new commands that allow you to look after the ASM spfile in 11gR2 http://download.oracle.com/docs/cd/E11882_01/server.112/e10500/asm_util002.htm#CIHDDJAF

The one I’m going to use is spmove

ASMCMD> spmove +LOG1/b-rac-cluster/ASMPARAMETERFILE/REGISTRY.253.713418645 +DATA/b-rac-cluster/ASMPARAMETERFILE/REGISTRY.253.713418645
ORA-15056: additional error message
ORA-17502: ksfdcre:4 Failed to create file +DATA/b-rac-cluster/ASMPARAMETERFILE/REGISTRY.253.713418645
ORA-15177: cannot operate on system aliases
ORA-06512: at line 7 (DBD ERROR: OCIStmtExecute)


What? Why didn’t it work? Well the clue was in the error message, it can’t move the spfile to an alias location.
So lets try putting it somewhere simple

ASMCMD> spmove +LOG1/b-rac-cluster/ASMPARAMETERFILE/REGISTRY.253.713418645 +DATA/spfileASM.ora
ORA-15032: not all alterations performed
ORA-15028: ASM file ‘+LOG1/b-rac-cluster/ASMPARAMETERFILE/REGISTRY.253.713418645’ not dropped; currently being accessed (DBD ERROR: OCIStmtExecute) 

hmmm, I guess since ASM is open, it will be accessed by someone.

So, next steps, stop and restart the ASM instances and check they are using the new spfile

$srvctl stop asm -n db-rac1
$srvctl stop asm -n db-rac2
$sqlplus ‘/ as sysasm’

SQL*Plus: Release 11.2.0.1.0 Production on Fri Apr 23 03:30:14 2010

Copyright (c) 1982, 2009, Oracle.  All rights reserved.

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

SQL> show parameter spfile

NAME                                 TYPE        VALUE
———————————— ———– ——————————
spfile                               string      +DATA/spfileasm.ora



Disks

First step, remove all the logfiles from LOG1

alter database drop logfile group XX;

Remember the minimum amount of logfiles for a thread is 2, so I needed to temporarily create logfiles for thread 1 in +LOG2. (not described)
Login as sysasm to the ASM2 instance and dismount the +LOG1 diskgroup

$sqlplus ‘/ as sysasm’

SQL*Plus: Release 11.2.0.1.0 Production on Fri Apr 23 03:30:14 2010

Copyright (c) 1982, 2009, Oracle.  All rights reserved.

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

SQL> alter diskgroup log1 dismount;

Login as sysasm to the ASM1 instance and drop the +LOG1 diskgroup

$sqlplus ‘/ as sysasm’

SQL*Plus: Release 11.2.0.1.0 Production on Fri Apr 23 03:30:14 2010

Copyright (c) 1982, 2009, Oracle.  All rights reserved.

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

SQL> drop diskgroup log1;

The devices that make up the LOG1 diskgroup need the ASM headers removing before we can reuse them..
dd if=/dev/zero of=/dev/rdsk/c10t60060E8005B182000000B18200000102d0s0 bs=8192 count=12800
dd if=/dev/zero of=/dev/rdsk/c10t60060E8005B182000000B18200000101d0s0 bs=8192 count=12800
Then, using format as root, repartition the disks
# format /dev/rdsk/c10t60060E8005B182000000B18200000102d0s2
partition> p
Current partition table (original):
Total disk cylinders available: 36644 + 2 (reserved cylinders)

Part      Tag    Flag     Cylinders         Size            Blocks
  0        usr    wm       1 –  5462       80.01GB    (5462/0/0)   167792640
  1        usr    wm    5463 – 10924       80.01GB    (5462/0/0)   167792640
  2     backup    wu       0 – 36643      536.78GB    (36644/0/0) 1125703680
  3        usr    wm   10925 – 16386       80.01GB    (5462/0/0)   167792640
  4        usr    wm   16387 – 21848       80.01GB    (5462/0/0)   167792640
  5        usr    wm   21849 – 27310       80.01GB    (5462/0/0)   167792640
  6        usr    wm   27311 – 32772       80.01GB    (5462/0/0)   167792640
  7 unassigned    wu       0  
              0         (0/0/0)              0
You can copy this partition table to other disks using

# prtvtoc /dev/rdsk/c10t60060E8005B182000000B18200000102d0s2 | fmthard -s – /dev/rdsk/c10t60060E8005B182000000B18200000101d0s2

This server has complications – the device tree for the disks does not match on the nodes, the shared storage is c10xx on b-rac1 and c7 on b-rac2. So, to work around this, I need to create symbolic links for the disks on both nodes.

# mkdir /ORACLE/RAW
# cd /ORACLE/RAW
# ln -s /dev/rdsk/c10t60060E8005B182000000B18200000102d0s0 RAWLOG102s0
#ln -s /dev/rdsk/c10t60060E8005B182000000B18200000102d0s1 RAWLOG102s1
#ln -s /dev/rdsk/c10t60060E8005B182000000B18200000102d0s3 RAWLOG102s3
#ln -s /dev/rdsk/c10t60060E8005B182000000B18200000102d0s4 RAWLOG102s4
#ln -s /dev/rdsk/c10t60060E8005B182000000B18200000102d0s5 RAWLOG102s5
#ln -s /dev/rdsk/c10t60060E8005B182000000B18200000102d0s6 RAWLOG102s6
#ln -s /dev/rdsk/c10t60060E8005B182000000B18200000101d0s0 RAWLOG101s0
#ln -s /dev/rdsk/c10t60060E8005B182000000B18200000101d0s1 RAWLOG101s1
#ln -s /dev/rdsk/c10t60060E8005B182000000B18200000101d0s3 RAWLOG101s3
#ln -s /dev/rdsk/c10t60060E8005B182000000B18200000101d0s4 RAWLOG101s4
#ln -s /dev/rdsk/c10t60060E8005B182000000B18200000101d0s5 RAWLOG101s5
#ln -s /dev/rdsk/c10t60060E8005B182000000B18200000101d0s6 RAWLOG101s6

Set the permissions for Oracle
# cd /ORACLE
# chown -R oracle:dba RAW
Go through the same process on b-rac2, using c7 rather than c10.

Now, I can create my logfiles using my new raw devices

SQL> alter database add logfile thread 1 group 10 (‘/ORACLE/RAW/RAWLOG101s0’) size 5g reuse;
SQL> alter database add logfile thread 1 group 11 (‘/ORACLE/RAW/RAWLOG102s0’) size 5g reuse;
SQL> alter database add logfile thread 1 group 12 (‘/ORACLE/RAW/RAWLOG101s1’) size 5g reuse;
SQL> alter database add logfile thread 1 group 13 (‘/ORACLE/RAW/RAWLOG102s1’) size 5g reuse;

Do the usual alter system switch logfile until you’re into the new logs. Drop any temporary logs you’ve created for the instance.

 
Follow the same process with group +LOG2.

11g R2 – Moving your voting disk and cluster registry.

What happens if you’ve created your voting disk or cluster registry in the wrong place?

They’re very simple to move, for example to move them from a cluster filesystem to ASM

OCR
Login as root

Run ocrcheck to see where your existing OCR location (so you can delete it later).

# ocrcheck
Status of Oracle Cluster Registry is as follows :
Version : 3
Total space (kbytes) : 262120
Used space (kbytes) : 2744
Available space (kbytes) : 259376
ID : 277541681
Device/File Name : /OCR/b-rac-cluster/ocr
Device/File integrity check succeeded

Cluster registry integrity check succeeded

Logical corruption check succeeded

# ocrconfig -add +DATA
# ocrconfig -delete /OCR/b-rac-cluster/ocr

Run ocrcheck to check it’s all ok

# ocrcheck
Status of Oracle Cluster Registry is as follows :
Version : 3
Total space (kbytes) : 262120
Used space (kbytes) : 2744
Available space (kbytes) : 259376
ID : 277541681
Device/File Name : +DATA
Device/File integrity check succeeded

Cluster registry integrity check succeeded

Logical corruption check succeeded

Voting

Login as Oracle

Find your existing voting disk

$ crsctl query css votedisk

## STATE File Universal Id File Name Disk group
— —– —————– ——— ———
1. ONLINE 96953eb1cac64fcabf4d0f60d0012ef9 (/OCR/b-rac-cluster/vdsk) []

Change your voting disk to the +DATA diskgroup

b-rac1 $ crsctl replace votedisk +DATA
CRS-4256: Updating the profile
Successful addition of voting disk 3ddb5231538c4fc3bf2d0053e34a75f0.
Successful deletion of voting disk 96953eb1cac64fcabf4d0f60d0012ef9.
Successfully replaced voting disk group with +DATA.
CRS-4256: Updating the profile
CRS-4266: Voting file(s) successfully replaced

Check it’s moved ok

b-rac1 $ crsctl query css votedisk
## STATE File Universal Id File Name Disk group
— —– —————– ——— ———
1. ONLINE 3ddb5231538c4fc3bf2d0053e34a75f0 (/ORACLE/ASM/ASM0103) [DATA]
Located 1 voting disk(s).