Wednesday 31 October 2012

Flashbacks Time Base Recovery


http://www.conacent.com

Accidentally, the user executes a wrong query, probably with & without a WHERE clause, and COMMITS the data. Finally, when he queries the table to check the record count, he notices that all or some of the rows from the table are deleted. The Administrator uses the Flashback Query feature to restore the data to prior point of time, the data as existed some time minutes back.


1.  Suppose we have created one table named test
    SQL> create table test (name varchar2(10));
 



2. Insert few records into the table
    SQL> insert into test (name) values ('aa');



3. Check the date and time of the system
    SQL> select to_char(sysdate,'dd-mm-yyyy hh24:mi:ss') from dual;

4. Delete all records or some of it.
    SQL> delete from test;
or
    SQL> delete from test where name='cc';






5. Commit after you have deleted the record
    SQL> commit;

6. Check the date and time of the system
    SQL> select to_char(sysdate,'dd-mm-yyyy hh24:mi:ss') from dual;

7. Place the value  of <Date & Time> taken from step 3.
    If all the records are deleted then use the below query
    SQL> insert into test select * from test as of timestamp to_timestamp('<Date & Time>','dd-mm-yyyy hh24:mi:ss');

   If specific record is deleted then use the below query
   SQL> insert into test select * from test as of timestamp to_timestamp('<Date & Time>','dd-mm-yyyy hh24:mi:ss') where name='cc' ;

Monday 29 October 2012

Direct Delivery Receipts (Lot enabled items) using Interface tables

Steps to upload Direct Delivery Receipts (Lot enabled items) using Interface tables:

1. Set the Profile option "RCV: Processing Mode" to "Batch" using System Administrator responsibility:
    System Administrator -> Profile ->RCV: Processing Mode.

2. Populate the given fields of the two interface tables i.e. RCV_HEADERS_INTERFACE and   RCV_TRANSACTIONS_INTERFACE .

3. In order to insert Lot Numbers the MTL_TRANSACTION_LOTS_INTERFACE table needs to be populated.
 For multiple lot insertion the above table needs to be populated with different lot numbers.

4. Run the Receiving Transaction Processor request from the front end Apps Screen.

/************************* Interface Tables Population ******************************/

/********** Population of RCV_HEADERS_INTERFACE table**********/
INSERT INTO RCV_HEADERS_INTERFACE
(HEADER_INTERFACE_ID,
GROUP_ID,
PROCESSING_STATUS_CODE,
RECEIPT_SOURCE_CODE,
TRANSACTION_TYPE,
AUTO_TRANSACT_CODE,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
LAST_UPDATE_LOGIN,
CREATION_DATE,
CREATED_BY,
VENDOR_ID,
SHIP_TO_ORGANIZATION_ID,
EXPECTED_RECEIPT_DATE,
VALIDATION_FLAG
)
VALUES
(rcv_headers_interface_s.nextval , --Header_Interface_Id
rcv_interface_groups_s.nextval, --Group_Id
'PENDING', --Processing_Status_Code
'VENDOR', --Receipt_Source_Code
'NEW', --Transaction_Type
'DELIVER', --Auto_Transact_Code
SYSDATE, --Last_Update_Date
0, --Last_Updated_By
0, --Last_Update_Login
SYSDATE, --Creation_Date
0, --Created_By
341, --Vendor_Id
87, --Ship_To_Organization_Id,
SYSDATE, --Expected_Receipt_Date
'Y' --Validation_Flag
);

/*********Population of RCV_TRANSACTIONS_INTERFACE table**********/
 INSERT INTO RCV_TRANSACTIONS_INTERFACE
