oracle backup and recovery

Oracle Backup and Recovery

Created By: Saurabh Kulkarni

www.yogijicreations.com

Index Backup and Need of it Different types of backup

Hot vs cold Full vs incremental Physical vs logical

User Managed backup RMAN Database recovery

User managed RMAN based

Flashback Recovery

Backup

What do we mean by database backup ?

The process of periodically taking a copy of the database and

log file (and possibly programs) onto offline storage media.

A backup is a copy of data.

Need of taking backup Instance Failures

instance shuts down without synchronizing all the database files to the same system change number (SCN)

A few causes for instance failure: A power outage A server hardware failure Failure of an Oracle background process Emergency shutdown procedures (intentional power outage

or SHUTDOWN ABORT)

Solution :STARTUP Database and let Oracle automatically perform instance recovery using the online redo logs and undo data in the undo tablespace.

Need of taking backup

User Error Failures Inadvertently delete or modify data in

tables or drop an index. the DROP TABLE command worked fine,

but you really didn’t want to drop that table!

Media Failures The loss of one or more database files

(Datafiles, Control files, redo log files) The database file can be lost or

corrupted for a number of reasons: Failure of a disk drive Failure of a disk controller Inadvertent deletion or corruption of a

database file

Different types of backup techniques

Hot or On-line Backups backup of one or more database files

while the database is open Inconsistent !!!

Mismatch in SCNSCN ( System change Number):A stamp that defines a committed version of a database at a pointin time.

Oracle assigns every committed transaction a unique SCN.

In 24 X 7 environment Database should be in ARCHIVELOG

Cold /offline Backup Backing up the database when it is

closed (shutdown state). Consistent

No need of recovery because the data is already consistent.

Whole database backups Tablespace Backups Datafile Backups

Full Backup Incremental Backup

Differential Incremental Backup Cumulative Incremental Backup

Differential Incremental Backup Backup all the blocks that have changed between level 0 and

level 1

Cumulative Incremental Backup Backup all the blocks till the level 1.

User-Managed Backups

The basic user-managed backup strategy is to make periodic backups of datafiles and archived logs with operating system

commands.

Logical or physical. Physical: Backup of physical file structure (datafiles, control

files..) Logical: backups of logical objects such as tables, views…

Export / Import utility is used to take logical backup and restore it back.

Basic Backup Methodology Identify the datafiles, control files, and archived redo logs to be

backed up by querying dynamic performance views or data

dictionary tables Use an operating system command such as the

UNIX cp command to back up datafiles and archived redo logs Use a SQL statement to back up the control file Use an operating system command such as the

UNIX cp command to back up configuration files

Basic Backup Methodology

The RMAN Oracle provided utility for backing-up,

restoring and recovering Oracle Databases Doesn't require a separate installation. Off-line and on-line database backups. Can not write directly to tape.

3rd-party tools (like Veritas, Omiback) can integrate with RMAN to handle tape library management.

Only compatible with Oracle release 8.0 or higher

RMAN features Compress backups of datafiles so that only those data

blocks that have been written to are included in a backup

Store frequently executed backup and recovery operations in scripts.

Perform incremental backups Create a duplicate production database for testing

purpose printable message log of all backup and recovery

operations. Recovery catalog automates both restore and

recovery operations Perform crosschecks

RMAN Backup Concepts

Server-managed backup Can back up entire database, all

datafiles in a tablespace, selected datafiles, control files, archived redo log files

RMAN: Backup techniques

Image File

ArchivedLog file

Datafile 3

Copy of datafile 3

Copy of archived log

Datafile 3

ArchivedLog file

Control file

Copy of control file

RMAN: Backup techniques

Backup Set

Backup set 1

Backup set 2

Backup set 3

Datafile 1

Datafile 2

Datafile 3

Datafile 4

Controlfile

Datafile 4

Controlfile

Datafile 1

Datafile 2

Datafile 3

Backup Set Usually contain more than one file. Can be written to a disk or tape. Required to extract files for restoration Can be incremental or full. Do not include never-used blocks.

Backup Piece A backup piece is a file in a backup set. A backup piece can contain blocks from

more than one datafile.

Backup set 1 (Logical)

Piece 2 (file)Piece 1 (file)

Datafile 1

Datafile 4

Datafile 5

RMAN commands

BACKUP Back up database files, archive logs, backups, or copies.

CATALOG Add information about files to the repository. (10g+)

