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

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 ;

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;

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

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