(INTERFACE_TRANSACTION_ID,
GROUP_ID,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
CREATION_DATE,
CREATED_BY,
LAST_UPDATE_LOGIN,
TRANSACTION_TYPE,
TRANSACTION_DATE,
PROCESSING_STATUS_CODE,
PROCESSING_MODE_CODE,
TRANSACTION_STATUS_CODE,
QUANTITY,
UNIT_OF_MEASURE,
ITEM_ID,
EMPLOYEE_ID,
AUTO_TRANSACT_CODE,
SHIP_TO_LOCATION_ID,
RECEIPT_SOURCE_CODE,
VENDOR_ID,
SOURCE_DOCUMENT_CODE,
PO_HEADER_ID,
PO_LINE_ID,
PO_LINE_LOCATION_ID,
DESTINATION_TYPE_CODE,
-- DELIVER_TO_PERSON_ID,
LOCATION_ID,
DELIVER_TO_LOCATION_ID,
SUBINVENTORY,
HEADER_INTERFACE_ID,
DOCUMENT_NUM,
TO_ORGANIZATION_ID,
VALIDATION_FLAG
)
SELECT
rcv_transactions_interface_s.nextval, --Interface_Transaction_id
rcv_interface_groups_s.currval, --Group_id
SYSDATE, --Last_update_date
0, --Last_updated_by
SYSDATE, --Creation_date
0, --Created_by
0, --Last_update_login
'RECEIVE', --TRANSACTION_TYPE
SYSDATE, --TRANSACTION_DATE
'PENDING', --PROCESSING_STATUS_CODE
'BATCH', --PROCESSING_MODE_CODE
'PENDING', --TRANSACTION_STATUS_CODE
2, --QUANTITY
'Kilogram', --UNIT_OF_MEASURE
9142 , --ITEM_ID
61, --EMPLOYEE_ID
'DELIVER', --AUTO_TRANSACT_CODE
147, --SHIP_TO_LOCATION_ID
'VENDOR', --RECEIPT_SOURCE_CODE
341, --VENDOR_ID
'PO', --SOURCE_DOCUMENT_CODE
7439, --PO_HEADER_ID
8054, --PO_LINE_ID
9200, --PO_LINE_LOCATION_ID
'INVENTORY', --DESTINATION_TYPE_CODE
-- 13706, --DELIVER_TO_PERSON_ID
147, --LOCATION_ID
147, --DELIVER_TO_LOCATION_ID
'EEBFGWIP', --SUBINVENTORY
rcv_headers_interface_s.currval, --Header_interface_id
'40031000160', --PO NUMBER
87, --TO_ORGANIZATION_ID
'Y' --VALIDATION_FLAG
FROM DUAL;
 
/*********** For Lot Insertion *************/
INSERT INTO mtl_transaction_lots_interface
(
TRANSACTION_interface_ID      
,LAST_UPDATE_DATE          
,LAST_UPDATED_BY          
,CREATION_DATE            
,CREATED_BY                
,LAST_UPDATE_LOGIN        
,TRANSACTION_QUANTITY      
,PRIMARY_QUANTITY          
,LOT_NUMBER                
,LOT_EXPIRATION_DATE      
,SERIAL_TRANSACTION_TEMP_ID
,PRODUCT_CODE              
,PRODUCT_TRANSACTION_ID    
)
values
(
MTL_MATERIAL_TRANSACTIONS_S.NEXTVAL --TRANSACTION_interface_ID      
,SYSDATE --LAST_UPDATE_DATE          
,1 --LAST_UPDATED_BY          
,SYSDATE --CREATION_DATE            
,1 --CREATED_BY                
,1 --LAST_UPDATE_LOGIN        
,60 --TRANSACTION_QUANTITY      
,60 --PRIMARY_QUANTITY          
,'B/L100' --LOT_NUMBER                
,NULL --LOT_EXPIRATION_DATE      
,NULL --SERIAL_TRANSACTION_TEMP_ID
,'RCV' --PRODUCT_CODE              
,RCV_TRANSACTIONS_INTERFACE_S.CURRVAL --PRODUCT_TRANSACTION_ID    
);

Thursday 4 October 2012

RMAN Restoration on different Server as well as same Mount Point for Oracle APPS

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

RMAN Restoration on different Server as well as same Mount Point for Oracle APPS

1. if mount point is not same in target server change the /etc/fstab and restart the server.

2. Copy db backup file system from source server to target server as well as rman backup files.

2. Clone the database technology Stack.
    $su - oracln
    $cd $ORACLE_HOME/appsutil/clone/bin
    $perl adcfgclone.pl dbTechStack

3. Connect to RMAN utility.
    $RMAN target /

4. Startup Database to nomount state.
    RMAN>startup nomount;

5. Restore controlfile from the rman backup file system
    RMAN> restore controlfile from '/data2/rman_archive/autobackup_control_files';  [Latest control file]

6. Database need to be startup to mount state.
    RMAN> alter database mount;

