Thursday, February 28, 2013

Oracle How to found current running SQL

SELECT osuser, username, sql_text
from v$session a, v$sqltext b
where a.sql_address =b.address
order by address, piece;

select sid,user_name,sql_text
from v$open_cursor
where sid in (select sid from (select sid,serial#,username,program
from v$session
where status='ACTIVE'));

select s.USERNAME, sq.SQL_TEXT, s.LAST_CALL_ET
from v$open_cursor oc, v$sql sq, v$session s
where oc.HASH_VALUE = sq.HASH_VALUE
and s.SID = oc.SID
and s.STATUS = 'ACTIVE';

Tuesday, February 26, 2013

ORA-15260: permission denied on ASM disk group


select * from v$asm_disk;

select * from v$asm_diskgroup;

alter diskgroup DATA01 resize disk ORA_MAIN size 360G;

ORA-15260: permission denied on ASM disk group


When I login 11gR2 ASM instance as sysdba to mount diskgroup, it showed permission denied on ASM disk group. After I login the ASM instance as sysasm, it is fixed.


SQL> alter diskgroup DATA1 mount;
alter diskgroup DATA1 mount
*
ERROR at line 1:
ORA-15032: not all alterations performed
ORA-15260: permission denied on ASM disk group

 

SQL> connect / as sysasm <– as sysasm
Connected.
SQL> alter diskgroup data1 mount;

Diskgroup altered.

 

alter diskgroup DATA01 resize disk ORA_MAIN size 360G;

 

[grid@MDS-ORA-RAC-03 ~]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.3.0 Production on Tue Feb 26 15:40:38 2013

Copyright (c) 1982, 2011, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Real Application Clusters and Automatic Storage Management options

SQL> connect / as sysasm
Connected.
SQL> alter diskgroup DATA01 resize disk ORA_MAIN size 360G;

Diskgroup altered.

Friday, February 22, 2013

Add new hard drive in CentOS/RHEL/OEL without reboot

Add new hard drive in CentOS/RHEL/OEL without reboot:

1. First add an new 200 GB hard drive in scsi mode from VMware ESXi console.

Please notice only scsi disk allow operation while power on.

2. Then echo "- - -" > /sys/class/scsi_host/host2/scan

Please notice host2 is the scsi number I added from the ESXi.

# ls /sys/class/scsi_host/
host0/ host1/ host2/


2.1 Before:

# fdisk -l

Disk /dev/sda: 17.2 GB, 17179869184 bytes
255 heads, 63 sectors/track, 2088 cylinders
Units = cylinders of 16065 * 512 = 8225280 bytes
Sector size (logical/physical): 512 bytes / 512 bytes
I/O size (minimum/optimal): 512 bytes / 512 bytes
Disk identifier: 0x00073bb3

Device Boot Start End Blocks Id System
/dev/sda1 * 1 17 131072 83 Linux
Partition 1 does not end on cylinder boundary.
/dev/sda2 17 531 4128768 82 Linux swap / Solaris
Partition 2 does not end on cylinder boundary.
/dev/sda3 531 2089 12516352 83 Linux

Disk /dev/sdb: 107.4 GB, 107374182400 bytes
255 heads, 63 sectors/track, 13054 cylinders
Units = cylinders of 16065 * 512 = 8225280 bytes
Sector size (logical/physical): 512 bytes / 512 bytes
I/O size (minimum/optimal): 512 bytes / 512 bytes
Disk identifier: 0xbf77fef7

Device Boot Start End Blocks Id System
/dev/sdb1 1 13054 104856223+ 83 Linux

2.2 Then run:

# echo "- - -" > /sys/class/scsi_host/host2/scan

2.3 After:

# fdisk -l

Disk /dev/sda: 17.2 GB, 17179869184 bytes
255 heads, 63 sectors/track, 2088 cylinders
Units = cylinders of 16065 * 512 = 8225280 bytes
Sector size (logical/physical): 512 bytes / 512 bytes
I/O size (minimum/optimal): 512 bytes / 512 bytes
Disk identifier: 0x00073bb3

Device Boot Start End Blocks Id System
/dev/sda1 * 1 17 131072 83 Linux
Partition 1 does not end on cylinder boundary.
/dev/sda2 17 531 4128768 82 Linux swap / Solaris
Partition 2 does not end on cylinder boundary.
/dev/sda3 531 2089 12516352 83 Linux

