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

Installing 9i/10g on RHEL 3

Prerequisite: RHEL 3 has been installed with all required packages(see bottom of this post)
Also check, if you need OS Patch. For install of 9i u need 3006854 patch.Not needed for oracle 10g

Install the 3006854 patch:
unzip p3006854_9204_LINUX.zip
cd 3006854
sh rhel3_pre_install.sh

1. system configuration change
Login from root user and add following entries to /etc/sysctl.conf file:

kernel.shmmax = 2147483648
kernel.shmmni = 4096
kernel.shmall = 2097152
kernel.sem = 250 32000 100 128
fs.file-max = 65536
net.ipv4.ip_local_port_range = 1024 65000

In addition the following lines can be added to the /etc/security/limits.conf file:
oracle soft nofile 1024
oracle hard nofile 65536
oracle soft nproc 2047
oracle hard nproc 16384

2. install groups/user creation
Create the new groups and users:

groupadd oinstall
groupadd dba

useradd -g oinstall -G dba oracle

change password for oracle user
passwd oracle

3. install directory creation /setup
Create the directories in which the Oracle software will be installed and give ownership to install user

mkdir /u01
chown oracle.dba /u01
chmod 777 /u01

4. install user configuration
Login as the install user ,here oracle user and add the following lines at the end of the .bash_profile file:

# Oracle 9i /10g
export ORACLE_BASE=/u01/app/oracle
export ORACLE_HOME=$ORACLE_BASE/product/9.2.0.1.0
export PATH=$PATH:$ORACLE_HOME/bin
export ORACLE_SID=orcl
export LD_LIBRARY_PATH=$ORACLE_HOME/lib
export CLASSPATH=$ORACLE_HOME/JRE:$ORACLE_HOME/jlib:$ORACLE_HOME/rdbms/jlib:
export CLASSPATH=$CLASSPATH:$ORACLE_HOME/network/jlib;
export LD_ASSUME_KERNEL=2.4.1

5. run OUI
For 9i /mnt/cdrom/runInstallerv

10g does not support installation by cd so
For 10g ,copy the 10g software install cd to disk and begin installation .

Now follow the steps suggested by OUI , for troubleshooting , see the install log etc.

required package selection list

1. X Window System.
2. GNOME Desktop Environment.
3. Graphical Internet.
4. Text-based Internet.
5. Server Configuration Tools.
6. FTP Server.
7. Legacy Network Server.
8 Development Tools.
9. Legacy Software Development.
10. Administration Tools.
11. System Tools.

be careful with 7,8,9
click on details and try to select all inside components of the packages.

I hope it helps you.

ORA-02082: a loopback database link must have a connection qualifier

case : scott user wantt to access objects of the sales user in same database by using dblink.(assume he has select any table privilege)


SQL> conn scott/tiger
Connected.

SQL> select * from session_privs;

PRIVILEGE
----------------------------------------
CREATE SESSION
ALTER SESSION
UNLIMITED TABLESPACE
CREATE TABLE
CREATE CLUSTER
CREATE SYNONYM
CREATE VIEW
CREATE SEQUENCE
CREATE DATABASE LINK
CREATE PROCEDURE
CREATE TRIGGER

PRIVILEGE
----------------------------------------
CREATE TYPE
CREATE OPERATOR
CREATE INDEXTYPE

14 rows selected.

SQL> create database link proddb connect to sales identified by password using 'proddblocal'
create database link proddb connect to sales identified by password using 'proddblocal'
*
ERROR at line 1:
ORA-02082: a loopback database link must have a connection qualifier
SQL> ed
Wrote file afiedt.buf
1 create database link proddb@proddb
2 connect to sales identified by password
3* using 'proddblocal'
SQL> /

Database link created.

SQL> select count(*) from dual@proddb ;

COUNT(*)
----------
1

SQL> select * from user_db_links;

DB_LINK USERNAME PASSWORD HOST

CREATED
proddb.sales.ORG@proddb sales password proddblocal

27-APR-07

SQL> conn sales/password@proddblocal
Connected.
SQL> select count(*) from tsales;

COUNT(*)
----------
246678

SQL> conn scott/tiger
Connected.
SQL> select count(*) from tsales@proddb@proddb;

COUNT(*)
----------
246678

using pen drive in linux, mount usb pen drive in linux

Now a days from RHEL 5 linux is able to auto mount the pen drive but if it is not the case then you can follow below steps to mount pen drive in linux.

Back 6 years ago , I had to transfer oracle 11g download to linux machine found ftp services were not installed and no linux cd available. luckily I had 2G pen drive and I was able to move my 2g 11g download from windows to linux.

