RMAN Backup scripts ( Full, Level 0 and Level 1 Incremental )

Oracle Database, Recovery Manager (RMAN) backups are mainly of two types:

Full Backup and Incremental Backup (Level 0, Level 1).

Level 1 can be Differential or Cumulative.

RMAN Full Backup

A Full Backup copies all blocks of the database datafiles, regardless of whether they changed or not.

Backs up entire database

Includes used + unused blocks

Does not act as a base for incremental backups

Larger backup size

Syntax 

    Backup database; ## FRA Location

    Backup database plus archivelog;

    Backup database format ='/backup/rman/Bak_%d_%T_%U.bkp'

    Backup database format ='/backup/rman/Bak_%d_%T_%U.bkp' plus archivelog format ='/backup/rman/Arch_%d_%T_%U.bkp';

RMAN Incremental Backup

Incremental backups copy only changed blocks since a previous backup.

Two levels of backups

Level 0

Level 1

Level 0 Backup

A Level 0 backup is the base backup for incremental strategy.

Copies all used blocks

Acts as base for Level 1 backups

Similar to full backup but supports incremental chain

RMAN> BACKUP INCREMENTAL LEVEL 0 DATABASE;

RMAN> BACKUP INCREMENTAL LEVEL 0 DATABASE format ='/backup/rman/Bak_L0_%d_%T_%U.bkp';

Level 0 = foundation backup

Level 1 Backup

Level 1 backs up only blocks changed since the Level 0 backup or previous Level 1 backup.

Two types exist:

Differential

Cumulative

Level 1 Differential Backup

Backs up blocks changed since the last incremental backup (Level 0 or Level 1).

RMAN> BACKUP INCREMENTAL LEVEL 1 DATABASE;

RMAN> BACKUP INCREMENTAL LEVEL 1 DATABASE format ='/backup/rman/Bak_L0_%d_%T_%U.bkp';

Level 0 (Sunday)

     |---- Level 1 Differential (Mon)

     |---- Level 1 Differential (Tue)

     |---- Level 1 Differential (Wed)

     |---- Level 1 Differential (Thu)

     |---- Level 1 Differential (Fri)

     |---- Level 1 Differential (Sat)

Level 1 Cumulative Backup

Backs up all blocks changed since the last Level 0 backup.

RMAN> BACKUP INCREMENTAL LEVEL 1 CUMULATIVE DATABASE;

RMAN> BACKUP INCREMENTAL LEVEL 1 CUMULATIVE  DATABASE format ='/backup/rman/Bak_L0_%d_%T_%U.bkp';

    Level 0 (Sunday)

     |---- Level 1 Cum (Mon -Changes from since Sun)

     |---- Level 1 Cum (Tue -Changes from since Sun)

     |---- Level 1 Cum (Wed -Changes from since Sun)

     |---- Level 1 Cum (Thu -Changes from since Sun)

     |---- Level 1 Cum (Fri -Changes from since Sun)

     |---- Level 1 Cum (Sat -Changes from since Sun)

Backup Archive Log

RMAN> Backup archivelog all;

RMAN> Backup archivelog all format='/backup/rman/Arch_%d_%T_%U.bkp';

Crontab Schedule :

crontab -e

# Sunday Level 0 Backup at 2 AM

0 2 * * 0 /u01/scripts/rman_level0.sh >> /u01/scripts/logs/l0_backup.log 2>&1

# Monday–Saturday Level 1 Cumulative Backup at 2 AM

0 2 * * 1-6 /u01/scripts/rman_level1_cum.sh >> /u01/scripts/logs/l1_cum_backup.log 2>&1

#Sunday – Level 0 Backup Script

#!/bin/bash

export ORACLE_SID=PROD

export ORACLE_HOME=/u01/app/oracle/product/19c/dbhome_1

export PATH=$ORACLE_HOME/bin:$PATH

rman target / <<EOF

