Mar 21 2017

Oracle 11g R2 Cascade Standby Data Guard Installation and Configuration Steps

Category: Administration,Backup And RecoveryFatih Acar @ 16:32

Oracle 11g R2 database supports cascade standby database structure. If you are using RAC on primary side, your database version has to be least 11.2.0.2 to support cascade standby structure. You can use to reduce the load on your primary database with using cascade standby database structure.

Primary database redo is written to the standby redo log as it is received at a cascading standby database. The redo is not immediately cascaded however. It is cascaded after the standby redo log file that it was written to has been archived locally. A cascaded destination will therefore always have a greater redo transport lag, with respect to the primary database, than the cascading standby database.

Restrictions

  • Cascading Structure is not supported by Data Guard Broker.
  • To use Oracle RAC on primary side, you have to use least 11.2.0.2 verion.
  • Cascaded standby database do not support Real Time Apply

I will configure cascade standby database structure like below schema.

System Informations

PRIMARY DATABASE
VERSION : 11.2.0.3
IP : 192.168.9.129
SID : PRI
HOSTNAME : primary.localdomain

Continue reading “Oracle 11g R2 Cascade Standby Data Guard Installation and Configuration Steps”

1,541 total views, 4 views today

Tags: Database Administration, Oracle Administration


Mar 03 2017

Oracle 12c R2 Downloadable Now

Category: AdministrationFatih Acar @ 16:27

You can download Oracle 12c R2 database now. Only Linux x64 and Solaris Operation Systems supported for now.

You can download with below link;

http://www.oracle.com/technetwork/database/enterprise-edition/downloads/index.html

Also you can find new features about of Oracle 12c R2 with below links;

https://docs.oracle.com/database/122/

https://blogs.oracle.com/sql/entry/12_things_developers_will_love

1,535 total views, no views today

Tags: Oracle 12c, Oracle Administration


Feb 06 2017

Configure Default full-text language Parameter Server Configuration in SQL Server 2016

Category: AdministrationFatih Acar @ 15:33

You can change default full-text language server configuration option in SQL Server 2016 by using SQL Server Management Studio or Transact-SQL. default full-text language option specifies a default language value for full-text indexes. Linguistic analysis is performed on all data that is full-text indexed and is dependent on the language of the data. The default value of this option is the language of the server. For a localized version of SQL Server, SQL Server Setup sets the default full-text language option to the language of the server if an appropriate match exists. For a non-localized version of SQL Server, the default full-text language option is English.

Change Parameter With SQL Server Management Studio

  1. Open Object Explorer, right-click a server and select Properties.
  2. Click Advanced Step.
  3. Under Miscellaneous, use Default Full Text Language to specify a default language value for full-text indexed columns.

Change Parameter With Transact-SQL

I will change option to 1055 that is Turkish language code. You can see LCID codes of language with below query. You can change with below queries.

select * from sys.fulltext_languages;

USE ;
GO
EXEC sp_configure ‘show advanced options’, 1 ;
GO
RECONFIGURE
GO
EXEC sp_configure ‘default full-text language’, 1055 ;
GO
RECONFIGURE
GO

2,120 total views, 10 views today

Tags: MsSQL Server, SQL Server Administration


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

2,810 total views, no views today

Tags: Oracle Administration, Oracle SQL Query, SQL


Jan 10 2017

How to Create Linked Server From SQL Server To SQL Server Database

Category: AdministrationFatih Acar @ 15:58

You can connect another SQL Server database or different type database from SQL Server database with Linked Server feature. You can use below operations to create new linked server for SQL Server database to SQL Server database.

Create Linked Server Steps

  1. Create a user on remote database to use connection and give permission on which database using.
  2. SQL Server Management Studio with sysadmin privilege.
  3. Open SQL Server Management Studio with sysadmin privilege.
  4. Click New Linked Server below Server Objects > Linked Servers
  5. General Section > Select SQL Server as Server type.
  6. Write remote Instance Name to Linked Server text box.
  7. Security Section > Add > write Local and Remote User information for connection, and OK.
  8. You can see added new Linked Server below Linked Server section

1,525 total views, no views today

