Whole database backup refer to a backup of all data file, control file and log file of the database. whole database backup can be perform when database open or closed.
The backup takes when database is closed called consistent backup. (Because database file header are consistent with the control file and when restore completely the database can be opened without any recovery.)
The backup takes when database is opened and operational called inconsistent backup. (Because database file header are not consistent with the control file.)
Physical Backup Method
Database Operation Mode Recovery Senerio
Archive log mode recover to the point of failure
No Archive log mode recover to the point of the last backup
Querying View to obtain Database file Information
V$database( use for obtaining data file information)
v$controlfile( user for obtaining control file information)
v$logfile ( user for obtaining log file information)
Use the v$tablespace and v$datafile data dictonery view to obtain a list of all datafiles and there respective tablespace.
SQL> SELECT T.NAME TABLESPACE,F.NAME DATAFILE
FROM V$TABLESPACE T, V$DATAFILE F
WHERE T.TS# = F.TS#
ORADER BY T.NAME;
Making a consistent whole Database Backup
* Shutdown the database.
* Backup all data file, control file and log file by using an operating system command. we can also include password file and parameter file.
* Restart the oracle database/Instance.
Making a inconsistent whole database backup
Requirement for inconstant database backup:
* The database is set to ARCHIVELOG mode.
* You ensure that the online redo logs are archived, either by enabling the Oracle automatic archiving (ARCn) process.
Making a Backup of an Online teblespace or Data file
* Set the datafile or tablespace is backup mode by issuing following command:
SQL> ALTER TABLESPACE
(Note:This prevent the sequence number in the datafile header from changing.)
* Use an operating system backup utility to copy all database in the tablespace to backup storage.
Copy c:\datafile_path e:\datafilepath
* After the datafile of the tablespace have been backed up, set them into mode by issuing the following command:
SQL> ALTER TABLESPACE
* Archive the unarchive redo logs;
SQL> ALTER SYSTEM ARCHIVE LOG CURRENT;
Repeat these steps for all tablespaces.
Mechanism of Open database backup
When a datafile is placed in backup mode, more redo log entries may be generated because the log writer writes block image of changes block of the datafile in backup mode to the redo log instead of just the row information
Backup Status Information( When performing open database)
select * from v$backup; (view to determine which file are in backup mode, when alter tablespace begin backup command is issued the status change to ACTIVE.)
Manual Control File Backups
*
Creating a binary image:
ALTER DATABASE BACKUP CONTROLFILE TO 'control.bak';
*
Creating a taxt trace file:
ALTER DATABASE BACKUP CONTROLFILE TO TRACE;
Backing Up the Initilization Parameter File
CREATE PFILE FROM SPFILE;( For Default location)
CREATE PFILE ='C:\BACKUP\INIT.ORA' FRoM SPFILE;
Backup Verification (Command line Interface)
Use to ensure that a backup database or datafile is valid before a restore.
>dbv file='path of file location' start=1 logfile='enter path for log file generation'
Backup Issue with Logging and nologging Option
Tablespace, table, index may be use to set to NOLOGGING mode for Faster load of data when using direct load operation like SQL LOADER.( Because the redo logs do not contain the values that were inserted when the table was in NOLOGGING mode.
