Its all about Database

Prometheus NSSM Windows Exporter and Grafana

2026-03-08T10:35:00.003+05:30

prometheus.exe --config.file=prometheus.yml

Access is denied.

https://wingetgui.com/apps/Prometheus-WMIExporter?utm_source=chatgpt.com

cd C:\Prometheus

prometheus.exe --config.file=prometheus.yml

http://localhost:9090

Download NSSM

--Correct command

R:\nssm-2.24\win64\nssm.exe install prometheus

Path

C:\Prometheus\prometheus.exe

Startup Dir

C:\Prometheus

Argument

--config.file=prometheus.yml

R:\nssm-2.24\win64\nssm.exe install prometheus C:\Prometheus\prometheus.exe --config.file=C:\Prometheus\prometheus.yml

--Final Architecture

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

Windows Server

↓

Windows Exporter (9182)

↓

Prometheus (9090)

↓

Grafana (3000)

↓

Monitoring Dashboard

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

windows_exporter-0.24.0-amd64.msi

2️⃣ Select collectors (recommended)

Enable:

cpu

memory

logical_disk

net

service

system

Default port 9182

http://localhost:9182/metrics

Add to Prometheus

scrape_configs:

- job_name: "windows"

static_configs:

- targets: ["192.168.1.10:9182"]

Test in Prometheus

Open Prometheus UI and run query:

windows_cpu_time_total

If you are monitoring multiple Windows servers, install windows_exporter on each server.

Azure DTU

2025-09-28T11:18:00.001+05:30

In Azure SQL Database, DTU stands for Database Transaction Unit — it’s a performance metric that combines CPU, memory, reads, and writes into a single measure.

Think of it as a “power level” — the higher the DTU, the more workload the database can handle.

How to Check DTU Usage

You can monitor DTU utilization via:

Azure Portal → SQL Database → Monitoring → DTU usage (%)

Query via T-SQL

SELECT * FROM sys.dm_db_resource_stats ORDER BY end_time DESC;

You can estimate your DTU requirement using Microsoft’s DTU Calculator:

https://dtucalculator.azurewebsites.net

fn_my_permissions

2025-07-10T23:02:00.008+05:30

fn_my_permissions ( securable , 'securable_class' )

Value	Description
`DATABASE`	Permissions on the current database
`OBJECT`	Table, View, Stored Procedure, etc.
`SCHEMA`	Database schema
`SERVER`	Server-level permissions

SELECT * FROM fn_my_permissions(NULL, 'DATABASE');

SELECT * FROM fn_my_permissions(NULL, 'SERVER');

SELECT * FROM fn_my_permissions('dbo.YourTableName', 'OBJECT');

Monitoring SQL Server Deadlocks using Extended Events

2025-05-06T11:18:00.001+05:30

Extended Events were first introduced in SQL Server 2008, and therefore you will not face any problem implementing them in our environment.

Extended Events is a lightweight performance monitoring feature that enables users to collect data to monitor and troubleshoot problems.

A deadlock is a situation where two or more processes or threads are blocked indefinitely, waiting for each other to release resources that they need to proceed.

Here we are going to observe how to gather deadlock information through Extended Events :

Creating SQL Server Extended Events to Capture Deadlocks using Management Studio

Step 1: First, open SQL Server Management Studio (SSMS) and navigate to Management > Extended Events > Sessions.

Step 2: Right-click on Sessions and select New Session.

Step 3: In the new window, provide a name for your event, we will call it SQLWATCH_deadlock in this case. Select the event to start at the beginning of the server and when the session is created, you can change these settings if necessary.

Step 4: Go to the Events tab and in the event library textbox type “deadlock” to filter deadlock events:

Step 5: We will select 2 events: Lock_Deadlock (Raised when a request to acquire a lock is cancelled for the victim of a deadlock) and Lock_deadlock_chain (Raised when a request to acquire a lock results in a deadlock. This event is raised for all members of the deadlock).

Step 6: Having both the events chosen, click on the configure button, and a new window will be opened, in this window we will take a snapshot of the sql_text field so that we can view the query that generated the deadlock

Step 7: In the Data Storage tab, select where you want to store the Extended Event data use a file.

Step 8: After that, click on OK to save the Event configuration, we can see that the event is created and is already running.

The operating system returned error 59(An unexpected network error occurred.) to SQL Server during a read at offset 0x0000 in file.

2025-02-05T16:34:00.005+05:30

Error Message

Msg 823, Level 24, State 2, Procedure tables, Line 2 [Batch Start Line 2]

The operating system returned error 59(An unexpected network error occurred.) to SQL Server during a read

at offset 0x00004675548000 in file '\\Server_Name\D2A_DB\MSSQL\Data\HISTORICAL_NDF_1.ndf'.

Additional messages in the SQL Server error log and operating system error log may provide more detail.

This is a severe system-level error condition that threatens database integrity and must be corrected

immediately. Complete a full database consistency check (DBCC CHECKDB). This error can be caused by many

factors; for more information, see SQL Server Books Online.

Cause

The Windows Session Timeout is 60 s by default. This timeout is dependent not only on Data Domain response time,

but also any network latency.

If there is a slow network or longer than expected Data Domain processing (such as rapid index expansion under

a heavy backup load), Windows resets the connection causing backup errors.

PS C:\Windows\system32> get-SmbClientConfiguration

Resolution

Increase the Windows Session Timeout to a higher value such as 180 s by running the following command:

PS C:\Windows\system32> Set-SmbClientConfiguration -SessionTimeout 180 -Force

Mysql Replication

2024-08-01T13:17:00.001+05:30

MySQL database servers (slave)-

I/O thread and SQL Thread-

Replication I/O threads-

When a start slave statement is issued on a replica server, the replica creates an I/O thread, which connects to the source and asks it to send the updates recorded in its binary logs. The replication I/O thread reads the updates that the source's Binlog Dump thread sends and copies them to local files that comprise the replica's relay log.

The state of this thread is shown as Slave_IO_running in the output of show slave status.

Replication SQL threads-

The replica creates an SQL thread to read the relay log that is written by the replication I/O thread and execute the transactions contained in it.

There are three main threads for each source/replica connection. A source that has multiple replicas creates one binary log dump thread for each currently connected replica, and each replica has its own replication I/O and SQL threads.

Binary log dump thread-

The source creates a thread to send the binary log contents to a replica when the replica connects. This thread can be identified in the output of show process list on the source as the Binlog Dump thread.

The binary log dump thread acquires a lock on the source's binary log for reading each event that is to be sent to the replica. As soon as the event has been read, the lock is released, even before the event is sent to the replica.

Step 1-

Step 2-

To Check the server id and binary logfile.

Step 3-

Open the MySQL configuration file and config.

Gtid_mode=ON

enforce_gtid_consistency=ON

server id=100

log-bin=gtidsource

Step 4 –

Restart MySQL service.

Step 5-

Now check global variable gtid, server_id and binary log.

Step 6-

Create a user in master.

Create user repl@’%’ identified by ‘Rainbow@123’

Step 7-

check global variable gtid in replica.

Step 8-

Check server_id and binary_log in replica.

Step 9-

Now check replica status.

Step 10-

Change Master to master user.

mysql> change master to master_user='repl',master_password='Secure@321’, master_host='192.168.0.195',master_auto_position=1,get_master_public_key=1;

Query OK, 0 rows affected, 8 warnings (0.02 sec)

mysql> start replica;

Query OK, 0 rows affected (0.01 sec)

mysql> show replica status \G

*************************** 1. row ***************************

Replica_IO_State: Waiting for source to send event

Source_Host: 192.168.0.195

Source_User: repl

Source_Port: 3306

Connect_Retry: 60

Source_Log_File: gtidsource.000003

Read_Source_Log_Pos: 157

Relay_Log_File: clota-dbl06-relay-bin.000002

Relay_Log_Pos: 375

Relay_Source_Log_File: gtidsource.000003

Replica_IO_Running: Yes

Replica_SQL_Running: Yes

Replicate_Do_DB:

Replicate_Ignore_DB:

Replicate_Do_Table:

Replicate_Ignore_Table:

Replicate_Wild_Do_Table:

Replicate_Wild_Ignore_Table:

Last_Errno: 0

Last_Error:

Skip_Counter: 0

Exec_Source_Log_Pos: 157

Relay_Log_Space: 591

Until_Condition: None

Until_Log_File:

Until_Log_Pos: 0

Source_SSL_Allowed: No

Source_SSL_CA_File:

Source_SSL_CA_Path:

Source_SSL_Cert:

Source_SSL_Cipher:

Source_SSL_Key:

Seconds_Behind_Source: 0

Source_SSL_Verify_Server_Cert: No

Last_IO_Errno: 0

Last_IO_Error:

Last_SQL_Errno: 0

Last_SQL_Error:

Replicate_Ignore_Server_Ids:

Source_Server_Id: 100

Source_UUID: 25474780-4a56-11ef-b77c-000c2926dc41

Source_Info_File: mysql.slave_master_info

SQL_Delay: 0

SQL_Remaining_Delay: NULL

Replica_SQL_Running_State: Replica has read all relay log; waiting for more updates

Source_Retry_Count: 86400

Source_Bind:

Last_IO_Error_Timestamp:

Last_SQL_Error_Timestamp:

Source_SSL_Crl:

Source_SSL_Crlpath:

Retrieved_Gtid_Set:

Executed_Gtid_Set: 89ffa9fd-4a4c-11ef-8d4a-000c2995d827:1-4

Auto_Position: 1

Replicate_Rewrite_DB:

Channel_Name:

Source_TLS_Version:

Source_public_key_path:

Get_Source_public_key: 1

Network_Namespace:

1 row in set (0.00 sec)

Step 11-

After enabaling gtid.

Step 12-

Create a database and check the global variable gtid.

Step 13-

Check server_uuid.

Step 14-

Change database and check global variable gtid in replica.

Step 15-

In replica use f1 database and check the table.

Install MySQL 8 on Oracle Linux

2024-07-29T19:05:00.006+05:30

Installing MySQL on Oracle Linux is simple and straightforward. Download the latest version of MySQL based on your OS version.

Download via Command Line (optional):

Use wget or curl to download the file directly if you prefer using the command line

wget https://downloads.mysql.com/archives/get/p/23/file/mysql-8.0.30-linux-glibc2.12-x86_64.tar.xz

Change to the directory where you downloaded the tarfile:

cd /test

Extract the tarfile using tar:

tar -xvf mysql-8.0.30-linux-glibc2.12-x86_64.tar.xz

Now Please install all the package Step by Step :

Step 1 :Install mysql-commercial-common

yum install mysql-commercial-common-8.0.32-1.1.el9.x86_64.rpm

Step 2 :Install mysql-commercial-client-plugins

yum install mysql-commercial-client-plugins-8.0.32-1.1.el9.x86_64.rpm

Step 3 :Install mysql-commercial-libs

yum install mysql-commercial-libs-8.0.32-1.1.el9.x86_64.rpm

Step 4 :Install mysql-commercial-client

yum install mysql-commercial-client-8.0.32-1.1.el9.x86_64.rpm

Step 5 :Install mysql-commercial datafile

yum install mysql-commercial-icu-data-files-8.0.32-1.1.el9.x86_64.rpm

Step 6 :Install MySQL server:

yum install mysql-commercial-server-8.0.32-1.1.el9.x86_64.rpm

Start and enable the MySQL service:

systemctl start MySQL

systemctl enable MySQL

Run the Mysql_secure_installation script to secure your installation:

mysql_secure_installation

Check MySQL Status verify that MySQL is running:

systemctl status MySqld

Access MySQL connect to MySQL using the root account:

mysql -u root –p

Check the temporary password for MySQL:

grep 'temporary password' /var/log/mysqld.log

/usr/bin/mysql_secure_installation

Securing the MySQL server deployment:

Enter password for user root: <enter temp password>

The existing password for the user account root has expired. Please set a new password.

New password: <give new password>

Re-enter new password: <re-enter new password>

Connect to MySQL and check the databases:

mysql -u root -p

password ******

mysql> show databases;

Network actual bandwidth between servers.

2024-07-28T16:18:00.009+05:30

Iperf:

Download the tool from https://iperf.fr/iperf-download.php

Keep it on both source and target server.

On one server (server), run:

Copy code

iperf3 -s

On server (server2){Cline machine}, run:

Copy code

iperf3 -c server1_IP

Note: make sure to open port 5201 on the server.

2024-04-08T21:28:00.000+05:30

Cloning In Oracle

In the context of Oracle databases, "cloning" typically refers to the process of creating an exact replica of a database, usually for purposes such as testing, development, or disaster recovery. There are several methods for cloning an Oracle database, each with its own advantages and use cases. Here's an overview of some common cloning methods in Oracle:

RMAN (Recovery Manager) Cloning: RMAN is a powerful Oracle utility for backup and recovery operations. With RMAN, you can create a clone of a database by taking a backup of the source database and then restoring it to a different location. RMAN also provides options for efficiently copying data files and performing point-in-time recovery if needed.

RMAN Client: The RMAN client is the interface through which database administrators interact with RMAN to perform backup, recovery, and cloning tasks. Administrators use RMAN commands and scripts to initiate and manage database operations.

Control File: The control file is a critical component of the Oracle database that stores metadata about the database structure, including data file locations, redo log file information, and backup details. RMAN interacts with the control file to obtain information necessary for backup and recovery operations.

RMAN Repository: RMAN maintains a repository that stores metadata and configuration information related to backup and recovery operations. The repository can be stored in either the control file, a recovery catalog (a separate database schema created specifically for RMAN metadata), or both.

Channel: RMAN channels are mechanisms for communicating between the RMAN client and the database server during backup and recovery operations. Channels can be allocated for different tasks, such as reading data files, writing backup sets, or performing parallel operations.

Media Management Software: RMAN relies on media management software to perform backup and restore operations to physical storage devices, such as tape drives, disk arrays, or cloud storage. Media management software provides the interface between RMAN and the storage devices, handling tasks such as data transfer, compression, and encryption.

Data Files: Data files are the primary storage components of an Oracle database, containing user data, indexes, and other database objects. RMAN interacts with data files to back them up, restore them, and create copies during cloning or recovery operations.

Redo Logs: Redo logs are files that record changes made to the database, enabling recovery of transactions in the event of a failure. RMAN uses redo logs during recovery operations to apply changes and bring the database to a consistent state.

Backup Sets and Image Copies: RMAN can create backups in the form of backup sets (collections of data blocks) or image copies (exact duplicates of data files). These backups are stored in the configured backup destination, such as disk or tape.

Recovery Catalog (Optional): In addition to the control file repository, RMAN can use a recovery catalog database to store metadata and configuration information. The recovery catalog provides additional flexibility and management capabilities, especially in environments with multiple databases.

Data Pump Export/Import: Oracle Data Pump is another utility that allows you to export and import database objects and data. You can use Data Pump to export the schema(s) or the entire database from the source environment and then import it into the target environment. This method is suitable for smaller databases or when you need to clone specific schemas.

Export Process:

1. When you run expdp, it connects to the source database and reads metadata about the objects to be exported.

2. It then creates one or more dump files containing the exported data and metadata.

3. These dump files are stored in the specified directory.

expdp username/password@database_name schemas=schema_name directory=dir_name dumpfile=export.dmp

Data Pump Import (impdp):

Importing Data:

Data Pump Import (impdp) is used to import data and metadata into Oracle databases.

It reads the dump files created by expdp and loads the data and metadata into the target database.

Similar to expdp, you can import entire databases, specific schemas, tablespaces, or individual tables.

Import Process:

When you run impdp, it connects to the target database and reads the dump files created by expdp.

It then loads the data and metadata into the target database based on the specified parameters.

You can control various aspects of the import process such as parallelism, data transformation, error handling, etc.

impdp username/password@database_name directory=dir_name dumpfile=export.dmp schemas=schema_name

Cold Backup: This method involves shutting down the source database, copying its data files to the target location, and then starting up the database in the target environment. While this approach is straightforward, it requires downtime for the source database during the cloning process.

Oracle Database Cold Backup:

Oracle Cold Database backup is rarely used these days. DBAs hardly take cold database backup but sometimes it is important to take one before decommissioning a database.

• Key points

• Take Cold Backup

Key Points

• Backup is a copy of original data which will be used to recover databases

• If the data is reproducible and backup not existing, still we can recover the data. But it is a tedious and time consuming task

• Taking backup after shutting down the database is called cold backup and because no transactions exist, the backup will be consistent

• In real time, we will perform cold backup very rarely

Take Cold Backup

SQL> select name from v$datafile;

SQL> select member from v$logfile;

SQL> select name from v$controlfile;

SQL> shutdown immediate

[oracle@server1 ~]$ mkdir /u03/coldbkp

