DBA Heaven

Reset VM Manager Portal "admin" user password

Guys,

This is how I reset the 'admin' user password of my Oracle VM Manager Installation.

1. There's a "register" link under the password field in Login page. Click that link and register a new user with simple password like 123456.

2. Login as sys or OVS user using sqlplus and connect to the DB.

3. select account_name, password from ovs.ovs_user where account_name='';

4. Copy the password field output.

5. update ovs.ovs_user set password='' where account_name='';

6. Commit and voila, you can now login with admin username and password will be 123456;

Isn't it Simple?

Difference Between Oracle Standard Edtion and Standard Edtion One (SE and SE1)

Recently, I came across difference between Oracle Editions in a particular version.

The differences are enormous.

Also, there's a new edition apart from standard and Enterprises Editions. It is Standard Edition One (SE1).

Following table lists the Differences: (Referred with Oracle docs)

Feature/Option	SE1	SE	EE	Notes
High Availability
Oracle Fail Safe	Y	Y	Y	Windows only
Oracle Data Guard—Redo Apply	N	N	Y
Oracle Data Guard—SQL Apply	N	N	Y
Rolling Upgrades—Patch Set, Database, and Operating System	N	N	Y
Online index rebuild	N	N	Y
Online index-organized table organization	N	N	Y	`ALTER` `TABLE` ... `MOVE` `ONLINE` operations
Online table redefinition	N	N	Y	Using the `DBMS_REDEFINITION` package
Duplexed backup sets	N	N	Y
Block change tracking for fast incremental backup	N	N	Y
Unused block compression in backups	N	N	Y
Block-level media recovery	N	N	Y
Parallel backup and recovery	N	N	Y
Tablespace point-in-time recovery	N	N	Y
Trial recovery	N	N	Y
Fast-start fault recovery	N	N	Y
Flashback Table	N	N	Y
Flashback Database	N	N	Y
Flashback Transaction Query	N	N	Y
Scalability
Oracle Real Application Clusters	N	Y	Y	Extra cost with EE, included with SE
Automatic Workload Management	N	Y	Y	Requires RAC
Performance
In-Memory Database Cache	N	N	Y	Extra cost option
Security
Advanced Security Option	N	N	Y	Extra cost option
Oracle Label Security	N	N	Y	Extra cost option
Virtual Private Database	N	N	Y
Fine-grained auditing	N	N	Y
Development Platform
SQLJ	Y	Y	Y	Requires Oracle Programmer
Oracle Developer Tools for Visual Studio .NET	Y	Y	Y	Windows only
Microsoft Distributed Transaction Coordinator support	Y	Y	Y	Windows only
Active Directory integration	Y	Y	Y	Windows only
Native .NET Data Provider—ODP.NET	Y	Y	Y	Windows only
.NET Stored Procedures	Y	Y	Y	Windows only
Manageability
Oracle Change Management Pack	N	N	Y	Extra cost option
Oracle Configuration Management Pack	N	N	Y	Extra cost option
Oracle Diagnostic Pack	N	N	Y	Extra cost option
Oracle Tuning Pack	N	N	Y	Extra cost option, also requires the Diagnostic Pack
Oracle Provisioning and Patch Automation Pack	N	N	Y	Extra cost option
Database Resource Manager	N	N	Y
VLDB, Data Warehousing, Business Intelligence
Oracle Partitioning	N	N	Y	Extra cost option
Oracle OLAP	N	N	Y	Extra cost option
Oracle Data Mining	N	N	Y	Extra cost option
Data Compression	N	N	Y
Bitmapped index, bitmapped join index, and bitmap plan conversions	N	N	Y
Parallel query/DML	N	N	Y
Parallel statistics gathering	N	N	Y
Parallel index build/scans	N	N	Y
Parallel Data Pump Export/Import	N	N	Y
Transportable tablespaces, including cross-platform	N	N	Y	Import of transportable tablespaces supported into SE, SE1, and EE
Summary management—Materialized View Query Rewrite	N	N	Y
Asynchronous Change Data Capture	N	N	Y
Integration
Basic Replication	Y	Y	Y	SE1/SE: read-only, updateable materialized view
Advanced Replication	N	N	Y	Multi-master replication
Oracle Streams	N	N	Y	SE1/SE: apply only
Messaging Gateway	N	N	Y
Transparent Gateways	Y	Y	Y	Extra cost option
Networking
Oracle Connection Manager	N	N	Y
Infiniband Support	N	N	Y
Content Management
Oracle Spatial	N	N	Y	Extra cost option

The Only (of-course two) differences between SE and SE1 are support for RAC and AWR feature.

Your updates are welcome.

Sundar K

ksh not found while pre/post cloning

I usually receive the below mentioned error while Pre/Post Installation Check.

System Utilities Check

These utilities need to be accessible on your system in order to do administrative tasks.
command : /usr/Stage11i/startCD/Disk1/rapidwiz/bin/adchkutl.sh
Checking for make... found - /usr/bin/make
Checking for ld... found - /usr/bin/ld
Checking for cc... found - /usr/bin/cc
Checking for ar... found - /usr/bin/ar
Checking for ksh...
Unable to find PD KSH version.
Unable to locate all utilities with system path.
PATH = /usr/bin:/usr/Stage11i/startCD/Disk1/rapidwiz/unzip/Linux:/usr/bin:/usr/local/bin:/usr/kerberos/bin:/usr/local/bin:/usr/bin:/bin:/usr/X11R6/bin:/home/oracle/bin

The Solution for this issue is:
export KSH_VERSION='@(#)PD KSH v5.2.14 99/07/13.2'

Even though you have performed all your pre-reqs.

Anyone knows what this '@(#)PD KSH v5.2.14 99/07/13.2' mean to?

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..

Swap space Calculation

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