Josemar Furegatti de Abreu Silva

terça-feira, 3 de fevereiro de 2015

PostgreSQL for Oracle professional - Source code migration roadmap, command equivalence, comparison, differences, etc

1. Introduction

This post gathers a lot of tips, commands, equivalences, concepts and diferences between PostgreSQL vs Oracle. Itens covered:

Connection; disconnecting; help; listing databases; listing tables; create new databases; connect database; create tablespace; user vs schema; data type; ddl sintax; function sintax.; script file;
characterset; Trigger differences

2. PostgreSQL for Oracle professional

2.1. Connection command line mode, disconnecting and basic help

a) The equivalent to Oracle 'sqlplus' in 'postgre' is an application called 'psql'.

[root@srvpsql ~]# su - postgres

-bash-4.2$ psql

psql (9.3.5)

Type "help" for help.

postgres=#

b) The equivalent Oracle 'sqlplus exit' to exit command line mode use '\q', Unfortunatly 'exit' or 'quit' without '\' does not work ! Using terminator ';' doesn't help.

postgres=# exit

postgres-# exit;

ERROR: syntax error at or near "exit"

LINE 1: exit

postgres=# quit

postgres-# quit;

ERROR: syntax error at or near "quit"

LINE 1: quit

postgres=# \quit

-bash-4.2$

c) To connect using a specific user or to a specific database use command line qualifiers. Equivalent to Oracle 'sqlplus username/password@db_instance'

-bash-4.2$ psql --username=postgres

psql (9.3.5)

Type "help" for help.

postgres=# \quit

d) There are different types of help. You can ask for a basic help, or ask for a SQL help or ask for programming help, etc

postgres=# help

You are using psql, the command-line interface to PostgreSQL.

Type: \copyright for distribution terms

\h for help with SQL commands

\? for help with psql commands

\g or terminate with semicolon to execute query

\q to quit

2.2. Listing existing PostgreSQL databases and Listing existing tables for current database

a) To list existing database use command list database. The equivalent to Oracle 'select * from v$instance'

postgres-# \list

List of databases

-----------+----------+----------+-------------+-------------+-----------------------

(3 rows)

b) List database. The equivalent to Oracle 'select * from v$instance'

postgres=# SELECT datname FROM pg_database

postgres-# WHERE datistemplate = false;

datname

----------

postgres

(1 row)

c) List all tables for all database. The equivalent to Oracle 'select * from all_tables'

postgres=# SELECT table_schema,table_name

postgres-# FROM information_schema.tables

postgres-# ORDER BY table_schema,table_name;

table_schema | table_name

--------------------+---------------------------------------

information_schema | administrable_role_authorizations

information_schema | applicable_roles

information_schema | attributes

postgres-# SELECT * FROM pg_catalog.pg_tables ORDER BY table_schema,table_name;

postgres-# \dt * -- all tables for all owners

postgres-# \dt myowner -- all tables for owner myowner

c) Describe table structure

postgres-# \d+ information_schema.tables;

2.3. How to create new PostgreSQL databases

a) PostgreSQL database is quite different from Oracle database_instance. Considering data administration PostgreSQL Database is iquals to Oracle database instance.

postgres=# CREATE DATABASE prod;

CREATE DATABASE

2.4. How to connect specific database

a) The equivalent to Oracle 'sqlplus username/password@db_instance' is:

-bash-4.2$ psql -d prod

psql (9.3.5)

Type "help" for help.

prod=#

b) The equivalent to Oracle 'connect username/password@db_instance' is:

prod=# \connect prod

You are now connected to database "prod" as user "postgres".

2.5. Create tablespace

a) Creating tablespace needs physical directory previous creation

postgres=# select setting||'/base' from pg_settings where name='data_directory';
?column?
------------------------------
/var/lib/pgsql/9.3/data/base
(1 row)postgres=#
postgres=# show data_directory
data_directory
-------------------------
/var/lib/pgsql/9.3/data
(1 row)
postgres=# \q

-bash-4.2$

-bash-4.2$ mkdir /var/lib/pgsql/9.3/data/base/tbs_bi
-bash-4.2$ mkdir /var/lib/pgsql/9.3/data/base/tbs_sig
-bash-4.2$ mkdir /var/lib/pgsql/9.3/data/base/tbs_users
-bash-4.2$
-bash-4.2$ psql prod
psql (9.3.5)
Type "help" for help.
insig_prod=# CREATE TABLESPACE TBS_BI LOCATION '/var/lib/pgsql/9.3/data/base/tbs_bi';
CREATE TABLESPACE
-bash-4.2$

2.6. Create User Schema Owner and User Access

a) Oracle and PostgreSQL have different aproches to access users and owner users:

Oracle: 'user' can be used both to access and own schema.
PostgreSQL: Access user is 'user' and owner user is 'schema'

