Oracle DBA experience

Monday, January 23, 2012

ACFS Snapshot setup & views

In this below note, I have configured the ADVM & ACFS mount point already. So I am creating snapshot for the existing ACFS mount points. If you don’t have ACFS mount points then we have to create it first.

What is ACFS Snapshot

Oracle ACFS snapshot is an online, read-only or read-write, point in time copy of an Oracle ACFS file system.
The snapshot copy is space-efficient and uses Copy-On-Write functionality. Oracle ACFS snapshots are immediately available for use after they are created. The snapshots are created in the .ACFS/snaps/ directory of the file system. They are always online while the file system is mounted. Oracle ACFS read-write snapshots enable fast creation of a snapshot image that can be both read and written without impacting the state of the Oracle ACFS file system

1) We can use the snapshot before installing & removing patches for the $ORACLE_HOME or any application which are in production environment. We will have original copy of data in the snapshot which we can move it at any time.

2) Testing of new versions of application software on production file data reflected in the read-write snapshot image without modifying the original production file system

3) Running test scenarios on a real data set without modifying the original production file system

4) Oracle ACFS snapshot can support the online recovery of files inadvertently modified or deleted from a file system.

View detail information on each file system

To Collect more information about ACFS, you will get it through the below view

oracrs +ASM1 > /sbin/acfsutil info fs –h

It has penalty of option available on this view

To collect the statistics for the ACFS Mount point

oracrs +ASM1 > /sbin/acfsutil info fs /oracle/dbdump -s

amount of change since mount: 0.11 MB

average rate of change since mount: 0 KB/s

CREATE ACFS SNAPSHOTS

TO create a snapshot in read-write or read-only mode

oracrs +ASM1> /sbin/acfsutil snap create SNAP_PREUPGRADE /oracle/dbadmin

acfsutil snap create: Snapshot operation is complete.

VIEW ACFS SNAPSHOTS

To know about the snapshot for the particular mount in the ACFS, below command will work from 11.2.0.3 onwards

$ /sbin/acfsutil snap info /oracle/dbdump

To view current snapshot files. All the snapshot will be located under the mount point of the ACFS file system .ACFS/snaps folder

oracrs +ASM1 > cd /oracle/dbadmin/

oracrs +ASM1 > pwd

/oracle/dbadmin/.ACFS/snaps

oracrs +ASM1 > ls -lrt

total 4

drwxrwxr-x 22 oracle oinstall 4096 Jan 20 11:42 SNAP_PREUPGRADE

oracrs +ASM1 >

To view the specific mount point information and it gives us total no of snapshot available for the current mount point

oracrs +ASM1 > /sbin/acfsutil info fs /oracle/dbadmin

/oracle/dbadmin

ACFS Version: 11.2.0.2.0

flags: MountPoint,Available

mount time: Thu Jan 12 09:58:51 2012

volumes: 1

total size: 20669530112

total free: 20450643968

primary volume: /dev/asm/acfs_vol1-98

label:

flags: Primary,Available,ADVM

on-disk version: 40.0

allocation unit: 4096

major, minor: 252, 50177

size: 20669530112

free: 20450643968

ADVM diskgroup ACFS

ADVM resize increment: 268435456

ADVM redundancy: unprotected

ADVM stripe columns: 4

ADVM stripe width: 131072

number of snapshots: 1

snapshot space usage: 3198976

To view the snapshot information through sqlplus

SQL> SELECT SUBSTR(fs_name,1,34) FILESYSTEM,SUBSTR(snap_name,1,28) SNAPSHOT, CREATE_TIME TIME FROM V$ASM_ACFSSNAPSHOTS;

FILESYSTEM SNAPSHOT TIME

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

/oracle/dbadmin SNAP_PREUPGRADE 20-JAN-2012

DELETE ACFS SNAPSHOTS

oracrs +ASM1 > /sbin/acfsutil snap delete SNAP_PREUPGRADE /oracle/dbadmin

acfsutil snap delete: Snapshot operation is complete.

To identify the file system which are allocated through the ACFS

SQL> col FS_NAME format a15

SQL> select * from V$ASM_FILESYSTEM;

FS_NAME AVAILABLE_T BLOCK_SIZE STATE CORRU NUM_VOL TOTAL_SIZE

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

TOTAL_FREE TOTAL_SNAP_SPACE_USAGE

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

/oracle/dbadmin 12-JAN-2012 4 AVAILABLE FALSE 1 19712

19492.9375 0

To know about the mount point and volumes allocated in the ACFS

SQL> col FS_NAME format a15

SQL> col VOL_DEVICE format a15

SQL> col VOL_LABEL format a15

SQL> select * from v$asm_acfsvolumes;

FS_NAME VOL_DEVICE VOL_LABEL PRIMA TOTAL_MB FREE_MB

