(Based on notes from Ganesh's friend largely based on Oracle docs)
Backup
A database backup is is a copy of the data stored in a database. There are two types of backups: physical backups and logical backups.
A physical backup is a copy of the files that belong to a database at a specific point in type. It can either be a cold backup or hot backup.
A logical backup is does not copy the files, instead, it extracts the data into a specific file format (for example with exp.)
A backup is needed to restore the data in a database if it gets damaged.
Some structural changes as well as direct path load require a backup.
Consistent and inconsistent backups
Consistent backup
A consistent backup exhibits the following three properties:
All headers of datafiles that belong to a writable tablespaces have the same checkpoint SCN.
These datafiles don't have any changes past this checkpoint SCN.
Lastly, The SCNs of the datafile headers match the checkpoint information in the controlfiles.
Inconsistent backup
An inconsistent backup is where some files contain changes that were made after the files were checkpointed. A recovery is needed in order to make the backup consistent.
An inconsistent backup is created by a hot backup. Also, if the database crashed (or was shutdown abort) and then the backup was made.
Restore
A restore is the copying of backed up data to their original location in order to recover the database.
Usually, a file is restored after a media failure. However, there are other situations where a file is restored, for example a point in time recovery.
Recovery
Recovery is the process of bringing the database "up to date" since the last backup (whose data files would have been restored). All database changes are applied to the restored database files by "rolling forward" on the archived redolog files.
It is also possible to recover (incomplete) if the current redo log is lost or if the controlfiles are lost.
In rare cases consider applying the unarchived redolog file/s in the recovery process if it makes sense.
Types of recovery
There are four types of recovery:
• Media/Datafile media recovery
• Crash recovery
Oracle performs an automatic crash recovery when the instance is started after a shutdown abort or after a crash.
A crash recovery 'only' uses the online redo log to recover online datafiles.
Crash recovery is the recovery of a database in a single-instance configuration or an Oracle Real Application Clusters configuration in which all instances have crashed. In contrast, instance recovery is the recovery of one failed instance by a live instance in an Oracle Real Application Clusters configuration.
• Instance recovery
Instance Recovery is only used in a RAC environment. It is the application of online redo log by one instance that detected the crash of another instance.
• Disaster recovery
There are at least five options to be considered for disaster recovery when running Oracle:
• OPS (Oracle Parallel Server)
• Replication
• Standby databasess
• OS oriented solutions
• Architectural solutions
A recovery (if it is not a crash recovery) can either be a
• Complete recovery, or an
• Incomplete recovery (Also known as Point in time recovery (PITR), or database point in time recovery (DBPITR))
Not all redo generated since the last backup is applied in a incomplete recovery. It is also called database point in time recovery (DBPITR).
Flashback can sometimes be used as an alternative to point in time recovery
Performing backup and recovery
Backups and recovery can be performed either
• with RMAN (the recovery manager) or
• using SQL*Plus and operating system file copy commands (such as cp on Unix and copy on Windows).
recover syntax
recover database until cancel
recover database until time '2004-03-21:22:59:04'
recover database until change 123456
recover datafile 'filename' until cancel
recover datafile 'filename' until time '2004-03-21:22:59:04'
recover datafile 'filename' until change 123456
recover tablespace ts_name until cancel
recover tablespace ts_name until time '2004-03-21:22:59:04'
recover tablespace ts_name until change 123456
recover database using backup controlfile
Until time
Performs a incomplete recovery (=Point in time recovery).
The format of the time is 'YYYY-MM-DD:HH24:MI:SS'
Until change
Performs a incomplete recovery.
The number that follows until change is an SCN. In order to recover up to and including SCN 999, use recover until change 1000.
Standby database
Recovers a standby database.
Using backup controlfile
Don't use the current controlfiles, use backed up control files instead.
Useful SQL to run in a recovery scenario
select substr(name, 1, 50), status from v$datafile;
select substr(name,1,40), recover, fuzzy, checkpoint_change#
select name, open_mode, checkpoint_change#, ARCHIVE_CHANGE# from v$database;
select GROUP#,THREAD#,SEQUENCE#,MEMBERS,ARCHIVED,STATUS,FIRST_CHANGE# from v$log;
select GROUP#,substr(member,1,60) from v$logfile;
select * from v$recover_file;
select distinct status from v$backup;
select hxfil FILENUMBER,fhsta STATUS,fhscn SCN,fhrba_Seq SEQUENCE from x$kcvfh;
select fhsta, count(*) from X$KCVFH group by fhsta;
select * from v$log;
select * from v$logfile;
No comments:
Post a Comment