İntelligence Technology

Postgresql TimeScaleDB ve mevcut tabloyu timeseri tabloya dönüştürme

2024-10-01T10:42:00.003+03:00

PostgreSQL'de Zaman Serisi Veritabanı Oluşturmak ve Mevcut tablolarınızı Zaman Seri tablolarda saklayarak performansı artırabilirsiniz. bunun için güçlü bir kullanım ve performans olarak ta ciddi kazanımlar sağlamaktadır.

Postgresql 16 da sürümünde henüz resmi pakteleri desteklememektedir, 15 sürümünde doğrudan kurabilirsiniz. ben hem 15 hemde 16 versiyon için kurulum paktlerini paylaşacağım.

postgresql 15 versiyon ullanıyorsanız. aşağıdaki komutlar ile timescale extentionu postgresql kurulu makinanıza kolay bi şekilde kurabilirsiniz. tabi bu kurulumlar debian ve türevleri için,

--postgresql 15 versioyon için

sudo apt update

sudo apt install postgresql-15-timescaledb-2-postgresql-15

--postgresql 16 versiyon için;

--timescale db nin ihtiyaç duyduğu araçları yükleyelim.

sudo apt update

sudo apt install build-essential cmake git libssl-dev clang llvm libclang-dev postgresql-server-dev-16

--TimeScaleDb kaynak kodu indiriyoruz githup tan.

cd /usr/local/src/

sudo git clone https://github.com/timescale/timescaledb.git

cd timescaledb

# Uyumlu sürüm için TimescaleDB'nin ana dalına çekelim

sudo git checkout latest

-- TimeScaleDB yi Derliyoruz

# Kuruluma hazırlık

sudo ./bootstrap

# Derleme

cd build && sudo make

# Kurulum

sudo make install

buraya kadar hem 15 hemde 16 versiyon postgresql sürümleri için kurulumu tamamladık. Şimdide extention ı db de oluşturacağız

sudo systemctl restart postgresql

-- TimescaleDB'nin yüklü olduğunu doğrulayın

\dx

-- Eklentiyi yüklemek için bir veritabanına bağlanın ve TimescaleDB'yi ekleyin

biz burada test_timescale_db adında bir db oluşturup işlemlerimizi orada yapacağız.

create database test_timescale_db

\c test_timescale_db

CREATE EXTENSION IF NOT EXISTS timescaledb;

-- Tablomuzun createini pgadmin üzerinden alarak yeni veritabanımızda oluşturalım.

>>> buradaki adımı pgadmin üzerinden yapıyorsunuz..

--TimescaleDB ile Hypertable Dönüşümü

Tabloyu TimescaleDB’nin performans özelliklerinden faydalanmak için hypertable’a dönüştürelim. Bu işlemi sendtime sütununu kullanarak yapacağız (zaman temelli veri olduğu için bu sütunu seçtik).

Burada önemli bir konu daha var, eğer time seri data da null varsa bunu yapmaya izin vermeyecektir. data da null olmaması gerekir.

-- Test veritabanına geri dönün

\c test_timescale_db;

-- Tabloyu hypertable'a dönüştürün

SELECT create_hypertable('public.projectadata', 'sendtime');

Timescale db de sıkıştırma özelliğini de kullanabilirsiniz.

-- Sıkıştırmayı tabloya uygulayın

ALTER TABLE public.projectadata SET (timescaledb.compress, timescaledb.compress_orderby = 'sendtime');

-- Sıkıştırmayı uygula

SELECT compress_chunk(i.chunk_name)

FROM show_chunks('public.projectdata') i;

tüm işlemler bu kadar,

şimdi burada aynı instance yani cluster da bulunan farklı db lerde bulunan bir tabloyu test_timescale_db ye atarak kullanıma başlayacağız.

bunun için dblinki kullanabiliriz, yada pg_admin , pg_dump gibi diğer toollar ile aktarımı yapabiliriz.

.-dblink Kullanarak Veri Kopyalayın

-- Mevcut veritabanında

CREATE EXTENSION dblink;

-- Test veritabanında

\c test_timescale_db

CREATE EXTENSION dblink;

daha sonra verileri atıyoruz..

tabi bu işlemden önce data da null olmadığını kontrol ediyoruz..

--varsa farklı kolon secebiliriz.

SELECT count(*)

FROM public.projectdata

WHERE sendtime IS NULL;

-- Mevcut veritabanına bağlanarak veri çekin ve yeni veritabanındaki tabloya ekleyin

INSERT INTO test_timescale_db.public.projectdata

SELECT * FROM dblink('dbname=test user=postgres', 'SELECT * FROM public.projectdata')

AS t(customername VARCHAR(25), platformname VARCHAR(25), systemname VARCHAR(25), categoryname VARCHAR(255), tagname VARCHAR(100), sensorname VARCHAR(25), tagvalue DOUBLE PRECISION, tagdate VARCHAR(10), tagtime TIME, des_unit VARCHAR(155), hour INTEGER, minute INTEGER, second INTEGER, tagdescription VARCHAR(100), tagminvalue DOUBLE PRECISION, tagmaxvalue DOUBLE PRECISION, tagunit VARCHAR(50), data_status VARCHAR(8), tagerrvalue INTEGER, tagdatetime VARCHAR(26), sendtime TIMESTAMP);

tablo üzerinde chunklar olup olmadığını görebiliriz

SELECT show_chunks('public.proectdata');

--eğer chunk'lar varsa bunları sıkıştırabiliriz.

SELECT compress_chunk(chunk)

FROM show_chunks('public.projectdata') AS chunk;

linux locale dil ayar değişimi sonrası Patroni Create cluster failed hatası

2024-03-12T02:01:00.000+03:00

Merhabalar,

Ubunutu/debian türevleri üzerine kurulu olan bir patroni cluster yapısınnda diğer node larda aynı dil ayarı yoksa patroni direkt failed verecektir..

Peki nasıl çözeriz.? Hem local hemde sistem dilini istediğimiz ayara cevirebilir miyiz.?

makinalara ssh ile 3 party toollar ile bağlandığında aslında değişiklikleri yapıldığını herhangi bri hata ekranı vermediği görülebilir. bu durumda aslında doğrudan sunucu üzerinden yada sisteme Vm üzerinden direkt bağlanmak gerekebilir.

öncelikle ~/.bashrc ve ~/.profile dosyası altında bulunan export ile başlayan dil ayarlarını kaldırın.

root@node:# vi /etc/default/locales altında bulunan dil ayarlarınızı kaldırın.

root@node:#dpkg-reconfigure locales ile gelen mor ekranda en_US.UTF-8 ve tr_TR.UTF-8 secili olduğuna emin olalım (bu ayar aslında ana makinada bulunan ayar ile aynı olmalıdır.)

daha sonra secimimiz local dil ayarı ile devam ediyor olacağız. aşağdaıki ekranda local dil ayarımızın hangisi olmasını istiyorsak onu secerek terminal ekranımızın dilinin de o olmasını sağlamış olacağız.

Burada önemli olan nokta bunu mobaXterm ile yada putty ile yapıldığında failed olması durumunda ekrana information bilgisi vermemsinden kaynaklanmaktadır. bu durumda direk olarak idirect, vm portal yada vcenter üzerinden doğrudan makina arayüzüne bağlanarak gerçekleştirmek doğru olacaktır.

Postgresql HA Yüksek kullanılabilir mimari- Patroni kurulumu

2023-10-06T12:24:00.000+03:00

Merhaba arkadaşlar burada postgresql database'in Streaming Replication ile gelen cluster mimarisini manage eden yöneten patroni uygulamasının debian- ubuntu kurulumunu açıklayacağım.

bu mimari da 3 node'lu bir clusterımız streaming replicaiton alt yapısını kullanarak patroni tarafından yönetilmesini sağlayacağız.

yapı şöyle olacak: 3 node'da da etcd,postgresql,patroni ve vip-manager kurarak clusterımız tamamlamış olacağız.

1- Sunucu kurulumlarında bazen default da image den otamatik kurulum yaptığı durumlar olabiliyor. bunların olmadığını teyit ediyoruz.

apt list --installed | grep postgres

apt list --installed | grep etcd

apt list --installed | grep vip_manager

apt list --installed | grep patroni

2- host dosyasına ekleme yapıyoruz.

>>vim /etc/hosts

192.168.10.21 psql01

192.168.10.22 psql02

192.168.10.23 psql03

3- Burada Debian- Ubuntu türevini kullandığımız için selinux ' ü kontrol etmeye gerek yok. firewall acık ise disable edip kapatmamız yeterli.

systemctl stop ufw

systemctl disable ufw

4- ETCD kurulumunu gerçekleştiriyoruz

mkdir /tmp/etcd

cd /tmp/etcd

wget https://github.com/etcd-io/etcd/releases/download/v3.5.9/etcd-v3.5.9-linux-amd64.tar.gz --no-check-certificate

tar -xzvf etcd-v3.5.9-linux-amd64.tar.gz

groupadd --system etcd

useradd --create-home --home-dir /var/lib/etcd --shell /bin/bash --system --gid etcd etcd

mkdir /var/log/etcd

chown etcd: /var/log/etcd

chmod 700 /var/log/etcd

mkdir /etc/etcd.d

chown etcd: /etc/etcd.d

chmod 700 /etc/etcd.d

mkdir /var/lib/etcd/data

chown -R etcd: /var/lib/etcd

chmod -R 700 /var/lib/etcd

install /tmp/etcd/etcd-v3.5.9-linux-amd64/etcd /usr/bin/etcd

install /tmp/etcd/etcd-v3.5.9-linux-amd64/etcdctl /usr/bin/etcdctl

5- etcd configurasyonlarını yapıyoruz. burada 3 node için ayrı ayrı gireceksini o kısım önemli

vim /etc/etcd.d/etcd.conf

data-dir: "/var/lib/etcd/data"

strict-reconfig-check: true

enable-v2: true

logger: zap

log-outputs:

- stdout

- stderr

initial-cluster: "psql01=http://192.168.10.21:2380,psql02=http://192.168.10.22:2380,psql03=http://192.168.10.23:2380"

