Tuesday, December 20, 2011

RENAME ASM DISKGROUP ON RAC WITH STANDBY


In this below exercise, I have performed renaming the ASM DISKGROUP, WHILE THE DB IS DOWN. We can perform the rename when the DB is up and running by placing the tablespace in the offline mode

I have performed this activity on 11.2.0.2 RAC setup. Having 2 node primary DB & 2 node standby database

PRIMARY DB è bhuora01/ bhuora02
STANDBY DB è karlora01/ karlora02

Please understand the steps before performing it in the production database
We are renaming a DISKGROUP from +TEST_RENAME to +TEST. We are performing rename both in the primary & standby cluster database

STEP#1

First we need to identify the datafile which are available in the DISKGROUP in primary & standby database

On bhuora01(primary database)

SQL> select name from v$datafile where name like '+TEST%';

NAME
--------------------------------------------------------------------------------
+TEST_RENAME/BHU_b/datafile/test.256.769969469

On karlora01(standby database)

SQL> select name from v$datafile where name like '+TEST%';

NAME
--------------------------------------------------------------------------------
+TEST_RENAME/BHU_a/datafile/test.256.769969471

STEP#2

Before starting the RENAME of DISK GROUP, we have checked the status of Data guard broker

-- we can connect in the primary or standby database to check the sync status using broker.

DGMGRL> show configuration;

Configuration - DG_BHU

  Protection Mode: MaxAvailability
  Databases:
    BHU_B - Primary database
    BHU_A - Physical standby database

Fast-Start Failover: DISABLED

Configuration Status:
SUCCESS

DGMGRL> exit

STEP#3

Shutdown the database on the primary & standby database environments
-- Shutdown primary database
bhuora01> srvctl stop database -d BHU_b

-- finding the status of the database after shutdown

 bhuora01> srvctl status database -d BHU_b
Instance BHU_1 is not running on node bhuora01
Instance BHU_2 is not running on node bhuora02

-- shutdown standby database

 karlora01>  srvctl stop database -d BHU_a

-- finding the status of the database after shutdown

 karlora01>  srvctl status database -d BHU_a
Instance BHU_1 is not running on node karlora01
Instance BHU_2 is not running on node karlora02
 karlora01>

STEP#4


-- Changed the ASM profile On primary database, I am looking in to the details of the DISKGROUP

  bhuora01> asmcmd –p

ASMCMD [+] > lsdg test*
State    Type    Rebal  Sector  Block       AU  Total_MB  Free_MB  Req_mir_free_MB  Usable_file_MB  Offline_disks  Voting_files  Name
MOUNTED  EXTERN  N         512   4096  4194304      6144     4968                0            4968              0             N  TEST_RENAME/
ASMCMD [+] >

-- To check the status of diskgroup using cluster resource

  bhuora01> crsctl stat res ora.TEST.dg
NAME=ora.TEST_RENAME.dg
TYPE=ora.diskgroup.type
TARGET=ONLINE               , ONLINE
STATE=ONLINE on bhuora01, ONLINE on bhuora02

-- Changed the ASM profile On standby database, I am looking in to the details of the DISKGROUP

  karlora01> asmcmd -p

ASMCMD [+] > lsdg test*
State    Type    Rebal  Sector  Block       AU  Total_MB  Free_MB  Req_mir_free_MB  Usable_file_MB  Offline_disks  Voting_files  Name
MOUNTED  EXTERN  N         512   4096  1048576      6144     5012                0            5012              0             N  TEST_RENAME/
ASMCMD [+] >

-- To check the status of diskgroup using cluster resource

  karlora01> crsctl stat res ora.TEST.dg
NAME=ora.TEST_RENAME.dg
TYPE=ora.diskgroup.type
TARGET=ONLINE               , ONLINE
STATE=ONLINE on karlora01, ONLINE on karlora02

STEP#5

Now below operation performed only on the primary database environment and it has to be performed on entire cluster nodes
ALL THE STEPS ARE PERFORMED USING ASM PROFILE (No need to login as a root user)

Node#1 of primary database
è checking the status of diskgroup before performing the umount on node#1

  bhuora01> asmcmd –p

ASMCMD [+] > lsdg test*
State    Type    Rebal  Sector  Block       AU  Total_MB  Free_MB  Req_mir_free_MB  Usable_file_MB  Offline_disks  Voting_files  Name
MOUNTED  EXTERN  N         512   4096  4194304      6144     4968                0            4968              0             N  TEST_RENAME/

