DBA WORLD

20 August 2025

SQL Server Configuration Manager Missing

Go to respective shared folder and register -

27 July 2025

SQL Server Daily Health Check - Script

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

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';

05 May 2025

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

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

23 April 2025

Difference between NOLOCK and READPAST

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

22 April 2025

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

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'

07 April 2025

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

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.

04 April 2025

Grant select access to specific object or tables

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

DBA WORLD

Ads

20 August 2025

SQL Server Configuration Manager Missing

27 July 2025

SQL Server Daily Health Check - Script

05 May 2025

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

23 April 2025

Difference between NOLOCK and READPAST

22 April 2025

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

07 April 2025

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

04 April 2025

Grant select access to specific object or tables

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