Feb 03 2017

Round Up A Number By Protecting 2 Decimal in Oracle

Category: SQLFatih Acar @ 12:04

I will show how to round up a number by protecting 2 decimal. When I use round or ceil function, I can not have correct result. I want to round a number upwards with calculate 2 decimal.

I want result like below examples.

1.2445 > 1.25
1.21 > 1.21
1.521 > 1.53
1.1 > 1.1

You can use trunc function with add 0.009 to number.

Examples

select trunc(1.224 + 0.009,2) from dual;
1.23
select trunct(1.16 + 0.009,2) from dual;
1.16
select trunct(1.521 + 0.009,2) from dual;
1.53

370 total views, 10 views today

Tags: Oracle Administration, Oracle SQL Query, SQL


Dec 09 2016

Check Service Status With Python on OEL 7 / Centos 7

Category: Linux & Unix,PythonFatih Acar @ 21:42

I write a python code to check mysql service open or close. It is simple and you can change service_name parameter in the code to check another services. You can download from here check_service_status.py Below code maybe can not run because of python language format is tab structure. You can use with download original code check_service_status.py

Check MySQL Service Status


#-*- coding: utf-8 -*-

import os
import commands

service_name = "mysql"

def check_service_status(service_name):

        status = os.system('systemctl status '+service_name+ ' > /dev/null')
        return status

def main():

        if (check_service_status(service_name) == 0):
                print "Running"
        else:
                print "Stopped"
main()

Download

Execute Script
Continue reading “Check Service Status With Python on OEL 7 / Centos 7”

1,536 total views, no views today

Tags: Linux Administration, Python, System Administration


Dec 08 2016

Update Another Column On The Same Table With Trigger While Updating Table in Oracle

Category: Errors and Solutions,Procedure,SQLFatih Acar @ 11:01

If you want to do update another column when execute an update on table, you can use before update trigger. If you use after update trigger, you can get some errors. if you use pragma autonomous_transaction parameter as declare in trigger you can get errors like that are “ORA-00060: deadlock detected while waiting for resource”, “ORA-06512: at “trigger_name” line 7″,”ORA-04088:error during execution of trigger “trigger_name”” if you don’t use, you can get errors like that are “ORA-04091: table tablename is mutating, trigger/function may not see it”,”ORA-06512: at trigger_name”, line 6″,”ORA-04088: error during execution of trigger trigger_name”

You can resolve this problem with using before update trigger. You can update another column of updating the same table.

Trigger Example

My example is about that If status column of table1 change from 0 to 1, I will write another columns that are name, surname and studentno at the same table table1.

create table table1 (id number, status number, name varchar2(100), surname varchar2(100), studentno varchar2(100),address varchar2(500),city varchar2(100));

create or replace trigger trg_trigger_name_upt
before update of status on table1
referencing OLD as old NEW as new
for each row
v_studentno varchar2(100);
begin

IF :old.status = 0 AND :new.status = 1 and UPDATING THEN

:new.name := ‘Fatih’;
:new.surname := ‘Acar’;
v_studentno := seq_studentno.nextval;
:new.studentno := v_studentno;

END IF;

end;
/

1,620 total views, 10 views today

Tags: Oracle Administration, Oracle Error Solutions, Oracle SQL Query


Nov 11 2016

PostgreSQL Upgrade From 9.5 Version To 9.6 Version With PG_UPGRADE

Category: AdministrationFatih Acar @ 11:03

pg_upgrade (formerly called pg_migrator) allows data stored in PostgreSQL data files to be upgraded to a later PostgreSQL major version without the data dump/reload typically required for major version upgrades, e.g. from 8.4.7 to the current major release of PostgreSQL. It is not required for minor version upgrades, e.g. from 9.0.1 to 9.0.4.

Major PostgreSQL releases regularly add new features that often change the layout of the system tables, but the internal data storage format rarely changes. pg_upgrade uses this fact to perform rapid upgrades by creating new system tables and simply reusing the old user data files. If a future major release ever changes the data storage format in a way that makes the old data format unreadable, pg_upgrade will not be usable for such upgrades. (The community will attempt to avoid such situations.)

pg_upgrade does its best to make sure the old and new clusters are binary-compatible, e.g. by checking for compatible compile-time settings, including 32/64-bit binaries. It is important that any external modules are also binary compatible, though this cannot be checked by pg_upgrade.

