How To Set APPLPTMP and UTL_FILE_DIR

 

How To Set APPLPTMP and UTL_FILE_DIR 

How to set APPLPTMP and UTL_FILE_DIR for Run/Patch systems?

If using another instance on same node, how to separate APPLPTMP.

Run/Patch filesystems are switched in ADOP cutover phase.
Therefore, need to implement the conditions of the following for APPLPTMP and UTL_FILE_DIR.

• Set the path to outside of filesystem (fs1/fs2).
• Set the same APPLPTMP value for Run and Patch file systems. Set the APPLPTMP value as the first value in utl_file_dir.
• If using another instance on same node, create new directory to be able to separate for each instance.

Good example
   APPLPTMP of fs1 : /home/oracle/PROD/temp
   APPLPTMP of fs2 : /home/oracle/PROD/temp
   utl_file_dir : /home/oracle/PROD/temp, /usr/tmp

Bad example
   APPLPTMP of fs1 : /home/oracle/PROD/fs1/EBSapps/comn/temp
   APPLPTMP of fs2 : /home/oracle/PROD/fs2/EBSapps/comn/temp
   utl_file_dir : /home/oracle/PROD/fs1/EBSapps/comn/temp, /usr/tmp

NOTE:
If APPLPTMP is set to the directory in the file systems, they will be synchronized in ADOP prepare phase.
This will cause the error reported　Document:2099179.1 in Document:2099179.1 while running ADOP cutover phase.

To change the APPLPTMP:

  1. Please use the OAM context editor to edit the application context file and change APPLPTMP value.
  2. Run the autoconfig application tier.

To change the UTL_FILE_DIR:

  1. Edit the database context file using OAM (not manually - this is not supported) and save the new context file.
  2. Keeping database and listener up, shutdown the application.
  3. Remove the old init.ora.
  4. Run autoconfig database tier and let autoconfig generate a brand new init.ora.
  5. Bounce database.

Ref :- (Doc ID 2141438.1)

R12.ATG_PF.C.Delta.8 Release Update Pack

  R12.ATG_PF.C.Delta.8 Release Update Pack(Doc ID 2490921.1)

Apply the R12.ATG_PF.C.Delta.8 Release Update Pack

After you apply the latest available AD and TXK Release Update Pack (RUP) for Oracle E-Business Suite Release 12.2, you can proceed to apply the R12.ATG_PF.C.Delta.8 Release Update Pack and its associated patches.

a)Source the run edition application environment.

$ . <RUN_BASE>/EBSapps/appl/APPS<CONTEXT_NAME>.env

b)Start an online patching cycle.

$ adop phase=prepare

Apply the R12.ATG_PF.C.Delta.8 Release Update Pack (Patch 28840844) and the online help patch for R12.ATG_PF.C.Delta.8 (Patch 28840884).

UNIX

$ adop phase=apply patches=28840844 , 8840884

Complete the online patching cycle by running the following commands in the order shown.

a) Source the run edition environment file.

$ . $RUN_BASE/EBSapps/appl/APPS$CONTEXT_NAME.env

b) Run the finalize phase.

$ adop phase=finalize

c) Run the cutover phase.

$ adop phase=cutover

d) Run the cleanup phase.

$ adop phase=cleanup

Synchronize the file systems using the command shown below. This action will copy the new run edition code and configuration to the other file system, to help the next online patching cycle complete faster.

$ adop phase=fs_clone

Data Dictionary Curruption error in R12.2 - TS Mistmatch

 Data Dictionary Curruption error in R12.2 - TS Mistmatch

Wwhile running adop prepare step fails. in EBSR12.2 using adop you might observe some dictionary corruption interms of TS mismatch

Data dictionary corruption errorThe data dictionary corruption error might occur when the adop prepare step fails.

ERROR

The following error might vary for different environments.

[EVENT]     Verifying data dictionary.

[UNEXPECTED]Data dictionary corrupted:

[UNEXPECTED]Data dictionary corruption - missing parent

.1 Check for Data Dictionary Corruption

1. Run the $AD_TOP/patch/115/sql/adzddtsfix.sql script, following the usage instructions provided.
2. Run the $AD_TOP/sql/ADZDDBCC.sql script to identify whether logical data dictionary corruption is present.
   1. If no corruption is found, proceed with the upgrade.
   2. If corruption is present, follow Step 3.2 below.

3.2 Fix Data Dictionary Corruption (Conditional)

Note: Follow these steps only as a fix when logical data dictionary corruption (missing parent) is present.

