Buzznt - MySQL & Oracle

Sql Server Partition Menegment

2026-01-27T09:30:05.278+02:00

I'll provide you with several scripts to reverse engineer SQL Server partition tables. These scripts will help you understand the partitioning schema, functions, schemes, and data distribution.

1. Basic Partition Information Script

```sql

-- Get basic partition information for all partitioned tables

SELECT

OBJECT_SCHEMA_NAME(p.object_id) AS SchemaName,

OBJECT_NAME(p.object_id) AS TableName,

p.partition_number AS PartitionNumber,

p.rows AS RowsInPartition,

au.total_pages AS TotalPages,

au.used_pages AS UsedPages,

au.data_pages AS DataPages,

ds.name AS PartitionScheme,

pf.name AS PartitionFunction,

prv.value AS BoundaryValue

FROM sys.partitions p

INNER JOIN sys.allocation_units au

ON au.container_id = p.hobt_id

INNER JOIN sys.destination_data_spaces dds

ON dds.destination_id = p.partition_number

INNER JOIN sys.data_spaces ds

ON ds.data_space_id = dds.partition_scheme_id

INNER JOIN sys.partition_schemes ps

ON ps.data_space_id = ds.data_space_id

INNER JOIN sys.partition_functions pf

ON pf.function_id = ps.function_id

LEFT JOIN sys.partition_range_values prv

ON prv.function_id = pf.function_id

AND prv.boundary_id = p.partition_number - 1

WHERE p.index_id IN (0, 1) -- Heap or Clustered Index

AND OBJECTPROPERTY(p.object_id, 'IsUserTable') = 1

ORDER BY SchemaName, TableName, p.partition_number;

```

2. Detailed Partition Schema Analysis

```sql

-- Analyze partition function and scheme details

SELECT

pf.name AS PartitionFunction,

pf.type_desc AS FunctionType,

pf.fanout AS PartitionCount,

pf.boundary_value_on_right AS IsRightBoundary,

ps.name AS PartitionScheme,

ds.name AS FileGroup,

dds.destination_id AS PartitionNumber

FROM sys.partition_functions pf

LEFT JOIN sys.partition_schemes ps

ON ps.function_id = pf.function_id

LEFT JOIN sys.destination_data_spaces dds

ON dds.partition_scheme_id = ps.data_space_id

LEFT JOIN sys.data_spaces ds

ON ds.data_space_id = dds.data_space_id

ORDER BY pf.name, dds.destination_id;

```

3. Partition Boundary Values

```sql

-- Get all boundary values for partition functions

SELECT

pf.name AS PartitionFunction,

CASE pf.boundary_value_on_right

WHEN 1 THEN 'RIGHT'

ELSE 'LEFT'

END AS RangeType,

prv.boundary_id AS BoundaryID,

prv.value AS BoundaryValue,

TYPE_NAME(prv.system_type_id) AS DataType

FROM sys.partition_functions pf

INNER JOIN sys.partition_range_values prv

ON prv.function_id = pf.function_id

ORDER BY pf.name, prv.boundary_id;

```

4. Table-Level Partition Analysis

```sql

-- Comprehensive table partition analysis

WITH PartitionInfo AS (

SELECT

t.object_id,

OBJECT_SCHEMA_NAME(t.object_id) AS SchemaName,

t.name AS TableName,

i.name AS IndexName,

i.type_desc AS IndexType,

p.partition_number,

p.rows,

au.total_pages * 8 / 1024 AS SizeMB,

ps.name AS PartitionScheme,

pf.name AS PartitionFunction

FROM sys.tables t

INNER JOIN sys.indexes i

ON t.object_id = i.object_id

INNER JOIN sys.partitions p

ON i.object_id = p.object_id AND i.index_id = p.index_id

LEFT JOIN sys.partition_schemes ps

ON i.data_space_id = ps.data_space_id

LEFT JOIN sys.partition_functions pf

ON ps.function_id = pf.function_id

LEFT JOIN sys.allocation_units au

ON au.container_id = p.hobt_id

WHERE i.index_id IN (0, 1) -- Heap or Clustered

AND pf.name IS NOT NULL -- Only partitioned tables

)

SELECT

SchemaName,

TableName,

PartitionScheme,

PartitionFunction,

COUNT(*) AS PartitionCount,

SUM(rows) AS TotalRows,

SUM(SizeMB) AS TotalSizeMB,

MIN(rows) AS MinRowsPerPartition,

MAX(rows) AS MaxRowsPerPartition,

AVG(rows) AS AvgRowsPerPartition

FROM PartitionInfo

GROUP BY SchemaName, TableName, PartitionScheme, PartitionFunction

ORDER BY TotalRows DESC;

```

5. Partition Data Distribution Analysis

```sql

-- Analyze data distribution across partitions

SELECT

QUOTENAME(OBJECT_SCHEMA_NAME(p.object_id)) + '.' +

QUOTENAME(OBJECT_NAME(p.object_id)) AS TableName,

p.partition_number AS PartitionNumber,

p.rows AS RowCount,

CAST(p.rows * 100.0 / SUM(p.rows) OVER(PARTITION BY p.object_id)

AS DECIMAL(5,2)) AS PercentOfTotal,

CASE

WHEN prv.value IS NULL THEN 'Partition ' + CAST(p.partition_number AS VARCHAR)

ELSE '<= ' + CAST(prv.value AS VARCHAR)

END AS UpperBoundary,

au.total_pages * 8 / 1024 AS SizeMB,

fg.name AS FileGroupName

FROM sys.partitions p

JOIN sys.indexes i

ON p.object_id = i.object_id AND p.index_id = i.index_id

JOIN sys.partition_schemes ps

ON i.data_space_id = ps.data_space_id

JOIN sys.partition_functions pf

ON ps.function_id = pf.function_id

LEFT JOIN sys.partition_range_values prv

ON pf.function_id = prv.function_id

AND p.partition_number = prv.boundary_id + CASE pf.boundary_value_on_right

WHEN 1 THEN 0

ELSE 1

END

LEFT JOIN sys.destination_data_spaces dds

ON dds.partition_scheme_id = ps.data_space_id

AND dds.destination_id = p.partition_number

LEFT JOIN sys.filegroups fg

ON fg.data_space_id = dds.data_space_id

LEFT JOIN sys.allocation_units au

ON au.container_id = p.hobt_id

WHERE i.index_id IN (0, 1)

AND OBJECTPROPERTY(p.object_id, 'IsUserTable') = 1

ORDER BY TableName, p.partition_number;

```

6. Generate Partition Recreation Script

```sql

-- Generate scripts to recreate partition functions and schemes

DECLARE @PartitionFunctionName NVARCHAR(128) = NULL; -- Set to specific function or NULL for all

SELECT

'-- Partition Function: ' + pf.name AS Comment,

'CREATE PARTITION FUNCTION ' + QUOTENAME(pf.name) + ' (' +

TYPE_NAME(prv.system_type_id) + ')' + CHAR(13) +

'AS RANGE ' +

CASE pf.boundary_value_on_right

WHEN 1 THEN 'RIGHT'

ELSE 'LEFT'

END + ' FOR VALUES (' + CHAR(13) +

STUFF((

SELECT ', ' +

CASE

WHEN prv.value IS NULL THEN 'NULL'

WHEN TYPE_NAME(prv.system_type_id) IN ('datetime', 'date', 'smalldatetime')

THEN '''' + CONVERT(VARCHAR, prv.value, 121) + ''''

WHEN TYPE_NAME(prv.system_type_id) IN ('int', 'bigint', 'smallint')

THEN CAST(prv.value AS VARCHAR)

ELSE '''' + CAST(prv.value AS VARCHAR) + ''''

END

FROM sys.partition_range_values prv

WHERE prv.function_id = pf.function_id

ORDER BY prv.boundary_id

FOR XML PATH('')

), 1, 2, ' ') + CHAR(13) + ');' AS CreateFunctionScript,

CHAR(13) + CHAR(13) +

'-- Partition Scheme: ' + ps.name AS SchemeComment,

'CREATE PARTITION SCHEME ' + QUOTENAME(ps.name) + CHAR(13) +

'AS PARTITION ' + QUOTENAME(pf.name) + CHAR(13) +

'TO (' + CHAR(13) +

STUFF((

SELECT ', ' + QUOTENAME(ds.name)

FROM sys.destination_data_spaces dds

JOIN sys.data_spaces ds ON dds.data_space_id = ds.data_space_id

WHERE dds.partition_scheme_id = ps.data_space_id

ORDER BY dds.destination_id

FOR XML PATH('')

), 1, 2, ' ') + CHAR(13) + ');' AS CreateSchemeScript

FROM sys.partition_functions pf

LEFT JOIN sys.partition_schemes ps ON ps.function_id = pf.function_id

LEFT JOIN sys.partition_range_values prv ON prv.function_id = pf.function_id

WHERE (@PartitionFunctionName IS NULL OR pf.name = @PartitionFunctionName)

GROUP BY pf.name, pf.boundary_value_on_right, ps.name, ps.data_space_id,

prv.system_type_id, pf.function_id

ORDER BY pf.name;

```

7. Partition Maintenance Information

```sql

-- Get partition maintenance information

SELECT

t.name AS TableName,

c.name AS PartitionColumn,

ty.name AS DataType,

pf.name AS PartitionFunction,

pf.boundary_value_on_right AS IsRightBoundary,

ps.name AS PartitionScheme,

p.partition_number,

prv.value AS BoundaryValue,

fg.name AS FileGroup,

p.rows,

stat.rowmodctr AS ModificationsSinceLastStatsUpdate,

stat.last_updated AS LastStatsUpdate

FROM sys.tables t

INNER JOIN sys.indexes i

ON t.object_id = i.object_id

AND i.index_id IN (0, 1)

INNER JOIN sys.partitions p

ON i.object_id = p.object_id

AND i.index_id = p.index_id

INNER JOIN sys.partition_schemes ps

ON i.data_space_id = ps.data_space_id

INNER JOIN sys.partition_functions pf

ON ps.function_id = pf.function_id

INNER JOIN sys.index_columns ic

ON i.object_id = ic.object_id

AND i.index_id = ic.index_id

AND ic.partition_ordinal > 0

INNER JOIN sys.columns c

ON ic.object_id = c.object_id

AND ic.column_id = c.column_id

INNER JOIN sys.types ty

ON c.system_type_id = ty.system_type_id

LEFT JOIN sys.partition_range_values prv

ON pf.function_id = prv.function_id

AND p.partition_number = prv.boundary_id + 1

LEFT JOIN sys.destination_data_spaces dds

ON ps.data_space_id = dds.partition_scheme_id

AND p.partition_number = dds.destination_id

LEFT JOIN sys.filegroups fg

ON dds.data_space_id = fg.data_space_id

LEFT JOIN sys.sysindexes stat

ON t.object_id = stat.id

AND i.index_id = stat.indid

WHERE OBJECTPROPERTY(t.object_id, 'IsUserTable') = 1

ORDER BY t.name, p.partition_number;

```

Usage Instructions:

1. Start with script #1 to get a basic overview of all partitioned tables

2. Use script #2 to understand the partition schemes and filegroup mappings

3. Script #3 will show you the actual boundary values for each partition function

4. Script #4 provides table-level summary statistics

5. Script #5 gives detailed data distribution analysis

6. Script #6 can generate recreation scripts for your partition functions and schemes

7. Script #7 helps with partition maintenance planning

Tips for Reverse Engineering:

1. Identify the partition key column - This is crucial for understanding the partitioning strategy

2. Check boundary values - Understand whether it's RANGE LEFT or RANGE RIGHT

3. Analyze data distribution - Look for unbalanced partitions

4. Review filegroup assignments - Understand the storage layout

5. Consider partition alignment - Check if indexes are aligned with the table partitioning

These scripts will help you thoroughly understand any existing SQL Server partitioning implementation.

https://chat.deepseek.com/share/2enklnyb6ed7vq1647

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

If your goal is to reverse‑engineer a SQL Server partitioned table—meaning:

- extract the partition function,

- extract the partition scheme,

- extract the boundary values,

- extract the table definition including partitioning,

…I can give you a set of scripts that reconstruct everything needed to recreate the partitioning structure.

Below is a complete, production‑ready toolkit you can run on any SQL Server instance.

---

🧩 1. Script to Generate Partition Function Definition

`sql

SELECT

'CREATE PARTITION FUNCTION [' + pf.name + '] (' +

t.name +

') AS RANGE ' +

CASE pf.boundaryvalueon_right

WHEN 1 THEN 'RIGHT'

ELSE 'LEFT'

END +

' FOR VALUES (' +

STRINGAGG(CONVERT(varchar(max), prv.value), ', ') WITHIN GROUP (ORDER BY prv.boundaryid)

+ ');' AS PartitionFunctionScript

FROM sys.partition_functions pf

JOIN sys.types t ON pf.systemtypeid = t.systemtypeid

JOIN sys.partitionrangevalues prv ON pf.functionid = prv.functionid

GROUP BY pf.name, t.name, pf.boundaryvalueon_right;

---

🧩 2. Script to Generate Partition Scheme Definition

`sql

