Oracle a modo mio

Statistiche

exec dbms_stats.gather_schema_stats(ownname=>'HR',cascade=>true,options=>'GATHER STALE',method_opt=>'FOR ALL COLUMNS SIZE AUTO',degree=>8);
exec dbms_stats.gather_database_stats(cascade=>true,options=>'GATHER STALE',method_opt=>'FOR ALL COLUMNS SIZE AUTO',degree=>8);

System Statistics

set pagesize 200
set linesize 200
col SNAME for a20
col PNAME for a30
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

Molta attenzione all'utilizzo di risorse ed all'eventuale rallentamento che tale configurazione provoca. Sebbene Oracle dichiari che il calo di performances sia basso, va valutato caso per caso. In alcuni casi e per alcune procedure, si può verificare un calo di prestazioni notevole.

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

Con versione 12.2 le funzioni del parametro sono state divise in due parametri diversi, per cui non serve modificare il default:

optimizer_adaptive_plans            boolean	 TRUE
optimizer_adaptive_statistics       boolean	 FALSE

default bigfile

alter database set default bigfile tablespace;

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

svuota schema

--
alter session set recyclebin=off;
begin
 for c1 in (select owner,table_name from dba_tables where owner='&1')
  loop
   begin
    execute immediate 'drop table ' || c1.owner || '."' || c1.table_name || '" cascade constraints';
    exception
     when others then
      null;
   end;
  end loop;
end;
/

begin
 for c1 in (select owner,object_name,object_type from dba_objects where owner='&1' and object_type !='JOB')
  loop
   begin
    execute immediate 'drop ' || c1.object_type || ' ' || c1.owner || '."' || c1.object_name || '"';
    exception
     when others then
      null;
   end;
  end loop;
end;
/

begin
 for c1 in (select owner,object_name,object_type from dba_objects where owner='&1' and object_type ='JOB')
  loop
   begin
    dbms_scheduler.drop_job(c1.owner || '.' || C1.object_name);
    exception
     when others then
      null;
   end;
  end loop;
end;
/

set pagesize 200 linesize 200 trim on trims on
col object_name for a40
col object_type for a40
select object_name,object_type from dba_objects where owner='&1';

impdp

Via db link:

impdp "'/ as sysdba'" directory=DUMP01 logfile=impdp_dblink_01.log network_link=dblink_impdp01 exclude=statistics \
schemas=USER01 flashback_time='"to_timestamp_tz(systimestamp)"' transform=DISABLE_ARCHIVE_LOGGING:Y,OID:N

via dblink con parfile:

userid="/ as sysdba"
directory=DPUMP_DIR
logfile=impdp_tabella_singola.log
network_link=impdp_database_prod
tables=esempio.tabella01 
remap_schema=ESEMPIO:NUOVOSCHEMA1
remap_tablespace=DATI:NUOODATI01
flashback_time="systimestamp"
exclude=statistics,grant
transform=DISABLE_ARCHIVE_LOGGING:Y,OID:N

Opzioni possibili:

transform=table_compression_clause:"ROW STORE COMPRESS ADVANCED"
transform=lob_storage:securefile

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

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