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”

15 total views, 15 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 : 192.168.10.11
Pimary DB Hostname : pg01
Hot Standby DB IP : 192.168.10.12
Host Standby DB Hostname : pg02
Virtual Service IP : 192.168.10.10
OS : Centos 7 x64
Data directory : /pgdata/data
Archive direcytory : /pgdata/ARCHIVELOG

streaming_replication

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 192.168.10.12/32 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,076 total views, 15 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 : 192.168.10.10
OS : Centos 7 x64
Service : postgresql.service

Oracle DB
IP : 192.168.10.20
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 192.168.10.20/32 md5

systemctl reload postgresql.service

Oracle Side

yum install postgresql-odbc

vi /etc/odbc.ini

[PG]
Description = PG
Driver = /usr/lib64/psqlodbc.so
ServerName = 192.168.10.10
Username = dblinkuser
Password = dblinkuserpass
Port = 5432
Database = testdb
[Default]
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_CONNECT_INFO = PG
HS_FDS_TRACE_LEVEL = 0
HS_FDS_SHAREABLE_NAME = /usr/lib64/psqlodbc.so
HS_LANGUAGE = AMERICAN_AMERICA.WE8ISO8859P9
#
# 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 =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.10.20)(PORT = 1521))
(CONNECT_DATA =
(SID = PG)
)
(HS = OK)
)

vi $GRID_HOME/network/admin/listener.ora

SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(SID_NAME=PG)
(ORACLE_HOME=/oracle/db/11.2.0/db_home)
(PROGRAM=dg4odbc)
)
)

lsnrctl reload

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

lsnrctl stop
lsnrctl start
lsnrctl status

Create Database Link on Oracle

CREATE DATABASE LINK PG
CONNECT TO “dblinkuser”
IDENTIFIED BY ‘dblinkuserpass’
USING ‘PG’;

Test

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

960 total views, 5 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

641 total views, 5 views today

Tags: Linux Administration, Oracle Linux


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

or

postgres=# create database testdb with owner facar;


Drop database

postgres=# dropdb testdb

Postgres_logo

List, Add, Drop, Grant User and Schema

List Users

postgres=# select * from pg_user;

Add User

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

or

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 “192.168.20.12” -p 5432 (connect testdb with facar user on database that is 192.168.20.12 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 “192.168.20.12” -p 5432 (connect testdb with facar user on database that is 192.168.20.12 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               192.168.20.12/32          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)

1,267 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

1,671 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

Error

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.

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

3,693 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.

Suggest

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

2,447 total views, 10 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.

Systems

Product Database : Oracle 11g R2 (11.2.0.3) RAC
Data Guard : Oracle 11g R2 (11.2.0.3) 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.

failover_parameters

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

runfailover

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

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

6,977 total views, 15 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,631 total views, 5 views today

Tags: Linux Administration, Oracle Linux, System Administration


Next Page »