Oracle Gateway database link to MSSQL server failed with error "Ora-28500 error with argument 523 80 {82} "

Solution:

Check and modify the .LOC file located in Gateway_Home/<gtw>/driver to get the right PATH to the ODBC library.

On 11.5.10.2/Redhat Enterprise version 5.2, Running Autoconfig adconfig.Sh Perl, Error While Loading Shared Libraries: the libdb.so.3: can't open shared object file: no such file or directory

Solution:

with root:
create a link, ln -s /usr/lib/libdb.so.2 /usr/lib/libdb.so.3

Enjoy

How to find a concurrent program's trace file

SELECT
req.request_id
,req.logfile_node_name node
,req.oracle_Process_id
,req.enable_trace
,dest.VALUE||'/'||LOWER(dbnm.VALUE)||'_ora_'||oracle_process_id||'.trc' trace_filename
,prog.user_concurrent_program_name
,execname.execution_file_name
,execname.subroutine_name
,phase_code
,status_code
,ses.SID
,ses.serial#
,ses.module
,ses.machine
FROM
fnd_concurrent_requests req
,v$session ses
,v$process proc
,v$parameter dest
,v$parameter dbnm
,fnd_concurrent_programs_vl prog
,fnd_executables execname
WHERE 1=1
AND req.request_id = &request
AND req.oracle_process_id=proc.spid(+)
AND proc.addr = ses.paddr(+)
AND dest.NAME='user_dump_dest'
AND dbnm.NAME='db_name'
AND req.concurrent_program_id = prog.concurrent_program_id
AND req.program_application_id = prog.application_id
AND prog.application_id = execname.application_id
AND prog.executable_id=execname.executable_id

How to resolve ORA-00031 on Linux based systems.

How to resolve ORA-00031 on Linux based systems.

1. First of all you should find block session id:

select  'SID ' || l1.sid ||' is blocking  ' || l2.sid blocking
from  v$lock l1, v$lock l2
where  l1.block =1 and l2.request > 0
and  l1.id1=l2.id1
and  l1.id2=l2.id2

2. With this select you can find Linux process ID and kill it:

select
sid,
spid
from v$process p, v$session s
where paddr = addr
and s.sid in(XXXX)
order by s.sid;

XXXX - it's SID from first select.

Enjoy

How to find OPP Service log file

OPP Service log file and location on server In previous post on OPP, we learnt about OPP services using application. Below mentioned SQL is to identify the log file name and location in unix server.

OPP Service log file and location on server In previous post on OPP, we learnt about OPP services using application. Below mentioned SQL is to identify the log file name and location in unix server.

If concurrent program ends in warning due to any OPP service failure, run below sql by passing request id.

SELECT fcpp.concurrent_request_id req_id,
fcp.node_name,
fcp.logfile_name
FROM fnd_conc_pp_actions fcpp,
fnd_concurrent_processes fcp
WHERE fcpp.processor_id = fcp.concurrent_process_id
AND fcpp.action_type = 6
AND fcpp.concurrent_request_id = &REQ -- 12345

How to switch the database to a new UNDO tablespace and drop the old one

$ sqlplus / as sysdba

SQL> show parameter undo

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
undo_management                      string      AUTO
undo_retention                       integer     900
undo_tablespace                      string      UNDOTBS1
SQL> 


The current undo tablespace as suggested by the initialization parameter undo_tablespace is UNDOTBS1. Leave this sysdba as is, open another console, log in as user SCOTT and initiate a transaction.

$ sqlplus scott/tiger

update emp set sal = sal + 1000 where empno=7839;

1 row updated.



With an update on emp table we have initiated a transaction. The undo data is written to a segment in the UNDOTBS1 tablespace. Now leave this SCOTT's session intact and go back to the sysdba console without issuing any COMMIT or ROLLBACK.

CREATE UNDO TABLESPACE undotbs2
DATAFILE '/d01/apps/oradata/oraxpo/undotbs201.dbf'
         SIZE 50M AUTOEXTEND ON NEXT 5M;

Tablespace created.

-- We created a new UNDO tablespace named UNDOTBS2


ALTER SYSTEM SET UNDO_TABLESPACE=UNDOTBS2 SCOPE=BOTH;
System altered.

-- Switch the database to the new UNDO tablespace.

SQL> DROP TABLESPACE undotbs1 INCLUDING CONTENTS AND DATAFILES;
DROP TABLESPACE undotbs1 INCLUDING CONTENTS AND DATAFILES
*
ERROR at line 1:
ORA-30013: undo tablespace 'UNDOTBS1' is currently in use

-- Try to drop the tablespace but failed. 

SQL>


With the alter system set undo_tablespace=UNDOTBS2, the database UNDO tablespace is changed and any new transaction's undo data will go to the new tablespace i.e. UNDOTBS2. But the undo data for already pending transaction (e.g. the one initiated by SCOTT before the database UNDO tablespace switch) is still in the old tablespace with a status of PENDING OFFLINE. As far as it is there you cannot drop the old tablespace.

set lines 10000
column name format a10

SELECT a.name,b.status 
FROM   v$rollname a,v$rollstat b
WHERE  a.usn = b.usn
AND    a.name IN ( 
    SELECT segment_name
    FROM dba_segments 
    WHERE tablespace_name = 'UNDOTBS1'
   );

NAME       STATUS
---------- ---------------
_SYSSMU8$  PENDING OFFLINE


The above query shows the name of the UNDO segment in the UNDOTBS1 tablespace and its status. Now lets see which users/sessions are running this pending transaction.

column username format a6

SELECT a.name,b.status , d.username , d.sid , d.serial#
FROM   v$rollname a,v$rollstat b, v$transaction c , v$session d
WHERE  a.usn = b.usn
AND    a.usn = c.xidusn
AND    c.ses_addr = d.saddr
AND    a.name IN ( 
    SELECT segment_name
    FROM dba_segments 
    WHERE tablespace_name = 'UNDOTBS1'
   );

NAME       STATUS          USERNA        SID    SERIAL#
---------- --------------- ------ ---------- ----------
_SYSSMU8$  PENDING OFFLINE SCOTT         147          4


So this is SCOTT with SID=147 and SERIAL#=4. Since we know now the user, we can go to him/her and request to end the transaction gracefully i.e. issue a ROLLBACK or COMMIT. However, if this is not possible (say the user initiated the transaction and left for annual leave :) and trust me this happens) you may go ahead and kill the session to release the undo segments in the UNDOTBS1 tablespace.

SQL> alter system kill session '147,4';
 
System altered.
 
SELECT a.name,b.status , d.username , d.sid , d.serial#
FROM   v$rollname a,v$rollstat b, v$transaction c , v$session d
WHERE  a.usn = b.usn
AND    a.usn = c.xidusn
AND    c.ses_addr = d.saddr
AND    a.name IN ( 
    SELECT segment_name
    FROM dba_segments 
    WHERE tablespace_name = 'UNDOTBS1'
   );
 
no rows selected


As we can see once the session is kills we don't see anymore segments occupied in the UNDOTBS1 tablespace. Lets drop UNDOTBS1.

SQL> DROP TABLESPACE undotbs1 INCLUDING CONTENTS AND DATAFILES;
DROP TABLESPACE undotbs1 INCLUDING CONTENTS AND DATAFILES
*
ERROR at line 1:
ORA-30013: undo tablespace 'UNDOTBS1' is currently in use


If you are retaining undo data then you still won't be able to drop the tablespace because it is still in use by undo_retention. Let the UNDO_RETENTION time pass and then try to drop the tablespace. In my case it is 900 seconds i.e. 15 minutes.

-- After 15 minutes.
SQL> DROP TABLESPACE undotbs1 INCLUDING CONTENTS AND DATAFILES;

Tablespace dropped.

How to alter the password expiration policy for certain user.

To alter the password expiry policy for a certain user profile

in Oracle first check wich profile the user is in using:

select profile from DBA_USERS where username = <username>;

Then you can change the limit to never expire using:

alter profile <profile_name> limit password_life_time UNLIMITED;

If you want to previously check the limit you may use:

select resource_name,limit from dba_profiles where profile='<profile_name>';

How to check the space left on the DEFAULT_TABLESPACE of the logged user

SELECT
  ts.tablespace_name,
  TO_CHAR(SUM(NVL(fs.bytes,0))/1024/1024, '99,999,990.99') AS MB_FREE
FROM
  user_free_space fs,
  user_tablespaces ts,
  user_users us
WHERE
  fs.tablespace_name(+)   = ts.tablespace_name
AND ts.tablespace_name(+) = us.default_tablespace
GROUP BY
  ts.tablespace_name;

Max Memory Settings for SQL Server 2005/2008

Suggested Max Memory Settings for SQL Server 2005/2008

• Posted on 29 October 2009
• Comments
•   Briefcase
•   Print

It is pretty important to make sure you set the Max Server memory setting for SQL Server 2005/2008 to something besides the default setting (which allows SQL Server to use as much memory as it wants, subject to signals from the operating system that it is under memory pressure). This is especially important with larger, busier systems that may be under memory pressure. 

This setting controls how much memory can be used by the SQL Server Buffer Pool.  If you don’t set an upper limit for this value, other parts of SQL Server, and the operating system can be starved for memory, which can cause instability and performance problems. It is even more important to set this correctly if you have “Lock Pages in Memory” enabled for the SQL Server service account (which I always do for x64 systems with more than 4GB of memory).

These settings are for x64, on a dedicated database server, only running the DB engine, (which is the ideal situation).

Physical RAM                        MaxServerMem Setting
2GB                                           1500
4GB                                           3200
6GB                                           4800
8GB                                           6400
12GB                                         10000
16GB                                         13500
24GB                                         21500
32GB                                         29000
48GB                                         44000
64GB                                         60000
72GB                                         68000
96GB                                         92000
128GB                                       124000

If you are running other SQL Server components, such as SSIS or Full Text Search, you will want to allocate less memory for the SQL Server Buffer Pool. You also want to pay close attention to how much memory is still available in Task Manager. This is how much RAM should be available in Task Manager while you are under load (on Windows Server 2003):

Physical RAM            Target Avail RAM in Task Manager
< 4GB                               512MB – 1GB
4-32GB                              1GB – 2GB
32-128GB                            2GB – 4GB
> 128GB                              > 4GB

You can use T-SQL to set your MaxServerMemory setting. The sample below sets it to 3500, which is the equivalent of 3.5GB. This setting is dynamic in SQL Server 2005/2008, which means that you can change it and it goes into effect immediately, without restarting SQL Server.

-- Turn on advanced optionsEXEC  sp_configure'Show Advanced Options',1;GO
RECONFIGURE;GO
-- Set max server memory = 3500MB for the serverEXEC  sp_configure'max server memory (MB)',3500;GO
RECONFIGURE;GO

-- See what the current values are
EXEC sp_configure;

You can also change this setting in the SSMS GUI, as you see below:

Finally, I have learned that it is a good idea to temporarily adjust your MaxServerMemory setting downward by a few GB if you know you will be doing a large file copy on your database server (such as copying a large database backup file).

Taken from: http://www.sqlservercentral.com/blogs/glennberry/2009/10/29/suggested-max-memory-settings-for-sql-server-2005_2F00_2008/

How to identify what locked PL/SQL package

select * from v$locked_object lo join dba_objects o on lo.object_id = o.object_id
where o.object_name = 'xxPACKAGE NAMExx' and o.object_type = 'PACKAGE';

ORA-02020: too many database links in use

ORA-02020: too many database links in use

Error :


ORA-02020: too many database links in use


Solution :

Increase the open_links and open_links_instance parameter in the DB . Bounce Database

Or

SQL>alter session close database link "link name";


Thank you

Steps to Configure database in archive log mode

Please follow below steps to configure database in archive log mode.

Check if the database running archive log mode or not by using following query.

SQL> select NAME,LOG_MODE from v$database;

NAME      LOG_MODE
——— ————
RMANDV1   NOARCHIVELOG

There are two init.ora parameters we need to change before configuring database in archive log mode.

1)LOG_ARCHIVE_DEST —/u01/app/archive/rmandv1
2)LOG_ARCHIVE_FORMAT —’rmandv1%t_%s_%r.arc’

alter system set LOG_ARCHIVE_DEST=’/d01/app/archive/rmandv1′ scope =both;

alter system set LOG_ARCHIVE_FORMAT=’rmandv1%t_%s_%r.arc’ scope =spfile;

Step1)Shutdown the database

SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.

Step2)Start  up a new instance and mount, but do not open the database.

SQL> startup mount;
ORACLE instance started.

Total System Global Area  180355072 bytes
Fixed Size                  2094896 bytes
Variable Size              88082640 bytes
Database Buffers           83886080 bytes
Redo Buffers                6291456 bytes
Database mounted.

Step3)Put the database in archive log mode

SQL> alter database archivelog;

Database altered

Now open the database

SQL> alter database open;

Database altered.

check now if your database is in archive log mode.

SQL> archive log list;
Database log mode              Archive Mode
Automatic archival             Enabled
Archive destination            /u01/app/archive/rmandv1
Oldest online log sequence     18
Next log sequence to archive   20
Current log sequence           20

How to find Oracle Application connection problems

If you can't login into EBS via logon page you have few tools to find the problems:


http://<server>.<domain>:<PORT>/OA_HTML/ServletPing

http://<server>.<domain>:<PORT>/OA_HTML/jsp/fnd/aoljtest.jsp

http://<server>.<domain>:<PORT>/OA_HTML/OA.jsp?OAFunc=OAHOMEPAGE

http://<server>.<domain>:<PORT>

http://<server>.<domain>:<PORT>/OA_MEDIA/FNDLOGOL.gif

http://<server>.<domain>:<PORT>/forms/frmservlet

http://<hostname.domainname>:<port>/OA_HTML/fndvald.jsp?username=sysadmin&password=<sysadmin_password>

Good luck.

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.