Archive for the ‘RMAN backup and recovery’ Category

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.

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.

Cloning/Duplicating Database with RMAN

Cloning database ASM to ASM

Clone an Oracle database using RMAN duplicate (same server)

Duplicating Database using dynamic SQL

This tutorial is for cloning database on the same server.

1. Create Password File for Auxiliary Database

orapwd file=c:\oracle\ora92\database\PWDAUX.ora password=oracle

2.Create an Initialization Parameter for the Auxiliary Database

Copy the initialization parameter for the target database and make the necessary changes for the duplicated database.

SQL> create pfile=’c:\oracle\product\10.2.0\db\database\initAUX.ora’ from spfile;

After creating the initialization parameter for the duplicate database, and change at least the following parameters:

db_file_name_convert = ('C:\ORACLE\ORADATA\ORCL', 'C:\ORACLE\ORADATA\AUX')
log_file_name_convert = ('C:\ORACLE\ORADATA\ORCL', 'C:\ORACLE\ORADATA\AUX')
control_files = 'C:\oracle\oradata\AUX\control01.ctl'
              , 'C:\oracle\oradata\AUX\control02.ctl'
              , 'C:\oracle\oradata\AUX\control03.ctl'
db_name = 'AUX'
instance_name = 'AUX'
background_dump_dest = 'C:\oracle\admin\AUX\bdump'
core_dump_dest = 'C:\oracle\admin\AUX\cdump'
user_dump_dest = 'C:\oracle\admin\AUX\udump'
service_names = 'AUX.IDEVELOPMENT.INFO'
log_archive_dest_1 = 'location=C:\oracle\oradata\AUX\archive MANDATORY'
  

3. Create / Start the Auxiliary Instance

    Create new windows service for duplicate database AUX using oradim

oradim -new -sid AUX -intpwd oracle -startmode auto -pfile ‘C:\oracle\product\10.2.0\db\dbs\initaux.ora’

3. Ensure Oracle Net Connectivity to Auxiliary Database

Modify both the listener.ora and tnsnames.ora file to be able to connect to the auxiliary database. After making changes to the networking files, test the connection keeping in mind that you must be able to connect to the auxiliary instance with SYSDBA privileges, so a valid password file must exist.

C:\> lsnrctl stop LISTENER

C:\> lsnrctl start LISTENER

C:\> sqlplus sys/oracle@AUX as sysdba
It would connect as idle instance

SQL> startup nomount pfile=’C:\oracle\product\10.2.0\db\database\initAUX.ora’;
ORA-02778: Name given for the log directory is invalid

Check the location of your bdump file(other dump directories as well). If situation persists then change the bdump destination and other log file destinations.

SQL> startup nomount pfile=’C:\oracle\product\10.2.0\db\database\initAUX.ora’;

3. Mount or Open the Source Database

As mentioned in the pre-requisites section of this article, the target database should be either opened or mounted.

C:\> set ORACLE_SID=orcl
C:\> sqlplus "/ as sysdba"
SQL> startup open

4. Ensure You Have the Necessary Backups and Archived Redo Log Files

As mentioned in the pre-requisites section of this article, ensure that you have a current backup that you wish to use to create the duplicate database. Login to query the RMAN catalog:

C:\> rman target sys/passwd@ORCL

RMAN> list backup summary;
 

5.Login to Target and Auxiliary Database using RMAN

C:\> rman target sys/passwd@ORCL  auxiliary sys/password@AUX
 

6.Run the RMAN DUPLICATE DATABASE Command

The following RUN block can be used to fully duplicate the target database from the latest full backup.
Note that you can duplicate the database to a specific date/time using the UNTIL TIME '<DATE>' clause. For example, to duplicate the new database to yesterdays date/time, use the following:

duplicate target database to AUX until time 'SYSDATE-1';.

run {
# Allocate the channel for the duplicate work
allocate auxiliary channel ch1 type disk;

# Duplicate the database to AUX
set until sequence 156;

duplicate target database to AUX;

}

Reblog this post [with Zemanta]

RMAN Lists Crosschecks and Reports

RMAN Lists

For complete list of syntax and options see link

The primary purpose of the LIST command is to determine which backups or copies are available. Note that only backups and copies that completed successfully are stored in the repository. For example, you can list:

  • Backups (backup sets and proxy copies) or image copies recorded in the RMAN repository
  • Backups or image copies of a specified database, tablespace, datafile, archived redo log, or control file
  • Backups and image copies that have expired
  • Backups and image copies restricted by options such as time, path name, device type, tag, or recoverability
  • Incarnations of a specified database

