Vineet's Oracle Blog Spot: January 2016

It’s been long since i shared something related to core competency. So here is snippet of it; going through Jet Lag I somehow manage to brush up some core skills on ASM 11gr2 RAC machine.

The whole idea was to rename the disk-group of Data File<+DATA>and I started working on it; most of you already faced this issue so pardon me and let me know if think in some or the other way. Going through I came across the flaw in my already created design on virtual machine and this flaw didn’t allowed me to rename the disk-groups.

Version : 11.2.0.2

Linux: 5.8

Database: 2 Nodes RAC SYSTEM.

Storage: ASM with UDEV configuration.

Here are the steps that I performed;

1) Switched to clusterware

2) Gathered all the information about the Instance on the node and ASM configuration. I used srvctl command to do so;

[oracle@rac1 bin]$ ./srvctl config database -d RAC

Database unique name: RAC

Database name: RAC

Oracle home: /u01/app/oracle/product/11.2.0/db_1

Oracle user: oracle

Spfile: +DATA/RAC/spfileRAC.ora

Domain:

Start options: open

Stop options: immediate

Database role: PRIMARY

Management policy: AUTOMATIC

Server pools: RAC

Database instances: RAC1,RAC2

Disk Groups: DATA

Services:

Database is administrator managed

[oracle@rac1 bin]$ srvctl config asm -a

ASM home: /u01/app/11.2.0/grid

ASM listener: LISTENER

ASM is enabled.

Just to add Even if you are managing a non-RAC database, in 11g, it is recommended to use “srvctl” commands to start and stop the database. But, if the database is not created using DBCA or not upgraded using DBUA, the database will not be registered in the “Oracle Restart” configuration automatically.

Oracle Restart monitors the services registered and restarts if there is an abnormal end or can stop and start the databases during server reboot. Please read Oracle Documentation to learn more about Oracle Restart.

3) Prepared Database configuration: So just collected up and didn’t executed it.

Since I am changing the disk-group; on DB side I have to rename the files too with New disk group<Here DGDATA>

-----Log file Renaming--------

ALTER DATABASE RENAME FILE '+DATA/rac/onlinelog/group_2.264.892205005' TO '+DGDATA1/rac/onlinelog/group_2.264.892205005';

ALTER DATABASE RENAME FILE '+DATA/rac/onlinelog/group_2.265.892205007' TO '+DGDATA1/rac/onlinelog/group_2.265.892205007';

ALTER DATABASE RENAME FILE '+DATA/rac/onlinelog/group_1.262.892205003' TO '+DGDATA1/rac/onlinelog/group_1.262.892205003';

ALTER DATABASE RENAME FILE '+DATA/rac/onlinelog/group_1.263.892205005' TO '+DGDATA1/rac/onlinelog/group_1.263.892205005';

ALTER DATABASE RENAME FILE '+DATA/rac/onlinelog/group_3.269.892205175' TO '+DGDATA1/rac/onlinelog/group_3.269.892205175';

ALTER DATABASE RENAME FILE '+DATA/rac/onlinelog/group_3.270.892205175' TO '+DGDATA1/rac/onlinelog/group_3.270.892205175';

ALTER DATABASE RENAME FILE '+DATA/rac/onlinelog/group_4.271.892205175' TO '+DGDATA1/rac/onlinelog/group_4.271.892205175';

ALTER DATABASE RENAME FILE '+DATA/rac/onlinelog/group_4.272.892205177' TO '+DGDATA1/rac/onlinelog/group_4.272.892205177';

-----Data file Renaming-------

ALTER DATABASE RENAME FILE '+DATA/rac/datafile/system.256.892204895' TO '+DGDATA1/rac/datafile/system.256.892204895';

ALTER DATABASE RENAME FILE '+DATA/rac/datafile/sysaux.257.892204897' TO '+DGDATA1/rac/datafile/sysaux.257.892204897';

ALTER DATABASE RENAME FILE '+DATA/rac/datafile/undotbs1.258.892204897' TO '+DGDATA1/rac/datafile/undotbs1.258.892204897';

ALTER DATABASE RENAME FILE '+DATA/rac/datafile/users.259.892204899' TO '+DGDATA1/rac/datafile/users.259.892204899';

ALTER DATABASE RENAME FILE '+DATA/rac/datafile/example.267.892205013' TO '+DGDATA1/rac/datafile/example.267.892205013';

ALTER DATABASE RENAME FILE '+DATA/rac/datafile/undotbs2.268.892205125' TO '+DGDATA1/rac/datafile/undotbs2.268.892205125';

-----Control File----