Disk /dev/sdb: 107.4 GB, 107374182400 bytes
255 heads, 63 sectors/track, 13054 cylinders
Units = cylinders of 16065 * 512 = 8225280 bytes
Sector size (logical/physical): 512 bytes / 512 bytes
I/O size (minimum/optimal): 512 bytes / 512 bytes
Disk identifier: 0xbf77fef7

Device Boot Start End Blocks Id System
/dev/sdb1 1 13054 104856223+ 83 Linux

Disk /dev/sdc: 214.7 GB, 214748364800 bytes
255 heads, 63 sectors/track, 26108 cylinders
Units = cylinders of 16065 * 512 = 8225280 bytes
Sector size (logical/physical): 512 bytes / 512 bytes
I/O size (minimum/optimal): 512 bytes / 512 bytes
Disk identifier: 0x00000000

2.4 Check if the device showed up:
# ls /dev/sd*
sda sda1 sda2 sda3 sdb sdb1 sdc


3. Format the disk:

3.1 # fdisk /dev/sdc
Device contains neither a valid DOS partition table, nor Sun, SGI or OSF disklabel
Building a new DOS disklabel with disk identifier 0x14910557.
Changes will remain in memory only, until you decide to write them.
After that, of course, the previous content won't be recoverable.


Warning: invalid flag 0x0000 of partition table 4 will be corrected by w(rite)

WARNING: DOS-compatible mode is deprecated. It's strongly recommended to
switch off the mode (command 'c') and change display units to
sectors (command 'u').

Command (m for help): p

Disk /dev/sdc: 214.7 GB, 214748364800 bytes
255 heads, 63 sectors/track, 26108 cylinders
Units = cylinders of 16065 * 512 = 8225280 bytes
Sector size (logical/physical): 512 bytes / 512 bytes
I/O size (minimum/optimal): 512 bytes / 512 bytes
Disk identifier: 0x14910557

Device Boot Start End Blocks Id System

Command (m for help): n
Command action
e extended
p primary partition (1-4)
p
Partition number (1-4): 1
First cylinder (1-26108, default 1):
Using default value 1
Last cylinder, +cylinders or +size{K,M,G} (1-26108, default 26108):
Using default value 26108

Command (m for help): p

Disk /dev/sdc: 214.7 GB, 214748364800 bytes
255 heads, 63 sectors/track, 26108 cylinders
Units = cylinders of 16065 * 512 = 8225280 bytes
Sector size (logical/physical): 512 bytes / 512 bytes
I/O size (minimum/optimal): 512 bytes / 512 bytes
Disk identifier: 0x14910557

Device Boot Start End Blocks Id System
/dev/sdc1 1 26108 209712478+ 83 Linux

Command (m for help): w
The partition table has been altered!

Calling ioctl() to re-read partition table.
Syncing disks.

3.2 Format into ext4:

# mkfs.ext4 /dev/sdc1
mke2fs 1.41.12 (17-May-2010)
Filesystem label=
OS type: Linux
Block size=4096 (log=2)
Fragment size=4096 (log=2)
Stride=0 blocks, Stripe width=0 blocks
13107200 inodes, 52428119 blocks
2621405 blocks (5.00%) reserved for the super user
First data block=0
Maximum filesystem blocks=4294967296
1600 block groups
32768 blocks per group, 32768 fragments per group
8192 inodes per group
Superblock backups stored on blocks:
32768, 98304, 163840, 229376, 294912, 819200, 884736, 1605632, 2654208,
4096000, 7962624, 11239424, 20480000, 23887872


Writing inode tables: done
Creating journal (32768 blocks): done
Writing superblocks and filesystem accounting information: done

This filesystem will be automatically checked every 31 mounts or
180 days, whichever comes first. Use tune2fs -c or -i to override.

4. Try to mount the new partition:

# mkdir -p /data2
# mount /dev/sdc1 /data2


vi /etc/fstab
#Data FS - EXT4
/dev/sdb1 /data ext4 defaults 1 2
/dev/sdc1 /data2 ext4 defaults 1 2

5. Test if it works:
# touch lamber_test.please_delete

Thursday, February 21, 2013

Installing YUM in CentOS 5 minimal

I’m configuring a new CentOS 5.9 server in minimal mode (without any package select).