1. Copy the script $AD_TOP/patch/115/sql/adzddmpfix.sql to the database tier.
   
   
2. On the database tier, connect to the database as SYSDBA.
   
   
   Note: If you are on Oracle E-Business Suite Release 12.1.3 or Release 12.2 with a multitenant architecture, to connect to a multitenant architecture database as SYSDBA, run the following commands to set the database SID field to <EBS_PDB_Name>, the pluggable database name for your Oracle E-Business Suite database:
   $ source <CDB_NAME>_<NODE_NAME>.env
$ export ORACLE_PDB_SID=<EBS_PDB_Name>;
$ sqlplus "/ as sysdba"


3. Run the $AD_TOP/patch/115/sql/adzddmpfix.sql script.
   
   
4. On the application tier, run the $AD_TOP/sql/ADZDDBCC.sql script again to identify whether data dictionary corruption is still present.
   1. If no corruption is found, proceed with the upgrade or patching cycle.
   2. If corruption is still present, proceed to Step 3.3 below.

3.3 Additional Actions for Data Dictionary Corruption (Conditional)

Note: Follow these steps only when data dictionary corruption is present after following the steps in 3.2 above.

1. On the database tier, go to the $ORACLE_HOME/rdbms/admin directory.
   
   
2. Run the utlirp.sql script , following the usage instructions provided.
   
   
3. Run the utlrp.sql script, following the usage instructions provided.
   
   
4. On the application tier, run the $AD_TOP/sql/ADZDDBCC.sql script again to identify whether data dictionary corruption is still present.
1. If no corruption is found, proceed with the upgrade or patching cycle.
2. If corruption is still present, contact Oracle Support and request a bug to be logged.

AFTER THE ISSUE IS RESOLVED RETRY - PREPARE STEP

REF - (Doc ID 1531121.1)

Adop Phase Cleanup Issue ORA-04020: deadlock detected

 Adop Cleanup Issue: "[ERROR] [CLEANUP] ORA-04020: deadlock detected "

When attempting to complete an adop cycle, the following error occurs: 

(ADOP CLEANUP HUNGS FOREVER)

Running cleanup in STANDARD mode.

  Log: @ADZDSHOWLOG.sql "2018/07/16 02:51:22"

  [ERROR] [CLEANUP 1:1 ddl_id=69120] ORA-04020: deadlock detected while trying to lock object SYS.DBMS_SYS_SQL SQL: begin sys.ad_grants.cleanup; end;

Generating log report.

  Output: /<DIR>/R122/fs_ne/EBSapps/log/adop/19/20180716_015413/cleanup/lhosterp01/adzdshowlog.out

Summary report for current adop session:

  Node lhostwserp01: Completed successfully

  - Cleanup status: Completed successfully

  Node lhostwserp02: Completed successfully

  - Cleanup status: Completed successfully

  For more details, run the command: adop -status -detail

Cause :-

This issue is related to Bug 25449103 - QRE1227.2:AD:CLEANUP REPORTED ERROR FOR SYS.AD_GRANTS.CLEANUP
 
After running adop phase=cleanup:


Running cleanup in STANDARD mode.
Log: @ADZDSHOWLOG.sql "2018/07/09 08:57:59"
[ERROR] [CLEANUP 1:1 ddl_id=54777] ORA-04020: deadlock detected while trying to lock object SYS.DBMS_SYS_SQL SQL: begin sys.ad_grants.cleanup; end;

Discussed in Bug below:
Bug 25449103: QRE1227.2:AD:CLEANUP REPORTED ERROR FOR SYS.AD_GRANTS.CLEANUP

Solution :-

NOTE: Ensure that you have taken a backup of your system before applying the recommended solution

a) Connect to SQL*PLUS as APPS

SQL>select count(1)
  from dba_tab_privs
  where table_name='DBMS_SYS_SQL'
  and privilege='EXECUTE'
  and grantee='APPS'

If retun row.

b) Run the following command: "exec sys.ad_grants.cleanup;" 

c) Run the select below and verify 0 rows are returned:

  SQL>select count(1)

  from dba_tab_privs

  where table_name='DBMS_SYS_SQL'

  and privilege='EXECUTE'

  and grantee='APPS'

verify 0 rows are returned

6. Test if the adop cleanup issue is resolved. 

Doc id: Adop Cleanup Issue: "[ERROR] [CLEANUP] ORA-04020: deadlock detected " (Doc ID 2424333.1)

Adgrants.sql errors with 'ORA-00942: table or view does not exist' for dbmshp_runs and other objects

 Adgrants.sql errors with 'ORA-00942: table or view does not exist' for dbmshp_runs and other objects

While running adgrants.sql 

ORA-00942: table or view does not exist

when trying to drop Objects :

dbmshp_runs
dbmshp_function_info
dbmshp_parent_child_info
dbmshp_runnumber

Solution -:

After trying to drop these objects, dbmshptab.sql run by adgrants.sql creates them, so the errors while dropping them can be ignored.