It was no different than mounting any normal disk.

How I did :

0. created a directory /pendrive

1. When I started linux saw removable devices sercies status [OK]

2. next I gave fdisk -l and noticed the output as below.
[root@lnx2 root]# fdisk -l

Disk /dev/sda: 10.7 GB, 10737418240 bytes
255 heads, 63 sectors/track, 1305 cylinders
Units = cylinders of 16065 * 512 = 8225280 bytes

Device Boot Start End Blocks Id System
/dev/sda1 * 1 13 104391 83 Linux
/dev/sda2 14 172 1277167+ 82 Linux swap
/dev/sda3 173 236 514080 83 Linux
/dev/sda4 237 1305 8586742+ f Win95 Ext'd (LBA)
/dev/sda5 237 1305 8586711 83 Linux


3. now
[root@lnx2 root]# fdisk -l

Disk /dev/sda: 10.7 GB, 10737418240 bytes
255 heads, 63 sectors/track, 1305 cylinders
Units = cylinders of 16065 * 512 = 8225280 bytes

Device Boot Start End Blocks Id System
/dev/sda1 * 1 13 104391 83 Linux
/dev/sda2 14 172 1277167+ 82 Linux swap
/dev/sda3 173 236 514080 83 Linux
/dev/sda4 237 1305 8586742+ f Win95 Ext'd (LBA)
/dev/sda5 237 1305 8586711 83 Linux

Disk /dev/sdb: 1024 MB, 1024966656 bytes
32 heads, 63 sectors/track, 993 cylinders
Units = cylinders of 2016 * 512 = 1032192 bytes

Device Boot Start End Blocks Id System
/dev/sdb1 1 992 999813+ 6 FAT16

then noticed the difference , I found the pen drive device name

[root@lnx2 root]# mount /dev/sdb1 /pendrive


now pen drive content are loaded in this location /pendriver .

after my read from ths location and wrote all desired stuff to local hard disk but cancelled meanwhile.I tried dismounting pen drive using umount but found

[root@lnx2 root]# umount /dev/sdb1
umount: /pendrive: device is busy

I retried after a minute and got successful
[root@lnx2 root]# umount /dev/sdb1

Making 10g RAC ready for worst

focussing on vulenrable part of RAC

RAC gives you high availablity and performance,but what if you dont have clustered disks and your voting disk or cluster registry fails. from 10g R2 these can be mirrored but you should regularly backup and voting/ocr disk.Also you should be heedfull to run integrity test.


manual mirriring of voting disk be

query for current voting disk :
C:\>crsctl query css votedisk
0. 0 P:\cdata\crs\votedsk

located 1 votedisk(s).

adding redundant voting disk:
C:\>crsctl add css votedisk Q:\cdata\crs\votedsk
Cluster is not in a ready state for online disk addition

C:\>crsctl add css votedisk Q:\cdata\crs\votedsk -force
Now formatting voting disk: Q:\cdata\crs\votedsk
successful addition of votedisk Q:\cdata\crs\votedsk.

C:\>crsctl query css votedisk
0. 0 P:\cdata\crs\votedsk
1. 0 Q:\cdata\crs\votedsk

located 2 votedisk(s).

C:\>crsctl add css votedisk O:\cdata\crs\votedsk
Cluster is not in a ready state for online disk addition

investigate ( is CRS running/stopped)

C:\>crsctl add css votedisk O:\cdata\crs\votedsk -force
Now formatting voting disk: O:\cdata\crs\votedsk
successful addition of votedisk O:\cdata\crs\votedsk.


running integrity test
C:\>cluvfy comp ocr -n all

Verifying OCR integrity

Checking OCR integrity...

Checking the absence of a non-clustered configuration...
All nodes free of non-clustered, local-only configurations.

Uniqueness check for OCR device passed.

Checking the version of OCR...
OCR of correct Version "2" exists.

Checking data integrity of OCR...
Data integrity check for OCR passed.

OCR integrity check passed.

Verification of OCR integrity was successful.

qyuery of sweetie

Asked by a old DBA friend about waits caused by wait event "OS THREAD STARTUP" found in his 10g RAC(4 nodes on solaris with ASM)


SELECT DECODE (session_state, 'WAITING', event, NULL) event,
session_state, COUNT(*), SUM (time_waited) time_waited
FROM gv$active_session_history
WHERE sample_time > SYSDATE - 1
GROUP BY DECODE (session_state, 'WAITING', event, NULL),
session_state order by time_waited desc;

Node 1

enq: TX - row lock contention
WAITING
3517
1734721441

