25. ORACLE FULL AND INCREMENTAL BACKUPS. - sayanbhattacharya1234/Oracle_DBA GitHub Wiki

1. Learning objectives

Explain full backup, level 0 backup , differential level 1 backup , cumulative level 1 backup

Full backup : A full backup is nothing but it takes backup of all physical files block by block , but automatically an incremental level 0 or 1 backup unless maintioned(tag) .

Level 0 backup : Level 0 backup is same as full backup, incremental baseline containing all used blocks.

Differential level 1 backup : incremental differential backup always search for last level 1 backup , if level 1 backup is not there , it will search for level 0 backup. if level 0 backup is also not there , it start taking backup as level 0 backup, Takes backup from last level 0 backup to particular time, only changed blocks are taken as a differential incremental level 1 backup .

Cumulative Level 1 : This Incremental cumulative level 1 backup always search for last level 0 backup, only changed blocks are taken as backup since most recent level 0.

difference between backup sets vs image copies.

Run backup database and archivelog using RMAN.

2. Backup Flowchart

3. Restore and Recovery

Flow Explanation:

RMAN restores the baseline full or level 0 backup.

RMAN applies the necessary level 1 backups to move datafiles towards efficiently.

Archived redo logs and online redo are applied to reach the required recovery point.

The database is opened normally after complete recovery or with RESETLOGS after incomplete recovery.

4. Core concepts of backup

5. Full vs Level 0 vs level 1 backup

6. Backup sets vs Image copies

7. Importent Views

  1. V$RMAN_BACKUP_JOB_HISTORY .
  2. V$RMAN_STATUS.
  3. V$BACKUP_SET 4.V$BACKUP_PIECE
  4. V$BACKUP_DATAFILE
  5. V$BACKUP_REDOLOG
  6. V$SESSION_LONGOPS
  7. LIST BACKUP
  8. RESTORE VALIDATE

8. Pre backup checks

Database state

Datafile status

Archive destination and fra

Rman config.

Destination capacity

9. Rman backup scenario

Step 1 and 2 : connect and review

Step 3 run { allocate channel c1 device type disk format '/u01/backup/full_%d_%T_%U.bkp' ; backup as compressed backupset database tag='FULL_DB_LAB'; backup current controlfile tag='FULL_DB_LAB_CONTROLFILE'; backup spfile tag='FULL_DB_LAB_SPFILE'; release channel c1; }

Step 4: list result

**Step 5 : sql verification ** select session_key,input_type,status,start_time,end_time,input_bytes_display,output_bytes_display,compression_ratio from v$rman_backup_job_details order by start_time DESC FETCH FIRST 10 ROWS ONLY

10. Incremental level 0 :

backup incremental level 0 as compressed backupset database tag='WEEKLY_LV0';

SQL VERIFICATION select bs.recid,bs.set_stamp,bs.incremental_level,bs.backup_type,bs.start_time,bs.completion_time,bp.handle,bp.bytes/1024/1024 as piece_mb from v$backup_set bs join v$backup_piece bp on bp.set_stamp=bs.set_stamp and bp.set_count=bs.set_count where bs.incremental_level=0 order by bs.completion_time DESC;

11. Incremental Differential Level 1

backup incremental level 1 as compressed backupset database tag='DAILY_LV1_DIFF';

How it works:

RMAN backs up the blocks changed since the most recent level 0 or level 1 backup. Every day's backup is usually smaller but recovery may require multiple level 1 backups.

LIST RESULT

SQL VERIFICATION

select set_stamp,set_count,incremental_level,start_time,completion_time,blocks,datafile_included from v$backup_set where incremental_level=1 order by completion_time DESC;

12. Incremental Cumulative Level 1

backup incremental level 1 cumulative as compressed backupset database tag='DAILY_LV1_CUM';

How its works

RMAN backs up the blocks changed since the most recent level 0 backup. This backup is larger than differential level 1 but recovery generally larger but recovery needs only the last level 0 backup and the most latest level 1 backup.

LIST RESULT

DIFFERENCE between Differential and Cumulative backups

13. Image copy

Create a image copy backup as copy database tag='DB_IMG_CPY';

List image copies: list copy of database;

Incrementally updated copy pattern:

RUN { 2> RECOVER COPY OF DATABASE WITH TAG 'INCR_UPDATE' UNTIL TIME 'SYSDATE-1'; 3> BACKUP INCREMENTAL LEVEL 1 FOR RECOVER OF COPY WITH TAG 'INCR_UPDATE' DATABASE; 4> }

14. List and verify backup results:

Backup summary:

list backup summary;

Database backups;

list backup of database;

Incremental backups:

list backup of database completed after 'SYSDATE-7';

