Compile 10g Forms/Reports Takes a Very Long Time against 11g database

Compile 10g Forms/Reports Takes a Very Long Time against 11g database

Hi All

Here the new issue that I found during upgrade to R12 with 11g Database.
As you know the biggest part of patches are generating Forms and Reports in the end of patch installation. This procedure was very slow and takes a lot of time.

After some search in Metalink and Google I found the Doc ID 880660.1 - Compilation Against a 11g Database Hangs or Takes a Very Long Time (Thanks to Harry Tieb).

Action Plan 

1. Apply the database Patch 8560951 on top of your Database. 
   The Patch 8560951 brings modifications in a sensible area and it is needed to use_FIX_CONTROL to enable the fix. 

*** This patch is already included in higher database versions (e.g. 10.2.0.5, 11.2.0.2). For these it's not necessary to install the patch.  But, _FIX_CONTROL='8560951:ON' still needs to be set as the fix is disabled by default. 

2. ALTER SYSTEM SET "_FIX_CONTROL"='8560951:ON'; - This will enable the fix in memory. 
OR add this parameter to init.ora file and restart the database.

3. Compile the Forms/Reports again.
    If needed to restore things as they were, you can similarly turn the fix off with:
    ALTER SYSTEM SET "_FIX_CONTROL"='8560951:OFF';
OR 
Use the following workaround: 

1. Connect to the DB with SQL*Plus as the user who compiles the Forms application 
2. Use the following command to create a synonym all_objects with:
    create synonym all_objects for sys.dba_objects; 
    If it's not working, grant SELECT privelege on sys.dba_objects to the user who will be  compiling the form.
3. Compile the Forms/Reports again.
    If you want at the end, you can drop this synonym with:  drop synonym all_objects;

Taken from http://dba-story.blogspot.co.il/2012/02/compile-10g-formsreports-takes-very.html

WebADI Run-time error'1004' Method 'VBProject' of object '_Workbook' failed

ISSUE:

Run-time error '1004':

Method 'VBProject' of Object'_Workbook' Failed
this issue reproduce when downloading the excel sheet via webADI .

SOLUSTION:
In Excel 2003:
go to "Tools" > "Macro" > "Security" and select the "Trusted Sources" tab.  Check the box next to "Trust access to Visual Basic Project".
(Note: The box for "Trust all installed add-ins and templates" should also be checked.)

In Excel 2007, use the following navigation:

·         Click on the Office button in the upper left corner

·         Click on the Excel Options button

·         On the left, click on the Trust Center

·         Click on the Trust Center Settings button

·         On the left, click on Macro Settings

·         Click on "Trust access to the VBA project object model"

Ref: 376013.1
       406526.1      

Turn off user password expiration in ORACLE

Hi,

First of all you can check if user password not expire:
 
SELECT LIMIT FROM dba_profiles WHERE resource_name ='FAILED_LOGIN_ATTEMPTS' 
and PROFILE = (select profile from dba_users where username = 'SCOTT');

Replace scott with user you are interested in , if it is unlimited or no rows selected it means it wont expire.

You can disable passsword expiration for specified user:

SELECT PROFILE FROM dba_users WHERE username = 'User you are interested in';

When you know profile name, you can change it:

ALTER PROFILE <profile_name> LIMIT FAILED_LOGIN_ATTEMPTS UNLIMITED;
ALTER PROFILE <profile_name> LIMIT PASSWORD_LIFE_TIME UNLIMITED;

Best regards.

SQL SERVER – FIX : Error 15023: User already exists in current database.

February 15, 2007 by pinaldave

Error 15023: User already exists in current database.

1) This is the best Solution.
First of all run following T-SQL Query in Query Analyzer. This will return all the existing users in database in result pan.
USE YourDB
GO
EXEC sp_change_users_login 'Report'
GO

