deciding execution plan when query is transformed.

Oracle 10g brought cost based query transformation. Consider two below cases and decide what plan of COST based optimizer is good.  Is transformation it is considering is good.

INDEX IDX_HIST_ID ON HISTDATA(ID)

exec :b1:=1

SELECT  /*+ use_nl (A B ) */  MAX (id)
                         FROM histdata A ,  sustained_feed b
                      WHERE A.id=b.id
                        AND b. data_bucket = :B1;

SELECT STATEMENT Optimizer Mode=ALL_ROWS 1   1779                    
  SORT AGGREGATE 1   10                    
    NESTED LOOPS 610 K 5 M 1779                    
      TABLE ACCESS FULL MT_RPT.sustained_feed 17   119   3                    
      FIRST ROW 35 K 105 K 104                    
        INDEX RANGE SCAN (MIN/MAX) MT_RPT.IDX_HIST_ID 35 K 105 K 104                    


SELECT    MAX (id)
                          FROM histdata
                         WHERE id IN (SELECT  id
                                                   FROM sustained_feed
                                                  WHERE data_bucket = :B1);


Operation Object Name Rows Bytes Cost Object Node In/Out PStart PStop

SELECT STATEMENT Optimizer Mode=ALL_ROWS 1   872                    
  SORT AGGREGATE 1   10                    
    HASH JOIN 610 K 5 M 872                    
      TABLE ACCESS FULL MT_RPT.sustained_feed 17   119   3                    
      INDEX FAST FULL SCAN MT_RPT.IDX_HIST_ID 933 K 2 M 759                

corrupt indexes key-rowid value mismatch

Short Circuit caused power Down=>SAN Down=>All databases servers using this storage  Crashed=>Oracle 9i DB started fine=>but…OOPS! SAP Application not working properly

Once a User complained about some modules having issues :
There was a Database Alert Log recorded ORA-600 internal error with different first actual code argument. A search into Meta link showed that DB had problems in executing DMLs on some tables, as some of  the indices on these tables had logical corruption. So it was thought, when DB crashed, DMLS on these tables were in progress,causing update of index entries. so BAD row mismatch occurred as key-row information was not consistent. So  queries and DMLs which would involve these indexes were failing.
Restart of the database, automatic Crash Recovery of Database[without intervention of DBA] was successful, there was no corruption shown in logs nor in any datafile block.  So when DB started,  SAN was made up again. After restoring power supply, it was opened successfully. But it had a logical corruption hidden inside bad leaf blocks of indices.  Automatic crash recovery was not able to fix index logical corruption. This is obvious behavior. Some  won’t expect such issue and blame RDBMS some won't. I won't.


Solution:


1. Identify such indices which are in bad shape and fix problematic indexes
a) drop and recreate such indexes  OR
b) rebuild them  online so that it read key values/rowed from table (normal rebuild  reads existing index entries so corruption does not go away)


2. Restore from backup and recover
The latter  was not feasible as DB had size 1TB and prod database was on RAID 5. It would  have taken more than 2 days to restore it!!!

Another case:
I seen some people killing their application had corrupt indexes. It means when they killed they had some running DMLs on tables.

oracle education content

Oracle has changed a lot the look and feel of its tutorials. They have changed links to reach viewlets. You need good at search. below is URL:

http://apex.oracle.com/pls/apex/f?p=44785:2:1734844480451698::NO:2,CIR,RIR:P2_PRODUCT_ID,P2_RELEASE_ID:2014,85

litle bit on jdbc & oracle when every milisecond matters

when every milisecond matters

1. use OCI JDBC driver , undoubetdly they are fastest JDBC driver , especially when there is lot of fetch. I found them performing 10% faster for a bulk fetch.

2. if java application has to fecth large data then use larger fetch size .

3. When DB server and Java application server are on same box, use OCI JDBC driver. In this case db connection will be using IPC and gives best performance. And ovbvious as states in 1. above , JDBC OCI are fastest even when used for remote connection to DB.

4. if using connection pooling,there should be good number of connections available otherwise there will be more opened cursors per session.

5. perform analysis for use of preparestatement vs statement. Don't go blindly for preparestatement

6. use batch update but just be wise to choose batch size. If keep it is small no benefit and if it is large again it will need more undo in database. Similarly guys when you add statements in batch be wise loop does not iterate very much other wise you will need good memory for your java program.

some tail info not related actually to performance:

-- use the JDBC drivers matching DB server jdbc drivers version. I seen people running  oracle 10.2.0.4 but their jdbc driver was version 10.2.0.1. They had issue in fetching from result set sometimes. Fix suggested was to use matching jdbc driver and it did .

--  if using connection pool,take extreme care to check result sets are closed properly and opened cursors does not exceed limit.

text indexes in comparison query ( tune like query using text index for large tables)

consider text comparison query:
select e.empno,e.ename,d.loc,d.dname from emp e ,dept d
where e.ename like '%'||d.dname||'%'

This is suitable case of use of text indexes if table emp is quite big.

steps:

1 .FROM DBA ---------

GRANT SELECT ON ctxsys.dr$ths_phrase to scott;
GRANT EXECUTE ON CTX_DDL TO scott;

FROM CTXSYS USER ----------------
exec ctx_ddl.create_stoplist('empty_stoplist', 'BASIC_STOPLIST');
exec ctx_ddl.create_preference('matching_lexer', 'BASIC_LEXER');

2.FROM application USER scott ------

CREATE INDEX emp_ename2 ON emp2 (ename) INDEXTYPE IS CTXSYS.CONTEXT parameters ('lexer CTXSYS.matching_lexer');

in order to take benefit of this text index rewrite above query as below :

select e.*
from emp2 e, dept d
where CONTAINS(e.ename, '%'||d.dname||'%')>0


***** Spool outputs along with execution plans *****

SYSTEM session:

1* alter user ctxsys identified by sys
SQL> /

User altered.

SQL> GRANT SELECT ON ctxsys.dr$ths_phrase to scott;

Grant succeeded.

SQL> GRANT EXECUTE ON CTX_DDL TO scott;

Grant succeeded.

SQL> exec ctx_ddl.create_stoplist('empty_stoplist', 'BASIC_STOPLIST');

PL/SQL procedure successfully completed.

SQL> exec ctx_ddl.create_preference('matching_lexer', 'BASIC_LEXER');

PL/SQL procedure successfully completed.


--SCOTT session --

SQL> create table emp2 as select * from emp;

Table created.

SQL> alter table emp2 modify empno number(7);

Table altered.

SQL> alter table emp2 modify ename varchar2(200);

Table altered.

SQL> create sequence s1 ;

Sequence created.

SQL> insert into emp2(empno,ename) select s1.nextval,'SALES' from emp2;

15 rows created.

insert repeated for

30720 rows created.

SQL> commit;

Commit complete.

SQL> insert into emp2(empno,ename) select s1.nextval,'ABCD SALES HOLA' from emp2 where rownum<=1000; 1000 rows created. SQL> select * from dept;

DEPTNO DNAME LOC
---------- -------------- -------------
10 ACCOUNTING NEW YORK
20 RESEARCH DALLAS
30 SALES CHICAGO
40 OPERATIONS BOSTON

SQL> insert into emp2(empno,ename) select s1.nextval,'ABCDACCOUNTINGEXYZ' from emp2;

62440 rows created.

SQL> commit;

Commit complete.

SQL> CREATE INDEX emp_ename2 ON emp2 (ename) INDEXTYPE IS CTXSYS.CONTEXT parameters ('lexer CTXSYS.matching_lexer');

Index created.

SQL> set autotrace traceonly explain
SQL> select e.*
2 from emp2 e, dept d
3 where CONTAINS(e.ename, '%'||d.dname||'%')>0 ;

Execution Plan
----------------------------------------------------------
Plan hash value: 4225699595

-------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 224 | 45696 | 66 (0)| 00:00:01 |
| 1 | NESTED LOOPS | | 224 | 45696 | 66 (0)| 00:00:01 |
| 2 | TABLE ACCESS FULL | DEPT | 4 | 40 | 3 (0)| 00:00:01 |
| 3 | TABLE ACCESS BY INDEX ROWID| EMP2 | 56 | 10864 | 66 (0)| 00:00:01 |
|* 4 | DOMAIN INDEX | EMP_ENAME2 | | | 4 (0)| 00:00:01 |
-------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

4 - access("CTXSYS"."CONTAINS"("E"."ENAME",'%'||"D"."DNAME"||'%')>0)

Note
-----
- dynamic sampling used for this statement (level=2)

SQL> insert into emp2(empno,ename) select s1.nextval,'bola' from emp2 ;

124880 rows created.

SQL> commit;

Commit complete.

SQL> set autotrace traceonly explain statistics timing on
SQL> select e.*
2 from emp2 e, dept d
3 where CONTAINS(e.ename, '%'||d.dname||'%')>0 ;

124866 rows selected.

Elapsed: 00:00:01.23

Execution Plan
----------------------------------------------------------
Plan hash value: 4225699595

