Oracle Apps Notes

A collection of my random notes, primarily on Oracle Apps

Monthly Archives: February 2012

Reclaiming reserved space using tune2fs

The mountpoint (/u01) on which I had installed Oracle E-Business Suite was running out of space.   More frustratingly, almost 5GB of precious space was unavailable since it was reserved by the system. This being an installation on my laptop, hard disk space was at a premium.


[root@myapps ~]# df -h
Filesystem Size Used Avail Use% Mounted on
/dev/hda3 6.0G 178M 5.5G 4% /
/dev/hda1 99M 8.7M 85M 10% /boot
none 681M 0 681M 0% /dev/shm
/dev/hda7 2.0G 36M 1.9G 2% /tmp
/dev/hda8 95G 89G 1.3G 99% /u01
/dev/hda2 6.0G 1.9G 3.8G 34% /usr
/dev/hda6 3.0G 93M 2.8G 4% /var
[root@myapps ~]#

The 5GB of space was reserved because ext2 or ext3 filesystem by default allocate 5% of the available blocks for use by the root user. This allows the system to continue running if non-root users fill up the file system and also assists in preventing file fragmentation because the filesystem does not fill up completely.

The good news is that you an use the tune2fs utility to fully reclaim the reserved space. Be careful though not to reclaim all of the reserved space if the file system is used by the root user or for storing log/system files (such as /var and /tmp).

You can use tune2fs with the -l option to view the file system information.


[root@myapps ~]# tune2fs -l /dev/hda8
tune2fs 1.35 (28-Feb-2004)
Filesystem volume name: /u01
Last mounted on: <not available>
Filesystem UUID: e458ad7d-d6c7-481f-8476-e2cd878bd38c
Filesystem magic number: 0xEF53
Filesystem revision #: 1 (dynamic)
Filesystem features: has_journal ext_attr resize_inode dir_index filetype needs_recovery sparse_super large_file
Default mount options: (none)
Filesystem state: clean
Errors behavior: Continue
Filesystem OS type: Linux
Inode count: 12648448
Block count: 25276261
Reserved block count: 1263813
Free blocks: 1589667
Free inodes: 11888886
First block: 0
Block size: 4096
Fragment size: 4096
Reserved GDT blocks: 1024
Blocks per group: 32768
Fragments per group: 32768
Inodes per group: 16384
Inode blocks per group: 512
Filesystem created: Mon May 23 21:44:24 2011
Last mount time: Tue Jan 31 07:12:49 2012
Last write time: Tue Jan 31 07:12:49 2012
Mount count: 44
Maximum mount count: -1
Last checked: Mon May 23 21:44:24 2011
Check interval: 0 (<none>)
Reserved blocks uid: 0 (user root)
Reserved blocks gid: 0 (group root)
First inode: 11
Inode size: 128
Journal inode: 8
Default directory hash: tea
Directory Hash Seed: d32dc62a-1aef-417e-a80e-50f8882bf946
Journal backup: inode blocks
[root@myapps ~]#

And then use the -m option to set the reserved space according to your needs. In this case, I have set the reserved space to 1% of the total space. (Note that the available space is now 5.1GB from the earlier 1.3GB…muchos happiness)


[root@myapps ~]# tune2fs -m 1 /dev/hda8
tune2fs 1.35 (28-Feb-2004)
Setting reserved blocks percentage to 1 (252762 blocks)
[root@myapps ~]# df -h
Filesystem Size Used Avail Use% Mounted on
/dev/hda3 6.0G 178M 5.5G 4% /
/dev/hda1 99M 8.7M 85M 10% /boot
none 681M 0 681M 0% /dev/shm
/dev/hda7 2.0G 36M 1.9G 2% /tmp
/dev/hda8 95G 89G 5.1G 95% /u01
/dev/hda2 6.0G 1.9G 3.8G 34% /usr
/dev/hda6 3.0G 93M 2.8G 4% /var
[root@myapps ~]#

Interacting with SQL and PL/SQL from shell scripts

