Oracle a modo mio

system statistics

set pagesize 200
set linesize 200
col PVAL2 for a20
select * from sys.aux_stats$;

-- raccolta timed statistics
exec dbms_stats.gather_system_stats(gathering_mode=>'START');
exec dbms_stats.gather_system_stats(gathering_mode=>'STOP');
-- in minuti
exec dbms_stats.gather_system_stats(gathering_mode=>'INTERVAL',interval=>30);

-- imposta valori a mano
-- importanti da impostare in modalità workload:
--   SREADTIM (single block read in ms)
--   MREADTIM (multiple block read in ms)
--   MRBC     (average multiblock read count in blocks)
exec dbms_stats.set_system_stats('SREADTIM',10);
exec dbms_stats.set_system_stats('MREADTIM',20);
exec dbms_stats.set_system_stats('MBRC',16);
exec dbms_stats.set_system_stats('MBRC',64);

-- intervento drastico:
exec dbms_stats.set_system_stats('SREADTIM',1);
exec dbms_stats.set_system_stats('MREADTIM',100);
exec dbms_stats.set_system_stats('MBRC',64);

-- per cancellare e tornare in modalità noworkload
exec dbms_stats.delete_system_stats();

-- per esportare le system statistics in una tabella
exec dbms_stats.export_system_stats(stattab=>'TABLE1');
-- per importare le system statistics da una tabella
exec dbms_stats.import_system_stats(stattab=>'TABLE1');
-- oppure
exec dbms_stats.restore_system_stats(stattab=>'TABLE1');
-- la tabella va create con
exec dbms_stats.create_stat_table( ownname => 'SYS',stattab =>'TABLE1',tblspace => 'SYSAUX');

aggiunta service names

--

set pagesize 0
set linesize 200
set feedback off
set trim on trims on

