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/

Using GLOGIN.SQL

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>". This can be achieved using a variety of methods.

The easiest method (according to me) is detailed below.

Whenever sqlplus is invoked, 2 files are executed: glogin.sql and login.sql (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.

Table Fragmentation

Table Fragmentation

Found this good note on net:-
When rows are not stored contiguously, or if rows are split onto more than one block, performance decreases because these rows require additional block accesses.

Note that table fragmentation is different from file fragmentation. When a lot of DML operations are applied on a table, the table will become fragmented because DML does not release free space from the table below the HWM.

HWM is an indicator of USED BLOCKS in the database. Blocks below the high water mark (used blocks) have at least once contained data. This data might have been deleted. Since Oracle knows that blocks beyond the high water mark don't have data, it only reads blocks up to the high water mark when doing a full table scan.

DDL statement always resets the HWM.

Table size (with fragmentation)

SQL> select table_name,round((blocks*8),2)||'kb' "size"
2 from user_tables
3 where table_name = 'BIG1';

TABLE_NAME size
------------------------------ ------------------------------------------
BIG1 72952kb

Actual data in table:

SQL> select table_name,round((num_rows*avg_row_len/1024),2)||'kb' "size"
2 from user_tables
3 where table_name = 'BIG1';

TABLE_NAME size
------------------------------ ------------------------------------------
BIG1 30604.2kb

Note = 72952 - 30604 = 42348 Kb is wasted space in table

The difference between two values is 60% and Pctfree 10% (default) - so, the table has 50% extra space which is wasted because there is no data.

How to reset HWM / remove fragemenation?

For that we need to reorganize the fragmented table.

We have four options to reorganize fragmented tables:

1. alter table ... move + rebuild indexes
2. export / truncate / import
3. create table as select ( CTAS)
4. dbms_redefinition

GSD (Global Syncorinization Demone is a dummy service after 10.1 .
This is because iOracle does not want to support any other clusters other that CRS.

But you can still find GSD running

Eg:

Creating VIP application resource on (0) nodes.
Creating GSD application resource on (0) nodes.
Creating ONS application resource on (0) nodes.


Basically GSD is simply a dummy service . gsdctl.sh will call lsnodes in $CRS_HOME/bin.

lsnodes is obselete in 10.2 all because of GSD. in 10.2 we use olsnode

eg:

olsnodes -n nodename


but still you can find lsnodes in $CRS_HOME/bin. this is there just to show GSD service is UP.

lsnodes -n nodename will throw a syntak error ,


Same Script:

####gsdctl.sh

if olsnodes = o

then
......
......
...

return 0
else
return 0

endif;


The If condition will never be true since lsnodes has a syntak error . So the piece of code that executes in gsdctl.sh is just

return(0) from the else condition.


To check this

mv gsdct gsdct.old
touch gsdctl

srvctl start nodeapps -n



Still your cluster will function normal.


Thanks,
Sandarsh

Live session tracing

Live session tracing

If you have identified a particular session from a concurrent request or a forms user, you can enable trace while the session is active. To do so, you must first have the spid (LOCAL=NO) process for this sid.

vi process.sql
column a.program format a40
select b.sid, b.serial#, a.spid, a.program from v$process a , v$session b
where a.addr = b.paddr and b.sid = &sid
/

SQL> @process
Enter value for sid: 919

SID SERIAL# SPID PROGRAM
---------- ---------- ------------ ------------------------------------------------
919 6335 15191 oracle@sun9999 (TNS V1-V3)

Make sure you’re running with the environment for oracle user – can’t run the oradebug statement from applmgr environment.
SQL> oradebug setospid 15191
Oracle pid: 152, Unix process pid: 15191, image: oracle@sun9999 (TNS V1-V3)
SQL> oradebug unlimit
Statement processed.
SQL> oradebug event 10046 trace name context forever, level 12
Statement processed.

When you are finished tracing…

SQL> oradebug setospid 15191 -- may not need to execute setospid again
Oracle pid: 152, Unix process pid: 15191, image: oracle@sun9999 (TNS V1-V3)
SQL> oradebug event 10046 trace name context off
Statement processed.

Your trace file will contain the spid in the string.

Script to Find Which Manager is Down

Guys , this script was developed for creating UDM's for one of our customer.

For creating UDM's , we can use select or Function . Nothing else.

To develop this script I took about 10 days , really hard. But got one good approach to do this. The idea is

Go to SYSADMIN>Concurrent manager>
Enable Trace with Bind and then click on Administer.

If you properly analyze the trace you get very good inputs to develop scripts based on your requirement.

declare a number;
b number;
c varchar2(100);
d number;
l_desc varchar2(100);
cursor cur is select CONCURRENT_QUEUE_id from FND_CONCURRENT_QUEUES_VL where ENABLED_FLAG='Y';
begin for cur_rec in cur loop
APPS.FND_CONCURRENT.GET_MANAGER_STATUS(0,cur_rec.concurrent_queue_id,a,b,c,d);
if a <> b then
select DESCRIPTION into l_desc from fnd_concurrent_queues_vl where CONCURRENT_QUEUE_ID = cur_rec.concurrent_queue_id;
dbms_output.put_line(l_desc ' is down');
end if;
/* dbms_output.put_line('queue id ' cur_rec.concurrent_queue_id); dbms_output.put_line('a 'a); dbms_output.put_line('b 'b); dbms_output.put_line('c 'c); dbms_output.put_line('d 'd); */
end loop;
end;

Four output lines are commented . If you really want to understand how his function works then uncomment them.

Also make sure you set the dbmsoutput on .


Thanks,
Sandarsh