è UNMOUNT THE DISKGROUP on Node#1 on the primary cluster environment

ASMCMD [+] > umount TEST_RENAME

è Checking the diskgroup status after it has been unmounted on the same node

ASMCMD [+] > lsdg TEST_RENAME
ASMCMD-08001: diskgroup 'TEST_RENAME' does not exist or is not mounted

Node#2 of primary database

è checking the status of diskgroup before performing the umount on node#2

  bhuora02> asmcmd –p

ASMCMD [+] > lsdg test*
State    Type    Rebal  Sector  Block       AU  Total_MB  Free_MB  Req_mir_free_MB  Usable_file_MB  Offline_disks  Voting_files  Name
MOUNTED  EXTERN  N         512   4096  4194304      6144     4968                0            4968              0             N  TEST_RENAME/

è UNMOUNT THE DISKGROUP on Node#2 on the primary cluster environment

ASMCMD [+] > umount TEST_RENAME

è Checking the diskgroup status after it has been unmounted

ASMCMD [+] > lsdg TEST_RENAME
ASMCMD-08001: diskgroup 'TEST_RENAME' does not exist or is not mounted


Note: make sure that above steps are completed successfully. if the diskgroup is accessed by some one in the server then it will throw an error

à check the status of diskgroup using cluster service

  bhuora01> crsctl  stat res ora.TEST_RENAME.dg
NAME=ora.TEST_RENAME.dg
TYPE=ora.diskgroup.type
TARGET=OFFLINE, OFFLINE
STATE=OFFLINE, OFFLINE

STEP#6

Renaming the diskgroup should be performed only on one node of the cluster database environment. I am performing for primary database.
THE STEPS ARE PERFORMED USING ASM PROFILE(No need to login as a root user)

I am running rename command on the node#1 of the primary database. I am renaming the diskgroup from TEST_RENAME to TEST

  bhuora01> renamedg phase=both dgname=test_rename newdgname=TEST verbose=true

Parsing parameters..

Parameters in effect:

         Old DG name       : TEST_RENAME
         New DG name          : TEST
         Phases               :
                 Phase 1
                 Phase 2
         Discovery str        : (null)
         Clean              : TRUE
         Raw only           : TRUE
renamedg operation: phase=both dgname=test_rename newdgname=TEST verbose=true
Executing phase 1
Discovering the group
Performing discovery with string:
Identified disk ASM:/opt/oracle/extapi/64/asm/orcl/1/libasm.so:ORCL:DG_TEST with disk number:0 and timestamp (32960944 -1401007104)
Identified disk ASM:/opt/oracle/extapi/64/asm/orcl/1/libasm.so:ORCL:DG_TEST1 with disk number:1 and timestamp (32960944 -1401007104)
Identified disk ASM:/opt/oracle/extapi/64/asm/orcl/1/libasm.so:ORCL:DG_TEST2 with disk number:2 and timestamp (32960944 -1401007104)
Checking for hearbeat...
Re-discovering the group
Performing discovery with string:
Identified disk ASM:/opt/oracle/extapi/64/asm/orcl/1/libasm.so:ORCL:DG_TEST with disk number:0 and timestamp (32960944 -1401007104)
Identified disk ASM:/opt/oracle/extapi/64/asm/orcl/1/libasm.so:ORCL:DG_TEST1 with disk number:1 and timestamp (32960944 -1401007104)
Identified disk ASM:/opt/oracle/extapi/64/asm/orcl/1/libasm.so:ORCL:DG_TEST2 with disk number:2 and timestamp (32960944 -1401007104)
Checking if the diskgroup is mounted or used by CSS
Checking disk number:0
Checking disk number:1
Checking disk number:2
Generating configuration file..
Completed phase 1
Executing phase 2
Looking for ORCL:DG_TEST
Modifying the header
Looking for ORCL:DG_TEST1
Modifying the header
Looking for ORCL:DG_TEST2
Modifying the header
Completed phase 2
Terminating kgfd context 0x2b3a420f00a0
  bhuora01>

STEP#7

Once the rename has been completed successfully, we need to mount the diskgroup on entire cluster nodes on the primary database environment


è MOUNT THE DISKGROUP with the new name on Node#1 on the primary cluster environment

  bhuora01> asmcmd mount test