Run following T-SQL Query in Query Analyzer to associate login with the username. ‘Auto_Fix’ attribute will create the user in SQL Server instance if it does not exist. In following example ‘ColdFusion’ is UserName, ‘cf’ is Password. Auto-Fix links a user entry in the sysusers table in the current database to a login of the same name in sysxlogins.
USE YourDB
GO
EXEC sp_change_users_login 'Auto_Fix', 'ColdFusion', NULL, 'cf'
GO

Run following T-SQL Query in Query Analyzer to associate login with the username. ‘Update_One’ links the specified user in the current database to login. login must already exist. user and login must be specified. password must be NULL or not specified
USE YourDB
GO
EXEC sp_change_users_login 'update_one', 'ColdFusion', 'ColdFusion'
GO

2) If login account has permission to drop other users, run following T-SQL in Query Analyzer. This will drop the user.
USE YourDB
GO
EXEC sp_dropuser 'ColdFusion'
GO

Create the same user again in the database without any error.

How to create Backup & Delete all rows from a table

Hello,

Sometime you should make backup for a table and then delete all from original table.
You can do it which next steps.

First of all you have check data in original table:
1. select * from CHOICE_VALUES

When you know count of rows this is a time for backup table:
2. create table CHOICE_VALUES_backup as select * from  CHOICE_VALUES;

Delete old data from original table.
3. delete from CHOICE_VALUES;
4. commit;

Good luck....

How to kill terminating/long running/hang request oracle

Action:

1) #First Terminate the Request as follows

         update fnd_concurrent_requests

         set status_code='X', phase_code='C'

         where request_id=10122488;

         commit;

2) #Then change the status with Completed-Error as follows.

         update fnd_concurrent_requests

         set status_code='E', phase_code='C'

         where request_id=10122488;

         commit;

#This will change the status of any request.

#Status Code

E -  Error

X -  Terminate

G -  Warning

Session timeouts, profile options and adconfig for the Oracle eBusiness Suite

Symptoms:

Your log on session is no longer valid. Would you like to log back in so you can continue working? If you do not log in, any outstanding data changes in your forms will not be saved.

Your Session has expired, please login again

Solution:
Set the values of the following profile options appropriately. See Note:171261.1 for details:


ICX:Session Timeout
E.g. From 30 to null (minutes)
ICX: Limit time
E.g. From 10 to 24 (hours)

Remember: When you refresh or clone Test / Development environments the setting for ICX:Session Timeout gets reset!

Also you can solve the issue in $CONTEXT_FILE
located in directory $APPL_TOP/admin/$CONTEXT_NAME.xml.

<session_timeout oa_var="s_sesstimeout">1800000</session_timeout>

And set to the appropriate value. Note that s_sesstimeout is in milliseconds so multiple by (1000*60) to get the time in minutes that is represented in the profile option.
E.g. if you want to set it to 60 minutes then in the context file put value 60 * 1000 * 60 = 3600000. Either that or just set to null:

<session_timeout oa_var="s_sesstimeout">1800000</session_timeout>

Then next time you run adconfig.sh (or a patch/clone does) then you won't have to reset profile options.

How To EXPORT/IMPORT Full DB in ORACLE

Hi,
If you'd like to make full export for your oracle DB, you can use "EXP" command for this.
Here you see examples for "exp" command:

exp system/manager@xyz FULL=Y FILE=FULL.DMP

Here is EXPORT example by OWNER:
exp system@manager@xyz FILE=File_name.dmp OWNER=Owner_Name


You should drop tables before import:
Create file drop_list.sql


set head off
set pagesize 0
set verify off
set feedback off
spool drop_objects.sql
select 'drop table '||table_name ||';' 
from user_tables
/
spool off

Connect with user/password and run this script: sqlplus owner/owner @drop_list.sql
The script will create file drop_objects.sql - this is a list of tables that should be deleted.



When you should import dumped file to other database you can use "IMP" command.
imp system/manager@xyz FULL=Y FILE=FULL.DMP


Here is IMPORT example by OWNER:
imp system@manager@xyz FILE=File_name.dmp OWNER=Owner_Name