ALTER DATABASE RENAME FILE '+DATA/rac/tempfile/temp.266.892205009' TO '+DGDATA1/rac/tempfile/temp.266.892205009'

4) Prepare new initialization parameter file say any backup file and replace below

.control_files
.db_create_file_dest
.db_create_online_log_dest_1
.db_create_online_log_dest_2
.db_recovery_file_dest
and all other parameters containing old DISK GROUPS names

5) Stop databases

$ Switch to clusterware

srvctl stop database -d RAC

Disable Oracle Restart so that it does not restart any component. 'crsctl status resource' can be used to check status of resources. As you can see I have stopped database but ASM Diskgroup DATA is still mounted.

./crsctl disable has

Now came the pain-point for me and here I realize the flaw in my RAC ASM creation;

Pre-requisite for renaming is that disk-group should be dismounted. So have used ASMCMD command to dismount the diskgroup +DATA

ASMCMD> umount DATA

ORA-15032: not all alterations performed

ORA-15027: active use of diskgroup "DATA" precludes its dismount (DBD ERROR: OCIStmtExecute)

This led me to new corner of ASM; I checked what all clients are connected to ASM Db.

SQL> select instance_name,db_name,status from v$asm_client;

INSTANCE_NAME DB_NAME STATUS

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

+ASM1 +ASM CONNECTED

This didn’t give me any direction; just that ASM is connected and probably using spfile so this I need to remove in any case. But for this also I need to shut the Db down which was not allowing me to do so.

So I checked what is actually hindering this?

I went back to asmcmd command and used lsof command which tell which file is in use.

[oracle@rac1 bin]$ asmcmd lsof

DB_Name Instance_Name Path

+ASM +ASM1 +data.255.4294967295

So 255 file number is having the issue but what is this file? And Why it is hindering me to dismount the disk-group. Here v$asm_file dictionary comes in to play

SQL> select group_number, file_number, bytes, type from v$asm_file;

GROUP_NUMBER FILE_NUMBER BYTES TYPE

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

1 255 272756736 OCRFILE

So the blunder was: I created only one +DATA Diskgroup and this disk group was storing all clusterware and DB files <Spfile,OCR/VOTING DISK/DATAFILE,REDO LOG etc>

Now it was clear what is the issue; but how to solve is again a challenge for me; I Checked documentation and found out that there was command for replacing voting disk/OCR. In 11gR2 you can run this command while your cluster is up and running but for replacing or adding you need to have a new disk-group.

My virtual box was self-contained with only 3 shared disk<asm-disk1,2,3> and all these were created as part of Normal redundancy of +DATA disk-group. And as I mentioned this is the disk-group was storing OCR and Voting Disk.

Now I decide to add new shared disk<Block device> using UDEV functionality have probe new partition as /dev/sde1 (There is procedure to convert Disk device to discoverable ASM disk you can find it on Internet so am not discussing it here); When done created external redundant disk so that I can save OCR/Voting Disk/Spfile to it.

So I logged on to asm instance<Node 1> and executed below.

CREATE DISKGROUP DGDATA EXTERNAL REDUNDANCY

DISK '/dev/asm-disk4'

ATTRIBUTE 'au_size'='1M';

Don’t forget to mount the diskgroup to NODE 2; as Oracle wont mount it automatically. If you forget to do this then you may not able to crack OCR to other Disk-group and will fail <PROT-28: Cannot delete or replace the only configured Oracle Cluster Registry location>

Anyways first will work on Voting disk J

Once diskgroup created I used below command to replace the voting Disk and I failed againL

./crsctl replace votedisk +DGDATA

I checked alert log and I see ASM required compatibility of 11.2 and higher.

NOTE: diskgroup resource ora.DGDATA.dg is online

Fri Jan 15 01:14:46 2016

NOTE: updated gpnp profile ASM diskstring: /dev/asm*

Fri Jan 15 01:14:46 2016

NOTE: Creating voting files in diskgroup DGDATA

Fri Jan 15 01:14:46 2016

NOTE: Voting File refresh pending for group 2/0x15f8a1da (DGDATA)

NOTE: Attempting voting file creation in diskgroup DGDATA

ERROR: Voting file allocation failed for group DGDATA

Errors in file /u01/app/oracle/diag/asm/+asm/+ASM1/trace/+ASM1_ora_5428.trc:

ORA-15221: ASM operation requires compatible.asm of 11.2.0.0.0 or higher

"alert_+ASM1.log" 3090L, 125974C

And I checked the new diskgroup has the compatibility of 10.1 and why this; so this basically happens when creating diskgroup via ASM Instance<sqlplus / as sysasm> and not providing attributes keys; so ASM instance allocates default 10.1 for 11G Db’s. If you create diskgroup by using ASMCMD command than this will automatically give correct compatibility in compare to sqlplus version. So its ups to you whichever suits youJ