Today’s post is a  shell script which I had written for monitoring and storing the status of network links in Oracle tables. I am posting it here since it serves as a pretty good example of interacting with SQL and PL/SQL from a shell script including the passing of script variables.


#!/bin/bash
# This script pings the IP addresses provided to determine the status of the network link.
# In addition to logging the status in a log file, it also updates a table in the Oracle database
# with the status of the link. Additionally, if the status of a link has changed since it was
# last checked, records are inserted into the SMS tables(so that the SMS sending program can
# send the link status to the appropriate persons).

#------------------------------------------------------------------------------------------------#
# Enter the list of the IP addresses which you want to ping. Seperate multiple entries with spaces
#HOSTS="180.100.4.9 180.100.4.45 180.100.4.69"
HOSTS="192.168.56.10"
#------------------------------------------------------------------------------------------------#

#-----------------------------------------------------------------------------------------------#
# Enter the 10-digit phone numbers where texts will be sent. Seperate multiple entries with spaces.
PHONES="9999999999 8888888888"
#-----------------------------------------------------------------------------------------------#

# Count the number of phone numbers provided
NUMBER_COUNT=0
for myPhone in $PHONES
do
NUMBER_COUNT=$((NUMBER_COUNT+1))
done

#-------------------------------------------------------------------------#
# Number of packets to be sent to each IP address. Change this as required
COUNT=3
#-------------------------------------------------------------------------#

# This part will check the response of the IP pings.
# All packets received -- Link is UP
# No packets received -- Link is DOWN
# Some packets are received -- Link is experiencing packet loss
for $HOST in $HOSTS
do
 STATUS=UP
 count=$(ping -c $COUNT $HOST | grep 'received' | awk -F',' '{ print $2 }' | awk '{ print $1 }')
 if [ $count -eq 0 ]; then
 # 100% failed
 STATUS=DOWN
 echo "$HOST: $STATUS on $(date)" >> link_status.log
 elif [ $count -gt 0 ] && [ $count -lt $COUNT ]; then
 # Some packets were received
 STATUS=PACKET_LOSS
 echo "$HOST: $STATUS on $(date)" >> link_status.log
 else
 # 100% received
 echo "$HOST: $STATUS on $(date)" >> link_status.log
 fi

#Insert the link status into Oracle tables
sqlplus -s apps/apps << EOM >> link_status.log
set verify off
set heading off
set feedback off
set serveroutput on;
declare
v_ip varchar2(20);
v_old_status varchar2(15);
v_sms_id number;
v_sms_text varchar2(60);
v_server_name varchar2(20);
v_all_phones varchar2(100);
v_phone_no number;
v_docket_id varchar2(10);
begin
v_all_phones:='$PHONES';

--Fetch the previous status of the link
select status, server_name into v_old_status, v_server_name from xxx_link_status where server_ip='$HOST';

--Update table with current status of the link
update xxx_link_status set status='$STATUS',last_update=sysdate where server_ip='$HOST';

--If the current status is different from the previous status, then insert into SMS tables so that
--the SMSs will be sent during the next run of the SMS program
if v_old_status<>'$STATUS' then
 v_sms_text:=v_server_name||' link status: '||'$STATUS';

--This is required for the SMS table
 select sms_seq.nextval
 into v_sms_id
 from dual;

--This is required for the SMS table
 select docket_seq.nextval
 into v_docket_id
 from dual;

--Insert a record into the SMS_MESSAGES table
 BEGIN
 insert into sms_messages
 (sms_id, sms_date, sms_text, docket, resource_id)
 values
 (v_sms_id, sysdate, v_sms_text, v_docket_id, '');
 commit;
 dbms_output.put_line('Successfully updated sms_messages');
 EXCEPTION
 when others then
 dbms_output.put_line('Could not update sms_messages');
 END;

--Insert one record in SMS_MESSAGES for each phone number to which the SMS is to be sent
 for i in 1..$NUMBER_COUNT loop
 select to_number(substr(v_all_phones,((11*i)-10),10)) into v_phone_no from dual;