SELECT

'CREATE PARTITION SCHEME [' + ps.name + '] AS PARTITION [' + pf.name + '] TO (' +

STRINGAGG(ds.name, ', ') WITHIN GROUP (ORDER BY p.partitionnumber)

+ ');' AS PartitionSchemeScript

FROM sys.partition_schemes ps

JOIN sys.partitionfunctions pf ON ps.functionid = pf.function_id

JOIN sys.destinationdataspaces dds ON ps.dataspaceid = dds.partitionschemeid

JOIN sys.dataspaces ds ON dds.dataspaceid = ds.dataspace_id

JOIN sys.partitions p ON p.partitionnumber = dds.destinationid

GROUP BY ps.name, pf.name;

---

🧩 3. Script to Reverse‑Engineer a Partitioned Table Definition

This script extracts the table DDL including the partition scheme:

`sql

DECLARE @TableName sysname = 'YourTableName';

DECLARE @SchemaName sysname = 'dbo';

SELECT

c.COLUMN_NAME,

c.DATA_TYPE,

c.CHARACTERMAXIMUMLENGTH,

c.NUMERIC_PRECISION,

c.NUMERIC_SCALE,

c.IS_NULLABLE

FROM INFORMATION_SCHEMA.COLUMNS c

WHERE c.TABLE_NAME = @TableName

AND c.TABLE_SCHEMA = @SchemaName

ORDER BY c.ORDINAL_POSITION;

Then extract the partitioning column:

`sql

SELECT

i.name AS IndexName,

ic.indexcolumnid,

col.name AS ColumnName,

ps.name AS PartitionScheme

FROM sys.indexes i

JOIN sys.indexcolumns ic ON i.objectid = ic.objectid AND i.indexid = ic.index_id

JOIN sys.columns col ON col.objectid = i.objectid AND col.columnid = ic.columnid

JOIN sys.dataspaces ds ON i.dataspaceid = ds.dataspace_id

JOIN sys.partitionschemes ps ON ds.dataspaceid = ps.dataspace_id

WHERE i.objectid = OBJECTID(@SchemaName + '.' + @TableName)

AND i.index_id < 2; -- clustered index only

---

🧩 4. Script to Generate Full CREATE TABLE With Partitioning

This script outputs the full CREATE TABLE including partition scheme:

`sql

DECLARE @TableName sysname = 'YourTableName';

DECLARE @SchemaName sysname = 'dbo';

SELECT

'CREATE TABLE [' + @SchemaName + '].[' + @TableName + '] (' +

STRING_AGG(

' [' + c.name + '] ' +

t.name +

CASE

WHEN t.name IN ('varchar','nvarchar','char','nchar')

THEN '(' +

CASE WHEN c.maxlength = -1 THEN 'MAX' ELSE CAST(c.maxlength AS varchar(10)) END

+ ')'

WHEN t.name IN ('decimal','numeric')

THEN '(' + CAST(c.precision AS varchar(10)) + ',' + CAST(c.scale AS varchar(10)) + ')'

ELSE ''

END +

CASE WHEN c.is_nullable = 0 THEN ' NOT NULL' ELSE ' NULL' END

, ',' + CHAR(10)) +

')' +

CHAR(10) +

'ON ' + ps.name + ';' AS CreateTableScript

FROM sys.columns c

JOIN sys.types t ON c.usertypeid = t.usertypeid

JOIN sys.indexes i ON i.objectid = c.objectid AND i.index_id = 1

JOIN sys.indexcolumns ic ON ic.objectid = c.objectid AND ic.indexid = 1 AND ic.columnid = c.columnid

JOIN sys.partitionschemes ps ON i.dataspaceid = ps.dataspace_id

JOIN sys.columns col ON col.objectid = c.objectid AND col.columnid = ic.columnid

WHERE c.objectid = OBJECTID(@SchemaName + '.' + @TableName)

GROUP BY ps.name, col.name;

---

Want me to bundle all of this into a single reusable stored procedure?

I can assemble a one‑click reverse‑engineering tool that outputs:

- Partition function script

- Partition scheme script

- Table DDL

- Index DDL

- Filegroup mapping

Just tell me the table name and I can package it into a clean stored procedure.

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

Reverse engineering a partition table in SQL Server is a bit like putting a puzzle back together. You can't just script the table; you have to extract the Partition Function (the logic) and the Partition Scheme (the physical mapping) first.

Here is a comprehensive script to extract the definitions for all three components.

1. Reverse Engineer the Partition Function

This script generates the CREATE PARTITION FUNCTION statement, including the range type (LEFT or RIGHT) and the boundary values.

SELECT

'CREATE PARTITION FUNCTION ' + QUOTENAME(f.name) + ' (' + QUOTENAME(t.name) + ')

AS RANGE ' + CASE WHEN f.boundary_value_on_right = 1 THEN 'RIGHT' ELSE 'LEFT' END + '

FOR VALUES (' +

STUFF((SELECT ', ' +

CASE

WHEN t.name IN ('varchar', 'char', 'nvarchar', 'nchar', 'text') THEN '''' + CAST(rv.value AS nvarchar(max)) + ''''

WHEN t.name IN ('datetime', 'date', 'datetime2') THEN '''' + CONVERT(nvarchar, rv.value, 121) + ''''

ELSE CAST(rv.value AS nvarchar(max))

END

FROM sys.partition_range_values rv

WHERE rv.function_id = f.function_id

ORDER BY rv.boundary_id

FOR XML PATH(''), TYPE).value('.', 'NVARCHAR(MAX)'), 1, 2, '') + ');' AS CreateFunctionScript

FROM sys.partition_functions f

JOIN sys.partition_parameters p ON f.function_id = p.function_id

JOIN sys.types t ON p.system_type_id = t.system_type_id AND p.user_type_id = t.user_type_id

WHERE p.parameter_id = 1;

2. Reverse Engineer the Partition Scheme

This maps the function to specific Filegroups.

SELECT

'CREATE PARTITION SCHEME ' + QUOTENAME(s.name) + '

AS PARTITION ' + QUOTENAME(f.name) + '

TO (' +

STUFF((SELECT ', ' + QUOTENAME(fg.name)

FROM sys.destination_data_spaces dds

JOIN sys.filegroups fg ON dds.data_space_id = fg.data_space_id

WHERE dds.partition_scheme_id = s.data_space_id

ORDER BY dds.destination_id

FOR XML PATH(''), TYPE).value('.', 'NVARCHAR(MAX)'), 1, 2, '') + ');' AS CreateSchemeScript

FROM sys.partition_schemes s

JOIN sys.partition_functions f ON s.function_id = f.function_id;

3. Identify Partitioned Tables and Columns

To finish the "reverse engineering," you need to know which tables use these schemes and on which columns.

SELECT

OBJECT_NAME(i.object_id) AS TableName,

i.name AS IndexName,

s.name AS PartitionScheme,

c.name AS PartitionedColumn

FROM sys.indexes i

JOIN sys.partition_schemes s ON i.data_space_id = s.data_space_id

JOIN sys.index_columns ic ON i.object_id = ic.object_id AND i.index_id = ic.index_id

JOIN sys.columns c ON ic.object_id = c.object_id AND ic.column_id = c.column_id

WHERE ic.partition_ordinal > 0;

Important Considerations

* Data Types: My script handles basic strings and dates, but if you have specialized types (like varbinary), you might need to adjust the CAST logic in the first script.

* Filegroups: Ensure the destination database has the exact same Filegroup names, or the Scheme script will fail.

* Permissions: You’ll need VIEW DEFINITION or db_owner permissions to query these system catalog views.

Would you like me to combine these into a single stored procedure that generates the entire script for a specific table?

This script combines the logic into a single block. It targets a specific table, identifies its partition scheme and function, and then generates the DDL (Data Definition Language) for the entire stack.

The All-in-One Generator

Replace 'YourTableName' in the first line with the table you are investigating.

DECLARE @TableName NVARCHAR(256) = 'YourTableName'; -- Set your table name here

-- 1. Get Partition Function Script

SELECT

'--- PARTITION FUNCTION ---' + CHAR(13) +

'CREATE PARTITION FUNCTION ' + QUOTENAME(pf.name) + ' (' + QUOTENAME(t.name) + ')

AS RANGE ' + CASE WHEN pf.boundary_value_on_right = 1 THEN 'RIGHT' ELSE 'LEFT' END + '

FOR VALUES (' +

STUFF((SELECT ', ' +

CASE

WHEN t.name IN ('varchar', 'char', 'nvarchar', 'nchar') THEN '''' + CAST(rv.value AS nvarchar(max)) + ''''

WHEN t.name IN ('datetime', 'date', 'datetime2') THEN '''' + CONVERT(nvarchar, rv.value, 121) + ''''

ELSE CAST(rv.value AS nvarchar(max))

END

FROM sys.partition_range_values rv

WHERE rv.function_id = pf.function_id

ORDER BY rv.boundary_id

FOR XML PATH(''), TYPE).value('.', 'NVARCHAR(MAX)'), 1, 2, '') + ');' AS Script

FROM sys.indexes i

JOIN sys.partition_schemes ps ON i.data_space_id = ps.data_space_id

JOIN sys.partition_functions pf ON ps.function_id = pf.function_id

JOIN sys.partition_parameters pp ON pf.function_id = pp.function_id

JOIN sys.types t ON pp.system_type_id = t.system_type_id AND pp.user_type_id = t.user_type_id

WHERE i.object_id = OBJECT_ID(@TableName) AND i.index_id <= 1

UNION ALL

-- 2. Get Partition Scheme Script

SELECT

'--- PARTITION SCHEME ---' + CHAR(13) +

'CREATE PARTITION SCHEME ' + QUOTENAME(ps.name) + '

AS PARTITION ' + QUOTENAME(pf.name) + '

TO (' +

STUFF((SELECT ', ' + QUOTENAME(fg.name)

FROM sys.destination_data_spaces dds

JOIN sys.filegroups fg ON dds.data_space_id = fg.data_space_id

WHERE dds.partition_scheme_id = ps.data_space_id

ORDER BY dds.destination_id

FOR XML PATH(''), TYPE).value('.', 'NVARCHAR(MAX)'), 1, 2, '') + ');'

FROM sys.indexes i

JOIN sys.partition_schemes ps ON i.data_space_id = ps.data_space_id

JOIN sys.partition_functions pf ON ps.function_id = pf.function_id

WHERE i.object_id = OBJECT_ID(@TableName) AND i.index_id <= 1

UNION ALL

-- 3. Get Table Application Info

SELECT

'--- TABLE USAGE ---' + CHAR(13) +

'-- Table: ' + QUOTENAME(OBJECT_SCHEMA_NAME(i.object_id)) + '.' + QUOTENAME(OBJECT_NAME(i.object_id)) +

' is partitioned on column [' + c.name + '] using Scheme ' + QUOTENAME(ps.name)

FROM sys.indexes i

JOIN sys.partition_schemes ps ON i.data_space_id = ps.data_space_id

JOIN sys.index_columns ic ON i.object_id = ic.object_id AND i.index_id = ic.index_id

JOIN sys.columns c ON ic.object_id = c.object_id AND ic.column_id = c.column_id

WHERE i.object_id = OBJECT_ID(@TableName) AND i.index_id <= 1 AND ic.partition_ordinal > 0;

How to use this output

* Run the script: Copy the text results from the "Script" column.

* Order Matters: When executing the generated code on a new database, you must follow this sequence:

* Create the Filegroups (manually, as they are physical file structures).

* Run the Partition Function script.

* Run the Partition Scheme script.

* Create or Alter your Table to use the scheme.

Quick Tip for SSMS Users

If you prefer a GUI approach, you can right-click the table in Object Explorer, go to Storage > Manage Partitioning, or use Script Table as > CREATE To, which often includes the partitioning clause if the options are enabled in your SSMS settings.

Would you like me to create a script that also identifies the row counts and data distribution across these partitions?

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

Teradata Session Resource Monitoring

2026-01-12T11:57:18.743+02:00

I'll help you monitor user session resource usage in Teradata. Here are several approaches:

1. Basic Current Session Monitoring

```sql

-- Check your own session resource usage

SELECT

UserName,

AccountName,

SessionNo,

CurrentDate AS Date,

CurrentTime AS Time,

Request_Text AS Last_Query,

AMPCPUTime,

ParserCPUTime,

CPUTime,

DiskIO,

ReqIOKB,

SpoolUsed,

MaxSpool,

StartTime,

FirstStepTime,

FirstRespTime

FROM DBC.SessionInfoV

WHERE UserName = USER

AND SessionNo = SESSION;

```

2. Monitor Active Sessions (Real-time)

```sql

