Abdul Hafeez Kalsekar -- Oracle database tuning case studies

  There are multiple ways to apply patch 1) Using Console  2) Using dbcli  3) For exacloud we can use dbaascli   or  exadbcpatchmulti   In this Blog we will mainly discuss about applying patch using console and dbcli . This was tested by my friend i still need to test it .  Using OCI  Console : The one-off patches (now they are call interim patches) can be applied via the Console, API or even manually. To apply an interim patch manually, you can use the Opatch utility. The detailed steps are provided in the  Applying one-off patches on Oracle Database 21c  documentation. The patches can be applied in any order. Here is how simple and easy it is: 1. For the database on which you want to apply the patches, just click its name to display details and under  Resources , click  Updates : 2. Click on “ Apply a one-off patch “: 3. Then, in the  Apply one-off patch  dialog, enter the patch numbers. Use a comma-separa...

  There are three methods to patching an EXA-CC.  Each of these methods can be initiated from the cloud console or from the back-end Exadata compute nodes. Out of Place Patching  In-Place Patching  One-off Patching Prechecks :  1) Check exiting Patch versions :  for name in `olsnodes`; do ssh $name -c hostname; ssh $name /u01/app/19.0.0.0/grid/OPatch/opatch lspatches; done 2) Take neccessary backups    1) Check and Update Cloud Tooling [oracle@host1-db2 ~]$ sudo dbaascli patch tools list dbaascli admin updateStack --version LATEST All Nodes have the same tools version 2) Listing Available Software Image and Versions for Database and Grid Infrastructure sudo dbaascli cswlib showImages If the Image is not available in the list use the command below to download, although it will be downloaded automatically during patch process. [oracle@host1-db1 ~]$ sudo dbaascli cswlib download --ohImageType db --imageTag 19.09.0.0.0 3)  Apply the Patch on Grid...

   We usually face situation where we need to encrypt password for shell scripts .  Recently we faced this  requirement and below is how we added encrypted password in shell  script   We need to retain  file containing hexadecimal value  .   in our case it was  /dbmonitoring/clone/clone.conf  ################ Encrypt Phase ################# cat /tmp/pass.lst Pass#Fri_09_24_2021 echo ${pass} > /tmp/pass.lst hex=`perl -e 'print unpack "H*",\`cat /tmp/pass.lst\`'`    echo $hex 50617373234672695f30395f32345f323032315f74617267657a echo $hex > /dbmonitoring/clone/clone.conf   cat /dbmonitoring/clone/clone.conf 50617373234672695f30395f32345f323032315f74617267657a ################ Decrypt Phase ################# decrypt=`perl -e 'print pack "H*",\`cat /dbmonitoring/clone/clone.conf\`'` echo $decrypt Pass#Fri_09_24_2021 ▒ Password=${decrypt:0:-2} echo $Password Pass#Fri_09_24_2021 sqlplus sys/${Password}@CLONE1...

   We  recently  had requirement to write script to Clone Rac database on same server with different database name . Below is script I prepared.   In  script i am resetting source database password on each execution . If we don't want to change password  we can keep same password and encrypt password  in script . Below Blog mentions  how we can encrypt password/. https://abdul-hafeez-kalsekar.blogspot.com/2021/10/oracle-password-encrypt-for-shell.html Reference :  STEP BY STEP RMAN DUPLICATE Database From RAC ASM To RAC ASM (Doc ID 1913937.1)    ########################################################## #Script clone_auto.sh to clone  database on same server  #Author: Abdul  Hafeez Kalsekar                          # #Date  : 29th August 2021                          ...

  _FIX_CONTROL is a special hidden dynamic parameter introduced in 10.2.0.2 which can be used to enable or disable certain bug fixes. This is typically used in the optimizer area to allow specific fixes to be turned on or off as some fixes can have a significant  effect on execution plans. The value parameter set to 1 means bug fix is activated  When you upgrade oracle database version, you can be face  a problem of CBO that changes its behavior.   To enable: "_fix_control"='Bugno:ON'    (OR)   "_fix_control"="Bugno:1" To disable: "_fix_control"='Bugno:OFF'  (OR)   "_fix_control"="Bugno:0" Eg alter system set "_fix_control"='27268249:0';   --> Disable  For _FIX_CONTROL to work, several conditions must be met: 1) The patch that is referenced must have the option to use _FIX_CONTROL.  Using _FIX_CONTROL can't be used to backout any patch.  The patch (usually an Optimizer patch)  has to be...

  From Oracle 12.1.0.2 Start , have access to asmfd To replace udev Under the rules asm Disk device binding , At the same time, he also has the ability to filter illegal IO Characteristics of operation . Check that the operating system version supports ASMFD, You can use the following code ： acfsdriverstate -orahome $ORACLE_HOME supported   Step  1> Stop Crs Step 2> Configure AFD (ASM Filter Driver)   asmcmd afd_configure Step 3> Configure Disk Discovery for AFD Modify the below mentioned files as shown  cat /etc/afd.conf afd_diskstring='/dev/xvd*' afd_filtering=enable  cat /etc/oracleafd.conf afd_diskstring='/dev/xvd*' afd_filtering=enable At this point AFD is configured you can verify the status as below   asmcmd afd_state ASMCMD-9526: The AFD state is 'LOADED' and filtering is 'ENABLED' on host 'wdtest05' I would recommend to restart acfs before you proceed  acfsload stop  acfsload start  lsmod | grep acfs Step5> Label the...

  Most of time we face performance issues , we miss to  check if there  is rollback ongoing . However we come across many situations where rollback is ongoing which impacts database performance . We can speed up and slowdown rollback seeing business hours .  If Undo tablespace is used up and rollback is slow , we can add new undo tablespace and change default undo tablespace  If smon recovery is causing performance issues we may opt to disable smon recovery temporarily and re-enable back after business hours .  Similarly we can speed up and reduce speed of smon recovery  The message ‘ Waiting for SMON to disable tx recovery’  will be posted in the alert log as well. After killing the shadow process or aborting the database the v$transaction entry is lost, so you cannot estimate by examining v$transaction.used_ublk how the rollback procedure proceeds. How to check Smon recovery :  set linesize 100 alter session set NLS_DATE_FORMAT='DD-MON-YYYY...

   Oracle  Orion - ORacle I/O Numbers Since Oracle 11g, the Oracle ORION package has bee shipped within the Oracle Database and Grid Infrastructure binaries $ORACLE_HOME/bin/orion and requires no downloads and only minimal set-up, so is really easy to be get started quickly. NOTE: orion requires you to provide a full path e.g. $ORACLE_HOME/bin/orion , if you try to call without providing a full path orion will return an ORA-56727 error. Setting-Up Orion Now we have prepared and presented our storage we can configure Orion, by default Orion looks for orion.lun, you can also use alternative configuration files by using the testname switch. (Example bellow) [oracle@z-oracle orion_test]$ cat orion.lun  /dev/mapper/orion-vol1 /dev/mapper/orion-vol2 /dev/mapper/orion-vol3 /dev/mapper/orion-vol4 /dev/mapper/orion-vol5 Orion support 5 types of workload tests Simple, Normal, Advanced, DSS and OLTP. (example output from orion -help    simple   - Tests rando...

   Oracle ASM disk scrubbing improves availability and reliability by searching for data that may be less likely to be read. Disk scrubbing checks logical data corruptions and repairs them automatically in normal and high redundancy disks groups. The scrubbing process repairs logical corruptions using the mirror disks. Disk scrubbing can be combined with disk group rebalancing to reduce I/O resources. The disk scrubbing process has minimal impact to the regular I/O in production systems. You can perform scrubbing on a disk group, a specified disk, or a specified file of a disk group with the ALTER DISKGROUP SQL statement. For example, the following SQL statements show various options used when running the ALTER DISKGROUP disk_group SCRUB SQL statement. Exadata Disk Scrubbing A subtler Exadata Maintenance job is the bi-weekly Disk Scrub. This job does not appear in the CellCLI alert history. It only appears in the $CELLTRACE/alert.log. Disk Scrubbing is designed to periodically...