Chapter 33 – Managing Storage with Automatic Storage Management

Intro

• ASM stands for Automatic Storage Management.
• ASM is an Oracle-aware LVM (logical volume manager) that can stripe and mirror database files across a number of physical devices.

The Purpose of a Logical Volume Manager

• An LVM allows putting many physical volumes into arrays that can be treated as one huge disk area and then partitioned into as many logical volumes as you want.

RAID (Redundant Array of Independent Disks) Levels

• Level 0
  • Optimal for performance but suboptimal for fault tolerance
  • Logical volume cut across two or more physical drives (aka striping data)
  • Allows for data to be written to multiple physical drives concurrently
• Level 1
  • Optimal for fault tolerance
  • Logical volume mirrored against multiple physical drives
• Level 5
  • Combination of Level 0 and Level 1
  • Logical volume is cut across multiple physical drives
  • Checksumming algorithm writes out information to recover from loss of one physical volume
  • Checksumming algorithm requires overhead which effects performance
• Level 0+1
  • Optimal for both performance and fault tolerance
  • Mirroring of striped disks

Volume Sizes

• Physical volumes have definite sizes. With an LVM you can add, remove and resize logical volumes while the system is running.

Choice of RAID Level

• Different RAID levels can be applied to different files:
  • SYSTEM & UNDO tablespaces -> RAID 1 (redundancy)
  • Redo logs -> RAID 0 (multiplexing means no need to mirror)
  • Control file -> RAID 0+1 (any damage to any copy crashes db)
  • Other tablespaces -> RAID 5 or RAID 0+1 (depends on importance)

ASM Compared with other LVMs

• Different file types will perform better or worse depending on stripe size. ASM knows how to size the stripes.
• ASM provides RAID functionality at the file level. Other LVMs only work at the volume level.
• ASM is an Oracle standard and works the same on all platforms.
• ASM is always available on Oracle while other OSes require you to purchase a third-party application.

The ASM Architecture

• An ASM instance has the parameter INSTANCE_TYPE set to ASM instead of RDBMS.
• An ASM instance controls access to ASM files on ASM diskgroups, on behalf of the RDBMS instance.

The ASM Disks and Disk Groups

• An ASM disk group is a pool of ASM disks managed as one logical volume. The disks must be raw devices.
• The usable size of the ASM disk group will depend on the level of fault tolerance. The default is single mirror.

The ASM Instance

• The RDBMS instance performs its own I/O (reading, writing to datafiles & redo logs), not the ASM.
• Since an ASM instance cannot mount or open a database, it cannot read a data dictionary; therefore, you can only connect to it with a password file or OS authentication (as SYSOPER or SYSDBA).
• It makes the most sense to have one ASM instance that manages all RDBMS instances on the server. Although, it is possible to have many ASM instances (just pointless).
• An ASM instance will have two background processes:
  • RBAL & ARBn are used to rebalance data, I/O, striping, etc.

The RDBMS Intances

• An RDBMS instance running with ASM will have two background processes:
  • RBAL & ASMB. ASMB is used to create a session against the ASM process.
  • The information passed from the RDBMS to the ASM will be requests for physical changes, such as file creation, deletion, or resizing, and also various statistics and status messages.
• The RDBMS knows the name of the ASM diskgroups because they are embedded in the controlfile.
• When an ASM instance starts, it registers its name with the Cluster Synchronization service. The ASMB process of the RDBMS locates the ASM instance via the Cluster Sync service.
• A common config for RDBMS with ASM is two diskgroups. One contains the flash_recovery_area and the other has the database files.

The ASM Files

• Files in the ASM disk groups are managed by ASM on behalf of the RDBMS instance
  • Controlfile
  • Initialization parameter file
  • Online / Archived Redo Logs
  • Datafiles
  • Tempfiles
  • RMAN backup sets / image copies
  • Flashback logs
  • Controlfile autobackups
  • Data Pump dump files
• Excluded files
  • Alert log
  • Trace files
  • Password file
• All ASM files (not AUs) are striped across all disks in the group.
  • Coarse striping – the AU (allocation unit) is set to a large amount (1,000k) to deal with big I/O
  • Fine striping – the AU is set to a small amount (128k) to deal with small I/O
• The syntax for managing files within ASM is the same as RDBMS. The only difference is when specifying a location, you simply point to the ASM and it will be organized by the instance.
• There is a one-to-one relationship between a database file and an ASM file.
• Moving files from the OS to the ASM instance must be done via RMAN backup and restore operations. This is because the OS can’t see the ASM disks.
• It is possible to have some files using ASM and others located within the OS. However, there is no point in doing this.

Creating, Starting and Stopping an ASM Instance

• An ASM instance uses a parameter file just like an RDBMS instance.
  • instance_type = ‘asm’ // REQUIRED
  • instance_name = ‘+asm’ // must be prefixed with +
  • asm_diskstring = ‘/dev/hda4/’, ‘/dev/hda6′ or ‘\\.\*:’
  • asm_diskgroups = dg1, dg2
  • backgroung_dump_dest = ‘/opt/app/oracle/bdump’
• To start the instance you must connect as SYSDBA and issue a STARTUP command. The ORACLE_SID must be set to the instance name (ex. +ASM).
• There is no OPEN state for an ASM instance – only NOMOUNT & MOUNT.
• The RDBMS cannot OPEN until the ASM instance is MOUNTED.
• If an ASM is issued a SHUTDOWN, it will first shutdown all RDBMS instances and then shut itself down.
• A SHUTDOWN ABORT will kill the ASM and then all RDBMS instances will crash, as well.

Creating ASM Disk Groups

• Syntax:
  • create diskgroup dg1 disks ‘/dev/sdc’, ‘/dev/sdd’ [ NORMAL | HIGH | EXTERNAL ] REDUNDANCY;
• If you choose a disk that is already part of a disk group, then the command will fail.
• The default level of redundancy is “normal”, which means that each AU (allocation unit) is mirrored once.
• The default RAID level is RAID 0+1 for every file unless specified, otherwise.
• All files are striped across all disks with ASM choosing coarse or fine depending on the file type (datafile, redo log, controlfile).
• Redundancy types:
  • NORMAL – mirror
  • HIGH – three copies
  • EXTERNAL – no redundancy – assumes external LVM
• When ASM mirrors extents, it will never mirror an extent to another disk in the same failure group.
• By default, each disk (/dev/hda, /dev/hdb, etc.) is considered its own failure group.
• Failure groups are like specifying submirrors. The below will group failgrp1 & failgrp2 as single logical units.

  • FAILGROUP failgrp1 DISK
    '/dev/d1',
    '/dev/d2',
    FAILGROUP failgrp2 DISK
    '/dev/d3',
    '/dev/d4';

Creating and Using ASM Files

• ASM disk groups are created from within the ASM instance while ASM files are created from within the RDBMS instance.
  • SQL> create tablespace tbs1 datafile ‘+dg1′ size 100m;
  • SQL> alter tablespace system add datafile ‘+system_dg’ size 100m;
  • SQL> alter database add logfile group 4 ‘+dg_log1′,’+dg_log2′ size 100m;
  • SQL> alter system set log_archive_dest_1=’+dg_arc1′;
  • SQL> alter system set db_recovery_file_dest=’+dg_arc1′;

ASM and RMAN

