Oct 21 2016

MsSQL Server 2012 Availability Groups (AG) Manual Failover

Category: AdministrationFatih Acar @ 09:57

If primary side of Availability Groups (AG) is down and can not be online, you can perform failover operation. When primary side is down, probably, you can see state of Availability Groups at the secondary side like below.


Failover Steps



If you get an error after click Failover button, probably your AG has a problem. You can use force manual failover steps. Steps is here : http://www.fatihacar.com/blog/mssql-server-2012-availability-groups-ag-force-manual-failover/

Continue reading “MsSQL Server 2012 Availability Groups (AG) Manual Failover”

40 total views, no views today

Tags: MsSQL Server, SQL Server Administration, SQL Server Availability Group

Oct 20 2016

MsSQL Server 2012 Availability Groups (AG) Force Manual Failover

Category: AdministrationFatih Acar @ 08:15

When you can not access primary side of AG and you can not run failover on second side (disaster side), you can perform force manual failover operation on second side. You have to know that force manual failover operation can cause loss data which is able to last some database operation.

Failover State

When primary side is down, you can see your Availability Groups and database on SQL Server Management Studio at the secondary (disaster side) like below.


Failover Cluster Manager is down. Probably, you can not see any failover cluster on Failover Cluster Manager.


Force Manual Failover

You can use the forced quorum procedure on a WSFC cluster node to override the safety controls that took the cluster offline. This effectively tells the cluster to suspend the quorum voting checks, and lets you bring the WSFC cluster resources and SQL Server back online on any of the nodes in the cluster.

You have to restart your WSFC (Quorum) with below commands.

Open a command prompt and execute below commands.

net.exe stop clussvc
net.exe start clussvc /forcequorum

Continue reading “MsSQL Server 2012 Availability Groups (AG) Force Manual Failover”

85 total views, 10 views today

Tags: MsSQL Server, SQL Server Administration, SQL Server Availability Group

Oct 17 2016

Obtain Surname, Last Name and Mid Name From Full Name in Oracle

Category: Procedure,SQLFatih Acar @ 13:43

If you have a column with full name data and you want to split this data, you can use below function to split operation.

Split Name ve Surname

Table : test
Data1 : Emin Fatih Acar
Data2 : Fatih Acar

SQL> select fullname,
substr(fullname,0,instr(fullname,’ ‘,Instr(fullname,’ ‘,-1))-1) name,
substr(fullname,Instr(fullname,’ ‘,-1)+1) surname
from test;


name : Emin Fatih
surname : Acar

name : Fatih
surname : Acar

Split Name,Midname and Surname

SQL> select fullname,
substr(fullname,0,instr(fullname,’ ‘)-1) name,
substr(fullname,instr(fullname,’ ‘)+1,Instr(fullname,’ ‘,-1,1)- instr(fullname,’ ‘)-1) midname,
substr(fullname,Instr(fullname,’ ‘,-1)+1) surname,
from test;


name : Emin
midname : Fatih
surname : Acar


name : Fatih
midname : null
surname : Acar

135 total views, no views today

Tags: Database Administration, Oracle Administration, Oracle SQL Query

Oct 12 2016

Turkey Stay Summer Time Zone and How to Implement TZDATA Package for Linux Systems

Category: Linux & UnixFatih Acar @ 15:59

Turkey is breaking step with Europe and keeping summer time through the winter. Instead of turning clocks back an hour as they do in the European Union in the autumn, Turkey will keep the clocks as they are and effectively change the country’s time zone. You have to implement new tzdata-2016g package on your systems to accommodate new decision.

You can install from Red Hat yum repository if you use Red Hat system. (Red Hat, Centos, Oracle Linux)


yum install tzdata*2016g*

Both tzdata-java-2016g and tzdata-2016g package installation will be useful.

Check New Adjustment

You check new time zone status with zdump command like below.

zdump -c 2016,2017 -v “Europe/Istanbul”


You have not to see Oct 30 date and gmtoff value 7200. You have to see gmtoff value 10800.

855 total views, no views today

Tags: System Administration

Oct 12 2016

Oracle 11g Oracle Checkpoint Not Complete, Cannot Allocate New Log Warning

Category: Administration,Errors and SolutionsFatih Acar @ 11:54

Error: Checkpoint not complete, Cannot Allocate New Log Warning

Thread 1 cannot allocate new log, sequence 125487
Checkpoint not complete

