Monday, July 28, 2008

Solution to "End Program - WMS Idle"

Though this topic is unrelated any Apps DBA topics, I found it necessary to share the solution so that everyone benifits out of it!

Issue:


I used to receive the below error message whenever I tried to shutdown windows XP. A rather vexing problem, no doubt.




Cause:

This message appears because when windows is trying to shutdown and end all the running processes, the WMS idle process keeps working. Thus an end-program message appears. The two most common reasons for this are:

  1. Scout service run by Nero 7
  2. Microsoft Office Communicator 2005

Solution:

The fix is simple, disable the Nero 7 scout service if it is enabled.

For this, go to Start > All Programs > Nero > Tools > Nero Scout.

On this screen ,Uncheck Enable the Nero scout.



This will fix the issue for most of the users, for the rest of you who are not using Nero are most probably getting this error because of Microsoft Office Communicator 2005. For that you need to either manually exit the communicator 2005 from the system tray before you shutdown windows, or also you can use this hot-fix provided by Microsoft for WMI Idle error message.


References: http://www.troublefixers.com/end-program-wms-idle-message-on-windows-shutdown/

Tuesday, July 22, 2008

Version of mod_security with EBS 11.5.10.2

Though it was a bit tough to find out the version of Mod_security that is shipped with E-Business Suite 11.5.10.2, I finally managed to do it, thanks to Metalink Fora!

The version that comes with eBS 11.5.10.2 is 1.8.4. Below is how you find out the version of mod_security shipped with your version of eBS.

$ strings $IAS_ORACLE_HOME/Apache/Apache/libexec/mod_security.so | grep mod_security/

Introduction to Mod_Security

What Is ModSecurity?

ModSecurity is a web application firewall that can work either embedded or as a reverse proxy. It provides protection from a range of attacks against web applications and allows for HTTP traffic monitoring, logging and real-time analysis. [7]

mod_security is an Apache module (for Apache 1 and 2) that provides intrusion detection and prevention for web applications. It aims at shielding web applications from known and unknown attacks, such as SQL injection attacks, cross-site scripting, path traversal attacks, etc. [6]

mod_security is an Apache module designed as a sort of web application firewall. It’s most useful for preventing SQL Injection and Cross Site Scripting (or XSS). [2]

It is also an open source project that aims to make the web application firewall technology available to everyone. [7]


SQL Injection

SQL injection is a technique that exploits a security vulnerability occurring in the database layer of an application. The vulnerability is present when user input is either incorrectly filtered for string literal escape characters embedded in SQL statements or user input is not strongly typed and thereby unexpectedly executed. It is in fact an instance of a more general class of vulnerabilities that can occur whenever one programming or scripting language is embedded inside another.


Cross-site scripting (XSS)

Cross-site scripting (XSS) is a type of computer security vulnerability typically found in web applications which allow code injection by malicious web users into the web pages viewed by other users. Examples of such code include HTML code and client-side scripts. An exploited cross-site scripting vulnerability can be used by attackers to bypass access controls such as the same origin policy. Vulnerabilities of this kind have been exploited to craft powerful phishing attacks and browser exploits. As of 2007, cross-site scripting carried out on websites were roughly 80% of all documented security vulnerabilities. Often during an attack "everything looks fine" to the end-user who may be exposed to unauthorized access, theft of sensitive data and financial loss.


References:

Monday, July 21, 2008

Find the size of an Oracle database

A very common question in any interview is "How do you find the size of a database?" This question can be answered in numerous ways. For e.g., some might say that the size of the database is the sum of the size of each datafile. Some might add the size of the tempfiles to the afore mentioned answer.

However, according to me, the size of the database can be calculated as below:

Database size = size of datafiles + size of tempfiles + size of redologs + size of controlfiles

The above formula can be put to use using the below mentioned SQL.

set serveroutput on
declare
DB_SIZE number;
dfsize number;
tfsize number;
rlsize number;
cfsize number;
begin
select sum(bytes)/1024/1024 "DATA_FILE_SIZE" into dfsize from dba_data_files;
select sum(bytes)/1024/1024 "TEMP_FILE_SIZE" into tfsize from dba_temp_files;
select sum(bytes)/1024/1024 "REDO_LOG_SIZE" into rlsize from v$log;
select (BLOCK_SIZE * (1 + FILE_SIZE_BLKS))/1024/1024 "CONTROL_FILE_SIZE" into cfsize from sys.v_$controlfile where rownum = 1;
DB_SIZE:=round((dfsize+tfsize+rlsize+cfsize)/1024,3);
dbms_output.put_line('-------------------------------------');
dbms_output.put_line('The Database Size is ' || DB_SIZE || ' Gigabytes');
dbms_output.put_line('-------------------------------------');
end;
/



