are my resources in favored place?

On a 11gR1 Oracle Cluster I had an issue with some resources not in their favored place.
While investigating, I had to get the affected resources fast (at least faster than reading a huge list of resources manually and think if they match the rules).

This brought me to a small script:

crs_stat -f | awk ' BEGIN { FS="=" 
                            c=0}
/^NAME=/{n=$2}
/TYPE=/{t=$2}
/HOSTING_MEMBERS=/{hm=$2}
/PLACEMENT=/{p=$2}
/TARGET=/{g=$2}
/STATE=/{s=$2; 
  ch=s
  gsub("ONLINE on ", "",ch)
  if( (p=="favored") && !( index(hm,ch)>0 ) ) {
    printf("%-30s%-20s%-17s%-15s%-30s\n", n, " should be located on ", hm, " but has STATE: ", s) ;
    c+=1 ;
    }
} 
END { exit c }'

It prints out a line for every resource which has a favored placement, but currently is not on any HOSTING_MEMBERS.

I'm sure it's not the best awk-code, but it's fine for me.

To use it in other scripts (e.g. monitoring) I can use | wc -l to get the number of suboptimal located resources or just get the return value $?

result cache not enabled for owner SYS and SYSTEM

EDIT: the original title of this blog was "result cache not enabled in SYSTEM and SYSAUX Tablespace".
After a totally correct post from Coskan I had to rework it totally. I mixed up the problem and conclusio so much it was totally messed up.

I hope this one is the better one:

---

During some Tests with result cache in 11gR2 I discovered a small docu bug.

You can still see the bug in the 11gR1 docu:

You cannot cache results when you use the following database objects or functions in your SQL query:

• Dictionary and temporary tables

• Sequence CURRVAL and NEXTVAL pseudo columns

• SQL functions current_date, current_timestamp, local_timestamp, userenv/sys_context (with non-constant variables), sys_guid, sysdate, and sys_timestamp

• Non-deterministic PL/SQL functions

In 11gR2 it sounds slightly different: 7.6.4.2 Additional Requirements for the Result Cache:

You cannot cache results when the following objects or functions are in a query:

• Temporary tables and tables in the SYS or SYSTEM schemas

• Sequence CURRVAL and NEXTVAL pseudo columns

• SQL functions CURRENT_DATE, CURRENT_TIMESTAMP, LOCAL_TIMESTAMP, USERENV/SYS_CONTEXT (with non-constant variables), SYS_GUID, SYSDATE, andSYS_TIMESTAMP

for those of you who want to run the testcase:

create tablespace berx1 datafile '+DG' SIZE 50M;
create user berx1 identified by berx1;
grant connect to berx1;
grant unlimited tablespace to berx1;

create table sys.berx1_1 tablespace system as select * from dual;
create table sys.berx1_2 tablespace sysaux as select * from dual;
create table sys.berx1_3 tablespace berx1 as select * from dual;
create table system.berx1_4 tablespace system as select * from dual;
create table system.berx1_5 tablespace sysaux as select * from dual;
create table system.berx1_6 tablespace berx1 as select * from dual;
create table berx1.berx1_7 tablespace system as select * from dual;
create table berx1.berx1_8 tablespace sysaux as select * from dual;
create table berx1.berx1_9 tablespace berx1 as select * from dual;
grant select on sys.berx1_1 to berx1;
grant select on sys.berx1_2 to berx1;
grant select on sys.berx1_3 to berx1;
grant select on system.berx1_4 to berx1;
grant select on system.berx1_5 to berx1;
grant select on system.berx1_6 to berx1;

set autotrace on

-- run as user sys, system and berx1:

select /*+ result_cache */ * from sys.berx1_1;
select /*+ result_cache */ * from sys.berx1_2;
select /*+ result_cache */ * from sys.berx1_3;
select /*+ result_cache */ * from system.berx1_4;
select /*+ result_cache */ * from system.berx1_5;
select /*+ result_cache */ * from system.berx1_6;
select /*+ result_cache */ * from berx1.berx1_7;
select /*+ result_cache */ * from berx1.berx1_8;
select /*+ result_cache */ * from berx1.berx1_9;

