Backup/Recovery

1. Which types of backups you can take in Oracle?
Backup Types:
1. Physical backup (Physical files backup-Datafile, Archived Redo log file, Control file, parameter file and password file- User Managed Backup, RMAN backup)
2. Logical backup(Tables, schema, tablespace and database backup-taking object backup and transfer to same or other database
Note: In oracle when we talk about the backup/recovery, it is mostly dedicated to physical backup not logical.
So, here we have types of backup:
1. Online/Hot/Inconsistent backup
2. Offline/Cold/Consistent Backup
3. Whole database backup
4. Incremental backup
5. Differential backup
6. Partial backup
2. A database is running in NOARCHIVELOG mode then which type of backups you can take?
Offline/Cold/Consistent Backup
3. Can you take partial backups if the Database is running in NOARCHIVELOG mode?
No, Partial backup cannot take while database is not in archive log mode.
Partial backup will not be synchronized with the rest of the database. It is a copy of just part of the database, at a particular moment in time. If it is ever necessary to restore a file from partial backup , it will have to resynchronized with the rest of the database with the help of archivelog. Means applying the changes from the archived and online redo log files to bring it up to date.
So, there is no concept of partial backup, if our database is not running in archive log mode.
4. Can you take Online Backups if the database is running in NOARCHIVELOG mode?
No, we can’t take online backup while database is running in no archive log mode.A datafile that is backed up online will not be synchronized with any particular SCN, nor it will be synchronized with other data fileor the control files. Archive log applying is mandatory to resynchrinize the backed up datafile with SCN and other datafiles or control file.
5. How do you bring the database in ARCHIVELOG mode from NOARCHIVELOG mode?
Note: To put the database from no archive to archive, database must be in mount mode( No mount or open mode will not allow to put the database in archive log mode). Similary to put the database in no archivelog mode from archive, the same rule will be applicable (No mount or open mode will not allow to put the database in archive log mode).
Archive to No Archive log mode
Sql> select log_mode from v$database;
LOG_MODE
------------
ARCHIVELOG
Sql> shut down immediate;
Sql> startup mount;
sql> alter database noarchivelog;
sql> select log_mode from v$database;
LOG_MODE
------------
NOARCHIVELOGNo Archive log mode to archive log mode
Sql> select log_mode from v$database;
LOG_MODE
------------
NOARCHIVELOG
SQL> archive log list;
Database log mode No Archive Mode
Automatic archival Disabled
Archive destination USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence 14
Current log sequence 17
SQL> shutdown immediate;
ORA-01109: database not open


Database dismounted.
ORACLE instance shut down.
SQL> startup mount;
ORACLE instance started.

Total System Global Area 369098752 bytes
Fixed Size 1249056 bytes
Variable Size 201326816 bytes
Database Buffers 163577856 bytes
Redo Buffers 2945024 bytes
Database mounted.
SQL> select log_mode from v$database;

LOG_MODE
------------
NOARCHIVELOG
SQL> alter database archivelog;
Database altered.
SQL> select log_mode from v$database;
LOG_MODE
------------
ARCHIVELOG
SQL> select log_mode from v$database;

LOG_MODE
------------
ARCHIVELOG

SQL> archive log list;
Database log mode Archive Mode
Automatic archival Enabled
Archive destination USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence 14
Next log sequence to archive 17
Current log sequence 17
SQL>
6. You cannot shutdown the database for even some minutes, then in which mode you should run the database?
Database must be running in Archive log mode.
Differences concerning backups
No archive log
Archive log
Must backup entire database.
Can backup parts of database (datafiles tablespaces)
DB must be shut down.
hot backups possible
Only entire DB can be restored
Tablespaces can be restored
In case of a failure, all changes sinces the last backup will be lost
All commited transactions will be restorable
7. Where should you place Archive logfiles, in the same disk where DB is or another disk?For the database performance reason and disk failure , we should store the archive log file at another disk .
8. Can you take online backup of a Control file if yes, how?
There are the way by that we can take the online backup of control file
1. User managed backup technique:
Sql> alter database backup controlfile to trace;
The control file creation sql script will be created in user_dump_dest (udump) directory.
Ther trace file will be having sql script to create the control file
Sql> alter database backup control file to ‘/oracle/app/ctrl.bak’ resue
To take the control file backup in binary format
2. RMAN is another technique by that we can take the online backup of control file, but database must be in mount mode, else RAMN will not be able to connect with database and do the required operation
Rman> backup current controlfile;
Rman> backup current controlfile to ‘/backup/ctrlfile.copy’;
Rman> configure controlfile autobackup on;
9. What is a Logical Backup?
Logical backup is the way to take the backup of data using sql commands in binary format file and import the same to the other database.
It’s a logical backup why because we used to take the objects(data) backup from one database and to restore to other database.
Traditional import/export and Datapump is the technique to perform the logical backup.Example of logical backup are:
Table backup, tablespace backup, schema backup, full database backup
10. Should you take the backup of Logfiles if the database is running in ARCHIVELOG mode?
No, there is no need to take the backup of online redo log file while our database is running in no archivelog mode, why because whatever information is containg by redo log jhas already been moved to archived redo log file before switching to next redo. So there is no benefit to take up the backup of online redo log files.
11. Why do you take tablespaces in Backup mode?
The goal of ALTER TABLESPACE BEGIN BACKUP and END BACKUP is to set special actions in the current database files in order to make their copy usable , without affecting the current operations.
Nothing needs to be changed in the current datafile , but, as the copying is done by the external tool( Operating system utility), the only way to have something set in the copy is to do it in the current datafiles before the copy , and revert it back at the end.
Sql> alter tablespace begin backup;
While putting the tablespace in backup mode,
-the hot backup falg in the datafile header is set , so that the copy is identified to be a hot backup copy. This is to manage the backup consistency issue when the copy will be used for recovery.
- Checkpoint is done for the tablespace, so that in case of recovery , no redo generated before that point will be applied. Begin backup command completes only when checkpoint is done.