E-Business Suite R12 Change the IP address

1) Change the IP address at server level

2) Update the hosts file

	 /etc/hosts

3) Check the Current IP
connect to DB as APPS user

select NODE_NAME,SERVER_ADDRESS, HOST, DOMAIN, VIRTUAL_IP from fnd_nodes;

4) STOP DB and APPLICATION services
5) Clean up the IP Address

perl $AD_TOP/bin/adgentns.pl appspass=appspasswd contextfile=$APPL_TOP/admin/SID_hostname.xml -removeserver

connect with sqlplus apps user and run the following

sql> begin
FND_NET_SERVICES.remove_server('SID', 'hostname');
end;
sql>/
sql>commit;
sql>/
sql>exec fnd_conc_clone.setup_clean;
sql>commit;
sql>exit;

6) Run Autoconfig On the database tier first

RDBMS ORACLE_HOME/appsutil/scripts/Context_name/adautocfg.sh

7) Run Autoconfig On the Apps Tier

COMMON_TOP/admin/scripts/Context_name/adautocfg.sh

8) Check the IP (NEW IP Address got updated) connect to DB as APPS user

select NODE_NAME,SERVER_ADDRESS, HOST, DOMAIN, VIRTUAL_IP from fnd_nodes;

9) START DB and APPLICATION services

~~ Cheers

Script to Get Data for SR Update Apache, Java, Client JRE, Forms, Perl, PL/SQL versions and Forms communication mode & Oracle Patch information

1) Apache, Java, Client JRE, Forms, Perl, PL/SQL versions and Forms communication mode

 
#!/bin/bash
# +===========================================================================+ 
# | FILENAME 
# | Instancedetails_R12.sh 
# | 
# | DESCRIPTION 
# |   This script can be used to collect Apache, Jserv, Forms, Jinitiator and   
# |   Perl version in E-Business suite R12 
# | 
# | PLATFORM 
# | Unix Generic, Linux, HP-UX,IBM AIX, Sunsolaris  
# | 
# | NOTES 
# |   Before running the script ensure that you have sourced the environment  
# |   by running APPS_host.env file from $APPL_TOP.   
# | 
# | HISTORY 
# |   Version 1.0
# +===========================================================================+ 
# 
#--------------------------------------------------------------------
# CONSTANTS

TEMP_FILE=/tmp/`hostname`_`date +%m%d%y.%H%M`.txt
ID1=dba@oncalldba.com

#--------------------------------------------------------------------
# MAIN
( 
echo "Script Started " 
date 
## The script shows output on screen and creates file named ${TEMP_FILE} file in /tmp  ## directory 
## Collect the Apache version 
echo "*****Apache Version*****" 
$IAS_ORACLE_HOME/Apache/Apache/bin/httpd -v 
echo " " 
## Collect perl version 
echo "*****perl version******" 
$IAS_ORACLE_HOME/perl/bin/perl -v|grep built 
echo " " 
## Collect Java version 
echo "******Java Version******" 
sh -c "`awk -F= '$1 ~ /^JSERVJAVA.*$/ {print $2}' $ADMIN_SCRIPTS_HOME/java.sh` -version;" 
echo " " 
## Collect client JRE version 
echo "*****Client JRE version*****" 
cat $FORMS_WEB_CONFIG_FILE|grep sun_plugin_version| cut -c 1-35 
echo " " 
## Collect Forms version 
echo "*****Forms Version*****" 
$ORACLE_HOME/bin/frmcmp_batch|grep Forms| grep Version 
echo " " 
## Collect PL/SQL Version 
echo "*****PL/SQL Version****" 
$ORACLE_HOME/bin/frmcmp_batch|grep PL/SQL|grep Version 
echo " " 
## Collect Forms communication mode 
echo "****Forms Communication Mode****" 
cat $FORMS_WEB_CONFIG_FILE|grep serverURL= 
echo "If the serverURL parameter has no value then Forms is implemented in socket mode else it is servlet" 
echo " " 
echo "Script Completed Successfully and it has generated the file  ${TEMP_FILE} in /tmp directory" 
echo "Script completed " 
date 
) 2>&1 | tee $TEMP_FILE
###
### END OF SCRIPT
###
# Use This to get the File to Inbox
uuencode $TEMP_FILE $TEMP_FILE |mail -s "${TWO_TASK} Details for SR Update" $ID1
# This Can be used if Mailx is activated for the host