my results are reflected in this matrix:

	berx1_1	berx1_2	berx1_3	berx1_4	berx1_5	berx1_6	berx1_7	berx1_8	berx1_9
sys	N	N	N	N	N	N	Y	Y	Y
system	N	N	N	N	N	N	Y	Y	Y
berx1	N	N	N	N	N	N	Y	Y	Y

So in my testcase it's not dependend of the schema (or the data dichtionary), but the tablespace.
This issue is addressed in BUG:8558309 and fixed in the latest docu :)

I know, I should never create any objects in SYS or SYSTEM schema. Especially if you want this object to be cached in result cache.

Once again thank you Coskan for showing my ridiculous error.

multiple ASM instances on one node (11gR1)

I just searched a way to circumvent the limit of 63 ASM DiskGroups in a storage system.
My first thought was to create a 2nd (or multiple) ASM instances on the same node which manage seperated DGs. This is quite easily possible: Just create a spfile for the 2nd ASM, just make sure to add *.DB_UNIQUE_NAME to avoid ORA-15150. I also recommend to use different ASM_DISKSTRINGs, to avoid multiple mount-attempts of the same DG on all ASM instances.
So I could create different DGs in different ASM-instances.
But now the troubles starts: In my test-RDBMS I could only see the DGs of the first ASM.
So I created a SR(7275580.994) at MetaLink to ask how to do it and wether or not it's supported in single instance or RAC.
To summarize the answers:

• It is possible to run multiple ASMs on a node
• One RDBMS can onlybe served by one ASM
  
• all the GUIs are not aware of multiple ASMs
• it's not supported in RAC

Even these answers are worth some forther testcases.
I have 2 ASMs: +ASM (default) and +ASM2 (2nd, with changed DB_UNIQUE_NAME and ASM_DISKSTRING) and one RDBMS. DG ASM1_DG created in +ASM, DG ASM2_DG created in +ASM2.

1. Test 1
   +ASM up
   +ASM2 up
   => only ASM1_DG1 visible in RDBMS .
2. Test 2
   +ASM down
   +ASM2 up
   => ASM2_DG1 visible
   ! create tablespace in ASM2_DG1
3. Test 3
   +ASM up
   +ASM2 up
   - restart RDBMS
   => only ASM2_DG1 visible in RDBMS
   
4. Test 4
   +ASM up
   +ASM2 up
   => DBCA only shows ASM1_DG1: 
5.  Test 5
   
   • shutdown +ASM
   • switch ASM1_DG to +ASM2 (some fiddling with ASM_DISKSTRING and symlinks)
   • mount ASM1_DG in +ASM2
   • create TS in ASM1_DG
   • shutdown RDBMS and +ASM2
   • switch ASM1_DG back to +ASM
   • startup +ASM, +ASM2 and rdbms
   • this constelation (ASM1_DG mounted on +ASM, ASM2_DG mounted on +ASM2) led to this error-message:
     
     

     ORA-01157: cannot identify/lock data file 8 - see DBWR trace file
     ORA-01110: data file 8: '+ASM1_DG/berx2/datafile/asm1_dg.256.676065339'
     ORA-17503: ksfdopn:2 Failed to open file +ASM1_DG/berx2/datafile/asm1_dg.256.67
     6065339
     ORA-15001: diskgroup "ASM1_DG" does not exist or is not mounted
     ORA-15001: diskgroup "ASM1_DG" does not exist or is not mounted
     

Test5 led me to one guess: At startup a RDBMS can potentially access all ASMs, but the first attempt to access a DG pins the RDBMS to the ASM. Unfortunately I do not know how to prove this.
Within the SR Bug:6991236 was mentioned.
This Bug is not visible right now, but interpreting the header I can guess it's an enhancement request to allow or support multiple ASM-instances with differen OS-users in a cluster-environment. It's shown to be fixed (implemented) in 11gR2 and has status 98 (Suggestion Implemented). We will see when 11gR2 is out.