Manual compilation of INVALID objects in Oracle

#TO Compile Package 

ALTER PACKAGE schema.package COMPILE;

#To Compile Package Body 

ALTER PACKAGE schema.package COMPILE BODY;

#To Compile Synonym

ALTER SYNONYM schema.synonym compile;

#To Compile Procedure 

ALTER PROCEDURE schema.procedure COMPILE;

#To compile Function 

ALTER FUNCTION schema.function COMPILE;

#To Compile Trigger 

ALTER TRIGGER my_trigger COMPILE;

#To Compile View 

ALTER VIEW schema.view COMPILE;

#To Compile Materialized view

ALTER MATERIALIZED VIEW schema.view compile; 

#To Compile Public Synonym

ALTER PUBLIC SYNONYM my_synonym compile;

Script to check number of user hits on a Oracle EBS server on Monthly Basis

Script to check number of user hits on a Oracle EBS server on Monthly Basis:-

monthly_hits.sh

TIME=`date +%y%m%d%H`
export TIME
file_name=VIS_$TIME.txt
export file_name
touch $file_name
touch $ip_files.txt
SCR_DIR=/u01/oracle/VIS/inst/apps/VIS_hostname/logs/ora/10.1.3/Apache
curmth=`date +%m`
curyr=`date +%Y`
prevyr=$((curyr - 1))
echo $curmth
echo $curyr
echo $prevyr

mth=('Dec' 'Jan' 'Feb' 'Mar' 'Apr' 'May' 'Jun' 'Jul' 'Aug' 'Sep' 'Oct' 'Nov')
export MONTH='Jan'

ls -lrt $SCR_DIR|grep "access_log*"|grep "$MONTH"|awk '{print $9}'|sed -e "s/^/\/u01\/oracle\/VIS\/inst\/apps\/VIS_hostname\/logs\/ora\/10.1.3\/Apache\//g" >ip_files_$MONTH.txt
#ls -lrt $SCR_DIR|grep "access_log*"|grep "Apr"|awk '{print $9}'|sed -e "s/^/\/h
if [ $curmth == 01 ]
then
 for i in `cat ip_files_$MONTH.txt`
        do
        cat "$i"|grep "AppsLocalLogin"|grep "$MONTH"|grep $prevyr|awk '{print $1 $4}'|sed -e 's/:/ /g'|sed -e 's/\[/ /g'|sed -e 's/\//-/g'|awk '{print $2"," $1 ",VIS,EBS,US,EBS Sandbox"}'>>$file_name
        done

else
        for i in `cat ip_files_$MONTH.txt`
        do
 cat "$i"|grep "AppsLocalLogin"|grep "$MONTH"|grep $curyr|awk '{print $1 $4}'|sed -e 's/:/ /g'|sed -e 's/\[/ /g'|sed -e 's/\//-/g'|awk '{print $2 "," $1 ",VIS,EBS,US,EBS  Sandbox"}'>>$file_name
        done
fi

solution for fnd_ldap_wrapper.create_user issue

from application OS user (applmgr) login to sqlplus as apps/xxxx and run the following:-

alter package APPS.fnd_user_pkg compile;
alter package APPS.fnd_user_pkg compile body;

expdp/impdp in Oracle Database

Login to database as 'sys' user and execute

CREATE OR REPLACE DIRECTORY dmpdir AS 'D:\Backup';
GRANT READ, WRITE ON DIRECTORY dmpdir TO system;


expdp system/xxxx@SID full=Y directory=dmpdir dumpfile=full_sid.dmp logfile=full_exp_sid.log


impdp system/xxxx@SID full=Y directory=dmpdir dumpfile=full_sid.dmp logfile=full_imp_sid.log

CUSTOM_TOP creation in Oracle e-Business Suite

CUSTOM_TOP Creation :-

1) Create Read only user (appldev) on os level

2) appldev user id should be member of appldev group
   applmgr need to be secondary member of appldev group
  
