Jun 15 2016

PostgreSQL Frequently Using Commands

Category: Administration,SQLFatih Acar @ 16:03

List, Add, Drop Database

List databases

postgres=# \d

Add database

postgres=# createdb testdb


postgres=# create database testdb with owner facar;

Drop database

postgres=# dropdb testdb


List, Add, Drop, Grant User and Schema

List Users

postgres=# select * from pg_user;

Add User

postgres=# create user facar with encrypted password ‘password’;


postgres=# createuser (interactive user create)

Change User Password

postgres=# \password facar
postgres=# alter user facar with encrypted password ‘newpassword’;

Create Schema

postgres=# create schema schfacar;
postgres=# grant usage on schema schfacar to facar;
postgres=# grant all on schema schfacar to facar;

Add Search Path

postgres=# alter user facar set search_path=schfacar;

Grant Permissions to User

postgres=# alter user facar superuser;
postgres=# alter user facar createdb;
postgres=# alter user facar replication;
postgres=# alter user facar bypassrls;
postgres=# grant all privileges on database testdb to facar;

Revoke Permissions from User

postgres=# alter user facar nosuperuser;
postgres=# alter user facar nocreatedb;
postgres=# alter user facar noreplication;
postgres=# alter user facar nobypassrls;
postgres=# revoke all privileges on database testdb from facar;

Connect Database

[root@testdb ~] su – postgres
-bash-4.2$ psql
-bash-4.2$ psql testdb (connect testdb)
-bash-4.2$ psql -U facar testdb (connect testdb with facar user)
-bash-4.2$ psql -U facar testdb -h “” -p 5432 (connect testdb with facar user on database that is IP and 5432 port)

[root@testdb ~]  psql
[root@testdb ~]  psql testdb (connect testdb)
[root@testdb ~]  psql -U facar testdb (connect testdb with facar user)
[root@testdb ~]  psql -U facar testdb -h “” -p 5432 (connect testdb with facar user on database that is IP and 5432 port)

Note : If you want to connect database with different user from postgres, you have to add line pg_hba.conf like that

host         all            all               md5

Information Queries

postgres=# \conninfo (current connection information)
postgres=# \dt (tables of current user schema)
postgres=# \d (list of object of current user schema)
postgres=# \d tablename (columns of tablename)
postgres=# \df (list of functions)
postgres=# \df+ (list of functions with details)
postgres=# select version(); (detailed version of database)
postgres=# \? (psql commands list for help)
postgres=# \h (list of help options)
postgres=# \h CREATE USER (help of CREATE USER command)
postgres=# \timing (open timing to show query execution time)
postgres=# \c testdb (connect testdb)
postgres=# \c testdb facar (connect testdb with facar user)
postgres=# select pg_database_size(current_database()); (size of current database as byte)
postgres=# select pg_database_size(‘testdb’); (size of testdb database as byte)
postgres=# select pg_relation_size(‘tablename’); (size of table as byte)
postgres=# select usename, datname from pg_stat_activity; (who are connected to which database now)
postgres=# select pg_reload_conf(); (to reload configurations like service postgresql reload)

371 total views, 10 views today

Tags: Database Administration, PostgreSQL, PostgreSQL Administration

May 26 2016

Oracle 12c : RMAN New Features and Enhancements

Category: Administration,Backup And RecoveryFatih Acar @ 10:45

 1. Fine Grained Recovery

With Oracle Database 12c, you can use a simple RECOVER TABLE command to perform a point-in-time recovery of a table/partition without having to go through a manual point-in-time recovery process. This command automatically performs the following steps: creation of the auxiliary instance, table recovery, exporting of the object, and importing it into the production database.

2. Support For Multitenant Databases

Oracle Database 12c offers this unprecedented consolidation feature called Oracle Multitenant. This capability simplifies database consolidation and management by enabling many individual pluggable databases (PDBs) to be “plugged-into” and supported within a container database (CDB).  Data protection is greatly simplified because you can perform backup and recovery at the CDB level, which includes and protects all the associated PDBs. For additional flexibility, you can still choose to perform backup and recovery for an individual PDB or a selected group of PDBs.

3. Improved RMAN Duplication (Cloning) Performance

Duplicating an Oracle database can be performed in many ways. Today, customers use both Oracle features such as RMAN DUPLICATE or storage-based snapshot and cloning technologies. RMAN duplication can be performed by using an existing backup or by directly duplicating the database using ACTIVE DUPLICATE.  Prior to Oracle Database 12c,  the ACTIVE DUPLICATE process used production database processes to send image copies across the network. This could be a time-consuming activity because the duplication process is directly proportional to the database size. Now, with 12c, the database duplication process has been improved, with the use of backup sets instead of image copies. As a result, the database size is relatively smaller because RMAN skips unused blocks, committed undo blocks etc. Plus, you can use compression and multi-section options for even faster duplication. Moreover, auxiliary channels from the destination site are used to PULL the backups over the network, as opposed to the PUSH method, used prior to 12c.

4. Faster Recovery in a Data Guard or Active Data Guard Environment

