Link

Pre-Setup for OAEA Datasource

Create new script from Original txk script and change as per below differene
##############################################
[applmgr@sjebsappuatg52 bin]$ diff $FND_TOP/patch/115/bin/txkManageDBConnectionPool.pl /dba/scripts/OAEAJDBCDS_Update.pl
 439c439
 trDirPathToBase($gCtxVars{$s_fndtop} . "/patch/115/bin/txkManageDBConnectionPool.py");
   my $py_script     = "OAEAJDBCDS_Update.py";

##############################################
   [applmgr@sjebsappuatg52 bin]$ diff $FND_TOP/patch/115/bin/txkManageDBConnectionPool.py /dba/scripts/OAEAJDBCDS_Update.py
   69a70,72
           print '---------------------------------------------------------------------'
           print 'Calling OAEA Script(Modifified) !!!'
           print '---------------------------------------------------------------------'
   204c207
   < DATASOURCENAME            = 'EBSDataSource'
   DATASOURCENAME            = 'OAEADatasource'
   [applmgr@sjebsappuatg52 bin]$

Original Script

#*********************************************************************

mkdir -p $HOME/log
 LOGFILE=/dba/scripts/logs/${TWO_TASK}<em>update_apps_weblogic</em>$(date +"%d_%b_%Y"-%H_%M).log;touch $LOGFILE
 echo "……………………..The Log file for Session is Locate at $LOGFILE"
 ##### Script STARTS Here
 read -s -p "Enter Current APPS Password: " APPS_PWD
 echo $'\n'
 read -s -p "Enter Current Weblogic Password: " WLS_PWD
 echo $'\n'
 read -s -p "Enter New Weblogic Password: " WLS_NEW_PWD
 echo $'\n'
 #
 (echo "Current APPS Password     is :  $APPS_PWD";
 echo "Current Weblogic Password is :  $WLS_PWD"
 echo "New Weblogic Password     is :  $WLS_NEW_PWD"
 echo $'\n'
 echo " ------------------------------------------------------------------"
 echo " Starting Admin Server …….."
 echo " ------------------------------------------------------------------"
 cd $ADMIN_SCRIPTS_HOME
 sh adadminsrvctl.sh start <<EOF
 $WLS_PWD
 $APPS_PWD
 EOF
 echo " Starting Admin Server …….. Completed"
 echo $'\n'
 echo " ------------------------------------------------------------------"
 echo " Updating WLS Data Source"
 echo " ------------------------------------------------------------------"
 Updating WLS Data Source
 cd $FND_TOP/patch/115/bin
 perl txkManageDBConnectionPool.pl -options=updateDSPassword -contextfile=$CONTEXT_FILE <<EOF
 $WLS_PWD
 $APPS_PWD
 EOF
 Updating WLS OAEA Data Source --Custom
 cd /dba/scripts/
 perl OAEAJDBCDS_Update.pl -options=updateDSPassword -contextfile=$CONTEXT_FILE <<EOF
 $WLS_PWD
 $APPS_PWD
 EOF
 echo " ------------------------------------------------------------------"
 echo " Changing the Weblogic Password"
 echo " ------------------------------------------------------------------"
 Change the Weblogic Password
 cd $FND_TOP/patch/115/bin
 perl txkUpdateEBSDomain.pl -action=updateAdminPassword -contextfile=$CONTEXT_FILE <<EOF
 Yes
 $WLS_PWD
 $WLS_NEW_PWD
 $APPS_PWD
 EOF
 echo "---------------------------------------------"
 echo "Apps & Weblogic Password updated Successfully"
 echo "Login into Admin Server and Verify the new weblogic passowrd"
 echo "Start anyone of Managed server (oacore) ,Test DataSource."
 echo "---------------------------------------------" )|tee -a $LOGFILE
 echo "……………………..The Log file for Session is Locate at $LOGFILE"
 ##### Script ENDS Here

Oracle Apps 12.2 CPU Patching

 
 Oracle E-Business Suite Release 12 Critical Patch Update Knowledge Document (July 2020) (Doc ID 2679563.1)         
  
 Please initiate FS_CLONE before starting the ADOP patching cycle 
  
 FS_CLONE PHASE
adop phase=fs_clone
  
 Take a backup of invalid objects before starting the below steps
 create table INVALIDS_B4_JULY_PSU as select * from dba_objects where status=' INVALID';
  
 Pre-requisite before applying the CPU Patch
 a. Create $ORACLE_HOME/appsutil/admin on the database server.
 b. cp /patches/PSU_patch_072020/EBS_Patches/31198342/admin/adgrants.sql  $ORACLE_HOME/appsutil/admin
 d. Set the environment to point to ORACLE_HOME on the database server
 e. Use SQL*Plus to run the script:
      UNIX:
      $ sqlplus /as sysdba
      SQL> @?/appsutil/admin/adgrants.sql APPS
  
 PREPARE PHASE
adop phase=prepare
  
 Patch Staging Directory :
 /patches/PSU_patch_072020/EBS_Patches/
  
 Checking the status of the patches
 select * from ad_bugs where bug_number in (31198342,31444270,31206584,30948437,30980446,25229413,26282050,29662975,29631318);
  
  
 APPLY PHASE
  
 1)Main patch 31198342
  
 time adop phase=apply patches=31198342 patchtop=/patches/PSU_patch_072020/EBS_Patches
  
 2)Patches to be Applied After the CPU Patch
  
 select * from ad_bugs where bug_number in (31444270,31206584);
  
  
     2.1) Patch 31444270:R12.FWK.C
  
 time adop phase=apply patches=31444270 patchtop=/patches/PSU_patch_072020/EBS_Patches
  
     2.2) Patch 31206584:R12.FND.C
  
 time adop phase=apply patches=31206584 patchtop/patches/PSU_patch_072020/EBS_Patches
  
 3)Patches listed in the following table were required to be applied as part of earlier CPUs. Apply these patches after the CPU if they have not yet been applied.
     
     3.1) Patch 30980446:R12.JTT.C
     
 time adop phase=apply patches=30980446 patchtop=/patches/PSU_patch_072020/EBS_Patches
     
 If you are using Oracle Workflow and have one or more Workflow one-off patches or the Workflow recommended patch collection in your instance, apply the below patch
  
 4)Patch 29631318:R12.OWF.C
     
 time adop phase=apply patches=29631318 patchtop=/patches/PSU_patch_072020/EBS_Patches
  
 Oracle WebLogic Server 10.3.6.0 (PSU 10.3.6.0.200714 (Patch 31178492))
  
 Source the patch FS
 . ./EBSapps.env patch
 echo $FILE_EDITION
 1)     Preparing to Install WLS Patch Set Update 10.3.6.0.200714
 cd $FMW_HOME/utils/bsu
        Check the status for the applied patches
        bsu.sh -prod_dir=$FMW_HOME/wlserver_10.3/ -status=applied -verbose -view
        Edit the bsu.sh under $FMW_HOME/utils/bsu script and change memory options as follows:
        MEM_ARGS="-Xms1024m -Xmx4096m"
  
 2) Remove the conflict Patches
 time bsu.sh -remove -patchlist=SUPD -prod_dir=$FMW_HOME/wlserver_10.3/
 time bsu.sh -remove -patchlist=BWKV -prod_dir=$FMW_HOME/wlserver_10.3/
 time bsu.sh -remove -patchlist=4R4W -prod_dir=$FMW_HOME/wlserver_10.3/
 time bsu.sh -remove -patchlist=3L3H -prod_dir=$FMW_HOME/wlserver_10.3/
  
 3) Installing WLS Patch Set Update 10.3.6.0.200714
  
 mv $FMW_HOME/utils/bsu/cache_dir $FMW_HOME/utils/bsu/cache_dir_bkp
 mkdir -p $FMW_HOME/utils/bsu/cache_dir
 cd $FMW_HOME/utils/bsu/cache_dir 
 unzip /patches/PSU_patch_072020/WLS_Patches/p31178492_1036_Generic.zip
  
 cd $FMW_HOME/utils/bsu
 time bsu.sh -install -patch_download_dir=$FMW_HOME/utils/bsu/cache_dir -patchlist=I37G -prod_dir=$FMW_HOME/wlserver_10.3/
  
 4) Install overlay patch
  
 cp patches/PSU_patch_072020/WLS_Patches/overlay_patch/DTN2.jar $FMW_HOME/utils/bsu/cache_dir/
 cp /patches/PSU_patch_072020/WLS_Patches/overlay_patch/patch-catalog_27158.xml $FMW_HOME/utils/bsu/cache_dir/
 cd $FMW_HOME/utils/bsu/
 time bsu.sh -install -patch_download_dir=$FMW_HOME/utils/bsu/cache_dir -patchlist=DTN2 -prod_dir=$FMW_HOME/wlserver_10.3/
  
 5) Validation 
 The following command is a simple way to verify the successful installation of WebLogic Server PSU patch 10.3.6.0.200714.
             $ . $FMW_HOME/wlserver_10.3/server/bin/setWLSEnv.sh
             $ java weblogic.version
  
 Oracle Fusion Middleware 11.1.1.9 OSS - Web Tier Home (OSS Security Patch Update CPUJul2020 Patch 31304503)
  
 1)Prerequisite Steps to Configure Oracle Fusion Middleware 11.1.1.9 Components for Oracle E-Business Suite Release 12.2 Before Applying the July 2019 and Later FMW OSS Security Patch (Doc ID 2555355.1)
  
 Applying the OSS and OPMN Patches
  
  
 2)Patch Staging Location:
  
 OSS Patch
 cd /patches/PSU_patch_072020/FMW_Patches/31304503
  
 OPMN Patch 
 cd /patches/PSU_patch_072020/OPMN_Patch/23716938
  
 3)Conflict Check:
 cd /patches/PSU_patch_072020/FMW_Patches/31304503
 . ./$FMW_HOME/SetWebtier.env
 time $ORACLE_HOME/OPatch/opatch prereq CheckConflictAgainstOHWithDetail -ph ./
  
 cd /patches/PSU_patch_072020/OPMN_Patch/23716938
 time $ORACLE_HOME/OPatch/opatch prereq CheckConflictAgainstOHWithDetail -ph ./
  
 4)Apply the July2020 FMW (OSS) Patches
  
 . $FMW_HOME/SetWebtier.env
 cd /patches/PSU_patch_072020/FMW_Patches/31304503
 time $ORACLE_HOME/OPatch/opatch apply
  
 5)Apply the OPMN Patch
  
 . $FMW_HOME/SetWebtier.env
 cd /patches/PSU_patch_072020/OPMN_Patch/23716938
 time $ORACLE_HOME/OPatch/opatch apply
  
 Open a New session in App Node 1 and source the Patch FS 
 . ./EBSapps.env patch
 Start the weblogic admin server in patch FS and do the below setup for all the Managed servers
 cd $ADMIN_SCRIPTS_HOME/
 adadminsrvctl.sh start
  
 Add Parameter to Managed Servers
 ·        Log in to the Oracle Fusion Middleware Administration Console at http://<hostname>.<domain>:<AdminServerPort>/console.
 ·        Click Lock & Edit.
 ·        Under Domain Structure > <your EBS domain> > Environment > Servers, select one of the managed servers. Then under the Server Start tab in the Arguments section, append the following (there must be a space at the start before -D)
 -DUseSunHttpHandler=true
 Click Save.
 ·        Repeat the above step for all remaining managed servers.
 ·        Click Activate Changes.
 ·        Stop AdminServer on Patch FS
 ·        Stop Nodemanager on Patch FS
  
  
 FINALIZE PHASE
 adop phase=finalize
  
 Shutdown the Application and Database services before taking the cold backup
 adstpall.sh -mode=allnodes
 Login to DB node and shutdown the DB and listener
 srvctl stop database -d EBSDBA
 lsnrctl stop LISTENER_NAME
  
  
  
 Taking Cold backup

  
 Login to DB node and start the DB and listener
 
  
 CUTOVER PHASE
 adop phase=cutover mtrestart=no
  
 Bring Down DB and Listener after Cutover completes
 srvctl stop database -d EBSDBA
 lsnrctl stop LISTENER_NAME
  
 DB Critical Patch Update (CPU) Program July 2020 Patch Availability Document (PAD) (Doc ID 2664876.1)
  
 2)Patch Tree :
  
 31326402 - Combo Patch for OJVM PSU 12.1.0.2.200714 and Database Proactive BP 12.1.0.2.200714
 |
 |-31219939 - Oracle JavaVM Component 12.1.0.2.200714 -->
 |-31307682 - Database Proactive Bundle Patch (DB BP)
  |
  |-31136382 - GI/RDBMS (OCW Patch Set Update 12.1.0.2.200714)
  |-31001106 - RDBMS (Database Bundle Patch 12.1.0.2.200714)
  
 3)Patch Staging Location:
 cd /patches/PSU_patch_072020
  
 4) Copy the latest OPatch directory to ORACLE_HOME
 cd $ORACLE_HOME
 mv OPatch OPatch_Oct2019
 cp -rp /patches/PSU_patch_072020/OPatch .
  
 5)Conflict Check:
 ------------------------------------------------
 31001106 - Database Bundle Patch 12.1.0.2.200714
 ------------------------------------------------
 cd /patches/PSU_patch_072020/31326402/31307682/31001106
 $ORACLE_HOME/OPatch/opatch prereq CheckConflictAgainstOHWithDetail -ph ./
  
 Composite Patch : 31001106
  
         Conflict with 23141592
  
         Conflict with 30808393
  
         Bug Superset of 28507324
  
         Bug Superset of 27929509
  
         Bug Superset of 25099339
  
         Conflict with 29943372
  
         Conflict with 19239846
  
         Bug Superset of 19068380
  
         Bug Superset of 8975044
  
         Conflict with 19472320
  
 ---------------------------------------------------------------
 31136382 - No Conflicts (OCW Patch Set Update 12.1.0.2.200714)
 ---------------------------------------------------------------
 cd /patches/PSU_patch_072020/31326402/31307682/31136382
 $ORACLE_HOME/OPatch/opatch prereq CheckConflictAgainstOHWithDetail -ph ./
  
 No Conflicts
  
 -------------------------------------
 31219939 - No Conflicts - OJVM Patch
 -------------------------------------
  
 cd /patches/PSU_patch_072020/31326402/31219939
 $ORACLE_HOME/OPatch/opatch prereq CheckConflictAgainstOHWithDetail -ph ./
  
 No Conflicts
  
 5)Rollback conflict patches:
  
 time $ORACLE_HOME/OPatch/opatch nrollback -id 23141592,30808393,29943372,19239846
 time $ORACLE_HOME/OPatch/opatch nrollback -id 17305006,30242335
  
  
 Roll back for patch 19472320 will fail with directory not available, hence we need to apply the patch first and then rollback as per the steps provided below.
  
 mkdir -p $ORACLE_HOME/.patch_storage/19472320_Oct_20_2014_03_14_44
 cd /patches/PSU_patch_072020/19472320/
 time $ORACLE_HOME/OPatch/opatch apply
 time $ORACLE_HOME/OPatch/opatch rollback -id 19472320
  
 6)Apply the July2020 DB bundle patches:
 ---------
 31001106:
 ---------
 cd /patches/PSU_patch_072020/31326402/31307682/31001106
  
 time $ORACLE_HOME/OPatch/opatch apply
  
 ---------
 31136382:
 ---------
  
 cd /patches/PSU_patch_072020/31326402/31307682/31136382
  
 time $ORACLE_HOME/OPatch/opatch apply
  
 7)Apply the Overlay patches:
  
 cd /patches/PSU_patch_072020/overlay_patches/
 time $ORACLE_HOME/OPatch/opatch napply -id 19239846,23604553,19472320,25139545,31239450,31548611,23141592
  
 8)Apply the July2020 OJVM patch:
 cd /patches/PSU_patch_072020/31326402/31219939 
  
 time $ORACLE_HOME/OPatch/opatch apply
  
 (III) Postinstallation
 ----------------------
 1.    Startup Database in Upgrade mode
                sqlplus / as sysdba
 startup mount;
 select name,open_mode from v$database;
 alter system set cluster_database=FALSE scope=spfile;
 shutdown immediate;
 startup upgrade
 2.    Check the number of invalid objects count before runing the datapatch
         Select count(*) from dba_objects where status='INVALID';
  
 3.  Install the SQL portion of the patch by running the following command:
       cd $ORACLE_HOME/OPatch
       time datapatch -verbose
  
 4.  Start sqlplus and run the utlrp.sql to clear the invalids only if dpload.sql is a part of the patch
       SQL> @?/rdbms/admin/utlrp.sql
  
 5.   Shut down the database
                shut immediate;
  
 6.  Startup database using srvctl in all nodes
 sqlplus / as sysdba
 startup mount;
 alter system set cluster_database=TRUE scope=spfile;
 shut immediate;
 srvctl start database -d EBSDBA
