Killing processes by process name

Killing processes by process name

In order to kill multiple processes by name template use the ps -ef|grep NAME_OF_PROCESS|grep -v grep| awk '{print $2}' | xargs kill

root[+ASM1]@yuzdc1-n-b00001 ~ # ps -ef|grep CDBAUX|grep -v grep
oracle 1325 1 0 May08 ? 00:00:08 ora_w00b_CDBAUX
oracle 12727 1 0 May07 ? 00:00:09 ora_w003_CDBAUX
oracle 13370 1 0 May08 ? 00:00:08 ora_w00a_CDBAUX
oracle 24014 1 0 May08 ? 00:00:08 ora_w007_CDBAUX
oracle 80734 1 0 May07 ? 00:00:33 ora_pmon_CDBAUXR1
oracle 80736 1 0 May07 ? 00:00:48 ora_psp0_CDBAUXR1
......
oracle 149625 1 0 May11 ? 00:02:32 ora_o001_CDBAUX
oracle 184346 1 0 May08 ? 00:00:09 ora_w008_CDBAUX
root[+ASM1]@yuzdc1-n-b00001 ~ # ps -ef|grep CDBAUX|grep -v grep| awk '{print $2}' | xargs kill
root[+ASM1]@yuzdc1-n-b00001 ~ # ps -ef|grep CDBAUX|grep -v grep

Как быстро проверить лог и статус Oracle RMAN backup задач

Спасибо https://orahow.com/check-rman-log-file-and-status/

Steps to check the RMAN backup job details.

STEP 1:  To find the status of the jobs:

set lines 300
 col STATUS format a22
 col hrs format 999.99
 select
 SESSION_KEY, SESSION_RECID, SESSION_STAMP,INPUT_TYPE, STATUS,
 to_char(START_TIME,'mm/dd/yy hh24:mi') start_time,
 to_char(END_TIME,'mm/dd/yy hh24:mi')   end_time,
 elapsed_seconds/3600                   hrs
 from V$RMAN_BACKUP_JOB_DETAILS
 order by session_key;

STEP 2:  Check the % completed and more detailed information:

SELECT SID, SERIAL#, CONTEXT, SOFAR, TOTALWORK,
 ROUND (SOFAR/TOTALWORK*100, 2) "% COMPLETE"
 FROM V$SESSION_LONGOPS
 WHERE OPNAME LIKE 'RMAN%' AND OPNAME NOT LIKE '%aggregate%'
 AND TOTALWORK! = 0 AND SOFAR <> TOTALWORK;

 STEP 3: Check the logs or output of the running RMAN jobs 

set lines 200
 set pages 1000
 select output from GV$RMAN_OUTPUT
 where session_recid = &SESSION_RECID
 and session_stamp = &SESSION_STAMP
 order by recid;

NOTE: Please enter  SESSION_RECID  and  SESSION_STAMP  from the 1st query mentioned in step 1.

Быстрый поиск блокировки в Oracle

 Быстрый поиск блокировки в Oracle

https://www.support.dbagenesis.com/post/lock-conflict-in-oracle

Быстро о блокировках

 https://oracle-patches.com/oracle/begin/%D0%B1%D0%BB%D0%BE%D0%BA%D0%B8%D1%80%D0%BE%D0%B2%D0%BA%D0%B8-oracle

Имеется проблема в БД Dashboard, когда при синхронизации данных у пользователей выскакивает ошибка, видимая в Error.log как

2022-02-02 09:53:57.65 spid153     Error: 8623, Severity: 16, State: 1.

2022-02-02 09:53:57.65 spid153     The query processor ran out of internal resources and could not produce a query plan. This is a rare event and only expected for extremely complex queries or queries that reference a very large number of tables or partitions. Please simplify the query. If you believe you have received this message in error, contact Customer Support Services for more information.

Саша Х. из поддержки приложений лечит ее при помощи ребилда не-уникального не-кластерного индекса каждое утро. 

Была мысль сделать секционирование, но на таблицу из 40 млн. записей, это может быть неправильным. 

И сегодня я обнаружил две устаревшие auto-created статистики по таблице, которая принимает участие в проблемных запросах Dashboard.

И так я собрал статистику по ним при помощи статьи https://www.sqlshack.com/sql-server-statistics-and-how-to-perform-update-statistics-in-sql/

всего ушло на одну 30 сек., на другую 270 сек. CPU time

Посмотрим проявится ли ошибка завтра. 

-- Список всех статистик по таблице [dbo].[logical_values]

SELECT sp.stats_id, name, filter_definition, last_updated, rows, rows_sampled, steps, unfiltered_rows, modification_counter FROM sys.stats AS stat CROSS APPLY sys.dm_db_stats_properties(stat.object_id, stat.stats_id) AS sp WHERE stat.object_id = OBJECT_ID('[dbo].[logical_values]') order by last_updated; -- We see two old AUTO Created statistics -- logical_id statistics -- [_WA_Sys_00000002_5BE2A6F2] -- extra_id statistics -- [_WA_Sys_00000005_5BE2A6F2] -- Let's update these OLD statistics with FULL SCAN set statistics io on; set statistics time on; -- update statistics on extra_id column Update STATISTICS [dbo].[logical_values] _WA_Sys_00000005_5BE2A6F2 WITH FULLSCAN; ---SQL Server Execution Times: ---- CPU time = 25640 ms, elapsed time = 28841 ms. -- update statistics on logical_id column Update STATISTICS [dbo].[logical_values] _WA_Sys_00000002_5BE2A6F2 WITH FULLSCAN; -- SQL Server Execution Times: -- CPU time = 27516 ms, elapsed time = 29202 ms.