initial-cluster-state: "new"

initial-cluster-token: "23D69B8D-B236-4D74-ACC3-26ED82891DE9"

initial-advertise-peer-urls: "http://192.168.10.21:2380"

listen-peer-urls: "http://192.168.10.21:2380"

listen-client-urls: "http://192.168.10.21:2379,http://127.0.0.1:2379"

advertise-client-urls: "http://192.168.10.21:2379"

listen-metrics-urls: "http://0.0.0.0:2381"

heartbeat-interval: 150

election-timeout: 1500

------------------------------------------node2

vim /etc/etcd.d/etcd.conf

data-dir: "/var/lib/etcd/data"

strict-reconfig-check: true

enable-v2: true

logger: zap

log-outputs:

- stdout

- stderr

initial-cluster: "psql01=http://192.168.10.21:2380,psql02=http://192.168.10.22:2380,psql03=http://192.168.10.23:2380"

initial-cluster-state: "new"

initial-cluster-token: "23D69B8D-B236-4D74-ACC3-26ED82891DE9"

initial-advertise-peer-urls: "http://192.168.10.22:2380"

listen-peer-urls: "http://192.168.10.22:2380"

listen-client-urls: "http://192.168.10.22:2379,http://127.0.0.1:2379"

advertise-client-urls: "http://192.168.10.22:2379"

listen-metrics-urls: "http://0.0.0.0:2381"

heartbeat-interval: 150

election-timeout: 1500

------------------------------------------node3

vim /etc/etcd.d/etcd.conf

data-dir: "/var/lib/etcd/data"

strict-reconfig-check: true

enable-v2: true

logger: zap

log-outputs:

- stdout

- stderr

initial-cluster: "psql01=http://192.168.10.21:2380,psql02=http://192.168.10.22:2380,psql03=http://192.168.10.23:2380"

initial-cluster-state: "new"

initial-cluster-token: "23D69B8D-B236-4D74-ACC3-26ED82891DE9"

initial-advertise-peer-urls: "http://192.168.10.23:2380"

listen-peer-urls: "http://192.168.10.23:2380"

listen-client-urls: "http://192.168.10.23:2379,http://127.0.0.1:2379"

advertise-client-urls: "http://192.168.10.23:2379"

listen-metrics-urls: "http://0.0.0.0:2381"

heartbeat-interval: 150

election-timeout: 1500

6- Etcd servisini oluşturuyoruz. 3 node da da ayınısı yapıyoruz

vim /etc/systemd/system/etcd.service

[Unit]

Description="etcd - A distributed, reliable key-value store for the most critical data of a distributed system"

Documentation=https://etcd.io/docs/latest

Requires=network-online.target

After=network-online.target

ConditionFileNotEmpty=/etc/etcd.d/etcd.conf

[Service]

Type=notify

User=etcd

Group=etcd

WorkingDirectory=/var/lib/etcd

Environment=ETCD_CONFIG_FILE=/etc/etcd.d/etcd.conf

Environment=GOMAXPROCS=1

StandardOutput=append:/var/log/etcd/etcd.log

StandardError=append:/var/log/etcd/etcd-error.log

ExecStart=/usr/bin/etcd

Restart=on-failure

RestartSec=10s

LimitNOFILE=65536

OOMScoreAdjust=-1000

[Install]

WantedBy=multi-user.target

7- etcd profile dosyasını oluşturuyoruz. 3 node da da aynı bilgiler olmalı

vim /etc/profile.d/etcd_profile.sh

export ETCDCTL_API=2

export ETCDCTL_ENDPOINT='http://127.0.0.1:2379,http://192.168.10.21:2379,http://192.168.10.22:2379,http://192.168.10.23:2379'

export ETCD_CONFIG_FILE=/etc/etcd.d/etcd.conf

8- şimdi etcdyi çalıştırarak kontrol ediyoruz

systemctl daemon-reload

systemctl start etcd

systemctl status etcd

systemctl enable etcd

etcdctl cluster-health

V3 versiyonlarda aşağıdaki komut ile kontrol edilebilir.

etcdctl --cluster=true endpoint health

9- makinamızı reboot ediyoruz. 3 node da da.

reboot

10-###################Postgresql kurulum#########

sudo sh -c 'echo "deb https://apt.postgresql.org/pub/repos/apt $(lsb_release -cs)-pgdg main" > /etc/apt/sources.list.d/pgdg.list'

wget --quiet -O - https://www.postgresql.org/media/keys/ACCC4CF8.asc | sudo apt-key add -

apt update

sudo apt-get update

apt list | grep postgres

sudo apt-get -y install postgresql-15 postgresql-contrib-15

sudo su - postgres

pg_lsclusters

pg_dropcluster 15 main --stop

pg_lsclusters

11- postgresql config ayarları için ilgili dizinler oluşturulur.

mkdir -p /pg_data/15/main

chown -R postgres: /pg_data

chmod 700 -R /pg_data

12- postgresql de locale dil tr-utf8 ekliyoruz. 3 node da bu ayarı yapmamız gerekiyor aksi halde sonradan bu ayarı yapmamız için restart gerekir. o açıdan şimdiden bu dil desteğini ekliyoruz

root@psql01: localectl list-locales

C.UTF-8

en_US.UTF-8

root@psql01: vi /etc/locale.gen

tr_TR.UTF-8 UTF-8

root@psql01: locale-gen

root@psql01: localectl list-locales

C.UTF-8

en_US.UTF-8

tr_TR.UTF-8

13-#####PATRONI kurulumu##########

mkdir -m 700 /var/log/patroni

chown postgres: /var/log/patroni

apt-get install libpq-dev

apt-get install python3-psycopg2

apt-get install python3-dev

apt-get install python3-pip

pip3 install -U pip

pip3 install patroni[etcd]

mkdir /etc/patroni

touch /etc/patroni/env.conf

chmod 600 /etc/patroni/env.conf

14- patroni sh dosyasını düzenliyoruz. [3 node]

vim /etc/profile.d/patroni.sh

export PATRONICTL_CONFIG_FILE=/etc/patroni/patroni.yml

15-Patroni servisini ekliyoruz. [3 node]

vim /lib/systemd/system/patroni.service

[Unit]

Description=Runners to orchestrate a high-availability PostgreSQL

After=syslog.target network.target

[Service]

Type=simple

User=postgres

Group=postgres

Environment="PATRONI_CONFIGURATION_FILE=/etc/patroni/patroni.yml"

EnvironmentFile=-/etc/patroni/env.conf

ExecStart=/usr/local/bin/patroni ${PATRONI_CONFIGURATION_FILE}

ExecReload=/bin/kill -s HUP $MAINPID

KillMode=process

TimeoutSec=30

Restart=no

[Install]

WantedBy=multi-user.target

16- Burada patroni yml dosyasını 3 node için giriyoruz alt alta yazıyorum dikkatli izleyiniz.

vim /etc/patroni/patroni.yml

namespace: "/postgres/"

scope: "main-postgres"

log:

level: INFO

dir: /var/log/patroni

bootstrap:

dcs:

ttl: 30

loop_wait: 10

retry_timeout: 10

maximum_lag_on_failover: 1048576

postgresql:

use_pg_rewind: true

use_slots: true

remove_data_directory_on_rewind_failure: true

remove_data_directory_on_diverged_timelines: true

parameters:

logging_collector: 'on'

log_filename: 'postgresql-%a.log'

log_truncate_on_rotation: 'on'

log_rotation_size: '0'

log_min_duration_statement: '1s'

log_lock_waits: on

log_checkpoints: 'on'

log_temp_files: '0'

log_autovacuum_min_duration: '0'

lc_messages: 'C'

wal_compression: "on"

wal_log_hints: "on"

fsync: "on"

max_connections: "500"

superuser_reserved_connections: "10"

archive_mode: "on"

archive_command: "/bin/true"

password_encryption : "scram-sha-256"

log_statement: ddl

shared_preload_libraries: pg_stat_statements,auto_explain

work_mem: '10MB'

effective_cache_size: '6144MB'

maintenance_work_mem: '512MB'

shared_buffers: '2048MB'

autovacuum_work_mem: '512MB'

autovacuum_vacuum_threshold: 3000

autovacuum_analyze_threshold: 1000

autovacuum_vacuum_scale_factor: 0.002

autovacuum_analyze_scale_factor: 0.001

checkpoint_completion_target: '0.8'

initdb:

- data-checksums

- encoding: UTF8

pg_hba:

- local all postgres peer

- host all all 127.0.0.1/32 scram-sha-256

- host replication standby 127.0.0.1/32 scram-sha-256

- host replication standby 192.168.10.21/24 scram-sha-256

- host replication standby 192.168.10.22/24 scram-sha-256

- host replication standby 192.168.10.23/24 scram-sha-256

- host all postgres 192.168.10.21/24 scram-sha-256

- host all postgres 192.168.10.22/24 scram-sha-256

- host all postgres 192.168.10.23/24 scram-sha-256

users:

standby:

password: 123

options:

- replication

- login

etcd:

hosts: 192.168.10.21:2379,192.168.10.22:2379,192.168.10.23:2379

postgresql:

authentication:

superuser:

username: postgres

password: 123

replication:

username: standby

password: 123

connect_address: "192.168.10.21:5432"

data_dir: /pg_data/15/main

config_dir: /pg_data/15/main

bin_dir: /usr/lib/postgresql/15/bin

listen: "0.0.0.0"

use_unix_socket: true

pgpass: /var/lib/postgresql/patroni.pgpass

pg_hba:

- local all postgres peer

- host all all 127.0.0.1/32 scram-sha-256

- host replication standby 127.0.0.1/32 scram-sha-256

- host replication standby 192.168.10.21/24 scram-sha-256

- host replication standby 192.168.10.22/24 scram-sha-256

- host replication standby 192.168.10.23/24 scram-sha-256

- host all postgres 192.168.10.21/24 scram-sha-256

- host all postgres 192.168.10.22/24 scram-sha-256

- host all postgres 192.168.10.23/24 scram-sha-256

restapi:

listen: 192.168.10.21:8009

connect_address: 192.168.10.21:8009