os thread startup
WAITING
743
681425655

log file sync
WAITING
3557
530889167

DFS lock handle
WAITING
1131
528346071

db file sequential read
WAITING
34162
502368757

PX Deq: Signal ACK
WAITING
3917
406436176

reliable message
WAITING
283
251896304

gc buffer busy
WAITING
580
175092687

db file parallel read
WAITING
2606
169615544

Streams AQ: qmn coordinator waiting for slave to start
WAITING
123
121679315


I'm concluding on base of my experiecne of parallel queries(on test
machine with single CPU!!!! so no benefit in my case no matter I had RAC)

I see there are PX events indicating your RAC database
instances has parallel query/dml executions but I
guess the os thread startup wait event has nothing to
do with rac environment. This event indicates the
waitupon starting os process(thread) to start query
slave process for execution of parallel query. if u
set init parameter parallel_min_server sufficently
high,you may slightly cut this overhead but its not
recomendded. But instead of following rules of thumb
you check trade-off if you have significant waits.

basic question- deciding index type

guy 1: a table has 10,00000 db blocks
guy 1: it has 9999999999 rows
guy 2: vempires r on the floor
guy 1: on column productid there are 23 distinct values
guy 1: what index would u prefer
guy 2: b+tree
guy 1: nope
guy 1: this column has very high cardinality
guy 2: k
guy 2: sir
guy 1: this column has very few distinct values as comparing to the number of rows in table
guy 1: so very less selectivity . if it was highly selctive only then b+tree
guy 2: yup
guy 1: column seems good candidate for for bmp index
guy 2: im recalling
guy 1: but if heavy dml then even bmp index is also avoided
guy 2: its good .commender
guy 1: because it locks segments (actallyu exents) of objects
guy 2: k
guy 1: so no index if heavy dml on above
guy 2: k

Log_buffer parameter has no effect from 10g

I installed ORACLE SPOTLIGHT for RAC and found LOG_BUFFER was shown to occupy unusually large value, then I investigated and found fixed sga and log buffer are given a one granule size(4m) irrespective of log_buffer value.

SQL> select * from v$sgainfo;

NAME BYTES RES
-------------------------------- ---------- ---
Fixed SGA Size 1290952 No
Redo Buffers 2899968 No
Buffer Cache Size 243269632 Yes
Shared Pool Size 163577856 Yes
Large Pool Size 4194304 Yes
Java Pool Size 4194304 Yes
Streams Pool Size 0 Yes
Granule Size 4194304 No
Maximum SGA Size 419430400 No
Startup overhead in Shared Pool 37748736 No
Free SGA Memory Available 0

11 rows selected.

SQL> select (1290952+ 2899968) /1024 from dual;

(1290952+2899968)/1024
----------------------
4092.69531

Recovery Writer process (RVWR) 10g onwards

Recovery Writer process (RVWR)
The Recovery Writer process is responsible for writing to the flashback logs in the flash recovery area.

These logs are required to restore the database to a previous point in time by using the "flashback" option for Oracle databases (Oracle 10g and later).


U enable the flashback feature in your database and c its power

what fascinates to RAC DBAs and my peers..more and more nodes

great scenario for 2gbps insertion speed......

please look at
http://forums.oracle.com/forums/thread.jspa?threadID=533820&tstart=-2

BT (British Telecom) ran a 82 node (or more) OPS cluster of Pyramid Nile boxes from Siemens.

Resource intensive sessions and SQL

I frequently talk about optimizing top resource consumption.And now most obvious question from audience is always how to find such sessions/sqls. here are ways to get both while you dont have statspack snapshot you can use them as alternate.

Also see my previous blog magical ......

QUERY(cusomizable,according to your workload and) and Sample Output from SPOOL

