pinbol

Killing processes by process name

2023-07-24T10:08:00.000+10:00

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

2023-02-18T07:37:00.005+10:00

Как быстро проверить лог и статус 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

2022-12-22T20:15:00.002+10:00

Быстрый поиск блокировки в 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

2022-02-02T11:53:00.003+10:00

Имеется проблема в БД 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

2017-12-10T10:23:00.000+10:00

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

2017-05-26T20:53:00.002+10:00

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

Oracle waits прозрачно

2017-05-22T19:47:00.002+10:00

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

Linux 'screen' command

2017-05-09T20:59:00.000+10:00

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

2017-05-03T09:53:00.000+10:00

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?

2017-04-10T14:00:00.003+10:00

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 ...

oracle preinstall

2017-03-17T09:16:00.002+10:00

# cd /etc/yum.repos.d
# wget http://public-yum.oracle.com/public-yum-ol6.repo

oracle-rdbms-server-11gR2-preinstall
oracle-rdbms-server-12cR1-preinstall

echo "proxy=http://Proxy:Port/" >> /etc/yum.conf

No incremental levels for DB INCR in v$rman_backup_job_details

2017-03-02T12:17:00.004+10:00

It is advised to use V$RMAN_BACKUP_JOB_DETAILS to identify status of backup jobs. But it is difficult to understand when it was taken full backup of Oracle DB due to in the INPUT_TYPE of the output you can find "DB INCR" value as for FULL Incremental level 0 backups as for Incremental level 1 backups.

You can find way to clarify incremental levels or to find out when was taken FULL backup with the V$BACKUP_SET_DETAILS view.

For instance,
select * from V$BACKUP_SET_DETAILS where BACKUP_TYPE='I' and INCREMENTAL_LEVEL='0'

Ad hoc useful querries (SQL Server)

2016-12-19T13:16:00.000+10:00

SELECT
SERVERPROPERTY('MachineName') as Host,
SERVERPROPERTY('InstanceName') as Instance,
SUBSTRING(@@VERSION, 1,25) as Version,
SERVERPROPERTY('Edition') as Edition, /*shows 32 bit or 64 bit*/
SERVERPROPERTY('ProductVersion') AS 'Patch Level',
SERVERPROPERTY('ProductLevel') as SP, /* RTM or SP1 etc*/
Case SERVERPROPERTY('IsClustered')
when 1 then 'CLUSTERED' else
'STANDALONE' end as ServerType

My MoSCoW education 2017

2016-12-16T09:28:00.001+10:00

Must have:
1) Install VirtualBox - Jan
2) Install Oracle12c - Jan
3) Clusterware & RAC - Jan-Feb
4) New Features Ora 12c - Jan-Feb
5) OEM Cloud Control - March
6) Performance labs - April
7) MSSQL AlwaysOn lab - May

Should have:
1) Postgresql install - Jan
2) RHEL 7 new features - Sept

Links:
1) Обучающие курсы, видео и учебники по Visio 2013

https://support.office.com/ru-ru/article/%D0%9E%D0%B1%D1%83%D1%87%D0%B0%D1%8E%D1%89%D0%B8%D0%B5-%D0%BA%D1%83%D1%80%D1%81%D1%8B-%D0%B2%D0%B8%D0%B4%D0%B5%D0%BE-%D0%B8-%D1%83%D1%87%D0%B5%D0%B1%D0%BD%D0%B8%D0%BA%D0%B8-%D0%BF%D0%BE-Visio-2013-93e93e94-0899-4530-aebb-c70fd39faee5

2) Updating your Database Management Skills to SQL Server 2014

https://mva.microsoft.com/en-us/training-courses/updating-your-database-management-skills-to-sql-server-2014-8313?l=S3j693Yy_704984382

If you change password of Oracle USER1 wrongly

2016-12-09T15:19:00.002+10:00

Go to the previous backup or copy of database and select user's password hash

SQL> SELECT name, password FROM sys.user$ WHERE name='USER1';

NAME
--------------------------------------------------------------------------------
PASSWORD
--------------------------------------------------------------------------------
USER1
48F7196FF1A87215

On the problem DB change password by issue:

SQL> alter user USER1 identified by values '48F7196FF1A87215';

User altered.

yum provides */uuencode

2016-11-22T11:46:00.000+10:00

I always sent to myself files with uuencode. And today I was surprised when I did not find it in one of the systems.
'yum list |grep uuencode' didn't show any package
Then I googled and found advise to install sharutils

To understand it was enough to issue
yum provides */uuencode
which found the package with uuencode

sharutils-4.7-6.1.el6.x86_64 : The GNU shar utilities for packaging and unpackaging shell archives
Repo : rhel6-server-...
Matched from:
Filename : /usr/bin/uuencode

After 'yum install sharutils' the 'which uuencode' is sucsessful
/usr/bin/uuencode

MSDN related links

2016-09-22T11:45:00.004+10:00

Unlimited Dev & Test Enviroments with MSDN Subscriptions

https://blogs.msdn.microsoft.com/govdev/2012/04/18/unlimited-dev-test-enviroments-with-msdn-subscriptions/

MSDN Pricing https://www.visualstudio.com/products/how-to-buy-vs

About the SQL Server License Terms