-------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 682 | 135K| 146 (0)| 00:00:02 |
| 1 | NESTED LOOPS | | 682 | 135K| 146 (0)| 00:00:02 |
| 2 | TABLE ACCESS FULL | DEPT | 4 | 40 | 3 (0)| 00:00:01 |
| 3 | TABLE ACCESS BY INDEX ROWID| EMP2 | 170 | 32980 | 146 (0)| 00:00:02 |
|* 4 | DOMAIN INDEX | EMP_ENAME2 | | | 4 (0)| 00:00:01 |
-------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

4 - access("CTXSYS"."CONTAINS"("E"."ENAME",'%'||"D"."DNAME"||'%')>0)

Note
-----
- dynamic sampling used for this statement (level=2)


Statistics
----------------------------------------------------------
750 recursive calls
0 db block gets
10354 consistent gets
5 physical reads
0 redo size
2295427 bytes sent via SQL*Net to client
91959 bytes received via SQL*Net from client
8326 SQL*Net roundtrips to/from client
26 sorts (memory)
0 sorts (disk)
124866 rows processed

SQL> /

124866 rows selected.

Elapsed: 00:00:00.84

Execution Plan
----------------------------------------------------------
Plan hash value: 4225699595

-------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 682 | 135K| 146 (0)| 00:00:02 |
| 1 | NESTED LOOPS | | 682 | 135K| 146 (0)| 00:00:02 |
| 2 | TABLE ACCESS FULL | DEPT | 4 | 40 | 3 (0)| 00:00:01 |
| 3 | TABLE ACCESS BY INDEX ROWID| EMP2 | 170 | 32980 | 146 (0)| 00:00:02 |
|* 4 | DOMAIN INDEX | EMP_ENAME2 | | | 4 (0)| 00:00:01 |
-------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

4 - access("CTXSYS"."CONTAINS"("E"."ENAME",'%'||"D"."DNAME"||'%')>0)

Note
-----
- dynamic sampling used for this statement (level=2)


Statistics
----------------------------------------------------------
52 recursive calls
0 db block gets
9682 consistent gets
0 physical reads
0 redo size
2295427 bytes sent via SQL*Net to client
91959 bytes received via SQL*Net from client
8326 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
124866 rows processed

SQL> drop index EMP_ENAME2;

Index dropped.

Elapsed: 00:00:02.93
SQL> ed
Wrote file afiedt.buf

1 select e.*
2 from emp2 e, dept d
3* where e.ename like '%'||d.dname||'%'
SQL>
SQL> /

124866 rows selected.

Elapsed: 00:00:01.01

Execution Plan
----------------------------------------------------------
Plan hash value: 4088618096

---------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 43131 | 8087K| 831 (2)| 00:00:10 |
| 1 | NESTED LOOPS | | 43131 | 8087K| 831 (2)| 00:00:10 |
| 2 | TABLE ACCESS FULL| DEPT | 4 | 40 | 3 (0)| 00:00:01 |
|* 3 | TABLE ACCESS FULL| EMP2 | 10783 | 1916K| 207 (2)| 00:00:03 |
---------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

3 - filter("E"."ENAME" LIKE '%'||"D"."DNAME"||'%')

Note
-----
- dynamic sampling used for this statement (level=2)


Statistics
----------------------------------------------------------
178 recursive calls
0 db block gets
11239 consistent gets
0 physical reads
0 redo size
2295427 bytes sent via SQL*Net to client
91959 bytes received via SQL*Net from client
8326 SQL*Net roundtrips to/from client
5 sorts (memory)
0 sorts (disk)
124866 rows processed

SQL> /

124866 rows selected.

Elapsed: 00:00:01.01

Execution Plan
----------------------------------------------------------
Plan hash value: 4088618096

---------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 43131 | 8087K| 831 (2)| 00:00:10 |
| 1 | NESTED LOOPS | | 43131 | 8087K| 831 (2)| 00:00:10 |
| 2 | TABLE ACCESS FULL| DEPT | 4 | 40 | 3 (0)| 00:00:01 |
|* 3 | TABLE ACCESS FULL| EMP2 | 10783 | 1916K| 207 (2)| 00:00:03 |
---------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

3 - filter("E"."ENAME" LIKE '%'||"D"."DNAME"||'%')

Note
-----
- dynamic sampling used for this statement (level=2)


Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
11157 consistent gets
0 physical reads
0 redo size
2295427 bytes sent via SQL*Net to client
91959 bytes received via SQL*Net from client
8326 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
124866 rows processed

SQL> insert into emp2(empno,ename) select s1.nextval,'z' from emp2;

249760 rows created.

Elapsed: 00:00:02.75


SQL> commit;

Commit complete.

Elapsed: 00:00:00.10
SQL> set autotrace off
SQL> insert into emp2(empno,ename) select s1.nextval,null from emp2;

499520 rows created.

Elapsed: 00:00:06.32
SQL> commit;

Commit complete.

Elapsed: 00:00:00.01
SQL> CREATE INDEX emp_ename2 ON emp2 (ename) INDEXTYPE IS CTXSYS.CONTEXT parameters ('lexer CTXSYS.matching_lexer');

Index created.

Elapsed: 00:00:22.06
SQL> select e.*
2 from emp2 e, dept d
3 where CONTAINS(e.ename, '%'||d.dname||'%')>0
4
SQL>
SQL> set autotrace traceonly explain statistics timing on
SQL>
SQL> /

124866 rows selected.

Elapsed: 00:00:00.86

Execution Plan
----------------------------------------------------------
Plan hash value: 4225699595

-------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1852 | 368K| 356 (0)| 00:00:05 |
| 1 | NESTED LOOPS | | 1852 | 368K| 356 (0)| 00:00:05 |
| 2 | TABLE ACCESS FULL | DEPT | 4 | 40 | 3 (0)| 00:00:01 |
| 3 | TABLE ACCESS BY INDEX ROWID| EMP2 | 463 | 89822 | 356 (0)| 00:00:05 |
|* 4 | DOMAIN INDEX | EMP_ENAME2 | | | 4 (0)| 00:00:01 |
-------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

4 - access("CTXSYS"."CONTAINS"("E"."ENAME",'%'||"D"."DNAME"||'%')>0)

Note
-----
- dynamic sampling used for this statement (level=2)


Statistics
----------------------------------------------------------
392 recursive calls
0 db block gets
10671 consistent gets
2 physical reads
0 redo size
2295427 bytes sent via SQL*Net to client
91959 bytes received via SQL*Net from client
8326 SQL*Net roundtrips to/from client
6 sorts (memory)
0 sorts (disk)
124866 rows processed

SQL> select e.*
2 from emp2 e, dept d
3 where e.ename like '%'||d.dname||'%' ;

124866 rows selected.

Elapsed: 00:00:01.42

Execution Plan
----------------------------------------------------------
Plan hash value: 4088618096

---------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 185K| 33M| 2103 (3)| 00:00:26 |
| 1 | NESTED LOOPS | | 185K| 33M| 2103 (3)| 00:00:26 |
| 2 | TABLE ACCESS FULL| DEPT | 4 | 40 | 3 (0)| 00:00:01 |
|* 3 | TABLE ACCESS FULL| EMP2 | 46308 | 8230K| 525 (3)| 00:00:07 |
---------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

3 - filter("E"."ENAME" LIKE '%'||"D"."DNAME"||'%')

Note
-----
- dynamic sampling used for this statement (level=2)


Statistics
----------------------------------------------------------
5 recursive calls
0 db block gets
15761 consistent gets
0 physical reads
0 redo size
2295427 bytes sent via SQL*Net to client
91959 bytes received via SQL*Net from client
8326 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
124866 rows processed

SQL> /

124866 rows selected.

Elapsed: 00:00:01.42

Execution Plan
----------------------------------------------------------
Plan hash value: 4088618096

---------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 185K| 33M| 2103 (3)| 00:00:26 |
| 1 | NESTED LOOPS | | 185K| 33M| 2103 (3)| 00:00:26 |
| 2 | TABLE ACCESS FULL| DEPT | 4 | 40 | 3 (0)| 00:00:01 |
|* 3 | TABLE ACCESS FULL| EMP2 | 46308 | 8230K| 525 (3)| 00:00:07 |
---------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

3 - filter("E"."ENAME" LIKE '%'||"D"."DNAME"||'%')

Note
-----
- dynamic sampling used for this statement (level=2)


Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
15693 consistent gets
0 physical reads
0 redo size
2295427 bytes sent via SQL*Net to client
91959 bytes received via SQL*Net from client
8326 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
124866 rows processed

SQL> select e.*
2 from emp2 e, dept d
3 where CONTAINS(e.ename, '%'||d.dname||'%')>0;

124866 rows selected.

Elapsed: 00:00:00.82

Execution Plan
----------------------------------------------------------
Plan hash value: 4225699595

-------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1852 | 368K| 356 (0)| 00:00:05 |
| 1 | NESTED LOOPS | | 1852 | 368K| 356 (0)| 00:00:05 |
| 2 | TABLE ACCESS FULL | DEPT | 4 | 40 | 3 (0)| 00:00:01 |
| 3 | TABLE ACCESS BY INDEX ROWID| EMP2 | 463 | 89822 | 356 (0)| 00:00:05 |
|* 4 | DOMAIN INDEX | EMP_ENAME2 | | | 4 (0)| 00:00:01 |
-------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

4 - access("CTXSYS"."CONTAINS"("E"."ENAME",'%'||"D"."DNAME"||'%')>0)

