Oracle a modo mio
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='*';
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;
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
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
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
usare kernel uek
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