2) Oracle Home & OPatch Information

 
#!/bin/bash
#===================================================================#
#                                                                   #
# Name     : sr_update.sh                                           #
# Synopsis : Gathers the  Host info and ORACLE_HOME & OPatch        #
#            Information and Stores in to File.                     #
#                                                                   #
#===================================================================#
#--------------------------------------------------------------------
# CONSTANTS

TEMP_FILE=/tmp/sr_update.txt
ID1=dba@oncalldba.com

#--------------------------------------------------------------------
# MAIN

echo "############ BEGIN INFO GATHERING ############" > $TEMP_FILE 
echo "[Date]: " `date` >> $TEMP_FILE 
echo "" >> $TEMP_FILE 
echo "------------- ORACLE HOME Info ----------------" >> $TEMP_FILE 
echo "" >> $TEMP_FILE 
echo "[ORACLE_HOME]: "$ORACLE_HOME >> $TEMP_FILE 
echo "" >> $TEMP_FILE 
echo "------------ Machine Info ------------" >> $TEMP_FILE 
echo "" >> $TEMP_FILE 
echo "[Name]: " `uname -an` >> $TEMP_FILE 
echo "" >> $TEMP_FILE 
echo "----------- ORACLE_HOME Patch Info -------------" >> $TEMP_FILE 
echo "" >> $TEMP_FILE 
$ORACLE_HOME/OPatch/opatch lsinventory -details -invPtrLoc \ 
$ORACLE_HOME/oraInst.loc >> $TEMP_FILE 
echo "" >> $TEMP_FILE 
echo "############ END INFO GATHERING ############" >> $TEMP_FILE 
echo "" >> $TEMP_FILE 

# Use This to get the File to Inbox
uuencode $TEMP_FILE $TEMP_FILE |mail -s "${ORACLE_SID} Details for SR Update" $ID1
# This Can be used if Mailx is activated for the host

Metalink Note ID's R12 : -- Script to find Apache, Java, JRE, Forms version for Oracle E-Business Suite R12 (Doc ID 468311.1) 11i/R12 : -- Utility /Script To Check The Techstack Component Versions (Forms, Http Server, JDK, Framework, Database, etc) (Doc ID 601736.1) ~~ Cheers

Blocking Development Tools Like Toad to Connect PROD DB

Blocking Development Tools from Connecting from Production Database

CONNECT / AS SYSDBA;
 
CREATE OR REPLACE TRIGGER block_tools_from_prod
  AFTER LOGON ON DATABASE
DECLARE
  v_prog sys.v_$session.program%TYPE;
BEGIN
  SELECT program INTO v_prog 
    FROM sys.v_$session
  WHERE  audsid = USERENV('SESSIONID')
    AND  audsid != 0  -- Don't Check SYS Connections
    AND  ROWNUM = 1;  -- Parallel processes will have the same AUDSID's
 
  IF UPPER(v_prog) LIKE '%TOAD%' OR UPPER(v_prog) LIKE '%T.O.A.D%' OR -- Toad
     UPPER(v_prog) LIKE '%SQLNAV%' OR     -- SQL Navigator
     UPPER(v_prog) LIKE '%PLSQLDEV%' OR -- PLSQL Developer
     UPPER(v_prog) LIKE '%BUSOBJ%' OR   -- Business Objects
     UPPER(v_prog) LIKE '%EXCEL%'       -- MS-Excel plug-in
  THEN
     RAISE_APPLICATION_ERROR(-20000, 'Development tools are not allowed here.');
  END IF;
END;
/
SHOW ERRORS