Note
-----
- dynamic sampling used for this statement (level=2)


Statistics
----------------------------------------------------------
52 recursive calls
0 db block gets
10242 consistent gets
0 physical reads
0 redo size
2295427 bytes sent via SQL*Net to client
91959 bytes received via SQL*Net from client
8326 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
124866 rows processed

thoughts on wheel

I wrote this post from mobile phone while coming from office so it is thoughts coming on wheel.

Setting up database connectivity from oracle(10.2.0.4 on RHEL 5.3 64bit) to foreign data source(any odbc compliant database mysql, mssql server, postgres, netezzza )

What softwares you need to have:

-11g Gateway (if connecting to mysql needs patched to 11.1.0.7) or 11.2.0.1 gateway w
-ODBC Driver Manager (download from unixodbc.org)
-ODBC driver [provided by DB vendor or third party like Data Direct technologies]
- 10g software should have interim patch ******* installed for some bugs for mysql.

Here is steps outline:

1. install ODBC Driver Manager [ need check it is right version]
2. install mysql connector [ need check it is right version]
3. prepare /etc/odbc.ini [location given by env variable
and /etc/odbcinst.ini [location given by odbcinst -j -q]
export NZ_ODBC_INI_PATH=/etc
export ODBCSYSINI=/etc
export ODBCINI=/etc/odbc.ini

4. prepare DSN file
5. check connect from isql [dsn_name] -v
6. install oracle gateway
7. preparare initINSTANCENAME.ora in gateway/hs/admin
8. prepare gateway listener with librarries specified in envs
9. start gateway listener
10. create tnsname which addresses to gateway listener
11. create dblink using tnsname name created in 9.
12. test dblink with various cases

Config files you need to create:

1. Odbc.ini and odbcinst.ini for odbc connectivitt.
2. initDG4ODBC.ora in 11g gateway home/hs/admin
3.

NetApp - You will love too

Much overlooked : UAT and DEV DB standard

Much over looked thing while building UAT and DEV databases is it does not model Production database. I'v seen people tend to ignoring unless they are pushed hard to keep UAT and DEV databases as much as close to Production database design, Data distribution and Hawrdware/Software environment.

for e.g. There was an Java Application running on Tomcat - Apache on Solaris 10 on on 64 bit SPARC machines.

1. UAT database was refreshed from export dump instead from Physical Hot/Cold/RMAN backup of Prod. Extent size on UAT and Prod was different. Prod had large fragmentation in some tables,indexes. While UAT did not have as it was refreshed from export dump

2. Statistics were gathered in different way than Production.

3. All database were placed on single Disk array on UAT database. Production has three mirrored copies of Redo logs while UAT has no mirrored redo log.

4. Application was using Connection pooling implemented through java developed code in application only (instead using Oracle's default connection pooling or connection pooling of Weblogic etc)

5. UAT middle tier was using different JDBC driver than Production.

Basic questions to understand Oracle 10g RAC

My Friend and old Pupil Jamshed went through couple of interview questions in RAC in Interviews. To help guys like him here I thought of  more possible questions compiled by me on Oracle 10g RAC to hackle your mind for good. It is not Boggling. It will help you better understand the concept of RAC basics(except GCS and GES role)

1. what is node eviction(gud to start with simple question)
2. what is split brain
3. who do your client connect to VIP or public IP? or is it your choice!
4. how can you change VIP
5. can private IP be changed.
6. what does root.sh do when you install 10g RAC.
7. how is virtual IP configured,what is done behind the VIP configuration assistant.
--some simple questions
8. what is client balancing and server side balancing.
9. how does listener handles requests in RAC
10. Have you ever set TAF. If yes ,expalin how does fail over happens
11. how can cache fusion improve or degreade performance.
12. Have you ever faced any performance issue due RAC
13. what is the background process for cache fusion. Does it have anything to do with log writer process.
14. will you increase parallelism if you have RAC, to gain inter instance parallelism. what are the considerations to decide.
15. what is single point of failure in RAC
16. how do you backup voting disk and how do you recover.
17. what information is stored in OCR, what if you loose it. How can you recover it.
18. how many voting disks and OCRs you can have. Why voting disks can be in odd numbers only.
19. A query running fast on one node is very slow on other node. All the nodes have same configurations. What could be the reasons.
20. Does RMAN behave differently in RAC?
21. Can archive logs be placed on ASM disk. what about on RAW.
22. have you ever used OCFS, can you place OCR and voting disks on OCFS.

Don't assume!

We can not assume in same way we don't believe in rule of thumbs!

consider query:

select c1, sum(c2)

from t1

group by c1

this query returns result set in sorted order of c1 but it changes in 10g R2 as 10g used HASH GROUP BY Operation to implement grouping,rather than using SORT GROUP BY as it would do in earlier versions. So Here if sorting is desired there must be explicit order by query.

Similary There can be some join queries in which users might be getting sorted result set , but they can not rely on it always, may be if execution plan changes it can not sort the result set, so if sorting is required, developers need explicit specify order by clause in query.

I remember a case in which a junior developer wrote a query to dump the table data to asciii csv file, Here was obviously clear columns data in csv need in same order as in table. But as Developer came to know about view user_tab_columns I told him, he used query on this view to estimate the maximum record length of table in csv file(rather than manully summing the all columns widths of table) what he could have done alternate way is set large linesize along with trimspool on, but he wamted to cut short work of typing select c1||','|| c2||','||c3||','||... from table. So he generated this select query from user_tab_columns. But he assumed columns orders would be same as in table name. Result was wrong columns order in csv file. So please don't assume - it was view- so not guaranteed.

 

I/O how much you have - mind it!

Rules of thumb are never advised by me but some can be taken as part of check list one by one while tunning I/O:

rule 1: I/O how much you have - mind it! so rule 1 is minimize I/O

rule 2: maximize cached I/O

rule 3: minimize I/O contention.

how to cut I/O:

1. cut unnecessary fetch. Be restrictive about columns in selected list. Make sure all columns fetched in explicit/implicit cursor are used some where in code. try take benefit of 'fast full index scan' .

2. check usefullness of indexed columns. They may be slowing DMLs and not yielding any query performance gain at all. So identify such indexes and drop.

3. avoid triggers which performs lot of queries/transactions and auditing from inside - these may actually be slowing DMLs especially when dmls in bulks are issued .

4. check all tables/indexes have appropriate values ser for PCTFREE and PCTUSED .

PCTFREE has default 10% so you may be wasting not only 10% extra disk/cache memory but also causing more I/O for objecting not undergoing future updates.

similarly setting PCTUSED quite higher means taking block more frequent on/off from free list.

trick: setting PCTFREE higher can reduce hot block contention.

5. If CPU resources are available some tables can be compressed. this will not ony minimize the I/O at the expense of CPU but also meets the objective "maximize cache" - how ? Because table now needs less buffers, you have more free buffers where other objects can be assigned. This is very useful in case when there is no shortage of CPU but scarcity of memory is.

Remember 10g has compress feature for only CTAS and insert into select queries. It is 11g with which comes OLTP table compression.

6. If using materialized views for replication or reporting then, try their refresh possible by FAST method. And if using FULL mechanism , think twice. What about rfresh by truncate/insert.

7. Optimize query execution plan. It is subjective topic in itself.

- tables are indexed appropriately and indexes have good selectivity. If index is not unique it will be good to have indexes with low clustering factor.

- check all tables have accurate statistics and statistics must have been gathered when tables had representative data.

-You need check instance optimizer parameters have been set correctly. If it is RAC instances they should have same values on all instances.

- Trade off of saving CPU versus good execution plan.

cursor_sharing =exact may be far better than cursor_sharing=similar or cursor_sharing=force

 

 

Maximize cached I/O

1. explore if you need configure KEEP and RECYCEL pools in your database for frequently accessed(small in size) and least accessed(bigger) tables and the set and size them appropriately. Assign the related objects to these pools.

2. set the buffer cache appropriately enough high to minimize physical reads.

3. if using bigger SGA > 16GB, in linux use huge pages memory.

4. set the PGA_AGGREGATE_TARGET appropriately. remember higher value for PGA can favour sort merge join over nested loop join.

.

.

.

 

Minimize I/O contention:

 

Balance the I/O across multiple disks array if possible.

take care of all I/O source redo logs, undo tablespace, temporay tablespaces , index tablespaces and DATA tablespaces and archive log too if DB is running in archivelog mode. SPREAD these across Disks, depending on their concurrent usage. you can check statspack/AWR report for I/O usage on tablespace/datafile wise.

.

.

.

Love Me, Love The Life I Lead Lyrics

(words & music by macauley - greenaway)
I am not a wise man neither am I a fool
But what I am the way the good lord made me
Though I need you more than you may ever understand
I cant wear a face that will betray me

Oh, if youre gonna love me, love the life I lead
Need the things I need, dont try to change me
If youre gonna take me, take me for what I am
I cant be another man, I cant be free

full lyrics

more Elvis Presley lyrics

basics of connectivity - connect Oracle Database Server and easy connect from 10g

go to oracle installation directory,like C:\oracle\product\10.2.0\db_1\network\ADMIN\

check for listener.ora and tnsnames.ora file at DB server.
if there is IP/port change make sure to reflect same here.

if OS is windows : Net Manager tool can be run from:

windows > start > oracle entry in

to check what system level DB privilege have been granted:sqlplus /nolog
SQL>conn username/pwd
SQL> select * from session_privs;

using SQLPLUS login from sys or system user:

sqlplus /nolog
SQL> conn sys/pwd as sysdba
drop user username cascade;

grant connect,resource to username identified by pwd;

From 11g onward connect role has only the create session privilege.

then from cmd line:

imp username/pwd file=filename.dmp fromuser=scott touser=username log=impuser.log

Oracle easy connect feature from 10g onward:

sqlplus username/pwd@IP_ADDRESS_OF_DB_SERVER:PORT_NUMBER/SERVIVCE_NAME

sqlplus scott/tiger@192.168.90.100:1521/DEVDB


 

tracking DDL,while DB is in noarchivelog mode

You want to tack DDL and your DB is in noarchivelog , attempted as below,[no way but to use catalog in flat file]
SQL> conn / as as sysdba
Connected.
SQL> ALTER SESSION SET NLS_DATE_FORMAT = 'DD-MON-YYYY HH24:MI:SS';
Session altered.

SQL> EXECUTE DBMS_LOGMNR.START_LOGMNR( -
> STARTTIME => '13-apr-2009 12:42:00', -
> ENDTIME => '15-apr-2009 11:55:00', -
> OPTIONS => DBMS_LOGMNR.DICT_FROM_ONLINE_CATALOG + -
> DBMS_LOGMNR.CONTINUOUS_MINE);
BEGIN DBMS_LOGMNR.START_LOGMNR( STARTTIME => '13-apr-2009 12:42:00', ENDTIME => '15-apr-2009 11:55:00', OPTIONS => DBMS_LOGMNR.DICT_FROM_ONLINE_CATALOG + DBMS_LOGMNR.CONTINUOUS_MINE); END;

*
ERROR at line 1:
ORA-01325: archive log mode must be enabled to build into the logstream

And again below you get error when utry use redo logs for building dict
SQL> EXECUTE DBMS_LOGMNR_D.BUILD ( options=>DBMS_LOGMNR_D.STORE_IN_REDO_LOGS);
BEGIN DBMS_LOGMNR_D.BUILD ( options=>DBMS_LOGMNR_D.STORE_IN_REDO_LOGS); END;
*
ERROR at line 1:
ORA-01325: archive log mode must be enabled to build into the logstream

if DB bouce is affordable go ahead as follow:
SQL> alter system set utl_file_dir='c:\ora';
SQL> shutdown immediate
SQL> startup

SQL> EXECUTE DBMS_LOGMNR_D.BUILD('dictionary.ora', 'c:\dict', OPTIONS => DBMS_LOGMNR_D.STORE_IN_FLAT_FILE);
PL/SQL procedure successfully completed.

SQL> EXECUTE DBMS_LOGMNR.START_LOGMNR(OPTIONS => DBMS_LOGMNR.DDL_DICT_TRACKING);
BEGIN DBMS_LOGMNR.START_LOGMNR(OPTIONS => DBMS_LOGMNR.DDL_DICT_TRACKING); END;
*
ERROR at line 1:
ORA-01292: no log file has been specified for the current LogMiner sessionORA-06512: at "SYS.DBMS_LOGMNR", line 53
ORA-06512: at line 1

seems in much hurry!!

SQL> EXECUTE DBMS_LOGMNR.ADD_LOGFILE(LOGFILENAME => 'C:\ORACLE\ORADATA\CORPENH\REDO01.LOG', OPTIONS => DBMS_LOGMNR.NEW);

SQL> EXECUTE DBMS_LOGMNR.ADD_LOGFILE(LOGFILENAME => 'C:\ORACLE\ORADATA\CORPENH\REDO02.LOG', OPTIONS => DBMS_LOGMNR.NEW);

SQL> EXECUTE DBMS_LOGMNR.ADD_LOGFILE(LOGFILENAME => 'C:\ORACLE\ORADATA\CORPENH\REDO03.LOG', OPTIONS => DBMS_LOGMNR.NEW);

[file 3 was current log group file]

SQL> EXECUTE DBMS_LOGMNR.START_LOGMNR(OPTIONS => DBMS_LOGMNR.DDL_DICT_TRACKING);

SQL> select count(*) from v$logmnr_contents;
COUNT(*)
----------
48050

SQL> create table t1 as select * from v$logmnr_contents;
Table created.

SQL> create table t2(c1 number);
Table created.

SQL> truncate table t2;
Table truncated.

SQL> create table t1_log as select * from v$logmnr_contents;
Table created.

query from, another session SELECT t.session_info,t.sql_redo, t.* FROM t1_log t WHERE UPPER(sql_redo) LIKE UPPER('%truncate%') OR operation LIKE 'DDL'
gives: DDL operations above in SQL>prompt are also tracked

tracking down resource intensive queries - made easy 10g

from 10g u dont need continuously query v$sesion_wait to track wait events and log in the data to table to aggregate them: follows ASH


top wait events and total time waited :

select active_session_history.event,
sum(active_session_history.wait_time +
active_session_history.time_waited) ttl_wait_time
from v$active_session_history active_session_history
where active_session_history.sample_time between sysdate - 60/2880 and sysdate
group by active_session_history.event
order by 2

top sessions id,username and total time waited :

select sesion.sid,
sesion.username,
sum(active_session_history.wait_time +
active_session_history.time_waited) ttl_wait_time
from v$active_session_history active_session_history,
v$session sesion
where active_session_history.sample_time between sysdate - 60/2880 and sysdate
and active_session_history.session_id = sesion.sid
group by sesion.sid, sesion.username
order by 3



top user id/name,sql_text and total time waited:

SELECT active_session_history.user_id,
dba_users.username,
sqlarea.sql_text,
SUM(active_session_history.wait_time +
active_session_history.time_waited) ttl_wait_time
FROM v$active_session_history active_session_history,
v$sqlarea sqlarea,
dba_users
WHERE active_session_history.sample_time BETWEEN SYSDATE - 1 AND SYSDATE
AND active_session_history.sql_id = sqlarea.sql_id
AND active_session_history.user_id = dba_users.user_id
GROUP BY active_session_history.user_id,sqlarea.sql_text, dba_users.username
ORDER BY 4 DESC

top segments name/type ,events and total wait on them:


SELECT dba_objects.object_name,
dba_objects.object_type,
active_session_history.event,
SUM(active_session_history.wait_time +
active_session_history.time_waited) ttl_wait_time
FROM v$active_session_history active_session_history,
dba_objects
WHERE active_session_history.sample_time BETWEEN SYSDATE - 1 AND SYSDATE
AND active_session_history.current_obj# = dba_objects.object_id
GROUP BY dba_objects.object_name, dba_objects.object_type, active_session_history.event
ORDER BY 4 DESC

top 10 completed queries:

SELECT *
FROM
(SELECT sql_text,
sql_id,
elapsed_time,
cpu_time,
user_io_wait_time,
elapsed_Time/executions,executions
FROM sys.v_$sqlarea
WHERE executions>0
ORDER BY 6 DESC)
WHERE ROWNUM < 11


executions=0 means query are currently in progress

extracting execution plan for library cache:


SELECT LPAD(' ', 2*(LEVEL-1))||operation||' '||
DECODE(ID, 0, 'Cost = '||position) "OPERATION",
options, object_name
FROM v$sql_plan
START WITH (sql_id='&sql_idof_query'
AND child_number = 0
AND ID=0 )
CONNECT BY PRIOR ID = parent_id
AND PRIOR address = address
AND PRIOR hash_value = hash_value
AND PRIOR child_number = child_number
ORDER BY ID, position

query taken: merge join

SELECT st.sql_id,operation,options,object_name,
FROM v$sqltext st , v$sql_plan sp
WHERE st.sql_id=sp.sql_id AND
operation LIKE '%MERGE JOIN%' AND options IS NULL
ORDER BY st.sql_id,piece


query to find sqls which have text 'INDX_IND1':

SELECT /*+ hola */ * FROM v$sqlarea WHERE sql_text LIKE '%INDX_IND1%' AND sql_text NOT LIKE '%hola%'

Parallel Query in Oracle !! Beware!!

Run query in parallel only if you have idle CPUs and Table is distributed across disk but again be careful to choose degree of parallelism. Over Parallelization can kill performance

just imagine of 8 CPUs and parallel_threads_per_cpu parameter default value 2

you fired a query with degree 4 but its taking cpu_counts*parallel_threads_per_cpu =16! You can hit few moere such a query and see PX idle wait evens and helplessly watch terrific slow down!!

query session wait details for parallel query(PX wait events)-

SELECT * FROM v$Session_wait
WHERE sid IN ( SELECT sid FROM v$session
WHERE
username='TEST_USER' AND
status='ACTIVE'
)
AND wait_time=0
AND sid IN ( SELECT sid FROM v$px_session)
ORDER BY 1


351 1699 direct path read file number 5 0000000000000005 first dba 2588307 0000000000277E93 block cnt 126 000000000000007E 1740759767 8 User I/O 0 0 WAITING
416 1498 direct path read file number 5 0000000000000005 first dba 2573843 0000000000274613 block cnt 126 000000000000007E 1740759767 8 User I/O 0 0 WAITING
419 26 PX Deq: Execution Msg sleeptime/senderid 268566527 000000001001FFFF passes 26 000000000000001A 0 00 2723168908 6 Idle 0 3 WAITING
445 26 PX Deq: Execution Msg sleeptime/senderid 268566527 000000001001FFFF passes 26 000000000000001A 0 00 2723168908 6 Idle 0 3 WAITING
575 26 PX Deq: Execution Msg sleeptime/senderid 268566527 000000001001FFFF passes 26 000000000000001A 0 00 2723168908 6 Idle 0 3 WAITING
618 996 PX Deq: Execute Reply sleeptime/senderid 200 00000000000000C8 passes 1 0000000000000001 0 00 2723168908 6 Idle 0 21 WAITING
684 26 PX Deq: Execution Msg sleeptime/senderid 268566527 000000001001FFFF passes 26 000000000000001A 0 00 2723168908 6 Idle 0 3 WAITING
693 1616 direct path read file number 5 0000000000000005 first dba 2566803 0000000000272A93 block cnt 126 000000000000007E 1740759767 8 User I/O 0 0 WAITING
702 1329 direct path read file number 5 0000000000000005 first dba 2599955 000000000027AC13 block cnt 126 000000000000007E 1740759767 8 User I/O 0 0 WAITING

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

345 194 PX Deq Credit: send blkd sleeptime/senderid 268566527 000000001001FFFF passes 175 00000000000000AF qref 0 00 1893977003 0 Other 0 0 WAITING
356 1248 PX Deq: Execution Msg sleeptime/senderid 268566527 000000001001FFFF passes 1244 00000000000004DC 0 00 2723168908 6 Idle 0 0 WAITING

So better you diable parallelization of tables by statement alter table noparallel

Oracle Bugs and Life!!I I'm amused to see.

Its been 2 months!!! Much has since in life and seems life will never be same again. oh.. and Im back again to Post but little.

Although once I did it already I faced issueses again in installation of Oracle (bit) version on RHEL 5.2 (64 bit ) and it took again few more hours to delay installation. But caused more panicked than before.

Issue was same again that Oracle Universal Installer was still requiring 32 bit libraries to start with. That is known bug but Oracle Documents is missing this point even in lates version of docs.

RPMS default installation includes those libraries but system administrator did not do default RPMSs installation rather he customized and so dependency call of 32 library was unresolved.


One another day I was awaked in middle of night by call: error was 4031 but that was not so simple so obvious here culprit was new bug in 10g ERROR 4031 was misleading.
Oh what the life has become !!!

And more: one day when I stayed home to take care of some my dear one at same time I had to monitor DB from home. But much more than this some important query used Graphical User Interface hanged and major work suffered. Many users sat idle!! They had no option but to call me to fix Oh what a life has become!! while I did not have remote access to that Prod DB I asked Junior DBA to send me execution Plan and I guessed query can be fixed to get the same old execution plan by setting hidden parameters!! Parameter turned off using B+ indices as Bitmap Index and it worked!! oh what a life is!!! It simply rocked. Ah but what a life has become. But I'm amused to think what I did and I did it my way!!!

adieu !! till Im back

extracting SQLS from redo logs 10g to tracks all/commited changes

I -to track all(commited+non commited changes)

since v$logmmr_contents data is only persistent to session in which log miner is run a table logmined_commited is created to store all extracted information.

SQL> ALTER SESSION SET NLS_DATE_FORMAT = 'DD-MON-YYYY HH24:MI:SS';

Session altered.

In below options parameters is saving the time of adding individual redo logs/archive logs.

SQL> EXECUTE DBMS_LOGMNR.START_LOGMNR( -
> STARTTIME => '01-jan-2008 01:00:00', -
> ENDTIME => '03-jan-2008 21:00:00', -
> OPTIONS => DBMS_LOGMNR.DICT_FROM_ONLINE_CATALOG + -
> DBMS_LOGMNR.CONTINUOUS_MINE);

PL/SQL procedure successfully completed.

SQL> select count(*) from v$logmnr_contents;

COUNT(*)
----------
98558

SQL> create table extlogs as select * from v$logmnr_contents;

Table created.
------

OR

II -to track commited changes only

SQL> ALTER SESSION SET NLS_DATE_FORMAT = 'DD-MON-YYYY HH24:MI:SS';

Session altered.

SQL> EXECUTE DBMS_LOGMNR.START_LOGMNR( -
> STARTTIME => '01-JAN-2008 01:00:00', -
> ENDTIME => '03-JAN-2008 23:00:00', -
> OPTIONS => DBMS_LOGMNR.DICT_FROM_ONLINE_CATALOG + -
> DBMS_LOGMNR.CONTINUOUS_MINE + DBMS_LOGMNR.COMMITTED_DATA_ONLY);

PL/SQL procedure successfully completed.


SQL> select count(*) from v$logmnr_contents;

COUNT(*)
----------
54994


SQL> create table extlogs as select * from v$logmnr_contents ;

Table created.

SQL> exec DBMS_LOGMNR.END_LOGMNR( );

PL/SQL procedure successfully completed.

sample query from extracted logs:
SELECT SEG_OWNER, SEG_NAME,operation, COUNT(*) AS Hits FROM
sys.logmined_commited
WHERE SEG_OWNER NOT IN('SYS','SYSTEM','WKSYS')
AND TRUNC(timestamp)='01-jan-07'
GROUP BY SEG_OWNER, SEG_NAME,operation
ORDER BY 1,2,3,4

above shows tables which had undergone DMLS

below shows DML type,corresponding DML statement and equivalent UNDO statement to undo DML for above affected objects.

SELECT OPERATION, SQL_REDO, SQL_UNDO
FROM sys.logmined_commited
WHERE SEG_OWNER = 'SEGOWNER'
AND SEG_NAME = 'segment_name'
AND OPERATION = 'UPDATE'
AND USERNAME = 'username';

Performance Tuning considerations for developing new Database system

Over all tuning = Oracle Tuning + OS Tuning (including Network Tuning)
Oracle tuning = DB Instance tuning + Application tuning

DB Instance tuning =Tuning Instance initialization parameters

Application Tuning=SQL Tuning + PL/SQL tuning

OS Tuning= Tuning Kernel Parameters+use of large page size of virtual memory+tuning of swap space+tuning of network kernel parameter+tuning of I/O kernel parameters

 

Whenever we have tuning issues I found to tune at system levevl is best approach efforts to gain higher benefit in short time. This is the area where lies the maximum scope of return of efforts as mostly its ignored. So Let us first begin with this.

 

1. Resources under utilized

 

a) SGA+PGA not configured to make best use of available memory.

 

b) Database files not distributed to balanced I/O. Most of the cases they are not following SAME methods.

 