ex:
id applmgr
uid=504(applmgr) gid=501(dba) groups=501(dba),503(appldev)


3) Create following structure with Developer userid (appldev) at home location(/home/appldev) at   OS level:

Step 1> Create the directory structure.
mkdir -p XXCUS
mkdir -p XXCUS/12.0.0/admin/sql
mkdir -p XXCUS/12.0.0/admin/odf
mkdir -p XXCUS/12.0.0/sql
mkdir -p XXCUS/12.0.0/bin
mkdir -p XXCUS/12.0.0/reports/US
mkdir -p XXCUS/12.0.0/forms/US
mkdir -p XXCUS/12.0.0/lib
mkdir -p XXCUS/12.0.0/out
mkdir -p XXCUS/12.0.0/log
mkdir -p XXCUS/12.0.0/java/classes


4) Create a soft link using the following command(Login as applmgr user and go to $APPL_TOP)

 ln -s /home/appldev/XXCUS XXCUS



5) Login to oracle user and connect to database,Now create a tablespace and schema for custom top (XXCUS).

create tablespace XXCUS datafile '/u01/oracle/VIS/db/apps_st/data/XXCUS01.dbf' size 1000m;
create user XXCUS identified by XXCUS default tablespace XXCUS;
grant connect, resource to XXCUS;

6) Register and add datagroup of custom user and custom Application with Oracle application using system admin responsibility.

Security-->Oracle-->Register (register XXCUS user)
Naviate to Security-->Oracle-->Register
Database User Name = XXCUS
Password = XXCUS
Privilege = Enabled
Install Group = 0
Description = XXCUS Custom Application User

Application-->Register (register custom application)
Navigate to Application-->Register
Application = XXCUS Custom Application
Short Name = XXCUS
Basepath = XXCUS_TOP
Description = XXCUS Custom Application

Security-->Oracle-->DataGroup (add custom application under datagroup)
Navigate to Security-->Oracle-->DataGroup
search for "standard" data group.
add new entry and search for the application name ->XXCUS Custom Application
Oracle ID->APPS


7) ADD the custom top entry in enviromnet file of applmgr

XXCUS_TOP="/u01/oracle/VIS/apps/apps_st/appl/XXCUS/12.0.0"
  export XXCUS_TOP

8) Login as APPLMGR os user and run following

chmod 755 $ORACLE_HOME/bin/sqlplus
chmod 755 $ORACLE_HOME/sqlplus
chmod 755 $ORACLE_HOME/sqlplus/mesg
chmod 755 $ORACLE_HOME/sqlplus/mesg/sp1us.msb
chmod 755 $ORACLE_HOME/nls
chmod 755 $ORACLE_HOME/nls/data
chmod 755 $ORACLE_HOME/nls/data/lx1boot.nlb
chmod 755 $ORACLE_HOME/oracore
chmod 755 $ORACLE_HOME/oracore/zoneinfo
chmod 755 $ORACLE_HOME/oracore/zoneinfo/timezlrg.dat
chmod -R 777 $ORACLE_HOME/forms/admin/terminal

9) Login as appldev user and edit bash_profile with the environment details of applmgr

Shell script for startup & shutdown of database

Shutting Down and starting up of Oracle Database using shell scripts:-

1.       vi dbstop.sh

export ORACLE_HOME=/u01/oracle/product/11.1.0/db (Path of Oracle Home)
export PATH=$ORACLE_HOME/bin:$PATH
export ORACLE_SID= SID
lsnrctl stop SID
sqlplus "/ as sysdba"<<EOF
shutdown immediate
EOF

2.       vi dbstart.sh

export ORACLE_HOME=/u01/oracle/product/11.1.0/db
export PATH=$ORACLE_HOME/bin:$PATH
lsnrctl start  SID

export ORACLE_SID= SID

sqlplus "/ as sysdba"<<EOF
startup
EOF