You may already be aware of some cool RMAN features that are supported with Active Data Guard – for example, direct Block Media Recovery from the standby. However, in the event of either primary or standby datafile corruption (e.g. due to media errors), the traditional recovery process would be to copy the backup over the network and perform a restore/recovery.  With Oracle Database 12c, there is a new RMAN keyword called “FROM SERVICE” whereby you can perform restores directly from the standby or from the primary (depending on which site has issues). This command creates a backup set and streams it over the network. This new process dramatically reduces the overall recovery time.

5. Expansion of Multi-Eection Support

Prior to Oracle Database12c, parallelizing a single data file using MULTI SECTION was only supported with a level 0 backup or a full backup set. From 12c, Multi section is now supported with incremental backups as well as image copy backups.

6. Simplified Cross-Platform Migration

Migrating the database from one platform to another can be performed in many ways. Oracle supports both database-level migration and tablespace-level migration. Database-level migration requires the endian type to be same on the source and destination platforms. Using tablespace migration, you can migrate across platforms and across endian formats. Oracle 12c introduces new keywords – FROM PLATFORM and TO PLATFORM. Using these keywords, RMAN takes care of converting the endian-ness,  so that the overall process is simplified. Depending on the availability requirements, tablespace migration can be performed with either long downtime or reduced downtime processes.

a) When using a longer downtime model, you place the tablespace(s) in read-only mode, take the full backup, and restore at the destination. You also take the metadata export of the tablespace at the source and then apply at the destination. Once you’re done, the tablespaces are made readable/writable at the destination.

b) When using a reduced downtime model, you can keep your source database running for a longer time by doing incremental backups to the destination. Only the last step involves the procedure mentioned in (a).

7. Separation of Duty

A new role SYSBACKUP is introduced to separate backup administrator tasks from the SYS role. You can use this administrative privilege to perform backup and recovery operations from either RMAN or from SQL*Plus.

8. SQL interface in RMAN

Beginning with Oracle Database12c, you no longer have to switch between the SQL*Plus interface and RMAN interface. The RMAN interface now supports SQL commands so you can directly run the commands from within RMAN.

Source : Oracle Documents

716 total views, 10 views today

Tags: Oracle 12c, Oracle Administration, Oracle Rman Backup

Mar 02 2016

ORA-31634 job already exists When Export Operation

Category: Errors and SolutionsFatih Acar @ 17:07


ORA-31634: job already exists
ORA-31664: unable to construct unique job name when defaulted

This error normally occurs when you do not specify a unique JOB_NAME for the DataPump job and for some reason that JOB_NAME already exists in the database, or else if you are running many jobs at the same time (more than 99 jobs) and DataPump cannot create a new job.

Continue reading “ORA-31634 job already exists When Export Operation”

1,953 total views, 15 views today

Tags: Oracle Administration, Oracle Error Solutions

Jan 20 2016

MsSQL Server Unknown Server Hostname Problem Solution

Category: Administration,Errors and SolutionsFatih Acar @ 12:25

When you try to connect MsSQL Server database, you can get an error that is Unknown Server Hostname or similar sometimes. You can resolve this problem with a few operation.


If you use SERVERNAME\INSTANCENAME statement, you can try SERVERNAME;instance=INSTANCENAME statement

1,666 total views, no views today

Tags: Database Administration, MsSQL Server, MsSQL Server Error and Solutions

Jan 12 2016

Oracle 11g Data Guard Failover With Python Script

Category: Administration,Backup And Recovery,Linux & Unix,PythonFatih Acar @ 10:56

I wrote a python script to perform failover steps and to take product database IP addresses when product database is down. If you dont use data guard broker or other application for failover operations, you can use this script for failover. My scenario is that I have Oracle 11gR2 2 nodes RAC product database and 1 node standby real time apply type data guard database. When the product database is fail, the data guard database can run as product database with take product database IP addresses and service name. Python script will do all steps that take product IP address, take product service name, perform failover steps.

I made test when data guard was archivelog apply type, script could run correctly.


