Abdul Hafeez Kalsekar -- Oracle database tuning case studies

  Recently after  migrating to  19c  , Application team started complaining that sequences  value are not properly calculated .  It was later found that  it was due to  Dynamic sequence cache  introduced in 19c .  This issue is supposed to be fixed in 19.13  If the nextval value of the sequence is frequently called in the application, there may be performance  problems.   The default sequence cache is usually 20, I still recommend configuring cache 200 to start when creating. The feature is enabled by default and requires no additional setup by a DBA or end user. This feature resizes sequence caches dynamically based on the rate of consumption of sequence numbers. As a result sequence cache sizes can grow well beyond the configured DDL cache size. If a sequence is used across nodes in a RAC setting, this can result in large gaps in sequence numbers seen across instances. If you see large gaps in sequence numbers ...

   Pre-Checks  --> Confirm DGMGRL status is ok . And no Lag  DGMGRL> show configuration DGMGRL> show database standby_sb2 DGMGRL> validate database  verbose standby_sb2 select database_role  , switchover_status  from v$database;  ( on booth primary and  standby )  select INST_ID , DEST_ID , max(SEQUENCE#) from gv$archived_log group by INST_ID , DEST_ID ; ALTER DATABASE SWITCHOVER TO Standby VERIFY;  sudo dbaascli dataguard status --dbname DBNAME --details yes   ( Put Exact db name as in crs )  Logfile will be under /var/opt/oracle/log/dbaastools_launcher/ Below are pre check based on known issues faced : ISSUE – Parameter missing in .INI FIX – Add parameters ( dg_observer=no and dg_observer_host=no) are added in file /var/opt/oracle/creg/<dbname>.ini ISSUE – Password in Wallet is not sync between PRIMARY and STANDBY FIX – Copy the cwallet.sso with correct password to STANDBY node. Switchover...

  Had  to  write this  Blog  Considering Lot of Pga issues we  been facing   after migration from 12c to 19c .  This is  because   MGA is also  Part of   Pga  now .    To get more information on  Mga please refer  2638904.1  In Oracle release 12.1:    Pga  setting was  the greater of the following: 1. 2 GB  2. 200% of PGA_AGGREGATE_TARGET  3. (Value of PROCESSES initialization parameter setting) * 3 MB It will not exceed 120% of the physical memory size minus the total SGA size. In Oracle release 12.2:   Pga  setting was as per below  * If MEMORY_TARGET is set, then PGA_AGGREGATE_LIMIT defaults to the MEMORY_MAX_TARGET value. * If MEMORY_TARGET is not set, then PGA_AGGREGATE_LIMIT defaults to 200% of PGA_AGGREGATE_TARGET. * If MEMORY_TARGET is not set, and PGA_AGGREGATE_TARGET is explicitly set to 0, then the value of PGA_AGGREGA...

  Resource and performance capacity of the servers is one side of the puzzle. Equally important is to size/estimate the database for storage and the data growth. This would mean the database, the database objects, and the underlying storage subsystem would also have to be sized for today and tomorrow. Oracle provides few packages and procedures that help determine the size of objects and indexes based on the   estimated growth size. Even further, using the DBMS_SPACE.OBJECT_GROWTH_TREND function, a growth pattern for existing tables can be obtained. The following query will list the object growth trend for an object; the data for the trend listed is gathered from Automatic Workload Repository (AWR). The growth trends for two of the tables are listed following. The OBJECT_GROWTH_TREND function returns four values: •  TIMEPOINT—Is a time stamp value indicating the time of the recording/reporting. •  SPACE_USAGE—Lists the amount of space used by the object at t...

     Most of time after migration  of database to new server we observe performance degradation . Usually we dont have historical performance data before migration which land us is big mess. To overcome this , its very important to  take awr dump from existing database before migration .  Extract AWR data For Extract the AWR data from source database run the awrextr.sql script which extracts AWR data for a range of snapshots from the database into a Data Pump export file.   1. Run the following script for extract AWR: @?/rdbms/admin/awrextr.sql; 2. Script ask for select DBID Enter value for dbid: 3. Enter the number of days backup export: Enter value for num_days: 2 4. It will list the 2 days snapshot in AWR. Choose the begin and end snapshot for export: Enter value for begin_snap: 76 Begin Snapshot Id specified: 76 Enter value for end_snap: 86 End Snapshot Id specified: 86 5. List the Directory present in Database, Choose the directory location and du...

   What's New :  Automatic SQL Plan Management is enabled by default in Oracle 19c New SQL plan baselines will be created automatically By default, the SPM Evolve Advisor runs daily in the maintenance window • In 19c it can be configured to also run outside of the maintenance window • To control it, the DBMS_SPM.CONFIGURE procedure supports a new parameter • AUTO_SPM_EVOLVE_TASK (OFF, ON, AUTO) • In 19c AUTO is equivalent to ON • When enabled, it runs every hour for no longer than 30 minutes How to check : column parameter_value format a45 column parameter_name format a25 set pages 200 SELECT PARAMETER_NAME, PARAMETER_VALUE   FROM   DBA_ADVISOR_PARAMETERS   WHERE  TASK_NAME = 'SYS_AUTO_SPM_EVOLVE_TASK'     AND PARAMETER_VALUE <> 'UNUSED'   ORDER BY 1; SELECT sql_handle, plan_name, enabled, accepted FROM dba_sql_plan_baselines; The new default AUTO for ALTERNATE_PLAN_BASELINE and ALTERNATE_PLAN_SOURCE enables the Automatic SQL ...

   Thought of  documenting  this as at times we come across situations where w have to kill certain background process to high load  or other issues .  This is only for information purpose . Its advisable to  check with  Oracle Support for any actions  Killable: ARCn: Redo log archivers CJQn: Job scheduler coordinator Dnnn: Dispatchers DIA0: Diagnosibility process 0 DIAG: Diagnosibility coordinator FDBA: Flashback data archiver process Jnnn: Job scheduler processes MMNL: Manageability Monitor Process 2 MMON: Manageability Monitor Process PING: Interconnect latency measurement Qnnn: Queue cleanup processes QMNC: Queue coordinator RECO: Distributed recovery process Snnn: Shared servers SMCO: Space management coordinator Wnnn: Space management processes Instance-Critical: ACMS: Atomic controlfile to memory server CKPT: Checkpoint DBRM: Resource manager process DBWn: Database writer processes LGWR: Redo log writer LMDn: Global enqueue service dae...

   1) Listing Available Software Images and Versions for Database [oracle@host1 ~]$ sudo dbaascli cswlib showImages 2)  Creating Oracle Database Home with Unified Audit Enabled  [oracle@host1 ~]$ sudo dbaascli dbhome create --version 19000 --bp JAN2021 --oracleHomeName UnifiedAuditenabled --enableUnifiedAuditing true 3) Creating Oracle Database In the Specified Oracle Database Home [oracle@host1 ~]$ sudo dbaascli database create --dbName DBNAME --dbUniqueName DBNAME --dbSid DBNAME --oracleHomeName UnifiedAuditenabled --nodeList host1,host2 --createAsCDB true --waitForCompletion false The command will prompt for the sys and TDE password. Use the flag --waitForCompletion false To run in the background. To run prerequisites checks, use the --executePrereqs command option. This will perform only the prerequisite checks without performing the actual Oracle Database home creation. sudo dbaascli dbhome create --version Oracle Home Version --imageTag image tag --executePrere...

  Since we were exploring enabling awr snapshots at  pdb level ,  we came across   below 3 parameters  that control  awr   generation at pluggable database level . AWR_PDB_AUTOFLUSH_ENABLED Awr_snapshot_time_offset AWR_PDB_MAX_PARALLEL_SLAVES AWR_PDB_AUTOFLUSH_ENABLED 1. AWR Snapshots and reports can be created only at the container database (CDB) level in     Oracle 12c R1 (12.1.0.1.0 / 12.1.0.2.0) 2. AWR Snapshots and reports can be created at the container database (CDB) level as well as pluggable database (PDB) level     in Oracle 12c R2 (12.2.0.1.0) 3. By default, AWR Snapshots and reports can be generated only at the container database (CDB) level The default value of AWR_PDB_AUTOFLUSH_ENABLED is false. Thus, by default, automatic AWR snapshots are disabled for all the PDBs in a CDB. When you change the value of AWR_PDB_AUTOFLUSH_ENABLED in the CDB root, the new value takes effect in all the PDBs in the CDB...

  It just that we come across situation where we see some of rac process down .Below are  2 of process that can be started manually .   Starting CHA  manually  chad stands for the Cluster Health Advisor (CHA) daemon which is is part of the Oracle Autonomous Health Framework(AHF),  It continuously monitors cluster nodes and Oracle RAC databases for performance and availability issues. Oracle Cluster Health Advisor runs as a highly available cluster resource, ochad, on each node in the cluster. Each Oracle Cluster Health Advisor daemon (ochad) monitors the operating system on the cluster node and optionally, each Oracle Real Application Clusters (Oracle RAC) database instance on the node.  The ochad daemon receives operating system metric data from the Cluster Health Monitor and gets Oracle RAC database instance metrics from a memory-mapped file. The daemon does not require a connection to each database instance. This data, along with the selected m...

  We need to check what  component  are started and what are pending based on that we need to  take approach .  Known Issues  / Issues faced :  1)  ora.crsd remains in INTERMEDIATE and commands like crsctl hang and Logical corruption check failed (Doc ID 2075966.1) 2) CRSD Failing to Start or in INTERMEDIATE State Due to OLR corruption. (Doc ID 2809968.1)  3) CRS is not starting up on the cluster node (Doc ID 2445492.1)  --  Missing execute permission  on <GRID_HOME>/srvm/mesg directory 4)  Crs activeversion is different on both nodes after patching/upgrade  5)  Permission of Voting/Ocr disk was changed  6)  ohasd process was no started due to missing /etc/systemd/system/ohasd.service  7)  Asm startup issues faced  due to  below  -->  asm disk were not shared between nodes  --> asm_diskstring parameters were somehow removed  7) asm not s...

  Writing  this blog to keep all  scripts handy for handling dataguard  issues  Script to Collect Data Guard Physical and Active Standby Diagnostic Information for Version 10g and above (Including RAC) ( ID 1577406.1) https://abdul-hafeez-kalsekar-tuning.blogspot.com/2023/01/script-to-collect-data-guard-physical_21.html Script to Collect Data Guard Primary Site Diagnostic Information [ID 241374.1] https://abdul-hafeez-kalsekar-tuning.blogspot.com/2023/01/script-to-collect-dataguard-primary.html Script to Collect Data Guard Primary Site Diagnostic Information for Version 10g and above (Including RAC). (Doc ID 1577401.1) https://abdul-hafeez-kalsekar-tuning.blogspot.com/2023/01/script-to-collect-data-guard-primary.html Script to Collect Data Guard Physical Standby Diagnostic Information [ID 241438.1] https://abdul-hafeez-kalsekar-tuning.blogspot.com/2023/01/script-to-collect-data-guard-physical.html SRDC - Collect Data Guard Diagnostic Information (Doc ID 2219763....

  srdc_DGPhyStby_diag.sql REM srdc_DGPhyStby_diag.sql - collect primary database information in dataguard environment define SRDCNAME='DG_PHYSTBY_DIAG' set markup html on spool on set TERMOUT off; COLUMN SRDCSPOOLNAME NOPRINT NEW_VALUE SRDCSPOOLNAME  select 'SRDC_'||upper('&&SRDCNAME')||'_'||upper(value)||'_'||       to_char(sysdate,'YYYYMMDD_HH24MISS') SRDCSPOOLNAME from v$parameter where lower(name)='instance_name'; REM  spool &&SRDCSPOOLNAME..htm Set heading off; select '+----------------------------------------------------+' from dual  union all  select '| Script version:  '||'25-Aug-2021' from dual union all select '| Diagnostic-Name: '||'&&SRDCNAME' from dual  union all  select '| Timestamp: '|| to_char(systimestamp,'YYYY-MM-DD HH24:MI:SS TZH:TZM') from dual  union all  select '| Machine: '||host_name from v$instance  union all  select ...