relocate services as per definition  
 7.   Check the number of invalid objects count after runing the datapatch
       Select count(*) from dba_objects where status='INVALID';
  
 8. Package body AD_ZD_ADOP and FND_DIAGNOSTICS will become INVALID after datapatch. Do the below steps to make it VALID.
                Connect as SYS User
 alter package DBMS_METADATA_UTIL compile body;
 grant execute on DBMS_METADATA_UTIL to apps;
 grant select on dba_datapump_jobs to apps;
 Connec as APPS User
 alter package AD_ZD_ADOP compile body;
 alter package apps.FND_DIAGNOSTICS compile body;      
  
 9.    Startup TNS listener in all the DB nodes
  
 8)Check the inventory for applied patches:
 $ORACLE_HOME/OPatch/opatch lsinventory
 $ORACLE_HOME/OPatch/opatch lspatches
  
 Adding the JVM Parameter for TLS-Enabled Release 12.2 Environments
  
 Add Parameter to WLS Admin Server
 ·        Take a backup of existing context file in each app and dmz nodes

  
 ·        Source the patch FS and do the above steps once again to take a backup of the xml file
 ·        Update the context variable s_nm_jvm_startup_properties to include
 -DUseSunHttpHandler=true parameter
 ·        Run AutoConfig in all APP and DMZ nodes and run in APP node 1 once again at last.
  
  
 Bring up the application
 adstrtal.sh apps/<apps> -mode=allnodes
 Verify that the URL is working fine for DMZ
 "For every OHS component on DMZ Nodes only for both run and patch  (Note grep OHS in $CONTEXT_FILE  - as run and patch domains are different)
  
 trusted.conf - Change Allow from localhost to allow from all
  
 <Location ~ ""^(/)+webservices(/)+ECXOTAInbound"">
         Order deny,allow
         Allow from all
         Allow from all
 </Location>"
  
 CLEANUP PHASE
 adop phase=cleanup
  
 Once the cleanup phase is completed, please run FS_CLONE to sync the run and patch FS as we have applied the FMW and WLS Technology level patches.  

New commands ( Just Collection)

alter database open resetlogs upgrade ; —RMAN Restore backup of lower version database to a higher version
Reference:

https://taliphakanozturken.wordpress.com/tag/alter-database-open-resetlogs-upgrade/
https://shivanandarao-oracle.com/2015/09/16/rman-restore-backup-of-lower-version-database-to-a-higher-version/

EBS Upgrades and Platform Migration

Upgrade     : Install & Upgrade All Data
Reimplement : Legacy Instance --> Data Migration --> Reimplemented Instance
Hybrid: Upgrade & Consolidate : Upgrade,Migrate data ==> Single Global Instance
... upgrading to a newer version of your current operating system?
... migrating to a different platform with the same endian format?
... migrating to a different platform with a different endian format?

Best practices for combined upgrades
----------------------------------------------------------
1)Consider the database and application tier migrations separately and plan to perform the database migration first.
2)Choose the right migration process for the database while considering the target platform, database size and process complexity.
3)Migrate and upgrade your EBS application tier from 11i to R12 by laying down the new application tier on the target platform as part of the upgrade.
Database Migrations and Upgrades
-------------------------------------------------------------------
A. An OS upgrade on the same platform
B. Migration to a new platform of the same endian format
Transportable Database (TDB)
C. Migration to a new platform of different endian formats
1. Export/Import (Datapump) , but slow for databases > 1TB
2. Transportable Tablespaces  is better for large databases

Application Tier Migrations and Upgrades
-------------------------------------------------------------------
Migrations without Upgrades
R12 Upgrade using Rapid Install
R12 Upgrade using Release Update Packs

Transportable Database is the fastest way to migrate data between two platforms(same endian format), as the process fundamentally is one of copying database files and
then using Recovery Manager (rman) to convert data files (using the ‘rman convert database’ command).
The EBS TDB process for migration does however require that the source and target database be of the same release and patchset version.
For larger databases (>1TB) however, the use of export/import can be an extremely slow process and alternatives should be considered.
TTS essentially is a process of moving or copying the data portion of the database,
and then using the Recovery Manager (rman) utility to convert the endian format of the data.
The use of TTS will still require export/import of certain objects in the EBS database such as metadata, system tables, etc.
Full Transportable Export/Import migration by default –
this is similar to Transportable Tablespaces
but automates a number of steps in the process of migration and reduces the manual tasks required to perform the migration.

References
Migration
https://blogs.oracle.com/stevenchan/a-primer-on-migrating-oracle-applications-to-a-new-platform
https://blogs.oracle.com/stevenchan/best-practices-for-combining-ebs-upgrades-with-platform-migrations
Planning Your EBS Upgrade from 11i to R12 and beyond https://www.oracle.com/technetwork/apps-tech/upgrade-planning-2011-1403212.pdf
Oracle E-Business Suite Upgrades and Platform Migration (Doc ID 1377213.1) *************

Click to access maa-ebs-exadata-xtts-321616.pdf


https://www.cisco.com/c/en/us/solutions/collateral/servers-unified-computing/ucs-5100-series-blade-server-chassis/Whitepaper_c11-707249.html
https://hansrajsao.wordpress.com/2015/03/06/migrating-oracle-database-to-exadata/
Cross Platform Transportable Tablespaces on 11i with 10gR2 (Doc ID 454574.1)

Upgrade
Oracle E-Business Suite Upgrade Guide, Release 11i to 12.1.3 https://docs.oracle.com/cd/B53825_08/current/acrobat/121upgrade.pdf
Oracle E-Business Suite Upgrade Guide, Release 12.0/1 to 12.2 https://docs.oracle.com/cd/E26401_01/doc.122/e48839.pdf
Oracle E-Business Suite Upgrade Guide, Release 11i to 12.2 https://docs.oracle.com/cd/E51111_01/current/acrobat/122upg11i.pdf

Planning Tips and Best Practices for Upgrading to EBS 12.2


Oracle E-Business Suite Release 12.2 Information Center – Upgrade (Doc ID 1583158.1) *****
Best Practices for Minimizing Oracle E-Business Suite Release 12.2.n Upgrade Downtime (Doc ID 1581549.1)
Oracle E-Business Suite Release 12.2 Information Center (Doc ID 1581299.1)
https://blogs.oracle.com/stevenchan/getting-started-with-the-release-12-technology-stack
Oracle E-Business Suite Release 12.2 Technology Stack Documentation Roadmap (Doc ID 1934915.1)

SCHEMA Refresh

SCHEMA Refresh Steps with Datapump
Script to take an EXPDP backup of the XX_DUMMY schema via Cron

