11gR2: Steps to Rename ASM Diskgroup with DB files

11gR2 introduced renamedg utility to rename diskgroups. I would discuss renaming a diskgroup on a Standalone Grid Infrastructure installation with Oracle Restart. Before you use the command, ensure that ASM diskgroup is dismounted.
Below is syntax for the command. You can refer to
11gR2 docs for details

renamedg
[phase={ one|two |both } ] dgname=diskgroup
newdgname=newdiskgroup [config=configfile]
[ asm_diskstring=discoverystring, discoverystring ... ]
[ clean={true|false} ] [ check={true|false} ]
[ confirm={true|false}] [ verbose={ true|false} ]
[ keep_voting_files={true|false}]

Basically we have two phases of using renamedg command, phase 1 generates a configuration file to be used by phase 2. In phase 2 actual renaming of diskgroup is done.

Note : – I have tried these steps in a Test environment and tried to cover the most components. But would recommend you  to cross verify the steps in a test environment before implementing in production environment

Lets get Started!!

1) Gather all the information for Oracle restart Configuration and DB parameters

Database Configuration

[oracle@db11g bin]$ srvctl config database -d test11r2
Database unique name: test11r2
Database name: test11r2
Oracle home: /u02/app/oracle/product/11.2.0/dbhome_1
Oracle user: oracle
Spfile: +DATA/test11r2/spfiletest11r2.ora
Domain:
Start options: open
Stop options: immediate
Database role: PRIMARY
Management policy: AUTOMATIC
Disk Groups: DATA
Services:

ASM Information

[oracle@db11g bin]$ srvctl config asm
ASM home: /u02/app/oracle/product/11.2.0/grid
ASM listener: LISTENER
Spfile: +DATA/asm/asmparameterfile/registry.253.696489255
ASM diskgroup discovery string: /dev/oracleasm/disks/VOL*

[oracle@db11g bin]$ srvctl config listener
Name: LISTENER
Home: /u02/app/oracle/product/11.2.0/grid
End points: TCP:1521

Create Database parameter file

sql> create pfile='/tmp/param.txt' from spfile;

Oracle Restart configuration

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.

[oracle@db11g bin]$ crsctl disable has
[oracle@db11g bin]$ crsctl status resource
NAME=ora.DATA.dg
TYPE=ora.diskgroup.type
TARGET=ONLINE
STATE=ONLINE on db11g

NAME=ora.FLASH_ARC.dg
TYPE=ora.diskgroup.type
TARGET=ONLINE
STATE=ONLINE on db11g

NAME=ora.LISTENER.lsnr
TYPE=ora.listener.type
TARGET=ONLINE
STATE=ONLINE on db11g

NAME=ora.asm
TYPE=ora.asm.type
TARGET=ONLINE
STATE=ONLINE on db11g

NAME=ora.cssd
TYPE=ora.cssd.type
TARGET=ONLINE
STATE=ONLINE on db11g

NAME=ora.diskmon
TYPE=ora.diskmon.type
TARGET=ONLINE
STATE=ONLINE on db11g

NAME=ora.test11r2.db
TYPE=ora.database.type
TARGET=OFFLINE
STATE=OFFLINE

2) We are renaming DATA diskgroup to DG.

[oracle@db11g bin]$ renamedg dgname=data newdgname=dg asm_diskstring='/dev/oracleasm/disks/VOL*' verbose=true
NOTE: No asm libraries found in the system

Parsing parameters..

Parameters in effect:

         Old DG name       : DATA
         New DG name          : DG
         Phases               :
                 Phase 1
                 Phase 2
         Discovery str        : /dev/oracleasm/disks/VOL*
         Clean              : TRUE
         Raw only           : TRUE
renamedg operation: dgname=data newdgname=dg asm_diskstring=/dev/oracleasm/disks/VOL* verbose=true
Executing phase 1
Discovering the group
Performing discovery with string:/dev/oracleasm/disks/VOL*
Identified disk UFS:/dev/oracleasm/disks/VOL11 with disk number:2 and timestamp (32924744 398242816)
Identified disk UFS:/dev/oracleasm/disks/VOL12 with disk number:1 and timestamp (32924741 928525312)
Identified disk UFS:/dev/oracleasm/disks/VOL21 with disk number:0 and timestamp (32924744 362846208)
Identified disk UFS:/dev/oracleasm/disks/VOL22 with disk number:3 and timestamp (32924741 928525312)
Checking for hearbeat...
Re-discovering the group
Performing discovery with string:/dev/oracleasm/disks/VOL*
Identified disk UFS:/dev/oracleasm/disks/VOL11 with disk number:2 and timestamp (32924744 398242816)
Identified disk UFS:/dev/oracleasm/disks/VOL12 with disk number:1 and timestamp (32924741 928525312)
Identified disk UFS:/dev/oracleasm/disks/VOL21 with disk number:0 and timestamp (32924744 362846208)
Identified disk UFS:/dev/oracleasm/disks/VOL22 with disk number:3 and timestamp (32924741 928525312)
Checking if the diskgroup is mounted
Checking disk number:2
Checking disk number:1
Checking disk number:0
Checking disk number:3
Checking if diskgroup is used by CSS
Generating configuration file..
Completed phase 1
Executing phase 2
Looking for /dev/oracleasm/disks/VOL11
Modifying the header
Looking for /dev/oracleasm/disks/VOL12
Modifying the header
Looking for /dev/oracleasm/disks/VOL21
Modifying the header
Looking for /dev/oracleasm/disks/VOL22
Modifying the header
Completed phase 2
Terminating kgfd context 0xb7e7a050

