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.

Understanding RMAN ( Recovery Manager) and Backups

 RMAN Key Points:

    - RMAN (Recovery Manager)

    - Oracle Database backup and recovery automation

    - It Introduced in Oracle 8i version

    - It performs block level backup

    - Parallelism

    - Detecting corruption in datafiles

    - Validating the backup

    - Incremental Backup

    - Recovery Catalog etc

    - Multi Destination Backups

    - Archive log mode is must to use RMAN

RMAN is faster because it takes block level backups.

RMAN is faster because we can initiate parallel processes.

RMAN will data block corruptions and repair it for you.

RMAN stores backup metadata information in the database control file, you can also store the information into deferent database on different server which is called as RECOVERY catalog

RMAN Incremental Recovery

              - Level 0 - Full database backup

              - Level 1 - Backup of only changed blocks taken by referring data block header for updated SCN

              -  

              - As per recovery, we must only restore the database from level 0 and recovery the Database using level 1. When you try to take level 1 backup, RMAN checks if already you have level 0 backup or not. If not then it will take level 0 backup automatically.

Recovery is of two parts:

              - Restore and Recover - We cannot perform recovery without restore. Hence Level 0 is used for restore and level 1 is used for recovery.

RMAN configuration Items

              - show all;

RMAN Utility:

RMAN utility comes with Oracle Binaries

No special installation or license required for using RMAN

At command prompt just type rman

It defaults connects to database environmental variables defined

RMAN utility can be used only when your DB is in at least MOUNT stage

RMAN is used while the DB is up and running and have very little performance impact if the backup is running

RMAN Backup Methodology:

- Full Backup

        - Entire Database backup plus archive logs 

        - Cannot apply Incremental backup on full backup

                            

- Incremental Backup

        - Level 0 - FULL Database backup

        - Level 1 - Backup changes only from last incremental backup

- Differential Backup 

                - Cumulative Backup

Components of RMAN:

  - RMAN Prompt

  - Target Database

  - Recovery Catalog

  - Auxiliary Database --- When cloning, Clone DB is called as auxiliary Database

  - Media Management Layer --- layer between RMAN 3rd part vendor backup tools netbackup,  Veritos and Tape backups

  - RMAN Channels

RMAN Configuration Parameter:

- RETENTION POLICY - tells till what date our backup will be stored which we can use for recovery

               - Redundancy -- How many backups to be retained

               - Recover Window -- How many days backup to be retained

        - Channels -- You can define channel to take backup to disk or tape

        - Control file auto backup -- includes control file and spfile auto backup

        - Parallelism -- Creates multiple processes to speed up backup

        - Encryption -- to secure the backup