Oracle 11gR2 ASM Spfile is in Disk Group ( How ASM starts then?)

I recently got very intresting confusion ASM Spfile is stored in ASM diskgroup in 11gR2 in first default diskgroup created during GI installtion and in order to start ASM we need spfile.
So if my ASM is down, how do i start it as my Spfile lies in ASM diskgroup.

Here lies the answer to this question:

$ asmcmd spget
+DEV_DATA01/asm/asmparameterfile/registry.253.754437197

 

While Clusterware (GI) Startup GPnP ( Grid plug and play ) profile provides the ASM discovery string

$gpnptool getpval -asm_dis

ORCL:*

These string is used to scan the device headers which contain the ASM spfile i.e. (kfdhdb.spfflg=1).

$ asmcmd lsdsk -G RDPDEV_DATA01
Path
ORCL:ASMSDB12
ORCL:ASMSDB2
ORCL:ASMSDB3
ORCL:ASMSDB5

 
Let's scan above disk devices :

$ kfed read ORCL:ASMSDB12 | grep -E 'spf|ausize'
kfdhdb.ausize:                  1048576 ; 0x0bc: 0x00100000
kfdhdb.spfile:                        0 ; 0x0f4: 0x00000000
kfdhdb.spfflg:                        0 ; 0x0f8: 0x00000000

$ kfed read ASMSDB2  | grep -E 'spf|ausize'

$  kfed read ORCL:ASMSDB3  | grep -E 'spf|ausize'
kfdhdb.ausize:                  1048576 ; 0x0bc: 0x00100000
kfdhdb.spfile:                        0 ; 0x0f4: 0x00000000
kfdhdb.spfflg:                        0 ; 0x0f8: 0x00000000

$  kfed read ORCL:ASMSDB5  | grep -E 'spf|ausize'
kfdhdb.ausize:                  1048576 ; 0x0bc: 0x00100000
kfdhdb.spfile:                       19 ; 0x0f4: 0x00000013
kfdhdb.spfflg:                        1 ; 0x0f8: 0x0000000   --> here is your spfile on this disk

Final Story:

To read the ASM spfile during the ASM instance startup, it is not necessary to open the disk group. All information necessary to access the data is stored in the device's header. By the way, the same technique is used to access the Clusterware voting files which are also stored in an ASM disk group. In this case, Clusterware does not need a running ASM instance to access the cluster voting files:
grid@iudb007:~/ [+ASM5] kfed read ORCL:ASMSDB5 | grep vf
kfdhdb.vfstart:                     256 ; 0x0ec: 0x00000100 <- START offset of the voting file
kfdhdb.vfend:                       288 ; 0x0f0: 0x00000120 <- END offset of the voting file

OEM Agent : The agentTZRegion value not in Agreement

Due to DST  changes some days back we see couple of OEM Agents failed to start with below mentioned errors:

oracle@machine[TEST01]> ./emctl start agent
Starting agent ......... failed.
The agentTZRegion value in /orcl/app/oracle/agent11g/sysman/config/emd.properties is not in agreement with what agent thinks it should be.Please verify your environment to make sure that TZ setting has not changed since the last start of the agent.
If you modified the timezone setting in the environment, please stop the agent and exectute 'emctl resetTZ agent' and also execute the script mgmt_target.set_agent_tzrgn(<agent_name>, <new_tz_rgn>) to get the value propagated to repository.
Consult the log files in: /orcl/app/oracle/agent11g/sysman/log

OS level time as changed but it's not changed in Oracle Agent binaries. We have manaully fix it using below menthod:

1. Set the Correct Env variable to TZ parameter

oracle@machine[TEST01]> echo $TZ
Europe/Moscow
export TZ=Etc/GMT-4

2. Reset the agent time zone using emctl resetTZ agent

oracle@machine[TEST01]> ./emctl resetTZ agent

Oracle Enterprise Manager 11g Release 1 Grid Control 11.1.0.1.0
Copyright (c) 1996, 2010 Oracle Corporation.  All rights reserved.
Updating /orcl/app/oracle/agent11g/sysman/config/emd.properties...
Successfully updated /orcl/app/oracle/agent11g/sysman/config/emd.properties.

Login as the em repository user and run the  script: ( OEM Grid )
exec mgmt_target.set_agent_tzrgn('machinename2:1830','Etc/GMT-4')
and commit the changes

This can be done for example by logging into sqlplus and doing
SQL> exec mgmt_target.set_agent_tzrgn('machinename:1830','Etc/GMT-4')
SQL> commit

3. Agent started:

oracle@machine[TEST01]> ./emctl start agent
Oracle Enterprise Manager 11g Release 1 Grid Control 11.1.0.1.0
Copyright (c) 1996, 2010 Oracle Corporation.  All rights reserved.
Starting agent ..................... started.

Reference Metalink ID note:
DST Compliance for Grid Control OMS, Repository and Agents: Checks and Updates [ID 457792.1]

ORA-12801: error signaled in parallel query server P001 ORA-01722: invalid number

Today I came accross with above two errors. These are two sets of errors

1. Parallel Query Failing
2. Invalid Comparision Operation.

Query Failing:

SELECT COUNT(*) FROM SALES_MASTER_HISTORY WHERE TREASURYNUMBER < 20000;

ORA-12801: error signaled in parallel query server P001

ORA-01722: invalid number

When i check degree on this table it was set to 1 so no problem found there but degree with above table indexes was set to 4. I changed the degree to 1 to sort first issue as below:

SQL> SQL> select owner,table_name,degree from dba_tables where table_name in ('SALES_MASTER_HISTORY');

OWNER                          TABLE_NAME                     DEGREE
------------------------------ ------------------------------ ----------
CAP                          SALES_MASTER_HISTORY                   1

SQL> select index_name,degree from dba_indexes where table_name in ('SALES_MASTER_HISTORY');

INDEX_NAME                     DEGREE
------------------------------ ----------------------------------------
IDX_TMH_NETTEDBOOKINGREF       4
IDX_VALUEDATE                  4
PK_TRADES_MASTER_HISTORY       4
IDX_TMH_CORESYSREF             4
IDX_TMH_TRUNC_BOOKEDDATE_IDX   4
IDX_TMH_BRANCH_CODE            4
IDX_TMH_CUSTOMER_NUMBER        4
IDX_TMH_BROKERCODE             4
IDX_BOOKEDDATE                 4
IDX_TMH_CONTRACTNUMBER         4
IDX_TMH_CUSTOMER               4

11 rows selected.

SQL> alter index CAP.IDX_BOOKEDDATE  parallel 1;

Index altered.

SQL> alter index CAP.IDX_TMH_BRANCH_CODE  parallel 1;

Index altered.

SQL> alter index CAP.IDX_TMH_BROKERCODE  parallel 1;

Index altered.

SQL> alter index CAP.IDX_TMH_CONTRACTNUMBER  parallel 1;

Index altered.

SQL>  alter index CAP.IDX_TMH_CUSTOMER_NUMBER  parallel 1;

Index altered.

SQL> alter index CAP.IDX_TMH_NETTEDBOOKINGREF  parallel 1;

Index altered.

SQL>  alter index CAP.IDX_VALUEDATE  parallel 1;

Index altered.

SQL> alter index CAP.PK_TRADES_MASTER_HISTORY  parallel 1;

Index altered.

SQL> alter index CAP.IDX_TMH_TRUNC_BOOKEDDATE_IDX  parallel 1;

Index altered.

SQL> select owner,index_name,degree from dba_indexes where table_name in ('SALES_MASTER_HISTORY') order by INDEX_NAME;

OWNER                          INDEX_NAME                     DEGREE
------------------------------ ------------------------------ ----------------------------------------
CAPFX                          IDX_BOOKEDDATE                 1
CAPFX                          IDX_TMH_BRANCH_CODE            1
CAPFX                          IDX_TMH_BROKERCODE             1
CAPFX                          IDX_TMH_CONTRACTNUMBER         1
CAPFX                          IDX_TMH_CORESYSREF             1
CAPFX                          IDX_TMH_CUSTOMER               1
CAPFX                          IDX_TMH_CUSTOMER_NUMBER        1
CAPFX                          IDX_TMH_NETTEDBOOKINGREF       1
CAPFX                          IDX_TMH_TRUNC_BOOKEDDATE_IDX   1
CAPFX                          IDX_VALUEDATE                  1
CAPFX                          PK_TRADES_MASTER_HISTORY       1

After that when i execute query it was giving just :

SQL> select count(*)  FROM SALES_MASTER_HISTORY WHERE TREASURYNUMBER < 20000;
select count(*)  FROM SALES_MASTER_HISTORY WHERE TREASURYNUMBER < 20000
                                                  *
ERROR at line 1:
ORA-01722: invalid number

This is not really a database problem, here problem is TREASURYNUMBER is char(8) and when we compare it with number it causing implicit datatype conversion which causes error. This can be resolved by using 2000 value in quotes as below:

SQL> SELECT COUNT(*) FROM SALES_MASTER_HISTORY WHERE TREASURYNUMBER < '20000';

  COUNT(*)
----------
   109090909

So, this is simple solution but beneficial to  know.