list backup;

list backup of database; # lists backups of all files in database

list backup of datafile ‘/oracle/dbs/tbs_1.f’; #lists backup of specified datafile

list backup of tablespace SYSTEM; # lists all backups of SYSTEM tablespace

list backup of archivelog all; # lists all archived redo logs and copies of logs

list backup of controlfile; # lists all control file backups list expired backup;

list incarnation;

RMAN Crosschecks

Crosschecks update outdated RMAN repository information about backups whose repository records do not match their physical status. For example, if a user removes archived logs from disk with an operating system command, the repository still indicates that the logs are on disk, when in fact they are not.

If the backup is on disk, then the CROSSCHECK command determines whether the header of the file is valid. If the backup is on tape, then the command simply checks that the backup exists. The possible status values for backups are AVAILABLE, UNAVAILABLE, and EXPIRED.

CROSSCHECK BACKUP;

CROSSCHECK COPY; # crosschecks only disk copies of db files

CROSSCHECK BACKUPSET; # crosschecks backupsets on disk and SBT

CROSSCHECK BACKUP OF DATAFILE “?/oradata/trgt/system01.dbf” COMPLETED AFTER ‘SYSDATE-14’;

CROSSCHECK BACKUP OF ARCHIVELOG ALL SPFILE;


Reporting on Backups, Copies, and Database Schema

To gain more detailed information from the RMAN repository, generate a report. Use the REPORT command to answer questions such as the following:

  • Which files need a backup?
  • Which files have had unrecoverable operations performed on them?
  • Which backups or copies are obsolete and can be deleted?
  • What was the physical schema of the database at some previous time?
  • Which files have not been backed up recently?

report schema at time ‘SYSDATE-7’;

report need backup days 2 tablespace system;

report unrecoverable;

report obsolete;

REPORT NEED BACKUP INCREMENTAL = 1 DATABASE;

REPORT OBSOLETE RECOVERY WINDOW OF 7 DAYS; # lists backups or copies that are superfluous because more than 2 copies of the # files exist on tape

REPORT OBSOLETE REDUNDANCY = 2 DEVICE TYPE sbt;

REPORT SCHEMA AT SCN 1000; # schema as it existed at scn 1000

REPORT SCHEMA AT SEQUENCE 100 THREAD 1; # schema as it existed at log sequence 100

Monitor RMAN job progress

SELECT SID, SERIAL#, CONTEXT, SOFAR, TOTALWORK,ROUND(SOFAR/TOTALWORK*100,2) “%_COMPLETE”FROM V$SESSION_LONGOPS
WHERE OPNAME LIKE ‘RMAN%’
AND OPNAME NOT LIKE ‘%aggregate%’
AND TOTALWORK != 0   AND SOFAR <> TOTALWORK

RMAN Backup Configuration

Displaying Current RMAN Configuration Settings: SHOW

After you connect to the target database and recovery catalog (if you use one), run the SHOW command with the name of a setting you wish to view. For example:

RMAN> SHOW RETENTION POLICY;
RMAN> SHOW DEFAULT DEVICE TYPE;

To view all configured settings, run the RMAN SHOW ALL command, as in this example:

RMAN> SHOW ALL;    # shows all CONFIGURE settings, both user-entered and default
Configuring the Default Backup Type for Disk Backups

You can configure backup sets or image copies as the default, using either of the following commands:

RMAN> CONFIGURE DEVICE TYPE DISK BACKUP TYPE TO COPY; # image copies
RMAN> CONFIGURE DEVICE TYPE DISK BACKUP TYPE TO BACKUPSET; # uncompressed
RMAN> CONFIGURE DEVICE TYPE DISK BACKUP TYPE TO COMPRESSED BACKUPSET; #compressed  
RMAN> CONFIGURE DEVICE TYPE sbt BACKUP TYPE TO COMPRESSED BACKUPSET; 

Configuring Disk Devices and Channels

The following command configures RMAN to write disk backups to the /backup directory (refer to "Backing Up Database Files and Archived Logs with RMAN").:

CONFIGURE CHANNEL DEVICE TYPE DISK FORMAT '/backup/ora_df%t_s%s_s%p';

The format specifier %t is replaced with a four byte time stamp, %s with the backup set number, and %p with the backup piece number.