### Scrpt to take an EXPDP backup of the XX_DUMMY schema
. $HOME/EBSDEV_dev-01.env
pswd=<code>cat $HOME/scripts/db_mon/.watchword</code>; export pswd
export SCHEMA=XX_DUMMY;
DUMP_LOG="$SCHEMA"_EXPDP_<code>date +"%d_%b_%y"</code>.log; export DUMP_LOG
DBA_MAIL=ebsdba@srinalla.com
echo "$SCHEMA Schema Backup started in $ORACLE_SID"|mailx -s "$SCHEMA Schema Backup started in $ORACLE_SID" $DBA_MAIL
expdp system/"$pswd" directory=WEEKLY_BACKUP dumpfile="$SCHEMA"_"$ORACLE_SID"_<code>date +"%d_%b_%y"</code>_EXP%U.dmp logfile="$DUMP_LOG" schemas=$SCHEMA filesize=3G cluster=n parallel=16
bkp_err=<code>cat /u03/EBSDEV/DMP_BKPS/$DUMP_LOG|grep -i "ora-"|wc -l</code>; export bkp_err
if [ "$bkp_err" -gt "0" ]
then
<code>cat /u03/EBSDEV/DMP_BKPS/WEEKLY_BKPS/$DUMP_LOG</code>|mailx -s "$SCHEMA Schema Backup completed with errors in $ORACLE_SID, Pls chk!!!" $DBA_MAIL
else
echo "$SCHEMA Schema Backup completed in $ORACLE_SID"|mailx -s "$SCHEMA Schema Backup completed in $ORACLE_SID" $MAILING_LIST
fi
#To Remove dumpfiles older than a month
find /u03/EBSDEV/DMP_BKPS/WEEKLY_BKPS/* -mtime +10 -exec rm {} ;

Working with Datapump ?

Let’s look at below in detail


Data Pump Best Practices
Dont Invoke expdp using SYS 
Purge recyclebin before Export , User/Table/DB Level
** PARALLELISM doesn't work with LOB COLUMN
How to use/estimate PARALLEL parameter in Datapump?
How to Check/Monitor DATAPUMP JOBS?
**Datapump will use two different load methods during import(impdp).

Data Pump Best Practices

pga_aggregate_target  -->Set this to high,it will improve the Data pump performance.
For export consistency use:-
------------------------------
FLASHBACK_TIME=SYSTIMESTAMP, This will increase UNDO requirements for the duration of the export

compression_algorithm=medium --12C Recommended option. Similar characteristics to BASIC, but uses a different algorithm

Always set parameters:-
------------------------------
METRICS=YES
EXCLUDE=STATISTICS
LOGTIME=ALL -->Timestamps   (From 12C)

Speed up Data Pump:-
------------------------------
PARALLEL=n
EXCLUDE=STATISTICS on export
EXCLUDE=INDEXES on import
1. Initial impdp with EXCLUDE=INDEXES 
2. Second impdp with INCLUDE=INDEXES SQLFILE=indexes.sql 
3. Split indexes.sql into multiple SQL files and run in multiple sessions

– Set COMMIT_WAIT=NOWAIT and COMMIT_LOGGING=BATCH during full imports

Direct import via database link (Network bandwidth and CPU bound):-
----------------------------------------------------------------------------------------------------
– Parameter: NETWORK_LINK
Run only impdp on the target system - no expdp necessary
No dump file written, no disk I/O, no file transfer needed

Restrictions of database links apply: – Does not work with LONG/LONG RAW and certain object types
Performance: Depends on network bandwidth and target's CPUs

Some Commands /Use Cases

remap_tablespace=OLD_TBS:NEW_TBS ==>Move all objects from one tablespace to another
remap_schema=old_schema:new_schema ==> Move a object to a different schema
expdp with content=metadata_only & impdp with remap_schema=A:Z  ==> Clone a User
remap_datafile=’/u01/app/oracle/oradata/datafile_01.dbf’:’/u01/datafile_01.dbf’  ==> Create your database in a different file structure
transform=pctspace:70 ,sample=70 -->tell the Data Pump to reduce the size of extents to 70% in impdp
transform=disable_archive_logging:Y
there is a database parameter FORCE LOGGING which overwrites this feature.
sqlfile=x_24112010.sql

EXPDP Filesize : Split or Slice the Dump file into Multiple Directories
expdp srinalla/srinalla job_name=exp_job_multiple_dir  schemas=STHOMAS  filesize=3G dumpfile=datapump:expdp_datapump_%U.dmp,TESTING:expdp_testing_%U.dmp logfile=dump.log compression=all parallel=10

While import,mention like this
dumpfile=datapump:expdp_datapump_%U.dmp,TESTING:expdp_testing_%U.dmp


Statistics are imported by default
compression
parallel
cluster  (Default=Y,From 11gR2,Parallelization in RAC, Can be on all nodes or only few nodes based on service_name=EBS_DP_12
         Sttaus Check :select inst_id, session_type from dba_datapump_sessions;
Commit the Import on every row with COMMIT=Y.

If COMMIT=Y, Import commits tables containing LONG, LOB, BFILE, ROWID, UROWID,
DATE or Type Columns after each row.
		  
Restart
restart the job with a different degree of parallelism, say 4 (earlier it was 6):
Export> parallel=4
Export> START_JOB
Export> continue_client --show progress

import using “table_exists_action=replace” and TABLES=(list of skipped tables)

nohup impdp system/secret NETWORK_LINK=olddb FULL=y  PARALLEL=25 &
impdp system attach
Import> status
Import> parallel=30 << this will increase the parallel processes if you want

Do not invoke expdp using ‘/ as sysdba’

Also, do not invoke expdp using ‘/ as sysdba’ – use the SYSTEM account – see the first Note section here
http://docs.oracle.com/cd/E11882_01/server.112/e22490/dp_export.htm#i1012781

Purge recyclebin before Export , User/Table/DB Level
select object_name, original_name, type, can_undrop as “UND”, can_purge as “PUR”, droptime from dba_recyclebin
where owner = ‘XX_DUMMY’;
purge table “BIN$HGnc55/7rRPgQPeM/qQoRw==$0” ;

** PARALLELISM doesn’t work with LOB COLUMN
parallelism doesn’t work ,because data pump serializes the dump when it comes to a LOB table.
The Approach should be like this
1) the whole database/schema minus LOB table and
2) the LOB table.
** pga_aggregate_target proved to be the most important change in the overall scheme of things
because indexes were built towards the end of the job and took 3 times longer
than actually creating the tables and importing the data in this test.
Check LOB Columns with below Query

SELECT  s.tablespace_name ,l.owner,l.table_name,l.column_name,l.segment_name,s.segment_type, round(s.bytes/1024/1024/1024,2) "Size(GB)"
FROM DBA_SEGMENTS s,dba_lobs l
where l.owner = s.owner and l.segment_name = s.segment_name
and l.owner not in ('SYS','SYSTEM','APPS','APPLSYS')
--and round(s.bytes/1024/1024/1024,2)>1
order by s.bytes desc;

Check below links how to fix the issue
http://jensenmo.blogspot.com/2012/10/optimising-data-pump-export-and-import.html
Master Note: Overview of Oracle Large Objects (BasicFiles LOBs and SecureFiles LOBs) (Doc ID 1490228.1)

How to use/estimate PARALLEL parameter in Datapump?

Before starting any export/import, it is better to use ESTIMATE_ONLY parameter. Divide the output by 250MB and based on the result decide on PARALLEL value
Finally when using PARALLEL option, do keep below points in mind
a. Set the degree of parallelism 2*no of CPU, then tune from there.
b. For Data Pump Export, the PARALLEL parameter value < dumpfiles
c. For Data Pump Import, the PARALLEL parameter value  < dumpfiles
For more details, you can refer to MOS doc 365459.1

How to Check/Monitor DATAPUMP JOBS?

DBA_DATAPUMP_JOBS
DBA_DATAPUMP_SESSIONS
V$SESSION_LONGOPS
Monitoring Data Pump http://www.dbaref.com/home/oracle-11g-new-features/monitoringdatapump
Queries to Monitor Datapump Jobs https://databaseinternalmechanism.com/2016/09/13/how-to-monitor-datapump-jobs/
How to delete/remove non executing datapump jobs? https://pavandba.com/2011/07/12/how-to-deleteremove-non-executing-datapump-jobs/

Datapump will use two different load methods during import(impdp)

  1. Direct load path – this is the main reason why datapump import (impdp) is faster than traditional import (imp)
  2. external table path
    But datapump cannot use direct path always due to some restrictions and because of this reason, sometimes you may observe impdp run slower than expected.
    Now, what are those situations when datapump will not use direct path? If a table exist with

    1. A global index on multipartition tables exists during a single-partition load. This includes object tables that are partitioned.
    2. A domain index exists for a LOB column.
    3. A table is in a cluster.
    4. There is an active trigger on a pre-existing table.
    5. Fine-grained access control is enabled in insert mode on a pre-existing table.
    6. A table contains BFILE columns or columns of opaque types.
    7. A referential integrity constraint is present on a pre-existing table.
    8. A table contains VARRAY columns with an embedded opaque type.
    9. The table has encrypted columns
    10. The table into which data is being imported is a pre-existing table and at least one of the following conditions exists:
    – There is an active trigger
    – The table is partitioned
    – A referential integrity constraint exists
    – A unique index exists
    11. Supplemental logging is enabled and the table has at least 1 LOB column.
    Note: Data Pump will not load tables with disabled unique indexes. If the data needs to be loaded into the table, the indexes must be either dropped or re-enabled.
    12. using TABLE_EXISTS_ACTION=TRUNCATE ON IOT

References
http://www.orafaq.com/wiki/Datapump
Master Note for Data Pump:MOS Note:1264715.1
For Compatibility and version changes:MOS Note:553337.
Using Oracle’s recycle bin http://www.orafaq.com/node/968
Master Note: Overview of Oracle Large Objects (BasicFiles LOBs and SecureFiles LOBs) (Doc ID 1490228.1)
Checklist For Slow Performance Of DataPump Export (expdp) And Import (impdp) (Doc ID 453895.1)
https://mayankoracledba.wordpress.com/2018/01/15/oracle-12c-data-pump-best-practices/
http://jeyaseelan-m.blogspot.com/2016/05/speed-up-expdpimpdp.html

Data Pump Export-Import Performance tips


http://its-all-about-oracle.blogspot.com/2013/06/datapump-expdpimpdp-utility.html
http://jensenmo.blogspot.com/2012/10/optimising-data-pump-export-and-import.html
https://rajat1205sharma.wordpress.com/2015/07/03/data-pump-export-import-performance-tips/
http://jensenmo.blogspot.com/2012/10/optimising-data-pump-export-and-import.html ***************
How to use PARALLEL parameter in Datapump? https://pavandba.com/2011/07/15/how-to-use-parallel-parameter-in-datapump/
impdp slow with TABLE_EXISTS_ACTION=TRUNCATE https://pavandba.com/2013/03/22/impdp-slow-with-table_exists_actiontruncate/

Transportable Tablespaces(TTS) for Upgrade/Migration

Let’s look at below in detail

Why TTS ?  
-->Transportable Tablespaces (TTS)
-->FTEX (TTS + Data Pump)  
-->FTEX using RMAN Backups (Less Down Time) 
High Level Steps for Migration of Data Using TTS
Time Taken Cross-Platform Tablespace(Xtts) Transport To The Export/Import Method  
-->Normal TTS Approach 
-->RMAN TTS Approach  
-->12C TTS Enhancement using RMAN backup sets  

tts-flow

Why TTS ?
Moving data using transportable tablespaces is much faster than performing either an export/import or unload/load of the same data. This is because the datafiles containing all of the actual data are just copied to the destination location, and you use an export/import utility to transfer only the metadata of the tablespace objects to the new database.

Transportable Tablespaces (TTS)

Transportable Tablespaces feature exists since Oracle 8i
– Can be used cross version ,Version to transport to must be always equal or higher
– Cross platform Transportable Tablespaces got introduced in Oracle Database 10g
==> Can be used cross version and cross platform
==> Required tablespaces to be in read-only mode
==> Extra work necessary for everything in SYSTEM/SYSAUX

Full Transportable Export/Import FTEX (TTS + Data Pump)

Transport an entire database in a single operation
– Cross version and cross platform
– Can include the database upgrade
– Combination of TTS for data tablespaces and Data Pump for administrative tablespaces (SYSTEM, SYSAUX ...)
– Supports information from database components such as Spatial,Text, Multimedia, OLAP, etc.
 Full transportable export supported since Oracle 11.2.0.3
 Full transportable import supported since Oracle 12.1.0.1
Relationship of XTTS to Data Pump and Recovery Manager 
XTTS works within the framework of Data Pump and Recovery Manager
(RMAN). Use Data Pump to move the metadata of the objects in the tablespaces
being transported to the target database. 
RMAN converts the datafiles being transported to the endian format of the target platform
You can use transportable tablespaces to perform tablespace point-in-time recovery (TSPITR).
RMAN uses the transportable tablespaces functionality to perform TSPITR. Therefore, any limitations on transportable tablespaces are also applicable to TSPITR.

Learn more at ………
OLL Video : Oracle Database 12c: Transporting Data

FTEX using RMAN Backups (Less Down Time)

Read more at ………
11G – Reduce Transportable Tablespace Downtime using Cross Platform Incremental Backup (Doc ID 1389592.1)
12C – Reduce Transportable Tablespace Downtime using Cross Platform Incremental Backup (Doc ID 2005729.1)

High Level Steps for Migration of Data Using TTS

Step 1: Check Platform Support And File Conversion Requirement
Step 2: Identify Tablespaces To Be Transported And Verify Self-Containment
Step 3: Check For Problematic Data Types
Step 4: Check For Missing Schemas And Duplicate Tablespace And Object Names
Step 5: Make Tablespaces Read-Only In Source Database
Step 6: Extract Metadata From Source Database
Step 7: Copy Files To Target Server And Convert If Necessary 
Step 8: Import Metadata Into Target Database
Step 9: Copy Additional Objects To Target Database As Desired

Read more at ………
Moving Oracle Databases Across Platforms without Export/Import

Time Taken Cross-Platform Tablespace(Xtts) Transport To The Export/Import Method

Task Export/Import Tablespace Transport
Export time 37 min 2 min
File transfer time 8 min 13 min
File conversion time n/a 14 min
Import time 42 min 2 min
Approximate total time 87 min 31 min
Export file size 4100 Mb 640 Kb
Target database extra TEMP tablespace requirement 1200 Mb n/a

Normal TTS Approach

1) On the source database
--------------------------------------
Validating Self Containing Property
exec DBMS_TTS.TRANSPORT_SET_CHECK('tbs', TRUE);
a) Put TBS in Read Only
b) Export the Metadata
exp FILE=/path/dump_file.dmp LOG=/path/tts_exp.log TABLESPACES=tbs-names TRANSPORT_TABLESPACE=y STATISTICS=none
**************Transfer Datafiles and export file to TARGET *********************
2) on the destination database
--------------------------------------
a)Import the export file.
impdp DUMPFILE=tts.dmp LOGFILE=tts_imp.log DIRECTORY=exp_dir REMAP_SCHEMA=master:scott TRANSPORT_DATAFILES='/path/tts.dbf'
b) Make TBS in READ-WRITE

RMAN TTS Approach

1) RMAN transport tablespace  On the source database
**************Transfer Datafiles and export file to TARGET *********************
2) Run Import script created by RMAN on the destination database
   impscrpt.sql (or) impdp command from the file

Read more at ………
RMAN TRANSPORT TABLESPACE By Franck Pachot
RMAN Transportable Tablespace
RMAN TRANSPORT TABLESPACE – Oracle Doc

Why Using RMAN TTS ?

-->RMAN checks that they are self-contained
-->Faster 
-->no need to put in Read only
Using RMAN TTS feature,the datafiles which contain actual data can be copied, thus making the migration faster. 
RMAN creates transportable tablespace sets from backup,eliminates need of putting tablespaces in read-only mode.
RMAN process for creating transportable tablespaces from backup uses the Data Pump Export and Import utilities
RMAN creates the automatic auxiliary instance used for restore and recovery on the same node as the source instance, 
there is some performance overhead during the operation of the TRANSPORT TABLESPACE command.

RMAN> transport tablespace tbs_2, tbs_3
   tablespace destination '/disk1/transportdest' --->  Set of Datafiles will be created here with Original Names & export log,Export dump file ,impscrpt.sql will also be created
   auxiliary destination '/disk1/auxdest'
   DATAPUMP DIRECTORY  mypumpdir
   DUMP FILE 'mydumpfile.dmp'
   IMPORT SCRIPT 'myimportscript.sql'
   EXPORT LOG 'myexportlog.log';

12C TTS Enhancement using RMAN backup sets
1) RMAN TTS on the source database

    a) Put TBS in Read Only ,
   b) RMAN--> BACKUP FOR TRANSPORT ( Metadata by Datapump,Backup set by RMAN)
      --> convert the platform and the endian format if required
   c) Back TBS to READ-WRITE

****** Transfrer Backup set & Dump files to Target Server from Source
2) RMAN TTS on the destination database

   a) Restore foreign tablespace ( Restore by RMAN, Import Metadata by Datapump)
   b) Make TBS in READ-WRITE  
RMAN> backup for transport format '/tmp/stage/tbs1.bkset' datapump format '/tmp/stage/tbs1.dmp' tablespace tbs1;
RMAN> restore foreign tablespace tbs1 
format '/u01/app/oracle/oradata/sekunda/tbs1.dbf' 
from backupset '/tmp/stage/tbs1.bkset' 
dump file from backupset '/tmp/stage/tbs1.dmp';

Read more at ………
Transport Tablespace using RMAN Backupsets in #Oracle 12c
12c How Perform Cross-Platform Database Transport to different Endian Platform with RMAN Backup Sets (Doc ID 2013271.1)

References:
How to Move a Database Using Transportable Tablespaces (Doc ID 1493809.1)
How to Migrate to different Endian Platform Using Transportable Tablespaces With RMAN (Doc ID 371556.1)
Master Note for Transportable Tablespaces (TTS) — Common Questions and Issues (Doc ID 1166564.1)
Transportable Tablespaces
Transportable Tablespaces Tips
Using Transportable Tablespaces for Oracle Upgrades

Transparent Data Encryption(TDE) -Overview

ebs-tde

Let’s look at below in detail

What is Oracle Transparent Data Encryption (TDE)? 
How do I migrate existing clear data to TDE encrypted data? 
How to find details for encryption/encrypted objects? 
How to Enable TDE in 12C? 
Best Practices for TDE 

What is Oracle Transparent Data Encryption (TDE)?
Oracle Advanced Security Encryption -TDE(Transparent Data Encryption) (From 10gR2)
allows administrators to encrypt sensitive data (i.e. Personally Identifiable Information or PII)
by protecting it from unauthorized access via encryption key if storage media, backups, or datafiles are stolen.
TDE supports two levels of encryption
1)TDE column encryption ( From 10gR2)
2)TDE tablespace encryption ( From 11gR1)
tde-col
tde-tablesspace

TDE uses a two tier encryption key architecture, consisting of a master key and one or more table and/or tablespace keys.
The table and tablespace keys are encrypted using the master key. The master key is stored in the Oracle Wallet.
When TDE column encryption is applied to an existing application table column, a new table key is created and stored in the Oracle data dictionary.
When TDE tablespace encryption is used, the individual tablespace keys are stored in the header of the underlying OS file(s).

How do I migrate existing clear data to TDE encrypted data?

You can  migrate existing clear data to encrypted tablespaces or columns using Online/Offline.
Existing tablespaces can be encrypted online with zero downtime on production systems or encrypted offline with no storage overhead during a maintenance period.
Online conversion  is available on Oracle Database 12.2.0.1 and above
Offline conversion  on Oracle Database 11.2.0.4 and 12.1.0.2.
In 11g/12c,you can use DBMS_REDEFINITION(Online Table Redefinition) copy existing clear data into a new encrypted tablespace background with no downtime.

How to find details for encryption/encrypted objects?
gv$encryption_wallet
V$ENCRYPTED_TABLESPACES
DBA_ENCRYPTED_COLUMNS

select * from gv$encryption_wallet; ---(gv$wallet)
select * from dba_encrypted_columns;
select table_name from dba_tables where tablespace_name in (select tablespace_name from dba_tablespaces where encrypted='yes');
select tablespace_name, encrypted from dba_tablespaces where encrypted='yes';
select et.inst_id, ts.name, et.encryptionalg, et.encryptedts from v$encrypted_tablespaces et, ts$ ts where et.ts# = ts.ts#;

How to Enable TDE in 12C?

Step 1: Set KEYSTORE location in $TNS_ADMIN/sqlnet.ora
Step 2: Create a Password-based KeyStore
Step 3: Open the KEYSTORE
Step 4: Set Master Encryption Key
Step 5: Encrypt your Data
     --> Make sure compatible parameter value greater than 11.2.0.
     5.1 Encrypt a Columns in Table
     5.2 Encrypt Tablespace
	 -->Note: Encrypting existing tablespace is not supported.( Do online/Offline Conversion)

Step 1: Set KEYSTORE location in $TNS_ADMIN/sqlnet.ora

==> created the directory
mkdir -p /u01/app/oracle/admin/SLOB/tde_wallet
==>declared it in sqlnet.ora
ENCRYPTION_WALLET_LOCATION=(SOURCE=(METHOD=FILE)(METHOD_DATA=(DIRECTORY=/u01/app/oracle/admin/SLOB/tde_wallet)))

Step 2: Create a Password-based KeyStore

  ==> sqlplus sys/password as sysdba
        administer key management create keystore '/u01/app/oracle/admin/SLOB/tde_wallet' identified by oracle;

Step 3: Open the KEYSTORE

administer key management set keystore open identified by oracle;

Step 4: Set Master Encryption Key

administer key management set key identified by oracle with backup;
  ==> Optionally created an auto-login wallet
Administer key management create auto_login keystore from keystore '/u01/app/oracle/admin/SLOB/tde_wallet' identified by oracle;

Step 5: Encrypt your Data
–> Make sure compatible parameter value greater than 11.2.0.
===> 5.1 Encrypt a Columns in Table

create table job(title varchar2(128) encrypt);->Create a table with an encrypted column,By default it is AES192
create table emp(name varchar2(128) encrypt using '3DES168', age NUMBER ENCRYPT NO SALT);--Create a column with an encryption algorithm
Alter table employees add (new_name varchar(40) ENCRYPT);--Encrypt an existing table column
alter table employees rekey using '3DES168'; --Change the Encryption key of an existing column

===> 5.2 Encrypt Tablespace
Note: Encrypting existing tablespace is not supported.( Do online/Offline Conversion)

CREATE TABLESPACE D_CRYPT DATAFILE '+DATA' SIZE 10G ENCRYPTION USING 'AES256' DEFAULT STORAGE (ENCRYPT);
Alter table TABLE_WITH_PAN move online tablespace D_CRYPT;

For 11g, We use orapki wallet & encryption key commands are used as below

orapki wallet create -wallet <wallet location> -pwd "<wallet password>" -auto_login
ALTER SYSTEM SET ENCRYPTION KEY IDENTIFIED BY "welcome1";
ALTER SYSTEM SET WALLET OPEN IDENTIFIED BY  "welcome1";

Best Practices for TDE
1)Avoiding accidentally deleting the TDE Wallet files on Linux
Go to Wallet Directory and set set the ‘immutable’ bit

chattr +i ewallet.p12  ( encrypted wallet )
chattr +i cwallet.sso  ( auto-open wallet )

Using Data Pump with TDE
By default,data in the resulting dump file will be in clear text, even the encrypted column data.
To protect your encrypted column data in the data pump dump files, you can password-protect your dump file when exporting the table.

expdp arup/arup ENCRYPTION_PASSWORD=pooh tables=accounts

Importing a password-protected dump file

impdp arup/arup ENCRYPTION_PASSWORD=pooh  tables=accounts table_exists_action=replace

References

Oracle TDE 12c – Concepts and Implementation

How to enable Transparent Data Encryption (TDE) in Oracle database


TDE best practices in EBS https://www.oracle.com/technetwork/database/security/twp-transparent-data-encryption-bes-130696.pdf
http://psoug.org/reference/tde.html
https://www.oracle.com/database/technologies/security/advanced-security.html
https://www.oracle.com/technetwork/database/security/tde-faq-093689.html
Encrypting Tablespaces By Arup Nanda https://www.oracle.com/technetwork/articles/oem/o19tte-086996.html
Encrypt sensitive data transparently without writing a single line of code https://blogs.oracle.com/oraclemagazine/transparent-data-encryption-v2
https://blogs.oracle.com/stevenchan/using-fast-offline-conversion-to-enable-transparent-data-encryption-in-ebs

Oracle EBS R12 and TDE Tablespace Encryption


https://juliandontcheff.wordpress.com/2017/06/02/twelve-new-features-for-cyber-security-dbas/
Transparent Data Encryption (TDE) (Doc ID 317311.1)
Master Note For Transparent Data Encryption ( TDE ) (Doc ID 1228046.1)
Quick TDE Setup and FAQ (Doc ID 1251597.1)
Managing TDE Wallets in a RAC Environment (Doc ID 567287.1)
How to Export/Import with Data Encrypted with Transparent Data Encryption (TDE) (Doc ID 317317.1)
Using Transportable Tablespaces to Migrate Oracle EBS Release 12.0 or 12.1 Using Oracle Database 12.1.0 (Doc ID 1945814.1)
Using Transparent Data Encryption (TDE) Column Encryption with Oracle E-Business Suite Release 12 (Doc ID 732764.1)

What is Table Fragmentation in Oracle ? ( Finding & Removing)

Fragmentation/ Reclaiming Wasted Space by Table Shrinking

select owner,table_name,round(((blocks*8)/1024/1024),2) "Size(GB)" ,round(((num_rows*avg_row_len/1024))/1024/1024,2) "Actual_Data(GB)",round((((blocks*8)) - ((num_rows*avg_row_len/1024)))/1024/1024,2) "Wasted_Space(GB)", round(((((blocks*8)-(num_rows*avg_row_len/1024))/(blocks*8))*100 -10),2) "Reclaimable Space(%)", partitioned from dba_tables where (round((blocks*8),2) &gt; round((num_rows*avg_row_len/1024),2))

and round(((((blocks*8)-(num_rows*avg_row_len/1024))/(blocks*8))*100 -10),2) &gt;20 -- More than  20% fragmentation

and round((((blocks*8)) - ((num_rows*avg_row_len/1024)))/1024/1024,2)&gt;5 --Wasted Space is more than 5GB

order by 5 desc;
OWNER TABLE_NAME Size(GB) Actual_Data(GB) Wasted_Space(GB) Reclaimable Space(%) PARTITIONED
XLA XLA_DISTRIBUTION_LINKS 375.6 246.22 129.37 24.44 YES
ZX ZX_LINES_DET_FACTORS 162.61 108.24 54.36 23.43 NO
AR RA_CUST_TRX_LINE_GL_DIST_ALL 122.6 74.72 47.88 29.05 NO
ASO ASO_ORDER_FEEDBACK_T 54.91 8.16 46.75 75.14 NO
XLA XLA_AE_LINES 82.74 55.29 27.45 23.17 YES
APPLSYS WF_ITEM_ACTIVITY_STATUSES_H 27 1.92 25.07 82.87 NO
AR RA_CUSTOMER_TRX_LINES_ALL 57.44 36.49 20.96 26.48 NO
ZX ZX_LINES 34.06 22.78 11.28 23.12 NO
AR AR_RECEIVABLE_APPLICATIONS_ALL 21.81 12.97 8.84 30.54 NO
XXCUST XXCUST_INTERFACE_LINES_ALL 15.39 8.9 6.49 32.16 NO
APPLSYS WF_JAVA_DEFERRED 5.08 0 5.08 90 NO
select owner,segment_name TABLE_NAME,segment_type,round(bytes/1024/1024/1024,2) "Size(GB)"

from dba_segments

where segment_type='TABLE'

and owner='APPLSYS'

and round(bytes/1024/1024/1024,2)&gt;1

order by 4 desc;
OWNER TABLE_NAME SEGMENT_TYPE Size(GB)
APPLSYS FND_LOG_MESSAGES TABLE 86.08
APPLSYS WF_ITEM_ACTIVITY_STATUSES_H TABLE 27.11
APPLSYS WF_ITEM_ATTRIBUTE_VALUES TABLE 20.38
APPLSYS FND_FLEX_VALUE_HIERARCHI_A TABLE 10
APPLSYS WF_ITEM_ACTIVITY_STATUSES TABLE 6.95
APPLSYS WF_NOTIFICATION_ATTRIBUTES TABLE 5.35
APPLSYS WF_JAVA_DEFERRED TABLE 5.1
APPLSYS WF_DEFERRED TABLE 4.8
APPLSYS FND_CONCURRENT_REQUESTS TABLE 3.51
APPLSYS FND_DOCUMENTS_TL TABLE 2.95
APPLSYS FND_LOGINS TABLE 2.16
APPLSYS FND_SEARCHABLE_CHANGE_LOG TABLE 1.97
APPLSYS WF_NOTIFICATIONS TABLE 1.79
APPLSYS DR$FND_LOBS_CTX$I TABLE 1.48

References

http://expertoracle.com/2017/05/07/reorg-tables-and-indexes-in-oracle-ebs-applications-best-practices/
http://select-star-from.blogspot.com/2013/09/how-to-check-table-fragmentation-in.html
How to Find and Remove Table Fragmentation in Oracle Database
https://www.oracle.com/technetwork/articles/database/fragmentation-table-shrink-tips-2330997.html

#tuning

What is Oracle Partitioning – ( Why ?,How ? &Benefits)

Partitioning ==>Increased performance and Ease of data management

Partitioning allows a database object(Table/View/IOT-index-organized table) to be subdivided into smaller pieces,called a partition.

Partition

It has its own name, and may optionally have its own storage characteristics
can be managed either collectively or individually.
From application point of View , no modifications are required.

Moreover, partitioning can greatly reduce the total cost of data ownership, using a “tiered archiving” approach of keeping older info still online on low cost storage devices.IT administrators can implement Information Lifecycle Management (ILM) protocols by partitioning data and moving historical data to low-cost storage.Partitioning can be used to obtain better concurrency as well as to decrease the number of rows to be processed through partition pruning and partition-wise joins

Partitioning for Performance
Partitioning pruning==> leveraging the partitioning metadata to only touch the data of relevance for a SQL operation
Partitioning for Manageability
Partitioning for Availability
Information
Lifecycle Management(ILM) with Partitioning

db_part_usagesWhich Partitioning Method Should Be Used?

Step 1 – Is Partitioning Necessary?
Step 2 - Should This Object Be Partitioned?
Step 3 - Which Partitioning Method Should Be Used?
Step 4 - Identify the Partition Key
Step 5 – Performance Check &amp; Access Path Analysis
Step 6 – Partitioned Table Creation and Data Migration

Method: 1 – Straight Insert
Oracle Data Pump
Data Pump Access Methods
Method 2 – Import/Export using Data Pump

Step 7 – Maintenance Step

PARTITION MAINTENANCE OPERATIONS
Adding partitions or sub partitions
Dropping a partition
Moving a partition
Splitting and merging partitions
Exchanging a partition with a table
Renaming a partition
Practical Partitioning Case Study 
Oracle General Ledger -->340 GB in size with General Ledger representing 90% of this data.
Background – Current Table Volumes , (35 % of database) , (6% of database) ,(5% of database)
Strategy ?
Partition Maintenance ?

The Benefits of This Partitioning Strategy

Maintenance
Purging historic data
Performance- Performance improvements of SQL were achieved through partition pruning.
Read-only partitions-in read only table spaces as this reduced the cost of storage
Types of Partitioning Methods ( Oracle Versions)
Oracle Database 11g provides the most comprehensive partitioning offering in the market, with a broad variety of partitioning techniques and advanced mechanisms.
With Oracle8, Range partitioning (on single column) was introduced.
With Oracle8i, Hash and Composite(Range-Hash) partitioning was introduced.
With Oracle9iList partitioning and Composite(Range-List) partitioning was introduced.
With Oracle 11g, Interval partitioning, Reference partitioning, Virtual column based partitioning, System partitioning and Composite partitionings [Range-Range, List-List, List-Range, List-Hash, Interval-Range, Interval-List, Interval-Interval] was introduced.
With Oracle 12c, Automatic List-Partitioning introduced.
USEFUL DATABASE VIEWS
DBA_PART_TABLES |DBA_PART_INDEXES
DBA_TAB_PARTITIONS | DBA_TAB_SUBPARTITIONS
DBA_PART_KEY_COLUMNS | DBA_SUBPART_KEY_COLUMNS
DBA_PART_COL_STATISTICS | DBA_SUBPART_COL_STATISTICS
DBA_PART_HISTOGRAMS | DBA_SUBPART_HISTOGRAMS
DBA_IND_PARTITIONS |DBA_IND_SUBPARTITIONS 
DBA_SUBPARTITION_TEMPLATES

References
Using_Database_Partitioning_with_Oracle_E-Business_Suite.pdf
White-paper:Optimizing Storage for Oracle E-Business Suite Applications

Meta link:Using Database Partitioning with Oracle E-Business Suite
#tuning
11g Partitioning Features
Partitioning in Oracle

sqlrpt.sql

sqlrpt.sql (Enhanced for Output Display & Skip Top Session

SET NUMWIDTH 10

SET TAB OFF

set long 1000000;

set lines 300 pages 300

set longchunksize 1000;

set feedback off;

set veri off;

prompt Specify the Sql id

prompt ~~~~~~~~~~~~~~~~~~

column sqlid new_value sqlid;

set heading off;

select 'Sql Id specified: &&sqlid' from dual;

set heading on;

prompt

prompt Tune the sql

prompt ~~~~~~~~~~~~

variable task_name varchar2(64);

variable err       number;

-- By default, no error

execute :err := 0;

set serveroutput on;

DECLARE

  cnt      NUMBER;

  bid      NUMBER;

  eid      NUMBER;

BEGIN

  -- If it's not in V$SQL we will have to query the workload repository

  select count(*) into cnt from V$SQLSTATS where sql_id = '&&sqlid';

  IF (cnt > 0) THEN

    :task_name := dbms_sqltune.create_tuning_task(sql_id => '&&sqlid',

	                                             scope=> DBMS_SQLTUNE.scope_comprehensive

												 ,time_limit  => 7200);

  ELSE

    select min(snap_id) into bid

    from   dba_hist_sqlstat

    where  sql_id = '&&sqlid';

    select max(snap_id) into eid

    from   dba_hist_sqlstat

    where  sql_id = '&&sqlid';

    :task_name := dbms_sqltune.create_tuning_task(begin_snap => bid,

                                                  end_snap => eid,

                                                  sql_id => '&&sqlid',

												  time_limit  => 7200);

  END IF;

    DBMS_OUTPUT.put_line('l_sql_tune_task_id: ' || :task_name);

  dbms_sqltune.execute_tuning_task(:task_name);

EXCEPTION

  WHEN OTHERS THEN

    :err := 1;

    IF (SQLCODE = -13780) THEN

      dbms_output.put_line ('ERROR: statement is not in the cursor cache ' ||

                            'or the workload repository.');

      dbms_output.put_line('Execute the statement and try again');

    ELSE

      RAISE;

    END IF;

END;

/

set heading off;

set lines 300 pages 3000

select dbms_sqltune.report_tuning_task(:task_name) from dual where :err <> 1;

select '   ' from dual where :err = 1;

set heading on;

undefine sqlid;

set feedback on;

set veri on;

Srinivasu Nalla

21 Dec 2018

Let’s look at below in detail


What is SGA/PGA ? 
SGA/PGA Suggestions (Best Practices)
Configuring SHMMAX and SHMALL for Oracle in Linux
Linux Huge Pages for Oracle (For Large SGA On Linux)
Disabling Transparent HugePages 
SGA/PGA Suggestions ( From AWR/ADDM)
SGA/PGA Paremeters ( What is? )
SGA/PGA Details ( From v$views)

sga

SGA is shared memory, so it is allocated when the database is started.
PGA (Process Global Area) is private memory allocated to individual processes,it can’t be pre-allocated,One need basis it will be allocated in RAM

SGA/PGA Suggestions (Best Practices)

OS Reserved RAM                  --> 10% of RAM for Linux & 20% or RAM for Windows
Oracle Database Connections RAM -->pga_aggregate_target(RAM regions for sorting and hash joins)
Oracle SGA Sizing for RAM        --> sga_max_size/sga_target
SGA Allocation (Capacity Planning)
45% of ram size RAM SIZE = 484 MB,
So 45 % should be = 216 MB
-->Split the 216 MM for SGA,PGA,BACKGROUNG PROCESSES
-->Fixed background process requires = 40 MB SGA =160 MB PGA =16 MB

Configuring SHMMAX and SHMALL for Oracle in Linux

Shared memory is important for the Oracle Database System Global Area (SGA).
Shared memory is nothing but part of Unix IPC System (Inter Process Communication) maintained by kernel
where multiple processes share a single chunk of memory to communicate with each other.
If there is insufficient shared memory, the Oracle database instance will not start. Set the SHMMAX and SHMALL to maximum values,for SGA Requirements

shm

SHMALL ==>(40% RAM), total size of Shared Memory Segments System wide set in “pages”.
SHMMAX ==> the maximum size (in bytes) of a single shared memory segment set
If the SHMMAX is set incorrectly, for example too low, you may receive the following error:
ORA-27123: unable to attach to shared memory segment.
ORA-27102: out of memory
Linux-x86_64 Error: 28: No space left on device.
is not relevant when you use /dev/shm POSIX shared memory (Oracle AMM).

Note :

SHMALL is the division of SHMMAX/PAGE_SIZE, e.g:. 1073741824/4096=262144.
Make SHMALL smaller than free RAM to avoid paging.
32-bit servers -3GB
64-bit servers - Half the RAM
Oracle recommends that the SHMALL should be the sum of the SGA regions divided by 4096 (The Linux page size).
-->Oracle 11g uses AMM by default,which relies on POSIX shared memory (/dev/shm-maps shared memory to files using a virtual shared memory filesystem) ,uses 4 KB pages(default PAGE_SIZE in Linux) and set to 50 % of RAM (allocated on demand and can be swapped)
For performance reasons,any Oracle database that uses more than 4 GB of SGA should use kernel Huge Pages.
Note:Linux kernel Huge Pages are setup manually, use 2M (2048 KB) pages, cannot be swapped and are reserved at system start up.
AMM uses either Sys V(virtual) shared memory(ipcs) including kernel hugepages or (POSIX) /dev/shm for SGA, but not both at the same time.
oracle@srinalla-db-1:~ $ grep -i shm /etc/sysctl.conf|grep -v '^#';ls -lrth /proc/sys/kernel/shm*;cd /proc/sys/kernel/;cat shmall;cat shmmni;cat shmmax
kernel.shmall = 154618822656
kernel.shmmax = 154618822656
kernel.shmmni = 4096
-rw-r--r-- 1 root root 0 Dec 21 03:11 /proc/sys/kernel/shmall
-rw-r--r-- 1 root root 0 Dec 21 03:11 /proc/sys/kernel/shmmni
-rw-r--r-- 1 root root 0 Dec 21 03:11 /proc/sys/kernel/shmmax
154618822656
4096
154618822656
oracle@srinalla-db-1:~ $ cat /etc/*-release ;ipcs -l
Red Hat Enterprise Linux Server release 5.11 (Tikanga)
------ Shared Memory Limits --------
max number of segments = 4096  /* SHMMNI  */
max seg size (kbytes) = 150994944 /* SHMMAX  */
max total shared memory (kbytes) = 618475290624 /* SHMALL  */
min seg size (bytes) = 1
------ Semaphore Limits --------
max number of arrays = 4096
max semaphores per array = 256
max semaphores system wide = 32000
max ops per semop call = 100
semaphore max value = 32767
------ Messages: Limits --------
max queues system wide = 2878
max size of message (bytes) = 8192
default max size of queue (bytes) = 65535