--------------- --------------- --------------- ----- ---------- ----/oracle/dbadmin /dev/asm/acfs_v TRUE 19712 19492.9375

ol1-98

TO understand more about the current volume statistics for ACFS volumes

SQL> col VOLUME_NAME format a15

SQL> select * from V$ASM_VOLUME_STAT;

GROUP_NUMBER VOLUME_NAME COMPOUND_INDEX VOLUME_NUMBER READS WRITES

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

READ_ERRS WRITE_ERRS READ_TIME WRITE_TIME BYTES_READ BYTES_WRITTEN

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

1 ACFS_VOL1 16777217 1 23342 5887

0 0 12.073 18.228 34204672 5329920

SQL> col VOLUME_NAME format a15

SQL> col USAGE format a15

SQL> col MOUNTPATH format a15

SQL> select VOLUME_NAME,SIZE_MB,STRIPE_COLUMNS,USAGE,MOUNTPATH from v$asm_volume;

VOLUME_NAME SIZE_MB STRIPE_COLUMNS USAGE MOUNTPATH

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

ACFS_VOL1 19712 4 ACFS /oracle/dbadmin

In side the ASMCMD tool, we can find about the volumes through the below commands

volcreate

voldelete

voldisable

volenable

volinfo

volresize

volset

volstat

Wednesday, January 18, 2012

restored spfile in DB_UNKNOWN Directory

In the below scenario, I am placing the spfile correctly after the RMAN restored the spfile in the DB_UNKNOWN directory in the diskgroup.

This is oracle bug( Bug 5370663: RMAN RESTORES SPFILE IN ASM TO DB_UNKNOWN’)

Note: 1) we have to enable the autobackup mode in the RMAN to restore the spfile from the backup. If autobackup mode is not enabled then we can’t restore the spfile from backup

2) I have started with one instance on the RAC database. Till I completed the restore, I will be performing all the activity from the same node. Keep the second instance down.

3) Remove the init_<INSTANCE_NAME>.ora parameter file from the $ORACLE_HOME/dbs folder. This file will contain the old spfile details. When you start the database with the srvctl command, this file will be created automatically by the oracle agent in the $ORACLE_HOME/dbs folder.

We need to identify the DB_NAME, DBID & DB_UNIQUE_NAME details for recovering the database using RMAN

To start the database in the force mode, only DB_NAME is sufficient

oracle BHU_1 bhuora01> cat initBHU.ora

*.db_name='BHU'

*.db_unique_name='BHU_A'

Start the instance with the force option to the nomount stage

oracle BHU_1 bhuora01> sqlplus / as sysdba

SQL*Plus: Release 11.2.0.2.0 Production on Tue Jan 17 15:28:28 2012

Connected to an idle instance.

SQL> startup nomount force pfile='/home/oracle/initBHU.ora';

ORACLE instance started.

Total System Global Area 304861184 bytes

Fixed Size 2225872 bytes

Variable Size 159385904 bytes

Database Buffers 134217728 bytes

Redo Buffers 9031680 bytes

SQL> exit

Note: 1) you can identify the DBID from the level 0 or 1 backup log file

2) Some time oracle writes the DBID in the alert log file

We have to connect to the database using rman and provide the DBID for the database. If you have the recovery catalog then there is no need to specify the DBID.

oracle BHU_1 bhuora01> rman target /

Recovery Manager: Release 11.2.0.2.0 - Production on Tue Jan 17 15:28:59 2012

connected to target database: BHU (not mounted)

RMAN> set dbid=263762951

executing command: SET DBID

RMAN> run {

ALLOCATE CHANNEL 'dev_1' type 'SBT_TAPE';

restore spfile to '+BHU_DATA1' from autobackup;

}

2> 3> 4>

using target database control file instead of recovery catalog

allocated channel: dev_1

channel dev_1: SID=175 device type=SBT_TAPE

channel dev_1: Data Protector A.06.11/PHSS_41802/PHSS_41803/DPSOL_00435/DPLNX_

Starting restore at 17-JAN-2012

channel dev_1: looking for AUTOBACKUP on day: 20120117

channel dev_1: AUTOBACKUP found: c-263762951-20120117-00

channel dev_1: restoring spfile from AUTOBACKUP c-263762951-20120117-00

channel dev_1: SPFILE restore from AUTOBACKUP complete

Finished restore at 17-JAN-2012

released channel: dev_1

Spfile has been restored to the DB_UNKNOWN directory under the diskgroup which we specified in the restore

ASMCMD [+BHU_DATA1] > ls -lt

Type Redund Striped Time Sys Name

Y BHU_A/

Y DB_UNKNOWN/

ASMCMD [+BHU_DATA1] > cd D*

ASMCMD [+BHU_DATA1/DB_UNKNOWN] > cd p*

ASMCMD [+BHU_DATA1/DB_UNKNOWN/PARAMETERFILE] > ls -lt