Eg:

SQL > set serveroutput on
SQL > declare

2 DB_SIZE number;
3 dfsize number;
4 tfsize number;
5 rlsize number;
6 cfsize number;
7 begin
8 select sum(bytes)/1024/1024 "DATA_FILE_SIZE" into dfsize from dba_data_files;
9 select sum(bytes)/1024/1024 "TEMP_FILE_SIZE" into tfsize from dba_temp_files;
10 select sum(bytes)/1024/1024 "REDO_LOG_SIZE" into rlsize from v$log;
11 select (BLOCK_SIZE * (1 + FILE_SIZE_BLKS))/1024/1024 "CONTROL_FILE_SIZE" into cfsize from sys.v_$controlfile where rownum = 1;
12 DB_SIZE:=round((dfsize+tfsize+rlsize+cfsize)/1024,3);
13 dbms_output.put_line('-------------------------------------');
14 dbms_output.put_line('The Database Size is ' || DB_SIZE || ' Gigabytes');
15 dbms_output.put_line('-------------------------------------');
16 end;
17 /
-------------------------------------
The Database Size is 88.259 Gigabytes
-------------------------------------

PL/SQL procedure successfully completed.

Find the size of the control files

Below is the SQL statement to find the size of the control files.


select (BLOCK_SIZE * (1 + FILE_SIZE_BLKS))/1024/1024 "CONTROL_FILE_SIZE (MB)" from sys.v_$controlfile where rownum = 1;

OR

select distinct (BLOCK_SIZE * (1 + FILE_SIZE_BLKS))/1024/1024 "CONTROL_FILE_SIZE (MB)" from sys.v_$controlfile;


Eg:

SQL > select (BLOCK_SIZE * (1 + FILE_SIZE_BLKS))/1024/1024 "CONTROL_FILE_SIZE (MB)" from sys.v_$controlfile where rownum = 1;

CONTROL_FILE_SIZE (MB)
----------------------
18.515625


SQL > select distinct (BLOCK_SIZE * (1 + FILE_SIZE_BLKS))/1024/1024 "CONTROL_FILE_SIZE (MB)" from sys.v_$controlfile;


CONTROL_FILE_SIZE (MB)
----------------------
18.515625

SQL > select distinct (BLOCK_SIZE * (1 + FILE_SIZE_BLKS)) "CONTROL_FILE_SIZE (BYTES)" from sys.v_$controlfile;

CONTROL_FILE_SIZE (BYTES)
-------------------------
19415040


$ ls -slrt [path_to_Controlfile]/cntrl0*
18984 -rw-rw---- 1 oracle dba 19415040 Jul 21 02:56
[path_to_Controlfile]/cntrl03.dbf
18984 -rw-rw---- 1 oracle dba 19415040 Jul 21 02:56
[path_to_Controlfile]/cntrl02.dbf
18984 -rw-rw---- 1 oracle dba 19415040 Jul 21 02:56
[path_to_Controlfile]/cntrl01.dbf


Note: 1 block needs to be added to the file_size_blks to allow for the file header.

Thursday, July 3, 2008

Customising SQL Prompt in 10g Database/Oracle Home

Many a time, DBAs and Developers tend to run scripts, accidentally, in instances that they do not actually intend to. In extreme cases, this might result in disastrous consequences, sometimes even leading to recovering/restoring the database from a backup.


This mistake can easily be avoided if the sql prompt displays the username and/or the SID in lieu of just "SQL>".


Whenever sqlplus is invoked, 2 files are executed: glogin.sql and login.sql (if it exists) in the order mentioned. These 2 files are located under $ORACLE_HOME/sqlplus/admin.



Modify the file glogin.sql and add the below line:


set sqlprompt "_user'@'_connect_identifier > "


The net result of this is that the sqlprompt will appear as below whenever you login to sqlplus:


system@ORCL>


where system is the username and ORCL is the SID.


Note: One drawback of this method, if it may be called so, is that whenever one logs in to sqlplus using /nolog option, only the '@' character is displayed as the connection to the database is yet to be established. As soon as the connection is established, the username and SID are again populated in the sqlprompt. Incidentally, this is a new feature in 10g, wherein the glogin.sql and login.sql files are executed after the connect command.