APPSDBA Data Lake

  Sometimes database hangs due to many reasons and higher managements look for for RCA. It is important to have all trace file to find the issue for hanging the database or fine the RCA. Get a hang analyze report by running below commands  SQL> sqlplus / as sysdba SQL> oradebug setmypid SQL> oradebug unlimit SQL> oradebug hanganalyze 3 # Wait at least 2-3 minutes to give time to identify process state changes. SQL> oradebug hanganalyze 3 # Wait at least 2-3 minutes to give time to identify process state changes. SQL> oradebug hanganalyze 3 SQL> oradebug tracefile_name SQL> select name, value from v$diag_info where name='Default Trace File'; In case database can't be connected  at all then please do  to the following commands  connect / as sysdba oradebug setmypid oradebug unlimit oradebug dump systemstate 266 -g; oradebug dump systemstate 266 -g; oradebug dump systemstate 266 -g; To generate HANGANALYZE for RAC: SQL> oradebug setmypid S

AWR report can be generating in RAC database using 2 scripts awrrpt.sql or awrrpti.sql and It can be generated at the CDB and PDB levels. awrrpt.sql  – > This will generate the one report for the database across all the nodes. awrrpti.sql  – > This will generates report for a particular instance levels. [demantra@dsiddem admin]$ sqlplus "/as sysdba" SQL*Plus: Release 19.0.0.0.0 - Production on Fri Jul 28 13:05:32 2023 Version 19.3.0.0.0 Copyright (c) 1982, 2019, Oracle. All rights reserved. Connected to: Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production Version 19.3.0.0.0 SQL> @ $ORACLE_HOME/rdbms/admin/awrrpt.sql   Specify the Report Type ~~~~~~~~~~~~~~~~~~~~~~~ AWR reports can be generated in the following formats. Please enter the name of the format at the prompt. Default value is 'html'. 'html' HTML format (default) 'text' Text format 'active-html' Includes Performance Hub active re

 Make Sure oraInst.loc location is correct and accessble. [demantra@dsiddem ~]$ cat /etc/oraInst.loc inventory_loc=/orastage/oraInventory inst_group=dba [demantra@dsiddem ~]$ cd /orastage/oraInventory [demantra@dsiddem oraInventory]$ ls -ltr total 16 drwxrwx---. 5 demantra dba 4096 Jul 22 19:37 backup drwxrwx---. 2 demantra dba 4096 Jul 22 19:37 ContentsXML drwxrwx---. 2 demantra dba 4096 Jul 22 19:37 locks drwxrwx---. 3 demantra dba 4096 Jul 23 10:08 logs Remove Oracle Home from Inventory [demantra@dsiddem oraInventory]$ cd $ORACLE_HOME/oui/bin [demantra@dsiddem bin]$ ./runInstaller -detachHome ORACLE_HOME=$ORACLE_HOME Starting Oracle Universal Installer... Checking swap space: must be greater than 500 MB.   Actual 3039 MB    Passed The inventory pointer is located at /etc/oraInst.loc Run Clone.pl [demantra@dsiddem bin]$ echo $ORACLE_HOME /dnbsid/demantra/oracle/19.0.0 [demantra@dsiddem bin]$ echo $ORACLE_BASE /dnbsid/demantra [demantra@dsiddem bin]$ E01=ORACLE_HOME=$ORACLE_HOME [dema