• The only way to migrate or backup ASM files is via RMAN.
  • SQL> alter system set controlfiles=’+dg1′,’+dg2′ scope=spfile;
  • SQL> shutdown immediate;
  • SQL> startup mount;
  • RMAN> restore controlfile from ‘/u01/../control01.ctl’;
• This script will migrate all datafiles:
  • SQL> shutdown immediate;
  • SQL> startup mount;
  • SQL> backup as copy database format ‘+dg1′;
  • SQL> switch database to copy;
  • SQL> alter database open;

ASM and Linux

• Requires the ASMLib library which is specific to the OS (RedHat, Suse, etc)

Views and Tables

• V$ASM_DISK – shows list of disks discovered by the ASM instance
• V$ASM_DISKGROUP – shows diskgroups
• V$DATAFILE – you can see the ASM generated file name
• V$LOGFILE – you can see the ASM generated file name
• V$ARCHIVED_LOG – you can see the ASM generated file name

Chapter 32 – Monitoring and Managing Storage

Online and Archive Redo Log File Storage

Disk I/O and the online Redo Log Files

• When a user issues a commit statement, the session will hang until LGWR flushes the data from the log buffer to the redo log files.
• When LGWR flushes the log buffer to disk, it issues a parallel write request to the OS. If the multiplexed files are on separate devices then the OS can do a parallel write to the different devices. Otherwise, if multiple files are on the same device, then the OS must write sequentially.

Log Switches and Performance

• LGWR writes out data in realtime but DBWn writes as little as possible, as rarely as possible. The gap between the LGWR flush and DBWn is the amount of redo that will have to be processed to back out corruptions in case of a crash. The FAST_START_MTTR_TARGET controls the gap. The faster the recovery time, the smaller the gap and therefore the more frequent DBWn writes to disk.
• There will always be enough redo data in the online data files to bring the db up to date in case of a crash. To ensure this, LGWR can’t overwrite a logfile group until DBWn has written the data to disk (datafiles). For each log switch, DBWn must write data to disk (from the db buffer cache) which means frequent log switches (due to small redo log files) can impact performance.
• In the Redo Logs section of DB Control, there is a log advisor which tells you if DBWn is being forced to write data that it wouldn’t otherwise write when log switches occur.

Archive Log Files and Performance

• It is possible, at times of peak activity that the redo logs are generating data faster than it can be copied to the archive logs. The instance will hang until the archiver catches up.
  • To minimize this effect:
    • Never locate archive logs on the same device as the redo logs. This guarantees contention between LGWR and ARCn.
    • Always launch as many ARCn processes (default 2; max 10) as archiver destinations (also 10 max). This is controlled by LOG_ARCHIVE_MAX_PROCESSES.
    • If problems still occur, add more online redo log groups. It will ensure more time before log files get overwritten.

Space Errors and Resumable Statements

Common Space-Related Errors

• The AUTOEXTEND attribute of a datafile allows Oracle to increase the size of the datafile as long as the max size hasn’t been reached or the disk isn’t full.
• If the UNDO tablespace is full, then everyone will get errors on DML.
• If the user’s temporary tablespace is full and a SELECT needs to use temporary space to sort, then the user will get errors.

Autoextension of Datafiles

• Can be specified at datafile creation time or subsequently with the ALTER DATABASE DATAFILE <name> AUTOEXTEND ON NEXT 10m MAXSIZE 200m;

Resumable Statements

• A session or the whole instance can be configured to suspend statements that hit space problems rather than rolling back and giving an error. The DBA can then fix the problem and the statements will resume where it left off. Default timeout is 2 hours which will then give error.
• All row locks and undo or temporary space will be held.
• SQL> alter session enable resumable timeout 60 name ‘AR archive’; (60 seconds)
• The table DBA_RESUMABLE will show suspended sessions.
• After a session is suspended, the event “after suspend” can be used to run PL/SQL functions for things like emailing the DBA, etc.

Use of Alerts to Monitor Tablespace Usage

• To check the free space available in tablespaces use the DBA_FREE_SPACE view.
• Alerts will be raised when tablespaces hit 85% and 97% of capacity and will be checked every 10 minutes. By default, alerts will be shown in database control and the DBA_OUTSTANDING_ALERTS / DBA_ALERT_HISTORY views.
• For autoextensible tablespaces, alert compares current usage with max file size.

Monitoring and Managing Segment Sizes

• As rows are inserted, the tablespace is allocated extents as necessary. On delete however, the extents are not deallocated.
• It is best practice to allocate enough space instead of having Oracle allocate dynamically.
• Since row deletions don’t deallocate space, it is possible to have more space than necessary for the data in the rows. This is a waste of space and also a performance hit since table scans must unnecessarily scan deleted data.
  • Indexes have similar problems.
• Database Control Advisors can scan objects and give recommendations.

Estimating Segment Sizes

• If you give an estimated number of table rows, an advisor will estimate the amount of space needed.
• A similar option is available for indexes.

Shrinking Table Segments

• The HWM or High Water Mark is a marker in a segment of the last block that has been used. As extents fill up, new extents are added to the table. As rows are deleted, space within the segment is freed up but the HWM doesn’t move and the segments retain their space. New insertions can use the freed up space but in some cases segments can be sparsely populated below the HWM. This is a waste of storage and inefficient since full table scans have to go through the deleted data.
• SQL> ALTER TABLE users SHRINK SPACE;
  • Will reorganize the table into as few rows as possible with all valid rows at the front of the table. Will push down HWM and free all unneeded extents back to the tablespace. The result is a small and compact table with all rows packed as tightly together as possible. Optimization and space utilization are achieved.
  • Rows are moved via copy, paste and delete. If a user tries to access a row while it is being moved, they may experience a momentary delay until the lock is released.
  • The final stage is moving the HWM and freeing up the empty blocks.
• Insert and delete triggers don’t fire while shrinking takes place. Row movement must be enabled. Segment shrink operations only work with heap organized tables, in segments using ASSM (Auto Segment Space Mgt). Segment Management can be viewed in the DBA_TABLESPACES table.
• Tables that cannot be shrunk:
  • Clustered tables
  • Tables with columns of type LONG
  • LOB segments (although the table can be shrunk)
  • Tables with on-commit materialized views (b/c triggers are disabled)
  • IOT mapping tables and IOT overflow segments
  • Tables with function based indexes
• Other options:
  • SQL> ALTER TABLE users SHRINK SPACE COMPACT;
    • Stops the shrink before moving the HWM because the HWM move requires a (very brief) table lock. It can be run with COMPACT and then subsequently without it. The second run will be very fast since it’s already compacted.
  • SQL> ALTER TABLE users SHRINK SPACE CASCADE;
    • Shrinks tables and indexes and moves the HWM.

Shrinking Index Segments

• Indexes also become fragmented from deletes due to their B*Tree structure. However, new insertions do not reuse the space from deleted rows.
• To see the index’s space utilization:
  • SQL> analyze index I1 validate structure;
  • SQL> select LF_ROWS_LEN, DEL_LF_ROWS_LEN FROM INDEX_STATS WHERE NAME=’I1′;
  • DEL_LF_ROWS_LEN tells how many rows from the index are used on deleted rows.
  • LF_ROWS_LEN tells how many rows are in total.