Solution: Checkpoint not complete messages are generated due to the logs are switching so fast that the checkpoint associated with the log switch isn’t complete. You should increase redo log file size and amount to resolve. Also, If you use archive_lag_target parameter as near zero for example like 1 or 2 minutes, you should change this parameter zero (no lag) or more than 10-15 min. Oracle recommends that redo log switch operation interval should be between 15-30 minutes.


Show and change archive_lag_target parameter

SQL> show parameter archive_lag_target;
SQL> alter system set archive_lag_target=0 scope=both;
SQL> alter system set archive_lag_target=1800 scope=both;
1800 is 15 minutes. Parameter value as second.

If your archive_lag_target parameter is normal and you get checkpoint not complete error, you have to look your redo log file size and amount.
Continue reading “Oracle 11g Oracle Checkpoint Not Complete, Cannot Allocate New Log Warning”

230 total views, no views today

Tags: Oracle Error Solutions

Oct 11 2016

SQL Server 2012 AlwaysOn Availability Groups Insallation and Configuration

Category: AdministrationFatih Acar @ 09:00

Always On Availability Group is a new feature in SQL Server Database with 2012 release. Before this feature, we was using database mirroring feature in SQL Server 2005. But, mirroring has some limitations and weaknesses. and mirroring feature will be disabled near future.

AlwaysOn Availability Groups Features

  • Multi or Single Database Failovers
  • Multiple Secondaries
  • Active (Readably) Secondaries
  • Integrated HA Management


System Information

OS: Windows 2012 Server
DB: SQL Server 2012
Side 1: TESTDB1
Side 2: TESTDB2
Failover Cluster Name: TESTDB
Availability Group Name: TESTDB_AG
Listener Name: TESTDBVIP
Listener Port: 1433

AlwaysOn Availability Groups Installation Steps
Continue reading “SQL Server 2012 AlwaysOn Availability Groups Insallation and Configuration”

210 total views, no views today

Tags: Database Administration, MsSQL Server, SQL Server Administration, SQL Server Availability Group

Sep 27 2016

Keep Table in Buffer Pool Oracle 11g

Category: AdministrationFatih Acar @ 09:35

Oracle provides a feature to keep objects in the Buffer pool. This feature allows you to keep the frequently accessed table in memory itself which will provide the faster access of the table. The object will be kept under the KEEP pool and the purpose of the keep pool is to accommodate / cache the small objects like look up tables.

In general the server process has to bring data blocks for the table from the disk to the memory (buffer pool) which requires an overhead. Moreover the object gets removed from the memory whenever the object is not getting used or oracle needs room for another table in the buffer pool. If you keep the frequently accessed objects in the memory the above mentioned overhead can be reduced.

Keep Buffer is also working in First In First Out mode. If Oracle has more objects to keep in the KEEP buffer, automatically it removes the older objects from the keep buffer.  DB_KEEP_CACHE_SIZE is the parameter which determines the size of the Keep Buffer Pool. If this parameter is 0, Oracle adjust automatically size.

Keep Table in Buffer Pool

SQL> alter table testtable storage (buffer_pool keep);

Table altered.

Continue reading “Keep Table in Buffer Pool Oracle 11g”

366 total views, no views today

Tags: Database Administration, Oracle Administration

Aug 24 2016

PostgreSQL Hot Standby (Streaming Replication) Database Installation and Failover Operations

Category: AdministrationFatih Acar @ 09:33

PostgreSQL has few feature for standby technology. I generally use streaming replication. This is very easy and confident. Streaming replication use a replication user for replication operations.Streaming replication allows a standby server to stay more up-to-date than is possible with file-based log shipping. The standby connects to the primary, which streams WAL records to the standby as they’re generated, without waiting for the WAL file to be filled.

Streaming replication is asynchronous by default, in which case there is a small delay between committing a transaction in the primary and the changes becoming visible in the standby.

If you use streaming replication without file-based continuous archiving, the server might recycle old WAL segments before the standby has received them. If this occurs, the standby will need to be reinitialized from a new base backup. You can avoid this by setting wal_keep_segments to a value large enough to ensure that WAL segments are not recycled too early, or by configuring a replication slot for the standby. If you set up a WAL archive that’s accessible from the standby, these solutions are not required, since the standby can always use the archive to catch up provided it retains enough segments.

Test Systems Features

PG Version : 9.5
Primary DB IP :
Pimary DB Hostname : pg01
Hot Standby DB IP :
Host Standby DB Hostname : pg02
Virtual Service IP :
OS : Centos 7 x64
Data directory : /pgdata/data
Archive direcytory : /pgdata/ARCHIVELOG


1- Keygen Generate And Copy

