Inside Oracle 10g with Lutz Hartmann
15/16-05-2007 İstanbul

- lruw list is used for the dirty(modified) blocks, there is just a dirty flag extra. a buffer can be free, dirty, clean or pinned status,
- nK caches are not auto tunable,
- ash pool is located in the shared pool,
- asm benchmark shows it is the fastest lvm you can get, and it is free,
- streams pool is also auto tunable with 10gR2,
- mmon; manageability monitor background process, advices
- mman; memory manager, executing resize and trasnfer requests
- v$sgainfo - v$db_cache_advice - v$shared_pool_advice - v$streams_pool_advice
- allias plus = 'rlwrap sqlplus'; rlwrap creates an sqlplus or rman history similar to linux shell history, free software, http://sysdba.wordpress.com/?s=rlwrap
- starting with solaris 8 dynamic intimate shared memory feature, metalink note 230453.1, 
- isga; startup overhead in shared pool, run before you upgrade to see the parameter condiguration needs and calculate the size you need; $ORACLE_HOME/rdbms/admin/utlu102i.sql
- additional shared pool area is needed when using asm; http://sysdba.wordpress.com/2006/03/30/how-to-calculate-the-minimum-size-of-the-shared-pool-in-oracle-10g/
- incremental checkpointing mechanism; fast_start_mttr; configure yourself as if you will be bringing up the instance in x seconds, where as oracle gives you feedback at alert log which value is possible if you are not realistic
- during smon roll forwards(instance recovery), fast_start_parallel_rollback_high means 4*cpu smon slaves, if recovery_parallelism > 1 smon will work parallely
- 1 dbwr for each 8 cpu by default with 10g
- x$ksspi fixed dict. table for all hidden parameters, but you need to join it with x$ksppcu and x$ksppsu to get the exact values,
- maximum allowed number cr buffers per dba slot on 10gR2 is 6, parameter _db_block_max_cr,
- sga_target = 0 means no asmm, also statistics_level must be at least typical for mman to collect statistics needed, v$sga_dynamic_components
- oracle uses parameters starting with "__" for to remeber the old values of the auto tuned pools,
- uga and pga are auto tunable where as stack space is not,
- with automatic pga memory unused memory space is really returned to OS,
- with assm there is a low high water mark also showing that there is no unused block under it for sure,
- v$sql_workarea - v$sql_workarea_active  - v$sql_workarea_histograms - v$tempseg_usage - v$pgastat - v$process_memory - v$sysstat - v$pga_target_advice_histogram
- ckpt process has an assignment, global sql memory management
- migrate from dictionary managed to loclly with dbms_space_admin.tablespace_migrate_to_local, after 9iR2 system can also be migrated if all others are done,
- ddl will create redo, but will not create undo
- temporary tablespace group feature; dba_tablespace_groups, every session will be distributed through the tablespaces fo sort I/O needs, even with same oracle user or parallel query execution
- analyze command does not calculate avg_col_length, also not correct statistics for partitioned tables with analyze,
- be careful analyze collects statistics on all associated indexes automatically where as dbms_stats does not,
- dbms_stats.gather_database_stats_job_proc is the internal procedure used by dbms_scheduler with 10 for automatic statistic collection,
- after adding function based index collect statistics with method_opt for all hidden columns
- dba_tab_modifications for dml monitoring is enable dby default with 10g, statistics_level typical is needed
- for sample size use dbms_stats.auto_sample_size
- with 10g histogram creation is in place by default, method_opt to for all columns size auto,
- dynamic sampling with 10g is controlled by optimizer_dynamic_sampling and value is 2 meaning sample 32 blocks for any table without statistics. it was 1 at 9iR2, and if you set it 0 you disable this feature,
- if there is no stats and dynamic sampling is not enabled, cbo will use the defaults(pg 1-13),
- after 10g oracle recommend dbms_stats.gather_dictionary_stats on dictionary schemas like sys, system, drsys, ctx%
- system statistics were not enabled by default in 9i but are automatically used in 10g, if not collected dbms_stats.gather_system_stats cbo will use noworkload statistics. collect and tell cbo about your harware capabilities, so you must be collecting again after each hardware upgrade.
- unlike object statistics collecting system stats does not invalidate already parsed SQL
- noworkload stats by default are 1-19
- bind variable peeking is implemented througgh parameter _optim_peek_user_inds which is true by default,
- in memory statistics are updated every second on 10g, mmon stores these memory stats to sysaux tablespace under awr tables by deafult every hour. this period can be managed by package dbms_workload_repository, default snapshot retention is 7 days.
- mmon is also responsible for server generated alerts,
- mmnl(light version of mmon) will flush ash buffer to awr tables when the buffer is full or a snapshot is taken,
- addm is an engine in the kernel with ee, this tool analyzes a snapshot and db_time is the most important time model statistics here,
- there is an decition tree for performance management which addm uses in order to find the root cause and do recoomandations based on the significant db_time values,
- first tuning goal is the reduction of db_time of sessions after 10g, and with addm finding the root cause of a performance accurately is easy, this is the reason why this feature is licienced separetely,
- 10g kernel collects more than 800 wiat events on the fly which are classified into 12 wait classes,
pmon can restart mmon and mmnl when they die, instance of course does not crash,
- v$active_session_history - v$session_wait_history
- automatic tuning optimizer(ato) with dbms_sqltune, wait 11g :)
- preserved snapshot sets never age out, must be manually dropped, can be created by emor dms_workload_repository package,
- addm can detect problems before they can hurt and can give suggestions,
- addm is controlled by parameter dbio_expected which defaults to 10 mili seconds, if needed change this value for better advices,
- change track writer process captures modified block adress information and write it to change tracking file;
alter database enable bock change tracking;
this is used for fast incremental backups with rman, turned of by default and default location is db_create_file_dest parameter;
http://sysdba.wordpress.com/2006/05/18/how-to-use-fast-incremental-backups-with-block-change-tracking-with-oracle-10g/
- rman will know which archive is owned by which incarnation after resetlogs by help of parameter log_Archive_format %r option,
- rman can create a lost datafile and bring it as it was today from archives,
- backup duration and throttling can be given;
backup a copy database duration 10:00 minimze load ; -- upto 10 hours with min. load
backup database partial duration 04:00 .. ; -- backup as much as possible within 4 hours
- select current_scn from v$database; or dbms_flashback.get_systemchange_number
- global rman scripts, not target specific anymore,
- with 10gR2 a whole database transportation is possible, limitation is same endianness, very quickliy and flexiable with rman, two options a. convert and transport, b. transport and convert. use dbnewid to change the dbid.
- oracle secure backup for entire oracle enterprise environment like db.+appl.serv.+coll.suite+all ora files+all non db files etc. uses of course rman behind,
- rman encrypted backups, someone may steal your backup, transparent mode uses oracle wallet, password mode uses a password, dual mode either one of them, v$rman_encryption_alorithms for a list of algor.s
- drop database <including backups>; hey careful this is not a joke!
- rman compressed backups,
- show parameter asm, rbal; asm re-balancer process, asmcmd > help ; cd-ls-pwd-even find 
- backup as copy database; rman will backup to flashback recovery area,
- to swtich a database to asm using flashback recovery are and rman is the easiest way, first backup as copy database than swith to copy database and recover database. after this also online redo logs+control files and temp files need to be transfered,
- backup recovery files/area backs up flashback recovery area, check advanced b&r guide for this strategy's details;
- for flashback database archivelog mode and flash recovery area configurations are mandatory,
db_flashback_retention_target=2880 -- default is 1440(this is minutes, 1 day)
alter database flashback on;
- with 9iR1 dbms_flashback_enable_at_time(timestamp); -- time journey, from undo images, session level
- with 9iR2 select ... as of timestamp/scn .. -- query level
- with 10gR1 select ... versions_xid .. version between scn minvalue and maxvalue -- row level
also select undo_sql from flashback_transaction_query where xid = hextoraw('') -- transaction level
also flashback table .. to timestamp/scn ; -- segment level, enable row movement needed
also flashback table .. before drop <rename to..> ;
also flashback database .. -- will use flashback log files in the flash recovery area
- with 10gR2 sql > flashback databse to before resetlogs;
rman > reset database to incarnation 1;
also flashback to a point in time before a resetlogs operations
and also guarenteed restore points similar to savepoint of plsql(rollback to savepoint_a;)
flashback database/table to restore point ..;
v$respore_point
http://sysdba.wordpress.com/2006/01/23/flashback-any-error-with-oracle-10g-release-2/
- rvwr background process and flashback buffer cache area, 
- v$flashback_database_log - v$flashback_database_stat