3) Modify ASM configuration to correct the spfile location

$srvctl modify asm -p +DG/asm/asmparameterfile/registry.253.696489255
$srvctl start asm

Connect to ASM instance and mount +DG diskgroup.

sqlplus "/ as sysasm"
sql> alter diskgroup DG mount;

At this moment, you will see additional ASM diskgroup resource ora.DG.dg in crsctl configuration. Remove the old ora.DATA.dg resource

oracle@db11g ~]$ crsctl delete resource ora.DATA.dg
CRS-2730: Resource 'ora.test11r2.db' depends on resource 'ora.DATA.dg'
CRS-4000: Command Delete failed, or completed with errors.

We get above error as the test11r2 database configuration states ora.DATA.dg as dependent resource. Use -f option to drop it by force. Or you can first modify the database configuration and drop the resource later.

[oracle@db11g ~]$ crsctl delete resource ora.DATA.dg -f

4) Startup Database.

To do that we need to modify parameter file as control_files parameter and other *_file_dest needs to be changed. Change all occurence of ‘+DATA’ to ‘+DG’. Open database in mount mode as we would have to rename database files and redo logs before opening databases

SQL> startup nomount pfile='/tmp/param.txt'
ORACLE instance started.

Total System Global Area  343154688 bytes
Fixed Size                  1336428 bytes
Variable Size             218106772 bytes
Database Buffers          117440512 bytes
Redo Buffers                6270976 bytes

SQL>alter database mount;

Use below command to get sql for renaming diskgroup information for datafiles/redo logs

 select 'alter database rename file '''||name||''' to ''<strong>+DG</strong>'||substr(name,instr(name,'/',1,1))||''';' from V$DATAFILE;

select 'alter database rename file '''||member||''' to ''<strong>+DG</strong>'||substr(member,instr(member,'/',1,1))||''';' from V$logfile;

V$RECOVER_FILE view can be used to check for any issues with datafiles not being available.
If everything is fine, you can open the database. You would have noticed that I have not done anything for Temp files. Checking alert log, we see that it is renamed automatically

 Re-creating tempfile +DATA/test11r2/tempfile/temp.268.696494395 as +DG/test11r2/tempfile/temp.272.704195475

Now we need to re-create spfile on ASM Diskgroup. Use following steps

$cd $ORACLE_HOME/dbs
$cat inittest11r2.ora
SPFILE='+DATA/test11r2/spfiletest11r2.ora'
$mv inittest11r2.ora inittest11r2.ora.new
$ cp /tmp/param.txt $ORACLE_HOME/dbs/inittest11r2.ora
$sqlplus "/ as sysdba"
SQL>create spfile='+DG/test11r2/spfiletest11r2.ora' from pfile;
exit
$mv inittest11r2.ora initest11r2.old

--- Modify inittest11r2.ora.new and change diskgroup to +DG---
$cat inittest11r2.ora.new
SPFILE='+DG/test11r2/spfiletest11r2.ora'
$mv inittest11r2.ora.new inittest11r2.ora

Now we need to modify Database configuration for Oracle Restart

[oracle@db11g ~]$ srvctl config database -d test11r2
Database unique name: test11r2
Database name: test11r2
Oracle home: /u02/app/oracle/product/11.2.0/dbhome_1
Oracle user: oracle
Spfile: +DATA/test11r2/spfiletest11r2.ora
Domain:
Start options: open
Stop options: immediate
Database role: PRIMARY
Management policy: AUTOMATIC
PRCD-1012 : Failed to retrieve disk group list for database test11r2.
PRCR-1035 : Failed to look up CRS resource ora.DATA.dg for test11r2
PRCR-1001 : Resource ora.DATA.dg does not exist

As there is no DATA diskgroup available, it gives a error. If you notice initial output (before renaming diskgroup), there was entry for DATA Diskgroup. We also need to modify the entry for spfile.

[oracle@db11g ~]$ srvctl modify database -d test11r2 -p +DG/test11r2/spfiletest11r2.ora
[oracle@db11g ~]$ srvctl modify database -d test11r2 -a DG
[oracle@db11g dbs]$ srvctl config database -d test11r2
Database unique name: test11r2
Database name: test11r2
Oracle home: /u02/app/oracle/product/11.2.0/dbhome_1
Oracle user: oracle
<strong>Spfile: +DG/test11r2/spfiletest11r2.ora</strong>
Domain:
Start options: open
Stop options: immediate
Database role: PRIMARY
Management policy: AUTOMATIC
<strong>Disk Groups: DG</strong>
Services:

You can now shutdown database and use srvctl to start If you have not dropped ora.DATA.dg resource earlier (due to errors), you can drop it now without errors

 [oracle@db11g ~]$ crsctl delete resource ora.DATA.dg