1 SELECT ses.sid
2 , DECODE(ses.action,NULL,'online','batch') "User"
3 , MAX(DECODE(sta.statistic#,9,sta.value,0))
4 /greatest(3600*24*(sysdate-ses.logon_time),1) "Log IO/s"
5 , MAX(DECODE(sta.statistic#,40,sta.value,0))
6 /greatest(3600*24*(sysdate-ses.logon_time),1) "Phy IO/s"
7 , 60*24*(sysdate-ses.logon_time) "Minutes"
8 FROM V$SESSION ses
9 , V$SESSTAT sta
10 WHERE ses.status = 'ACTIVE'
11 AND sta.sid = ses.sid
12 AND sta.statistic# IN (9,40)
13 GROUP BY ses.sid, ses.action, ses.logon_time
14 ORDER BY
15 SUM( DECODE(sta.statistic#,40,100*sta.value,sta.value) )
16* / greatest(3600*24*(sysdate-ses.logon_time),1) DESC
17
SQL> /

SID User Log IO/s Phy IO/s Minutes
---------- ------ ---------- ---------- ----------
9 online 12913.0075 12912.9963 26.7166667
5 online 98.9491051 98.9362416 29.8
14 online 4578.26388 .012476606 26.7166667
19 online 3170.5866 .00270636 24.6333333
20 online 1328.76316 .035087719 1.9
18 online .111731844 .026536313 11.9333333
7 online .749860101 0 29.7833333
21 online .2 0 .5
6 online .016219239 0 29.8
1 online 0 0 29.8166667
2 online 0 0 29.8166667

SID User Log IO/s Phy IO/s Minutes
---------- ------ ---------- ---------- ----------
3 online 0 0 29.8166667
4 online 0 0 29.8

13 rows selected.



1 SELECT hash_value, executions, buffer_gets, disk_reads, parse_calls
2 FROM V$SQLAREA
3 WHERE buffer_gets > 1000
4 OR disk_reads > 100
5* ORDER BY buffer_gets + 100*disk_reads DESC
SQL>
SQL> /

HASH_VALUE EXECUTIONS BUFFER_GETS DISK_READS PARSE_CALLS
---------- ---------- ----------- ---------- -----------
2626326413 1 572 384 1
690085868 1 2575 134 1
2963598673 349 1095 55 6
657604649 1 1172 18 1

Use of AWR history data:

SELECT T1.*, DBA_HIST_SQLTEXT.sql_text,dba_hist_snapshot.BEGIN_INTERVAL_TIME
FROM
(
SELECT
sub.sql_id,
ROUND(sub.seconds_since_date/60,2) elapsed_time_delta_mins,
sub.execs_since_date,
sub.gets_since_date,
sub.snap_id,
ROUND(sub.seconds_since_date/DECODE(execs_since_date,0,1,execs_since_date)/60,2) avg_exec_time
FROM
( -- sub to sort before rownum
SELECT
sql_id,
ROUND(SUM(elapsed_time_delta)/1000000) AS seconds_since_date,
SUM(executions_delta) AS execs_since_date,
SUM(buffer_gets_delta) AS gets_since_date,
snap_id
FROM
dba_hist_snapshot NATURAL JOIN dba_hist_sqlstat
WHERE
dba_hist_sqlstat.parsing_schema_name='schema_name'
AND
begin_interval_time BETWEEN TRUNC(SYSDATE-1) AND TRUNC(SYSDATE)
-- SYSDATE-1 AND SYSDATE
GROUP BY
sql_id,snap_id
ORDER BY
2 DESC
) sub
WHERE ROWNUM <= 200
)T1 , DBA_HIST_SQLTEXT , dba_hist_snapshot
WHERE T1.sql_id=DBA_HIST_SQLTEXT.sql_id
AND T1.snap_id=dba_hist_snapshot.snap_id
--AND LOWER(DBA_HIST_SQLTEXT.sql_text) LIKE '%rule%'
ORDER BY T1.avg_exec_time DESC

select sql_id from top sqls in awr report and then see its execution plan as below:
select * from table (dbms_xplan.display_awr(sql_id));

simple log management scripts

select * from v&log; --status of logfile groups
select * from v&logfile order by group#; --status of logfiles
select * from v&instance; --status of the archiver
alter system archive log start; --restart the archiver
alter system switch logfile; --switch online log
alter system set log_archive_max_processes=4;

--Add logfile group
alter database add logfile group 4
('&logfilename1',
'&logfilename2') size 64M;

--Drop logfile group and all members in it
alter database drop logfile group &N;

--Add logfile member
alter database add logfile member '&logfilename' reuse to group 4;

--Drop logfile member
alter database drop logfile member '&logfilename';



--Checking archivelog mode
select dbid, name, resetlogs_time, log_mode from v&database;

alter system archive log start; -- restarts the archiver
select * from v&archive_dest; -- archiver destinations

--Altering destination
alter system set log_archive_dest_1='location=&path';
alter system set log_archive_dest_state_1='enable';

--Archived log info from the control file
select * from v&archived_log;

--The sequence# of last backed up log
select thread#, max(sequence#) from v&archived_log
where BACKUP_COUNT>0 group by thread#;

--Redo size (MB) per day, last 30 days
select trunc(first_time) arc_date, sum(blocks * block_size)/1048576 arc_size
from v&archived_log
where first_time >= (trunc(sysdate)-30)
group by trunc(first_time);