select docket_seq.nextval
 into v_docket_id
 from dual;
 BEGIN
 execute immediate
 'insert into sms_numbers values ('
 ||v_sms_id||
 ','||v_phone_no||
 ','||'''N'''||
 ','||'NULL'||
 ','||v_docket_id||
 ','||'NULL'||
 ')';
 commit;
 dbms_output.put_line('Successfully updated sms_numbers');
 EXCEPTION
 when others then
 dbms_output.put_line('Could not update SMS_NUMBERS');
 END;

end loop;
end if;
end;
/
exit;
EOM

done
echo '++++++++================================================++++++++' >> link_status.log

All about Host Concurrent Programs

One of the things to remember while registering a host concurrent program is that the first five parameters are reserved by Oracle E-Business Suite for its own use. The sixth parameter onwards is used for user-defined concurrent program parameters. The first five parameters refer to the following:

$0: The shell script to be executed
$1: Oracle user/password
$2: Applications user_id
$3: Application user_name
$4: Concurrent program request_id

In addition to these, the environment variable FCP_LOGIN is also used to store the Oracle user/password. The steps required to register a shell script as a concurrent program are:

1. Create the shell script in the $APPLBIN directory of the specific application top. In this example, I have created the script under $CS_TOP/bin and named it myscr.prog(Oracle documentation mentions that the extension should be .prog). Its contents are listed below:


#!/bin/sh
echo 'Printing parameters....'
echo '0:'$0
echo '1:'$1
echo '2:'$2
echo '3:'$3
echo '4:'$4
echo '5:'$5

echo 'FCPLOGIN:'$FCP_LOGIN
echo 'Finished printing parameters.'

2. Define the concurrent executable with Execution Method ‘Host’. In the Execution File Name field, specify the name of the shell script without extension(‘myscr’ for this example)

3. Define the concurrent program with parameters as required. For this example, I have defined one parameter with default value ‘ABCDEF’

4. Add the concurrent program to a request group

5. Create a symbolic link to fndcpesr (which is located in the $FND_TOP/$APPLBIN directory) and give it the same name as the Execution File Name.  The symbolic link is created in the same directory as the shell script.


[oracle@myapps bin]$ ln -s $FND_TOP/bin/fndcpesr  myscr

Ensure that all files have execute permissions to prevent any ‘Permission denied’ errors. You should now be able to execute the concurrent program which in turn will run the shell script. Executing the concurrent program for this example results in a log file containing the following output. Note that the values of FCP_LOGIN, the reserved parameters and the user-defined parameter are all printed.

Printing parameters….
0:/u01/oracle/visappl/cs/11.5.0/bin/myscr.prog
1:APPS/APPS
2:1001530
3:EBUSINESS
4:2721361
5:ABCDEF
FCPLOGIN:APPS/APPS
Finished printing parameters.

Return codes for host concurrent program

If the script traps an error, use the UNIX exit command ‘exit 1’ to return failure (status code 1) to the Concurrent Manager running the program. Of course, code sections after the exit command will not be executed.


#!/bin/sh
echo 'Printing parameters....'
echo '0:'$0
echo '1:'$1
echo '2:'$2
echo '3:'$3
echo '4:'$4
echo '5:'$5

exit 1

echo 'FCPLOGIN:'$FCP_LOGIN
echo 'Finished printing parameters.'

The concurrent program will complete with status ‘Error’ and the log file will contain the following:

Printing parameters….
0:/u01/oracle/visappl/cs/11.5.0/bin/myscr.prog
1:APPS/APPS
2:1001530
3:EBUSINESS
4:2721405
5:ABCDEF
/u01/oracle/visappl/cs/11.5.0/bin/myscr
Program exited with status 1

There are no defined exit commands to return a warning status. However, it can be done by using the FND_CONCURRENT.SET_COMPLETION_STATUS API  to set the completion status of the request to ‘Warning’. Gareth Roberts deserves a big ‘Thank You’ for posting this on Oracle Forums.


