If you would like to know the responsibility that can submit a particular concurrent then use the query mentioned below . It's working for 90% of programs and I'm working on the same to make it 100 % effective ( will update the same when succeed )
Step 1 :
select user_concurrent_program_name from fnd_concurrent_programs_vl where user_concurrent_program_name like '%DIRECT BILLING%'
DIRECT BILLING BO REPORT
Now user the query to find the required responsibilities ...
Query :
select responsibility_name , responsibility_key ,request_group_id , application_id from fnd_responsibility_vl
where request_group_id in (
select request_group_id from FND_REQUEST_GROUP_UNITS where request_unit_id in
( select concurrent_program_id from fnd_concurrent_programs_vl where
user_concurrent_program_name='DIRECT BILLING BO REPORT'))
Wednesday, November 18, 2009
Tuesday, November 10, 2009
How to create a database with minimum configuration and parameters
Step 1 : Export the required PATH
export ORACLE_SID=TEST5
export ORACLE_HOME=/u11m/app/ilproddb/9.2.0/
export PATH=$PATH:$ORACLE_HOME/lib:$ORACLE_HOME/bin
Step 2 : Create pfile with the minimum configuration
[ilerpap03:ilmis] /u11m/app/ilproddb/9.2.0/dbs> vi initTEST5.ora
"initTEST5.ora" [New file]
instance_name=TEST5
db_name=TEST5
Step 3 : Now , create the database
[ilerpap03:ilmis] /u11m/app/ilproddb/9.2.0/dbs> sysdba
SQL*Plus: Release 9.2.0.5.0 - Production on Mon Nov 9 12:26:31 2009
Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.
Connected to an idle instance.
12:26:38 TEST5 > startup nomount
ORACLE instance started.
Total System Global Area 178227232 bytes
Fixed Size 743456 bytes
Variable Size 121634816 bytes
Database Buffers 50331648 bytes
Redo Buffers 5517312 bytes
12:26:46 TEST5 > create database ;
Database created
12:30:46 TEST5 >
That's it . Database is created now .
export ORACLE_SID=TEST5
export ORACLE_HOME=/u11m/app/ilproddb/9.2.0/
export PATH=$PATH:$ORACLE_HOME/lib:$ORACLE_HOME/bin
Step 2 : Create pfile with the minimum configuration
[ilerpap03:ilmis] /u11m/app/ilproddb/9.2.0/dbs> vi initTEST5.ora
"initTEST5.ora" [New file]
instance_name=TEST5
db_name=TEST5
Step 3 : Now , create the database
[ilerpap03:ilmis] /u11m/app/ilproddb/9.2.0/dbs> sysdba
SQL*Plus: Release 9.2.0.5.0 - Production on Mon Nov 9 12:26:31 2009
Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.
Connected to an idle instance.
12:26:38 TEST5 > startup nomount
ORACLE instance started.
Total System Global Area 178227232 bytes
Fixed Size 743456 bytes
Variable Size 121634816 bytes
Database Buffers 50331648 bytes
Redo Buffers 5517312 bytes
12:26:46 TEST5 > create database ;
Database created
12:30:46 TEST5 >
That's it . Database is created now .
Monday, November 9, 2009
Error :
Suppressing further error logging of LOG_ARCHIVE_DEST_2.
Fri Nov 6 08:45:53 2009
Error 1031 received logging on to the standby
Suppressing further error logging of LOG_ARCHIVE_DEST_2.
Fri Nov 6 08:50:53 2009
Error 1031 received logging on to the standby
Suppressing further error logging of LOG_ARCHIVE_DEST_2.
Fri Nov 6 08:55:53 2009
Error 1031 received logging on to the standby
Suppressing further error logging of LOG_ARCHIVE_DEST_2.
Fri Nov 6 09:00:04 2009
Thread 1 advanced to log sequence 15599
Current log# 1 seq# 15599 mem# 0: /DDMESP_home2/oradata/ORAPMES/redo1a.log
Current log# 1 seq# 15599 mem# 1: /DDMESP_home4/oradata/ORAPMES/redo1b.log
Fri Nov 6 09:00:53 2009
Error 1031 received logging on to the standby
Suppressing further error logging of LOG_ARCHIVE_DEST_2.
Fri Nov 6 09:01:24 2009
WARNING: inbound connection timed out (ORA-3136)
Fri Nov 6 09:01:32 2009
WARNING: inbound connection timed out (ORA-3136)
OR
Errors in file c:\oracle\product\10.2.0\admin\lims\bdump\lims_arc1_2756.trc:
ORA-01017: invalid username/password; logon denied
Sun Jul 20 05:08:19 2008
Error 1017 received logging on to the standby
Check that the primary and standby are using a password file
and remote_login_passwordfile is set to SHARED or EXCLUSIVE,
and that the SYS password is same in the password files.
returning error ORA-16191
OR
*** 2008-07-20 21:47:24.812
*** SERVICE NAME:() 2008-07-20 21:47:24.781
*** SESSION ID:(156.1) 2008-07-20 21:47:24.781
Redo shipping client performing standby login
OCISessionBegin failed -1
.. Detailed OCI error val is 1031 and errmsg is 'ORA-01031: insufficient privileges
'
*** 2008-07-20 21:47:25.046 62692 kcrr.c
Error 1031 received logging on to the standby
Error 1031 connecting to destination LOG_ARCHIVE_DEST_2 standby host 'to_prod'
Error 1031 attaching to destination LOG_ARCHIVE_DEST_2 standby host 'to_prod'
ORA-01031: insufficient privileges
*** 2008-07-20 21:47:25.062 62692 kcrr.c
PINGARC1: Heartbeat failed to connect to standby 'to_prod'. Error is 1031.
*** 2008-07-20 21:47:25.062 60970 kcrr.c
kcrrfail: dest:2 err:1031 force:0 blast:1
Solution :
1. REMOTE_LOGIN_PASSWORDFILE configured to SHARED or EXCLUSIVE
2. Recreate the password file on the standby database to have the same sys password as the primary database.
OR
Copy the password from Primary server to the stnadby server
Suppressing further error logging of LOG_ARCHIVE_DEST_2.
Fri Nov 6 08:45:53 2009
Error 1031 received logging on to the standby
Suppressing further error logging of LOG_ARCHIVE_DEST_2.
Fri Nov 6 08:50:53 2009
Error 1031 received logging on to the standby
Suppressing further error logging of LOG_ARCHIVE_DEST_2.
Fri Nov 6 08:55:53 2009
Error 1031 received logging on to the standby
Suppressing further error logging of LOG_ARCHIVE_DEST_2.
Fri Nov 6 09:00:04 2009
Thread 1 advanced to log sequence 15599
Current log# 1 seq# 15599 mem# 0: /DDMESP_home2/oradata/ORAPMES/redo1a.log
Current log# 1 seq# 15599 mem# 1: /DDMESP_home4/oradata/ORAPMES/redo1b.log
Fri Nov 6 09:00:53 2009
Error 1031 received logging on to the standby
Suppressing further error logging of LOG_ARCHIVE_DEST_2.
Fri Nov 6 09:01:24 2009
WARNING: inbound connection timed out (ORA-3136)
Fri Nov 6 09:01:32 2009
WARNING: inbound connection timed out (ORA-3136)
OR
Errors in file c:\oracle\product\10.2.0\admin\lims\bdump\lims_arc1_2756.trc:
ORA-01017: invalid username/password; logon denied
Sun Jul 20 05:08:19 2008
Error 1017 received logging on to the standby
Check that the primary and standby are using a password file
and remote_login_passwordfile is set to SHARED or EXCLUSIVE,
and that the SYS password is same in the password files.
returning error ORA-16191
OR
*** 2008-07-20 21:47:24.812
*** SERVICE NAME:() 2008-07-20 21:47:24.781
*** SESSION ID:(156.1) 2008-07-20 21:47:24.781
Redo shipping client performing standby login
OCISessionBegin failed -1
.. Detailed OCI error val is 1031 and errmsg is 'ORA-01031: insufficient privileges
'
*** 2008-07-20 21:47:25.046 62692 kcrr.c
Error 1031 received logging on to the standby
Error 1031 connecting to destination LOG_ARCHIVE_DEST_2 standby host 'to_prod'
Error 1031 attaching to destination LOG_ARCHIVE_DEST_2 standby host 'to_prod'
ORA-01031: insufficient privileges
*** 2008-07-20 21:47:25.062 62692 kcrr.c
PINGARC1: Heartbeat failed to connect to standby 'to_prod'. Error is 1031.
*** 2008-07-20 21:47:25.062 60970 kcrr.c
kcrrfail: dest:2 err:1031 force:0 blast:1
Solution :
1. REMOTE_LOGIN_PASSWORDFILE configured to SHARED or EXCLUSIVE
2. Recreate the password file on the standby database to have the same sys password as the primary database.
OR
Copy the password from Primary server to the stnadby server
Sunday, October 25, 2009
ORA-00997: illegal use of LONG datatype
Error :
I got this error " ORA-00997: illegal use of LONG datatype" while creating the backup of table with long datatype .
SQL> create table test as select * from csn_user;
create table test as select * from csn_user
*
ERROR at line 1:
ORA-00997: illegal use of LONG datatype
Solution :
Solution 1 :
1.export the table
2.rename the table
3.import the table
But above solution is not possible , if tables is highly accessible . In that case , you can go for solution2
Solution 2 :
Table thats backup need to be created is CSN _USER ( owner : CSN ) .
Step1 : Take the export of the table
exp file=csn_user_2409.dmp log=csn_user_2409.log tables=csn.csn_user
Username: / as sysdba
Step 2 : Now import the table the table in a schema other that CSN . In that schema there shouldn't be any table with the name CSN_USER . Here , we're importing the table CSN.CSN_USER into the schema USER2
imp file=csn_user_2409.dmp log=imp_csn_user_2409.log fromuser=csn touser=user2 tables=CSN_USER
Username: / as sysdba
Step 3 : Now rename the table ( of USER2 schema )
sqlplus user2/user2
SQL > alter table user2.CSN_USER rename to csn_user_241009 ;
Step 4 : Now take the export of the table from USER2 schema
exp file=exp_csn_user_2409.dmp log=exp_csn_user_2409.log tables=user2.csn_user_241009
Username: / as sysdba
Step 5 : Import the table into required schema ( that's CSN )
imp file=exp_csn_user_2409.dmp log=imp_exp_csn_user_2409.log fromuser=user2 touser=csn tables=csn_user_241009
Username: / as sysdba
I got this error " ORA-00997: illegal use of LONG datatype" while creating the backup of table with long datatype .
SQL> create table test as select * from csn_user;
create table test as select * from csn_user
*
ERROR at line 1:
ORA-00997: illegal use of LONG datatype
Solution :
Solution 1 :
1.export the table
2.rename the table
3.import the table
But above solution is not possible , if tables is highly accessible . In that case , you can go for solution2
Solution 2 :
Table thats backup need to be created is CSN _USER ( owner : CSN ) .
Step1 : Take the export of the table
exp file=csn_user_2409.dmp log=csn_user_2409.log tables=csn.csn_user
Username: / as sysdba
Step 2 : Now import the table the table in a schema other that CSN . In that schema there shouldn't be any table with the name CSN_USER . Here , we're importing the table CSN.CSN_USER into the schema USER2
imp file=csn_user_2409.dmp log=imp_csn_user_2409.log fromuser=csn touser=user2 tables=CSN_USER
Username: / as sysdba
Step 3 : Now rename the table ( of USER2 schema )
sqlplus user2/user2
SQL > alter table user2.CSN_USER rename to csn_user_241009 ;
Step 4 : Now take the export of the table from USER2 schema
exp file=exp_csn_user_2409.dmp log=exp_csn_user_2409.log tables=user2.csn_user_241009
Username: / as sysdba
Step 5 : Import the table into required schema ( that's CSN )
imp file=exp_csn_user_2409.dmp log=imp_exp_csn_user_2409.log fromuser=user2 touser=csn tables=csn_user_241009
Username: / as sysdba
Sunday, October 18, 2009
table in read only mode OR restricitng dml on a table
HI ,
I found this somewhere and it's a nice trigger to make a table in read only mode .
16:06:06 > create table test11 (num number(20));
Table created.
16:06:13 > insert into test11 values(1);
1 row created.
16:06:26 > commit;
Commit complete.
16:06:29 > select * from test11;
NUM
----------
1
Now , create the trigger to disable the dml on the table test11
16:06:33 > create or replace trigger test11_read_only
before insert or update or delete
on test11
begin
raise_application_error (-20001, 'RAHUL has restricted DML on this table , Table TEST11 is Read Only ');
end;
/
16:06:56 2 16:06:56 3 16:06:56 4 16:06:56 5 16:06:56 6 16:06:56 7
Trigger created.
16:06:56 > insert into test11 values(1);
insert into test11 values(1)
*
ERROR at line 1:
ORA-20001: RAHUL has restricted DML on this table , Table TEST11 is Read Only
ORA-06512: at "APPS.TEST11_READ_ONLY", line 2
ORA-04088: error during execution of trigger 'APPS.TEST11_READ_ONLY'
16:07:03 >
I found this somewhere and it's a nice trigger to make a table in read only mode .
16:06:06 > create table test11 (num number(20));
Table created.
16:06:13 > insert into test11 values(1);
1 row created.
16:06:26 > commit;
Commit complete.
16:06:29 > select * from test11;
NUM
----------
1
Now , create the trigger to disable the dml on the table test11
16:06:33 > create or replace trigger test11_read_only
before insert or update or delete
on test11
begin
raise_application_error (-20001, 'RAHUL has restricted DML on this table , Table TEST11 is Read Only ');
end;
/
16:06:56 2 16:06:56 3 16:06:56 4 16:06:56 5 16:06:56 6 16:06:56 7
Trigger created.
16:06:56 > insert into test11 values(1);
insert into test11 values(1)
*
ERROR at line 1:
ORA-20001: RAHUL has restricted DML on this table , Table TEST11 is Read Only
ORA-06512: at "APPS.TEST11_READ_ONLY", line 2
ORA-04088: error during execution of trigger 'APPS.TEST11_READ_ONLY'
16:07:03 >
Wednesday, September 16, 2009
ORA-29342: user APPS does not exist in the database ; During transport tablesapce option
[plantopiadb:oraperf] /home/users/oraperf> imp transport_tablespace=y file=/perf/dump/perfdump.dmp log=/perf/dump/perfdump>
Import: Release 9.2.0.7.0 - Production on Fri Sep 11 15:45:06 2009
Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.
Username: / as sysdba
Connected to: Oracle9i Enterprise Edition Release 9.2.0.7.0 - 64bit Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.7.0 - Production
Export file created by EXPORT:V09.02.00 via conventional path
About to import transportable tablespace(s) metadata...
import done in US7ASCII character set and AL16UTF16 NCHAR character set
import server uses UTF8 character set (possible charset conversion)
. importing SYS's objects into SYS
IMP-00017: following statement failed with ORACLE error 29342:
"BEGIN sys.dbms_plugts.checkUser('APPS'); END;"
IMP-00003: ORACLE error 29342 encountered
ORA-29342: user APPS does not exist in the database
ORA-06512: at "SYS.DBMS_PLUGTS", line 1594
ORA-06512: at line 1
IMP-00000: Import terminated unsuccessfully
Soltuion :
APPS user is having object in respective tablespace (PERFSTAT in our case) at the source database. But no APPS user at the destination database. Use the query mentioned below to fine all the users having the object in PERFSTAT tablespace at source database and need to be created at the destination database , in case if not present .
SQL> select distinct owner from dba_segments where tablespace_name='PERFSTAT' ;
OWNER
------------------------------
APPS
BIZMAX
DBSNMP
KIC
PERFSTAT
TRCANLZR
create all the users in your destiantion database . Where you're plugin the tablespace ..
Import: Release 9.2.0.7.0 - Production on Fri Sep 11 15:45:06 2009
Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.
Username: / as sysdba
Connected to: Oracle9i Enterprise Edition Release 9.2.0.7.0 - 64bit Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.7.0 - Production
Export file created by EXPORT:V09.02.00 via conventional path
About to import transportable tablespace(s) metadata...
import done in US7ASCII character set and AL16UTF16 NCHAR character set
import server uses UTF8 character set (possible charset conversion)
. importing SYS's objects into SYS
IMP-00017: following statement failed with ORACLE error 29342:
"BEGIN sys.dbms_plugts.checkUser('APPS'); END;"
IMP-00003: ORACLE error 29342 encountered
ORA-29342: user APPS does not exist in the database
ORA-06512: at "SYS.DBMS_PLUGTS", line 1594
ORA-06512: at line 1
IMP-00000: Import terminated unsuccessfully
Soltuion :
APPS user is having object in respective tablespace (PERFSTAT in our case) at the source database. But no APPS user at the destination database. Use the query mentioned below to fine all the users having the object in PERFSTAT tablespace at source database and need to be created at the destination database , in case if not present .
SQL> select distinct owner from dba_segments where tablespace_name='PERFSTAT' ;
OWNER
------------------------------
APPS
BIZMAX
DBSNMP
KIC
PERFSTAT
TRCANLZR
create all the users in your destiantion database . Where you're plugin the tablespace ..
ORA-29335: tablespace 'PERFSTAT' is not read only ( during transport tablespace option )
[veritas:oraprod] /home/oraprod > TAT file=/u43/oradata/stats_dump/perfdump.dmp log=/u43/oradata/stats_dump/perfdump.log <
Export: Release 9.2.0.5.0 - Production on Fri Sep 11
Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.
Username: / as sysdba
Connected to: Oracle9i Enterprise Edition Release 9.2.0.5.0 - 64bit Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.5.0 - Production
Export done in US7ASCII character set and AL16UTF16 NCHAR character set
server uses UTF8 character set (possible charset conversion)
Note: table data (rows) will not be exported
About to export transportable tablespace metadata...
EXP-00008: ORACLE error 29335 encountered
ORA-29335: tablespace 'PERFSTAT' is not read only
ORA-06512: at "SYS.DBMS_PLUGTS", line 436
ORA-06512: at line 1
EXP-00000: Export terminated unsuccessfully
Soltuion :
This is because of the tablespace in READ WRITE mode . Before taking the export of metadata, tablespace
needs to be taken in READ ONLY mode
How to change the character set of a database
Step 1 : Find the present character set of your database ..
SQL> select * from NLS_DATABASE_PARAMETERS where parameter in ('NLS_CHARACTERSET','NLS_NCHAR_CHARACTERSET');
PARAMETER VALUE
------------------------------ ----------------------------------------
NLS_CHARACTERSET US7ASCII
NLS_NCHAR_CHARACTERSET AL16UTF16
SHUTDOWN IMMEDIATE;
STARTUP MOUNT;
ALTER SYSTEM ENABLE RESTRICTED SESSION;
ALTER SYSTEM SET JOB_QUEUE_PROCESSES=0;
ALTER SYSTEM SET AQ_TM_PROCESSES=0;
ALTER DATABASE OPEN;
select value from nls_database_parameters where parameter='NLS_CHARACTERSET';
---- TRUNCATE TABLE SYS.METASTYLESHEET; ---to get rid of ORA-12716 per Metalink Note - 213015.1
ALTER DATABASE CHARACTER SET UTF8;
SHUTDOWN IMMEDIATE;
STARTUP;
@$ORACLE_HOME/rdbms/admin/catmet.sql
SHUTDOWN IMMEDIATE;
STARTUP;
Step 3 : Now confirm the new character set of your database ..
SQL> select * from NLS_DATABASE_PARAMETERS where parameter in ('NLS_CHARACTERSET','NLS_NCHAR_CHARACTERSET');
PARAMETER VALUE
------------------------------ ----------------------------------------
NLS_CHARACTERSET UTF8
NLS_NCHAR_CHARACTERSET AL16UTF16
Thursday, August 20, 2009
FND Debug Log - Debugging Oracle Apps code
Refernce : http://oracle.anilpassi.com
There was a time when every individual Oracle Applications module used its own debugging technique.
But this is changing now, thanks to FND Logging. I have been using FND Logging for over one year now, ever since 11.5.10 was released, hence I would like to share knowledge on this topic.
What is the use of FND Debug Log?
1. It helps you pinpoint the cause of error in standard Oracle Code, by making debug messages to appear in a centralized table named FND_LOG_MESSAGES.
2. You can design and build your custom extensions in a manner that can easily be debugged. This can be done by calling Oracle delivered API’s in your custom code.
Where is the debug message stored, once the logging is turned on?
Debug messages are stored in a table called FND_LOG_MESSAGES
A program written in any technology, either form, or report, or pl/sql or java concurrent program or OAF…all their debug messages will be stored in fnd_log_messages.
All debug messages spooled to fnd_log_messages
How to debug the issue being faced in Oracle Application?
Step 1 Set the following profile options at your user level(your fnd_user)
FND: Debug Log Level
Following possible values are available, but I suggest you set this to "Statement" level when debugging code.
LEVEL_UNEXPECTED : Internal Level Id is 6
LEVEL_ERROR : Internal Level Id is 5
LEVEL_EXCEPTION : Internal Level Id is 4
LEVEL_EVENT : Internal Level Id is 3
LEVEL_PROCEDURE : Internal Level Id is 2
LEVEL_STATEMENT : Internal Level Id is 1
FND: Debug Log Enabled
Set this profile to Yes
FND: Debug Log Module
Set this to %
Step 2
Login to the application and reproduce the problem.
Step 3
SELECT *
FROM fnd_log_messages
WHERE user_id = 209122 /*your FND_USER user_id here*/
AND TIMESTAMP > SYSDATE - .3
ORDER BY log_sequence DESC /*note the order by clause here*/
The result of this select statement will provide the list of all the debug messages, on top will appear the most recent debug messages..
Why should I setup the module name to %, in profile option?
You can set this to po%, if you know for sure that the error was caused by code written in po module. However po code might be internally calling hr code which might inturn be calling fnd code.
Hence it’s best to set this profile value as %.
You may also use comma delimited values i.e po%,hr%,fnd%
Why must I bother debugging Oracle's Standard code when I can quickly raise a tar.
If the issue is with Standard Oracle Code, first thing you must do is to search into Metalink. However having the debug information on error helps your searching ability further. Uploading the debug messages upfront during Tar creation will also help Oracle speedily understand and fix your issues.
Why to set the profile option to statement level?
This profile option has following main levels.-
Error
Warning
Procedure
Statement
I like setting this to "Statement" level as it extracts debug messages at all levels, in one glance. You can latter filter those debug messages by using below SQL for example
select * from fnd_log_messages where user_id = 111 and LOG_LEVEL =5
What if the piece of code causing the error is not appearing in fnd_log_messages?
This is very much possible. The fnd_log_messages might have helped you get close to the culprit piece of code , but may not be able to pinpoint the error as there may not be enough debug messages implanted by Oracle.
You can do one of the below:-
A. Run the database sql trace for the session with bind variables and see the last meaningful SQL statement in the raw trace file.
Please note that PL/SQL statements will not appear in trace, only the SQL Statements will appear, hence you may consider option (b) below
B. Add your own debug messages to the pl/sql code that was delivered by oracle, which you suspect is causing problem. This is a temporary change, and must only be done on development environment, NEVER DO THIS CHANGE ON PRODUCTION.
The size of table FND_LOG_MESSAGES will keep on increasing?
You can run concurrent program “Purge Debug Log and System Alerts”.
I have written a pl/sql concurrent process to interface Purchase Orders from 3rd Party System. How will add debug messages?
fnd_log.STRING(log_level => fnd_log.level_statement
,module => 'xxpo.packagename.procedurename'
,message => 'debug message here');
Will the above debug command create an entry into fnd_log_messages ?
Debug records will be created in fnd_log_messages if and only if you run the interface program after setting the profile options as suggested above.
What if a rollback occurs due to unhandled exception. Will the inserts done to fnd_log_messages be lost?
fnd_log.string eventually calls procedure FND_LOG.STRING_UNCHECKED_INTERNAL2. This procedure uses pragma AUTONOMOUS_TRANSACTION with a commit.
Hence your debug messages will not be lost despite a rollback in parent session.
What if an exception is incurred within the Debug Logging API itself?
Oracle takes well care of this by handling the WHEN OTHERS exception.
It is evident from below Oracle Code for debug API
PROCEDURE STRING(LOG_LEVEL IN NUMBER,
MODULE IN VARCHAR2,
MESSAGE IN VARCHAR2) is
begin
/* Short circuit if logging not turned on at this level */
if (LOG_LEVEL < log_level =""> LOG_LEVEL,
MODULE => MODULE,
MESSAGE_TEXT => MESSAGE);
end if;
exception
when others then
NULL; /* supress the exception */
end;
There was a time when every individual Oracle Applications module used its own debugging technique.
But this is changing now, thanks to FND Logging. I have been using FND Logging for over one year now, ever since 11.5.10 was released, hence I would like to share knowledge on this topic.
What is the use of FND Debug Log?
1. It helps you pinpoint the cause of error in standard Oracle Code, by making debug messages to appear in a centralized table named FND_LOG_MESSAGES.
2. You can design and build your custom extensions in a manner that can easily be debugged. This can be done by calling Oracle delivered API’s in your custom code.
Where is the debug message stored, once the logging is turned on?
Debug messages are stored in a table called FND_LOG_MESSAGES
A program written in any technology, either form, or report, or pl/sql or java concurrent program or OAF…all their debug messages will be stored in fnd_log_messages.
All debug messages spooled to fnd_log_messages
How to debug the issue being faced in Oracle Application?
Step 1 Set the following profile options at your user level(your fnd_user)
FND: Debug Log Level
Following possible values are available, but I suggest you set this to "Statement" level when debugging code.
LEVEL_UNEXPECTED : Internal Level Id is 6
LEVEL_ERROR : Internal Level Id is 5
LEVEL_EXCEPTION : Internal Level Id is 4
LEVEL_EVENT : Internal Level Id is 3
LEVEL_PROCEDURE : Internal Level Id is 2
LEVEL_STATEMENT : Internal Level Id is 1
FND: Debug Log Enabled
Set this profile to Yes
FND: Debug Log Module
Set this to %
Step 2
Login to the application and reproduce the problem.
Step 3
SELECT *
FROM fnd_log_messages
WHERE user_id = 209122 /*your FND_USER user_id here*/
AND TIMESTAMP > SYSDATE - .3
ORDER BY log_sequence DESC /*note the order by clause here*/
The result of this select statement will provide the list of all the debug messages, on top will appear the most recent debug messages..
Why should I setup the module name to %, in profile option?
You can set this to po%, if you know for sure that the error was caused by code written in po module. However po code might be internally calling hr code which might inturn be calling fnd code.
Hence it’s best to set this profile value as %.
You may also use comma delimited values i.e po%,hr%,fnd%
Why must I bother debugging Oracle's Standard code when I can quickly raise a tar.
If the issue is with Standard Oracle Code, first thing you must do is to search into Metalink. However having the debug information on error helps your searching ability further. Uploading the debug messages upfront during Tar creation will also help Oracle speedily understand and fix your issues.
Why to set the profile option to statement level?
This profile option has following main levels.-
Error
Warning
Procedure
Statement
I like setting this to "Statement" level as it extracts debug messages at all levels, in one glance. You can latter filter those debug messages by using below SQL for example
select * from fnd_log_messages where user_id = 111 and LOG_LEVEL =5
What if the piece of code causing the error is not appearing in fnd_log_messages?
This is very much possible. The fnd_log_messages might have helped you get close to the culprit piece of code , but may not be able to pinpoint the error as there may not be enough debug messages implanted by Oracle.
You can do one of the below:-
A. Run the database sql trace for the session with bind variables and see the last meaningful SQL statement in the raw trace file.
Please note that PL/SQL statements will not appear in trace, only the SQL Statements will appear, hence you may consider option (b) below
B. Add your own debug messages to the pl/sql code that was delivered by oracle, which you suspect is causing problem. This is a temporary change, and must only be done on development environment, NEVER DO THIS CHANGE ON PRODUCTION.
The size of table FND_LOG_MESSAGES will keep on increasing?
You can run concurrent program “Purge Debug Log and System Alerts”.
I have written a pl/sql concurrent process to interface Purchase Orders from 3rd Party System. How will add debug messages?
fnd_log.STRING(log_level => fnd_log.level_statement
,module => 'xxpo.packagename.procedurename'
,message => 'debug message here');
Will the above debug command create an entry into fnd_log_messages ?
Debug records will be created in fnd_log_messages if and only if you run the interface program after setting the profile options as suggested above.
What if a rollback occurs due to unhandled exception. Will the inserts done to fnd_log_messages be lost?
fnd_log.string eventually calls procedure FND_LOG.STRING_UNCHECKED_INTERNAL2. This procedure uses pragma AUTONOMOUS_TRANSACTION with a commit.
Hence your debug messages will not be lost despite a rollback in parent session.
What if an exception is incurred within the Debug Logging API itself?
Oracle takes well care of this by handling the WHEN OTHERS exception.
It is evident from below Oracle Code for debug API
PROCEDURE STRING(LOG_LEVEL IN NUMBER,
MODULE IN VARCHAR2,
MESSAGE IN VARCHAR2) is
begin
/* Short circuit if logging not turned on at this level */
if (LOG_LEVEL < log_level =""> LOG_LEVEL,
MODULE => MODULE,
MESSAGE_TEXT => MESSAGE);
end if;
exception
when others then
NULL; /* supress the exception */
end;
Monday, August 10, 2009
Error :
==============================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ============
RMAN-00571: ==============================
RMAN-03002: failure of configure command at
RMAN-06004: ORACLE error from recovery catalog database:
RMAN-20011: target database incarnation is not current in recovery catalog
Cause :
The database incarnation that matches the resetlogs change# and time of the mounted target database control file is not the current incarnation of the database .
In our case we cloned our database LGEIL as CRM and thus used “ reset database “ to reset the incarnation as per the new cloned CRM database . But after moving back to old database LGEIL , backup getting errored out with the error mentioned above ..
Details :
The above error occurs when the Database target Incarnation is not current in the Recovery Catalog.
The incarnation of a database is a number that is used to identify a version of the database.A database incarnation is created whenever you open the database with the RESETLOGS.
V$DATABASE_INCARNATION displays information about all database incarnations. Oracle creates
a new incarnation whenever a database is opened with the RESETLOGS option. Records about the
current and immediately previous incarnation are also contained in the V$DATABASE view.
To Reference the corresponding Incarnation information in the recovery catalog review the RC_DATABASE_INCARNATION and RC_DATABASE views.
à RC_DATABASE_INCARNATION lists information about all database incarnations registered in the recovery catalog.
à RC_DATABASE lists information about the databases registered in the recovery catalog. It corresponds to the V$DATABASE view in your target database.
Solution :
Connect to the RMAN schema as rman user e.g.
sqlplus rman/rman@EMREP
SQL> SELECT dbid, name, dbinc_key, resetlogs_change#, resetlogs_time FROM rc_database_incarnation WHERE dbid=1243244440 ORDER BY resetlogs_time;
DBID NAME DBINC_KEY RESETLOGS_CHANGE# RESETLOGS_TIME
---------- -------- ---------- ----------------- --------------------
1243244440 LGEIL 2812522 1 08-JUL-2007 06:09:28
1243244440 LGEIL 2812503 9.6778E+12 08-JUL-2007 07:11:27
1243244440 CRM 5026006 1.1191E+13
Now , here our current incarnation is 5026006 (CRM ) and we have to change it to 2812503 ( LGEIL ) .
Incarnation time of your database ( LGEIL in our case ) can be confirmed from v$database ..
SQL> select RESETLOGS_TIME , RESETLOGS_CHANGE# , PRIOR_RESETLOGS_CHANGE# , PRIOR_RESETLOGS_TIME from v$database;
RESETLOGS_TIME RESETLOGS_CHANGE# PRIOR_RESETLOGS_CHANGE# PRIOR_RESETLOGS_TIME
-------------------- ------------------------ ---------------------------- --------------------
Here it’s “
Now , reset the incarnation …..
[csnetnorthdb:oracle] /northact/LGEIL/rman/scripts> rman target / catalog rman/rman@EMREP
Recovery Manager: Release 9.2.0.8.0 - 64bit Production
Copyright (c) 1995, 2002, Oracle Corporation. All rights reserved.
connected to target database: LGEIL (DBID=1243244440)
connected to recovery catalog database
RMAN> list incarnation;
No rows will be selected here ..
RMAN>
RMAN> RESET DATABASE TO INCARNATION 2812503;
database reset to incarnation 2812503 in recovery catalog
RMAN> resync catalog;
starting full resync of recovery catalog
full resync complete
RMAN> list incarnation;
List of Database Incarnations
DB Key Inc Key DB Name DB ID CUR Reset SCN Reset Time
------- ------- -------- ---------------- --- ---------- ----------
2812502 2812522 LGEIL 1243244440 NO 1
2812502 2812503 LGEIL 1243244440 YES 9677807775711
2812502 5026006 CRM 1243244440 NO 11190641794400
Now ..you can take the backup
RMAN-20011: target database incarnation is not current in recovery catalog
Error :
==============================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ============
RMAN-00571: ==============================
RMAN-03002: failure of configure command at
RMAN-06004: ORACLE error from recovery catalog database:
RMAN-20011: target database incarnation is not current in recovery catalog
Cause :
The database incarnation that matches the resetlogs change# and time of the mounted target database control file is not the current incarnation of the database .
In our case we cloned our database LGEIL as CRM and thus used “ reset database “ to reset the incarnation as per the new cloned CRM database . But after moving back to old database LGEIL , backup getting errored out with the error mentioned above ..
Details :
The above error occurs when the Database target Incarnation is not current in the Recovery Catalog.
The incarnation of a database is a number that is used to identify a version of the database.A database incarnation is created whenever you open the database with the RESETLOGS.
V$DATABASE_INCARNATION displays information about all database incarnations. Oracle creates
a new incarnation whenever a database is opened with the RESETLOGS option. Records about the
current and immediately previous incarnation are also contained in the V$DATABASE view.
To Reference the corresponding Incarnation information in the recovery catalog review the RC_DATABASE_INCARNATION and RC_DATABASE views.
à RC_DATABASE_INCARNATION lists information about all database incarnations registered in the recovery catalog.
à RC_DATABASE lists information about the databases registered in the recovery catalog. It corresponds to the V$DATABASE view in your target database.
Solution :
Connect to the RMAN schema as rman user e.g.
sqlplus rman/rman@EMREP
SQL> SELECT dbid, name, dbinc_key, resetlogs_change#, resetlogs_time FROM rc_database_incarnation WHERE dbid=1243244440 ORDER BY resetlogs_time;
DBID NAME DBINC_KEY RESETLOGS_CHANGE# RESETLOGS_TIME
---------- -------- ---------- ----------------- --------------------
1243244440 LGEIL 2812522 1 08-JUL-2007 06:09:28
1243244440 LGEIL 2812503 9.6778E+12 08-JUL-2007 07:11:27
1243244440 CRM 5026006 1.1191E+13
Now , here our current incarnation is 5026006 (CRM ) and we have to change it to 2812503 ( LGEIL ) .
Incarnation time of your database ( LGEIL in our case ) can be confirmed from v$database ..
SQL> select RESETLOGS_TIME , RESETLOGS_CHANGE# , PRIOR_RESETLOGS_CHANGE# , PRIOR_RESETLOGS_TIME from v$database;
RESETLOGS_TIME RESETLOGS_CHANGE# PRIOR_RESETLOGS_CHANGE# PRIOR_RESETLOGS_TIME
-------------------- ------------------------ ---------------------------- --------------------
Here it’s “
Now , reset the incarnation …..
[csnetnorthdb:oracle] /northact/LGEIL/rman/scripts> rman target / catalog rman/rman@EMREP
Recovery Manager: Release 9.2.0.8.0 - 64bit Production
Copyright (c) 1995, 2002, Oracle Corporation. All rights reserved.
connected to target database: LGEIL (DBID=1243244440)
connected to recovery catalog database
RMAN> list incarnation;
No rows will be selected here ..
RMAN>
RMAN> RESET DATABASE TO INCARNATION 2812503;
database reset to incarnation 2812503 in recovery catalog
RMAN> resync catalog;
starting full resync of recovery catalog
full resync complete
RMAN> list incarnation;
List of Database Incarnations
DB Key Inc Key DB Name DB ID CUR Reset SCN Reset Time
------- ------- -------- ---------------- --- ---------- ----------
2812502 2812522 LGEIL 1243244440 NO 1
2812502 2812503 LGEIL 1243244440 YES 9677807775711
2812502 5026006 CRM 1243244440 NO 11190641794400
Now ..you can take the backup
Saturday, June 13, 2009
Saturday, June 6, 2009
ORA-12838: cannot read/modify an object after modifying it in parallel
Problem Description
SQL> INSERT /*+APPEND NOLOGGING */ INTO STATION_RATE(ID,COUNTRY,CODE,PURCHASE_RATE,STATION_ID) SELECT
ID.NEXTVAL, COUNTRY,CODE,PURCHASE_RATE, 4 from STATION_RATE_DUMMY;
7561 rows created.
SQL> INSERT /*+APPEND NOLOGGING */ INTO STATION_RATE(ID,COUNTRY,CODE,PURCHASE_RATE,STATION_ID) SELECT
ID.NEXTVAL, COUNTRY,CODE,PURCHASE_RATE, 5 from STATION_RATE_DUMMY;
INSERT /*+APPEND NOLOGGING */ INTO STATION_RATE(ID,COUNTRY,CODE,PURCHASE_RATE,STATION_ID) SELECT
*
ERROR at line 1:
ORA-12838: cannot read/modify an object after modifying it in parallel
Cause of the Problem
A table is modified in parallel or with direct path load in a transaction. Now within the same transaction if an attempt was made to read or modification statements on a table then ORA-12838 will occur. In oracle within same transaction table is modified with direct path load or parallel and then access of it is not permitted.
Solution of the Problem
Break up the transaction into two or rewrite the transaction. You can break the transaction into two simply after doing a commit after direct path load or parallel modification of the table.
SQL> commit;
Commit complete.
SQL> INSERT /*+APPEND NOLOGGING */ INTO STATION_RATE(ID,COUNTRY,CODE,PURCHASE_RATE,STATION_ID) SELECT
ID.NEXTVAL, COUNTRY,CODE,PURCHASE_RATE, 5 from STATION_RATE_DUMMY;
7561 rows created.
As now data is loaded direct path load so we can't read data from the table unless we do a commit.
SQL> select count(*) from station_rate;
select count(*) from station_rate
*
ERROR at line 1:
ORA-12838: cannot read/modify an object after modifying it in parallel
SQL> commit;
Commit complete.
SQL> select count(*) from station_rate;
COUNT(*)
----------
53228
SQL> INSERT /*+APPEND NOLOGGING */ INTO STATION_RATE(ID,COUNTRY,CODE,PURCHASE_RATE,STATION_ID) SELECT
ID.NEXTVAL, COUNTRY,CODE,PURCHASE_RATE, 4 from STATION_RATE_DUMMY;
7561 rows created.
SQL> INSERT /*+APPEND NOLOGGING */ INTO STATION_RATE(ID,COUNTRY,CODE,PURCHASE_RATE,STATION_ID) SELECT
ID.NEXTVAL, COUNTRY,CODE,PURCHASE_RATE, 5 from STATION_RATE_DUMMY;
INSERT /*+APPEND NOLOGGING */ INTO STATION_RATE(ID,COUNTRY,CODE,PURCHASE_RATE,STATION_ID) SELECT
*
ERROR at line 1:
ORA-12838: cannot read/modify an object after modifying it in parallel
Cause of the Problem
A table is modified in parallel or with direct path load in a transaction. Now within the same transaction if an attempt was made to read or modification statements on a table then ORA-12838 will occur. In oracle within same transaction table is modified with direct path load or parallel and then access of it is not permitted.
Solution of the Problem
Break up the transaction into two or rewrite the transaction. You can break the transaction into two simply after doing a commit after direct path load or parallel modification of the table.
SQL> commit;
Commit complete.
SQL> INSERT /*+APPEND NOLOGGING */ INTO STATION_RATE(ID,COUNTRY,CODE,PURCHASE_RATE,STATION_ID) SELECT
ID.NEXTVAL, COUNTRY,CODE,PURCHASE_RATE, 5 from STATION_RATE_DUMMY;
7561 rows created.
As now data is loaded direct path load so we can't read data from the table unless we do a commit.
SQL> select count(*) from station_rate;
select count(*) from station_rate
*
ERROR at line 1:
ORA-12838: cannot read/modify an object after modifying it in parallel
SQL> commit;
Commit complete.
SQL> select count(*) from station_rate;
COUNT(*)
----------
53228
Thursday, May 28, 2009
Alert log monitoring scripts
export SID=ILPROD
export ORACLE_HOME=/u11/app/ilproddb/9.2.0
export DIR=$ORACLE_HOME/Scr
> $DIR/alert_${SID}.hist
> $DIR/alert.err
cd /u11/app/ilproddb/9.2.0/admin/ILPROD_ilerpdb01/bdump
tail -100 alert_${SID}.log > $DIR/alert_${SID}.hist
cat $DIR/alert_${SID}.hist |grep ORA- |grep -v "ORA-01001: invalid cursor" |grep -v "17281" |grep -v "ORA-000060: Deadlock de
tected" > $DIR/alert.err
if [ `cat $DIR/alert.err|wc -l` -gt 0 ]
then
mailx -s "${SID} ALERT : ORA- ERRORS" erpdba@lgezbuy.com < $DIR/alert.err
fi
export ORACLE_HOME=/u11/app/ilproddb/9.2.0
export DIR=$ORACLE_HOME/Scr
> $DIR/alert_${SID}.hist
> $DIR/alert.err
cd /u11/app/ilproddb/9.2.0/admin/ILPROD_ilerpdb01/bdump
tail -100 alert_${SID}.log > $DIR/alert_${SID}.hist
cat $DIR/alert_${SID}.hist |grep ORA- |grep -v "ORA-01001: invalid cursor" |grep -v "17281" |grep -v "ORA-000060: Deadlock de
tected" > $DIR/alert.err
if [ `cat $DIR/alert.err|wc -l` -gt 0 ]
then
mailx -s "${SID} ALERT : ORA- ERRORS" erpdba@lgezbuy.com < $DIR/alert.err
fi
Thursday, March 5, 2009
Tuesday, February 24, 2009
Invalid CATALOG and CATAPROC after after upgradation to 10g
HI ,
We upgraded our 9i database to 10g and after that our CATALOG & CATALOG got Invalid ..
SQL> select comp_name, version, status,comp_id from dba_registry;
COMP_NAME VERSION STATUS COMP_ID
--------------------------------------------- ------------------------------ ----------- -----------
Oracle Database Catalog Views 10.2.0.3.0 INVALID CATALOG
Oracle Database Packages and Types 10.2.0.3.0 INVALID CATPROC
Oracle Real Application Clusters 10.2.0.3.0 INVALID RAC
JServer JAVA Virtual Machine 10.2.0.3.0 VALID JAVAVM
Oracle XDK 10.2.0.3.0 VALID XML
Oracle Database Java Packages 10.2.0.3.0 VALID CATJAVA
Oracle interMedia 10.2.0.3.0 VALID ORDIM
Spatial 10.2.0.3.0 VALID SDO
Oracle Text 10.2.0.3.0 VALID CONTEXT
OLAP Analytic Workspace 10.2.0.3.0 INVALID APS
Oracle OLAP API 10.2.0.3.0 VALID XOQ
OLAP Catalog 10.2.0.3.0 VALID AMD
Solution :
$sqlplus "/as sysdba"
SQL> startup upgrade
-- Use 'startup migrate' if database version is lower than 9.2.
SQL > @?/rdbms/admin/catalog.sql
SQL > @?/rdbms/admin/catproc.sql
SQL > @?/rdbms/admin/utlrp.sql
SQL> select comp_name, version, status,comp_id from dba_registry;
COMP_NAME VERSION STATUS COMP_ID
--------------------------------------------- ------------------------------ ----------- ---------
Oracle Database Catalog Views 10.2.0.3.0 VALID CATALOG
Oracle Database Packages and Types 10.2.0.3.0 INVALID CATPROC
Oracle Real Application Clusters 10.2.0.3.0 INVALID RAC
JServer JAVA Virtual Machine 10.2.0.3.0 VALID JAVAVM
Oracle XDK 10.2.0.3.0 VALID XML
Oracle Database Java Packages 10.2.0.3.0 VALID CATJAVA
Oracle interMedia 10.2.0.3.0 VALID ORDIM
Spatial 10.2.0.3.0 VALID SDO
Oracle Text 10.2.0.3.0 VALID CONTEXT
OLAP Analytic Workspace 10.2.0.3.0 INVALID APS
Oracle OLAP API 10.2.0.3.0 VALID XOQ
OLAP Catalog 10.2.0.3.0 VALID AMD
CATALOG is valid now . But CATAPROC is still invalid . To valid the same ...
SQL> exec DBMS_REGISTRY.valid('CATPROC');
For more information refer to the docs 457861.1
We upgraded our 9i database to 10g and after that our CATALOG & CATALOG got Invalid ..
SQL> select comp_name, version, status,comp_id from dba_registry;
COMP_NAME VERSION STATUS COMP_ID
--------------------------------------------- ------------------------------ ----------- -----------
Oracle Database Catalog Views 10.2.0.3.0 INVALID CATALOG
Oracle Database Packages and Types 10.2.0.3.0 INVALID CATPROC
Oracle Real Application Clusters 10.2.0.3.0 INVALID RAC
JServer JAVA Virtual Machine 10.2.0.3.0 VALID JAVAVM
Oracle XDK 10.2.0.3.0 VALID XML
Oracle Database Java Packages 10.2.0.3.0 VALID CATJAVA
Oracle interMedia 10.2.0.3.0 VALID ORDIM
Spatial 10.2.0.3.0 VALID SDO
Oracle Text 10.2.0.3.0 VALID CONTEXT
OLAP Analytic Workspace 10.2.0.3.0 INVALID APS
Oracle OLAP API 10.2.0.3.0 VALID XOQ
OLAP Catalog 10.2.0.3.0 VALID AMD
Solution :
$sqlplus "/as sysdba"
SQL> startup upgrade
-- Use 'startup migrate' if database version is lower than 9.2.
SQL > @?/rdbms/admin/catalog.sql
SQL > @?/rdbms/admin/catproc.sql
SQL > @?/rdbms/admin/utlrp.sql
SQL> select comp_name, version, status,comp_id from dba_registry;
COMP_NAME VERSION STATUS COMP_ID
--------------------------------------------- ------------------------------ ----------- ---------
Oracle Database Catalog Views 10.2.0.3.0 VALID CATALOG
Oracle Database Packages and Types 10.2.0.3.0 INVALID CATPROC
Oracle Real Application Clusters 10.2.0.3.0 INVALID RAC
JServer JAVA Virtual Machine 10.2.0.3.0 VALID JAVAVM
Oracle XDK 10.2.0.3.0 VALID XML
Oracle Database Java Packages 10.2.0.3.0 VALID CATJAVA
Oracle interMedia 10.2.0.3.0 VALID ORDIM
Spatial 10.2.0.3.0 VALID SDO
Oracle Text 10.2.0.3.0 VALID CONTEXT
OLAP Analytic Workspace 10.2.0.3.0 INVALID APS
Oracle OLAP API 10.2.0.3.0 VALID XOQ
OLAP Catalog 10.2.0.3.0 VALID AMD
CATALOG is valid now . But CATAPROC is still invalid . To valid the same ...
SQL> exec DBMS_REGISTRY.valid('CATPROC');
COMP_NAME VERSION STATUS COMP_ID
------------------------- ---------------------------- ----------- ------------------------------
Oracle Database Catalog Views 10.2.0.3.0 VALID CATALOG
Oracle Database Packages and Types 10.2.0.3.0 VALID CATPROC
Oracle Real Application Clusters 10.2.0.3.0 INVALID
RAC JServer JAVA Virtual Machine 10.2.0.3.0 VALID
JAVAVM Oracle XDK 10.2.0.3.0 VALID XML
Oracle Database Java Packages 10.2.0.3.0 VALID CATJAVA
Oracle interMedia 10.2.0.3.0 VALID ORDIM Spatial 10.2.0.3.0 VALID
SDO Oracle Text 10.2.0.3.0 VALID CONTEXT
OLAP Analytic Workspace 10.2.0.3.0 INVALID APS
Oracle OLAP API 10.2.0.3.0 VALID XOQ OLAP Catalog 10.2.0.3.0 VALID AMD
------------------------- ---------------------------- ----------- ------------------------------
Oracle Database Catalog Views 10.2.0.3.0 VALID CATALOG
Oracle Database Packages and Types 10.2.0.3.0 VALID CATPROC
Oracle Real Application Clusters 10.2.0.3.0 INVALID
RAC JServer JAVA Virtual Machine 10.2.0.3.0 VALID
JAVAVM Oracle XDK 10.2.0.3.0 VALID XML
Oracle Database Java Packages 10.2.0.3.0 VALID CATJAVA
Oracle interMedia 10.2.0.3.0 VALID ORDIM Spatial 10.2.0.3.0 VALID
SDO Oracle Text 10.2.0.3.0 VALID CONTEXT
OLAP Analytic Workspace 10.2.0.3.0 INVALID APS
Oracle OLAP API 10.2.0.3.0 VALID XOQ OLAP Catalog 10.2.0.3.0 VALID AMD
For more information refer to the docs 457861.1
Saturday, February 14, 2009
Metalink Important Note IDs
1. How to run OATM migration utility ---- Note:404954.1
2. 11.5.10 Oracle E-Business Suite Consolidated Update 2 (CU2)---- Note:316366.1
3. Upgrading Oracle Applications ---- Note:289788.1
4. E-Business Suite Diagnostics Patch Installation Guide ---- Note:167000.1
5. Step by Step Troubleshooting Guide to Solve APPS Invalid Objects ---- Note:113947.1
6. Compatibility Matrix for Export And Import Between Different Oracle Versions ----Note:132904.1
7. ORA-06550 Running Sys.Utl_recomp.Recomp_parallel(1) During ADPATCH or ADADMIN Session ---- Note:362727.1
8. Now Available: Oracle E-Business Suite Release 12.0.4 ---- Note:556312.1
9. Cloning Oracle Applications Release 11i with Rapid Clone ---- Note:230672.1
10.Upgrading Developer 6i with Oracle Applications 11i ---- Note:125767.1
11.The Basics About Report Review Agent (FNDFS) on 11i ---- Note:111383.1
12.How To Use FNDCPASS to Change The Oracle Users, APPS, APPLSYS and Application Module Passwords (INV, AR, AP, etc.) For Applications 11.5 in Unix ---- Note:159244.1
13.USAGE OF INDIA LOCALIZATION PATCH TOOL (INDPATCH) ---- Note:376756.1
14.Sharing the Application Tier File System in Oracle Applications 11i ----Note:233428.1
15.Shared APPL_TOP FAQ ---- Note:243880.1
16.Using a Staged Applications 11i System to Reduce Patching Downtime ---- Note:242480.1
17.Summary of Possible Reasons and Solutions for the Problem Where All Concurrent Requests Stuck in Pending Phase ---- Note:182154.1
18.Release 11i Adpatch Basics ---- Note:181665.1
19.How to Apply an 11i Patch When adpatch is Already Running ---- Note:175485.1
20.How to Create a Custom Concurrent Manager ---- Note:170524.1
21.Concurrent Manager Questions and Answers Relating to Generic Platform ----Note:105133.1
22.E-Business Suite Diagnostics Patch Installation Guide ---- Note:167000.1
2. 11.5.10 Oracle E-Business Suite Consolidated Update 2 (CU2)---- Note:316366.1
3. Upgrading Oracle Applications ---- Note:289788.1
4. E-Business Suite Diagnostics Patch Installation Guide ---- Note:167000.1
5. Step by Step Troubleshooting Guide to Solve APPS Invalid Objects ---- Note:113947.1
6. Compatibility Matrix for Export And Import Between Different Oracle Versions ----Note:132904.1
7. ORA-06550 Running Sys.Utl_recomp.Recomp_parallel(1) During ADPATCH or ADADMIN Session ---- Note:362727.1
8. Now Available: Oracle E-Business Suite Release 12.0.4 ---- Note:556312.1
9. Cloning Oracle Applications Release 11i with Rapid Clone ---- Note:230672.1
10.Upgrading Developer 6i with Oracle Applications 11i ---- Note:125767.1
11.The Basics About Report Review Agent (FNDFS) on 11i ---- Note:111383.1
12.How To Use FNDCPASS to Change The Oracle Users, APPS, APPLSYS and Application Module Passwords (INV, AR, AP, etc.) For Applications 11.5 in Unix ---- Note:159244.1
13.USAGE OF INDIA LOCALIZATION PATCH TOOL (INDPATCH) ---- Note:376756.1
14.Sharing the Application Tier File System in Oracle Applications 11i ----Note:233428.1
15.Shared APPL_TOP FAQ ---- Note:243880.1
16.Using a Staged Applications 11i System to Reduce Patching Downtime ---- Note:242480.1
17.Summary of Possible Reasons and Solutions for the Problem Where All Concurrent Requests Stuck in Pending Phase ---- Note:182154.1
18.Release 11i Adpatch Basics ---- Note:181665.1
19.How to Apply an 11i Patch When adpatch is Already Running ---- Note:175485.1
20.How to Create a Custom Concurrent Manager ---- Note:170524.1
21.Concurrent Manager Questions and Answers Relating to Generic Platform ----Note:105133.1
22.E-Business Suite Diagnostics Patch Installation Guide ---- Note:167000.1
Tuesday, February 10, 2009
Solution for 500 Internal server Error in R12
Error:
500 Internal Server Error
java.lang.NoClassDefFoundError
at oracle.apps.fnd.sso.AppsLoginRedirect.AppsSetting(AppsLoginRedirect.java:120)
at oracle.apps.fnd.sso.AppsLoginRedirect.init(AppsLoginRedirect.java:161)
at com.evermind[Oracle Containers for J2EE 10g (10.1.3.0.0)
].server.http.HttpApplication.loadServlet(HttpApplication.java:2231)
at com.evermind[Oracle Containers for J2EE 10g (10.1.3.0.0)
].server.http.HttpApplication.findServlet(HttpApplication.java:4617)
at com.evermind[Oracle Containers for J2EE 10g (10.1.3.0.0)
].server.http.HttpApplication.findServlet(HttpApplication.java:4541)
at com.evermind[Oracle Containers for J2EE 10g (10.1.3.0.0)
].server.http.HttpApplication.getRequestDispatcher(HttpApplication.java:2821)
at com.evermind[Oracle Containers for J2EE 10g (10.1.3.0.0)
].server.http.HttpRequestHandler.doProcessRequest(HttpRequestHandler.java:740)
at com.evermind[Oracle Containers for J2EE 10g (10.1.3.0.0)
].server.http.HttpRequestHandler.processRequest(HttpRequestHandler.java:451)
at com.evermind[Oracle Containers for J2EE 10g (10.1.3.0.0)
].server.http.AJPRequestHandler.run(AJPRequestHandler.java:299)
at com.evermind[Oracle Containers for J2EE 10g (10.1.3.0.0)
].server.http.AJPRequestHandler.run(AJPRequestHandler.java:187)
Solution:
Autoconfig
1) Bring down opmn services.
Path: /tst03/oracle/VIS/inst/apps/VIS_bkpsvr/admin/scripts>./adopmnctl.sh stop apps/apps
2) Verify no process is listening for s_java_object_cache_port value
3) Change s_java_object_cache_port on all the nodes in the context file
Path: /tst03/oracle/VIS/inst/apps/VIS_bkpsvr/appl/admin>vi VIS_bkpsvr.xml
Before:
12348
After:
12349
4) Run autoconfig on apps node.
Path: /tst03/oracle/VIS/inst/apps/VIS_bkpsvr/admin/scripts>./ adautocfg.sh
5) Start opmn services on apps node.
Path: /tst03/oracle/VIS/inst/apps/VIS_bkpsvr/admin/scripts>./adopmnctl.sh start apps/apps
500 Internal Server Error
java.lang.NoClassDefFoundError
at oracle.apps.fnd.sso.AppsLoginRedirect.AppsSetting(AppsLoginRedirect.java:120)
at oracle.apps.fnd.sso.AppsLoginRedirect.init(AppsLoginRedirect.java:161)
at com.evermind[Oracle Containers for J2EE 10g (10.1.3.0.0)
].server.http.HttpApplication.loadServlet(HttpApplication.java:2231)
at com.evermind[Oracle Containers for J2EE 10g (10.1.3.0.0)
].server.http.HttpApplication.findServlet(HttpApplication.java:4617)
at com.evermind[Oracle Containers for J2EE 10g (10.1.3.0.0)
].server.http.HttpApplication.findServlet(HttpApplication.java:4541)
at com.evermind[Oracle Containers for J2EE 10g (10.1.3.0.0)
].server.http.HttpApplication.getRequestDispatcher(HttpApplication.java:2821)
at com.evermind[Oracle Containers for J2EE 10g (10.1.3.0.0)
].server.http.HttpRequestHandler.doProcessRequest(HttpRequestHandler.java:740)
at com.evermind[Oracle Containers for J2EE 10g (10.1.3.0.0)
].server.http.HttpRequestHandler.processRequest(HttpRequestHandler.java:451)
at com.evermind[Oracle Containers for J2EE 10g (10.1.3.0.0)
].server.http.AJPRequestHandler.run(AJPRequestHandler.java:299)
at com.evermind[Oracle Containers for J2EE 10g (10.1.3.0.0)
].server.http.AJPRequestHandler.run(AJPRequestHandler.java:187)
Solution:
Autoconfig
1) Bring down opmn services.
Path: /tst03/oracle/VIS/inst/apps/VIS_bkpsvr/admin/scripts>./adopmnctl.sh stop apps/apps
2) Verify no process is listening for s_java_object_cache_port value
3) Change s_java_object_cache_port on all the nodes in the context file
Path: /tst03/oracle/VIS/inst/apps/VIS_bkpsvr/appl/admin>vi VIS_bkpsvr.xml
Before:
After:
4) Run autoconfig on apps node.
Path: /tst03/oracle/VIS/inst/apps/VIS_bkpsvr/admin/scripts>./ adautocfg.sh
5) Start opmn services on apps node.
Path: /tst03/oracle/VIS/inst/apps/VIS_bkpsvr/admin/scripts>./adopmnctl.sh start apps/apps
ORA-19595: archivelog already included in backup conversation
If a standby database is brought down and then later
restarted then RMAN backups may fail with:
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03009: failure of backup command on ORA_DISK_1 channel at 05/18/2004 10:00:56
ORA-19595: archivelog /ora_logs/archivelogs/2_6512.arc already included in backup conversation
Workaround:
Find the duplicate archivelog names using following query -
select al.recid, al.name from v$archived_log al
where al.status = 'A' and al.standby_dest= 'NO'
minus
select al.recid, al.name from v$archived_log al,
(select max(recid) recid
from v$archived_log
where status = 'A'
group by name) keepal
where al.status = 'A'
and al.standby_dest = 'NO'
and keepal.recid = al.recid;
Then:
RMAN> catalog archivelog '';
This will register the missing archivelogs.
restarted then RMAN backups may fail with:
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03009: failure of backup command on ORA_DISK_1 channel at 05/18/2004 10:00:56
ORA-19595: archivelog /ora_logs/archivelogs/2_6512.arc already included in backup conversation
Workaround:
Find the duplicate archivelog names using following query -
select al.recid, al.name from v$archived_log al
where al.status = 'A' and al.standby_dest= 'NO'
minus
select al.recid, al.name from v$archived_log al,
(select max(recid) recid
from v$archived_log
where status = 'A'
group by name) keepal
where al.status = 'A'
and al.standby_dest = 'NO'
and keepal.recid = al.recid;
Then:
RMAN> catalog archivelog '
This will register the missing archivelogs.
Monday, February 9, 2009
ORA-12557: TNS:protocol adapter not loadable
Problem DescriptionIn my machine I had oracle 10g home , using sqlplus of 10g I could connect to an Oracle database 10g. Now I have installed a new oracle 11g home, but using sqlplus of 11g I could not connect to Oracle database 10g. Below is an example,
With 10.2g sqlplus I can connect to 10g database.
C:\>e:\oracle\product\10.2.0\db_1\bin\BIN\sqlplus.exe maximsg/a@192.168.100.160/tiger
SQL*Plus: Release 10.2.0.1.0 - Production on Mon Jan 26 01:54:10 2009
Copyright (c) 1982, 2005, Oracle. All rights reserved.
Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options
With 11g sqlplus I could not connect to oracle database 10g. It returns error message, ORA-12557: TNS:protocol adapter not loadable.
C:\>d:\app\oracle\BIN\sqlplus.exe maximsg/a@192.168.100.160/tiger
SQL*Plus: Release 11.1.0.6.0 - Production on Mon Jan 26 01:55:00 2009
Copyright (c) 1982, 2007, Oracle. All rights reserved.
ERROR:
ORA-12557: TNS:protocol adapter not loadable
Cause of the Problem
The problem happens because of two ORACLE_HOME are installed on your system. As after 10g you hav e installed 11g so whenever you write sqlplus by default new 11g binaries are selected and raises ORA-12557. But working with old home 10g works fine.
Simply sqlplus does not work but 10g home location sqlplus (e:\oracle\product\10.2.0\db_1\bin\BIN\sqlplus.exe ) works.
C:\>sqlplus.exe maximsg/a@192.168.100.160/tiger
SQL*Plus: Release 11.1.0.6.0 - Production on Mon Jan 26 22:47:08 2009
Copyright (c) 1982, 2007, Oracle. All rights reserved.
ERROR:
ORA-12557: TNS:protocol adapter not loadable
Connecting to old oracle database using new binaries are not supported in oracle and error will return.
Solution of the Problem
Only setting ORACLE_HOME is not sufficient on windows environment. Because the location is taken from windows registry. So either uninstall newer oracle home or explicitly pointing to old oracle binaries will solve the problem.
Here using pointing to old home,
C:\>e:\oracle\product\10.2.0\db_1\bin\BIN\sqlplus.exe maximsg/a@192.168.100.160/tiger
SQL*Plus: Release 10.2.0.1.0 - Production on Mon Jan 26 01:54:10 2009
Copyright (c) 1982, 2005, Oracle. All rights reserved.
Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options
Alternatively you can follow below.
Step 01: Remove registry entries of new ORACLE_HOME.
To do this,
i)Type regedit on Run.
ii)Press enter and expand HKEY_LOCAL_MACHINE.
iii)Then expand SOFTWARE and then expand ORACLE tab. There you will see two oracle home. Right click on the one that you want to delete and then select delete. If prompting click yes.
Step 02: Remove any environmental variable.
i)Right click on My computer icon. Then select properties.
ii)System properties window will appear. Click on Advanced tab.
iii)Select environmental variables.
iv)Find the variable/system variable path and ORACLE_HOME. Edit or modify them so that it point to you desired sql*plus.
Usually in the PATH system variable you will get both ORACLE_HOME path. Just remove one path. Of course if you have ORACLE_HOME variable settings first delete the key.
Wednesday, January 14, 2009
The Function Is Not Available Under The Responsibility
Cause
This issue is caused by launching the menu item before the Compile Security has completed.When a function is added to a menu , a concurrent program 'Compile Security' is submitted which
loads the data in Fnd_Compiled_Menu_Functions table.
Whenever a function is accessed, security code checks Fnd_Compiled_Menu_Functions table to
determine whether that function is accessible under the given security context or not. So if this
function is accessed before the 'Compile Security' program is finished, the function cache gets
loaded with stale data and the reported error message is thrown.
So after modifying the 2 menu definitions associated with 'System Administrator' and 'Application
Developer' make sure that 2 concurrent requests submitted for the above 2 modifications completed
normally and then try to access the functions.
Further once this error occurs, it will be cached which means that even after the menu item has
been flagged as available, it the error will be returned from the cache.
Solution
-- To implement the solution, please execute the following steps::
1) Manually run the 'Compile Security' concurrent program with parameter "YES"
2) Shut down Middle Tier (Apache)
3) Restart Middle tier
4) Test the menu item
Subscribe to:
Posts (Atom)