pg_upgrade supports upgrades from 8.4.X and later to the current major release of PostgreSQL, including snapshot and alpha releases.

Source : postgresql.org

Postgres_logo

Steps Of Upgrade

  • Install PostgreSQL new release packages
  • Create new directory for 9.6
  • Initialize 9.6 version in 9.6 directory
  • Configure pg_hba.conf and postgresql.conf regarding to changed parameters of 9.5 version
  • Stop 9.5 PostgreSQL service
  • Start pg_upgrade
  • Change PostgreSQL service name and start PostgreSQL 9.6

System Information

OS : Centos 7 x64
PG : 9.5
DIR : /pgdata/9.5/data/
SERVICE : postgresql.service

Install PostgreSQL new release packages

[root@testdb /tmp]# yum install postgresql96-libs.x86_64 postgresql96.x86_64 postgresql96-server.x86_64 postgresql96-contrib.x86_64 postgresql96-devel.x86_64 postgresql96-python.x86_64 postgresql96-plpython.x86_64

Create new directory for 9.6
Continue reading “PostgreSQL Upgrade From 9.5 Version To 9.6 Version With PG_UPGRADE”

4,555 total views, 20 views today

Tags: PostgreSQL, PostgreSQL Administration


Nov 01 2016

Change Hostname in RHEL 7

Category: Linux & UnixFatih Acar @ 10:29

Changing hostname is different from RHEL 6 version in RHEL 7. You can use few method to change hostname. I will demonstrate useful and easy method that is hostnamectl command.

Show Current Hostname

[root@localhost ~]# hostname
localhost.localdomain

or

[root@localhost ~]# hostnamectl status
Static hostname: localhost.localdomain
Icon name: computer-vm
Chassis: vm
Machine ID: 418f684e1a5c45c9a22f76065c732de3
Boot ID: 9b07d8a45e60437e9ef31164d394ad17
Virtualization: vmware
Operating System: CentOS Linux 7 (Core)
CPE OS Name: cpe:/o:centos:centos:7
Kernel: Linux 3.10.0-327.36.3.el7.x86_64
Architecture: x86-64

Change Hostname
Continue reading “Change Hostname in RHEL 7”

1,856 total views, 25 views today

Tags: Linux Administration, System Administration


Oct 31 2016

Firewalld Add New Port, List All Ports, Remove Port with firewall-cmd command in Oracle Linux 7

Category: Linux & UnixFatih Acar @ 16:32

What is firewalld

Firewalld provides a dynamically managed firewall with support for network/firewall zones that define the trust level of network connections or interfaces. It has support for IPv4, IPv6 firewall settings, ethernet bridges and IP sets. There is a separation of runtime and permanent configuration options. It also provides an interface for services or applications to add firewall rules directly.

Benefits of using firewalld

Changes can be done immediately in the runtime environment. No restart of the service or daemon is needed.

With the firewalld D-Bus interface it is simple for services, applications and also users to adapt firewall settings. The interface is complete and is used for the firewall configuration tools firewall-cmd, firewallctl, firewall-config and firewall-applet.

The separation of the runtime and permanent configuration makes it possible to do evaulation and tests in runtime. The runtime configuration is only valid up to the next service reload and restart or to a system reboot. Then the permanent configuration will be loaded again. With the runtime environment it is possible to use runtime for settings that should only be active for a limited amount of time. If the runtime configuration has been used for evaluation, and it is complete and working, then it is possible to save this configuration to the permanent environment.

firewall

Add a Port to Firewalld as temporary

[root@test ~]# firewall-cmd –add-port=1521/tcp

Add a Port to Firewalld as permanent
Continue reading “Firewalld Add New Port, List All Ports, Remove Port with firewall-cmd command in Oracle Linux 7”

3,305 total views, 20 views today

Tags: Linux Administration, System Administration


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.

sqlserverfailoversteps1

Failover Steps

sqlserverfailoversteps2

sqlserverfailoversteps3

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/

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

1,415 total views, 15 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.

sqlserverforcemanualfailover1

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

sqlserverforcemanualfailover2

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”

1,610 total views, 5 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;

Result:

Data1
name : Emin Fatih
surname : Acar

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

Result:

Data1
name : Emin
midname : Fatih
surname : Acar

Data2

name : Fatih
midname : null
surname : Acar

1,186 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)

https://access.redhat.com/errata/RHEA-2016:1982

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”

tzdataturkey

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

5,096 total views, 20 views today

Tags: System Administration


Next Page »