#!/bin/sh
echo 'Printing parameters....'
echo '0:'$0
echo '1:'$1
echo '2:'$2
echo '3:'$3
echo '4:'$4
echo '5:'$5

MYSTATUS=`sqlplus -s $1 &lt;&lt;!
SET HEADING FEEDBACK OFF PAGESIZE 0
declare
l_result boolean;
l_session_id number;
begin
fnd_global.INITIALIZE(l_session_id, null, null, null,null, -1, null, null, null, null, $4, null,null,null,null,null,null,-1);
l_result := fnd_concurrent.set_completion_status('WARNING','Review log file for details.');
commit;
end;
/
exit;
!`

echo 'FCPLOGIN:'$FCP_LOGIN
echo 'Finished printing parameters.'

This solution makes use of a SQL script to initialize a session with the request_id of the concurrent program using FND_GLOBAL.INITIALIZE and then sets the completion status. Upon execution, the concurrent program ends with a ‘Warning’ status and generates the following output:

Printing parameters….
0:/u01/oracle/visappl/cs/11.5.0/bin/myscr.prog
1:APPS/APPS
2:1001530
3:EBUSINESS
4:2721408
5:ABCDEF
FCPLOGIN:APPS/APPS
Finished printing parameters.

One important thing to notice is that echoing the parameters $1 and $FCP_LOGIN leads to the Oracle user/password being written to the log file. This can be prevented by using the options ENCRYPT and SECURE while defining the concurrent program. ENCRYPT signals the Concurrent Manager to pass the Oracle password in the environment variable FCP_LOGIN. The Concurrent Manager leaves the password in the argument $1 blank. To prevent the password from being passed, enter SECURE in the Execution Options field. With this change, Concurrent Manager does not pass the password to the program.

For this example specifying SECURE in the concurrent program options:

and then running the concurrent program does not set the completion status to ‘Warning’ since the Oracle user/password is not passed and the SQL script cannot run. This can be observed from the contents of the log file.

Printing parameters….
0:/u01/oracle/visappl/cs/11.5.0/bin/myscr.prog
1:APPS/
2:1001530
3:EBUSINESS
4:2721412
5:ABCDEF
FCPLOGIN:
Finished printing parameters.

If we set the options field in the concurrent program to ENCRYPT

then the Oracle user/password will be passed only to $FCP_LOGIN and not to $1. We can change the SQL  script to use $FCP_LOGIN instead of $1 and execute the concurrent program. It will now complete with a ‘Warning’ status since the Oracle user/password was passed to the script through $FCP_LOGIN. This can be verified from the contents of the log file:

Printing parameters….
0:/u01/oracle/visappl/cs/11.5.0/bin/myscr.prog
1:APPS/
2:1001530
3:EBUSINESS
4:2721409
5:ABCDEF
FCPLOGIN:APPS/APPS
Finished printing parameters.

Note:

  1. Use ${10}, ${11} instead of $10,$11 to refer to double-digit parameters
  2. fndcpesr parses the arguments passed to the shell script. So if you don’t create the symbolic link to fndcpesr while registering the program, parameter values will not be parsed and passed automatically to the variables $0, $1, $2 etc. You will have to parse the individual parameters yourself (I think they are tab-separated).
  3. The FND_GLOBAL.INITIALIZE procedure is used to set new values for security globals during login or when the responsibility is changed, it accepts the following parameters:
 session_id in out number,
 user_id in number,
 resp_id in number,
 resp_appl_id in number,
 security_group_id in number,
 site_id in number,
 login_id in number,
 conc_login_id in number,
 prog_appl_id in number,
 conc_program_id in number,
 conc_request_id in number,
 conc_priority_request in number,
 form_id in number default null,
 form_appl_id in number default null,
 conc_process_id in number default null,
 conc_queue_id in number default null,
 queue_appl_id in number default null,
 server_id in number default -1