#################################################################### # Listing of a number of "undocumented" init.ora / spfile.ora # # Oracle database parameters. # # ---------------------------------------------------------------- # # This is not a "very nice file". # # You cannot really "read" this file. Just browse around, # # or search the file for a string or term. # # ---------------------------------------------------------------- # # Sources: some selections from documents found on the internet. # # Date : 20/06/2008 # # Version: 1.0 # # Compiled by: Albert van der Sel # #################################################################### >>> IMPORTANT NOTICE: <<< >>> DO NOT CONSIDER ANY OF THE INFORMATION BELOW, AS DIRECTLY USABLE. <<< >>> JUST REGARD IT AS A TIP OR POINTER ON WHICH YOU MAY INVESTIGATE FURTHER. <<< Ofcourse, you can find information on undocumented init.ora parameters all over the Internet. Using this document, I want you to be aware that: - Use of undocumented parameters without the knowledge of Oracle Support is a violation of the Support agreement with Oracle. Oracle Worldwide support (in principle) has the right to refuse to support the databases affected by using these parameters. - A few parameters can potentially corrupt the database and some other parameters will help you in bringing up a (corrupted) database. - This document is just a dumb listing, often without a full explanation of the consequences. ##################################################################################################### NOTE 0: ##################################################################################################### If you cannot open a database, you might want to investigate further on the following init.ora/spfile.ora parameters _allow_read_only_corruption=true default=FALSE Usually allows a read-only open of the database, even if database is severely corrupt. So, while it helps to open a database in almost all cases, later on quite nasty ORA-600 errors may popup. Potentially, depending on the reason why you could not open the database "in the normal way", the scn of some blocks might be "out of order" (for example, they are "in the future"). But at least you can save data (using exp for example). _allow_resetlogs_corruption=true default=FALSE allows a resetlogs action, even if it will cause corruption. Remark for "_allow_resetlogs_corruption": If you use "_allow_resetlogs_corruption=true" at a recovery, and you do not have all archived redologs, then after trying to open the database with "ALTER DATABASE OPEN RESETLOGS", it might still fail to open. You might care to study the following: You might resolve that in setting the UNDO mode to manual (using the rollback segments in system): 1. in init.ora: UNDO_MANAGEMENT=MANUAL 2. startup the database 3. create a new UNDO tablespace 4. in init.ora: UNDO_MANAGEMENT=AUTO and specify the new UNDO tablespace 5. restart database ##################################################################################################### NOTE 1: ##################################################################################################### http://www.fortunecity.com/skyscraper/oracle/699/orahtml/misc/undoc.html Oracle Undocumented Parameters select ksppinm from x$ksppi where substr(ksppinm,1,1) = '_'; The following is a list of undocumented parameters. 1. _db_block_cache_protect On VMS, the DB_BLOCK_CACHE_PROTECT mechanism has been made much faster. During normal use, having it turned on shouldn't be noticeable (the degradation is less than 1%). Developers who link non-share will need PSWAPM privilege to use this feature. When DB_BLOCK_CACHE_PROTECT is turned on, developers may either use the VMS mailboxes with the M (MEMORY_LOG) command or they may just examine the ring buffer in the PGA (index SMPREI_, array SMPREB_) to determine what buffer requests have been made recently. DB_BLOCK_CACHE_PROTECT will prevent certain corruption from getting to disk; although, it may crash the foreground of the instance. It will help catch stray writes in the cache. When you try to write past the buffer size in the sga, it will fail first with a stack violation. It seems that the db_block_cache_protect has a significant performance overhead. Preliminary testing shows that it has considerable overhead (a single update took twice as long with the parameter set to TRUE). 2. _db_block_compute_checksums There is another new init.ora parameter, DB_BLOCK_COMPUTE_CHECKSUMS, that controls whether a checksum is put into every block before the block is written to disk. The default is FALSE. A block read validates an exiting checksum whether or not this option is enabled. A block is marked as corrupt if a checksum fails. It helps determine corruption due to hardware problems. The incarnation number and the sequence number are added to the end of the block to help catch corruption. If the problem (corruption) is in the middle of the block this test will not detect it. To detect this problem a checksum may be generated in the block header before every write and verified on every read. 3. _db_block_hash_buckets= "Number of database block hash buckets" The number of hash buckets is a) by default to be set to a prime number; b) coerced to a prime if there is an init.ora parameter setting. The value, if not a prime number > 2, is rounded up to the next highest prime. I would tend not to change it unless there is latch contention on the hash chains. raising it to equal the number of buffers would clearly remove any contention (basically, this is just saying that each buffer lives on its own hash chain). Having it set too small would mean that we might have to scan over lots of buffers to find the one we want. I think the default is to make it 1/4 of the total number of buffers 4. _db_block_multiple_hashchain_latches "Use one latch per hash chain" 5. _db_handles "System-wide simultaneous buffer operations" 6. _db_handles_cached "Buffer handles cached each process" 7. _wait_for_sync " Wait for sync on commit " Wait_for_sync is an oracle generic parameter which, when set to false, will allow the system to complete commits without waiting for the redo-log buffer flushes to complete. 8. _db_block_max_scan_cnt="Maximum number of buffers to inspect when looking for free buffer" DB_BLOCK_MAX_SCAN_CNT is an init.ora parameter which specifies the number of unavailable buffers a process should scan before signaling DBWR to write dirty buffers from the buffer cache to disk. 9. _db_writer_scan_depth "Number of LRU buffers for dbwr to scan when looking for dirty buffers" 10a. _db_writer_scan_depth_increment "Add to dbwr scan depth when dbwr is behind" 10b. _db_writer_scan_depth_decrement Subtract from dbwr scan depth when dbwr is working too hard 11. _db_large_dirty_queue "Number of buffers which force dirty queue to be written 12. _db_block_write_batch Number of blocks to group in each DB Writer IO specifies the no of blocks to be written to the disk in one write operation. Should be increased till write wait time and write complete waits starts to increase. DBWR Free Low is the number of times DBWR is invoked because a user process found at least DB_BLOCK_WRITE_BATCH/2 buffers on the dirty list. This parameter specifies the number of blocks which should be written to disk at one time. This parameter should only be increased until the statistics Write Complete Waits and Write Wait Time show growth. Write Complete Waits is the number of times a process waited for DBWR to write a current block before making a change to a buffer. 13. _db_block_cache_clone "Always clone data blocks on get (for debugging)" This parameter setting has a significantly adverse affect on performance and we were told to run without it. 14. _controlfile_enqueue_timeout /* use parameter value (default is 900) */ /* System Parameter: control file enqueue timeout in seconds */ 15. _db_no_mount_lock add hidden parameter to not acquire mount lock If hidden int.ora parameter _db_no_mount_lock is set to TRUE then no mount locks are acquired when the the database is mounted exlusive. This allows two databases with the same name to be simultaneously mounted. Note that this circumvents the mechanism to prevent two simultaneous startups of the same database, and is thus a dangerous parameter to set. This only affects ports that ar compiled with the SYS_DFS option enabled (i.e. VMS only). It does not get a mount lock on the database name during startup. This allows 2 instances with the same name to run on one machine 16. _log_checkpoint_recovery_check Check redo log after checkpoints. Add debugging code to check the red log after a checkpoint. This code is intended to help find a problm that is generating ora-600 [3020] during recovery. This code is enabed with a new init.ora parameter: _log_checkpoint_recovery_check=XXX, where XXX is the number of redo blocks to check. This is called in LGWR after every checkpoint. If the init.ora parameter "_log_checkpoint_recovery_check" is zero (default) it does nothing. If it is a positive value then that many blocks of redo are scanned to see that the data file blocks on disk could be recovered if there was an immediate crash. This code was introduced to catch an elusive bug that results in OERI(3020) errors occasionally during crash recovery. 17. _switch_on_stuck_recovery REDO GENERATION HAS BEEN CHANGED SO THAT WE SHOULD NEVER ENCOUNTER A CHANGE IN THE FUTURE OF THE BLOCK. APPLYING REDO IN SCN ORDER NOW INSURES THAT CHANGES ARE APPLIED IN INCARNATION/SEQUENCE # ORDER. THERE IS A LOT OF CODE IN RECOVERY THAT IS NO LONGER NEEDED BECAUSE OF THIS CHANGE. THIS INCLUDES THE CODE TO BACKUP AND RESCAN AFTER REPAIRING CORRUPTIONS. THE CODE IS BEING LEFT IN TO AVOID LARGE CHANGES JUST BEFORE V7.0 PRODUCTION RELEASE. THE FOLLOWING WILL INSURE THE UNNEEDED CODE IS NEVER EXECUTED UNLESS REDO WAS GENERATED BY A BETA RELEASE OR THE SECRET INIT.ORA PARAMETER _SWITCH_ON_STUCK_RECOVERY IS SET TO TRUE. Check redo Log contents after checkpoint. This is called in LGWR after every checkpoint. If this parameter is zero (default) it does nothing. If it is a positive value then that many blocks of redo are scanned to see that the data file blocks on disk could be recovered if there was an immediate crash. This code was introduced to catch an elusive bug that results in OERI(3020) errors occasionally during crash recovery. Checkpoint recovery check: this is the number of redo blocks that kcracl will verify after every LGWR generated checkpoint. Defaults to zero for no checking. When opening the named offline log for redo application and to recalculate future change thread switching this parameter is used. 18. _log_io_size=redo log IO chunk size (blocks/write) /* System Parameter: IO chunk size */ 1. that the value is o/s dependent. 2. if left at 0, the value will be automatically determined for each log file. 19. _log_buffers_debug /* debugging: fill redo buffers with [well known] junk after writes */ "debug redo buffers (slows things down)" 20. _log_debug_multi_instance /* debugging : pretend multi-instance */ "debug redo multi instance code" 21. _log_entry_prebuild_threshold /* redo entry pre-build threshold */ /* this is a bad idea for a uniprocessor , and is only helpful for a multiprocessor when there is latch contention */ LOG_ENTRY_PREBUILD_THRESHOLD determines the maximum size of a redo entry to prebuild before the copy to the log buffer. Increasing this parameter reduces the time that the redo copy latch is held. This parameter should not be modified if it is a single processor environment or there will be memory contention. 22. _disable_logging If this is true, redo records will not be generated -- no recovery is possible if the instance crashes. It is mainly used for getting good benchmarking results. Default is false 23. _log_blocks_during_backup TRUE value implies before any change is made to a db_block in the buffer cache, a *complete image* of the block is copied to the redo redo log. (This explains why excessive redo would be generated for datafiles excessive redo would be generated for datafiles in hot backup mode.) There is a new init.ora parameter, LOG_BLOCKS_DURING_BACKUP, that controls whether block images ar written to the redo log during hot backup. Default is TRUE for VM, but port specific with the default defined in sparams.h. This may beset to FALSE if the Oracle block size equals the O/S physical sector sie or if it is otherwise ensured that hot backup reads consistent versios of blocks even if those blocks are being written at the time. Put anther way, this may be set to FALSE on machines that can guarantee the aomicity of a single block I/O request. Default is true Problem is due to split blocks. 24. _allow_resetlogs_corruption Added new secret init.ora parameter to override error 1138. When set to TRUE the resetlogs option will be allowed even if there are hot backups that need more redo applied. Unless you are certain that absolutely all redo, includig the online logs, has been applied, then a full export and import mst be done to insure the database is internally consistant. from 6.0 code /* if we crashed/shutdown during a hot backup it is over now and we ** are no longer logging blocks. If they did a manual recovery ** followed by a NORESETLOGS then the hot backup recovery flag can ** be cleared. The hot backup recovery flag may alsoneed clearing ** if RESETLOGS was allowed because of _allow_resetlogs_corruption ** parameter. */ from the c file kcv.c 7.0 code /* if the resetlogs option is in effect we save the highest checkpoint ** scn that we see, to be used as the incomplete recovery scn. It only ** gets used if a resetlogs is done without any recovery. Only backup ** control file recovery will be allowed and it always rewrites the ** incomplete recovery scn. We use the highest scn we can find so that ** new resetlogs scn will be greater than anything in the database. ** This may not be strictly true if the user sets ** _allow_resetlogs_corruption, and there is a fuzzy file. Note that ** since resetlogs does not look at the log file headers, datafile ** checkpoints are all we have to look at. 25. _reuse_index_loop "number of blocks being examine for index block reuse" /* secret system parameter to control how agressive we should walk the free ** list when attempting to reuse block - default is 5. ** Set to 0 for fast index operation which is susceptible to growth, ** Set to > 5 for slower index op but more agressive in reusing blocks */ Controls the amount of work done when looking for a block to reusse for n index entry. The value determines the number of blocks to check on the freelist when looking for a reusable block. 26. _mts_load_constants /* hidden init.ora to set server load balancing constants */ /* fill in load balancing parameters (from _mts_load_constants) */ * PID Controller - calculate control on number of servers using: * control = Kp * err + Kd * delta(err) + Ki * sum(err) * where Kp = proportional, Kd = derivative constant, Ki = integral constant * Kp,Kd,Ki can be changed with the hidden parameter _mts_load_constants * in order to tune the system as desired. This values should only be changed after gathering enough information to determine that the mts is not optimal. 27. _mts_fastpath /* hidden init.ora to enable dispatcher fastpath */ default is false * Return TRUE if the system should implement the dispatcher network * fastpath. When enabled, the dispatcher fastpath causes network i/o * to be buffered and only flushed when absolutely necessary. When not * enabled, flushes will occur for every dirty virtual circuit buffer. *** The following parameters are from the Kernel SQL Library manager 28. _kgl_multi_instance_lock Only for debugging. all the _kgl_multi_instance_xxxx "whether KGL to support multi-instance locks" Default is 0 29. _kgl_multi_instance_pin "whether KGL to support multi-instance pins" Default is 0. 30. _kgl_multi_instance_invalidation "whether KGL to support multi-instance invalidations" Default is 0. 31. _row_cache_instance_locks Kernel SQL Row cache management component, number of row cache instance locks default is 100 32. _row_cache_buffer_size "size of row cache circular buffer" default is 200 33. _messages " message queue resources - dependent on # processes " The asynchronous message mechanism allows processes to send messages to each other. A process may send a message to a specified other process (always without waiting for a reply), may wait for a message to arrive on its queue, and may obtain the next message. Messages sent to detached processes are reliably delivered. Messages sent to foreground processes are reliably delivered as long as the process is active. The mechanism also permits sending of a simple "reply", which is a one-byte message without queuing. It should normally be used to reply to asynchronous messages, and this is a safer technique than regular messages for responding to foreground processes. This mechanism is not used in single process mode. 34. _cpu_count ksb - Kernel Service Background processes "number of cpu's for this instance" CPU_COUNT has to be set on some platforms in order for Oracle to take advantage of multi-processor system, on others it does not have effect on performance since load balancing between processors is handled by the o/s. 35. _debug_sga /* Debug SGA, don't make the SGA a global section so we can set watchpoints 36. _enqueue_locks ksq1 - Kernal Service enQueues (1) Definitions for enqueues client objects, "locks for managed enqueues" 37. _enqueue_hash "enqueue hash table length" 38. _enqueue_debug_multi_instance "debug enqueue multi instance" KST is a trace facility used for "realtime" tracing of events. Below are defined KST macros that will enable the tracing of such things as latch operations, memory assignments, etc. Tracing is done to a per process circular buffer stored in the SGA. Access to these buffers is via fixed tables. Tracing is enabled for classes of events, particular events, and ranges of events. The tracing state may be dynamically changed with the following syntax "ALTER TRACING" - "ON" - Processes begin logging events using the current enabled events - "OFF" - Processes stop tracing - "ENABLE" - Add in the events indicated in to those which are being traced. - "DISABLE" - No longer trace events specified by 39._trace_buffers_per_process Note that by default, tracing is NOT enabled. In order to enable tracing at instance startup time, add _trace_buffers_per_process = 1 40. _trace_block_size _trace_xxxxx (xxxxx = buffers_per_process, block_size, archive_start, flushing, enabled, get_time_every, archive_dest etc.) These parameters are only there for debugging purposes. Customers or support will never have to use them. 41. _trace_archive_start "start trace process on SGA initialization" 42. _trace_flushing "TRWR should try to keep tracing buffers clean" 43. _trace_enabled By default, tracing is NOT enabled. In order to enable tracing, _trace_enabled = true 44. _trace_events If you wish to enable tracing of waits at instance startup time, you can either add the line '_trace_events = "WAT,ALL"' to init.ora or execute 'alter tracing enable "WAT,ALL"' in a sqldba session. If you wish to limit tracing to specific events, you can use the the following syntax: alter tracing enable "WAT,,..." where "id" is either a specific event number, or an event range (event number 1 - event number 2). 45. _trace_archive_dest "trace archival destination" 46. _trace_file_size "trace file size" default is 10000 blocks 47. _trace_write_batch_size "trace write batch size" default is 32 48. _rollback_segment_initial "starting undo segment number" Default is 1. DO NOT SPECIFY 0 AS THAT HAPPENS TO BE THE SYSTEM ROLLBACK 49. _rollback_segment_count "number of undo segments" default is 0 50. _offline_rollback_segments If a rollback segment is not accessible because the file it is in is offline or corrupted, one can force the system to come up without the rollback segment by specifying the rollback segment in init.ora paramater '_offline_rollback_segments'. The system will come up by estimating the current system commit time since it cannot access transaction tble in the rollback segment. The system commit number is a conservative guess based on current time, the database creation time and the assumed transaction rate of 8000 tps. ONE MUST MAKE SURE THAT THE SYSTEM TIME IS SET CORRECTLY WHEN FORCING THE SYSTEM UP USING '_OFFLINE_ROLLBACK_SEGENTS'. A trace is written with information about the estimated system commit number. 51. _corrupted_rollback_segments Mark a rollback segment as corrupted. 52. _label_tag_cache_size /* hidden size of the SGA label tag comparison cache (bitcache) */ "number of tags in the label tag comparison cache" default is 200 53. _trace_get_time_every "Number of trace sequence numbers per call to slgcs()" default is 200 54. _vms_bg_priority "VMS process priority to give to background processes" default is 4 55. _sort_use_os_files_as_temporaries Use O/S files rather than temp segments for sorting. 56. _log_checkpoints_to_alert Whether checkpoint messages should be written to alert.log or not. Turned off in benchmarks. 57. _large_memory_system : Used in internal benchmarks. Doesn't concern us. "Configure system to use memory and save cpu cycles, paging, etc default is false 58. _wakeup_timeout This is WMONs sleeptime between checks of it's queue of processes to wake. 59. _latch_wait_posting enable posting of processes that may be waiting for a latch after a process frees the same latch (set this parameter to a value greater than one this parameter to a value greater than one for it to take effect). 60. _sql_connect_capability_code allows database links from a pre-7.0.15 release to work with release 7.1. It is necessary to set this parameter for database links from a V6 ? ##################################################################################################### NOTE 2: ##################################################################################################### http://www.csinc-us.com/oratip.htm Introduction Before thinking about the use of the undocumented parameter "_allow_resetlogs_corruption" all other avenues of database recovery must have been exhausted. Because this parameter forces the opening of the datafiles even if their SCNs do not match up. Then at the next checkpoint the old SCN values are over-written. This could leave the database in an unknown state as far as concurrency. For that reason, you must export and rebuild your database after using this recovery method. SCENARIO 1 Most of the time, this recovery method is required when a data file has been left in hot backup mode through several backup cycles without an intervening shutdown and startup. Upon shutdown and startup the database will complain that a file (usually file id#1 the SYSTEM tablespace) needs more recovery and asks for logs past all available archive logs and online logs. SCENARIO 2 An other scenario could be that the database is recovered from a hot backup and the above scenario occurs, or, the database asks for an archive log that is before any that are available (usually for the ROLLBACK segment tablespace datafiles.) A typical error stack would be like that: SVRMGR> connect internal Connected. SVRMGR> startup ORACLE instance started. Total System Global Area xxx bytes Fixed Size xxx bytes Variable Size xxx bytes Database Buffers xxx bytes Redo Buffers xxx bytes Statement processed. ALTER DATABASE OPEN resetlogs * ORA-01194: file 1 needs more recovery to be consistent ORA-01110: data file 1: '/u01/oradata/ORCL/system01.dbf' Or: ORA-01547: warning: RECOVER succeeded but OPEN RESETLOGS would get error below ORA-01194: file 12 needs more recovery to be consistent ORA-01110: data file 12: '/u12/oradata/ORCL/data01.dbf' If all available archive logs are applied and all available online redo logs applied and the error is not corrected, only then should use of the parameter "_allow_resetlogs_corruption" be considered. Make sure a good backup of the database in a closed state (all files) is taken before attempting recovery using "_allow_resetlogs_corruption". It cannot be stressed enough that the database will no longer be supported by Oracle until it is rebuilt after this type of recovery is accomplished. HOW TO USE "_allow_resetlogs_corruption" 1. If no recovery attempts have been made, shutdown and backup the database as-is (all files). 2. If recovery attempts have been made, recovery database to state just before any other recovery attempts where made. 3. Use svrmgrl, sqlplus or appropriate interface to startup the database in a mounted by not open condition: · STARTUP MOUNT 4. Ensure all datafiles are set to END BACKUP status: · SELECT 'alter database datafile '||file_name||' END BACKUP;' from v$datafile; 5. Alter the database into open condition: · ALTER DATABASE OPEN RESETLOGS; 6. If the database asks for recovery, use a UNTIL CANCEL type recovery and apply all available archive and online redo logs, then issue the CANCEL and reissue the ALTER DATATBASE OPEN RESETLOGS; command. 7. If the database asks for logs that are no longer available or the above still resulted in errors, shutdown the database. 8. Insert into the initialization file the following line: · _allow_resetlogs_corruption=TRUE 9. Use svrmgrl, sqlplus or appropriate interface to startup the database in a mounted by not open condition: · STARTUP MOUNT 10. Ensure all datafiles are set to END BACKUP status: · SELECT 'alter database datafile '||file_name||' END BACKUP;' from v$datafile; 11. Alter the database into open condition: · ALTER DATABASE OPEN RESETLOGS; 12. If the database asks for recovery, use a UNTIL CANCEL type recovery and apply all available archive and online redo logs, then issue the CANCEL and reissue the ALTER DATATBASE OPEN RESETLOGS; command. 13. One the database is open, immediately do a full export of the database, or an export of the schemas you need to recover. 14. Shutdown the database, remove the parameter "_allow_resetlogs_corruption" 15. Rebuild database 16. Import to finish the recovery 17. Implement a proper backup plan and procedure 18. It may be advisable to perform an ANALYZE TABLE…VALIDATE STRUCTURE CASCADE on critical application tables after the recovery and before the export. Note: Uncommitted records that had been written to disk will possible be marked as committed by this procedure. ##################################################################################################### NOTE 3: ##################################################################################################### http://www.dba-oracle.com/concepts/initialization_parameters.htm Parameter Value Description _NUMA_instance_mapping Not Specified Set of nodes that this instance should run on _NUMA_pool_size Not Specified aggregate size in bytes of NUMA pool _advanced_dss_features FALSE enable advanced DSS features _affinity_on FALSE enable/disable affinity at run time _all_shared_dblinks NULL treat all dblinks as shared _allocate_creation_order FALSE should files be examined in creation order during allocation _allow_read_only_corruption FALSE allow read-only open even if database is corrupt _allow_resetlogs_corruption FALSE allow resetlogs even if it will cause corruption _always_star_transformation FALSE always favor use of star transformation _arch_io_slaves 0 ARCH I/O slaves _average_dirties_half_life 2097152 Half life in bytes of first dirties running average _b_tree_bitmap_plans FALSE enable the use of bitmap plans for tables w. only B-tree indexes _backup_disk_io_slaves 0 BACKUP Disk I/O slaves _backup_io_pool_size 262144 memory to reserve from the large pool _bump_highwater_mark_count 0 How many blocks should we allocate per free list on advancing HW _cleanup_rollback_entries 20 no. of undo entries to apply per transaction cleanup _close_cached_open_cursors FALSE close cursors cached by PL/SQL at each commit _column_elimination_off FALSE turn off predicate-only column elimination _compatible_no_recovery 0.0.0 Database will be compatible unless crash or media recovery is needed _complex_view_merging FALSE enable complex view merging _controlfile_enqueue_timeout 900 control file enqueue timeout in seconds _corrupt_blocks_on_stuck_recovery 0 number of times to corrupt a block when media recovery stuck _corrupted_rollback_segments NULL corrupted undo segment list _cpu_to_io NULL multiplier for converting CPU cost to I/O cost _cr_server NULL if TRUE, enable CR server TRUE (DFS) _cursor_db_buffers_pinned 78 additional number of buffers a cursor can pin at once _db_aging_cool_count 1 Touch count set when buffer cooled _db_aging_freeze_cr 2 Make CR buffers always be FALSE too cold to keep in cache _db_aging_hot_criteria 2 Touch count which sends a buffer to head of replacement list _db_aging_stay_count 99 Touch count set when buffer moved to head of replacement list _db_aging_touch_time 3 Touch count which sends a buffer to head of replacement list _db_block_cache_clone FALSE Always clone data blocks on get (for debugging) _db_block_cache_map 0 Map / unmap and track reference counts on blocks (for debugging) _db_block_cache_protect FALSE protect database blocks (true only when debugging) _db_block_hash_buckets 2000 Number of database block hash buckets _db_block_hi_priority_batch_size 0 Fraction of writes for high priority reasons _db_block_max_cr_dba 10 Maximum Allowed Number of CR buffers per dba _db_block_max_scan_cnt 0 Maximum number of buffers to inspect when looking for free _db_block_med_priority_batch_size 0 Fraction of writes for medium priority reasons _db_handles 700 System-wide simultaneous buffer operations _db_handles_cached 5 Buffer handles cached each process _db_large_dirty_queue 0 Number of buffers which force dirty queue to be written _db_no_mount_lock FALSE do not get a mount lock _db_percent_hot_default 50 Percent of default buffer pool considered hot _db_percent_hot_keep 0 Percent of keep buffer pool considered hot _db_percent_hot_recycle 0 Percent of recycle buffer pool considered hot _db_writer_chunk_writes 0 Number of writes DBWR should wait for _db_writer_histogram_statistics FALSE maintain dbwr histogram statistics in x$kcbbhs _db_writer_max_writes 0 Max number of outstanding DB Writer Ios _db_writer_scan_depth 0 Number of LRU buffers for dbwr to scan when looking for dirty blocks _db_writer_scan_depth_decrement 0 Subtract from dbwr scan depth when dbwr is working too hard _db_writer_scan_depth_increment 0 Add to dbwr scan depth when dbwr is behind _dbwr_async_io TRUE Enable dbwriter asynchronous writes _dbwr_tracing 0 Enable dbwriter tracing _defer_multiple_waiters TRUE if TRUE, defer down converts when there were waiters (DFS) _disable_incremental_checkpoints FALSE Disable incremental checkpoints for thread recovery _disable_latch_free_SCN_writes_via_32cas FALSE disable latch-free SCN writes using 32-bit compare & swap _disable_latch_free_SCN_writes_via_64cas TRUE disable latch-free SCN writes using 64-bit compare & swap _disable_logging FALSE Disable logging _disable_ntlog_events FALSE Disable logging to NT event log _discrete_transactions_enabled FALSE enable OLTP mode _distributed_lock_timeout 60 number of seconds a distributed transaction waits for a lock _distributed_recovery_connection_hold_time 200 number of seconds RECO holds outbound connections open _domain_index_batch_size 2000 maximum number of rows from one call to domain index fetch routine _domain_index_dml_batch_size 200 maximum number of rows for one call to domain index dml routines _dss_cache_flush TRUE enable full cache flush for parallel execution _dynamic_stats_threshold 30 Delay threshold (in seconds) between sending statistics messages _enable_NUMA_optimization TRUE Enable NUMA specific optimizations _enable_block_level_transaction_recovery true enable block level recovery _enable_cscn_caching TRUE enable commit SCN caching for all transactions _enable_default_affinity 0 to enable default implementation of affinity osds _enable_list_io TRUE Enable List I/O _enable_multitable_sampling FALSE enable multitable sampling _enqueue_debug_multi_instance FALSE Debug enqueue multi-instance _enqueue_hash 265 enqueue hash table length _enqueue_hash_chain_latches 2 enqueue hash chain latches _enqueue_locks 1479 Locks for managed enqueues _fairness_threshold 4 number of times to CR serve before downgrading lock (DFS) _fast_full_scan_enabled FALSE enable fast full scan _foreground_locks TRUE if TRUE, foregrounds acquire PCM locks (DFS) _full_pwise_join_enabled TRUE enable full partition-wise join when TRUE _gc_class_locks 0 set locks for the minor classes (DFS) _gc_latches 4 number of latches per lock process (DFS) _gc_lck_procs 1 number of background parallel server lock processes to start _groupby_nopushdown_cut_ratio 3 groupby nopushdown cut ratio _high_server_threshold 0 high server thresholds _idl_conventional_index_maintenance TRUE enable conventional index maintenance for insert direct load ##################################################################################################### NOTE 4: ##################################################################################################### http://www.oracle-books.com/orasnap/example/main/undoc.htm UNDOCUMENTED INIT.ORA PARAMETER NOTES: Parameter - Name of the undocumented parameter Value - Current value for the parameter Desc - Brief description of parameter _ALLOW_RESETLOGS_CORRUPTION - This saves you when you have corrupted redo logs _CORRUPTED_ROLLBACK_SEGMENTS - This saves you when you have corrupted RB segments _SPIN_COUNT - This shows how often the processor will take a new request (reduce CPU timeouts) _LOG_ENTRY_PREBUILD_THRESHOLD - Redo entries larger than this will be prebuilt _LATCH_SPIN_COUNT - This shows how often a latch request will be taken (reduce latch timeouts) _DB_BLOCK_WRITE_BATCH - Number of blocks to group in each DB Writer I/O _CPU_COUNT - Number of CPUs that you have (causes bugs with Parallel Query option). Check with Oracle support for restrictions on some versions and O/S _INIT_SQL_FILE - Where the sql.bsq file is located _TRACE_FILES_PUBLIC - This allows users to see the TRACE output without major privileges _FAST_FULL_SCAN_ENABLED - This allow indexed fast full scans if only indexes are needed _CORRUPT_BLOCKS_ON_STUCK_RECOVERY - This sometimes gets a corrupted database up. _ALWAYS_STAR_TRANSFORMATION - This is a fast method for Data Warehouse if you have a good designer. _SMALL_TABLE_THRESHOLD - Small tables are pinned into memory if smaller than this Do NOT change any of these values unless instructed by ORACLE support Parameter (1) Value Desc _affinity_on TRUE enable/disable affinity at run time _allow_resetlogs_corruption FALSE allow resetlogs even if it will cause corruption _bump_highwater_mark_count 0 how many blocks should we allocate per free list on advancing HW _controlfile_enqueue_timeout 900 control file enqueue timeout in seconds _corrupt_blocks_on_stuck_recovery 0 number of times to corrupt a block when media recovery stuck _corrupted_rollback_segments NULL corrupted undo segment list _cr_deadtime 6 global cache lock CR deadlock timeout in seconds _db_block_cache_clone FALSE Always clone data blocks on get (for debugging) _db_block_cache_protect FALSE protect database blocks (true only when debugging) _db_block_hash_buckets 250 Number of database block hash buckets _db_block_max_cr_dba 10 Maximum Allowed Number of CR buffers per dba _db_block_max_scan_cnt 10000 Maximum number of buffers to inspect when looking for free _db_block_multiple_hashchain_latches TRUE Use one latch per hash chain (vs one latch for all changes) _db_block_no_idle_writes FALSE Disable periodic writes of buffers when idle _db_block_write_batch 512 Number of blocks to group in each DB Writer IO _db_handles 2100 System-wide simultaneous buffer operations _db_handles_cached 3 Buffer handles cached each process _db_large_dirty_queue 0 Number of buffers which force dirty queue to be written _db_no_mount_lock FALSE do not get a mount lock _db_writer_scan_depth 0 Number of LRU buffers for dbwr to scan when looking for dirty _db_writer_scan_depth_decrement 0 Subtract from dbwr scan depth when dbwr is working too hard _db_writer_scan_depth_increment 0 Add to dbwr scan depth when dbwr is behind _dbwr_slave_batch_size 1 DBWR slave I/O batch size _debug_sga FALSE debug sga _disable_blocking_waiters FALSE Don't allow waiters to block PCM lock release _disable_logging FALSE Disable logging _disable_oracle_resilvering FALSE disable oracle resilvering for fast instance recovery _enable_dba_locking FALSE enable persistent locking _enqueue_debug_multi_instance FALSE debug enqueue multi instance _enqueue_hash 1145 enqueue hash table length _enqueue_hash_chain_latches 4 enqueue hash chain latches _enqueue_locks 6754 locks for managed enqueues _gc_recovery_scan_smon FALSE if TRUE, do recovery scans in SMON stead of LCK processes _init_sql_file ?/dbs/sql.bsq File containing SQL statements to execute upon database creation _kgl_bucket_count 0 index to the bucket count array _kgl_latch_count 0 number of library cache latches _kgl_multi_instance_invalidation TRUE whether KGL to support multi-instance invalidations _kgl_multi_instance_lock TRUE whether KGL to support multi-instance locks _kgl_multi_instance_pin TRUE whether KGL to support multi-instance pins _latch_recovery_alignment 60 align latch recovery structures _latch_spin_count 100 amount to spin waiting for a latch _latch_wait_posting 1 post sleeping processes when free latch _log_blocks_during_backup TRUE log block images when changed during backup _log_buffers_debug FALSE debug redo buffers (slows things down) _log_checkpoint_recovery_check 0 # redo blocks to verify after checkpoint _log_debug_multi_instance FALSE debug redo multi instance code _log_entry_prebuild_threshold 0 redo entries larger than this will be prebuilt before getting la _log_io_size 0 automatically initiate log write if this many redo blocks in buf _log_space_errors TRUE should we report space errors to alert log _max_exponential_sleep 0 max sleep during exponential backoff _max_sleep_holding_latch 4 max time to sleep while holding a latch _messages 1015 message queue resources - dependent on # processes & # buffers _minimum_giga_scn 0 Minimum SCN to start with in 2^30 units _mts_fastpath TRUE dispatcher network fastpath _mts_listener_retry 120 listener connection retry rate (secs) _mts_load_constants 3,0.75,0.25,0.1 server load balancing constants (S,P,D,I) _number_cached_attributes 10 maximum number of cached attributes per instance _offline_rollback_segments NULL offline undo segment list _optimizer_undo_changes FALSE undo changes to query optimizer _oracle_trace_events NULL Oracle TRACE event flags _oracle_trace_facility_version NULL Oracle TRACE facility version _parallel_allocation wide allocation method for parallel query servers _parallel_min_message_pool 19332 minimum size of shared pool memory to reserve for pq servers _parallel_server_sleep_time 10 sleep time between dequeue timeouts (in 1/100ths) _passwordfile_enqueue_timeout 900 password file enqueue timeout in seconds _plsql_dump_buffer_events NULL conditions upon which the PL/SQL circular buffer is dumped _release_insert_threshold 5 maximum number of unusable blocks to unlink from freelist _reuse_index_loop 5 number of blocks being examine for index block reuse _rollback_segment_count 0 number of undo segments _rollback_segment_initial 1 starting undo segment number _row_cache_buffer_size 200 size of row cache circular buffer _row_cache_instance_locks 100 number of row cache instance locks _session_idle_bit_latches 0 one latch per session or a latch per group of sessions _shared_session_sort_fetch_buffer 0 size of in-memory merge buffer for mts or xa fetch calls _single_process FALSE run without detached processes _small_table_threshold 512 threshold level of table size for forget-bit enabled during scan _sql_connect_capability_override 0 SQL Connect Capability Table Override _sql_connect_capability_table NULL SQL Connect Capability Table (testing only) _standby_lock_name_space NULL lock name space used for generating lock names for standby datab _star_transformation_enabled FALSE enable the use of semi-join transformations _test_param_1 25 test parmeter 1 _test_param_2 NULL test parameter 2 _test_param_3 NULL test parameter 3 _trace_archive_dest ?/rdbms/log trace archival destination _trace_archive_start FALSE start trace process on SGA initialization _trace_block_size 2048 trace block size _trace_buffers_per_process 0 trace buffers per process _trace_enabled TRUE Should tracing be enabled at startup _trace_events NULL turns on and off trace events _trace_file_size 10000 trace file size _trace_files_public TRUE Create publicly accessible trace files _trace_flushing FALSE TRWR should try to keep tracing buffers clean _trace_instance_termination FALSE trace instance termination actions _trace_write_batch_size 32 trace write batch size _wait_for_sync TRUE wait for sync on commit MUST BE ALWAYS TRUE _wakeup_timeout 100 wakeup monitor timeout value _walk_insert_threshold 0 maximum number of unusable blocks to walk across freelist ##################################################################################################### NOTE 5: ##################################################################################################### http://www.dba-oracle.com/art_so_undoc_parms_p2.htm Using Oracle undocumented hidden parameters Oracle Tips by Burleson Consulting In part 1 of this series we explored ways to extract undocumented packages, hints, utilities and executables from Oracle. Now we are ready to explore some ways that the undocumented Oracle parameters are used to aid the Oracle professional in managing and tuning their systems. Oracle makes a huge disclaimer that the undocumented initialization parameters are usually only used in emergencies. However, those who want to manipulate the internal mechanisms of Oracle to customize the behavior to their systems find the undocumented parameters very useful. WARNING! Using undocumented parameters without the consent of Oracle can make your system un-supported and you may be on your own if you experience data corruption. Don't say I didn't warn you! With over 100 undocumented parameters (see listing 1) it impossible to cover them all in this short article. Let's look at performance-related parameters first. For specialized tasks such as database tuning, it's possible to set the Oracle undocumented ("hidden" parameters). You should carefully test all undocumented parameters and get permission from Oracle technical support before using undocumented parameters in production. Here is a common list of some of the undocumented parameters: _allow_resetlogs_corruption _column_tracking_level _db_aging_cool_count _db_aging_freeze_cr _db_aging_hot_criteria _db_aging_stay_count _db_aging_touch_time _db_percent_hot_default _db_percent_hot_keep _db_percent_hot_recycle _init_sql_file _kghdsidx_count _log_io_size _realfree_heap_pagesize_hint _recyclebin _small_table_threshold _system_trig_enabled _trace_files_public _use_ism _use_ism_for_pga _wait_for_sync Also, see these undocumented parameters from this Oracle IBM terabyte RAM benchmark: _NUMA_pool_size = 536870912 _collect_undo_stats=false _awr_restrict_mode=true _db_writer_flush_imu=false _db_cache_pre_warm=FALSE _undo_autotune = false _imu_pools = 358 _optimizer_cache_stats = false _optimizer_cost_model = io _cursor_cache_frame_bind_memory = true _db_writer_coalesce_area_size = 16777216 _kghdsidx_count = 1 _ksmg_granule_size=268435456 _two_pass=false _session_idle_bit_latches=3000 Many savvy Oracle professionals commonly adjust the hidden parameters to improve the overall performance of their systems. However, because these are "undocumented" parameters, most Oracle professionals rely on publications such as "Oracle Internals" to get insights into the proper setting for the hidden parameters. >>>>Oracle latch parameters: Whenever index contention is experienced (as evidenced by process waits), adjusting the following parameters may be helpful. _db_block_hash_buckets - Defaults to 2 x db_block_buffers but should be the nearest prime number to the value of 2x db_block_buffers. _db_block_hash_latches - Defaults to 1024 but 32768 is a sometimes a better value. _kgl_latch_count - Defaults to zero which is means 1+number of CPUs. Lock contention can often be reduced by re-setting this value to 2*CPUs +1. _latch_spin_count - This parameter shows how often a latch request will be taken. _db_block_write_batch - Formerly documented, now undocumented. It is the number of blocks that the db writers will write in each batch. It defaults to 512 or db_files*db_file_simultaneous_writes/2 up to a limit of one-fourth the value of db_cache_size. >>>>Oracle parallel query parameters: OPQ is an amazing facility for improving the speed of large-table full-table scans, and some DBAs are not aware that there are a dozen hidden parameters that can be changed to affect the behavior of parallel queries. Here is a query to display the parameters: COLUMN parameter FORMAT a37 COLUMN description FORMAT a30 WORD_WRAPPED COLUMN "Session VALUE" FORMAT a10 COLUMN "Instance VALUE" FORMAT a10 SET LINES 100 PAGES 0 SELECT a.ksppinm "Parameter", a.ksppdesc "Description", b.ksppstvl "Session Value", c.ksppstvl "Instance Value" FROM x$ksppi a, x$ksppcv b, x$ksppsv c WHERE a.indx = b.indx AND a.indx = c.indx AND a.ksppinm LIKE '/_parallel%' escape '/' ; SPOOL OFF Note that this script must be run from the SYS user as only the SYS user can access the X$ internal tables. NAME VALUE ----------------------------------- ------------------------------ _parallel_adaptive_max_users 1 _parallel_default_max_instances 1 _parallel_execution_message_align FALSE _parallel_fake_class_pct 0 _parallel_load_bal_unit 0 _parallel_load_balancing TRUE _parallel_min_message_pool 64560 _parallel_recovery_stopat 32767 _parallel_server_idle_time 5 _parallel_server_sleep_time 10 _parallel_txn_global FALSE _parallelism_cost_fudge_factor 350 The most important of these hidden parallel parameters is the _parallelism_cost_fudge_factor. This parameter governs the invocation of OPQ by the cost-based SQL optimizer when parallel_automatic_tuning=true. By adjusting this parameters you can control the threshold for invoking parallel queries. Now let's look at some common DBA undocumented parameters. The following undocumented parameters are the most commonly-used in Oracle administration. >>>> Corruption undocumented parameters These parameters allow you to ignore corrupt data blocks when your database is corrupted. These should only be used in emergencies. _allow_resetlogs_corruption - This parameter may be the only way to start a db backed-up open without setting backup on tablespaces, it will result in an unsupported system. _corrupted_rollback_segments - The only way to start up with corrupted public rollback segments. This undocumented parameter can be used without fear of invalidating support. _allow_read_only_corruption - This parameter allows the database to be opened even if it has corruption. This should only be used to export as much data from a corrupted database as is possible before re-creating a database. A database that has been opened in this manner should not be used in a normal manner, as it will not be supported. _corrupt_blocks_on_stuck_recovery - This parameter can sometimes be useful for getting a corrupted database started. However, it probably won't be supported if done without Oracle's blessing. Immediately export the tables needed and rebuild the database if used. >>>> SQL Optimizer undocumented parameters: These parameters control the internal behavior of the cost-based SQL optimizer (CBO). _fast_full_scan_enabled - This enables (or disables) fast full index scans, if only indexes are required to resolve the queries. _always_star_transformation - This parameter helps to tune data warehouse queries, provided that the warehouse is designed properly. _small_table_threshold - This sets the size definition of a small table. A small table is automatically pinned into the buffers when queried. Defaults to 2 percent in Oracle9i. >>>> Data Buffer behavior parameters: For the very brave DBA, you can change the caching and aging rules within the Oracle db_cache_size and change the way that Oracle keeps data blocks in RAM memory. While these parameters are somewhat dangerous, some savvy DBAs have been able to get more efficient data caching by adjusting these values: _db_aging_cool_count - Touch count set when buffer cooled _db_aging_freeze_cr - Make CR buffers always be FALSE too cold to keep in cache _db_aging_hot_criteria - Touch count which sends a buffer to head of replacement list _db_aging_stay_count - Touch count set when buffer moved to head of replacement list _db_aging_touch_time - Touch count which sends a buffer to head of replacement list _db_block_cache_clone - Always clone data blocks on get (for debugging) _db_block_cache_map - Map / unmap and track reference counts on blocks (for debugging) _db_block_cache_protect - Protect database blocks (true only when debugging) _db_block_hash_buckets - Number of database block hash buckets _db_block_hi_priority_batch_size - Fraction of writes for high priority reasons _db_block_max_cr_dba - Maximum Allowed Number of CR buffers per dba _db_block_max_scan_cnt - Maximum number of buffers to inspect when looking for free _db_block_med_priority_batch_size - Fraction of writes for medium priority reasons A Case study in undocumented parameters If you have a limited number of active sessions you may wish to override the PGA governor that only allows any single task to consume 5% of the total PGA. Laurent Schneider notes in Oracle MetaLink that overriding the PGA defaults made a large batch processes run more than 8x faster: "I set appropriate values for pga_aggregate_target and _pga_max_size... alter system set pga_aggregate_target=6G; alter system set "_pga_max_size"=2000000000; ...and I gave the query some hints "NOREWRITE FULL USE_HASH ORDERED". As a result, it boosted my query performance from 12 hours to 1.5 hour." Ah, if only it were that easy, just change an undocumented setting and batch jobs magically run times faster. Laurent Schneider notes some perils and reliability issues relating to this parameter and says "this parameter often leads to an ORA-4030, even when plenty of memory available, for some obscure reasons". While Laurent abandoned the undocumented approach in favor of parallelizing the batch job, the promise of eight times faster execution speeds are very tempting. Once you get permission from Oracle Technical Support to set an undocumented parameter, they can work with to resolve errors. While they may not address bugs, they may be able to provide alternatives and workarounds. Conclusion These Oracle undocumented parameters are especially useful to the senior Oracle DBA who needs to go beyond the recommended level of detail and wants to change the internal behavior of their SGA. The undocumented parameters are also a lifesaver for performing re-starts of corrupted databases, but we must always remember that these parameters are hidden for a reason. They are very powerful and undocumented, so you should only play with them if you have a clear understanding about how they change the internal behavior of Oracle. ##################################################################################################### NOTE 6: ##################################################################################################### http://www.praetoriate.com/t_grid_rac_undoc_init_procedures.htm Almost every DBA knows about Oracle's documented initialization parameters; most of them are easy to look up and use. However, not everyone knows about the undocumented parameters, and few know how or when to use them. There are many parameters that Oracle will not allow DBAs to use unless specifically directed to by Oracle support. DBAs should be aware that use of certain undocumented parameters will result in an unsupported system. I will attempt to identify where parameters should be used only under Oracle support’s guidance and where a DBA can safely utilize these high-powered tools. What’s the Difference? The major difference between the documented and undocumented parameters is that the undocumented parameters begin with an underscore character. In many cases, the undocumented parameters were either documented at one time in previous releases or will be in future releases. Those parameters that have been, or will be, documented are usually those that are safe to use. The undocumented parameters that have never been really documented and the ones that never will be are those for which it is difficult to determine their use safety. When in doubt, get guidance from Oracle support. And always back up the database before using any of the questionable parameters so that you have a way to restore a "supported" version on which you can get help. So Many Parameters, So Little Time Let’s begin this discussion with what an expert has to say on the topic. Rich Niemiec of TUSC cites, in Oracle Performance Tuning Tips and Techniques (Oracle Press, 2000), the following 13 undocumented parameters as his favorites: _ALLOW_RESETLOGS_CORRUPTION. May be only way to start a db backed-up open without setting backup on tablespaces; will result in unsupported system. See detailed section on using _allow_resetlogs_corruption which follows.. _CORRUPTED_ROLLBACK_SEGMENTS. Only way to start up with corrupted public rollback segments. Can be used without fear of desupport. _ALLOW_READ_ONLY_CORRUPTION. Allows you to open a database even if it has corruption. This should only be used to export as much data from a corrupted database as is possible before re-creating a database. You should not use a database for normal use that has been opened in this manner, as it will not be supported. _SPIN_COUNT. Sets the number of spins a process will undergo before trying to get a latch. If CPU is not fully loaded, a high value may be best; for a fully loaded CPU, a smaller value may help. Usually defaults to 2000. Can be changed without fear of desupport. Flips from undocumented to documented depending on version. _LOG_ENTRY_PREBUILD_THRESHOLD. Formerly documented, now is undocumented; is the minimum size of entry in blocks that will be prebuilt for redo log entries; usually set to 30. _LATCH_SPIN_COUNT. Shows how often a latch request will be taken _DB_BLOCK_WRITE_BATCH. Formerly documented, now undocumented; the number of blocks that the db writers will write in each batch; defaults to 512 or DB_FILES*DB_FILE_SIMULTANEOUS_WRITES/2 up to a limit of one-fourth the value of DB_BLOCK_BUFFERS. _CPU_COUNT. Flips from undocumented to documented. Should be set automatically; but on some platforms, doesn't; set to the number of CPUs. This determines several other parameters. _INIT_SQL_FILE. The initialization SQL script run by Oracle when a db is created. This should be sql.bsq; if you change it, you may not be supported. _TRACE_FILES_PUBLIC. Changes the privileges on trace files such that everyone can read them. Should be Okay to change at will. _FAST_FULL_SCAN_ENABLED. Enables (or disables) fast full index scans if only indexes are required to resolve the queries. Change at will. _CORRUPT_BLOCKS_ON_STUCK_RECOVERY. Can sometimes get a corrupted db up; you probably won't be supported if done without Oracle support’s blessing. Immediately export the tables you need and rebuild the db if used. _ALWAYS_STAR_TRANSFORMATION. Helps to tune data warehouse queries if you have a properly designed data warehouse. _SMALL_TABLE_THRESHOLD. Sets the size of table considered a small table. A small table is automatically pinned into the buffers when queried. Defaults to 2 percent in Oracle9i. Niemiec lists another 13 parameters that bear mentioning: _DEBUG_SGA. Has no noticeable effect. _LOG_BUFFERS_DEBUG. Slows things down. _REUSE_INDEX_LOOPS. The blocks to examine for index block reuse. _SAVE_ESCALATES. Not sure what it does; no measurable effects. According to Steve Adams, Oracle may take an exclusive lock earlier than required to save lock escalations; if this is set to FALSE, it won’t. Don’t mess with it. _OPTIMIZER_UNDO_CHANGES. Reverts to pre-6.0.3 optimizer for IN statements. This one is required in certain versions of the Oracle Applications. According to K. Gopalakrishnan of the India Times, this parameter has nothing to do with cost-based optimization. In Version 6, somewhere around V6.0.36, if he remembers correctly, Ed Peeler made several changes to the way the optimizer made choices. Many queries in the existing Oracle Applications at that time relied in particular on the old way the optimizer worked to be certain to come up with the intended plan. The code was written prior to hints, so many tricks were used to influence the optimizer to come up with a certain plan and were scattered throughout the code. When the new database release was made, tests showed that many critical applications processes ran catastrophically slower. Fortunately, the old optimizer code had not been stripped out yet, and a way to use the old optimizer code was allowed via the “hidden*” parameter _undo_optimizer_changes (probably so that Oracle could test the old versus the new optimizer internally). So Oracle released this parameter to the applications community. This was long before the cost-based optimizer even existed. _DSS_CACHE_FLUSH. Enables full cache flush for parallel; effect is not measurable. _DB_NO_MOUNT_LOCK. Doesn't get a mount lock; no noticeable effect. _AFFINITY. Defaults to TRUE; enables or disables CPU affinity. _CORRUPT_BLOCKS_ON_STUCK_RECOVERY. Tried in a corrupt recovery and didn't do anything; no noticeable effect _CURSOR_DB_BUFFERS_PINNED. Lists additional buffers a cursor can pin; no noticeable effect. The default value is max(db_block_buffers/processes-2,2). One note from Oracle development says that playing around with this parameter will almost always damage performance, so it was made hidden starting in 8.0.3. _DB_BLOCK_NO_IDLE_WRITES. Disables writes of blocks when the db is idle; may result in an unusable system if used. If you get an instance Crash with the following Error ORA-00600: internal error code, arguments: [kcbbzo_2], [1], [25974], setting this parameter may help. _DISABLE_LOGGING. Even I won't support you if you set this one. If this parameter is set to TRUE, redo records will not be generated, and recovery is not possible if the instance crashes or is terminated with shutdown abort. _IO_SLAVES_DISABLED. Disables I/O slaves, probably not the safest way. Other Notables Other parameters, covered in various notes on Metalink, ORACLE8i Internal Services for Waits, Latches, Locks and Memory, by Steve Adams (Oracle Press, 1999), and other boards include the following: _DISTRIBUTED_LOCK_TIMEOUT. Sets the amount of time a lock used in a distributed transaction will be held, usually 5. Safe to reset without worry of desupport. _ROW_CACHE_CURSORS. Maximum number of cached recursive cursors used for data dictionary cache queries. This sets the size of the array in the PGA used for the data dictionary cursors. It takes 28 bytes per cached cursor. Long-running procedures that do a lot of data dictionary lookups can benefit from higher values. If the view K$KQDPG shows excessive overflows of the cache, a small increase may help. This view is for the current process only. The ORADUBUG or DBMS_SYSTEM.SET_EV can be used to grab a trace of a different process. In the resulting trace, find the X$KQDPG structure, count 12 blocks, and use the hexadecimal values to get the values. Recursive calls can be caused by firing of database triggers, execution of SQL statements within stored procedures and functions and anonymous PL/SQL blocks, and enforcement of referential integrity constraints. Setting this value higher may reduce these recursive calls. _LOG_BLOCKS_DURING_BACKUP. From Oracle support, but we still do not recommend the use of this parameter on production systems. There is a known problem with performance on systems using certain configurations of Veritas with Solaris 2.6. Contact Veritas for further information. Use of this parameter will result in an unsupported system. _DB_WRITER_SCAN_DEPTH_INCREMENT. Controls the rate at which the scan depth increases if the db writer is idle; defaults to one-eighth the difference between the upper and lower bounds of the scan depth. _DB_WRITER_SCAN_DEPTH_DECREMENT. Controls the rate at which the scan depth decreases of the db writer is working too hard. The X$KVIT table gives the details of the db writer; you can find the details using the following SELECT statement, courtesy of Steve Adams at www.ixora.com.au: SELECT Kvitdsc, Kvitval FROM Sys.x$kvit WHERE Kvittag IN ('kcbldq', 'kcbsfs') OR Kvittag LIKE 'kcbsd_' / _DB_LARGE_DIRTY_QUEUE. Defaults to one-sixteenth of the write batch size limit. Sets the frequency at which DBWR writes. Should be decreased gradually in one-block increments until buffer waits are eliminated. The cost of reducing this parameter is in CPU usage, so shouldn't be changed without good reason. _DB_BLOCK_MAX_SCAN_CNT. Defaults to one-fourth of the working set size, rounded down. If this number of blocks is scanned, then the free buffer request fails. The failure of a scan increments the dirty buffers inspected parameter in the V$SYSSTAT view. If there are no free buffer waits, and there are dirty buffers inspected, then there is a potentially serious problem and the parameter should be tuned. _ENQUEUE_HASH_CHAINS. Derived from the PROCESSES parameter, so is the value for ENQUEUE_RESOURCES, which is directly affected by _ENQUEUE_HASH_CHAINS; therefore, if you explicitly set ENQUEUE_RESOURCES, you will need to adjust _ENQUEUE_HASH_CHAINS to a prime number just less than the value of ENQUEUE_RESOURCES. If the value of _ENQUEUE_HASH is not set to a prime number, long enqueue hash chains could develop. Unless you are receiving ORA-00052 or ORA-00053 errors, this parameter and ENQUEUE_RESOURCES probably don’t need adjusting. The default value is equal to CPU_COUNT. _ENQUEUE_LOCKS. Use V$RESOUCE_LIMIT to see if you need more locks or ENQUEUE_RESOUORCES. A lock takes 60 bytes; a resource, 72 bytes. To increase the enqueue_locks value in v$resource limit, you have to increase the _enqueue_limit value in init.ora. _USE_ISM. Determines if intimate shared memory is used. On some platforms this can cause problems, and _USE_ISM should be set to FALSE instead of its default of TRUE. _DB_BLOCK_HASH_BUCKETS. In releases prior to 9i, this was set to twice the value of DB_BLOCK_BUFFERS. Unfortunately, this should be set to a prime number to keep the hash chains from getting out of hand; therefore, on releases prior to 9i, resetting this to a prime number near the value of twice the DB_BLOCK_BUFFERS is a good idea and will not result in loss of support. According to Steve Adams, _db_block_hash_buckets could be used to set both the number of hash chains and latches in previous releases. From 7.1, it was constrained to prime numbers, and used to default to next_prime(db_block_buffers / 4). db_block_buffers, and the _db_block_hash_latches parameter must be used to control the number of hash latches if necessary. This parameter is constrained to binary powers so that Oracle can calculate which latch to use with a simple SHIFT operation, rather than a DIVIDE operation. The default number of hash latches depends on db_block_buffers. If db_block_buffers is less than 2052 buffers, then the default number of latches is 2 ^ trunc(log(2, db_block_buffers - 4) - 1). If db_block_buffers is greater than 131075 buffers, then the default number of latches is 2 ^ trunc(log(2, db_block_buffers - 4) - 6). If db_block_buffers is between 2052 and 131075 buffers, then there are 1024 latches by default. Sites that have used _db_block_hash_buckets to combat cache buffer chains latch contention under previous releases should allow the value to default when upgrading to Oracle 8i. Remember that contention for these latches is almost always a symptom of one or more blocks being very hot due to unresolved application or SQL tuning problems. Adams may be correct; however, I have seen improvements on 8.1.7 by setting these values as in previous releases. _DB_BLOCK_HASH_LATCHES. Usually set to 1024, which is usually too small; set it to near 32K (32768) for better performance. Up to release 8.0. _KGL_LATCH_COUNT. Value defaults to 7. This determines the number of latches that control the shared pool. If you need a large shared pool, or have a large number of items that are placed in the shared pool, set this to a larger prime number. According to Oracle support: In general, on systems that have multiple CPUs and/or when parsing a lot of SQL with few shared cursors, it is recommended to set it to 1. On all other systems it must be set to the default, in which case the latch contention may not be significant compared to the cost of building a new cursor for each SQL statement. However, I tend to agree with Steve Adams: The default is the least prime number greater than or equal to cpu_count. The maximum is 67. It can safely be increased to combat library cache latch contention, as long as you stick to prime numbers. That said, it is only effective if the activity across the existing child library cache latches is evenly distributed as shown in V$LATCH_CHILDREN. Here are some undocumented parameters for maximizing DBWR performance: * Increase _db_block_write_batch (hidden parameter in Oracle8, obsolete in Oracle8i). * Decrease _db_block_max_scan_count, _db_writer_scan_depth, and _db_writer_scan_depth_increment to decrease the dirty buffer backlog. * Adjust_db_writer_chunk_writes, which controls the number of writes DBWR should try to group into one batch I/O operation. * Adjust _db_block_med_priority_batch_size for regular writes. * Adjust _db_block_hi_priority_batch_size for urgent writes such as when LRUW is full or there are no free buffers or when free buffers are below limit. Parameters That Must Be Used Here I attempt to list the specific conditions when the following undocumented parameters must be changed: _DB_HANDLES. In versions before 8.1.7.2, if set too high, this may cause ORA-04031 because of bug 1397603. _DB_HANDLES_CACHED. Before 8.1.7.2, may need to be set to 0. _ELIMINATE_COMMON_SUBEXPR. If left set to TRUE, may cause some queries using IN clauses to return too many rows or bad answers; set to FALSE in 8.1.7.0 and 8.1.7.1. _IGNORE_DESC_IN_INDEX. May have to set to TRUE on some platforms (such as AIX) in versions 8.1.6 and 8.1.7 if you get ORA-03113 and ORA-07445 errors when you try different management or DML operations on descending indexes. On some platforms may have to use the event-setting “10612 trace name context forever, level 1” to reset it. _INIT_SQL_FILE. This may have to be reset to use 32K block sizes on some platforms, as they will use the sql.bsq.32K file instead of the sql.bsq file. _MV_REFRESH_SELECTIONS. Is 8.1.7 only, and if you have multilevel joins in your materialized view, setting this to TRUE may allow fast refreshes. _NEW_INITIAL_JOIN_ORDERS. Set to TRUE when upgrading to 11.5.4 on 8.1.7. _OGMS_HOME. Used in Oracle Parallel Server. Set explicitly or may default to /tmp, which could result in file loss and inability to start Oracle Parallel Server. _SQLEXEC_PROGRESSION_COST. For Oracle Application 11.5.4 in 8.1.7, set to 0. _UNNEST_SUBQUERY. Where you cannot use the UNNEST hint, may improve performance. _USE_ISM. Set to FALSE on Solaris 2.6 or you may have system crashes and poor performance (depending on Oracle version may be USE_ISM). _DB_ALWAYS_CHECK_SYSTEM_TS. Always perform block check and checksum for SYSTEM tablespace; this defaults to TRUE. You may need to set this to FALSE after upgrade from a pre-8i version of Oracle. If you need to set this to FALSE to restart the DB, immediately export and rebuild the database, as it detected corruption in the data dictionary (probably in the C_TS# cluster, but that is another story). _DB_CACHE_ADVICE. Will turn on the buffer cache sizing advisory if set to ON to help you perform cache sizing. ##################################################################################################### NOTE 7: ##################################################################################################### Check out: http://psoug.org/reference/startup_parms.html http://www.fors.com/velpuri2/dul_ucg8.html ##################################################################################################### NOTE 8: ##################################################################################################### http://www.fors.com/velpuri2/Oracle%20block%20structure/BLOCKCORRUPTIONONUNIX Subject: BLOCK CORRUPTIONS ON ORACLE AND UNIX Creation Date: 24-NOV-1999 PURPOSE This article discusses block corruptions in Oracle and how they are related to the underlying operating system and hardware. To better illustrate the discussion, Unix is taken as the operating system of reference. SCOPE & APPLICATION For users requiring further understanding as to how a block could become corrupted. Block corruption has been a common occurrence on most UNIX based systems and relational databases for many years. It is one of the most frequent ways to lose data and cause serious business impact. Through a survey of literary technical sources, this document will discuss several ways that block corruptions can occur, provide conclusions and possible solutions. To fully comprehend all the reasons for block corruption's, it is necessary to understand how I/O device subsystems work, how memory buffers are used to support the reading and writing of data blocks, how blocks are sized on both UNIX and Oracle, and how these three objects work together to maintain data consistency. I/O devices are designed specifically for host machines and there have been few attempts to standardize a particular interface across the industry. Most software, including Oracle, on UNIX machines uses standard C program calls that in turn perform system calls to support the reading and writing of data to disk. These system calls access I/O device software that retrieves or writes data on disk. The UNIX system contains two types of devices, block devices and raw or character devices. Block devices look like random access storage devices to the rest of the system while character devices include all other devices such as terminals and network media. (Bach, 1990 314). These device types are important to understand because different combinations can increase corruptions. Device drivers are configured by the operating system and the configuration procedure generates or populates tables that form part of the code of the kernel. This kernel to device driver interface is described by the block device switch table and the character device switch table. Each device type has entries in these tables that direct the kernel to the appropriate driver interfaces for the system calls. The open and close system calls of a device file funnel through the two device switch tables, according to file type. The mount and umount system calls also invoke the device open and close procedures for block devices. Read and write system calls of character special files pass through the respective procedures in the character device switch tables. Read and write system calls of block devices and of files on mounted file systems invokes the algorithms of the buffer cache, which invoke the device strategy procedure. (Bach, 1990 314). This buffer cache plays an important role in block corruptions since it is the location where data blocks are the most vulnerable. The difference between the two disk interfaces is whether they deal with the buffer cache. When accessing the block device interface, the UNIX kernel follows the same algorithm as for regular files, except that after converting the logical byte offset into a logical block offset, it treats the logical block offset as a physical block number in the file system. It then accesses the data via the buffer cache and, ultimately, the driver strategy interface. However, when accessing the disk via the raw interface, the kernel does not convert the byte offset into the file but passes the offset immediately to the driver. The driver's read or write routine converts the byte offset to a block offset and copies the data directly to the user address space, bypassing kernel buffers. Thus, if one process writes a block device and a second process then reads a raw device at the same address, the second process may not read the data that the first process had written, because the data may still be in the buffer cache and not on disk. However, if the second process had read the block device, it would automatically pick up the new data, as it exists in the buffer cache. (Bach, 1990 328). Use of the raw interface may also introduce strange behavior. If a process reads or writes a raw device in units smaller than the block size, results are driver-dependent. For instance, when issuing 1-byte writes to a tape drive, each byte may appear in different tape blocks. (Bach 1990) The advantage of using the raw interface is speed, assuming there is no advantage to caching data for later access. Processes accessing block devices transfer blocks of data whose size are constrained by the file system logical block size. Furthermore, use of the block interface entails an extra copy of data between user address space and kernel buffers, which is avoided in the raw interface. For example, if a file system has a logical block size 1K bytes, at most 1K bytes are transferred per I/O operation. However, processes accessing the disk as a raw device can transfer many disk blocks during a disk operation, subject to the capabilities of the disk controller. Disk controllers are hardware devices that control the I/O actions of one or more disks. These controllers can also create a bottleneck in a system. (Corey, Abbey, Dechichio 1995). Controllers are the most frequent piece of hardware to have and cause problems on many systems. When a system has multiple disks controlled by one controller, the results can be fatal. The bottleneck on controllers is a common cause of write error. It is important to remember that Oracle and other products use these device access methods to perform their work. It is also important to note the added complexity that the Oracle kernel adds to the I/O game. The Oracle Relational Database Management System (RDBMS) keeps its information, including data, in block format. However, the Oracle data block can be, and in most cases is, composed of several operating system blocks. An Oracle database block is the physical unit of storage in which all Oracle database data are stored in files. The Oracle database block size is determined by setting a parameter called db_block_size when the database is created. (Millsap, 1995). The most common UNIX block is 512 bytes but the Oracle block size can range from 512 to 32K. The difference in block sizing between the operating system and the Oracle kernel are beneficial for Oracle; boosting performance gains while allowing UNIX to maintain small files with minimal wasted space. The Oracle block can be considered a superset of the UNIX file system block size. Each block of an Oracle data file is formatted with a fixed header that contains information about the particular block. This information provides a means to ensure the integrity for each block and in turn, the entire Oracle database. One component of the fixed header of a data block is called a Data Block Address (DBA). This DBA is a 48 bit integer that stores the file number of the Oracle database and the Oracle block number offset relative to the beginning of the file. (Presley, 1993). Whenever there is a problem with the DBA, Oracle will signal an Oracle error ORA-00600[3339][arg1][arg2] and possibly ORA-01578: Data block corrupted in file # block #. These errors provide information that point to where the corruption exists and can provide several potential causes. (Presley, 1993). The ORA-00600[3339] has two arguments the are meaningful to the person evaluating the corruption. Argument 1 is the DBA that Oracle found in the data block just read from disk. Argument 2 is the DBA Oracle expected to find in the data block it just read. If they are different, the ORA-00600[3339] is signaled. Oracle uses the standard C system function calls to read and write blocks to its database files. Once the block has been read it is mapped to shared memory by the operating system, After the block has been read into shared memory, the Oracle kernel does verification checks on the block to ensure the integrity of the fixed header. The DBA check is the first verification made on the fixed header. So why do DBAs become corrupt and how can we identify and correct them? Case One -------- The first case of block corruption occurs when the first argument of the ORA-00600[3339] error has the value of zero while argument two contains the DBA which Oracle was trying to retrieve. Remember that argument 1 is the DBA just read from disk. Usually the first operating system block of an Oracle block is zeroed out when there was a soft error on disk and the operating system attempted to repair its block. In addition, disk repair utility programs have caused this zeroing out effect. Programs that read from and write to the disk directly can destroy the consistency of file system data. The file system algorithms coordinate disk I/O operation to maintain a consistent view of disk data structures, including linked lists of free disk blocks and pointer from inodes to direct and indirect data blocks. Processes that access the disk directly bypass these if they run while other file system activity is going on. For this reason, these programs should not be run on an active file system. (Bach, 1990 328). On some early versions of the Oracle RDBMS, a software bug specific to UNIX platforms also caused the ORA-00600[3339]. This bug was part of the code that dealt with multiple database writers. A database writer is a background process that is responsible for managing the contents of the data block buffer cache and the dictionary cache. It reads the blocks from the datafiles and stores them in the Shared Global Area (SGA). The database writer also performs batch writes of changed blocks back to the datafiles. (Loney, 1994 23). The SGA is a segment of memory allocated to Oracle the contains data and control information particular to an Oracle database instance. Using multiple database writers causes multiple background processes to perform disk operations at the same time. However, if there are process conflicts, incorrect values could be stored and corruption can occur. Also, using multiple database writers with asynchronous I/O has been know to cause similar results. Asynchronous I/O allows a process to execute a system call to start and I/O operation and have the system call return immediately after the operation is started or queued. Another system call is required to wait for the operation to complete. The advantage of asynchronous I/O is that a process can overlap its execution with I/O, or it can overlap I/O between different devices. (Stevens, 1990 163). Case Two -------- In the second case, both arguments returned with the ORA-00600[3339] error are large numbers. There are several causes that signal this error. The DBA in the physical block on disk is incorrect. This can happen if the block was corrupted in memory but was written to disk. This situation is quite rare and in most cases it is usually caused by memory faults that go undetected. The DBA found in the block is usually garbage and not a valid DBA. Argument two that is returned with the error is always a valid DBA. If there is a possibility of memory problems on the system, the database administrator can enable further sanity block checking by placing the following event parameters in the database instance init.ora parameter file: event = "10210 trace name context forever, level 10" event = "10211 trace name context forever, level 10" _db_block_cache_protect= true These parameters force the Oracle RDBMS kernel to call functions that check the block header and block trailer to ensure that the block is in the proper format and has not been corrupted. The 10210 event parameter validates data blocks for tables while the 10211 validates data blocks for indexes. The _db_block_cache_protect=true protects the cache layer from becoming corrupted. This parameter will prevent certain corruption from getting to disk, although it may crash the foreground of the database instance. It will help catch stray writes in the cache. When a process tries to write past the buffer size in the SGA, it will fail first with a stack violation. If the database writer process detects a corrupted block in cache prior to writing the block to disk, it will signal an ORA-00600[3398] and will crash the database instance. The block that is corrupted is never written to disk. Various arguments including the DBA are passed to the ORA-00600[3398] and after receiving such an error, simply attempt to restart the database instance. There is no doubt that this can be a costly workaround to avoid block corruptions. However, the workaround once a corruption has occurred can be even costlier. Blocks are sometimes written into the wrong places in the data file. This is called "write blocks out of sequence." In this case, both DBAs returned with the ORA-00600[3339] are valid. This typically happens when the operating system I/O device driver fails to write the block in the proper location that Oracle requested via the lseek() system call. Some hardware and operating system vendors supports large files or "large file systems" that maintain files larger that 4.2 gigabytes. This is larger than what can be represented by a 32 bit unsigned integer. Therefore, the operating system must translate the offset transparent to the Oracle kernel. Oracle does not support files larger than 2 gigabytes even though the operating system might. On large file systems, the configuration is such that even smaller Oracle data files suffer corruptions caused by blocks being written out of sequence because the lseek() system call did not translate the correct location. (Velpuri, 1995). The lseek() system call is one of the most important calls related to block corruption. The calculations that lseek() performs are often the cause of block problems. To understand lseek() a brief discussion of byte positioning is necessary. Every open file has a "current byte position" associated with it. This is measured as the number of bytes from the start of the file. The create system call sets the file's position to the beginning of the file, as does the open system call. The read and write system calls update the file's position by the number of bytes read or written. Before a read or write, an open file can be positioned using lseek(). The format is: lseek(int fildes, long offset, int whence); The offset and whence arguments are interpreted as follows: If whence is 0, the file's position is set to offset bytes from the beginning of the file. If whence is 1, the file's position is set to its current position plus the offset. If whence is 2, the file's position is set to the size of the file plus the offset. The file's offset can be greater than the file's current size, in which case the next write to the file will extend the file. Lseek() returns a long integer byte offset of the file. (Stevens, 1990 40). There is great opportunity for miscalculation of an offset based on the lseek() system call. Though lseek is not the only system call culprit in the block corruption problem, it is a major contributor. Case 3 ------ A third cause for block corruption is the requested I/O not being serviced by the operating system. In this case, both arguments returned from the ORA-00600[3339] are valid but the DBA found in argument one is from the previous block read into shared memory prior to the current read request. The calls that Oracle makes to lseek() and read() are checked for return error codes. In addition, Oracle checks to see the number of bytes read in by the read() system call to ensure that the block size or a multiple of the block size was read. Since these checks appeared to have been successful, Oracle assumes that the direct read succeeded. Upon sanity checking, the DBA is incorrect and the database operation request fails. Therefore, the I/O read request really never took place. In this case, the DBA found can point to a block of a different file. Case 4 ------ Another reason for block corruption is reading the wrong block from the same device. Typically, this is caused by a very busy disk. In some cases, the block read was off by 1 block but can range into several hundreds of blocks. The DBAs returned with the ORA-00600[3339] are valid DBA's but are not the block requested. Since this occurs when the disk is very busy and under lots of stress, try spreading datafiles across multiple disks and ensure that the disk drive can support the load. In the third and fourth situations, the database files will not be physically corrupted and the operation can be tried again with success. Most diagnostics testing will not reveal anything wrong with either the operating system or the hardware. However, the problem is due to operating system or hardware related problems. (Velpuri, 1995). So what causes the operating system calls to behave the way they do and how can companies try to minimize their risk? To evaluate these questions, another look into how UNIX works is required. UNIX vendors, in a attempt to speed performance, have implemented many features into the filesystem. The filesystem manages a large cache of I/O buffers, called the buffer cache. This cache allows UNIX to optimize read and write operations. When a program writes data, the filesystem stores the data in a buffer rather that writing it to disk immediately. At some later point in time, the system will send this data to the disk driver, together with other data that has accumulated in the cache. In other words, the buffer cache lets the disk driver schedule disk operations in batches. It can make larger transfers and use techniques such as seek optimization to make disk access more efficient. This is called write-behind. When a program reads data, the system first checks the buffer cache to see if the desired data is already there. If the data is already in the buffer cache, the filesystem does not need to access the disk for those blocks. It just gives the user the data it found in its buffer, eliminating the need to wait for a disk drive. The filesystem only needs to read the disk if the data isn't already in the cache. To increase efficiency even further, the filesystem assumes the program will read the file consecutively and read several blocks from the disk at once. This increases the likelihood that the data for future read operations will already be in the cache. (Loukides, M., 1990) This also increases the chance of block corruption. As a filesystem gets busy and buffers are being read, modified, written, and aged out of the cache the chance of the kernel reading or writing the wrong block increases. Also, the more complex the scheme to read from and write to disk, the greater the likelihood of function failure. The UNIX kernel uses the strategy interface to transmit data between the buffer cache and a device, although the read and write procedures of character devices sometime use their block counterpart strategy procedure to transfer data directly between the device and the user address space. The strategy procedure may queue I/O jobs for a device on a work list or do more sophisticated processing to schedule I/O jobs. Drivers can set up data transmission for one physical address or many, as appropriate. The UNIX kernel passes a buffer header address to the driver strategy procedure. The header contains a list of addresses and sizes for transmission of data to or from the device. This is also how the swapping operations work. For the buffer cache, the kernel transmits data from one address; when swapping, the kernel transmits data from many data addresses. If data is being copied to or from the user's address space, the driver must lock the process in memory until the I/O transfer is complete. The kernel loses control over a buffer only when it waits for the completion of I/O between the buffer and the disk. It is conceivable that a disk drive is corrupt so that it cannot interrupt the CPU, preventing the kernel from ever releasing the buffer. There are processes that monitor the hardware for such cases and zero out the block and return an error to the kernel for a bad disk job. (Bach, 1990 52). On the UNIX level there are several utilities that will check for bad disk blocks and zero out any blocks they find corrupted. These utilities do not realize that the block in question may be an Oracle RDBMS block and zero out the block by mistake. In (Silberschatz, Galvin, 1994), the authors consider the possible effect of a computer crash. In this case, the table of opened files is generally lost, and with it any changes in the directories of opened files. This event can leave the file system in an inconsistent structure. Frequently, a special program is run at reboot time to check for and correct disk inconsistencies. The consistency checker compares the data in the directory structure with the data blocks on disk, and tries to fix and inconsistencies it finds. (Silberschatz, Galvin, 1994) This will often result in the reformatting of blocks which will cause the Oracle block information to be removed. This will definitely cause Oracle corruption. It is important to realize that monitoring of hardware is required for all operating systems. Hardware monitors can sense electrical signals on the busses and can accurately record them even at high speed. A hardware monitor keeps observing the system even when it is malfunctioning, and thus, it can be used to debug the system. (Jain, 1991 99) These tools can help determine the cause of the problem and detect problems like controller error and media faulting which are frequent corruption contributors. In any case, there are many opportunities for blocks, either on disk or in the buffer cache, to become corrupt. Fixing the corruption can sometimes provide even greater opportunities. Conclusion ---------- Data block corruption is an ongoing problem on all operating systems, especially UNIX. There are many types and causes of corruptions to consider. Advanced system configurations can increase the chance and hardware problems are a common source of corruptions. When receiving block corruption errors, remember that a couple of them are not physical corruptions but memory corruptions that are never written to disk. Oracle Customer Support provides a number of bulletins on block corruption problems that help recover what is left of the data once corruption has occurred. If block corruption occurs on a machine, be sure to identify the type of corruption and establish a plan for its correction. [1] Bach, M. (1990). The Design of the UNIX Operating System. The I/O Subsystem 328. [2] Corey, M., Abbey, M., Dechichio, D. (1995). Tuning Oracle 52. [3] Jain, R. (1991). The Art of Computer Systems Performance Analysis. 99 [4] Loney, K. (1994). Oracle DBA Handbook. 23. [5] Loukides, M., (1990) System Performance Tuning. 161-162. [6] Millsap, C. (1995). Oracle7 Server Space Management. 1-2. [7] Presley, D. (1993). Data Block Corruption Detection. Oracle Corporation. [8] Silberschatz A., Galvin P. (1994) Operating System Concepts. 404. [9] Stevens, W. (1990). UNIX Network Programming. 163. [10] Velpuri, R. (1995). Oracle Backup and Recovery Handbook. 286 ##################################################################################################### NOTE 9: ##################################################################################################### No source available. BLOCK CORRUPTION: Note 1: ======= Doc ID : Note:47955.1 Content Type: TEXT/PLAIN Subject: Block Corruption FAQ Creation Date: 14-NOV-1997 Type: FAQ Last Revision Date: 17-AUG-2004 Status: PUBLISHED ORACLE SERVER ------------- BLOCK CORRUPTION ---------------- FREQUENTLY ASKED QUESTIONS -------------------------- 25-JAN-2000 CONTENTS -------- 1. What does the error ORA-01578 mean? 2. How to determine what object is corrupted? 3. What are the recovery options if the object is a table? 4. What are the recovery options if the object is an index? 5. What are the recovery options if the object is a rollback segment? 6. What are the recovery options if the object is a data dictionary object? 7. What methods are available to assist in pro-actively identifying corruption? 8. How can corruption be prevented? 9. What are the common causes of corruption? QUESTIONS & ANSWERS 1. What does the error ORA-01578 mean? An Oracle data block is written in an internal binary format which conforms to a defined structure. The size of the physical data block is determined by the "init.ora" parameter DB_BLOCK_SIZE set at the time of database creation. The format of the block is similar regardless of the type of data contained in the block. Each formatted block on disk has a wrapper which consists of a block header and footer. Unformatted blocks should be zero throughout. Whenever a block is read into the buffer cache, the block wrapper information is checked for validity. The checks include verifying that the block passed to Oracle by the operating system is the block requested (data block address) and also that certain information stored in the block header matches information stored in the block footer in case of a split (fractured) block. On a read from disk, if an inconsistency in this information is found, the block is considered to be corrupt and ORA-01578: ORACLE data block corrupted (file # %s, block # %s) is signaled where file# is the file ID of the Oracle datafile and block# is the block number, in Oracle blocks, within that file. However, this does not always mean that the block on disk is truely physically corrupt. That fact needs to be confirmed. 2. How to determine what object is corrupted? The following query will display the segment name, type, and owner: SELECT SEGMENT_NAME, SEGMENT_TYPE, OWNER FROM SYS.DBA_EXTENTS WHERE FILE_ID = AND BETWEEN BLOCK_ID AND BLOCK_ID + BLOCKS - 1; Where is the file number and is the block number reported in the ORA-01578 error message. Suppose block 82817 from table 'USERS' is corrupt: SQL> select extent_id, block_id, blocks from dba_extents where segment_name='USERS'; EXTENT_ID BLOCK_ID BLOCKS ---------- ---------- ---------- 0 82817 8 1 82825 8 2 82833 8 3 82841 8 4 82849 8 SQL> SELECT SEGMENT_NAME, SEGMENT_TYPE, OWNER 2 FROM SYS.DBA_EXTENTS 3 WHERE FILE_ID = 9 4 AND 82817 BETWEEN BLOCK_ID AND BLOCK_ID + BLOCKS - 1; SEGMENT_NAME SEGMENT_TYPE OWNER --------------------------------------------------------------------------------- ------------------ USERS TABLE VPOUSERDB 3. What are the recovery options if the object is a table? The following options exist for resolving non-index block corruption in a table which is not part of the data dictionary: o Restore and recover the database from backup (recommended). o Recover the object from an export. o Select the data out of the table bypassing the corrupted block(s). If the table is a Data Dictionary table, you should contact Oracle Support Services. The recommended recovery option is to restore the database from backup. [NOTE:28814.1] contains information on how to handle ORA-1578 errors in Oracle7. References: ----------- [NOTE:28814.1] TECH ORA-1578 and Data Block Corruption in Oracle7 4. What are the recovery options if the object is an index? If the object is an index which is not part of the data dictionary and the base table does not contain any corrupt blocks, you can simply drop and recreate the index. If the index is a Data Dictionary index, you should contact Oracle Support Services. The recommended recovery option is to restore the database from backup. There is a possibility you might be able to drop the index and then recreate it based on the original create SQL found in the administrative scripts. Oracle Support Services will be able to make the determination as to whether this is a viable option for you. 5. What are the recovery options if the object is a rollback segment? If the object is a rollback segment, you should contact Oracle Support Services. The recommended recovery option is to restore the database from backup. 6. What are the recovery options if the object is a data dictionary object? If the object is a Data Dictionary object, you should contact Oracle Support Services. The recommended recovery option is to restore the database from backup. If the object is an index on a Data Dictionary table, you might be able to drop the index and then recreate it based on the original create SQL found in the administrative scripts. Oracle Support Services will be able to make the determination as to whether this is a viable option. 7. What methods are available to assist in pro-actively identifying corruption? ANALYZE TABLE/INDEX/CLUSTER ... VALIDATE STRUCTURE is a SQL command which can be executed against a table, index, or cluster which scans every block and reports a failure upon encountering any potentially corrupt blocks. The CASCADE option checks all associated indices and verifies the 1 to 1 correspondence between data and index rows. This is the most detailed block check available, but requires the database to be open. DB Verify is a utility which can be run against a datafile of a database that will scan every block in the datafile and generate a report identifying any potentially corrupt blocks. DB Verify performs basic block checking steps, however it does not provide the capability to verify the 1 to 1 correspondence between data and index rows. It can be run when the database is closed. Export will read the blocks allocated to each table being exported and report any potential block corruptions encountered. References: ----------- [NOTE:35512.1] DBVERIFY - Database file Verification Utility (7.3.2 onwards) 8. How can corruption be prevented? Unfortunately, there is no way to totally eliminate the risk of corruption. You can only minimize the risk and plan accordingly. 9. What are the common causes of corruption? o Bad I/O, H/W, Firmware. o Operating System I/O or caching problems. o Memory or paging problems. o Disk repair utilities. o Part of a datafile being overwritten. o Oracle incorrectly attempting to access an unformatted block. o Oracle or operating system bug. Note 77587.1 discusses block corruptions in Oracle and how they are related to the underlying operating system and hardware. References: ----------- [NOTE:77587.1] BLOCK CORRUPTIONS ON ORACLE AND UNIX Note 2: ======= ORA-00600: Internal message code, arguments: [01578] [...] [...] [] [] []. ORA-01578: Oracle data block corrupted (file ..., block ...). Having encountered the Oracle data block corruption, we must firstly investigate which database segment (name and type) the corrupted block is allocated to. Chances are that the block belongs either to an index or to a table segment, since these two type of segments fill the major part of our databases. The following query will reveil the segment that holds the corrupted block identified by and (which were given to you in the error message): SELECT ds.* FROM dba_segments ds, sys.uet$ e WHERE ds.header_file=e.segfile# and ds.header_block=e.segblock# and e.file#= and between e.block# and e.block#+e.length-1; If the segment turns out to be an index segment, then the problem can be very quickly solved. Since all the table data required for recreating the index is still accessable, we can drop and recreate the index (since the block will reformatted, when taken FROM the free-space list and reused for the index). If the segment turns out to be a table segment a number of options for solving the problem are available: - restore and recovery of datafile the block is in - imp table - sql The last option involves using SQL to SELECT as much data as possible FROM the current corrupted table segment and save the SELECTed rows into a new table. SELECTing data that is stored in segment blocks that preceede the corrupted block can be easily done using a full table scan (via a cursor). Rows stored in blocks after the corrupted block cause a problem. A full table scan will never reach these. However these rows can still be fetched using rowids (single row lookups). 2.1 Table was indexed Using an optimizer hint we can write a query that SELECTs the rows FROM the table via an index scan (using rowid's), instead of via a full table scan. Let's assume our table is named X with columns a, b and c. And table X is indexed uniquely on columns a and b by index X_I, the query would look like: SELECT /*+index(X X_I) */ a, b, c FROM X; We must now exclude the corrupt block FROM being accessed to avoid the internal exception ORA-00600[01578]. Since the blocknumber is a substring of the rowid ( ) this can very easily be achieved: SELECT /*+index(X X_I) */ a, b, c FROM X WHERE rowid not like ||'.%.'||; But it is important to realize that the WHERE-clause gets evaluated right after the index is accessed and before the table is accessed. Otherwise we would still get the ORA-00600[01578] exception. Using the above query as a subquery in an insert statement we can restore all rows of still valid blocks to a new table. Since the index holds the actual column values of the indexed columns we could also use the index to restore all indexed columns of rows that reside in the corrupt block. The following query, SELECT /*+index(X X_I) */ a, b FROM X WHERE rowid like ||'.%.'||; retreives only indexed columns a and b FROM rows inside the corrupt block. The optimizer will not access the table for this query. It can retreive the column values using the index segment only. Using this technique we are able to restore all indexed column values of the rows inside the corrupt block, without accessing the corrupt block at all. Suppose in our example that column c of table X was also indexed by index X_I2. This enables us to completely restore rows inside the corrupt block. First restore columns a and b using index X_I: create table X_a_b(rowkey,a,b) as SELECT /*+index(X X_I) */ rowid, a, b FROM X WHERE rowid like ||'.%.'||; Then restore column c using index X_I2: create table X_c(rowkey,c) as SELECT /*+index(X X_I2) */ rowid, c FROM X WHERE rowid like ||'.%.'||; And finally join the columns together using the restored rowid: SELECT x1.a, x1.b, x2.c FROM X_a_b x1, X_c x2 WHERE x1.rowkey=x2.rowkey; In summary: Indexes on the corrupted table segment can be used to restore all columns of all rows that are stored outside the corrupted data blocks. Of rows inside the corrupted data blocks, only the columns that were indexed can be restored. We might even be able to use an old version of the table (via Import) to further restore non-indexed columns of these records. 2.2 Table has no indexes This situation should rarely occur since every table should have a primary key and therefore a unique index. However when no index is present, all rows of corrupted blocks should be considered lost. All other rows can be retrieved using rowid's. Since there is no index we must build a rowid generator ourselves. The SYS.UET$ table shows us exactly which extents (file#, startblock, endblock) we need to inspect for possible rows of our table X. If we make an estimate of the maximum number of rows per block for table X, we can build a PL/SQL-loop that generates possible rowid's of records inside table X. By handling the 'invalid rowid' exception, and skipping the corrupted data block, we can restore all rows except those inside the corrupted block. declare v_rowid varchar2(18); v_xrec X%rowtype; e_invalid_rowid exception; pragma exception_init(e_invalid_rowid,-1410); begin for v_uetrec in (SELECT file# file, block# start_block, block#+length#-1 end_block FROM uet$ WHERE segfile#=6 and segblock#=64) -- Identifies our segment X. loop for v_blk in v_uetrec.start_block..v_uetrec.end_block loop if v_uetrec.file<>6 and v_blk<>886 -- 886 in file 6 is our corrupted block. then for v_row in 0..200 -- 200 is maximum number of rows per block for segment X. loop begin SELECT a,b,c into v_rec FROM x WHERE rowid=chartorowid('0000'||hex(v_blk)||'.'|| hex(v_row)||'.'||hex(v_uetrec.file); insert into x_saved(a,b,c) values(v_rec.a,v_rec.b,v_rec.c); commit; exception when e_invalid_rowid then null; end; end loop; /*row-loop*/ end if; end loop; /*blk-loop*/ end loop; /*uet-loop*/ end; / The above code assumes that block id's never exceed 4 hexadecimal places. A definition of the hex-function which is used in the above code can be found in the appendix. Note 3: ======= Doc ID : Note:33405.1 Content Type: TEXT/PLAIN Subject: Extracting Data from a Corrupt Table using SKIP_CORRUPT_BLOCKS or Event 10231 Creation Date: 24-JAN-1996 Type: BULLETIN Last Revision Date: 13-SEP-2000 Status: PUBLISHED ***************** *** *** ***************** This note is an extension to article [NOTE:28814.1] about handling block corruption errors where the block wrapper of a datablock indicates that the block is bad. (Typically for ORA-1578 errors). The details here will not work if only the block internals are corrupt (eg: for ORA-600 or other errors). Please read [NOTE:28814.1] before reading this note. Introduction ~~~~~~~~~~~~ This short article explains how to skip corrupt blocks on an object either using the Oracle8i SKIP_CORRUPT table flag or the special Oracle event number 10231 which is available in Oracle releases 7 through 8.1 inclusive. The information here explains how to use these options. Before proceeding you should: a) Be certain that the corrupt block is on a USER table. (i.e.: not a data dictionary table) b) Have contacted Oracle Support Services and been advised to use event 10231 or the SKIP_CORRUPT flag. c) Have decided how you are to recreate the table. Eg: Export , and disk space is available etc.. d) You have scheduled down-time to attempt the salvage operation. e) Have a backup of the database. f) Have the SQL to rebuild the problem table, its indexes constraints, triggers, grants etc... This SQL should include relevant storage clauses. What is event 10231 ? ~~~~~~~~~~~~~~~~~~~~~ This event allows Oracle to skip certain types of corrupted blocks on full table scans ONLY hence allowing export or "create table as select" type operations to retrieve rows from the table which are not in the corrupt block. Data in the corrupt block is lost. The scope of this event is limited for Oracle versions prior to Oracle 7.2 as it only allows you to skip 'soft corrupt' blocks. Most ORA 1578 errors are a result of media corruptions and in such cases event 10231 is useless. From Oracle 7.2 onwards the event allows you to skip many forms of media corrupt blocks in addition to soft corrupt blocks and so is far more useful. It is still *NOT* guaranteed to work. [NOTE:28814.1] describes alternatives which can be used if this event fails. What is the SKIP_CORRUPT flag ? ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ In Oracle8i the functionality of the 10231 event has been externalised on a PER-SEGMENT basis such that it is possible to mark a TABLE or PARTITION to skip over corrupt blocks when possible. The flag is set or cleared using the DBMS_REPAIR package. DBA_TABLES has a SKIP_CORRUPT column which indicates if this flag is set for an object or not. Setting the event or flag ~~~~~~~~~~~~~~~~~~~~~~~~~ The event can either be set within the session or at database instance level. If you intend to use a CREATE TABLE AS SELECT then setting the event in the session may suffice. If you want to EXPORT the table data then it is best to set the event at instance level, or set the SKIP_CORRUPT table attribute if on Oracle8i. Oracle8i ~~~~~~~~ Connect as a DBA user and mark the table as needing to skip corrupt blocks thus: execute DBMS_REPAIR.SKIP_CORRUPT_BLOCKS('',''); or for a table partition: execute DBMS_REPAIR.SKIP_CORRUPT_BLOCKS('',''.''); Now you should be able to issue a CREATE TABLE AS SELECT operation against the corrupt table to extract data from all non-corrupt blocks, or EXPORT the table. Eg: CREATE TABLE salvage_emp AS SELECT * FROM corrupt_emp; To clear the attribute for a table use: execute DBMS_REPAIR.SKIP_CORRUPT_BLOCKS('','', flags=>dbms_repair.noskip_flag); execute DBMS_REPAIR.SKIP_CORRUPT_BLOCKS('VPOUSERDB','USERS', flags=>dbms_repair.noskip_flag); Setting the event in a Session ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ Connect to Oracle as a user with access to the corrupt table and issue the command: ALTER SESSION SET EVENTS '10231 TRACE NAME CONTEXT FOREVER, LEVEL 10'; Now you should be able to issue a CREATE TABLE AS SELECT operation against the corrupt table to extract data from all non-corrupt blocks, but an export would still fail as the event is only set within your current session. Eg: CREATE TABLE salvage_emp AS SELECT * FROM corrupt_emp; Setting the event at Instance level ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ This requires that the event be added to the init$ORACLE_SID.ora file used to start the instance: shutdown the database Edit your init.ora startup configuration file and ADD a line that reads: event="10231 trace name context forever, level 10" Make sure this appears next to any other EVENT= lines in the init.ora file. STARTUP If the instance fails to start check the syntax of the event parameter matches the above exactly. Note the comma as it is important. SHOW PARAMETER EVENT To check the event has been set in the correct place. You should see the initial portion of text for the line in your init.ora file. If not check which parameter file is being used to start the database. Select out the data from the table using a full table scan operation. Eg: Use a table level export or create table as select. Export Warning: If the table is very large then some versions of export may not be able to write more than 2Gb of data to the export file. See [NOTE:62427.1] for general information on 2Gb limits in various Oracle releases. Salvaging data from the corrupt block itself ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ SKIP_CORRUPT and event 10231 extract data from good blocks but skip over corrupt blocks. To extract information from the corrupt block there are three main options: - Select column data from any good indexes This is discussed towards the end of the following 2 articles: Oracle7 - using ROWID range scans [NOTE:34371.1] Oracle8/8i - using ROWID range scans [NOTE:61685.1] - See if Oracle Support can extract any data from HEX dumps of the corrupt block. - It may be possible to salvage some data using Log Miner Once you have the data extracted ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ Once you have the required data extracted either into an export file or into another table make sure you have a valid database backup before proceeding. The importance of this cannot be over-emphasised. Double check you have the SQL to rebuild the object and its indexes etc.. Double check that you have any diagnostic information if requested by Oracle support. Once you proceed with dropping the object certain information is destroyed so it is important to capture it now. Now you can: If 10231 was set at instance level: Remove the 'event' line from the init.ora file SHUTDOWN and RESTART the database. SHOW PARAMETER EVENT Make sure the 10231 event is no longer shown RENAME or DROP the problem table If you have space it is advisable to RENAME the problem table rather than DROP it at this stage. Recreate the table. Eg: By importing. Take special care to get the storage clauses correct when recreating the table. Create any indexes, triggers etc.. required Again take care with storage clauses. Re-grant any access to the table. If you RENAMEd the original table you can drop it once the new table has been tested. . Note 4: Analyze table validate structure: ========================================= validate structure table: ANALYZE TABLE CHARLIE.CUSTOMERS VALIDATE STRUCTURE; validate structure index: ANALYZE INDEX CHARLIE.PK_CUST VALIDATE STRUCTURE; Als er geen corrupte blocks worden gevonden, is de output slechts "table analyzed". Als er wel corrupte blocks worden gevonden, moet een aangemaakte trace file worden bekeken. Note 5: DBVERIFY Utility: ========================= Vanaf de OS prompt kan het dbv utility gedraaid worden om een datafile te onderzoeken. $ dbv FILE=/u02/oracle/cc1/data01.dbf BLOCKSIZE=8192 Note 6: DBMS_REPAIR package: ============================ Het DBMS_REPAIR package wordt aangemaakt door bmprpr.sql script. Stap 1. via ANALYZE TABLE ben je er achter gekomen dat van een table een of meer blocks corrupt zijn. Stap 2. Gebruik eerst DBMS_REPAIR.ADMIN_TABLES om de REPAIR_TABLE aan te maken. Deze table zal dan gegevens gaan bevatten over de blocks, en of die gemarkeerd zijn als zijnde corrupt e.d. declare begin dbms_repair.admin_tables('REPAIR_TABLE, dbms_repair.repair_table, dbms_repair.create_action, 'users'); end; / Stap 3. Gebruik nu de DBMS_REPAIR.CHECK_OBJECT procedure op het object om de repair_table uit stap 2 te vullen met corruptie gegevens. set serveroutput on declare rpr_count int; begin rpr_count:=0; dbms_repair.check_object('CHARLIE', 'CUSTOMERS', 'REPAIR_TABLE', rpr_count); dbms_output.put_line('repair_block_count :'||to_char(rpr_count)); end; / Note 7: ======= Tom, If I have this information: select * from V$DATABASE_BLOCK_CORRUPTION; FILE# BLOCK# BLOCKS CORRUPTION_CHANGE# CORRUPTIO ---------- ---------- ---------- ------------------ --------- 11 12357 12 197184960 LOGICAL and select * from v$backup_corruption; RECID STAMP SET_STAMP SET_COUNT PIECE# FILE# BLOCK# BLOCKS CORRUPTION_CHANGE# MAR CO ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ------------ 1 533835361 533835140 3089 1 11 12357 12 197184960 NO LOGICAL How can I get more details of what data resides on this blocks? and being 'Logical' can they be recoverd without loosing that data at all? Any extra details would be appreciated. Thanks, Orlando Followup: select * from dba_extents where file_id = 11 and 12357 between block_id an block_id+blocks-1; if it is something "rebuildable" -- like an index, drop and recreate might be the path of least resistance, else you would go back to your backups -- to before this was detected and restore that file/range of blocks (rman can do block level recovery) Tom trace file generated by analyze contained table scan: segment: file# 55 block# 229385 skipping corrupt block file# 55 block# 251372 This is repeated every day (analyzed each morning) but daily direct export / import succeeds. SQL> select segment_type from dba_extents where file_id=55 and 229385 between block_id and (block_id +( blocks -1)); SEGMENT_TYPE ---------------------------------------- TABLE $ dbv file=/u03/oradata/emu/emu_data_large02.dbf \ blocksize=8192 logfile=/dbv.log DBVERIFY: Release 8.1.7.2.0 - Production on Mon Aug 10 10:10:13 2004 (c) Copyright 2000 Oracle Corporation. All rights reserved. DBVERIFY - Verification starting : FILE = /u03/oradata/emu/emu_data_large02.dbf Block Checking: DBA = 230938092, Block Type = KTB-managed data block Found block already marked corrupted DBVERIFY - Verification complete Total Pages Examined : 256000 Total Pages Processed (Data) : 253949 Total Pages Failing (Data) : 0 Total Pages Processed (Index): 0 Total Pages Failing (Index): 0 Total Pages Processed (Other): 11 Total Pages Empty : 2040 Total Pages Marked Corrupt : 0 Total Pages Influx : 0 Any thoughts ? Thanks Note 6: ------- Detect And Correct Corruption Oracle provides a number of methods to detect and repair corruption within datafiles: DBVerify ANALYZE .. VALIDATE STRUCTURE DB_BLOCK_CHECKING. DBMS_REPAIR. Other Repair Methods. DBVerify DBVerify is an external utility that allows validation of offline datafiles. In addition to offline datafiles it can be used to check the validity of backup datafiles: C:>dbv file=C:\Oracle\oradata\TSH1\system01.dbf feedback=100 blocksize=4096 ANALYZE .. VALIDATE STRUCTURE The ANALYZE command can be used to verify each data block in the analyzed object. If any corruption is detected rows are added to the INVALID_ROWS table: -- Create the INVALID_ROWS table. SQL> @C:\Oracle\901\rdbms\admin\UTLVALID.SQL -- Validate the table structure. SQL> ANALYZE TABLE scott.emp VALIDATE STRUCTURE; -- Validate the table structure along with all it's indexes. SQL> ANALYZE TABLE scott.emp VALIDATE STRUCTURE CASCADE; -- Validate the index structure. SQL> ANALYZE INDEX scott.pk_emp VALIDATE STRUCTURE; DB_BLOCK_CHECKING When the DB_BLOCK_CHECKING parameter is set to TRUE Oracle performs a walk through of the data in the block to check it is self-consistent. Unfortunately block checking can add between 1 and 10% overhead to the server. Oracle recommend setting this parameter to TRUE if the overhead is acceptable. DBMS_REPAIR Unlike the previous methods dicussed, the DBMS_REPAIR package allows you to detect and repair corruption. The process requires two administration tables to hold a list of corrupt blocks and index keys pointing to those blocks. These are created as follows: BEGIN Dbms_Repair.Admin_Tables ( table_name => 'REPAIR_TABLE', table_type => Dbms_Repair.Repair_Table, action => Dbms_Repair.Create_Action, tablespace => 'USERS'); Dbms_Repair.Admin_Tables ( table_name => 'ORPHAN_KEY_TABLE', table_type => Dbms_Repair.Orphan_Table, action => Dbms_Repair.Create_Action, tablespace => 'USERS'); END; / With the administration tables built we are able to check the table of interest using the CHECK_OBJECT procedure: SET SERVEROUTPUT ON DECLARE v_num_corrupt INT; BEGIN v_num_corrupt := 0; Dbms_Repair.Check_Object ( schema_name => 'SCOTT', object_name => 'DEPT', repair_table_name => 'REPAIR_TABLE', corrupt_count => v_num_corrupt); Dbms_Output.Put_Line('number corrupt: ' || TO_CHAR (v_num_corrupt)); END; / Assuming the number of corrupt blocks is greater than 0 the CORRUPTION_DESCRIPTION and the REPAIR_DESCRIPTION columns of the REPAIR_TABLE can be used to get more information about the corruption. At this point the currupt blocks have been detected, but are not marked as corrupt. The FIX_CORRUPT_BLOCKS procedure can be used to mark the blocks as corrupt, allowing them to be skipped by DML once the table is in the correct mode: SET SERVEROUTPUT ON DECLARE v_num_fix INT; BEGIN v_num_fix := 0; Dbms_Repair.Fix_Corrupt_Blocks ( schema_name => 'SCOTT', object_name=> 'DEPT', object_type => Dbms_Repair.Table_Object, repair_table_name => 'REPAIR_TABLE', fix_count=> v_num_fix); Dbms_Output.Put_Line('num fix: ' || to_char(v_num_fix)); END; / Once the corrupt table blocks have been located and marked all indexes must be checked to see if any of their key entries point to a corrupt block. This is done using the DUMP_ORPHAN_KEYS procedure: SET SERVEROUTPUT ON DECLARE v_num_orphans INT; BEGIN v_num_orphans := 0; Dbms_Repair.Dump_Orphan_Keys ( schema_name => 'SCOTT', object_name => 'PK_DEPT', object_type => Dbms_Repair.Index_Object, repair_table_name => 'REPAIR_TABLE', orphan_table_name=> 'ORPHAN_KEY_TABLE', key_count => v_num_orphans); Dbms_Output.Put_Line('orphan key count: ' || to_char(v_num_orphans)); END; / If the orphan key count is greater than 0 the index should be rebuilt. The process of marking the table block as corrupt automatically removes it from the freelists. This can prevent freelist access to all blocks following the corrupt block. To correct this the freelists must be rebuilt using the REBUILD_FREELISTS procedure: BEGIN Dbms_Repair.Rebuild_Freelists ( schema_name => 'SCOTT', object_name => 'DEPT', object_type => Dbms_Repair.Table_Object); END; / The final step in the process is to make sure all DML statements ignore the data blocks marked as corrupt. This is done using the SKIP_CORRUPT_BLOCKS procedure: BEGIN Dbms_Repair.Skip_Corrupt_Blocks ( schema_name => 'SCOTT', object_name => 'DEPT', object_type => Dbms_Repair.Table_Object, flags => Dbms_Repair.Skip_Flag); END; / The SKIP_CORRUPT column in the DBA_TABLES view indicates if this action has been successful. At this point the table can be used again but you will have to take steps to correct any data loss associated with the missing blocks. Other Repair Methods Other methods to repair corruption include: Full database recovery. Individual datafile recovery. Block media recovery (BMR), available in Oracle9i when using RMAN. Recreate the table using the CREATE TABLE .. AS SELECT command, taking care to avoid the corrupt blocks by retricting the where clause of the query. Drop the table and restore it from a previous export. This may require some manual effort to replace missing data. Hope this helps. Regards Tim... Note 7: ------- If you know the file number and the block number indicating the corruption, you can salvage the data in the corrupt table by selecting around the bad blocks. Set event 10231 in the init.ora file to cause Oracle to skip software- and media- corrupted blocks when performing full table scans: Event="10231 trace name context forever, level 10" Set event 10233 in the init.ora file to cause Oracle to skip software- and media- corrupted blocks when performing index range scans: Event="10233 trace name context forever, level 10" Note 8: ------- Detecting and reporting data block corruption using the DBMS_REPAIR package: Note: Note that this event can only be used if the block "wrapper" is marked corrupt. Eg: If the block reports ORA-1578. 1. Create DBMS_REPAIR administration tables: To Create Repair tables, run the below package. SQL> EXEC DBMS_REPAIR.ADMIN_TABLES(‘REPAIR_ADMIN’, 1,1, ‘REPAIR_TS’); Note that table names prefix with ‘REPAIR_’ or ‘ORPAN_’. If the second variable is 1, it will create ‘REAIR_key tables, if it is 2, then it will create ‘ORPAN_key tables. If the thread variable is 1 then package performs ‘create’ operations. 2 then package performs ‘delete’ operations. 3 then package performs ‘drop’ operations. 2. Scanning a specific table or Index using the DBMS_REPAIR.CHECK_OBJECT procedure: In the following example we check the table employee for possible corruption’s that belongs to the schema TEST. Let’s assume that we have created our administration tables called REPAIR_ADMIN in schema SYS. To check the table block corruption use the following procedure: SQL> VARIABLE A NUMBER; SQL> EXEC DBMS_REPAIR.CHECK_OBJECT (‘TEST’,’EMP’, NULL, 1,’REPAIR_ADMIN’, NULL, NULL, NULL, NULL,:A); SQL> PRINT A; To check which block is corrupted, check in the REPAIR_ADMIN table. SQL> SELECT * FROM REPAIR_ADMIN; 3. Fixing corrupt block using the DBMS_REPAIR.FIX_CORRUPT_BLOCK procedure: SQL> VARIABLE A NUMBER; SQL> EXEC DBMS_REPAIR.FIX.CORRUPT_BLOCKS (‘TEST’,’EMP’, NULL, 1,’REPARI_ADMIN’, NULL,:A); SQL> SELECT MARKED FROM REPAIR_ADMIN; If u select the EMP table now you still get the error ORA-1578. 4. Skipping corrupt blocks using the DBMS_REPAIR. SKIP_CORRUPT_BLOCK procedure: SQL> EXEC DBMS_REPAIR. SKIP_CORRUPT.BLOCKS (‘TEST’, ‘EMP’, 1,1); Notice the verification of running the DBMS_REPAIR tool. You have lost some of data. One main advantage of this tool is that you can retrieve the data past the corrupted block. However we have lost some data in the table. 5. This procedure is useful in identifying orphan keys in indexes that are pointing to corrupt rows of the table: SQL> EXEC DBMS_REPAIR. DUMP ORPHAN_KEYS (‘TEST’,’IDX_EMP’, NULL, 2, ‘REPAIR_ADMIN’, ‘ORPHAN_ADMIN’, NULL,:A); If u see any records in ORPHAN_ADMIN table you have to drop and re-create the index to avoid any inconsistencies in your queries. 6. The last thing you need to do while using the DBMS_REPAIR package is to run the DBMS_REPAIR.REBUILD_FREELISTS procedure to reinitialize the free list details in the data dictionary views. SQL> EXEC DBMS_REPAIR.REBUILD_FREELISTS (‘TEST’,’EMP’, NULL, 1); NOTE Setting events 10210, 10211, 10212, and 10225 can be done by adding the following line for each event in the init.ora file: Event = "event_number trace name errorstack forever, level 10" - When event 10210 is set, the data blocks are checked for corruption by checking their integrity. Data blocks that don't match the format are marked as soft corrupt. - When event 10211 is set, the index blocks are checked for corruption by checking their integrity. Index blocks that don't match the format are marked as soft corrupt. - When event 10212 is set, the cluster blocks are checked for corruption by checking their integrity. Cluster blocks that don't match the format are marked as soft corrupt. - When event 10225 is set, the fet$ and uset$ dictionary tables are checked for corruption by checking their integrity. Blocks that don't match the format are marked as soft corrupt. - Set event 10231 in the init.ora file to cause Oracle to skip software- and media-corrupted blocks when performing full table scans: Event="10231 trace name context forever, level 10" - Set event 10233 in the init.ora file to cause Oracle to skip software- and media-corrupted blocks when performing index range scans: Event="10233 trace name context forever, level 10" To dump the Oracle block you can use below command from 8.x on words: SQL> ALTER SYSTEM DUMP DATAFILE 11 block 9; This command dumps datablock 9 in datafile11, into USER_DUMP_DEST directory. Dumping Redo Logs file blocks: SQL> ALTER SYSTEM DUMP LOGFILE ‘/usr/oracle8/product/admin/udump/rl. log’; Rollback segments block corruption, it will cause problems (ORA-1578) while starting up the database. With support of oracle, can use below under source parameter to startup the database. _CORRUPTED_ROLLBACK_SEGMENTS=(RBS_1, RBS_2) DB_BLOCK_COMPUTE_CHECKSUM This parameter is normally used to debug corruption’s that happen on disk. The following V$ views contain information about blocks marked logically corrupt: V$ BACKUP_CORRUPTION, V$COPY_CORRUPTION When this parameter is set, while reading a block from disk to catch, oracle will compute the checksum again and compares it with the value that is in the block. If they differ, it indicates that the block is corrupted on disk. Oracle makes the block as corrupt and signals an error. There is an overhead involved in setting this parameter. DB_BLOCK_CACHE_PROTECT=‘TRUE’ Oracle will catch stray writes made by processes in the buffer catch. Oracle 9i new RMAN futures: Obtain the datafile numbers and block numbers for the corrupted blocks. Typically, you obtain this output from the standard output, the alert.log, trace files, or a media management interface. For example, you may see the following in a trace file: ORA-01578: ORACLE data block corrupted (file # 9, block # 13) ORA-01110: data file 9: '/oracle/dbs/tbs_91.f' ORA-01578: ORACLE data block corrupted (file # 2, block # 19) ORA-01110: data file 2: '/oracle/dbs/tbs_21.f' $rman target =rman/rman@rmanprod RMAN> run { 2> allocate channel ch1 type disk; 3> blockrecover datafile 9 block 13 datafile 2 block 19; 4> } Recovering Data blocks Using Selected Backups: # restore from backupset BLOCKRECOVER DATAFILE 9 BLOCK 13 DATAFILE 2 BLOCK 19 FROM BACKUPSET; # restore from datafile image copy BLOCKRECOVER DATAFILE 9 BLOCK 13 DATAFILE 2 BLOCK 19 FROM DATAFILECOPY; # restore from backupset with tag "mondayAM" BLOCKRECOVER DATAFILE 9 BLOCK 13 DATAFILE 2 BLOCK 199 FROM TAG = mondayAM; # restore using backups made before one week ago BLOCKRECOVER DATAFILE 9 BLOCK 13 DATAFILE 2 BLOCK 19 RESTORE UNTIL 'SYSDATE-7'; # restore using backups made before SCN 100 BLOCKRECOVER DATAFILE 9 BLOCK 13 DATAFILE 2 BLOCK 19 RESTORE UNTIL SCN 100; # restore using backups made before log sequence 7024 BLOCKRECOVER DATAFILE 9 BLOCK 13 DATAFILE 2 BLOCK 19 RESTORE UNTIL SEQUENCE 7024; Note 9: ======= Displayed below are the messages of the selected thread. Thread Status: Closed From: nitinpawar@birlasunlife.com 23-Feb-05 11:51 Subject: ORA-01578 on system datafile RDBMS Version: Oracle9i Enterprise Edition Release 9.2.0.1.0 Operating System and Version: Windows 2000 Error Number (if applicable): ORA-01578 Product (i.e. SQL*Loader, Import, etc.): Product Version: ORA-01578 on system datafile A data block in SYSTEM tablespace datafile is corrupted. The error has been occuring since past 7 months. I noticed it recently when I took over the support. The database is in archivelog mode. We don't have any old hot backups of the database files. Both export and alert log indicate corrupt block to be # 7873, but dbverify declares block #7875 to be corrupt. It seems there is no object using the block. Following is the extract from the alert log. *** Corrupt block relative dba: 0x00401ec1 (file 1, block 7873) Fractured block found during buffer read Data in bad block - type: 16 format: 2 rdba: 0x00401ec1 last change scn: 0x0000.00007389 seq: 0x1 flg: 0x04 consistency value in tail: 0x23430601 check value in block header: 0x5684, computed block checksum: 0x396b spare1: 0x0, spare2: 0x0, spare3: 0x0 *** Reread of rdba: 0x00401ec1 (file 1, block 7873) found same corrupted data From: Oracle, Fahad Abdul Rahman 25-Feb-05 08:18 Subject: Re : ORA-01578 on system datafile Nitin, I would suggest you to relocate the system datafiles to a new location on disk and see if the corruption is removed. If the issue still persist ,then I would suggest you to log a TAR with Oracle Support for further research. ##################################################################################################### NOTE 10: ##################################################################################################### Find segments with corrupted block from alert log with ORA-01578 1. Get the list of corrupted blocks grep 'ORA-01578' alert_DB.log | sort -u > tmp.txt 2. Generate SQL script to find segments with corrupted blocks cat tmp.txt | awk '{ printf("%s %s\n", $8, $11); }' | \ awk -F\, '{ printf ("%s %s\n", $1, $2); }' | \ awk -F\) '{ printf("%s\n", $1); }' | \ awk '{printf ("SELECT SEGMENT_NAME, SEGMENT_TYPE, RELATIVE_FNO FROM DBA_EXTENTS WHERE FILE_ID = %s AND %s BETWEEN BLOCK_ID AND BLOCK_ID + BLOCKS - 1;\n", $1, $2); }' > find_segs.sql 3. Run SQL to find out which objects have corrupted blocks SQLPLUS> col SEGMENT_NAME format a30 SQLPLUS> spool corrupted_segments.log SQLPLUS> @find_segs SQLPLUS> spool off 4. Sort the output to remove duplicates sort -u corrupted_segments.log ##################################################################################################### NOTE 11: ##################################################################################################### http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:1184635450789 You Asked I´ve recently been aware of the undocumented init.ora parameters -- those begining with "_" (underline). There is list of them in a book available on the market. In which situation could (or should) we, mortal DBAs, use them ? Do you remeber any specific situation where it can be necessary to take advantage of them ? In what aspects do they differ from the documented ones ? Can we regularily use them in any init.ora file ? Can you give me an example ? Thanks ! and we said... In my development database, i use only one -- _TRACE_FILES_PUBLIC. That makes trace files readable by all, not just the DBA group. In my real database, I use none. On my development database I want my developers (well, i demand it actually) to use sql_trace+timed_statistics and tkprof frequently -- hence they need to be able to read the trace files. In general -- you will only use these undocumented parameters at the request of Oracle Support. The use of them can be damaging to a database and their implementation can and will change from release to release. Reviews oracle support April 28, 2002 - 8am US/Eastern Bookmark | Bottom | TopReviewer: liz from fl helllo tom i need more underestanding of the folowing In general -- you will only use these undocumented parameters at the request of Oracle Support. The use of them can be damaging to a database and their implementation can and will change from release to release. Followup April 28, 2002 - 10am US/Eastern: Hmmm, I don't know how to make it more clear. Basically I'm saying: DO NOT use undocumented parameters that begin with underscores. If you do, you may damage your database. Many of these parameters cause side effects that if you are not aware of them, could seriously damage your database. Until a parameter is documented, it is unsafe. Use at your own risk. parameters December 23, 2002 - 9am US/Eastern Bookmark | Bottom | TopReviewer: mo Tom: Does "Select * from v$parameter" give you all the parameters defined for that database? Are those the same defined in the init.ora paramete file? 2. how you query the session parameters? any v$ for that? Thank you, Followup December 23, 2002 - 11am US/Eastern: yes, maybe. many of the session parameters modify your view of the v$parameter table. change your sort_area_size for example and see. How to list the undocumented parameters.... January 27, 2005 - 1pm US/Eastern Bookmark | Bottom | TopReviewer: Ma$e Hi Tom: Could you please provide me the method to list all undocumented parameters? Thanks Ma$e Followup January 27, 2005 - 2pm US/Eastern: why, you won't be changing them.... (you should have seen that coming, based on the pre-existing text....) you can always google oracle undocumented parameters Here is the SQL January 28, 2005 - 3am US/Eastern Bookmark | Bottom | TopReviewer: Rajesh from Hyderabad, India SELECT a.ksppinm "Parameter", a.ksppdesc "Description", b.ksppstvl "Session Value", c.ksppstvl "Instance Value" FROM x$ksppi a, x$ksppcv b, x$ksppsv c WHERE a.indx = b.indx AND a.indx = c.indx AND a.ksppinm LIKE '/_%' escape '/' / Followup January 28, 2005 - 7am US/Eastern: borrowed straight from the first link google returns when you did the search.... just remember to consider the source Well, Thanks Tom January 30, 2005 - 11pm US/Eastern Bookmark | Bottom | TopReviewer: Rajesh from Hyderabad, India All i needed was a response from you Tom!! and I am glad ! (will nomore behave silly!) the source for the above SQL :: http://www.dba-oracle.com/oracle_faq/faq_dba_9i_hidden_parms.htm Followup January 31, 2005 - 8am US/Eastern: (just remember to consider the source -- didn't need to attribute it, that wasn't what I was after) Well, Got it now... May 19, 2005 - 3am US/Eastern Bookmark | Bottom | TopReviewer: Rajesh from Hyderabad, India Finally, after 6 months of extensive reading here(and elsewhere), I realised the blunder I made :-) ##################################################################################################### NOTE 11: ##################################################################################################### http://www.fors.com/velpuri2/dul_ucg8.html Table of contents Principles and feature list Setting Up and Using DUL Configuration Files Data Dictionary available No Data Dictionary Migrated database UNLOAD statement overview Output format Some DUL internals DDL - DUL Dump Language specification DDL description DUL startup behaviour Configuring DUL DUL paramater file: init.dul Port specific parameters Control file: control.dul Sample unload sessions Data Dictionary available Without Data Dictionary Incorrect init.dul Parameters SQL*Loader problems and work arounds Script to unload Data Dictionary DUL's PRINCIPLES and FEATURE LIST STANDALONE C-PROGRAM DUL is a standalone C program that directly retrieves rows from tables in data files. The Oracle RDBMS software is NOT used at all. DUL does dirty reads, it assumes that every transaction is committed. Nor does it check/require that media recovery has been done. LAST RESORT DUL is intended to retrieve data that cannot be retrieved otherwise. It is NOT an alternative for EXP, SQL*Plus etc. It is meant to be a last resort, not for normal production usage. Before you use DUL you must be aware that the rdbms has many hidden features to force a bad database open. Undocumented init.ora parameters and events can be used to skip roll forward, to disable rollback and more. DATABASE CORRUPT - BLOCKS OK The database can be corrupted, but an individual data block used must be 100% correct. During all unloading checks are made to make sure that blocks are not corrupted and belong to the correct segment. If during a scan a bad block is encountered, an error message is printed in the loader file and to standard output. Unloading will continue with the next row or block. ROWS in CLUSTERS/TABLES/INDEXES DUL can and will only unload table/cluster data. It will NOT dump triggers, stored procedures nor create scripts for tables or views. (But the data dictionary tables describing them can be unloaded). The data will be unloaded in a format suitable for SQL*Loader or IMP. A matching control file for SQL*Loader is generated as well. DUL8 can unload indices and index organized tables. Index unload is usefull to determine how many rows a table should have or to identify the missing rows. CROSS PLATFORM UNLOADING Cross-platform unloading is supported. The database can be copied from a different operating system than the DUL-host. (Databases/systems done so far: Sequent/ptx, Vax Vms, Alpha Vms, MVS, HP9000/8xx, IBM AIX, SCO Unix, Alpha OSF/1, Intel Windows NT). The configuration parameters within "init.dul" will have to be modified to match those of the original platform and O/S rather than the platform from which the unload is being done. ROBUST DUL will not dump, spin or hang no matter how badly corrupted the database is. (NEARLY) ALL ORACLE FEATURES SUPPORTED Full support for all database constructs: row chaining, row migration, hash/index clusters, longs, raws, rowids, dates, numbers, multiple free list groups, segment high water mark, NULLS, trailing NULL columns, and unlimited extents, new block layout of Oracle8, partitioned tables, Index Organized Tables and LOBS. SUPPORTED RDBMS VERSIONS DUL should work with all versions 6,7, 8 and 8i. DUL has been tested with versions from 6.0.26 up to 7.3.2 and 8.1.7. Even the old block header layout (pre 6.0.27.2) is supported. MULTI BYTE SUPPORT DUL is essentially a single byte application. The command parser does not understand multi byte characters, but it is possible to unload any multi byte database. For all possible caveats there is a work around. RESTRICTIONS MLSLABELS Multi Level Security Lables of trusted oracle are not supported. (LONG) RAW DUL can unload (long) raws, but there is no way to reload these 1-to-1 with SQL*Loader. There is no suitable format in SQL*Loader to preserve all long raws. Use the export mode instead or write a Pro*C program to load the data. ORACLE8 OBJECT OPTION AND LOBS Varrays, objects, and nested tables are not yet supported PORTABLE DUL can be ported to any operating system with an ANSI-C compiler. DUL has been ported to many UNIX variants, VMS and WindowsNT. RDBMS INTERNALS A good knowledge of the Oracle RDBMS internals is a pre requisite to be able to use DUL successfully. Andre Bakker's two days internals course is a minimum. SETTING UP and USING DUL CONFIGURATION FILES There are two configuration files for DUL. "init.dul" contains all configuration parameters. (size of caches, details of header layout, oracle block size, output file format) In the control file, "control.dul", the data file names and the oracle file numbers must be specified. DATA DICTIONARY AVAILABLE The Oracle data dictionary is available if the data files which made up the SYSTEM TableSpace are available and useable. The number which Oracle assigned to these files and the name you have given them, which does not have to be the original name which Oracle knew, must be included in the "control.dul" file. You also need to eventually include the file numbers and names of any files from other TableSpaces for which you wish to eventually unload TABLES and their data. The lack of inclusion of these files will not affect the data dictionary unload step but it will affect later TABLE unloading. USING DUL WHEN USER$, OBJ$, TAB$ and COL$ CAN BE UNLOADED Steps to follow: configure DUL for the target database. This means creating a correct init.dul and control.dul. The SYSTEM TableSpace's data file numbers and names must be included within the control.dul file along with any data files for TableSpaces from which you wish to unload TABLEs and their data. For Oracle8 the tablespace number and the relative file number must be specified for each datafile. Unload the (first) four data dictionary tables. Use "dul dictv7.ddl" for Oracle7 or "dul dictv6.ddl" for version 6 of Oracle. Oracle8 requires "dictv8" "dictv8" will fail for a database that has been migrated from oracle7 to oracle8 or higher. In this case you need to follow the generic bootstrap procedure Re-start DUL and unload the tables for which data files have been included within the "control.dul" file. Use one of the following commands: "UNLOAD TABLE owner.table ; (do not forget the semicolon) This will unload the one table definition and the table's data. "UNLOAD USER user name ; This unloads all tables and data for the specified user. "UNLOAD DATABASE ; This unloads all of the database tables available. Migrated database A database that has been migrated from Oracle7 to Oracle has a different data dictionary. Be aware that it is very possible that the seed database used for Rapid Install (or one hour install) for apps database has been migrated. So even a seemingly brand new database could be in fact migrated. That is the tables are the same but they are difficult to find for DUL, because they are not in the usual place, and have different object ids. A sure sign of a migrated database is that the file number of the first system datafile is not 1 but 4 or another power of 2. (Due to the file bit layout change for oracle 8) The solution is the generic bootstrap procedure that follows the way the rdbms starts in more detail. Generic Bootstrap Procedure There is a new generic bootstrap procedure. The old dict.dll scripts are still supplied as a handy shortcut. If they fail you should use the complete procedure. scan database; # build segment map and locate compatibility segment restart dul # load the just generated files bootstrap; # find location of bootstrap$ in comp segment and unload restart dul # load the just generated files bootstrap generate; # generate dict.ddl for first four tables @dict.ddl # unload the first four tables restart dul # load the just generated files bootstrap generate; #generate dict.ddl for all tables @dict.ddl # unload all dictionary tables restart dul # now your ready to go NO DATA DICTIONARY AVAILABLE If data files are not available for the SYSTEM TableSpace the unload can still continue but USER, TABLE and COLUM names will not be known. Identifying the tables can be an overwhelming task. But it can be (and has been) done. You need in depth knowledge about your application and the application tables. Column types can be guessed by DUL, but table and column names are unknown. Any old SYSTEM tablespace from the same database but weeks old can be of great help!. The information that DUL uses does not change. USING DUL WITHOUT SYSTEM TABLESPACE Steps to follow: configure DUL for the target database. This means creating a correct init.dul and control.dul. (See Port specific parameters ). In this case control.dul file will need the numbers and names of datafiles from which TABLEs and data will be unloaded but it does not require the SYSTEM TableSpace's information. SCAN DATABASE; : scan the database, build extent and segment map SCAN TABLES; or SCAN EXTENTS; : gather row statistics Identify the lost tables from the output of step 3. UNLOAD the identified tables. AUTOMATED SEARCH To ease the hunt for the lost tables: the scanned statistical information in seen_tab.dat and seen_col.dat can be loaded into a fresh database. If you recreate the tables ( Hopefully the create table scripts are still available) then structure information of a "lost" table can be matched to the "seen" tables scanned information with two SQL*Plus scripts. (fill.sql and getlost.sql). HINTS WHEN LOOKING FOR TABLES: Names are not really relevant for DUL, only for the person who must load the data. But the unloaded data does not have any value, if you do not know from which table it came. The guessed column types can be wrong. Even though the algorithm is conservative and decides UNKNOWN if not sure. Trailing NULL columns are not stored in the database. So if the last columns only contain NULL's than the scanner will NOT find them. (During unload trailing NULL columns are handled correctly). When a table is dropped, the description is removed from the data dictionary only. The data blocks are not overwritten unless they are reused for a new segment. So the scanner software can see a table that has been dropped. Tables without rows will go unnoticed. Newer objects have a higher object id than older objects. If an table is recreated, or if there is a test and a production version of the same table the object id can be used to decide. DDL (DUL Description Language) UNLOAD STATEMENT OVERVIEW DUL uses an SQL like command interface. There are DDL statements to unload extents, tables, users or the entire database. Data dictionary information required can be specified in the ddl statements or taken from the previously unloaded data dictionary. The following three statements will unload the DEPT table. The most common form is if the data dictionary and the extent map are available: UNLOAD TABLE scott.dept; All relevant information can be specified in the statement as well: REM Columns with type in the correct order REM The segment header loaction in the storage clause UNLOAD TABLE dept( deptno NUMBER, dname CHAR, loc CHAR) STORAGE( EXTENTS ( FILE 1 BLOCK 1205 )); Oracle version 6: REM version 6 data blocks have segment header location in each block ALTER SESSION SET USE_SCANNED_EXTENT_MAP = TRUE; UNLOAD TABLE dept( deptno NUMBER, dname CHAR, loc CHAR) STORAGE( EXTENTS ( FILE 1 BLOCK 1205 )); Oracle7: REM Oracle7 data blocks have object id in each block ALTER SESSION SET USE_SCANNED_EXTENT_MAP = TRUE; UNLOAD TABLE dept( deptno NUMBER, dname CHAR, loc CHAR) STORAGE( OBJNO 1501 ); DUL'S OUTPUT FORMAT. There are three different modes of output format. Export mode SQL*Loader mode: stream data files SQL*Loader mode: Fixed physical record data files EXPORT MODE Export mode is a feature available only in DUL version 3 or DUL8. To enable export mode, you must set the init.dul parameter EXPORT_MODE to TRUE. For each table a separate IMP loadable file will be generated. The generated file is completely different from a table mode export generated by EXP! The file is the minimal format that IMP can load. It is a single table dump file. With only an insert table statement and the table data. Table grants, storage clauses, or triggers will not be included. An optional create table statement is included if the COMPATIBLE parameter has been set to 6 or 7. The character set indication in the file in the generated header is V6 style. It is set to mean ASCII based characterset. Extreme care has been taken that the dump file can always be loaded with imp. Only complete good rows are written to the dump file. For this each row is buffered. The size of this buffer can changed with the init.dul parameter BUFFER. Incomplete or bad rows are not written out. SQL*LOADER MODES For both SQL*Loader output formats the columns will be space separated and enclosed in double quotes. Any double quote in the data will be doubled. SQL*Loader recognizes this and will load only one. The character used to enclose the columns can be changed from double quote to any character you like with the init.dul parameter LDR_ENCLOSE_CHAR. There are two styles of physical record organization: Stream Mode Nothing special is done in stream mode, a newline is printed after each record. This is a compact format and can be used if the data does not contain newline characters. To enable stream mode set LDR_PHYS_REC_SIZE = 0 in init.dul. Fixed Physical Records This mode is essential if the data can contain newlines. One logical record, one comlete row, can be composed of multiple physical records. The default is record length is 81, this fits nicely on the screen of a VT220. The physical record size can be specified with LDR_PHYS_REC_SIZE in init.dul. OUTPUT FILE NAMES The file names generated are: owner name_table name.ext. The extension is ".dmp" for IMP loadable files. ".dat" and ".ctl" are used for the SQL*Loader datafile and the control file. To prevent variable substitution and other unwanted side effects, strange characters are stripped.(Only alpha numeric and '_' are allowed). If the FILE parameter is set the generated names will be FILEnnn.ext. This possibility is a work around if the file system does not support long enough file names. SOME DUL INTERNALS REQUIRED INFORMATION To unload table data from a database block the following information must be known: Column/Cluster Information: The number and type of the columns. For char or varchar columns the maximum length as well. The number of cluster columns and the table number in the cluster. This information can be supplied in the unload statement or it can be taken from the previously unloaded USER$, OBJ$, TAB$ and COL$. Segment/Extent information: When unloading a table the extent table in the data segment header block is used to locate all data blocks. The location of this segment header block (file number and block number) is taken from the data dictionary or can be specified in the unload statement. If the segment header is not correct/available then another method must be used. DUL can build its own extent map by scanning the whole database. (in a separate run of DUL with the scan database statement.) BINARY HEADERS C-Structs in block headers are not copied directly, they are retrieved with specialized functions. All offsets of structure members are programmed into DUL. This approach makes it possible to cross-unload. (Unload an MVS created data file on an HP) Apart from byte order only four layout types have been found so far. Vax VMS and Netware : No alignment padding between structure members. Korean Ticom Unix machines : 16 bit alignment of structure members. MS/DOS 16 bit alignment and 16 bit wordsize. Rest of the world (Including Alpha VMS) structure member alignment on member size. MACHINE DEPENDENCIES Machine dependencies (of the database) are configurable with parameters: Order of bytes in a word (big/little endian). Number of bits for the low part of the FILE# in a DBA (Block Address). Alignment of members in a C-struct. Number of blocks or bytes before the oracle file header block. Size of a word used in the segment header structure. UNLOADING THE DATA DICTIONARY DUL can use the data dictionary of the database to be unloaded if the files for it exist and are uncorrupted. For the data dictionary to be used, internal tables must be unloaded first to external files: (USER$, OBJ$, TAB$ and COL$). Scripts are provided to assist in this task. They are "dictv6.ddl" which is to be used with Oracle version 6 databases and "dictv7.ddl" for Oracle7 databases. These scripts are different for different DUL versions and cannot be intermixed.. DDL ( DUL DESCRIPTION LANGUAGE ) SPECIFICATION [ ALTER SESSION ] SET init.dul parameter = value ; COMMIT; commit will write the updated block to disk. The init.dul parameter writable_datafiles must be set to true; DESCRIBE owner_name . table_name ; DUMP [ TABLESPACE tablespace_no ] [ FILE file_no ] [ BLOCK block_no ] [ LEVEL level_no ] ; sets the current block and dumps it(headers only), last location is remembered accross calls. PREPARE; Must be done after unloading the data dictionary. It will unload all the lob indexes needed to unload lobs. UNLOAD DATABASE; UNLOAD USER user_name; UNLOAD TABLE [ schema_name . ] table_name [ ( column_definitions ) ] [ cluster_clause ] [ storage_clause ] ; UNLOAD EXTENT table_name [ ( column_definitions ) ] [ TABLESPACE tablespace_no ] FILE extent_start_file_number BLOCK extent_start_block_number BLOCKS extent_size_in oracle_blocks UPDATE SET {ub1|ub2|ub4} @ offset = new_value ; Updates the indicated variable at the specified offset to be changed in the current block in memory only. Navigate to the current block with dump. If you like the result, use commit to really write the change. For instance to fix avsp corruption in c_ts# cluster: dump block 1093; # move to file 1 block 1093 update set ub2@102 = 7478; # avsp is of type ub2 and often at offset 102 # 7478 is in this case the new value commit; # write the change to disk. storage_clause ::= STORAGE ( storage_specification [ more_storage_specs ] ) storage_specification ::= OBJNO object_id_number | TABNO cluster_table_number | SEGOBJNO cluster/data_object_number /* v7/v8 style data block id */ | FILE data_segment_header_file_number /* v6 style data block id */ BLOCK data_segment_header_block_number ) | any_normal_storage_specification_but_silently_ignored SCAN DATABASE; Scans all blocks of all data files. Two files are generated: 1: seg.dat information of found segment headers (index/cluster/table): (object id, file number, and block number). 2: ext.dat information of contiguous table/cluster data blocks. (object id(V7), file and block number of segment header (V6), file number and block number of first block, number of blocks, number of tables) SCAN TABLES; Uses seg.dat and ext.dat as input. Scans all tables in all data segments (a header block and at least one matching extent with at least 1 table). SCAN EXTENTS; Uses seg.dat and ext.dat as input. All extents for which no corresponding segment header has been found. (Only useful if a tablespace is not complete, or a segment header is corrupt). REM any_text_you_like_till_End_Of_Line : comment NOT allowed inside ddl statements. ( To avoid a two layer lexical scan). EXIT QUIT and EOF all cause DUL to terminate. DDL ( DUL DESCRIPTION LANGUAGE ) DESCRIPTION Rules for UNLOAD EXTENT and UNLOAD TABLE: Extent Map UNLOAD TABLE requires an extent map. In 99.99% of the cases the extent map in the segment header is available. In the rare 0.01% that the segment header is lost an extent map can be build with the scan database command. The self build extent map will ONLY be used during an unload if the parameter USE_SCANNED_EXTENT_MAP is set to TRUE. All data blocks have some ID of the segment they belong to. But there is a fundamental difference between V6 and V7. Data blocks created by Oracle version 6 have the address of the segment header block. Data blocks created by Oracle7 have the segment object id in the header. Column Specification The column definitions must be specified in the order the columns are stored in the segment, that is ordered by col$.segcol#. This is not necessarily the same order as the columns where specified in the create table statement. Cluster columns are moved to the front, longs to the end. Columns added to the table with alter table command, are always stored last. Unloading a single extent UNLOAD EXTENT can be used to unload 1 or more adjacent blocks. The extent to be unloaded must be specified with the STORAGE clause: To specify a single extent use: STORAGE ( EXTENTS( FILE fno BLOCK bno BLOCKS #blocks) ) (FILE and BLOCK specify the first block, BLOCKS the size of the extent) DUL specific column types There are two extra DUL specific data types: UNKNOWN: a heuristic guess will be made for each column. IGNORE: the column will be skipped as if it was not there at all. Identifying USER$, OBJ$, TAB$ and COL$ There is a "hidden" trick with file and object numbers that is used to locate the data dictionary tables. The trick is based on the fact that object numbers are fixed for OBJ$, COL$, USER$ and TAB$ due to the rigid nature of sql.bsq. This will not be documented because I myself could not understand my first attempt to describe it. DESCRIPTION OF SCAN COMMANDS SCAN TABLES and SCAN EXTENTS scan for the same information and produce similar output. ALL columns of ALL rows are inspected. For each column the following statistics are gathered: How often the column is seen in a data block. The maximum internal column length. How often the column IS NULL. How often the column consists of at least 75% printable ascii. How often the column consists of 100% printable ascii. How often the column is a valid oracle number. How often the column is a nice number. (not many leading or trailing zero's) How often the column is a valid date. How often the column is a possible valid rowid. These statistics are combined and a column type is suggested. Using this suggestion five rows are unloaded to show the result. These statistics are dumped to two files (seen_tab.dat and seen_col.dat). There are SQL*Loader and SQL*Plus scripts available to automate a part of the identification process. (Currently known as the getlost option). DESCRIBE There is a describe command. It will show the dictionary information for the table, available in DUL's dictionary cache. DUL STARTUP SEQUENCE During startup DUL goes through the following steps: the parameter file "init.dul" is processed. the DUL control file (default "control.dul") is scanned. Try to load dumps of the USER$, OBJ$, TAB$ and COL$ if available into DUL's data dictionary cache. Try to load seg.dat and col.dat. Accept DDL-statements or run the DDL script specified as first arg. DUL parameters to be specified in init.dul: ALIGN_FILLER OBSOLETE Replaced by OSD_C_STRUCT_ALIGNMENT ASCII2EBCDIC BOOLEAN Must (var)char fields be translated from EBCDIC to ASCII. (For unloading MVS database on a ASCII host) BIG_ENDIAN_FLAG OBSOLETE Replaced by OSD_BIG_ENDIAN_FLAG BLOCKS_TO_SKIP OBSOLETE BUFFER NUMBER (bytes) row output buffer size used in export mode only. In export mode each row is first stored in this buffer, before it is written to the dump file. COMPATIBLE NUMBER Database version , valid values are 6, 7 or 8. This parameter must be specified CONTROL_FILE TEXT Name of the DUL control file (default: "control.dul"). DBA_FILE_BITS OBSOLETE Replaced by OSD_DBA_FILE_BITS DB_BLOCK_SIZE NUMBER Oracle block size in bytes (Maximal 32 K) DB_LEADING_OFFSET OBSOLETE Replaced by OSD_FILE_LEADER_SIZE DC_COLUMNS NUMBER DC_OBJECTS NUMBER DC_TABLES NUMBER DC_USERS NUMBER Sizes of dul dictionary caches. If one of these is too low startup will fail. EXPORT_MODE BOOLEAN EXPort like output mode or SQL*Loader format FILE TEXT Base for (dump or data) file name generation. Use this on 8.3 DOS like file systems LDR_ENCLOSE_CHAR TEXT The character to enclose fields in SQL*Loader mode. LDR_PHYS_REC_SIZE NUMBER Physical record size for the generated loader datafile. LDR_PHYS_REC_SIZE = 0 No fixed records, each record is terminated with a newline. LDR_PHYS_REC_SIZE > 2: Fixed record size. MAX_OPEN_FILES Maximum # files that are concurrently kept open at the OS level. MAX_UNLOAD_BLOCKS OBSOLETE OSD_BIG_ENDIAN_FLAG Byte order in machine word. Big Endian is also known as MSB first. DUL8 sets the default according to the machine it is running on. For an explanation why this is called Big Endian, you should read Gullivers Travels. OSD_DBA_FILE_BITS File Number Size in DBA in bits. Or to be more precise the size of the low order part of the file number. OSD_FILE_LEADER_SIZE bytes/blocks added before the real oracle file header block OSD_C_STRUCT_ALIGNMENT C Structure member alignment (0,16 or 32) Must be set to 32 for most ports OSD_WORD_SIZE Size of a machine word always 32, except for MS/DOS(16) PARSE_HEX_ESCAPES Use \\xhh hex escape sequences in strings while parsing. The default value is FALSE. If set to true then strange characters can be specified using escape sequences. This feature is also for specifying multi-byte characters. TICOM_FILLER OBSOLETE The new parameter is OSD_C_STRUCT_ALIGNMENT USE_SCANNED_EXTENT_MAP BOOLEAN Use the scanned extent map in ext.dat in unload table. This parameter is only useful if some segment headers are missing or incorrect. SAMPLE init.dul : # sample init.dul configuration parameters # these must be big enough for the database in question # the cache must hold all entries from the dollar tables. dc_columns = 200000 dc_tables = 10000 dc_objects = 10000 dc_users = 40 # OS specific parameters osd_big_endian_flag = false osd_dba_file_bits = 6 osd_c_struct_alignment = 32 osd_file_leader_size = 1 # database parameters db_block_size = 2048 # loader format definitions LDR_ENCLOSE_CHAR = " LDR_PHYS_REC_SIZE = 81 Configuring the port dependent parameters osd_big_endian_flag big endian or little endian (byte order in machine words): HP, SUN and mainframes are generally big endian: OSD_BIG_ENDIAN_FLAG = TRUE. DEC and Intel platforms are little endian: OSD_BIG_ENDIAN_FLAG = FALSE. The default is correct for the platform where DUL is running on. There is no standard trick for this, the following might work on a unix system: echo dul | od -x If the output is like: 0000000 6475 6c0a 0000004 You are on a big endian machine (OSD_BIG_ENDIAN_FLAG=TRUE). If you see: 0000000 7564 0a6c 0000004 This is a little endian machine (OSD_BIG_ENDIAN_FLAG=FALSE). osd_dba_file_bits The number of bits in a dba used for the low order part of file number. For most platforms osd_dba_file_bits = 10 for Oracle8. To be sure perform the following query: SQL> select dump(chartorowid('0.0.1')) from dual; Typ=69 Len=6: 8,0,0,0,0,0 -> osd_dba_filebits = 5 (SCO) Typ=69 Len=6: 4,0,0,0,0,0 -> osd_dba_filebits = 6 (Sequent , HP) Typ=69 Len=6: 1,0,0,0,0,0 -> osd_dba_filebits = 8 (NCR,AIX) Typ=69 Len=6: 0,16,0,0,0,0 -> osd_dba_filebits = 12 (MVS) Typ=69 Len=10: 0,0,0,0,0,64,0,0,0,0 osd_dba_filebits = 10 (Oracle8) OSD_C_STRUCT_ALIGNMENT Structure layout in data file headers. 0: No padding between members in a C-struct (VAX/VMS only) 16: Some korean ticom machines and MS/DOS 32: Structure members are member size aligned. (All others including ALPHA/VMS) Check the following query: SELECT * FROM v$type_size WHERE type IN ( 'KCBH', 'KTNO', 'KCBH', 'KTBBH', 'KTBIT', 'KDBH' , 'KTECT', 'KTETB', 'KTSHC') ; In general osd_c_struct_alignment = 32 and the following output is expected: K KTNO TABLE NUMBER IN CLUSTER 1 KCB KCBH BLOCK COMMON HEADER 20 KTB KTBIT TRANSACTION VARIABLE HEADER 24 KTB KTBBH TRANSACTION FIXED HEADER 48 KDB KDBH DATA HEADER 14 KTE KTECT EXTENT CONTROL 44 KTE KTETB EXTENT TABLE 8 KTS KTSHC SEGMENT HEADER 8 8 rows selected. For VAX/VMS and Netware ONLY osd_c_struct_alignment = 0 and this output is expected: COMPONEN TYPE DESCRIPTION SIZE -------- -------- -------------------------------- ---------- K KTNO TABLE NUMBER IN CLUSTER 1 KCB KCBH BLOCK COMMON HEADER 20 KTB KTBIT TRANSACTION VARIABLE HEADER 23 KTB KTBBH TRANSACTION FIXED HEADER 42 KDB KDBH DATA HEADER 14 KTE KTECT EXTENT CONTROL 39 KTE KTETB EXTENT TABLE 8 KTS KTSHC SEGMENT HEADER 7 8 rows selected. If there is a different list this will require some major hacking and sniffing and possibly a major change to DUL. (Email bduijnen@nl.oracle.com) osd_file_leader_size Number of blocks/bytes before the oracle file header. Unix datafiles have an extra leading block ( file size, block size magic number) A large number ( > 100) is seen as a byte offset, a small number is seen as a number of oracle blocks. Unix : osd_file_leader_size = 1 Vms : osd_file_leader_size = 0 Desktop : osd_file_leader_size = 512 Others : Unknown ( Use Andre Bakker's famous PATCH utility to find out) An Oracle7 file header block starts with the pattern 0X0B010000. You can add an additional byte offset in control.dul in the optional third field (for instance for AIX or DEC UNIX data files on raw device) Control file specification A control file (default name "control.dul") is used to translate the file numbers to file names. The format of the control has been extended: if COMPATIBLE is 6 or 7: control_file_line_for_6_or_7 ::= file_number data_file_name [ optional extra leader offset ] [ startblock block_no ] [ endblock block_no ] If COMPATIBLE is 8: control_file_line_for_8 ::= tablespace_no relative_file_number data_file_name [ optional extra leader offset ] [ startblock block_no ] [ endblock block_no ] Each entry on a separate line. The optional extra leader offset is an extra byte offset, that will be added to all lseek() operations for that datafile. This makes it possible to skip over the extra block for AIX on raw devices. Each entry can contain a part of a datafile. This way it is possible to split datafiles that are too big for DUL in parts where each part is smaller than 2GB. For instance: # AIX version 7 example with one file on raw device 1 /usr/oracle/dbs/system.dbf 8 /dev/rdsk/data.dbf 4096 # Oracle8 example with a datafile split in multiple parts, each part smaller than 2GB 0 1 /fs1/oradata/PMS/system.dbf 1 2 /tmp/huge_file_part1 startblock 1 endblock 1000000 1 2 /tmp/huge_file_part2 startblock 1000001 endblock 2000000 1 2 /mnt3/huge_file_part3 startblock 2000001 endblock 2550000 The file header blocks are NOT verified. This would make it impossible to unload files with a corrupted header block. For debugging it is possible to dump the file header. Sample unload session: data dictionary usable for DUL create a suitable "init.dul" create a control.dul sqldba connect internal startup mount spool control.dul select * from v$dbfile; exit edit the result For Oracle8 a different query must be used: select ts#, rfile#, name from v$datafile; dul dictv7.ddl $ dul dictv7.ddl UnLoader: Version 2.0.0.0 - Very Restricted on Tue May 16 11:04:12 1995 Copyright (c) 1994/95 Oracle Corporation, The Netherlands. All rights reserved. . unloading table OBJ$ 1487 rows unloaded . unloading table TAB$ 197 rows unloaded . unloading table COL$ 5566 rows unloaded . unloading table USER$ 13 rows unloaded restart dul UnLoader: Version 2.0.0.0 - Very Restricted on Tue May 16 11:05:00 1995 Copyright (c) 1994/95 Oracle Corporation, The Netherlands. All rights reserved. Loaded 1487 objects Loaded 197 tables Loaded 5566 columns Loaded 13 users DUL> unload table scott.emp; About to unload SCOTT's tables ... . unloading table EMP 14 rows unloaded DUL> Example unload session: data dictionary UNUSABLE for DUL create a suitable "init.dul" (See config guide) create a control.dul See above scan the database for segment headers and extents: $ dul UnLoader: Version 2.0.0.0 - Very Restricted on Tue May 16 11:10:16 1995 Copyright (c) 1994/95 Oracle Corporation, The Netherlands. All rights reserved. DUL> scan database; data file 1 20480 blocks scanned data file 4 7680 blocks scanned data file 5 512 blocks scanned DUL>quit Restart DUL and scan the found tables for column statistics this creates a huge amount of output: echo scan tables \; | dul > scan.out& [ many lines here] Object id 1601 table number 0 UNLOAD TABLE T1601_0 ( C1 NUMBER, C2 UNKNOWN, C3 UNKNOWN, C4 NUMBER, C5 DATE , C6 NUMBER, C7 NUMBER, C8 NUMBER ) STORAGE ( TABNO 0 EXTENTS( FILE 1 BLOCK 10530)); Colno Seen MaxIntSz Null% C75% C100 Num% NiNu% Dat% Rid% 1 14 3 0% 0% 0% 100% 100% 0% 0% 2 14 6 0% 100% 100% 100% 14% 0% 21% 3 14 9 0% 100% 100% 100% 14% 0% 0% 4 14 3 7% 0% 0% 100% 100% 0% 0% 5 14 7 0% 0% 0% 0% 0% 100% 0% 6 14 3 0% 0% 0% 100% 100% 0% 0% 7 14 2 71% 0% 0% 100% 100% 0% 0% 8 14 2 0% 0% 0% 100% 100% 0% 0% "7369" "SMITH" "CLERK" "7902" "17-DEC-1980 AD 00:00:00" "800" "" "20" "7499" "-0.000025253223" "SALESMAN" "7698" "20-FEB-1981 AD 00:00:00" "1600" "30+ 0" "30" "7521" "WARD" "SALESMAN" "7698" "22-FEB-1981 AD 00:00:00" "1250" "500" "30" "7566" "JONES" "MANAGER" "7839" "02-APR-1981 AD 00:00:00" "2975" "" "20" "7654" "MARTIN" "SALESMAN" "7698" "28-SEP-1981 AD 00:00:00" "1250" "1400" "30" [ many more lines here ] This looks familiar, use the above information and your knowledge of the emp table to compose: UNLOAD TABLE emp ( empno number, ename char, job char, mgr number, hiredate date, sal number, comm number deptno number) STORAGE ( TABNO 0 EXTENTS( FILE 1 BLOCK 10530)); use this statement to unload emp: $ dul UnLoader: Version 2.0.0.0 - Very Restricted on Tue May 16 11:46:33 1995 Copyright (c) 1994/95 Oracle Corporation, The Netherlands. All rights reserved. Loaded 350 segments Loaded 204 extents Extent map sorted DUL> UNLOAD TABLE emp ( empno number, ename char, job char, mgr number, DUL 2> hiredate date, sal number, comm number deptno number) DUL 3> STORAGE ( TABNO 0 EXTENTS( FILE 1 BLOCK 10530)); . unloading table EMP 14 rows unloaded DUL>quit Example unload session: Incorrect init.dul Parameters WRONG osd_dba_file_bits size This can generate output similar to below. Normally this should not happen since you should create a demo database and check this via the DUL documented (in html page) query. The mismatch in DBA's is only in the file number (first number in brackets) part. The second number, the block number, is correct. Data UnLoader: Release 3.2.0.1 - Internal Use Only - on Wed Sep 3 10:40:33 1997 Copyright (c) 1994/95 Oracle Corporation, The Netherlands. All rights reserved. Session altered. Session altered. Session altered. Session altered. Session altered. DUL: Warning: Block[1][2] DBA in block mismatch [4][2] DUL: Warning: Bad cache layer header file#=1, block#=2 DUL: Warning: Block[1][3] DBA in block mismatch [4][3] DUL: Warning: Bad cache layer header file#=1, block#=3 ...........and etc.......... WRONG osd_file_leader_size This may create output similar to below, but many other flavours are possible. In this case we are a fixed number of blocks off. The file number is correct. The difference in the block numbers is constant.: Data UnLoader: Release 3.2.0.1 - Internal Use Only - on Wed Sep 3 10:44:23 1997 Copyright (c) 1994/95 Oracle Corporation, The Netherlands. All rights reserved. Session altered. Session altered. Session altered. Session altered. Session altered. DUL: Warning: Block[1][2] DBA in block mismatch [1][3] DUL: Warning: Bad cache layer header file#=1, block#=2 DUL: Warning: Block[1][3] DBA in block mismatch [1][4] DUL: Warning: Bad cache layer header file#=1, block#=3 ...........and etc.......... WRONG osd_c_struct_alignment This may generate output similar to the following: Data UnLoader: Release 3.2.0.1 - Internal Use Only - on Wed Sep 3 10:46:10 1997 Copyright (c) 1994/95 Oracle Corporation, The Netherlands. All rights reserved. Session altered. Session altered. Session altered. Session altered. Session altered. . unloading table OBJ$ DUL: Warning: file# 0 is out of range DUL: Warning: Cannot read data block file#=0, block# = 262145 OS error 2: No such file or directory DUL: Warning: file# 0 is out of range DUL: Warning: Cannot read data block file#=0, block# = 262146 OS error 2: No such file or directory ...........and etc.......... WRONG db_block_size The following output was generated when the db_block_size was set too small. The correct value was 4096 and it was set to 2048. Normally, the value for this parameter should be taken from the Oracle instances's init.ora file and will not be correctly set. Data UnLoader: Release 3.2.0.1 - Internal Use Only - on Thu Sep 4 12:38:25 1997 Copyright (c) 1994/95 Oracle Corporation, The Netherlands. All rights reserved. Session altered. Session altered. Session altered. Session altered. Session altered. DUL: Warning: Block[1][2] DBA in block mismatch [513][1159680] DUL: Warning: File=1, block 2: illegal block version 2 DUL: Warning: Block[1][2] Illegal block type[0] DUL: Warning: Bad cache layer header file#=1, block#=2 DUL: Warning: Block[1][4] DBA in block mismatch [1][2] DUL: Warning: File[1]Block[4]INCSEQ mismatch[90268!=0] DUL: Warning: Bad cache layer header file#=1, block#=4 DUL: Warning: Block[1][6] DBA in block mismatch [1][3] DUL: Warning: File[1]Block[6]INCSEQ mismatch[139591710!=86360346] DUL: Warning: Bad cache layer header file#=1, block#=6 ...........and etc.......... QUOTE MISSING If you get the follwing error it is caused by the data dictionary tables "USER$, OBJ$, TAB$ and COL$" not being correctly generated. To fix this error simply delete all dictv6.ddl or dictv7.ddl created .dat and .ctl files and restart. Data UnLoader: Release 3.2.0.1 - Internal Use Only - on Wed Sep 3 10:49:30 1997 Copyright (c) 1994/95 Oracle Corporation, The Netherlands. All rights reserved. DUL: Error: Quote missing SQL*Loader problems and work arounds 233827 LOADER REJECTS MULTI-LINE RECORDS IF USING 'TERMINATED BY WHITESPACE' DESCRIPTION: Load of multi line records fails if the continuation is between columns. WORK AROUND: Use larger physical records (init.dul: LDR_PHYS_REC_SIZE) FIXED: 7.3 250325 CANNOT LOAD CHAR THAT'S USED AS ENCLOSING CHARACTER DESCRIPTION: The stutter syntax of a column that starts with an enclosing character is not parsed correctly. ("""string itself enclosed in quotes""" is not parsed correctly). WORK AROUND: Use other enclosing character. (init.dul: LDR_ENCLOSE_CHAR) FIXED: 7.1.6 Script to unload USER$, OBJ$, TAB$ and COL$ REM DDL Script to unload the dictionary cache for DUL V3 (Oracle 7) REM force the settings, so I know what happens alter session set export_mode = false; alter session set ldr_phys_rec_size = 0; alter session set ldr_enclose_char = """" ; alter session set file = "" ; alter session set max_unload_blocks = 0; alter session set blocks_to_skip = 0; unload table OBJ$ ( OBJ# number, OWNER# number, NAME varchar2(30), NAMESPACE ignore, TYPE number) storage( objno 17 file 1); unload table TAB$( OBJ# number, TS# ignore, FILE# number, BLOCK# number, CLU# ignore, TAB# number, COLS number, CLUCOLS number) cluster C_OBJ#(OBJ#) storage ( tabno 1 segobjno 1 file 1) ; unload table COL$ ( OBJ# number, COL# number , SEGCOL# number, SEGCOLLENGTH ignore, OFFSET ignore, NAME char(30), TYPE# number, LENGTH number) cluster C_OBJ#(OBJ#) storage( tabno 5 segobjno 1 file 1) ; unload table USER$( USER# number, NAME varchar2(30)) cluster C_USER#(USER#) storage(tabno 1 segobjno 9 file 1) ; REM restart and load the dictinary in the cache exit ##################################################################################################### NOTE 12: ##################################################################################################### http://www.remote-dba.cc/teas_aegis_util16.htm Handling Corruption Some errors reported by dbv are transient in nature. Therefore, the utility should be executed on the suspect file again to confirm block corruption. If problems are again reported in the same page locations, then the file is indeed corrupt. Once one or more corrupted blocks are detected, the DBA must resolve the issue. Below are some options available to the DBA to address block corruption: • Drop and re-create the corrupted object – If the loss of data is not an issue, this is the preferred approach. For Data Warehouses, the data can be reloaded from external sources and the loss of data is minor. However, for OLTP tables (customer_orders), no data can be lost without a serious negative impact on the business. If the object is an index, rebuild it. If a few blocks are corrupt, determine which object(s) are causing the corruption. This can be done in the query below by mapping the physical file location to an object(s) contained in the file. select tablespace_name, segment_type, owner, segment_name from dba_extents where file_id = and between block_id AND block_id + blocks-1; • Restore the file from a backup – The tried and true method for restoring good blocks back into the datafiles. • Use dbms_repair – Dealing with block corruption is always a risky proposition so limit the use of dbms_repair to extreme situations. dbms_repair is a package supplied by Oracle that identifies and repairs block corruption (described in next section). If the first two options are unacceptable, using dbms_repair can resolve some block corruption issues. dbv is a useful utility to inspect datafiles for block corruption. It should be used primarily against offline datafiles on a regular basis. In should be used in combination with other corruption detection mechanisms, including the analyze table command and init.ora parameters. For online checking, the configuration parameter db_block_checking should be enabled, provided the overhead incurred on the database is at an acceptable level. Finally, when corrupted blocks are detected, the DBA should choose the most appropriate method of recovery – be it a restore, a rebuild of the object, or utilizing the dbms_repair utility ##################################################################################################### NOTE 13: ##################################################################################################### A few pointers on corrupt blocks: Suppose you do this: SQL> SELECT * FROM TESTUSER.EMPLOYEE1; (many records skipped...) .. 2415 harry 2500 2416 harry 2500 2417 harry 2500 ERROR: ORA-01578: ORACLE data block corrupted (file # 5, block # 19) ORA-01110: data file 5: 'C:\ORADATA\SALES\SALES_DATA_01.DBF' 2820 rows selected. Here, you face the dreaded ORA-01578 error. METHOD 1: ========= Next, try the following ALTER SESSION SET EVENTS command: SQL> ALTER SESSION SET EVENTS '10231 TRACE NAME CONTEXT FOREVER, LEVEL 10'; Again you try the query: SQL> SELECT * FROM TESTUSER.EMPLOYEE1; (many records skipped...) 9997 harry 2500 9998 harry 2500 9999 harry 2500 9596 rows selected. This time, no error is produced, and your query just continues and is not halted. You are now able to select the whole table again, allowing you to salvage all 'good' rows to a new table. Ofcourse, the bad block(s) were skipped, but the query was not halted. METHOD 2: ========= (as sys) SQL> exec dbms_repair.skip_corrupt_blocks('TESTUSER', 'EMPLOYEE1'); PL/SQL procedure successfully completed. SQL> SELECT * FROM TESTUSER.EMPLOYEE1; All rows from all good blocks are shown. Don't forget the quotes around the SCHEMA_NAME and OBJECT_NAME (thus: 'TESTUSER', 'EMPLOYEE1') The above action has made the EMPLOYEE1 table accesible again ! As always, you can create a copy table in using a statement like: CREATE TABLE salvage_table AS SELECT * FROM corrupt_table;