• To free up the space used by deleted rows for reuse, do:
  • SQL> ALTER INDEX I1 SHRINK SPACE;
  • This command will not compact the index, it will only free up the deleted blocks. In order to compact the blocks, you must REBUILD the index.
  • If you check the DBA_SEGMENTS view, the segment size will still be the same.

Rebuilding Index Segments

• Command:
  • SQL> ALTER INDEX I1 REBUILD ONLINE [tablespace indexes];
  • Without the ONLINE keyword, the table will be locked for the duration of the rebuild.
• A rebuild will create an entirely new index and therefore requires up to twice the amount of space during the operation.

Monitoring Index Usage

• Indexes can improve queries but can be bad for DML operations. Whenever the value of an indexed column is changed, the block of the table row must be read into memory and changed, plus the blocks of the index must be changed. Every unused index slows down the database and serves no purpose.
• To see if an index is being used, you can monitor it:
  • SQL> alter index idx1 monitoring usage;
  • … wait for minutes, hours or days …
  • SQL> select index_name,table_name,used from v$object_usage;
  • SQL> alter index idx1 nomonitoring usage; (to stop monitoring)
  • The used column (yes/no) will tell whether the index has been used since monitoring began.

Alternative Table Storage Structures

• Heap organized tables are the default table type and are made up of variable length rows in random order.
• Other options are IOTs, index clusters, hash clusters and sorted hash clusters.
• Programmers don’t need to know the structure of the table, their code will remain unchanged.

Index Organized Tables (IOTs)

• An IOT looks and acts like a table but the segment used to store it is of type index.
• The key and entire row is stored in the leaf blocks of the B*Tree. (A regular index holds the key and a pointer to the row, in the leaf.)
• IOTs are faster than a heap table with an index because there is no need to reference the rowid pointer.
• IOTs can be created with an “overflow” segment. This works on the assumption that programmers will only need a few columns on a regular basis and the rest will be stored in the overflow segment.
  • SQL> create table emp_iot (emp_no number, emp_ name varchar2(20), emp_dept varchar2(20), emp_dept number, constraint emp_pk primary key(emp_no)) organization index including emp_name overflow tablespace hr_over;
  • The “including emp_name” part means all columns before and including emp_name will be stored in the IOT while the remainder will be stored in the overflow.
  • An alternative to “including emp_name” would be “PCTTHRESHOLD n”. This states that n percent of the row will be stored in the IOT with the remainder going to the overflow.
• Mapping table required for secondary index:
  • SQL> alter table emp_iot move mapping table;
    • IOT will be reorganized and generate a mapping table (heap) which holds rowids for IOT. Secondary bitmap indexes can be built on the mapping table.
    • SQL> create bitmap index emp_indx on emp_iot(emp_dept);
• Limitations:
  • Table must be created with a primary key, nondeferrable, constraint.
    • nondeferrable – checks constraints as soon as statement executes.
    • deferrable – waits until commit to check constraints.
  • An IOT Cannot be a clustered table
  • Cannot have columns of type LONG
  • Cannot have partitioning (which is only offered as an add-on anyway)

Index Clustered Tables

• An index cluster is a group of tables stored in one physical segment.
• All tables in the index cluster must have a common cluster key or column (sno in the example below).
  • SQL> create cluster staff_clust (sno number) index;
  • SQL> create index staff_idx on cluster staff_clust;
  • SQL> create table staff (sno number, sname varchar2(20), constraint staff_pk primary key (sno)) cluster staff_clust (sno);
  • SQL> create table address_lines(sno number constraint addr_fk references staff, lno number, ltext varchar2(100)) cluster staff_clust (sno);

Hash Clustered Tables

• Hash clusters also have a cluster key like index clusters. However, the key is used to construct a hashing algorithm.
• Whenever a row is inserted, Oracle assigns a physical location based on the hash value. This method, hash key access is the fastest method of retrieval.
• Searching an index will retrieve a location from the hash algorithm. This is CPU intensive instead of I/O.
• One drawback is that it is only efficient when searching for specific values, not ranges or get-next-record type of statements.
• Another drawback is the size consideration. The estimated number of rows is used to construct the hashing algorithm so if it’s very inaccurate, the performance will degrade.

Sorted Hash Clustered Tables

• Same as hash clustered tables except you specify which columns are to be sorted. The rows are sorted as part of the hash algorithm.

Parameters

• FAST_START_MTTR_TARGET (the smaller the value, the more often DBWn writes to disk )
• LOG_ARCHIVE_MAX_PROCESSES (number of archiver processes writing to arc destination. should be equal to number of destinations.)
• RESUMABLE_TIMEOUT (to set ENABLE RESUMABLE for entire instance – in seconds)

Tables & Views

• DBA_DATA_FILES (see which datafiles have autoextend)
• DBA_RESUMABLE (shows all suspended sessions / SQL text / error, etc.)
• V$SESSION (shows whether a session is suspended or not)
• DBA_FREE_SPACE (shows the free space available in the tablespaces)
• DBA_OUTSTANDING_ALERTS (shows alerts on tablespace size, etc.)
• DBA_ALERT_HISTORY (when alerts are no longer outstanding, they are moved here)
• DBA_SEGMENTS (check size of segment after shrink space on tables or rebuild on indexes)
• DBA_TABLES or USER_TABLES (will tell which cluster the table belongs to)
• DBA_SEGMENTS or USER_SEGMENTS (has field SEGMENT_TYPE of cluster or index, etc.)

Chapter 31 – Tools for Oracle Database 10g Tuning

The Advisor Methodology

• All performance tuning is based on statistics (the STATISTICS_LEVEL parameter). TYPICAL is the default level and usually sufficient.
• To view what is causing wait events on the system, see the V$SYSTEM_WAIT_CLASS view.

The Automatic Database Diagnostic Monitor (ADDM)

• Background process MMON (Manageability Monitor) flushes data from memory to disk (snapshot) by default every 60 minutes
  • Writes snapshot data to SYSAUX tablespace which makes up the AWR (Automatic Workload Repository)
  • Whenever a snapshot is taken, the ADDM is run. This generates a report on activity and possible problems between current snapshot and previous snapshot
  • Retention of snapshots is 7 days by default
  • dbms_workload_repository package will allow setting snapshot defaults
• ADDM is usually the first stop for tuning but it will recommend other advisors

The Advisors

• SQL Tuning Advisor
  • Inspects SQL statements and makes recommendations regarding inefficient coding methods
• SQL Access Advisor
  • Suggestions for changing indexing and materialized views to limit number of block visits
• Memory Advisor
  • Predicts effects on I/O of resizing the db buffer cache and PGA and shared pool
• MTTR (Mean Time to Recover) Advisor
  • Predicts effects on I/O in regards to MTTR
• Segment Advisor
  • Recommends which segments could be shrinked and can estimate space required for tables and indexes at creation time with anticipated number of rows
• Undo Advisor
  • Determines how large undo tablespace needs to be in order to guarantee that transactions don’t run out of undo space and queries don’t fail with read consistency errors

SQL Tuning Advisor

• Probably the most important aspect of database tuning
• Four sources of SQL data in the GUI:
  • Top SQL – SQL cached in the SGA
  • SQL Tuning Sets – set of statements that can be built from current SGA, snapshot or groups of statements entered manually
  • Snapshots – High-load SQL statements from AWR
  • Preserved snapshots aka baselines – stored in the AWR indefinitely