[postgres@pg01 ~] ssh-keygen -t rsa
[postgres@pg02 ~] ssh-keygen -t rsa
[postgres@pg01 ~] ssh-copy-id -i .ssh/id_rsa.pub postgres@pg02
[postgres@pg02 ~] ssh-copy-id -i .ssh/id_rsa.pub postgres@pg01

2- Create Streaming Replication User on Primary DB

psql# createuser -U postgres -p 5432 repuser -P -c 10 –replication

3- Primary DB pg_hba.conf configuration

[root@pg01 ~] vi /pgdata/data/postgresql.conf

Add below line

host replication repuser md5

[root@pg01 ~] systemctl reload postgresql.service

4- Create Archive Directory on Hot Standby Server
Continue reading “PostgreSQL Hot Standby (Streaming Replication) Database Installation and Failover Operations”

1,696 total views, 5 views today

Tags: Database Administration, PostgreSQL Administration, PostgreSQL High Availability, PostgreSQL Hot Standby

Aug 23 2016

Create Dblink From Oracle to PostgreSQL Database

Category: Administration,AdministrationFatih Acar @ 14:36

Since fetching data from PostgreSQL database to Oracle database, you can create database link between two database systems. For that you can apply below operations.

Test Systems

PostgreSQL DB
IP :
OS : Centos 7 x64
Service : postgresql.service

Oracle DB
IP :
OS : OEL 6 x64


PostgreSQL Side

psql# create user dblinkuser encrypted password ‘dblinkuserpass’;

psql# grant usage on schema “testschema” to dblinkuser;

psql# grant select on “testschema”.tbl_test to dblinkuser;

Add new line to pg_hba.conf

vi /pgdir/data/pg_hba.conf

host testdb dblinkuser md5

systemctl reload postgresql.service

Oracle Side

yum install postgresql-odbc

vi /etc/odbc.ini

Description = PG
Driver = /usr/lib64/psqlodbc.so
ServerName =
Username = dblinkuser
Password = dblinkuserpass
Port = 5432
Database = testdb
Driver = /usr/lib64/liboplodbcS.so

vi $ORACLE_HOME/hs/admin/initPG.ora

# This is a sample agent init file that contains the HS parameters that are
# needed for the Database Gateway for ODBC

# HS init parameters
HS_FDS_SHAREABLE_NAME = /usr/lib64/psqlodbc.so
# ODBC specific environment variables
set ODBCINI=/etc/odbc.ini

# ODBC specific environment variables

# Environment variables required for the non-Oracle system
#set =

vi $ORACLE_HOME/network/admin/tnsnames.ora

PG =
(SID = PG)
(HS = OK)

vi $GRID_HOME/network/admin/listener.ora


lsnrctl reload

Listener reload can be adacuate to apply changes;but, sometimes not.

lsnrctl stop
lsnrctl start
lsnrctl status

Create Database Link on Oracle

CONNECT TO “dblinkuser”
IDENTIFIED BY ‘dblinkuserpass’


select * from “testschema”.”tbl_test”@PG

1,506 total views, no views today

Tags: Database Administration, Oracle Administration, PostgreSQL Administration

Aug 18 2016

How to Add EPEL Yum Repository on Oracle Linux 7

Category: Linux & UnixFatih Acar @ 11:50

EPEL (Extra Packages for Enterprise Linux) is open source and free community based repository project from Fedora team which provides 100% high quality add-on software packages for Linux distribution including RHEL (Red Hat Enterprise Linux), CentOS, and Scientific Linux. Epel project is not a part of RHEL/CentOS but it is designed for major Linux distributions by providing lots of open source packages like networking, sys admin, programming, monitoring and so on. Most of the epel packages are maintained by Fedora repo. Nagios packages are example in EPEL Repo.

Why we use EPEL repository?

  • Provides lots of open source packages to install via Yum.
  • Epel repo is 100% open source and free to use.
  • It does not provide any core duplicate packages and no compatibility issues.
  • All epel packages are maintained by Fedora repo.

Download EPEL Repo

Address : http://download.fedoraproject.org/pub/epel/7

Download link mirror: http://mirror.dgn.net.tr/epel//7/x86_64/e/epel-release-7-8.noarch.rpm

Install RPM

rpm -ivh epel-release-7-8.noarch.rpm

Control Epel Repo

yum repolist

Install Nagios Packages

List Nagios Packages
yum –enablerepo=epel -y list nagios-plugins*

Install Packages
yum –enablerepo=epel -y install nrpe nagios-plugins

881 total views, 5 views today

Tags: Linux Administration, Oracle Linux

Next Page »