~~ Cheers

Sending Mail from Oracle

1) Send Mail

Usage:

set serveroutput on

exec send_mail(msg_to =>'dba@oncalldba.com');

exec send_mail(msg_to =>'dba@oncalldba.com', -
msg_text=>'Sample email text from the Oracle database' -
);

Procedure:

CREATE OR REPLACE PROCEDURE SEND_MAIL (
  msg_from    VARCHAR2 := 'oracle',
  msg_to      VARCHAR2,
  msg_subject VARCHAR2 := 'Email alert from the Oracle database',
  msg_text    VARCHAR2 := '' )
IS
  c  UTL_TCP.connection;
  rc INTEGER;
BEGIN
  c := UTL_TCP.open_connection('127.0.0.1', 25);       -- open the SMTP port 25 on local machine
  DBMS_OUTPUT.put_line(UTL_TCP.get_line(c, TRUE));
  rc := UTL_TCP.write_line(c, 'HELO localhost');
  DBMS_OUTPUT.put_line(UTL_TCP.get_line(c, TRUE));
  rc := UTL_TCP.write_line(c, 'MAIL FROM: '||msg_from);
  DBMS_OUTPUT.put_line(UTL_TCP.get_line(c, TRUE));
  rc := UTL_TCP.write_line(c, 'RCPT TO: '||msg_to);
  DBMS_OUTPUT.put_line(UTL_TCP.get_line(c, TRUE));
  rc := UTL_TCP.write_line(c, 'DATA');                 -- Start message body
  DBMS_OUTPUT.put_line(UTL_TCP.get_line(c, TRUE));
  rc := UTL_TCP.write_line(c, 'Subject: '||msg_subject);
  rc := UTL_TCP.write_line(c, '');
  rc := UTL_TCP.write_line(c, msg_text);
  rc := UTL_TCP.write_line(c, '.');                    -- End of message body
  DBMS_OUTPUT.put_line(UTL_TCP.get_line(c, TRUE));
  rc := UTL_TCP.write_line(c, 'QUIT');
  DBMS_OUTPUT.put_line(UTL_TCP.get_line(c, TRUE));
  UTL_TCP.close_connection(c);                         -- Close the connection
EXCEPTION
  WHEN OTHERS THEN
       raise_application_error(
           -20000, 'Unable to send e-mail message from pl/sql because of: '||
           SQLERRM);
END;
/ 

2) Sending Mail with Attachments

CREATE OR replace PROCEDURE Send_mail (
msg_from    VARCHAR2 := 'dba@oncalldba.com',
----- MAIL BOX SENDING THE EMAIL
msg_to      VARCHAR2 := 'dba@oncalldba.com',
----- MAIL BOX RECIEVING THE EMAIL
msg_subject VARCHAR2 := 'Output file TEST1',----- EMAIL SUBJECT
msg_text    VARCHAR2 := 'THIS IS THE TEXT OF THE EMAIL MESSAGE.',
v_output1   VARCHAR2 :=
'THIS IS THE TEXT OF THE ATTACHMENT FILE. THIS TEXT SHOULD BE IN A TEXT FILE ATTACHED TO THE EMAIL.'
)
IS
  c    utl_tcp.connection;
  rc   INTEGER;
  crlf VARCHAR2(2) := Chr(13)
                      ||Chr(10);
  mesg VARCHAR2( 32767 );