Linux Huge Pages for Oracle (For Large SGA On Linux)

With HugePages, the Linux memory page size is set at 2MB (instead of the default 4K).
This will improve OS performance when running Oracle databases with large SGA sizes.
For 11g,Enable ASMM feature additionally ,Refer more Linux HugePages

The AMM and HugePages are not compatible. One needs to disable AMM on 11g to be able to use HugePages. See Document 749851.1 for further information.
ebsdba@ebsdb-prd-01:~ $ grep Hugepagesize /proc/meminfo
Hugepagesize: 2048 kB
oracle@of3200:~ 
Oracle Linux: Script to find Recommended Linux HugePages (Doc ID 401749.1)
HugePages on Linux: What It Is... and What It Is Not... (Doc ID 361323.1)

Disabling Transparent HugePages 
Starting from RHEL6/OL6, Transparent HugePages are implemented and enabled by default. 
This is causing node reboots in RAC installations and performance problems on both single instance and RAC installations.
Oracle recommends disabling Transparent HugePages on all servers running Oracle databases, as described in this MOS note (Doc ID 1557478.1)
Check setting below
# cat /sys/kernel/mm/transparent_hugepage/enabled
[always] madvise never
#
SGA/PGA Suggestions ( From AWR/ADDM)
1)Undersized SGA Findings in ADDM Report:
2)SGA/PGA Advice Views select * from v$SGA_TARGET_ADVICE ;
    (Remember, bigger does not necessarily mean better/faster.) 
select * from v$PGA_TARGET_ADVICE;

SGA/PGA Paremeters ( What is? )

SGA

Some parameters to consider in SGA sizing include

SGA_MAX_SIZE --> For Dynamic Allocation set this higher than sga_target SGA_TARGET --> new(in 10g) -->Automatically sizes SGA components dynamic,Can be increased till SGA_MAX_SIZE,Change in value of 
affects only automatically sized components  
the (redo) log buffers, the shared pool, Java pool, streams pool, buffer cache, keep/recycle caches, 
and if they are  specified, the non-standard block size caches.
-------------->Usually, sga_max_size and sga_target will be the same value
DB_CACHE_SIZE - ->RAM buffer for the data buffers DB_XK_CACHE_SIZE SHARED_POOL_SIZE ->RAM buffer for Oracle and the library cache. LARGE_POOL_SIZE -> For RMAN parallel queries & Parallel execution allocates  buffers out of the large pool only when parallel_automatic_tuning=true. 
LOG_BUFFER ->RAM buffer for redo logs

PGA

PGA_AGGREGATE_LIMIT -> (12c),limit on Total PGA per Instance can't be below the PGA_AGGREGATE_TARGET.
** To revert to the pre-12c functionality,set parameter value to "0"

PGA_AGGREGATE_TARGET-->(9i), soft limit on the PGA used by the instance _pga_max_size ->

SGA/PGA Details ( From v$views)

set lines 200 pages 300 
Select Round(sum(bytes)/1024/1024,0) "Total Free memory(MB)" From V$sgastat Where Name Like '%free memory%'; 
Select POOL, Round(bytes/1024/1024,0) "Free memory(MB)" From V$sgastat Where Name Like '%free memory%';
show parameter sga ;
show parameter pga;
select NAME,round(BYTES/(1024*1024*1024),0) as "Size(GB)",round(BYTES/(1024*1024),0) as "Size(MB)" ,RESIZEABLE from v$sgainfo order by 2 desc;
Total Free memory(MB)
---------------------
5117
SQL>
POOL Free memory(MB)
------------ ---------------
shared pool 2143
large pool 706
java pool 1756
streams pool 512
SQL>
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
allow_group_access_to_sga boolean FALSE
lock_sga boolean FALSE
pre_page_sga boolean TRUE
sga_max_size big integer 100G
sga_target big integer 85G
unified_audit_sga_queue_size integer 1048576
SQL>
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
pga_aggregate_limit big integer 50G
pga_aggregate_target big integer 30G
SQL>
NAME Size(GB) Size(MB) RES
-------------------------------- ---------- ---------- ---
Maximum SGA Size 100 102400 No
Buffer Cache Size 70 71936 Yes
Free SGA Memory Available 15 15360
Shared Pool Size 12 11776 Yes
Startup overhead in Shared Pool 5 5078 No
Java Pool Size 2 1792 Yes
Streams Pool Size 1 512 Yes
Large Pool Size 1 768 Yes
In-Memory Area Size 0 0 No
Shared IO Pool Size 0 256 Yes
Data Transfer Cache Size 0 0 Yes
Granule Size 0 256 No
Redo Buffers 0 249 No
Fixed SGA Size 0 7 No
14 rows selected.

References:

Oracle PGA behavior
PGA and SGA sizing on linux
Monitoring Oracle SGA & PGA Memory Changes
Oracle SGA Sizing
Optimal SHMMAX for Oracle
Monitoring SGA (Free Memory) Using v$sgastat
Get confused about how to calculate SHMMAX and SHMALL on Linux.
Configuring SHMMAX and SHMALL for Oracle in Linux
https://docs.oracle.com/database/121/LADBI/app_manual.htm#LADBI7864
Configuring HugePages for Oracle on Linux (x86-64)

#tuning

Srinivasu Nalla

20 Dec 2018

oci-200

OCI Level 200 – Identity and Access Management

OCI Level 200 – Virtual Cloud Network

OCI Level 200 – Connectivity IPsec VPN

OCI Level 200 – Virtual Cloud Network Best Practices

OCI Level 200 – Connectivity FastConnect – Part 1

OCI Level 200 – Connectivity FastConnect – Part 2

OCI Level 200 – Compute

OCI Level 200 – Storage

OCI Level 200 – LoadBalancer

OCI Learn 200 – Database

#ebs-cloud

Oracle SQL Plan Changed?

Let’s look at below in detail


Why Execution Plan gets changes? 
How to fix Bad SQL Plan? 
Image flow of SQL Profile & SQL Plan BaseLine
Create SQL Profile ( Considering SQL Tuning Adviser Suggestions)
How to Create SQL Profile ? ( Which script is used?)

How to fix Bad SQL Plan? .

You can fix the SQL Plan by either SQL Profile / SQL Plan Baseline
Let’s take look at what they are and differences?

SQL profiles and SQL plan baselines help improve the performance of SQL statements by ensuring that the optimizer uses only optimal plans.
==>Baselines define the set of execution plans that can be used by each query, 
    SQL Profiles only provide additional information to “push” the optimizer to favor one or another plan,
==>SQL plan baselines are proactive, whereas SQL profiles are reactive.
==>SQL plan baselines reproduce a specific plan, whereas SQL profiles correct optimizer cost estimates.
SQL plan baselines prevent the optimizer from using suboptimal plans in the future.

Image flow of SQL Profile & SQL Plan BaseLine.

sqltsqlbase

Create SQL Profile ( Considering SQL Tuning Adviser Suggestions)