7. Restore database from rman backup file.
    RMAN> restore database;  /  restore database until time "to_date('01-OCT-2012 23:45:00','dd-mon-rrrr hh24:mi:ss')";
    RMAN> recover database;  /  recover database until time "to_date('01-OCT-2012 23:45:00','dd-mon-rrrr hh24:mi:ss')";
    RMAN> alter database open resetlogs;



Please give your feedback if any thing is not correct. Your feedback is valuable.

RMAN Restore on another machine with different file system - same database name In Oracle Apps



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

RMAN Restoration on different Server as well as different Mount Point for Oracle APPS

1. Copy db backup file system from source server to target server as well as rman backup files.

2. Clone the database technology Stack.
    $su - oracln
    $cd $ORACLE_HOME/appsutil/clone/bin
    $perl adcfgclone.pl dbTechStack

3. Connect to RMAN utility.
    $RMAN target /


     Recovery Manager: Release 11.1.0.7.0 - Production on Fri Oct 5 14:03:49 2012
     Copyright (c) 1982, 2007, Oracle.  All rights reserved.
     connected to target database (not started)


4. Startup Database to nomount state.
    RMAN>startup nomount;


    Oracle instance started
    Total System Global Area    1068937216 bytes
    Fixed Size                                 2166536 bytes
    Variable Size                         427819256 bytes
    Database Buffers                   624951296 bytes
    Redo Buffers                           14000128 bytes


5. Restore controlfile from the rman backup file system. Before changing the path of the RMAN backup change the owner of the directory.

    RMAN> restore controlfile from '/data2/rman_archive/autobackup_control_filec-147839628-20120930-00';  [Latest control file]


                  Starting restore at 05-OCT-12
                  using channel ORA_DISK_1
                  channel ORA_DISK_1: restoring control file
                  channel ORA_DISK_1: restore complete, elapsed time: 00:00:03
                  output file name=/d01/CLONE/db/apps_st/data/cntrl01.dbf
                  output file name=/d01/CLONE/db/apps_st/data/cntrl02.dbf
                  output file name=/d01/CLONE/db/apps_st/data/cntrl03.dbf
                  Finished restore at 05-OCT-12


6. Database need to be startup to mount state.
    RMAN> alter database mount;

                using target database control file instead of recovery catalog
               database mounted

7. if mount point is different then start with catalog command
    RMAN> catalog start with '/d01/CLONE/db';

                   File Name: /d01/CLONE/db/tech_st/11.1.0/opmn/conf/ons.config
                   File Name: /d01/CLONE/db/tech_st/11.1.0/opmn/conf/ons.config.ouibak.2
                   File Name: /d01/CLONE/db/tech_st/11.1.0/opmn/conf/ons.config.ouibak.3
                   File Name: /d01/CLONE/db/tech_st/11.1.0/opmn/conf/ons.config.ouibak.5
                   File Name: /d01/CLONE/db/tech_st/11.1.0/opmn/lib/ons.jar
                   File Name: /d01/CLONE/db/tech_st/11.1.0/opmn/lib/onc.jar
                   File Name: /d01/CLONE/db/tech_st/11.1.0/root.sh.old.1
                   File Name: /d01/CLONE/db/tech_st/11.1.0/root.sh.old

                   Do you really want to catalog the above files (enter YES or NO)? YES
                   cataloging files...


8. if redo logfile is in different mount point
    $ sqlplus '/as sysdba'
    SQL> alter database rename file '/oracle/OBA/obaora/db/apps_st/data/log03a.dbf' to '/d01/CLONE/db/apps_st/data/log03a.dbf';
    SQL> alter database rename file '/oracle/OBA/obaora/db/apps_st/data/log03b.dbf' to '/d01/CLONE/db/apps_st/data/log03b.dbf';
    SQL> alter database rename file '/oracle/OBA/obaora/db/apps_st/data/log04a.dbf' to '/d01/CLONE/db/apps_st/data/log04a.dbf';
    SQL> alter database rename file '/oracle/OBA/obaora/db/apps_st/data/log04b.dbf' to '/d01/CLONE/db/apps_st/data/log04b.dbf';
    SQL> alter database rename file '/oracle/OBA/obaora/db/apps_st/data/log05a.dbf' to '/d01/CLONE/db/apps_st/data/log05a.dbf';
    SQL> alter database rename file '/oracle/OBA/obaora/db/apps_st/data/log05b.dbf' to '/d01/CLONE/db/apps_st/data/log05b.dbf';
    SQL> alter database rename file '/oracle/OBA/obaora/db/apps_st/data/log06a.dbf' to '/d01/CLONE/db/apps_st/data/log06a.dbf';
    SQL> alter database rename file '/oracle/OBA/obaora/db/apps_st/data/log06b.dbf' to '/d01/CLONE/db/apps_st/data/log06b.dbf';