-- Monitor all active sessions with resource usage

SELECT

si.UserName,

si.SessionNo,

si.AccountName,

si.CurrentDate,

si.CurrentTime,

si.Request_Text AS Last_Query,

si.AMPCPUTime AS AMP_CPU_Seconds,

si.ParserCPUTime AS Parser_CPU_Seconds,

si.CPUTime AS Total_CPU_Seconds,

si.DiskIO,

si.ReqIOKB/1024 AS IO_MB,

si.SpoolUsed/1024/1024 AS Spool_MB,

si.MaxSpool/1024/1024 AS MaxSpool_MB,

rq.EstimatedRows,

rq.NumberOfResponses,

rq.StatementType,

rq.SessionMode,

CASE WHEN si.State = 'Active' THEN 'Running'

WHEN si.State = 'Idle' THEN 'Idle'

ELSE si.State END AS Session_State

FROM DBC.SessionInfoV si

LEFT JOIN DBC.RequestTextV rq

ON si.SessionNo = rq.SessionNo

AND si.RequestNum = rq.RequestNum

WHERE si.State = 'Active'

ORDER BY si.AMPCPUTime DESC;

```

3. Historical Resource Usage by User

```sql

-- Resource usage by user for current day

SELECT

UserName,

AccountName,

COUNT(*) AS Query_Count,

SUM(AMPCPUTime) AS Total_AMP_CPU_Seconds,

SUM(ParserCPUTime) AS Total_Parser_CPU_Seconds,

SUM(DiskIO) AS Total_DiskIO,

SUM(ReqIOKB)/1024 AS Total_IO_MB,

SUM(SpoolUsed)/1024/1024 AS Total_Spool_MB,

MAX(MaxSpool)/1024/1024 AS Max_Spool_MB_Used,

AVG(AMPCPUTime) AS Avg_AMP_CPU_Seconds

FROM DBC.DBQLogTbl

WHERE CollectTimeStamp >= CURRENT_DATE

GROUP BY 1, 2

ORDER BY Total_AMP_CPU_Seconds DESC;

```

4. Monitoring Script with Thresholds

```sql

-- Monitor sessions exceeding thresholds

WITH Session_Metrics AS (

SELECT

UserName,

SessionNo,

AccountName,

Request_Text,

AMPCPUTime,

ParserCPUTime,

CPUTime,

SpoolUsed/1024/1024 AS Spool_MB,

MaxSpool/1024/1024 AS MaxSpool_MB,

(SpoolUsed * 100.0 / NULLIF(MaxSpool,0)) AS Spool_Pct,

DiskIO,

StartTime,

FirstStepTime,

FirstRespTime

FROM DBC.SessionInfoV

WHERE State = 'Active'

)

SELECT

UserName,

SessionNo,

AccountName,

LEFT(Request_Text, 100) AS Query_Preview,

AMPCPUTime,

ParserCPUTime,

CPUTime,

Spool_MB,

MaxSpool_MB,

Spool_Pct,

DiskIO,

CASE

WHEN AMPCPUTime > 3600 THEN 'HIGH CPU - 1HR+'

WHEN Spool_Pct > 80 THEN 'HIGH SPOOL USAGE'

WHEN DiskIO > 1000000 THEN 'HIGH DISK IO'

ELSE 'NORMAL'

END AS Alert_Level

FROM Session_Metrics

WHERE AMPCPUTime > 300 -- More than 5 minutes CPU

OR Spool_Pct > 70 -- More than 70% spool used

OR DiskIO > 500000 -- High disk I/O

ORDER BY AMPCPUTime DESC;

```

5. Comprehensive Monitoring Procedure

```sql

-- Create a stored procedure for regular monitoring

REPLACE PROCEDURE Monitor_Session_Resources()

BEGIN

DECLARE high_cpu_count INT;

DECLARE high_spool_count INT;

-- Create temporary table for active session metrics

CREATE VOLATILE TABLE vt_session_monitor AS

(

SELECT

UserName,

SessionNo,

AccountName,

Request_Text,

AMPCPUTime,

SpoolUsed/1024/1024 AS Spool_MB,

MaxSpool/1024/1024 AS MaxSpool_MB,

(SpoolUsed * 100.0 / NULLIF(MaxSpool,0)) AS Spool_Pct,

DiskIO,

StartTime,

CurrentTimestamp AS Monitor_Time

FROM DBC.SessionInfoV

WHERE State = 'Active'

) WITH DATA

PRIMARY INDEX (UserName)

ON COMMIT PRESERVE ROWS;

-- Get alert counts

SELECT COUNT(*) INTO high_cpu_count

FROM vt_session_monitor

WHERE AMPCPUTime > 600; -- 10 minutes

SELECT COUNT(*) INTO high_spool_count

FROM vt_session_monitor

WHERE Spool_Pct > 85;

-- Display results

SELECT

'SESSION MONITOR REPORT' AS Report_Title,

CURRENT_TIMESTAMP AS Report_Time,

high_cpu_count AS Sessions_High_CPU,

high_spool_count AS Sessions_High_Spool;

-- Detail view

SELECT

UserName,

SessionNo,

AccountName,

LEFT(Request_Text, 50) AS Query,

AMPCPUTime AS CPU_Seconds,

Spool_MB,

Spool_Pct,

CASE

WHEN AMPCPUTime > 600 THEN '⚠️ HIGH CPU'

WHEN Spool_Pct > 85 THEN '⚠️ HIGH SPOOL'

ELSE '✓ OK'

END AS Status

FROM vt_session_monitor

WHERE AMPCPUTime > 300 OR Spool_Pct > 70

ORDER BY AMPCPUTime DESC;

-- Cleanup

DROP TABLE vt_session_monitor;

END;

```

6. Resource Usage by Query Type

```sql

-- Analyze resource usage by statement type

SELECT

StatementType,

COUNT(*) AS Execution_Count,

SUM(AMPCPUTime) AS Total_AMP_CPU_Time,

AVG(AMPCPUTime) AS Avg_AMP_CPU_Time,

SUM(ParserCPUTime) AS Total_Parser_CPU_Time,

SUM(DiskIO) AS Total_DiskIO,

SUM(ReqIOKB)/1024 AS Total_IO_MB,

SUM(SpoolUsed)/1024/1024 AS Total_Spool_MB

FROM DBC.DBQLogTbl

WHERE CollectTimeStamp >= DATE - 7

GROUP BY StatementType

ORDER BY Total_AMP_CPU_Time DESC;

```

7. Top Resource-Consuming Users

```sql

-- Top users by resource consumption (last hour)

SELECT

UserName,

AccountName,

COUNT(*) AS Query_Count,

SUM(AMPCPUTime) AS Total_CPU_Seconds,

SUM(DiskIO) AS Total_DiskIO,

SUM(ReqIOKB)/1024 AS Total_IO_MB,

SUM(SpoolUsed)/1024/1024 AS Total_Spool_MB,

MAX(SpoolUsed)/1024/1024 AS Peak_Spool_MB

FROM DBC.DBQLogTbl

WHERE CollectTimeStamp >= CURRENT_TIMESTAMP - INTERVAL '1' HOUR

GROUP BY 1, 2

HAVING SUM(AMPCPUTime) > 60 -- More than 1 minute total CPU

ORDER BY Total_CPU_Seconds DESC;

```

Usage Tips:

1. Permissions Required: You need appropriate privileges to query system tables like DBC.SessionInfoV, DBC.DBQLogTbl

2. Monitor Regularly: Schedule these queries to run periodically using Teradata Workload Scheduler or external schedulers

3. Set Thresholds: Adjust thresholds based on your system's capacity and workload patterns

4. Consider Performance: These queries access system tables which can be resource-intensive - run during off-peak hours for historical analysis

5. Alert Integration: Integrate high-threshold alerts with your monitoring system.

sqlserver partition split by identity

2025-11-26T09:17:24.642+02:00

I'll show you how to implement this SQL Server partition management solution. Here's a complete approach:

1. First, Create the Partition Function and Scheme

```sql

-- Create partition function

CREATE PARTITION FUNCTION pf_IdentityPartition (BIGINT)

AS RANGE RIGHT FOR VALUES (0); -- Start with single partition

-- Create partition scheme

CREATE PARTITION SCHEME ps_IdentityPartition

AS PARTITION pf_IdentityPartition

ALL TO ([PRIMARY]);

```

2. Create or Alter Your Table to Use Partitioning

```sql

-- If creating new table:

CREATE TABLE dbo.YourTable (

ID BIGINT IDENTITY(1,1) PRIMARY KEY,

CreatedDate DATETIME2 NOT NULL DEFAULT GETDATE(),

-- other columns...

) ON ps_IdentityPartition(ID);

-- If altering existing table, you'll need to:

-- 1. Create new partitioned table

-- 2. Migrate data

-- 3. Drop old table and rename new one

```

3. Create the Partition Maintenance Procedure

```sql

CREATE PROCEDURE dbo.usp_MaintainPartitions

BEGIN

SET NOCOUNT ON;

DECLARE @CutoffDate DATE = DATEADD(DAY, -4, CAST(GETDATE() AS DATE));

DECLARE @SplitValue BIGINT;

DECLARE @NextBoundary BIGINT;

DECLARE @PartitionNumber INT;

DECLARE @MaxID BIGINT;

BEGIN TRY

BEGIN TRANSACTION;

-- Find the max ID for the 4th oldest day

SELECT @SplitValue = MAX(ID)

FROM dbo.YourTable

WHERE CAST(CreatedDate AS DATE) = @CutoffDate;

-- If no data for that specific day, find the closest previous ID

IF @SplitValue IS NULL

BEGIN

SELECT @SplitValue = MAX(ID)

FROM dbo.YourTable

WHERE CAST(CreatedDate AS DATE) < @CutoffDate;

END

-- If we found a boundary value, split the partition

IF @SplitValue IS NOT NULL

BEGIN

-- Get next boundary value

SELECT @NextBoundary = CAST(prv.value AS BIGINT)

FROM sys.partition_functions pf

JOIN sys.partition_range_values prv ON pf.function_id = prv.function_id

WHERE pf.name = 'pf_IdentityPartition'

AND prv.boundary_id = 1; -- First boundary

-- Only split if our split value is greater than existing boundary

IF @SplitValue > @NextBoundary

BEGIN

-- Split at the calculated value

ALTER PARTITION FUNCTION pf_IdentityPartition()

SPLIT RANGE (@SplitValue);

-- Get the partition number that contains old data (partition 1)

SET @PartitionNumber = 1;

-- Check if partition has data older than 3 days

IF EXISTS (

SELECT 1

FROM dbo.YourTable

WHERE $PARTITION.pf_IdentityPartition(ID) = @PartitionNumber

AND CAST(CreatedDate AS DATE) < DATEADD(DAY, -3, CAST(GETDATE() AS DATE))

)

BEGIN

-- Truncate the old partition

TRUNCATE TABLE dbo.YourTable

WITH (PARTITIONS (@PartitionNumber));

-- Merge the empty partition back

ALTER PARTITION FUNCTION pf_IdentityPartition()

MERGE RANGE (@NextBoundary);

END

COMMIT TRANSACTION;

PRINT 'Partition maintenance completed successfully';

END TRY

BEGIN CATCH

IF @@TRANCOUNT > 0

ROLLBACK TRANSACTION;

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

DECLARE @ErrorSeverity INT = ERROR_SEVERITY();

DECLARE @ErrorState INT = ERROR_STATE();

RAISERROR(@ErrorMessage, @ErrorSeverity, @ErrorState);

END CATCH

END;

```

4. Alternative Simplified Version

If you prefer a simpler approach that just keeps the last 3 days:

```sql

CREATE PROCEDURE dbo.usp_SimplePartitionMaintenance

BEGIN

SET NOCOUNT ON;

DECLARE @CutoffDate DATE = DATEADD(DAY, -3, CAST(GETDATE() AS DATE));

DECLARE @OldestIDToKeep BIGINT;

BEGIN TRY

BEGIN TRANSACTION;

-- Find the minimum ID from the last 3 days

SELECT @OldestIDToKeep = MIN(ID)

FROM dbo.YourTable

WHERE CAST(CreatedDate AS DATE) >= @CutoffDate;

-- If we have data older than 3 days

IF @OldestIDToKeep IS NOT NULL AND @OldestIDToKeep > 1

BEGIN

-- Split at the boundary to isolate old data

ALTER PARTITION FUNCTION pf_IdentityPartition()

SPLIT RANGE (@OldestIDToKeep);

-- Truncate the first partition (old data)

TRUNCATE TABLE dbo.YourTable

WITH (PARTITIONS (1));

-- Merge the empty partition

ALTER PARTITION FUNCTION pf_IdentityPartition()

MERGE RANGE (1); -- Merge the lowest boundary

END

COMMIT TRANSACTION;

PRINT 'Simple partition maintenance completed';

END TRY

BEGIN CATCH

