Friday, November 2, 2012

Export CSV directly from MySQL

How ofter were you asked by a client for a CSV (or excel) file with data from their app? I get asked that question quite often, so I wanted make the process as easy as possible. And guess what? You can create CSV files directly from MySQL with just one query!
Let's say you want to export the id, name and email fields from your users table to a CSV file. Here is your code:
SELECT id, name, email INTO OUTFILE '/tmp/result.csv'
FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
ESCAPED BY ‘\\’
LINES TERMINATED BY '\n'
FROM users WHERE 1
Well, if you know MySQL, you'll know how to customize this query to spit out the the right data. Your csv file can be found in /tmp/result.csv
Make sure your MySQL server has write permissions to the location where you want to store your results file.

Monday, October 29, 2012

Installation of Oracle 11g R1 (11.1.0.6.0) on SuSE Linux Enteprise Server (SLES) 10 and OpenSuSE 10.3

This paper (HOWTO) describes step-by-step installation of Oracle 11g R1 database software on SuSE Linux Enteprise Server (SLES) 10 and OpenSuSE 10.3 (currently for x86_64. x86 version will come soon). Note that OpenSUSE distribution is not certified by Oracle Corporation.
This article does not cover database creation process, and ASM Instance creation process.

This paper covers following steps:

Thursday, September 20, 2012

Dhcp client stopped at startup

1- first you have to login using any user with administrative
privileges.
2- start---> Run---> regedit
3- go to HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Services\Tcpip.
4- right-click on the Tcpip from the left tap menu and choose
permission.
5- Choose (Allow) for all listed groups and users, then press apply and
ok.
6- go to HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Services\Dhcp.
7- do the same as before.
8- now plug your network cable or connect to the wireless, if u got an
IP from the DHCP then it's done, if not you have to open the services
from the administrative tools to run the DHCP clinet or stop and run the
service again.

*** YOU HAVE TO DO THIS EVERYTIME U LOG IN OR CONNECT TO THE
NETWORK.
*** THIS PROBLEM ASLO EXIST WITH WINDOWS 7 ULTIMATE.

This problem might be related to the DHCP server, seems like up-grading
from windows 2000 advanced server to windows 2003 may cause this probem,
or maybe the group policy in the active directory.


Or try this *netsh winsock reset catalog *

Creating local yum repository on RHEL 6.2

1. Mount the DVD or ISO
#mount -t cifs -o username=user,password=pass,domain=abc.com //servername/share /mnt
#mount /dev/cdrom /mnt
#mount -o ro,loop redhat.iso /mnt

2. Copy files
#mkdir -p /var/repo/rhel6
#cp -r /mnt/* /var/repo/rhel6/
#cd /var/repo/rhel6/Packages
#rpm -i createrepo...
#cd /var/repo
#createrepo .

3. Create file local.repo in /etc/yum.repos.d/
#cd  /etc/yum.repos.d/
#touch local.repo

[local]
enabled=1
gpgcheck=0
name=Red Hat Linux $releasever - $basearch - DVD
baseurl=file:///var/repo


Save and exit
Try to install a package:
#yum install httpd

Device eth0 does not seem to be present, delaying initialization

PROBLEM
You receive random errors failing to initialize your network card at boot time or during manual restart that look like the one below:
$ifup eth0
via-rhine device eth0 does not seem to be present, delaying initialization.
$
SOLUTION
There are a couple of potential solutions to this issue:
  • Missing 'HWADDR' variable from '/etc/sysconfig/network-scripts/ifcfg-eth0'.  Try adding "HWADDR=01:23:45:67:89:AB" to '/etc/sysconfig/network-scripts/ifcfg-eth0' and rerunning 'ifup eth0' command.
     
  • You could try renaming the interface you have to the one the system is currently configured for and regunning service network restart.  An example of renaming a wireless LAN card is below: $ cd /sys/class/net
    $ ll
    lrwxrwxrwx 1 root root 0 Jun 14 06:17 wlan1 -> ../../devices/pci0000:00/0000:00:1d.7/usb1/1-2/1-2:1.0/net/wlan1
    $ ip link set dev wlan1 name wlan0
    $ ll
    lrwxrwxrwx 1 root root 0 Jun 14 06:17 wlan1 -> ../../devices/pci0000:00/0000:00:1d.7/usb1/1-2/1-2:1.0/net/wlan1
To make the above change persistent across reboots, please see the following post Linux Networking: Persistent naming rules based on MAC for eth0 and wlan0.


Monday, August 27, 2012

ORA-01034: ORACLE not available ORA-27101: shared memory realm does not exist Linux-x86_64 Error: 2: No such file or directory

I get the following issue when connecting to Oracle DB via SID:

$sqlplus myusername/mypassword@sid
SQL*Plus: Release 11.2.0.3.0 Production on Wed Aug 22 00:35:56 2012
Copyright (c) 1982, 2011, Oracle. All rights reserved.

ERROR:
ORA-01034: ORACLE not available
ORA-27101: shared memory realm does not exist
Linux-x86_64 Error: 2: No such file or directory
Process ID: 0
Session ID: 0 Serial number: 0