BEGIN
    c := utl_tcp.Open_connection('192.35.140.18', 25);
    ----- OPEN SMTP PORT CONNECTION
    rc := utl_tcp.Write_line(c, 'HELO 192.35.140.18');
    ----- PERFORMS HANDSHAKING WITH SMTP SERVER
    dbms_output.Put_line(utl_tcp.Get_line(c, TRUE));
    rc := utl_tcp.Write_line(c, 'EHLO 192.35.140.18');
    ----- PERFORMS HANDSHAKING WITH SMTP SERVER, INCLUDING EXTRA INFORMATION
    dbms_output.Put_line(utl_tcp.Get_line(c, TRUE));
    rc := utl_tcp.Write_line(c, 'MAIL FROM: '
                                ||msg_from); ----- MAIL BOX SENDING THE EMAIL
    dbms_output.Put_line(utl_tcp.Get_line(c, TRUE));
    rc := utl_tcp.Write_line(c, 'RCPT TO: '
                                ||msg_to); ----- MAIL BOX RECIEVING THE EMAIL
    dbms_output.Put_line(utl_tcp.Get_line(c, TRUE));
    rc := utl_tcp.Write_line(c, 'DATA'); ----- EMAIL MESSAGE BODY START
    dbms_output.Put_line(utl_tcp.Get_line(c, TRUE));
    rc := utl_tcp.Write_line(c, 'Date: '
                                ||To_char(SYSDATE, 'dd Mon yy hh24:mi:ss'));
    rc := utl_tcp.Write_line(c, 'From: '
                                ||msg_from
                                ||' <'
                                ||msg_from
                                ||'>');
    rc := utl_tcp.Write_line(c, 'MIME-Version: 1.0');
    rc := utl_tcp.Write_line(c, 'To: '
                                ||msg_to
                                ||' <'
                                ||msg_to
                                ||'>');
    rc := utl_tcp.Write_line(c, 'Subject: '
                                ||msg_subject);
    rc := utl_tcp.Write_line(c, 'Content-Type: multipart/mixed;');
    ----- INDICATES THAT THE BODY CONSISTS OF MORE THAN ONE PART
    rc := utl_tcp.Write_line(c, ' boundary="-----SECBOUND"');
    ----- SEPERATOR USED TO SEPERATE THE BODY PARTS
    rc := utl_tcp.Write_line(c, '');
    ----- INSERTS A BLANK LINE. PART OF THE MIME FORMAT AND NONE OF THEM SHOULD BE REMOVED.
    rc := utl_tcp.Write_line(c, '-------SECBOUND');
    rc := utl_tcp.Write_line(c, 'Content-Type: text/plain');
    ----- 1ST BODY PART. EMAIL TEXT MESSAGE
    rc := utl_tcp.Write_line(c, 'Content-Transfer-Encoding: 7bit');
    rc := utl_tcp.Write_line(c, '');
    rc := utl_tcp.Write_line(c, msg_text); ----- TEXT OF EMAIL MESSAGE
    rc := utl_tcp.Write_line(c, '');
    rc := utl_tcp.Write_line(c, '-------SECBOUND');
    rc := utl_tcp.Write_line(c, 'Content-Type: text/plain;');
    ----- 2ND BODY PART.
    rc := utl_tcp.Write_line(c, ' name="Test.txt"');
    rc := utl_tcp.Write_line(c, 'Content-Transfer_Encoding: 8bit');
    rc := utl_tcp.Write_line(c, 'Content-Disposition: attachment;');
    ----- INDICATES THAT THIS IS AN ATTACHMENT
    rc := utl_tcp.Write_line(c, ' filename="Test.txt"');
    ----- SUGGESTED FILE NAME FOR ATTACHMENT
    rc := utl_tcp.Write_line(c, '');
    rc := utl_tcp.Write_line(c, v_output1);
    rc := utl_tcp.Write_line(c, '-------SECBOUND--');
    rc := utl_tcp.Write_line(c, '');
    rc := utl_tcp.Write_line(c, '.'); ----- EMAIL MESSAGE BODY END
    dbms_output.Put_line(utl_tcp.Get_line(c, TRUE));
    rc := utl_tcp.Write_line(c, 'QUIT'); ----- ENDS EMAIL TRANSACTION
    dbms_output.Put_line(utl_tcp.Get_line(c, TRUE));
    utl_tcp.Close_connection(c); ----- CLOSE SMTP PORT CONNECTION
EXCEPTION
  WHEN OTHERS THEN
             Raise_application_error(-20000, SQLERRM);
END;
/  

~~ Cheers