Oracle 11g: wat u moet weten
Bram van der Vos www.axisintoict.nl
[email protected]
Agenda
New Features: de algemene richting Parameters Tracing en Logging Securtity gebasseerde aspecten Deferred Segment Creation Adaptive Cursor Sharing Export utility Automatic Memory Management Health monitoring ….
Er is dus erg veel nieuws.....
Grid Infra Layer ASM Oracle Restart RMAN features Deferred Segment Creation ACL op diverse packages Data Guard Active Data Guard Flashback technologie SQL syntax (standaards) ….
Deprecated & Obsolete
Oracle Enterprise Manager DatabaseControl ● ACTIVE_INSTANCE_COUNT ● *_DUMP_DEST (vervangen) ● COMMIT_WRITE(COMMIT_LOGGING /COMMIT_WAIT) ● CURSOR_SPACE_FOR_TIME ● PARALLEL_IO_CAP_ENABLED (parallel_degree_limit ) ● REMOTE_OS_AUTHENT ● SQL_TRACE (gebruik DBMS_MONITOR and DBMS_SESSION) ● PLSQL_NATIVE_LIBRARY_SUBDIR_COUNT ● PLSQL_NATIVE_LIBRARY_DIR ● PLSQL_DEBUG (nu: PLSQL_OPTIMIZE_LEVEL) ● PLSQL_V2_COMPATIBILITY ● RESOURCE_MANAGER_CPU_ALLOCATION ● JOB_QUEUE_PROCESSES voor DBMS_SCHEDULER_JOB jobs ●
Nieuwe parameters
CONTROL_MANAGEMENT_PACK_ACCESS ● MEMORY_TARGET ● MEMORY_MAX_TARGET ● SEC_PROTOCOL_ERROR_FURTHER_ACTION ● SEC_PROTOCOL_ERROR_TRACE_ACTION ● SEC_CASE_SENSITIVE_LOGON ● DEFFERED_SEGMENT_CREATION ●
V$FLASH_RECOVERY_AREA_USAGE
FLASH Recovery Area is nu FAST Recovery Area ● Replaced by V$RECOVERY_AREA_USAGE ●
Automatic Diagnostic Repository
Oracle 10g en eerder: ●
● ●
BACKGROUND_DUMP_DEST USER_DUMP_DEST CORE_DUMP_DEST
Vanaf Oracle 11g ●
DIAGNOSTIC_DEST
Een geconsolideerde trace omgeving
Automatic Diagnostic Repository
Éen geconsolideerde trace omgeving ● Centraal beheerd ● Alle produkten ➢ Veel XML gebasseerd ➢ Denk aan je standaard scripts ➢ *.trm files • Nieuwe componenten •Nieuwe functionaliteit
ADR-structuur ADR Base
DIAGNOSTIC_DEST
ADR
rdbms diag
rdbms rdbms rdbms rdbms rdbms
...
...
dbname
sid
...
V$DIAG_INFO
alert sid
trace
cdump
hm
incident
….
Waar vind ik nu mijn files?
V$DIAG_INFO
select * from v$diag_info; NAME ----------------------------Diag Enabled ADR Base ADR Home Diag Trace Diag Alert Diag Incident Diag Cdump Health Monitor Default Trace File Active Problem Count Active Incident Count .
VALUE --------------------------------------------------------TRUE C:\APP\ORACLE C:\APP\ORACLE\diag\rdbms\orcl\orcl C:\APP\ORACLE\diag\rdbms\orcl\orcl\trace C:\APP\ORACLE\diag\rdbms\orcl\orcl\alert C:\APP\ORACLE\diag\rdbms\orcl\orcl\incident C:\app\oracle\diag\rdbms\orcl\orcl\cdump C:\APP\ORACLE\diag\rdbms\orcl\orcl\hm C:\APP\ORACLE\diag\rdbms\orcl\orcl\trace\orcl_ora_7876.trc 1 1
ADRCI–ADR Command Interpreter
ADRCI :Commando's adrci> help HELP [topic] Available Topics: CREATE REPORT ECHO EXIT HELP HOST IPS PURGE RUN SET BASE SET BROWSER SET CONTROL SET ECHO SET EDITOR SET HOMES | HOME | HOMEPATH SET TERMOUT SHOW ALERT SHOW BASE SHOW CONTROL SHOW HM_RUN SHOW HOMES | HOME | HOMEPATH SHOW INCDIR SHOW INCIDENT SHOW PROBLEM SHOW REPORT SHOW TRACEFILE SPOOL
Purging ADR data
PURGE commando default lifecycle. – sommige produkten automatisch. – incidents vs dump files. SHOW CONTROL: default purging policies Syntax and Description purge [-i {id | start_id end_id} | -age mins [-type {ALERT|INCIDENT|TRACE|CDUMP|HM}]]
Security Aspecten
Algemeen Passwords Auditing Netwerk aanpassingen
DBCA: aangepast gedrag
• DBCA geen Default Security Settings
– Automatisch audit options – Automatisch password policies – Veel extra settings
Default Accounts
Oracle heeft veel Default accounts – Scott – Example users – System users (OUTLN) Standaard Account zijn een security risico – Locked & Expired – Wijzig het Password – Controleer DBA_USERS_WITH_DEFPWD
Oracle Password zaken Passwords miv Oracle 11g Case-sensitive ● Upgrade: na 'alter user' ● Init.ora: SEC_CASE_SENSITIVE_LOGON ● Orapwd: ignorecase
●
Multibyte characters zonder quotes – Salt gebruikt in de hash – Hashed password niet meer in dba_users – 'more secure hash algorithm'
●
Default Profile:
●
PASSWORD_LIFE_TIME PASSWORD_GRACE_TIME PASSWORD_REUSE_TIME PASSWORD_REUSE_MAX FAILED_LOGIN_ATTEMPTS PASSWORD_LOCK_TIME PASSWORD_VERIFY_FUNCTION
180 7 UNLIMITED UNLIMITED 10 1 NULL
Netwerk Parameters
Protect against DoS attacks: – SEC_PROTOCOL_ERROR_FURTHER_ACTION • CONTINUE|(DELAY,integer)(DROP,integer)
– SEC_PROTOCOL_ERROR_TRACE_ACTION • NONE | TRACE | LOG | ALERT
Audited Privileges AUDIT_TRAIL default naar DB (DBCA) ON SUCCESS & FAILURE BY ACCESS ALTER/CREATE/DROP ROLE
CREATE PUBLIC DATABASE LINK
AUDIT SYSTEM
ALTER/DROP/CREATE ANY TABLE
ALTER/CREATE/DROP USER
GRANT ANY PRIVILEGE
ALTER SYSTEM POLICY
CREATE ANY LIBRARY
CREATE SESSION
EXEMPT ACCESS POLICY
ALTER/DROP PROFILE ALTER DATABASE ALTER/CREATE/DROP ANY PROCEDURE GRANT ANY ROLE
GRANT ANY OBJECT CREATE ANY JOB CREATE EXTERNAL JOB
Connect Role (10.2)
CONNECT Role update tov vroeger ● Miv Oracle10 Release 2 (10.2) ● CONNECT role ALLEEN CREATE SESSION privilege. ● Risico: Applicatie problemen. SELECT grantee FROM dba_role_privs WHERE granted_role = 'CONNECT' and grantee NOT IN ('SYS' » , 'OUTLN' » , 'SYSTEM' » , 'CTXSYS' ,
..);
Autotask Framework
● ●
●
● ●
Optimizer Statistics Gathering (On) Segment Advisor (On) Automatic SQL Tuning Advisor (On)
DBA_AUTOTASK_CLIENT DBMS_AUTO_TASK_ADMIN
BEGIN 2 DBMS_AUTO_TASK_ADMIN.disable( 3 client_name => 'auto optimizer stats collection', 4 operation => NULL, 5 window_name => 'MONDAY_WINDOW'); 6 END;
Object Statistics
Wanneer te genereren? Hoe genereren (Histogrammen) Default Settings Selectiviteit Beschikbaarheid
Statistics Settings
Statistic Management
Publish Statistics ● OPTIMIZER_USE_PENDING_STATISTICS=TRUE ● DBA_TAB_PENDING_STATS
●
●
dbms_stats.publish_pending_stats
Stale Percentage ●Invalidation van executieplan. ● (un)locken van statistieken ●Extended Statistics – Multicolumn – Functions ●
Unlimited tablespace
Komt nog steeds mee met resource role ● Kan apart revoked worden ● Maar wat gebeurt er dan? ●
●
Miv 11g: ●Alle quotas op 0 ● Dus zelf weer expliciet zetten ● Oude settings dus geen effect meer
Van exp-imp naar expdp-impdp Traditionele export/import is oud ● Exp is officieel desuported sinds 10.2 (345187.1) ● Alleen nog aanwezig voor migratie doeleinden ●
●
Datapump export en import is de nieuwe manier ● PL/SQL gebasseerd ● In de database ● Flexibel ● Maar wel andere parameters ● 11.2: compatibility mode ● 11.2: enkele nieuwe prettige opties.
Deferred Segment Creation
Init.ora: DEFERRED_SEGMENT_CREATION
Extent pas gealloceerd bij insert DBMS_SPACE_ADMIN voor PL/SQL support
Unusable Indexes
< 11.2
11.2
Adaptive Cursor Sharing
Default Functionaliteit
Lost een historisch Oracle Probleem op
Adaptive Cursor Sharing is: – Andere executieplannen bij verschillende binds – Omdat een executie plan niet altijd voldoet – Maar zeker niet te veel – Werk automatisch
Adaptive Cursor Sharing
Select * from emp Where emp#-:b1 And col=b#2
Automatic Memory Management
9i pga_aggregate_target 10g ASMM – sga_target – sga_max_size – __*_pool_size 11g AMM – memory_target – memory_max_target
Memory_max_target
Memory_target
Sga_target
Shared Pool
Shared Pool
Large Pool
Buffer Cache
Java Pool
Large Pool
Buffer Cache
Java Pool
AMM parameters
shared_pool large_pool db_cache java_pool streams_pool sga_max_size sga_target memory_max_target
memory_target
pga_aggreget_target
DB_KEEP_CACHE db_recycle_cache db_nk_cache result_cache log_buffer
Health Monitor
●
●
Verzameling diagnostic checks op de database ● Interactief door de DBA ● Reactief door de DB in geval van een 'error' ● Open en closed checks ● Sterk geïntegreerd met database Kernel Resultaat in ADR en V$HM_* views
Check Types:
●
●
Check for health monitor functionality Checks integrity of all database files Checks integrity of a control file block Checks integrity of a data file block Checks integrity of redo log content Checks logical content of a block Checks a transaction for corruptions Checks integrity of an undo segment Checks control file in NOMOUNT mode Checks dictionary integrity Diagnose allocation failure Checks control file in mount mode Checks a multiplexed copy of the control file Checks all datafiles in the database Checks a data file Checks a tablespace Checks all members of a log group
●
●
●
●
DB Structure Integrity Check Integriteit (data)file (mount/open) Data Block Integrity Check vergelijk dbv/RMAN VALIDATE Redo Integrity Check accessibility en corrupties Undo Segment Integrity Check logische corrupties Transaction Integrity Check als undo voor 1 transactie Dictionary Integrity Check ● dictionary voor elk object. ● cross-row level check ● Object relationship check
Running Checks
● ●
DBMS_HM of EM framework Starten:
BEGIN DBMS_HM.RUN_CHECK('Dictionary Integrity Check', 'my_run'); END;
●
Resultaat bekijken:
BEGIN SELECT DBMS_HM.GET_RUN_REPORT('my_run') FROM DUAL; END; ADRCI: – –
create report hm_run run_name show report hm_run run_nam
V$HM_findings
Samenvatting
• Oracle heeft flink getimmerd • ADR gaat impact hebben • Geniet van Adaptive Cursor Sharing • Reduceer storage met deferred segments • Ga slim om met Unusable indexes • Denk aan je security • Kies bewust voor je memory model • Neem de Health_checks op in je analyse tools ●….