9. if tempfile is in different mount point
    RMAN> run {
    2> set newname for tempfile '/oracle/OBA/obaora/db/apps_st/data/temp01.dbf' to '/d01/CLONE /db/apps_st/data/temp01.dbf';
     3> set newname for tempfile '/oracle/OBA/obaora/db/apps_st/data/temp02.dbf' to '/d01/CLONE/db/apps_st/data/temp02.dbf';
     4> switch tempfile all;
     5> }

      executing command: SET NEWNAME
      using target database control file instead of recovery catalog

       executing command: SET NEWNAME

        renamed tempfile 1 to /d01/CLONE/db/apps_st/data/temp01.dbf in control file
        renamed tempfile 2 to /d01/CLONE/db/apps_st/data/temp02.dbf in control file

  

10. if datafile is in different mount point

    RMAN> run {
          set newname for datafile 1 to '/d01/CLONE/db/apps_st/data/system01.dbf';
          set newname for datafile 2 to '/d01/CLONE/db/apps_st/data/system02.dbf';
          set newname for datafile 3 to '/d01/CLONE/db/apps_st/data/system03.dbf';
          set newname for datafile 4 to '/d01/CLONE/db/apps_st/data/system04.dbf';
          set newname for datafile 5 to '/d01/CLONE/db/apps_st/data/system05.dbf';
          set newname for datafile 6 to '/d01/CLONE/db/apps_st/data/ctxd01.dbf';
          set newname for datafile 7 to '/d01/CLONE/db/apps_st/data/owad01.dbf';
          set newname for datafile 8 to '/d01/CLONE/db/apps_st/data/a_queue02.dbf';
          set newname for datafile 9 to '/d01/CLONE/db/apps_st/data/odm.dbf';
          set newname for datafile 10 to '/d01/CLONE/db/apps_st/data/olap.dbf';
          set newname for datafile 11 to '/d01/CLONE/db/apps_st/data/sysaux01.dbf';
          set newname for datafile 12 to '/d01/CLONE/db/apps_st/data/apps_ts_tools01.dbf';
          set newname for datafile 13 to '/d01/CLONE/db/apps_st/data/system12.dbf';
          set newname for datafile 14 to '/d01/CLONE/db/apps_st/data/a_txn_data04.dbf';
          set newname for datafile 15 to '/d01/CLONE/db/apps_st/data/a_txn_ind06.dbf';
          set newname for datafile 16 to '/d01/CLONE/db/apps_st/data/a_ref03.dbf';
          set newname for datafile 17 to '/d01/CLONE/db/apps_st/data/a_int02.dbf';
          set newname for datafile 18 to '/d01/CLONE/db/apps_st/data/sysaux02.dbf';
          set newname for datafile 19 to '/d01/CLONE/db/apps_st/data/sysaux03.dbf';
          set newname for datafile 20 to '/d01/CLONE/db/apps_st/data/a_txn_data05.dbf';
          set newname for datafile 21 to '/d01/CLONE/db/apps_st/data/a_txn_data06.dbf';
          set newname for datafile 22 to '/d01/CLONE/db/apps_st/data/a_txn_ind07.dbf';
          set newname for datafile 23 to '/d01/CLONE/db/apps_st/data/XXILL01.dbf';
          set newname for datafile 288 to '/d01/CLONE/db/apps_st/data/system10.dbf';
          set newname for datafile 295 to '/d01/CLONE/db/apps_st/data/system06.dbf';
          set newname for datafile 314 to '/d01/CLONE/db/apps_st/data/portal01.dbf';
          set newname for datafile 351 to '/d01/CLONE/db/apps_st/data/system07.dbf';
          set newname for datafile 352 to '/d01/CLONE/db/apps_st/data/system09.dbf';
          set newname for datafile 353 to '/d01/CLONE/db/apps_st/data/system08.dbf';
          set newname for datafile 354 to '/d01/CLONE/db/apps_st/data/system11.dbf';
          set newname for datafile 379 to '/d01/CLONE/db/apps_st/data/undo01.dbf';
          set newname for datafile 392 to '/d01/CLONE/db/apps_st/data/a_txn_data01.dbf';
          set newname for datafile 393 to '/d01/CLONE/db/apps_st/data/a_txn_ind01.dbf';
          set newname for datafile 394 to '/d01/CLONE/db/apps_st/data/a_ref01.dbf';
          set newname for datafile 395 to '/d01/CLONE/db/apps_st/data/a_int01.dbf';
          set newname for datafile 396 to '/d01/CLONE/db/apps_st/data/a_summ01.dbf';
          set newname for datafile 397 to '/d01/CLONE/db/apps_st/data/a_nolog01.dbf';
          set newname for datafile 398 to '/d01/CLONE/db/apps_st/data/a_archive01.dbf';
          set newname for datafile 399 to '/d01/CLONE/db/apps_st/data/a_queue01.dbf';
          set newname for datafile 400 to '/d01/CLONE/db/apps_st/data/a_media01.dbf';
          set newname for datafile 401 to '/d01/CLONE/db/apps_st/data/a_txn_data02.dbf';
          set newname for datafile 402 to '/d01/CLONE/db/apps_st/data/a_txn_data03.dbf';
          set newname for datafile 403 to '/d01/CLONE/db/apps_st/data/a_txn_ind02.dbf';
          set newname for datafile 404 to '/d01/CLONE/db/apps_st/data/a_txn_ind03.dbf';
          set newname for datafile 405 to '/d01/CLONE/db/apps_st/data/a_txn_ind04.dbf';
          set newname for datafile 406 to '/d01/CLONE/db/apps_st/data/a_txn_ind05.dbf';
          set newname for datafile 407 to '/d01/CLONE/db/apps_st/data/a_ref02.dbf';
          switch datafile all;
}