1) Run SQL Tuning Adviser(sqltrpt.sql/OEM) and get the suggestions for available best plan
2) Create SQL Profile using SQLT Script (coe_xfr_sql_profile.sql)
3) Flush the SQL ID from Memory ( Cancel any Jobs w.r to SQL ID,then flush)
4) Now you could see the new plan is reflected ( From gv$sql) 

This is best plan available for this SQL ID , we have to force the good plan by SQL Profile creation

PLAN_HASH_VALUE AVG_ET_SECS
————— ———–
2538395789 .437————> not reproducibe
2141716862 100.932———–last seen 2018-12-03/17:00:03 original plan
420475214 4898.131 ========== current Plan

How to Create SQL Profile ? ( Which script is used?)

Using coe_xfr_sql_profile.sql ,we can create it ( No need of Installation ,Just Download SQLTXPLAIN (SQLT),Refer Download SQLT section

SQL >  @coe_xfr_sql_profile.sql
SQL> @/dba/srinalla/scripts/sqlt/sqlt/utl/coe_xfr_sql_profile.sql
Parameter 1:
SQL_ID (required)

Enter value for 1: 8ap1zyhp2q7xd

PLAN_HASH_VALUE AVG_ET_SECS
--------------- -----------
     2538395789        .437
     2819292650        .599
     3897290700       4.068
     3111005336        4.21
     3446519203         6.4

Parameter 2:

PLAN_HASH_VALUE (required)
Enter value for 2: 2141716862

Values passed to coe_xfr_sql_profile:
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
SQL_ID : "8ap1zyhp2q7xd"
PLAN_HASH_VALUE: "2141716862"

Execute coe_xfr_sql_profile_8ap1zyhp2q7xd_2141716862.sql
on TARGET system in order to create a custom SQL Profile
with plan 2141716862 linked to adjusted sql_text.

Now, You can create the SQL Profile with above Script

SQL> @coe_xfr_sql_profile_8ap1zyhp2q7xd_2141716862.sql
PL/SQL procedure successfully completed.
SQL> WHENEVER SQLERROR CONTINUE
SQL> SET ECHO OFF;
            SIGNATURE
---------------------
   582750245629242078
           SIGNATUREF
---------------------
   582750245629242078
  ... manual custom SQL Profile has been created 

<strong><span style="color:#0000ff;">FROM SQL Tuning Advisor, this Can be run from DB Node as below(or OEM Cloud Control)</span></strong>
SQL @?/rdbms/admin/sqltrpt.sql

Specify the Sql id ~~~~~~~~~~~~~~~~~~ 
Enter value for sqlid: 8ap1zyhp2q7xd 
Sql Id specified: 8ap1zyhp2q7xd 
Tune the sql ~~~~~~~~~~~~
GENERAL INFORMATION SECTION
Tuning Task Name : TASK_313228
 Tuning Task Owner : SYS 
Workload Type : Single SQL Statement 
Scope : COMPREHENSIVE 
Time Limit(seconds): 1800 
Completion Status : COMPLETED 
Started at : 12/10/2018 02:09:31 
Completed at : 12/10/2018 02:16:37

Schema Name: APPS SQL ID : 8ap1zyhp2q7xd 
SQL Text : SELECT /*+ leading (gjl gjh) */ GJL.EFFECTIVE_DATE ACC_DATE,
/
FINDINGS SECTION (3 findings)
1- SQL Profile Finding (see explain plans section below)
 -------------------------------------------------------- 
A potentially better execution plan was found for this statement.
Recommendation (estimated benefit: 82.16%)
 ------------------------------------------ -
 Consider accepting the recommended SQL profile. 
execute dbms_sqltune.accept_sql_profile(task_name => 'TASK_313228',task_owner => 'SYS', replace => TRUE); 
id plan hash last seen elapsed (s) origin note 
-- ---------- -------------------- ------------ --------------- -------------- 
1 3013459780 2018-06-03/23:58:03 2.674 STS not reproducible 
2 3897290700 2018-12-06/14:00:54 7.268 AWR not reproducible 
3 2141716862 2018-12-03/17:00:03 22.321 AWR original plan
 4 1462347114 2018-12-06/17:00:38 50.572 AWR not reproducible 
5 1471114134 2018-12-07/16:00:04 60.236 AWR not reproducibe

3- Alternative Plan Finding
----------------------------------------------
Some alternative execution plans for this statement were found by searching the system's real-time and historical performance data. The following table lists these plans ranked by their average elapsed time. 
See section "ALTERNATIVE PLANS SECTION" for detailed information on each plan.

References

Database SQL Tuning Guide
Using Oracle baselines you can fix the sql plan for a SQLID
How To Improve SQL Statements Performance: Using SQL Plan Baselines
SQL Profiles Vs SQL Plan Baselines? by Girish Kumar
What is the difference between SQL Profiles and SQL Plan Baselines?
One Easy Step To Get Started With SQL Plan Baselines
2 Useful Things To Know About SQL Plan Baselines
Parsing SQL Statements in Oracle

#tuning

AD Patch Scripts

Patch Application Steps

1. Review Downtime required or not,
a. if downtime required,
i. Get list of invalid objects.
ii. Bring Down Applications
iii. Enable Maintenance Mode through adadmin
iv. Apply patch through adpatch
v. Disable Maintenance Mode through adadmin
vi. Bring up applications.
vii. Validate the patch has been applied and any new invalid objects
viii. Ensure all services are up and release.
b. if downtime not required,
i. Get list of invalid objects.
ii. Apply patch through adpatch options=hotpatch
iii. Validate the patch has been applied and any new invalid objects.
iv. Ensure all services are functioning.

Patching Activity on EBSPRD:

Steps:

1. Bring down application.
2. Enable maintenence mode.
3. adpatch defaultsfile=$APPL_TOP/admin/$TWO_TASK/ERPdefaults.txt
4. Enter credential till it prompt for location.
5. when prompt for location type ‘abort’
6. disable maintenence mode.
7. open screen session.
8. sudo to application user(applmgr) and source application env.
9. execute sh /apps/patches/Test2.sh
10. when it prompt for old session then say ‘NO’

Bringing down application tier.

1. concurrent node service
2. form/web services

Applying Patch:

Total 43 patch.
adpatch defaultsfile=$APPL_TOP/admin/$TWO_TASK/ERPdefaults.txt
above step we are running for recording all input we are providing to store in <strong>ERPdefaults.txt</strong><em></em> file
Merging patch's----------->If language patch then merging all language patch.

Adpatch script:

cat /apps/patches/Test2.sh
sqlplus apps @$AD_TOP/patch/115/sql/adsetmmd.sql ENABLE
adpatch defaultsfile=$APPL_TOP/admin/$TWO_TASK/ERPdefaults.txt patchtop=/apps/patches/Test2/22673920 logfile=l22673920.log driver=u22673920.drv workers=50
adpatch defaultsfile=$APPL_TOP/admin/$TWO_TASK/ERPdefaults.txt patchtop=/apps/patches/Test2/19066382 logfile=l19066382.log driver=u19066382.drv workers=50
adpatch defaultsfile=$APPL_TOP/admin/$TWO_TASK/ERPdefaults.txt patchtop=/apps/patches/Test2/20086596 logfile=l20086596.log driver=u20086596.drv workers=50
adpatch defaultsfile=$APPL_TOP/admin/$TWO_TASK/ERPdefaults.txt patchtop=/apps/patches/Test2/21471243 logfile=l21471243.log driver=u21471243.drv workers=50
adpatch defaultsfile=$APPL_TOP/admin/$TWO_TASK/ERPdefaults.txt patchtop=/apps/patches/Test2/25611260 logfile=l25611260.log driver=u25611260.drv workers=50
adpatch defaultsfile=$APPL_TOP/admin/$TWO_TASK/ERPdefaults.txt patchtop=/apps/patches/Test2/16972536 logfile=l16972536.log driver=u16972536.drv workers=50
adpatch defaultsfile=$APPL_TOP/admin/$TWO_TASK/ERPdefaults.txt patchtop=/apps/patches/Test2/21424549 logfile=l21424549.log driver=u21424549.drv workers=50
adpatch defaultsfile=$APPL_TOP/admin/$TWO_TASK/ERPdefaults.txt patchtop=/apps/patches/Test2/18426069 logfile=l18426069.log driver=u18426069.drv workers=50
adpatch defaultsfile=$APPL_TOP/admin/$TWO_TASK/ERPdefaults.txt patchtop=/apps/patches/Test2/16966157 logfile=l16966157.log driver=u16966157.drv workers=50
adpatch defaultsfile=$APPL_TOP/admin/$TWO_TASK/ERPdefaults.txt patchtop=/apps/patches/Test2/12802881 logfile=l12802881.log driver=u12802881.drv workers=50
adpatch defaultsfile=$APPL_TOP/admin/$TWO_TASK/ERPdefaults.txt patchtop=/apps/patches/Test2/22653309 logfile=l22653309.log driver=u22653309.drv workers=50
adpatch defaultsfile=$APPL_TOP/admin/$TWO_TASK/ERPdefaults.txt patchtop=/apps/patches/Test2/13692238 logfile=l13692238.log driver=u13692238.drv workers=50
adpatch defaultsfile=$APPL_TOP/admin/$TWO_TASK/ERPdefaults.txt patchtop=/apps/patches/Test2/16970138 logfile=l16970138.log driver=u16970138.drv workers=50
adpatch defaultsfile=$APPL_TOP/admin/$TWO_TASK/ERPdefaults.txt patchtop=/apps/patches/Test2/All_Lan_Dst logfile=Test2_lan_pth.log driver=u_Test22.drv workers=128
sqlplus apps @$AD_TOP/patch/115/sql/adsetmmd.sql DISABLE;
exit

ebsprd@apps-ebsprd01:/apps/patches/ $ cat $APPL_TOP/admin/$TWO_TASK/ERPdefaults.txt
#
# AD Default Values File
#
#
# Updated by AutoPatch on Fri Jun 16 2017 22:16:42
#

## Start of Defaults Record
  %%START_OF_TOKEN%%
        APPL_TOP
  %%END_OF_TOKEN%%

  %%START_OF_VALUE%%
        /im/EBSPRD/apps/apps_st/appl
  %%END_OF_VALUE%%
## End of Defaults Record

## Start of Defaults Record
  %%START_OF_TOKEN%%
        DBNAME
  %%END_OF_TOKEN%%

  %%START_OF_VALUE%%
        EBSPRD_BALANCE
  %%END_OF_VALUE%%
## End of Defaults Record

## Start of Defaults Record
  %%START_OF_TOKEN%%
        MATCH_APPL_TOP
  %%END_OF_TOKEN%%

  %%START_OF_VALUE%%
        Yes
  %%END_OF_VALUE%%
## End of Defaults Record

## Start of Defaults Record
  %%START_OF_TOKEN%%
        LOG_FNAME
  %%END_OF_TOKEN%%

  %%START_OF_VALUE%%
        ERPdefaults.log
  %%END_OF_VALUE%%
## End of Defaults Record

## Start of Defaults Record
  %%START_OF_TOKEN%%
        DEF_BATCH_SIZE
  %%END_OF_TOKEN%%

  %%START_OF_VALUE%%
        1000
  %%END_OF_VALUE%%
## End of Defaults Record

## Start of Defaults Record
  %%START_OF_TOKEN%%
        ORACLE_HOME
  %%END_OF_TOKEN%%

  %%START_OF_VALUE%%
        /im/EBSPRD/apps/tech_st/10.1.2
  %%END_OF_VALUE%%
## End of Defaults Record

## Start of Defaults Record
  %%START_OF_TOKEN%%
        CORRECT_DBENV
  %%END_OF_TOKEN%%

  %%START_OF_VALUE%%
        Yes
  %%END_OF_VALUE%%
## End of Defaults Record

## Start of Defaults Record
  %%START_OF_TOKEN%%
        DEF_SYSTEM_PWD
  %%END_OF_TOKEN%%

  %%START_OF_VALUE%%
        7782A9A20F0B4F635CADA0EA24D095D610C0
  %%END_OF_VALUE%%
## End of Defaults Record

## Start of Defaults Record
  %%START_OF_TOKEN%%
        ORACLE_username_Application_Object_Library
  %%END_OF_TOKEN%%

  %%START_OF_VALUE%%
        APPLSYS
  %%END_OF_VALUE%%
## End of Defaults Record

## Start of Defaults Record
  %%START_OF_TOKEN%%
        ORACLE_password_Application_Object_Library
  %%END_OF_TOKEN%%

  %%START_OF_VALUE%%
        7782A9A282CA42FED619A93FF9202C88D75612
  %%END_OF_VALUE%%
## End of Defaults Record

## Start of Defaults Record
  %%START_OF_TOKEN%%
        DEF_ACTIVATE_EMAIL
  %%END_OF_TOKEN%%

  %%START_OF_VALUE%%
        No
  %%END_OF_VALUE%%
## End of Defaults Record

## Start of Defaults Record
  %%START_OF_TOKEN%%
        MISSING_TRANSLATED_VERSION
  %%END_OF_TOKEN%%

  %%START_OF_VALUE%%
        yes
  %%END_OF_VALUE%%
## End of Defaults Record

#notes

EBS on OCI ( My Notes)

Oracle Cloud Infrastructure (OCI), IaaS from Oracle.
There are many cloud infrastructure providers, like Amazon (AWS), Oracle (OCI), Microsoft (Azure), Google (Google Cloud).
What’s New in Oracle Cloud Infrastructure

EBS (R12) Deployment Options on Cloud
EBS Middle/Application Tier
Oracle EBS Middle Tier can only be deployed on IaaS Service Model and within IaaS Service Model, it is either on Oracle Cloud Infrastructure (OCI) or OCI Classic.
EBS Database Tier
Oracle EBS Database Tier can be deployed either on IaaS or PaaS Service Model and within PaaS Service Model, it’ll be Database Cloud Service (DBCS). DBCS has few deployment options and out of them, two are supported for EBS (R12) on Cloud i.e. Database as a Service DBaaS and Exadata Cloud Service (Exa CS).

Metalink EBS on Oracle Cloud

OLL
OCI Study Guide *********
Just-in-Time Videos

OCI Introduction
OCI -Foundation
OCI Advanced
Tips for OCI Architect Certification
Certification Path

White Paper: EBS Deployment on OCI ****

Cloud On boarding Guide

OCI Blog

EBS on OCI Blog Posts
Cloud computing concepts (HA, DR, Security), regions, availability domains, OCI terminology and services, networking, databases, load balancing, IAM, DNS, FASTCONNECT, VPN, Compartments, tagging, Terraform, with focus on how to use it with OCI and Exadata

OCI for Apps DBA **
EBS (R12) on Cloud: Architects Perspective ****
EBS (R12) On Cloud (OCI): High Level Steps *****
EBS Cloud Admin Tool
EBS Cloud Manager

EBS Cloud Admin Tool on OCI is superseded with EBS Cloud Manager
EBS Cloud Manager
Provisioning –> 2 options (Simple & Advance)
Migration –> Lift and Shift using Cloud Manager
Cloning –>
Deletion
Cloud Service Model: SaaS | PaaS | IaaS
EBS (R12) On Cloud Deployment Architecture
Role of Apps DBA in Cloud

OCI vs OCI Classic
Storage

Summary

Knowledge of Oracle Cloud (OCI & DBCS) ,EBS-OCI Lift & Shift
	Taken Oracle Cloud Internal Training on OCI(Iaas),DBCS(Paas)
	High Level understanding of Core OCI fundamentals
	Familiar with EBS-OCI Lift & Shit & High Level Deployment & Tools like EBS Cloud Manager(EBS Cloud Admin tool)

INTERNAL TRAININGS & KNOWLEDGE

Topic	Oracle Cloud offerings for EBS (OCI & DBCS-Dbas/Exacs)
Mode	Online ,Self-Paced &
Role	Cloud EBS DBA

Gained Knowledge in following Areas

•	OCI(IaaS) & DBCS (Exa CS)
•	Knowledge of OCI (Infrastructure,Compute,Database,Networking(VCN,VPN,IPSec),Storage Services(FSS,IAM,VPN IPSec tunnel functionality)
•	Knowledge in DBCS(DBaaS & ExaCS),Cloud Backup Storage Service.
•	Basic Understanding of Migrating EBS to OCI ((Lift & Shift)
•	 Familiar with High-Level Steps of deployment of EBS on OCI
•	 Knowledge in EBS Cloud Admin Tool
•	 Familiar with Install and configure EBS Cloud Manager
•	Knowledge in EBS Cloud Manager (Provisioning,Migration,Cloning,Cloning,Deletion)

#ebs-cloud, #notes

EBS Info

EBS 12.1: Premier Support to December 2021
EBS 12.2: Premier Support to At Least December 2030
db

https://blogs.oracle.com/ebs/oracle-ebs-news
https://blogs.oracle.com/ebsandoraclecloud/
https://blogs.oracle.com/stevenchan/training-3
https://blogs.oracle.com/stevenchan
E-Business Suite DBA
dba-self

Apps DBA Interview-1
Apps DBA Interview-2
Apps DBA Interview-3

Few Support Issues
Support Issues

 

EBS Maintenance ( Patching/Upgrades/Migration)

My Posts 

( Still Writing Notes for below..)
EBS Upgrades and Platform Migration
Transportable Tablespaces(TTS) for Upgrade/Migration
Working with Datapump ?

EBS Upgrades
Oracle E-Business Suite Upgrade Guide
Sizing and Best Practices
12.2 Upgrade Process Flow
Planning Your Upgrade
12.2 Upgrade Best Practices to Minimize Downtime

EBS Patching Issues
Top Patching Issues 11i & 12.x
Restart a failed patch?

Webcasts
Webcast:EBS Maintenance
Webcast: Advanced Architectures:DMZ
Webcast:Auditing and Security
Webcast: TLS 1.2 Configuration

EBS Debug and Tracing
Query : Trace/Debug Profile

Critical Patch Update Advisory

15 January 2019
16 April 2019
16 July 2019
15 October 2019

JRE Plugin Upgrade (Doc ID 393931.1)

JRE 8-->JRE 1.8.0_191/192
JRE7 -->JRE 1.7.0_201
JRE 6-->JRE 1.6.0_211 

Java Web Start and Java Plug-in ?

browser-independent architecture ,to deprecate the Java Plug-in for web browsers starting with the release of Java SE 9

#notes

Performace Tuning (DB/EBS)

perf


My Posts    Tuning (WIP)-Private


Oracle SQL Plan Changed?
Configuring Oracle SGA/PGA ?

( Still Writing Notes for below..)
What is Table Fragmentation in Oracle ? ( Finding & Removing)
What is Oracle Partitioning – ( Why ?,How ? &Benefits)
OS Watcher-OSWbb/RDA-Remote Diagnostics Agent/ORAchk – Health Checks
Locks vs Latch/Enqueues & Its Types 


Database Performance ( Article Collection)


Oracle Statistics (All About)>An Excellent Article about end to end to Statistics for DB/SCHEAMA/object level
DBMS_ADVISOR–>Details of How to execute SQL access advisor (DBMS_ADVISOR)?
DB Performance Toolkit –> Overview of Performance tools (AWR/ADDM/ASH,SQLT & SQLHC) & What data you need to collect for Performance Issues for Oracle SR?
Performace Tuning DB level->Collection of articles related to DB Time,Hints ,Execution plans,Shared pool,WAIT EVENTS,Latch Issues & Sql Trace & Autotrace

================ Newly Added on 01-JAN-2019=================== (Gavin Soorma(Oracle ACE) Articles)

Oracle Wait Events Causes And Resolutions->Just going through
Why Do My Execution Plans Not Change After Gathering Statistics?->Just going through
Performance Tuning Tips And Techniques->Just going through
ASH And AWR Performance Tuning Scripts->Just going through
Using The Awr History Tables To Compare Performance – Part 1->Just going through
Using The Awr History Tables To Compare Performance – Part 2>->Just going through
LOB’S – SOME PERFORMANCE TUNING CONSIDERATIONS>->Just going through
DBMS_ADVANCED_REWRITE – TUNING WITHOUT TOUCHING THE CODE>->Just going through


EBS Performance ( Article Collection)


Gathering EBS Database Statistics
Recommended Database Parameters for EBS
Approach to Performance Tuning Oracle Apps
Webcast:EBS performance tuning
Troubleshooting Performance Issues??
Debugging ??
Database Partitioning-EBS
Tuning the EBS Database Layer
Tuning All Layers of EBS
Queries for Troubleshooting Adv Typologies
Pinning Objects to Improve Apps Performance
SR Data for Perf Issue

#tuning

Perf Tuning/ Trace/Gather Stats

How to Use AWR Reports to Diagnose Database Performance Issues (Doc ID 1359094.1)
https://carlos-sierra.net/tag/execution-plan/
http://kerryosborne.oracle-guy.com/category/oracle/plan-stability/
Historical SQL Monitor reports in 12c!
https://community.oracle.com/thread/3269017 SQL review from an DBA perspective

Enable Trace and Generate TKPROF
SELECT p.PID, p.SPID, s.SID,s.serial# FROM v$process p, v$session s WHERE s.paddr = p.addr AND s.SID = 2414 ;
PID SPID SID


552 4999 2414

ORADEBUG SETOSPID 5382
ORADEBUG UNLIMIT
ORADEBUG EVENT 10046 TRACE NAME CONTEXT FOREVER,LEVEL 12
ORADEBUG TRACEFILE_NAME

SQL> ORADEBUG SETOSPID 5382
Oracle pid: 366, Unix process pid: 2244, image: oracle@123.nus.sri.com
SQL> ORADEBUG UNLIMIT
Statement processed.
SQL> ORADEBUG EVENT 10046 TRACE NAME CONTEXT FOREVER,LEVEL 12
Statement processed.
SQL> ORADEBUG TRACEFILE_NAME
/u01/app/db_1/diag/rdbms/SR12/SR/trace/SR_ora_2244.trc
SQL>
  1. Turn off the SQL trace for the session.
    SQL> ORADEBUG SETOSPID 5382
    SQL>oradebug event 10046 trace name context off 

Others:
—————-
alter system set events ‘sql_trace[sql: cjrha4bzuupzf] level=12’;
alter system set events ‘sql_trace[sql: cjrha4bzuupzf] off’;

tkprof aa.trc aa.tkprof explain=apps/ZW48Acbrtkmts sort=prsela,exeela,fchela sys=no

SQLID TEXT AND Plan Details

SELECT inst_id,sql_id,LAST_LOAD_TIME, LAST_ACTIVE_TIME ,PLAN_HASH_VALUE,SQL_FULLTEXT,ROWS_PROCESSED,EXECUTIONS,sql_plan_baseline,sql_profile
FROM   gV$SQl a
WHERE  a.sql_id ='7ttgy18puazt6'
ORDER BY LAST_ACTIVE_TIME desc;

–> awr_high_cost_sql.sql

select p.sql_id,p.PLAN_HASH_VALUE,p.TIMESTAMP,p.cost
from dba_hist_sql_plan p,dba_hist_sqltext s
where p.id = 0 and p.sql_id = s.sql_id
and p.sql_id='8ap1zyhp2q7xd'
order by p.TIMESTAMP desc;

Select * from table(dbms_xplan.display_cursor(‘9w6zqap3f5xbw’));
Select * from table(dbms_xplan.display_awr(‘9w6zqap3f5xbw’));
Select * from table(dbms_xplan.display_awr(‘9w6zqap3f5xbw’,’2095498791′));
Select * from table(dbms_xplan.display_awr(‘9w6zqap3f5xbw’,’2716887413′));

@?/rdbms/admin/sqltrpt.sql–> SQL Tuning Advisor
coe_xfr_sql_profile.sql – -> To Create SQL Profile
@?/rdbms/admin/awrrpti.sql – -> To Create AWR by Node
@?/rdbms/admin/awrrpt.sql – -> To Create AWR by DB
@?/rdbms/admin/awrsqrpt.sql – -> To Create AWR by SQL
@?/rdbms/admin/ashrpt.sql – -> To Create ASH
@?/rdbms/admin/utlrp.sql – -> Recompile Invalids

select object_name,owner,created,status,object_type,’/*’ || created || ‘*/’ || dbms_metadata.get_ddl(‘INDEX’,object_name,owner)
from dba_objects
where object_type = ‘INDEX’ and object_name in
(select INDEX_NAME from dba_indexes where table_name in (‘GL_PERIOD_STATUSES’))
order by created desc, object_name;

select * from DBA_IND_COLUMNS where table_name=’GL_PERIOD_STATUSES’;

–> ADDMM Suggestin

Set pages 1000
Set lines 75
Select a.execution_end,a.OWNER,a.TASK_NAME,a.DESCRIPTION,a.ADVISOR_NAME, b.type, b.impact, d.rank, d.type,
'Message           : '||b.message MESSAGE,
'Command To correct: '||c.command COMMAND,
'Action Message    : '||c.message ACTION_MESSAGE
From dba_advisor_tasks a, dba_advisor_findings b,
Dba_advisor_actions c, dba_advisor_recommendations d
Where a.owner=b.owner and a.task_id=b.task_id
And b.task_id=d.task_id and b.finding_id=d.finding_id
And a.task_id=c.task_id and d.rec_id=c.rec_Id
--And a.task_name like 'ADDM%'
and a.status='COMPLETED'
and trunc(a.execution_end)>sysdate -1
Order by a.execution_end,b.impact, d.rank;

Gather Stats /Stale :

select OWNER,table_name,stale_stats,last_analyzed,NUM_ROWS,blocks,OBJECT_TYPE from
dba_tab_statistics where table_name=’WF_ITEMS’;

SELECT ob.owner, ob.object_name, ob.object_type, savtime,analyzetime,
DECODE(rowcnt,0,0,round((samplesize/rowcnt)*100,0)) "Sample(%)",
flags, rowcnt, blkcnt, avgrln ,samplesize
FROM sys.WRI$_OPTSTAT_TAB_HISTORY, dba_objects ob
WHERE 1=1
and object_name='WF_ITEMS'
and object_type in ('TABLE')
and object_id=obj#
and  savtime > SYSDATE - 30
order by savtime desc;

select ‘exec fnd_stats.gather_table_stats(”’||owner||”’,”’||table_name||”’,percent=>25,degree=>8,cascade=>true);’ ” GSS”
from dba_tables where table_name in (‘WF_ITEMS’);
set lines 300 pages 300
select ‘exec DBMS_STATS.GATHER_TABLE_STATS(ownname =>”’||owner||”’,tabname =>”’||table_name||”’,ESTIMATE_PERCENT=>DBMS_STATS.AUTO_SAMPLE_SIZE,degree=>8,cascade=>true);’ ” GSS”
from dba_tables where table_name in (‘WF_ITEMS’);

SELECT owner,
segment_name,
segment_type,
tablespace_name,
round(bytes/1048576/1024,2) GB,
initial_extent,
next_extent,
extents,
pct_increase
FROM
DBA_SEGMENTS
WHERE
SEGMENT_TYPE = ‘TABLE’
AND SEGMENT_NAME in (‘HR_LOCATIONS_ALL_TL’,’HR_LOCATIONS_ALL’);

Select * from DBA_TAB_MODIFICATIONS  where table_name='WF_ITEMS';
SELECT m.table_owner
, m.table_name
, t.last_analyzed,m.timestamp
, m.inserts
, m.updates
, m.deletes
, t.num_rows
, round(( m.inserts + m.updates + m.deletes ) / CASE WHEN t.num_rows IS NULL OR t.num_rows = 0 THEN 1 ELSE t.num_rows END,0) "Change Factor"
FROM dba_tab_modifications m
, dba_tables t
WHERE t.owner = m.table_owner
AND t.table_name = m.table_name
AND m.inserts + m.updates + m.deletes > 1
AND m.table_name='WF_ITEMS'
and round(( m.inserts + m.updates + m.deletes ) / CASE WHEN t.num_rows IS NULL OR t.num_rows = 0 THEN 1 ELSE t.num_rows END,0) >1
ORDER BY "Change Factor" DESC;
select trunc(last_analyzed),count(*)
from dba_tables
where owner  not in ('SYS','SYSTEM')
group by trunc(last_analyzed)
order by 1 desc;

EBS READ ONLY Form Access

Need to Enable all accounts (including SYSADMIN) are read only as part of DR Testing

Requirement to enable query only for all the application users.
Step 1: Create backup table for fnd_form_functions

Create table fnd_form_functions_bkp_t1 as select * from fnd_form_functions;

Step 2: update parameters as QUERY_ONLY=”YES”

update fnd_form_functions set parameters=’QUERY_ONLY=”YES”‘ where parameters is null and form_id is not null;

DECLARE
f1 number;
p1 VARCHAR2(2000 BYTE);
CURSOR C1 IS
select function_id, PARAMETERS from fnd_form_functions where form_id is not null
and parameters ‘QUERY_ONLY=”YES”‘ AND parameters ‘QUERY_ONLY=YES’
and parameters not like ‘%QUERY_ONLY=”YES”%’ AND parameters not like ‘%QUERY_ONLY=YES%’;
begin
open c1;
loop
fetch c1 into f1,p1;
exit when c1%notfound;
update fnd_form_functions set parameters=p1||’ QUERY_ONLY=”YES”‘
where function_id=f1
;
–commit;
END LOOP;
close c1;
end;
/

commit;

Rollback plan :

Step 1: update parameters to null in fnd_form_functions with backup table

update fnd_form_functions set parameters=null where function_name in (select function_name from fnd_form_functions_bkp_t1 where parameters is null and form_id is not null);

DECLARE
f1 number;
p1 VARCHAR2(2000 BYTE);
CURSOR C1 IS
select function_id, PARAMETERS from fnd_form_functions_bkp_t1 where form_id is not null
and parameters ‘QUERY_ONLY=”YES”‘ AND parameters ‘QUERY_ONLY=YES’
and parameters not like ‘%QUERY_ONLY=”YES”%’ AND parameters not like ‘%QUERY_ONLY=YES%’;
begin
open c1;
loop
fetch c1 into f1,p1;
exit when c1%notfound;
update fnd_form_functions set parameters=p1
where function_id=f1
;
–commit;
END LOOP;
close c1;
end;
/

Commit;

CPU July-2018 EBS R12.1.3

Patch Location:

/interface/patches/CPU_2018

######################################################################################################

1) Apply AD Pacthes (US) in below order

====>Enable Maintaince Mode

sqlplus apps/apps4ebsdb @$AD_TOP/patch/115/sql/adsetmmd.sql ENABLE

cd /interface/patches/CPU_2018/ADPATCH_US

23231676

24840574

25110821

22284589

23200210

————————————————————>

28018146–> PRE-Steps : adgrants.sql from DB Node

o Compare the version of adgrants.sql(UNIX) in $APPL_TOP/admin to that in patch directory(PATCH/admin/adgrants.sql)

o Copy the higher version of adgrants.sql (UNIX) to RDBMS “$ORACLE_HOME/appsutil/admin”

Login to DB tier & use SQL*Plus to run the script as sys user.

sqlplus “/as sysdba”

@$ORACLE_HOME/appsutil/admin/adgrants.sql APPS

Apply Patch –28018146

——————————————————————>

====>Disable Maintaince Mode

sqlplus apps/apps4ebsdb @$AD_TOP/patch/115/sql/adsetmmd.sql DISABLE

######################################################################################################

2) Apply NLS Pacthes in Hotpacth ( Screen Mode)

screen -S CPU_18JUL

cd /interface/patches/CPU_2018/ADPATCH_NLS/ADPATCH_NLS_D

adpatch options=hotpatch driver=u_merged.drv logfile=CPU_JUL2018_12_17SEP.log

######################################################################################################

Take backup of 10.1.3 and 10.1.2 Homes

cp -pr 10.1.3 10.1.3_bkp_CPU_19SEP

cp -pr 10.1.2 10.1.2_bkp_CPU_19SEP

10.1.3 Patch :

######################################################################################################

1) Apply 21845960———————–>