• Four areas of recommendataion
  • Optimizer statistics – Advisor will notify if there are missing or out of date statistics
  • SQL profiling – Will partially run statements to test various execution plans and gather execution statistics
  • Access path analysis – Recommend indexes, etc.
  • SQL structure analysis – Suggest alternate ways to structure the statements

SQL Access Advisor

• Takes a workload from the SGA, snapshot, or SQL tuning set
• Tests the effects of using additional indexes and materialized views

The (SGA) Memory Advisor

• There are three memory advisors:
  • Shared Pool Advisor
    • Shared pool is used to cache parsed code. A larger shared pool means that more parsed code is kept. However, an oversized shared pool can impact on increased time to retrieve objects.
    • Plots parse time vs. size
  • DB Buffer Cache Advisor
    • Larger buffer cache will reduce disk reads as more data is kept in memory
    • Plots physical reads (disk I/O) vs. size
  • Program Global Area Advisor
    • Used for individual session data
    • Plots cache hit vs total size (not size per session)

The MTTR Advisor

• Speeding up the estimated MTTR can result in downgraded performance
• The V$INSTANCE_RECOVERY view shows info like how long it would take Oracle to recover based on the current level of redo, etc.

The Segment Advisor

• Three parts:
  • Size estimates can be generated when creating tables and indexes by specifying anticipated number of rows
  • Growth and trend estimates are generated via data from the AWR
  • Recommendations on whether a segment should be shrunk also come from the AWR

The Undo Advisor

• The V$UNDOSTAT view displays statistics for 10 minute intervals. Holds information like longest query, blocks of undo, etc. (each 10 minute period is a table row)
• Called Undo Management through EM
• Undo advisor plots retention time (minutes) vs size (MB)

Automatic Undo Retention

• Active undo data is used for rolling back uncommitted data
• Unexpired undo data is needed to satisfy long running queries
• Expired undo data is no longer needed
• The UNDO_RETENTION parameter is redundant in 10g (unless you are using rentention guarantee) because Oracle will overwrite the oldest unexpired or expired undo data

Views & Tables

• V$SYSTEM_WAIT_CLASS (view which category of events are causing the most database strain)
• V$INSTANCE_RECOVERY (MTTR info)
• V$UNDOSTAT

Parameters

• STATISTICS LEVEL (BASIC, TYPICAL or ALL)
• FAST_START_MTTR_TARGET (desired time to recover)
• UNDO_TABLESPACE
• UNDO_RETENTION

Chapter 30 – Detecting and Recovering from Database Corruption

Block Corruption and Its Causes

• Blocks can be corrupted in two ways:
  • Media Corruption – where the contents of the block make no sense whatsoever; its contents don’t match the format expected by Oracle.
  • Logical Corruption – where the Oracle formatting is correct but the contents of the block are internally inconsistent.

Parameters Relating to Block Corruption

• DB_BLOCK_CHECKSUM – helps detect damage introduced by the disk or I/O systems
  • defaults to TRUE
  • Everytime the DBWn writes to disk or a block is accessed on disk, it will compare checksums
  • Even when set to false, is enabled on SYSTEM tablespace
• DB_BLOCK_CHECKING – helps detect damage introduced by faulty memory
  • defaults to FALSE
  • Checks for consistency every time buffer is accessed
  • Impacts performance when enabled
  • Even when set to false, is enabled on SYSTEM tablespace

Detecting Block Corruptions

• Detailed information occurs in the alert log
• Errors tell you the .dbf file, file number and block number of the corruption
• Block information is in the dba_extents & dba_objects view

The DBVERIFY Utility

• An OS executable (dbv in $ORACLE_HOME/bin) used to verify datafiles
• Works against datafiles, live or backup, but nothing else (doesn’t work against backupsets)
• Works for datafiles on raw devices but you must give it a range of blocks to look at.
• In the output, it refers to blocks as “pages”. Block 5 would be page 5.
• RMAN performs its own validation when backing up so dbv is not necessary. It can be used on user backups (aka non-RMAN backups).
• If a block is “influx” then DBV was forced to check it multiple times because it was in use.

The ANALYZE Command

• Usually used for SQL tuning by the optimizer to find most efficient way of executing a SQL statement.
• Its other use is checking whether a table or index is corrupt:
  • SQL> analyze table <tablename> validate structure;
  • SQL> analyze table <tablename> validate structure cascade;
    • Cascade validates tables and associated indexes
    • Cascade can take a really long time
  • SQL> analyze index <indexname> validate structure;
• The check performed by ANALYZE is only of blocks below the high water mark. Conversely, DBV will check all blocks in the file.

The DBMS_REPAIR Package

• Set of procedures that will check for corruption and repair corrupted objects but CANNOT recover data
• First step is to create a table which will be used for detection. It must be prefixed with the text REPAIR_ and will be created under the SYS schema
  • SQL> dbms_repair.admin_tables(table_name=>’REPAIR_TAB’, table_type=>dbms_repair.repair_table, action=>dbms_repair.create_action);
• Check object synatx:
  • SQL> dbms_repair.check_object(schema_name=>’HR’, object_name=>’EMP’, repair_table_name=>’REPAIR_TAB’, corrupt_count=>num_corrupt);
    • check_object writes out damaged blocks to the REPAIR_ table
• Others:
  • dbms_repair.fix_corrupt_blocks
    • Marks damaged blocks as corrupted; followed by skip_corrupt_blocks
  • dbms_repair.skip_corrupt_blocks
    • Will skip blocks so there won’t be errors; there may be a loss of data
    • Indexes may need to be rebuilt because they will contain inconsistent data (index will have data that is no longer in the table)

Recovering Corrupt Blocks with RMAN

• Only applies to datafiles; NOT online logs, archive logs or controlfiles because they use multiplexing
• Detection of Corrupt Blocks
  • RMAN automatically detects corruption while running backups
  • RMAN can set tolerance for corruption
    • RMAN> set maxcorrupt for datafile 7 to 100;
      • If maxcorrupt is set, the views v$database_block_corruption, v$backup_corrruption or v$copy_corruption will be populated
• By default RMAN checks for media corruption (or physical corruption)
  • You can break the default:
    • RMAN> backup nochecksum datafile 7;
• It can be configured to check for logical corruption, as well:
  • RMAN> backup check logical datafile 4;

Block Media Recovery

• Block Media Recovery or BMR changes the granularity of restore and recovery from the datafile to just the damaged blocks
• Unlike restore and recover, BMR does not require datafiles to be taken offline
• BMR provides RMAN with a list of corrupted blocks and RMAN restores these blocks from a backupset or image copy. Then RMAN will apply redo logs to the blocks since the backup was created
• BMR is not restricted to whole datafiles like dbms_repair; it can be applied to any data block whatsoever within the datafiles
• All BMR must be complete recovery. Imagine what would happen if only a few blocks were recovered to a point in the past (the data would be inconsistent)
• Examples
  • RMAN> blockrecover datafile 7 block 5;
  • RMAN> blockrecover datafile 7 block 5,6,7 datafile 9 block 21,25
  • RMAN> blockrecover datafile 7 block 5 from backupset 1093;
  • RMAN> blockrecover datafile 7 block 5 from tag monthly_whole;
  • RMAN> blockrecover corruption list until time sysdate – 7;
    • If RMAN ran with maxcorrupt set, then v$database_block_corruption will be populated;”corruption list” will instruct RMAN to recover every block listed in the view
    • To make sure that the recover is from before the corruption occurred, there is the “until” keyword