CHANGE Update the status of a backup in the RMAN repository.

CONFIGURE Persistent RMAN settings. CONNECT Connect to a recovery catalog or auxiliary

database CROSSCHECK Check whether backup items still exist. DELETE Delete backups from disk or tape EXIT Exit

the RMAN console LIST List backups and copies

RMAN Commands RECOVER Apply redo logs or incremental backups to a restored

backup set (or copy) in order to update it to a specified time. REPORT Report backup status: database, files, backups RESTORE Restore files from an RMAN backup (or copy) RUN Some RMAN commands are only valid inside a RUN block. SET Settings for the current RMAN session. SHOW Display the current configuration SHUTDOWN Shutdown the database SQL Execute a PL/SQL

procedure or SQL statement(not SELECT) STARTUP Startup the database ADVISE FAILURE Display repair options (11g Data Recovery

Advisor) REPAIR FAILURE Repair database failures (11g Data Recovery

Advisor)

Backing up database using RMAN

Command: Backup During an RMAN backup, the Oracle

database server reads the datafiles, not an operating system utility.

The server reads each block and determines whether the block is inconsistent.

If the block is inconsistent, then Oracle re-reads the block until it gets a consistent picture of the data.

Backing up database using RMAN

Logical name assigned to a backup set or image copy

month_full_backup week_full_backup Wednesday_1_backup

Datafile 3

Datafile 4

Backup set

Datafiles 1,3

Datafiles 2,4

Backup set

Datafile 1

Backup set

RMAN Control File Autobackups

CONFIGURE CONTROLFILE AUTOBACKUP

When enabled, RMAN automatically performs a control file autobackup after BACKUP or COPY commands

Archived Redo Log Backups

Archived redo logs are essential for recovering an inconsistent backup.

BACKUP ARCHIVELOG

RMAN Dynamic Views

V$ARCHIVED_LOG V$BACKUP_CORRUPTION V$COPY_CORRUPTION V$BACKUP_DATAFILE V$BACKUP_REDOLOG V$BACKUP_SET V$BACKUP_PIECE

Database Recovery

Difference between restoring and recovering?

Restoring: copying backup files from secondary storage (backup media) to disk.

Recovering: process of applying redo logs to the database to roll it forward.

Preparing to recover (user managed recovery)

Remain calm and do not panic Restore database files to their

respective locations Recover the database to the point of

failure using Online redo log files. (Roll forward)

Roll back to the last consistent state.

Restoring Backups of the Damaged or Missing Files

Determine which datafiles to recover SELECT * FROM V$RECOVER_FILE;

Restoring Backups of the Damaged or Missing Files Query V$DATAFILE and V$TABLESPACE to obtain filenames and

tablespace names for datafiles requiring recovery. Incase of media failure, indicate the new locations of datafiles

files to the control file. If the database is open, then take the tablespaces containing

the inaccessible datafiles offline. ALTER TABLESPACE users OFFLINE IMMEDIATE;

Copy backups of the damaged datafiles to their default location % cp /disk2/backup/tbs_24.bak /disk1/oracle/dbs/tbs_24.f

Recover the affected tablespace.

RECOVER TABLESPACE users Bring the recovered tablespace online

ALTER TABLESPACE users ONLINE

Re-Creating Datafiles When Backups Are Unavailable All archived log files written after the creation of the original

datafile must be available

The control file contains the name of the damaged file Create a new, empty datafile to replace a damaged

datafile that has no corresponding backup. ALTER DATABASE CREATE DATAFILE '/disk1/users1.f' AS

'/disk2/users1.f';

Perform media recovery on the empty datafile. ALTER DATABASE CREATE DATAFILE '/disk1/users1.f' AS

'/disk2/users1.f';

Recovery of Database when Control files are lost Find the location of the datafiles and log

files. Create a new control file based on above

information CREATE CONTROLFILE REUSE DATABASE "ORCL10G" RESETLOGS

LOGFILE GROUP 1 'K:\ORCL10G\LOG\REDO01.LOG' SIZE 50M, GROUP 2 'K:\ORCL10G\LOG\REDO02.LOG' SIZE 50M,

DATAFILE 'K:\ORCL10G\DATA\SYSTEM01.DBF', 'K:\ORCL10G\DATA\UNDOTBS01.DBF', 'K:\ORCL10G\DATA\SYSAUX01.DBF‘;

Open the database with resetlogs option. alter database open resetlogs;

