DBA Heaven: 2011

Food for Thought

No candle loses its light while lighting another candle'.

So never stop sharing and helping others because it makes our

life more meaningful..

Refer MOS Doc: 286388.1

There are two swap memory allocation algorithms, used widely.
1. Eager Allocation Model
This algorithm preallocates swap at the time of process creation.
2. Lazy Allocation Model
In this swap is allocated on-demand. (Called Paging).

In Eager model,
Minimum swap required = Total virtual memory of all the processes on the Machine

In Lazy Model:
Minimum swap required = Total virtual memory of all processes - Size of RAM

In both models, the Virtual Memory is calculated using, the following sql (in peak time)

select (value/1024/1024)+(select (sum(st.value )/1024/1024)
from v$sesstat st , v$statname sn
where st.statistic# = sn.statistic#
and sn.name like '%ga memory') "Virtual Memory"
from v$parameter
where name='sga_max_size'
;

For 32-bit machines swap can be
Minimum of 400Mb or 2* size of RAM which ever is greater.
OR
If physical RAM < 2GB , swap = 2* Physical RAM
If Physical RAM >2B & <4GB swap = Size of Physical RAM
If Physical RAM > 4GB swap = 4GB

Difference Between GV$ASM_DISKGROUP_STAT and GV$ASM_DISKGROUP

Last night I was working through some of the ASM Global Dynamic Views.

I was wondering, why both GV$ASM_DISKGROUP_STAT and GV$ASM_DISKGROUP are having same columns and showing the same data.

I got a nice explanation from www.orafaq.com. (Link: http://www.orafaq.com/wiki/ASM_FAQ)

GV$ASM_DISKGROUP_STAT fetches data from cache while
GV$ASM_DISKGROUP fetches data from disk headers.

The former view (%_stat) operates faster than the later.

Moreover, they suggested to change the references of GV$ASM_DISKGROUP to GV$ASM_DISKGROUP_STAT in $ORACLE_HOME/bin/asmcmdcore, if someone feels that, asmcmd command line utility is slow.
There are 4 references of GV$ASM_DISKGROUP in "asmcmdcore" file.

(These findings are applicable to oracle 10.2.0.3 EE. Readers kindly check the documentation of their Db version)

Stats Gathering script

Here's a nice Statistics gathering script.

spool '/tmp/gather.spl';
set heading off;
select '################################' from dual;
select 'Start of Gathering Schema Stats...' from dual;

select sysdate from dual;

exec dbms_stats.gather_schema_stats('SYS',options=>'GATHER',estimate_percent =>DBMS_STATS.AUTO_SAMPLE_SIZE, method_opt => 'FOR ALL COLUMNS SIZE AUTO', cascad
e => TRUE);

exec dbms_stats.gather_schema_stats('APPS',options=>'GATHER',estimate_percent =>DBMS_STATS.AUTO_SAMPLE_SIZE, method_opt => 'FOR ALL COLUMNS SIZE AUTO', casca
de => TRUE);

exec dbms_stats.gather_schema_stats('APPLSYS',options=>'GATHER',estimate_percent =>DBMS_STATS.AUTO_SAMPLE_SIZE, method_opt => 'FOR ALL COLUMNS SIZE AUTO', ca
scade => TRUE);

exec dbms_stats.gather_schema_stats('AP',options=>'GATHER',estimate_percent =>DBMS_STATS.AUTO_SAMPLE_SIZE, method_opt => 'FOR ALL COLUMNS SIZE AUTO', cascade
=> TRUE);

exec dbms_stats.gather_schema_stats('AR',options=>'GATHER',estimate_percent =>DBMS_STATS.AUTO_SAMPLE_SIZE, method_opt => 'FOR ALL COLUMNS SIZE AUTO', cascade
=> TRUE);

exec dbms_stats.gather_schema_stats('INV',options=>'GATHER',estimate_percent =>DBMS_STATS.AUTO_SAMPLE_SIZE, method_opt => 'FOR ALL COLUMNS SIZE AUTO', cascad
e => TRUE);

exec dbms_stats.gather_schema_stats('GL',options=>'GATHER',estimate_percent =>DBMS_STATS.AUTO_SAMPLE_SIZE, method_opt => 'FOR ALL COLUMNS SIZE AUTO', cascade
=> TRUE);

select sysdate from dual;
select null from dual;
select 'End of Stats gathering!!!' from dual;
select '################################' from dual;
spool off;

Thanks to Nazim.

Status of Conc Request with last update date

Below given sql query provides the status, phase, user submitted, last update time with Concurrent request name.

select r.concurrent_program_id,
r.request_id,
r.last_update_date,
r.last_updated_by,
u.user_name,r.phase_code,
(case r.phase_code
when 'I' then 'Inactive'
when 'P' then 'Pending'
when 'R' then 'Running'
when 'C' then 'Completed'
else 'N/a'
end) Phase,r.status_code,
( case r.status_code
when 'A' then 'Waiting'
when 'B' then 'Resuming'
when 'C' then 'Normal'
when 'D' then 'Cancelled'
when 'E' then 'Error'
when 'F' then 'Scheduled'
when 'G' then 'Warning'
when 'H' then 'On Hold'
when 'I' then 'Normal'
when 'M' then 'No Manager'
when 'Q' then 'Standby'
when 'R' then 'Normal'
when 'S' then 'Suspended'
when 'T' then 'Terminating'
when 'U' then 'Disabled'
when 'W' then 'Paused'
when 'X' then 'Terminated'
when 'Z' then 'Waiting'
else 'N/a'
end
) Status,
t.user_concurrent_program_name
from
fnd_concurrent_requests r,
fnd_concurrent_programs_tl t,
fnd_user u
where
-- status_code='I' and
r.concurrent_program_id=t.concurrent_program_id
and
r.last_updated_by=u.user_id
order by r.last_update_date;

The status and phase code seems mis-matching with the application.
Anybody, knows the correct codes ?

Today's Count of Concurrent Requests

A) This query provides the concurrent requests submitted since 12AM, today

