DBA WORLD

Oracle datapump utility EXPDP and IMPDP

noreply@blogger.com (Sai Sreenu) — Sun, 12 Apr 2026 16:31:00 +0530

The Oracle Data Pump utilities — EXPDP (Export Data Pump) and IMPDP (Import Data Pump) — are powerful tools used for logical backup and restore in Oracle Database.

They are faster and more flexible than the old exp/imp utilities.

Logical backups using Datapump:

Table level

Schema level

Entire database

Tablespace level

Transportable tablespace level

Create Data Pump Directory at OS level:

[oracle@ora101 ~]$ mkdir -p /u01/app/datapump/

Create directory inside the database:

SQL> create directory DATA_PUMP as '/u01/app/datapump/';

Directory created.

Grant permissions on directory to system user:

SQL> GRANT READ,WRITE on directory DATA_PUMP to system;

Grant succeeded.

View directory information:

SQL>

SQL> select * from dba_directories where DIRECTORY_NAME='DATA_PUMP';

OWNER

--------------------------------------------------------------------------------

DIRECTORY_NAME

--------------------------------------------------------------------------------

DIRECTORY_PATH

--------------------------------------------------------------------------------

ORIGIN_CON_ID

-------------

SYS

DATA_PUMP

/u01/app/datapump/

Table Level Export:

[oracle@ora101 ~]$ expdp system/Manager1 directory=DATA_PUMP tables=HR.EMPLOYEES dumpfile=HR_EMPLOYEES.dump logfile=HR_EMPLOYEES.log

[oracle@ora101 datapump]$ expdp system/Manager1 directory=DATA_PUMP tables=HR.EMPLOYEES,HR.JOBS dumpfile=HR_JOBS_EMPLOYEES.dump logfile=HR_JOBS_EMPLOYEES.log

[oracle@ora101 datapump]$ expdp system/Manager1 directory=DATA_PUMP tables=HR.EMPLOYEES,INFODBA.DEPT1 dumpfile=HR_EMPLOYEES_INFODBA.DEPT1.dump logfile=HR_EMPLOYEESINFODBA.DEPT1.log

[oracle@ora101 datapump]$ expdp system/Manager1 directory=DATA_PUMP tables=HR.EMPLOYEES,INFODBA.DEPT1 dumpfile=HR_EMPLOYEES_INFODBA.DEPT1.dump logfile=HR_EMPLOYEESINFODBA.DEPT1.log PARALLEL=4

[oracle@ora101 ~]$ expdp system/Manager1 directory=DATA_PUMP tables=HR.EMPLOYEES dumpfile=HR_EMPLOYEESDATA_ONLY.dump logfile=HR_EMPLOYEESDATA_ONLY.log content=DATA_ONLY

[oracle@ora101 ~]$ expdp system/Manager1 directory=DATA_PUMP tables=HR.EMPLOYEES dumpfile=HR_EMPLOYEESMETADATA_ONL.dump logfile=HR_EMPLOYEESMETADATA_ONL.log content=METADATA_ONLY

Table Level Import:

[oracle@ora101 datapump]$ impdp system/Manager1 directory=DATA_PUMP tables=HR.EMPLOYEES dumpfile=HR_EMPLOYEES.dump logfile=HR_EMPLOYEES.log

[oracle@ora101 datapump]$ impdp system/Manager1 directory=DATA_PUMP tables=HR.EMPLOYEES dumpfile=HR_EMPLOYEES.dump logfile=HR_EMPLOYEES.log table_exists_action=skip

[oracle@ora101 datapump]$ impdp system/Manager1 directory=DATA_PUMP tables=HR.EMPLOYEES REMAP_TABLE=HR.EMPLOYEES:HR dumpfile=HR_EMPLOYEES.dump logfile=HR_EMPLOYEES.log

[oracle@ora101 datapump]$ impdp system/Manager1 directory=DATA_PUMP tables=HR.EMPLOYEES REMAP_SCHEMA=HR:INFODBA dumpfile=HR_EMPLOYEES.dump logfile=HR_EMPLOYEES.log

[oracle@ora101 datapump]$ impdp system/Manager1 directory=DATA_PUMP tables=HR.EMPLOYEES REMAP_SCHEMA=HR:INFODBA REMAP_TABLE=EMPLOYEES:EMPLOYEES1 dumpfile=HR_EMPLOYEES.dump logfile=HR_EMPLOYEES.log

Schema Level Export:

[oracle@ora101 ~]$ expdp system/Manager1 directory=DATA_PUMP schemas=HR dumpfile=HR.dump logfile=HR.log PARALLEL=4

[oracle@ora101 ~]$ expdp system/Manager1 directory=DATA_PUMP schemas=HR,INFODBA dumpfile=HR_INFODBA.dump logfile=HR_INFODBA.log PARALLEL=4

[oracle@ora101 ~]$ expdp system/Manager1 directory=DATA_PUMP schemas=HR dumpfile=HR_DATA_ONLY.dump logfile=HR_DATA_ONLY.log content=DATA_ONLY PARALLEL=4

[oracle@ora101 ~]$ expdp system/Manager1 directory=DATA_PUMP schemas=HR dumpfile=HR_METADATA_ONLY.dump logfile=HR_METADATA_ONLY.log content=METADATA_ONLY PARALLEL=4

Schema Level Export:

[oracle@ora101 datapump]$ impdp system/Manager1 directory=DATA_PUMP dumpfile=HR.dump logfile=HR.log schemas=HR

[oracle@ora101 datapump]$ impdp system/Manager1 directory=DATA_PUMP dumpfile=HR.dump logfile=HR.log remap_schema=HR:HR

[oracle@ora101 datapump]$ impdp system/Manager1 directory=DATA_PUMP dumpfile=HR.dump logfile=HR.log remap_schema=HR:APP

Database Level Export:

[oracle@ora101 ~]$ expdp system/Manager1 directory=DATA_PUMP FULL=Y dumpfile=PROD_FULL.dump logfile=PROD_FULL.log

Database Level Import:

Import full database

On source

SQL> select name from v$tablespace;

On target

SQL> select name from v$tablespace;

Create missing tablespaces on target

Make sure target tablespace has enough free space

Drop all non-oracle schemas (done during refresh)

DROP USER HR CASCADE;

[oracle@ora101 datapump]$ impdp system/Manager1 directory=DATA_PUMP FULL=Y dumpfile=PROD_FULL.dump logfile=PROD_FULL.log

[oracle@ora101 datapump]$ impdp system/Manager1 directory=DATA_PUMP FULL=Y dumpfile=PROD_FULL.dump logfile=PROD_FULL.log table_exists_action=skip

Tablespace Level Export:

[oracle@ora101 ~]$ expdp system/Manager1 directory=DATA_PUMP TABLESPACES=HR

dumpfile=TBS_HR.dump logfile=TBS_HR.log

[oracle@ora101 ~]$ expdp system/Manager1 directory=DATA_PUMP TABLESPACES=HR,MANIT

dumpfile=TBS_HR_MANIT.dump logfile=TBS_HR_MANIT.log

[oracle@ora101 ~]$ expdp system/Manager1 directory=DATA_PUMP TABLESPACES=HR

dumpfile=TBS_HR.dump logfile=TBS_HR.log PARALLEL=4 COMPRESSION=ALL

Option Purpose

PARALLEL=4 Faster export

COMPRESSION=ALL Reduce size

EXCLUDE=STATISTICS Skip stats

FLASHBACK_TIME=SYSTIMESTAMP Consistent backup

LOGTIME=ALL Detailed logs

Option What Happens Use Case

SKIP Ignore table Safe runs

APPEND Add rows Data merge

TRUNCATE Delete + reload Refresh

REPLACE Drop & recreate Migration

Data Pump Export Progress %:

Use below query to get the percentage (%) completion of the export process

SELECT SID, SERIAL#, USERNAME, CONTEXT, SOFAR, TOTALWORK,ROUND(SOFAR/TOTALWORK*100,2) "%_COMPLETE"FROM V$SESSION_LONGOPS WHERE TOTALWORK != 0 AND SOFAR <> TOTALWORK;

SELECT * FROM dba_datapump_sessions;

SET LINES 200

COL job_name FORMAT A25

COL state FORMAT A12

SELECT owner_name, job_name, operation, job_mode, state

FROM dba_datapump_jobs;

SELECT sid, serial#, opname,

ROUND(sofar/totalwork*100,2) "%DONE"

FROM v$session_longops

WHERE opname LIKE 'SYS_EXPORT%' OR opname LIKE 'SYS_IMPORT%';

Oracle Database Tablespace Management

noreply@blogger.com (Sai Sreenu) — Fri, 10 Apr 2026 12:27:00 +0530

A tablespace is a logical storage container in Oracle Database used to store database objects such as tables and indexes. Each tablespace consists of one or more physical datafiles where the actual data is stored

We have two types tablespaces in oracle

1) Normal Tablespace

2) Big Tablespace

Note: Data file MAX size is depends on the data block size.

Normal Tablespace

Block Size Max Datafile Size

2 KB 8 GB

4 KB 16 GB

8 KB 32 GB

16 KB 64 GB

32 KB 128 GB

Big Tablespace:

Block Size Max Datafile Size

8 KB 32 TB

16 KB 64 TB

32 KB 128 TB

Default Tablespaces:

SYSTEM:

Stores data dictionary objects

Contains core metadata of the database