RUN {

ALLOCATE CHANNEL c1 DEVICE TYPE DISK;

BACKUP INCREMENTAL LEVEL 0 DATABASE TAG 'WEEKLY_SUN_L0';

BACKUP ARCHIVELOG ALL DELETE INPUT;

RELEASE CHANNEL c1;

}

EXIT;

EOF

# Weekdays – Level 1 Differential Script

#!/bin/bash

export ORACLE_SID=PROD

export ORACLE_HOME=/u01/app/oracle/product/19c/dbhome_1

export PATH=$ORACLE_HOME/bin:$PATH

rman target / <<EOF

RUN {

ALLOCATE CHANNEL c1 DEVICE TYPE DISK;

BACKUP INCREMENTAL LEVEL 1 DATABASE TAG 'DAILY_L1';

BACKUP ARCHIVELOG ALL DELETE INPUT;

RELEASE CHANNEL c1;

}

EXIT;

EOF

Sunday Level 0 RMAN Script

#!/bin/bash

export ORACLE_SID=PRODDB

export ORACLE_HOME=/u01/app/oracle/product/19c/dbhome_1

export PATH=$ORACLE_HOME/bin:$PATH

rman target / <<EOF

RUN {

ALLOCATE CHANNEL c1 DEVICE TYPE DISK;

BACKUP INCREMENTAL LEVEL 0 DATABASE

TAG 'WEEKLY_L0'

FORMAT '/backup/rman/L0_%d_%T_%U.bkp';

BACKUP ARCHIVELOG ALL DELETE INPUT;

RELEASE CHANNEL c1;

}

EXIT;

EOF

#Weekdays Level 1 Cumulative Script

# %d = Database, %T = Date Time and %U= 8 number 

#!/bin/bash

export ORACLE_SID=PROD

export ORACLE_HOME=/u01/app/oracle/product/19c/dbhome_1

export PATH=$ORACLE_HOME/bin:$PATH

rman target / <<EOF

RUN {

ALLOCATE CHANNEL c1 DEVICE TYPE DISK;

BACKUP INCREMENTAL LEVEL 1 CUMULATIVE DATABASE

TAG 'DAILY_L1_CUM'

FORMAT '/backup/rman/L1C_%d_%T_%U.bkp';

BACKUP ARCHIVELOG ALL DELETE INPUT;

RELEASE CHANNEL c1;

}

EXIT;

EOF

### Script 

export ORACLE_SID=PROD

export ORACLE_HOME=/u01/app/oracle/product/19c/dbhome_1

export PATH=$ORACLE_HOME/bin:$PATH

rman target / <<EOF

run

{

Allocate channel ch1 device type disk;

allocate channel ch2 device type disk;

crosscheck backup;

delete noprompt expired backup;

crosscheck archivelog all;

delete noprompt archivelog all completed before 'SYSDATE -7'; 

backup as backupset format='/u01/app/data/PROD/Backup_%T_%d_%U'

plus archivelog format ='/u01/app/data/PROD/Archive_%T_%d_%U';

backup current controlfile format='/u01/app/data/PROD/controlfile_%F';

release channel ch1;

release channel ch2;

}

quit;

EOF

RMAN Settings/Configuration Parameters

[oracle@ORA-TEST301 ~]$ rman target /

RMAN> show all;

using target database control file instead of recovery catalog

RMAN configuration parameters for database with db_unique_name TEST are:

CONFIGURE RETENTION POLICY TO REDUNDANCY 1;

       CONFIGURE RETENTION POLICY TO Redundancy 7; ## Number of backup files

       CONFIGURE RETENTION POLICY TO Recovery window 7 days; ## Number of days backup files

CONFIGURE BACKUP OPTIMIZATION OFF; # default

        CONFIGURE BACKUP OPTIMIZATION ON;

            Read only tablespace and data files not backup.

CONFIGURE DEFAULT DEVICE TYPE TO DISK; # default

    Defines where backups are stored Disk or Tape/SBT

CONFIGURE CONTROLFILE AUTOBACKUP ON;

    Automatically backs up the control file and SPFILE after backup operations.

    Full backup,Level0 , Level 1. Archive log backup and data file backup

CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO '/2c/data/oracle12c/rman_bkup/SDADS/%F';

    %F 12 character alph numaric 

CONFIGURE DEVICE TYPE DISK PARALLELISM 1 BACKUP TYPE TO BACKUPSET; # default

    How many backup streams run simultaneously.

    RMAN uses 4 channels

    Backup runs faster because multiple files are backed up in parallel.

    CONFIGURE DEVICE TYPE DISK PARALLELISM 4;

    Backup Types

        Backup of copy ==> An Image Copy is an exact byte-to-byte copy of a datafile.

        Backup as copy database;

        Backup sets ==> RMAN stores backups in compressed binary files.

        Backup as bakupset database;

CONFIGURE DATAFILE BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default

CONFIGURE ARCHIVELOG BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default

CONFIGURE CHANNEL DEVICE TYPE DISK FORMAT   '/2c/data/oracle12c/rman_bkup/TEST/bkup_%U';

    Backup file naming convention.

    %d database name

    %U unique backup identifier

    %T date

CONFIGURE MAXSETSIZE TO UNLIMITED; # default

    Only for backup sets

    If we keep MAXSETSIZE to 100GB if your database size is 300GB it will take 3 backup files

CONFIGURE ENCRYPTION FOR DATABASE OFF; # default

    Backup will be encryption 

CONFIGURE ENCRYPTION ALGORITHM 'AES128'; # default

   Default AES125 Backup algorithm, we need to enable TDE 

CONFIGURE COMPRESSION ALGORITHM 'BASIC' AS OF RELEASE 'DEFAULT' OPTIMIZE FOR LOAD TRUE ; # default

    Backup size will be reduced 

    Default is Basic but we have Low , Medium and High

CONFIGURE RMAN OUTPUT TO KEEP FOR 7 DAYS; # default

    How many days RMAN Backup information we have to keep on control file

    List backup;

CONFIGURE ARCHIVELOG DELETION POLICY TO NONE; # default

    NONE means anyone can delete Archive log files on OS level

    If we have Standby server, if we keep NONE Primary and DR will go out of sync 

    CONFIGURE ARCHIVELOG DELETION POLICY TO APPLIED ON STANDBY;

CONFIGURE SNAPSHOT CONTROLFILE NAME TO '/2c/data/oracle12c/rman_bkup/TEST/snapcf_TEST.f';

    During backup:

            Datafiles are being backed up

            Control file records are constantly updating

            Archivelogs are generated

 

SQL Server Always ON Readbale Secondary Options

 Always ON Readbale Secondary Options

NO

Secondary replica cannot accept any read connections. Only used for failover. Yes Secondary replica accepts all read connections (even if the connection string does not specify read-intent). READ_INTENT_ONLY Secondary replica accepts connections only if the client specifies ApplicationIntent=ReadOnly in the connection string. Server=AOAGListener; Database=DB; ApplicationIntent=ReadOnly;

How to validate the Backups in SQL server

 How to checks whether the SQL backup file is structurally valid and readable.

1) RESTORE VERIFYONLY

2) RESTORE HEADERONLY

3) RESTORE FILELISTONLY 

RESTORE VERIFYONLY

FROM DISK = 'D:\SQLData\Backup\DBA_FULL.bak';

RESTORE HEADERONLY

FROM DISK = 'D:\SQLData\Backup\DBA_FULL.bak';

RESTORE FILElistonly

FROM DISK = 'D:\SQLData\Backup\DBA_FULL.bak';

PostgreSQL Architecture

PostgreSQL Cluster / Instance is combination of Memory and Background process, cluster is nothing but collection / Group of databases managed by single instance of the PostgreSQL cluster 

Memory Components

Shared Memory 

Shared Buffer ==> This is the primary cache of PostgreSQL, where data blocks writes/reads from the disk. 

WAL Buffer ==> Buffer for Write ahead logging (WAL) data before it is written to disk.

