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;
profilo scadenza password
recyclebin cancellazione a tempo
pulizia alert e diag con adrci
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
via dblink con parfile:
userid="/ as sysdba" directory=DPUMP_DIR logfile=impdp_tabella_singola.log network_link=impdp_daol_prod tables=esempio.tabella01 remap_schema=ESEMPIO:NUOVOSCHEMA1 remap_tablespace=DATI:NUOODATI01 flashback_time="systimestamp" exclude=statistics,grant transform=DISABLE_ARCHIVE_LOGGING:Y
systimestamp
ritorna sempre un timestamp with time zone
.
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
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