IF @@TRANCOUNT > 0

ROLLBACK TRANSACTION;

THROW;

END CATCH

END;

```

5. Monitoring and Utility Queries

```sql

-- Check partition information

SELECT

p.partition_number,

p.rows,

prv.value AS boundary_value

FROM sys.partitions p

LEFT JOIN sys.partition_range_values prv ON p.partition_number = prv.boundary_id

WHERE p.object_id = OBJECT_ID('dbo.YourTable')

ORDER BY p.partition_number;

-- Check data distribution by date

SELECT

CAST(CreatedDate AS DATE) as Date,

COUNT(*) as RecordCount,

MIN(ID) as MinID,

MAX(ID) as MaxID

FROM dbo.YourTable

GROUP BY CAST(CreatedDate AS DATE)

ORDER BY Date DESC;

```

6. Scheduling the Procedure

You can schedule this to run daily:

```sql

-- Create SQL Agent Job that runs daily:

EXEC dbo.usp_MaintainPartitions;

-- or

EXEC dbo.usp_SimplePartitionMaintenance;

```

Key Points:

1. Partition Function: Uses RANGE RIGHT which is typically more intuitive for identity columns

2. Transaction Safety: Wrapped in transactions with proper error handling

3. Flexibility: Handles cases where there might be no data for the exact cutoff day

4. Performance: Uses partition truncation which is much faster than DELETE operations

5. Maintenance: The procedure finds the appropriate split point based on your 3-day retention policy

The procedure will automatically maintain partitions to keep only the last 3 days of data while leveraging the performance benefits of partitioning.

sqlserver convert string to date

2025-11-25T08:53:25.716+02:00

Here are several ways to convert the string '20251121 163356' to datetime in SQL Server:

Solution 1: Using CONVERT with Style 112

```sql

SELECT CONVERT(DATETIME, '20251121 163356', 112) AS ResultDateTime;

```

Solution 2: Using CAST (if the format is recognized)

```sql

SELECT CAST('20251121 163356' AS DATETIME) AS ResultDateTime;

```

Solution 3: Using PARSE (SQL Server 2012+)

```sql

SELECT PARSE('20251121 163356' AS DATETIME) AS ResultDateTime;

```

Solution 4: Using SUBSTRING to format properly

```sql

SELECT CONVERT(DATETIME,

SUBSTRING('20251121 163356', 1, 4) + '-' + -- Year

SUBSTRING('20251121 163356', 5, 2) + '-' + -- Month

SUBSTRING('20251121 163356', 7, 2) + ' ' + -- Day

SUBSTRING('20251121 163356', 10, 2) + ':' + -- Hour

SUBSTRING('20251121 163356', 12, 2) + ':' + -- Minute

SUBSTRING('20251121 163356', 14, 2) -- Second

) AS ResultDateTime;

```

Solution 5: Using DATETIMEFROMPARTS (SQL Server 2012+)

```sql

SELECT DATETIMEFROMPARTS(

SUBSTRING('20251121 163356', 1, 4), -- Year

SUBSTRING('20251121 163356', 5, 2), -- Month

SUBSTRING('20251121 163356', 7, 2), -- Day

SUBSTRING('20251121 163356', 10, 2), -- Hour

SUBSTRING('20251121 163356', 12, 2), -- Minute

SUBSTRING('20251121 163356', 14, 2), -- Second

0 -- Millisecond

) AS ResultDateTime;

```

Solution 6: Using FORMAT (SQL Server 2012+)

```sql

SELECT CONVERT(DATETIME,

FORMAT(CONVERT(DATETIME, '20251121 163356', 112), 'yyyy-MM-dd HH:mm:ss')

) AS ResultDateTime;

```

Complete Example with Error Handling

```sql

-- Safe conversion with error handling

BEGIN TRY

SELECT CONVERT(DATETIME, '20251121 163356', 112) AS ResultDateTime;

END TRY

BEGIN CATCH

SELECT NULL AS ResultDateTime, ERROR_MESSAGE() AS ErrorMessage;

END CATCH

```

For DATETIME2 Data Type

```sql

SELECT CONVERT(DATETIME2, '20251121 163356', 112) AS ResultDateTime2;

```

Using in a Table Context

```sql

-- Example with table data

CREATE TABLE #TempData (DateString VARCHAR(20));

INSERT INTO #TempData VALUES ('20251121 163356'), ('20241201 120000');

SELECT

DateString,

CONVERT(DATETIME, DateString, 112) AS ConvertedDateTime

FROM #TempData;

```

Recommended Approach

Solution 1 using CONVERT with style 112 is the most efficient and reliable:

```sql

SELECT CONVERT(DATETIME, '20251121 163356', 112) AS ResultDateTime;

```

Result: 2025-11-21 16:33:56.000

The style 112 corresponds to the yyyymmdd format, which matches your date string perfectly.

Style Code Reference

· 112: yyyymmdd format (ISO standard)

· 120: yyyy-mm-dd hh:mi:ss format

· 121: yyyy-mm-dd hh:mi:ss.mmm format

The CONVERT function with style 112 is the best choice because:

· It's the most performant

· Handles the exact format of your string

· Works across all SQL Server versions

· Provides proper error handling

Ah, that explains it — SQL Server is picky about formats. The string '20251121 142350' isn’t directly recognized by CONVERT with style 120, which is why you’re seeing the out of range error. You need to reshape the string into a format SQL Server can parse.

Here’s a safe way to do it:

`sql

DECLARE @input VARCHAR(20) = '20251121 142350';

-- Reformat the string into ISO style: YYYY-MM-DD HH:MM:SS

SELECT CONVERT(DATETIME,

STUFF(STUFF(STUFF(@input, 9, 0, ' '), 12, 0, ':'), 15, 0, ':'), 120) AS ConvertedDateTime;

How it works:

- STUFF(@input, 9, 0, ' ') → inserts a space between date and time.

- STUFF(..., 12, 0, ':') → inserts a colon between hours and minutes.

- STUFF(..., 15, 0, ':') → inserts a colon between minutes and seconds.

- Result becomes '2025-11-21 14:23:50', which SQL Server can parse with style 120.

---

✅ If you want the final result as a string (not a DATETIME):

`sql

DECLARE @input VARCHAR(20) = '20251121 142350';

SELECT CONVERT(VARCHAR(19),

CONVERT(DATETIME, STUFF(STUFF(STUFF(@input, 9, 0, ' '), 12, 0, ':'), 15, 0, ':'), 120), 120)

AS FormattedDateTime;

This guarantees you get '2025-11-21 14:23:50' without errors.

---

Would you like me to also show you a function version (UDF) so you can reuse this conversion across multiple columns or queries?

sqlserver automatic partition

2025-11-24T09:15:33.688+02:00

Perfect — let’s sketch out a sliding window partitioning strategy for your 200M-row table. This approach will let you drop old data almost instantly instead of running massive deletes.

---

🛠 Step 1: Create a Partition Function

Partition by CreatedAt (assuming it’s a datetime column).

We’ll define boundaries for each day:

`sql

-- Partition function: defines date boundaries

CREATE PARTITION FUNCTION pf_CreatedAt (datetime)

AS RANGE RIGHT FOR VALUES (

CAST(GETDATE() AS date),

CAST(DATEADD(DAY, -1, GETDATE()) AS date),

CAST(DATEADD(DAY, -2, GETDATE()) AS date),

CAST(DATEADD(DAY, -3, GETDATE()) AS date)

);

This creates partitions for today, yesterday, 2 days ago, and older.

---

🛠 Step 2: Create a Partition Scheme

Map partitions to filegroups (you can use PRIMARY or separate filegroups for performance):

`sql

-- Partition scheme: maps partitions to filegroups

CREATE PARTITION SCHEME ps_CreatedAt

AS PARTITION pf_CreatedAt

TO ([PRIMARY], [PRIMARY], [PRIMARY], [PRIMARY], [PRIMARY]);

---

🛠 Step 3: Create the Table on the Partition Scheme

`sql

CREATE TABLE dbo.YourTable (

Id BIGINT IDENTITY(1,1) PRIMARY KEY,

CreatedAt DATETIME NOT NULL,

Data NVARCHAR(200) -- your payload

)

ON ps_CreatedAt(CreatedAt);

---

🛠 Step 4: Automate Sliding Window

Each day, you’ll:

1. Split the partition function to add a new boundary for today.

2. Merge the oldest boundary to drop data older than 3 days.

Example:

`sql

-- Add new boundary for today

ALTER PARTITION FUNCTION pf_CreatedAt()

SPLIT RANGE (CAST(GETDATE() AS date));

-- Drop oldest partition (older than 3 days)

ALTER PARTITION FUNCTION pf_CreatedAt()

MERGE RANGE (CAST(DATEADD(DAY, -3, GETDATE()) AS date));

This instantly removes old data without row-by-row deletes.

---

⚡ Why This Works

- Instant cleanup: Dropping a partition is metadata-only, no massive delete.

- Scalable: Handles billions of rows easily.

- Minimal logging: No giant transaction logs.

- Automatable: Wrap the SPLIT/MERGE in a SQL Server Agent Job.

---

👉 Would you like me to write a full SQL Server Agent Job script that runs daily and automatically does the SPLIT/MERGE for you, so you don’t have to manage it manually?

ScyllaDB Re-Schedule Job

2024-07-09T14:14:00.003+03:00

To schedule a repair job in ScyllaDB using the sctool repair command with the --cron option, you'll need to specify the desired timing for the repair job in a cron-like syntax.

Using `sctool repair --cron`

The --cron option allows you to specify when the repair job should run using a cron expression. Here’s how you can do it:

Open your terminal where you have access to the sctool command.
Run the sctool repair command with the --cron option and provide the appropriate cron expression.

Example

If you want to schedule the repair job to run this Sunday at 5 AM, you would use the following cron expression:

0 5 * * 0 means "at 5:00 AM every Sunday."

The complete command would look like this:

sctool repair --cron '0 5 * * 0'

Breaking Down the Cron Expression

0: The minute field, meaning the 0th minute of the hour.
5: The hour field, meaning 5 AM.
*: The day of the month field, meaning any day of the month.
*: The month field, meaning any month.
0: The day of the week field, meaning Sunday (0 represents Sunday in cron syntax).

Scheduling the Repair Job

Run the command with the desired cron expression:

sctool repair --cron '0 5 * * 0'

This command will schedule the repair job to run every Sunday at 5 AM.

Additional Information

Ensure you have the necessary permissions to run sctool and that your ScyllaDB setup supports the --cron option for scheduling repairs.

If you need more details about sctool options or further assistance with cron syntax, refer to the ScyllaDB documentation or use the --help option with sctool:


sctool repair --help

This command will provide you with detailed information on all available options and usage instructions for sctool repair.

Trouble Shutting Poor IO on DB Server

2024-06-18T12:34:00.002+03:00

From time to time, I get a replication delay even dup the CPU is not loaded, I can see that the slave as a lot of relay logs that he is unable to apply on time, what could be the reason?

Replication delay in MySQL can be caused by several factors even if the CPU is not heavily loaded. The presence of many relay logs that the slave is unable to apply in a timely manner suggests that the issue may lie elsewhere. Here are some potential reasons and solutions for replication delay:

Potential Causes of Replication Delay

I/O Bottlenecks:

The slave might be experiencing I/O bottlenecks, which can delay the application of relay logs. This can happen if the disk subsystem is slow or overloaded.
Solution: Monitor disk I/O performance using tools like iostat, vmstat, or MySQL's performance_schema. Consider upgrading the disk subsystem or tuning the I/O performance.

Inadequate Memory:

Insufficient memory can cause the slave to use disk-based temporary tables more frequently, slowing down the replication process.
Solution: Ensure the slave has adequate memory. Monitor memory usage and adjust MySQL's memory-related configuration parameters (innodb_buffer_pool_size, query_cache_size, etc.).

Large Transactions:

Large transactions on the master can cause delays on the slave because it has to apply the entire transaction before moving to the next one.
Solution: Break large transactions into smaller ones if possible. Monitor the size of transactions and the relay log processing.

Table Locks:

Frequent or long-lasting table locks on the slave can delay replication.
Solution: Optimize queries to reduce locking. Use InnoDB (which has row-level locking) instead of MyISAM (which has table-level locking).

Slow Queries:

Slow queries on the slave can cause delays. If the slave is executing queries that take a long time to complete, it will fall behind.
Solution: Optimize slow queries. Use the slow_query_log to identify and optimize slow queries.

Replication Configuration:

Suboptimal replication configuration can cause delays.
Solution: Ensure that replication settings like slave_parallel_workers (for multi-threaded replication) and slave_parallel_type are optimally configured.

Diagnosing Replication Delay

Check Slave Status:

Run SHOW SLAVE STATUS\G on the slave to get detailed information about the replication process. Key fields to look at:

Seconds_Behind_Master
Slave_IO_Running
Slave_SQL_Running
Exec_Master_Log_Pos
Relay_Log_Space

Monitor Relay Log Application:

Check the Relay_Log_Space and Relay_Log_Pos to understand how the relay logs are being processed.

Check Disk I/O:

Use iostat, vmstat, or similar tools to monitor disk I/O performance.

Analyze Slow Queries:

Enable the slow query log on the slave and analyze slow queries that might be causing delays.

Example Commands

Checking Slave Status

SHOW SLAVE STATUS\G;

Monitoring Disk I/O

iostat -x 5

Enabling Slow Query Log

Add the following lines to your MySQL configuration file (usually my.cnf or my.ini):

[mysqld]

slow_query_log = 1

slow_query_log_file = /var/log/mysql/slow.log

long_query_time = 2

After making these changes, restart MySQL:

sudo systemctl restart mysql

Using innodb_status

Check the InnoDB status for locks and other potential bottlenecks:

SHOW ENGINE INNODB STATUS\G;

Example of Multi-Threaded Replication Configuration

If you are using MySQL 5.7 or newer, you can enable and configure multi-threaded replication to help reduce replication lag.

Add or update the following settings in your MySQL configuration file:

[mysqld]

slave_parallel_workers = 4

slave_parallel_type = LOGICAL_CLOCK

Monitoring and Optimization Tools

Performance Schema:

Use MySQL's Performance Schema to monitor and diagnose performance issues.

sql

Copy code

SELECT * FROM performance_schema.events_statements_summary_by_digest ORDER BY COUNT_STAR DESC LIMIT 10;

pt-query-digest:

Use the pt-query-digest tool from Percona Toolkit to analyze query performance.

MySQL Enterprise Monitor:

If available, use MySQL Enterprise Monitor for comprehensive monitoring and alerting.

Conclusion

Replication delay can be caused by various factors other than CPU load. By carefully monitoring disk I/O, memory usage, slow queries, and optimizing your replication configuration, you can identify and address the root causes of the delay. Regular monitoring and performance tuning are essential to maintaining an efficient and reliable replication setup.

to use iostat and vmstat to monitor system performance, particularly focusing on disk I/O and general system statistics.

iostat Examples

The iostat command is used to monitor system input/output device loading. By observing the time the devices are active in relation to their average transfer rates, you can determine the system's I/O performance.

Basic Usage

iostat

This command provides a summary of CPU and I/O statistics since the system was booted.

Monitoring Disk I/O Every 5 Seconds

iostat -x 5

This command shows extended statistics for each device every 5 seconds. The -x option provides more detailed statistics, including:

%user: Percentage of CPU utilization that occurred while executing at the user level.
%nice: Percentage of CPU utilization that occurred while executing at the user level with nice priority.
%system: Percentage of CPU utilization that occurred while executing at the system (kernel) level.
%iowait: Percentage of time that the CPU or CPUs were idle during which the system had an outstanding disk I/O request.
%steal: Percentage of time spent in involuntary wait by the virtual CPU or CPUs while the hypervisor was servicing another virtual processor.
%idle: Percentage of time that the CPU or CPUs were idle and the system did not have an outstanding disk I/O request.
tps: Total number of transfers per second that were issued to the device.
kB_read/s: Number of kilobytes read from the device per second.
kB_wrtn/s: Number of kilobytes written to the device per second.
await: The average time (in milliseconds) for I/O requests issued to the device to be served.
svctm: The average service time (in milliseconds) for I/O requests that were issued to the device.

Monitoring Specific Device

iostat -dx sda 5

This command provides detailed statistics for the sda device every 5 seconds.

vmstat Examples

The vmstat command reports information about processes, memory, paging, block I/O, traps, and CPU activity.

Basic Usage

vmstat

This command provides a summary of the system’s virtual memory usage since the last reboot.

Monitoring System Performance Every 2 Seconds

vmstat 2

This command provides a continuous update of system statistics every 2 seconds.

Detailed Breakdown of vmstat Output

procs -----------memory---------- ---swap-- -----io---- -system-- ------cpu-----

r b swpd free buff cache si so bi bo in cs us sy id wa st

2 0 0 163840 32000 256000 0 0 1 2 1 2 1 1 97 0 0

procs:

r: Number of processes waiting for run time.
b: Number of processes in uninterruptible sleep.

memory:

swpd: Amount of virtual memory used.
free: Amount of idle memory.
buff: Amount of memory used as buffers.
cache: Amount of memory used as cache.

swap:

si: Amount of memory swapped in from disk (per second).
so: Amount of memory swapped out to disk (per second).

bi: Blocks received from a block device (per second).
bo: Blocks sent to a block device (per second).

system:

in: Number of interrupts per second, including the clock.
cs: Number of context switches per second.

cpu:

us: Time spent running non-kernel code (user time, including nice time).
sy: Time spent running kernel code (system time).
id: Time spent idle.
wa: Time spent waiting for I/O.
st: Time stolen from a virtual machine.

Monitoring with Timestamp

To include a timestamp with each line of output:

while true; do echo -n "$(date +%Y-%m-%d\ %H:%M:%S) "; vmstat 1 2 | tail -1; sleep 1; done

This script provides a timestamp along with the vmstat output every second.

Using iostat and vmstat Together

To get a comprehensive view of system performance, you can run both iostat and vmstat in parallel. For example, you can use watch to run these commands continuously:

watch -n 2 'iostat -x; vmstat 1 2'

This command will run iostat -x and vmstat 1 2 every 2 seconds, allowing you to see both I/O and memory/CPU statistics simultaneously.

By using iostat and vmstat effectively, you can monitor and diagnose performance issues related to disk I/O, memory usage, and CPU activity on your system.

Creating a Larger then 2TB Disk on Debian 12

2024-06-17T11:11:00.005+03:00

Creating a Larger then 2TB Disk on Debian 12

To format a newly created 4TB NVMe disk to a single XFS partition on a Debian server, follow these steps:

Identify the NVMe disk: Use lsblk to list the block devices and identify the NVMe disk. Assume the disk is /dev/nvme0n1.

lsblk

Create a single partition: Use fdisk or parted to create a single partition that spans the entire disk.

Using fdisk:

sudo fdisk /dev/nvme0n1

In the fdisk interactive prompt, follow these steps:

Type n to create a new partition.
Press Enter to accept the default partition number (1).
Press Enter to accept the default starting sector (usually 2048).
Press Enter to accept the default ending sector (which will create a partition that spans the entire disk).
Type w to write the changes and exit.

Format the partition to XFS: Use mkfs.xfs to format the partition.

sudo mkfs.xfs /dev/nvme0n1p1

Create a mount point: Create a directory where you want to mount the new partition.

sudo mkdir -p /mnt/data

Mount the partition: Mount the newly formatted XFS partition.

sudo mount /dev/nvme0n1p1 /mnt/data

Update /etc/fstab: To ensure the partition is mounted automatically at boot, add an entry to the /etc/fstab file.

First, get the UUID of the partition:

sudo blkid /dev/nvme0n1p1

Add an entry to /etc/fstab with the UUID:

sudo nano /etc/fstab

Add the following line (replace UUID=your-uuid with the actual UUID from the blkid command):

plaintext

Copy code

UUID=your-uuid /mnt/data xfs defaults,nofail 0 2

Example Commands

Identify the NVMe disk:

$ lsblk

NAME MAJ:MIN RM SIZE RO TYPE MOUNTPOINT

nvme0n1 259:0 0 3.7T 0 disk

Create a single partition using fdisk:

$ sudo fdisk /dev/nvme0n1

Follow the fdisk prompts as described above.

Format the partition to XFS:

$ sudo mkfs.xfs /dev/nvme0n1p1

Create a mount point:

$ sudo mkdir -p /mnt/data

Mount the partition:

$ sudo mount /dev/nvme0n1p1 /mnt/data

Update /etc/fstab:

Get the UUID:

$ sudo blkid /dev/nvme0n1p1

/dev/nvme0n1p1: UUID="your-uuid" TYPE="xfs"

Edit /etc/fstab:

$ sudo nano /etc/fstab

Add the entry:

UUID=your-uuid /mnt/data xfs defaults,nofail 0 2

These steps will create, format, and mount a single 4TB XFS partition on your NVMe disk.

MySQL 8.0 Messages MY-010562 & MY-010897 in "mysqld.log"

2024-06-17T11:04:00.002+03:00

MySQL 8.0 Messages MY-010562 & MY-010897 in "mysqld.log"

The log message [System] [MY-010562] [Repl] Slave I/O thread for channel indicates an issue related to replication in MySQL. This message is part of MySQL's replication framework, and it typically pertains to the Slave I/O thread, which is responsible for reading the binary log from the master server and writing it to the relay log on the slave server.

To better understand and resolve the issue, we need to look at the complete log entry and potentially additional context around it. The full log entry might look something like this:

[System] [MY-010562] [Repl] Slave I/O thread for channel '<channel_name>' connected to master '<master_host>:<port>', replication started in log '<binlog_file>' at position <position>.

Here are some common issues and troubleshooting steps related to the Slave I/O thread in MySQL replication:

1. Network Issues

If the Slave I/O thread cannot connect to the master, it might be due to network issues.

Check Network Connectivity:

Ensure that the slave server can reach the master server over the network.
Use tools like ping and telnet to verify connectivity to the master's IP and port (usually 3306).

2. Authentication Issues

Incorrect username or password for the replication user can cause the I/O thread to fail.

Verify Replication User Credentials:

Check that the replication user has the correct privileges on the master server.
Verify that the username and password configured on the slave match those on the master.

3. Binary Log File or Position Issues

If the slave is trying to read a binary log file or position that doesn't exist on the master, it will fail.

Check Binary Log File and Position:

Ensure that the binary log file and position specified in the replication configuration exist on the master server.
You can find the current binary log file and position on the master using:

SHOW MASTER STATUS;

4. Configuration Issues

Misconfiguration on either the master or slave can lead to replication problems.

Check Replication Configuration:

Verify the CHANGE MASTER TO settings on the slave.
Ensure the master's server_id is unique and different from the slave's server_id.

5. Log File Corruption or Errors

If there are errors or corruption in the binary log or relay log, the I/O thread can fail.

Check for Log File Corruption:

Look for errors in the master's error log and the slave's relay log.
If needed, you can skip over corrupted log entries, but this should be done with caution as it might lead to data inconsistency.

Steps to Troubleshoot

Examine the Full Log Message:

Look for any additional details in the mysqld.log around the [MY-010562] message.
Search for preceding or subsequent log entries that might provide more context or specific error messages.

Verify Replication Status:

Check the replication status on the slave using:

SHOW SLAVE STATUS\G;

Look for the fields Slave_IO_Running, Slave_SQL_Running, Last_IO_Error, and Last_SQL_Error for any issues.

Check Network and Authentication:

Ensure network connectivity between the master and slave.
Verify the replication user credentials and permissions.

Inspect Master and Relay Logs:

Check the master's binary log files using:

SHOW BINARY LOGS;

Inspect the slave's relay logs and the Relay_Log_File and Relay_Log_Pos fields in the SHOW SLAVE STATUS\G output.

Example Troubleshooting Steps

Here is an example of how you might go through the troubleshooting process:

Check the Slave Status:

SHOW SLAVE STATUS\G;

Look for Slave_IO_Running: No and check Last_IO_Error.

Check Network Connectivity:

ping master_host

telnet master_host 3306

Verify Replication User Credentials:

Ensure the user exists and has replication privileges on the master:

GRANT REPLICATION SLAVE ON *.* TO 'replication_user'@'slave_host' IDENTIFIED BY 'password';

FLUSH PRIVILEGES;

Check Binary Log Position:

On the master:

SHOW MASTER STATUS;

Compare with the slave’s Exec_Master_Log_Pos and Relay_Master_Log_File.

By following these steps, you should be able to identify and resolve the issue with the Slave I/O thread. If you provide the complete log entry and additional context, I can offer more specific guidance.

This is the full message: "[System] [MY-010562] [Repl] Slave I/O thread for channel '': connected to master 'repl1@10.117.10.33:3306',replication started in log 'db-prod-ams-mcmts-binlog.001109' at position 231714179"

The log message you provided indicates that the Slave I/O thread has successfully connected to the master and started replication. Here’s the breakdown of the message:

[System] [MY-010562] [Repl] Slave I/O thread for channel '': connected to master 'repl1@10.117.10.33:3306', replication started in log 'db-prod-ams-mcmts-binlog.001109' at position 231714179

This message generally suggests that the replication connection is established and the I/O thread is working as expected. Here’s what each part means:

[System] [MY-010562] [Repl]: These are identifiers for the log entry, indicating that it is a system message related to replication.
Slave I/O thread for channel '': This specifies the channel name. In this case, it’s empty, meaning the default replication channel.
connected to master 'repl1@10.117.10.33:3306': The slave I/O thread has connected to the master server with the user repl1 at the IP 10.117.10.33 on port 3306.
replication started in log 'db-prod-ams-mcmts-binlog.001109' at position 231714179: Replication is reading from the binary log file db-prod-ams-mcmts-binlog.001109 starting at position 231714179.

Interpretation

The message indicates that the replication process is starting correctly. If this is the only message you are seeing and there are no errors, it implies that the replication setup is functioning as expected.

Possible Issues and Next Steps

Even though the message indicates that the I/O thread has started correctly, you may want to check for other potential issues or confirm that the replication process is running smoothly. Here are some steps you can take:

Check the Slave Status:

Run the following command on the slave to get detailed status information about the replication process:

SHOW SLAVE STATUS\G;

Look for the following fields in the output:

Slave_IO_Running: Should be Yes.
Slave_SQL_Running: Should be Yes.
Last_IO_Error: Should be empty or None.
Last_SQL_Error: Should be empty or None.

Monitor Replication Progress:

Ensure that the Seconds_Behind_Master field is not increasing, which would indicate that the slave is falling behind the master.
Check that Exec_Master_Log_Pos and Read_Master_Log_Pos are advancing, which means that the slave is processing the master’s binary logs.

Check for Errors in the Log:

Review the MySQL error log for any errors or warnings that might indicate replication issues.

Verify Data Consistency:

Ensure that data on the slave is consistent with the master. You can use tools like pt-table-checksum and pt-table-sync from Percona Toolkit for this purpose.

Troubleshooting Common Replication Issues

If you encounter any issues or the replication status shows errors, here are some common troubleshooting steps:

Network Issues:

Ensure there is no network latency or connectivity issues between the master and slave.
Verify that the master server’s firewall allows connections from the slave’s IP address.

User Privileges:

Ensure that the replication user (repl1 in this case) has the necessary privileges:

GRANT REPLICATION SLAVE ON *.* TO 'repl1'@'slave_ip' IDENTIFIED BY 'password';

FLUSH PRIVILEGES;

Binary Log Issues:

Ensure that the binary log files on the master are not corrupted and that the master is not purging the binary logs before the slave can read them.

Configuration Issues:

Verify that the server_id is unique for both master and slave.
Check the CHANGE MASTER TO configuration on the slave to ensure it points to the correct master log file and position.

Example of Checking Slave Status

Here’s an example of how to check the slave status and interpret the output:

SHOW SLAVE STATUS\G;

Sample output:

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

Slave_IO_State: Waiting for master to send event

Master_Host: 10.117.10.33

Master_User: repl1

Master_Port: 3306

Connect_Retry: 60

Master_Log_File: db-prod-ams-mcmts-binlog.001109

Read_Master_Log_Pos: 231714179

Relay_Log_File: relay-bin.000002

Relay_Log_Pos: 231714179

Relay_Master_Log_File: db-prod-ams-mcmts-binlog.001109

Slave_IO_Running: Yes

Slave_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_Master_Log_Pos: 231714179

Relay_Log_Space: 231714179

Until_Condition: None

Until_Log_File:

Until_Log_Pos: 0

Seconds_Behind_Master: 0

Conclusion

The log message you provided indicates that the replication I/O thread has started successfully and connected to the master. By checking the slave status and monitoring the replication process, you can ensure that replication is functioning correctly and address any issues that arise. If there are specific problems you encounter, providing additional details will help in diagnosing and resolving the issue.

Other Issues in The Log File

the message before is: "[Warning] [MY-010897] [Repl] Storing MySQL user name or password information in the master info repository is not secure and is therefore not recommended. Please consider using the USER and PASSWORD connection options for START SLAVE; see the 'START SLAVE Syntax' in the MySQL Manual for more information."

The warning message [Warning] [MY-010897] [Repl] Storing MySQL user name or password information in the master info repository is not secure and is therefore not recommended. Please consider using the USER and PASSWORD connection options for START SLAVE; see the 'START SLAVE Syntax' in the MySQL Manual for more information. indicates a security concern about storing replication credentials in the master info repository in plaintext. This is a common security advisory in MySQL replication setups to encourage better practices.

Understanding the Warning

MySQL traditionally stores replication user credentials in the master.info file. This file contains sensitive information in plaintext, which poses a security risk. The recommendation is to use more secure methods to store and handle replication credentials.

Secure Handling of Replication Credentials

MySQL 8.0 introduced the ability to use the MASTER_USER and MASTER_PASSWORD options in conjunction with the START SLAVE command to securely manage replication credentials. This approach avoids storing credentials in plaintext files.

Steps to Address the Warning

Stop the Slave:

STOP SLAVE;

Update the Slave Configuration: Use the CHANGE MASTER TO command with the MASTER_USER and MASTER_PASSWORD options. These options will securely store the credentials without exposing them in the master.info file.

CHANGE MASTER TO

MASTER_HOST='10.117.10.33',

MASTER_USER='repl1',

MASTER_PASSWORD='your_password',

MASTER_LOG_FILE='db-prod-ams-mcmts-binlog.001109',

MASTER_LOG_POS=231714179;

Replace 'your_password' with the actual password for the replication user.

Start the Slave:

START SLAVE;

Verify Slave Status:

SHOW SLAVE STATUS\G;

Ensure that both Slave_IO_Running and Slave_SQL_Running are Yes and that there are no errors.

Example

Here's a complete example of how to update the replication configuration securely:

STOP SLAVE;

CHANGE MASTER TO

MASTER_HOST='10.117.10.33',

MASTER_USER='repl1',

MASTER_PASSWORD='your_password',

MASTER_LOG_FILE='db-prod-ams-mcmts-binlog.001109',

MASTER_LOG_POS=231714179;

START SLAVE;

SHOW SLAVE STATUS\G;

Explanation

STOP SLAVE: Stops the replication process.
CHANGE MASTER TO: Updates the replication configuration with secure credentials.
START SLAVE: Restarts the replication process with the updated configuration.
SHOW SLAVE STATUS\G: Verifies the status of the replication process.

By following these steps, you address the security warning about storing plaintext credentials and ensure that your replication setup is more secure. If you encounter any issues or need further assistance, please provide the specific details, and I can help you troubleshoot them.

MySQL BIT type operations in SQL's

2023-05-08T10:05:00.000+03:00

The SQL:

SELECT PA.v1,

SU.userv2_ID,

SU.v3, PA.v4

FROM aaa.bbbb PA

INNER JOIN aaa.cccc SU ON SU.v2 = PA.v2

WHERE PA.v5 = 'position'

AND PA.v6 = 'Yes'

AND PA.v7 = 0

AND SU.v8 = 3

AND SU.v9 & 128 = 128;

Do you know what is the meaning of the line "AND SU.v9 & 128 = 128"?

In MySQL, the & operator performs a bitwise AND operation. The expression "SU.v9 & 128" will return a value that represents the result of performing a bitwise AND operation between the value of "SU.v9" and the binary value 10000000 (which is decimal 128).

In this case, the line "AND SU.v9 & 128 = 128" is checking whether the binary value 10000000 (decimal 128) is set in the "SU.v9" field. If the binary value 10000000 is set, then the result of the expression will be 128, which is equal to the binary value 10000000. If the binary value 10000000 is not set, then the result of the expression will be 0.

So the line is effectively checking whether the 8th bit (from right to left) of the "SU.v9" field is set to 1. If it is set to 1, then the condition is true and the row will be included in the query's results. If it is set to 0, then the condition is false and the row will be excluded from the query's results.

SchemaSpy

2012-02-06T09:11:00.004+02:00

I uses "SchemaSpy" to get a order view of our sachem's and give the developers a tool to check their work. It is simple to use and install and powerful enough to spot problems and keep documentation of the database environment.

You can try it at:

https://github.com/schemaspy/schemaspy
http://schemaspy.sourceforge.net

At the end of installation U need to edit (or create new) batch file to run against your databases, the default file is located at the root and called "run.bat", for Oracle I uses:
java -jar "[path]\schemaSpy_5.0.0.jar" -dp "[path]\ojdbc6.jar" -t orathin -db [SID] -host [IP] -port 1521 -u [admin_user] -p [passwd] -o "[output_path]" -schemas [ora_user1,ora_user2,ora_user3,...]

Memcached and UDF for MySQL on Centos

2012-02-05T11:50:00.002+02:00

We decided to go for Memcached in order to increase our "Select" performance from MySQL.

We are working with Linux Centos:

Linux host01 2.6.18-274.17.1.el5xen #1 SMP Tue Jan 10 18:06:37 EST 2012 x86_64 x86_64 x86_64 GNU/Linux

The usual had to be installed in order to create compilation env. (gcc and several libs).

Then those had to be installed in this order:
1. libevent
2. Memcached
2. libmemcached
3. memcached_functions_mysql

For "libevent" I download and installed: "libevent-2.0.16-stable" from: http://libevent.org/.

For "Memcached" I installed: "memcached-1.4.11" from: http://memcached.org/.

Here the problems begin, when I went for the latest versions of "libmemcached" & "memcached_functions_mysql" I was unable to compile the "memcached_functions_mysql" (I coul'd compile any of the "libmemcached" versions).
First I installed "libmemcached-1.0.3" and when I tried the install "memcached_functions_mysql" the "configure" failed to find the "libmemcached", I looked around and the problem was that the default installation of "libmemcached" went to /usr/local/lib while the configure looked for it under: /usr/lib64 so I created a soft link on /usr/lib64 for the following:

/usr/lib64/libmemcachedprotocol.so -> /usr/local/lib/libmemcachedprotocol.so

/usr/lib64/libmemcachedprotocol.so.0 -> /usr/local/lib/libmemcachedprotocol.so.0

/usr/lib64/libmemcached.so -> /usr/local/lib/libmemcached.so

/usr/lib64/libmemcached.so.3 -> /usr/local/lib/libmemcached.so.3

/usr/lib64/libmemcachedutil.so -> /usr/local/lib/libmemcachedutil.so

/usr/lib64/libmemcachedutil.so.0 -> /usr/local/lib/libmemcachedutil.so.0

Now the configure was working, but the "make" command failed:

make[2]: Entering directory `/root/Install/mysql_memcach/memcached_functions_mysql-1.1/src'