è Checking the diskgroup status after it has been mounted

  bhuora01> asmcmd lsdg test
State    Type    Rebal  Sector  Block       AU  Total_MB  Free_MB  Req_mir_free_MB  Usable_file_MB  Offline_disks  Voting_files  Name
MOUNTED  EXTERN  N         512   4096  4194304      6144     4968                0            4968              0             N  TEST/
  bhuora01>

è MOUNT THE DISKGROUP with the new name on Node#2 on the primary cluster environment

 +ASM2 bhuora02> asmcmd mount test

è Checking the diskgroup status after it has been mounted

 +ASM2 bhuora02> asmcmd lsdg test
State    Type    Rebal  Sector  Block       AU  Total_MB  Free_MB  Req_mir_free_MB  Usable_file_MB  Offline_disks  Voting_files  Name
MOUNTED  EXTERN  N         512   4096  4194304      6144     4968                0            4968              0             N  TEST/
 +ASM2 bhuora02>

STEP#8

When you look in to the cluster resource, we can see a new resource (renamed diskgroup) has been automatically added on the cluster environment.

  bhuora01> crsctl stat res ora.TEST.dg
NAME=ora.TEST.dg
TYPE=ora.diskgroup.type
TARGET=ONLINE               , ONLINE
STATE=ONLINE on bhuora01, ONLINE on bhuora02

  karlora01> crsctl  stat res ora.TEST_RENAME.dg
NAME=ora.TEST_RENAME.dg
TYPE=ora.diskgroup.type
TARGET=OFFLINE, OFFLINE
STATE=OFFLINE, OFFLINE

STEP#9

We have to perform the same steps in the standby database environment also, if we have a plan to change the diskgroup.

We have to follow the step from STEP 3 to STEP 8.
I am pasting the steps for reference which we performed in the standby database environment

UNMOUNT IN ALL THE CLUSTER NODES OF THE CLUSTER
Node#1
  karlora01> asmcmd umount TEST_RENAME

  karlora01> asmcmd lsdg test_rename
ASMCMD-08001: diskgroup 'test_rename' does not exist or is not mounted

Node#2
  karlora02> asmcmd umount TEST_RENAME

  karlora02> asmcmd lsdg test_rename
ASMCMD-08001: diskgroup 'test_rename' does not exist or is not mounted

RESOURCE DETAILS AFTER UMOUNT
ora.TEST_RENAME.dg             OFFLINE, OFFLINE

I am running rename command on the node#1 of the standby database. I am renaming the diskgroup from TEST_RENAME to TEST

  karlora01> renamedg phase=both dgname=test_rename newdgname=TEST verbose=true

Parsing parameters..

Parameters in effect:

         Old DG name       : TEST_RENAME
         New DG name          : TEST
         Phases               :
                 Phase 1
                 Phase 2
         Discovery str        : (null)
         Clean              : TRUE
         Raw only           : TRUE
renamedg operation: phase=both dgname=test_rename newdgname=TEST verbose=true
Executing phase 1
Discovering the group
Performing discovery with string:
Identified disk ASM:/opt/oracle/extapi/64/asm/orcl/1/libasm.so:ORCL:DG_TEST with disk number:0 and timestamp (32960971 1603601408)
Identified disk ASM:/opt/oracle/extapi/64/asm/orcl/1/libasm.so:ORCL:DG_TEST1 with disk number:1 and timestamp (32960971 1603601408)
Identified disk ASM:/opt/oracle/extapi/64/asm/orcl/1/libasm.so:ORCL:DG_TEST2 with disk number:2 and timestamp (32960971 1603601408)
Checking for hearbeat...
Re-discovering the group
Performing discovery with string:
Identified disk ASM:/opt/oracle/extapi/64/asm/orcl/1/libasm.so:ORCL:DG_TEST with disk number:0 and timestamp (32960971 1603601408)
Identified disk ASM:/opt/oracle/extapi/64/asm/orcl/1/libasm.so:ORCL:DG_TEST1 with disk number:1 and timestamp (32960971 1603601408)
Identified disk ASM:/opt/oracle/extapi/64/asm/orcl/1/libasm.so:ORCL:DG_TEST2 with disk number:2 and timestamp (32960971 1603601408)
Checking if the diskgroup is mounted or used by CSS
Checking disk number:0
Checking disk number:1
Checking disk number:2
Generating configuration file..
Completed phase 1
Executing phase 2
Looking for ORCL:DG_TEST
Modifying the header
Looking for ORCL:DG_TEST1
Modifying the header
Looking for ORCL:DG_TEST2
Modifying the header
Completed phase 2
Terminating kgfd context 0x2ac61d6d40a0
  karlora01>

