Oracle a modo mio: differenze tra le versioni
Riga 1: | Riga 1: | ||
==aggiunta service names== |
|||
<pre> |
|||
-- |
|||
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 || '.engie.com' from v$spparameter where name='db_name' |
|||
union |
|||
select value || '.cofely-gdfsuez.com' from v$spparameter where name='db_name' |
|||
); |
|||
spool off |
|||
@/tmp/aa.sql |
|||
!rm /tmp/aa.sql |
|||
alter system register; |
|||
</pre> |
|||
==audit asm su syslog== |
==audit asm su syslog== |
Versione delle 15:32, 9 ago 2019
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 || '.engie.com' from v$spparameter where name='db_name' union select value || '.cofely-gdfsuez.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