c) Database code is not using partitioned tables or parallelization benefit

 

So even finest tuned aplication would be slow if they wait/content too much for memory/cpu/IO.

 

2. Poor hardware configuration

 

a) very few CPUs or underpower CPU cores

 

b) less memory so more physical I/O and swapping

 

c) not using appropriate RAID level, for e.g. using RAID for write intensive DB or using same for storing redo log files

 

d) very few diks array or physical drives

 

I found many servers having 4GB RAM, RAID 5 and too with only single logical array drive. To make it more slow I found less RPMS of individual hdds in Disk Array

 

3.Poor indexing

• indices missing
• indices unselective
• indices not used
• In other cases I found over indexing was kill for slow DMLs
• too high clustering factor

 4. Statistics not gathered in way to provide  accurate statistics or they are stale

 

database configuration parameters

 

Poor rdbms version / Poor operating system - One should be wise to choose RDBMS software and OS.

 

5. extra load[unnessary work] I found one database project where there were too much redundant materialized views.

 

6. Too often gathering statistics

 

7. bugs(in case some specific sqls are slow) mostly these re fixed in patches.
not using hidden parameters to counter these

 

8. not using latest upgrade may also be one issue for performance slow down as new versions and patches generally have fixes for the known performance bugs and

 

9. Not using the latest features is also one concern for performance tuning one should go for features such as analytial functions,Bulk loading, Query Subfactoring [with clause ], partitioned tables can be used if required.

