Oracle a modo mio

Da Emigar.
Jump to navigation Jump to search

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='*';

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 undo temp

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

default bigfile

alter database set default bigfile tablespace;

profilo scadenza password

recyclebin cancellazione a tempo

pulizia alert e diag con adrci

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 consistente

flashback_time='"to_timestamp_tz(systimestamp)"'

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

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

usare kernel uek

preallocazione spazio

alter system set "_enable_space_preallocation"=0;

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