This server is running Oracle 11.2.0.3 on Redhat 6 64bit.

I made a change in /opt/oracle/112/network/admin/tnsnames.ora

# tnsnames.ora Network Configuration File: /opt/oracle/112/network/admin/tnsnames.ora
# Generated by Oracle configuration tools.

PSDS =
    (DESCRIPTION =
        (ADDRESS = (PROTOCOL = TCP)(HOST = oracle.abc.com)(PORT = 1521))
        (CONNECT_DATA =
            (SERVER = DEDICATED)
            #(SERVICE_NAME = sid)
            (SID=sid)
        )
    )


Then it worked!

Wednesday, August 1, 2012

How to Install Munin on CentOS


Munin is a monitoring tool for servers. It uses RRDtool to log and graph data from your servers. The plugin API is very easy to grasp. Actually, I haven’t read the API documentation yet. I just looked at the output of the plugins and it looks easy to achieve. The data can be accessed through the web.

This guide will walk you through installing and configuring Munin on CEntOS 4.3/4.4 x86. The steps are pretty much the same for later releases of CEntOS, Red Hat, Fedora [Core] and/or Red Hat-based installations.

Munin works by polling your servers for the data hence two applications, Munin and Munin Node. The former periodically gathers data (cronned) and the latter serves the data to the former. Please refer to the following for our example configuration. You can make up a domain if you want Munin to group your servers similar to the live demo.

Munin “Graph Server” – alpha.sample.net (192.168.1.1)
A Munin Node – bravo.sample.net (192.168.1.2)

Thursday, July 26, 2012

Memcache vs APC cache in PHP

I often see this question asked; which is a better caching mechanism for PHP: Memcache or APC cache. I wanted to write about how different both of them are inherently in their very concept.

Memcache for PHP is a distributed caching mechanism. If you have several webservers running under a load balancer serving the same content and you want a caching mechanism to avoid frequent database hits , Memcache is the way to go. When using Memcache you will make the update to one webserver and it would be auto-replicated across all the severs through distributed caching mechanism. Since it requires dealing with network protocols in order to support the distributed part of caching, it is slower compared to APC cache. If data is stored in APC cache, updates need to be done individually on all APC caches on all web servers. They wont be automatically replicated.

However, Memcached is NOT an Opcode cacher like APC. APC when employed will cache all the opcode the first time it is converted and serve the cached version for subsequent HTTP requests. APC can also be used to store data like Memcache but in a non-distributed manner. Most of the times memcache is used to store results of time consuming data queries, so the need to hit database on every query is eliminated and this gives a huge performance benefit.

The good part of both these technologies, is that they are compatible with each other. A good design for scalable websites should be employing APC for opcode caching and data-caching through Memcache to exploit the distributed capabilities across several webservers. If there is just a single webserver, using just APC cache for both opcode and data caching is a good idea.



http://www.4elements.com/blog/2011/07/

Tuesday, July 24, 2012

Convert MyISAM to InnoDB

This shows how to convert MyISAM to InnoDB engine from command line:


#mysql -p -e "show tables in <database_name>;" | tail --lines=+2 | xargs -i echo "ALTER TABLE {} ENGINE=INNODB;" > alter_table.sql


#mysql --database=<database_name> -p < alter_table.sql

Enable InnoDB in MySQL

For time to time searching in the internet, I finally found a solution for enabling InnoDB in MySQL.
First of all, check your MySQL database if it supports InnoDB or not:


#mysqladmin -u root -p variables |grep have_innodb
Enter password:
| have_innodb                             | NO


Try one more time:


#mysql -u root -p
mysql>show engines\g;


+------------+---------+------------------------------------------------------------+--------------+------+------------+
| Engine     | Support | Comment                                                    | Transactions | XA   | Savepoints |
+------------+---------+------------------------------------------------------------+--------------+------+------------+
| MRG_MYISAM | YES     | Collection of identical MyISAM tables                      | NO           | NO   | NO         |
| CSV        | YES     | CSV storage engine                                         | NO           | NO   | NO         |
| MyISAM     | DEFAULT | Default engine as of MySQL 3.23 with great performance     | NO           | NO   | NO         |
| MEMORY     | YES     | Hash based, stored in memory, useful for temporary tables  | NO           | NO   | NO         |
+------------+---------+------------------------------------------------------------+--------------+------+------------+
4 rows in set (0.00 sec)



Friday, July 13, 2012

Install RADIUS plugin for PHP on CentOS 6.3

To install RADIUS plugin for PHP on CentOS 6.3, do the following:

#pear install radius Auth_RADIUS

If you get this:
"No releases available for package "pear.php.net/radius"
"No releases available for package "pear.php.net/Auth_RADIUS"

Please update pear channel:

#pear channel-update pear.php.net
Updating channel "pear.php.net"
Update of Channel "pear.php.net" succeeded


Run the command again
# pear install radius Auth_RADIUS



Tuesday, July 10, 2012

Export Import Oracle database

http://www.orafaq.com/wiki/Import_Export_FAQ



