Change all tables, view and sequences owner in a Postgres DB from bash: Tables: for tbl in `psql -qAt -c "select tablename from pg_tables where schemaname = 'public';" maksenov` ; do psql -c "alter table \"$tbl\" owner to maksenov" maksenov; done Sequences: for tbl in `psql -qAt -c "select sequence_name from information_schema.sequences where sequence_schema = 'public';" maksenov` ; do psql -c "alter sequence \"$tbl\" owner to maksenov" maksenov ; done Views: for tbl in `psql -qAt -c "select table_name from information_schema.views where table_schema = 'public';" maksenov` ; do psql -c "alter view \"$tbl\" owner to maksenov" maksenov; done
Temp Usage
by session:
SELECT S.sid || ',' || S.serial# sid_serial, S.username, S.osuser, P.spid, S.module, S.program, SUM (T.blocks) * TBS.block_size / 1024 / 1024 mb_used, T.tablespace, COUNT(*) sort_ops FROM v$sort_usage T, v$session S, dba_tablespaces TBS, v$process P WHERE T.session_addr = S.saddr AND S.paddr = P.addr AND T.tablespace = TBS.tablespace_name GROUP BY S.sid, S.serial#, S.username, S.osuser, P.spid, S.module, S.program, TBS.block_size, T.tablespace ORDER BY sid_serial;
когда арклоги не нужны
RMAN-08137: warning: archived log not deleted, needed for standby or upstream capture process
SQL>SELECT CAPTURE_NAME, CAPTURE_TYPE, STATUS, to_char(REQUIRED_CHECKPOINT_SCN,'999999999999999')as REQ_SCN ,to_char(OLDEST_SCN,'999999999999999')as OLDEST_SCN FROM DBA_CAPTURE;
выдаёт 0 строк
отключение политики
CONFIGURE ARCHIVELOG DELETION POLICY TO SHIPPED TO STANDBY;
хоть и отругается
RMAN-08591: warning: invalid archived log deletion policy
но удаление архивных логов пройдёт на ура
RMAN> DELETE NOPROMPT ARCHIVELOG UNTIL SEQUENCE 22097 THREAD 1;
Копируем файлы между серверами по ssh
очередная заметка, которая может пригодиться когда нет scp
easy way to send your file between your servers using ssh:
tar zcvf - ./DIRECTORY_TO_TARZIP_AND_SEND | ssh tomcat_server_name "cat > out_files.tar.gz"
initial postgres beauty
после установки свежего экземпляра postgresql отображение в консоли psql не очень радует на больших строках, которые не вмещаются в экран 🙂
поправим пейджинг:
cd echo "\setenv PAGER 'less -XS'" > .psqlrc
поправим bash prompt string:
echo 'PS1=["\u@\h \w]\$ "' >> .bash_profile
Частота переключения REDO логов по часам
Online Redo Log Switch Frequency Map Query.
Взято с: https://blog.zeddba.com/2019/05/31/online-redo-log-switch-frequency-map/
Пусть будет здесь, в последнее время часто ищу подобного рода запрос
SELECT TRUNC (first_time) "Date", inst_id, TO_CHAR (first_time, 'Dy') "Day", COUNT (1) "Total", SUM (DECODE (TO_CHAR (first_time, 'hh24'), '00', 1, 0)) "h0", SUM (DECODE (TO_CHAR (first_time, 'hh24'), '01', 1, 0)) "h1", SUM (DECODE (TO_CHAR (first_time, 'hh24'), '02', 1, 0)) "h2", SUM (DECODE (TO_CHAR (first_time, 'hh24'), '03', 1, 0)) "h3", SUM (DECODE (TO_CHAR (first_time, 'hh24'), '04', 1, 0)) "h4", SUM (DECODE (TO_CHAR (first_time, 'hh24'), '05', 1, 0)) "h5", SUM (DECODE (TO_CHAR (first_time, 'hh24'), '06', 1, 0)) "h6", SUM (DECODE (TO_CHAR (first_time, 'hh24'), '07', 1, 0)) "h7", SUM (DECODE (TO_CHAR (first_time, 'hh24'), '08', 1, 0)) "h8", SUM (DECODE (TO_CHAR (first_time, 'hh24'), '09', 1, 0)) "h9", SUM (DECODE (TO_CHAR (first_time, 'hh24'), '10', 1, 0)) "h10", SUM (DECODE (TO_CHAR (first_time, 'hh24'), '11', 1, 0)) "h11", SUM (DECODE (TO_CHAR (first_time, 'hh24'), '12', 1, 0)) "h12", SUM (DECODE (TO_CHAR (first_time, 'hh24'), '13', 1, 0)) "h13", SUM (DECODE (TO_CHAR (first_time, 'hh24'), '14', 1, 0)) "h14", SUM (DECODE (TO_CHAR (first_time, 'hh24'), '15', 1, 0)) "h15", SUM (DECODE (TO_CHAR (first_time, 'hh24'), '16', 1, 0)) "h16", SUM (DECODE (TO_CHAR (first_time, 'hh24'), '17', 1, 0)) "h17", SUM (DECODE (TO_CHAR (first_time, 'hh24'), '18', 1, 0)) "h18", SUM (DECODE (TO_CHAR (first_time, 'hh24'), '19', 1, 0)) "h19", SUM (DECODE (TO_CHAR (first_time, 'hh24'), '20', 1, 0)) "h20", SUM (DECODE (TO_CHAR (first_time, 'hh24'), '21', 1, 0)) "h21", SUM (DECODE (TO_CHAR (first_time, 'hh24'), '22', 1, 0)) "h22", SUM (DECODE (TO_CHAR (first_time, 'hh24'), '23', 1, 0)) "h23", ROUND (COUNT (1) / 24, 2) "Avg" FROM gv$log_history WHERE thread# = inst_id AND first_time > sysdate -7 GROUP BY TRUNC (first_time), inst_id, TO_CHAR (first_time, 'Dy') ORDER BY 1,2;
под катом — шапка для корретного отображения в sqlplus
Читать далее «Частота переключения REDO логов по часам»
Restore datafile on STANDBY from PRIMARY service
Не применяются арклоги на STANDBY сервере:
Reading datafile '/u01/app/oracle/oradata/databasename/superfile_edr_tbls01.dbf' for corruption at rdba: 0x054018ae (file 21, block 6318) Reread (file 21, block 6318) found same corrupt data (logically corrupt) Mon Jun 14 04:28:27 2021 Checker run found 1 new persistent data failures ERROR: ORA-00756 detected lost write of a data block Slave exiting with ORA-756 exception Mon Jun 14 04:28:32 2021 Errors in file /u01/app/oracle/diag/rdbms/databasename_bk/databasename/trace/databasename_pr02_22763.trc: ORA-00756: recovery detected a lost write of a data block ORA-10567: Redo is inconsistent with data block (file# 21, block# 6318, file offset is 51757056 bytes) ORA-10564: tablespace SUPERFILE_EDR_TBLS ORA-01110: data file 21: '/u01/app/oracle/oradata/databasename_bk/superfile_edr_tbls01.dbf' ORA-10561: block type 'TRANSACTION MANAGED DATA BLOCK', data object# 21388901
При живом PRIMARY восстановим файл с него на STB. Подключимся RMAN к стендбай серверу и дадим команду:
restore datafile 21 from service main;
…
alter database recover managed standby database disconnect from session;
profit
Добавление ASM диска
Администратор сервера подключил диск к серверу. Что же делать DBA?
Давайте просмотрим на наши блочные устройства:
# lsblk NAME MAJ:MIN RM SIZE RO TYPE MOUNTPOINT ... sdf 8:80 0 3T 0 disk └─sdf1 8:81 0 3T 0 part sdo 8:224 0 3T 0 disk sdd 8:48 0 3T 0 disk └─sdd1 8:49 0 3T 0 part ...
Запросы для АНДУ / Oracle UNDO queries
В редакцию поступил вопрос:
«ORA-30036: unable to extend segment by 8 in undo tablespace ‘UNDO_2’ at»
давайте поглядим, что можно посмотреть, может чего и увидим:
Мой любимый запрос, позволяет определить основного потребителя с разбивкой по инстансам (актуально для RAC конфигурации) :
select s.INST_ID,
s.sid,
s.username,
round( sum(ss.value) / 1024 / 1024 ,1)as undo_size_mb
from gv$sesstat ss
join gv$session s on s.sid = ss.sid AND s.INST_ID=ss.INST_ID
join gv$statname stat on stat.statistic# = ss.statistic#
where stat.name = ‘undo change vector size’
and s.type <> ‘BACKGROUND’
and s.username IS NOT NULL
group by s.INST_ID, s.sid, s.username
ORDER BY 4 desc
to be updated….
Get long lasting sql execution plans on email / Получить план долгих запросов на почту
This is upgraded version of nice utility procedure.
It sends execution plans if query is running longer then specific amount of time.
A trick was to split a big email into chunks as Oracle DB can’t send emails more than 32Kb.
Действия для SYS/SYS user actions:
1) Если у нас PDB, то выбираем необходимую:
PDBs action required:
alter session set container = OUR_PDB;
2) Обеспечиваем пользователя SYSTEM необходимыми правами. Хоть SYSTEM может селектить из вьюшек, гранты всё равно давать необходимо. Можно указать любого другого пользователя.
Provide required grants to system (or any other) user:
grant select on v_$sql to system; grant select on v_$sql_plan to system; grant select on v_$sql_plan_statistics_all to system; grant select on V_$SESSION to system; grant select on V_$instance to system;
3.1) Устанавливаем UTL_MAIL / Lets install UTL_MAIL:
Читать далее «Get long lasting sql execution plans on email / Получить план долгих запросов на почту»