[oracle@server1 ~]$ cp /datafiles/prod/*.dbf /u03/coldbkp

[oracle@server1 ~]$ cp /datafiles/prod/*.log /u03/coldbkp

[oracle@server1 ~]$ cp /datafiles/prod/*.ctl /u03/coldbkp

[oracle@server1 ~]$ cp $ORACLE_HOME/dbs/*.ora /u03/coldbkp

[oracle@server1 ~]$ sqlplus "/ as sysdba"

SQL> startup

SQL> alter database backup controlfile to trace;

Archives are not required to be copied with cold backup.

Oracle GoldenGate: Oracle GoldenGate is a data replication and integration tool that enables real-time data movement between heterogeneous systems. It can be used for database cloning by continuously capturing changes from the source database and applying them to the target database, keeping the two databases in sync.

Real-time Data Integration: GoldenGate captures changes made to data in source systems and delivers them in real-time to target systems, ensuring that the data is synchronized across multiple databases and platforms.

High Availability and Disaster Recovery: GoldenGate provides solutions for high availability and disaster recovery by replicating data in real-time from primary systems to standby systems, allowing for rapid failover and minimal data loss in case of system failures.

Data Warehousing and Reporting: GoldenGate facilitates data replication and consolidation for data warehousing and reporting purposes, enabling organizations to analyze data from multiple sources in real-time.

Data Migration and Upgrades: GoldenGate can be used to migrate data between different database platforms or versions with minimal downtime, allowing organizations to upgrade their systems or move data to new environments seamlessly.

Business Intelligence and Analytics: GoldenGate enables real-time data feeds for business intelligence and analytics applications, providing up-to-date information for decision-making processes.

Here are some key features and components of Oracle GoldenGate:

Extract Process: The Extract process captures data changes (inserts, updates, deletes) from the source database's transaction logs (redo logs or archive logs) in real-time.

Data Pump Process: The Data Pump process collects captured data from the Extract process and writes it to trail files.

Replicat Process: The Replicat process reads data from trail files and applies it to the target database, ensuring that changes are replicated accurately and efficiently.

Manager Process: The Manager process is responsible for managing GoldenGate processes, monitoring their status, and handling administrative tasks.

Trail Files: Trail files are used to store captured data changes in a platform-independent format, allowing for easy transfer and replication of data between different systems.

Checkpointing: GoldenGate uses checkpoints to track the progress of data replication, ensuring that transactions are replicated in the correct order and consistency is maintained between source and target systems.

Oracle Database Cloning Tools: Oracle offers additional tools such as Oracle Database Cloning Assistant for Standalone Database, Oracle Enterprise Manager (EM) Clone Database, and Oracle Cloud Control Database Clone feature. These tools provide user-friendly interfaces and automation capabilities for cloning databases.

Oracle provides several tools and methods for cloning databases, each suited for different scenarios and requirements. Here are some commonly used Oracle database cloning tools:

RMAN (Recovery Manager):

RMAN can be used for creating clones of databases using the DUPLICATE command.

It allows for point-in-time cloning, enabling you to create a clone of the database at a specific moment.

RMAN cloning can be disk-based or tape-based, depending on the backup destination.

RMAN provides flexibility in terms of customization and automation during the cloning process.

Oracle Enterprise Manager (OEM):

Oracle Enterprise Manager provides a graphical user interface for managing Oracle databases, including cloning operations.

OEM allows for easy and intuitive cloning of databases with minimal manual intervention.

It provides features for scheduling and monitoring cloning jobs, as well as managing post-cloning tasks.

Data Pump Export/Import:

Oracle Data Pump is a utility for exporting and importing database objects and data.

It can be used for cloning by exporting data from the source database and importing it into a new database instance.

Data Pump export/import is suitable for smaller databases or when specific customization is required during the cloning process.

Manual Cloning (Cold Cloning):

Manual cloning involves manually copying database files (data files, control files, redo logs, etc.) from the source database to the target location.

This method is typically used for non-production environments or when other cloning methods are not feasible.

Manual cloning requires downtime for the source database during the copy process and may not be suitable for large databases.

Snap Clone:

Oracle Snap Clone is a feature available with Oracle Enterprise Manager and Oracle Database 12c and later versions.

It enables rapid provisioning of database clones using storage snapshots.

Snap Clone leverages storage technologies to create space-efficient copies of databases, minimizing storage requirements and improving cloning speed.

Snapshot Cloning: Some storage solutions offer snapshot-based cloning, where a point-in-time snapshot of the source database's storage is taken, and clones are created from that snapshot. This method can be very fast and efficient but requires compatible storage infrastructure.

When choosing a cloning method, consider factors such as database size, downtime requirements, data consistency, and available resources. It's also essential to carefully plan and test the cloning process to ensure the integrity of the cloned database and minimize any potential disruptions to production environments.

Correlated Subquery

2024-03-14T14:41:00.002+05:30

Correlated Subquery

Introduction-

A correlated subquery is a SQL query in which the values of the inner query (subquery) are derived from the outer query. Essentially, the subquery is conducted many times, one for each row processed by the outer query. This makes correlated subqueries very useful for complex data retrieval scenarios, but they can also be more performance-intensive than non-correlated subqueries due to repeated execution.

Here's a step-by-step explanation to understand correlated subqueries in a structured way:

1. Basic Structure-

A correlated subquery typically looks something like this:

SELECT column_name(s)

FROM table_name AS outer_table

WHERE column_name OPERATOR

(SELECT column_name

FROM table_name AS inner_table

WHERE outer_table.column = inner_table.column);

2. Key Components

Outer Query-

This is the main query that first gets executed up to the point of the subquery. It retrieves data from the outer table.

Inner Query (Subquery)-

This query is nested inside the outer query. Unlike a regular subquery, it references columns from the outer query, creating a dependency.

Correlation-

The correlation happens through the condition that links the inner and outer queries (e.g., outer_table.column = inner_table.column). This condition is what makes the subquery "correlated" because it must reference a column from the outer query for each row it processes.

3. Execution Flow-

The outer query starts executing.

For each row in the outer query, the inner (correlated) subquery executes.

The subquery uses a value from the current row of the outer query to perform its operation.

The result of the subquery is used to complete the operation of the outer query for the current row.

Steps 2-4 repeat for each row processed by the outer query.

4. Use Cases

Correlated subqueries are useful for-

Row-by-row operations-

When you need to perform calculations or comparisons on each row individually.

Existence checks-

To check if some related data exists in another table for each row.

Aggregate calculations-

Such as finding the minimum, maximum, average, etc., of grouped data specific to each row in the outer query.

5. Performance Considerations-

Since the subquery maybe executed once for each row in the outer query, correlated subqueries can be slow, especially on large datasets. It's often recommended to look for

alternatives, such as JOINs or temporary tables, if performance becomes an issue.

Example

Consider two tables, employees and department, where you want to find the names of employees who earn more than the average salary in their respective departments-

SELECT e.name, e.salary

FROM employees e

WHERE e.salary > (

SELECT AVG(salary)

FROM employees

WHERE department_id = e.department_id

);

In this example, for each employee in the outer query, the subquery calculates the average salary of their department and checks if their salary is above this average. The correlation between the outer and inner query is established through department_id.

2024-03-11T19:52:00.001+05:30

Database corruption

Database corruption in Oracle can occur due to various reasons, such as hardware failures, software bugs, or issues during database operations. It's crucial to identify and address corruption promptly to ensure data integrity. Here are some steps you can take to deal with database corruption in Oracle:

Physical corruption: This happens generally due to media corruption. In this case, the block does have an invalid checksum. It may contain zero. This is generally the result of infrastructure issues like media, network, and memory.

RMAN block recovery can be used to remove physical corruption

We see the error ORA-01578: ORACLE data block corrupted in the alert log when Oracle detects physical corruption. Oracle detects corruption by checking the block header and block footer.

Logical corruption: This happens when Oracle find the block header and footer good but it finds internal consistency in the block. Generally, it happens due to some bug in Oracle software. When Oracle detects it, it throws the error in the alert log.

ORA-08103 is a logical corruption error for a particular data block.
ORA-00600 [2662] – This error is related to block corruption and occurs due to a higher SCN than database SCN.

How to check block corruption in Oracle database

Block corruption in Oracle Database can be found using the below method

RMAN

· Checking alert log for possible occurrence

dbverify
Export
Analyze command

Using RMAN Run below command

RMAN> backup validate check logical database;

The “CHECK LOGICAL” option is used to identify both Physical and Logical Block Corruptions.

We can use the below command in case we want to check selectively

BACKUP VALIDATE DATAFILE 1;

BACKUP VALIDATE check logical DATAFILE 1;

BACKUP VALIDATE TABLESPACE TOOLS;

BACKUP VALIDATE check logical TABLESPACE TOOLS;

BACKUP VALIDATE BACKUPSET 1;

BACKUP VALIDATE CURRENT CONTROLFILE;

For Multitenant

VALIDATE CHECK LOGICAL PLUGGABLE DATABASE test1;

Select the view to identify the corrupted blocks detected by RMAN.

QL> select * from v$database_block_corruption;

For each row, find the segment impacted

SQL> SELECT TABLESPACE_NAME, SEGMENT_NAME, PARTITION_NAME, SEGMENT_TYPE

FROM DBA_EXTENTS

WHERE FILE_ID=&FILE_ID AND

&BLOCK_ID BETWEEN BLOCK_ID AND BLOCK_ID + BLOCKS - 1;

Please note that after a corrupt block is repaired, the row identifying the block is deleted from the view.

Checking alert log for possible occurrence

Check alert. log file for corrupted blocks, data file list.

For ex:- ORA-01578: ORACLE data block corrupted (file # 11, block # 45)

ORA-01110: data file 5: '/xyz/db01.dbf'

dbverify

You can also use the dbverify utility to identify Physical and Logical Intra Block Corruptions.

dbv file=datafile_name blocksize=datafile_block_size

You can use the below command to generate the dbv command for all the datafiles

sqlplus /nolog

connect / as sysdba

set feedback off

set head off

set echo off

set linesize 200

set pagesize 2500

spool /tmp/dbvlogs/dbvchk.sh

select 'dbv file=' || name || ' blocksize='|| block_size || ' logfile=' ||

substr(name, instr(name, '/', -1, 1) +1) ||

'.' || file# || '.log' from v$datafile

spool off

In the case of RAW device

select 'dbv file='||name||' blocksize='||block_size||' end='||

(bytes/block_size)||' logfile='||

substr (name, instr (name, '/', -1, 1) + 1)||'.'||file#||'.log'

from  v$datafile;

In the case of ASM storage

select 'dbv file=' || name || ' blocksize='|| block_size || ' USERID=sys/&SYS_PASSWORD logfile=' ||

substr(name, instr(name, '/', -1, 1) +1) ||

'.' || file# || '.log' from v$datafile

Export

Using the export of a table or schema will also find the corruption in the table or schema

But the big limitation with export is that It fails immediately after encountering the first corruption., so the complete picture will be missed.

ANALYZE command

ANALYZE has been referred to as the best utility that performs maximum checks.

This is the easy utility to check the corruptions associated with an index by any means.

To check the corruption with a table do the following:

ANALYZE TABLE <OWNER.TABLE_NAME> VALIDATE STRUCTURE;

To check the corruption with an index do the following:

ANALYZE INDEX <OWNER.INDEX_NAME> VALIDATE STRUCTURE;

To check the corruption with the table and its index(s) to perform the cross-reference checkings do the following:

ANALYZE TABLE <OWNER.TABLE_NAME> VALIDATE STRUCTURE CASCADE;

For partition tables, we need to use the ANALYZE command with the INTO INVALID_ROWS option, similar to the following:

ANALYZE TABLE <OWNER.TABLE_NAME> VALIDATE STRUCTURE CASCADE INTO INVALID_ROWS;

How to Correct the Block Corruption using RMAN Blockrecover

1. Recovering Data blocks By Using All Available Backups

Run the BLOCKRECOVER command at the RMAN prompt, specifying the file and block numbers for the corrupted blocks

RMAN>BLOCKRECOVER DATAFILE 11 BLOCK 15;

Recover multiple blocks in a single command

RMAN>BLOCKRECOVER DATAFILE 11 BLOCK 15 DATAFILE 2 BLOCK 10

2. Recovering Data blocks Using Selected Backups

Run the BLOCKRECOVER command at the RMAN prompt, Specifying the data file and block numbers for the corrupted blocks and limiting the backup candidates by means of the available options. For example, specify what type of backup should be used to restore the blocks.

# restore from backupset


RMAN> BLOCKRECOVER DATAFILE 11 BLOCK 15 FROM BACKUPSET;

# Restore from datafile image copy

RMAN> BLOCKRECOVER DATAFILE 11 BLOCK 15 FROM DATAFILECOPY;

# Restore from backup set with tag “Sunday”



RMAN> BLOCKRECOVER DATAFILE 11 BLOCK 15 FROM TAG = Sunday

3 . Recovering blocks listed in V$DATABASE_BLOCK_CORRUPTION view

Run the below command to recover all blocks marked corrupt in V$DATABASE_BLOCK_CORRUPTION

RMAN> BLOCKRECOVER CORRUPTION LIST;

Restores blocks from backup sets created more than 7 days ago

RMAN> BLOCKRECOVER CORRUPTION LIST FROM BACKUPSET RESTORE UNTIL TIME 'SYSDATE-7';

How to Correct the Block Corruption Using Backup

Using data file recovery from backup or standby (if you have one)

Complete database recovery

Extended Events in SQL Server

2024-03-07T19:07:00.000+05:30

Extended Events in SQL Server

Introduction-

Extended Events (XEvents) is a highly scalable and adaptable event-driven monitoring system that debuted with SQL Server 2008. It is intended to assist database administrators (DBAs) and developers in gathering extensive information on SQL Server behavior, such as performance issues, query execution, and system diagnostics. Extended Events' architecture provides for minimum performance effect, making it appropriate for use in production applications.

Let us break down the word "Extended Events" and explain each component:

Extended-

The name indicates that this feature goes beyond typical monitoring and tracing tools such as SQL Trace and SQL Server Profiler. It has more events, targets, and configuration choices, making it more adaptable and comprehensive.

Events-

Events are locations of interest within the SQL Server engine where activities take place. Examples include query execution, login attempts, and failures. When an event occurs, the Extended Events system can collect it, along with any pertinent data.

Extended Events are based on several fundamental components:

Events-

These are the happenings in SQL Server that you want to track. Each event is assigned a unique name and contains detailed data about the occurrence.

Event Sessions-

To gather data, you must create an event session. An event session specifies which events to monitor, what data to gather, and how to store the data.

Targets-

The locations where the information gathered during an event session is kept are known as targets. Targets include things like event files, which write the data to disk files, ring buffers, which store the data in memory, and histograms, which aggregate the data according to predetermined standards.

Actions-

Further components, such as the SQL Server process ID or the execution time, can be appended to session events in order to gather further context.

Predicates-

Predicates let you filter occurrences according to particular standards, ensuring that you only gather the information you're interested in. This lessens the effect on system performance and helps to reduce the amount of data that is gathered.

Create Extended Events in SQL Server-

Let us create an Event for 5 seconds. Which query takes 5 seconds to finish will be tracked by this event.

IF EXISTS (SELECT 1 FROM sys.server_event_sessions WHERE name = 'DurationOver5Sec')

DROP EVENT SESSION [DurationOver5Sec] ON SERVER;

CREATE EVENT SESSION [DurationOver5Sec]

ON SERVER

ADD EVENT sqlserver.rpc_completed(

ACTION

(

sqlserver.client_app_name -- ApplicationName from SQLTrace

, sqlserver.client_hostname -- HostName from SQLTrace

, sqlserver.client_pid -- ClientProcessID from SQLTrace

, sqlserver.database_id -- DatabaseID from SQLTrace

, sqlserver.request_id -- RequestID from SQLTrace

, sqlserver.server_principal_name -- LoginName from SQLTrace

, sqlserver.session_id -- SPID from SQLTrace

)

WHERE

(

duration >= 5000000

)

ADD EVENT sqlserver.sql_batch_completed(

ACTION

(

sqlserver.client_app_name -- ApplicationName from SQLTrace

, sqlserver.client_hostname -- HostName from SQLTrace

, sqlserver.client_pid -- ClientProcessID from SQLTrace

, sqlserver.database_id -- DatabaseID from SQLTrace

, sqlserver.request_id -- RequestID from SQLTrace

, sqlserver.server_principal_name -- LoginName from SQLTrace

, sqlserver.session_id -- SPID from SQLTrace

)

WHERE

(

duration >= 5000000

)

ADD EVENT sqlos.wait_info(

ACTION

(

sqlserver.client_app_name -- ApplicationName from SQLTrace

, sqlserver.client_hostname -- HostName from SQLTrace

, sqlserver.client_pid -- ClientProcessID from SQLTrace

, sqlserver.database_id -- DatabaseID from SQLTrace

, sqlserver.request_id -- RequestID from SQLTrace

, sqlserver.server_principal_name -- LoginName from SQLTrace

, sqlserver.session_id -- SPID from SQLTrace

)

WHERE

(

duration > 5000 --This one is in milliseconds, and I'm not happy about that

AND ((wait_type > 0 AND wait_type < 22) -- LCK_ waits

OR (wait_type > 31 AND wait_type < 38) -- LATCH_ waits

OR (wait_type > 47 AND wait_type < 54) -- PAGELATCH_ waits

OR (wait_type > 63 AND wait_type < 70) -- PAGEIOLATCH_ waits

OR (wait_type > 96 AND wait_type < 100) -- IO (Disk/Network) waits

OR (wait_type = 107) -- RESOURCE_SEMAPHORE waits

OR (wait_type = 113) -- SOS_WORKER waits

OR (wait_type = 120) -- SOS_SCHEDULER_YIELD waits

OR (wait_type = 178) -- WRITELOG waits

OR (wait_type > 174 AND wait_type < 177) -- FCB_REPLICA_ waits

OR (wait_type = 186) -- CMEMTHREAD waits

OR (wait_type = 187) -- CXPACKET waits

OR (wait_type = 207) -- TRACEWRITE waits

OR (wait_type = 269) -- RESOURCE_SEMAPHORE_MUTEX waits

OR (wait_type = 283) -- RESOURCE_SEMAPHORE_QUERY_COMPILE waits

OR (wait_type = 284) -- RESOURCE_SEMAPHORE_SMALL_QUERY waits

--OR (wait_type = 195) -- WAITFOR

)

ADD TARGET package0.event_file

(

SET filename = 'DurationOver5Sec.xel',

max_file_size = 10,

max_rollover_files = 5

)

WITH

(

MAX_MEMORY = 10MB

, MAX_EVENT_SIZE = 10MB

, STARTUP_STATE = ON

, MAX_DISPATCH_LATENCY = 5 SECONDS

, EVENT_RETENTION_MODE = ALLOW_MULTIPLE_EVENT_LOSS

);

ALTER EVENT SESSION DurationOver5Sec

ON SERVER

STATE = START;

How to Check the server for events-

This query allows us to simply check the query.

DECLARE

@SessionName SysName

, @TopCount Int = 1000

--SELECT @SessionName = 'UserErrors'

SELECT @SessionName = 'DurationOver5Sec'

--SELECT @SessionName = 'system_health'

SELECT * FROM sys.traces

SELECT Session_Name = s.name, s.blocked_event_fire_time, s.dropped_buffer_count, s.dropped_event_count, s.pending_buffers

FROM sys.dm_xe_session_targets t

INNER JOIN sys.dm_xe_sessions s ON s.address = t.event_session_address

WHERE target_name = 'event_file'

--*/