You can also configure an Automatic Storage Management disk group as your destination, as in the following example:

CONFIGURE CHANNEL DEVICE TYPE DISK FORMAT '+dgroup1';

Configuring Tape Devices and Channels

CONFIGURE DEVICE TYPE 'SBT_TAPE' PARALLELISM 2 BACKUP TYPE TO COMPRESSED BACKUPSET;

Configuring Control File and Server Parameter File Autobackup

CONFIGURE CONTROLFILE AUTOBACKUP ON;

CONFIGURE CONTROLFILE AUTOBACKUP FORMAT 
  FOR DEVICE TYPE DISK TO '?/oradata/cf_%F'

the format of the autobackup file for all configured devices is the substitution variable %F. This variable format translates into c-IIIIIIIIII-YYYYMMDD-QQ, where:

  • IIIIIIIIII stands for the DBID.

  • YYYYMMDD is a time stamp of the day the backup is generated

  • QQ is the hex sequence that starts with 00 and has a maximum of FF

RMAN Backup Formats: Image Copies and Backup Sets

RMAN backups can be stored in one of two formats: as image copies or as backup sets.

Image Copies

An image copy is a bit-for-bit duplicate of a database file, identical to a copy made with an operating system command. Image copy backups can only be created on disk. RMAN can create image copies of datafiles and datafile copies, control files and control file copies, archived redo logs, and backup pieces. RMAN creates image copies when the AS COPY option is used with the BACKUP command.

Oracle Database Backup and Recovery Advanced User’s Guide for more detailed information about RMAN’s handling of image copies

Backup Sets

RMAN can also store backup information a logical structure called a backup set. A backup set contains the data from one or more datafiles, archived redo logs, or control files or SPFILE. (Datafiles and archivelogs cannot be mixed together in the same backup set.) You can also back up existing backup sets into another backup set.

A backup set consists of one or more files in an RMAN-specific format, known as backup pieces. By default, a backup set consists of one backup piece. For example, you can back up ten datafiles into a single backup set consisting of a single backup piece (that is, one backup piece will be produced as output, the backup set consists of the single file containing the backup piece, and the backup piece and the backup set that contains it will be recorded in the RMAN repository). A file cannot be split across backup sets.

Tags for RMAN BACKUP

To specify a tag to identify a group of backups, use the TAG clause of the BACKUP command. For example, enter:

RMAN> BACKUP DATABASE TAG = ‘weekly_backup’;  

Consistent and Inconsistent Backups with RMAN

A consistent backup of the database is one taken when the database is in a consistent state, that is, one taken after the database has been shut down normally . At this point, all changes in the redo log have been applied to the datafiles.

Any backup taken when the database has not been shut down normally is an inconsistent backup. When a database is restored from an inconsistent backup, Oracle must perform media recovery before the database can be opened, applying any pending changes from the redo logs.

LIST BACKUP: see Listing RMAN Backup , Archived Logs and Database Incarnations

LIST BACKUP;       # lists backup sets, image copies, and proxy copies
LIST BACKUPSET;    # lists only backup sets and proxy copies
LIST COPY;         # lists only disk copies

 

DELETE INPUT or DELETE ALL INPUT clauses for the BACKUP ARCHIVELOG command to delete archived logs after they are backed up, eliminating the separate step of manually deleting the archived redo logs.

Backup Archivelog with Database

BACKUP ARCHIVELOG ALL;

BACKUP ARCHIVELOG 
  FROM TIME 'SYSDATE-30' UNTIL TIME 'SYSDATE-7';

BACKUP DEVICE TYPE sbt  ARCHIVELOG ALL  DELETE ALL INPUT;

BACKUP DEVICE TYPE disk DATABASE PLUS ARCHIVELOG;

Validating Backup Files:

You can use the VALIDATE option of the BACKUP command to verify that database files exist and are in the correct locations, and have no physical or logical corruptions that would prevent RMAN from creating backups of them. When performing a BACKUPVALIDATE, RMAN reads the files to be backed up in their entirety, as it would during a real backup. It does not, however, actually produce any backup sets or image copies.

If the backup validation discovers corrupt blocks, then RMAN updates the V$DATABASE_BLOCK_CORRUPTION view with rows describing the corruptions. You can repair corruptions using block media recovery After a corrupt block is repaired, the row identifying this block is deleted from the view.

BACKUP VALIDATE DATABASE ARCHIVELOG ALL;