Oracle EBS Application and Database (11.1.0.7) Running Slow

Please Visit http://www.conacent.com/?page_id=218


 Problem :-
 Application and Database Slows down for 15-30 mins.
I have observed that one of the M00N background process taking huge resources. The Load increases to 6-7. I have found in the trace file of M00N some error. Error found in M00N trace file. Error is as follows
" kewrpanp - Clearing the error, continue to next tableDDE: Problem Key 'ORA 12751' was flood controlled (0x6) (no incident)
ORA-12751: cpu time or run time policy violation
*** KEWROCISTMTEXEC - encountered error: (ORA-12751: cpu time or run time policy violation
)
*** SQLSTR: total-len=350, dump-len=240,
STR={delete from WRH$_MEM_DYNAMIC_COMP tab where (:beg_snap <= tab.snap_id and tab.snap_id <= :end_snap and dbid = :dbid) and not exists (select 1 from WRM$_BASELINE b where (tab.dbid = b.dbid) and }
DDE: Problem Key 'ORA 12751' was flood controlled (0x6) (no incident)
ORA-12751: cpu time or run time policy violation
kewrpanp - Failed to purge non-partitioned table, tbid=103, errcode=13509"


Solution :-
These are the post installation steps. (after you have applied patch 10279045)

Post-install steps:
-------------------------
Apply the patch using OPatch (the normal way you would apply the patch)
( Note : Steps 1 and 5 to be followed for RAC environment only)
.
1. For RAC environment only,
set cluster_database=false in the init.ora
.
2. Startup the database in upgrade mode
SQL> startup upgrade
.
3. After the patch has been applied please reload the packages into
the database. To do this connect as SYSDBA and execute the following;
(Note: Order is important)
.
SQL> @?/rdbms/admin/dbmsstat.sql
SQL> @?/rdbms/admin/prvtstas.plb
SQL> @?/rdbms/admin/prvtstai.plb
SQL> @?/rdbms/admin/prvtstat.plb
.
4. Shutdown the database
.
5. For RAC environment only,
set cluster_database=true in the init.ora
.
6. Startup the database in normal mode
SQL> startup



Could you please confirm that you implemented the below things.
1. Check from SYSADMIN for IO throughput of the host.

2. Manual purging of old Optimizer STATS.
REFER : SYSAUX Grows Because Optimizer Stats History is Not Purged (Doc ID 1055547.1)
REFER : Statistics space used by SM/OPTSTAT in the SYSAUX tablespace is not reclaimed after purging (Doc ID 454678.1)
REFER : Suggestions if your SYSAUX Tablespace grows rapidly or too large (Doc ID 1292724.1)

Scheduler/Prereleaser Manager is showing status System Hold, Fix Manager before resetting counters

Please Visit http://www.conacent.com/?page_id=218

Problem description -
"Scheduler/Prereleaser Manager" is showing status "System Hold, Fix Manager before resetting counters".

Problem solution -
To implement the solution, please execute the following steps:

1. Stop all middle tier services including the concurrent managers.
Please make sure that no FNDLIBR, FNDSM, or any dead process is
running.

2. Stop the database.

3. Start the database.

4. Go to cd $FND_TOP/bin
$ adrelink.sh force=y link_debug=y "fnd FNDLIBR"
$ adrelink.sh force=y link_debug=y "fnd FNDFS"
$ adrelink.sh force=y link_debug=y "fnd FNDCRM"
$ adrelink.sh force=y link_debug=y "fnd FNDSM"

5. Run the CMCLEAN.SQL script from the referenced note below (don't forget to commit).
Note 134007.1 CMCLEAN.SQL - Non Destructive Script to Clean Concurrent Manager Tables

6. Execute the following SQL:
select CONCURRENT_QUEUE_NAME from FND_CONCURRENT_QUEUES where
CONCURRENT_QUEUE_NAME like 'FNDSM%';

7. Start the middle tier services including your concurrent manager.

8. Retest the issue.

Find the object which are locked / ORA-04021: timeout occurred while waiting to lock object

Please Visit http://www.conacent.com/?page_id=218

Find the object which are locked

This query is for finding the object_name which is been locked
select oracle_username, os_user_name,SESSION_ID,locked_mode,object_name,object_type from v$locked_object a, dba_objects b where a.object_id=b.object_id;

This query is for finding the SID of the locked object_name
SELECT * FROM v$access WHERE OBJECT = '<object_name>';

This query is for finding the SID and Serial# of the locked SID
SELECT s.inst_id, s.sid, s.serial#, p.spid, s.username, s.program FROM gv$session s JOIN gv$process p ON p.addr = s.paddr AND p.inst_id = s.inst_id where s.sid=<Locked SID>;

This query is for killing the sid
ALTER SYSTEM KILL SESSION 's.sid, s.serial#';


SELECT a.request_id, d.sid, d.serial# ,d.osuser,d.process , c.SPID
FROM apps.fnd_concurrent_requests a,
apps.fnd_concurrent_processes b,
v$process c,
v$session d
WHERE a.controlling_manager = b.concurrent_process_id
AND c.pid = b.oracle_process_id
AND b.session_id=d.audsid
AND a.request_id = &Request_ID;

RMAN in catalog mode implemented on Oracle EBS

Target Database - Means the database need to Backed up.

Repository (Catalog) Database - Means the database where we will store all the information of the target database. Which need to be different database all about. This database need to be same version or less than the target database.



1. Modify TNSNAMES.ORA(Into Both database Target as well as Repository(catalog) )
--------------------------------------------------------------------------------------------------
catalog$ cd $ORACLE_HOME/network/admin
catalog$ vi tnsnames.ora

-- add the following:
REPOS =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.119)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = master)
)
)
-- save change

catalog$ tnsping master
catalog$ ping 192.168.1.119

2. Put the Target Database(MASTER) into Archive Log Mode
---------------------------------------------------------------------
master$ sqlplus /nolog
SQL> conn / as sysdba
SQL> shutdown immediate;
SQL> startup mount exclusive;
SQL> alter database archivelog;
SQL> alter database open;
SQL> SELECT group#, thread#, sequence#, archived, status, first_time FROM v$log;
SQL> alter system switch logfile;
SQL> SELECT group#, thread#, sequence#, archived, status, first_time FROM v$log;
SQL> SELECT dbid, name FROM v$database;
SQL> select log_mode from v$database;
SQL> archive log list;
SQL> select DEST_NAME,STATUS,DESTINATION from V$ARCHIVE_DEST;

3. Create password file in Target database
-----------------------------------------------
$orapwd file=orapw entries= force=
After creating the password please check the view v$pwfile_users.

4. Create Repository Tablespace and Schema (Repository Database)
-----------------------------------------------------------------------------
SQL> conn sys@catalog AS SYSDBA
password: ************
SQL> SELECT file_name FROM dba_data_files;
SQL> SELECT name, value FROM gv$parameter WHERE name like '%block%';

-- Create tablepsace to hold repository
SQL> CREATE TABLESPACE "RMAN"
DATAFILE '/Path/RMAN.dbf' SIZE 50M
AUTOEXTEND ON
BLOCKSIZE 8192
FORCE LOGGING
DEFAULT NOCOMPRESS
EXTENT MANAGEMENT LOCAL UNIFORM SIZE 64K
SEGMENT SPACE MANAGEMENT AUTO
ONLINE;

-- Create rman schema owner
SQL> CREATE USER rman
IDENTIFIED BY oracle1
DEFAULT TABLESPACE RMAN
TEMPORARY TABLESPACE temp
QUOTA UNLIMITED ON RMAN;


SQL> GRANT connect, resource, recovery_catalog_owner TO rman;
OR
SQL> GRANT CREATE session TO rman;
SQL> GRANT recovery_catalog_owner TO rman;
SQL> GRANT execute ON dbms_stats TO rman;

SQL> conn rman#/oracle1@catalog
SQL> SELECT COUNT(*) FROM user_objects;
SQL> exit;


5. Create RMAN Catalog (Repository Database)
-------------------------------------------------------
catalog$ rman target / catalog rman/oracle1@catalog
OR
catalog$ rman catalog=rman/oracle1@catalog

RMAN> create catalog; -- if the tablespace already exists
OR
RMAN> create catalog tablespace 'RMAN'; -- if rman is not the default tablespace
RMAN> exit;


6. Register Database (Repository Database)
-------------------------------------------------
catalog$ rman catalog=rman/oracle1@backup target=sys/password@master;
RMAN> register database;
RMAN> REPORT SCHEMA;  -- Make sure that the registration was successful



7. Configure the Parameters for this Instance.  (Repository Database)
-----------------------------------------------------------------------------
RMAN> show all;    -- For see the all Configure Backup Parameters.
RMAN> configure retention policy to recovery window of 7 days;
RMAN> CONFIGURE BACKUP OPTIMIZATION ON;   -- do not back up unchanged data files
RMAN> CONFIGURE MAXSETSIZE TO 2 G;                  -- make filesize <= 2GB
RMAN> configure default device type to disk;
RMAN> configure controlfile autobackup on;                         --Enable automatic controlfile backup after
each database or archivelog backup.
RMAN> configure channel device type disk format 'uo1\Oracle\Backup%d_DB_%u_%s_%p';




8. Full Resyncronization on a regular basis (Repository Database)
-------------------------------------------------------------------------
RMAN> resync catalog;
RMAN> report need backup;


9. Run Full Backup (Repository Database)
-----------------------------------------------
RMAN> RUN
{
ALLOCATE CHANNEL d1 DEVICE TYPE DISK FORMAT '/home/oracle/backup1/%U';
ALLOCATE CHANNEL d2 DEVICE TYPE DISK FORMAT '/home/oracle/backup2/%U';
BACKUP DATABASE PLUS ARCHIVELOG;
}


10. Report (Repository Database)
--------------------------------------
RMAN> list backup summary;
SQL> ALTER SESSION SET NLS_DATE_FORMAT = 'DD-MON-YYYY HH24:MI:SS';
SQL> desc v$rman_status
SQL> SELECT operation, status, mbytes_processed, start_time, end_time FROM v$rman_status;
RMAN> report need backup;

Concept of RMAN Backup in nocatalog mode

Please Visit http://www.conacent.com/?page_id=218
$rman target /

RMAN> show all;
using target database control file instead of recovery catalog RMAN configuration parameters for database with db_unique_name MSPPROD are:
CONFIGURE RETENTION POLICY TO RECOVERY WINDOW OF 7 DAYS;
CONFIGURE BACKUP OPTIMIZATION OFF; # default
CONFIGURE DEFAULT DEVICE TYPE TO DISK; # default
CONFIGURE CONTROLFILE AUTOBACKUP ON;
CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO '/d03/RMAN_BKP/archive/autobackup_control_file%F';
CONFIGURE DEVICE TYPE DISK BACKUP TYPE TO COMPRESSED BACKUPSET PARALLELISM 1;
CONFIGURE DATAFILE BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default
CONFIGURE ARCHIVELOG BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default
CONFIGURE CHANNEL 1 DEVICE TYPE DISK FORMAT '/d03/RMAN_BKP/archive/databasefiles_%d_%u_%s_%T';
CONFIGURE MAXSETSIZE TO UNLIMITED; # default
CONFIGURE ENCRYPTION FOR DATABASE OFF; # default
CONFIGURE ENCRYPTION ALGORITHM 'AES128'; # default
CONFIGURE COMPRESSION ALGORITHM 'BZIP2'; # default
CONFIGURE ARCHIVELOG DELETION POLICY TO NONE; # default
CONFIGURE SNAPSHOT CONTROLFILE NAME TO '/d03/RMAN_BKP/archive/snapcf_MSPPROD.f';

Configuration of RMAN Backup

========================

CONFIGURE RETENTION POLICY TO RECOVERY WINDOW OF 7 DAYS;
[Remove the previous backupset as per the retention period]

CONFIGURE CONTROLFILE AUTOBACKUP ON;  
[Control file backup set to on]

CONFIGURE BACKUP OPTIMIZATION ON;
[Do not backup unchanged data files ]

CONFIGURE DEFAULT DEVICE TYPE TO DISK; # default
[By default device type will be disk. If you have to change to tape then CONFIGURE DEFAULT DEVICE TYPE TO SBT_TAPE;]

CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO ‘/(put backup directory here)/autobackup_control_file%F’;
[Specific directory to store the backup data. By default directory backed up at $ORACLE_HOME/dbs]

CONFIGURE DEVICE TYPE DISK BACKUP TYPE TO COMPRESSED BACKUPSET PARALLELISM 1;
[Database and archivelog file backup will be taken as in compress mode]

CONFIGURE CHANNEL 1 DEVICE TYPE DISK FORMAT '/disk1/databasefiles_%d_%u_%s_%T’; [Format and the path of the database backup file]

CONFIGURE SNAPSHOT CONTROLFILE NAME TO '/d03/RMAN_BKP/archive/snapcf_MSPPROD.f';
[Snapshot of controlfile backupset as well as the format of it]


$ rman target /

RMAN>run
1>{
2> backup check logical incremental level 0 database plus archivelog delete input tag 'monthly';
3> restore validate database;
4>}

backup check logical incremental level 0 database plus archivelog delete input tag 'monthly';

[This command is used 
          1. To take full database backup as well as archivelog. 
          2. To delete archivelog after backed up.
          3. It will tag the backup with 'monthly'.
          4. Check logical will check bad blocks while backing up the database (Checked physical as else as logical bad blocks in the database).
]

restore validate database;

[This command is used for the checking the backed up file is correct or not]






 
crosscheck backup;
[This command is used for the crosscheck of backup file exist physically or not]

list expired backup;

list backup summary;

report obsolete;

[This command is used for the expired backup file as per the retention period set while configuring the RMAN]



delete obsolete;
 

libobk.a(shr.o) could not be loaded.

http://www.conacent.com

RMAN> run
1> {
2> allocate channel c1 device type SBT_TAPE parms 'ENV=(TDPO_OPTFILE=/usr/tivoli/tsm/client/oracle/bin/tdpo.opt)';

3> backup database plus archivelog;

4> restore validate database; 

5> release channel c1;

6>}

Problem :-

RMAN-00571: =========================================================== RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS =============== RMAN-00571: =========================================================== RMAN-03009: failure of allocate command on ch1 channel at 07/21/2008 11:17:14
ORA-19554: error allocating device, device type: SBT_TAPE, device name:
ORA-27211: Failed to load Media Management Library
Additional information: 2

Solution :-
Step 1. Login to database user.
Step 2. sbttest test   [ This command will check whether tape drive can be monitor by oracle database or not].
Step 3. If not connected it will show error like this
            libobk.a(shr.o) could not be loaded. 
Step 4. Link the libobk.a with tivoli filesystem because the tape drive is manage by tivoli.
            ln -s /usr/tivoli/tsm/client/oracle/bin64/libobk64.a $ORACLE_HOME/lib/libobk.a
Step 5. Test it first using sbttest command.

Gather Schema Statistics fails with Ora-20001 errors after 11G database upgrade

http://www.conacent.com

Cause :-

There are duplicate rows on FND_HISTOGRAM_COLS table for JE_BE_LINE_TYPE_MAP table.Because of this problem, FND_STATS tries to gather histogram information using wrong command and it fails with ora-20001 errors.

Following SQL should have returned one row , not two.
SQL> select column_name, nvl(hsize,254) hsize
from FND_HISTOGRAM_COLS
where table_name = 'JE_BE_LINE_TYPE_MAP'
order by column_name;

COLUMN_NAME HSIZE
------------------------------ ----------
SOURCE 254
SOURCE 254


Solution :-Find out all duplicates and/or obsolete rows in FND_HISTOGRAM_COLS and delete one of them. Remember to take backup of the FND_HISTOGRAM_COLS table before deleting any data.

-- identify duplicate rows

select table_name, column_name, count(*)
from FND_HISTOGRAM_COLS
group by table_name, column_name
having count(*) > 1;

-- Use above results on the following SQL to delete duplicates

delete from FND_HISTOGRAM_COLS
where table_name = '&TABLE_NAME'
and column_name = '&COLUMN_NAME'
and rownum=1;




JE_FR_DAS_010
TYPE_ENREG

JE_FR_DAS_010_NEW
TYPE_ENREG

JE_BE_LINE_TYPE_MAP
SOURCE

JE_BE_LOGS
DECLARATION_TYPE_CODE

JG_ZZ_SYS_FORMATS_ALL_B
JGZZ_EFT_TYPE

JE_BE_VAT_REP_RULES
LINE_TYPE

JE_BE_VAT_REP_RULES
SOURCE

JE_BE_VAT_REP_RULES
VAT_REPORT_BOX



select owner, table_name, stattype_locked
from dba_tab_statistics
where stattype_locked is not null
and owner not in ('SYS');

SQL> select owner, table_name, stattype_locked
from dba_tab_statistics
where stattype_locked is not null
and owner not in ('SYS');


OWNER                          TABLE_NAME                     STATT
------------------------------ ------------------------------ -----
SYSTEM                         TBLMIG_MSG_QTAB                ALL
SYSTEM                         DEF$_AQERROR                   ALL
SYSTEM                         DEF$_AQCALL                    ALL
ODM                            DMS_QUEUE_TABLE                ALL
APPLSYS                        FND_CP_GSM_IPC_AQTBL           ALL
APPLSYS                        WF_NOTIFICATION_OUT            ALL
APPLSYS                        AQ$_WF_CONTROL_P               ALL


7 rows selected.

SQL>

From the above list 3 tables owned by APPS/APPLSYS shows as locked. Use the below API to release these locks and re-attempt the statitics task. Should be Ok Now.

exec dbms_stats.unlock_schema_stats('schema_owner');

In this case it will be APPS and APPLSYS

exec dbms_stats.unlock_schema_stats('APPLSYS');



SQL> exec dbms_stats.unlock_schema_stats('APPLSYS'); 


1. Run query to find the list of indexes which are in unusable status :

select owner, index_name, index_type,
table_owner, table_name, tablespace_name,
status, last_analyzed
from dba_indexes
where status='UNUSABLE';

 

How Can Used Function Multiple Data

One Function Can Used Multiple Data:

/* One Purchase Order Number Can Multiple Time Receipt and Multiple Invoice Generated . */

Function CF_rcp_noFormula return Char

is
vrcp varchar2(200);
begin
for i in (

Select Ad.Invoice_number rcp_no ----
From Rcv_shipment_headers rh,
Rcv_shipment_lines rl,
Po_distributions_all pd,
Ap_invoice_distributions_all ad
Where
pd.PO_DISTRIBUTION_ID = ad.po_distribution_id and
pd.PO_HEADER_ID = rl.PO_HEADER_ID and
pd.PO_LINE_ID = rl.PO_LINE_ID and
rh.SHIPMENT_HEADER_ID = rl.SHIPMENT_HEADER_ID

pd.PO_HEADER_ID = (Select Po_Header_id

From Po_Headers_all

Where Segment1 = :Po_Num ---Put any Purchase Order Number ))

loop
vrcp := i.rcp_no ||','||vrcp;
end loop ;
return substr(vrcp,1,instr(vrcp,',',1)-1); ------All Invoice Number Insert
exception
when no_data_found then
return null;
end;

OUTPUT:

10001, 10002, 1003, 1004,

How Can Create Place Holder In Report Builder

Create Place Holder (Used Function Data ) in Report Builder

Function CF_BANK_NAMEFormula return Char is
cursor c1 is
select
abb.BANK_NAME,
abb.BANK_BRANCH_NAME,
abb.ADDRESS_LINE1,
abb.ADDRESS_LINE2,
abb.ADDRESS_LINE3,
abb.CITY,
abb.ZIP,
aba.BANK_ACCOUNT_NAME,
aba.BANK_ACCOUNT_NUM
from
ap_invoice_payments_all aip,
ap_bank_branches abb,
ap_bank_accounts_all aba,
ap_checks_all ac
where
aip.CHECK_ID =ac.CHECK_ID and
ac.BANK_ACCOUNT_ID = aba.BANK_ACCOUNT_ID
and aba.BANK_BRANCH_ID = abb.BANK_BRANCH_ID
and aip.INVOICE_ID = :invoice_id;
vbank varchar2(150);
vaddr varchar2(1000);
vbranch varchar2(100);
vacc_name varchar2(100);
vacc_no varchar2(100);
begin
for i in c1
loop
vbank := i.bank_name||' '||vbank;
vaddr := i.ADDRESS_LINE1||chr(10)||i.ADDRESS_LINE2||chr(10)||
i.ADDRESS_LINE3||chr(10)||i.city||'-'||i.zip
||' '||vaddr;
vbranch := i.BANK_BRANCH_NAME||' '||vbranch;
vacc_name := i.BANK_ACCOUNT_NAME||' '||vacc_name;
vacc_no := i.BANK_ACCOUNT_NUM||' '||vacc_no;
end loop;
:CP_bank_acc_name := vacc_name; /*All Cp Name Is Place Holder*/

:CP_bank_acc_no := vacc_no;
:CP_branch_name := vbranch;
:CP_branch_addr := vaddr;
return vbank;
exception
when no_data_found then
return null;

end;

11i And 12i Customer Wise Sales Order Price List

/* Customer Wise Sales Order Price List Query OR Sales Order Wise Price List Query And Table Name*/

Select

Ql.OPERAND Price

, Ql.list_header_id /* Order Header Id Match Oe_orders_header_all and show Order Number */

From

Apps.Qp_List_lines Ql /* All types of modifiers including price modifier list lines used to derive factors. The different types of list lines are based on Look-up Type */

, Apps.Qp_pricing_attributes Qt

Where Qt.List_line_id = Ql.list_line_id

And Ql.LIST_HEADER_ID in (Select PRICE_LIST_ID

From JA_IN_CUSTOMER_ADDRESSES Ca --11i

/*12i Table JAI_CMN_CUS_ADDRESSES ,

Holds the Additional Customer information such as excise and sales tax registration numbers*/

Where Ca.CUSTOMER_ID = Ca.CUSTOMER_ID

Group by PRICE_LIST_ID)