CLOG Buffer ==> Store commit log information to manage transaction states

Temp Buffers ==> Used for storing temporary tables. Default 8MB

Work Memory ==> Used for sorting operations , Bitmap operation, hash join and merge join operations. Default 4MB 

Maintenance Work Memory ==> Used for vacuum and index operations. Default 64MB

Catalog Cache and Execution Memory ==> Memory used for caching system catalogs and                 executing SQL queries

Backend Process

Postmaster Process

    When we start PostgreSQL cluster ,the postmaster process is the first to initialize 

    Responsibilities of Postmaster

            Accepting incoming connection 

            Forks new backend process

            Starts background process

            Handles server restart and crash recovery 

BG Writer ==> Periodically Writes Dirty buffers/pages from shared buffer to the disk

WAL Writer ==> Writes contents of the WAL buffers to WAL files

Checkpoint ==> Writes dirty buffers to disk when a checkpoint occurs , this reduces the recovery time after a crash. 

Stats Collector ==> Collect DBMS usage statistics information such as session activity information(pg_stat_activity) and table usage statistics information (pg_stat_all_tables)

Gathers and maintains statistical data about the database, which the query planner uses to optimize query execution plans 

Archiver ==> When in archive log mode , copies the WAL files to the archive log location 

Sys Logger  ==> Logs error messages and a variety of information to log files

Auto vacuum Launcher ==> Fork the auto vacuum worker when vacuum is required(Automatically reclaims storage occupied by dead tuples( Rows that have updated or deleted), to prevent table bloat and maintains query performance.

Logical Replication Launcher ==> Manages the logical replication workers that handle replication of changes to other databases.

WAL Sender  ==> Sending WAL records to standby servers, runs on the primary server

WAL Receiver ==> Receive WAL records and apply on standby server , runs on the standby server

RMAN Expired and Obsolete Backups

 RMAN Expired and Obsolete Backups

Expired Backups:

Backup set or backup pieces are deleted on OS Level, Database control file have backup details on disk but OS level files are not available.

Action:

RMAN> crosscheck backup;

RMAN> delete expired backup;

Obsolete Backups:

The general meaning of OBSOLETE is no longer used or required for recovery.

RMAN considers backups as OBSOLETE when they are no longer required for database recovery.This is done by one of the RMAN CONFIGURATION parameters.

Action:

RMAN> report obsolete;

RMAN> delete obsolete;

RMAN Backups Methodology Full and Incremental

RMAN provides multiple backup types, but the most commonly used in production environments are Full backups and Incremental backups. Understanding their differences helps DBAs design efficient backup strategies that optimize storage, speed, and recovery time.

1. Full Backup

A Full Backup (or Level 0 backup) is a complete backup of the entire database.

Key Characteristics

Backs up all data blocks, regardless of whether they changed.

Forms the baseline for incremental backup strategies.

Larger in size than incremental backups.

Takes more time and requires more storage.

Recovery is straightforward—restore full backup + apply archived logs.

2. Incremental Backups

Incremental backups only capture blocks that have changed since a previous L0 backup.

RMAN supports two types:

Differential Incremental Backup (Level 1 Differential)

Cumulative Incremental Backup (Level 1 Cumulative)

Both rely on a Level L0 full backup as a baseline.

2.1. Differential Incremental Backup

A Differential Level 1 backup captures all blocks changed since the last backup (Level 0 or Level 1).

Key Characteristics

Smaller and faster than full backups.

Captures daily changes.

During recovery, RMAN may use multiple Level 1 backups.

When it comes to recovery we need L0 Backup and all L1 backups including Archive logs need to be apply.

2.2. Cumulative Incremental Backup

A Cumulative Level 1 backup captures all blocks changed since the last Level 0.

Key Characteristics

Slightly larger than differential backups.

Faster recovery (fewer incremental files to apply).

Preferred for large, mission-critical systems.

When it comes to recovery we need L0 and recent L1 Cumulative backup including Archive logs need to be apply.