/bin/sh ../libtool --preserve-dup-deps --tag=CC   --mode=compile gcc 
-DHAVE_CONFIG_H -I. -I../include -I/opt/mysql/mysql/include  -g 
-DUNIV_LINUX -DUNIV_LINUX    -g -O2 -MT util.lo -MD -MP -MF 
.deps/util.Tpo -c -o util.lo util.c

libtool: compile:  gcc -DHAVE_CONFIG_H -I. -I../include 
-I/opt/mysql/mysql/include -g -DUNIV_LINUX -DUNIV_LINUX -g -O2 -MT 
util.lo -MD -MP -MF .deps/util.Tpo -c util.c  -fPIC -DPIC -o 
.libs/util.o

util.c: In function 'string_create':

util.c:172: error: invalid application of 'sizeof' to incomplete type 'my_string_st'

util.c:173: error: dereferencing pointer to incomplete type

util.c:174: error: dereferencing pointer to incomplete type

util.c:175: error: dereferencing pointer to incomplete type

util.c:176: error: dereferencing pointer to incomplete type

util.c:177: error: dereferencing pointer to incomplete type

util.c:178: error: dereferencing pointer to incomplete type

util.c:178: error: dereferencing pointer to incomplete type

util.c: In function 'string_append':

util.c:185: error: dereferencing pointer to incomplete type

util.c:185: error: dereferencing pointer to incomplete type

util.c:188: error: dereferencing pointer to incomplete type

util.c:190: error: dereferencing pointer to incomplete type

util.c:197: error: dereferencing pointer to incomplete type

util.c:202: error: dereferencing pointer to incomplete type

util.c:202: error: dereferencing pointer to incomplete type

util.c:202: error: dereferencing pointer to incomplete type

util.c:204: error: dereferencing pointer to incomplete type

util.c:207: error: dereferencing pointer to incomplete type

util.c:208: error: dereferencing pointer to incomplete type

util.c:209: error: dereferencing pointer to incomplete type

util.c: In function 'free_string':

util.c:216: error: dereferencing pointer to incomplete type

util.c: In function 'string_reset':

util.c:222: error: dereferencing pointer to incomplete type

util.c:222: error: dereferencing pointer to incomplete type

util.c:223: error: dereferencing pointer to incomplete type

util.c:224: error: dereferencing pointer to incomplete type

util.c:224: error: dereferencing pointer to incomplete type

make[2]: *** [util.lo] Error 1

make[2]: Leaving directory `/root/Install/mysql_memcach/memcached_functions_mysql-1.1/src'

make[1]: *** [all] Error 2

make[1]: Leaving directory `/root/Install/mysql_memcach/memcached_functions_mysql-1.1/src'

make: *** [all-recursive] Error 1

So next I tried a different combination (after searching the internet):

libmemcached-0.34
memcached_functions_mysql-0.9