b) Create PostgreSQL 'schema' and 'user' with the same name

prod=# CREATE USER bi_owner WITH PASSWORD 'bi_owner';

prod=# CREATE SCHEMA bi AUTHORIZATION bi ;

2.7. Oracle vs PostgreSQL DataType equivalence

a) Here is roadmap DataType equivalence between Oracle and PostgreSQL

Oracle PostgreSQL Obs

=============== =============== ==============================================

NUMBER(n,m) NUMERIC(n,m)

VARCHAR2(n) VARCHAR(n)
DATE DATE (*) only DD/MM/YYYY
DATE TIMESTAMP (*) full date/time

2.8. Oracle vs PostgreSQL DDL Sintax and DataType equivalence

a) Create Index can *not* be prefixed by owner sintax

Oracle

CREATE INDEX OWNER.INDEX_NAME ON OWNER.TABLE_NAME( TABLE_COLUMN );

PostgreSQL

SQL> CREATE INDEX INDEX_NAME ON OWNER.TABLE_NAME( TABLE_COLUMN );

2.9. Oracle vs PostgreSQL FUNCTION SINTAX

a) Oracle implicit format does *not* works in PostgreSQL, you need to explicity de format in conversion TO_NUMBER( str [,fmt] )

Oracle:

SQL> SELECT TO_NUMBER(any_column) FROM ANY_TABLE;

PostgreSQL:

prod=# SELECT TO_NUMBER(any_column, '9') FROM ANY_TABLE;

b) Oracle DECODE( ) function does *not* works in PostgreSQL, you need to substitute DECODE( ) for CASE ... WHEN ... THEN ... ELSE ... END

Oracle:

SQL> SELECT DECODE( any_column,

1, 'one',

2, 'two',

'others'

)

FROM ANY_TABLE;

PostgreSQL:

prod=# SELECT CASE WHEN any_column = 1 THEN 'one'

WHEN any_column = 2 THEN 'two'

ELSE 'others'

END

FROM ANY_TABLE;

c) Oracle null value conversion NVL( ) does *not* works in PostgreSQL, you need to use COALESCE( expr#1, expr#2, ..., expr#n )

Oracle:

SQL> SELECT NVL(any_column, 0) FROM ANY_TABLE;

PostgreSQL:

prod=# SELECT COALESCE(any_column, 0) FROM ANY_TABLE;

d) Oracle SYSDATE built-in function does *not* works in PostgreSQL. The ANSI standard defines CURRENT_DATE or CURRENT_TIMESTAMP which is supported by Postgres and documented in the manual:

Oracle:

SQL> SELECT TRUNC(SYSDATE) FROM ANY_TABLE; -- 'dd/mm/yyyy'
SQL> SELECT SYSDATE FROM ANY_TABLE; -- 'dd/mm/yyyy hh24:mm:ssss'

PostgreSQL:

prod=# SELECT CURRENT_DATE FROM ANY_TABLE; -- 'dd/mm/yyyy'

prod=# SELECT CURRENT_TIMESTAMP FROM ANY_TABLE; -- 'dd/mm/yyyy hh24:mm:ssss'

e) Oracle DUAL pseudo table/view does not exists on PostgreSql. Oracle sintax requires ' from ...' to consider a valid statement. PostgreSQL does not need ' from ...', so you just use 'select ...' without from.

Oracle:

SQL> SELECT 'X' FROM DUAL;

PostgreSQL:

prod=# SELECT 'X';

f) Trunc date to first day of month

Oracle:

SQL> SELECT TRUNC(SYSDATE,'MM') FROM DUAL;

PostgreSQL:

prod=# SELECT DATE_TRUNC('month', CURRENT_DATE);

g) Add months to a date

Oracle:

SQL> SELECT ADD_MONTHS(SYSDATE,1) FROM DUAL;

PostgreSQL:

prod=# SELECT DATE_TRUNC('month', CURRENT_DATE) + interval '1 month';

2.10. Executing script file and spool output to a file

a) Spool sqlplus command line output to a file

Oracle: Run sqlplus executing a script file

# sqlplus username/password@db @path_to_script_filename.sql

Oracle: Use spool filename to start output capturing and spool off to stop.

SQL> spool filename
SQL> select * from dual;
DUMMY
-----
X
SQL> spool off

PostgreSQL: Executing a script from command line

-bash-4.2$ psql -U username -d myDataBase -a -f myInsertFile

PostgreSQL: Executing a script from command line

postgres=# \i path_to_sql_file

PostgreSQL: Using capture output to a file on command line

-bash-4.2$ psql -o filename -c 'select * from your_table_name;'

PostgreSQL: Starting capture output to a file on 'psql' command line with \o filename

