11g The Perfection of a Masterpiece Christo Kutrovsky The Pythian Group 2007 October A presentation about new features of 11g you may not have noticed.
Download ReportTranscript 11g The Perfection of a Masterpiece Christo Kutrovsky The Pythian Group 2007 October A presentation about new features of 11g you may not have noticed.
11g The Perfection of a Masterpiece Christo Kutrovsky The Pythian Group 2007 October A presentation about new features of 11g you may not have noticed Who Am I Joined Pythian in 2003 Became team lead for one of Pythian's service delivery teams in 2006 Notable clients: Palm Coast Data, Freshdirect.com Presented at Collaborate '06, '07, RMOUG Special interest in 11g, RAC, Disk IO performance, and memory Pythian's delegate to the 11g beta, participated at the camp level (two visits) Who is Pythian Provides turnkey global data architecture and operations teams on a linear-cost-to-effort basis Founded in 1997, headquartered in Ottawa, Canada, with offices in India and Australia Supporting almost 100 clients worldwide and more than 600 production databases Almost 50 production engineers engaged in client service delivery Broad data infrastructure expertise primarily focused on Oracle, Microsoft SQL Server, and MySQL on enterprise hardware Agenda 11g – an Evolution ASM – the missing pieces RMAN – easier then ever Standby DBs – more usable SQL & PL/SQL – improvements out of the box Security – out of the box An Evolution A lots of areas have been polished User feedback taken into account DBA feedback taken into account for the first time? ASM in 11g ASM – Rolling updates Can upgrade or patch ASM RAC instances without shutting down all nodes ALTER SYSTEM START ROLLING MIGRATION TO 11.2.0.0.0; Limited “services” from ASM only mount/open ASM – Variable AU The allocation units are variable size, like LMT tablespaces Data sits closer together and can be read in bigger chunks Reduces SGA memory for metadata for large files ASM - asmcmd “cp” command find command essential for cleanup remap – repairs blocks including from ASM to OS including support for remote instances for non-raid disks asmcmd -p – current directory RMAN in 11g RMAN – configure CONFIGURE COMPRESSION ALGORITHM ‘type’; Archivelog deletion policy zlib – less cpu (faster) Bzip2 – more compression applied/shipped on standby DB_UNIQUE_NAME configure for another db RMAN - backup section size can split big files into “sections” keep until restore point parameter can keep only logs needed to make backup consistent RMAN – repair failure RMAN> list failure; missing files corrupted files or blocks RMAN> advise failure; RMAN> repair failure; RMAN – list failure RMAN> LIST FAILURE; List of Database Failures ========================= Failure ID Priority Status Time Detected Summary ---------- -------- --------- ------------- ------142 HIGH OPEN 23-APR-07 One or more non-system datafiles are missing 101 HIGH OPEN 23-APR-07 Datafile 1: '/disk1/oradata/prod/system01.dbf' contains one or more corrupt blocks RMAN - duplicate from active database to restore point for ease of use password file, spfile no need for backup for completeness, a single command tablespace allows for only 1 tablespace to be duplicated RMAN – duplicate standby Can use “backup controlfile” instead of “standby controlfile” RMAN - list list failure; list restore point all; list recoverable to restore point; RMAN - other recover …exclude flashback log backup optimization committed undo is not backed up option available for enforcing UNDO_RETENTION backup of read only tablespaces now possible Standby Standby – running backups Can have persistent configuration Can have block change tracking Can be associated with production database On the fly compression for archived redo only for gap resolution – need to verify Standby – more uses Can be open read only and updated in real time Can be open read write, while still accepting logs from production ALTER DATABASE CONVERT TO SNAPSHOT STANDBY ALTER DATABASE CONVERT TO PHYSICAL STANDBY; Standby – more uses 2 RMAN aware network copies from standby to production rman target sys@standby auxiliary sys@prod BACKUP AS COPY DATAFILE 2 AUXILIARY FORMAT ‘/prod_disk/file.dbf’ SQL & PL/SQL Read Only table Read only tables now available alter table X read only; alter table X read write; Simple, insignificant, but needed Invisible indexes alter index SHOULD_I_DROP invisible; alternative to dropping can be used for testing alter session set optimizer_use_invisible_indexes = true; Default columns Default columns with not null maintained in data dictionary takes no space instant add DDL can now wait All DDLs can wait This is the new default ddl_lock_timeout default set to wait 0 seconds alter session set ddl_lock_timeout=5; create index on small_but_busy_table… Virtual Columns create table users ( display_name varchar2(30), name as (upper(display_name)) ); insert into users (display_name) values (‘test’); Amongts other things, IOTs not supported PL/SQL – sequence in variable declare v number := a_sequence.nextval; begin … end; Statistics gathering Can gather without applying DBMS_STATS.SET_SCHEMA_PREFS (‘schema’,’publish’,’false’); alter session set optimizer_pending_statistics = TRUE; Statistics gathering Automatically maintains history Can automatically roll back to a point in time dbms_stats.restore* Real Time SQL Monitor Much, much better then session_long_ops v$sql_monitor For queries running for more then 1 sec Real time v$sql_plan_monitor includes stats for each step, real time Security Security – password complexity Built in password check function in UTLPWDMG.SQL allows “standard” functionality Built in default profile for password expiration Tablespace encryption alter tablespace payroll encrypt; alternative to file system encryption puts some vendors out of business DataPump Export/Import DataPump use function to modify table data compress both metadata and data to hide sensitive data requires “advanced compression option” encrypt exports including passwordless from wallet The End Thank you, Questions? [email protected] Visit my blog at http://www.pythian.com/blogs/kutrovsky/ http://www.pythian.com/