Category Archives: MyWork

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

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/

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