SET STATISTICS IO, TIME ON

IF OBJECT_ID('tempdb..#Events') IS NOT NULL BEGIN

DROP TABLE #Events

END

IF OBJECT_ID('tempdb..#Queries') IS NOT NULL BEGIN

DROP TABLE #Queries

END

DECLARE @Target_File NVarChar(1000)

, @Target_Dir NVarChar(1000)

, @Target_File_WildCard NVarChar(1000)

SELECT @Target_File = CAST(t.target_data as XML).value('EventFileTarget[1]/File[1]/@name', 'NVARCHAR(256)')

FROM sys.dm_xe_session_targets t

INNER JOIN sys.dm_xe_sessions s ON s.address = t.event_session_address

WHERE s.name = @SessionName

AND t.target_name = 'event_file'

SELECT @Target_Dir = LEFT(@Target_File, Len(@Target_File) - CHARINDEX('\', REVERSE(@Target_File)))

SELECT @Target_File_WildCard = @Target_Dir + '\' + @SessionName + '_*.xel'

--SELECT @Target_File_WildCard

SELECT TOP (@TopCount) CAST(event_data AS XML) AS event_data_XML

INTO #Events

FROM sys.fn_xe_file_target_read_file(@Target_File_WildCard, null, null, null) AS F

ORDER BY File_name DESC

, file_offset DESC

SELECT EventType = event_data_XML.value('(event/@name)[1]', 'varchar(50)')

, Duration_sec = CAST(event_data_XML.value ('(/event/data[@name=''duration'']/value)[1]', 'BIGINT')/CASE WHEN event_data_XML.value('(event/@name)[1]', 'varchar(50)') LIKE 'wait%' THEN 1000.0 ELSE 1000000.0 END as DEC(20,3))

, CPU_sec = CAST(event_data_XML.value ('(/event/data[@name=''cpu_time'']/value)[1]', 'BIGINT')/1000000.0 as DEC(20,3))

, physical_reads_k = CAST(event_data_XML.value ('(/event/data [@name=''physical_reads'']/value)[1]', 'BIGINT')/1000.0 as DEC(20,3))

, logical_reads_k = CAST(event_data_XML.value ('(/event/data [@name=''logical_reads'']/value)[1]', 'BIGINT') /1000.0 as DEC(20,3))

, writes_k = CAST(event_data_XML.value ('(/event/data [@name=''writes'']/value)[1]', 'BIGINT')/1000.0 as DEC(20,3))

, row_count = event_data_XML.value ('(/event/data [@name=''row_count'']/value)[1]', 'BIGINT')

, Statement_Text = ISNULL(event_data_XML.value ('(/event/data [@name=''statement'']/value)[1]', 'NVARCHAR(4000)'), event_data_XML.value ('(/event/data [@name=''batch_text'' ]/value)[1]', 'NVARCHAR(4000)'))

, TimeStamp = DateAdd(Hour, DateDiff(Hour, GetUTCDate(), GetDate()) , CAST(event_data_XML.value('(event/@timestamp)[1]', 'varchar(50)') as DateTime2))

, SPID = event_data_XML.value ('(/event/action [@name=''session_id'']/value)[1]', 'BIGINT')

, Username = event_data_XML.value ('(/event/action [@name=''server_principal_name'']/value)[1]', 'NVARCHAR(256)')

, Database_Name = DB_Name(event_data_XML.value ('(/event/action [@name=''database_id'']/value)[1]', 'BIGINT'))

, client_app_name = event_data_XML.value ('(/event/action [@name=''client_app_name'']/value)[1]', 'NVARCHAR(256)')

, client_hostname = event_data_XML.value ('(/event/action [@name=''client_hostname'']/value)[1]', 'NVARCHAR(256)')

, result = ISNULL(event_data_XML.value('(/event/data [@name=''result'']/text)[1]', 'NVARCHAR(256)'),event_data_XML.value('(/event/data [@name=''message'']/value)[1]', 'NVARCHAR(256)'))

, Error = event_data_XML.value ('(/event/data [@name=''error_number'']/value)[1]', 'BIGINT')

, Severity = event_data_XML.value ('(/event/data [@name=''severity'']/value)[1]', 'BIGINT')

, EventDetails = event_data_XML

INTO #Queries

FROM #Events

SELECT q.EventType

, q.Duration_sec

, q.CPU_sec

, q.physical_reads_k

, q.logical_reads_k

, q.writes_k

, q.row_count

, q.Statement_Text

, q.TimeStamp

, q.SPID

, q.Username

, q.Database_Name

, client_app_name = CASE LEFT(q.client_app_name, 29)

WHEN 'SQLAgent - TSQL JobStep (Job '

THEN 'SQLAgent Job: ' + (SELECT name FROM msdb..sysjobs sj WHERE substring(q.client_app_name,32,32)=(substring(sys.fn_varbintohexstr(sj.job_id),3,100))) + ' - ' + SUBSTRING(q.client_app_name, 67, len(q.client_app_name)-67)

ELSE q.client_app_name

END

, q.client_hostname

, q.result

, q.Error

, q.Severity

, q.EventDetails

FROM #Queries q

--WHERE eventtype NOT IN /*rather typical filtering*/ ('security_error_ring_buffer_recorded', 'sp_server_diagnostics_component_result', 'scheduler_monitor_system_health_ring_buffer_record')

--AND eventtype NOT IN /*specific troubleshooting filtering*/ ('connectivity_ring_buffer_recorded', 'wait_info')

ORDER BY TimeStamp DESC

OFFSET Command in SQL Server

2024-02-29T18:49:00.002+05:30

OFFSET Command in SQL Server

Introduction-

In SQL Server, the OFFSET command is used in conjunction with the ORDER BY clause to specify the number of rows to skip before starting to return rows from a query. This is particularly useful for implementing pagination in applications or when you need to skip a certain number of rows before fetching results.

Here's the basic syntax of the OFFSET command-

SELECT column1, column2, ...

FROM table_name

ORDER BY column1, column2, ...

OFFSET {number_of_rows | expression} ROWS

FETCH {FIRST | NEXT} {number_of_rows | expression} ROWS ONLY;

Let's break down each part:

OFFSET-

This specifies the number of rows to skip before starting to return rows. It is always used with FETCH to define the number of rows to return.

{number_of_rows | expression}-

This can be a numeric value or an expression that evaluates to the number of rows to skip or fetch.

ROWS-

This keyword specifies that the value specified after OFFSET and FETCH is in terms of rows.

FETCH {FIRST | NEXT}-

This part is used to specify whether to fetch the first or next set of rows after the offset.

{number_of_rows | expression} ROWS ONLY-

This part specifies the number of rows to fetch after the offset.

Now let's see an example to understand how

to use the OFFSET command:

Suppose we have a table called Employees with columns EmployeeID, FirstName, and LastName.

We want to fetch 5 rows from the table starting from the 6th row, ordered by EmployeeID.

SELECT EmployeeID, FirstName, LastName

FROM Employees

ORDER BY EmployeeID

OFFSET 5 ROWS

FETCH NEXT 5 ROWS ONLY;

In this example:

OFFSET 5 ROWS-

Skips the first 5 rows.

FETCH NEXT 5 ROWS ONLY-

Fetches the next 5 rows after skipping the first 5.

This query will return 5 rows from the Employees table starting from the 6th row, ordered by EmployeeID.

Keep in mind that the OFFSET and FETCH clauses are available in SQL Server 2012 and later versions. If you're using an older version, you may need to use alternative methods like ROW_NUMBER () function with subqueries to achieve similar results.

Normalization in SQL Server

2024-02-15T17:47:00.001+05:30

Normalization in SQL Server

Introduction-

Normalization in SQL Server is a crucial concept in database design that aims to organize data efficiently, reduce redundancy, and maintain data integrity. It involves structuring relational databases to ensure that they meet certain criteria regarding the relationships between tables and the dependencies among their attributes.

Normalization's primary objectives are-

Eliminating Redundancy-

Redundant data storage wastes space and can lead to inconsistencies and anomalies when updating or deleting data. Normalization helps to minimize redundancy by breaking down large tables into smaller, more manageable ones.

Avoiding Update Anomalies-

Update anomalies occur when updating data in one place but not in others, leading to inconsistencies. Normalization helps prevent such anomalies by ensuring that data is stored in a way that makes it easy to update without risking inconsistency.

Ensuring Data Integrity-

Data integrity refers to the accuracy, consistency, and reliability of data stored in a database. Normalization contributes to data integrity by structuring data to reduce the risk of errors, inconsistencies, and inaccuracies.

Database Normalization-

Every database you construct should undergo the process of database normalization. Normal Forms are a collection of formal criteria and principles that are applied to a database design.

The disadvantages of data redundancy include-

Data upkeep becomes difficult; data deletion and upgrades become problematic.
It generates data discrepancies.
Insert, update, and delete abnormalities become common. An update anomaly, for example, means that all copies of the same record, duplicated in different places in the database, must be updated to maintain the record consistent.
Redundant data increases the size of a database and takes up an excessive amount of space on disk.

Normal Forms-

The goal of the following article is to present the essential information about database normalization. Although normalization is a broad topic, this article aims to give readers enough background knowledge to comprehend the first three types of database normalization.

1 NF, or First Normal Form
Form 2 Normal, second (NF)
Form 3: Third Normal (3 NF)

If a database satisfies the prerequisites of the first three normal forms, it is regarded as the third normal form.

First Normal Form (1NF)-

Ensures that each column in a table contains atomic (indivisible) values.

Example: Original Table:

| Student_ID | Name | Courses |

|------------|-----------------|--------------------|

| 1 | John Doe | Math, Physics |

| 2 | Jane Smith | Chemistry, Biology |

After normalization to 1NF-

Students Table-

| Student_ID | Name |

|------------|-----------|

| 1 | John Doe |

| 2 | Jane Smith|

Courses Table-

| Student_ID | Course |

|------------|----------|

| 1 | Math |

| 1 | Physics |

| 2 | Chemistry|

| 2 | Biology |

Second Normal Form (2NF)-

Every non-prime attribute is fully functionally dependent on the primary key.

Example-

Original Table-

| Student_ID | Course | Instructor |

|------------|----------|--------------|

| 1 | Math | Mr. Smith |

| 1 | Physics | Mr. Johnson |

| 2 | Chemistry| Mrs. Brown |

| 2 | Biology | Mr. Green |

After normalization to 2NF-

Students Table-

| Student_ID | Name |

|------------|-----------|

| 1 | John Doe |

| 2 | Jane Smith|

Courses Table-

| Course | Instructor |

|----------|------------|

| Math | Mr. Smith |

| Physics | Mr. Johnson|

| Chemistry| Mrs. Brown |

| Biology | Mr. Green |

Enrollments Table-

| Student_ID | Course |

|------------|----------|

| 1 | Math |

| 1 | Physics |

| 2 | Chemistry|

| 2 | Biology |

Third Normal form (3NF)-

Every non-prime attribute is non-transitively dependent on the primary key.

Example-

Original Table-

|------------|----------|--------------|------------------|

| 1 | Math | Mr. Smith | 555-1234 |

| 1 | Physics | Mr. Johnson | 555-5678 |

| 2 | Chemistry| Mrs. Brown | 555-9876 |

| 2 | Biology | Mr. Green | 555-4321 |

After normalization to 3NF-

Students Table-

| Student_ID | Name |

|------------|-----------|

| 1 | John Doe |

| 2 | Jane Smith|

Course Table-

| Course | Instructor |

|----------|------------|

| Math | Mr. Smith |

| Physics | Mr. Johnson|

| Chemistry| Mrs. Brown |

| Biology | Mr. Green |

Instructor Table-

| Instructor | Phone |

|--------------|-----------|

| Mr. Smith | 555-1234 |

| Mr. Johnson | 555-5678 |

| Mrs. Brown | 555-9876 |

| Mr. Green | 555-4321 |

Enrollment Table-

| Student_ID | Course |

|------------|----------|

| 1 | Math |

| 1 | Physics |

| 2 | Chemistry|

| 2 | Biology |

These three normalization forms are the ones that are discussed the most. Higher normal forms do exist, albeit they are not as commonly employed in practice as Boyce-Codd Normal Form (BCNF) and Fourth Normal Form (4NF). In a relational database, each normalization form aids in reducing anomalies, redundancies, and preserving data integrity.

Trigger and create a simple logon Trigger

2024-02-08T12:25:00.003+05:30

Trigger and create a simple logon Trigger

Introduction-

In order to connect using SSMS or client apps, SQL Server offers Windows and SQL authentication methods. For DBAs, security is always the first priority, particularly in the production setting. We can manage database access by giving users the minimal and suitable permissions for their task. SQL Server's provide and DENY statements can be used to provide each user the proper task permissions.

Trigger-

In SQL Server, a trigger is a special kind of stored procedure that is triggered automatically by specific events, such adding, modifying, or removing data from a table. Triggers can be used to audit data changes, uphold data integrity, and enforce business rules.

Types-

DML (Data Manipulation Language) and DDL (Data Definition Language) triggers are the two primary categories of triggers in SQL Server. DDL triggers are triggered by events connected to the database structure, including adding or removing tables, whereas DML triggers are triggered by insert, update, or delete statements.

Logon Trigger in SQL Server-

Database code known as "triggers" is run when a specific event occurs. Login triggers are a useful tool for managing SQL login security. When a logon event has place, SQL Server initiates the logon triggers automatically. It is carried out prior to the establishment of a user session and the accomplishment of authentication.

For an efficient database connection in any database, we use two terms.

Authentication-

To establish a connection using SQL authentication, we provide a username and password. It is an authentication process for the user.

Authorization-

Authorization is a permission that grants you the ability to carry out particular tasks. You might not be allowed to make any changes at the instance level, but you might be granted complete access to a particular database.

Create a simple Logon Trigger-

USE master;

CREATE TABLE LogonAudit (

LoginName nvarchar(128) NOT NULL,

LoginTime datetime NOT NULL

);

CREATE TRIGGER LogonAuditTrigger

ON ALL SERVER

FOR LOGON

BEGIN

INSERT INTO LogonAudit (LoginName, LoginTime)

VALUES (ORIGINAL_LOGIN(), GETDATE());

END;

In this example, we first create a table named "LogonAudit" to store the login audit information. The table has two columns: "LoginName" and "LoginTime".

Next, we create the logon trigger named "LogonAuditTrigger" those fires for every LOGON event. The trigger is created on the "ALL SERVER" scope, which means it applies to the entire SQL Server instance.

The trigger body is enclosed in the "BEGIN" and "END" keywords. It contains a single statement that inserts the login name and login time into the "LogonAudit" table.

The "ORIGINAL_LOGIN()" function returns the name of the original SQL Server login that initiated the session. The "GETDATE()" function returns the current date and time.

2024-02-05T16:56:00.002+05:30

Automatic Diagnostic Repository

The Automatic Diagnostic Repository (ADR) is a system-wide tracing and logging central repository for database diagnostic data. It includes the following items:

Background trace files : Background trace files store information from database

background processes. When a process detects an internal error, the process dumps

information about the error to its trace file. Some of the information written to a

trace file is intended for the database administrator, whereas other information is

for Oracle Support Services. Typically, database background process trace file

names contain the Oracle system identifier (SID), the background process name,

and the operating system process number

An example of a trace file for the RECO process is mytest_reco_10355.trc.

Common Background Processes Generating Trace Files:

PMON (Process Monitor): Monitors the other background processes and performs process recovery.

SMON (System Monitor): Performs instance recovery when an instance fails.

LGWR (Log Writer): Writes redo log entries to disk.

DBWn (Database Writer): Writes modified blocks from the database buffer cache to data files.

CKPT (Checkpoint): Performs database checkpoints to synchronize the data files and control files.

Location of Background Trace Files:

Background trace files are typically located in the Automatic Diagnostic Repository (ADR).

The ADR home path can be obtained using the SHOW PARAMETER DIAGNOSTIC_DEST

Enabling and Disabling Tracing:

Tracing for background processes can be enabled or disabled dynamically using the ALTER SYSTEM or ALTER SESSION commands.

For example,

to enable tracing for the LGWR process:

ALTER SYSTEM SET EVENTS 'trace[LGWR]:[Level]';

Foreground trace file:Foreground trace files store information from server processes. When a process detects an internal error, the process

dumps information about the error to its trace file. Server process trace file names

contain the Oracle SID, the

string ora, and the operating system process number. An example of a server

process trace file name is mytest_ora_10304.trc.

Location of Foreground Trace Files:

Foreground trace files are typically located in the Automatic Diagnostic Repository (ADR).

The ADR home path can be obtained using the SHOW PARAMETER DIAGNOSTIC_DEST

Enabling and Disabling Tracing for a Session:

Tracing for a specific user session can be dynamically enabled or disabled using the ALTER SESSION command.

For example, to enable tracing for a session: ALTER SESSION SET TRACEFILE_IDENTIFIER='trace_identifier';

Dump files: are a special type of trace file that contains detailed point-in-time information about a state or structure. A dump file is typically a one-time output of diagnostic data in response to an event, whereas a trace file tends to be a continuous output of diagnostic data.

Health monitor reports: Health monitor reports include results of diagnostic checks from the Health Monitor framework. Health checks detect file corruptions, physical and logical block corruptions, undo and redo corruptions, data dictionary corruptions, and more. The health checks generate reports of their findings and, in many cases, recommendations for resolving problems.

Incident package:Incident package contain information for uploading diagnostic data to Oracle Support. A package is a collection of metadata that is stored in the ADR and points to diagnostic data files and other files both in and outside of the ADR. When you create a package, you select one or more problems to add to the package. The Support Workbench then automatically adds to the package the problem information, incident information, and diagnostic data (such as trace files and dumps) associated with the selected problems.

Incident dump: Incident dumps contain diagnostic data that Oracle Database collects when an incident occurs. The database writes one or more dumps to the incident directory created for the incident. Incident dumps also contain the incident number in the file name.

Alert log file The Oracle alert log file is a critical component for monitoring and troubleshooting Oracle Database instances. The alert log provides a chronological record of messages and events that occur during the operation of the database. Database administrators (DBAs) regularly review the alert log to identify issues, errors, or significant events affecting the database. Here are key points about the Oracle alert log file:

Location of the Alert Log:

The alert log is typically located in the Automatic Diagnostic Repository (ADR), specifically in the "alert" subdirectory.

The default file name is alert_<SID>.log, where <SID> represents the system identifier of the database.

Purpose of the Alert Log:

The alert log serves as a comprehensive record of database activities, including startup and shutdown events, errors, warnings, and noteworthy occurrences.

It is a critical source of information for diagnosing issues, monitoring the health of the database, and maintaining a historical record of significant events.

Content of the Alert Log:

The alert log contains messages generated by the Oracle Database instance, including:

Startup and shutdown information.

Critical error messages.

Warnings and informational messages.

Changes in database configuration.

Log switches.

Block corruption messages.

Background process trace file names.

Automatic Diagnostic Repository (ADR):

The alert log is part of the Automatic Diagnostic Repository (ADR), which is a centralized location for database diagnostic data.

ADR simplifies the management of diagnostic data and provides tools like ADRCI (Automatic Diagnostic Repository Command Interpreter) for accessing and managing diagnostic information.

Rotating and Archiving:

The alert log file may become large over time, and Oracle Database has mechanisms for rotating and archiving the log to prevent it from growing indefinitely.

Log rotation typically involves switching to a new log file, and archived logs may be stored for historical purposes.

Viewing the Alert Log:

DBAs can view the alert log using various tools, including Oracle Enterprise Manager (EM), SQL*Plus, and ADRCI

SHOW PARAMETER background_dump_dest;

Monitoring Tools:

Database monitoring tools often include features to alert DBAs to critical messages in the alert log.

Oracle Enterprise Manager provides a graphical interface for monitoring the alert log and receiving alerts for important events.

Alert Log for Database Startup Issues:

The alert log is particularly crucial for diagnosing issues during the startup of the database. Any errors or warnings encountered during startup are typically recorded in the alert log.

Integration with Oracle Support:

When troubleshooting issues, Oracle Support often requests copies of the alert log to assist in diagnosing problems. Keeping a record of the alert log is essential for obtaining support.

Regularly reviewing the Oracle alert log is a fundamental practice for database administrators to ensure the health and reliability of the Oracle Database instance. It provides real-time insights into the operational status of the database and serves as a primary resource for diagnosing and resolving issues.

2024-01-29T15:54:00.000+05:30

Oracle Data Dictionary and the Dynamic Performance Views

An important part of an Oracle database is its data dictionary, which is a read-only set of tables that provides administrative metadata about the database.

A data dictionary contains information such as the following:

· The definitions of every schema object in the database, including default values for columns and integrity constraint information

· The amount of space allocated for and currently used by the schema objects

· The names of Oracle Database users, privileges and roles granted to users, and auditing information related to users

The data dictionary is a central part of data management for every Oracle database. For example, the database performs the following actions:

· Accesses the data dictionary to find information about users, schema objects, and storage structures

· Modifies the data dictionary every time that a DDL statement is issued

Because Oracle Database stores data dictionary data in tables, just like other data, users can query the data with SQL. For example, users can run SELECT statements to determine their privileges, which tables exist in their schema, which columns are in these tables, whether indexes are built on these columns, and so on.

Some commonly used Data Dictionary views include:

USER_ Views (or ALL_ and DBA_ Views):

USER_TABLES: Information about tables owned by the current user.

USER_COLUMNS: Details about columns of tables owned by the current user.

USER_VIEWS: Information about views owned by the current user.

ALL_ Views:

Similar to USER_ views, but they show information about objects accessible to the current user.

DBA_ Views:

These views show information about all objects in the database, but access to these views requires DBA privileges.

Dynamic Performance Views

Throughout its operation, Oracle Database maintains a set of virtual tables that record current database activity.

These views are dynamic because they are continuously updated while a database is open and in use. The views are sometimes called V$ views because their names begin with V$.

Dynamic performance views contain information such as the following:

System and session parameters
Memory usage and allocation
File states (including RMAN backup files)
Progress of jobs and tasks
SQL execution
Statistics and metrics

Some commonly used Dynamic Performance Views include:

V$SESSION:

Information about current sessions, including session ID, username, and status.

V$SQL:

Details about SQL statements currently in the shared SQL area, including SQL text and execution statistics.

V$WAITSTAT:

Information about wait statistics, showing where sessions are waiting.

V$DATAFILE:

Details about database data files, including file name, size, and status.

CDC in SQL Server

2024-01-27T13:13:00.003+05:30

A brief description of SQL Server's CDC-

Change Data Capture, or CDC for short, was initially introduced in the SQL Server 2008 edition as a useful feature to track and record changes made to SQL Server database tables without requiring extra programming work. Prior to SQL Server 2016, a SQL Server database could only have Change Data Capture enabled with the SQL Server Enterprise edition; however, as of SQL Server 2016, this is no longer necessary.

A mirrored table with the same column structure as the source tables and extra columns to capture the description of these changes is created by Change Data Capture, which keeps track of the INSERT, UPDATE, and DELETE actions on the database table. For each INSERT statement, SQL Server creates one record that displays the values that are inserted; for each DELETE statement, it creates one record that displays the data that is deleted; and for each UPDATE statement, it creates two records—one that displays the data that was there prior to the change and the other that displays the data that was there after it was made.

Design and configuration-

A SQL Server instance must have a SQL Server Agent active in order to use Change Data Capture. A SQL Server database table that has this feature enabled will have two SQL Server Agent jobs created for it. The first job's task is to update database change tables with the updated data, and the second job's task is to tidy up the change tables by removing records that are older than the three-day configurable retention period. As the data source changes, Change Data Capture is dependent on the SQL Server Transaction Log. A modification will be recorded in the Transaction Log file as soon as it is made.

Change Data Capture as a Solution for Audits-

Without the ability to track SELECT statements, Change Data Capture can be used as an asynchronous SQL Server Audit solution to monitor and audit DML changes made to the table, such as INSERT, UPDATE, and DELETE operations.

Change Data Capture is an effective SQL Server audit solution because it is simple to set up with a few T-SQL statements, offers historical data about values prior to the change process, and gives comprehensive information on the data modification procedure.

Let's see how we can audit the SQL Server DML modifications using Change Data Capture.

Enabling DAC-

Step 1-

Open the Management Studio for Microsoft SQL Server and make a new database and table.

Create Database CDC1

use CDC1

CREATE TABLE CDC_TAB

(

ID INT,

Name VARCHAR (50),

Place VARCHAR (50),

Salary INT,

)

Step 2-

After creating the previously mentioned database and table, use the following Insert SQL Statements to add some data to the CDC_TAB table.

Step 3-

CDC use in SQL Server

In SQL Server, CDC must be enabled in two steps. They are listed in the following order:

CDC must first be enabled at the database level.

Next, we must specify which tables require CDC activation.

Enable CDC on Database Level:

To enable CDC on the database level, run the following stored procedure in the system.

Step 4-

Enable CDC at Table Level

Using the CDC1 database, we generated the CDC_TAB table. Let's now activate CDC for this Employee table. As demonstrated below, we must run the system stored procedure sp_cdc_enable_table in order to enable CDC at the table level. Here, we are giving the stored procedure access to the schema, table name, and roll.

EXEC sys.sp_cdc_enable_table

@source_schema = 'dbo',

@source_name = 'CDC_TAB',

@role_name = null

Step 5-

The SQL Server Agent service must be running for the CDC to operate properly.

Step 6-

The tables that truly record insert, update, and delete action for a specific table are called _CT tables. For instance, every Insert, Update, and Delete operations made on the CDC_TAB table will be recorded in the dbo_CDC_TAB_CT table. A table named dbo_EMP_CT will be created to record all DML operations made on the EMP table if you have another table named EMP and you enable CDC for that table.

Step 7-

For instance, updating data in the CDC_TAB table

UPDATE CDC_TAB SET Name = 'Ayush' WHERE ID = 7

UPDATE CDC_TAB SET Name = 'Abhishek' WHERE ID = 6

Step 7-

It will be recorded in the system table.

Step 8-

Deleting from the CDC_TAB table, for instance

To observe what occurs in the dbo_CDC_TAB_CT table, let's remove one record from the CDC_TAB. In order to remove the CDC_TAB with ID number three, please run the SQL statement below.

DELETE FROM CDC_TAB WHERE ID = 7

Please review the dbo_CDC_TAB_CT table as indicated below after deleting the CDC_TAB. It creates a single record for the removed CDC_TAB, as you can see.

Step 9-

Example: Inserting into the CDC_TAB table.

Let's add one record to the CDC_TAB

INSERT INTO CDC_TAB VALUES (8, 'Yash', 'meerut', 900000)

Step 10 –

Identify which row is utilized for what type of activity.

The operation column can take one of four values (1, 2, 3, or 4), and we'll look at what each means.

Delete Statement = 1

Insert Statement = 2

Value before update statement = 3

Value after update statement = 4

Step 11-

To Disabled CDC Database Level and table level

EXECUTE sys.sp_cdc_disable_db;

EXEC sys.sp_cdc_disable_table

@source_schema = 'dbo',

@source_name = 'CDC_TAB',

@capture_instance = 'all';

Advantages and Disadvantages-

Advantages of CDC in SQL Server-

Efficient Change Tracking-

CDC provides an efficient mechanism for tracking changes to data without the need for manual triggers or logging mechanisms.

It captures changes at the database level, making it suitable for scenarios where detailed change history is required.

Minimized Performance Impact-

CDC is designed to have minimal impact on the performance of the database system.

It uses log-based change tracking, ensuring that changes are captured without affecting the operational performance of the database.

Support for ETL Processes-

CDC is often used in conjunction with Extract, Transform, Load (ETL) processes to identify and replicate changes to data between systems.

ETL processes can use CDC to efficiently identify and process only the changed data.

Built-in Change Tables-

CDC automatically creates change tables to store the historical changes, making it easy to query and analyze the historical data.

Integration with Replication-

CDC can be integrated with transactional replication to efficiently replicate changes to other databases or servers.

Disadvantages of CDC in SQL Server-

Configuration Complexity-

Setting up and configuring CDC can be complex, especially for users who are not familiar with the feature.

It requires specific permissions and steps to enable CDC on databases and tables.

Maintenance Overhead-

Change tables can grow in size over time, leading to increased storage requirements.

Regular maintenance is required to manage and clean up change tables, which may add administrative overhead.

Limited to Certain Editions-

CDC is not available in all editions of SQL Server. For example, it's not available in the Express edition, which may limit its use in certain environments.

Lack of Granular Control-

CDC operates at the database and table level, which means that it may capture more data than necessary in certain scenarios.

There is limited control over which specific columns or types of changes to capture.

Increased Storage Requirements-

The change tables can consume additional storage space, especially in scenarios where there are frequent and significant changes to the data.

In summary, CDC is a powerful feature for tracking changes in SQL Server, especially in scenarios where a comprehensive change history is required. However, its implementation should be carefully considered based on the specific requirements and constraints of the environment.

That covers everything regarding the CDC.

Materialized Views in Oracle

2024-01-22T22:54:00.001+05:30

Materialized Views in Oracle

A materialized view, or snapshot as they were previously known, is a table segment whose contents are periodically refreshed based on a query, either against a local or remote table.

Basic Syntax-

The full syntax description for the CREATE MATERIALIZED VIEW command is available in the documentation. Here we will only concern ourselves with the basics.

--Normal

CREATE MATERIALIZED VIEW view-name

BUILD [IMMEDIATE | DEFERRED]

REFRESH [FAST | COMPLETE | FORCE ]

ON [COMMIT | DEMAND]

[[ENABLE | DISABLE] QUERY REWRITE]

SELECT ...;

-- Pre-Built

CREATE MATERIALIZED VIEW view-name ON PREBUILT TABLE

REFRESH [FAST | COMPLETE | FORCE ]

ON [COMMIT | DEMAND]

[[ENABLE | DISABLE] QUERY REWRITE]

SELECT ...;

The BUILD clause options are shown below.

IMMEDIATE-

The materialized view is populated immediately.

DEFERRED-

The materialized view is populated on the first requested refresh.

The following refresh types are available.

FAST-

A fast refresh is attempted. If materialized view logs are not present against the source tables in advance, the creation fails.

COMPLETE-

The table segment supporting the materialized view is truncated and repopulated completely using the associated query.

FORCE-

A fast refresh is attempted. If one is not possible a complete refresh is performed.

A refresh can be triggered in one of two ways.

ON COMMIT –

The refresh is triggered by a committed data change in one of the dependent tables.

ON DEMAND-

The refresh is initiated by a manual request or a scheduled task.

The QUERY REWRITE clause tells the optimizer if the materialized view should be considered. For query rewrite operations. An example of the query rewrite functionality is shown below.

The ON PREBUILT TABLE clause tells the database to use an existing. table segment, which must have the same name as the materialized view and support the same column structure as the query.

Check Privileges -

Check the user who will own the materialized views has the correct privileges. At minimum they will require the CREATE MATERIALIZED VIEW privilege. If they are creating materialized views using database links, you may want to grant them CREATE DATABASE LINK privilege also.

GRANT CREATE MATERIALIZED VIEW TO username;

GRANT CREATE DATABASE LINK TO username;

Create Materialized View-

Connect to the materialized view owner and create the database link and the materialized view itself.

CREATE MATERIALIZED VIEW A2.employee_salary_summary

BUILD IMMEDIATE REFRESH FAST ON

COMMIT

SELECT employee_id, SUM(salary) as total_salary

FROM A2.employees

GROUP BY employee_id;

Create Materialized View Logs-

Since a complete refresh involves truncating the materialized view segment and re-populating it is using the related query, it can be quite time consuming and involve a considerable amount of network traffic when performed against a remote table. To reduce the replication costs, materialized view logs can be created to capture all changes to the base table since the last refresh. This information allows a fast refresh, which only needs to apply the changes rather than a complete refresh of the materialized view.

To take advantage of the of the fast refresh, connect to the master instance and create the materialized view log

CREATE MATERIALIZED VIEW LOG ON employees;

Refresh Materialized Views-

If a materialized view is configured to refresh on commit, you should never need to manually refresh it, unless a rebuild is necessary. Remember, refreshing on commit is a very intensive operation for volatile base tables. It makes sense to use fast refreshes where possible.

For on demand refreshes, you can choose to manually refresh the materialized view or refresh it as part of a refresh group.

The following code creates a refresh group defined to refresh every minute and assigns a materialized view to it.

Information about refresh groups and the materialize views in a refresh group can be queried from the DBA_RGROUP and DBA_RCHILD views respectively.

A materialized view can be manually refreshed using the DBMS_MVIEW package.

Rather than using a refresh group, you can schedule DBMS_MVIEW.REFRESH called using the Oracle Scheduler.

Cleaning Up-

To clean up we must remove all objects.

DROP MATERIALIZED VIEW your_materialized_view_name;

DROP DATABASE LINK your_database_link_name;

Considerations-

Before using materialized views and materialized view logs, consider the following:

Populating a materialized view adds load to both servers involved. The source server is queried to capture the data, which is inserted into the destination server. Be sure the additional load does not adversely affect your primary system.

Although materialized view logs improve the performance of materialized view refreshes, they do increase the work needed to perform DML on the base table. Check the additional work does not adversely affect performance on the primary system.

If regular refreshes are not performed, materialized view logs can grow very large, potentially reducing the performance of their maintenance and blowing tablespace limits.

Depending on the Oracle version and the complexity of the associated query, fast refreshes may not be possible.

When using materialized views to improve performance of transformations and aggregations

the QUERY_REWRITE_INTEGRITY and QUERY_REWRITE_ENABLED parameters must be set, or the server will not be able to automatically take advantages of query rewrites. These parameters may be set in the pfile or spfile file if they are needed permanently. Later releases have them enabled by default.

--BY Tarif Ahmed

Oracle Database Locking Mechanism

2024-01-18T18:14:00.002+05:30

Oracle Database Locking Mechanism

In the field of database management, maintaining data integrity and controlling concurrent access is paramount. Oracle Database, a robust relational database management system, employs a sophisticated locking mechanism to achieve these objectives. In this comprehensive guide, we delve into the intricacies of the Oracle Database Locking Mechanism, exploring key terms and concepts integral to its functionality.

Oracle Database:

Oracle Database is a relational database management system (RDBMS) developed by Oracle Corporation. It is widely used for managing and storing data in a structured manner.

Locking Mechanism:

In a database management system, a locking mechanism is a mechanism that prevents multiple transactions from accessing the same data simultaneously. It ensures data integrity and consistency by controlling access to shared resources.

Transaction:

A transaction in a database represents a sequence of one or more SQL statements executed as a single unit of work. It is either completed in its entirety or not at all to maintain the integrity of the data.

Database Lock:

Definition: A database lock is a mechanism used to control access to a database resource (e.g., a row, table, or page) to ensure that only one transaction can modify it at a time. Locks can be shared or exclusive, depending on the type of access required.

Concurrency Control:

Concurrency control is a mechanism that allows multiple transactions to access the database simultaneously while maintaining data consistency. Locking is one of the key methods used for concurrency control.

Lock Types:

Shared Lock:

A shared lock allows multiple transactions to read a resource simultaneously, but it prevents any of them from modifying it until the lock is released.

Exclusive Lock:

An exclusive lock prevents any other transaction from accessing a resource, either for reading or writing, until the lock is released.

Deadlock:

A deadlock occurs when two or more transactions are unable to proceed because each is waiting for the other to release a lock. This can lead to a standstill in transaction processing.

Oracle Lock Modes:

Row-Level Locking:

Oracle supports row-level locking, allowing locks to be applied at the granularity of individual rows.

Table-Level Locking:

Locks can also be applied at the table level, preventing any transactions from accessing the entire table.

Transaction Isolation Levels:

Oracle supports different transaction isolation levels (e.g., Read Committed, Serializable) that determine the visibility of uncommitted changes to other transactions.

Lock Escalation:

Lock escalation is a process where a database system automatically converts many fine-grained locks into fewer coarser-grained locks to reduce overhead.

In conclusion, understanding the Oracle Database Locking Mechanism is crucial for managing concurrent transactions effectively, ensuring data integrity, and preventing conflicts. By grasping the various lock types, isolation levels, and mechanisms, database administrators can optimize system performance and maintain a high level of data consistency.

ALL About TempDB

2024-01-17T12:01:00.001+05:30

For TempDB

--Determining the amount of free space in tempdb

USE tempdb;

SELECT SUM(unallocated_extent_page_count) AS [free pages],

(SUM(unallocated_extent_page_count)*1.0/128) AS [free space in MB]

FROM sys.dm_db_file_space_usage;

--Determining the Amount of Space Used by User Objects

USE tempdb;

SELECT SUM(user_object_reserved_page_count) AS [user object pages used],

(SUM(user_object_reserved_page_count)*1.0/128) AS [user object space in MB]

FROM sys.dm_db_file_space_usage;

use tempdb

SELECT GETDATE() AS runtime

,(SUM(user_object_reserved_page_count) * 8)/1024 AS usr_obj_mb

,SUM(internal_object_reserved_page_count) * 8 AS internal_obj_kb

,(SUM(version_store_reserved_page_count) * 8)/1024 AS version_store_mb

,(SUM(unallocated_extent_page_count) * 8)/1024 AS freespace_mb

,(SUM(mixed_extent_page_count) * 8)/1024 AS mixedextent_mb

FROM sys.dm_db_file_space_usage

use tempdb

SELECT session_id,

SUM(internal_objects_alloc_page_count) AS NumOfPagesAllocatedInTempDBforInternalTask,

SUM(internal_objects_dealloc_page_count) AS NumOfPagesDellocatedInTempDBforInternalTask,

SUM(user_objects_alloc_page_count) AS NumOfPagesAllocatedInTempDBforUserTask,

SUM(user_objects_dealloc_page_count) AS NumOfPagesDellocatedInTempDBforUserTask

FROM sys.dm_db_task_space_usage

GROUP BY session_id

ORDER BY NumOfPagesAllocatedInTempDBforInternalTask DESC, NumOfPagesAllocatedInTempDBforUserTask DESC

select * from sys.dm_db_task_space_usage

select *,db_name(database_id) from sys.dm_db_session_space_usage

Alter TempDB File Size

use master

ALTER DATABASE tempdb

MODIFY FILE (Name=N'tempdev', size = 176080MB)

For further information, click on the following link.

https://clotat.blogspot.com/2020/05/all-about-tempdb.html

Resource Manager and Task Schedular

2024-01-16T19:38:00.000+05:30

Resource Manager and Task Schedular-

In Oracle Database, the Resource Manager is a feature that allows you to manage and allocate resources among different users and applications. It enables you to control the number of resources (CPU, memory, and parallel execution servers) consumed by database sessions and helps in preventing runaway queries or resource-intensive operations from adversely impacting the overall performance of the database.

Key features and components of Oracle Resource Manager include:

Consumer Groups:

Resource Manager organizes database sessions into "consumer groups." Each consumer group represents a category of users or applications that share similar resource requirements.

Resource Plans:

Resource plans are a set of rules that define how resources should be allocated among different consumer groups. They specify the resource allocation policies, priorities, and limits for each group.

Directive and Consumer Group Mapping:

Directives are rules defined within a resource plan that specify how resources should be allocated to consumer groups. These directives may include rules for CPU usage, parallel execution servers, and other resources.

Consumer Group Mapping associates database sessions with specific consumer groups.

Parallel Degree Limits:

Resource Manager allows you to set limits on the degree of parallelism for specific operations. This helps in controlling the parallel execution of queries to prevent excessive parallelism.

Sub-Plan and Plan Directives:

Within a resource plan, you can define sub-plans and plan directives to further customize resource allocation for specific scenarios.

Automatic Consumer Group Mapping:

Oracle Database can automatically map sessions to consumer groups based on user attributes, application attributes, or other criteria defined by the database administrator.

Monitoring and Statistics:

Resource Manager provides monitoring views and statistics to track resource usage, plan effectiveness, and other relevant metrics.

Here's a simple example of creating a basic resource plan with Oracle Resource Manager:

-- Create a simple resource plan

BEGIN

DBMS_RESOURCE_MANAGER.create_pending_area();

DBMS_RESOURCE_MANAGER.create_consumer_group('low_priority');

DBMS_RESOURCE_MANAGER.create_consumer_group('high_priority');

DBMS_RESOURCE_MANAGER.create_plan('my_plan');

DBMS_RESOURCE_MANAGER.create_plan_directive(

plan => 'my_plan',

group_or_subplan => 'low_priority',

switch_group => 'TRUE',

max_utilization => 40);

DBMS_RESOURCE_MANAGER.create_plan_directive(

plan => 'my_plan',

group_or_subplan => 'high_priority',

switch_group => 'TRUE',

max_utilization => 60);

DBMS_RESOURCE_MANAGER.submit_pending_area();

END;

In this example, a resource plan named 'my_plan' is created with two consumer groups ('low_priority' and 'high_priority'). Directives are then defined to allocate resources based on the maximum utilization for each consumer group.

Resource Manager is a powerful tool for managing the performance of a multi-user Oracle Database by ensuring that resources are allocated in a controlled and predictable manner. It is particularly useful in environments where there is a need to prioritize and allocate resources among different types of workloads or users.

Task Schedular-

Ask scheduling in Oracle typically involves using the Oracle Scheduler, a comprehensive job scheduling solution provided by Oracle Database. The Oracle Scheduler allows you to automate and manage various database tasks, such as running PL/SQL procedures, calling external programs, or executing SQL scripts at specified intervals.

Here are the basic steps to create and manage scheduled tasks in Oracle:

Create a Job:

You can create a job using the DBMS_SCHEDULER.CREATE_JOB procedure.

Specify the job name, job type (PL/SQL block, stored procedure, etc.), and other relevant parameters.

BEGIN

DBMS_SCHEDULER.CREATE_JOB (

job_name => 'MY_JOB',

job_type => 'PLSQL_BLOCK',

job_action => 'BEGIN my_procedure; END;',

start_date => SYSTIMESTAMP,

repeat_interval => 'FREQ=DAILY; BYHOUR=0; BYMINUTE=0; BYSECOND=0',

enabled => TRUE

);

END;

Task scheduling in Oracle typically involves using the Oracle Scheduler, a comprehensive job scheduling solution provided by Oracle Database. The Oracle Scheduler allows you to automate and manage various database tasks, such as running PL/SQL procedures, calling external programs, or executing SQL scripts at specified intervals.

Here are the basic steps to create and manage scheduled tasks in Oracle:

Create a Job:

You can create a job using the DBMS_SCHEDULER.CREATE_JOB procedure.

Specify the job name, job type (PL/SQL block, stored procedure, etc.), and other relevant parameters.

BEGIN

DBMS_SCHEDULER.CREATE_JOB (

job_name => 'MY_JOB',

job_type => 'PLSQL_BLOCK',

job_action => 'BEGIN my_procedure; END;',

…

-- Disable a job

BEGIN

DBMS_SCHEDULER.DISABLE('MY_JOB');

END;

-- Enable a job

BEGIN

DBMS_SCHEDULER.ENABLE('MY_JOB');

END;

Dropping a Job:

If you want to remove a job from the scheduler, use the DBMS_SCHEDULER.DROP_JOB procedure.

sql

BEGIN

DBMS_SCHEDULER.DROP_JOB('MY_JOB');

END;

Remember to have the necessary privileges to create and manage jobs (e.g., CREATE JOB, CREATE PROCEDURE, CREATE ANY JOB, etc.).

Adjust the parameters and schedule according to your specific requirements. The provided example is a basic illustration, and you may need to customize it based on your needs.

--BY Tarif Ahmed

New Features of Oracle 19c

2024-01-11T15:53:00.000+05:30

New Features of Oracle 19c-

Oracle Database 19c refers to the 19th version of the Oracle Database, which is a relational database management system (RDBMS) developed and marketed by Oracle Corporation. The "c" in 19c stands for "Cloud," indicating Oracle's commitment to cloud computing and cloud-based solutions. Oracle 19c is the successor to Oracle Database 18c and comes with several new features, enhancements, and improvements over its predecessors.

Oracle Database 19c, released by Oracle Corporation, brought several significant features and enhancements to improve performance, manageability, and security. Here's a more detailed explanation:

Multitenant Architecture Enhancements:

Oracle 19c focused on enhancing its Multitenant Architecture, allowing for more efficient management and consolidation of databases. Notable improvements included features like PDB snapshots, hot cloning, and cross-container operations, streamlining administrative tasks.

Automatic Indexing:

A major highlight was the introduction of Automatic Indexing. This feature employs machine learning algorithms to automatically create and fine-tune indexes based on the observed workload patterns, simplifying the database optimization process.

Real-Time Statistics:

The addition of Real-Time Statistics aimed at improving query optimization. This feature ensures that the query optimizer has access to the most up-to-date statistics, particularly beneficial for partitioned tables.

SQL Quarantine:

Oracle 19c introduced SQL Quarantine, a feature designed to prevent the execution of SQL statements identified as problematic. This helps safeguard the database from potential performance issues caused by problematic queries.

In-Memory Database Enhancements:

Oracle Database In-Memory received enhancements, making it more versatile for both analytics and mixed workload environments. These improvements contributed to faster query performance and improved overall efficiency.

Blockchain Tables:

The introduction of Blockchain Tables aimed at simplifying the development and management of blockchain-enabled applications within the Oracle Database environment.

Enhanced Security Features:

Security received attention with various enhancements, including the introduction of new algorithms for Transparent Data Encryption (TDE), improvements in Data Redaction, and better integration with Oracle Key Vault for enhanced data protection.

JSON Enhancements:

Oracle 19c improved support for working with JSON data, introducing features like JSON_TRANSFORM, JSON_TABLE, and JSON_EXISTS, making it more seamless to work with JSON documents within the database.

Shading Enhancements:

Oracle Shading, a feature enabling the distribution and management of data across multiple discrete Oracle databases, saw improvements in manageability and performance, providing a more robust and scalable solution.

Database Replay:

The Database Replay functionality was enhanced, allowing for more effective testing and validation of database changes before implementing them in a production environment, reducing the risk of potential issues.

Machine Learning in the Database:

Oracle 19c integrated machine learning algorithms directly into the database. This inclusion allowed users to perform tasks such as predictive analysis, anomaly detection, and clustering without the need for external tools, bringing machine learning capabilities closer to the data.

AOAG Log Sync Analysis

2024-01-05T15:14:00.017+05:30

SELECT r.replica_server_name

AS Replica, DB_name(rs.database_id) 'database_name',

rs.is_primary_replica IsPrimary,

rs.last_received_lsn,

rs.last_hardened_lsn,

rs.last_redone_lsn,

rs.end_of_log_lsn,

rs.last_commit_lsn

FROM sys.availability_replicas r

INNER JOIN sys.dm_hadr_database_replica_states rs ON r.replica_id = rs.replica_id

ORDER BY replica;

select log_reuse_wait_desc from sys.databases where name like 'DB_Name'

SELECT ar.replica_server_name as Replica,

adc.database_name,

drs.is_local,

drs.is_primary_replica,

drs.synchronization_state_desc,

drs.is_commit_participant,

drs.synchronization_health_desc,

drs.recovery_lsn

FROM sys.dm_hadr_database_replica_states AS drs

INNER JOIN sys.availability_databases_cluster AS adc ON drs.group_id = adc.group_id

AND drs.group_database_id = adc.group_database_id

INNER JOIN sys.availability_groups AS ag ON ag.group_id = drs.group_id

INNER JOIN sys.availability_replicas AS ar ON drs.group_id = ar.group_id

AND drs.replica_id = ar.replica_id

ORDER BY Replica;

Secondary replica is lagging behind the Primary replica

SELECT ag.name AS ag_name, ar.replica_server_name AS ag_replica_server,

db_name(dr_state.database_id) AS database_name,

is_ag_replica_local = CASE

WHEN ar_state.is_local = 1 THEN N'LOCAL'

ELSE 'REMOTE'

END,

ag_replica_role = CASE

WHEN ar_state.role_desc IS NULL THEN N'DISCONNECTED'

ELSE ar_state.role_desc

END,

dr_state.last_hardened_lsn, dr_state.last_hardened_time,

datediff(s,last_hardened_time, getdate()) AS 'seconds behind primary'

FROM (( sys.availability_groups AS ag

JOIN sys.availability_replicas AS ar

ON ag.group_id = ar.group_id)

JOIN sys.dm_hadr_availability_replica_states AS ar_state

ON ar.replica_id = ar_state.replica_id)

JOIN sys.dm_hadr_database_replica_states dr_state

ON ag.group_id = dr_state.group_id AND dr_state.replica_id = ar_state.replica_id

Status of Replica

SELECT ag.name AS ag_name, ar.replica_server_name AS ag_replica_server,

db_name(dr_state.database_id) AS database_name,

is_ag_replica_local = CASE

WHEN ar_state.is_local = 1 THEN N'LOCAL'

ELSE 'REMOTE'

END,

ag_replica_role = CASE

WHEN ar_state.role_desc IS NULL THEN N'DISCONNECTED'

ELSE ar_state.role_desc

END,

ar_state.connected_state_desc, ar.availability_mode_desc, dr_state.synchronization_state_desc

FROM (( sys.availability_groups AS ag

JOIN sys.availability_replicas AS ar

ON ag.group_id = ar.group_id )

JOIN sys.dm_hadr_availability_replica_states AS ar_state

ON ar.replica_id = ar_state.replica_id)

JOIN sys.dm_hadr_database_replica_states dr_state

ON ag.group_id = dr_state.group_id AND dr_state.replica_id = ar_state.replica_id

Database Replica Log remaining for undo

SELECT [instance_name], [object_name], [counter_name], [cntr_value] FROM sys.dm_os_performance_counters WHERE [object_name] LIKE '%Database Replica%' AND [counter_name] = 'Log remaining for undo'

Monitor Log Send Queue Size

SELECT DB_Name(database_id) as 'DB_Name', log_send_queue_size, log_send_rate, redo_queue_size , redo_rate FROM sys.dm_hadr_database_replica_states

--WHERE is_local = 0;

log_send_queue_size: The amount of transaction log (in KB) that needs to be sent to the secondary replica.

log_send_rate: The rate at which the transaction log is being sent to the secondary replica (in KB per second).

redo_queue_size: The amount of transaction log (in KB) that needs to be redone on the secondary replica.

redo_rate: The rate at which the transaction log is being redone on the secondary replica (in KB per second).

DB Backup and Restore History

2024-01-05T15:11:00.005+05:30

--DB Backup History

SELECT CONVERT(CHAR(100), SERVERPROPERTY('Servername')) AS Server,msdb.dbo.backupset.database_name,msdb.dbo.backupset.backup_start_date,msdb.dbo.backupset.backup_finish_date,msdb.dbo.backupset.expiration_date,CASE msdb..backupset.type WHEN 'D' THEN 'Database' WHEN 'L' THEN 'Log' WHEN 'I' THEN 'Diff'END AS backup_type, msdb.dbo.backupset.backup_size, msdb.dbo.backupmediafamily.logical_device_name, msdb.dbo.backupmediafamily.physical_device_name, msdb.dbo.backupset.name AS backupset_name, msdb.dbo.backupset.description FROM msdb.dbo.backupmediafamily INNER JOIN msdb.dbo.backupset ON msdb.dbo.backupmediafamily.media_set_id = msdb.dbo.backupset.media_set_id WHERE (CONVERT(datetime, msdb.dbo.backupset.backup_start_date, 102) >= GETDATE() - 30) and msdb.dbo.backupset.database_name like 'DB_Name' order by 3 desc

--DB Restore History

SELECT [rh].[destination_database_name], [rh].[restore_date], [b].[backup_start_date], [b].[backup_finish_date], [b].[type], [b].[first_lsn], [b].[last_lsn], [b].[checkpoint_lsn], [b].[database_backup_lsn] FROM [msdb].[dbo].[restorehistory] AS [rh] JOIN [msdb].[dbo].[backupset] AS [b] ON [rh].[backup_set_id] = [b].[backup_set_id] WHERE [rh].[destination_database_name] = 'DB_Name' ORDER BY [rh].[restore_date] DESC;

======================================================================================================================

--DB Backup

--Full backup of DB_Name

BACKUP DATABASE [DB_Name] TO

DISK = N'T:\DB_Backup\DB_Name\DB_Name_FULL_Backup_1.bak',

DISK = N'T:\DB_Backup\DB_Name\DB_Name_FULL_Backup_2.bak',

DISK = N'T:\DB_Backup\DB_Name\DB_Name_FULL_Backup_3.bak',

DISK = N'T:\DB_Backup\DB_Name\DB_Name_FULL_Backup_4.bak',

DISK = N'T:\DB_Backup\DB_Name\DB_Name_FULL_Backup_5.bak',

DISK = N'T:\DB_Backup\DB_Name\DB_Name_FULL_Backup_6.bak',

DISK = N'T:\DB_Backup\DB_Name\DB_Name_FULL_Backup_7.bak',

DISK = N'T:\DB_Backup\DB_Name\DB_Name_FULL_Backup_8.bak',

DISK = N'T:\DB_Backup\DB_Name\DB_Name_FULL_Backup_9.bak',

DISK = N'T:\DB_Backup\DB_Name\DB_Name_FULL_Backup_10.bak'

WITH NOFORMAT, NOINIT,

NAME = N'DB_Name-Full Database Backup', SKIP, NOREWIND, NOUNLOAD, Compression, STATS = 1

--Diff backup of DB_Name

BACKUP DATABASE [DB_Name] TO
DISK = N'T:\DB_Backup\DB_Name_Diff_Backup_1.bak',
DISK = N'T:\DB_Backup\DB_Name_Diff_Backup_2.bak',
DISK = N'T:\DB_Backup\DB_Name_Diff_Backup_3.bak'
WITH DIFFERENTIAL, NOFORMAT, NOINIT,
NAME = N'Diff Backup', SKIP, NOREWIND, NOUNLOAD, compression, STATS = 10
GO

--T Log backup of DB_Name

BACKUP Log [DB_Name] TO DISK = N'T:\DB_Backup\DB_Name_T_Log_Backup_1.trn' WITH NOFORMAT, NOINIT, NAME = N'T:\DB_Backup\DB_Name-T_Log Database Backup', SKIP, NOREWIND, NOUNLOAD, compression, STATS = 1

======================================================================================================================

Restarting a single Db

use master

alter database YourDatabase

set offline

with rollback immediate

alter database YourDatabase

set online