è MOUNT THE DISKGROUP on Node#1 on the standby cluster environment

  karlora01> asmcmd mount TEST

  karlora01> asmcmd lsdg test
State    Type    Rebal  Sector  Block       AU  Total_MB  Free_MB  Req_mir_free_MB  Usable_file_MB  Offline_disks  Voting_files  Name
MOUNTED  EXTERN  N         512   4096  1048576      6144     5012                0            5012              0             N  TEST/
  karlora01>

è MOUNT THE DISKGROUP on Node#2 on the standby cluster environment

  karlora02> asmcmd mount TEST
  karlora02> asmcmd lsdg test
State    Type    Rebal  Sector  Block       AU  Total_MB  Free_MB  Req_mir_free_MB  Usable_file_MB  Offline_disks  Voting_files  Name
MOUNTED  EXTERN  N         512   4096  1048576      6144     5012                0            5012              0             N  TEST/

ora.TEST.dg                    ONLINE on karlora01, ONLINE on karlora02
ora.TEST_RENAME.dg             OFFLINE, OFFLINE


STEP#10 Before starting the database, we have modify the configuration details for the databases in the server control
This is the configuration details for the primary database in the server control. In this we have modify the DISK GROUPS column in the below output. Below output shows TEST_RENAME mount point is part of the primary database. If you start the database with out modifying it, you will be getting an error message but you can start using the sqlplus option.

  bhuora01> srvctl config database -d BHU_b
Database unique name: BHU_B
Database name: BHU
Oracle home: /oracle/BHU/112_64
Oracle user: oracle
Spfile: +BHU_DATA1/BHU_B/PARAMETERFILE/spfile.261.764676511
Domain:
Start options: open
Stop options: immediate
Database role: PRIMARY
Management policy: AUTOMATIC
Server pools: BHU_B
Database instances: BHU_1,BHU_2
Disk Groups: BHU_DATA1,BHU_ARCH,BHU_RECO,BHU_OLOG,BHU_MLOG,TEST_RENAME
Mount point paths: /oracle/BHU
Services: racbhu
Type: RAC
Database is administrator managed
  bhuora01>

we have modify the disk group from TEST_RENAME to TEST using server control for production database

 bhuora01> srvctl modify database -d BHU_B -a BHU_DATA1,BHU_ARCH,BHU_RECO,BHU_OLOG,BHU_MLOG,TEST


we have modify the disk group from TEST_RENAME to TEST using server control for standby database

 karlora01> srvctl modify database -d BHU_A -a BHU_DATA1,BHU_ARCH,BHU_RECO,BHU_OLOG,BHU_MLOG,TEST

Let us check the status of diskgroup after modifying the diskgroup. We need to check the same in the standby database also.

  bhuora01> srvctl config database -d BHU_b
Database unique name: BHU_B
Database name: BHU
Oracle home: /oracle/BHU/112_64
Oracle user: oracle
Spfile: +BHU_DATA1/BHU_B/PARAMETERFILE/spfile.261.764676511
Domain:
Start options: open
Stop options: immediate
Database role: PRIMARY
Management policy: AUTOMATIC
Server pools: BHU_B
Database instances: BHU_1,BHU_2
Disk Groups: BHU_DATA1,BHU_ARCH,BHU_RECO,BHU_OLOG,BHU_MLOG,TEST
Mount point paths: /oracle/BHU
Services: racbhu
Type: RAC
Database is administrator managed
oracle  bhuora01>


STEP#11
Start only one instance on the primary database to the mount stage. We have to rename the datafile which is located in the old diskgroup to the new diskgroup.
On bhuora01(primary database)

 bhuora01> sqlplus / as sysdba

SQL*Plus: Release 11.2.0.2.0 Production on Mon Dec 19 15:39:57 2011

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

Connected to an idle instance.

SQL> startup mount
ORA-32004: obsolete or deprecated parameter(s) specified for RDBMS instance
ORACLE instance started.

