Hinzinfotech

Troubleshooting Network Issues in Oracle RAC 19c – Step-by-Step Guide Oracle Real Application Clusters (RAC) 19c provides high availability and scalability, but network issues can impact node communication, cluster stability, and performance. Here’s a step-by-step approach to troubleshooting network problems in an Oracle RAC environment. 🛠️ Step 1: Verify the Network ConfigurationCheck if the Public, […]

Facing a multi-node database crash in Oracle RAC can be a critical challenge, but with the right approach, recovery is seamless. Recently, I encountered such a situation and successfully reinstated the Oracle RAC environment. Here’s how I did it: ✅ Steps to Reinstate Oracle RAC Multi-Node Instance After a Crash:1️⃣ Identify the Cause of the […]

As an Oracle DBA, staying on top of both your database and the underlying Linux system is key to ensuring smooth and efficient performance. Below are a few of the most useful scripts I run daily to check on the health of the Oracle Database and the Linux system it’s running on!🔹 Oracle Database Health […]

The Oracle RAC environments have a one to many relationship between the database and instances.In other words, multiple instances access a single database.Oracle RAC uses Oracle Clusterware for the infrastructure in order to bind multiple servers so that they operate as a single system.Oracle Clusterware also manages resources such as virtual Internet protocol, addresses,databases,listeners, services. […]

Description: You are in situation to quickly kill multiple sessions which consume high resource on database which cause performance issues. You can use the below scripts to quickly act based on your scenario SQL script to kill all the sessions from the database — Note: Use this script with caution as this will kill all […]

The basic health checks Performing the below-mentioned queries and commands which applicable for RAC,standalone database and windows. Check free space on disk and DB uptime$ df -k DB uptime control whether database Listener is running$ lsnrctl status control whether database is running$ srvctl status database -d racdb $ sqlplus / as sysdba SQL> select name,open_mode […]

step 1: create the tuning task for the sql idSQL> declaresql_tune_task_id varchar2(100);beginl_sql_tune_task_id := dbms_sqltune.create_tuning_task (begin_snap => 41062,end_snap => 44516,sql_id => ‘2mjr0fw17sjuh’,scope => dbms_sqltune.scope_comprehensive,time_limit => 10800,task_name => ‘2mjr0fw17sjuh_tuning_task’,description => ‘tuning_in_OLBUI’);dbms_output.put_line(‘l_sql_tune_task_id: ‘ || l_sql_tune_task_id);end; DECLARE  sql_tune_task_id VARCHAR2(100);BEGIN  sql_tune_task_id := DBMS_SQLTUNE.CREATE_TUNING_TASK (    sql_id       => ‘2mjr0fw17sjuh’,    begin_snap    => 41062,    end_snap     […]

Here are the Generating statistics in the Oracle database examples below: exec dbms_stats.gather_system_stats(‘Start’);select * from sys.aux_stats$;exec dbms_stats.gather_table_stats(ownname => ‘SH’, tabname => ‘SALES’, cascade=>true);select * from dba_tab_statistics where table_name = ‘SALES’; select * from sales;select * from dba_tab_columns where table_name = ‘SALES’;exec dbms_stats.gather_system_stats(‘NOWORKLOAD’);select * from sys.aux_stats$;select * from v$sql_plan; exec dbms_stats.gather_database_stats;exec dbms_stats.gather_dictionary_stats;exec dbms_stats.gather_schema_stats(ownname => ‘SH’);exec dbms_stats.gather_table_stats(ownname […]

Q1.when you decide to tune the queries? Answer: We call these queries as “top consuming” queries. These queries do not exceed 5 percent of the total queries Or mostly it is much less.But most of the times, the top consuming queries use 80% of the total resources. This is a really huge ratio. Less than […]

1.To list long-running forms user sessions select s.sid,s.process,p.spid,s.status ,s.action,s.module, (s.last_call_et/3600) fromv$session s, v$process p where round(last_call_et/3600) >4 and action like ‘%FRM%’ andp.addr=s.paddr ; 2.To list inactive Sessions respective username SELECT username,count(*) num_inv_sessFROM v$sessionwhere last_call_et > 3600and username is not nullAND STATUS=’INACTIVE’group by usernameorder by num_inv_sess DESC; SELECT count(*) FROM v$session where last_call_et > 43200 and […]