User Managed Backup Terminology

(Operating system command are used to make backups when database is closed or open in this terminology)

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.)

Important:

1. If Database is in No Archive log mode, We can not perform user managed hot backup / inconsistent backup

2. If Database is in No Archive log mode and we don’t want to change mode then we can only perform clod backup/consistent backup

3. If Database is in No Archive log mode, we can perform only Restore and not possible to recover database to the point of failure. Means, restore to the point of the last backup

4. If Database is in Archive log mode , then we can recover to the point of failure

How to know Database file location information

For Data File > select * from V$database
For Control File > select * from v$controlfile
For Log File > select * from v$logfile

How to Know all data files and there respective table space

SQL>
SELECT T.NAME TABLESPACE,F.NAME DATAFILE
FROM V$TABLESPACE T, V$DATAFILE F
WHERE T.TS# = F.TS#
ORADER BY T.NAME;

How to Make 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.

How to make 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 BEGIN BACKUP;

(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 END BACKUP;

· Repeat these steps for all tablespaces.

· Archive the unarchive redo logs;

SQL> ALTER SYSTEM ARCHIVE LOG CURRENT;

· 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;

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

How to check 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.)

How to verify backup

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)