Tables & Views

• V$DATABASE_BLOCK_CORRUPTION
  • Shows address of the corruption (datafile number, block number) for backups
• V$BACKUP_CORRUPTION
  • Addresses from the corrupted blocks for corrupt backupsets
• V$COPY_CORRUPTION
  • Addresses from the corrupted blocks for backup image copies

Chapter 29 – Recovering from User Errors

• RMAN can only be used for flashback database and no other flashback option.

Flashback Drop

• DROP TABLE is nothing more than a RENAME of the table and all its indexes, triggers and constraints (but not foreign key constraints). Foreign key constraints must be removed because non-dropped tables would be constrained by tables sitting in the recycle bin.
• Flashback drop works only for tables (and subsequently indexes and contraints).  You cannot flashback drop an index (solely).
• Only the table and indexes are actually moved to the recyclebin.  Grants and constraints are maintained in the Data Dictionary, except for foreign key constraints, which are dropped.
• Foreign key constraints can’t be flashed back.
• Tables and their indexes, triggers, (non-fk) constrainsts are renamed on flashback. Grants don’t have names but are also restored.
• Dropped tables are stored in USER_RECYCLEBIN & DBA_RECYCLEBIN or show recyclebin.
• Syntax:
  • SQL> flashback table employees to before drop [rename to <new name>];
• If a user (schema) is dropped then flashback drop won’t work.
  • SQL> drop user scott cascade;
• Flashback Drop and the Recyclebin are not available for the SYSTEM user.
• If two tables with the same name are in the recyclebin, you can restore them by their recyclebin name:
• SQL> flashback table “BIN$fhdsjsjlkjldWfsdOfsd==$0″ to before drop;
• The recyclebin has a column called CAN_UNDROP which lets you know if the table can be flashback dropped.
• Flashback drop is not part of the relational database model, it is simply a convenience that Oracle provides.
• Flashback drop does not exist for tables in the SYSTEM tablespace.
• The purge command can delete objects from the recyclebin.
  • purge table <tablename>
  • purge index <indexname>
  • purge tablespace <tablespacename>
  • purge tablespace <tablespacename> user <username>
  • purge user_recyclebin
  • purge dba_recyclebin

Flashback Table

• Flashback table reverts a table to an earlier timestamp or SCN most likely due to user error.
• Row movement must be enabled. This informs Oracle that ROWIDs may change in the tables. Since a flashback operation may have to restore deleted rows, it will do INSERTs and rows with primary IDs will now have different ROWIDs.
  • SQL> alter table emp enable row movement;
• To avoid foreign key failures, it is possible to flashback multiple tables at the same time:
  • SQL> flashback table emp, dept to timestamp to_timestamp(‘06-08-17 14:37:00′,’yy-mm-dd hh24:mi:ss’);
  • … to scn 574389537
  • … enable triggers (triggers will fire during flashback operation)
• Other reasons that flashback will fail:
  • Missing UNDO data (ORA-08180: no snapshot found based on specified time)
  • A primary key has been reused between a delete and a flashback.
  • Rows needed for flashback are locked (ORA-00054: resource busy and acquired with NOWAIT specified)
  • Table definition (DDLs) has changed (columns added, data type changed)
  • Flashback can never be run on tables in the SYS schema (Data Dictionary flashback could destroy database)

Flashback Query

• Flashback Query can be with an SCN or a time. If an SCN is specified, the flashback will be precise. If a time is specified, it will be mapped to an SCN at or after the time.
• Basic Flashback Query
  • Statement syntax:
    • select * from employees as of timestamp to_timestamp(‘06-08-17 14:03:56′, ‘yy-mm-dd hh24:mi:ss’) minus select * from employees;
  • Session syntax:
    • execute dbms_flashback.enable_at_time(to_timestamp(‘06-08-17 14:03:56′, ‘yy-mm-dd hh24:mi:ss’));
    • execute dbms_flashback.disable;
    • While in session mode, you can only use SELECT statements, all DML statements will throw errors.
• Flashback Versions Query
  • Allows you to see all versions of data in table rows by including the following pseudo columns:
    • VERSIONS_STARTSCN
    • VERSIONS_STARTTIME
    • VERSIONS_ENDSCN
    • VERSIONS_ENDTIME
    • VERSIONS_XID
    • VERSIONS_OPERATION
  • Syntax:
    • SQL> select name, versions_startscn,versions_starttime, versions_endscn, versions_endtime, versions_xid,versions_operation from test versions between scn minvalue and maxvalue;
    • SQL> … versions between timestamp to_timestamp(…) and to_timestamp(…)
• Flashback Transaction Query
  • Contains information similar in the view FLASHBACK_TRANSACTION_QUERY.
  • Every transaction will have one or more rows in the flashback_transaction_query table.
  • Can link with Flashback Versions Query on field XID however they are of different data type.
  • The XID column is type RAW, while VERSIONS_XID is hexadecimal. Can be joined with typecasting func:
    • select operation, undo_sql from flashback_transaction_query where xid=hextoraw(‘0054B43CE43′);
  • Can use undo_sql field to revert data.

Chapter 28 – Using Oracle Flashback Database

There are three distinct Flashback technologies available:

• Flashback Database
  • Like pressing the rewind button on the entire database
  • Requires archivelog mode and the use of ALTER DATABASE OPEN RESETLOGS
  • Basic Steps:
    • shutdown;
    • mount;
    • flashback statement to a time, SCN or log switch sequence number;
    • open with resetlogs;
• Flashback Query
  • Lets you query the database as it was in some point in the past, either for one select statement or by taking your whole session temporarily back in time.
  • Relies entirely on the use of UNDO segments
    • Flashback Versions makes it possible to select all versions of a row over a period of time, to show a history of what has happened to the row, when it happened, who did it, etc.
    • Flashback Transaction uses the Flashback Versions Query to identify which transaction caused the problem and uses SQL statements to undo the mistakes.
    • Flashback Table allows you to reverse all changes made to a table while leaving other tables intact.
• Flashback Drop
  • Applies only to dropped tables.
  • When a table is dropped it is actually renamed to a system-generated name and not dropped until later if the space is needed by Oracle.
  • A Flashback Drop simply restores the name back to the table.
  • Can ONLY be used for objects removed with the DROP command (not truncate).
  • All associated indexes and permissions will also be restored.

Flashback Database Architecture

• Altered data blocks are copied (from time to time) from the db buffer cache to the flashback buffer (which is newly created within the SGA).
• The flashback buffer is flushed to disk, to the flashback logs, by a new background process: RVWR (Recovery Writer).
• The flashback logs contain a log of complete block images.
• Since data is only flushed to the flashback logs from time to time, it is not possible to flashback to an exact point in time. The redo logs can be used to bring the flashback data up to date and synchronize all datafiles to the same SCN.
• The final step is rolling back all uncommitted transactions just like an incomplete recovery.

Configuring Flashback Database

• Requires parameters: db_recovery_file_dest, db_recovery_file_dest_size & db_flashback_retention_target
• In mount mode: alter database flashback on;
• alter database open;