Must always be ONLINE

SYSAUX:

Auxiliary tablespace for SYSTEM

Stores components like:

AWR (Automatic Workload Repository)

OEM data

UNDO:

Stores undo data:

Rollback operations ,Read consistency and Flashback

Users:

Default tablespace for normal users

Temp:

Temporary operations:

Sorting, Joins and Hash operations

You’ll deal with three main types:

Permanent tablespaces — where actual table/index data lives.

Temporary tablespaces — used for sorts, joins, and other temporary work.

Undo tablespaces — store undo data for rollbacks and read consistency.

Tablespace States:

State Description

ONLINE Accessible

OFFLINE Not accessible

READ ONLY No changes allowed

READ WRITE Default

To View Tablespace Information:

v$tablespace

v$datafile

v$tempfile

dba_tablespaces

dba_data_files

dba_temp_files

Create tablespace:

Create tablespace PROD_DATA datafile '/2a/data/oracle12c/SDADS/datafile/PROD_DATA.dbf' size 1G;

Create tablespace with autoextend on:

CREATE TABLESPACE PROD DATAFILE '/2a/data/oracle12c/SDADS/datafile/PROD.dbf' SIZE 500M AUTOEXTEND ON;

CREATE TABLESPACE PROD_IDATA DATAFILE '/2a/data/oracle12c/SDADS/datafile/PROD_IDATA .dbf' SIZE 500M AUTOEXTEND ON NEXT 50M

MAXSIZE UNLIMITED;

Resize Datafile:

alter database datafile '/2a/data/oracle12c/SDADS/datafile/prod1.dbf' resize 300m;

Add a new datafile to existing tablespace:

alter tablespace PROD add datafile '/2a/data/oracle12c/SDADS/datafile/prod1.dbf' size 20m;

Big Tablespace:

CREATE BIGFILE TABLESPACE Big_DATA DATAFILE '/2a/data/oracle12c/SDADS/datafile/Big_DATA.dbf' SIZE 5G AUTOEXTEND ON;

Resize Big Tablespace Datafile:

ALTER DATABASE DATAFILE '/2a/data/oracle12c/SDADS/datafile/Big_DATA.dbf' RESIZE 20G;

Enable Autoextend:

ALTER DATABASE DATAFILE ''2a/data/oracle12c/SDADS/datafile/Big_DATA.dbf'

AUTOEXTEND ON;

ALTER DATABASE DATAFILE ''2a/data/oracle12c/SDADS/datafile/Big_DATA.dbf'

AUTOEXTEND ON NEXT 1G MAXSIZE 100G;

Drop Tablespace:

Drop a tablespace without removing the physical database files:

drop tablespace PROD_DATA;

Drop tablespace including the physical datafiles:

Drop tablespace PROD_DATA including contents and datafiles;

Make tablespace Read only:

alter tablespace PROD_DATA READ ONLY;

alter tablespace PROD_DATA read write;

Take tablespace offline:

ALTER TABLESPACE PROD_DATA OFFLINE;

ALTER TABLESPACE PROD_DATA ONLINE;

Create a temporary tablespace:

CREATE TEMPORARY TABLESPACE TEMP1 '/1a/app/oracle/oradata/ORCL/datafile/temp01.dbf' size 1G autoextend on next 200M;

Adding a tempfile:

alter tablespace TEMP1 add tempfile '/1a/app/oracle/oradata/ORCL/datafile/temp02.dbf' size 1G autoextend on next 200M;

Resize tempfile:

alter database tempfile '/1a/app/oracle/oradata/ORCL/datafile/temp02.dbf' resize 2G;

Drop Temp Tablespace:

select FILE_NAME,FILE_ID,TABLESPACE_NAME,

AUTOEXTENSIBLE,STATUS,maxbytes/1024/1024/1024,BYTES/1024/1024/1024

from dba_temp_files ;

Verify if any active sessions are using the temp files:

SELECT b.tablespace,b.segfile#,b.segblk#,b.blocks,a.sid,a.serial#,

a.username,a.osuser, a.status

FROM v$session a,v$sort_usage b

WHERE a.saddr = b.session_addr;

If any active sessions using the temp tablespace kill it:

alter system kill session 'SID_NUMBER, SERIAL#' immediate;

Drop Temp Tablespace:

ALTER TABLESAPCETEMP drop tempfile '/1a/app/oracle/oradata/ORCL/datafile/temp01.dbf';

ALTER DATABASE TEMPFILE '/1a/app/oracle/oradata/ORCL/datafile/temp02.dbf' DROP INCLUDING DATAFILES;

Tablespaces and Datafiles Info:

SELECT file_name, tablespace_name, bytes/1024/1024 MB

FROM dba_data_files;

SELECT tablespace_name, bigfile FROM dba_tablespaces WHERE tablespace_name = 'PROD_BIGTS';

select FILE_NAME,TABLESPACE_NAME,BYTES/1024/1024,STATUS,AUTOEXTENSIBLE from dba_data_files;

SELECT tablespace_name, ROUND((used_space / tablespace_size) * 100, 2) pct_used

FROM dba_tablespace_usage_metrics;

Tablespace Usage Query:

SELECT

df.tablespace_name,

ROUND(df.total_mb,2) total_mb,

ROUND(fs.free_mb,2) free_mb,

ROUND(df.total_mb - fs.free_mb,2) used_mb,

ROUND((df.total_mb - fs.free_mb)/df.total_mb * 100,2) used_pct

FROM

(SELECT tablespace_name, SUM(bytes)/1024/1024 total_mb

FROM dba_data_files

GROUP BY tablespace_name) df,

(SELECT tablespace_name, SUM(bytes)/1024/1024 free_mb

FROM dba_free_space

GROUP BY tablespace_name) fs

WHERE df.tablespace_name = fs.tablespace_name

ORDER BY used_pct DESC;

Tablespace Usage Report:

SET LINES 200

SET PAGES 100

COLUMN tablespace_name FORMAT A20

COLUMN total_mb FORMAT 999999

COLUMN used_mb FORMAT 999999

COLUMN free_mb FORMAT 999999

COLUMN pct_used FORMAT 999.99

SELECT

df.tablespace_name,

ROUND(df.total_mb) total_mb,

ROUND(df.total_mb - fs.free_mb) used_mb,

ROUND(fs.free_mb) free_mb,

ROUND((df.total_mb - fs.free_mb)/df.total_mb * 100, 2) pct_used

FROM

(SELECT tablespace_name, SUM(bytes)/1024/1024 total_mb

FROM dba_data_files

GROUP BY tablespace_name) df,

(SELECT tablespace_name, SUM(bytes)/1024/1024 free_mb

FROM dba_free_space

GROUP BY tablespace_name) fs

WHERE df.tablespace_name = fs.tablespace_name

ORDER BY pct_used DESC;

TEMP Tablespace Monitoring:

SELECT

tablespace_name,

SUM(bytes_used)/1024/1024 used_mb,

SUM(bytes_free)/1024/1024 free_mb

FROM v$temp_space_header

GROUP BY tablespace_name;

Verify Tablesapce are comsuming above 85%:

SELECT tablespace_name || ' ' || used_percent

FROM dba_tablespace_usage_metrics

WHERE used_percent > 85;

Monitor longest-running transactions with:

SELECT s.sid, s.serial#, t.used_ublk, t.used_urec

FROM v$transaction t, v$session s

WHERE t.ses_addr = s.saddr;

Check usage:

SELECT tablespace_name, SUM(blocks)*8/1024 MB_USED

FROM v$sort_usage

GROUP BY tablespace_name;

Check Overall Usage:

SELECT tablespace_name, ROUND((used_space/tablespace_size)*100, 2) pct_used

FROM dba_tablespace_usage_metrics

ORDER BY pct_used DESC;

Identify Top Space Consumers:

SELECT owner, segment_name, segment_type, bytes/1024/1024 MB

FROM dba_segments

ORDER BY bytes DESC FETCH FIRST 10 ROWS ONLY;

TEMP Usage by Session:

SELECT s.username, s.sid, u.tablespace, u.blocks*8/1024 AS mb_used

FROM v$sort_usage u, v$session s

WHERE u.session_addr = s.saddr

ORDER BY mb_used DESC;

Enable and Disabling Archive Log Mode In Oracle

noreply@blogger.com (Sai Sreenu) — Sat, 28 Mar 2026 14:42:00 +0530

Checking Archivelog Mode:

SQL> archive log list;

You can also use below command

SQL> SELECT LOG_MODE FROM V$DATABASE;

Set Archivelog Destination:

You must set a destination for archivelog files

SQL> alter system set log_archive_dest_1='location=/u01/app/oracle/archivelogs';

Enable Archivelog Mode:

SQL> Shut immediate;

SQL> Startup mount;

SQL> alter database archivelog;

SQL> alter database open;

SQL> archive log list;

Disable Archivelog Mode:

SQL> shut immediate;

SQL> startup mount;

SQL> alter database noarchivelog;

SQL> alter database open;

SQL> archive log list;

How to Estimate Archive Destination Space:

SELECT A.*,