RMAN Recovery Techniques

Enhanced RMAN Features Automated File Creation During Recovery Simplified Recovery Through Resetlogs Change-Aware Incremental Backups Automated Disk-Based Backup and

Recovery RMAN Database Dropping and

Deregistration

Automated File Creation During Recovery

This feature enhances RMAN recovery by automatically creating and recovering datafiles that have never been backed up.

Archive log files are still required

Automated File Creation During Recovery

data file#1

data file#2

Missingdata file

Control fle

Archive log files

Importance of Redo log files The online redo logs are a set of two or more files that record

all changes made to Oracle datafiles and control files. Whenever a change is made to the database, the Oracle

database server writes the data and generates a redo record in

the redo buffer.

Recovery using Flashback Functions

Flashback is an Oracle database facility to quickly move an entire database or a table back to a prior state for recovery purposes.

Flashback was first introduced with Oracle 9i with Oracle's Flashback Query feature.

FRA ( Flashback Recovery area) storage area (directory on disk

or ASM diskgroup) that enables database Flash Backup and Recovery operations.

first introduced with Oracle 10g. Related parameters:

db_recovery_file_dest db_recovery_file_dest_size db_flashback_retention_target

Flashback Query ( 9i Feature)

Allows users to see a consistent view of the database as it was at a point in the past.

In Oracle 10g, we can now retrieve all versions of the rows that exist or ever existed between the time the query was issued

and a point back in time.

Flashback Database Faster than traditional point-in-time recovery. Traditional recovery method: Backups and Redo logs Flashback Database is implemented using a new type

of log file called the Flashback Database log. Oracle database server periodically logs before

images of data blocks in the Flashback Database logs. The data block images are used to quickly back out

changes to the database during Flashback Database. RVWR: similar to the LGWR (log writer) process.

writes Flashback Database data to the Flashback Database logs.

Enabling Flashback Database Make sure the database is in archive mode. Configure the recovery area by setting the two

parameters: DB_RECOVERY_FILE_DEST DB_RECOVERY_FILE_DEST_SIZE

Open the database in MOUNT EXCLUSIVE mode and turn on the flashback feature:SQL> STARTUP MOUNT EXCLUSIVE;SQL> ALTER DATABASE FLASHBACK ON;

Set the Flashback Database retention target: DB_FLASHBACK_RETENTION_TARGET

Flashback Drop

DROP command permanently removed objects from the database…

In Oracle 10g, a DROP command places the object in the recycle bin.

You can restore the object from the recycle bin at any time.

Flashback table emp to before drop; drop table emp PURGE;

Recycle Bin

A recycle bin contains all the dropped database objects until, Permanently drop them with the PURGE

command. Recover the dropped objects with the

FLASHBACK TABLE command. There is no room in the tablespace for

new rows or updates to existing rows. The tablespace needs to be extended.

Flashback Table Flashback Table allows you to recover a

table or tables to a specific point in time without restoring a backup.

When you use the Flashback Table feature to restore a table to a specific point in time, all associated objects, such as, indexes, constraints, and triggers will be restored.

FLASHBACK TABLE emp TO SCN 76230;

Flashback Transaction Query

It provides a way for you to view changes made to the database at the transaction level.

It allows you to diagnose problems in your database and perform analysis and audit transactions

Questions ?Questions ?

For queries: info@yogijicreations.com

oracle backup and recovery

oracle backup

logical backup

index backup

executed backup

backup store

online backups backup

basic backup methodology

backup user error failures

Education

oracle database backup & recovery - best practices

oracle exadata database machine - backup & recovery sizing

nocoug 2005 - oracle 10g backup and recovery

ibm db2 udb versus oracle backup and recovery

oracle engineered systems backup and recovery best practices

emc next-generation backup and recovery for oracle

oracle database 11g backup and recovery technologies ·...

oracle database backup and recovery reference

backup and recovery overview supinfo oracle lab. 6

dba - oracle dba - backup and recovery scripts

oracle database backup and recovery reference -...

oracle 9i - user-managed backup and recovery guide

emc backup and recovery for oracle 11g oltp backup and...

oracle recovery manager (rman) · oracle backup & recovery...

oracle database backup and recovery with vmax3 - emc

95979031 oracle database backup and recovery

oracle database backup & recovery, flashback* whatever, data...

h6540 oracle backup recovery perform avamar rman wp

backup and recovery oracle

oracle -arch-backup and recovery