Flashback with SQL*Plus

• Will accept either a timestamp or SCN; unlike RMAN, it will not accept a date or log switch sequence number
• Example:
  • shutdown abort;
  • startup mount;
  • flashback database to timestamp to_timestamp(‘18-08-06 10:00:00′,’dd-mm-yy hh24:mi:ss’);
  • alter database open resetlogs;

Flashback with RMAN

• RMAN> flashback database to time to_timestamp(‘18-08-06 10:00:00′,’dd-mm-yy hh24:mi:ss’);
• RMAN> flashback database to SCN 432789;
• RMAN> flashback database to sequence 5324543 thread 1;

Managing the Flash Recovery Area

• Flashback logs always go to the flash_recovery_area
• If the flash_recovery_area fills up:
  • If archive logs are being written to it, then DML operations will freeze because archiving will become impossible
  • Backups will fail if they are being written there

Backing Up the Flash Recovery Area

• RMAN> backup recovery area;
  • Full and incremental backup sets
  • Datafile and archive log file image copies
  • Controlfile auto backups
  • Archive logs
  • … all are backed up assuming they are in the DB_RECOVERY_FILE_DEST
  • It does NOT backup flashback logs
• RMAN> backup recovery files;
  • Backs up all recovery files whether or not they’re in the recovery area.

Processes

• RVWR

Views & Tables

• V$DATABASE
• V$FLASHBACK_DATABASE_LOG
• V$FLASHBACK_DATABASE_STAT
• V$SGASTAT (to see size of flashback buffer)

Parameters

• DB_RECOVERY_FILE_DEST (defaults to flash_recovery_area)
• DB_RECOVERY_FILE_DEST_SIZE
• DB_FLASHBACK_RETENTION_TARGET

Chapter 27 – Incomplete Database Recovery

Complete Recovery

• Assuming backups of the datafiles, copies of all necessary archive logs, online redo logs and the controlfile are available; the db can suffer any damage to the datafiles without losing any data:
• Complete Recovery:
  • Take damaged datafiles offline
  • Restore damaged datafiles
  • Recover damaged datafiles
  • Bring recovered datafiles online
• Syntax:
  • alter tablespace users offline immediate; (immediate is used because the tablespace is damaged and the DBWn will try to write dirty blocks to disk [aka checkpoint the tablespace], which may fail)
  • restore tablespace users;
  • recover tablespace users delete archivelog; (delete archivelog refers to the deletion of the restored logs after they are applied. recovery may also use online log files.)
  • alter tablespace users online;
• Complete recovery can be done with the database OPEN unless the SYSTEM or UNDO tablespaces are damaged (this will terminate the instance).

When is Incomplete Recovery Necessary?

• Incomplete recovery means losing data on purpose. It occurs when complete recovery is impossible or you want to lose some information that was entered by mistake.
• It is not possible to skip the recovery of a bad transaction and recover all other work.
• There are cases where incomplete recovery won’t be possible with the current controlfile:
  • All copies of the controlfile are lost (and it is not possible to recover with a CREATE CONTROLFILE command)
  • The current controlfile does not accurately describe the database that needs to be restored (typically due to dropped tablespaces)
• The syntax for recovery using a backup controlfile includes the UNTIL keyword even if the recovery is complete.
• All datafiles except temporary tablespaces are restored. Undo tablespaces are restored.

The Method for Incomplete Recovery

• It is often a good idea to perform a complete offline backup before beginning an incomplete recovery to ensure no loss of data.
• Steps to incomplete recovery:
  • Mount the database
  • Restore all datafiles (they don’t need to be from same backupset / time), and controlfile if necessary
  • Recover the database UNTIL time, cancel or change number
  • OPEN with RESETLOGS
• OPEN with RESETLOGS initializes the online redo files creating a new incarnation of the database. An incarnation of a database has a new thread of redo, beginning at log switch sequence number 1.
• Backups and archivelogs are specific to an incarnation and therefore those generated by one incarnation must be kept separate from those generated by a previous incarnation.
• Only SYSDBA can perform incomplete recoveries

UNTIL TIME Recovery

• SQL> shutdown immediate;
• SQL> startup mount;
• —No restore b/c OS or other means are used to restore necessary files—
• SQL> recover database UNTIL TIME '2006-08-15:14:07:00'
  • The time format must be in the format YYYY-MM-DD:HH24:MM:SS irrespective of NLS_DATE_FORMAT
• SQL> alter database open resetlogs;
• —–ALTERNATIVE in RMAN—–
• RMAN> run {
• RMAN> shutdown immediate;
• RMAN> startup mount;
• RMAN> sql "alter session set nls_date_format=''dd-mon-yyyy hh24:mi:ss''";
• RMAN> set until time '15-aug-2006 14:11:00';
• RMAN> restore database;
• RMAN> recover database;
• RMAN> alter database open resetlogs;}

UNTIL CANCEL / UNTIL SEQUENCE (RMAN) Recovery

• Cancel-based recovery is typically needed after complete recovery has failed: the recovery required an archive or online log that was missing.
• SQL> shutdown immediate;
• SQL> startup mount;
• —No restore b/c OS or other means are used to restore necessary files—
• SQL> recover database UNTIL CANCEL;
• SQL> alter database open resetlogs;
• —–ALTERNATIVE in RMAN—–
• RMAN> run {
• RMAN> shutdown immediate;
• RMAN> startup mount;
• RMAN> set until sequence 10305 thread 1;
• RMAN> restore database;
• RMAN> recover database;
• RMAN> alter database open resetlogs;}

UNTIL CHANGE / UNTIL SCN Recovery

• SQL> shutdown immediate;
• SQL> startup mount;
• —No restore b/c OS or other means are used to restore necessary files—
• SQL> recover database UNTIL CHANGE 309121;
• SQL> alter database open resetlogs;
• —–ALTERNATIVE in RMAN—–
• RMAN> run {
• RMAN> shutdown immediate;
• RMAN> startup mount;
• RMAN> set until scn 309121;
• RMAN> restore database;
• RMAN> recover database;
• RMAN> alter database open resetlogs;}

Incomplete Recovery Using EM

• Maintenance -> Perform Recovery

Recovery of the Controlfile

• SQL> alter database backup controlfile to trace
  • USER_DUMP_DEST will determine where trace file is created
• You should always create a trace file whenever you make physical changes to the structure of the database.
• SQL> alter database backup controlfile to ‘<filename>’

Restore a Controlfile with SQL*Plus

• startup mount;
• SQL> recover database until cancel using backup controlfile;

Backup / Restore a Controlfile with RMAN

• RMAN> backup as copy current controlfile;
• RMAN> backup as backupset current controlfile;
• RMAN> backup tablespace system include current controlfile;
• RMAN> configure controlfile autobackup on;
• RMAN> restore controlfile from autobackup;

Parameters

• USER_DUMP_DEST
• CONTROL_FILES

Chapter 26 – Recovering from Noncritical Losses

Recovering from Loss of a Temporary Tablespace

• It is impossible to backup or restore temporary tablespaces. If damaged, they can be replaced, instead of restored.
• Loss of undo data is a critical data loss.
• Temporary data in temporary tablespaces is data that exists for one database session or less. The data is also private to the session that created it.
• Temporary data comes in two forms:
  • Sort data is generated when an operation requiring rows to be sorted occurs and it can’t fit in memory. Examples are ORDER BY or creating indices.
  • Global temporary tables are used globally however, each session can only see the rows that it inserted. It also depends on operations not fitting into memory (ex. create global temporary table gt1 as select * from hr.employees;).
