Increasing ASM memory parameter

Note: if you are huge pages then automatic memory management is not support. please check that before using AMM.


Today I have increase the ASM memory on two environment. I have noticed huge difference in the ASM behaviour.

#1 (bhuora002/  bhuora003).

I have increase the ASM memory through command prompt.

Alter system set memory_max_target=2G scope=spfile sid=’*’;

Alter system set memory_target=2G scope=spfile sid=’*’;

Behaviour: When I try to bounce the GRID, ASM start without any issues.

#2 ( bhuora004/  bhuora005).

I have increase the ASM memory through command prompt.

Alter system set sga_max_target=2G scope=spfile sid=’*’;

Alter system set sga_target=2G scope=spfile sid=’*’;

Behaviour: When I try to bounce the GRID, ASM is not start and it has thrown error message. I taken the backup of spfile and change the values in the init file.  Once I have modified the values, I have used the same pfile to start the ASM instance(mount stage). Once it is mount, I have created the spfile from the pfile which I have used.This spfile set in the all the places.  Then I have stopped the ASM instance & bounced the GRID.

 So please use the memory_target & memory_max_target parameters while trying to increase the ASM memory parameters.

ORA-00376: file 6 cannot be read at this time

When I try to start a standby database in the read only mode, I have been thrown the below error message

oracle BHU_1> srvctl start database -d BHU_B

PRCR-1079 : Failed to start resource ora.BHU_b.db

CRS-5017: The resource action "ora.BHU_b.db start" encountered the following error:

ORA-01092: ORACLE instance terminated. Disconnection forced

ORA-00604: error occurred at recursive SQL level 1

ORA-00376: file 6 cannot be read at this time

ORA-01110: data file 6: '+BHU_B_SYSTEM/BHU_b/datafile/undo.264.789559193'

Process ID: 9165

Session ID: 3497 Serial number: 1

. For details refer to "(:CLSN00107:)" in "/oracle/GRID/11203/log/bhurac01/agent/crsd/oraagent_oracle/oraagent_oracle.log"

It is an undo tablespace (datafile) which is throwing the error and I have checked the datafile it is in the recovery mode.

select file#,name,status,enabled from v$datafile where file#=6;

6 +BHU_B_SYSTEM/BHU_b/datafile/undo.264.789559193                                                                                                   RECOVER READ WRITE

SO I HAVE FOLLOWED BELOW PROCEDURE TO OVERCOME THIS PROBLEM

#1

1 A)  Stop the apply process in the standby database

DGMGRL> edit database 'BHU_B' set state='APPLY-OFF';

Succeeded.

2 B)  It is RAC Database, kept only one instance in mount stage and other instance are in the offline mode(shutdown)

3

  C)  It is standby database (undo tablespace-datafile). So I am not able to do offline

#2 – Take a online backup of datafile through RMAN

RMAN> copy datafile 6 to '+BHU_B_DATA1';

Starting backup at 25-JUL-12

using target database control file instead of recovery catalog

allocated channel: ORA_DISK_1

channel ORA_DISK_1: SID=1842 instance=BHU_1 device type=DISK

channel ORA_DISK_1: starting datafile copy

input datafile file number=00006 name=+BHU_B_SYSTEM/BHU_b/datafile/undo.264.789559193

output file name=+BHU_B_DATA1/BHU_b/datafile/undo.301.789569089 tag=TAG20120725T124447 RECID=69 STAMP=789569388

channel ORA_DISK_1: datafile copy complete, elapsed time: 00:05:05

Finished backup at 25-JUL-12

Starting Control File and SPFILE Autobackup at 25-JUL-12

piece handle=/oracle/BHU/11203/dbs/c-72629545-20120725-01 comment=NONE

Finished Control File and SPFILE Autobackup at 25-JUL-12

#3 I doing a rename through RMAN itself, there is no need of using the RENAME command in the sqlplus

RMAN> switch datafile 6 to copy;

datafile 6 switched to datafile copy "+BHU_B_DATA1/BHU_b/datafile/undo.301.789569089"

RMAN> exit

#4 when I check the status of the datafile, it looks in the RECOVER MODE. So I cant open the database in the READ-ONLY MODE.

SQL> select name,status from v$datafile where file#=6;

NAME                                                 STATUS

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

+BHU_B_DATA1/BHU_b/datafile/undo.301.789569089  RECOVER

#5 Started the Recover through DG Broker

DGMGRL> edit database 'BHU_B' set state='APPLY-ON';

Succeeded.

#6 Monitor the apply Lag

I could see that the system is using the old archive log to recover the datafile.

Once the recovery is completed, you can open the database in the read only mode.

I have see the status of the datafile; it is in the online mode

SQL> select name,status from v$datafile where file#=6;

NAME                                  STATUS

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

+BHU_B_DATA1/BHU_b/datafile/undo.301.789569089 ONLINE

change the ASM spfile in 11gR2

How to change the ASM spfile in 11gR2