Round(A.Count#*B.AVG#/1024/1024/1024) Daily_Avg_gb

FROM

(SELECT

To_Char(First_Time,'YYYY-MM-DD') DAY,

Count(1) Count#,

Min(RECID) Min#,

Max(RECID) Max#

FROM v$log_history

GROUP

BY To_Char(First_Time,'YYYY-MM-DD')

ORDER

BY 1

) A,

(SELECT

Avg(BYTES) AVG#,

Count(1) Count#,

Max(BYTES) Max_Bytes,

Min(BYTES) Min_Bytes

FROM

v$log ) B;

RPO and RTO

noreply@blogger.com (Sai Sreenu) — Fri, 27 Mar 2026 14:16:00 +0530

RPO ==> Recover point object

How much data you can afford to lose

Measured in time

Defines backup frequency

RPO = 15 minutes

You can lose max 15 minutes of data

RTO ==> Recover time object

How quickly you must restore the system

Measured in time

Defines recovery speed

RTO = 30 minutes

System must be back within 30 minutes

PostgreSQL Source Code Installation

noreply@blogger.com (Sai Sreenu) — Wed, 11 Mar 2026 21:18:00 +0530

Build From Source Code:

PostgreSQL Source Code Installation means compiling and installing PostgreSQL directly from its source files instead of using prebuilt packages.

Prerequisite:

Before we install PostgreSQL software/cluster, we need to ensure that we have all the necessary tools and libraries on our system.

We need install software using root user only

○ C Compiler: PostgreSQL is written in C, so a C compiler is a must. Popular choices include GCC (GNU Compiler Collection) and Clang.

○ Build Tools: Ensure you have essential build tools like make and autoconf installed. These tools help in compiling the source code efficiently.

○ Libraries: PostgreSQL requires certain libraries like readline and zlib. We need to install these development libraries on our system.

○ readline-devel: Provides the necessary libraries for command-line editing and history.

○ zlib-devel: Required for compression support.

○ gcc: The GNU Compiler Collection needed to compile the PostgreSQL source code.

○ make: A build tool used to compile and install the software.

Step1) Install Required Packages

[root@post101 ~]# sudo dnf groupinstall "Development Tools" -y

[root@post101 ~]# yum install readline-devel zlib-devel openssl-devel libxml2-devel libxslt-devel systemd-devel wget tar perl perl-core perl-FindBin -y

[root@post101 ~]# sudo dnf install readline-devel zlib-devel openssl-devel libxml2-devel libxslt-devel systemd-devel wget tar perl perl-core perl-FindBin -y

[root@post101 ~]# sudo dnf groupinstall "Development Tools" -y

Step2) Create postgres user and add user to sudoers file

[root@post101 ~]# adduser postgres

[root@post101 ~]# passwd postgres

Retype new password:

passwd: all authentication tokens updated successfully.

[root@post101 ~]# vi /etc/sudoers

## Allow root to run any commands anywhere

root ALL=(ALL) ALL

postgres ALL=(ALL) ALL

Step3)

Download Source Code From https://www.postgresql.org/download/

Download software and copy software using WinSCP

Use below command if we have internet connection

[root@post101 ~]#

[root@post101 ~]# wget https://ftp.postgresql.org/pub/source/v17.1/postgresql-17.1.tar.gz

Step4) untar zip file and go to postgresql-17.1

[root@post101 ~]# tar -xvzf postgresql-17.1.tar.gz

[root@post101 ~]# cd postgresql-17.1

[root@post101 postgresql-17.1]# ls -lrt

total 820

drwxrwxr-x. 16 root root 4096 Nov 12 2024 src

-rw-rw-r--. 1 root root 983 Nov 12 2024 README.md

-rw-rw-r--. 1 root root 6484 Nov 12 2024 meson_options.txt

-rw-rw-r--. 1 root root 115220 Nov 12 2024 meson.build

-rw-rw-r--. 1 root root 1822 Nov 12 2024 Makefile

-rw-rw-r--. 1 root root 277 Nov 12 2024 HISTORY

-rw-rw-r--. 1 root root 4176 Nov 12 2024 GNUmakefile.in

drwxrwxr-x. 3 root root 87 Nov 12 2024 doc

-rw-rw-r--. 1 root root 1192 Nov 12 2024 COPYRIGHT

drwxrwxr-x. 59 root root 4096 Nov 12 2024 contrib

-rw-rw-r--. 1 root root 88218 Nov 12 2024 configure.ac

-rwxrwxr-x. 1 root root 578504 Nov 12 2024 configure

drwxrwxr-x. 2 root root 4096 Nov 12 2024 config

-rw-rw-r--. 1 root root 365 Nov 12 2024 aclocal.m4

[root@post101 postgresql-17.1]#

Step5) Create directory for software binaries installation

[root@post101 ~]# mkdir -p /u01/pgsql-17/

[root@post101 ~]# cd /u01/pgsql-17/

[root@post101 pgsql-17]# ls -lrt

total 0

[root@post101 pgsql-17]#

Step6) ./configure

Configure the Build

This prepares the build environment.

prefix ==> Installation directory

configure ==> Checks OS libraries & prepares compilation

[root@post101 postgresql-17.1]# ./configure --prefix=/u01/pgsql-17

i got the below errors , so can skip by using below command '

checking for icu-uc icu-i18n... no

configure: error: ICU library not found

If you have ICU already installed, see config.log for details on the

failure. It is possible the compiler isn't looking in the proper directory.

Use --without-icu to disable ICU support.

[root@post101 postgresql-17.1]# ./configure --prefix=/u01/pgsql-17 --without-icu --without-readline

This time this got succeed without any erros

config.status: creating src/include/pg_config_ext.h

config.status: creating src/interfaces/ecpg/include/ecpg_config.h

config.status: linking src/backend/port/tas/dummy.s to src/backend/port/tas.s

config.status: linking src/backend/port/posix_sema.c to src/backend/port/pg_sema.c

config.status: linking src/backend/port/sysv_shmem.c to src/backend/port/pg_shmem.c

config.status: linking src/include/port/linux.h to src/include/pg_config_os.h

config.status: linking src/makefiles/Makefile.linux to src/Makefile.port

[root@post101 postgresql-17.1]#

step7) make (Compile the Source Code)

This step builds PostgreSQL binaries.

[root@post101 postgresql-17.1]# make

Steep8) make install (Install PostgreSQL)

[root@post101 postgresql-17.1]# cd /u01/pgsql-17/

[root@post101 pgsql-17]# ls -lrt

total 16

drwxr-xr-x. 6 root root 4096 Mar 11 20:42 include

drwxr-xr-x. 2 root root 4096 Mar 11 20:43 bin

drwxr-xr-x. 6 root root 4096 Mar 11 20:43 share

drwxr-xr-x. 4 root root 4096 Mar 11 20:43 lib

[root@post101 pgsql-17]#

After installation, PostgreSQL will be located at:

/u01/pgsql-17/

we can see all executable files on bin location

[root@post101 postgresql-17.1]# cd /u01/pgsql-17/

[root@post101 pgsql-17]# ls -lrt

total 16

drwxr-xr-x. 6 root root 4096 Mar 11 20:42 include

drwxr-xr-x. 2 root root 4096 Mar 11 20:43 bin

drwxr-xr-x. 6 root root 4096 Mar 11 20:43 share

drwxr-xr-x. 4 root root 4096 Mar 11 20:43 lib

[root@post101 pgsql-17]# cd bin/

[root@post101 bin]# ls -lrt

total 15360

-rwxr-xr-x. 1 root root 10154008 Mar 11 20:42 postgres

-rwxr-xr-x. 1 root root 1083408 Mar 11 20:42 ecpg

-rwxr-xr-x. 1 root root 170704 Mar 11 20:42 initdb

-rwxr-xr-x. 1 root root 115224 Mar 11 20:42 pg_amcheck

-rwxr-xr-x. 1 root root 49664 Mar 11 20:42 pg_archivecleanup

-rwxr-xr-x. 1 root root 173376 Mar 11 20:42 pg_basebackup

-rwxr-xr-x. 1 root root 114304 Mar 11 20:42 pg_createsubscriber

-rwxr-xr-x. 1 root root 116408 Mar 11 20:42 pg_receivewal

-rwxr-xr-x. 1 root root 116160 Mar 11 20:42 pg_recvlogical

-rwxr-xr-x. 1 root root 85440 Mar 11 20:42 pg_checksums

-rwxr-xr-x. 1 root root 187312 Mar 11 20:42 pg_combinebackup

-rwxr-xr-x. 1 root root 47808 Mar 11 20:42 pg_config

-rwxr-xr-x. 1 root root 66592 Mar 11 20:42 pg_controldata

-rwxr-xr-x. 1 root root 82176 Mar 11 20:42 pg_ctl

-rwxr-xr-x. 1 root root 440376 Mar 11 20:42 pg_dump

-rwxr-xr-x. 1 root root 220784 Mar 11 20:42 pg_restore

-rwxr-xr-x. 1 root root 136528 Mar 11 20:42 pg_dumpall

-rwxr-xr-x. 1 root root 77088 Mar 11 20:42 pg_resetwal

-rwxr-xr-x. 1 root root 169448 Mar 11 20:42 pg_rewind

-rwxr-xr-x. 1 root root 55160 Mar 11 20:42 pg_test_fsync

-rwxr-xr-x. 1 root root 44120 Mar 11 20:42 pg_test_timing

-rwxr-xr-x. 1 root root 189328 Mar 11 20:42 pg_upgrade

-rwxr-xr-x. 1 root root 139072 Mar 11 20:42 pg_verifybackup

-rwxr-xr-x. 1 root root 123536 Mar 11 20:42 pg_waldump