Performance Tuning Myths

1. index as much as possible all columns used in join and where clause.

2. Daily gather the statistics

3. DBMS_STATS is always better than ANALYZE TABLE

4. tables should be listed in from clause in such a way that oracle can start joining from smaller tables to bigger ones.

5. use of bind variable is always faster.

6. use Dynamic sqls as much as possible

7. in good tuned DB Cache hit ratios should be above 90

8. newer release faster than previous, so upgrade can boost performance

9. RAC can magically enchance performance.

10. A good DBA can always tune evry query and make it run from 1 hours to 1 min.

11. select count(1) is faster than select count(*)

12. Rebuild indexes daily.

Oracle full of features but BUGs too ORA-00600: internal error code, arguments: [kcratr1_lostwrt],

SETUP : two node 9i rac 9204 ON ocfs ON WIN 2K

first instance crahed due OCFS bug
now second rtried instance recovery of crashed first instance and it quite successfully recovered the first instance(appareent fro malert log of seconds instance)
but sooner secondnstance crashes too and throws ora-600 internal error.

ORA-00600: internal error code, arguments: [kcratr1_lostwrt], [], [], [], [], [], [], []

I tried to manually start the database but oracle throwing same error and complaining about corrupted logs the lost update. I used first argument of ora-600 here [kcratr1_lostwrt] - searched in metalink using ora-600 lookup tool broadly suggested either that was dangerous situation - required media recovery or that could be BUG with other OS like HP Unix and oracle software versions 9205 onwards. which was not our case.Oracle was spuriously trying to recover in second case and metalink told it can be recovered easily in second case.