Type Redund Striped Time Sys Name

PARAMETERFILE UNPROT COARSE JAN 17 15:00:00 Y SPFILE.271.772818033

Once the spfile is restored. We have create a new pfile from the restore spfile from the ASM diskgroup

oracle BHU_1 bhuora01> sqlplus / as sysdba

SQL*Plus: Release 11.2.0.2.0 Production on Tue Jan 17 15:42:08 2012

Connected to:

Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production

With the Partitioning, Real Application Clusters, OLAP, Data Mining

and Real Application Testing options

SQL> create pfile='/home/oracle/BHU_afterbackup.ora' from spfile='+BHU_DATA1/DB_UNKNOWN/PARAMETERFILE/SPFILE.271.772818033';

File created.

Now, I am removing the DB_UNKNOWN directory from the diskgroup

Don’t remove before create a pfile from it.

ASMCMD [+BHU_DATA1] > ls -lt

Type Redund Striped Time Sys Name

Y BHU_A/

Y DB_UNKNOWN/

ASMCMD [+BHU_DATA1] > ls

DB_UNKNOWN/

BHU_A/

ASMCMD [+BHU_DATA1] > rm -rf db*

We are stopping the database and Re-start the database with the newly created pfile.

oracle BHU_1 bhuora01> sqlplus / as sysdba

SQL*Plus: Release 11.2.0.2.0 Production on Tue Jan 17 15:43:30 2012

Connected to:

Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production

With the Partitioning, Real Application Clusters, OLAP, Data Mining

and Real Application Testing options

SQL> shutdown immediate;

ORA-01507: database not mounted

ORACLE instance shut down.

SQL> startup nomount pfile='/home/oracle/zs1_afterbackup.ora';

ORA-32006: REMOTE_OS_AUTHENT initialization parameter has been deprecated

ORA-32006: STANDBY_ARCHIVE_DEST initialization parameter has been deprecated

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

SQL> create spfile='+BHU_DATA1' from pfile='/home/oracle/bhu_afterbackup.ora';

File created.

SQL>

We could see that the spfile has been created in the right directory structure.

ASMCMD [+BHU_DATA1/ZS2_A/PARAMETERFILE] > ls

spfile.268.772818375

+BHU_DATA1/ZS2_A/PARAMETERFILE/spfile.268.772818375

Now we are changing the configuration details in the server control

oracle BHU_1 bhuora01> srvctl modify database -d BHU_A -p +BHU_DATA1/BHU_A/PARAMETERFILE/spfile.268.772818375

oracle BHU_1 bhuora01> sqlplus / as sysdba

SQL*Plus: Release 11.2.0.2.0 Production on Tue Jan 17 15:48:07 2012

Connected to:

Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production

With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,

Data Mining and Real Application Testing options

SQL> shutdown immediate;

ORA-01507: database not mounted

ORACLE instance shut down.

If you start the database with sqlplus, Oracle can’t able to identify the spfile or pfile in the $ORACLE_HOME/dbs directory.

So we have to start the database with the srvctl command, which has the right spfile details.

If you start the database with the srvctl command, oracle creates pfile for the respective instance on the database.

SQL> startup nomount

ORA-01078: failure in processing system parameters

LRM-00109: could not open parameter file '/oracle/BHU/112_64/dbs/initBHU_1.ora'

SQL> exit

oracle BHU_1 bhuora01> srvctl start database -d BHU_A -o nomount

oracle BHU_1 bhuora01> sqlplus / as sysdba

SQL> show parameter spfile

NAME TYPE VALUE

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

spfile string +BHU_DATA1/BHU_a/parameterfile

/spfile.268.772818375

Saturday, January 14, 2012

Difference between CANDIDATE & PROVISIONED in ASM DISK

Disks that were discovered but that have not yet been assigned to a disk group have a header status of either CANDIDATE or PROVISIONED.

CANDIDATE

Disk is not part of a disk group and may be added to a disk group with the ALTER DISKGROUP statement
PROVISIONED
Disk is not part of a disk group and may be added to a disk group with the ALTER DISKGROUP statement. The PROVISIONED header status is different from the CANDIDATE header status in that PROVISIONED implies that an additional platform-specific action has been taken by an administrator to make the disk available for Automatic Storage Management.

For example, on Windows, the administrator used asmtool or asmtoolg to stamp the disk with a header, or on Linux, the administrator used ASMLib to prepare the disk for ASM.

Below are the HEADER_STATUS in the v$ASM_DISK. I have taken below status from 11gR2.

· UNKNOWN - Automatic Storage Management disk header has not been read

· CANDIDATE - Disk is not part of a disk group and may be added to a disk group with the ALTER DISKGROUP statement

· INCOMPATIBLE - Version number in the disk header is not compatible with the Automatic Storage Management software version.