-rwxr-xr-x. 1 root root 76488 Mar 11 20:42 pg_walsummary

-rwxr-xr-x. 1 root root 211344 Mar 11 20:43 pgbench

-rwxr-xr-x. 1 root root 528168 Mar 11 20:43 psql

-rwxr-xr-x. 1 root root 83648 Mar 11 20:43 createdb

-rwxr-xr-x. 1 root root 79032 Mar 11 20:43 dropdb

-rwxr-xr-x. 1 root root 88576 Mar 11 20:43 createuser

-rwxr-xr-x. 1 root root 78968 Mar 11 20:43 dropuser

-rwxr-xr-x. 1 root root 83888 Mar 11 20:43 clusterdb

-rwxr-xr-x. 1 root root 97744 Mar 11 20:43 vacuumdb

-rwxr-xr-x. 1 root root 93024 Mar 11 20:43 reindexdb

-rwxr-xr-x. 1 root root 78472 Mar 11 20:43 pg_isready

[root@post101 bin]#

Step8) Create Data Directory and grant Permissions to postgres user

[root@post101 bin]# mkdir -p /u01/pgdata

[root@post101 bin]# cd /u01/pgdata

[root@post101 pgdata]# ls -lrt

total 0

[root@post101 pgdata]#

[root@post101 pgdata]# chown -R postgres:postgres /u01/pgdata

Step9) switch to postgres user

[root@post101 pgdata]# su - postgres

[postgres@post101 ~]$ cd /u01/pg

pgdata/ pgsql-17/

[postgres@post101 ~]$ cd /u01/pgdata/

[postgres@post101 pgdata]$ pwd

/u01/pgdata

[postgres@post101 pgdata]$ ls -lrt

total 0

[postgres@post101 pgdata]$

Step10) Initialize Database Cluster

[postgres@post101 bin]$ /u01/pgsql-17/bin/initdb -D /u01/pgdata

The files belonging to this database system will be owned by user "postgres".

This user must also own the server process.

The database cluster will be initialized with locale "en_US.UTF-8".

The default database encoding has accordingly been set to "UTF8".

The default text search configuration will be set to "english".

Data page checksums are disabled.

fixing permissions on existing directory /u01/pgdata ... ok

creating subdirectories ... ok

selecting dynamic shared memory implementation ... posix

selecting default "max_connections" ... 100

selecting default "shared_buffers" ... 128MB

selecting default time zone ... Asia/Kolkata

creating configuration files ... ok

running bootstrap script ... ok

performing post-bootstrap initialization ... ok

syncing data to disk ... ok

initdb: warning: enabling "trust" authentication for local connections

initdb: hint: You can change this by editing pg_hba.conf or using the option -A, or --auth-local and --auth-host, the next time you run initdb.

Success. You can now start the database server using:

/u01/pgsql-17/bin/pg_ctl -D /u01/pgdata -l logfile start

[postgres@post101 bin]$

Step11) Start PostgreSQL cluster

[postgres@post101 bin]$ /u01/pgsql-17/bin/pg_ctl -D /u01/pgdata start

waiting for server to start....2026-03-11 20:56:42.024 IST [9732] LOG: starting PostgreSQL 17.1 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 4.8.5 20150623 (Red Hat 4.8.5-44.0.3), 64-bit

2026-03-11 20:56:42.035 IST [9732] LOG: listening on IPv6 address "::1%1", port 5432

2026-03-11 20:56:42.035 IST [9732] LOG: listening on IPv4 address "127.0.0.1", port 5432

2026-03-11 20:56:42.417 IST [9732] LOG: listening on Unix socket "/tmp/.s.PGSQL.5432"

2026-03-11 20:56:42.432 IST [9735] LOG: database system was shut down at 2026-03-11 20:54:02 IST

2026-03-11 20:56:42.446 IST [9732] LOG: database system is ready to accept connections

done

server started

[postgres@post101 bin]$

[postgres@post101 bin]$ ps -ef|grep postgres

root 7357 3458 0 20:51 pts/0 00:00:00 su - postgres

postgres 7358 7357 0 20:51 pts/0 00:00:00 -bash

postgres 9732 1 0 20:56 ? 00:00:00 /u01/pgsql-17/bin/postgres -D /u01/pgdata

postgres 9733 9732 0 20:56 ? 00:00:00 postgres: checkpointer

postgres 9734 9732 0 20:56 ? 00:00:00 postgres: background writer

postgres 9736 9732 0 20:56 ? 00:00:00 postgres: walwriter

postgres 9737 9732 0 20:56 ? 00:00:00 postgres: autovacuum launcher

postgres 9738 9732 0 20:56 ? 00:00:00 postgres: logical replication launcher

postgres 10878 7358 99 20:59 pts/0 00:00:00 ps -ef

postgres 10879 7358 0 20:59 pts/0 00:00:00 grep --color=auto postgres

[postgres@post101 bin]$

Step12) Set Environment Variables

[postgres@post101 ~]$ vi .bash_profile

export PATH=$PATH:/u01/pgsql-17/bin

export PGDATA=/u01/pgdata

[postgres@post101 ~]$ . .bash_profile

[postgres@post101 ~]$ source .bash_profile

[postgres@post101 ~]$ psql

psql (17.1)

Type "help" for help.

postgres=# \l

List of databases

Rules | Access privileges

-----------+----------+----------+-----------------+-------------+-------------+--------+-----

------+-----------------------

| =c/postgres +

| | | | | | |

| postgres=CTc/postgres

| =c/postgres +

| | | | | | |

| postgres=CTc/postgres

(3 rows)

Step13) How to start postgres services auto start whenever server rebooted/shutdown

Switch to root user

[postgres@post101 ~]$ su -

Password:

Last login: Wed Mar 11 19:59:13 IST 2026 from 2406:7400:9a:e591:fc9d:9082:d6a7:20c1 on pts/0

[root@post101 ~]#

[root@post101 ~]# cd /etc/rc.d

[root@post101 rc.d]# ls -lrt

total 8

drwxr-xr-x. 2 root root 61 Jun 12 2024 rc6.d

drwxr-xr-x. 2 root root 61 Jun 12 2024 rc5.d

drwxr-xr-x. 2 root root 61 Jun 12 2024 rc4.d

drwxr-xr-x. 2 root root 61 Jun 12 2024 rc3.d

drwxr-xr-x. 2 root root 61 Jun 12 2024 rc2.d

drwxr-xr-x. 2 root root 61 Jun 12 2024 rc1.d

drwxr-xr-x. 2 root root 61 Jun 12 2024 rc0.d

-rw-r--r--. 1 root root 473 Oct 10 2024 rc.local

drwxr-xr-x. 2 root root 4096 Feb 6 17:55 init.d

[root@post101 rc.d]# vi rc.local

[root@post101 rc.d]# cat rc.local

#!/bin/bash

# THIS FILE IS ADDED FOR COMPATIBILITY PURPOSES

# It is highly advisable to create own systemd services or udev rules

# to run scripts during boot instead of using this file.

# In contrast to previous versions due to parallel execution during boot

# this script will NOT be run after all other services.

# Please note that you must run 'chmod +x /etc/rc.d/rc.local' to ensure

# that this script will be executed during boot.

touch /var/lock/subsys/local

su - postgres -c 'pg_ctl -D $PGDATA start'

[root@post101 rc.d]#

[root@post101 rc.d]# chmod +x /etc/rc.d/rc.local

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

noreply@blogger.com (Sai Sreenu) — Mon, 9 Mar 2026 17:32:00 +0530

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

noreply@blogger.com (Sai Sreenu) — Mon, 9 Mar 2026 12:31:00 +0530

[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

noreply@blogger.com (Sai Sreenu) — Fri, 6 Mar 2026 22:37:00 +0530

Always ON Readbale Secondary Options

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

noreply@blogger.com (Sai Sreenu) — Fri, 6 Mar 2026 15:03:00 +0530

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

noreply@blogger.com (Sai Sreenu) — Wed, 4 Mar 2026 12:59:00 +0530

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

noreply@blogger.com (Sai Sreenu) — Fri, 27 Feb 2026 21:10:00 +0530

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

noreply@blogger.com (Sai Sreenu) — Thu, 26 Feb 2026 12:18:00 +0530

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

noreply@blogger.com (Sai Sreenu) — Thu, 26 Feb 2026 11:55:00 +0530

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

Backups and RMAN Backups ( Recovery Manager)

noreply@blogger.com (Sai Sreenu) — Wed, 25 Feb 2026 22:26:00 +0530

Why we need backup

The primary purpose of taking backups is to protect the database from data loss and ensure the ability to reconstruct the database in case of corruption, errors, or failures.

Categories of Database Failure:

Data loss can occur at any time

Human errors, accidental deletions, logical corruption, hardware failures, or storage issues can lead to partial or complete data loss.

Backups preserve data integrity

When data corruption or inconsistencies arise, backups enable you to restore the database to a consistent and trustworthy state.

Support for disaster recovery

In the event of serious failures—like server crashes, media corruption, or site outages—backups are the foundation for bringing the database back online.

Compliance and business continuity

Many industries require proper backup policies to meet audit standards and ensure uninterrupted business operations.

Backup Types:

Oracle backups are broadly classified into two categories:

1. Logical Backups

Logical backups extract database objects in a logical format.

Examples: Table-level, Schema-level and Tablespace-level export

Tools Used: Data Pump (expdp / impdp) and Export/Import (exp / imp)

Logical backups are mainly used for:

Application-level migrations

Object-level recoveries

Copying data between databases

2. Physical Backups

Physical backups involve copying actual database files at the OS level.

Files Included Datafiles, Control files, SPFILE and Archived redo logs

Types of Physical Backups

RMAN Backups (most recommended)

Hot Backup (User‑Managed Online Backup)

Cold Backup (User‑Managed Offline Backup)

Hot and Cold Backups

Hot and Cold backups are known as manual backups, as they are performed manually without using RMAN or any backup tool.

Cold Backup (Consistent Backup)

Database is shut down.

All database files are copied at OS level.

Backup is consistent.

No recovery required during restore.

Also referred to as a consistent backup.

Hot Backup (Inconsistent Backup)

Database is open and available to users.

Tablespaces are placed in backup mode.

Datafiles are copied manually at OS level.

Recovery is required because the backup is inconsistent.

Archived logs are needed for full recovery.

Backup States / Backup Types in Oracle

Oracle backups fall under two main categories based on the state of the database:

1) Consistent Backup

