Tuesday, July 31, 2012

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.

Wednesday, July 25, 2012

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

1 A)  Stop the apply process in the standby database

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

2 B)  It is RAC Database, kept only one instance in mount stage and other instance are in the offline mode(shutdown)
  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';

#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

Monday, July 23, 2012

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

oracle +ASM1> asmcmd spget

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="" Adapter="bond4" Use="cluster_interconnect"/><gpnp:Network id="net1" Adapter="bond2" IP="" 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>

oracle +ASM1 > sqlplus / as sysdba
SQL*Plus: Release 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 - 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.

#1 set the ASM environment values
$ asmcmd spget
$ gpnptool get

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