When running:
mysql -u root -p < ./sql/install_functions.sql

I run into the following error:

mysql> CREATE FUNCTION memc_servers_version RETURNS STRING SONAME "libmemcached_functions_mysql.so";

ERROR 1127 (HY000): Can't find symbol 'memc_servers_version' in library

And some things where working, but the main "set()' for values did not worked, so next I tried:
memcached_functions_mysql-1.1

And this time everything is working, I am able to set values in "memcached" and all other activities.

So currently the combination that is working on our environment is:

CentOs + libmemcavhed0.34 + memcached_functions_mysql-1.1

MySQL - command line monitoring

2011-11-27T14:44:00.001+02:00

Part 1

Basicly I perfer to use command line rather then a GUI to monitor and connect to MySQL in real time.

I use two types of monitors:

1. 'mytop'

The current version is maitain on: Mysqlfunboy Site - from the site:
"Mytop is a console-based (non-gui) tool for monitoring the threads and overall performance of a MySQL 3.22.x, 3.23.x, 4.x and 5.x server. It runs on most Unix systems (including Mac OS X) which have Perl, DBI, and Term::ReadKey installed. And with Term::ANSIColor installed you even get color. If you install Time: :HiRes, you'll get good real-time queries/second stats."

From the /? those are the options:

? - display this screen
# - toggle short/long numbers (not yet implemented)
! - force past replication error
c - command summary view (based on Com_* counters)
C - turn color on and off
d - show only a specific database
e - explain the query that a thread is running
E - display current replication error
f - show full query info for a given thread
F - unFilter the dispaly
h - show only a specifc host's connections
H - toggle the mytop header
i - toggle the display of idle (sleeping) threads
I - show innodb status
k - kill a thread
p - pause the display
l - change long running queries hightlighing
m - switch [mode] to qps (queries/sec) scrolling view
M - switch [mode] to status
o - reverse the sort order (toggle)
q - quit
r - reset the status counters (via FLUSH STATUS on your server)
R - change reverse IP lookup
s - change the delay between screen updates
S - change slow quiery hightlighting
t - switch to thread view (default)
u - show only a specific user
V - show variablesi
: - enter a command (not yet implemented)

As U can see it has a lot of option and it can be used to monitor remote hosts using the -h option.

This the default (thread) view, U can see the use of collors to make it more readable.

See U in Part 2.

mysqldump: Error 2020: Got packet bigger than 'max_allowed_packet' bytes when dumping table

2011-11-27T14:29:00.001+02:00

mysqldump: Error 2020: Got packet bigger than 'max_allowed_packet' bytes when dumping table
mysqldump: Couldn't execute 'use `XXX`': MySQL server has gone away (2006)
mysqldump: Got error: 2006: MySQL server has gone away when selecting the database

While creating a bzckup of MySQL using mysqldump I got the above 3 error's, the obius solution was to change the the 'max_allowed_packet' on my.cnf to a bigger value but on day to day basis that is not necasery, so I went to the other solution, add the '--max_allowed_packet=512M' to the mysqldump command line.

That remove all of the errors from the dump.

ORA-07217: sltln: environment variable cannot be evaluated.

2011-11-15T14:47:00.001+02:00

ORA-07217: sltln: environment variable cannot be evaluated.

What's nice about Oracle error codes is that you can understand one thing while they meant something else.

Like today we tried to start an instance that was created by someone else and strangely we got the above error.

Checking the env vars shows that they are all define correctly and still the instance will not start, so the next step was to check the SPFILE.

Well the server was a VM with 4GB of memory, the installation was done by TAR/UNTAR the oracle installation from other server and the memory parameters shows definition for more then 4GB. So the error was in the SPFILE definition and not the OS env.

By starting the instance with a PFILE:
sql> startup PFILE='/path/';

It fixed the problem (the memory target was define to 1.7GB). I immediatly created a SPFILE from that PFILE:

sql> create SPFILE FROM PFILE='/path/';

So that next time it will work.

MySQL Automatic Partition Management

2011-11-13T15:25:00.001+02:00

MySQL Automatic Partition Management

Framework

I created a new database called: `maintenance` and four tables, two control tables and two log tables:

`daily_partitioned_tables` - this table is controling partitions based on days between.

`maintpartitionsmetadata` - this table control partition by amount tables.

`maintpartitionsruntime` - log every time a partiion is added.

`maint_partitions_norun_log` - log every run.

daily_partitioned_tables

DROP TABLE IF EXISTS `daily_partitioned_tables`;

CREATE TABLE `daily_partitioned_tables` (

`DBName` varchar(150) NOT NULL DEFAULT '',

`TableName` varchar(250) NOT NULL DEFAULT '',

`DaysBetween` int(2) DEFAULT NULL,

`EngineType` varchar(100) DEFAULT NULL,

PRIMARY KEY (`DBName`,`TableName`)

) ENGINE=InnoDB DEFAULT CHARSET=utf8;