A backup is consistent when: The database is shut down cleanly using SHUTDOWN IMMEDIATE / NORMAL / TRANSACTIONAL.

All files (datafiles, control files, redo logs) are synchronized.

2) Inconsistent Backup

A backup is inconsistent when the database is in OPEN or MOUNT mode during the backup.

Files are not synchronized; hence recovery is mandatory.

Examples

Hot Backup (User‑managed online backup)

RMAN Backups (always inconsistent unless database is closed)

Oracle RMAN (Recovery Manager)

Oracle Recovery Manager (RMAN) is a powerful, built‑in Oracle database utility used for performing backup, restore, and recovery operations. It is a no‑cost, command‑line tool provided by Oracle to help DBAs protect critical database files—including datafiles, control files, SPFILEs, and archived redo logs.

RMAN integrates tightly with the Oracle database engine, enabling advanced features such as:

Block‑level corruption detection

RMAN scans blocks during backup and restore operations, identifying corrupted blocks early and ensuring data integrity.

Automated backup retention policies

You can define retention periods or redundancy levels, and RMAN will automatically manage obsolete backups.

Backup compression

Built‑in compression reduces storage costs and speeds up backup operations.

Parallelization

File identification

Block recovery

Media recovery

RMAN can run backup and restore operations across multiple channels, improving performance and meeting high‑availability needs.

What to Backup

Parameter file / SPfile , Control file , archive log files and data files

What file needed in different stages of database

1) Shutdown - Nothing needed

2) No Mount - Pfile or SPfile

3) Mount - Control file

4) Open - Data and redo log files

Recovery:

1. Complete Recovery

--- Recovering the database exactly till the point of failure

2. Incomplete Recovery

--- It cannot recover the database till the point of failure

--- You can recover the database till the time you had taken the backup

SQL Server 2022 In place Upgrade - Fails and resumes , succeeded, But SSRS failed to install.

noreply@blogger.com (PRADYOTHANA) — Sun, 24 Aug 2025 16:13:00 +0530

During in place upgrade of SQL Server 2022, Installation may fail with below error or warning, but it will end with successful message. During SSRS or PBIRS installation it may fail with Second error as shown below -

First Error/Warning During upgrade

Second Error SSRS/PBI RS:

UnexpectedError: setup failed for packageId: SSReportingServicesSetup, package: Microsoft SQL Server Reporting Services, errorCode: -2147483648, errorMessage: Something went wrong, please find details in setup logs.

Resolution :

Sometimes the SQL Server WMI namespace (root\Microsoft\SqlServer\ComputerManagement) is missing or corrupted.

Run this in Command Prompt:

mofcomp "C:\Program Files (x86)\Microsoft SQL Server\150\Shared\sqlmgmproviderxpsp2up.mof"

Note: make sure you are in the right folder and right version of the SQL Server.

SQL Server Configuration Manager Missing

noreply@blogger.com (PRADYOTHANA) — Wed, 20 Aug 2025 15:26:00 +0530

Go to respective shared folder and register -

SQL Server Daily Health Check - Script

noreply@blogger.com (PRADYOTHANA) — Sun, 27 Jul 2025 10:44:00 +0530

/* =======================================================================

SQL Server Health Check – HTML Email (Final)

- DBA's Heart Beat

======================================================================= */

SET NOCOUNT ON;

DECLARE @html NVARCHAR(MAX) = N'';

DECLARE @newline NVARCHAR(10) = CHAR(13) + CHAR(10);

-- =========================

-- HTML Header + Styles

-- =========================

SET @html = @html + N'<html><head><style>