Tags: Database Administration, MsSQL Server, SQL Server Administration


Jan 05 2017

How to Find Required Missing Indexes For Performance Tuning in SQL Server Database

Category: Administration,SQLFatih Acar @ 08:44

Indexes play vital role for performance tuning on database systems. When you adjust correct indexes on tables, queries can run faster. Otherwise, a proper index can improve the performance and a bad index can hamper the performance. You can find missing indexes with below query. But, result of the query do not mean that you have to create indexes which are all results of query. You have to think which index will be helpful. If you create a lot index on the same table, your database size will increase and DML operation speed will be able to decrease.

Find Missing Indexes Query



SELECT TOP 25
dm_mid.database_id AS DatabaseID,
dm_migs.avg_user_impact*(dm_migs.user_seeks+dm_migs.user_scans) Avg_Estimated_Impact,
dm_migs.last_user_seek AS Last_User_Seek,
OBJECT_NAME(dm_mid.OBJECT_ID,dm_mid.database_id) AS [TableName],
'CREATE INDEX [INX_' + OBJECT_NAME(dm_mid.OBJECT_ID,dm_mid.database_id) + '_'
+ REPLACE(REPLACE(REPLACE(ISNULL(dm_mid.equality_columns,''),', ','_'),'[',''),']','') 
+ CASE
WHEN dm_mid.equality_columns IS NOT NULL
AND dm_mid.inequality_columns IS NOT NULL THEN '_'
ELSE ''
END
+ REPLACE(REPLACE(REPLACE(ISNULL(dm_mid.inequality_columns,''),', ','_'),'[',''),']','')
+ ']'
+ ' ON ' + dm_mid.statement
+ ' (' + ISNULL (dm_mid.equality_columns,'')
+ CASE WHEN dm_mid.equality_columns IS NOT NULL AND dm_mid.inequality_columns 
IS NOT NULL THEN ',' ELSE
'' END
+ ISNULL (dm_mid.inequality_columns, '')
+ ')'
+ ISNULL (' INCLUDE (' + dm_mid.included_columns + ')', '') AS Create_Statement
FROM sys.dm_db_missing_index_groups dm_mig
INNER JOIN sys.dm_db_missing_index_group_stats dm_migs
ON dm_migs.group_handle = dm_mig.index_group_handle
INNER JOIN sys.dm_db_missing_index_details dm_mid
ON dm_mig.index_handle = dm_mid.index_handle
WHERE dm_mid.database_ID = DB_ID()
ORDER BY Avg_Estimated_Impact DESC
GO

Query Result Example

Action For Performance Increase

You can use create scripts which are query result to create indexes. Avg_Estimated_Impact column give index average impact and according to this value you can think which index is important. You do not forget that if you create a lot index on the same table, this increase size of database and decrease DML operation performance. You should create index which has only high Avg_Estimated_Impact value.

664 total views, 2 views today

Tags: MsSQL Server, SQL Server Performance Tuning


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”

3,130 total views, 6 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;
/

5,041 total views, no views today

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


Nov 30 2016

Change Hostname on OEL 7 CentOS 7

Category: Linux & UnixFatih Acar @ 10:24

You can use hostnamectl command to list and change hostname of operating system on OEL 7 or CentOS 7 version.

List Hostname Status


[root@localhost ~]# hostnamectl 

   Static hostname: localhost.localdomain
         Icon name: computer-vm
           Chassis: vm
        Machine ID: 6f021291e2384a219e1f23ca39eadc9a
           Boot ID: ec195c69cfde417b88b86f4d7419cf24
    Virtualization: vmware
  Operating System: Oracle Linux Server 7.3
       CPE OS Name: cpe:/o:oracle:linux:7:3:server
            Kernel: Linux 4.1.12-61.1.28.el7uek.x86_64
      Architecture: x86-64

Change Hostname Status
Continue reading “Change Hostname on OEL 7 CentOS 7”

2,570 total views, 5 views today

Tags: Linux Administration, System Administration


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”

8,116 total views, 10 views today

Tags: PostgreSQL, PostgreSQL Administration


« Previous PageNext Page »