tags:

nofailover: false

noloadbalance: false

clonefrom: false

nosync: false

--------------------------node2

namespace: "/postgres/"

scope: "main-postgres"

log:

level: INFO

dir: /var/log/patroni

bootstrap:

dcs:

ttl: 30

loop_wait: 10

retry_timeout: 10

maximum_lag_on_failover: 1048576

postgresql:

use_pg_rewind: true

use_slots: true

remove_data_directory_on_rewind_failure: true

remove_data_directory_on_diverged_timelines: true

parameters:

logging_collector: 'on'

log_filename: 'postgresql-%a.log'

log_truncate_on_rotation: 'on'

log_rotation_size: '0'

log_min_duration_statement: '1s'

log_lock_waits: on

log_checkpoints: 'on'

log_temp_files: '0'

log_autovacuum_min_duration: '0'

lc_messages: 'C'

wal_compression: "on"

wal_log_hints: "on"

fsync: "on"

max_connections: "500"

superuser_reserved_connections: "10"

archive_mode: "on"

archive_command: "/bin/true"

password_encryption : "scram-sha-256"

log_statement: ddl

shared_preload_libraries: pg_stat_statements,auto_explain

work_mem: '10MB'

effective_cache_size: '4096MB'

maintenance_work_mem: '512MB'

shared_buffers: '1024MB'

autovacuum_work_mem: '512MB'

autovacuum_vacuum_threshold: 3000

autovacuum_analyze_threshold: 1000

autovacuum_vacuum_scale_factor: 0.002

autovacuum_analyze_scale_factor: 0.001

checkpoint_completion_target: '0.8'

initdb:

- data-checksums

- encoding: UTF8

pg_hba:

- local all postgres peer

- host all all 127.0.0.1/32 scram-sha-256

- host replication standby 127.0.0.1/32 scram-sha-256

- host replication standby 192.168.10.21/24 scram-sha-256

- host replication standby 192.168.10.22/24 scram-sha-256

- host replication standby 192.168.10.23/24 scram-sha-256

- host all postgres 192.168.10.21/24 scram-sha-256

- host all postgres 192.168.10.22/24 scram-sha-256

- host all postgres 192.168.10.23/24 scram-sha-256

users:

standby:

password: 123

options:

- replication

- login

etcd:

hosts: 192.168.10.21:2379,192.168.10.22:2379,192.168.10.23:2379

postgresql:

authentication:

superuser:

username: postgres

password: 123

replication:

username: standby

password: 123

connect_address: "192.168.10.22:5432"

data_dir: /pg_data/15/main

config_dir: /pg_data/15/main

bin_dir: /usr/lib/postgresql/15/bin

listen: "0.0.0.0"

use_unix_socket: true

pgpass: /var/lib/postgresql/patroni.pgpass

pg_hba:

- local all postgres peer

- host all all 127.0.0.1/32 scram-sha-256

- host replication standby 127.0.0.1/32 scram-sha-256

- host replication standby 192.168.10.21/24 scram-sha-256

- host replication standby 192.168.10.22/24 scram-sha-256

- host replication standby 192.168.10.23/24 scram-sha-256

- host all postgres 192.168.10.21/24 scram-sha-256

- host all postgres 192.168.10.22/24 scram-sha-256

- host all postgres 192.168.10.23/24 scram-sha-256

restapi:

listen: 192.168.10.22:8009

connect_address: 192.168.10.22:8009

tags:

nofailover: false

noloadbalance: false

clonefrom: false

nosync: false

--------------------------node3

namespace: "/postgres/"

scope: "main-postgres"

log:

level: INFO

dir: /var/log/patroni

bootstrap:

dcs:

ttl: 30

loop_wait: 10

retry_timeout: 10

maximum_lag_on_failover: 1048576

postgresql:

use_pg_rewind: true

use_slots: true

remove_data_directory_on_rewind_failure: true

remove_data_directory_on_diverged_timelines: true

parameters:

logging_collector: 'on'

log_filename: 'postgresql-%a.log'

log_truncate_on_rotation: 'on'

log_rotation_size: '0'

log_min_duration_statement: '1s'

log_lock_waits: on

log_checkpoints: 'on'

log_temp_files: '0'

log_autovacuum_min_duration: '0'

lc_messages: 'C'

wal_compression: "on"

wal_log_hints: "on"

fsync: "on"

max_connections: "500"

superuser_reserved_connections: "10"

archive_mode: "on"

archive_command: "/bin/true"

password_encryption : "scram-sha-256"

log_statement: ddl

shared_preload_libraries: pg_stat_statements,auto_explain

work_mem: '10MB'

effective_cache_size: '4096MB'

maintenance_work_mem: '512MB'

shared_buffers: '1024MB'

autovacuum_work_mem: '512MB'

autovacuum_vacuum_threshold: 3000

autovacuum_analyze_threshold: 1000

autovacuum_vacuum_scale_factor: 0.002

autovacuum_analyze_scale_factor: 0.001

checkpoint_completion_target: '0.8'

initdb:

- data-checksums

- encoding: UTF8

pg_hba:

- local all postgres peer

- host all all 127.0.0.1/32 scram-sha-256

- host replication standby 127.0.0.1/32 scram-sha-256

- host replication standby 192.168.10.21/24 scram-sha-256

- host replication standby 192.168.10.22/24 scram-sha-256

- host replication standby 192.168.10.23/24 scram-sha-256

- host all postgres 192.168.10.21/24 scram-sha-256

- host all postgres 192.168.10.22/24 scram-sha-256

- host all postgres 192.168.10.23/24 scram-sha-256

users:

standby:

password: 123

options:

- replication

- login

etcd:

hosts: 192.168.10.21:2379,192.168.10.22:2379,192.168.10.23:2379

postgresql:

authentication:

superuser:

username: postgres

password: 123

replication:

username: standby

password: 123

connect_address: "192.168.10.23:5432"

data_dir: /pg_data/15/main

config_dir: /pg_data/15/main

bin_dir: /usr/lib/postgresql/15/bin

listen: "0.0.0.0"

use_unix_socket: true

pgpass: /var/lib/postgresql/patroni.pgpass

pg_hba:

- local all postgres peer

- host all all 127.0.0.1/32 scram-sha-256

- host replication standby 127.0.0.1/32 scram-sha-256

- host replication standby 192.168.10.21/24 scram-sha-256

- host replication standby 192.168.10.22/24 scram-sha-256

- host replication standby 192.168.10.23/24 scram-sha-256

- host all postgres 192.168.10.21/24 scram-sha-256

- host all postgres 192.168.10.22/24 scram-sha-256

- host all postgres 192.168.10.23/24 scram-sha-256

restapi:

listen: 192.168.10.23:8009

connect_address: 192.168.10.23:8009

tags:

nofailover: false

noloadbalance: false

clonefrom: false

nosync: false

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

17- Patroni servisini sisteme sinyal yollayarak start ederek açacağız.

chown -R postgres: /etc/patroni

systemctl daemon-reload