body { font-family: Segoe UI, Arial, sans-serif; font-size: 13px; color: #222; }

h3 { margin: 18px 0 8px; }

table { border-collapse: collapse; width: auto; max-width: 100%; }

th, td { border: 1px solid #ddd; padding: 6px 8px; text-align: left; }

th { background-color: #f2f2f2; }

tr.alert { color: #b00020; font-weight: 600; }

.small { color:#666; font-size:12px; }

</style></head><body>' + @newline;

-- =========================

-- SQL Server Version & EOL

-- =========================

DECLARE @Servername NVARCHAR(128) = CAST(SERVERPROPERTY('ServerName') AS NVARCHAR(128));

DECLARE @version NVARCHAR(128) = CAST(SERVERPROPERTY('ProductVersion') AS NVARCHAR(128));

DECLARE @edition NVARCHAR(128) = CAST(SERVERPROPERTY('Edition') AS NVARCHAR(128));

DECLARE @eol NVARCHAR(256);

DECLARE @MajorVersion NVARCHAR(256);

SET @MajorVersion = CASE

WHEN CONVERT(VARCHAR(128), SERVERPROPERTY('productversion')) LIKE '8%' THEN 'SQL2000'

WHEN CONVERT(VARCHAR(128), SERVERPROPERTY('productversion')) LIKE '9%' THEN 'SQL2005'

WHEN CONVERT(VARCHAR(128), SERVERPROPERTY('productversion')) LIKE '10.0%' THEN 'SQL2008'

WHEN CONVERT(VARCHAR(128), SERVERPROPERTY('productversion')) LIKE '10.5%' THEN 'SQL2008 R2'

WHEN CONVERT(VARCHAR(128), SERVERPROPERTY('productversion')) LIKE '11%' THEN 'SQL2012'

WHEN CONVERT(VARCHAR(128), SERVERPROPERTY('productversion')) LIKE '12%' THEN 'SQL2014'

WHEN CONVERT(VARCHAR(128), SERVERPROPERTY('productversion')) LIKE '13%' THEN 'SQL2016'

WHEN CONVERT(VARCHAR(128), SERVERPROPERTY('productversion')) LIKE '14%' THEN 'SQL2017'

WHEN CONVERT(VARCHAR(128), SERVERPROPERTY('productversion')) LIKE '15%' THEN 'SQL2019'

WHEN CONVERT(VARCHAR(128), SERVERPROPERTY('productversion')) LIKE '16%' THEN 'SQL2022'

ELSE 'Unknown/New Version'

END;

SET @eol = CASE

WHEN @@VERSION LIKE '%2005%' THEN 'Mainstream: 2011-04-12, Extended: 2016-04-12'

WHEN @@VERSION LIKE '%2008 R2%' THEN 'Mainstream: 2014-07-08, Extended: 2019-07-09'

WHEN @@VERSION LIKE '%2008%' THEN 'Mainstream: 2014-07-08, Extended: 2019-07-09'

WHEN @@VERSION LIKE '%2012%' THEN 'Mainstream: 2017-07-11, Extended: 2022-07-12'

WHEN @@VERSION LIKE '%2014%' THEN 'Mainstream: 2019-07-09, Extended: 2024-07-09'

WHEN @@VERSION LIKE '%2016%' THEN 'Mainstream: 2021-07-13, Extended: 2026-07-14'

WHEN @@VERSION LIKE '%2017%' THEN 'Mainstream: 2022-10-11, Extended: 2027-10-12'

WHEN @@VERSION LIKE '%2019%' THEN 'Mainstream: 2025-02-28, Extended: 2030-01-08'

WHEN @@VERSION LIKE '%2022%' THEN 'Mainstream: 2028-01-11, Extended: 2033-01-11'

ELSE 'Unknown/New Version'

END;

SET @html = @html + N'<h3>SQL Server Version and EOL</h3><table><tr>

<th>Instance Name</th><th>Version</th><th>PatchLevel</th><th>Edition</th><th>EOL Info</th></tr>';

SET @html = @html + N'<tr><td>' + @Servername + N'</td><td>' + @MajorVersion + N'</td><td>' +

@version + N'</td><td>' + @edition + N'</td><td>' + @eol + N'</td></tr></table>' + @newline;

-- =========================

-- Uptime (Start + Duration)

-- =========================

SET @html = @html + N'<h3>SQL Server Uptime</h3><table><tr><th>Start Time</th><th>Up Since</th></tr>';

DECLARE @start DATETIME = (SELECT sqlserver_start_time FROM sys.dm_os_sys_info);

DECLARE @now DATETIME = GETDATE();

DECLARE @diffSeconds BIGINT = DATEDIFF(SECOND, @start, @now);

DECLARE @days INT = @diffSeconds / 86400;

DECLARE @hours INT = (@diffSeconds % 86400) / 3600;

DECLARE @mins INT = (@diffSeconds % 3600) / 60;

DECLARE @secs INT = @diffSeconds % 60;

SELECT @html = @html +

N'<tr><td>' + CONVERT(NVARCHAR(19), @start, 120) + N'</td><td>' +

CAST(@days AS NVARCHAR(10)) + N' days ' +

CAST(@hours AS NVARCHAR(10)) + N' hrs ' +

CAST(@mins AS NVARCHAR(10)) + N' mins ' +

CAST(@secs AS NVARCHAR(10)) + N' secs</td></tr>';

SET @html = @html + N'</table>' + @newline;

-- =========================

-- Offline Databases

-- =========================

SET @html = @html + N'<h3>Offline Databases</h3>';

IF EXISTS (SELECT 1 FROM sys.databases WHERE state_desc = 'OFFLINE')

BEGIN

SET @html = @html + N'<table><tr><th>Database</th><th>Status</th></tr>';

SELECT @html = @html + N'<tr><td>' + name + N'</td><td>' + state_desc + N'</td></tr>'

FROM sys.databases WHERE state_desc = 'OFFLINE';

SET @html = @html + N'</table>' + @newline;

END

ELSE

BEGIN

SET @html = @html + N'<p>All databases are online.</p>' + @newline;

END

/* =======================================================================

Database Default Locations + All Disks (Fixed Drive Letters only)

WMI via OLE Automation – decimal math for Free%

======================================================================= */

-----------------------------

-- Database Default Locations

-----------------------------

DECLARE @DefaultData NVARCHAR(4000) = CAST(SERVERPROPERTY('InstanceDefaultDataPath') AS NVARCHAR(4000));

DECLARE @DefaultLog NVARCHAR(4000) = CAST(SERVERPROPERTY('InstanceDefaultLogPath') AS NVARCHAR(4000));

DECLARE @DefaultBackup NVARCHAR(4000);

BEGIN TRY

SET @DefaultBackup = CAST(SERVERPROPERTY('InstanceDefaultBackupPath') AS NVARCHAR(4000));

END TRY

BEGIN CATCH

SET @DefaultBackup = NULL;

END CATCH;

IF @DefaultBackup IS NULL OR LTRIM(RTRIM(@DefaultBackup)) = ''

BEGIN

DECLARE @bk NVARCHAR(4000);

EXEC master.dbo.xp_instance_regread

N'HKEY_LOCAL_MACHINE',

N'Software\Microsoft\MSSQLServer\MSSQLServer',

N'BackupDirectory',

@bk OUTPUT,

'no_output';

SET @DefaultBackup = @bk;

END

SET @html = @html + N'<h3>Database Default Locations</h3>'

+ N'<table><tr><th>Setting</th><th>Path</th></tr>'

+ N'<tr><td>Default Data</td><td>' + ISNULL(@DefaultData,'') + N'</td></tr>'

+ N'<tr><td>Default Log</td><td>' + ISNULL(@DefaultLog,'') + N'</td></tr>'

+ N'<tr><td>Default Backup</td><td>' + ISNULL(@DefaultBackup,'') + N'</td></tr>'

+ N'</table>' + @newline;

---------------------------------------------

-- All Disks: Fixed Drive Letters only (no \\?\Volume)

---------------------------------------------

IF OBJECT_ID('tempdb..#AllDisks') IS NOT NULL DROP TABLE #AllDisks;

CREATE TABLE #AllDisks

(

DriveLetter NVARCHAR(10) NOT NULL, -- 'C:'

TotalBytes BIGINT NOT NULL,

FreeBytes BIGINT NOT NULL

);

DECLARE @hr INT = 0, @locator INT = NULL, @svc INT = NULL, @set INT = NULL, @item INT = NULL, @count INT = 0, @i INT = 0;

BEGIN TRY

-- Connect to WMI

EXEC @hr = sp_OACreate 'WbemScripting.SWbemLocator', @locator OUT;

IF @hr <> 0 BEGIN RAISERROR('Failed to create WMI locator (sp_OACreate).', 16, 1); RETURN; END;

EXEC @hr = sp_OAMethod @locator, 'ConnectServer', @svc OUT, '.', 'root\cimv2';

IF @hr <> 0 BEGIN RAISERROR('Failed to connect to WMI root\cimv2.', 16, 1); RETURN; END;

-- Query: Fixed disks with letters (DriveType=3)

EXEC @hr = sp_OAMethod @svc, 'ExecQuery', @set OUT,

'SELECT DeviceID, Size, FreeSpace FROM Win32_LogicalDisk WHERE DriveType=3';

IF @hr <> 0 BEGIN RAISERROR('WMI ExecQuery failed for Win32_LogicalDisk.', 16, 1); RETURN; END;

EXEC @hr = sp_OAGetProperty @set, 'Count', @count OUT;

SET @i = 0;

WHILE @i < ISNULL(@count, 0)

BEGIN

EXEC @hr = sp_OAMethod @set, 'ItemIndex', @item OUT, @i;

IF @hr <> 0 BREAK;

DECLARE @DeviceID NVARCHAR(50), @Size NUMERIC(38,0), @Free NUMERIC(38,0);

EXEC @hr = sp_OAGetProperty @item, 'DeviceID', @DeviceID OUT;

EXEC @hr = sp_OAGetProperty @item, 'Size', @Size OUT;

EXEC @hr = sp_OAGetProperty @item, 'FreeSpace', @Free OUT;

-- Insert only if Size is present (>0)

IF @Size IS NOT NULL AND @Size > 0

BEGIN

INSERT INTO #AllDisks(DriveLetter, TotalBytes, FreeBytes)

VALUES (UPPER(@DeviceID), CONVERT(BIGINT, @Size), CONVERT(BIGINT, @Free));

END

SET @i = @i + 1;

END

END TRY

BEGIN CATCH

DECLARE @err NVARCHAR(4000) = ERROR_MESSAGE();

RAISERROR('WMI/OLE disk collection failed: %s', 16, 1, @err);

END CATCH

-- Cleanup WMI objects

IF @set IS NOT NULL BEGIN EXEC sp_OADestroy @set; END;

IF @svc IS NOT NULL BEGIN EXEC sp_OADestroy @svc; END;

IF @locator IS NOT NULL BEGIN EXEC sp_OADestroy @locator; END;

-- Render All Disks (decimal math for Free%)

SET @html = @html + N'<h3>All Disks</h3>'

+ N'<table><tr>'

+ N'<th>Drive</th><th>Total (GB)</th><th>Free (GB)</th>'

+ N'<th>Used (GB)</th><th>Free (%)</th></tr>';

;WITH calc AS

(

SELECT

Drive = DriveLetter,

totalGB = CONVERT(DECIMAL(18,2), TotalBytes / 1024.0 / 1024 / 1024),

freeGB = CONVERT(DECIMAL(18,2), FreeBytes / 1024.0 / 1024 / 1024),

usedGB = CONVERT(DECIMAL(18,2), (TotalBytes - FreeBytes) / 1024.0 / 1024 / 1024)

FROM #AllDisks

calc2 AS

(

SELECT

Drive, totalGB, freeGB, usedGB,

freePct = CONVERT(DECIMAL(5,2), CASE WHEN totalGB > 0 THEN (freeGB * 100.00) / totalGB ELSE 0 END)

FROM calc

)

SELECT @html = @html +

CASE WHEN freePct < 10 THEN N'<tr class="alert">' ELSE N'<tr>' END +

N'<td>' + Drive + N'</td><td>' +

CONVERT(NVARCHAR(50), totalGB) + N'</td><td>' +

CONVERT(NVARCHAR(50), freeGB) + N'</td><td>' +

CONVERT(NVARCHAR(50), usedGB) + N'</td><td>' +

CONVERT(NVARCHAR(50), freePct) + N'</td></tr>'

FROM calc2

ORDER BY Drive;

SET @html = @html + N'</table>' + @newline;

DROP TABLE #AllDisks;

-- =========================

-- Backup Status (exclude tempdb & mirrored DBs)

-- =========================

SET @html = @html + N'<h3>Backup Status (Failed or No Backup in Past 2 Days)</h3>';

IF EXISTS (

SELECT 1

FROM sys.databases d

LEFT JOIN msdb.dbo.backupset bs ON bs.database_name = d.name

LEFT JOIN sys.database_mirroring dm ON d.database_id = dm.database_id

WHERE d.name <> 'tempdb'

AND d.is_read_only = 0

AND dm.mirroring_guid IS NULL

GROUP BY d.name

HAVING MAX(bs.backup_finish_date) IS NULL

OR MAX(bs.backup_finish_date) < DATEADD(DAY, -2, GETDATE())

)

BEGIN

SET @html = @html + N'<table><tr><th>Database</th><th>Last Backup</th></tr>';

SELECT @html = @html + N'<tr><td>' + d.name + N'</td><td>' +

COALESCE(CONVERT(NVARCHAR(19), MAX(bs.backup_finish_date), 120), N'No Backup') + N'</td></tr>'

FROM sys.databases d

LEFT JOIN msdb.dbo.backupset bs ON bs.database_name = d.name

LEFT JOIN sys.database_mirroring dm ON d.database_id = dm.database_id

WHERE d.name <> 'tempdb'

AND d.is_read_only = 0

AND dm.mirroring_guid IS NULL

GROUP BY d.name

HAVING MAX(bs.backup_finish_date) IS NULL

OR MAX(bs.backup_finish_date) < DATEADD(DAY, -2, GETDATE());

SET @html = @html + N'</table>' + @newline;

END

ELSE

BEGIN

SET @html = @html + N'<p>All backups successful (within last 2 days).</p>' + @newline;

END

-- =========================

-- Mirroring Status

-- =========================

SET @html = @html + N'<h3>Mirroring Status</h3>';

IF EXISTS (SELECT 1 FROM sys.database_mirroring WHERE mirroring_guid IS NOT NULL)

BEGIN

SET @html = @html + N'<table><tr><th>Database</th><th>Mirroring State</th><th>Safety Level</th></tr>';

SELECT @html = @html + N'<tr><td>' + d.name + N'</td><td>' +

ISNULL(dm.mirroring_state_desc, N'Unknown') + N'</td><td>' +

ISNULL(dm.mirroring_safety_level_desc, N'Unknown') + N'</td></tr>'

FROM sys.databases d

JOIN sys.database_mirroring dm ON d.database_id = dm.database_id

WHERE dm.mirroring_guid IS NOT NULL;

SET @html = @html + N'</table>' + @newline;

END

ELSE

BEGIN

SET @html = @html + N'<p>No mirroring configured.</p>' + @newline;

END

-- =========================

-- Replication Status (GUID-safe + resilient + idempotent)

-- =========================

SET @html = @html + N'<h3>Replication Status</h3>';

IF DB_ID('distribution') IS NOT NULL

BEGIN

IF OBJECT_ID('distribution.dbo.MSreplication_monitordata') IS NOT NULL

BEGIN

-- Clean up any leftovers from previous runs in this session

IF OBJECT_ID('tempdb..#DA') IS NOT NULL DROP TABLE #DA;

IF OBJECT_ID('tempdb..#MM') IS NOT NULL DROP TABLE #MM;

IF OBJECT_ID('tempdb..#JobLast') IS NOT NULL DROP TABLE #JobLast;

IF OBJECT_ID('tempdb..#MonitorRepl') IS NOT NULL DROP TABLE #MonitorRepl;

/* 1) Distribution Agents with publication + subscriber */

SELECT

p.publisher_db,

a.publication,

a.subscriber_db,

a.job_id,

job_id_str = UPPER(CONVERT(NVARCHAR(36), a.job_id))

INTO #DA

FROM distribution.dbo.MSdistribution_agents AS a

INNER JOIN distribution.dbo.MSpublications AS p

ON p.publisher_id = a.publisher_id

AND p.publication = a.publication;

/* 2) Monitor data: normalize job_id string (trim, uppercase, remove braces) */

SELECT

publisher_db = m.publisher_db,

publication = m.publication,

job_id_str = NULLIF(UPPER(REPLACE(REPLACE(LTRIM(RTRIM(m.job_id)), '{', ''), '}', '')), ''),

[Status] = m.[Status],

[Warning] = m.[Warning],

cur_latency = m.cur_latency

INTO #MM

FROM distribution.dbo.MSreplication_monitordata AS m

WHERE m.agent_type = 3 -- distribution agents

AND NULLIF(LTRIM(RTRIM(m.job_id)), '') IS NOT NULL;

/* 3) Last job outcome for each agent (fallback when MM has no rows) */

SELECT

j.job_id,

job_id_str = UPPER(CONVERT(NVARCHAR(36), j.job_id)),

last_status = ISNULL(h_last.run_status, -1) -- -1 = unknown

INTO #JobLast

FROM msdb.dbo.sysjobs AS j

INNER JOIN #DA AS da ON da.job_id = j.job_id

OUTER APPLY (

SELECT TOP (1) run_status

FROM msdb.dbo.sysjobhistory

WHERE job_id = j.job_id AND step_id = 0

ORDER BY run_date DESC, run_time DESC

) AS h_last;

/* 4) Final projection: prefer MM status; else fallback to job history; else Unknown */

SELECT DISTINCT

da.publisher_db,

da.publication,

da.subscriber_db,

Status =

COALESCE(

/* MM status mapping */

CASE mm.[Status]

WHEN 1 THEN 'Started'

WHEN 2 THEN 'Succeeded'

WHEN 3 THEN 'In Progress'

WHEN 4 THEN 'Idle'

WHEN 5 THEN 'Retrying'

WHEN 6 THEN 'Failed'

ELSE NULL

END,

/* Job history mapping (fallback) */

CASE jl.last_status

WHEN 0 THEN 'Failed'

WHEN 1 THEN 'Succeeded'

WHEN 2 THEN 'Retrying'

WHEN 3 THEN 'Canceled'

WHEN 4 THEN 'In Progress'

ELSE 'Unknown'

END,

'Unknown'

)

INTO #MonitorRepl

FROM #DA AS da

LEFT JOIN #MM AS mm

ON (mm.job_id_str = da.job_id_str)

OR (mm.publisher_db = da.publisher_db AND mm.publication = da.publication)

LEFT JOIN #JobLast AS jl

ON jl.job_id = da.job_id;

/* 5) Render HTML */

SET @html = @html + N'<table><tr>'

+ N'<th>Publisher DB</th><th>Publication</th><th>Subscriber DB</th><th>Status</th>'

+ N'</tr>';

IF EXISTS (SELECT 1 FROM #MonitorRepl)

BEGIN

SELECT @html = @html + N'<tr><td>' + publisher_db + N'</td><td>' + publication

+ N'</td><td>' + subscriber_db + N'</td><td>' + Status + N'</td></tr>'

FROM #MonitorRepl

ORDER BY publisher_db, publication, subscriber_db;

END

ELSE

BEGIN

SET @html = @html + N'<tr><td colspan="4">No replication agent rows found.</td></tr>';

END

SET @html = @html + N'</table>' + @newline;

/* 6) Cleanup */

IF OBJECT_ID('tempdb..#MonitorRepl') IS NOT NULL DROP TABLE #MonitorRepl;

IF OBJECT_ID('tempdb..#JobLast') IS NOT NULL DROP TABLE #JobLast;

IF OBJECT_ID('tempdb..#MM') IS NOT NULL DROP TABLE #MM;

IF OBJECT_ID('tempdb..#DA') IS NOT NULL DROP TABLE #DA;

END

ELSE

BEGIN

SET @html = @html + N'<p>No replication activity object (MSreplication_monitordata) found in distribution database.</p>' + @newline;

END

ELSE

BEGIN

SET @html = @html + N'<p>No replication configured.</p>' + @newline;

END

-- =========================

-- Always On Availability Groups

-- =========================

SET @html = @html + N'<h3>Always On Availability Groups</h3>';

IF EXISTS (SELECT 1 FROM sys.availability_groups)

BEGIN

SET @html = @html + N'<table><tr>

<th>Availability Group</th>

<th>Replica Server</th>

<th>Database</th>

<th>Sync State</th>

<th>Health</th>

</tr>';

;WITH ags AS (

SELECT DISTINCT

ag.name AS AGName,

ar.replica_server_name AS ReplicaServer,

ars.role_desc AS RoleDesc,

adc.database_name AS DBName,

dhs.synchronization_state_desc AS SyncState,

dhs.synchronization_health_desc AS Health

FROM sys.availability_groups ag

JOIN sys.availability_replicas ar

ON ag.group_id = ar.group_id

JOIN sys.dm_hadr_availability_replica_states ars

ON ar.replica_id = ars.replica_id

JOIN sys.availability_databases_cluster adc

ON ag.group_id = adc.group_id

JOIN sys.dm_hadr_database_replica_states dhs

ON ars.replica_id = dhs.replica_id

AND dhs.group_database_id = adc.group_database_id

)

SELECT @html = @html + N'<tr><td>' + AGName + N'</td><td>' + ReplicaServer + N'</td><td>' +

RoleDesc + N'</td><td>' + DBName + N'</td><td>' + SyncState + N'</td><td>' + Health + N'</td></tr>'

FROM ags

ORDER BY AGName, ReplicaServer, DBName;

SET @html = @html + N'</table>' + @newline;

END

ELSE

BEGIN

SET @html = @html + N'<p>No Always On Availability Groups configured.</p>' + @newline;

END

-- =========================

-- HTML Footer

-- =========================

SET @html = @html + N'</body></html>';

-- =========================

-- Send Email

-- =========================

DECLARE @sub VARCHAR(MAX);

SET @sub = 'Alert: SQL Server Health Check Report - ' + CAST(SERVERPROPERTY('MachineName') AS VARCHAR(128));

EXEC msdb.dbo.sp_send_dbmail

@profile_name = 'Default',

@recipients = 'DBA@sqlserver.com',

@subject = @sub,

@body = @html,

@body_format = 'HTML';

The server principal owns one or more server role(s) and cannot be dropped. (Microsoft SQL Server, Error: 15141)

noreply@blogger.com (Sai Sreenu) — Mon, 5 May 2025 11:51:00 +0530

Some time we are trying to delete or drop a login from SQL server instance , we encountered with below error.

The server principal owns one or more server role(s) and cannot be dropped. (Microsoft SQL Server, Error: 15141)

Step 1) Find the server roles owned by login using below script

SELECT sp1.name AS ServerRoleName,

sp2.name AS RoleOwnerName

FROM sys.server_principals AS sp1

JOIN sys.server_principals As sp2

ON sp1.owning_principal_id=sp2.principal_id

WHERE sp2.name='Test' --Change the login name

Step 2 ) Change server role to another login

USE [master]

ALTER AUTHORIZATION ON SERVER ROLE :: Admin to SQLAdmin

Step 3) Drop login

USE [master]

Drop Login Test

Difference between NOLOCK and READPAST

noreply@blogger.com (Sai Sreenu) — Wed, 23 Apr 2025 15:46:00 +0530

NOLOCK

========

When we use NOLOCK in select command it will allow to read rows are available on dirty pages/uncommitted from buffer cache

Ex: i have table called replication with 4 records

update one record with out commit

in another session i try to select table, it not showing any result

Tried with NOLOCK , it resulting with dirty pages

READPAST

=========

it will not read rows that are locked by other transactions. When READPAST is specified, row-level locks are skipped.

SELECT * FROM [replication] with(readpast)

it will skip modified records until transaction will commit

Error: Database cannot be opened. It is in the middle of a restore

noreply@blogger.com (PRADYOTHANA) — Tue, 22 Apr 2025 17:57:00 +0530

This may be a bug in SSMS, after restoring FULL and LOG backups when we start mirroring we get this error - To solve execute bellow commands so that mirroring will start -

First In Mirror instance:

ALTER DATABASE DBNAME
    SET PARTNER =   
    'TCP://PRINCIPAL SERVER.DOMAIN:MIRRORING PORT NUMBER'

In Principal instance:

ALTER DATABASE DBNAME
    SET PARTNER =   
    'TCP://MIRRIR SERVER.DOMAIN:MIRRORING PORT NUMBER'

Restore oracle database on development server from production server using RMAN backup (Restore and Recovery Method)

noreply@blogger.com (Sai Sreenu) — Mon, 7 Apr 2025 13:13:00 +0530

Step 1) Copy Backup copies from Production to Development server using cp command

[Source server Backup]$ scp -p bkup_j93m9af4_95849_1_1 bkup_ja3m9ahh_95850_1_1 bkup_jb3m9aju_95851_1_1 bkup_jc3m9amb_95852_1_1 bkup_jd3m9aqj_95853_1_1 bkup_je3m9aql_95854_1_1 bkup_jf3m9aqn_95855_1_1 c-87645678-20250407-00 oracle@Target server:<Backup Location>

Step 2) Once copy done connect to target server verify spfile and create pfile from spfile

[oracle@sreenu ~]$ !sq

sqlplus / as sysdba

SQL*Plus: Release 19.0.0.0.0 - Production on Mon Apr 7 08:56:30 2025

Version 19.21.0.0.0

Connected to:

Oracle Database 19c Enterprise Edition 2 Release 19.0.0.0.0 - Production

Version 19.21.0.0.0

SQL>

SQL> sho parameter spfile

NAME TYPE VALUE

------------------------------------ ----------- ------------------------------

spfile string /u01/opt/oracle/dbhome/dbs/spfilePROD.ora

SQL> create pfile from spfile;

File created.

Step 3) Check Listener status and stop Listener on Target server ( It will not allow incoming connections to connect database)

[oracle@sreenu ~]$ lsnrctl status

[oracle@sreenu ~]$ lsnrctl stop

Step 4) Drop Database on target server (Before drooping the database take full backup of the development server )

