THREE FUNDAMENTAL TYPES ARE :
1) Physical Backup & Recovery
----------------------------------------------
> entire database
> often referred to as COLD or HOT backup
> Used for recovery
2) Logical Backup & Recovery
-------------------------------------------
> for specific logical database structures such as table,index,schema
> can be done by oracle data pump export/import utilities
> can be used only for restore and not for recovery
3) RMAN
---------------
> physical database backup in controlled manner.
> RMAN toolset is used
> simplified syntax, highly consistent and recommended
WHY WE SHOULD BACKUP BINARY FILES?
> Oracle Binaries should be backed up after every release and product patch as it is easy to restore file then installing again from CD.
> You may forget your current patch level
WHAT ABOUT PARAMETER FILES?
> MUST back up init.ora and spfile..they contains the parameters which define database.
> Also backup the control file ( both binary and text version)
WHAT ABOUT UNDO SEGMENTS, REDO LOG FILE and ARCHIVE log?
> Backup all UNDO, REDO and archive log.
> very important for DB recovery
> keep in mind that no recovery is possible without archive files ( only restore can be done)
TRACE FILES
---------------------
> Three type of dump files
> BDUMP (Background dumps) written by background processes ( one most important file to backup here is ALERT.LOG)
> UDUMP (User dumps) Written for user's processes for the purpose of debugging
> CDUMP (Core dumps) Core files dumped by oracle
These files contains a history of problems and are WORTH backing up for troubleshooting future problems.
USER-MANAGED BACKUPS
-----------------------------
1) COLD BACKUPS
2) HOT BACKUPS
1) COLD BACKUPS
-----------------------------
> All users disconnected; Database shutdown
> Simplest type
> All DB files backed up to Disk or Tape
> Database need not be in the Archive log mode
> Shutdown should be consistent ( means don't use shutdown ABORT)
> But say that in some situation you are forced to go for shutdown Abort ,THEN follow it up with a startup restrict and shutdown [ immediate, transactional, normal]
> Here ORACLE recommends not to backup REDO files as at times , restoring of redo files leads to overwriting of existing redo logs!
2) HOT BACKUPS
----------------------------
> Database is UP & Running and end users are working
> To be run when system is no busy and put one tablespace between begna dn end backup.
> Database must be in archivelog mode( means log_archive_start= true and sql>alter database archivelog; command run).
> Tablespaces are put in backupmode -> data is copied -> tablespace backup mode ended
> After completion of backup, make sure that all the log records created during are archived (sql> archive log current).
> For recovery use data files from hot backup + archive logs generated)
> online redo log should never be backed up during hot backup -> Rather archive them and then backup
> You will create the redo logs at the end of recovery by sql> alter database open resetlogs;
> Important point is we need to to recover all the files that make database to a same point
> Every High-availability database will require hot backup and archivelogs.
RECOVERY
------------------
> Generally can be broken down into SEVEN distinct steps
step 1) file restore from tape/disk to location where actual database resides ( location as pointed by control file)
step 2) Startup nomount -> ( for creation of control file)
- reads parameter file
- allocates the memory structure of SGA
- starts the background Oracle processes
- open the alert and trace files
- Data files are NOT opened
step 3) Create control file
- Use a text version of control file to create a new binary version of control file
- this step is optional, create only if it is unavailable
step 4) Check all Datafiles
- see if all files to be recovered are present or not
- read only data file need not be recovered
step 5) Mount the Database
- to associate teh database with the instance started in step 2 above
- control file listed in parameter files gets opened and all database files mentioned in it are located
step 6) Recover the database
- By locating and applying the archive logs
step 7) Open the database
- SQL> startup open
RECOVERY FROM COLD BACKUP
-----------------------------------------------------
> SIMPLEST ONE
> DB which are in archive log mode can be recovered till that point.
> If database is not in archivelog mode , then only restore will be possible and no recovery. Just restore files and startup!!
RECOVERY FROM HOT BACKUP
---------------------------------------------------
> Always require a recovery
> Recovery can be of two types -> 1) COMPLETE Recovery 2) INCOMPLETE Recovery
> Complete recovery : DB restored and recovered using all available archive logs. "No data loss" is the target.
> Incomplete recovery : DB restored and recovered to a predetermined point or till some specific SCN number desired by applying some, an not all archive log files. EXAMPLES for incomplete recovery are :
SQL> recover database until cancel;
SQL> recover database until change 123457;
SQL> recover database until time '2010-03-25:14:33:00';
Write a Database Backup
---------------------------------------
Whenever you perform a backup, it is extremely useful to know the status of your database including the data files and parameters in effect.
select * from dba_tablespaces; -- Tablespace Information
select * from dba_data_files; -- Data file Information
select * from v$datafile; -- More data file information
select * from v$logfile; -- log file information
select * from v$log; -- more log file information
select * from v$controlfile; -- control file information
select * from v$parameter; -- database parameters in effect
select * from v$nls_parameters; -- language characters in effect
select * from v$log_history where first_time > sysdate - 3; -- Get the log history info for the past 3 days
SAVE the above information before starting the backup.
Example of a hot backup SQL script
---------------------------------------------------------
set echo on;
spool /u02/backup/ora10g/hotBackup1.lst;
alter system archive log current;
alter tablespace INDX begin backup;
! cp /u01/oradata/ora10g/indx01.dbf /u02/backup/ora10g
alter tablespace INDX end backup;
alter tablespace TABLESPACE_n begin backup;
! cp /u01/oradata/ora10g/tablespace_n01.dbf /u02/backup/ora10g
! cp /u01/oradata/ora10g/tablespace_n02.dbf /u02/backup/ora10g
alter tablespace TABLESPACE_n end backup;
... more tablespaces ...
alter tablespace SYSTEM begin backup;
! cp /u01/oradata/ora10g/system01.dbf /u02/backup/ora10g
alter tablespace SYSTEM end backup;
-- Backup the log file
alter system archive log current;
-- Create 3 copies of a binary controlfile backup.
alter database backup controlfile to /u02/backup/ora10g/CONTROL01.CTL' reuse;
alter database backup controlfile to /u02/backup/ora10g/CONTROL02.CTL' reuse;
alter database backup controlfile to /u02/backup/ora10g/CONTROL03.CTL' reuse;
0191-CRITICAL SKILL 5.4 Back Up Archived Redo Logs
-- Create a text version of a controlfile backup
alter database backup controlfile to trace;
spool off;
exit;
You should also add your parameter files, dump files, and alert logs to your backups.
Once you've added those, you'll have everything backed up except for your archive logs.
Back Up Archived Redo Logs
---------------------------------------------
> Managing archive logs can be tricky at times. You need them for database recovery , but at the same time they can fill up the space in the directory.
> Compress the archive files if you need to BUT they should be available
> If you are unable to keep archive logs online due to space issues, then you will need to write a script that regularly backs up the archive logs to tape and deletes them from disk.
Example of an archive backup script
---------------------------------------------------------
#Pseudo-shell-code for free archive log space
# Check used and free space: this is a very simple script
# df -k (on linux: location of fields varies by platform)
# Filesystem 1k-blocks Used Available Use% Mounted on
# /dev/hda3 3763404 102676 3469556 3% /
Log_arch_dest='/u01/oradata/db01/arch'
arch_dir_mountPoint='df -k ${log_arch_dest}|grep -v blocks|awk '{print $6}''
arch_dir_freeSpace='df -k ${log_arch_dest}|grep -v blocks|awk '{print $4}''
arch_dir_used='df -k ${LOG_ARCH_DEST}|grep -v blocks|awk '{print $3}''
if [${arch_dir_freeSpace} -le ${arch_dir_Used}]; then
echo ''Place archiving logic here"
fi
All of this is unnecessary when you use RMAN !!
Oracle Data Pump
---------------------------