Вы здесь

Activities calendar

20 ноября 2019

Dblinks

 
Dblink - интересный и в то же время неприятный объект. Дело в том, что в синтаксисе Oracle нет способа просто проверить, работает он или нет, если он находится в другой схеме. Доступ через точку, в отличие от других объектов, не работает, т.к. точка может являться частью имени dblink'а.

Архивлоги

Информация о режиме archivelog:
 
SQL> show parameter log;
 
Показывает список всех параметров, связанных с архивлогами — места нахождения, интервалы и прочее.
 
SQL> select log_mode from v$database;
 
Собственно информация о том, включён режим или нет.
 
SQL> archive log list;
 
Показывает более детальную информацию чем предыдущая команда — тут так же есть последовательности, подлежащие архивированию и место локации архивлогов.
 

LOB

Следующий код выгружает содержимое столбца lobs_tab.clob_col в файл clob_col1.txt, 
используя пакет DBMS_LOB для чтения CLOB и пакет UTL_FILE для записи во внешний файл:
 
declare
    l_clob clob;
    l_clob_len pls_integer;
 
    l_file      utl_file.file_type;
    l_buffer    varchar2(32767);
    l_amount    pls_integer;
    l_pos       binary_integer := 1;
begin

STANDBY READ ONLY

 
 
ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL;
ALTER DATABASE OPEN READ ONLY;
 
 
ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT FROM SESSION;
ALTER DATABASE RECOVER MANAGED STANDBY DATABASE USING CURRENT LOGFILE;

Удалить архивлоги

crosscheck archivelog all;
delete expired archivelog all; 
 
CONFIGURE ARCHIVELOG DELETION POLICY TO APPLIED ON STANDBY;
 
 
#!/bin/bash
sqlplus /nolog @/home/oracle/scripts/delete_archlog.sql
 
 
 
 
set linesize 200;                                                                                                                                            

Sequence Number using the SCN

set pages 100 lines 100
col name for a70
col first_change# for 9999999999999
col next_change# for 9999999999999
alter session set nls_date_format='DD-MON-RRRR HH24:MI:SS';
select name, thread#, sequence#, status, first_time, next_time, first_change#, next_change# from v$archived_log
where 1021438627876 between first_change# and next_change#;
 

Drop database

 
 ibsodoc
select substr(name,2,3) from v$datafile group by substr(name,2,3);
u16
u15
u11
u12
u13
u10
u14
 
 select name from v$tempfile;
/u10/oradata/ibsodoc/temp01.dbf
 
 select MEMBER from V$LOGFILE;
/urdo/oradata/ibsodoc/redo06.log
/urdo/oradata/ibsodoc/redo05.log
/urdo/oradata/ibsodoc/redo04.log
/urdo/oradata/ibsodoc/redo03.log
/urdo/oradata/ibsodoc/redo02.log

Инкрементал снять и накатить на стенбай