spool /tmp/aa.sql
select 'alter system set service_names=''' || listagg(value,''',''') within group(order by value) || ''' scope=both sid=''*'';' from 
( select value from v$spparameter where name='service_names'
  union
  select value from v$spparameter where name='db_name'
  union
  select value || '.mydomain.com' from v$spparameter where name='db_name'
  union
  select value || '.otherdomain.com' from v$spparameter where name='db_name'
);

spool off
@/tmp/aa.sql
!rm /tmp/aa.sql

alter system register;

audit asm su syslog

alter system set AUDIT_SYS_OPERATIONS=TRUE scope=spfile sid='*';

alter system set AUDIT_SYS_OPERATIONS=FALSE scope=spfile sid='*';
alter system set AUDIT_SYSLOG_LEVEL='local0.info' scope=spfile sid='*';

audit database su syslog

alter system set AUDIT_SYS_OPERATIONS=FALSE scope=spfile sid='*';
alter system set AUDIT_SYSLOG_LEVEL='local6.info' scope=spfile sid='*';
alter system set AUDIT_TRAIL='OS' scope=spfile sid='*';

Per quali eventi generare record di audit:

audit session;
audit connect whenever not successful;

unified audit database

Su syslog solo dalla 18 in poi:

alter system set UNIFIED_AUDIT_SYSTEMLOG = 'LOCAL6.NOTICE' scope=spfile sid='*';

Con la versione 12 solo su tabelle;

Per scrittura immediata audit:

BEGIN
 DBMS_AUDIT_MGMT.SET_AUDIT_TRAIL_PROPERTY(
  DBMS_AUDIT_MGMT.AUDIT_TRAIL_UNIFIED,
  DBMS_AUDIT_MGMT.AUDIT_TRAIL_WRITE_MODE, 
  DBMS_AUDIT_MGMT.AUDIT_TRAIL_IMMEDIATE_WRITE);
END;
/

Per leggere le policy:

set linesize 2000
set pagesize 200
col policy_name for a20
col audit_condition for a20
col audit_option for a40
col object_schema for a20
col object_name for a20
select * from AUDIT_UNIFIED_POLICIES where policy_name='ORA_SECURECONFIG';

Per intercettare logon e logoff:

CREATE AUDIT POLICY LOG_ON_OFF ACTIONS LOGON,LOGOFF;
AUDIT POLICY LOG_ON_OFF;

Per leggere i risultati:

select audit_type,event_timestamp,action_name,object_name from UNIFIED_AUDIT_TRAIL order by event_timestamp desc;

db block checking

alter system set db_block_checking='FULL' scope=spfile sid='*';
alter system set db_block_checksum='FULL' scope=spfile sid='*';

11.2

alter system set db_block_checking='FULL' scope=both sid='*';
alter system set db_block_checksum='FULL' scope=both sid='*';

12c

Ottimizzazioni 12cR1

undo temp

alter system set temp_undo_enabled=true scope=both sid='*';

optimizer_adaptive_features

alter system set optimizer_adaptive_features=false scope=both sid='*';

default bigfile

alter database set default bigfile tablespace;

checkpoint con SE

Checkpoint ad ogni switch di log:

  select max(BYTES/BLOCKSIZE) from v$log;

   MAX(BYTES/BLOCKSIZE)
   --------------------
	         262144


  alter system set log_checkpoint_interval=262144 scope=both;

Checkpoint almeno ogni 5 minuti

 alter system set log_checkpoint_timeout=600 scope=both;

incrementare SDU e BUF_SIZE

per incrementare SDU bisogna agire sul listener.ora:

LISTENER =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (SDU = 32768)
      (SEND_BUF_SIZE=32768)
      (RECV_BUF_SIZE=32768)
      (ADDRESS = (PROTOCOL = TCP)(HOST = )(PORT = 2483))
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC2483))
    )
  )

Nel database:

  alter system set local_listener='(DESCRIPTION=(SDU=32768)(TDU=32768)(ADDRESS=(PROTOCOL=TCP)(HOST=192.168.1.1)(PORT=2483)))' scope=both;

expdp

Eliminare segmenti vuoti prima della export (solo Enterprise Edition):

exec DBMS_SPACE_ADMIN.DROP_EMPTY_SEGMENTS();

Consistente:

flashback_time='"to_timestamp_tz(systimestamp)"'

Evitare errori:

exclude=statistics

impdp

Via db link:

impdp "'/ as sysdba'" directory=DUMP01 logfile=impdp_dblink_01.log network_link=dblink_impdp01 exclude=statistics \
flashback_time='"to_timestamp_tz(systimestamp)"' transform=DISABLE_ARCHIVE_LOGGING:Y

clean impdp/expdp

SELECT 'drop table "' || owner_name || '"."' || job_name || '";' FROM dba_datapump_jobs where state = 'NOT RUNNING';

Su Linux

verificare con quale gid girano i processi oracle

ps axo stat,euid,ruid,tty,tpgid,sess,pgrp,ppid,pid,pcpu,comm,group,egroup,rgroup,sgroup,fgroup

ps -u oracle -o stat,euid,ruid,tty,tpgid,sess,pgrp,ppid,pid,pcpu,comm,group,egroup,rgroup,sgroup,fgroup | more

Va preso il gid del gruppo egroup o del gruppo rgroup?

attivare hugepages

in /etc/sysctl.conf (su EL <=5.x) o /etc/sysctl.d/99-oracle.conf (su EL >= 6.x)

### Hugepages
vm.hugetlb_shm_group=54322
vm.nr_hugepages=1024

disattivare transparent hugepages

disable transparent hugepages https://docs.oracle.com/database/121/CWLIN/memry.htm

[ -f /sys/kernel/mm/redhat_transparent_hugepage/enabled ] && echo never > /sys/kernel/mm/redhat_transparent_hugepage/enabled

preallocazione spazio

Disattivare completamente:

alter system set "_enable_space_preallocation"=0;

oppure ridurre la percentuale:

alter system set "_kttext_warning"=5;

SMCO (Space Management Coordinator) For Autoextend On Datafiles And How To Disable/Enable (Doc ID 743773.1)

AUTOEXTEND Grows To Full Size Without Reason (Doc ID 1459097.1)

Master Note: Overview of Oracle Segment Storage (Doc ID 1491960.1)

http://ksun-oracle.blogspot.com/2015/12/oracle-bigfile-tablespace-pre.html