Surprise found it didn’t come with yum installed (been with 6 too long).
rpm -Uvh http://centos.mirror.rafal.ca/5.9/os/x86_64/CentOS/elfutils-libs-0.137-3.el5.x86_64.rpm
rpm -Uvh http://centos.mirror.rafal.ca/5.9/os/x86_64/CentOS/gmp-4.1.4-10.el5.x86_64.rpm
rpm -Uvh http://centos.mirror.rafal.ca/5.9/os/x86_64/CentOS/readline-5.1-3.el5.x86_64.rpm
rpm -Uvh http://centos.mirror.rafal.ca/5.9/os/x86_64/CentOS/python-2.4.3-56.el5.x86_64.rpm
rpm -Uvh http://centos.mirror.rafal.ca/5.9/os/x86_64/CentOS/libxml2-2.6.26-2.1.15.el5_8.6.x86_64.rpm
rpm -Uvh http://centos.mirror.rafal.ca/5.9/os/x86_64/CentOS/libxml2-python-2.6.26-2.1.15.el5_8.6.x86_64.rpm
rpm -Uvh http://centos.mirror.rafal.ca/5.9/os/x86_64/CentOS/expat-1.95.8-11.el5_8.x86_64.rpm
rpm -Uvh http://centos.mirror.rafal.ca/5.9/os/x86_64/CentOS/python-elementtree-1.2.6-5.x86_64.rpm
rpm -Uvh http://centos.mirror.rafal.ca/5.9/os/x86_64/CentOS/sqlite-3.3.6-6.x86_64.rpm
rpm -Uvh http://centos.mirror.rafal.ca/5.9/os/x86_64/CentOS/python-sqlite-1.1.7-1.2.1.x86_64.rpm
rpm -Uvh http://centos.mirror.rafal.ca/5.9/os/x86_64/CentOS/elfutils-0.137-3.el5.x86_64.rpm
rpm -Uvh http://centos.mirror.rafal.ca/5.9/os/x86_64/CentOS/rpm-python-4.4.2.3-31.el5.x86_64.rpm
rpm -Uvh http://centos.mirror.rafal.ca/5.9/os/x86_64/CentOS/m2crypto-0.16-9.el5.x86_64.rpm
rpm -Uvh http://centos.mirror.rafal.ca/5.9/os/x86_64/CentOS/python-urlgrabber-3.1.0-6.el5.noarch.rpm
rpm -Uvh http://centos.mirror.rafal.ca/5.9/os/x86_64/CentOS/yum-metadata-parser-1.1.2-4.el5.x86_64.rpm
rpm -Uvh http://centos.mirror.rafal.ca/5.9/os/x86_64/CentOS/python-iniparse-0.2.3-6.el5.noarch.rpm
rpm -Uvh http://centos.mirror.rafal.ca/5.9/os/x86_64/CentOS/yum-3.2.22-40.el5.centos.noarch.rpm
rpm -Uvh http://centos.mirror.rafal.ca/5.9/os/x86_64/CentOS/yum-fastestmirror-1.1.16-21.el5.centos.noarch.rpm

Tuesday, February 19, 2013

How to install Xdebug

First make sure you added EPEL repository.
yum install php-pecl-xdebug

To open remote debug add more config into the xdebug.ini file:

xdebug.remote_enable = true
# your workstation IP
xdebug.remote_host = "192.168.1.5"
# port number
xdebug.remote_port = 9001


restart apache service:
service httpd restart

Optimize Oracle UNDO Parameters

Overview
Starting in Oracle9i, rollback segments are re-named undo logs. Traditionally transaction undo information was stored in Rollback Segments until a commit or rollback statement was issued, at which point it was made available for overlaying.

Best of all, automatic undo management allows the DBA to specify how long undo information should be retained after commit, preventing "snapshot too old" errors on long running queries.

This is done by setting the UNDO_RETENTION parameter.  The default is 900 seconds (5 minutes), and you can set this parameter to guarantee that Oracle keeps undo logs for extended periods of time.

Rather than having to define and manage rollback segments, you can simply define an Undo tablespace and let Oracle take care of the rest. Turning on automatic undo management is easy.  All you need to do is create an undo tablespace and set UNDO_MANAGEMENT = AUTO.

However it is worth to tune the following important parameters

  1. The size of the UNDO tablespace

  2. The UNDO_RETENTION parameter


Calculate UNDO_RETENTION  for given UNDO Tabespace

You can choose to allocate a specific size for the UNDO tablespace and then set the UNDO_RETENTION parameter to an optimal value according to the UNDO size and the database activity. If your disk space is limited and you do not want to allocate more space than necessary to the UNDO tablespace, this is the way to proceed. The following query will help you to optimize the UNDO_RETENTION
parameter:

optimal_undo_retention

Because these following queries use the V$UNDOSTAT statistics, run the queries only after the database has been running with UNDO for a significant and representative time!