• Working with temporary data never produces undo or redo data.
• If a tempfile is not available at startup, the database will still open. The problem won’t become apparent until temporary space is needed or you check the alert log. (ex. create global temporary table gt1 …)

Damage to a Tempfile

• Temporary tablespaces are very different from the tablespaces / datafiles that make up permanent tablespaces. Temporary tablespaces are not needed to OPEN the database, while all permanent tablespaces are. Missing temporary tablespaces will produce messages in the alert log and throw errors to users who require temp space.
• Temporary tablespaces are also not written to by the DBWn processes, they are written to by the server processes servicing the sessions that need temp space.

Restoring a Temporary Tablespace

• SQL> alter tablespace temp_tbs1 add tempfile ‘/opt/…/temp_tbs1.dbf’ size 100m;
  • add new datafile to existing damaged temp tablespace
• SQL> alter database tempfile ‘/…/old_tmp_tbs.dbf’ offline;
  • take damaged datafile offline
• SQL> alter database tempfile ‘/…/old_tmp_tbs.dbf’ drop;
  • drop damaged datafile

Recovering from Loss of an Online Redo Log File

• An Oracle db requires at least two online file groups, each with a valid member.
• The log with the highest sequence number (according to v$log) is the CURRENT group.
• An ACTIVE group is not current but still has data referrring to blocks in the db buffer cache (dirty) not yet written to the datafiles by the DBWR. If the instance failed, both the CURRENT and ACTIVE groups would be needed for recovery since they both contain references to dirty blocks in the db buffer cache. An INACTIVE group contains no dirty references.
• Damage to a multiplexed redo log member won’t affect the instance’s OPEN status.
• Damage to all copies of the current logfile group will cause the instance to terminate immediately. It will also terminate at log switch if all copies of the log group being made current are damaged.
• To recreate a damaged log file member there are two choices:
  • Drop the damaged member and add a replacement
    • alter database drop logfile member ‘/u01/…’;
    • delete the file from the operating system
    • alter database add logfile member ‘/u01/…’ to group 1;
  • Clear the log group
    • alter database clear logfile group 2;

Recovering from Loss of an Index Tablespace

• If an index is not available, the table will be locked for nearly all DML operations: (no inserts, deletes or updates).
• Index data can be regenerated from the base tables and is therefore considered noncritical.
• It is generally considered good practice to create a tablespace specifically for your indexes. If the index tablespace and table tablespace are on different disks, there will be less contention on the data, as well.
• Recreating an index tablespace:
  • Take damaged tablespace offline
  • Determine which indexes were in the damaged tablespace
    • select owner, segment_name from dba_segments where tablespace_name=’INDX’ and segment_type=’INDEX’;
  • Drop the tablespace and delete the files from the OS.
    • drop tablespace INDX including contents and datafiles;
  • Create a new tablespace.
  • Generate all the indexes in it.
• Regenerating indexes:
  • Many applications have GUI options to rebuild indexes.
  • There are also rebuild index scripts often contained in the documentation.
  • If there is no documentation, you can query the Data Dictionary (join DBA_INDEXES and DBA_IND_COLUMNS) to find which columns in which tables were indexed, which type of index (B*Tree or bitmap) or other characteristics like UNIQUE or COMPRESSED.
  • The Data Dictionary will delete index information with the “including contents” clause of the “drop tablespace” statement.
  • Another option is to use datapump to extract all index definitions and reimport them.
• IOTs are indexes (not tables) yet they must be backed up like regular heap tables. Backing up an index tablespace is optional, however. To decide you should do a cost benefit analysis of how long it would take to restore vs. how long it would take to recreate the indexes.
• If an index is not available due to media damage, any DML against the indexed columns will fail. Queries against the indexed columns will also fail. Even DML against constrained columns in a child table (if referencing a parent table with a missing index) will fail.

Recovering from Loss of a Read-Only Tablespace

• When the command ALTER TABLESPACE .. READ ONLY is issued, the datafiles are checkpointed (db buffer cache flushed to datafiles), the SCN is noted and the file headers (which store the SCN) are frozen.
• Obviously, the SCN in a read only tablespace will not be up to date with the rest of the database. This is an exception that does not apply to other tablespaces.
• You cannot perform any DML operations against a read-only tablespace. You can drop objects from it because a DROP updates the Data Dictionary which is not a read-only tablespace.
• Read-only tablespaces only need to be backed up once, because they never change. It is still possible to back them up regularly, just pointless.
• If RMAN is configured with BACKUP OPTIMIZATION ON, then it will only back up the tablespace to satisfy the retention policy.
• If the status of a read-only tablespace is changed to read/write then it must be backed up immediately. Otherwise, RMAN will require all archive logs since the last backup (which can be months, since read only tablespaces may not be backed up consistently) if a restore is required.

Recovering from Loss of the Password File

• Refresher: the password file is used to authenticate users when the db is not open (because it cannot access the Data Dictionary). The init parameter remote_login_passwordfile (if not set to none) specifies whether the password file is used to authenticate (regular) usernames with SYSDBA privileges or not. The password file is mainly used for authenticating SYSDBA over a network (sqlplus running on a client, connecting to a server).
• The password file is a secondary method of authentication. OS authentication is always available.
• RMAN does not backup the password file.
• If the password file is damaged, then the instance will continue to function as normal, except that remote SYSDBA connections will not be possible. Startup will fail when the instance tries to read the damaged password file. The quick fix is to set the REMOTE_LOGIN_PASSWORD (static) parameter to NONE.
• Another option is to recreate the password file:
  • orapwd file=<filename> password=<password> entries=<max_users>

Views & Tables

• V$TABLESPACE
• V$TEMPFILE
• V$DATAFILE
• V$LOG
• V$LOGFILE
• V$RECOVER_FILE
• DBA_TABLESPACES
• DBA_DATA_FILES
• DBA_TEMP_FILES
• DBA_USERS
• DATABASE_PROPERTIES
• DBA_SEGMENTS

Parameters

• REMOTE_LOGIN_PASSWORD

DVDs I Own

Movies