select count(*) from apps.fnd_concurrent_requests
where request_date > sysdate - (1/24*(select extract(hour from current_timestamp)+4
/*since my timezone is GST+4 */ from dual)) order by request_date ;

This can be fine tuned for count of completed requests.

B) This query provides the count of conc requests submitted in last one hour.

select count(request_date) from apps.fnd_concurrent_requests where request_date > sysdate -(1/24)

I created a job that uses this query to insert count and sysdate values into an "xyz" table on scott schema.
Later, I used this "xyz" table to create a graph (using ODBC) on MS Excel spreadsheet, that represents the Request submitting pattern of my organization.
The funny part is, the graph dives deep at coffee breaks and lunch break.

Your feedback are welcome.
Sundar K

Query to find Concurrent Request completion time

I use this query to find out the

a) time taken to complete the request in Minutes value.

b) user name

c) request_id

d) Concurrent program name

e) start date/time

f) end date/time

select u.user_name,r.request_id,p.user_concurrent_program_name,r.actual_start_date,r.actual_completion_date,r.completion_text,
(r.actual_completion_date-r.actual_start_date)*1440 "Minutes" from
fnd_concurrent_requests r,
fnd_user u,
fnd_concurrent_programs_tl p
where u.user_id=r.requested_by
and p.concurrent_program_id=r.concurrent_program_id
and phase_code='C' /* Completed */
and request_id > 13246690 /* limit the number of rows for quick processing */
and actual_completion_date is not null
and actual_start_date is not null
and actual_start_date > sysdate-1 /* requests completed in last 24 hrs */
order by "Minutes" desc ;

Hope this will help you.
Sundar k
Sr. Apps DBA

Nice Article about creating Standby using Real Time Apply

This is a nice article about Creating Standby by using Real Time Apply of archivelogs.

http://kb.dbatoolz.com/ex/pwpkg.dp?p_key=11&p_what=detail&p_sr_id=2693&p_sc_id=19&p_debug=&p_search=&p_suser=&p_sdate=#top

Are you in Comfort Zone ?