Also you can export/import specific schema with same commands with out "FULL" prefix.

How to shrink LOG file in MS-SQL Server 2005/2008

Hi,

There was a problem with Log file was bigger that 40Gb  in my DB.

With these steps I managed to shrink a file:


USE [DBNAME]
GO
CHECKPOINT -- Write checkpoint to the log file, and force SQL to write all dirty pages to the database.
GO
ALTER DATABASE [DBNAME] SET RECOVERY SIMPLE -- Set database to Simple recovery, essentially truncating all the non-active part of the transaction log file.
GO
ALTER DATABASE [DBNAME] SET OFFLINE WITH ROLLBACK IMMEDIATE -- Kill all active transactions/connections rolling back any changes that are outstanding.
GO
ALTER DATABASE [DBNAME] SET ONLINE -- Bring database back online (should have no active connection now).
GO
DBCC SHRINKFILE('LOGICAL_FILE_NAME',0) -- Since we have killed all active transactions, and we have set database to simple recovery mode; all VLF should be in inactive state now; and you should be able to shrink the file.
GO 


Of course you should change backup recovery mode to FULL after this steps.

How to check CPU temperature in Linux

Hi,

If you should check CPU Temperature in Linux
You can use follow command:

cat /proc/acpi/thermal_zone/THM0/temperature
or
cat /proc/acpi/thermal_zone/*/temperature

Good Luck...

How to create user in Linux.

In this example I'm creating user applprod with bash login option.
You should only change user name & run it with root permissions.

useradd -gdba -s/bin/bash -papplprod -d/home/applprod -m applprod
useradd -gdba -s/bin/bash -poraprod -d/home/oraprod -m oraprod

Starting Database Listener gives "Linux Error: 32: Broken pipe"

After attempting to start the listener it is giving following errors
TNS-12547:TNS:lost contact
TNS-12560:TNS:protocol adapter error
TNS-00517:lost contact
Intel SVR4 UNIX Error: 32: Broken pipe

To resolve this issue you should find listener log files location.
1. Login with "ORAUSER" and load environment for this user.
2. cd $TNS_ADMIN
3. Open listener.ora file and find "LOG_DIRECTORY" path.
4. cd to directory found in previous step
5. Check if log size (if log file bigger that 2Gb please delete this file)
6. Start listener.

How to clear ALL Oracle Application Cache.

1. Login to application with SYSADMIN or different user with sysadmin responsibility.

2. Navigate to -> Functional Administrator

3. Press -> Core Services

4. Press -> Caching Framework

5. Press ->  Global Configuration

6. Press -> Clear All Cache

7. Press -> Yes

8. Press -> Apply

Good Luck...

How to run gather statistics on a table.

You can use analyze command:

analyze table TABLE_NAME compute statistics  
or
analyze table TABLE_NAME estimate statistics 

also you can use "dbms_stats package" follow:

exec dbms_stats.gather_table_stats('ownername','tablename')

R12 Renders "Blue" Screen for Users in Right-to-Left Language Enabled Environments

After bouncing Apache and OC4J processes in Release 12 environments the users experience that colors in OA Framework pages are not appear as expected from the SWAN Look and Feel, but have a dark blue background and font(size) used may be smaller or larger.

This problem is experienced in environment having Right-to-Left languages installed like Arabic or Hebrew.

Although E-Business Suite functionality still works as expected the pages are harder to read and causing problems for the users.

Examples:

Wrong:

Good:

The problem is caused by incorrect invalidation of cached information in the UIX layer used to generate the Look-and-Feel of the OA Framework page when switching from LTR to RTL language. Due to this the HTML generated references the wrong style-sheet object and uses this definition (color, font, font size)  instead of the correct style-sheet object.

Solution:

The fix in the UIX layer is delivered in <Patch:10335521> R12.FWK.B THE APPLICATIONS BACKGROUND TURNS COMPLETELY BLUE INTERMITTENTLY.

ORACLE ID:

R12 Renders "Blue"Screen for Users in Right-to-Left Language Enabled Environments [ID 1403454.1]

SQL Server 2005/2008 – Renaming database and physical database files using T-SQL

Step1: Change Database Name



To Rename a database using T-SQL, use below script:

USE [master]

– Set Database to Single-User Mode

ALTER DATABASE [DBNAME] SET  SINGLE_USER WITH ROLLBACK IMMEDIATE

– Rename Database

ALTER DATABASE [DBNAME] MODIFY Name = [DBNAME2]

– Set Database to Multi-User Mode

ALTER DATABASE [DBNAME2] SET  MULTI_USER WITH ROLLBACK IMMEDIATE

Step2: Rename Files.

ALTER DATABASE [ DBNAME2 ]

            MODIFY FILE (NAME='DBNAME', NEWNAME='DBNAME2.mdf')

ALTER DATABASE [ DBNAME2 ]

            MODIFY FILE (NAME='DBNAME_log', NEWNAME='DBNAME2_log')





However, this method can change the physical database file name, you can check this:


SELECT      name, physical_name

FROM        [DBNAME].sys.database_files

Error while opening another instance using the "New Tab" feature in the same IE8 session

Issue while using the NEW TAB in IE8 to open the R12 instance, when another 11i instance is already open in the same browser session.[ID 1322928.1]

Getting the following error:
   Unable to authenticate session.



To implement the solution, perform the following in the second instance:

1. Update SESSION_COOKIE_NAME to a different value than the other instance (it can be set to the same value as the instance name):

update ICX_PARAMETERS
set SESSION_COOKIE_NAME = 'DEV';

commit;

2. Bounce the instance (apps and db).

3. Retest the issue.

Example for creating dblink in Oracle.

You should change all things that bolded:

create public database link TEST
connect to username identified by password
 using
'(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(Host=IP or hostname)(Port=1521)))(CONNECT_DATA=(SID=TEST)))'

If you would like to drop link, you can use follow:

drop public database link TEST

Create User in Oracle Database

For user creating you should create default TABLESPACE for this user or use current one.

You can create it follow:


1) First step you create tablespace for this.
create tablespace TEST1
datafile '/d01/oracle/oradata/test01.dbf'
size 100m;

2) Now you can create user:

CREATE USER test1 IDENTIFIED BY password

       DEFAULT TABLESPACE test1  

       TEMPORARY TABLESPACE temp

       QUOTA UNLIMITED ON test1;

3) After this you can give him permissions:

GRANT CREATE session, CREATE table, CREATE view, CREATE resource, 

      TO test1;

Adding Responsibility to an Application User for Oracle E-Business Suite Application

Step 1: Login with 'sysadmin' user into Oracle E-Business Suite


Step 2: Go to Security->User->Define.

Step 3:
In open window press F11 for search user.

Step 4: Write name of user and press CTRL + F11.

Step 5: In this step you should press on one of responsibilities and press NEW menu button. 

Step 6: The windows will be change view for one like this. Please press on ... (red marked) 

Step 7: Write needed responsibility name and press find.

Step 8: After you find needed responsibility and add it, press save.

Step 9: Press Switch Responsibility button.

Step 10:  Now you can change your responsibility to new one. 

GOOD LUCK....

How to find large directories in Linux

Use this simple command to find large directories. To find directories over 1GB

[root@localhost]# du -h / | grep ^[0-9.]*G

or

[root@localhost]#find / -type d -size +1G

How to compile Oracle Apps Forms 11i/R12

1. Login in Forms Server as APPLUSER and run .evn to set environment.

2. cd  $AU_TOP/forms/US

3. If you on 11i version use following command:
    f60gen module=XXX.fmb userid=apps/apps output_file=XXX.fmb

4. If you on R12 version use following command:
    frmcmp_batch.sh module="XXX.fmb" userid=APPS/APPS output_file="XXX.fmb" module_type=form
   OR
    frmcmp module="XXX.fmb" userid=APPS/APPS output_file="XXX.fmx" module_type=form

Oracle DB&APPL Prerequisite RPM's RedHat5

RedHat5:

OS packages:
------------
compat-libstdc++-egcs-1.1.2-1
compat-libstdc++-33-3.2.3-6
compat-libstdc++-296-2.96-138
compat-libcwait-2.1-1
compat-oracle-el5-1.0-5
openmotif21-2.1.30-11.EL5
binutils-2.15
The following packages must be installed from the OEL 5 or RHEL 5 distribution media:

libXp-1.0.0-8.1.el5
compat-libgcc-296-2.96-138
compat-libstdc++-33-3.2.3-61
compat-db-4.2.52-5.1

How to check "Actual" & "Needed" Undo size

SELECT d.undo_size/(1024*1024) "ACTUAL UNDO SIZE [MByte]",
       (TO_NUMBER(e.value) * TO_NUMBER(f.value) *
       g.undo_block_per_sec) / (1024*1024)
      "NEEDED UNDO SIZE [MByte]"
  FROM (
       SELECT SUM(a.bytes) undo_size
         FROM v$datafile a,
              v$tablespace b,
              dba_tablespaces c
        WHERE c.contents = 'UNDO'
          AND c.status = 'ONLINE'
          AND b.name = c.tablespace_name
          AND a.ts# = b.ts#
       ) d,
      v$parameter e,
       v$parameter f,
       (
       SELECT MAX(undoblks/((end_time-begin_time)*3600*24))
         undo_block_per_sec
         FROM v$undostat
       ) g
 WHERE e.name = 'undo_retention'
  AND f.name = 'db_block_size'

How to copy MS SQL 2008 database to another Server

1. Start Microsoft SQL Server Management Studio

2. Right click on DB what you'd like to copy.

3. Click NEXT in next window.

4. Choice source Server. (Remember to select Windows or SQL Server Authentication)

5. Choice Destination Server

6. You can choice two different ways of DB copy.

I   - Use the detach and attach method.

II  - Use the SQL Management Object Method.

My recommendation to use "detach and attach method" this is faster method.

7. Choice Destination Database name.

  There is two options:

   *Stop the transfer if a database or file with the same name exists at the destination.

     (if you copy DB to new server you can select this option)

   

   *Drop any database on destination server with the same name,

     then continue with database transfer, overwriting exist database files.

     (if you copy DB and you want to overwrite exist DB, select this option.) 

8. Select Server Objects

    Please check if all DB users selected. 

    

9. Select database files (*.mdf, *.log) provide full path for files.

10. In next window press "Next"

11. Database copy Scheduling.

12. The next screen shows the summary information before the database copy.

      Click on "Finish" button and wait until the copy finish.

Good Luck.

How to connect external USB disk to Linux server:

Before connect USB Disk

 1.Open /var/log/messages file

     tail -f /var/log/messages

 2.Connect disk.

 3.Find new device name (example /dev/sda ) in messages file.

After you know name of the disk (example /dev/sda)

   1. fdisk -l (list partitions)

   2. d - delete partition

   3. n - add new partition

   4. p - primary

Make file system

   1. mkfs -t ext3 /dev/sda
   2. vi /etc/fstab
   3. add mount point to /etc/fstab (example /dev/sda       /d01         ext3    defaults        1 1)
   4. save /etc/fstab
   5. mount -a

How to clear Apache cache in Oracle Application R12

How to clear Apache cache in Oracle Application R12

1. Login to Linux with "appluser" account and load environments.
2. Shutdown Apache server by running $INST_TOP/admin/scripts/adapcctl.sh stop
3. cd $OA_HTML/cabo/styles/cache
4. remove all files from this directory. (rm -rf *)
5. cd $OA_HTML/cabo/images/cache
6. remove all files from this directory. (rm -rf *)
7. Start Apache server by running $INST_TOP/admin/scripts/adapcctl.sh start