Oracle How to found current running SQL

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.

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

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).

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:

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

 UNDO_SIZE
———-
209715200

Undo Blocks per Second

UNDO_BLOCK_PER_SEC
——————
3.12166667

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!

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:

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.

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:

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

You will now be able to install with the command :

dependencies should be resolved automatically.

To allow access add the new iptables roule: