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)

Sales Order Type Name in Oracle Apps 11i and 12i

Oracle Apps 11i & 12i Sales Order Type Name Query
Select Ot.NAME /*Order Type Name*/
From apps.oe_transaction_types_tl Ot, /*All Sales Order Type */
apps.oe_order_headers_all h
Where

H.Order_Number = <Order_Number> /*Put Order Number and Show Sales Order type */

And H.order_type_id = ot.transaction_type_id /*Join Order Header id and Transaction Type Id */

How To Convert Numbers Into Words in Oracle Apps 11i

Oracle Apps Has Provided an Inbuilt function AP_AMOUNT_UTILITIES_PKG which can be used to achieve the result.

SELECT (ap_amount_utilities_pkg.ap_convert_number (111234234324)) Amount_In_Word
FROM DUAL

Example:

One hundred eleven billion two hundred thirty-four million two hundred thirty-four thousand three hundred twenty-four