Total System Global Area 1.4431E+10 bytes
Fixed Size                  2240272 bytes
Variable Size            3892314352 bytes
Database Buffers         1.0503E+10 bytes
Redo Buffers               34148352 bytes
Database mounted.
SQL>

SQL> select name from v$datafile where name like '+TEST%';

NAME
--------------------------------------------------------------------------------
+TEST_RENAME/BHU_b/datafile/test.256.769969469

-- RENAMING THE DATAFILE TO THE NEW DISKGROUP

SQL> alter database rename file  ' +TEST_RENAME/BHU_b/datafile/test.256.769969469’ to ‘+TEST/BHU_b/datafile/test.256.769969469’;
Database altered.

-- AFTER THE RENAME
SQL>  select name from v$datafile where name like '+TEST%';

NAME
--------------------------------------------------------------------------------
+TEST/BHU_b/datafile/test.256.769969469

SQL> alter database open;

Database altered.

STEP#12
Start only one instance on the standby database to the mount stage. We have to rename the datafile which is located in the old diskgroup to the new diskgroup.

On karlora01(standby database)

 karlora01> sqlplus / as sysdba

SQL*Plus: Release 11.2.0.2.0 Production on Mon Dec 19 15:39:57 2011

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

Connected to an idle instance.

SQL> startup mount
ORA-32004: obsolete or deprecated parameter(s) specified for RDBMS instance
ORACLE instance started.

Total System Global Area 1.4431E+10 bytes
Fixed Size                  2240272 bytes
Variable Size            3892314352 bytes
Database Buffers         1.0503E+10 bytes
Redo Buffers               34148352 bytes
Database mounted.
SQL>

SQL> select name from v$datafile where name like '+TEST%';

NAME
--------------------------------------------------------------------------------
+TEST_RENAME/BHU_a/datafile/test.256.769969471

Note: we cant rename a datafile in the standby database when the “standby_file_management” is in the AUTO mode

SQL> show parameter standby

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
standby_archive_dest                 string
standby_file_management              string      AUTO

SQL> alter system set standby_file_management='MANUAL' scope=both sid='*';

System altered.

SQL> alter database rename file '+TEST_RENAME/BHU_a/datafile/test.256.769969471' to '+TEST/BHU_a/datafile/test.256.769969471';

Database altered.

SQL> alter system set standby_file_management='AUTO'  scope=both sid='*';

System altered.

STEP#13

Stop the single instance on both the side (primary & standby database) and start the database on primary & standby database (all nodes)        
-- Shutdown single instance in the primary database and start the entire database
 bhuora01> srvctl stop database -d BHU_b

-- starting the entire database
 bhuora01> srvctl stop database -d BHU_b

-- finding the status of the database after shutdown

 bhuora01> srvctl status database -d BHU_b
Instance BHU_1 is  running on node bhuora01
Instance BHU_2 is  running on node bhuora02

-- shutdown single instance in the standby database and start the entire database in to mount stage or read only mode

 karlora01>  srvctl stop database -d BHU_a

-- starting the entire database

 karlora01>  srvctl start database -d BHU_a

-- finding the status of the database after shutdown

 karlora01>  srvctl status database -d BHU_a
Instance BHU_1 is running on node karlora01
Instance BHU_2 is running on node karlora02

     STEP#14

Now let us check, whether primary & standby database is sync

DGMGRL> show configuration;

Configuration - DG_BHU

  Protection Mode: MaxAvailability
  Databases:
    BHU_B - Primary database
    BHU_A - Physical standby database

Fast-Start Failover: DISABLED

Configuration Status:
SUCCESS

DGMGRL>

STEP#15
We have removed the old DISKGROUP name from the cluster registry.

On primary database(node#1). Set cluster profile before running the query

  bhuora01> crsctl delete resource ora.TEST_RENAME.dg

  bhuora01> crsctl  stat res ora.TEST_RENAME.dg
CRS-2613: Could not find resource 'ora.TEST_RENAME.dg'.


On standby database(node#1)

  karlora01> crsctl delete resource ora.TEST_RENAME.dg

  bhuora01> crsctl  stat res ora.TEST_RENAME.dg
CRS-2613: Could not find resource 'ora.TEST_RENAME.dg'.

Note: if the resource is not deleted and if the resource is not used by anyone then we can use the force option.

  karlora01> crsctl delete resource ora.TEST_RENAME.dg -f