SQL> select name,COMPATIBILITY,DATABASE_COMPATIBILITY from V$ASM_DISKGROUP where name='DGDATA';

NAME COMPATIBILITY DATABASE_COMPATIBILITY

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

DGDATA 10.1.0.0.0 10.1.0.0.0

Hence now will do it manually alteration of diskgroup.

SQL> alter diskgroup data set ATTRIBUTE 'COMPATIBLE.RDBMS'='11.2';

Diskgroup altered.

SQL> alter diskgroup data set ATTRIBUTE 'COMPATIBLE.ASM'='11.2';

Diskgroup altered.

Tried again with replace voting disk command and whoaaaa; successful J

./crsctl replace votedisk +DGDATA

Successful addition of voting disk 0ebedd9fff6e4f38bf8fd7a759cbbf0c.

Successful deletion of voting disk 718d42b5bb834f62bf97ca62131fe296.

Successful deletion of voting disk 89c65fbddc6a4f3cbf82c445bf67e116.

Successful deletion of voting disk 318cf7d9d79a4fd7bf62ebe2c930402d.

Successfully replaced voting disk group with +DGDATA.

Crosschecked at clusterware level:---à Half way through

./crsctl query css votedisk

## STATE File Universal Id File Name Disk group

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

1. ONLINE 0ebedd9fff6e4f38bf8fd7a759cbbf0c (/dev/asm-disk4) [DGDATA]

For OCR ; just checked status of OCR where it points

./ocrcheck

Status of Oracle Cluster Registry is as follows :

Version : 3

Total space (kbytes) : 262120

Used space (kbytes) : 2688

Available space (kbytes) : 259432

ID : 1171687482

Device/File Name : +DATA-à see here OCR is pointing to +DATA

Device/File integrity check succeeded

Device/File not configured

Cluster registry integrity check succeeded

Logical corruption check succeeded

Run below command to add OCR to DGDATA

./ocrconfig -add +DGDATA

Check the status again; you will see OCR is attached to both disk-group so we have to delete it from +DATA and this is what I wanted.

./ocrcheck

Status of Oracle Cluster Registry is as follows :

Version : 3

Total space (kbytes) : 262120

Used space (kbytes) : 2688

Available space (kbytes) : 259432

ID : 1171687482

Device/File Name : +DATA

Device/File integrity check succeeded

Device/File Name : +DGDATA

Device/File integrity check succeeded

Device/File not configured

Cluster registry integrity check succeeded

Logical corruption check succeeded

Now to delete the OCR from +DATA

[root@rac1 bin]# ./ocrconfig -delete +DATA

[root@rac1 bin]# ./ocrcheck

Status of Oracle Cluster Registry is as follows :

Version : 3

Total space (kbytes) : 262120

Used space (kbytes) : 2688

Available space (kbytes) : 259432

ID : 1171687482

Device/File Name : +DGDATA

Device/File integrity check succeeded

Device/File not configured

Cluster registry integrity check succeeded

Logical corruption check succeeded.

So finally I managed to put OCR on +DGDATA; now I can think of dismounting the DATA diskgroup; but again it failed and the reason is clear the ASM instance was started using spfile and it was part of +DATA so this to be moved too.

ASMCMD> umount DATA

ORA-15032: not all alterations performed

ORA-15027: active use of diskgroup "DATA" precludes its dismount (DBD ERROR: OCIStmtExecute)

So this can be done via creating first Pfile and then use Pfile for creating Spfile.

SQL> create pfile from spfile;

SQL> create spfile='+DGDATA' from pfile;

To drop the diskgroup , you will need to restart the cluster on both the nodes as ASM instance is still using the file. Run following commands on both the nodes

./crsctl stop cluster

./crsctl start cluster

Verify that OCR location is pointing to the correct diskgroup on all nodes before starting cluster.

[oracle@rac1 bin]$ cat /etc/oracle/ocr.loc

#Device/file +DATA getting replaced by device +DGDATA

ocrconfig_loc=+DGDATA

and finally trying to UMOUNT DATA

ASMCMD> umount DATA-------à +DATA now dismounted. Wow managed to do it quite a learningJ

ASMCMD>

Rest Data-file and renaming now easy; which I believe I can leave it to you.

Hope you enjoyed it!!!

Enjoy Learning!!!

Vineet's Oracle Blog Spot

Thursday 14 January 2016

ASM DISKGROUP RENAMING OCR AND VOTING DISK

About Me