-bash-4.2$ psql

postgres=# \o spool.txt

postgres=# select * from information_schema.tables;

postgres=# \o

PostgreSQL: Execute all statemments on 'script.sql' and capture output to 'script.log'. Use '-a' or '-echo--all' to echo command

-bash-4.2$ psql -o script.log -f script.sql -a

2.11. SQL*PLUS Prompt, Echo, Host equivalents

a) Oracle's equivalents functionality prompt, echo and host

Oracle:

SQL> prompt 'Hello world'
SQL> host 'ls -la'
SQL> set echo on
SQL> set feedback on

PostgreSQL:

prod=# \echo 'Hello world'
prod=# \echo `ls -la`

2.12. Usefull Script to convert most common Oracle 'sqlplus' sintax into PostgreSQL 'Psql' sintax

This Linux command line converts common Sql*Plus sintax into Psql

postgres=# PG_OWNER_SCHEMA=myschemaowner
for FILE_SQL in *.sql
do
#
echo -n "- $FILE_SQL "
FILE_TMP=$FILE_SQL.tmp
FILE_PSQL=$FILE_SQL.psql
rm -f $FILE_PSQL
cp $FILE_SQL $FILE_PSQL
#
# Step#1: prompt, feedback, echo, define "
echo -n "."
rm -f $FILE_TMP
cp $FILE_PSQL $FILE_TMP
sed -e 's/prompt /\\echo /g' -e 's/set feedback on/\\echo /g' -e 's/set define on/\\echo /g' $FILE_TMP > $FILE_PSQL
#
# Step#2: ("CREATE TABLE ", "ALTER TABLE TABLE_NAME") vs ( <PG_OWNER_SCHEMA>.TABLE_NAME )
echo -n "."
rm -f $FILE_TMP
cp $FILE_PSQL $FILE_TMP
sed -e 's/CREATE TABLE /CREATE TABLE '$PG_OWNER_SCHEMA'./g' -e 's/ALTER TABLE /ALTER TABLE '$PG_OWNER_SCHEMA'./g' $FILE_TMP > $FILE_PSQL
#
# Step#3: ("NUMBER", "VARCHAR2") vs ("NUMERIC", "VARCHAR")
echo -n "."
rm -f $FILE_TMP
cp $FILE_PSQL $FILE_TMP
sed -e 's/ NUMBER/ NUMERIC/g' -e 's/ VARCHAR2/ VARCHAR/g' $FILE_TMP > $FILE_PSQL
#
# Step#4: ("ORACLE_OWNER") vs ("POSTGRESQL_OWNER")
echo -n "."
rm -f $FILE_TMP
cp $FILE_PSQL $FILE_TMP
sed -e 's/ INSIG_OWNER./ insig./g' -e 's/ INBI2./ inbi./g' -e 's/ RM_INTEG./ rm_integ./g' -e 's/ MANAGER./ manager./g' $FILE_TMP > $FILE_PSQL
#
# Step#5: ("TRUNC(SYSDATE)","SYSDATE") vs ("CURRENT_DATE","CURRENT_TIMESTAMP")
echo -n "."
rm -f $FILE_TMP
cp $FILE_PSQL $FILE_TMP
sed -e 's/TRUNC(SYSDATE)/CURRENT_DATE/g' -e 's/SYSDATE/CURRENT_TIMESTAMP/g' $FILE_TMP > $FILE_PSQL
#
# Step#6: ("NVL()") vs ("COALESCE")
echo -n "."
rm -f $FILE_TMP
cp $FILE_PSQL $FILE_TMP
sed -e 's/NVL/COALESCE/g' $FILE_TMP > $FILE_PSQL
#
# Step#7: ("insert into ") vs ("insert into $PG_OWNER_SCHEMA")
echo -n "."
rm -f $FILE_TMP
cp $FILE_PSQL $FILE_TMP
sed -e 's/insert into /insert into '$PG_OWNER_SCHEMA'./g' $FILE_TMP > $FILE_PSQL
#
# Step#8: Convert CharSet -f ISO-8859-15 -t UTF-8
echo -n "."
rm -f $FILE_TMP
cp $FILE_PSQL $FILE_TMP
iconv -f ISO-8859-15 -t UTF-8 $FILE_TMP > $FILE_PSQL
#
# Step#n: Clean temporary file ...
# Remove '.tmp'
rm -f $FILE_TMP
echo ""
done

postgres=#

2.13. Solving script characterset problem

If you have exported your data into script file (full of insert values), maybe you can face a "character set" problem. Source script data should be generated in a character set compatible to target PostgreSQL database, otherwise you will get a PostgreSQL error. Example of problem:

postgres=# insert into DIM_TAREFA (ID_TAREFA, ID_PROJETO, COD_TAREFA, TAREFA, TAREFA_PAI) values (13834, 5559, '01.13', 'PU75621 - Novo ODS Auditor Pacote 2A ¿Planejament', null);
psql:deploy_09_a_insert_dim.tmp:19797: ERROR: invalid byte sequence for encoding "UTF8": 0xbf

To Convert the source script file to a "character set" compatible to target PostgreSql, using a Linux System, you can follow these steps:

Identify source script file type:

-bash-4.2$ file deploy_09_a_insert_dim.sql
deploy_09_a_insert_dim.sql: ISO-8859 text, with CRLF line terminators

Identify target PostgreSQL database character type:

Convert source script "character set" to target compatible:

-bash-4.2$ iconv -f ISO-8859-15 -t UTF-8 deploy_09_a_insert_dim.sql > deploy_09_a_insert_dim.psql.utf-8

2.14. Trigger differences

Here is the roadmap to convert Oracle trigger to PostgreSQL:

You need to create a PostgreSQL FUNCTION with return type TRIGGER and create a TRIGGER associating event to this function
Oracle reserved keywords ( UPDATING or DELETING or INSERTING ) must be converted to ( TG_OP = 'UPDATE' or TG_OP = 'DELETE' or TG_OP = 'INSERT' );
Oracle reserved keywords to reference line rows before and after changes ( ":NEW." and ":OLD." ) must be converted to ( "NEW." and "OLD" without semicolumn ":" )
PostgreSQL sintax requires language definition after end code. Ex: LANGUAGE plpgsql ;
PostgreSQL sintax requires a start and finish $mark$ for the body of procedure.
References#1: http://www.postgresql.org/docs/9.2/static/plpgsql-trigger.html
References#2: http://www.postgresqltutorial.com/creating-first-trigger-postgresql/
References#3: http://www.postgresql.org/docs/9.2/static/plpgsql-trigger.html

CREATE OR REPLACE FUNCTION fn_tr_b_er_iud_perfilusuario() RETURNS TRIGGER
AS
$sig_perfil_usuario$
DECLARE
vInfoAudit varchar(2000) := '';
BEGIN
RAISE debug '>>> tr_b_er_iud_perfilusuario >>>';
vInfoAudit := '';
IF ( TG_OP = 'UPDATE' OR TG_OP = 'INSERT') THEN
NEW.dthr_audit := current_timestamp;
END IF;
IF ( TG_OP = 'UPDATE' ) THEN
IF COALESCE(OLD.id_perfil,-1) <> COALESCE(NEW.id_perfil,-1) THEN
vInfoAudit := vInfoAudit || SUBSTR
(
'id_perfil(De: "' || TO_CHAR(OLD.id_perfil,'999999999999') || '", Para: "' || TO_CHAR(NEW.id_perfil,'999999999999') || '"); ',
1, 2000
);
END IF;
ELSIF ( TG_OP = 'DELETE' ) THEN
vInfoAudit := SUBSTR
(
SUBSTR( 'id_perfil(De: "' || TO_CHAR(OLD.id_perfil,'999999999999') || '"); ', 1, 2000) ||
SUBSTR( 'id_menu(De: "' || TO_CHAR(OLD.id_usuario,'999999999999') || '"); ', 1, 2000 ) ||
'', 2000
);
END IF;
--
-- Audit ...
--
IF vInfoAudit IS NOT NULL THEN
INSERT INTO insig.SIG_AUDIT
(
id_audit, dthr_audit, object_audit, event_audit, info_audit, id_object_ref, id_usuario_audit
)
VALUES
(
nextval('insig.SSIG_AUDIT'), -- id_audit
CURRENT_TIMESTAMP, -- dthr_audit
'SIG_PERFIL_USUARIO', -- object_audit
TG_OP, -- event_audit
vInfoAudit, -- info_audit
COALESCE(NEW.id_perfil_usuario,OLD.id_perfil_usuario), -- id_object_ref
COALESCE(NEW.id_usuario_audit,OLD.id_usuario_audit) -- id_usuario_audit
);
END IF;
RAISE debug '<<< tr_b_er_iud_perfilusuario <<<';
RETURN NEW;
END;
$sig_perfil_usuario$
LANGUAGE plpgsql ;
--
DROP TRIGGER tr_b_er_iud_perfilusuario ON insig.sig_perfil_usuario;
--
CREATE TRIGGER tr_b_er_iud_perfilusuario
BEFORE INSERT OR UPDATE OR DELETE
ON insig.sig_perfil_usuario
FOR EACH ROW
EXECUTE PROCEDURE fn_tr_b_er_iud_perfilusuario();

2.16. Debug messages inside trigger and procedure equivalent to Oracle DBMS_OUTPUT.put_line

Oracle

:
dbms_output.put_line( 'I have been here. The id value is ' || :new.id );
:

PostgreSQL

:
RAISE debug 'I have been here. The id value is ' || NEW.id );
:

2.17. Sequence equivalent to Oracle sequence_name.nextval is nextval('sequence_name') on PostgreSql

Oracle:

:
select sequence_name.nextval from dual;
:

PostgreSQL:

:
select nextval('schema.sequence_name')
:

2.18. Handling NO_DATA_FOUND differences between Oracle vs PostgreSql

PostgreSQL SELECT does not raise NO_DATA_FOUND exception unless you add qualifier "STRICT" on "INTO" clause.

Oracle:

DECLARE
vTableName VARCHAR2(30);
BEGIN
DBMS_OUTPUT.PUT_LINE( 'select ...' );
BEGIN
--
SELECT TABLE_NAME
INTO vTableName
FROM USER_TABLES
WHERE 0 =1;
--
DBMS_OUTPUT.PUT_LINE( '... DATA FOUND !' );
--
EXCEPTION
WHEN NO_DATA_FOUND THEN
DBMS_OUTPUT.PUT_LINE( '... NO DATA FOUND!' );
END;
END;

PostgreSQL:

DO
$$
DECLARE
vTableName VARCHAR(30);
BEGIN
RAISE info 'select ... (#1)' ;
BEGIN
--
SELECT tablename
INTO vTableName
FROM pg_tables
WHERE 0 =1;
--
RAISE info '... DATA FOUND ! (#1)' ;
--
EXCEPTION
WHEN NO_DATA_FOUND THEN
RAISE info '... NO DATA FOUND!(#1)' ;
END;
--
RAISE info 'select ... (#2)' ;
BEGIN
--
SELECT tablename
INTO STRICT vTableName
FROM pg_tables
WHERE 0 = 1;
--
RAISE info '... DATA FOUND ! (#2)' ;
--
EXCEPTION
WHEN NO_DATA_FOUND THEN
RAISE info '... NO DATA FOUND!(#2)' ;
END;
END;
$$