Actual Undo Size
SELECT SUM(a.bytes) "UNDO_SIZE"
FROM v$datafile a,
v$tablespace b,
dba_tablespaces c
WHERE c.contents = 'UNDO'
AND c.status = 'ONLINE'
AND b.name = c.tablespace_name
AND a.ts# = b.ts#;


 UNDO_SIZE
----------
209715200


Undo Blocks per Second
SELECT MAX(undoblks/((end_time-begin_time)*3600*24))
"UNDO_BLOCK_PER_SEC"
FROM v$undostat;

UNDO_BLOCK_PER_SEC
------------------
3.12166667


DB Block Size
SELECT TO_NUMBER(value) "DB_BLOCK_SIZE [KByte]"
FROM v$parameter
WHERE name = 'db_block_size';


DB_BLOCK_SIZE [Byte]
--------------------
4096

Optimal Undo Retention

209'715'200 / (3.12166667 * 4'096) = 16'401 [Sec]


Using Inline Views, you can do all in one query!

SELECT d.undo_size/(1024*1024) "ACTUAL UNDO SIZE[MByte]",
SUBSTR(e.value,1,25) "UNDO RETENTION [Sec]",
ROUND((d.undo_size/ (to_number(f.value) *
g.undo_block_per_sec))) "OPTIMAL UNDO RETENTION [Sec]"
FROM (
SELECT SUM(a.bytes) undo_size
FROM v$datafile a,
v$tablespace b,
dba_tablespaces c
WHERE c.contents = 'UNDO'
AND c.status ='ONLINE'
AND b.name =c.tablespace_name
AND a.ts# = b.ts#
) d,
v$parameter e,
v$parameter f,
(
SELECT
MAX(undoblks/((end_time-begin_time)*3600*24))
undo_block_per_sec
FROM v$undostat
) g
WHERE e.name = 'undo_retention'
AND f.name = 'db_block_size'
/


ACTUAL UNDO SIZE [MByte]


------------------------

200

UNDO RETENTION [Sec]

--------------------

10800

OPTIMAL UNDO RETENTION [Sec]

----------------------------

16401

Calculate Needed UNDO Size for given
Database Activity




If you are not limited by disk space, then it would be better to
choose the UNDO_RETENTION time that is best for you (for FLASHBACK, etc.). Allocate
the appropriate size to the UNDO tablespace according to the database
activity:


undo_size

Again, all in one query:



SELECT d.undo_size/(1024*1024) "ACTUAL UNDO SIZE [MByte]",
SUBSTR(e.value,1,25) "UNDO RETENTION [Sec]",
(TO_NUMBER(e.value) * TO_NUMBER(f.value) *
g.undo_block_per_sec) / (1024*1024)
"NEEDED UNDO SIZE [MByte]"
FROM (
SELECT SUM(a.bytes) undo_size
FROM v$datafile a,
v$tablespace b,
dba_tablespaces c
WHERE c.contents = 'UNDO'
AND c.status = 'ONLINE'
AND b.name = c.tablespace_name
AND a.ts# = b.ts#
) d,
v$parameter e,
v$parameter f,
(
SELECT MAX(undoblks/((end_time-begin_time)*3600*24))
undo_block_per_sec
FROM v$undostat
) g
WHERE e.name = 'undo_retention'
AND f.name = 'db_block_size'
/

ACTUAL UNDO SIZE [MByte]
------------------------
200
UNDO RETENTION [Sec]
--------------------
10800
NEEDED UNDO SIZE [MByte]
------------------------
131.695313


The previous query may return a "NEEDED UNDO SIZE" that is less
than the "ACTUAL UNDO SIZE". If this is the case, you may be wasting space. You can
choose to resize your UNDO tablespace to a lesser value or increase your
UNDO_RETENTION parameter to use the additional space.

Saturday, February 16, 2013

Installing the Webmin RPM

Using the Webmin YUM repository

If you like to install and update Webmin via RPM, create the /etc/yum.repos.d/webmin.repo file containing:

[Webmin]
name=Webmin Distribution Neutral
#baseurl=http://download.webmin.com/download/yum
mirrorlist=http://download.webmin.com/download/yum/mirrorlist
enabled=1


You should also fetch and install my GPG key with which the packages are signed, with the commands:

wget http://www.webmin.com/jcameron-key.asc
rpm --import jcameron-key.asc


You will now be able to install with the command :

yum install webminAll


dependencies should be resolved automatically.

To allow access add the new iptables roule:

vi /etc/sysconfig/iptables

-A INPUT -m state --state NEW -m tcp -p tcp --dport 10000 -j ACCEPT

service iptables restart