Note1:Only we can drop/delete database on mount mode only

Note2:Before drooping the database we need to shutdown database and startup restrict mode

SQL> shut immediate

Database closed.

Database dismounted.

ORACLE instance shut down.

SQL> startup restrict mount;

ORACLE instance started.

Total System Global Area 5905576504 bytes

Fixed Size 8938040 bytes

Variable Size 3909091328 bytes

Database Buffers 1979711488 bytes

Redo Buffers 7835648 bytes

Database mounted.

SQL> select name,open_mode from v$database;

NAME OPEN_MODE

--------- --------------------

PROD MOUNTED

SQL> drop database;

Database dropped.

Step 5) Startup database using pfile in nomount mode.

SQL> startup nomount;

ORACLE instance started.

Total System Global Area 5905576504 bytes

Fixed Size 8938040 bytes

Variable Size 3909091328 bytes

Database Buffers 1979711488 bytes

Redo Buffers 7835648 bytes

SQL>

SQL> sho parameter pfile;

NAME TYPE VALUE

------------------------------------ ----------- ------------------------------

spfile string

SQL>

Note: Database started using by pfile.

Step 6) Restore controlfile from backup using RMAN tool / Prompt

[oracle@czprg-tcora303 PROD]$ rman target /

connected to target database: PROD (not mounted)