Data Guard concepts and processes

Data Guard Modes include the following three:
- Maximum Protection
- Maximum Availability
- Maximum Performance

Physical
Logincal
Snapshot

In the physical standby database, your standby database is identical to the primary database, even at the block level. So the redo from the Primary could be applied directly to Standby.
MRP - Managed Recovery Process applies the redo entries from archived logs (or from 12c redo logs) to Physical Standby

Logical Standby database is different. It is independent database and its DB name is different from Primary Database.
LSP - Logical Standby Process applies the redo records from archived logs (or from 12c redo logs) to Logical Standby. LSP reads redo and convert it to SQL statement. LSP not applies redo, but applies SQL statement eventually.
Because SQL statements applies then the Logical Standby DB can be higher version of database or lower version. That's why we use Logical Standby DB in case of rolling upgrade.
Standby Logical Database is more resource usage consumable. Because we use LogMiner and other BG processes to read redo, to convert redo into SQL, to evaluate SQL statement, to apply SQL statement. We experience little more overhead that with physical stb db, but it is acceptable.

SQL Server Index and Statistics Maintenance

https://ola.hallengren.com/sql-server-index-and-statistics-maintenance.html

Oracle waits прозрачно

https://sites.google.com/site/embtdbo/wait-event-documentation/oracle-io-waits

Linux 'screen' command

It is very useful to use screen in the terminal leaving your session at work with a process and return to it at home. Below is example with steps sequence of using the 'screen'. 

$ script -c 'screen' /dev/null

$ screen -ls

There are screens on:

        32156.pts-8.host022     (Attached)

        25681.xdbfix    (Attached)

        3074.pts-8.host022      (Attached)

        25658.pts-2.host022     (Attached)

        1289.pts-11.host022     (Detached)

5 Sockets in /var/run/screen/S-oracle.

$ screen -r 1289.pts-11.host022

Multiple AWR reports with one hour break up

It seems strange, but sometimes Application support asks about AWR Reports on 1-2 days with one hour break up.

In this case it is useful  multiple_awr.sql script from
https://oracle-base.com/dba/script?category=10g&file=generate_multiple_awr_reports.sql

First to identify snap range for the date you can use the following:

set linesize 500

set head off

--col snap_id for a45

col begin_interval_time for a30

select snap_id,begin_interval_time,end_interval_time from dba_hist_snapshot

where trunc(begin_interval_time) = to_date('03/05/2017','DD/MM/YYYY')

order by snap_id;

and then use the multiple_awr.sql script replacing values of the: 

  l_snap_start       NUMBER := 7724;
  l_snap_end         NUMBER := 7730;

------- multiple_awr.sql ----------------------------------------------------------------------------

-- File Name    : http://www.oracle-base.com/dba/10g/generate_multiple_awr_reports.sql
-- Author       : DR Timothy S Hall
-- Description  : Generates AWR reports for all snapsots between the specified start and end point.
-- Requirements : Access to the v$ views, UTL_FILE and DBMS_WORKLOAD_REPOSITORY packages.
-- Call Syntax  : Create the directory with the appropriate path.
--                Adjust the start and end snapshots as required.
--                @generate_multiple_awr_reports.sql
-- Last Modified: 02/08/2007
-- -----------------------------------------------------------------------------------
CREATE OR REPLACE DIRECTORY awr_reports_dir AS '/tmp/awr';

DECLARE
  -- Adjust before use.
  l_snap_start       NUMBER := 7724;
  l_snap_end         NUMBER := 7730;
  l_dir              VARCHAR2(50) := 'AWR_REPORTS_DIR';

  l_last_snap        NUMBER := NULL;
  l_dbid             v$database.dbid%TYPE;
  l_instance_number  v$instance.instance_number%TYPE;
  l_file             UTL_FILE.file_type;
  l_file_name        VARCHAR(50);

BEGIN
  SELECT dbid
  INTO   l_dbid
  FROM   v$database;

  SELECT instance_number
  INTO   l_instance_number
  FROM   v$instance;

  FOR cur_snap IN (SELECT snap_id
                   FROM   dba_hist_snapshot
                   WHERE  instance_number = l_instance_number
                   AND    snap_id BETWEEN l_snap_start AND l_snap_end
                   ORDER BY snap_id)
  LOOP
    IF l_last_snap IS NOT NULL THEN
      l_file := UTL_FILE.fopen(l_dir, 'awr_' || l_last_snap || '_' || cur_snap.snap_id || '.htm', 'w', 32767);

      FOR cur_rep IN (SELECT output
                      FROM   TABLE(DBMS_WORKLOAD_REPOSITORY.awr_report_html(l_dbid, l_instance_number, l_last_snap, cur_snap.snap_id)))
      LOOP
        UTL_FILE.put_line(l_file, cur_rep.output);
      END LOOP;
      UTL_FILE.fclose(l_file);
    END IF;
    l_last_snap := cur_snap.snap_id;
  END LOOP;

EXCEPTION
  WHEN OTHERS THEN
    IF UTL_FILE.is_open(l_file) THEN
      UTL_FILE.fclose(l_file);
    END IF;
    RAISE;
END;
/

=============

How to identify ORACLE_HOME of an Oracle instance process?

How to identify ORACLE_HOME of an Oracle instance process?
Use strings /proc//environ | grep ORACLE_HOME

For, instance,
ps -ef|grep pmon
oracle 9466     1  0  2016 ?        00:29:36 ora_pmon_DBName

$ strings /proc/9466/environ | grep ORACLE_HOME
ORACLE_HOME=/oracle/app/product ...