Dear Readers, Trust you are doing well. In my last post, I foucssed on overview of RAT testing process. In this post, I’ll delve into the process of capturing RAT workloads. This post will provide insights into key considerations, best practices, implementation strategies, and essential MOS notes for RAT capture. WHAT IS CAPTURE? To initiate Database Replay, the initial step involves capturing a production workload. This process entails recording all requests originating from external clients directed towards the Oracle Database. Upon enabling workload capture, the Oracle Database meticulously ..read more

Dear Readers, Trust you are doing well. Today I am going to write about interesting issue I faced recently on one of critical 3 node RAC DB, wherein one of DB instance crashed & while it was rejoining cluster another instance crashed. This situation created a lot of panic in customer’s environment as 2 instances were crashed one after another during peak hours. Once all isntance were back up and running, we started investigating from available diagnostic data. While we had complete evidence of instance 1 crashed due to interconnect communication issue, symptoms for second instance failure ..read more

As the clock strikes midnight, may you find new reasons to smile and new reasons to love. Wishing you a New Year filled with joy, laughter, and endless possibilities. May 2024 be your best year yet! Photo by Jill Wellington: https://www.pexels.com/photo/happy-new-year-text-3334355 ..read more

Dear Readers, Trust you are doing well. I am writing this post to share you sample shell script code which will help to remove all files under particular folder in quick time. This requirement normally comes when, we have to remove huge number of old files under folders which have not been housekeeped for quite some time. I had a urgent request to remove *.aud files from one of UAT server which was not housekeeped in 2 years. Below is the sample shell script I used to resolve this issue. This script you can put it in any folder and requires complete path of directory to be housekeeped as a inp ..read more

Dear Readers, Trust you are doing well. As you already aware, object statistics play a very important role in optimizer in choosing optimal execution plan for any query. In this post, I will be focussing on commands that we use with respect to in stats gathering. This post can be considered as a one stop reference for any application objects statistics gathering stuff. Stats gathering Table level: exec dbms_stats.gather_table_stats(‘SCOTT’, ‘TAB1’); exec dbms_stats.gather_table_stats(‘SCOTT’, ‘TAB1’, estimate_percent => 15); exec dbms_stats.gather_table_stats(‘SCOTT’, ‘TAB1’, estimate_perc ..read more

Dear Readers, Trust you are doing well!!! In this post, I will discussing about critical issue, I faced recently wherein RMAN compressed backup started taking significant time post changing compatible parameter from 12.1.0.2 to 19.0.0. Normally RMAN L0 compressed backup of DB size 70+ TB used to take approx 10 hours to complete. Post parameter change, it started taking around 24+ hours which highly unacceptable. There were no changes in any RMAN configuration, DB parameter & compression algorithm etc. No specific warning/errors were seen in alertlog or traces while running backup. Even tak ..read more

Dear Readers, Trust you are doing well!!! In this post, I will discussing about an interesting issue I faced recently on newly installed 19c 4 node RAC cluster, wherein DB startup using command “srvctl startup database” was taking significant time. Each DB startup took around 15-20 minutes, sometimes more than 30 minutes as well. Interestingly it worked well when individual instances were started using command “srvctl startup instance” sequentially. From initial analysis, it looked like issue with cluster interconnect, as during DB startup DBW* process keep blocking “mount” or “open” operation ..read more

Dear Readers, Trust you are doing well!!! In this post I will be providing you shell/bash script to create dynamic DDL script for all non-default Oracle schemas/users in Oracle DB.  This is very helpful when we need to export/import multiple schemas from source to empty target database. Script has been successfully tested in solaris environment. You may need to edit it to work of specific os platforms. Script usage:   sh user_ddl.sh #!/bin/bash # Author: Adityanath Dewoolkar # Date: 16 JAN 2023 # Description: DDL for all non default users in database # Version: v1 - initial # synta ..read more

Dear Readers, Trust you are doing well!!! Today I am going to write about interesting feature being introduced in Oracle version 12.2 & widely used in 19c. Multi-Instance Redo Apply(MIRA) Basically Oracle has different ways to apply redo on standby site. SIRA: Single Instance Redo Apply ==> Wherein MRP runs only on one of the instance on Standby RAC/Single node database. MIRA: Multi Instance Redo Apply ==> Wherein MRP runs only on more than one instance on Standby RAC database. Multi-Instance Redo Apply (MIRA) significantly improves scalability/performance of redo apply for Oracle RA ..read more

Dear Readers, Trust you are doing well, Today I am writing this post related to recent issue which we faced on one of the critical database post switchover to standby site. When I was checking AWR comparison reports to do some sanity checks, I found write IO statistics were increase significantly. Some of these number looks unreal as there was no change in application transaction volume.  One more thing we noticed post switchover is that we had intermittent DB session spikes for wait event “Library cache load lock” as due to some application server misconfiguration logons per seconds were ..read more