Product Database : Oracle 11g R2 ( RAC
Data Guard : Oracle 11g R2 ( Single Instance With Grid Infrastructure
Operating System : Oracle Linux 6.6
Data Guard Type : Real Time Apply From Redo Logs
Script Name : failover.py

Python script file is failover.py .Firstly, we have to change parameters in script. You can use vi editor. I generally create python files under /opt/scripts directory.


Script has 3 types take virtual IP process. If you can not see product virtual IP address when you run ifconfig command after run failover.py script, you can use other type virtual IP process.

The product database is fail. We have to run data guard database like product database. We can use failover.py script.

Run failover.py

[root@dg scripts]# python failover.py


If all parameters are correct, you can write “evet” and push enter.

Continue reading “Oracle 11g Data Guard Failover With Python Script”

5,737 total views, 10 views today

Tags: Database Administration, Oracle Administration, Oracle Backup and Restore, Oracle Data Guard Failover, Oracle Failover

Dec 04 2015

RPM Command Using on Linux Systems

Category: Linux & UnixFatih Acar @ 15:46

If you connect linux repo, you can use yum install or yum update to manage your package. On the other hand, if you have RPM package, you can use rpm command to manage packages.

RPM command has been using for installing, uninstalling, upgrading, listing,querying and checking RPM packages on linux system. You can use rpm command with root user.

Frequently Used RPM Commands

Install RPM Package

[root #] rpm -ivh postgresql-libs-8.4.20-1.el6_5.x86_64.rpm

Update RPM Package

[root #] rpm -Uvh postgresql-libs-8.4.20-1.el6_5.x86_64.rpm

List All Installed Packages

[root #] rpm -qa

Search From Installed Packages

[root #] rpm -qa | grep postgresql-libs

Take Information From Installed Package

[root #] rpm -qi postgresql-libs

Uninstall RPM Package

[root #] rpm -ev postgresql-libs

2,095 total views, 10 views today

Tags: Linux Administration, Oracle Linux, System Administration

Dec 02 2015

Move Datafiles Of Tablespaces With RMAN Set Newname in Oracle 11g

Category: Administration,Backup And RecoveryFatih Acar @ 17:30

You can move datafiles of tablespaces. Scenarios can be asm to file system or file system to file system or file system to file system or asm to asm. Below operations show how to move datafiles. You have to take a backup and database has to be archivelog mode before this operations because of that you do restore and recover operation.


1 – Database has to be Archivelog Mode
2 – Take Full Rman Backup
3 – Execute Rman Run block

Run Block Description:
Offline Tablespace.
Set Newname.
Restore Datafile To New Location.
Write New Location information to controlfile with switch datafile all.
Recover Datafile.
Online Tablespace.


List Tablespaces

SQL> select tablespace_name from dba_tablespaces;

List Datafile Directories

SQL> select tablespace_name,file_name from dba_data_files;

Move Datafile From Asm To File System

SET NEWNAME FOR DATAFILE ‘+DATA/dg/datafile/USERS.329.896267113’ TO ‘/tmp/USERS.329.896267113’;

Move Datafile From File System To From Asm
Continue reading “Move Datafiles Of Tablespaces With RMAN Set Newname in Oracle 11g”

3,170 total views, 10 views today

Tags: Database Administration, Oracle, Oracle Administration, Oracle Backup and Restore, Oracle Rman Backup, Oracle Rman Restore

Nov 15 2015

Auto Increment Primary Key With Trigger in Oracle 11g

Category: Administration,Procedure,SQLFatih Acar @ 14:50

You can provide auto increment primary with trigger by appling below operations.

Create Table

(id number primary key,
name varchar2(100));

Create Sequence

MAXVALUE 999999999

Create Trigger For Auto Increment

before insert on TESTUSER.TBL_TEST
for each row
if inserting then
if :NEW.”ID” is null then
select TESTUSER.SEQ_TEST_ID.nextval into :NEW.ID from dual;
end if;
end if;

Oracle 12c release has primary key auto increment feature with identity type column. You can use like below.
Continue reading “Auto Increment Primary Key With Trigger in Oracle 11g”

1,959 total views, no views today

Tags: Database Administration, Oracle, Oracle Administration, Oracle SQL Query

Oct 02 2015

How to Create Virtual IP Address on Oracle Linux System

Category: Linux & UnixFatih Acar @ 16:50

You can create virtual IP addresses on Oracle Linux, Redhat and Centos Operating Systems with below operations.

Virtual IP address can be temporary or static according to your wishes. You have to have a ethernet adaptor interface for example eth0. You can use ifconfig eth0:0 <IP address> syntax to create temporary virtual IP.

Create Temporary Virtual IP

[root@test ~]# ifconfig eth0:0

Delete Temporary Virtual IP

[root@test ~]# ifconfig eth0:0 down

Create Static Virtual IP

[root@test ~]# cd /etc/sysconfig/network-scripts
[root@test ~]# vi ifcfg-eth0:0


Activate Network Configuration

[root@test ~]# service network restart

7,452 total views, 10 views today

Tags: Linux Administration, Oracle Linux, System Administration

Jul 01 2015

How to Move Datafiles to New Disk in MsSQL Server 2008

Category: AdministrationFatih Acar @ 15:38

If database data disk space is full or you have another problem about of disk, you can move datafiles to new disk in MsSQL Server 2008 database.

Show Current Paths and Names

use master
select name,physical_name from sys.master.files where database_id = DB_ID(‘DATABASENAME’)
dbf1 D:\OLDLOCATION\disk_1.mdf
dbf1_log D:\OLDLOCATION\disk1_log.ldf

Shutdown Database

use master
alter database DATABASENAME modify file(dbf1,filename=’E:\NEWLOCATIOB\disk_1.mdf’);
alter database DATABASENAME modify file(dbf1_log,filename=’E:\NEWLOCATIOB\disk1_log.ldf’);

Move Datafiles to New Disk
Continue reading “How to Move Datafiles to New Disk in MsSQL Server 2008”

3,283 total views, 10 views today

Tags: Database Administration, MsSQL Server

Next Page »