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 [...]

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 [...]

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 [...]

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 [...]

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 [...]

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 [...]

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 [...]

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 [...]

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 [...]

Chapter 24 – Using RMAN to Backup Databases

There are four interfaces for using RMAN: Enterprise Manager, OS scripts aka batch mode (*NIX cron or Windows AT often to schedule), Pipe Interface, RMAN executable. By using DBMS_PIPE, you can launch an RMAN session yet control it programmatically with another session. DBMS_PIPE syntax: rman PIPE pipename target sys/pass@orcl Some RMAN commands must be executed [...]