https://msdn.microsoft.com/en-us/library/ms143522(v=sql.120).aspx

Quite good paper on SQL Server 2012 with links at the end
https://www.directionsonmicrosoft.com/licensing/2013/06/licensing-sql-server-development-and-test

Licensing SQL Server Dev/Test Environments
http://www.sqlservercentral.com/blogs/nebraska-sql-from-dba_andy/2012/07/27/licensing-sql-server-devtest-environments/

Practices & Pitfalls of MSDN Subscription Licensing

ssh keys for Named credentials

2016-07-23T10:07:00.001+10:00

ssh-keys credentials for OEM CC
Put ssh public keys in the home (authorized_keys) of user on OEM server and in home of user of Oracle DB server (authorized_keys)

TNS-12514: TNS:listener does not currently know of service requested in connect descriptor

2016-04-15T16:04:00.001+10:00

One of the ways to resolve the error "TNS-12514: TNS:listener does not currently know of service requested in connect descriptor" could be registering DB Service Name as following:

SQL> alter system set local_listener=DBServiceName scope=both;

System altered.

SQL> alter system register;

System altered.

SQL> exit

$ lsnrctl reload

Outlook reminders don't work propperly

2016-04-12T09:31:00.002+10:00

I've just used article "How to make Outlook Calendar reminders stay on top in Windows 7" http://superuser.com/questions/251963/how-to-make-outlook-calendar-reminders-stay-on-top-in-windows-7?rq=1 to make Outlook reminders popped-up during working in other applications. Testing.

To identify most appropriated Oracle block size on given file system

2016-04-06T14:34:00.003+10:00

To identify most appropriated Oracle block size on file system mounted to /oracle/TEST/ we tried the following tests:

oracle$ time dd if=/dev/zero of=/oracle/TEST/16K.test bs=16k count=16384
16384+0 records in
16384+0 records out
268435456 bytes (268 MB) copied, 2.64192 seconds, 102 MB/s

real 0m2.651s
user 0m0.017s
sys 0m0.223s
oracle$ time dd if=/dev/zero of=/oracle/TEST/8K.test bs=8k count=32768
32768+0 records in
32768+0 records out
268435456 bytes (268 MB) copied, 2.57524 seconds, 104 MB/s

real 0m2.578s
user 0m0.014s
sys 0m0.386s
oracle$ time dd if=/dev/zero of=/oracle/TEST/4K.test bs=4k count=65536
65536+0 records in
65536+0 records out
268435456 bytes (268 MB) copied, 2.62402 seconds, 102 MB/s

real 0m2.626s
user 0m0.005s
sys 0m0.379s

"alter system register" does not work

2016-02-19T19:38:00.000+10:00

Today my new DB created from a template script was not registered in the listener. And c was not working. Denis advised me to add the local_listener parameter into database. When I checked this parameter it was in place. But its port was wrong - instead of 1521 it was another port.
After I corrected the port and issued the "alter system register" the database successfully registered in the listener.

Как Вам обучение в формате Training-on-Demand?

2016-02-17T14:24:00.001+10:00

Недавно я прошел курс Oracle 12c New Features в формате Training-on-Demand.

Приемлемый ли формат?

Такой формат тренинга в случае, если дают возможность пройти тренинг из дома, то да, приемлемый. Даже более эффективный, чем у наших российских тренеров на месте.

Вообще это мое личное мнение, но попробую выразить свои ощущения. Более высокую эффективность вижу в том, что волей неволей получаешь весь материал, который предполагается по программе, т.к. есть возможность прокрутки, паузы с тем, чтобы подробнее ознакомиться с понятиями, которые ты плохо знаешь, из других источников или документации – на курсах в классе часто не все дается или по многим темам пробегаешь что называется «галопом по европам». В надежде, что дома повторишь и разберешься, ты уезжаешь, но как правило, дома нет времени и актуальность теряется и соответственно глубина полученных знаний на порядок ниже.

На видеокурсе у меня есть возможность потратить на понимание и изучение той или иной темы больше времени. Есть возможность копать в глубину, поставив на паузу видео.

Также преподаватель выполняет все лабораторные работы в видео с объяснением причинно-следственных связей и их иллюстрациями случаями из своего опыта или особенностями прежних версий, что в некоторых практических заданиях очень важно для понимания. На курсах в классе порой не успеваешь и часто выполнение лабораторных либо сводится к перепечатыванию задания из методички, либо вообще пропускается и т.о. эффективность таких заданий порой нулевая.

Единственное, мне кажется одной недели на лабораторные работы маловато. Ведь на видео доступ дается на срок до 3 месяцев. Поэтому хорошо бы если бы давали возможность выбрать до трех недельных окон к лабораторным.

'purge recyclebin' does not purge recyclebin

2016-02-11T16:12:00.001+10:00

SQL> select count(*) from dba_recyclebin;

COUNT(*)
----------
10419

SQL> purge recyclebin;

Recyclebin purged.

SQL> select count(*) from dba_recyclebin;

COUNT(*)
----------
10419

How to delete all objects of a user?

2016-02-11T16:11:00.000+10:00

How to delete all objects of a user?

After googling I stopped on "Script to Drop a User's Objects" offered on the Toad World
https://www.toadworld.com/platforms/oracle/w/wiki/5013.script-to-drop-a-user-s-objects