I preferred to choose second solution path first over giving recover database command (with out restore from backup/after restore). So I set undocument parameter two_pass=false and opened the database successfully from first instance.then I removed this parameter and opened from first instance and second instance,respectively.

keep watch always on query causing extra I/O becuase of high HWM ..its high high high !!!

while I keep watch always on top resoucre intensive queries I observerd many time

HUGE I/O ON DEF$_AQERROR,THOUGH NO ROW in that CURRENTLY.
Since oracle checks for def error perioidically,it was wasting resource in our case.

for that Query oracle fires internally is below:

select q_name, state, delay, expiration, rowid, msgid,
dequeue_msgid, chain_no, local_order_no, enq_time, enq_tid,
step_no, priority, exception_qschema, exception_queue,
retry_count, corrid, time_manager_info
from SYSTEM.DEF$_AQERROR
where time_manager_info <= :1
and state != :2
for
update skip locked

currently there is no row in SYSTEM.DEF$_AQERROR table.
Though there were some errors seen in DEFERROR view a month ago due
confliction but we deleted them giving command like following for every error transaction.

.
execute dbms_defer_sys.delete_error(destination=>null,deferred_tran_id=>'1.26.1180542');
..
commit;


meanwhile we set parametr refresh_after_errors to true in all mv refreh group and when got all errors
removed from DEFERROR at master site we set back these parameter to default i.e
false.

Now my question was why did we still had reads from DEF$_ERRROR and that was comming in huge physical reads, the highest on instance.

Thouught:

that happening beacuse of HWM of DEF$_AQERROR was not reset by oracle's given error
removal procedure dbms_defer_sys.delete_error.

I took following action.

1. for all master groups
Suspend the master acivity at master site.
SQL> connect repadmin/repadmin
SQL> begin
dbms_repcat.suspend_master_activity(gname=>'REPDBA_MG');
end;
/

2.Truncate the table

SQL>connect SYSTEM/

SQL> truncate table def$aq_error;

3. Resume the master activity for all masterr groups.

SQL> connect repadmin/repadmin
SQL> begin
dbms_repcat.resume_master_activity(gname=>'REPDBA_MG');
end;
/



and I was done.

since then I never saw that query in top SQL

gotcha in distributed transactions

while I was just running a query fetching records from remote master db(MASTERDB), connection failed...resulted in PMON trying to clean this transaction every now and then(indicated from alert log).... I had worries my client DB(DEVDB) instance could be terminated,then I found way to resolve it as follows...

AT CLIENT DB(runing query fetching data from master db using dblink)

SQL> show parameter db_name

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_name string DEVDB
SQL> show user
USER is "SYS"
SQL> set line 300
SQL> SELECT LOCAL_TRAN_ID, GLOBAL_TRAN_ID, STATE, MIXED, HOST, COMMIT#
2 FROM DBA_2PC_PENDING;

LOCAL_TRAN_ID GLOBAL_TRAN_ID STATE MIX
---------------------- ------------------------------------------------------------------------------------------------------------------------------------------------------------------------- ---------------- ---
HOST COMMIT#
-------------------------------------------------------------------------------------------------------------------------------- ----------------
5.6.717654 DEVDB.schemaname.ORG.6c69aa83.5.6.717654 collecting no
schemaname\DEVNODE 3486961816


SQL> set line 80
SQL> desc dba_2pc_pending
Name Null? Type
----------------------------------------- -------- ----------------------------
LOCAL_TRAN_ID NOT NULL VARCHAR2(22)
GLOBAL_TRAN_ID VARCHAR2(169)
STATE NOT NULL VARCHAR2(16)
MIXED VARCHAR2(3)
ADVICE VARCHAR2(1)
TRAN_COMMENT VARCHAR2(255)
FAIL_TIME NOT NULL DATE
FORCE_TIME DATE
RETRY_TIME NOT NULL DATE
OS_USER VARCHAR2(64)
OS_TERMINAL VARCHAR2(255)
HOST VARCHAR2(128)
DB_USER VARCHAR2(30)
COMMIT# VARCHAR2(16)

SQL> col global_tran_id format a35
SQL> /

LOCAL_TRAN_ID GLOBAL_TRAN_ID STATE MIX
---------------------- ----------------------------------- ---------------- ---
HOST
--------------------------------------------------------------------------------
COMMIT#
----------------
5.6.717654 DEVDB.schemaname.ORG.6c69aa83.5.6.717654 collecting no
schemaname\DEVNODE
3486961816


SQL> set line 300
SQL> /

LOCAL_TRAN_ID GLOBAL_TRAN_ID STATE MIX HOST COMMIT#
---------------------- ----------------------------------- ---------------- --- -------------------------------------------------------------------------------------------------------------------------------- ----------------
5.6.717654 DEVDB.schemaname.ORG.6c69aa83.5.6.717654 collecting no schemaname\DEVNODE 3486961816

SQL>
SQL> SELECT LOCAL_TRAN_ID, IN_OUT, DATABASE, INTERFACE
2 FROM DBA_2PC_NEIGHBORS;

LOCAL_TRAN_ID IN_ DATABASE I
---------------------- --- -------------------------------------------------------------------------------------------------------------------------------- -
5.6.717654 in N
5.6.717654 out MASTERDB.schemaname.ORG N

SQL> execute DBMS_TRANSACTION.PURGE_LOST_DB_ENTRY('5.6.717654');
BEGIN DBMS_TRANSACTION.PURGE_LOST_DB_ENTRY('5.6.717654'); END;

*
ERROR at line 1:
ORA-30019: Illegal rollback Segment operation in Automatic Undo mode
ORA-06512: at "SYS.DBMS_TRANSACTION", line 65
ORA-06512: at "SYS.DBMS_TRANSACTION", line 85
ORA-06512: at line 1


SQL> set transaction use rollback segment system
2 ;
set transaction use rollback segment system
*
ERROR at line 1:
ORA-30019: Illegal rollback Segment operation in Automatic Undo mode


SQL> show user;
USER is "SYS"
SQL> alter session set "_smu_debug_mode" = 4;

Session altered.

SQL> execute DBMS_TRANSACTION.PURGE_LOST_DB_ENTRY('5.6.717654');

PL/SQL procedure successfully completed.

SQL> commit;

Commit complete.

SQL> spool off

ORA-12535: TNS:operation timed out: don't take it easy

ORA-12535: TNS:operation timed out

basic check is you check host name is resolved/reacheable correctly by PING and then further check and TNSPING succeeded but still not able to connect through oracle net(e.g. using SQLPLUS) - Why ?
so no address conflicts,no DNS outages, no virtual ip addresses that can not be resolved anymore, no failed cluster groups/veritas cluster packages
still getting.]

Hey ! you know ports concerned are open stil getting "ORA-12535: TNS:operation timed out" when connecting - why?

yet there could be FIREWALL problem

when connection request is made to an Oracle listener ( generally port 1521), a new process is spawned/thread created which listens on a different port (re u catching it),which is send back to the client, and the client tries to make a TCP connection to the new oracle process/thread. Thats typical way connection is made.

So you should not only add the lsnrctl to the FIREWALL rules, but also add the oracle process to the firewall rule. You know from basis OS knowledge the port given back to the client can range anywhere from 1024 to 65535.

if your oracle database server is on Windows you can set USE_SHARED_SOCKET = TRUE variable in the registry at HKEY_LOCAL_MACHINE\SOFTWARE\ORACLE

**** more may be needed to make it dunne but hope it helps

You can further look at :
http://www.orafaq.com/maillist/oracle-l/2003/10/24/2174.htm
http://ora-12535.ora-code.com/msg/54838.html
http://ora-12535.ora-code.com/msg/39530.html

on oracle database residing on unix servers this problem may be faced in shared sever mode. to get out from it , in dispatchers parameter port number can be specified so port no wont be selected randomly.(make sure this port is listed in firewall oepn ports list)

dipatchers="(address=(pro=tcp)(host=hostname)(port=1234)(disp=5)"

another way to get rid is use connection manager in win/unix and set port no in cman.ora

ORA-12535 is faced also when using SSL and to skip it you can use dedicated specific port no in dispacthers parameter or by using conn mgr.

10g R2 RAC on vmware

after burning midnight oils for many days I was able to get 10g RAC installed on windows 2003 enterprize edition using vmware. I later installed 10g RAC on linux later in the year. Below links were major help to me.

http://www.oracle-base.com/articles/10g/OracleDB10gR2RACInstallationOnWindows2003UsingVMware.php

http://www.dbasupport.com/oracle/ora10g/RACingAhead0101.shtml

http://forums.oracle.com/forums/thread.jspa?messageID=1123638


http://www.idevelopment.info/data/Oracle/DBA_tips/Oracle10gRAC/CLUSTER_14.shtml#Overview