SQL> select to_char(min(checkpoint_change#)) checkpoint_change# from v$datafile;
 
1022165566632
 
 
alter database create standby controlfile as '/uarch/ehd4/standby_ehd4_control.ctl';
 
backup device type disk incremental from scn 1022165566632 database format '/uarch/ehd4/Inc_backup_%U';
 
scp /uarch/ehd4/standby_ehd4_control.ctl oracle@ehd-db4:/uarch/ehd4/
 

Статистика быстро

Для сбора статистики используем процедуру
 
dbms_stats.gather_table_stats('CREDMON','GOAL_IMPORT');
Если в базе данных имеются таблицы, которые часто обновляются, то частый сбор статистики может негативно повлиять на производительность базы данных. Для того, чтоб исключить объекты из автоматического или любого другого сбора статистики можно «закрепить» ее статистику:
 
begin
  dbms_stats.gather_table_stats('SCOTT','EMP');
  dbms_stats.lock_table_stats('SCOTT','EMP');
end;

Ожидания

system_wait
 
set lines 100
set pages 100
clear breaks
clear columns
 
column c1 heading 'Event' format A30 wrap
column c2 heading 'Total Waits' format 999,999,999
column c3 heading 'Wait Time(s)' format 999,999
column c4 heading 'Timeouts' format 999,999,999
column c5 heading 'Avg. Wait (s)' format 99.999
 
select event c1,
total_waits c2,
time_waited/100 c3,
total_timeouts c4,
average_wait/100 c5

бысто про flashback

 
 
db_recovery_file_dest_size
 
на старом варианте
 
db_flashback_retention_target        integer     1440
db_recovery_file_dest_size           big integer 1400G
 
новом
 
db_flashback_retention_target        integer     1440
db_recovery_file_dest_size           big integer 900G
db_recovery_file_dest                string      /archlog/rtdm
 
$ sqlplus / as sysdba
 
shutdown immediate;

Загрузка базы

=======================   ресурсы
 
select * from v$resource_limit
 
 
изменение статистики по задержкам  например db file sequential read
 
select  to_char(ss.end_interval_time,'dd.mm.yyyy hh24:mi'),
round((time_waited_micro-lag(time_waited_micro,1) over (order by e.snap_id))/(total_waits-lag(total_waits,1) over (order by e.snap_id))/1000) dr from dba_hist_system_event  e, dba_hist_snapshot ss
  where
  ss.snap_id=e.snap_id

Заголовки файлов

 
set lin 300 pages 0
column checkpoint_change# format 999999999999999999999;
 
 
 
select file#,status,recover,fuzzy,checkpoint_change#,checkpoint_time,resetlogs_time from v$datafile_header;
 
select min(checkpoint_change#) checkpoint_change# from v$datafile_header;
 
 
 
 
FILE# STATUS REC FUZ CHECKPOINT_CHANGE# CHECKPOIN RESETLOGS
---------- ------- --- --- ------------------ --------- ---------

Move datafile

===================================   12 oracle 
 
select 'alter database move datafile '''||name||''' to ''/data/rtdm/rtdm_data_'||FILE#||'.dbf'';' from v$datafile where  FILE#<51;
 
select 'alter database move datafile '''||name||''' to ''/data/rtdm/system.dbf'';' from v$datafile where name like '%system%';
select 'alter database move datafile '''||name||''' to ''/data/rtdm/sysaux.dbf'';' from v$datafile where name like '%sysaux%';

Память

    Идентифицировать потребление памяти со стороны Oracle выполняется с помощью служебных view V$PROCESS и V$PROCESS_MEMORY - по процессам, 
V$SGASTAT и V$PGASTAT - кумулятивно для SGA и PGA соответственно.
 
SET linesize 80;
    COLUMN pid FORMAT 999999;
    COLUMN spid FORMAT a7;
    COLUMN program FORMAT a25;
    SELECT pid, spid, program, pga_used_mem, pga_alloc_mem, pga_max_mem FROM v$process;
 
 

Kill session

 
 
 
create user  killer identified by  killer;
grant connect, create procedure to killer;
grant alter system to killer;
 
connect killer/killer
 
create procedure kill(sid number, serial# number) as
  begin
  execute immediate 'alter system kill session '''||sid||','||serial#||'''';
  end;
  /
grant execute on kill to MBLOG;      
 
connect / as sysdba 
 

dbms_sqltune grant

grant select on sys.v_$sql_monitor to CDI;
grant select on sys.NLS_SESSION_PARAMETERS to CDI;
grant select on sys.DBA_HIST_REPORTS_DETAILS to CDI;
grant select on sys.kopm$  to CDI;
grant select on sys.X$KSPPI  to CDI;
grant select on SYS.X$KSPPCV to CDI;
grant select on sys."_REPORT_COMPONENT_OBJECTS" to CDI;
grant select on sys.REPORT_COMPONENTS to CDI;
grant select on sys."_REPORT_FORMATS"  to CDI;
grant select on sys."_REPORT_COMPONENT_OBJECTS"  to CDI;

Запросы текущие

select m.LAST_REFRESH_TIME, m.sql_text
,dbms_sqltune.report_sql_monitor(sql_id => m.sql_id, type => 'HTML', report_level => 'ALL') AS report
from  sys.v_$sql_monitor m
where m.sid = 1345 --and m.session_serial# = :serial#
order by m.LAST_REFRESH_TIME desc
 
__________________________________________________________
SELECT * FROM
(SELECT
    sql_fulltext,
    sql_id,
    first_load_time,
    last_load_time
FROM    v$sql

Планы

EXPLAIN PLAN FOR select ...;
 
 
select * from table(dbms_xplan.display);
 
 
 
 
Можно посмотреть планы вот так:
 
select * from table(dbms_xplan.display_cursor('4aj2y82rdfx97',null,'basic +PEEKED_BINDS'))
 
И сравнить индексный доступ и fullscan. Возможно, на NODE есть гистограмма, которой там быть не должно.
 

Профилирование запросов

 
 
 
@/app/oracle/product/12.1.0/db/rdbms/admin/proftab.sql
 
Running "proftab.sql" creates following objects:
 
PLSQL_PROFILER_RUNS - Run-specific information for the PL/SQL profiler.
PLSQL_PROFILER_UNITS - Information about each library unit in a run.
PLSQL_PROFILER_DATA - Accumulated data from all profiler runs.

Битый блок

SQL> select name, open_mode, log_mode from v$database;
 
NAME      OPEN_MODE            LOG_MODE
--------- -------------------- ------------
CR        READ WRITE           ARCHIVELOG
 
SQL> SELECT * FROM v$database_block_corruption;
 
     FILE#     BLOCK#     BLOCKS CORRUPTION_CHANGE# CORRUPTIO
---------- ---------- ---------- ------------------ ---------
       271    2482861          1                  0 FRACTURED
 
 

Блокировки

select * from v$session_wait sw, v$session s
where s.sid = sw.sid and event = 'enqueue'
 
 
 
select event, status, service_name, wait_class, state, seconds_in_wait  from v$session where sid=1098;
 
 
 
 
 
Можете посмотреть сессию SID=1454 на базе RBOWRK?
Сессия висит с ожиданием LIBRARY CACHE PIN и блокирует другие, в чем может быть проблема?
 
 
 

Курсоры

 
with c as (
select c.sid, c.user_name, count(*)
  from v$open_cursor c
group by c.sid, c.user_name
having count(*) > 50) 
select c.*, s.program from c, v$session s
  where c.sid = s.sid
order by 3 desc
 
select count(*) sessions from v$session;
select count(*) processes from v$process;
select count(*) cursors from v$open_cursor;
 
это посмотреть (у меня уперлось не в курсоры а в сессии)

Трассировка

alter system set events '1426 trace name errorstack level 3'
 
 
или
 
begin
execute immediate 'alter session set timed_statistics=true';
execute immediate 'alter session set tracefile_identifier=''FCS_DATA_MART'''; --FCS_GET_OBJ название трейса
execute immediate 'alter session set events ''10046 trace name context forever, level 12''';
проц какой-нибудь
execute immediate 'alter session set events ''10046 trace name context forever, level 0''';
end;

AWR все

 
дать права тут
 

Необходимые привилегии

Выбить план

 
select ADDRESS, HASH_VALUE from V$SQLAREA where SQL_Id='dmx08r6ayx800';
 
 
ADDRESS                            HASH_VALUE
---------------------------------- ----------
0000000693E4C268                   2515443712
 
 
 
exec DBMS_SHARED_POOL.PURGE ('0000000693E4C268,2515443712','C');
 

Фиксация плана

 
explain plan for select t.* from t_plan_test t where t.n1 = 100;
select * from table(dbms_xplan.display);
 
 
Во-первых, необходимо запомнить план первого запроса, взяв его sql_id и plan_hash_value:
 
begin

Rename database

создать spfile
 
alter user system IDENTIFIED BY WSXwsx;
 
SHUTDOWN IMMEDIATE
STARTUP MOUNT
 
nid TARGET=SYSTEM DBNAME=PSISBL
 
 
поменять профиль
 
export ORACLE_SID=CR
STARTUP MOUNT
ALTER SYSTEM SET DB_NAME=PSISBL SCOPE=SPFILE;
SHUTDOWN IMMEDIATE;
STARTUP MOUNT;
ALTER DATABASE OPEN RESETLOGS;
 

ASL

 
 
 
SET LINESIZE 300;
SET PAGES 300;
COLUMN host FORMAT A30;

Место в тейблспейсах

 
column "Схема"   format a15;
column "Таблица"   format a15;
 
SELECT a.owner "Схема",
       a.table_name "Таблица",
       b.bytes "Размер (Мб)",
       TRUNC((a.blocks * 100) / b.blocks) "Занято(%)",
       b.extents "Экстентов"
FROM dba_tables a,
     (
        SELECT owner, segment_name, SUM(bytes)/1024/1024 bytes,
               SUM(blocks) blocks, COUNT(*) extents
          FROM dba_extents

Полезные запросы ASM

select   substr(d.name,1,4),d.group_number, sum(d.total_mb), sum(d.free_mb) 
from v$asm_diskgroup dg,v$asm_disk d 
where d.group_number = dg.group_number 
group by substr(d.name,1,4),d.group_number;
 
 
 
select dg.name, d.name, d.path, d.total_mb, d.free_mb
  from v$asm_diskgroup dg, 
       v$asm_disk d
 where d.group_number = dg.group_number
 order by free_mb, 1, 2;
 
 
 
 
 
 
 

Диагностика

select protection_level from v$database;
 
 
show parameter standby_file_management ;
select group#, bytes from v$standby_log;
select name, open_mode, log_mode from v$database;
select recovery_mode from v$archive_dest_status;
 
 select flashback_on from v$database;
 
find  . -name "alert*.log" -exec ls -l -h {} \;
 
 
select name from v$archived_log where rownum <10;