“அந்த நாட்கள் எப்படி இருந்தது?” என்று கேட்டுப் பார்த்தால், “கருப்பாய் அடி வயிற்றை கவ்வுகிற பயமாய் இருந்தது” என்று சொல்லுவார்கள். வேலையிருக்குமா இருக்காதா, பாஸ் ஆவோமா மாட்டோமா என்ற நாட்கள் நமக்கும் நடந்திருக்கலாம், அப்போது நமக்கு எப்படி இருந்தது என்று கேட்டுப் பார்த்தாலும் இப்படித் தான் பதில் சொல்லுவோம். இதைத் தான், பெரும்பள்ளம் அல்லது Pit என்கிறார்கள்.

உங்கள் வேலையில் தினம் தினம் இம்மாதிரி தோன்றுகிறதா? ஆமாம் என்றால் நீங்கள் முன்னேறிக் கொண்டிருக்கிறீர்கள் என்று அர்த்தம். இம்மாதிரி அடிவயிற்று பயம் இல்லையென்றால் உங்களில் comfort zoneல் இருக்கிறீர்கள் என்று அர்த்தம். சிறந்த தலைமைப் பண்புள்ளவர்கள் மீண்டும் மீண்டும் இம்மாதிரி பெரும்பள்ளங்களைத் தேடிப்போய் விழுந்து, மீண்டும் மேலெழுந்து வருகிறார்கள். கேட்டால் சேலன்ஞ் என்கிறார்கள்.
..
..
கேள்விப்பட்டதுண்டா?

Usefull sql scripts

1. Number of Responsibilities assigned to users. Order by count desc.
============================================
select b.user_name,count(a.responsibility_id)
from fnd_user_resp_groups_direct a,
    fnd_user b
    where a.end_date is null and
    a.user_id=b.user_id and
    b.end_date is null
    group by b.USER_name
    order by 2 desc    ;
============================================

2. Profile options changed in the last 3 days with user name, who changed that.

select
a.profile_option_id "ID",
a.profile_option_name "short_name",
c.user_profile_option_name "Full Name",
b.last_update_date "updated on",
b.last_updated_by "updated_by_ID",
d.user_name "Login_name"
from
fnd_profile_options a,
fnd_profile_option_values b,
fnd_profile_options_tl c,
fnd_user d
where
a.profile_option_id=b.profile_option_id
and
a.profile_option_name=c.profile_option_name
and
b.last_updated_by=d.user_id
and
b.last_update_date > (sysdate -3 )
order by 4 desc;

Connecting to an Oracle DB using JDBC URL

There is a connection option, called JDBC_URL, available on widely used DB Management tools like TOAD, sqldeveloper, etc.

Most of the dbas prefer tns connections and they have less preference to connections using jdbc url.

Here, I will explain you, how to make a jdbc connection to oracle db using sqldeveloper.

Step 1: Click on the new connection.
Step 2: provide Connection name, username, password details.
Step 3: Select "advanced" on connection type.
Step 4: Copy the tns connection details from tnsnames.ora file (without connection name) and prepend "jdbc:oracle:thin:@" with the tns connection details.
Example:
jdbc:oracle:thin:@DESCRIPTION=
(ADDRESS=(PROTOCOL=tcp)(HOST=host-name)(PORT=1521))
(CONNECT_DATA=
(SID=DEV)
)
)

Step 5: Paste this block on the space provided for "Custome JDBC URL".

Test the connection and voila !!!

Oracle Backup and Recovery scenarios

Guys,

The following link contains "Oracle Disaster scenarios with possible options for db recovery".

Oracle Database Disaster and Recovery Scenarios

This purely based on my view and experience.

I kindly requests those, who has different views and options, can put their comments below.

Dear Friends,

On this new year with new dreams, new hopes, new aspirations and a desire to achieve new horizons I am stepping into a new beginning, yes its my marriage ceremony!!! With joyful heart I genially invite you and your family to grace this occasion and shower your love and blessings.

Kindly make the occasion more memorable and cheerful.
Find enclosed invitation for more details. (Treat as hard copy please).

Those who couldn't read Tamil, please click on the following link for an e-invitation.
My E-Invitation

Also in here