Backup & Recovery plans for VLDBs

Backup and recovery is a crucial and important job for a DBA to protect business data.
Especially if database size is too big, then backup and recovery plan must be tested to define RPO and RTO.

Recovery Time Objective (RTO) is the time duration in which you want to be able to recover your data.

A Recovery Point Objective, or RPO, is the maximum amount of data that can be lost before causing detrimental harm to the organization. RPO indicates the data loss tolerance of a business process or an organization in general.

An efficient backup and recovery strategy for very large databases to reduce the overall resources necessary to support backup and recovery by using some special characteristics that differentiate data warehouses from OLTP systems.

A DBA should initially approach the task of data warehouse backup and recovery by applying the same techniques that are used in OLTP systems: the DBA must decide what information to protect and quickly recover when media recovery is required, prioritizing data according to its importance and the degree to which it changes. However, the issue that commonly arises for data warehouses is that an approach that is efficient and cost-effective for a 100 GB OLTP system may not be viable for a 10 TB data warehouse. The backup and recovery may take 100 times longer or require 100 times more storage.

Oracle Backup and Recovery:

In general, backup and recovery refers to the various strategies and procedures involved in protecting your database against data loss and reconstructing the database after any kind of data loss.

A backup is a representative copy of data. This copy can include important parts of a database such as the control file, archived redo logs, and data files. A backup protects data from application error and acts as a safeguard against unexpected data loss, by providing a way to restore original data.

1) Architecture backup environment:
Use VTL ( virtual Tape libary) rather than actual tape drive. VTL are fast and random accessible as compare to actual tape library.

2) Evaluate Backup tools:
- Evaluate Full, incremental and archive-log backups
- Evaluate file level backups - helpful for logical backups
- Verify support related to multiplex / Parallel features
- Verify PDB/ CDB backups from Oracle 12c on wards

3) Flashback technologies
- Evaluate to take whole database with snapshot basis
- Evaluate CDP ( continuous data protection) capabilities

4) Evaluate Compression features
- RMAN Backup level
- file level

6) Backup startergies
- Weekly level 0 (full) and daily level 1 at night
- Periodically archive log backups

Case-study: VLDB deployments

1) Enable block change tracking:
SQL> ALTER DATABASE ENABLE BLOCK CHANGE TRACKING USING FILE '<path_to_block_change_file_name>';
e.g.,
SQL> ALTER DATABASE ENABLE BLOCK CHANGE TRACKING USING FILE '/u02/oradata/PROD/bct_prod.dbf';
-- incase of OMF/ RAC
SQL> alter database enable block change tracking;
2) Configure the preferred RMAN Recovery Window:
e.g.,
RMAN> CONFIGURE RETENTION POLICY TO RECOVERY WINDOW OF 3 DAYS;
3) enable auto-backup of control-file in RMAN:
e.g.,
RMAN> CONFIGURE CONTROLFILE AUTOBACKUP ON;
$) Enable/ configure parallelism:
RMAN> CONFIGURE DEVICE TYPE DISK PARALLELISM 4  BACKUP TYPE TO BACKUPSET; # use parallelism as per core availability
   Note: In case of tape/ 3rd party console, enable parallelism at console level
4) Performing the Incremental / Full backups:

-- To Tape
run {
allocate channel c1 type 'SBT_TAPE';
allocate channel c2 type 'SBT_TAPE';
sql 'alter system archive log current';
BACKUP CURRENT CONTROLFILE format '%d_%T_%s_%p_full_ctl_%U.bkp' tag 'Inc_CF';
backup incremental level 0 database format '%d_%T_%s_%p_full_%U.bkp' tag 'Inc_BKP';
sql 'alter system archive log current';
backup archivelog all format '%d_%T_%s_%p_full_al_%U.bkp' tag 'Arc_BKP;
DELETE FORCE NOPROMPT OBSOLETE;
release channel c1;
release channel c2;
} 
e.g.,

-- List backup

RMAN> list backup summary;

List of Backups
===============
Key     TY LV S Device Type Completion Time #Pieces #Copies Compressed Tag
------- -- -- - ----------- --------------- ------- ------- ---------- ---
22269123 B  F  A SBT_TAPE    17-NOV-19       1       1       NO         INC_CF
22269184 B  1  A SBT_TAPE    17-NOV-19       1       1       NO         INC_BKP
22269185 B  1  A SBT_TAPE    17-NOV-19       1       1       NO         INC_BKP
22269255 B  A  A SBT_TAPE    17-NOV-19       1       1       NO         ARC_BKP
22269256 B  A  A SBT_TAPE    17-NOV-19       1       1       NO         ARC_BKP
22269257 B  A  A SBT_TAPE    17-NOV-19       1       1       NO         ARC_BKP
22269258 B  A  A SBT_TAPE    17-NOV-19       1       1       NO         ARC_BKP
22269511 B  F  A SBT_TAPE    17-NOV-19       1       1       NO         TAG20191117T103335

RMAN>

-- Disk backup
run
{
allocate channel c1 type disk format '/u03/backup/%d_%T_%s_%p_%U.bkp' maxpiecesize 100G;
allocate channel c2 type disk format '/u03/backup/%d_%T_%s_%p_%U.bkp' maxpiecesize 100G;
allocate channel c3 type disk format '/u03/backup/%d_%T_%s_%p_%U.bkp' maxpiecesize 100G;
allocate channel c4 type disk format '/u03/backup/%d_%T_%s_%p_%U.bkp' maxpiecesize 100G;
sql 'alter system archive log current';
BACKUP CURRENT CONTROLFILE format '%d_%T_%s_%p_full_ctl_%U.bkp' tag 'Inc_CF';
backup  as compressed backupset incremental level 0  check logical database plus archivelog;
release channel c1 ;
release channel c2 ;
release channel c3 ;
release channel c4 ;
}

5) Perform Restore and recover to evaluate RTO:

6) ASM Best Practices for VLDBs
- Large allocation units
- Between 1MB and 64MB (4,8,16,32 and 64)
- Use 64MB for a very large data warehousing system, which will reduce ASM
- memory requirements, and also improve ASM instance startup time
Disk groups with an External Redundancy

I hope this document may help to define backups in your environment.