Oracle Export and Import Utilities FAQ:
NOTE: Users on Oracle 10g and later releases should use the Data Pump expdp and impdp utilities instead of the older imp and exp utilities described in this document.
Contents [hide] 
1 What is import/export and why does one need it?
2 How does one use the import/export utilities?
3 Can one export a subset of a table?
4 Can one monitor how fast a table is imported?
5 Can one import tables to a different tablespace?
6 Does one need to drop/ truncate objects before importing?
7 Can one import/export between different versions of Oracle?
8 Can one export to multiple files?/ Can one beat the Unix 2 Gig limit?
9 How can one improve Import/ Export performance?
10 What are the common Import/ Export problems?
[edit]What is import/export and why does one need it?

Friday, July 6, 2012

ORA-19602: cannot backup or copy active file in NOARCHIVELOG mode

-bash-3.00$ rman
RMAN> connect target /
connected to target database: ORCL(DBID=2560365586)
RMAN> backup database;
Starting backup at 27-DEC-10
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=59 device type=DISK
channel ORA_DISK_1: starting full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
RMAN-03009: failure of backup command on ORA_DISK_1 channel at 12/27/2010 09:21:13
ORA-19602: cannot backup or copy active file in NOARCHIVELOG mode
continuing other job steps, job failed will not be re-run
channel ORA_DISK_1: starting full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
including current control file in backup set
including current SPFILE in backup set
channel ORA_DISK_1: starting piece 1 at 27-DEC-10
channel ORA_DISK_1: finished piece 1 at 27-DEC-10
piece handle=/u01/app/oracle/flash_recovery_area/ORCL/
backupset/2011_12_27/o1_mf_ncsnf_TAG20111227T092110_7hlwhbss_.bkp tag=TAG20111227T092110 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03009: failure of backup command on ORA_DISK_1 channel at 12/27/2010 09:21:13
ORA-19602: cannot backup or copy active file in NOARCHIVELOG mode

To be able to take a hotbackup with RMAN, you have to enable ARCHIVELOG MODE.
 
To see how to enable ARCHIVELOG MODE, How to enable ARCHIVELOG MODE

To enable archivelog mode, user must have sysdba privileges.

SQL> connect sys/solarishowto as sysdba
SQL> shutdown immediate;
SQL> startup mount;
SQL> alter database archivelog;
SQL> alter database open;
Lets check that archivelog mode is enabled
SQL> select log_mode from v$database;
LOG_MODE
——————–
ARCHIVELOG

Tuesday, April 17, 2012

INFO: Stderr Read: Warning: Cannot convert string "-monotype-arial-regular-r-normal--*-140-*-*-p-*-iso8859-1" to type FontStruct

When installing Oracle 11gR2 on Solaris, you may get this warning when run ./runInstaller and the installation may hang at 86% and never complete.
If you install Oracle via putty and Xming, then you should install Xming-font and configure the file fonts-dir in Xming installation dir.

Edit file C:\Program files\Xming\fonts-dir and add the lines:
C:\Program files\Xming\fonts\TTF
 C:\Program files\Xming\fonts\misc
C:\Program files\Xming\fonts\75dpi
Note: with Windows 64bit, to make sure it works well, when installing Xming please make sure that the installation directory is C:\Program files\Xming, not C:\Program files (x86)\Xming.
After installing Xming-font, run ./runInstaller again the warning may appear in your putty console but it's ok. The installation will pass 86% and run Oracle configuration assistant.

Wednesday, April 11, 2012

Installing Oracle on Centos 6 64 bits

Base file is 10201_database_linux_x86_64.cpio.gz

1 System preparation

Install a X server
yum groupinstall "X Window System"
Or, for a remote install on a server that doesn't need a graphical interface, install the following packages and dependencies to do X11 forwarding:
yum install xorg-x11-xauth.x86_64 xorg-x11-apps.x86_64
Change the timezone to GMT
# rm /etc/localtime
# ln -s /usr/share/zoneinfo/GMT /etc/localtime
Install some additional packages (yes, oracle installer requires GCC…. :/ )
# yum install libXp libXtst binutils compat-db compat-libstdc++-33 glibc glibc-devel glibc-headers gcc gcc-c++ libstdc++ cpp make libaio ksh elfutils-libelf sysstat libaio libaio-devel setarch  libXp.i686 libXtst-1.0.99.2-3.el6.i686 glibc-devel.i686 libgcc-4.4.4-13.el6.i686 compat-libstdc++* compat-libf2c* compat-gcc* compat-libgcc* libXt.i686 libXtst.i686

Monday, April 9, 2012

Compiling PHP 5.3.10 / cannot find -lltdl

When compiling php from source, some CentOS 64 bit users have reported getting an error like this:
Code:
/usr/bin/ld: cannot find -lltdl
collect2: ld returned 1 exit status
Now, this error can be triggered in a couple different places and if you do a general Google search you'll get all kinds of advice about installing this or that module. All great advice, but not your solution. Here's what you need to do:
  1. Verify that the libtool and libtool-ltdl packages are installed.
  2. Symlink libltdl.so to libltdl.so.x.x.x