Moving platforms

I'm moving to wordpress for this blog - I haven't been happy with the editing tools provided by blogger.com and need a bit more control.

Find me at http://technicalsanctuary.wordpress.com/

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.