Archived log backup:

list backup of archivelog all;

Backup by tag:

List backup tag 'WEEKLY_LV0';

List backup tag 'DAILY_LV1_DIFF';

List backup tag 'DAILY_LV1_CUM' ;

Backup piece details:

Select handle, status, device_type, bytes/1024/1024 as size_mb, start_time, completion_time, compressed, tag from v$backup_piece order by completion_time desc fetch first 50 rows only;

Datafile backup SCNs:

Select file#, incremental_level, checkpoint_change#, checkpoint_time, completion_time, blocks, datafile_blocks from v$backup_datafile order by completion_time desc, file#;

15. Monitor backup progress:

RMAN long operations:

select sid,serial#,opname,target,sofar,totalwork,units,ROUND(sofar*100/NULLIF(totalwork,0),2) as pct_done,elapsed_seconds,time_remaining from v$session_longops where opname like 'RMAN%' and totalwork > 0 and sofar <> totalwork order by start_time;

Backup Job throughput:

Select session_key, input_type, status, start_time, end_time, elapsed_seconds, input_bytes_display, output_bytes_display, input_bytes_per_sec_display, output_bytes_per_sec_display, compression_ratio from v$rman_backup_job_details order by start_time desc fetch first 20 rows only;

Current RMAN status;

Select session_recid, operation, status, start_time, end_time, object_type, output_bytes from v$rman_status where status like 'RUNNING%' order by start_time;

16. Validate Restore readiness:

Restore preview:

Restore database preview summary;

restore validate:

restore database validate;

Validate logical blocks:

Backup validate check logical database;

Validate control file and spfile:

Restore controlfile validate: , Restore spfile validate

Check corruption:

Select file#, block#, blocks, corruption_change#, corruption_type from v$database_block_corruption order by file#, block#;

Check need for backup:

Report need backup recovery window of 7 days;

17. Example weekly backup strategy:

The reason for this weekly arrangment: Sunday is level 0 (level 0 is the same for both differential and cumulative) which is full backup .

Monday we can take level 1 differential backup,

Tuesday we can take another level 1 differtial backup (as if Mondays backup got deleted there will no data loss as that level 1 backup will record all changes happened after Sunday's level 0.

Wednesday we will take a level 1 cumulative, as in Wednesday's case if Mondays data got deleted, but Tuesday's remain intact then, Monday's changes will not be backed up again as differential level 1 backup only record the changes takes place after most recent level 0 and level 1 backup. This will break the chain. So we take cumulative level 1 backup here which always backups all the changes happened after last level 0, i,e after sunday.

Thursday we take another level 1 differential as we already have the cumulative level 1 backup in Wednesday.

Friday we also take a differential level 1 backup again as there will no risk of breaking the chain is Thursday's backup got deleted.

Saturday we will take another level 1 cumulative backup for the same reason as Wednesday.

18. Troubleshooting scenarios:

Level 1 runs without level 0:

RMAN may create a level 0 equivalant baseline bahavior depending on circumstances, but the strategy should explicitly schedule a level 0.

list backup of database;

Backup destination full:

host df -h /u05/rman/

host df -i /u05/rman/

report obsolete;

crosscheck backup;

list expired backup;

Backup piece missing:

crosscheck backup;

list expired backup;

Restore mount or file access before deleting metadata if the backup is still required.

Incremental backup too large: (?)

Possible causes:

  • High changed block workload

  • Block changed tracking dissabled or unavailable

  • Large batch operations

  • Level 0 too old

  • Nologging opeartions requiring additional protection.

Change block change tracking:

select status, filename, bytes from v$block_change_tracking;

Enable block change tracking:

alter database enable block change tracking using file '/u01/app/oracle/oradata/TATA/control01.ctl';

Disable block change tracking:

alter database disable block change tracking;

Restore validate fails:

list backup summary; crosscheck backup; restore database preview; restore database validate;

19. Production safe backup checklist:

  1. Confirm database, host, role, log_mode, FRA and destination.

  2. Review show all and retention policy

  3. Confirm control file autobackup and SPFILE protection.

  4. Validate free space and mount availability.

  5. Check recent backup failures and expired peices.

  6. Confirm data gaurd archived log deletion requirements.

  7. Use tags and timestamped logs.

Pre backup commands:

show all; crosscheck backup; crosscheck archivelog all; list backup summary; report obsolete;

Post backup commands:

list backup summary; list backup of database; list backup of archivelog all; restore database preview summary; restore database validate;

SQL post backup check:

Select input_type, status, start_time, end_time, elapsed_seconds, input_bytes_display, output_bytes_display, compression_ratio from v$rman_backup_job_details order by start_time desc fetch first 10 rows only;