RMAN>

RMAN> restore controlfile from '/u01/opt/oracle/rman_bkup/PROD/c-87645678-20250407-00';

Starting restore at 07-APR-25

using target database control file instead of recovery catalog

allocated channel: ORA_DISK_1

channel ORA_DISK_1: SID=21 device type=DISK

channel ORA_DISK_1: restoring control file

channel ORA_DISK_1: restore complete, elapsed time: 00:00:03

output file name=/u01/opt/oracle/control_files/control01.ctl

output file name=/u01/opt/oracle/control_files/control02.ctl

output file name=/u01/opt/oracle/mpx_PROD/control_files/control03.ctl

Finished restore at 07-APR-25

RMAN>

Step 7) Once controlfile restored we need to bring database to mount mode.

RMAN> alter database mount;

released channel: ORA_DISK_1

Statement processed

RMAN>

Step 8) restore database.

RMAN> restore database;

Step 9) Once restore done , we need to recovery the database.

RMAN> recover database;

Step 10) Once recovery done , we need to open database using resetlogs.

RMAN> alter database open resetlogs;

Statement processed

RMAN>

Step 11) Once restore and recovery done , we need to start database with spfile

Note: Always run database using spfile only.

Note: To change database from pfile to spfile, need to create spfile from pfile and bounce the database.

SQL> create spfile from pfile;

File created.

SQL> shut immediate

Database closed.

Database dismounted.

ORACLE instance shut down.

SQL> startup

ORACLE instance started.

Total System Global Area 5905576504 bytes

Fixed Size 8938040 bytes

Variable Size 3909091328 bytes

Database Buffers 1979711488 bytes

Redo Buffers 7835648 bytes

Database mounted.

Database opened.

SQL>

Step 12) Verify Database mode and its running with spfile or not.

SQL> sho parameter spfile

NAME TYPE VALUE

------------------------------------ ----------- ------------------------------

spfile string /u01/opt/oracle/dbhome/dbs/spfilePROD.ora

SQL> select name,open_mode,log_mode from v$database;

NAME OPEN_MODE LOG_MODE

--------- -------------------- ------------

PROD READ WRITE ARCHIVELOG

SQL>

Step 13) Start Listener and check status

[oracle@sreenu ~]$ lsnrctl start

[oracle@sreenu ~]$ lsnrctl status

Step 14) Ask Application team to verify from application end.

Grant select access to specific object or tables

noreply@blogger.com (Sai Sreenu) — Fri, 4 Apr 2025 23:43:00 +0530

We can grant select access to specific Tables / Objects in three ways

Create Login on SQL server instance

In my case Login name Appteam

Don't Grant any server role to Login

I need fetch data from TEST_Mirr database

Don't grant any database role

Default public will be there

On Database user will be created automatically

Here for Appteam not showing any tables on the TEST_Mirr database

1) using T SQL

Granted select on below two tables for Appteam user

use TEST_Mirr

Grant select on conn1 to Appteam

Grant select on conn2 to Appteam

2) Using GUI

Using Admin session go to user properties

Click on search and select all objects of the types and click ok , it will pop up new dialog box

Select Tables and click OK

This time i am going grant select access on Output_Field table

After granting select permissions on Output_Field table

3) Using Table Properties

For which we need to grand select on the table go to that table properties

Go to permissions tab >>> search for a user >>> grant select as shown in below screenshots

Now we can see all the tables which we granted select access

The feature “Scale-out deployment” is not supported in this edition of Reporting Services

noreply@blogger.com (PRADYOTHANA) — Mon, 9 Dec 2024 15:05:00 +0530

Step1 :Connect to SSMS

Step2: Open New query window

Step3: run below query

Use Report server

Select * from Keys

Step4: List all the keys that needs to be deleted, except the NULL one.

Note: Don't delete NULL this is current one.

Step5: Execute below command

Delete from Keys where instalationID=<>

Step 6: Restart SSRS

SQL Server Performance issue - With DB Compatibility upgrade from 120 to 150

noreply@blogger.com (PRADYOTHANA) — Sun, 27 Oct 2024 17:07:00 +0530

Worked Solution:

We turned on the Legacy Cardinality Estimation on the database properties.

If anyone has other solutions please comment.

SQL Server 2022 Services Failed To Come Online.

noreply@blogger.com (PRADYOTHANA) — Wed, 24 Jan 2024 16:30:00 +0530

Error: Script level upgrade for database ‘master’ failed because upgrade step msdb110_upgrade.sql encountered error 926, state 1, severity 25

The above error will be present in the Error log and the solution will be to start the SQL Server either from /T902 or add this in strtup parameter to permently resolve this issue.

This will bypass the upgrade script and starts the SQL Server.

Reference:

https://blog.sqlauthority.com/2019/04/04/sql-server-script-level-upgrade-for-database-master-failed-because-upgrade-step-msdb110_upgrade-sql-encountered-error-8649-state-1-severity-17/