Logical steps to change the ASM spfile

1. Create intermediate pfile from the current spfile or pfile
2. Create spfile in a new disk group from the intermediate pfile
3. Restart the HA stack to verify that ASM starts up fine with moved spfile
4. Remove the original spfile

You can verify whether you are using the correct spfile for the ASM in the cluster environment.

Multiple way to check the spfile details in 11gR2

#1

oracle +ASM1> asmcmd spget

+OCR_VOTE/bhurac1a/asmparameterfile/registry.253.789386957

#2

oracle +ASM1 > gpnptool get

<?xml version="1.0" encoding="UTF-8"?><gpnp:GPnP-Profile Version="1.0" xmlns="http://www.grid-pnp.org/2005/11/gpnp-profile" xmlns:gpnp="http://www.grid-pnp.org/2005/11/gpnp-profile" xmlns:orcl="http://www.oracle.com/gpnp/2005/11/gpnp-profile" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:schemaLocation="http://www.grid-pnp.org/2005/11/gpnp-profile gpnp-profile.xsd" ProfileSequence="11" ClusterUId="3aebd9e0996e5f57bf5fa6a0accabc43" ClusterName="bhurac1a" PALocation=""><gpnp:Network-Profile><gpnp:HostNetwork id="gen" HostName="*"><gpnp:Network id="net2" IP="10.217.11.16" Adapter="bond4" Use="cluster_interconnect"/><gpnp:Network id="net1" Adapter="bond2" IP="10.218.8.0" Use="public"/></gpnp:HostNetwork></gpnp:Network-Profile><orcl:CSS-Profile id="css" DiscoveryString="+asm" LeaseDuration="400"/><orcl:ASM-Profile id="asm" DiscoveryString="/dev/oracleasm/disks" SPFile="+OCR_VOTE/bhurac1a/asmparameterfile/registry.253.789386957"/><ds:Signature xmlns:ds="http://www.w3.org/2000/09/xmldsig#"><ds:SignedInfo><ds:CanonicalizationMethod Algorithm="http://www.w3.org/2001/10/xml-exc-c14n#"/><ds:SignatureMethod Algorithm="http://www.w3.org/2000/09/xmldsig#rsa-sha1"/><ds:Reference URI=""><ds:Transforms><ds:Transform Algorithm="http://www.w3.org/2000/09/xmldsig#enveloped-signature"/><ds:Transform Algorithm="http://www.w3.org/2001/10/xml-exc-c14n#"> <InclusiveNamespaces xmlns="http://www.w3.org/2001/10/xml-exc-c14n#" PrefixList="gpnp orcl xsi"/></ds:Transform></ds:Transforms><ds:DigestMethod Algorithm="http://www.w3.org/2000/09/xmldsig#sha1"/><ds:DigestValue>1ZtgbzWQAuybhO0J12R2X5D+gMc=</ds:DigestValue></ds:Reference></ds:SignedInfo><ds:SignatureValue>A1Bx+lPu1QSsxGYrRJ5jVbhJ/oVkP8DcKxoCgV90gLk7/m4CztcatcRftHSDvg92z/0HzEog7AGltl0pDZZLMgA9sglWUop/GOPkzF1jxO9I7qbjQjeqLOoS79+XXV9M9LQ8KtosNvGE50VdE2tswdWc2IDJ8AelhL9wNCabBwg=</ds:SignatureValue></ds:Signature></gpnp:GPnP-Profile>

Success.

#3

oracle +ASM1 > sqlplus / as sysdba

SQL*Plus: Release 11.2.0.3.0 Production on Mon Jul 23 12:22:00 2012

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

Connected to:

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

With the Real Application Clusters and Automatic Storage Management options

SQL> show parameter spfile

NAME                                 TYPE                                                  VALUE

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

spfile                               string  +OCR_VOTE/bhurac1a/asmparameterfile/registry.253.789386957

If you want to create a spfile from the existing spfile with some modification then you need to take backup from existing one.

SQL> create pfile='/home/oracle/init+ASM.ora' from spfile;

In the below case, my spfile is located in the environment and it is NOT picked up by the cluster during the startup.

So I am creating the pfile from spfile which is located in the diskgroup.

SQL> create pfile='/home/oracle/init+ASM.ora' from spfile='+OCR_VOTE/bhurac1a/ASMPARAMETERFILE/REGISTRY.253.757697363';

If you need modify any parameter in the ASM parameter, we can modify it.

Create a new spfile from the pfile

SQL> create spfile=’+OCR_VOTE’ from pfile='/home/oracle/init+ASM.ora';

Once the spfile is create, if you check spfile location in the cluster environment. it will show the new file.

You can verify using the below command. Note: THIS SPFILE WILL BE INCLUDING TO ALL THE CLUSTER NODES IN THE ENVIRONMENT.

#1 set the ASM environment values

$ asmcmd spget

(Or)

$ gpnptool get

Restart the cluster and you could see the cluster is picking up the new spfile.