;
INFO: select ... (#1)
INFO: ... DATA FOUND ! (#1)
INFO: select ... (#2)
INFO: ... NO DATA FOUND!(#2)
DO

3. References

quarta-feira, 22 de outubro de 2014

Install Zabbix on Centos-6.4

1. Introduction

This post shows how to install zabbix into CentOs 6.4

2. Step by Step

Step #1 - Update SO packages

# mkdir /tmp/install
# cd /tmp/install/
# wget http://epel.gtdinternet.com/6/i386/epel-release-6-7.noarch.rpm
# rpm -ivh epel-release-6-7.noarch.rpm
# yum -y update

Step #2 - Install Packages dependencies

# yum -y install httpd php php-common php-mysql php-gd php-bcmath php-mbstring php-xml mysql mysql-server mysql-devel net-snmp net-snmp-devel net-snmp-utils net-snmp-libs gcc gcc-devel gcc-devel curl curl-devel

Step #3 - Download Zabbix binnaries

# mkdir /tmp/install
# cd /tmp/install/
# wget http://sourceforge.net/projects/zabbix/files/ZABBIX%20Latest%20Stable/2.4.1/zabbix-2.4.1.tar.gz

Step #4 - Create Zabbix User

# adduser zabbix -s /bin/false

Step #5 - Extract Zabbix source

# tar xzvf zabbix-*.tar.gz
# cd zabbix-*

Step #6 - Start MySQL and create Zabbix database

# service mysqld start
# chkconfig mysqld on
# mysql -u root
mysql> create database zabbix character set utf8;
mysql> grant all privileges on zabbix.* to 'zabbix'@'localhost' identified by 'zabbix';
mysql> exit

# mysql -u root zabbix < database/mysql/schema.sql
# mysql -u root zabbix < database/mysql/images.sql
# mysql -u root zabbix < database/mysql/data.sql

Step #7 - Configure, Compile and Make

# ./configure --enable-server --with-mysql --with-net-snmp --with-libcurl --enable-agent

# make install

Step #8 - Configure Zabbix Server - /etc/zabbix

# vi /usr/local/etc/zabbix_server.conf
:
DBHost=localhost
DBName=zabbix
DBUser=zabbix
:
LogFileSize=10

Step #9 - Configure Zabbix Agent - /usr/local/etc/zabbix_agentd.conf

# vi /usr/local/etc/zabbix_agentd.conf
:
Server=127.0.0.1
:

Step #10 - Disable SELinux configuration

# vim /etc/selinux/config
:
SELINUX=disabled
:

Step #11 - Disable IPTables or Configure IPTables for zabbix

# iptables -nvL --line-numbers
Chain INPUT (policy ACCEPT 0 packets, 0 bytes)
num pkts bytes target prot opt in out source destination
1 588 39946 ACCEPT all -- * * 0.0.0.0/0 0.0.0.0/0 state RELATED,ESTABLISHED
2 0 0 ACCEPT icmp -- * * 0.0.0.0/0 0.0.0.0/0
3 56 3922 ACCEPT all -- lo * 0.0.0.0/0 0.0.0.0/0
4 0 0 ACCEPT tcp -- * * 0.0.0.0/0 0.0.0.0/0 state NEW tcp dpt:22
5 355 51380 REJECT all -- * * 0.0.0.0/0 0.0.0.0/0 reject-with icmp-host-prohibited

Chain FORWARD (policy ACCEPT 0 packets, 0 bytes)
num pkts bytes target prot opt in out source destination
1 0 0 REJECT all -- * * 0.0.0.0/0 0.0.0.0/0 reject-with icmp-host-prohibited

Chain OUTPUT (policy ACCEPT 462 packets, 92468 bytes)
num pkts bytes target prot opt in out source destination
[root@oracle-linux64 ~]#

As we can see, there is a INPUT rule #1 that accept all sources/destination but only in some states. And ther is a INPUT rule #5 that reject everything. So, we have to disable firewall or add iptable rule to accept Oracle port 1521.

Option #1: To disable Iptable do this:

# service iptables stop

iptables: Flushing firewall rules: [ OK ]

iptables: Setting chains to policy ACCEPT: filter [ OK ]

iptables: Unloading modules: [ OK ]

# chkconfig iptables off

Option #2: To keep Iptables running and add a new rule to accept Zabbix and HTTP ports ( 80, 10050, 10051) do this:

# iptables -I INPUT 1 -p tcp --dport 80 -j ACCEPT

# iptables -I INPUT 2 -p tcp --dport 10050 -j ACCEPT

# iptables -I INPUT 3 -p tcp --dport 10051 -j ACCEPT

# service iptables save

iptables: Saving firewall rules to /etc/sysconfig/iptables:[ OK ]

# service iptables restart

iptables: Flushing firewall rules: [ OK ]

iptables: Setting chains to policy ACCEPT: filter [ OK ]

iptables: Unloading modules: [ OK ]

iptables: Applying firewall rules: [ OK ]

Step #12 - Start Zabbix Service, check process

# echo
# echo 'Starting Zabbix (server and agent) ...'
# echo
# zabbix_server
# zabbix_agentd

# ps aux | grep zabbix
:
zabbix 26603 0.0 0.2 131636 2484 ? S 11:08 0:00 zabbix_server
zabbix 26607 0.0 0.1 131636 1960 ? S 11:08 0:00 zabbix_server: configuration syncer [synced configuration in 0.008139 sec, idle 60 sec]
zabbix 26608 0.0 0.1 131636 1556 ? S 11:08 0:00 zabbix_server: db watchdog [synced alerts config in 0.001322 sec, idle 60 sec]
zabbix 26609 0.0 0.3 229520 3208 ? S 11:08 0:00 zabbix_server: poller #1 [got 0 values in 0.000006 sec, idle 5 sec]
:

# echo

# echo 'Adding Zabbix (server and agent) start running during boot ...'

# echo

# vi /etc/rc.d/rc.local

/usr/local/sbin/zabbix_server
/usr/local/sbin/zabbix_agentd

Step #13 - Install and configure Zabbix Web Interface (php)

# cp -r frontends/php/* /var/www/html/zabbix/
# chown -R apache:apache /var/www/html/zabbix/
# vi /etc/php.ini
:
max_execution_time = 300
max_input_time = 300
date.timezone = "America/Sao_Paulo"
post_max_size = 16M
:
# echo
# echo 'Restarting HTTP Server ...'
#
# service httpd restart

Step #14 - Complete Web Installation in your browser

http://<zabbix-server-ip-address>/zabbix
Press "next" to see "2. Check of pre-requisites"
Press "next" to see "3. Configure DB Connection"
Configure Database Name "zabbix", User "zabbix" and Password "zabbix"
Press "Test" to Test connection and wait for message "Ok"
Press "Next" to "4. Zabbix server details"
Configure Name "Zabbix Server"
Press "Next" to "5. Pre-Installation summary"
Press "Next" to "6. Install". If you get an error message like "Configuration file "/var/www/html/zabbix/conf/zabbix.conf.php" create fail. Check permission and ownership on path "/var/www/html/zabbix/". Change ownership to "apache" and change permission to read, write and execute
Press "Finish" to complete installation
Sign in using username "admin" and password "zabbix"

Step #15 - Enabling Zabbix Server self monitoring

On Zabbix Menu :: "Configuration >> Host": click on hiperlink "not monitored" to change monitoring status. Confirm enable operation

3. References

http://zabbixbrasil.org/wiki/tiki-index.php?page=Instalacao+do+Zabbix+2.x+em+Ambiente+CentOS

quarta-feira, 15 de outubro de 2014

Internal and Performance Tuning from IBM JVM5.0

1. Introduction

This post gathers information about Internal and Performance Tuning from IBM JVM 5.0

2. References

Enhancements to Java 5.0 http://www.ibm.com/developerworks/java/library/j-ibmjava1/
Garbage collection polices (Part 1) http://www.ibm.com/developerworks/java/library/j-ibmjava2/
Garbage collection polices (Part 2) http://www.ibm.com/developerworks/java/library/j-ibmjava3/

terça-feira, 9 de setembro de 2014

Installing NodeJS, PhoneGap, Ant, Android SDK on Windows 8

1. Introduction

This post gathers information about and show steps for installation of phonegap

2. Step-by-Step

Step#1: Install NodeJS

- http://nodejs.org/
- Execute Installer of NodeJS

Step#2: Install PhoneGap

// - Windows >> Search :: Search for "node.js command prompt" and execute
- C:\> npm install -g phonegap
- C:\> npm install -g cordova
- Unzip PhoneGap into C:\Program Files\
+ Set Windows Environment Variables
- PATH=%PATH%;C:\Program Files\phonegap-2.9.1\lib\android\bin

Step#3: Install Apache Ant for Windows

- Download binary from http://ant.apache.org/bindownload.cgi
- Execute installer
+ Set Windows Environment Variables
- ANT_HOME=C:\PROGRA~1\APACHE~1.4
- PATH=%PATH%;%ANT_HOME%\bin

Step#4: Install JAVA JDK

+ Set Windows Environment Variables
- JAVA_HOME=C:\Program Files\Java\jdk1.7.0_60
- PATH=%PATH%;%JAVA_HOME%\bin

Step#5: Install ADK (Android Development Kit) and Adding SDK Packages

- Download http://developer.android.com/sdk/index.html
+ Set Windows Environment Variables
- ANDROID_HOME=C:\Program Files\adt-bundle-windows-x86_64\sdk
- PATH=%PATH%;%ANDROID_HOME%\platform-tools;C:\Program Files\adt-bundle-windows-x86_64\sdk\tools

+ Adding SDK Packages
- Read http://developer.android.com/sdk/installing/adding-packages.html
- Run %ANDROID_HOME%\eclipse\eclipse or %ANDROID_HOME%\SDK Manager
+ In Eclipse or Android Studio, click SDK Manager
+ Get the latest SDK tools
- [X] Android SDK Tools
  - [X] Android SDK Platform-tools
  - [X] Android SDK Build-tools (highest version)
  - [X] SDK Platform
  - [X] ARM EABI v7a System Image
+ Get the support library for additional APIs
  - [X] Android Support Repository
  - [X] Android Support Library
+ Get Google Play services for even more APIs
- [X] Google Repository
- [X] Google Play services
+ Install the packages

Step#6: To solve "The Import android.support.v7 cannot be resolved" do

Click add external jars "%ANDROID_HOME%/sdk/extras/android/support/v7/appcompat/libs/android-support-v7-appcompat.jar" to Java Build Path

Step#7: Run Eclipse ditributed with Install ADK - Android SDK for Windows

C:\..\> cd "%HOMEPATH%\Documents\Evaluation\PhoneGap"
C:\..\>REM create <sub-directory> <package-name> <app-name>
C:\..\> create hello br.com.josemar.hello HelloWorld

- C:\> C:\Program Files\adt-bundle-windows-x86_64-20140702\eclipse\eclipse
- Enter new path for ADK projects Ex: %HOMEPATH%\workspace-adt-bundle-windows
+ Create New Project:
+ Eclipse ADK :: File >> New >> Project
- Wizard :: Android Project from Existing Code
- Import Projects :: [Browse...] Root Directory: C:\Users\Josemarsilva\Documents\Evaluation\Phone Gap\projeto

3. References

How To Add Disk to Existing Linux VM on Hyper-v and Create New Volume Group

1. Introduction

This post shows screens captured step-by-step to add new disk to existing Linux VM on Hyper-v, create a new volume called "/u01" to oracle.

2. Step-by-Step

This step-by-step is divided into 3 parts:

Part-I: Configure Hyper-v virtual disk and attach to Linux VM
Part-II: Configure additional disk as new Logical Volume (LVM)
Part-III: Configure additional disk as new mount point

In this case we suppose that we need to add a new volume group or mount point /u01 for Oracle Database Installer.

2.1. Part-I: Configure Hyper-v virtual disk and attach to Linux VM

Edit Hyper-v VM

Configure VM Disk as follows:

Start Hyper-v VM:

Loggin on Linux VM and find out device added:

Create new disk partition for new device added using FDISK:

2.2. Part-II: Configure additional disk as new Logical Volume (LVM)

Create Physical Volume for disk partition /dev/sdb2

Create Volume Group for disk partition /dev/sdb2

Create Logical Volume:

Create and format FileSystem for Logical Volume

Create mount point "/u01" and mount disk manually (only testing)

Identify blockid to configure fstab to automatically mount point "/u01" on next boot:

2.3. Part-III: Configure additional disk as new mount point

3. References

segunda-feira, 8 de setembro de 2014

Oracle Data Pump Export / Import Examples

1. Introduction

This post show step-by-step how to configure DataPump Directory, how to Export and how to Import using.

1.1. Pre-requisites

Oracle Data Pump tool requires a directory configured on database. Example

2. Examples

2.1. Create Directory for DataPump Export/Import and adjust permission

SQL> CREATE DIRECTORY DPUMP_BACKUP AS '/u01/backup/dpump';

Directory created.

SQL> GRANT READ, WRITE ON DIRECTORY DPUMP_BACKUP TO SYSTEM, SYS;

Grant succeeded.

2.2. Create Physical DataPump Directory

# mkdir /u01/backup
# mkdir /u01/backup/dpump
# chown -R oracle:dba /u01/backup/

2.3. Example #1: Export DataPump Full Database mode

$ expdp "'/ AS SYSDBA'" DIRECTORY=DPUMP_BACKUP DUMPFILE=expdp_FULL.dmp LOGFILE=expdp_FULL.log FULL=Y REUSE_DUMPFILES=Y DATA_OPTIONS=XML_CLOBS

Export: Release 11.2.0.2.0 - Production on Fri Sep 5 23:27:57 2014

Connected to: Oracle Database 11g Express Edition Release 11.2.0.2.0 - 64bit Production

Starting "SYS"."SYS_EXPORT_FULL_01": "/******** AS SYSDBA" DIRECTORY=DPUMP_BACKUP DUMPFILE=expdp_FULL.dmp LOGFILE=expdp_FULL.log FULL=Y REUSE_DUMPFILES=Y DATA_OPTIONS=XML_CLOBS

Estimate in progress using BLOCKS method...

Processing object type DATABASE_EXPORT/SCHEMA/TABLE/TABLE_DATA

Total estimation using BLOCKS method: 164.3 MB

Processing object type DATABASE_EXPORT/TABLESPACE

. . exported "SYSTEM"."SQLPLUS_PRODUCT_PROFILE" 0 KB 0 rows

Master table "SYS"."SYS_EXPORT_FULL_01" successfully loaded/unloaded

******************************************************************************

Dump file set for SYS.SYS_EXPORT_FULL_01 is:

/u01/backup/dpump/expdp_FULL.dmp

Job "SYS"."SYS_EXPORT_FULL_01" successfully completed at 23:29:28

2.4. Example #2: Export DataPump some Schemas Database mode

$ expdp "'/ AS SYSDBA'" DIRECTORY=DPUMP_BACKUP DUMPFILE=expdp_FULL.dmp LOGFILE=expdp_FULL.log SCHEMAS=RM,SYSDBA,RM_INTEG,MANAGER,MANAGER_WEBAPP,INBI,INBI2,CONFLUENCE,CONFLUENCE_GTW,TRACKPLUS,FIRESCRUM,PYTHON_APP,SURVEY_APP,PRJBUILDER,PROJECTBUILDER,PBOWNER REUSE_DUMPFILES=Y DATA_OPTIONS=XML_CLOBS

Export: Release 11.2.0.3.0 - Production on Thu Sep 18 14:14:58 2014

Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Starting "SYS"."SYS_EXPORT_SCHEMA_01": "/******** AS SYSDBA" DIRECTORY=DPUMP_BACKUP DUMPFILE=expdp_full.dmp LOGFILE=expdp_full.log SCHEMAS=RM,SYSDBA,RM_INTEG,MANAGER,MANAGER_WEBAPP,INBI,INBI2,CONFLUENCE,CONFLUENCE_GTW,TRACKPLUS,ALEXJUNQ,FIRESCRUM,PYTHON_APP,SURVEY_APP,PRJBUILDER,PROJECTBUILDER,PBOWNER REUSE_DUMPFILES=Y
Estimate in progress using BLOCKS method...
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 12.51 GB

Processing object type SCHEMA_EXPORT/USER

Master table "SYS"."SYS_EXPORT_SCHEMA_01" successfully loaded/unloaded
******************************************************************************
Dump file set for SYS.SYS_EXPORT_SCHEMA_01 is:
/u01/backup/dpump/expdp_full.dmp
Job "SYS"."SYS_EXPORT_SCHEMA_01" successfully completed at 14:32:40