• 10 (1979)
• 24 Hour Party People (2002)
• 25th Hour (2002)
• 28 Days Later… (2002)
• A Midsummer Night’s Dream (1999)
• A Midsummer Night’s Sex Comedy (1982)
• About Schmidt (2002)
• Alice (1990)
• Amarcord (1973)
• American Splendor (2003)
• An Inconvenient Truth (2006)
• Anything Else (2003)
• Auberge espagnole, L’ (2002)
• Bad Santa (2003)
• Bananas (1971)
• Beerfest (2006)
• Before Sunrise (1995)
• Before Sunset (2004)
• Being John Malkovich (1999)
• Blade Runner (1982)
• Blame It on Rio (1984)
• Block-Heads (1938)
• Bottle Rocket (1996)
• Brazil (1985)
• Broadway Danny Rose (1984)
• Broken Flowers (2005)
• Capturing the Friedmans (2003)
• Carandiru (2003)
• Casablanca (1942)
• Caterina va in città (2003)
• Celebrity (1998)
• Chicago (2002)
• Cidade de Deus (2002)
• Cité des enfants perdus, La (1995)
• Coffee and Cigarettes (2003)
• Collateral (2004)
• Constantine (2005)
• Cool Hand Luke (1967)
• Crash (2004/I)
• Crimes and Misdemeanors (1989)
• Dazed and Confused (1993)
• Deconstructing Harry (1997)
• Defending Your Life (1991)
• Dirty Pretty Things (2002)
• Donnie Darko (2001)
• Easy Money (1983)
• Elf (2003)
• Everyone Says I Love You (1996)
• Everything You Always Wanted to Know About Sex * But Were Afraid to Ask (1972)
• Fabuleux destin d’Amélie Poulain, Le (2001)
• Fahrenheit 9/11 (2004)
• Fast Times at Ridgemont High (1982)
• Festen (1998)
• Fiddler on the Roof (1971)
• Fight Club (1999)
• Finding Nemo (2003)
• Fucking Åmål (1998)
• Ghost Dog: The Way of the Samurai (1999)
• Ghost World (2001)
• Gladiator (2000)
• Gosford Park (2001)
• Harold & Kumar Go to White Castle (2004)
• Haute tension (2003)
• He Got Game (1998)
• Heat (1995)
• Hotel Rwanda (2004)
• I Heart Huckabees (2004)
• Incident at Loch Ness (2004)
• Italiensk for begyndere (2000)
• Keep the River on Your Right: A Modern Cannibal Tale (2000)
• Kicking and Screaming (1995)
• Kill Bill: Vol. 2 (2004)
• Kinsey (2004)
• L.A. Confidential (1997)
• Lost in Translation (2003)
• Lucía y el sexo (2001)
• Man on Fire (2004)
• Manhattan (1979)
• Manhattan Murder Mystery (1993)
• Midnight Cowboy (1969)
• Midnight Madness (1980)
• Mighty Aphrodite (1995)
• Mononoke-hime (1997)
• Moulin Rouge! (2001)
• Much Ado About Nothing (1993)
• Mulholland Dr. (2001)
• My Dinner with Andre (1981)
• My Life Without Me (2003)
• Napoleon Dynamite (2004)
• Narc (2002)
• National Treasure (2004)
• Notti di Cabiria, Le (1957)
• Nuovo cinema Paradiso (1989)
• Old School (2003)
• Once Upon a Time in America (1984)
• Owning Mahowny (2003)
• Play It Again, Sam (1972)
• Punch-Drunk Love (2002)
• Radio Days (1987)
• Ray (2004/I)
• Respiro (2002)
• Riding Giants (2004)
• Rosencrantz & Guildenstern Are Dead (1990)
• Rushmore (1998)
• Saved! (2004)
• Secondhand Lions (2003)
• Sen to Chihiro no kamikakushi (2001)
• September (1987)
• Shadows and Fog (1992)
• Sideways (2004)
• Sin City (2005)
• Six Degrees of Separation (1993)
• Slacker (1991)
• Sleeper (1973)
• Spider-Man 2 (2004)
• Stardust Memories (1980)
• Summer Lovers (1982)
• Sweet and Lowdown (1999)
• Sydney (1996)
• Tape (2001)
• Thank You for Smoking (2005)
• The 13th Warrior (1999)
• The Aviator (2004)
• The Big Lebowski (1998)
• The Bourne Supremacy (2004)
• The Dreamers (2003)
• The Game (1997)
• The Green Mile (1999)
• The Hitchhiker’s Guide to the Galaxy (2005)
• The Last Supper (1995)
• The Life Aquatic with Steve Zissou (2004)
• The Magnificent Seven (1960)
• The Matador (2005)
• The Matrix (1999)
• The Matrix Reloaded (2003)
• The Purple Rose of Cairo (1985)
• The Royal Tenenbaums (2001)
• The Sixth Sense (1999)
• The Squid and the Whale (2005)
• The Station Agent (2003)
• The Terminal (2004)
• The Unbearable Lightness of Being (1988)
• Titus (1999)
• Traffic (2000)
• Travolti da un insolito destino nell’azzurro mare d’agosto (1974)
• Troy (2004)
• Tsotsi (2005)
• Van Wilder (2002)
• Waking Life (2001)
• Wall Street (1987)
• Way Out West (1937)
• Whale Rider (2002)
• Y tu mamá también (2001)
• Zelig (1983)

TV Shows

• Chappelle’s Show (2003)
• Curb Your Enthusiasm (2000)
• Fishing with John (1991)
• I, Claudius (1976) (mini)
• Laguna Beach: The Real Orange County (2004)
• Lonesome Dove (1989) (mini)
• Northern Exposure (1990)
• Rome (2005)
• The Office (2005)

Chapter 25 – Diagnosing Oracle Database Issues

• Alert information is in the alert_<SID>.log and trace files within the BACKGROUND_DUMP_DEST directory (bdump). There is also a separate alert system within the database.
• The alert log contains a continuous history of operations affecting the structure of the database and instance. It can be viewed through EM or the OS file.
• Trace files are named with the following format: instance_process name_[PID or thread number] (*NIX vs. Windows) then .trc suffix. (ex. orcl_lgwr_1632.trc).
• MMON (Manageability Monitor) sends out server generated alerts. It is assisted by the MMNL process (Manageability Monitor Light).
• A metric is a meaningful figure converted from a statistic. (ex. disk reads per second vs. disk reads). They are stored in the AWR (Automatic Workload Repository) within the SYSAUX tablespace.
• A threshold alert refers to situations that build up over time (disk space usage). Nonthreshold alerts refer to unpredictable events (snapshot too old).
• Stateful alerts persist until cleared (disk space). Stateless alerts do not persist (snapshot too old).
• MMON writes alerts to queues. The queues can be accessed by many sessions. The alert log is completely separate from the server alert system.
• Setting the parameter SQL_TRACE=true will enable tracing of every SQL statement against the instance. The trace files will be created in the USER_DUMP_DEST directory. The file format is <SID>_ora_<SPID>.trc. In order to find the username use a similar JOIN SQL statement :
  • SQL> select s.username, s.sid, p.spid from v$session s, v$process p where s.paddr=p.addr;
• To enable SQL tracing for your session:
  • SQL> alter session set sql_trace=true;
• To enable SQL tracing for someone else’s session:
  • Find sessionID & serial#:
    • SQL> select sid, serial# from v$session where username=’JSMITH’;
  • Enable tracing on session:
    • SQL> execute dbms_monitor.session_trace_enable(session_id=>162, serial_num=>14);
  • Disable tracing on session:
    • SQL> execute dbms_monitor.session_trace_disable(session_id=>162, serial_num=>14);
• To enable tracing through DB Control goto Performance -> Top Consumers -> Top Sessions

Views & Tables

• DBA_EXTENTS
• V$ALERT_TYPES (there are over 100 types of alerts)
• DBA_OUTSTANDING_ALERTS
• DBA_ALERT_HISTORY (stateless alerts go directly here)
• V$SESSION
• V$PROCESS

Parameters

• BACKGROUND_DUMP_DEST
• USER_DUMP_DEST
• MAX_DUMP_FILE_SIZE
• STATISTICS_LEVEL (set to TYPICAL by default)
• SQL_TRACE