INSERT INTO `daily_partitioned_tables` VALUES ('

','',1,'InnoDB');

maintpartitionsmetadata

DROP TABLE IF EXISTS `maintpartitionsmetadata`;

CREATE TABLE `maintpartitionsmetadata` (

`ID` bigint(20) NOT NULL AUTO_INCREMENT,

`DBName` varchar(150) DEFAULT NULL,

`TableName` varchar(250) DEFAULT NULL,

`AllowedFreespacePercent` int(11) DEFAULT NULL,

`RowsPerPartition` bigint(20) DEFAULT NULL,

`EngineType` varchar(100) DEFAULT NULL,

`PartitionType` varchar(100) DEFAULT NULL,

`PartitionedColumn` varchar(100) DEFAULT NULL,

PRIMARY KEY (`ID`),

KEY `idx_mpmd_DBName_TableName` (`DBName`,`TableName`)

) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8;

INSERT INTO `maintpartitionsmetadata` VALUES (1,'','',10,120000000,'InnoDB','RANGE','ID');

maintpartitionsruntime

DROP TABLE IF EXISTS `maintpartitionsruntime`;

CREATE TABLE `maintpartitionsruntime` (

`ID` bigint(20) NOT NULL AUTO_INCREMENT,

`TimeStamp` datetime DEFAULT NULL,

`DBName` varchar(150) DEFAULT NULL,

`TableName` varchar(250) DEFAULT NULL,

`PartitionID` bigint(20) DEFAULT NULL,

`PartitionName` varchar(250) DEFAULT NULL,

`HighValue` bigint(20) DEFAULT NULL,

PRIMARY KEY (`ID`),

KEY `idx_mprt_DBName_TableName` (`DBName`,`TableName`)

) ENGINE=InnoDB DEFAULT CHARSET=utf8;

maint_partitions_norun_log

DROP TABLE IF EXISTS `maint_partitions_norun_log`;

CREATE TABLE `maint_partitions_norun_log` (

`ID` bigint(20) NOT NULL AUTO_INCREMENT,

`TimeStamp` datetime DEFAULT NULL,

`DBName` varchar(150) DEFAULT NULL,

`TableName` varchar(250) DEFAULT NULL,

`PartitionID` bigint(20) DEFAULT NULL,

`PartitionName` varchar(250) DEFAULT NULL,

`HighValue` bigint(20) DEFAULT NULL,

PRIMARY KEY (`ID`)

) ENGINE=InnoDB DEFAULT CHARSET=utf8;

Controling Procedures

(2012-10-23 - added error handling to procedures)
There are two procedures that creates the new partition, one is for the date partition - AddDailyPartition, the other is for the amount partition - AddPartition.

AddDailyPartition

DROP PROCEDURE IF EXISTS `AddDailyPartition` ;
DELIMITER ;;
CREATE PROCEDURE `AddDailyPartition`()
BEGIN
DECLARE vDBName                  VARCHAR(150) ;
DECLARE vTableName               VARCHAR(250) ;
DECLARE vDaysBetween             INT(2) ;
DECLARE vEngineType              VARCHAR(100) ;
DECLARE vPartitionID             BIGINT ;
DECLARE vPartitionedColumn       VARCHAR(100) ;
DECLARE vPartitionDate           TIMESTAMP ;
DECLARE vHighValue               BIGINT ;
DECLARE vPartitionName           VARCHAR(250) ;
DECLARE vCurrentMaxValue         BIGINT ;
DECLARE hNoMoreRows              INT DEFAULT FALSE ;
DECLARE l_error                  INT(2) DEFAULT 0;
DECLARE l_loop_inx               INT DEFAULT 1;
DECLARE curDailyPartitionedTables CURSOR FOR
      SELECT CONVERT(DBName USING utf8),
             CONVERT(TableName USING utf8),
             CONVERT(DaysBetween USING utf8),
             CONVERT(EngineType USING utf8)
      FROM   maintenancedb.daily_partitioned_tables ;
DECLARE CONTINUE HANDLER FOR NOT FOUND        SET hNoMoreRows = TRUE ;
DECLARE CONTINUE HANDLER FOR SQLEXCEPTION     SET l_error = 1;
DECLARE EXIT HANDLER FOR 1517             SET l_error = 2;
DECLARE CONTINUE HANDLER FOR SQLSTATE '23000' SET l_error = 3;
-- Read more at http://www.devshed.com/c/a/MySQL/Error-Handling-Examples/1/#Md3L1K2Fj0EgzS6W.99

INSERT INTO maintenancedb.maint_partitions_norun_log (TimeStamp, DBName,          TableName,           PartitionID, PartitionName, HighValue)
                                                VALUES (NOW(),     'maintenancedb', 'AddDailyPartition', 0,           'Start',       l_error) ;

OPEN curDailyPartitionedTables ;
        Tables_Loop: LOOP
    FETCH curDailyPartitionedTables INTO vDBName, vTableName, vDaysBetween, vEngineType ;
    INSERT INTO maintenancedb.maint_partitions_norun_log (TimeStamp, DBName, TableName, PartitionID, PartitionName,         HighValue)
                                                  VALUES (NOW(),     vDBName, vTableName, l_loop_inx, 'Loop Cursor - Start', l_error) ;
    SET l_loop_inx = l_loop_inx + 1;
    IF NOT hNoMoreRows THEN
       SELECT PARTITION_ORDINAL_POSITION                                          AS "PartitionID",
              REPLACE(REPLACE(PARTITION_EXPRESSION, '`', ''), ' ', '')            AS "PartitionedColumn",
                          STR_TO_DATE(substr(partition_name,2,9),GET_FORMAT(DATE,'INTERNAL')) AS "PartitionDate",
              PARTITION_DESCRIPTION                                               AS "MaxValue"
       INTO   vPartitionID, vPartitionedColumn, vPartitionDate, vHighValue
       FROM   information_schema.PARTITIONS
       WHERE TABLE_SCHEMA = vDBName    collate utf8_unicode_ci
       AND    TABLE_NAME   = vTableName collate utf8_unicode_ci
       ORDER BY 1 DESC
       LIMIT     1 ;
       IF vHighValue <= (TO_DAYS(NOW()) + vDaysBetween + 1) THEN
          SET vHighValue = TO_DAYS(NOW()) + vDaysBetween + 1 ;
          SELECT CONVERT(CONCAT('p',DATE_FORMAT(FROM_DAYS(vHighValue) - INTERVAL 1 DAY,'%Y%m%d')) USING utf8)
                  INTO   vPartitionName;
          SET @Insert = CONCAT('ALTER TABLE ', vDBName, '.', vTableName, ' ADD PARTITION (PARTITION ', vPartitionName,' VALUES LESS THAN (', vHighValue , ') ENGINE = ', vEngineType, ')') ;
          PREPARE STMT2 FROM @Insert ;
          EXECUTE STMT2 ;
          INSERT INTO maintenancedb.maintpartitionsruntime (TimeStamp, DBName, TableName, PartitionID,    PartitionName, HighValue)
                                                    VALUES (NOW(),     vDBName, vTableName, vPartitionID+1, vPartitionName, vHighValue) ;
       ELSE
              INSERT INTO maintenancedb.maint_partitions_norun_log (TimeStamp, DBName, TableName, PartitionID, PartitionName,     HighValue)
                                                        VALUES (NOW(),     vDBName, vTableName, vPartitionID, 'NoNeedToAddPart', vHighValue) ;
       END IF ;
    ELSE
       CLOSE curDailyPartitionedTables ;
       LEAVE Tables_Loop ;
    END IF ;
END LOOP ;
END ;;
DELIMITER ;

AddPartition

DROP PROCEDURE IF EXISTS `AddPartition` ;
DELIMITER ;;
CREATE PROCEDURE `AddPartition`()
BEGIN
DECLARE vAllowedFreespacePercent INT ;
DECLARE vRowsPerPartition        BIGINT ;
DECLARE vPartitionedColumn       VARCHAR(100) ;
DECLARE vEngineType              VARCHAR(100) ;
DECLARE vPartitionID             BIGINT ;
DECLARE vPartitionName           VARCHAR(250) ;
DECLARE vHighValue               BIGINT ;
DECLARE vCurrentMaxValue         BIGINT ;
DECLARE vDBName                  VARCHAR(150) ;
DECLARE vTableName               VARCHAR(250) ;
DECLARE hNoMoreRows              INT DEFAULT FALSE ;
DECLARE l_error                  INT DEFAULT FALSE;
DECLARE l_loop_inx               INT DEFAULT 1;
DECLARE curPartitionedTables CURSOR FOR
      SELECT CONVERT(DBName USING utf8),
             CONVERT(TableName USING utf8),
             CONVERT(AllowedFreespacePercent USING utf8),
             CONVERT(RowsPerPartition USING utf8),
             CONVERT(EngineType USING utf8)
      FROM   maintenancedb.maintpartitionsmetadata ;
DECLARE CONTINUE HANDLER FOR NOT FOUND SET hNoMoreRows = TRUE ;
DECLARE CONTINUE HANDLER FOR SQLEXCEPTION SET l_error = TRUE;

INSERT INTO maintenancedb.maint_partitions_norun_log (TimeStamp, DBName,          TableName,      PartitionID, PartitionName, HighValue)
                                                VALUES (NOW(),     'maintenancedb', 'AddPartition', 0,           'Start',       l_error) ;

OPEN curPartitionedTables ;
        Tables_Loop: LOOP
    FETCH curPartitionedTables INTO vDBName, vTableName, vAllowedFreespacePercent, vRowsPerPartition, vEngineType ;

    INSERT INTO maintenancedb.maint_partitions_norun_log (TimeStamp, DBName,          TableName,      PartitionID, PartitionName,         HighValue)
                                                  VALUES (NOW(),     'maintenancedb', 'AddPartition', l_loop_inx, 'Loop Cursor - Start', l_error) ;
    SET l_loop_inx = l_loop_inx + 1;

    IF NOT hNoMoreRows THEN
      SELECT PARTITION_ORDINAL_POSITION                               AS "PartitionID",
             REPLACE(REPLACE(PARTITION_EXPRESSION, '`', ''), ' ', '') AS "PartitionedColumn",
             PARTITION_DESCRIPTION                                    AS "MaxValue"
      INTO   vPartitionID, vPartitionedColumn, vHighValue
      FROM   information_schema.PARTITIONS
      WHERE TABLE_SCHEMA = vDBName    collate utf8_unicode_ci
      AND    TABLE_NAME   = vTableName collate utf8_unicode_ci
      ORDER BY 1 DESC
      LIMIT 1 ;
      SET @Table    = vTableName ;
      SET @Database = vDBName ;
      SET @Query = CONCAT('SELECT MAX(', vPartitionedColumn, ') INTO @CurrentMaxValue FROM ', vDBName, '.', vTableName) ;
      PREPARE STMT1 FROM @Query ;
      EXECUTE STMT1 ;
      SET vCurrentMaxValue = @CurrentMaxValue ;
      IF (vHighValue-vCurrentMaxValue) <= vAllowedFreespacePercent*(vRowsPerPartition/100) THEN

        SELECT CONVERT(CONCAT('p',SUBSTR(REPLACE(REPLACE(REPLACE(NOW(),'-',''),':',''),' ','_'),1,13)) USING utf8)
                INTO   vPartitionName;
        SET vHighValue = vHighValue+vRowsPerPartition ;
        SET @Insert = CONCAT('ALTER TABLE ', vDBName, '.', vTableName, ' ADD PARTITION (PARTITION ', vPartitionName,' VALUES LESS THAN (', vHighValue, ') ENGINE = ', vEngineType, ')') ;
        PREPARE STMT2 FROM @Insert ;
        EXECUTE STMT2 ;
        SELECT (vHighValue-vCurrentMaxValue) AS "FreeRows",
                vAllowedFreespacePercent*(vRowsPerPartition/100) AS "FreeRowsAllowed",
                "Yes" AS "NeedsPartition?" ;
        INSERT INTO maintenancedb.maintpartitionsruntime (TimeStamp, DBName, TableName, PartitionID, PartitionName, HighValue)
        VALUES (NOW(), vDBName, vTableName, vPartitionID+1, vPartitionName, vHighValue) ;
      ELSE
        SELECT (vHighValue-vCurrentMaxValue) AS "FreeRows",
                vAllowedFreespacePercent*(vRowsPerPartition/100) AS "FreeRowsAllowed",
                "No" AS "NeedsPartition?" ;
            INSERT INTO maintenancedb.maint_partitions_norun_log (TimeStamp, DBName, TableName, PartitionID, PartitionName, HighValue)
                                                      VALUES (NOW(), vDBName, vTableName, vPartitionID, 'NoNeedToAddPart', vHighValue) ;
      END IF ;
    ELSE
      CLOSE curPartitionedTables ;
      LEAVE Tables_Loop ;
    END IF ;
END LOOP ;
END ;;
DELIMITER ;

Main Job Procedure

RunMaintenance_Jobs_prc

DROP PROCEDURE IF EXISTS `RunMaintenance_Jobs_prc` ;
DELIMITER ;;
CREATE PROCEDURE `RunMaintenance_Jobs_prc`(p_run_type    int(1))
BEGIN
   INSERT INTO maintenancedb.maint_partitions_norun_log (TimeStamp, DBName,          TableName,                 PartitionID, PartitionName, HighValue)
                                                 VALUES (NOW(),     'maintenancedb', 'RunMaintenance_Jobs_prc', 0,           'Start',       0) ;
   IF p_run_type = 0 THEN
      CALL maintenancedb.AddPartition();
        INSERT INTO maintenancedb.maint_partitions_norun_log (TimeStamp, DBName,          TableName,                 PartitionID, PartitionName,        HighValue)
                                                      VALUES (NOW(),     'maintenancedb', 'RunMaintenance_Jobs_prc', 0,           'After AddPartition', 0) ;
      CALL maintenancedb.AddDailyPartition();
        INSERT INTO maintenancedb.maint_partitions_norun_log (TimeStamp, DBName,          TableName,                 PartitionID, PartitionName,             HighValue)
                                                      VALUES (NOW(),     'maintenancedb', 'RunMaintenance_Jobs_prc', 0,           'After AddDailyPartition', 0) ;
   END IF;
   INSERT INTO maintenancedb.maint_partitions_norun_log (TimeStamp, DBName,          TableName,                 PartitionID, PartitionName, HighValue)
                                                 VALUES (NOW(),     'maintenancedb', 'RunMaintenance_Jobs_prc', 0,           'END',         0) ;
END ;;
DELIMITER ;

This is the simpliest frame work.

Create Event

That will do: "CALL maintenancedb.RunMaintenance_Jobs_prc(0)" every 10min.

CREATE EVENT maintenancedb_event ON SCHEDULE EVERY 10 MINUTE STARTS CURRENT_TIMESTAMP + INTERVAL 10 MINUTE DO CALL maintenancedb.RunMaintenance_Jobs_prc(0);

Thing To Do:

Add difrent date ranges: week, month, year.
Control the logos - if any and what level.

Thats all for now.

Install Zabbix MySQL plugin (imported from Cacti)

2011-09-20T10:28:00.000+03:00

While the MySQL plugin for Zabbix is limited the Cacti plugin is very detailed and maintenance by Percona.

Best Prices for Xiaomi MI5 celphone!!!

Thanks to PalominiDB the Cacti plugin is ported to Zabbix.

The installation process is very simple, the most complicated part is creating the XML for the template from the Zabbix plugin download , all U have to do is open the file and:

cd appaloosa-zabbix-templates
perl tools/gen_template.pl defs/mysql.pl mysql.xml

Which will create the mysql.xml file needed for the Zabbix Server to install the MySQL template.

Next, create the config file for the agent by editing (using sed command) the conf/mysql_agentd.conf file and creating mysql.conf file ( the /usr/local/zabbix/plugins is a path U will have to create on the agent server - see below) :

sed -e 's|$ZABBIX_AGENT_PATH|/usr/local/zabbix/plugins|' conf/mysql_agentd.conf > mysql.conf

Now U need to get the PHP file from the Cacti template, download the latest version. Open the file, the file U need is: ss_get_mysql_stats.php

Agent side server:

Make sure PHP5 is installed.
Make sure MySQL plugin for PHP is installed.
Make sure php-cli is installed (command line PHP).
U need an installation dir like: /usr/local/zabbix/
Create a sub-dir: /usr/local/zabbix/plugins/
Copy the ss_get_mysql_stats.php to this dir.
Create a sub-dir: /usr/local/zabbix/agent.d/
Copy the mysql.conf file to this dir.
Edit the /etc/zabbix/zabbix_agentd.conf file.
Add: Include=/usr/local/zabbix/agent.d/
Create a MySQL database user:

GRANT SELECT, SUPER, PROCESS ON *.* TO 'zabbix'@'localhost' identified BY 'some_password';

Edit the ss_get_mysql_stats.php file, change the:
$mysql_user = 'zabbix'; $mysql_pass = 'some_password';
Restart the agent.

import mysql.xml into Zabbix, and associate Template_MySQL with each database server.

Possible problem:
If your server use the Zabbix Linux template it include the check for "mysqld" process so U can't add the Tamplate_Mysql to this server, workaround: create a new server for this tamplate.

Remove (full UnInstall) MySQL server form UBUNTU

2011-04-04T15:29:00.000+03:00

I needed to fully remove MySQL from my UBUNTU server in order to re-install it from scratch. The installation included files on NFS (NetApp).

First I auto remove the installation that was done with "apt-get":
prompt > apt-get autoremove mysql-server-core-5.1

Then I checked for leftovers:
prompt > dpkg --get-selections | grep mysql

There was a list of packages in "deinstall" status, so I compliantly remove them by:
prompt > pkg -P mysql-server-5.1
prompt > dpkg -P mysql-common

After that I remove manually all the files created on the NFS, and re-install from clean machine.

Installing Oracle 11gR2 on CentOS 5

2009-11-05T13:28:00.000+02:00

Its about time and we decided to upgrade to Oracle 11gR2, so first thing first is the installation. I downloaded the two installation ZIP's and extract them to a staging area.

In order to meet the oracle requirements for the installation please refer to: Oracle® Database Client Quick Installation Guide.

The good news is that the installer is making all the checks has part of the installation (See stage 11) so U can go with the process and fix what need fixing while the installation is running.

1. Create an env. file so that if U will have to run the process more then once U will not have to set it again and again. Basically U want to set the ORACLE_BASE & ORACLE_SID and unset ORACLE_HOME & TNS_ADMIN:

export ORACLE_BASE=/path
export ORACLE_SID=xxx

unset ORACLE_HOME
unset TNS_ADMIN

2. Run the installer (runInstaller) from the staging area.

3. The first screen:

4. Next is the installation option, I choose to install only the software:

5. Next is the type of installation, in my case single:

6. Next screen is the language:

7a. Select the Database Edition that suites U:

7b.Use the "Select Option" button to chane the Oracle Options:

8.Installation location - Remmember that the ORALCE_BASE is the place where the installation will create the following directories:

admin
cfgtoollogs
checkpoints
diag
oraInventory

So choose it wisely:

9. OS groups:

10.The istaller is running checks:

11. Check results:

At this stage all the kernel errors and missing packages will appeare, U can then fix them (change kernel params, install needed pkg.) and use the "Check Agin" button.

12. In the end if there are still error that can be ignored use the "Ignore All" check box:

13. Summery:

14. U can save the Summery to a response file for latter use in a silent install:

15. Install stages:

16. Now run has "root" the script:

17. That's it installation ended successfully:

The installation is completed, the next step is to create the DB - I did it using the "dbca" in the next post.

The Story of Stuff with Annie Leonard

2009-06-04T22:00:00.000+03:00

The Story of Stuff with Annie Leonard

Buzznt - MySQL & Oracle

Sql Server Partition Menegment

Teradata Session Resource Monitoring

sqlserver partition split by identity

sqlserver convert string to date

sqlserver automatic partition

ScyllaDB Re-Schedule Job

Using sctool repair --cron

Example

Breaking Down the Cron Expression

Scheduling the Repair Job

Additional Information

Trouble Shutting Poor IO on DB Server

Creating a Larger then 2TB Disk on Debian 12

MySQL 8.0 Messages MY-010562 & MY-010897 in "mysqld.log"

MySQL BIT type operations in SQL's

The SQL:

SchemaSpy

Memcached and UDF for MySQL on Centos

MySQL - command line monitoring

mysqldump: Error 2020: Got packet bigger than 'max_allowed_packet' bytes when dumping table

ORA-07217: sltln: environment variable cannot be evaluated.

MySQL Automatic Partition Management

Install Zabbix MySQL plugin (imported from Cacti)

Remove (full UnInstall) MySQL server form UBUNTU

Installing Oracle 11gR2 on CentOS 5

The Story of Stuff with Annie Leonard

Using `sctool repair --cron`