RMAN> catalog start with '/s01/rman_archive';


RMAN>run {
          restore database  /  restore database until time "to_date('01-OCT-2012 23:45:00','dd-mon-rrrr hh24:mi:ss')";
           recover database  /  recover database until time "to_date('01-OCT-2012 23:45:00','dd-mon-rrrr hh24:mi:ss')";
          }

RMAN> alter database open resetlogs;



Please give your feedback if any thing is not correct. Your feedback is valuable.

Monday 1 October 2012

"RVTTH-192: Subroutine ORA-20001: APP—20110:Encountered the error in trigger JAI_RCVRT_BRIUD_TI ORA-06508: PL/SQL: could not find program unit being called"

While Performing Receipt/Delviery Error:-
 "RVTTH-192: Subroutine ORA-20001: APP—20110:Encountered the error in trigger JAI_RCVRT_BRIUD_TI ORA-06508: PL/SQL: could not find program unit being called"


Solution: If India Localization is used then it is mandatory set the profile option RCV:Processing Mode to Immediate at site level.

Query For Account Hiererchy:

Step:1

create table XX_test_acc1
as select xx.parent_flex_value,xx.flex_value,xx.DESCRIPTION
from
(select DISTINCT ffvc.parent_flex_value,ffvc.flex_value,FFVC.DESCRIPTION
from
fnd_flex_value_children_v ffvc
where ffvc.summary_flag='N'
AND ffvc.flex_value_set_id=1014870
START WITH ffvc.parent_flex_value IS NOT NULL
CONNECT BY PRIOR ffvc.flex_value=ffvc.parent_flex_value
ORDER BY FFVC.PARENT_FLEX_VALUE) xx;

Step:2

select a.PARENT_FLEX_VALUE,a.DESCRIPTION,xx.parent_flex_value_low,xx.flex_value,xx.flex_value_meaning,xx.description
from
(SELECT   parent_flex_value_low,flex_value, flex_value_meaning, description
    FROM fnd_flex_values_vl
   WHERE (   ('' IS NULL)
          OR (structured_hierarchy_level IN (
                 SELECT hierarchy_id
                   FROM fnd_flex_hierarchies_vl h
                  WHERE h.flex_value_set_id = 1014871
                    AND h.hierarchy_name LIKE '')
             )
         )
     AND (flex_value_set_id = 1014871)
     AND enabled_flag='Y'
          AND (parent_flex_value_low in(select a.flex_value from cil_test_acc a))
ORDER BY parent_flex_value_low) xx ,XX_test_acc1 a
where xx.parent_flex_value_low=to_number(a.FLEX_VALUE);