TABLE SPACE CHECK SCRIPT

select tablespace_name, ROUND(bytes_free,2) free,ROUND(bytes_total-bytes_free,2) used, ROUND(bytes_total,2) total,perused
from (select a.tablespace_name, a.bytes_free,b.bytes_total,
ROUND((100-(100*a.bytes_free/b.bytes_total)),2)  perused from
(Select tablespace_name,sum(bytes)/1024/1024 bytes_free From dba_free_space Group by tablespace_name) a,
(Select tablespace_name,sum(bytes)/1024/1024 bytes_total From dba_data_files Group by tablespace_name) b
where a.tablespace_name=b.tablespace_name) where perused >= 0 ORDER BY tablespace_name;

select tablespace_name,sum(bytes)/(1024*1024) from dba_free_space
group by tablespace_name;

Oracle Applications Multi Node To Single Node Cloning

With Oracle Applications 11.5.10, the ability to clone from a multi node to a single node system using rapid clone has now been certified.  This is accomplished by the Shared APPL_TOP and Merging APPL_TOP features.

For a more elaborate explanation you might want to refer to the metalink note Sharing the Application Tier File System in Oracle E-Business Suite 11i (Note 233428.1).

Source System here refers to the multi node system to be cloned

Target System refers to the newly to be created single node system

Applications Version 11.5.10

Operating System Linux ES 4

The following Steps are required to be performed on the source system

1.  Apply application tier patches using adpatch

Apply patch 4038964Apply patch 4175764

Both the above patches are include in the consolidated update 2 or CU2 so in case you are on 11.5.10.2 or later you can ignore this step.

2.  Maintain snapshot information

Log in as the applications user on each application tier node and run ‘Maintain Snapshot Information’ by using adadmin.

3.  Merge existing APPL_TOPs

Log in to the primary node of your application tier as the application user user and run:

$ cd [COMMON_TOP]/admin/scripts/[CONTEXT_NAME]

$ perl adpreclone.pl appsTier merge

This will prompt you with option to merge secondary nodes

Now log in as the applications user to each of the secondary application tier nodes being merged and run:

$ cd [COMMON_TOP]/admin/scripts/[CONTEXT_NAME]

$ perl adpreclone.pl appltop merge

4.  Prepare the source system database tier.

Log on to the database tier of the source system as the oracle user and run the following commands:

$ cd [RDBMS ORACLE_HOME]/appsutil/scripts/[CONTEXT_NAME]

$ perl adpreclone.pl dbTier

With this all the pre clone tasks on the source system have been completed.

The next set of tasks are required to be carried out on the target system that is the system on which you wish to place the merged single node instance.

1.  Create OS user accounts

Create a OS user account for your applications

$ useradd -g dba -d [home_directory] -p password username

Similarly create a OS user account for your database

2.  Modify the orainventory to reflect the new location

$ vi /etc/oraInst.loc

3.  Copy the following application tier directories

Copy the following application tier directories from the primary node of your source system to the target application tier node, retaining the original directory structure:

– [APPL_TOP]– [OA_HTML]– [OA_JAVA]– [COMMON_TOP/util]– [COMMON_TOP/clone>– [806 ORACLE_HOME]– [iAS ORACLE_HOME]

4.  Copy the required files for merging

Log in as the applications user to each of the secondary source nodes and recursively copy:

directory [COMMON_TOP]/clone/appl- to -directory [COMMMON_TOP]/clone/appl on the target system node

Before proceeding with the next steps you must shutdown your oracle applications services and the database on the source system

5. Copy the database tier file system

Log on to the source system as the database user

Copy the database (DBF) files from the source to the target systemCopy the source database ORACLE_HOME to the target system

After this you can now startup the database and applications services on your source system and release it for use.

6.  Configure the target system database server

Log on to the target system as the database user and type the following commands to configure and start the database:

$ cd [RDBMS ORACLE_HOME]/appsutil/clone/bin

$ perl adcfgclone.pl dbTier

This will prompt for new ORACLE_SID,ORACLE_HOME,Port Pool,JAVA_TOP and DATA_TOP give the appropriate values matching your target system

Once successful this should start your database and listener

7.  Configure the application tier server nodes

The database and its listener should remain up before executing the next set of commands.Log in to the merged APPL_TOP node as the applications user and execute the following commands:

$ cd [COMMON_TOP]/clone/bin

$ perl adcfgclone.pl appsTier

This will prompt you new port pool for applictaion tier services as well as new APPLTOP,COMMON_TOP,ORACLE_HOME and IAS_TOP

Successful completion of this task will bring up your application tier services on the target or the cloned node.

8. Post Clone Tasks

Log in to the target system application tier node as the APPLMGR user.Run the following tasks in adadmin for all products:o generate JAR fileso generate message fileso relink executableso copy files to destination

9. Clean up of the target system

Remove the temporary directory [COMMON_TOP]/clone/appl to reduce disk space usage.

Note: The version of Oracle Applications used is 11.5.10 and the operating system is Linux ES 4

How to clean FND_NODES table in Oracle Applications

Step 1:   Always apply the latest cloning patches to avoid all the bugs and fixes

Step 2:  SQL> EXEC FND_CONC_CLONE.SETUP_CLEAN;

COMMIT;

EXIT;

It will delete all old data from the table.

Step 3:  Run AutoConfig on all tiers, firstly on the DB tier and then the APPS tiers to repopulate the required system tables.

How to apply a Patch in Oracle Applications???

Hi, in this post, I will explain how to apply a patch to oracle applications and patching procedure.

Consider you have 3-node architecture (1-db; 1-concurrent node(db-ap), 1-AP node).

If you want to apply patch 6824767, follow these steps:

1.  Shutdown db-ap and ap nodes and keep db and db listener up.

2.  Take Invalid object List before patching.

COLUMN object_name FORMAT A30
SELECT owner, object_type, object_name, status FROM dba_objects WHERE status = ‘INVALID’
ORDER BY owner, object_type, object_name;

3.  Enable maintenance mode using adadmin.

4.  Apply patch on db-ap node.

adpatch defaultsfile=$APPL_TOP/admin/$TWO_TASK/defaults_file.txt  options=noautoconfig,nomaintainmrc,nocompilejsp \
patchtop=/patch/6824767 driver=u6824767.drv logfile=eap1_6824767.log workers=8

5.  Apply patch on ap node.

adpatch defaultsfile=$APPL_TOP/admin/$TWO_TASK/defaults_file.txt options=noautoconfig,nomaintainmrc,nocompilejsp \
patchtop=/patch/6824767 driver=u6824767.drv logfile=eap1_6824767.log workers=8

6.  After applying the patch, again check for invalid objects and compare it with the before applying patch result.

If any new invalid found then run adadmin and compile apps schema.

7.  Disable maintenance mode using adadmin.

8.  Start db-ap and ap nodes.

9.  Do health check by logging into front-end and by submitting a simple concurrent request like “print environment variables”.

that’s all………U r done with it

Apply Application patch on Oracle Applications r12

We will use the Autopatch utility called adpatch:

1- Download the patch from Metalink.

2- Extract the patch: 
$ cd /u01/patches
$ unzip patchname.xp
This will create a directory with the same patchname. Change the owner of this directory
to be the application user:
$ chown -R applprod:oradba /u01/patches/patchname


3- Stop the application tier:

$ su - applprod
$ cd $ADMIN_SCRIPTS_HOME
$ adstpall.sh apps/apps

4- Enable the maintenance mode:
$ adadmin
Select [5] Chnge maitinance mode
Select [1] Enable maitinance mode

5- Move to the patch directory
$ cd /u01/patches/patchname

6- run the Autopatch utility
$ adpatch

7- After successful patching Disable the maintenance mode.
$ adadmin
Select [5] Change maintenance mode
Select [2] Disable maintenance mode

8- run the application tier back.
$ cd $ADMIN_SCRIPTS_HOME
$ adstrtal.sh apps/apps

UNIX SCRIPTING

In Oracle APPS we can Run Unix Script as a Concurrent Program.

Basically the Unix file has a ".sh" Extension and we directly excute this Unix script in Putty,
but here in Oracle apps the Unix file has ".prog" Extension and we execute this Unix script as Concurrent program.
We call this Concurrent Program as HOST based Program

Basic Steps to Create Host Concurrent Program
 1. Create a ".prog" File
 2. Move the File to Server in any Custom TOP "bin" Folder (Tools Winscp / Filezilla)
 3. Execute the Below commands oderly maner
     3.1 Putty> cd $XXCUSTOM_TOP/bin
          -- Here the Custom TOP is the File Location TOP 
     3.2 Putty> dos2unix sample.prog
     3.3 chmod 755 sample.prog
     3.4 Putty> ln -s $FND_TOP/bin/fndcpesr sample
           -- Here the File name with out .prog Extension
           -- We are creating the Softlink to our File
     3.5 Add continue to create concurrent program Excutable and Program but the Executable type is "HOST"


Here i am Giving the Sample Commands i have used in my programming

1. Commenting any line in UNIX by using hash(#)
   # this is commented
2. Getting the Oracle Standard Parameters
   #user/bin/ksh
   ORAUSER_PASSWD=$1
   USER_ID=$2
   USER_NAME=$3
   REQUEST_ID=$4
  
3. Below command is used to print to Log File
   echo "Hi this is Kranthi!"
  
4. In Unix we can create Variables directly when evere we need and we can run any sql/plsql script directly
   like a block
  
Unixvariable =  `sqlplus -S  ${ORAUSER_PASSWD}<< ENDOFSQL
     set feedback off
     set serveroutput on
     exec APPS.Package_name.procedure_name(parameters);
     exit
     ENDOFSQL`
echo "${Unixvariable}"

5. I have text containg three value seperated by comma(,)
   -- Unixvariable = '123,234,456' 
   i want to store every value into different variable

value1 = `echo $Unixvariable | awk -F"," '{print $1}'`
value2 = `echo $Unixvariable | awk -F"," '{print $2}'`
value3 = `echo $Unixvariable | awk -F"," '{print $3}'`

6. if there is any spaces in text we can remove
 newvalue1 = `echo $value1 | sed -e 's# ##g'`

7. UNIX is power full we can track each and every step, below function works like SQL%NOTFOUND in Oracle
    here i have written script for connecting to SFTP Server, if the connection is success no issue, if connection is not established due to any problem we can track this failure by using  below command
 if [ ${?} -ne 0 ]
 
 it check most recent command is executed successfully / not.
 
 connection=`sftp "${vendor_username}"@"${vendor_host}" << END
    cd "${Direcoty Address}"
    lcd "$CUSTOM_TOP/bin"
    GET *.csv
    quit
    END`
 #Check Log File Exist Before Validation Start
 if [ ${?} -ne 0 ]
 then
    echo "Unable to connect to destination server."
 echo "${connection}"
    exit 2 
    fi
 
8. We can call the SQL*LOADER Programs from UNIX Script easily

   CONTROL_STATUS=`sqlldr userid=${ORAUSER_PASSWD}            control=$CUSTOM_TOP/bin/control_file.ctl data=datafile.csv log=$APPLCSF/log/datafile.log bad=$APPLCSF/log/datafile.bad<< ENDOFSQLLDR
   ENDOFSQLLDR`
   echo "${CONTROL_STATUS}"

9. We can capture the date into variable

   currdate = `date +%d_%m_%g_%H_%M_%S`

10. We can create Files into the server using unix script in an easy manner
    `sqlplus -S ${ORAUSER_PASSWD} >> Kranthi.csv <<EOF
 set head off;
 set pages 0;
 set linesize 2000;
 set feedback off;
 SELECT  'Emp Number'||','||
   'Emp Name' ||','||
      'Emp Sal'
 from dual
 /

 exit
 EOF`
 
11. Move the All ".csv" Files from Source Directory to Destination Directory
    cd "Source_Direcoty"
    find ./ -type f -name "*.csv" -print | xargs -l56 -i mv -f {} "Destination_Directory"
 
12. We can create Functions and we can pass parameters to Functions, here i am passing one parameter to Function
    Delete_Files()
    {
    cd $1
    echo "${PWD}"
    fileArray=($(find *.csv -mtime +10))
    filescount=${#fileArray[@]}
    for (( i=0; i<${filescount}; i++ ));
    do
    echo "${fileArray[$i]}"
    rm -f ${fileArray[$i]}
    echo "File deleted from Folder"=${fileArray[$i]}
    done
   }
   echo "Searching Files in bin direcory older than 10 Days...."
   Delete_Files $CUSTOM_TOP/bin

13. We can apply file permission(777) to all subfolders by using this command
    chmod -R 777 *
 
14. If i have a File name kranthi.csv i want to split the name and file extenstions into multiple variables
  
     datafile = 'kranthi.csv'
 
  filename = ${data_file%.*}
  fileextension = ${data_file#*.}
      
15. Control Structres in UNIX
  
     if test condition
      then
        statements;
  fi
 
  if [condition];
  then
       statements;
     else
       statements;
     fi
 
  if [condition];
  then
     statements;
     elif
     then
       statements;
  else
        statements;
     fi
 

     for expression
     do
    statements;
     done

16. File Comparisions
    -eq equal to
 -ne not equal to
 -lt less than
 -le less than or equal to
 -gt greater than
 -ge greater than or equal to
 

FND LOAD SCRIPTS

1.  FND Lookups

FNDLOAD apps/appspwd 0 Y DOWNLOAD $FND_TOP/patch/115/import/aflvmlu.lct   Lookup_filename.ldt FND_LOOKUP_TYPE APPLICATION_SHORT_NAME ='PO' LOOKUP_TYPE="Lookup_name"

FNDLOAD apps/appspwd 0 Y UPLOAD $FND_TOP/patch/115/import/aflvmlu.lct Lookup_filename.ldt 

2.  Form Functions

FNDLOAD apps/appspwd 0 Y DOWNLOAD $FND_TOP/patch/115/import/afsload.lct Function_filename.ldt FUNCTION FUNCTION_NAME="Function_name" 

FNDLOAD apps/appspwd 0 Y UPLOAD $FND_TOP/patch/115/import/afsload.lct Function_filename.ldt 

 3.   Menus

FNDLOAD apps/appspwd O Y DOWNLOAD $FND_TOP/patch/115/import/afsload.lct Menu_filename.ldt MENU MENU_NAME="Menu_name"

FNDLOAD apps/appspwd O Y UPLOAD $FND_TOP/patch/115/import/afsload.lct Menu_filename.ldt 

4.  Value Sets

FNDLOAD apps/appspwd 0 Y DOWNLOAD $FND_TOP/patch/115/import/afffload.lct Valueset_filename.ldt VALUE_SET FLEX_VALUE_SET_NAME="Valueset_name" 

FNDLOAD apps/appspwd 0 Y UPLOAD $FND_TOP/patch/115/import/afffload.lct Valueset_filename.ldt 

5.  Concurrent Programs

FNDLOAD apps/appspwd O Y DOWNLOAD $FND_TOP/patch/115/import/afcpprog.lct AP_INVOICE_GENERATION.ldt PROGRAM APPLICATION_SHORT_NAME="PO" CONCURRENT_PROGRAM_NAME="AP_INVOICE_GENERATION"

FNDLOAD apps/appspwd O Y UPLOAD $FND_TOP/patch/115/import/afcpprog.lct AP_INVOICE_GENERATION.ldt 

6.  Request Group

FNDLOAD apps/appspwd O Y DOWNLOAD $FND_TOP/patch/115/import/afcpreqg.lct REPORTS.ldt REQUEST_GROUP REQUEST_GROUP_NAME="REPORTS" APPLICATION_SHORT_NAME="PO"

FNDLOAD apps/appspwd O Y UPLOAD $FND_TOP/patch/115/import/afcpreqg.lct REPORTS.ldt

7.   Responsibilities

FNDLOAD apps/appspwd O Y DOWNLOAD $FND_TOP/patch/115/import/afscursp.lct XX_BUYER.ldt FND_RESPONSIBILITY RESP_KEY="XX_BUYER"

FNDLOAD apps/appspwd O Y UPLOAD $FND_TOP/patch/115/import/afscursp.lct XX_BUYER.ldt

8.Messages

FNDLOAD apps/appspwd O Y DOWNLOAD $FND_TOP/patch/115/import/afsload.lct abcd.ldt MENU MENU_NAME="abcd"

Bounce Apache server in R12

First Check the active user in the Application by Running Below Query in TOAD/Sql Developer.


select fnd.user_name,
       icx.responsibility_application_id,
    icx.responsibility_id,
    frt.responsibility_name,
       icx.session_id,
    icx.first_connect,
       icx.last_connect,
       DECODE ((icx.disabled_flag),'N', 'ACTIVE',
                                'Y', 'INACTIVE') status
from   fnd_user fnd,
       icx_sessions icx,
    fnd_responsibility_tl frt
where  fnd.user_id = icx.user_id
and icx.responsibility_id = frt.responsibility_id
and icx.disabled_flag <> 'Y'
and trunc(icx.last_connect) = trunc(sysdate)
order by icx.last_connect;


If no active users are there, then you can follow the below process otherwise any user is active in Application will get Connection Error

Server Bouncing for OAF Customizations: 



we need to run two scripts:

1)Script which is responsible for bouncing Oracle HTTP Server (adapcctl.sh)
2)Script which responsible for bouncing OC4J container (adoacorectl.sh)

So basically, here is the sequence of steps you need to do :
1) Connect to Host Server using Putty
2) Change the Directory by using this command  cd $ADMIN_SCRIPTS_HOME
3) And Execute below Commands in current Directory
   3.1) adapcctl.sh stop
   3.2) adoacorectl.sh stop
   3.3) adapcctl.sh start
   3.4) adoacorectl.sh start