http://www.oracle.com/technology/obe/10gr2_db_vmware/manage/clusterintro/clusterintro.htm



After long work and long sleep on blog I'm presenting some more useful links:

-- Listed in order as I found them and kept in store for U in random fashion --
http://www.vmware.com/community/thread.jspa?messageID=737640

http://dba.ipbhost.com/index.php?showtopic=8138

http://edelivery.oracle.com/EPD/GetUserInfo/get_form?caller=LinuxWelcome
http://www.oracle.com/technology/pub/articles/calish_file_commands.html



http://forums.oracle.com/forums/thread.jspa?threadID=345155

http://esemrick.blogspot.com/

http://www.oracle.com/technology/oramag/oracle/04-jul/index.html

http://download.oracle.com/docs/cd/B28359_01/server.111/b28310/undo007.htm

http://advancyn.com/advancyn/

http://www.pythian.com/blogs/279/failovers-with-oracle-dataguard


http://www.bloggingaboutoracle.org/archives/poc-maa-blog-10-implementing-a-physical-standby-database


http://www.orafaq.com/forum/t/70522/0/


http://www.idevelopment.info/cgi/ORACLE_dba_tips.cgi



http://www.bijoos.com/oracle/unix4dba1.htm

http://www.idevelopment.info/data/Oracle/DBA_tips/Unix/UNIX_3.shtml

http://www.idevelopment.info/cgi/ORACLE_dba_tips.cgi#Unix

http://www.oraclecoach.com/index.php?option=com_content&task=view&id=48&Itemid=1

http://kevinclosson.wordpress.com/2006/12/17/partition-or-real-application-clusters-will-not-work/

http://www.cnetics.com/RAC_tips.shtml


***********************



http://www.dbasupport.com/forums/archive/index.php/t-11409.html

http://www.dizwell.com/prod/node/681?page=0%2C3





*************
http://dbaworkshop.blogspot.com/2007/01/oracle-10g-rac-installation-using.html
http://vault.centos.org/4.2/isos/i386/

http://www.oracle.com/technology/pub/articles/hunter_rac10gr2.html





http://www.idevelopment.info/data/Oracle/DBA_tips/VMware_Workstation_50/VMWARE_11.shtml


http://www.idevelopment.info/data/Oracle/DBA_tips/VMware_Workstation_50/VMWARE_31.shtml


http://blogs.smokeytech.com/blogs/index.php?blog=1&p=38&more=1&c=1&tb=1&pb=1



http://kevinclosson.wordpress.com/


http://startoracle.com/2007/09/30/so-you-want-to-play-with-oracle-11gs-rac-heres-how/


FOR FTP ********************

http://www.linux.com/base/ldp/howto/FTP-3.html



http://www.phy.duke.edu/~rgb/Beowulf/smp-faq/prive/mentre/smp-faq/smp-faq-3.html


http://www.oracle.com/technology/pub/articles/smiley_10gdb_install.html

http://www.dbazine.com/olc/olc-articles/still5


http://download.oracle.com/docs/cd/B28359_01/server.111/b31107/asmdiskgrps.htm#i1020539


_________________________

http://download-uk.oracle.com/docs/cd/B10501_01/rac.920/a96600/cfgdsk.htm

http://www.puschitz.com/InstallingOracle9iRAC.shtml#StartingAndStoppingOracle9iClusterManager





C:\Documents and Settings\pradeep.singh\Desktop\zodiac\temp\Installing Oracle9i (9_2_0) on Red Hat Linux 8_0 using RAW devices.htm


http://www.dell.com/content/topics/global.aspx/power/en/ps3q03_mahmoodmigration?c=us&cs=555&l=en&s=biz


http://www.ardentperf.com/



http://articles.techrepublic.com.com/5100-1035-5224960.html


While I was able to instal log R2 RAc on vmware CentOS 4, I needed to troubleshoot for GSD and ons errors. Following is inline from http://blog.360.yahoo.com/blog-sWxKwVc0YarVsfeFmtf3gg--?cq=1&p=47
Manually register Oracle CRS resources (listeners, ASM) ----------------------------------------------
Create listeners
----------------------------------------------
In my example, after Oracle 10g Database software installed, when running "netca" to configure listener, the following error happened: "CRS-0259" Owner of the resource does not belong to the group.

The listener file was created, but not complete.

$ cat listener.ora
# listener.ora.sles101 Network Configuration File: /u01/app/oracle/product/10.2.0/db_1/network/admin/listener.ora.sles101
# Generated by Oracle configuration tools.

SID_LIST_LISTENER_SLES101 =
(SID_LIST =
(SID_DESC =
(SID_NAME = PLSExtProc)
(ORACLE_HOME = /u01/app/oracle/product/10.2/db_1)
(PROGRAM = extproc)
)
)

LISTENER_SLES101 =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(PORT = 1521)(IP = FIRST))
(ADDRESS = (PROTOCOL = TCP)(HOST = 172.16.2.145)(PORT = 1521)(IP = FIRST))
)
)

Modify the listener.ora file:
$ cat listener.ora
# listener.ora.sles101 Network Configuration File: /u01/app/oracle/product/10.2.0/db_1/network/admin/listener.ora.sles101
# Generated by Oracle configuration tools.

SID_LIST_LISTENER_SLES101 =
(SID_LIST =
(SID_DESC =
(SID_NAME = PLSExtProc)
(ORACLE_HOME = /u01/app/oracle/product/10.2.0/db_1)
(PROGRAM = extproc)
)
)

LISTENER_SLES101 =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = sles101-vip.test.com)(PORT = 1521)(IP = FIRST))
(ADDRESS = (PROTOCOL = TCP)(HOST = 172.16.2.145)(PORT = 1521)(IP = FIRST))
)
)

Then, start the listener manually by running "lsnctrl start LISTENER_SLES101".We will find that the server already started listening on port 1521 by both public IP And VIP.
$ netstat -nltp|grep 1521
(Not all processes could be identified, non-owned process info
will not be shown, you would have to be root to see it all.)
tcp 0 0 172.16.2.145:1521 0.0.0.0:* LISTEN 18449/tnslsnr
tcp 0 0 172.16.2.147:1521 0.0.0.0:* LISTEN 18449/tnslsnr

Although the listener could be started, while check the crs status by running "crs_stat -t", we will find that listener resources were not registered.

$ ./crs_stat -t
Name Type Target State Host
------------------------------------------------------------
ora....101.gsd application ONLINE ONLINE sles101
ora....101.ons application ONLINE ONLINE sles101
ora....101.vip application ONLINE ONLINE sles101
ora....102.gsd application ONLINE ONLINE sles102
ora....102.ons application ONLINE ONLINE sles102
ora....102.vip application ONLINE ONLINE sles102

Register manually by running "crs_register listener", got the following error:
$ crs_register listener
CRS-0181: Cannot access the resource profile '/u01/app/oracle/product/10.2.0/crs_1/crs/public/listener.cap'.

If we check other RAC machines that runs correctly, we will find some listener cap files like:
-rw-r--r-- 1 oracle oinstall 848 2006-11-25 18:42 ora.sles103.LISTENER_SLES101.lsnr.cap
-rw-r--r-- 1 oracle oinstall 848 2006-11-25 18:42 ora.sles104.LISTENER_SLES102.lsnr.cap

So we need to create manually 2 cap files - ora.sles101.LISTENER_SLES101.lsnr.cap, ora.sles102.LISTENER_SLES102.lsnr.cap under path "/u01/app/oracle/product/10.2.0/crs_1/crs/public/"

$ cat ora.sles101.LISTENER_SLES101.lsnr.cap
NAME=ora.sles101.LISTENER_SLES101.lsnr
TYPE=application
ACTION_SCRIPT=/u01/app/oracle/product/10.2.0/db_1/bin/racgwrap
ACTIVE_PLACEMENT=0
AUTO_START=1
CHECK_INTERVAL=600
DESCRIPTION=CRS application for listener on node
FAILOVER_DELAY=0
FAILURE_INTERVAL=0
FAILURE_THRESHOLD=0
HOSTING_MEMBERS=sles101
OPTIONAL_RESOURCES=
PLACEMENT=restricted
REQUIRED_RESOURCES=ora.sles101.vip
RESTART_ATTEMPTS=5
SCRIPT_TIMEOUT=600
START_TIMEOUT=0
STOP_TIMEOUT=0
UPTIME_THRESHOLD=7d
USR_ORA_ALERT_NAME=
USR_ORA_CHECK_TIMEOUT=0
USR_ORA_CONNECT_STR=/ as sysdba
USR_ORA_DEBUG=0
USR_ORA_DISCONNECT=false
USR_ORA_FLAGS=
USR_ORA_IF=
USR_ORA_INST_NOT_SHUTDOWN=
USR_ORA_LANG=
USR_ORA_NETMASK=
USR_ORA_OPEN_MODE=
USR_ORA_OPI=false
USR_ORA_PFILE=
USR_ORA_PRECONNECT=none
USR_ORA_SRV=
USR_ORA_START_TIMEOUT=0
USR_ORA_STOP_MODE=immediate
USR_ORA_STOP_TIMEOUT=0
USR_ORA_VIP=

