User Managed Backups in Oracle

Definition :Backup is real and consistent copy of data from database that could be used to reconstruct the data after and incident.

There are two types of backup

1. Physical Backup

2. Logical Backup

Physical Backup

Copy of all the physical datafiles that are required to perform the restore and recovery of database,

This type of backup includes copy of below files

• Datafiles
• Control files
• Parameter files
• Archived log files

Different Options to take physical backup are 

• User managed techniques
• RMAN (Recovery Manager)

Logical Backup

Oracle uses oracle datapump to allow us to generate a logical backup that can be used to migrate data even do partial or full recovery of database.Utilities available are 

• exp/expdp
• imp/impdp

Difference between restore and recovery

Restore: Act that involves the restoration pf all files that will be required to recover your database to a consistent state. for example copying all backup files from backup location such as TAPE or DISK

Recover: It is process to apply all transaction recorder in archivelog, rolling forward your database to point in time. 

User Managed Backups

COLD BACKUP

This is only possible way for DBA to make consistent backup of database independent of mode of database (archivelog or noarchivelog). Below are steps explained

1. If database is running, bring it down completely in a consistent mode (using shutdown ( NORMAL/ IMMEDIATE/ TRANSACTIONAL only). This will make sure all database files header is updated to same SCN
2. Backup of all datafiles ,control files, parameter files using OS copy command
3. Start the database
4. Archive all unarchived redo logs using below command and copy those to backup location

              alter system archive log current

OFFLINE BACKUP

Assume you want to take offline backup of one or more tablespace, offline backup method will help you to achieve the same, note that SYSTEM tablespace and UNDO tablespace with active undo cannot be taken offline.Steps are mentioned below

assume tablespace is TECHON_TBS

1. Identify all datafiles associated with tablespace using dba_data_files view

select tablespace_name,file_name from dba-data_files from dba_data_files where tablespace_name='TECHON_TBS';

2. Take tablepspace offline using normal priority (do not use temporary and immediate, it will require recovery at the time of bringing tablespace online)

alter tablespace TECHON_TBS offline normal;

3. Backup all datafiles related to tablespace using OS copy command

4. Bring tablespace online

alter tablespace TECHON_TBS online;

5.archive all unarchived redo logs and copy archived log to backup location

alter system archive log current;

HOT BACKUPS

This has been introduced back in version of 6 of oracle, but initially it was bound to  tablespace only , from version 10 they included whole database as well. This allows us to take hot backup of database or tablespace without need to shutdown the database.

When taking hot backup, oracle stops recording checkpoints to all the associated datafiles.

Below are two things happens internally while database is put in BEGIN BACKUP mode

• A hot backup flag in datafile header is set
• A checkpoint occur, flashing of all dirty blocks from memory to disk, synchronizing all the datafile headers to same SCN & freezing the headers for consistency, protection and recoverability.

Hot backups can be made to the whole database, tablespace or even at container level, this requires recovery process after backup is restored so always take backup of all required archived log files.

Hot Backup of Whole Database

For taking this type of backup, we need to put our database in backup mode using 'alter database begin backup;'

This is most common type of user managed backup being used by DBA across the world. 

Below are the steps

1. Place the database in backup mode

alter database begin backup;

2. Backup all datafiles and parameter files using OS copy command

3. Pull database out of backup mode

alter database end backup;

4. Archive all unarchived logs and copy required as backup

alter system archivelog current;

5. create copy of control file as trace

alter database backup controlfile to '/control_file.trc;

Hot Backup of Tablespaces
Now we will learn taking hot backup of one or more tablespaces i.e. backing up your tablespace while database is running. while we put tablespace in backup mode, the database copies all the changed blocks into redo stream to make all the data consistent until. Below are step explained.

1. Identify all datafiles associated with tablespace using dba_data_files view

select tablespace_name,file_name from dba-data_files from dba_data_files where tablespace_name='TECHON_TBS';

2. place the tablespace in backup mode using begin backup command

alter tablespace TECHON_TBS begin backup;

3. Backup all datafiles related to tablespace using OS copy command

4. pull tablespace out of backup mode

alter tablespace TECHON_TBS end backup;

5.archive all unarchived redo logs and copy archived log to backup location

alter system archive log current;

Hot Backup of Container Database

As introduced in Oracle 12c, you can perform user managed backup of whole container database or the only root or and individual PDB.

Whole Container Database:

Below are the steps mentioned 

1. login to database using user having sysdba or sysbackup privileges.

sqlplus /nolog

connect system@container1

2.Place your database in backup mode

alter database begin backup;

3. Identify all the databases related to container database using below command

select file_name from dba_data_files;

4. Copy all the database using OS copy command

5. Pull database out of backup mode

alter database end backup;

6. Archive all unarchived logs and copy required as backup

alter system archivelog current;

7. create copy of control file as trace

alter database backup controlfile to '/control_file.trc;

ROOT only or individual PDB

Below are the simple steps to complete hot backup of root 

container or individual pluggable database

1. connect to root or individual pluggable database with user having sysdba or sysbackup privileges.

sqlplus /nolog

connect system

alter session set container= tech_pdb;

2. Identify all the datafiles part of PDB using dba_data_files view

select file_name from dba_data_files;

3. Place pluggable database in backup mode

alter pluggable database tech_pdb begin backup;

4. Copy all the datafiles using OS copy command.

5. Pull PDB out of begin backup mode

alter pluggable database tech_pbd end backup;

6. Archive all unarchived logs and copy required as backup

alter system archivelog current;

7. create copy of control file as trace

alter database backup controlfile to '/control_file.trc;

Control File Backup

There are two ways to take backup of control 

1. Binary backup
2. Text file Backup

Having valid backup of control file of a database is crucial for successful recovery of database.

Binary backup

One can take binary copy of control file via SQL statement. This copy contains additional information such as the archived redo log history , offline range for read-only and offline tablespaces, tempfile entries and RMAN backup sets and copies of data

command to take binary backup

alter database backup controlfile to '';

Text file backup

This type of backup contains a create controlfile statement based on current control file in use. This type of backup can be taken while database is in mount or open mode. This is generated as trace file and can be easily modified.

Below are few statements used to created text based backup of control file

1. alter database backup controlfile to trace;

2. alter database backup controlfile to trace resetlogs;

3. alter database backup controlfile to trace noresetlogs;

4. alter database backup controlfile to trace as '';

5. alter database backup controlfile to trace as '' reuse;

6. alter database backup controlfile to trace as '' resetlogs;

7. alter database backup controlfile to trace as '' noresetlogs;

Please comment and share if you feel this is helpful.