export OPATCH_PLATFORM_ID=46

echo $OPATCH_PLATFORM_ID

cd /interface/patches/CPU_2018/1013_PATCH/21845960

. $INST_TOP/ora/10.1.3/*.env;echo $ORACLE_HOME;

$ORACLE_HOME/OPatch/opatch napply

$ORACLE_HOME/OPatch/opatch lsinventory|egrep 21845960

unset OPATCH_PLATFORM_ID

OUI Patch

applmgr@of201:/interface/patches $ unzip p6640838_10106_Linux-x86-64.zip

2) Apply 22123753———————–>

cd /interface/patches/CPU_2018/1013_PATCH/22123753

. $INST_TOP/ora/10.1.3/*.env;echo $ORACLE_HOME;

$ORACLE_HOME/OPatch/opatch apply

$ORACLE_HOME/OPatch/opatch lsinventory|egrep 22123753

10.1.2 Patch

######################################################################################################

1) Apply 26825525

cd /interface/patches/CPU_2018/1012_PATCH/26825525

. $INST_TOP/ora/10.1.2/*.env;echo $ORACLE_HOME;

$ORACLE_HOME/OPatch/opatch apply

$ORACLE_HOME/OPatch/opatch lsinventory|egrep 26825525

###################

“Forms having some issues while opening

to fix the issue,Re-deploy forms.ear file

(Given in Readme,but steps are not correct,refer comments)”

“Deploying a New EAR File in Oracle Applications Release 12 (Doc ID 397174.1) –Change Password for oc4jadmin

TXKRUN.PL Deploying a New Forms .EAR File Error with OPMNCTL Start (Doc ID 1295791.1) — EBSPRD patch opmnctl”

###################

JRE Patch

Using JDK 7.0 Latest Update with Oracle E-Business Suite Release 12.0 and 12.1 (Doc ID 1467892.1)

Upgrading JRE Plugin with Oracle Applications R12 (MetaLink Note 393931.1)

R12 E-Business Suite Java / JRE Upgrade Does Not Correctly Update The 3 Digit Version Of Java Installed In The Context File,

Forms Applications, Or Java Console (Doc ID 2227147.1)

http://erpondb.blogspot.com/2015/11/ebs-r121-patching-for-jre-18.html

######################################################################################################

cd /interface/patches/CPU_2018/JRE

——–Backup of Webapps———————————

cd $COMMON_TOP

cp -rp webapps webapps_bkp_CPU_19SEP

——–Backup of Webapps———————————

==> Apply interoperability Patch 21624242:R12.TXK.B. (ALready Applied ) ******************>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>

1)Download 32 Bit Microsoft Version

unzip p27890728_180181_WINNT.zip

cp -p jre-8u181-windows-i586.exe $COMMON_TOP/webapps/oacore/util/jinitiator/j2se18181.exe

$FND_TOP/bin/txkSetPlugin.sh 18181

$FND_TOP/bin/txkSetPlugin.sh 18073 –Revert to old if any issues

Issues :

#########################################################################################################################

diff -y /ebsdb/apps/apps_st/appl/fnd/12.0.0/admin/template/orion_web_xml_1013.tmp /ebsdb/apps/apps_st/appl/fnd/12.0.0/admin/template/custom/orion_web_xml_1013.tmp_bkp_18SEP |grep ‘|’

add diff lines

[ VERSION CONFLICTS INFORMATION ]

Template shipped by oracle is having a version different than the template lying in custom directory.

Template shipped by Oracle : /ebsdb/apps/apps_st/appl/fnd/12.0.0/admin/template/orion_web_xml_1013.tmp(version: 120.69.12010000.17)

Custom template : /ebsdb/apps/apps_st/appl/fnd/12.0.0/admin/template/custom/orion_web_xml_1013.tmp(version: 120.69.12010000.10)

Please resolve the differences between the two templates or refer to Metalink Note 387859.1 for further details.

Issues :

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

lsinventory is failing with below error

##############################

LsInventory: OPatch Exception while accessing O2O

OPATCH_JAVA_ERROR : An exception of type “OPatchException” has occurred:

OPatch Exception: OUI found no such ORACLE_HOME set in the environment

Can not get details for given Oracle Home

An exception occurs

null

##############################

. $INST_TOP/ora/10.1.2/*.env;echo $ORACLE_HOME;

cd $ORACLE_HOME/oui/bin

./runInstaller -silent -attachHome ORACLE_HOME=/EBSDEV/tech_st/10.1.2 ORACLE_HOME_NAME=EBSDEV_1012_home

. $INST_TOP/ora/10.1.3/*.env;echo $ORACLE_HOME;

cd $ORACLE_HOME/oui/bin

./runInstaller -silent -attachHome ORACLE_HOME=/EBSDEV/tech_st/10.1.3 ORACLE_HOME_NAME=EBSDEV_1013_home

##############################

10.1.2 runInstaller attachHome failing with below error

.The Java RunTime Environment was not found at /tmp/OraInstall2018-09-16_12-18-52PM/jre/bin/java. Hence, the Oracle Universal Installer cannot be run.

##############################

Reason for the error

On investigation,I found the following solution to the problem

The oraparam.ini file is incorrect in $ORACLE_HOME/oui directory.

The line in the oraparm.ini file that was incorrect was:

appltst@of552:/ebsdb/apps/tech_st/10.1.2/oui $ cat oraparam.ini|grep -i JRE|grep -v ^#

JRE_LOCATION=/apps/prd/apps/tech_st/10.1.2/jdk/jre ****************************************************pointing to prod Path

JRE_MEMORY_OPTIONS=”-mx256m”

1. Back up the oraparm.ini file in $ORACLE_HOME/oui

2. Modify the oraparam.ini file:

vi oraparam.ini

JRE_LOCATION=../../jdk/jre

3. Run the universal installer from $ORACLE_HOME/oui/bin/runInstaller

TXKRUN.PL Deploying a New Forms

applmgr@of201:~ $ $FND_TOP/bin/txkrun.pl -script=CfgOC4JApp

*** ALL THE FOLLOWING FILES ARE REQUIRED FOR RESOLVING RUNTIME ERRORS

*** Log File = /EBSDEV/inst/apps/EBSDEV_of201/logs/appl/rgf/TXK/txkCfgOC4JApp_Wed_Sep_19_05_57_14_2018.log

Program : /EBSDEV/apps_st/appl/fnd/12.0.0/patch/115/bin/txkCfgOC4JApp.pl started @ Wed Sep 19 05:57:14 2018

*** Log File = /EBSDEV/inst/apps/EBSDEV_of201/logs/appl/rgf/TXK/txkCfgOC4JApp_Wed_Sep_19_05_57_14_2018.log

Enter Application name for re-deployment ? forms

Enter Oc4j Instance password for re-deployment ?

Run Autoconfig ? No

*****************************************************

Required values for starting OC4J instance “forms”:

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

s_formsstatus = enabled

s_forms_nprocs = 1 (value should be greater than 0)

Existing values from the context file:

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

s_formsstatus = enabled

s_forms_nprocs = 1

———————————————-

*** Values for context variables are VALID ***

———————————————-

*****************************************************

Stopping all OPMN processes.

OPMN stopped.

OPMN started.

Deplolying Application : “forms” onto OC4J instance: “forms”

Application deployed successfully.

Stopping and starting OC4J instances.

Started OC4J instances.

Binding webApp : “forms” with webmodule : “formsweb” for OC4J instance: “forms”

Web application bound successfully.

Stopping OPMN.

OPMN stopped.

Program : /EBSDEV/apps_st/appl/fnd/12.0.0/patch/115/bin/txkCfgOC4JApp.pl completed @ Wed Sep 19 05:57:54 2018

End of /EBSDEV/apps_st/appl/fnd/12.0.0/patch/115/bin/txkCfgOC4JApp.pl : No Errors encountered

applmgr@of201:~ $

AD Pacthes
Patch –  Desciption Pre

requisities

Post

requisities

Applied Patch –  Desciption Pre-Requisities

Not Appiled

Post

requisities

Not Appiled

28018146

12.1:CPU PATCH FOR JULY 2018

Yes NoRun ADGRANTS Yes 9239089

R12.AD.B.delta.3

NA NA
Yes 19559960

R12.FRM.B.delta.4

NA NA
No 23231676

R12.JTT.B.delta.4

NA 24840574

JSP COMPILATION FAILS AFTER APPLYING R12.JTT.B.DELTA.4

25110821

ERROR ON FLEXFIELDS AFTER APPLYING R12.JTT.B.DELTA.4

Yes 8919491

R12.ATG_PF.B.delta.3

NA NA
Yes 19273341

R12.BNE.B.delta.4

NA NA
No 22284589

R12.FWK.B.delta.5

NA 23200210

PERFORMANCE ISSUE WHILE SEARCHACCOUNT FLEXFIELD( SELECT FROM FND_FLEX_VALUES_VL)

12572001:

AFTER FRAMEWORK PATCH 11894708 JAVA ERROR ON CREATE CLAIM, CLAIM LINE, SYS PARAM (Not Required as we don’t have OZF)

Required

Actions

If Not On

Apr 2018 CPU

14108961

Only if Landed Cost Management is used

Check Below Applications are used or not with Funactional Teamand see if this are applied INL Inactive select

abbreviation

,codelevel

from

ad_trackable_entities

where

abbreviation in( ‘hr_pf’,’ibe’,’prc_pf’,’inl’,’ozf’ )

order by

abbreviation;

22465286

IF have applied Patch 21198991:R12.PRC_PF.B then apply Patch 22465286:R12.PON.B after applying this CPU patch.

PON Inactive select decode(nvl(a.APPLICATION_short_name,’Not Found’),

‘SQLAP’,’AP’,’SQLGL’,’GL’,’OFA’,’FA’,

‘Not Found’,’id ‘||to_char(fpi.application_id),

a.APPLICATION_short_name) apps,

decode(fpi.status,’I’,’Installed’,’S’,’Shared’,

‘N’,’Inactive’,fpi.status) status,

fpi.product_version,

nvl(fpi.patch_level,’– Not Available –‘) Patchset,

to_char(fpi.last_update_date,’dd-Mon-RRRR’) “Update Date”

from fnd_oracle_userid o, fnd_application a, fnd_product_installations fpi

where fpi.application_id = a.application_id(+)

and fpi.oracle_id = o.oracle_id(+)

and APPLICATION_short_name in( ‘HR’,’IBE’,’PRC’,’INL’,’OZF’ )

order by 1,2

25510561

If have applied R12.HR_PF.B.delta.9, Release 12.1 HRMS RUP9, apply Patch 25510561:R12.PAY.B after applying this CPU patch.

Current Version is HR PF  DELTA 3

PAY Shared

26267391

If have applied R12.IBE.B.Delta.4, apply Patch 26267391:R12.IBE.B after applying this CPU patch.

IBE Inactive
DB Patches:
28317232

COMBO OF OJVM COMPONENT 12.1.0.2.180717 DB PSU + DBPSU 12.1.0.2.180717

12.1.0.2 NA REVIEW With CORE DBA TEAM
Java JRE
27890728

Oracle JRE 8 Update 181

Apply only 32 bit patch

No -Pre Req

10.1.3.5 Patches
21845960CPUOCT2015 TRACKING BUG FOR APPLICATION SERVER 10.1.3.5 UNIX 10.1.3.5.0 NA If you encounter conflicts with Patch 19943587 and Patch 18025250, allow OPatch to roll it back. The fix for 19943587 and 18025250 is already included in the Oct 2015 CPU Patch 21845960.mv: cannot stat `.backup/.10011970/cabo’: No such file or directory

mv: cannot stat `cabo’: No such file or directory

This message can safely be ignored.

We can apply 32 bit

version in Linux X86-64

run the below command before applying patch

$ export OPATCH_PLATFORM_ID=46

19943587/18025250

Not appliedApply 21845960. directly

. $INST_TOP/ora/10.1.3/*.env;echo $ORACLE_HOME;$ORACLE_HOME/OPatch/opatch lsinventory|egrep ‘22123753|20683632|20426272|19943587|18025250|21845960’
22123753CPUOCT2015: MLR ON TOP OF 10.1.3.5.0 FOR BUGS 20034700 8939568 20492610 10.1.3.5.0 NA If you have previously installed Patch 20683632 or Patch 20426272, use OPatch to explicitly roll it back. Then, apply Patch 22123753.why this patch ?

Slow performance of HTML-based (self-service) applications on Oracle E-Business Suite Release 12.1.

20683632/20426272

Not appliedApply 22123753 directly

Only if TLS 1.2 Configured

27078378|27208670

10.1.3.5.0 NA to enable TLS 1.2 you should update the OpenSSL libraries by applying two patches – Patch 27078378 and Patch 27208670 27078378|27208670

Not appplied

. $INST_TOP/ora/10.1.3/*.env;echo $ORACLE_HOME;$ORACLE_HOME/OPatch/opatch lsinventory|egrep ‘27078378|27208670’
10.1.2 Patches
26825525MERGE REQUEST ON TOP OF 10.1.2.3.2PSU FOR BUGS 25768948 1770039 25990479 10.1.2.3 NA Only Available for Linux X86, not with 64 bit

we have to check with Oracle for applying

Pre-Reqs are applied.

14825718 /8551790OUI 10.1 8551790

. $INST_TOP/ora/10.1.2/*.env;echo $ORACLE_HOME;$ORACLE_HOME/OPatch/opatch lsinventory|egrep ‘14825718|8551790|26825525’. $INST_TOP/ora/10.1.2/*.env;echo $ORACLE_HOME;grep -i version= $ORACLE_HOME/oui/oraparam.ini
 Known Issue Categories, Details and Resolutions
UI issues (such as poorly rendered fonts, missing links, or buttons and fields out of place) in Oracle E-Business Suite Release 12.1.3. Refer to Solution A in Document 1348791.1, R12: Font and Links Have Changed After Patching.
Patch Type Pacth# Comments References
10.1.3 21845960
10.1.3 22123753
10.1.2 26825525 Forms having some issues while opening

to fix the issue,Re-deploy forms.ear file

(Given in Readme,but steps are not correct,refer comments)

Deploying a New EAR File in Oracle Applications Release 12 (Doc ID 397174.1)   –Change Password for oc4jadmin

TXKRUN.PL Deploying a New Forms .EAR File Error with OPMNCTL Start (Doc ID 1295791.1) — Prod patch opmn ctl

JRE Plugin 27890728 After applying JRE Plugin,Forms are not opening ,no errors.

Reverted the changes,after that forms are opening

Upgrading JRE Plugin with Oracle Applications R12 (MetaLink Note 393931.1)

#notes

TLS 1.2 Upgrade EBS R12.1.3

In Summary
Source Document: Enabling TLS in Oracle E-Business Suite Release 12.1 (Doc ID 376700.1)

Step 1 : Upgrade to Latest JDK (JDK_7_181_32bit_27411505) & apply 10.1.2 Patches ==> Completed Successfully
Step 2 : Apply the October 2015 CPU (10.1.3) –>21845960 ==> Completed Successfully
Step 3 : Apply the 10.1.3.5 OpenSSL patches. –> All Other 10.1.3 Patches==> Completed Successfully
Step 4 : Apply product-specific patches(ad patches) –Ad patches ==> Completed Successfully

Error Faced:
Only Error , is OPMN Services are timed-out.
ERROR : Timed out( 100000 ): Interrupted Exception - This is JDK Upgrade (10.1.2 Pathes) , this can be safely ignored

One patch needs OUI and needs to be enabled with unix team and using 1301320.1

Pacthes ==> adpacthes , 10.1.2,10.1.3
JDK ==> JDK Pacth Version & Details
Certificates ==> Certificates Required for TLS1.2

Sample Steps (Other)

Enabling TLS in Oracle E-Business Suite Release 12.1 (Doc ID 376700.1)

Using JDK 7.0 Latest Update with Oracle E-Business Suite Release 12.0 and 12.1 (Doc ID 1467892.1)

Apply patches 16545472, 17309237 (EBS patches – adpatch)

Install new JDK version on server

Create new JDK folder in $IAS_ORACLE_HOME/applutil

Copy new JDK folder (jdk1.7.0_181-i586) from installation directory to $IAS_ORACLE_HOME/applutil/jdk1.7.0_181-i586 .

in $IAS_ORACLE_HOME/applutil/ rename existing jdk directory (mv jdk jdk_old)
in $IAS_ORACLE_HOME/applutil/ rename new jdk1.7.0_181-i586 jdk directory (mv jdk1.7.0_181-i586 jdk)

Install Fonts

cp $FND_TOP/resource/ALBANYWT.ttf $IAS_ORACLE_HOME/appsutil/jdk/jre/lib/fonts
cp $FND_TOP/resource/ALBANWTJ.ttf $IAS_ORACLE_HOME/appsutil/jdk/jre/lib/fonts
cp $FND_TOP/resource/ALBANWTK.ttf $IAS_ORACLE_HOME/appsutil/jdk/jre/lib/fonts
cp $FND_TOP/resource/ALBANWTS.ttf $IAS_ORACLE_HOME/appsutil/jdk/jre/lib/fonts
cp $FND_TOP/resource/ALBANWTT.ttf $IAS_ORACLE_HOME/appsutil/jdk/jre/lib/fonts

apply patch 5659594 to 10.1.2 oracle home (opatch)

mv $ORACLE_HOME/jdk $ORACLE_HOME/jdk_old

In ORACLE_HOME:

cp -rp $IAS_ORACLE_HOME/appsutil/jdk .

apply patches (IN THIS ORDER) 16271876, 17907988, 17653437, 17645157, 16241466 to 10.1.2 home (opatch)

ignore conflict on 17653437

ignore conflict on 17645157

refer to 1569998.1 for make commands

Please create the below softlink:

cd $ORACLE_HOME/lib/stubs
ln -s libjvm-1.7-stub.so libjvm.so

cd $ORACLE_HOME/forms/lib
$ make -f ins_forms.mk sharedlib install

cd $ORACLE_HOME/reports/lib
$ make -f ins_reports.mk install

Run ADAdmin and select the Forms and Reports regeneration
1, 2 and 1,3

Enter the number of workers [X] :

take all defaults when asked

apply patches 27078378, 27208670 to 10.1.3 home (opatch)

27208670 could error due to no OUI 10.1 support

OPATCH_JAVA_ERROR=CheckConflict: OPatch cannot process overlay patches because of no OUI support. Please take latest OUI 10.1 patchset from “My Oracle Support” and try again.
Cannot check bug/file conflict and component prerequisite checks.

ERROR: OPatch failed during prerequisite check.

apply 6640838 using UltraVNC, refer to below note

How to patch OUI for installing overlay patches on top of Forms Bundle Patch – 9593176 (Doc ID 1301320.1)

source 10.1.3 home

./runInstaller -ignoreSysPrereqs

Once installed, then apply 27208670

apply patches 23645824, 22974534 (EBS patches – adpatch)

Punchout in Oracle iProcurement and Exchange Fails After Supplier Site Migrates From SSLv3 to TLS Protocol (with SSL Handshake SSLIOClosedOverrideGoodbyeKiss) (Doc ID 1937220.1)

apply patch 21473055 (EBS patch – adpatch)

source 10.1.3

/d01/oracle/SUPPORT1/apps/tech_st/10.1.3

Follow 376700.1 instructions for requesting a new certificate

copy files to $INST_TOP/certs/Apache: ca.crt, intermediate.crt, new.cnf, new.csr, server.crt, server.key

make $PATH and $LD_LIBRARY_PATH per 376700.1

export OPENSSL_CONF=$INST_TOP/certs/Apache/new.cnf

cat server.crt intermediate.crt ca.crt > opmn.crt

Copy files to custom directory and make changes as per 376700.1

cp -rp $FND_TOP/admin/template/opmn_xml_1013.tmp $FND_TOP/admin/template/custom/opmn_xml_1013.tmp

cp -rp $FND_TOP/admin/template/httpd_conf_1013.tmp $FND_TOP/admin/template/custom/httpd_conf_1013.tmp

cp -rp $FND_TOP/admin/template/ssl_conf_1013.tmp $FND_TOP/admin/template/custom/ssl_conf_1013.tmp

make file changes as per 5.2 Step 6 in 376700.1

Copy files to custom directory and make changes as per 376700.1

cp -rp $FND_TOP/admin/template/oc4j_properties_1013.tmp $FND_TOP/admin/template/custom/oc4j_properties_1013.tmp

cp -rp $FND_TOP/admin/template/oafm_oc4j_properties_1013.tmp $FND_TOP/admin/template/custom/oafm_oc4j_properties_1013.tmp

cp -rp $FND_TOP/admin/template/forms_oc4j_properties_1013.tmp $FND_TOP/admin/template/custom/forms_oc4j_properties_1013.tmp

make file changes as per 5.3 Step 1 in 376700.1

Run autoconfig and pray it all works

====================================================================
JDK_7_181_32bit & 10.1.2 Patches
For JDK 7 upgrade, follow the instructions in  (Doc ID 1467892.1)
====================================================================
27411505 --> JDK_7_181_32bit_27411505

1)12848228
2)16271876 -- Follow Read me
3)17907988 -- Follow Read me
4)17653437-- Follow Read me
5)17645157-- Follow Read me
6)16241466--- Follow Read me

====================================================================
10.1.3 Patches
Refer DOc: 376700.1 ,Step 5
====================================================================
1) 21845960-CPUOCT2015--> -- Follow Read me
2) 27078378
3) 22322938-- Support for Oracle Workflow
4) 6640838 -->OUI 10.1 : apply 6640838 using UltraVNC, refer to below note 1301320.1
5) 27208670
====================================================================
AD Pacthes
Refer DOc: 376700.1 ,Step 6
====================================================================

16545472
17309237
23645824
22974534 --NLS
27881758 --NLS
22724663 --NLS

TLS_JDK_Upgrade_7u181_32bit_workflog

http://123.srinalla.com:8011/OA_HTML/AppsLogin

apps4ebsdb
manager

/interface/patches/TLS_1.2_Patches
####################################################################################################################################################

Take backup of 10.1.3 and 10.1.2 Homes

appltst@of223:/ebsdb/tech_st $
cp -pr 10.1.3 10.1.3_bkp_after_jdk_upgrade &
cp -pr 10.1.2 10.1.2_bkp_after_jdk_upgrade &
####################################################################################################################################################
Apply 10.1.3 Oracle Home using Opatch.

====================================================================
10.1.3 Patches
Refer DOc: 376700.1 ,Step 5
====================================================================
1) 21845960-CPUOCT2015–> — Follow Read me
2) 27078378
3) 22322938– Support for Oracle Workflow
4) 6640838 –>OUI 10.1 : apply 6640838 using UltraVNC, refer to below note 1301320.1
5) 27208670

====================================================================
AD Pacthes
Refer DOc: 376700.1 ,Step 6
====================================================================

16545472
17309237
23645824
22974534 –NLS
27881758 –NLS
22724663 –NLS

——————————————————————————————————————>
1) Apply 21845960———————–>

echo $OPATCH_PLATFORM_ID
unset OPATCH_PLATFORM_ID

. $INST_TOP/ora/10.1.3/*.env;echo $ORACLE_HOME;
$ORACLE_HOME/OPatch/opatch lsinventory|grep 21845960
cd /interface/patches/TLS_1.2_Patches/10.1.3/21845960
export OPATCH_PLATFORM_ID=46
$ORACLE_HOME/OPatch/opatch napply
unset OPATCH_PLATFORM_ID

——————-WORKLOG…………………………
Applying patch 9273888…

Patching copy files…

Inventory is good and does not have any dangling patches.

Updating inventory…

Verifying patch…
Backing up comps.xml …

OPatch succeeded.

OPatch succeeded.
appltst@of223:/interface/patches/TLS_1.2_Patches/10.1.3/21845960 $

appltst@of223:/interface/patches/TLS_1.2_Patches/10.1.3/21845960 $ unset OPATCH_PLATFORM_ID
appltst@of223:/interface/patches/TLS_1.2_Patches/10.1.3/21845960 $

——————————————————————————————————————>
2) 27078378

. $INST_TOP/ora/10.1.3/*.env;echo $ORACLE_HOME;
$ORACLE_HOME/OPatch/opatch lsinventory|grep 27078378
cd /interface/patches/TLS_1.2_Patches/10.1.3/27078378
$ORACLE_HOME/OPatch/opatch apply

——————-WORKLOG…………………………——————-WORKLOG…………………………
OPatch succeeded.
appltst@of223:/interface/patches/TLS_1.2_Patches/10.1.3/27078378 $ $ORACLE_HOME/OPatch/opatch lsinventory|grep 27078378

1) Patch 27078378 applied on Fri Dec 07 01:04:10 EST 2018
[ Bug fixes: 22447165 25859264 27078378 24483815 ]
appltst@of223:/interface/patches/TLS_1.2_Patches/10.1.3/27078378 $

——————————————————————————————————————>
3) 22322938

. $INST_TOP/ora/10.1.3/*.env;echo $ORACLE_HOME;
$ORACLE_HOME/OPatch/opatch lsinventory|grep 22322938
cd /interface/patches/TLS_1.2_Patches/10.1.3/22322938
$ORACLE_HOME/OPatch/opatch apply

——————-WORKLOG…………………………——————-WORKLOG…………………………

OPatch succeeded.
appltst@of223:/interface/patches/TLS_1.2_Patches/10.1.3/22322938 $ $ORACLE_HOME/OPatch/opatch lsinventory|grep 22322938
1) Patch 22322938 applied on Fri Dec 07 01:30:56 EST 2018
[ Bug fixes: 22322938 ]

——————————————————————————————————————>
4) 6640838

####################################################################################################################################################
OUI Pacth 6880880 ——————————–> GUI Setiings

Invoking fuser to check for active processes.

OPATCH_JAVA_ERROR=CheckConflict: OPatch cannot process overlay patches because of no OUI support. Please take latest OUI 10.1 patchset from “My Oracle Support” and try again.
Cannot check bug/file conflict and component prerequisite checks.
##########################################################
MobaXterm GUI Settings——————————————————————————————–>
[srinalla@of223 ~]$ xauth list
123.srinalla.com/unix:10 MIT-MAGIC-COOKIE-1 6bb0f2dd724792d1fa72abbb87f3c8a6
appltst@of223:~ $ xauth add 123.srinalla.com/unix:10 MIT-MAGIC-COOKIE-1 6bb0f2dd724792d1fa72abbb87f3c8a6
appltst@of223:~ $ xclock
——————————————————————————————–>
##########################################################
Download 32bit 10106 Version=========> p6640838_10106_LINUX.zip
. $INST_TOP/ora/10.1.3/*.env;echo $ORACLE_HOME;
mv $ORACLE_HOME/OPatch $ORACLE_HOME/OPatch.pre_6640838
unzip p6640838_10106_LINUX.zip
appltst@of223:/interface/patches/TLS_1.2_Patches $ cd cd/Disk1/install/
appltst@of223:/interface/patches/TLS_1.2_Patches/cd/Disk1/install $ chmod +x *
appltst@of223:/interface/patches/TLS_1.2_Patches/cd/Disk1/install $ ./runInstaller -ignoreSysPrereqs

Follow (Doc ID 1301320.1) –> for GUI steps

appltst@of223:/interface/patches/TLS_1.2_Patches/10.1.3/27208670 $ opatch version

Oracle Interim Patch Installer version 1.0.0.0.62
Copyright (c) 2009 Oracle Corporation. All Rights Reserved..

Oracle recommends you to use the latest OPatch version
and read the OPatch documentation available in the OPatch/docs
directory for usage. For information about the latest OPatch and
other support-related issues, refer to document ID 293369.1
available on My Oracle Support (https://myoraclesupport.oracle.com)

OPatch Version: 1.0.0.0.62
####################################################################################################################################################

5) 27208670

Follow Read me

. $INST_TOP/ora/10.1.3/*.env;echo $ORACLE_HOME;
$ORACLE_HOME/OPatch/opatch lsinventory|grep 27208670
cd /interface/patches/TLS_1.2_Patches/10.1.3/27208670
$ORACLE_HOME/OPatch/opatch apply

–> First time , we had error & rollbacked the patch as per
–> And pacth went fine

—————> Refer Error Details on below of Document

——————-WORKLOG…………………………——————-WORKLOG…………………………

Verifying patch…
Backing up comps.xml …

OPatch succeeded.
appltst@of223:/interface/patches/TLS_1.2_Patches/10.1.3/27208670 $ $ORACLE_HOME/OPatch/opatch lsinventory|grep 27208670
1) Patch 27208670 applied on Tue Dec 11 10:51:12 EST 2018
[ Bug fixes: 27208670 22458773 24484104 ]

####################################################################################################################################################
Error Details for 27208670
####################################################################################################################################################

appltst@of223:~ $ $ORACLE_HOME/OPatch/opatch lsinventory|grep 27208670

appltst@of223:~ $ cd /interface/patches/TLS_1.2_Patches/10.1.3/27208670/
appltst@of223:/interface/patches/TLS_1.2_Patches/10.1.3/27208670 $ $ORACLE_HOME/OPatch/opatch apply

Oracle Interim Patch Installer version 1.0.0.0.62
Copyright (c) 2009 Oracle Corporation. All Rights Reserved..

Oracle recommends you to use the latest OPatch version
and read the OPatch documentation available in the OPatch/docs
directory for usage. For information about the latest OPatch and
other support-related issues, refer to document ID 293369.1
available on My Oracle Support (https://myoraclesupport.oracle.com)

Oracle Home : /ebsdb/tech_st/10.1.3
Oracle Home Inventory : /ebsdb/tech_st/10.1.3/inventory
Central Inventory : /im/finupg/oraInventory
from : /etc/oraInst.loc
OUI location : /ebsdb/tech_st/10.1.3/oui
OUI shared library : /ebsdb/tech_st/10.1.3/oui/lib/linux/liboraInstaller.so
Java location : /ebsdb/tech_st/10.1.3/jre/1.4.2/bin/java
Log file location : /ebsdb/tech_st/10.1.3/.patch_storage//*.log

Creating log file “/ebsdb/tech_st/10.1.3/.patch_storage/27208670/Apply_27208670_12-11-2018_10-43-00.log”

Invoking fuser to check for active processes.

Patch “27208670” overlays ” 21845942 “. Conflict check between them is skipped.

Backing up comps.xml …

OPatch detected non-cluster Oracle Home from the inventory and will patch the local system only.

Please shut down Oracle instances running out of this ORACLE_HOME
(Oracle Home = /ebsdb/tech_st/10.1.3)
Is this system ready for updating?
Please respond Y|N >
Y
Applying patch 27208670…

Patching copy files…

OPatch encounters the following issues during file patching:
The following files had problems with being patched:
1. /ebsdb/tech_st/10.1.3/opmn/bin/opmn
[ Couldn’t copy /interface/patches/TLS_1.2_Patches/10.1.3/27208670/files/opmn/bin/opmn to /ebsdb/tech_st/10.1.3/opmn/bin/opmn from /interface/patches/TLS_1.2_Patches/10.1.3/27208670. ]

Replying ‘Y’ will terminate the patch installation immediately. It WILL NOT restore any updates that have been performed to this point. It WILL NOT update the inventory.
Replying ‘N’ will update the inventory showing the patch has been applied.
NOTE: After replying either ‘Y’ or ‘N’ it is critical to review:
My Oracle Support Note 312767.1 How to rollback a failed Interim patch installation.
Do you want to STOP?
Please respond Y|N >
N

Inventory is good and does not have any dangling patches.

Updating inventory…

Verifying patch…
Verifying that patch ID is in Oracle Home inventory.
Verifying copy files.

Comparing “/interface/patches/TLS_1.2_Patches/10.1.3/27208670/files/opmn/bin/opmn” and “/ebsdb/tech_st/10.1.3/opmn/bin/opmn”
Source file name is : /interface/patches/TLS_1.2_Patches/10.1.3/27208670/files/opmn/bin/opmn, size is : 501295
Destination file name(from OracleHome) is : /ebsdb/tech_st/10.1.3/opmn/bin/opmn, size is : 509724
Copy failed: failed to update “/ebsdb/tech_st/10.1.3/opmn/bin/opmn” with updated “/interface/patches/TLS_1.2_Patches/10.1.3/27208670/files/opmn/bin/opmn”

Comparing “/interface/patches/TLS_1.2_Patches/10.1.3/27208670/files/opmn/lib/libopmnoraclessl.so” and “/ebsdb/tech_st/10.1.3/opmn/lib/libopmnoraclessl.so”

Comparing “/interface/patches/TLS_1.2_Patches/10.1.3/27208670/files/opmn/lib/libopmnopenssl.so” and “/ebsdb/tech_st/10.1.3/opmn/lib/libopmnopenssl.so”

Comparing “/interface/patches/TLS_1.2_Patches/10.1.3/27208670/files/opmn/lib/libmodapi.so” and “/ebsdb/tech_st/10.1.3/opmn/lib/libmodapi.so”
There are 1 issues copying files to Oracle Home.
FILE PROBLEM: some files are not patched.
OPATCH_JAVA_ERROR: Patch was not successfully applied.
Verification of the patch failed.

ERROR: OPatch failed as verification of the patch failed.
appltst@of223:/interface/patches/TLS_1.2_Patches/10.1.3/27208670 $ opatch version

Oracle Interim Patch Installer version 1.0.0.0.62
Copyright (c) 2009 Oracle Corporation. All Rights Reserved..

Oracle recommends you to use the latest OPatch version
and read the OPatch documentation available in the OPatch/docs
directory for usage. For information about the latest OPatch and
other support-related issues, refer to document ID 293369.1
available on My Oracle Support (https://myoraclesupport.oracle.com)

OPatch Version: 1.0.0.0.62

appltst@of223:/interface/patches/TLS_1.2_Patches/10.1.3/27208670 $ $ORACLE_HOME/OPatch/opatch lsinventory|grep 27208670
1) Patch 27208670 applied on Tue Dec 11 10:48:26 EST 2018
[ Bug fixes: 27208670 22458773 24484104 ]

appltst@of223:/interface/patches/TLS_1.2_Patches/10.1.3/27208670 $ opatch rollback -id 27208670

Oracle Interim Patch Installer version 1.0.0.0.62
Copyright (c) 2009 Oracle Corporation. All Rights Reserved..

Oracle recommends you to use the latest OPatch version
and read the OPatch documentation available in the OPatch/docs
directory for usage. For information about the latest OPatch and
other support-related issues, refer to document ID 293369.1
available on My Oracle Support (https://myoraclesupport.oracle.com)

Oracle Home : /ebsdb/tech_st/10.1.3
Oracle Home Inventory : /ebsdb/tech_st/10.1.3/inventory
Central Inventory : /im/finupg/oraInventory
from : /etc/oraInst.loc
OUI location : /ebsdb/tech_st/10.1.3/oui
OUI shared library : /ebsdb/tech_st/10.1.3/oui/lib/linux/liboraInstaller.so
Java location : /ebsdb/tech_st/10.1.3/jre/1.4.2/bin/java
Log file location : /ebsdb/tech_st/10.1.3/.patch_storage//*.log

Creating log file “/ebsdb/tech_st/10.1.3/.patch_storage/27208670/RollBack_27208670_12-11-2018_10-50-20.log”

Invoking fuser to check for active processes.

Backing up comps.xml …

OPatch detected non-cluster Oracle Home from the inventory and will patch the local system only.

Please shut down Oracle instances running out of this ORACLE_HOME
(Oracle Home = /ebsdb/tech_st/10.1.3)
Is this system ready for updating?
Please respond Y|N >
Y
Removing patch 27208670…

Restoring copied files…

Updating inventory…
Backing up comps.xml …

Inventory is good and does not have any dangling patches.

OPatch succeeded.
appltst@of223:/interface/patches/TLS_1.2_Patches/10.1.3/27208670 $
appltst@of223:/interface/patches/TLS_1.2_Patches/10.1.3/27208670 $
appltst@of223:/interface/patches/TLS_1.2_Patches/10.1.3/27208670 $ $ORACLE_HOME/OPatch/opatch lsinventory|grep 27208670

appltst@of223:/interface/patches/TLS_1.2_Patches/10.1.3/27208670 $ $ORACLE_HOME/OPatch/opatch apply

Oracle Interim Patch Installer version 1.0.0.0.62
Copyright (c) 2009 Oracle Corporation. All Rights Reserved..

Oracle recommends you to use the latest OPatch version
and read the OPatch documentation available in the OPatch/docs
directory for usage. For information about the latest OPatch and
other support-related issues, refer to document ID 293369.1
available on My Oracle Support (https://myoraclesupport.oracle.com)

Oracle Home : /ebsdb/tech_st/10.1.3
Oracle Home Inventory : /ebsdb/tech_st/10.1.3/inventory
Central Inventory : /im/finupg/oraInventory
from : /etc/oraInst.loc
OUI location : /ebsdb/tech_st/10.1.3/oui
OUI shared library : /ebsdb/tech_st/10.1.3/oui/lib/linux/liboraInstaller.so
Java location : /ebsdb/tech_st/10.1.3/jre/1.4.2/bin/java
Log file location : /ebsdb/tech_st/10.1.3/.patch_storage//*.log

Creating log file “/ebsdb/tech_st/10.1.3/.patch_storage/27208670/Apply_27208670_12-11-2018_10-50-53.log”

Invoking fuser to check for active processes.

Patch “27208670” overlays ” 21845942 “. Conflict check between them is skipped.

Backing up comps.xml …

OPatch detected non-cluster Oracle Home from the inventory and will patch the local system only.

Please shut down Oracle instances running out of this ORACLE_HOME
(Oracle Home = /ebsdb/tech_st/10.1.3)
Is this system ready for updating?
Please respond Y|N >
Y
Applying patch 27208670…

Patching copy files…

Inventory is good and does not have any dangling patches.

Updating inventory…

Verifying patch…
Backing up comps.xml …

OPatch succeeded.
appltst@of223:/interface/patches/TLS_1.2_Patches/10.1.3/27208670 $ $ORACLE_HOME/OPatch/opatch lsinventory|grep 27208670
1) Patch 27208670 applied on Tue Dec 11 10:51:12 EST 2018
[ Bug fixes: 27208670 22458773 24484104 ]