$ ll
total 12
drwxrwxrwt 2 oracle users 192 2006-11-25 18:42 .
drwxr-xr-x 14 oracle oinstall 336 2006-11-24 19:13 ..
-rw-rw---- 1 oracle users 3396 2004-08-03 11:26 action_scr.scr
-rw-r--r-- 1 oracle oinstall 848 2006-11-25 18:42 ora.sles101.LISTENER_SLES101.lsnr.cap
-rw-r--r-- 1 oracle oinstall 848 2006-11-25 18:42 ora.sles102.LISTENER_SLES102.lsnr.cap
[oracle@sles101 /u01/app/oracle/product/10.2.0/crs_1/crs/public]


Register the 2 listeners again:

$ crs_register ora.sles101.LISTENER_SLES101.lsnr
$ crs_register ora.sles102.LISTENER_SLES102.lsnr

$ crs_stat -t
Name Type Target State Host
------------------------------------------------------------
ora....01.lsnr application OFFLINE OFFLINE
ora....101.gsd application ONLINE ONLINE sles101
ora....101.ons application ONLINE ONLINE sles101
ora....101.vip application ONLINE ONLINE sles101
ora....02.lsnr application OFFLINE OFFLINE
ora....102.gsd application ONLINE ONLINE sles102
ora....102.ons application ONLINE ONLINE sles102
ora....102.vip application ONLINE ONLINE sles102

Stop and restart the listener on sles101:
$ lsnrctl stop

LSNRCTL for Linux: Version 10.2.0.1.0 - Production on 25-NOV-2006 19:20:25

Copyright (c) 1991, 2005, Oracle. All rights reserved.

Connecting to (ADDRESS=(PROTOCOL=tcp)(HOST=)(PORT=1521))
The command completed successfully
[oracle@sles104 /u01/app/oracle/product/10.2.0/db_1/bin]
$ lsnrctl start LISTENER_SLES101

LSNRCTL for Linux: Version 10.2.0.1.0 - Production on 25-NOV-2006 19:20:50

Copyright (c) 1991, 2005, Oracle. All rights reserved.

Starting /u01/app/oracle/product/10.2.0/db_1/bin/tnslsnr: please wait...

TNSLSNR for Linux: Version 10.2.0.1.0 - Production
System parameter file is /u01/app/oracle/product/10.2.0/db_1/network/admin/listener.ora
Log messages written to /u01/app/oracle/product/10.2.0/db_1/network/log/listener_sles101.log
Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=172.16.2.145)(PORT=1521)))
Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=172.16.2.147)(PORT=1521)))

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=sles101-vip.test.com)(PORT=1521)(IP=FIRST)))
STATUS of the LISTENER
------------------------
Alias LISTENER_SLES101
Version TNSLSNR for Linux: Version 10.2.0.1.0 - Production
Start Date 25-NOV-2006 19:20:50
Uptime 0 days 0 hr. 0 min. 0 sec
Trace Level off
Security ON: Local OS Authentication
SNMP OFF
Listener Parameter File /u01/app/oracle/product/10.2.0/db_1/network/admin/listener.ora
Listener Log File /u01/app/oracle/product/10.2.0/db_1/network/log/listener_sles101.log
Listening Endpoints Summary...
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=172.16.2.147)(PORT=1521)))
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=172.16.2.145)(PORT=1521)))
Services Summary...
Service "PLSExtProc" has 1 instance(s).
Instance "PLSExtProc", status UNKNOWN, has 1 handler(s) for this service...
The command completed successfully

Check the CRS status again, we will find listener resources registered for machine sles101.
[oracle@sles101 /u01/app/oracle/product/10.2.0/crs_1/bin]
$ ./crs_stat -t
Name Type Target State Host
------------------------------------------------------------
ora....01.lsnr application ONLINE ONLINE sles101
ora....101.gsd application ONLINE ONLINE sles101
ora....101.ons application ONLINE ONLINE sles101
ora....101.vip application ONLINE ONLINE sles101
ora....02.lsnr application OFFLINE OFFLINE
ora....102.gsd application ONLINE ONLINE sles102
ora....102.ons application ONLINE ONLINE sles102
ora....102.vip application ONLINE ONLINE sles102

The register process does not need to be run again on the other machine, only one registeration on one machine is required. Just stop and restart the listener on the other node, the registration process is now completed successfully.

$ ./crs_stat -t
Name Type Target State Host
------------------------------------------------------------
ora....01.lsnr application ONLINE ONLINE sles101
ora....101.gsd application ONLINE ONLINE sles101
ora....101.ons application ONLINE ONLINE sles101
ora....101.vip application ONLINE ONLINE sles101
ora....02.lsnr application ONLINE ONLINE sles102
ora....102.gsd application ONLINE ONLINE sles102
ora....102.ons application ONLINE ONLINE sles102
ora....102.vip application ONLINE ONLINE sles102

----------------------------------------------
Add ASM instance resources
----------------------------------------------
In this example, it assumes that ASM instances have been created on both RAC nodes, but they are not registered with CRS, therefore, they are non-clusterware, and could not be used to create an RAC database.

'srvctl' command line will be used to manage/add CRS instances in Oracle 10g, but in SuSE10.1, some modifications have to be applied before running it.

1. Backup file 'srvctl'
$ cp srvctl srvctl.bak

2. Uncomment the export of LD_ASSUME_KERNEL
$ cat srvctl.bak | sed "s/export LD_ASSUME_KERNEL/#export LD_ASSUME_KERNEL/" > srvctl

3. Run 'srvctl' to add ASM resources into the CRS.
Use the srvctl add asm command with the following syntax:
srvctl add asm -n node_name -i asm_instance_name -o oracle_home
(For asm_instance_name, just use ASM1 or ASM2, do not use full name like "ora.sles101.ASM1.asm")

[oracle@sles101 /u01/app/oracle/product/10.2.0/crs_1/bin]
$ ./srvctl add asm -n sles101 -i ASM1 -o /u01/app/oracle/product/10.2.0/db_1
[oracle@sles101 /u01/app/oracle/product/10.2.0/crs_1/bin]
$ ./srvctl add asm -n sles102 -i ASM2 -o /u01/app/oracle/product/10.2.0/db_1
[oracle@sles101 /u01/app/oracle/product/10.2.0/crs_1/bin]

If you do not uncomment LD_ASSUME_KERNEL, you will get error message:
[oracle@sles101 /u01/app/oracle/product/10.2.0/crs_1/bin]
$ ./srvctl add asm -n sles101 -i ASM1 -o /u01/app/oracle/product/10.2.0/db_1
/u01/app/oracle/product/10.2.0/crs_1/jdk/jre/bin/java: error while loading shared libraries: libpthread.so.0: cannot open shared object file: No such file or directory

If you run 'srvctl' in path '/u01/app/oracle/product/10.2.0/crs_1/bin', remember to add './' before 'srvctl', otherwise, you will also get error message:

$ srvctl
/u01/app/oracle/product/10.2.0/crs_1/jdk/jre/bin/java: error while loading shared libraries: libpthread.so.0: cannot open shared object file: No such file or directory

4. Check the status, the ASM instance created, but the state is "OFFLINE"
$ ./crs_stat -t
Name Type Target State Host
------------------------------------------------------------
ora....SM1.asm application OFFLINE OFFLINE
ora....01.lsnr application ONLINE ONLINE sles101
ora....101.gsd application ONLINE ONLINE sles101
ora....101.ons application ONLINE ONLINE sles101
ora....101.vip application ONLINE ONLINE sles101
ora....SM2.asm application OFFLINE OFFLINE
ora....02.lsnr application ONLINE ONLINE sles102
ora....102.gsd application ONLINE ONLINE sles102
ora....102.ons application ONLINE ONLINE sles102
ora....102.vip application ONLINE ONLINE sles102
[oracle@sles101 /u01/app/oracle/product/10.2.0/crs_1/bin]

5. Start the registered ASM resources manually.
$ ./crs_start ora.sles101.ASM1.asm
Attempting to start `ora.sles101.ASM1.asm` on member `sles101`
Start of `ora.sles101.ASM1.asm` on member `sles101` succeeded.
[oracle@sles101 /u01/app/oracle/product/10.2.0/crs_1/bin]
$ ./crs_start ora.sles102.ASM2.asm
Attempting to start `ora.sles102.ASM2.asm` on member `sles102`
Start of `ora.sles102.ASM2.asm` on member `sles102` succeeded.
[oracle@sles101 /u01/app/oracle/product/10.2.0/crs_1/bin]
$ ./crs_stat -t
Name Type Target State Host
------------------------------------------------------------
ora....SM1.asm application ONLINE ONLINE sles101
ora....01.lsnr application ONLINE ONLINE sles101
ora....101.gsd application ONLINE ONLINE sles101
ora....101.ons application ONLINE ONLINE sles101
ora....101.vip application ONLINE ONLINE sles101
ora....SM2.asm application ONLINE ONLINE sles102
ora....02.lsnr application ONLINE ONLINE sles102
ora....102.gsd application ONLINE ONLINE sles102
ora....102.ons application ONLINE ONLINE sles102
ora....102.vip application ONLINE ONLINE sles102
[oracle@sles101 /u01/app/oracle/product/10.2.0/crs_1/bin]

Now, the ASM instances are registered with CRS, and you can use 'dbca' to create RAC database.
Tags: oracle, suse