rm -rf /pg_data/15/main/*

systemctl start patroni

patronictl list

patronictl -c /etc/patroni/patroni.yml list

systemctl enable patroni

tail /var/log/patroni/patroni.log

18- Burda vip manager olarak yeni versiyon kurulursa etcd ile uyumsuz olabilyor o açıdan eski versiyon vip managaer kurulumunu gerçekleştireceğiz

#######################VIP MANAGER kurulumu#########

cd /tmp/

wget https://github.com/cybertec-postgresql/vip-manager/releases/download/v1.0.2/vip-manager_1.0.2-1_amd64.deb

dpkg -i /tmp/vip-manager_2.1.0_Linux_x86_64.deb

systemctl daemon-reload

mv /etc/default/vip-manager.yml /etc/default/vip-manager.yml.backup

19- vip manager yml dosyasını düzenliyoruz. [3 node]

vim /etc/default/vip-manager.yml

interval: 1000

trigger-key: "/postgres/main-postgres/leader"

trigger-value: "psql01"

ip: 192.168.10.25 # the virtual ip address to manage

netmask: 24 # netmask for the virtual ip

interface: eth1 #interface to which the virtual ip will be added

hosting-type: basic # possible values: basic, or hetzner.

dcs-type: etcd # etcd or consul

dcs-endpoints: # a list that contains all DCS endpoints to which vip-manager could talk.

- http://127.0.0.1:2379

- https://192.168.10.21:2379

- https://192.168.10.22:2379

- https://192.168.10.23:2379

#etcd-user: "patroni"

#etcd-password: "Julian's secret password"

retry-num: 2

retry-after: 250 #in milliseconds

verbose: false

------------------------------------node2

interval: 1000

trigger-key: "/postgres/main-postgres/leader"

trigger-value: "psql02"

ip: 192.168.10.25 # the virtual ip address to manage

netmask: 24 # netmask for the virtual ip

interface: eth1 #interface to which the virtual ip will be added

hosting-type: basic # possible values: basic, or hetzner.

dcs-type: etcd # etcd or consul

dcs-endpoints: # a list that contains all DCS endpoints to which vip-manager could talk.

- http://127.0.0.1:2379

- https://192.168.10.21:2379

- https://192.168.10.22:2379

- https://192.168.10.23:2379

#etcd-user: "patroni"

#etcd-password: "Julian's secret password"

retry-num: 2

retry-after: 250 #in milliseconds

verbose: false

---------------------------------node3

interval: 1000

trigger-key: "/postgres/main-postgres/leader"

trigger-value: "psql02"

ip: 192.168.10.25 # the virtual ip address to manage

netmask: 24 # netmask for the virtual ip

interface: eth1 #interface to which the virtual ip will be added

hosting-type: basic # possible values: basic, or hetzner.

dcs-type: etcd # etcd or consul

dcs-endpoints: # a list that contains all DCS endpoints to which vip-manager could talk.

- http://127.0.0.1:2379

- https://192.168.10.21:2379

- https://192.168.10.22:2379

- https://192.168.10.23:2379

#etcd-user: "patroni"

#etcd-password: "Julian's secret password"

retry-num: 2

retry-after: 250 #in milliseconds

verbose: false

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

19- vip manager servisimizi açarak kontrol ediyoruz.

systemctl enable --now vip-manager

umarım faydalı olmuştur. sevgiler

no supported authentication methods available (server sent publickey gssapi-keyex gssapi-with-mic) on google Cloud , AWS, Azure

2020-11-09T13:59:00.002+03:00

no supported authentication methods available (server sent publickey gssapi-keyex gssapi-with-mic)

Hello,

You got an error like this on Google Cloud , AWS, Azure or something like that.

the problem is about your sshd_config file.

change the PasswordAuthentication yes from no

sudo vi /etc/ssh/sshd_config

PasswordAuthentication yes

:wq!

and restart sshd service

##sudo systemctl restart sshd

thats All.

How to move SQL Server database files (LDF and MDF) to another location (path)

2020-11-06T22:40:00.002+03:00

DatabaseName: CND (example)

NewPathNameData: C:\DATA1\

NewPathNameLog: C:\LOG\

checking to Database PathLocation:

SELECT name, physical_name AS CurrentLocation, state_desc

FROM sys.master_files

WHERE database_id = DB_ID(N'CND');

use master

ALTER DATABASE CND

MODIFY FILE ( NAME = CND,

FILENAME = 'C:\DATA1\CND.mdf');

ALTER DATABASE CND

MODIFY FILE ( NAME = CND_log,

FILENAME = 'C:\LOG\CND_Log.ldf');

ALTER DATABASE CND SET OFFLINE;

move data (*.mdf and *.ldf ) to new location (as phisical) "like copy and past to new location etc."

ALTER DATABASE CND SET ONLINE;

checking to New Database PathLocation:

SELECT name, physical_name AS NewLocation, state_desc AS OnlineStatus

FROM sys.master_files

WHERE database_id = DB_ID(N'CND')

How to reset/crack the password of the "dba" account - SQL Anywhere

2020-10-10T00:18:00.000+03:00

Trying to login as account "dba/sysadmin" and receiving the error -103 "Invalid user ID or password."
We have "lost/forget" the password of the "dba/sysadmin" account.
How can we reset the password of the "dba" account?

password_error

Cause

Prior to build 17.0.5739

The default password for the "dba"¹ account is "sql" has been changed.²
A different password can be defined to the account at creation time or later using the "ALTER USER ..." statement.
A password change, after the creation of the database, will be logged in the transaction action log, with the password encrypted.
There is no option to start a database and print a new dba password to console (as in ASE or SQL Server).
SQL Anywhere has been designed to run on systems where security from the operating system level cannot be guaranteed.
Passwords are stored encrypted from the moment they are written to the database and there is no option to decrypt them or reset it,
unless another administrator account or transaction log with logged password change is available (see below).

From build 17.0.5739 or above, a new feature has been introduced:
New feature is added to allow to reset the forgotten password.
1. New role (SYS_OFFLINE_RESET_PASSWORD_ROLE) is added. It can be granted by the role administrator to a user who is authorized to reset password.
2. New server command line switch is added to reset the password of the specified user by the user whom SYS_OFFLINE_RESET_PASSWORD_ROLE role has been granted.
-orp "{UserID|UID}=<userid>;{ NewPassword|NEWPWD}=<newpassword>;{ AuthUserID |AUTHUID}=<authuserid>;{ AuthPassword|AUTHPWD}=<authpassword>"
- <userid>: the user whose password is going to be reset.
- <newpassword>: new password of the <userid>
- <authuserid>: the user who has SYS_OFFLINE_RESET_PASSWORD_ROLE granted by the role administrator
- <authpassword>: password of <authuserid>
If -orp is used, the server would start a database and attempt to reset the password with given parameters, and immediately stop the database and shutdown. The console output reports the success message or an error message if failed.
Note:
- The password reset would not be allowed if the <userid> has no password.
- Must specify one database with the -orp option
- No database option is not allowed with -orp server option
- No other server option (except -o) is allowed with -orp server option
- minimum password length of <newpassword> is 6.

Notes:

Some applications may have chosen a different account to serve as their dba but you may still be
able to follow this KBA if you know what the name of that account is supposed to be.
There is no predefined default dba or password as of version 17.

Resolution

You do have three options to resolve this situation

use the -orp database server option added in version 17.0.5739 or above
reset the password using another DBA account
rebuild the database using the transaction log files

Use the -orp database server option added in version 17.0.5739 or above

Example:

User ‘DBA’ had password ‘sql123’ and the user ‘reset_user’ has password ‘sql456’. As a role administrator, user ‘DBA’ granted SYS_OFFLINE_RESET_PASSWORD_ROLE to user ‘reset_user’ by the following statement.

grant role sys_offline_reset_password_role to reset_user;

Suppose that user ‘DBA’ has forgotten the password. After shutting down the server, it can be reset to ‘newpassword’ by the following server command line.

dbeng17 -orp "UID=DBA;NEWPWD=newpassword;AUTHUID=reset_user;AUTHPWD=sql456" mydb.db

Reset the password using another DBA account

The account "dba" is the default super-user of a database. Additional super-user accounts can exist in a database. If such an additional super-user exists and the credentials are known, this account can be used to reset the password of the "dba" account. Please refer to KBA 2207121 for more information about super-user account creation.

In Sybase Central use the credentials of another DBA account to log in to the database (If you do not have Sybase Central please refer to KBA 2040854)
In the left hand Folders Double Click Users
Right Click the user credentials you want to reset > Click Properties

Change the password of the account, confirm it and click OK

Rebuild the database using the transaction log files

A rebuild can be done, if one the following conditions is meet.

All Transaction log files do exist, since the creation of the database.
A database file backup is available and all transaction log files, created after the backup, do exist.

The important aspect is that the database can only be rebuild, using one or more transaction log files, if these do contain all data definition language (DDL) and data manipulation language (DML) statements since the creation of the database.

Using all transaction log files, since the database was created

Create a new database, with the same parameters as the original database was built with.

For example: dbinit -dba DBA,sql -p 4k demo.db
You can find the original parameters of a database from a console/server log. In the console log file, on the top it should say something like the following, describing how much memory/cache size/ page size are you using

Translate the transaction log files into .sql script files, using the SQL Anywhere tool "dbtran".

For example: dbtran demo.log demo.sql

If the password of the "dba" account was changed after the creation of the database, the "ALTER USER" statement needs to be located in the created .sql files and commented out.
OR
If you cannot find the ALTER USER statement, go to the bottom of .sql file and add the following statement:
ALTER USER dba IDENTIFIED BY sql;
Please replace sql with the new password you want for your database.
Adding this statement will override the previous password change.
In the same order as the corresponding transaction files were created, apply each single .sql script file to the database, using e.g. Interactive SQL (dbisql)

For example: dbisql -c "DBF=demo;UID=DBA;PWD=sql" demo.sql

Using all transaction log files, since the backup of the database was created

Translate the transaction log files into .sql script files, using the SQL Anywhere tool "dbtran".

For example: dbtran demo.log demo.sql

If the password of the "dba" account was changed after the creation of the database, the "ALTER USER" statement needs to be located in the created .sql files and commented out.
OR
If you cannot find the ALTER USER statement, go to the bottom of .sql file and add the following statement:
ALTER USER dba IDENTIFIED BY sql;
Please replace sql with the new password you want for your database.
Adding this statement will override the previous password change.
Get the backup of the database
In the same order as the corresponding transaction files were created, apply each single .sql script file to the database, using e.g. Interactive SQL (dbisql)

For example: dbisql -c "DBF=demo;UID=DBA;PWD=sql" demo.sql

Caveats:

Rebuilding a database has in general some side effects you should be aware of.

This will break any MobiLink or SQL Remote synchronization that is ongoing.
This should only be done in a development environment.
For a database of an OEM Authenticated edition, further steps might be required to re-authenticate the database.
If there is an event that created and scheduled in the past, error "Start date/time for non-recurring event %1 is in the past" might return. You need to open the translated sql file, and comment out all the event that schedule in the past or change the schedule time to future time.

Notes: The tool "dbtran" prints the start & end offset of the translated transaction log file. This way you can verify that no transaction log is missing. The end offset of the first transaction log should be identical to the start offset of the second transaction, and so on.

Source:Sybaseinfocenter

attempt to fetch logical page (1:XXXXXX) in database XX failed. It belongs to allocation unit XXX not to XXX failed

2020-09-26T19:00:00.005+03:00

Merhabalar VeriTabanlarında index yada Non-Cluster index bozulmalarından dolayı aşağıdaki gibi bir hata alabilirsiniz? Bu index bakımlarının zamanında yapılmaması, yanlış index oluşturulması, tabloların datalarının bulunduğu disklerin corrupt olması yada sistem disklerinin crush olması gibi birçok sebebten kaynaklı olabilir. Peki bu hatayı aldık sonra herşey bitti mi? hayır. data kaybında önce veriyi kurtarmak için birçok yöntem vardır.

Örnek olarak aşağıdaki bir hatayı ele alalım.

attempt to fetch logical page (1:438927981) in database 14 failed. It belongs to allocation unit 72060409138315264 not to 72060409197035520

öncelikle tablomuzun yedeğini alalım. ve satır satır row row tüm sağlam veriyi ayrı bir tabloya alalım.

bunun için aşağıdaki scripti çalıştırabilirsiniz?

CODE:

set nocount on;
DECLARE @Counter INT = 1;
DECLARE @LastRecord INT = 10000000; --your table_count

WHILE @Counter < @LastRecord
BEGIN
BEGIN TRY
BEGIN
insert into your_table_new SELECT * FROM your_table WHERE your_column= @Counter --dont forget! create your_table_new before
END
END TRY
BEGIN CATCH
BEGIN
insert into error_code select @Counter,'error_number' --dont forget the create error_code table before.
END
END CATCH
SET @Counter += 1;
END;

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

tablomuzun yedeği alındı. şimdi tablo üzerinde hertürlü kurtarma senaryolarımızı uygulayabiliriz.

Öncelikle tablodaki cluster mı noncluster indexlerde mi bozluma var tespit etmeye çalışıyoruz. Eğer noncluster ise işimiz kolay tablomuzu kurtarabiliriz. Ancak cluster ise fiziki olarak diskimizde arıza var demek anlamına gelir ve onarma yöntemlerinin diğer adımlarına geçerek onarma yoluna gideceğiz.

DBCC TRACEON(3604)

DBCC PAGE('table_name',1, 438927981,3) WITH TABLERESULTS

DBCC TRACEOFF(3604)

Metadata: IndexId

0 (heap) yada 1 (clustered index) ise backupdan dönmeniz gerekir.

0 yada 1 den farklı bir değerse ise noncluster indexi rebuilt etmeniz yeterlidir.

bunların hiç biri işe yaramadı diyelim. tabloyu onarmayı deneyeceğiz.

DBCC CHECKTABLE(TabloAdi, REPAIR_REBUILD )

bu da çzümolmazsa. Son bir seçenek Veri Kaybını da göze alarak onarma yoluna gideceğiz.

DBCC CHECKTABLE(TabloAdi, REPAIR_ALLOW_DATA_LOSS )

şu sitede index datalarına göz atmak için bakabilirsiniz.

KAYNAK: https://www.mssqltips.com/sqlservertip/1578/using-dbcc-page-to-examine-sql-server-table-and-index-data/

ASSERT: [025]: working path 'X' is not the same path as 'X'

2020-08-27T14:34:00.005+03:00

Merhabalar Eğer loglarınızda aşağıdaki hatayı alıyorsanız muhtemel pgbackrest config dosyanız da uyuşmayan bir yer var demektir.

ASSERT: [025]: working path 'X' is not the same path as 'X'

primary de pgbackrest için tanımlı pgbackrest.conf dosyasında ayarlama yapıcağız. master instance da aşağıdaki path lerin doğru olduğuna emin olunuz.

checking the pgbackrest.conf of your primary instance

[root_primary_instance]# cat /etc/pgbackrest.conf

[global]
repo1-path=/pgdata/backrest
repo1-retention-full=2
repo1-host=10.150.153.169
repo1-host-user=postgres

archive-async=y
log-level-file=detail

[global:archive-get]
process-max=8

[global:archive-push]
process-max=8

[pgtest1]
#pg1-path=/var/lib/pgsql/12/data
pg1-path=/pgdata/data
pg1-port=543

servisimizi restart edip hatanın kaybolduğunu göreceksiniz..

[root_primary_instance]# systemctl restart postgres-12

The transaction has already committed locally, but might not have been replicated to the standby

2020-08-27T13:55:00.004+03:00

Merhabalar Postgres de eğer aşağıdaki hatayı alıyorsanız; muhtemelen bu sorun sizin Replication name'iniz yanlış olduğu anlamına geliyor.

DETAIL: The transaction has already committed locally, but might not have been replicated to the standby

Öncelikle check edelim.

primary DBde;

>postgres=# show synchronous_commit;

synchronous_commit
--------------------
on
(1 row)

senkronizasyonumuz açık görünüyor.. değilse off durumunda ise postgres.conf dosyasından off kısmı on yapalım.

postgres=#
postgres=# select * from pg_stat_replication ;

pid usesysid usename application_name client_addr client_hostname client_port backend_start backend_xmin state sent_lsn write_lsn flush_lsn replay_lsn write_lag flush_lag replay_lag sync_priority sync_state reply_time

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

24330 16388 replikaci walreceiver 10.150.153.167 (null) 42554 2020-08-27 11:43:52.374787+03 (null) streaming 0/19010C88 0/19010C88 0/19010C88 0/19010C88 (null) (null) (null) 1 sync 2020-08-27 13:43:42.137884+03

postgres=# select application_name, sync_state from pg_stat_replication;
application_name | sync_state
------------------+------------
walreceiver | async
(1 row)

postgres=# show synchronous_standby_names;
synchronous_standby_names
---------------------------
pgdbtest1

replication application name walreceiver ise synchronous_standby_names inde aynı isim olması gerekir. pgdbtest1 ismini değiştireceğiz.

Standby da

postgres=# show synchronous_commit;
synchronous_commit
--------------------
local

olmasını bekleriz.

değişiklik için

[root@pg_primary_instance]# vi postgresql.conf

#synchronous_standby_names = 'pgdbtest1'

synchronous_standby_names = 'walreceiver'

kayıt edip çıkıyoruz.

[root@pg_primary_instance]# systemctl restart postgresql-12

şimdi işlemlerinize devam edebilirsiniz.. /pgdata/log/ yada İnstance ınızı nereye kurduysanız oradan da log ları kontrol edebilirsiniz?

SanDisk SSD PLUS 120 GB hız sorunu ve çözümü

2019-08-17T19:22:00.000+03:00

Merhabalar,

elinizde SanDisk SSD PLUS 120 GB ssd diskiniz var ama yazma hızı çok mu düşük.
Sorunu Diskin güncellemelerini yaparak düzeltebilirsiniz.. Bendeki SSD disk üzerine Windows kurulu ve bu işlemleri kurulu windows üzerinde yaptım.Ben Veri kaybı yaşamadım ama siz yine de önemli dosyalarını isterseniz yedekleyebilirsiniz. İşlemleri Fotoğraflarla anlatıyorum.

1. Öncelikli olarak SanDisk SSD güncellemelerini yapabilmeniz için programımızı SanDiskin kendi sitesinden indiriyoruz.
https://kb-tr.sandisk.com/app/answers/detail/a_id/15607/~/sandisk-ssd-dashboard-destek-bilgileri

2. Programı yükledikten sonra ayarlar sekmesinden Güncelleştirmeleri Denetle seçeneğine tıklayalım ve son güncelleştirmeleri alalım.(Merak etmeyin tüm bu işlemler fazla internet harcamıyor program boyutu da çok küçük)

3. Araçlar Sekmesine geçerek Güncelleştirmeleri Denetle seçeneğine tıklayalım. Bu işlemden sonra Bilgisayarı yeniden başlatmanızı isteyecektir. Tekrar başlatıp programı tekrar açınız.

4. Son aşamada Performans ekmesine geçin ve iyileştirme kısmından TRIM i Secin ve Trimi Şimdi çalıştır'ı tıklayınız ve işlem bu kadar..

5. Bendeki Eski ve yeni sonuclar ekrandaki gibi. Benim Bilgisayarım eski(data yolu eski teknoloji) olduğu için yazma hızı max 300Mb lara çıkabildi.Ancak siz 500Mb lara ulaşırsınız.

SanDisk SSD PLUS 120 GB, hız sorunu

Sql Server AlwaysOn tüm portlar

2019-06-19T11:56:00.001+03:00

Kaynak:
https://blogs.technet.microsoft.com/fort_sql/2012/07/03/sql-server-ports/

unix de büyük yada coklu dosyalarda ilk satırları silme

2019-03-19T13:47:00.000+03:00

Delete first and (OR) last line on files(s)

aşağıdaki gibi verimiz olsun

K|ACTCTTT|TXEC|123|TEMPCOLUMN|COLUMN|
18494002|448|1208004|1
18493007|228|1208004|1
ASDFTT|234342

<><>BU komutla ilk ve son satrıları silebiliriz.

sed '$d' > file1.txt | sed "1d"

<><>bu komutlada coklu dosyalarda çalıştırabilirsiniz.ilk satırı siler.

  for f in  *.txt ;do sed '1d' $f > tmpfile; mv tmpfile $f;done

<><>Çoklu dosyalarda baştan iki satır silinecekse

 for f in  *.txt ;do sed '1,2d' $f > tmpfile; mv tmpfile $f;done

unix,aix,linux,sed,multiplefiles

power shell yada cmd ekranında dummy file oluşturma

2019-03-18T14:33:00.002+03:00

windowsta bazen istediğimiz kadar büyüklükte bir dosya create etmek isteriz. Bunu komutlarla cok rahat yapabiliriz.

önce istediğimiz dizine aşağıdaki komutla gidiyoruz.
C:\>cd /d Z:\

Z:\>cd DENEMEFILE

35 bytle lık dummy adında bir dosya oluşturur.
Z:\DENEMEFILE>fsutil file createnew dummy 35

AlwaysOndan çıkarılmış database'i second (ikinci) note da AlwayOn'a alma

2019-02-14T15:12:00.003+03:00

Merhabalar,

Eğer Database'iniz daha önceden ikinci yada diğer notlarda ise çıkartmışsanız yada bir şekilde AlwaysOn listenerdan cıkarmışsanız. Aşağıdaki adımlarla ve benim gibi görsel değilde komutlarla çalışmaya alışmışsanız hızlı bir şekilde alwayson'a alabilirsiniz..

1Adım:
A) eğer Dblerinizin sürekli full ve log backupları alınıyorsa aşağıdaki komutla backuplarınız alındığı pathleri çıkarın..

SELECT          physical_device_name,
                backup_start_date,
                backup_finish_date,
                backup_size/1024.0 AS BackupSizeKB
FROM msdb.dbo.backupset b
JOIN msdb.dbo.backupmediafamily m ON b.media_set_id = m.media_set_id
WHERE database_name = 'DATABASE_NAME'
ORDER BY backup_finish_date DESC

Çıkan sonuc ".bak" ile biten dosya pathlerinden itibaren full backuptan sıra ile yukarıya restore edilecek(şuanki zamana kadar alınan backupları).

B)Eğer hazırda backupınız yoksa bu komutlarla full ve log backuplar alınır..(ilk note da çalıştırılacak.)
BACKUP DATABASE DATABASE_NAME
   FROM DISK = '\\10.10.10.12\backupfile\DATABASE_NAME.bak'
WITH COPY_ONLY;
GO

BACKUP LOG DATABASE_NAME
   FROM DISK = '\\10.10.10.12\backupfile\DATABASE_NAME.trn'
WITH COPY_ONLY;
GO

2Adım: ikinci notda çalıştırılacak restore işlemleri(elimizde backuplar var şimdi eşitlemeye geldi)
RESTORE DATABASE DATABASE_NAME
   FROM DISK = '\\10.10.10.12\backupfile\DATABASE_NAME.bak'
WITH NORECOVERY, REPLACE,
   MOVE 'vdk' TO 'W:\DATA\DATABASE_NAME.mdf',
   MOVE 'vdk_log' TO 'X:\LOG\DATABASE_NAME_log.ldf';
GO

RESTORE LOG DATABASE_NAME
   FROM DISK = '\\10.10.10.12\backupfile\DATABASE_NAME4.trn' WITH NORECOVERY;
GO

RESTORE LOG DATABASE_NAME
   FROM DISK = '\\10.10.10.12\backupfile\DATABASE_NAME3.trn' WITH NORECOVERY;
GO
RESTORE LOG DATABASE_NAME
   FROM DISK = '\\10.10.10.12\backupfile\DATABASE_NAME2.trn' WITH NORECOVERY;
GO
RESTORE LOG DATABASE_NAME
   FROM DISK = '\\10.10.10.12\backupfile\DATABASE_NAME1.trn' WITH NORECOVERY;
GO

3ADIM:Eşitleme tamamlandı şimdi sıra lsn'eri eşitlemek kaldı..
--->>eğer daha önce hiç gruba alınmadısya alttaki komuıtla gruba alabilirsin.
ALTER AVAILABILITY GROUP [AO_GROUPNAME] ADD DATABASE [DATABASE_NAME]
go

--gruba alınmışsa zaten aşagıdaki komutla eşitlemeyi başlatabilir ve AlwaysOna alabilirsin.
ALTER DATABASE [vdk] SET HADR AVAILABILITY GROUP = AO_GROUPNAME;
go

4.ADIM:Eğer backupı alınmış db içeriğine bakmak istersen aşağıdaki komutla detaylarını listeleyebilirsin.
RESTORE FILELISTONLY FROM DISK = '\\10.10.10.12\backupfile\DATABASE_NAME1.bak'

MSSQL,SQL,SQL SERVER, SQL BACKUP, ALWAYSON

Sybase ASE Load ve Drop database hatası ve kısır döngü

2018-01-02T12:54:00.000+03:00

Merhabalar,
Diyelimki VeriTabanı Load işleminde bu şekilde bir hata aldınız.

The log segment in the target database % is too small (% MB) to accommodate the log segment from the dumped database (% MB)

sizden LogSegmentin az yada cok olduğuınu söylüyor. bu durumda disk resize ederek sorunu aşağıdaki şekilde çözebilirsiniz
eğer hatada 15MegaByte'a ihtiyaç varsa aşağıdaki şekilde ekstra '15M' eklenebilirEğer GigaByte ise GigaByte eklenebilir '45G'

1> disk resize name='VT_log01', size= "15M"
2> go
1> alter database TEST_DB log on VT_log01 = "15M"
2> go

çalıştırdınız ama hata aldınız. Ne diyor.

An attempt was made to fetch logical page '%' from cache 'default data cache'. Page belongs to database '%' (12), object 'sysdepends' (12), index 'csysdepends' (2), partition 'csysdepends_12' (12) and not to database '%' (12), object 'sysreferences' (16), index 'sysreferences' (0), partition 'sysreferences_16' (16).

bu hatanın birçok sebebi olabilir. bunlardan biri de Dtabase Load ederken sizin tarafınızdan load işlemin kesilmesi yada bi şekilde kesilmiş olması. yani load tamamlanmadan load işlem kesildi ise bu hatayı alabilirsiniz.

yapmamız gereken. Database'i drop edip tekrardan create etmemiz.

1>drop database TEST_DB

dedik. :) Ancak bunda da hata alıyoruz.

An attempt was made to fetch logical page '%' from cache 'default data cache'. Page belongs to database '%' (12), object 'sysdepends' (12), index 'csysdepends' (2), partition 'csysdepends_12' (12) and not to database '%' (12), object 'sysreferences' (16), index 'sysreferences' (0), partition 'sysreferences_16' (16).

VeriTabanına ve disklerine zaten erişemiyoruz ki. Nasıl drop edelim.
Ozaman bizde VeriTabanını Repair yaparız.

dbcc dbrepair(TEST_DB,dropdb)
go

Database 'ebyn_arc' is not marked suspect. You cannot drop it with DBCC

yine hata verdi. Benim daha önceden işaretleme yaptığım birşey yok. bunu yapamazsın diyor.

O zman bizde Kısır döngüye giriyoruz. burada çıkmaz yoldayız. ne yapacağız.
Öncelikle sistem databaselerinin update yetkisini açacağız.

sp_configure "allow updates", 1
go

daha sonra database in statusunu değiştireceğiz. sonra sistem databaselerine erişimi kapatacağız. bunu yaparken sa logini ile yapmanız gerekmektedir.

  1> use master 
  2> go

  1> begin transaction
  2> update sysdatabases set status = 320
  3> where name = 'TEST_DB'
  4> go

kontorl edin gerçekten 1 tane mi kayıt etkilendi. sonra commitlemeyi yapın

  1> commit transaction
  2> go

daha sonra sistem database erişimi kaldırın.

     1> sp_configure "allow updates", 0
      2> go

sonra VeriTabanı instance ını kapatıyoruz tekrar açıyoruz.

   1> shutdown with nowait
2> go

kapattıktan sonra tekrar açmayı Unix/Linux sistemlerde komutla windows sistemlerde servislerden açıyorsunuz.

daha sonra dbccrepair komutu ile databasei kaldırıyoruz.

1> dbcc dbrepair(TEST_DB,dropdb)

2> go

bundan sonrası artık size kalmış. VeriTabanını tekrardan create edeceksiniz.

iyi şanslar.

Fiziksel Okuma ve Mantıksal Okuma (Physical read and Logical read)

2017-12-05T09:31:00.000+03:00

Soru — Vt'lerde Fiziksel okuma ve mantıksal okuma arasındaki fark nedir?

Cevap:

— Fiziksel Okuma Diskten okunan sayfa(Page)lar olarak tanımlanır.

— Mantıksal Okuma Bellekten okunan sayfa(Page)lar olarak tanımlanır. (RAM/cache).

BACKUP detected corruption in the database log hatası

2017-03-17T14:47:00.000+03:00

Veri Tabanlarınızdan birinde eğer log corruption olmuşsa aşağıdaki yöntemi uygulayabilirsiniz.

hata

BACKUP detected corruption in the database log. Check the errorlog for more information.
Msg: 3049, Level: 16, State: 1, Procedure: , Line: 1

>[Error] Script lines: 1-2 --------------------------
BACKUP DATABASE is terminating abnormally.
Msg: 3013, Level: 16, State: 1, Procedure: , Line: 1

çözüm
trun back-up' ı alıyoruz CONTINUE_AFTER_ERROR option'u ile

BACKUP LOG [MyDb]
TO DISK = N'C:\\MyDb.trn'
WITH NOFORMAT, NOINIT, NAME = N'MyDb', SKIP, NOREWIND,
NOUNLOAD, COMPRESSION, STATS = 10, NO_CHECKSUM, CONTINUE_AFTER_ERROR
GO

sonra full back-up alarak devam edebilirsiniz her ihtimale karşı elimizde bulunsun

BACKUP DATABASE [MyDb] TO DISK = N'C:\\MyDb.bak' WITH NOFORMAT, NOINIT, NAME = N'VDK-DRIVE', SKIP, REWIND, NOUNLOAD, STATS = 10

kolay gelsin..

ubuntuda windows alanı mount etme (mounting windows share on ubuntu )

2017-03-10T11:54:00.000+03:00

öncelikle cifs ' i yükleyeceğiz

# sudo apt-get install cifs-utils

linuxde id komutu ile group ve user id kodunu bulun(büyük ihtimal ikisi de 1000 dir)

#id

şimdi mount komutunu çalıştırabiliriz.

# sudo mount //WINSRV/SHARE -t cifs -o uid=1000,gid=1000,username=winuser /mnt/path

örnek:
#sudo mount //10.1.2.3/share -t cifs -o uid=1000,gid=1000,username=IBRAHIMPEKTAS,domain=BIM /mnt/share
bu kadar

:)

ubuntu rdesktop "ERROR: recv: Connection reset by peer" hatası

2016-11-28T12:58:00.003+03:00

ubuntudan Widows makinalara bağlantıda bu tarz hatalar alınıyorsa
windowsta seçili olan sadece rdp'ye izin ver seçeneğini kaldırmanız gerekir.

ubuntu rdesktop "ERROR: recv: Connection reset by peer" hatası

winowsta
Alt + x
Run
SystemPropertiesRemote.exe + ENTER
kaldırın "Allow connections only from computers running Remote Desktop with
Network Level Authentication (recommended).

Linux^te
# rdesktop -z -k it -u $MYUSER -z -g 1250x720 -p '$password' -d $MYDOMAIN $HOSTNAME -r sound:local
yada

remmina desktop^ta giriş yapmak için advance ayarlarında şu değişikliği yapmanız gerekebilir.

Send mails from command line

2016-01-20T11:11:00.001+02:00

Install the mailx command

On Ubuntu/Debian based systems the mailx command is available from 2 different packages -
1. heirloom-mailx
2. bsd-mailx
We shall be using the heirloom-mailx package because it has more features and options.
On CentOS/Fedora based systems, there is only one package named "mailx" which is the heirloom package.
To find out what mailx package is installed on your system, check the "man mailx" output and scroll down to the end and you should see some useful information.

# ubuntu/debian
$ sudo apt-get install heirloom-mailx

# fedora/centos
$ sudo yum install mailx

Using the mailx command

Once installed, the mailx command can be directly referenced with the name mail, so you just type in that in the command line.

1. Simple mail

Run the following command, and then mailx would wait for you to enter the message of the email. You can hit enter for new lines. When done typing the message, press Ctrl+D and mailx would display EOT.
After than mailx automatically delivers the email to the destination.

$ mail -s "This is the subject" someone@example.com
Hi someone
How are you
I am fine
Bye
EOT

2. Take message from a file

The message body of the email can be taken from a file as well.

$ mail -s "This is Subject" someone@example.com < /path/to/file

The message can also be piped using the echo command -

$ echo "This is message body" | mail -s "This is Subject" someone@example.com

3. Multiple recipients

To send the mail to multiple recipients, specify all the emails separated by a comma

$ echo "This is message body" | mail -s "This is Subject" someone@example.com,someone2@example.com

4. CC and BCC

The "-c" and "-b" options can be used to add CC and BCC addresses respectively.

$ echo "This is message body" | mail -s "This is Subject" -c ccuser@example.com someone@example.com

5. Specify From name and address

To specify a "FROM" name and address, use the "-r" option. The name should be followed by the address wrapped in "<>".

$ echo "This is message body" | mail -s "This is Subject" -r "Harry" someone@example.com

6. Specify "Reply-To" address

The reply to address is set with the internal option variable "replyto" using the "-S" option.

# replyto email
$ echo "This is message" | mail -s "Testing replyto" -S replyto="mark@gmail.com" someone@example.com

# replyto email with a name
$ echo "This is message" | mail -s "Testing replyto" -S replyto="Mark" someone@example.com

7. Attachments

Attachments can be added with the "-a" option.

$ echo "This is message body" | mail -s "This is Subject" -r "Harry" -a /path/to/file someone@example.com

8. Use external SMTP server

This is an exclusive feature, that you get only with heirloom mailx and not bsd mailx, or the mail command from gnu mailutils or the mutt command.
The mailx command can use an external smtp server to use to relay the message forward. The syntax is a bit lengthy but makes sense.

$ echo "This is the message body and contains the message" | mailx -v -r "someone@example.com" -s "This is the subject" -S smtp="mail.example.com:587" -S smtp-use-starttls -S smtp-auth=login -S smtp-auth-user="someone@example.com" -S smtp-auth-password="abc123" -S ssl-verify=ignore yourfriend@gmail.com

Here is a breakdown

$ echo "This is the message body and contains the message" | mailx -v \
> -r "someone@example.com" \
> -s "This is the subject" \
> -S smtp="mail.example.com:587" \
> -S smtp-use-starttls \
> -S smtp-auth=login \
> -S smtp-auth-user="someone@example.com" \
> -S smtp-auth-password="abc123" \
> -S ssl-verify=ignore \
> yourfriend@gmail.com

You can use the gmail smtp servers and send emails via your gmail account. That is so cool!

9. Verbose - watch smtp communication

When using external smtp servers, you can choose to watch the entire smtp communication that is done in the background. This is useful specially when testing or debugging smtp servers.

$ echo "This is the message body and contains the message from heirloom mailx" | mailx -v -s "This is the subject" -S smtp="smtp.gmail.com:587" -S smtp-use-starttls -S smtp-auth=login -S smtp-auth-user="mygmail@gmail.com" -S smtp-auth-password="mypassword" -S ssl-verify=ignore someone@example.com
Resolving host smtp.gmail.com . . . done.
Connecting to 74.125.68.109:587 . . . connected.
220 mx.google.com ESMTP je4sm32812877pbd.94 - gsmtp
>>> EHLO enlightened
250-mx.google.com at your service, [122.163.43.21]
250-SIZE 35882577
250-8BITMIME
250-STARTTLS
250-ENHANCEDSTATUSCODES
250-PIPELINING
250-CHUNKING
250 SMTPUTF8
>>> STARTTLS
220 2.0.0 Ready to start TLS
>>> EHLO enlightened
250-mx.google.com at your service, [122.163.43.21]
250-SIZE 35882577
250-8BITMIME
250-AUTH LOGIN PLAIN XOAUTH XOAUTH2 PLAIN-CLIENTTOKEN OAUTHBEARER
250-ENHANCEDSTATUSCODES
250-PIPELINING
250-CHUNKING
250 SMTPUTF8
>>> AUTH LOGIN
334 VXNlcm5hbWU6
>>> Ymx1ZWJ1enppdEBnbWFpbC5jb20=
334 UGFzc3dvcmQ6
>>> KnJpc2hhYmgzKg==
235 2.7.0 Accepted
>>> MAIL FROM:
250 2.1.0 OK je4sm32812877pbd.94 - gsmtp
>>> RCPT TO:
250 2.1.5 OK je4sm32812877pbd.94 - gsmtp
>>> DATA
354  Go ahead je4sm32812877pbd.94 - gsmtp
>>> .
250 2.0.0 OK 1417930703 je4sm32812877pbd.94 - gsmtp
>>> QUIT
221 2.0.0 closing connection je4sm32812877pbd.94 - gsmtp

Troubleshooting

In case the mails are not being delivered properly you need to check a few things. The first thing to check is that an smtp server (mta) is running locally. The netstat command can tell that

$ sudo netstat -ltnp | grep 25
[sudo] password for enlightened: 
tcp        0      0 0.0.0.0:25              0.0.0.0:*               LISTEN      2541/master     
tcp6       0      0 :::25                   :::*                    LISTEN      2541/master

If an stmp server like Postfix is running and still mails are not going, then try re-configuring Postfix for example. On Ubuntu/Debian systems, this can be done with the dpkg-reconfigure command

$ sudo dpkg-reconfigure postfix

Then retry, the mail command and it should work. If it still doesn't, try contacting your server provider.

Source: binarytides

Aix'de file sistem ve disk yönetimi

2016-01-05T16:30:00.000+02:00

# cfgmgr

The disks will now be presented as shown below. Use the lspv command to view the disks:

# lspv

hdisk0         00c23bed42b3aefe                   rootvg         active

hdisk1         00c23bed42b3afff                   rootvg         active

hdisk2         00525c6a888e32cd                   None

hdisk3         00c23bed32883598                   None

What Type of Disk Is Present

In this example, the new disks come in as hdisk2 and hdisk3. New disks will not necessarily come in sequential order. This is particularly true if the disks use disk slots and you do not populate them one after the other. Notice that the new disks have not yet been assigned to a VG, so the third column states None. We can tell what sort of disk each is by querying the ODM using the lsdev command:

# lsdev -Cc disk

hdisk0 Available 04-08-00-3,0 16 Bit LVD SCSI Disk Drive

hdisk1 Available 04-08-00-4,0 16 Bit LVD SCSI Disk Drive

hdisk2 Available 04-08-00-5,0 16 Bit LVD SCSI Disk Drive

hdisk3 Available 04-08-00-8,0 16 Bit LVD SCSI Disk Drive

The new disks are SCSI disks as are the others.

Disk Sizes: How to Tell

Let's now determine the size of the disks. There are a few methods you can use to see the disk size. Let's look at the most common methods. First, we'll use the lsattr command, which returns device information on the system. The format of the command is this:

lsattr -El < device>

# lsattr -El hdisk2

PCM             PCM/friend/scsiscsd               Path Control Module

False

algorithm       fail_over                        Algorithm

True

…

…

size_in_mb     18200                             Size in Megabytes

False

unique_id       21080006CB0A0AST318305LC03IBMscsi Unique device identifier

False

The above lsattr output states that the disk is 18200 MB or 17.7 GB.

You can also use the undocumented command bootinfo. Though this is discouraged by IBM, it will return the usable amount of disk that can be used by the system after formatting. Here's the format of the command:

bootinfo -s

# bootinfo -s hdisk2

17357

The above output states we have a 17357 MB disk or 16.9 GB.

The best and recommended way to determine the size of a disk is to use the getconf command. The getconf will extract the systemwide variables from the kernel. Here's the format:

getconf DISK_SIZE

# getconf DISK_SIZE /dev/hdisk2

17357

In the above output, the getconf returns 17357MB or 16.9 GB.

We have used three commands to get the disk sizes. Two of them agree on the size. Not bad, I guess. To get the correct disk size, use getconf, as this comes from the kernel, the heart of the AIX system.
Creating a Volume Group

For a disk to be used for storage, it needs to be added to an existing VG or a new VG. Let's first add it to a new VG called testvg. The format for the command I am now going to run is this:

mkvg -B -y

here, -B means it should be a big VG. It will be able to have up to 128 disks if required in the VG. The vg_name is the name you are going to call it, followed by a list of hdisks you wish to assign to the VG. Here is the command:

# mkvg -B -y testvg hdisk2

testvg

Viewing the current VGs on the system, we can see that it is present.

# lsvg

rootvg

holdvg

testvg

You can see that hdisk2 is now assigned to the VG testvg. Use the lspv command to confirm:

# lspv

hdisk0         00c23bed42b3aefe                   rootvg         active

hdisk1         00c23bed42b3afff                   rootvg        active

hdisk2         00525c6a888e32cd                   testvg         active

hdisk3         00c23bed32883598                   None

You can also query the disks contained in a VG by issuing the lsvg command:

# lsvg -p testvg

testvg:

PV_NAME           PV STATE         TOTAL PPs   FREE PPs   FREE DISTRIBUTION

hdisk2           active           542         542         109..108..108..108..109
Volume Group Maintenance

Let's now add another disk, hdisk3 to the volume group testvg. First, though, determine the disk size:

# getconf DISK_SIZE /dev/hdisk3

70006

It's 70 GB or thereabouts. Now, let's add the disk to the existing VG testvg, using the extendvg comamnd. The format for the command I am now going to run is this:

extendvg

Here, vg_name is the name of the VG, followed by a list of hdisks you wish to add to the VG. Here's the command:

# extendvg testvg hdisk3

Now let's view the disks and to see what VG is assigned to them. The following output states that hdisk2 and hdisk3 are assigned to the VG testvg:

# lspv

hdisk0         00c23bed42b3aefe                   rootvg         active

hdisk1         00c23bed42b3afff                   rootvg         active

hdisk2         00525c6a888e32cd                   testvg         active

hdisk3         00c23bed32883598                   testvg         active

Now that we've added the extra disk, let's get the size of the VG. In the following output, we can determine the size of the VG by locating the TOTAL PP value (total number of physical partitions on the disks). In this example, it's 87328 MB or 85.2 GB.

# lsvg testvg

VOLUME GROUP:       testvg                   VG IDENTIFIER: 00c23bed00004c00000

0013805b7d417

VG STATE:               active             PP SIZE:       32 megabyte(s)

VG PERMISSION:     read/write               TOTAL PPs:     2729 (87328 megabytes

…

…

To calculate the size of the VG, you can also use the following formula, using the values from the above lsvg command:

PP SIZE * TOTAL PP

Like so:

# expr 2729 \* 32

87328

To remove a disk from a VG, first be sure you have removed all data from that disk. You will be warned if there is data on the disk and you try to remove it, which is a good reminder in my books.

Typically, you would unmount any file systems and remove them using the rmfs command on the disk you want to remove. Alternatively, you could use migratepv to literally migrate the data from that disk to another disk contained in that VG. Let's assume all data has been removed from that disk. To remove a hdisk from a VG, use the reducevg command. The format for this example is this:

reducevg

Here, the vg_name is the name of the VG, followed by a list of hdisks you wish to remove from the VG. Here's the command:

# reducevg testvg hdisk2

The VG testvg now has only hdisk3 assigned to it:

# lsvg -p testvg

testvg:

PV_NAME           PV STATE         TOTAL PPs FREE PPs   FREE DISTRIBUTION

hdisk3           active           2187       1866       438..116..437..437..438
Dynamically Changing File System Sizes

In our VG, we also now have some data. A file system called /data_fs is present with a size of 8 GB. The file system has data residing in it; it's 71% used and has only 2.38 GB of free space left:

# df -g

Filesystem   GB blocks     Free %Used   Iused %Iused Mounted on

….

….

/dev/fslv00       8.00     2.38   71%       27     1% /data_fs

That file is getting pretty full, so let's now increase that file system to 12G. The format for increasing or decreasing a file system in GB or MB increments in this example is this:

chfs -a size= /file_filesystem

So to increase the file system from 8 GB to 12 GB, I could use this:

# chfs -a size=12G /data_fs

Filesystem size changed to 25165824

Looking at the newly change file system size, we see the change:

# df -g| grep data_fs

Filesystem   GB blocks     Free %Used   Iused %Iused Mounted on

…

...

/dev/fslv00       12.00     6.38   47%       27     1% /data_fs

Now suppose we wanted to reduce the file system size by 2G, so the new size would be 10G. We could use this:

# chfs -a size=10G /data_fs

Filesystem size changed to 20971520

Let's view the new file system size:

# df -g| grep data_fs

Filesystem   GB blocks     Free %Used   Iused %Iused Mounted on

..

...

/dev/fslv00       10.00     4.38   57%       27     1% /data_fs

Resource: http://www.mcpressonline.com/unix-/-aix/managing-disks-and-file-systems-on-aix.html

IBM AIX lere disk ekleme cıkarma ve mount etme yada mount edilmiş bir disk

2015-12-31T17:09:00.001+02:00

How to Add a Disk on AIX Logical Volume Manager (LVM)

1) Add the physical or virtual disk to the logical partition.

2) Rescan your hardware so that the OS is aware of your new disk.

# cfgmgr

3) Check to see your disk. For the purpose of this example, let’s say the new disk is hdisk2.

# lsdev -Cc disk
# lspv

4) Associate your new disk to a volume group. In this case, let’s create a new group called myvg and put hdisk2 in there.

# mkvg -y myvg hdisk2

5) Now you can look at the size of hdisk2. (This command won’t work if it’s not associated with a volume group).

# lspv hdisk2

6) Create a log logical volume for jfs2. This needs to be part of myvg. Note: in the example below, the type is jfs2log and we’re giving it 1 physical partition (PP).

# mklv -t jfs2log myvg 1

7) Look for your new logical volume (lv). Chances are that AIX named it loglv00.

# lsvg
# lsvg -l myvg

8) Create your production logical volume. Let’s make it about 30GB? I’ll name it mylv1.

# lsvg myvg
# mklv -t jfs2 -y mylv1 myvg 30G

9) Lay down your file system on mylv1.

# mkfs -o log=/dev/loglv00 -V jfs2 /dev/mylv1

10) Mount your filesystem.

# mkdir /myfs1
# mount -o log=/dev/loglv00 /dev/mylv1 /myfs1

11) Edit /etc/filesystem and change the dev and log stanzas to match your dev and log names from above

Aşağıdaki 4 komut vgname, lvname ve mount point alanlarına isim vererek sırayla çalıştırabilirsiniz.

öncesinde check etmeliyiz tüm diskleri
##cfgmgr

##mkvg -y vgname -s 512 hdiskX
##mklv -y lvname -t jfs2 -L lvname vgname 100
##crfs -v jfs2 -d lvname -m mount_point -p rw -A yes
##mount mount_point

50GB 'lik bir filesystem oluşturur ve mount_point olarak verdiğiniz alana mount eder.
Diskte yer varsa,
##chfs -size=+10G mount_point

seklinde alani buyutebilirsiniz,

IBM AIX lerde vmount: Operation not permitted hatası

2015-12-31T16:37:00.000+02:00

Merhabalar,

Eğer IBM AIX lerde mount etmeye çalışırken bu hatayı alıyorsanız sistem bunu yapmanıza izin vermiyor olabilir.

hata: vmount: Operation not permitted.

check edelim

### nfso -a

          client_delegation = 1
           nfs_max_read_size = 65536
          nfs_max_write_size = 65536
                 nfs_rfc1323 = 1
   nfs_securenfs_authtimeout = 0
    nfs_server_base_priority = 0
           nfs_server_clread = 1
      nfs_use_reserved_ports = 0
   nfs_v3_server_readdirplus = 1
    nfs_v4_fail_over_timeout = 0
                   portcheck = 0
           server_delegation = 1
             utf8_validation = 1
      nfs_server_close_delay = 0

ayarı değiştirip 1 yapmanız gerekebilir.

## nfso -o nfs_use_reserved_ports=1

şimdi mount komutunu çalıştırabilirsiniz

## mount dd2500:/data/col1/dbshare /dbshare

mount edilmiş mi check edelim
##mount

eğer back-up serverınız varsa orada da makinaya mount edilmiş var mı check edebilirsiniz

back-up serverda

###nfs show clients

.

Sybase iq da LOAD TABLE

2015-12-31T14:39:00.001+02:00

Sybase iq da LOAD TABLE komutu ile veri atımının yapılması.

LOAD [ INTO ] TABLE [ owner.]table-name
... ( load-specification [, …] )
...  { FROM | USING [ CLIENT ] FILE }  
{ 'filename-string' | filename-variable } [, …]
... [ CHECK CONSTRAINTS { ON | OFF } ]
... [ DEFAULTS { ON | OFF } ]
... [ QUOTES OFF ]
... ESCAPES OFF
... [ FORMAT { ascii | binary | bcp } ]
... [ DELIMITED BY 'string' ]
... [ STRIP { ON | OFF | RTRIM } ]
... [ WITH CHECKPOINT { ON | OFF } ]
... [ { BLOCK FACTOR number | BLOCK SIZE number } ]
... [ BYTE ORDER { NATIVE | HIGH | LOW } ]
... [ LIMIT number-of-rows ]
... [ NOTIFY number-of-rows ]
... [ ON FILE ERROR { ROLLBACK | FINISH | CONTINUE } ]
... [ PREVIEW { ON | OFF } ]
... [ ROW DELIMITED BY 'delimiter-string' ]
... [ SKIP number-of-rows ]
... [ WORD SKIP number ]
... [ START ROW ID number ]
... [ UNLOAD FORMAT ]
... [ ON PARTIAL INPUT ROW { ROLLBACK | CONTINUE } ]
... [ IGNORE CONSTRAINT constrainttype [, …] ]
... [ MESSAGE LOG ‘string’ ROW LOG ‘string’ [ ONLY LOG logwhat [, …] ]
... [ LOG DELIMITED BY ‘string’ ]

Kaynak: devamı ...

IBM AIX' lerde bir kaç kullanılacak ayar.

2015-12-31T14:18:00.001+02:00

# refresh -s sshd
0513-005 The Subsystem, sshd, only supports signal communication.

In order to have a process such as SSHD re-read its configuration file (/etc/ssh/sshd_config), you must do a "stopsrc -s sshd" followed by a "startsrc -s sshd":

# stopsrc -s sshd
0513-044 The sshd Subsystem was requested to stop.
# startsrc -s sshd
0513-059 The sshd Subsystem has been started. Subsystem PID is 3670044.

As you can see when you do the "stopsrc" it just says the process was requested to stop; not that it was stopped. This implies that there might be a delay before the process actually stops.

So what a lot of people do in scripts where they need to restart processes like this is something like this:

stopsrc -s sshd

sleep 10
startsrc -s sshd

Here is a one liner to request a stop to a SRC process like SSHD, verify it is stopped, and then restart it:

stopsrc -s sshd && until lssrc -s sshd | grep -q inoperative; do echo "Waiting for sshd to stop"; perl -e 'select(undef,undef,undef,.25)'; done && startsrc -s sshd

IBM AIX'de core sayılarını ve hostname version kernel bilgilerini kontrol etmek için kullanılan komutlar

1. lsdev -Cc processor

proc0 Available 00-00 Processor
proc2 Available 00-02 Processor
SMT thread processors are seen with bindprocessor

1. bindprocessor -q

The available processors are: 0 1 2 3

lparstat -i will show the virtual and logical processors.

1. lparstat -i | grep CPU

Online Virtual CPUs : 2
Maximum Virtual CPUs : 15
Minimum Virtual CPUs : 1
Maximum Physical CPUs in system : 2
Active Physical CPUs in system : 2
Active CPUs in Pool : 2
Physical CPU Percentage : 25.00%

topas -L shows logical processors,
mpstat shows virtual

1. lsattr -El proc0

frequency 1498500000 Processor Speed False
smt_enabled true Processor SMT enabled False
smt_threads 2 Processor SMT threads False
state enable Processor state False
type PowerPC_POWER5 Processor type False

1. lscfg -v | grep -i proc

Model Implementation: Multiple Processor, PCI bus
proc0 Processor
proc2 Processor

1. prtconf | pg

System Model: IBM,9111-520
Machine Serial Number: 10EE6FE
Processor Type: PowerPC_POWER5
Number Of Processors: 2
Processor Clock Speed: 1499 MHz
CPU Type: 64-bit
Kernel Type: 64-bit