· PROVISIONED - Disk is not part of a disk group and may be added to a disk group with the ALTER DISKGROUP statement. The PROVISIONED header status is different from the CANDIDATE header status in that PROVISIONED implies that an additional platform-specific action has been taken by an administrator to make the disk available for Automatic Storage Management.

· MEMBER - Disk is a member of an existing disk group. No attempt should be made to add the disk to a different disk group. The

ALTER
DISKGROUP

statement will reject such an addition unless overridden with the FORCE option

· FORMER - Disk was once part of a disk group but has been dropped cleanly from the group. It may be added to a new disk group with the ALTER DISKGROUP statement.

· CONFLICT - Automatic Storage Management disk was not mounted due to a conflict

· FOREIGN - Disk contains data created by an Oracle product other than ASM. This includes datafiles, logfiles, and OCR disks.

When adding a disk, the FORCE option must be used if Oracle ASM

recognizes that the disk was managed by Oracle. Such a disk appears

in the V$ASM_DISK view with a status of FOREIGN.

Friday, January 13, 2012

.patch_storage in RDBMS_HOME & GI_HOME

Can we delete .patch_storage in RDBMS_HOME & GI_HOME directoryy

Yes, Please follow the below document. read it very carefully before clearing the files

Is it necessary to keep the $ORACLE_HOME/.patch_storage directory after successful installation of a patch?

Yes, It is necessary to keep the $ORACLE_HOME/.patch_storage directory even after successful installation of a patch.

Reason for ORACLE storing the patch binaries & patch information on .patch_storage directory

* When you apply an interim patch to an Oracle home, OPatch stores the patch information in $ORACLE_HOME/.patch_storage directory. Inside this directory, there are separate directories created for each patch applied to the Oracle home. Interim patches are bug fixes that are made available to customers in response to specific bugs. CPU (Critical Patch Update) is also a kind of. The latest CPU will rollback the previously applied CPU as CPU is cumulative. So this rollback information is from the .patch_storage directory.

* You may come across a bug conflict and might want to remove the conflicting patch during the patch installation. This process is known as patch rollback. During patch installation, OPatch saves copies of all the files that were replaced by the new patch before the new versions of these files are loaded and stores it in $ORACLE_HOME/.patch_storage/patch ID/. These saved files are called rollback files and are the key to making patch rollback possible.

To Clean up the .patch_storage directory, oracle has come up with option called 

"opatch util cleanup"  option, please read the below document.

How To Avoid Disk Full Issues Because OPatch Backups Take Big Amount Of Disk Space. [ID 550522.1]

Thursday, January 12, 2012

How to Upgrade RMAN Catalog in 11gR2 after applying a PSU Patch

This post discuss about how to upgrade RMAN catalog in 11gR2 after applying a PSU Patch

Note: This is not the database upgrade, this post provides information on the RMAN Catalog upgrade for an database after a PSU patch has been applied in the database server.

“The recovery catalog schema version must be greater than or equal to the RMAN client version”.

For your 11.2 database, if you are running RMAN from the same ORACLE_HOME, your RMAN Client is 11.2. The catalog schema must be 11.2. Although it can be in an 11.1 database, it would have been preferable to have it in 11.2. The better course is to upgrade the RMAN Catalog database to the highest of all the target databases and the Catalog schema to the highest RMAN client.

-- Connect to the target database on the server

oracle > rman target /

Recovery Manager: Release 11.2.0.2.0 - Production on Thu Jan 12 15:03:42 2012

connected to target database: BHUVAN (DBID=3982664177)

-- Connect to the Recover catalog database

RMAN> connect catalog rman/xxxxx@RMAN_CATALOG

connected to recovery catalog database

-- upgrade the catalog by connecting to the target database and catalog database.

RMAN> UPGRADE CATALOG;

recovery catalog owner is RMAN

enter UPGRADE CATALOG command again to confirm catalog upgrade

RMAN> UPGRADE CATALOG;

recovery catalog upgraded to version 11.02.00.02

DBMS_RCVMAN package upgraded to version 11.02.00.02

DBMS_RCVCAT package upgraded to version 11.02.00.02

RMAN>

TO DETERMINE THE CURRENT RELEASE OF THE CATALOG SCHEMA, YOU MUST RUN A SQL QUERY.

oracle> sqlplus rman/xxxx@RMAN_CATALOG

SQL*Plus: Release 11.2.0.2.0 Production on Thu Jan 12 15:19:51 2012

Connected to:

Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production

With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,

Data Mining and Real Application Testing options

SQL> SELECT * FROM rcver;

VERSION

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

11.02.00.02

Note:

1) If multiple versions are listed, then the last row is the current version, and the rows before it are prior versions.

2) For releases 11.2 and later, the last two digits in the rcver output indicate patch level. For earlier releases, they are always zeros.

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

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

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