Archive for April, 2011

Recovery Manager Features

RMAN is an Oracle utility that you use to manage the backup, restore, and recovery operations on Oracle databases. RMAN has a powerful command language that is independent of the operating system.

RMAN provides several features not available when you make user-managed backups with
operating system commands.

  • Frequently executed operations can be stored as scripts in the database.
  • With block change tracking enabled in the database RMAN can limit incremental backups to recording only those blocks that have changed since the previous backup.
  • RMAN can be used to manage the size of backup pieces and save time by parallelizing the backup operation.
  • RMAN can recover an individual corrupt data block or set of data blocks within a data file rather than restoring and recovering the entire data file.
  • RMAN operations can be integrated with the Oracle Database Scheduler to automate
    backup operations.
  • You can use RMAN to detect block corruption. The information relating to the block
    corruption that is detected during backup can be obtained by using the
    V$BACKUP_CORRUPTION and V$COPY_CORRUPTION dynamic views.
  • RMAN provides performance enhancements such as:
            – Automatic parallelization of backup, restore, and recovery operations
            – No generation of extra redo during online database backups
            – Backups can be restricted to limit reads per file, per second to avoid interfering   with OLTP work
            -  Prevention of flooding of any one file with reads and writes while still keeping a tape drive streaming, using multiplexing
  • RMAN has a media management API to work seamlessly with third-party media
    management tools interfacing with storage devices providing increased speed and
    reliability.
  • Under the user-managed method you need to keep track of all database files and backups. In a recovery situation you must locate backups for each data file, copy them to the correct place using operating system commands, and choose which redo log files to apply. RMAN manages these tasks automatically.

Reading Alert Log

With Oracle 10g, external table was required to read alert log  but luckily Oracle 11g created a system table that can be used to access alert log from SQL:  X$DBGALERTEXT. Oracle reads the log.xml from alert directory (which contains all the data what alert.log does), parses it and returns the details back as rows.

Beginning with Release 11g, the alert log file is written as XML formatted and as a text file (as in previous releases). The default location of both these files is the new ADR home (Automatic Diagnostic Respository, another dump dest in 11g).

The database, Automatic Storage Management (ASM), Cluster Ready Services (CRS), and other Oracle products or components store all diagnostic data in the ADR.

The ADR is set by using the DIAGNOSTIC_DEST initialization parameter. If this parameter is omitted, then, the default location of ADR is, ‘$ORACLE_HOME/log’

The location of an ADR home is given by the following path, which starts at the ADR base directory:

$ORACLE_BASE/diag/product_type/product_id/insance_id

v$diag_info can be queried to check ADR locations, including ADR BASE and ADR home.

Within the ADR home directory are subdirectories:

alert       – XML formatted alertlog
trace      – Files and text alert.log file
cdump – core files

message_text can have multiple lines of ORA- errors and related tracing file information, but for monitoring only need first error code.

SELECT        record_id, substr(message_text, instr(message_text,'ORA-'),
              instr(message_text,chr(10),1,2)-instr(message_text,'ORA-') ) message_text
FROM          <strong>X$DBGALERTEXT</strong>
WHERE         originating_timestamp > sysdate-1
              AND message_text like '%ORA-%'
ORDER BY  record_id desc

Query to get errors for the past day:

</span>
<pre>SELECT  distinct    originating_timestamp,   message_text
FROM    X$DBGALERTEXT
WHERE   originating_timestamp > sysdate-1
AND     (  message_text = 'ORA-00600'  OR
           message_text like '%Fatal%'
        )

Query to get errors for the last 10 minutes:

</span>
<pre>SELECT         rownum       “line”,    message_text “error”
FROM           X$DBGALERTEXT
WHERE          originating_timestamp > (sysdate – 10/1440)  AND
               message_text like ‘%ORA-%’
ORDER BY       originating_timestamp;


Links:

Oracle 11g Reading Alert Log Via SQL

Note: 1072547.1 – How To Edit, Read, and Query the Alert.Log

Oracle 11g Automatic Diagnostic Repository

Backup Concepts

Database Backup

 

A whole database backup includes all data files and at least one control file.

Full backups make a copy of every data block within the files being backed up that contains data.

Partial database backups may include zero or more tablespaces, zero or more data files, and may or may not include a control file.

 

Incremental backups make a copy of all data blocks that have changed since some previous backup. Oracle Database 10g supports two levels of incremental backup (0-1). A level 0 or baseline backup is equivalent to a full backup and contains all data blocks. A level 1 incremental backs up all database blocks changed since the level 0 backup. To restore using incremental backups, the baseline backup must first be restored, and then the incremental.

Offline backups or Cold backups(also known as consistent backups) are taken while the database is not open. They are consistent because at the time of the backup, the SCN data file headers matches the SCN in the control files.

Online backups (also known as hot or inconsistent backups) are taken while the database is open. The backups are inconsistent because with the database open there is no guarantee that the data files are synchronized with the control files. Inconsistent backups require recovery in order to be used. For online backup, ARCHIVELOG mode should be enabled.

 

Modes of Backups

i. ARCHIVELOG

If the database is in noarchive log mode, online redo logs can be overwritten without making sure they are saved. This implies that a database cannot be recovered fully (it can be rolled forward to the last transaction) even if backups were made.

ii. NOARCHIVELOG

If the database is in log archive mode, the database makes sure that online redo logs are not overwritten before they have been archived. Database can be recovered to the last

Types of Backups

i. Physical Backup

Physical backups are backups of the physical files used in storing and recovering your database, such as datafiles, control files, and archived redo logs.

ii. Logical Backup

Logical backups contain logical data (tables or stored procedures) exported from a database with an Oracle export utility and stored in a binary file, for later re-importing into a database using the corresponding Oracle import utility.

 

Backup Types based on Physical Backup

For performing backup and recovery based on physical backups, you have two solutions available:

  • Recovery Manager (RMAN), a tool (with command-line client and Enterprise Manager GUI interfaces) that integrates with sessions running on the Oracle server to perform a range of backup and recovery activities, as well as maintaining a repository of historical data about your backups
  • The traditional user-managed backup and recovery, where you directly manage the files that make up your database with a mixture of host operating system commands and SQL*Plus backup and recovery-related capabilities

Recovery Manager isthe preferred solution for database backup and recovery. It can perform the same types of backup and recovery available through user-managed methods more easily, provides a common interface for backup tasks across different host operating systems, and offers a number of backup techniques not available through user-managed methods.

 

Image Copies and Backup Sets

 

Image copies are duplicates of data or archived log files (similar to simply copying the files using OS commands).

Backup sets are copies of one or more data or archived log files. With backup sets, empty data locks are not stored, thereby causing backup sets to use less space on disk or tape. Backup sets an be compressed to further reduce the space requirements of the backup.

Image copies must be backed up to disk whereas Backup sets can be sent to disk or directly to tape.