You can now enable Oracle Restart

[oracle@db11g dbs]$ crsctl enable has
CRS-4622: Oracle High Availability Services autostart is enabled.

Phew!! This was quite a posting after long time!!

11gR2 – SysAsm vs SysDba

SYSASM role was introduced in 11gR1 and was designed to administer ASM instances. In 11gR1 , if you connected with SYSDBA role , you used to get a warning which was only recorded in alert log (Refer to my earlier post here). But things have changed in 11gR2. While trying to dismount a Diskgroup, I found following errors

SQL> alter diskgroup flash_arc mount;
alter diskgroup flash_arc mount
*
ERROR at line 1:
ORA-15032: not all alterations performed
ORA-15260: permission denied on ASM disk group

Above error indicates that I do not have permission on the ASM Diskgroup.  As per 11gR2 documentation, SYSASM privilege is used for carrying out administration tasks on ASM Diskgroups. SYSDBA privilege can be used only for creating/deleting aliases and querying ASM dictionary views.  Frankly speaking, you should unlearn the habit of connecting as “/ as sysdba” to ASM instance and learn connecting as “/ as sysasm”

ORA-27146 Errors while starting up Database

Short post on a issue faced by me, while starting up a cloned database

SQL> startup nomount
ORA-24323: value not allowed
ORA-27146: post/wait initialization failed

This database environment was cloned and was being started for first time after cloning. Running hcve scripts on system indicated that OS kernel parameters were adequately sized.

SHMMNI set to 1000 is adequate
SHMSEG set to 120 is adequate
SEMMNI set to 700 is adequate
SEMMNS set to 2000 is adequate
MAX_THREAD_PROC set to 256 is adequate
NFLOCKS set to 1450 is adequate

My Oracle Support articles suggested this issue is related to semaphores setting. As settings were correct, I attempted to start instance by commenting processes parameter in init.ora (setting was 1000). This time database started.

I modified the processes parameter back to 1000 and attempted to restart the Database. It started  again!! I am clueless on why it initially failed and then started with same setting. Does anyone have any reasoning for this behavior?

Don’t blindly implement Blog suggestions on your Production Servers

Don’t blindly implement Blog suggestions on your Production Servers. This post is prompted by a recent issue where in DBA had modified racgvip script based on suggestion on one of blog (won’t name it).  Any such suggestions or similar to these which involve modification of oracle provided scripts should always be verified with Oracle Support.  Anyways I have put comment on the blog entry to correct or put a disclaimer 🙂

Oracle Patch Set update (PSU 2) released for 10.2.0.4 and 11.1.0.7

Oracle has recently released Oracle Patch Set update (PSU 2) along with CPU October 2009. As informed earlier , PSU are released quarterly along with CPU patches.

If you are running 10.2.0.4 RAC database, you need to apply following patches

1) Apply Patch 8833280 (10.2.0.4.2 PSU 2) on Oracle Database 10.2.0.4
2) Apply Patch 8436582 (10.2.0.4 CRS Bundle Patch #4) on Oracle Clusterware 10.2.0.4
3) Apply Patch 8705958 (10.2.0.4 CRS PSU 2 ) on Oracle Clusterware 10.2.0.4

1)  Patch 8833280 (10.2.0.4.2 PSU 2) for Oracle Database 10.2.0.4

2) Patch 8436582 (10.2.0.4 CRS Bundle Patch #4) for Oracle Clusterware 10.2.0.4 CRS PSU2 patch contains fixes for bugs fixed in Bundle patch 4

3)  Patch 8705958 (10.2.0.4 CRS PSU 2 ) for Oracle Clusterware 10.2.0.4

Critical Patch Update (CPU) are released each quarter and contain security fixes identified by Oracle. Oracle has recently introduced, Patch Set Update (PSUs) which are proactive cumulative patches containing recommended bug fixes that are released on a regular and predictable schedule (it also contains Security fixes part of CPU). PSUs are on the same quarterly schedule as the Critical Patch Updates (CPU), specifically the Tuesday closest to the 15th of January, April, July, and October.

It is recommended to apply latest Database Patchet (aleady on 10.2.0.4) along with latest Patch Set Update (PSU2) to Oracle Database as this helps to fix the critical bugs and also serve as a new baseline version for reporting issues to Oracle.

In case of RAC, you also need to apply CRS Patch Bundle to fix the CRS issues identified by Oracle.

Please note that Patch Set Updates can be applied on the base release version or on any earlier Patch Set Update. For example, 10.2.0.4.2 can be applied on 10.2.0.4.1 and 10.2.0.4.0 . Once a Patch Set Update has been applied, the recommended way to get future security content is to apply subsequent Patch Set Updates. Reverting from an applied Patch Set Update back to the Critical Patch Update, while technically possible, requires significant time and effort, and is not advised.

To know further, please read following My Oracle Support articles

Note 405820.1 – 10.2.0.X CRS Bundle Patch Information

Note 854428.1 – Intro to Patch Set Updates (PSU)

Note 8833280.8 lists down the bugs fixed as part of PSU2.