Thursday, December 30, 2010

Test storage performance using Orion (Come with 11gR2 Grid Infrastructure)


[grid@vmxdb03 grid]$ orion -help
ORION: ORacle IO Numbers -- Version 11.2.0.2.0

ORION runs IO performance tests that model Oracle RDBMS IO workloads.
It measures the performance of small (2-32K) IOs and large (128K+) IOs
at various load levels.

Each Orion data point is a test for a specific mix of small and large
IO loads sustained for a duration. An Orion test consists of multiple
data point tests. These data point tests can be represented as a
two-dimensional matrix. Each column in the matrix represents data
point tests with the same small IO load, but varying large IO loads.
Each row represents data point tests with the same large IO load, but
varying small IO loads. An Orion test can be for a single point, a
single row, a single column, or the whole matrix.

The 'run' parameter is the only mandatory parameter. Defaults
are indicated for all other parameters. For additional information on
the user interface, see the Orion User Guide.

is the prefix used for all input and output filenames. By
default, it is 'orion'. It can be specified with the 'testname'
parameter.

.lun should contain a carriage-return-separated list of LUNs.

The output files for a test run are prefixed by _ where
date is "yyyymmdd_hhmm".

The output files are:
__summary.txt - Summary of the input parameters, along with
the minimum small IO latency (in usecs), the maximum
MBPS, and the maximum IOPS observed.
__mbps.csv - Performance results of large IOs in MBPS.
__iops.csv - Performance results of small IOs in IOPS.
__lat.csv - Latency of small IOs in microseconds.
__hist.csv - Histogram of IO latencies.
__trace.txt - Extended, unprocessed output.

WARNING: IF YOU ARE PERFORMING WRITE TESTS, BE PREPARED TO LOSE ANY DATA STORED
ON THE LUNS.

Mandatory parameters:
run Type of workload to run (simple, normal, advanced, dss, oltp).
simple - Tests random small (8K) IOs at various loads,
then random large (1M) IOs at various loads.
normal - Tests combinations of random small (8K) IOs and
random large (1M) IOs.
advanced - Tests the workload specified by the user
using optional parameters.
dss - Tests with random large (1M) IOs at increasing
loads to determine the maximum throughput.
oltp - Tests with random small (8K) IOs at increasing
loads to determine the maximum IOPS.

Optional parameters:
testname Name of the test run.

num_disks Number of disks (physical spindles). This number is
used to gauge the range of loads that Orion should test
at. Increasing this parameter results in Orion using
heavier IO loads. Default is the number of LUNs in
.lun.

size_small Size of small IOs in KB. Default is 8.

size_large Size of large IOs in KB. Default is 1024.

type Type of large IOs (rand, seq). Default is rand.
rand - Randomly distributed large IOs.
seq - Sequential streams of large IOs.

num_streamIO Number of concurrent IOs per stream. This parameter is only
used if -type is seq. Default is 4.

simulate Orion tests on a virtual LUN formed by combining the
specified LUNs in one of these ways. This parameter is
typically only used if -type is seq. Default is concat.
concat - A serial concatenation of the LUNs. Each
sequential stream issues IOs to only one LUN.
raid0 - A RAID-0 mapping across the LUNs. Each
sequential stream issues IOs across all LUNs,
using RAID-0 striping.

write Percentage of IOs that are writes (SEE WARNING ABOVE).
Default is 0.

cache_size Size in MBs of the array's cache.
Unless this option is set to 0, Orion issues a number
of unmeasured, random IOs before each large sequential
data point. These IOs fill up the storage array's cache
(if any) with random data so that IOs from one
data point do not result in cache hits for the next
data point. Read tests are preceded with junk reads
and write tests are preceded with junk writes. If
specified, this 'cache warming' is performed until
cache_size MBs of IO have been read or written.
Default behavior is to issue 2 minutes of unmeasured random
IOs before each data point.

duration Duration of each data point in seconds. Default is 60.

num_small Number of outstanding small IOs. This parameter controls the
small IO load. Only used if -matrix is point, col, or max.
No default.

num_large This parameter controls the large IO load.
For -type rand, number of outstanding large IOs.
For -type seq, number of sequential IO streams. Only used
if -matrix is point, row, or max. No default.

matrix An Orion test consists of multiple data point tests. These data
point tests can be represented as a two-dimensional matrix.
Each column in the matrix represents data point tests with
the same small IO load, but varying large IO loads. Each
row represents data point tests with the same large IO load,
but varying small IO loads. An Orion test can be for a
single point, a single row, a single column, or the whole
matrix, depending on the matrix option setting below.
Default is basic.
basic - Test small IOs only, then large IOs only.
detailed - Test entire matrix.
point - Test with num_small small IOs, num_large large
IOs.
col - Test a varying large IO load with num_small
small IOs.
row - Test a varying small IO load with num_large
large IOs.
max - Test varying loads up to the num_small and
num_large limits.

verbose Prints tracing information to standard output if set.
Not set by default.

Examples:
For a preliminary set of data
-run simple
For a basic set of data
-run normal
To evaluate storage for an OLTP database
-run oltp
To evaluate storage for a data warehouse
-run dss
To generate combinations of 32KB and 1MB reads to random locations
-run advanced
-size_small 32 -size_large 1024 -type rand
-matrix detailed
To generate multiple sequential 1MB write streams, simulating RAID0 striping
-run advanced
-simulate RAID0 -write 100 -type seq
-matrix col -num_small 0




[grid@vmxdb03 grid]$ cat iscsi_io.lun
/dev/iscsi/asmdisk2p1
/dev/iscsi/asmdisk3p1
/dev/iscsi/asmdisk4p1
/dev/iscsi/asmdisk5p1


[grid@vmxdb03 grid]$ orion -run simple -testname iscsi_io -num_disks 4
ORION: ORacle IO Numbers -- Version 11.2.0.2.0
iscsi_io_20101218_1854
Calibration will take approximately 30 minutes.
Using a large value for -cache_size may take longer.

ORA-56727: orion must be invoked using its full, absolute path
orion_main: orion_spawn sml failed
Test aborted due to errors.
[grid@vmxdb03 grid]$ $ORACLE_HOME/bin/orion -run simple -testname iscsi_io -num_disks 4
ORION: ORacle IO Numbers -- Version 11.2.0.2.0
iscsi_io_20101218_1854
Calibration will take approximately 30 minutes.
Using a large value for -cache_size may take longer.

[root@vmxdb03 ~]# iostat -x 5 1000
Linux 2.6.18-194.el5 (vmxdb03.lab.dbaglobe.com) 12/18/2010

avg-cpu: %user %nice %system %iowait %steal %idle
1.12 0.00 9.77 45.07 0.00 44.05

Device: rrqm/s wrqm/s r/s w/s rsec/s wsec/s avgrq-sz avgqu-sz await svctm %util
sda 0.00 64.60 0.00 22.00 0.00 694.40 31.56 0.39 17.67 0.91 2.00
sda1 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00
sda2 0.00 64.60 0.00 22.00 0.00 694.40 31.56 0.39 17.67 0.91 2.00
dm-0 0.00 0.00 0.00 86.80 0.00 694.40 8.00 0.86 9.96 0.23 2.00
dm-1 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00
hdc 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00
sdb 0.00 0.00 0.00 0.80 0.00 2.20 2.75 0.02 26.75 26.75 2.14
sdb1 0.00 0.00 0.00 0.80 0.00 2.20 2.75 0.02 26.75 26.75 2.14
sdc 0.00 0.00 29.80 0.00 476.80 0.00 16.00 0.55 18.41 13.70 40.82
sdc1 0.00 0.00 29.80 0.00 476.80 0.00 16.00 0.55 18.41 13.70 40.82
sdd 0.00 0.00 33.40 0.00 534.40 0.00 16.00 0.59 17.75 13.46 44.94
sdd1 0.00 0.00 33.40 0.00 534.40 0.00 16.00 0.59 17.75 13.46 44.94
sde 0.00 0.00 32.80 0.00 524.80 0.00 16.00 0.58 17.60 13.35 43.80
sde1 0.00 0.00 32.80 0.00 524.80 0.00 16.00 0.58 17.60 13.35 43.80
sdf 0.00 0.00 27.40 0.00 438.40 0.00 16.00 0.49 17.84 14.42 39.52
sdf1 0.00 0.00 27.40 0.00 438.40 0.00 16.00 0.49 17.84 14.42 39.52


avg-cpu: %user %nice %system %iowait %steal %idle
1.42 0.00 9.50 43.98 0.00 45.10

Device: rrqm/s wrqm/s r/s w/s rsec/s wsec/s avgrq-sz avgqu-sz await svctm %util
sda 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00
sda1 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00
sda2 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00
dm-0 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00
dm-1 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00
hdc 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00
sdb 0.00 0.00 0.00 0.80 0.00 2.20 2.75 0.10 121.75 121.75 9.74
sdb1 0.00 0.00 0.00 0.80 0.00 2.20 2.75 0.10 121.75 121.75 9.74
sdc 0.00 0.00 28.40 0.00 454.40 0.00 16.00 2.21 78.43 24.70 70.16
sdc1 0.00 0.00 28.40 0.00 454.40 0.00 16.00 2.21 78.43 24.70 70.16
sdd 0.00 0.00 31.00 0.00 496.00 0.00 16.00 2.15 68.19 23.50 72.84
sdd1 0.00 0.00 31.00 0.00 496.00 0.00 16.00 2.15 68.19 23.50 72.84
sde 0.00 0.00 39.20 0.00 627.20 0.00 16.00 2.49 63.71 18.80 73.70
sde1 0.00 0.00 39.20 0.00 627.20 0.00 16.00 2.49 63.71 18.80 73.70
sdf 0.00 0.00 36.20 0.00 579.20 0.00 16.00 2.31 63.98 21.02 76.08
sdf1 0.00 0.00 36.20 0.00 579.20 0.00 16.00 2.31 63.98 21.02 76.08


---- OpenFiler ----
[root@openfiler ~]# iostat -x 5 1000 |grep sda5

avg-cpu: %user %nice %system %iowait %steal %idle
0.00 0.00 1.07 0.00 0.00 98.93

Device: rrqm/s wrqm/s r/s w/s rsec/s wsec/s rkB/s wkB/s avgrq-sz avgqu-sz await svctm %util
sda5 0.33 0.02 68.91 1.93 863.40 492.88 431.70 246.44 19.15 0.58 8.24 1.43 10.10
sda5 0.00 0.40 133.87 1.20 2141.88 4.41 1070.94 2.20 15.89 7.97 59.10 7.41 100.12
sda5 0.00 0.00 130.34 1.60 2085.43 2.99 1042.71 1.50 15.83 7.98 60.25 7.57 99.88
sda5 0.00 0.00 135.20 1.40 2163.20 2.80 1081.60 1.40 15.86 7.99 58.62 7.32 100.00
sda5 0.00 0.20 131.80 1.60 2108.80 3.20 1054.40 1.60 15.83 7.96 59.64 7.50 100.00



[grid@vmxdb03 grid]$ $ORACLE_HOME/bin/orion -run simple -testname iscsi_io -num_disks 4
ORION: ORacle IO Numbers -- Version 11.2.0.2.0
iscsi_io_20101218_1854
Calibration will take approximately 30 minutes.
Using a large value for -cache_size may take longer.

Maximum Large MBPS=6.86 @ Small=0 and Large=4

Maximum Small IOPS=136 @ Small=16 and Large=0
Small Read Latency: avg=117588 us, min=29496 us, max=269881 us, std dev=25157 us @ Small=16 and Large=0

Minimum Small Latency=11782 usecs @ Small=1 and Large=0
Small Read Latency: avg=11782 us, min=2988 us, max=201139 us, std dev=3985 us @ Small=1 and Large=0
Small Read / Write Latency Histogram @ Small=16 and Large=0
Latency: # of IOs (read) # of IOs (write)
0 - 1 us: 0 0
2 - 4 us: 0 0
4 - 8 us: 0 0
8 - 16 us: 0 0
16 - 32 us: 0 0
32 - 64 us: 0 0
64 - 128 us: 0 0
128 - 256 us: 0 0
256 - 512 us: 0 0
512 - 1024 us: 0 0
1024 - 2048 us: 0 0
2048 - 4096 us: 6 0
4096 - 8192 us: 640 0
8192 - 16384 us: 4251 0
16384 - 32768 us: 189 0
32768 - 65536 us: 0 0
65536 - 131072 us: 0 0
131072 - 262144 us: 1 0
262144 - 524288 us: 0 0
524288 - 1048576 us: 0 0
1048576 - 2097152 us: 0 0
2097152 - 4194304 us: 0 0
4194304 - 8388608 us: 0 0
8388608 - 16777216 us: 0 0
16777216 - 33554432 us: 0 0
33554432 - 67108864 us: 0 0
67108864 - 134217728 us: 0 0
134217728 - 268435456 us: 0 0

-rw-r--r-- 1 grid oinstall 88 Dec 18 18:51 iscsi_io.lun
-rw-r--r-- 1 grid oinstall 23211 Dec 18 19:22 iscsi_io_20101218_1854_trace.txt
-rw-r--r-- 1 grid oinstall 1969 Dec 18 19:22 iscsi_io_20101218_1854_summary.txt
-rw-r--r-- 1 grid oinstall 738 Dec 18 19:22 iscsi_io_20101218_1854_mbps.csv
-rw-r--r-- 1 grid oinstall 903 Dec 18 19:22 iscsi_io_20101218_1854_lat.csv
-rw-r--r-- 1 grid oinstall 838 Dec 18 19:22 iscsi_io_20101218_1854_iops.csv
-rw-r--r-- 1 grid oinstall 26434 Dec 18 19:22 iscsi_io_20101218_1854_hist.txt


[grid@vmxdb03 grid]$ cat iscsi_io_20101218_1854_summary.txt
ORION VERSION 11.2.0.2.0

Command line:
-run simple -testname iscsi_io -num_disks 4

These options enable these settings:
Test: iscsi_io
Small IO size: 8 KB
Large IO size: 1024 KB
IO types: small random IOs, large random IOs
Sequential stream pattern: one LUN per stream
Writes: 0%
Cache size: not specified
Duration for each data point: 60 seconds
Small Columns:, 0
Large Columns:, 0, 1, 2, 3, 4, 5, 6, 7, 8
Total Data Points: 29

Name: /dev/iscsi/asmdisk2p1 Size: 2145957888
Name: /dev/iscsi/asmdisk3p1 Size: 2145957888
Name: /dev/iscsi/asmdisk4p1 Size: 2145957888
Name: /dev/iscsi/asmdisk5p1 Size: 2145957888
4 files found.

Maximum Large MBPS=6.86 @ Small=0 and Large=4

Maximum Small IOPS=136 @ Small=16 and Large=0
Small Read Latency: avg=117588 us, min=29496 us, max=269881 us, std dev=25157 us @ Small=16 and Large=0

Minimum Small Latency=11782.15 usecs @ Small=1 and Large=0
Small Read Latency: avg=11782 us, min=2988 us, max=201139 us, std dev=3985 us @ Small=1 and Large=0
Small Read / Write Latency Histogram @ Small=16 and Large=0
Latency: # of IOs (read) # of IOs (write)
0 - 1 us: 0 0
2 - 4 us: 0 0
4 - 8 us: 0 0
8 - 16 us: 0 0
16 - 32 us: 0 0
32 - 64 us: 0 0
64 - 128 us: 0 0
128 - 256 us: 0 0
256 - 512 us: 0 0
512 - 1024 us: 0 0
1024 - 2048 us: 0 0
2048 - 4096 us: 6 0
4096 - 8192 us: 640 0
8192 - 16384 us: 4251 0
16384 - 32768 us: 189 0
32768 - 65536 us: 0 0
65536 - 131072 us: 0 0
131072 - 262144 us: 1 0
262144 - 524288 us: 0 0
524288 - 1048576 us: 0 0
1048576 - 2097152 us: 0 0
2097152 - 4194304 us: 0 0
4194304 - 8388608 us: 0 0
8388608 - 16777216 us: 0 0
16777216 - 33554432 us: 0 0
33554432 - 67108864 us: 0 0
67108864 - 134217728 us: 0 0
134217728 - 268435456 us: 0 0



[grid@vmxdb03 grid]$ $ORACLE_HOME/bin/orion -run oltp -testname iscsi_io -num_disks 4 -duration 120
ORION: ORacle IO Numbers -- Version 11.2.0.2.0
iscsi_io_20101218_1947
Calibration will take approximately 49 minutes.
Using a large value for -cache_size may take longer.


Maximum Small IOPS=135 @ Small=48 and Large=0
Small Read Latency: avg=354599 us, min=46399 us, max=1336551 us, std dev=52605 us @ Small=48 and Large=0

Minimum Small Latency=32641 usecs @ Small=4 and Large=0
Small Read Latency: avg=32641 us, min=7475 us, max=733521 us, std dev=12886 us @ Small=4 and Large=0
Small Read / Write Latency Histogram @ Small=48 and Large=0
Latency: # of IOs (read) # of IOs (write)
0 - 1 us: 0 0
2 - 4 us: 0 0
4 - 8 us: 0 0
8 - 16 us: 0 0
16 - 32 us: 0 0
32 - 64 us: 0 0
64 - 128 us: 0 0
128 - 256 us: 0 0
256 - 512 us: 0 0
512 - 1024 us: 0 0
1024 - 2048 us: 0 0
2048 - 4096 us: 0 0
4096 - 8192 us: 3 0
8192 - 16384 us: 57 0
16384 - 32768 us: 7611 0
32768 - 65536 us: 7011 0
65536 - 131072 us: 5 0
131072 - 262144 us: 0 0
262144 - 524288 us: 0 0
524288 - 1048576 us: 4 0
1048576 - 2097152 us: 0 0
2097152 - 4194304 us: 0 0
4194304 - 8388608 us: 0 0
8388608 - 16777216 us: 0 0
16777216 - 33554432 us: 0 0
33554432 - 67108864 us: 0 0
67108864 - 134217728 us: 0 0
134217728 - 268435456 us: 0 0

Wednesday, December 15, 2010

Configure Openfiler as DNS server for oracle 11gR2 RAC installation (without GNS)


[root@openfiler ~]# conary update bind:runtime
Including extra troves to resolve dependencies:
info-named:user=1-1-0.1 util-linux-ng:runtime=2.16-0.0.4-1
Applying update job 1 of 2:
Install info-named(:user)=1-1-0.1
Applying update job 2 of 2:
Install bind:runtime=9.4.3_P5-1.1-1
Update util-linux-ng(:runtime) (2.16-0.0.3-1 -> 2.16-0.0.4-1)


[root@openfiler ~]# cat /etc/named.conf
options {
directory "/srv/named/data";
};


# forward zone
zone "lab.dbaglobe.com" IN {
type master;
file "lab.dbaglobe.com.zone";
};


# reverse zone
zone "1.168.192.in-addr.arpa" IN {
type master;
file "1.168.192.in-addr.arpa.zone";
};


[root@openfiler ~]# cat /srv/named/data/lab.dbaglobe.com.zone
$TTL 86400
@ IN SOA openfiler.lab.dbaglobe.com. root.localhost. (
2010063000 ; serial
28800 ; refresh
14400 ; retry
3600000 ; expiry
86400 ) ; minimum
@ IN NS openfiler.lab.dbaglobe.com.
localhost IN A 127.0.0.1
openfiler IN A 192.168.1.100
vmxdb03 IN A 192.168.1.23
vmxdb04 IN A 192.168.1.24
vmxdb03-vip IN A 192.168.1.123
vmxdb04-vip IN A 192.168.1.124
vmxdb-scan IN A 192.168.1.126
IN A 192.168.1.127
IN A 192.168.1.128


[root@openfiler ~]# cat /srv/named/data/1.168.192.in-addr.arpa.zone
$TTL 86400
@ IN SOA openfiler.lab.dbaglobe.com. root.localhost. (
2010063000 ; serial
28800 ; refresh
14400 ; retry
3600000 ; expiry
86400 ) ; minimum
@ IN NS openfiler.lab.dbaglobe.com.
100 IN PTR openfiler.lab.dbaglobe.com.
23 IN PTR vmxdb03.lab.dbaglobe.com.
24 IN PTR vmxdb04.lab.dbaglobe.com.
123 IN PTR vmxdb03-vip.lab.dbaglobe.com.
124 IN PTR vmxdb04-vip.lab.dbaglobe.com.


[root@openfiler ~]# service named start
Starting named: [ OK ]


[root@openfiler ~]# chkconfig named on


[root@openfiler ~]# chkconfig named --list
named 0:off 1:off 2:on 3:on 4:on 5:on 6:off

Openfiler ISCSI LUN persistent naming using UDEV


SCSI device sda: 90275840 512-byte hdwr sectors (46221 MB)
SCSI device sda: drive cache: write back
SCSI device sda: 90275840 512-byte hdwr sectors (46221 MB)
SCSI device sda: drive cache: write back
Vendor: OPNFILER Model: VIRTUAL-DISK Rev: 0
SCSI device sdb: 4194304 512-byte hdwr sectors (2147 MB)
Vendor: OPNFILER Model: VIRTUAL-DISK Rev: 0
SCSI device sdc: 4194304 512-byte hdwr sectors (2147 MB)
Vendor: OPNFILER Model: VIRTUAL-DISK Rev: 0
SCSI device sdd: 4194304 512-byte hdwr sectors (2147 MB)
Vendor: OPNFILER Model: VIRTUAL-DISK Rev: 0
SCSI device sde: 4194304 512-byte hdwr sectors (2147 MB)
Vendor: OPNFILER Model: VIRTUAL-DISK Rev: 0
SCSI device sdf: 4194304 512-byte hdwr sectors (2147 MB)


[root@vmxdb02 ~]# lsscsi
[0:0:0:0] disk ATA VBOX HARDDISK 1.0 /dev/sda
[1:0:0:0] disk OPNFILER VIRTUAL-DISK 0 /dev/sdb
[1:0:0:1] disk OPNFILER VIRTUAL-DISK 0 /dev/sdc
[1:0:0:2] disk OPNFILER VIRTUAL-DISK 0 /dev/sdd
[1:0:0:3] disk OPNFILER VIRTUAL-DISK 0 /dev/sde
[1:0:0:4] disk OPNFILER VIRTUAL-DISK 0 /dev/sdf



[root@vmxdb02 ~]# ls -l /dev/disk/by-id/
total 0
lrwxrwxrwx 1 root root 9 Dec 15 09:12 ata-VBOX_CD-ROM_VB2-01700376 -> ../../hdc
lrwxrwxrwx 1 root root 9 Dec 15 09:30 scsi-14f504e46494c45003079486253592d664130732d4d316730 -> ../../sdc
lrwxrwxrwx 1 root root 9 Dec 15 09:30 scsi-14f504e46494c45004e72793839712d444b536c2d7a453543 -> ../../sdf
lrwxrwxrwx 1 root root 9 Dec 15 09:30 scsi-14f504e46494c45005556417744532d757952702d58783772 -> ../../sde
lrwxrwxrwx 1 root root 9 Dec 15 09:30 scsi-14f504e46494c45006531506a52412d625645702d79354674 -> ../../sdd
lrwxrwxrwx 1 root root 9 Dec 15 09:30 scsi-14f504e46494c450074324f794a562d335134562d626f756e -> ../../sdb
lrwxrwxrwx 1 root root 9 Dec 15 09:12 scsi-SATA_VBOX_HARDDISK_VB3b4884d5-bc4c8fd3 -> ../../sda
lrwxrwxrwx 1 root root 10 Dec 15 09:12 scsi-SATA_VBOX_HARDDISK_VB3b4884d5-bc4c8fd3-part1 -> ../../sda1
lrwxrwxrwx 1 root root 10 Dec 15 09:12 scsi-SATA_VBOX_HARDDISK_VB3b4884d5-bc4c8fd3-part2 -> ../../sda2



[root@vmxdb02 ~]# scsi_id -gus /block/sdc
14f504e46494c45003079486253592d664130732d4d316730



[root@vmxdb02 ~]# vi /etc/udev/rules.d/55-iscsi.rules
[root@vmxdb02 ~]# cat /etc/udev/rules.d/55-iscsi.rules
# /dev/iscsi/asmdisk1
KERNEL=="sd*", BUS=="scsi", PROGRAM=="/sbin/scsi_id -gus %p",
RESULT=="14f504e46494c450074324f794a562d335134562d626f756e", SYMLINK+="iscsi/asmdisk1p%n", OWNER="grid", GROUP="oinstall", MODE="0660"

# /dev/iscsi/asmdisk2
KERNEL=="sd*", BUS=="scsi", PROGRAM=="/sbin/scsi_id -gus %p",
RESULT=="14f504e46494c45003079486253592d664130732d4d316730", SYMLINK+="iscsi/asmdisk2p%n", OWNER="grid", GROUP="oinstall", MODE="0660"

# /dev/iscsi/asmdisk3
KERNEL=="sd*", BUS=="scsi", PROGRAM=="/sbin/scsi_id -gus %p",
RESULT=="14f504e46494c45006531506a52412d625645702d79354674", SYMLINK+="iscsi/asmdisk3p%n", OWNER="grid", GROUP="oinstall", MODE="0660"

# /dev/iscsi/asmdisk4
KERNEL=="sd*", BUS=="scsi", PROGRAM=="/sbin/scsi_id -gus %p",
RESULT=="14f504e46494c45005556417744532d757952702d58783772", SYMLINK+="iscsi/asmdisk4p%n", OWNER="grid", GROUP="oinstall", MODE="0660"

# /dev/iscsi/asmdisk5
KERNEL=="sd*", BUS=="scsi", PROGRAM=="/sbin/scsi_id -gus %p",
RESULT=="14f504e46494c45004e72793839712d444b536c2d7a453543", SYMLINK+="iscsi/asmdisk5p%n", OWNER="grid", GROUP="oinstall", MODE="0660"



[root@vmxdb02 ~]# udevcontrol reload_rules
[root@vmxdb02 ~]# start_udev
Starting udev:
[ OK ]


[root@vmxdb02 ~]# ls -l /dev/iscsi/asmdisk*
lrwxrwxrwx 1 root root 6 Dec 15 09:57 /dev/iscsi/asmdisk1p -> ../sdb
lrwxrwxrwx 1 root root 6 Dec 15 09:57 /dev/iscsi/asmdisk2p -> ../sdc
lrwxrwxrwx 1 root root 6 Dec 15 09:57 /dev/iscsi/asmdisk3p -> ../sdd
lrwxrwxrwx 1 root root 6 Dec 15 09:57 /dev/iscsi/asmdisk4p -> ../sde
lrwxrwxrwx 1 root root 6 Dec 15 09:57 /dev/iscsi/asmdisk5p -> ../sdf



[root@vmxdb02 ~]# ls -l /dev/sd*
brw-r----- 1 root disk 8, 0 Dec 15 09:12 /dev/sda
brw-r----- 1 root disk 8, 1 Dec 15 09:12 /dev/sda1
brw-r----- 1 root disk 8, 2 Dec 15 09:12 /dev/sda2
brw-rw---- 1 grid oinstall 8, 16 Dec 15 09:57 /dev/sdb
brw-rw---- 1 grid oinstall 8, 32 Dec 15 09:57 /dev/sdc
brw-rw---- 1 grid oinstall 8, 48 Dec 15 09:57 /dev/sdd
brw-rw---- 1 grid oinstall 8, 64 Dec 15 09:57 /dev/sde
brw-rw---- 1 grid oinstall 8, 80 Dec 15 09:57 /dev/sdf

Tuesday, December 14, 2010

Quick install rpms using YUM with local DVD Repo for Oracle database 11gR2


[root@unknown-08-00-27-07-fa-07 ~]# cat /etc/redhat-release
Red Hat Enterprise Linux Server release 5.5 (Tikanga)



[root@unknown-08-00-27-07-fa-07 ~]# mount -r -o loop -t iso9660 /dev/cdrom /mnt



[root@unknown-08-00-27-07-fa-07 ~]# cat /etc/yum.repos.d/dvd.repo
[dvd]
name=OEL5DVD
baseurl=file:///mnt/Server
enabled=1
gpgcheck=1
gpgkey=file:///mnt/RPM-GPG-KEY file:///mnt/RPM-GPG-KEY-oracle



[root@unknown-08-00-27-07-fa-07 ~]# yum clean all
Loaded plugins: security
Cleaning up Everything
[root@unknown-08-00-27-07-fa-07 ~]# yum update
Loaded plugins: security
dvd | 1.1 kB 00:00
dvd/primary | 818 kB 00:00
dvd 2334/2334
Skipping security plugin, no data
Setting up Update Process
No Packages marked for Update


[root@unknown-08-00-27-07-fa-07 ~]# yum install binutils compat-libstdc++-33 elfutils-libelf elfutils-libelf-devel elfutils-libelf-devel-static gcc gcc-c++ glibc glibc-common glibc-devel glibc-headers kernel-headers ksh libaio libaio-devel libgcc libgomp libstdc++ libstdc++-devel make sysstat unixODBC unixODBC-devel
Loaded plugins: security
Setting up Install Process
Package binutils-2.17.50.0.6-14.el5.i386 already installed and latest version
Package elfutils-libelf-0.137-3.el5.i386 already installed and latest version
Package glibc-2.5-49.i686 already installed and latest version
Package glibc-common-2.5-49.i386 already installed and latest version
Package ksh-20100202-1.el5.i386 already installed and latest version
Package libaio-0.3.106-5.i386 already installed and latest version
Package libgcc-4.1.2-48.el5.i386 already installed and latest version
Package libstdc++-4.1.2-48.el5.i386 already installed and latest version
Package 1:make-3.81-3.el5.i386 already installed and latest version
Resolving Dependencies
--> Running transaction check
---> Package compat-libstdc++-33.i386 0:3.2.3-61 set to be updated
---> Package elfutils-libelf-devel.i386 0:0.137-3.el5 set to be updated
---> Package elfutils-libelf-devel-static.i386 0:0.137-3.el5 set to be updated
---> Package gcc.i386 0:4.1.2-48.el5 set to be updated
---> Package gcc-c++.i386 0:4.1.2-48.el5 set to be updated
---> Package glibc-devel.i386 0:2.5-49 set to be updated
---> Package glibc-headers.i386 0:2.5-49 set to be updated
---> Package kernel-headers.i386 0:2.6.18-194.el5 set to be updated
---> Package libaio-devel.i386 0:0.3.106-5 set to be updated
---> Package libgomp.i386 0:4.4.0-6.el5 set to be updated
---> Package libstdc++-devel.i386 0:4.1.2-48.el5 set to be updated
dvd/filelists | 2.4 MB 00:00
---> Package sysstat.i386 0:7.0.2-3.el5 set to be updated
---> Package unixODBC.i386 0:2.2.11-7.1 set to be updated
---> Package unixODBC-devel.i386 0:2.2.11-7.1 set to be updated
--> Finished Dependency Resolution

Dependencies Resolved

================================================================================================
Package Arch Version Repository Size
================================================================================================
Installing:
compat-libstdc++-33 i386 3.2.3-61 dvd 232 k
elfutils-libelf-devel i386 0.137-3.el5 dvd 24 k
elfutils-libelf-devel-static i386 0.137-3.el5 dvd 66 k
gcc i386 4.1.2-48.el5 dvd 5.2 M
gcc-c++ i386 4.1.2-48.el5 dvd 3.4 M
glibc-devel i386 2.5-49 dvd 2.0 M
glibc-headers i386 2.5-49 dvd 614 k
kernel-headers i386 2.6.18-194.el5 dvd 1.1 M
libaio-devel i386 0.3.106-5 dvd 12 k
libgomp i386 4.4.0-6.el5 dvd 70 k
libstdc++-devel i386 4.1.2-48.el5 dvd 2.9 M
sysstat i386 7.0.2-3.el5 dvd 170 k
unixODBC i386 2.2.11-7.1 dvd 830 k
unixODBC-devel i386 2.2.11-7.1 dvd 743 k

Transaction Summary
================================================================================================
Install 14 Package(s)
Upgrade 0 Package(s)

Total download size: 17 M
Is this ok [y/N]: y
Downloading Packages:
------------------------------------------------------------------------------------------------
Total 708 MB/s | 17 MB 00:00
warning: rpmts_HdrFromFdno: Header V3 DSA signature: NOKEY, key ID 1e5e0159
dvd/gpgkey | 1.4 kB 00:00
Importing GPG key 0x1E5E0159 "Oracle OSS group (Open Source Software group) " from /mnt/RPM-GPG-KEY
Is this ok [y/N]: y
dvd/gpgkey | 1.4 kB 00:00
Running rpm_check_debug
Running Transaction Test
Finished Transaction Test
Transaction Test Succeeded
Running Transaction
Installing : libgomp 1/14
Installing : unixODBC 2/14
Installing : compat-libstdc++-33 3/14
Installing : sysstat 4/14
Installing : libstdc++-devel 5/14
Installing : kernel-headers 6/14
Installing : glibc-headers 7/14
Installing : glibc-devel 8/14
Installing : unixODBC-devel 9/14
Installing : libaio-devel 10/14
Installing : gcc 11/14
Installing : gcc-c++ 12/14
Installing : elfutils-libelf-devel 13/14
Installing : elfutils-libelf-devel-static 14/14

Installed:
compat-libstdc++-33.i386 0:3.2.3-61 elfutils-libelf-devel.i386 0:0.137-3.el5
elfutils-libelf-devel-static.i386 0:0.137-3.el5 gcc.i386 0:4.1.2-48.el5
gcc-c++.i386 0:4.1.2-48.el5 glibc-devel.i386 0:2.5-49
glibc-headers.i386 0:2.5-49 kernel-headers.i386 0:2.6.18-194.el5
libaio-devel.i386 0:0.3.106-5 libgomp.i386 0:4.4.0-6.el5
libstdc++-devel.i386 0:4.1.2-48.el5 sysstat.i386 0:7.0.2-3.el5
unixODBC.i386 0:2.2.11-7.1 unixODBC-devel.i386 0:2.2.11-7.1

Complete!

Configure DHCP Server on Linux

Server Part:
=========================
[root@rh5 ~]# rpm -q dhcp-3.0.5-23.el5_5.2
dhcp-3.0.5-23.el5_5.2

[root@rh5 ~]# vi /etc/dhcpd.conf
[root@rh5 ~]# cat /etc/dhcpd.conf
#
# DHCP Server Configuration file.
# see /usr/share/doc/dhcp*/dhcpd.conf.sample
#
ddns-update-style interim;
ignore client-updates;

subnet 192.168.1.0 netmask 255.255.255.0 {

# --- default gateway
range 192.168.1.200 192.168.1.210;
option routers 192.168.1.254;
option subnet-mask 255.255.255.0;

option nis-domain "lab.dbaglobe.com";
option domain-name "lab.dbaglobe.com";
option domain-name-servers 192.168.1.254;

# option time-offset -18000; # Eastern Standard Time
# option ntp-servers 192.168.1.1;
# option netbios-name-servers 192.168.1.1;
}



[root@rh5 ~]# service dhcpd restart
Starting dhcpd: [ OK ]


Client Part:
=========================
[root@unknown-08-00-27-07-fa-07 ~]# cat /etc/dhclient.conf
# 192.168.1.254 is one of the DHCP Server in the local network
# After reject it, the dhcp network can use 192.168.1.100 as DHCP Server
reject 192.168.1.254;
interface "eth0" {
send host-name "vmxdb02.lab.dbaglobe.com";
request subnet-mask, broadcast-address, time-offset, routers,
domain-name, domain-name-servers, host-name;
require subnet-mask, domain-name-servers;
}

[root@unknown-08-00-27-07-fa-07 ~]# dhclient
Internet Systems Consortium DHCP Client V3.0.5-RedHat
Copyright 2004-2006 Internet Systems Consortium.
All rights reserved.
For info, please visit http://www.isc.org/sw/dhcp/

Listening on LPF/eth0/08:00:27:07:fa:07
Sending on LPF/eth0/08:00:27:07:fa:07
Sending on Socket/fallback
DHCPREQUEST on eth0 to 255.255.255.255 port 67
DHCPACK from 192.168.1.254 rejected.
DHCPACK from 192.168.1.100
bound to 192.168.1.210 -- renewal in 19873 seconds.

[root@unknown-08-00-27-07-fa-07 ~]# ifconfig eth0
eth0 Link encap:Ethernet HWaddr 08:00:27:07:FA:07
inet addr:192.168.1.210 Bcast:192.168.1.255 Mask:255.255.255.0
inet6 addr: fe80::a00:27ff:fe07:fa07/64 Scope:Link
UP BROADCAST RUNNING MULTICAST MTU:1500 Metric:1
RX packets:15501 errors:0 dropped:0 overruns:0 frame:0
TX packets:2505 errors:0 dropped:0 overruns:0 carrier:0
collisions:0 txqueuelen:1000
RX bytes:1015295 (991.4 KiB) TX bytes:326697 (319.0 KiB)

Sunday, December 5, 2010

How to fix "file /dev/zero: mmap anon failed: Resource temporarily unavailable" (Solaris)

- Linking Oracle
rm -f /u01/app/oracle/product/11.2.0/db_1/rdbms/lib/oracle
/usr/ccs/bin/ld -o /u01/app/oracle/product/11.2.0/db_1/rdbms/lib/oracle -L/u01/app/oracle/product/11.2.0/db_1/rdbms/lib/ -L/u01/app/oracle/product/11.2.0/db_1/lib/ -dy /u01/app/oracle/product/11.2.0/db_1/lib/prod/lib/amd64/crti.o /u01/app/oracle/product/11.2.0/db_1/lib/prod/lib/amd64/crt1x.o /u01/app/oracle/product/11.2.0/db_1/rdbms/lib/opimai.o /u01/app/oracle/product/11.2.0/db_1/rdbms/lib/ssoraed.o /u01/app/oracle/product/11.2.0/db_1/rdbms/lib/ttcsoi.o -z allextract -lperfsrv11 -z defaultextract /u01/app/oracle/product/11.2.0/db_1/lib/nautab.o /u01/app/oracle/product/11.2.0/db_1/lib/naeet.o /u01/app/oracle/product/11.2.0/db_1/lib/naect.o /u01/app/oracle/product/11.2.0/db_1/lib/naedhs.o /u01/app/oracle/product/11.2.0/db_1/rdbms/lib/config.o -lserver11 -lodm11 -lcell11 -lnnet11 -lskgxp11 -lsnls11 -lnls11 -lcore11 -lsnls11 -lnls11 -lcore11 -lsnls11 -lnls11 -lxml11 -lcore11 -lunls11 -lsnls11 -lnls11 -lcore11 -lnls11 -lclient11 -lvsn11 -lcommon11 -lgeneric11 -lknlopt `if /usr/ccs/bin/ar tv /u01/app/oracle/product/11.2.0/db_1/rdbms/lib/libknlopt.a | grep xsyeolap.o > /dev/null 2>&1 ; then echo "-loraolap11" ; fi` -lslax11 -lpls11 -lplp11 -lserver11 -lclient11 -lvsn11 -lcommon11 -lgeneric11 `if [ -f /u01/app/oracle/product/11.2.0/db_1/lib/libavserver11.a ] ; then echo "-lavserver11" ; else echo "-lavstub11"; fi` `if [ -f /u01/app/oracle/product/11.2.0/db_1/lib/libavclient11.a ] ; then echo "-lavclient11" ; fi` -lknlopt -lslax11 -lpls11 -lplp11 -ljavavm11 -lserver11 -lwwg `cat /u01/app/oracle/product/11.2.0/db_1/lib/ldflags` -lncrypt11 -lnsgr11 -lnzjs11 -ln11 -lnl11 -lnro11 `cat /u01/app/oracle/product/11.2.0/db_1/lib/ldflags` -lncrypt11 -lnsgr11 -lnzjs11 -ln11 -lnl11 -lnnz11 -lzt11 -lztkg11 -lmm -lsnls11 -lnls11 -lcore11 -lsnls11 -lnls11 -lcore11 -lsnls11 -lnls11 -lxml11 -lcore11 -lunls11 -lsnls11 -lnls11 -lcore11 -lnls11 -lztkg11 `cat /u01/app/oracle/product/11.2.0/db_1/lib/ldflags` -lncrypt11 -lnsgr11 -lnzjs11 -ln11 -lnl11 -lnro11 `cat /u01/app/oracle/product/11.2.0/db_1/lib/ldflags` -lncrypt11 -lnsgr11 -lnzjs11 -ln11 -lnl11 -lnnz11 -lzt11 -lztkg11 -lsnls11 -lnls11 -lcore11 -lsnls11 -lnls11 -lcore11 -lsnls11 -lnls11 -lxml11 -lcore11 -lunls11 -lsnls11 -lnls11 -lcore11 -lnls11 `if /usr/ccs/bin/ar tv /u01/app/oracle/product/11.2.0/db_1/rdbms/lib/libknlopt.a | grep "kxmnsd.o" > /dev/null 2>&1 ; then echo " " ; else echo "-lordsdo11"; fi` -L/u01/app/oracle/product/11.2.0/db_1/ctx/lib/ -lctxc11 -lctx11 -lzx11 -lgx11 -lctx11 -lzx11 -lgx11 -lordimt11 -lclsra11 -ldbcfg11 -lhasgen11 -lskgxn2 -lnnz11 -lzt11 -lxml11 -locr11 -locrb11 -locrutl11 -lhasgen11 -lskgxn2 -lnnz11 -lzt11 -lxml11 -lasmclnt11 -lcommon11 -lcell11 -lskgxp11 -lgeneric11 -lcommon11 -lgeneric11 -loraz -llzopro -lorabz2 -lsnls11 -lnls11 -lcore11 -lsnls11 -lnls11 -lcore11 -lsnls11 -lnls11 -lxml11 -lcore11 -lunls11 -lsnls11 -lnls11 -lcore11 -lnls11 -lsnls11 -lunls11 -lsnls11 -lnls11 -lcore11 -lsnls11 -lnls11 -lcore11 -lsnls11 -lnls11 -lxml11 -lcore11 -lunls11 -lsnls11 -lnls11 -lcore11 -lnls11 -lasmclnt11 `cat /u01/app/oracle/product/11.2.0/db_1/lib/sysliblist` -R /opt/SUNWcluster/lib:/u01/app/oracle/product/11.2.0/db_1/lib:/opt/ORCLcluster/lib/ -Y P,:/opt/SUNWcluster/lib:/opt/ORCLcluster/lib/:/usr/ccs/lib/amd64:/usr/lib/amd64 -Qy -lc -lrt -laio -lposix4 -lkstat -lm /u01/app/oracle/product/11.2.0/db_1/lib/prod/lib/amd64/crtn.o -lpthread
ld: fatal: *** Error code 1
file /dev/zero: mmap anon failed: Resource temporarily unavailable
make: Fatal error: Command failed for target `/u01/app/oracle/product/11.2.0/db_1/rdbms/lib/oracle'


###########SetUp###########

###########Method 1###########


bash-3.00$ swap -s
total: 1333976k bytes allocated + 104980k reserved = 1438956k used, 661796k available

bash-3.00$ /usr/sbin/mkfile 2048m /u01/app/oracle_install_swap

bash-3.00$ swap -a /u01/app/oracle_install_swap
"/u01/app/oracle_install_swap" is not valid for swapping.
It must be a block device or a regular file with the
"save user text on execution" bit set.
bash-3.00# /usr/sbin/lofiadm -a /u01/app/oracle_install_swap
/dev/lofi/1
bash-3.00# ls -l /dev/lofi/1
lrwxrwxrwx 1 root root 29 Dec 5 12:27 /dev/lofi/1 -> ../../devices/pseudo/lofi@0:1
bash-3.00# swap -a /dev/lofi/1
bash-3.00# swap -s
total: 1334520k bytes allocated + 105104k reserved = 1439624k used, 2689628k available
bash-3.00#

###########Method 2###########

bash-3.00# /usr/sbin/mkfile 20m /u01/app/oracle_install_swap1
bash-3.00# chmod 1600 /u01/app/oracle_install_swap1
bash-3.00# ls -l /u01/app/oracle_install_swap1
-rw------T 1 root root 20971520 Dec 5 12:39 /u01/app/oracle_install_swap1
bash-3.00# swap -a /u01/app/oracle_install_swap1



bash-3.00# swap -l
swapfile dev swaplo blocks free
/dev/dsk/c1t0d0s1 30,1 8 2988080 348504
/dev/lofi/1 144,1 8 4194296 3909400
/u01/app/oracle_install_swap1 - 8 40952 15088



PID USERNAME SIZE RSS STATE PRI NICE TIME CPU PROCESS/NLWP
22733 oracle 1185M 571M sleep 60 0 0:01:35 6.5% ld/1
1705 oracle 118M 5572K sleep 59 0 0:01:25 1.5% java/15
964 noaccess 126M 6444K sleep 59 0 0:01:14 0.9% java/18


bash-3.00$ while true; do swap -s; sleep 1; done
total: 2021720k bytes allocated + 139628k reserved = 2161348k used, 2032852k available
total: 2021992k bytes allocated + 139356k reserved = 2161348k used, 2032852k available
total: 2022392k bytes allocated + 138956k reserved = 2161348k used, 2032852k available
total: 2022652k bytes allocated + 138696k reserved = 2161348k used, 2032868k available
total: 2023056k bytes allocated + 138292k reserved = 2161348k used, 2032864k available
total: 2023208k bytes allocated + 138140k reserved = 2161348k used, 2032852k available
total: 2023300k bytes allocated + 138048k reserved = 2161348k used, 2032852k available
total: 2023348k bytes allocated + 138000k reserved = 2161348k used, 2032844k available
total: 2023392k bytes allocated + 137956k reserved = 2161348k used, 2032840k available
total: 2023624k bytes allocated + 137724k reserved = 2161348k used, 2032844k available



###########Clean up###########

bash-3.00# lofiadm -d /dev/lofi/1
bash-3.00# ls -l /dev/lofi/1
/dev/lofi/1: No such file or directory
bash-3.00# rm /u01/app/oracle_install_swap

bash-3.00# swap -d /u01/app/oracle_install_swap1
bash-3.00# rm /u01/app/oracle_install_swap1
bash-3.00# swap -l
swapfile dev swaplo blocks free
/dev/dsk/c1t0d0s1 30,1 8 2988080 1775224
bash-3.00#

Configure Solaris 10 Network (Changing from DHCP to Static IP)

bash-3.00# rm /etc/dhcp.e1000g0
bash-3.00# cat /etc/hostname.e1000g0
10.0.2.15
bash-3.00# cat /etc/hosts
#
# Internet host table
#
::1 localhost
127.0.0.1 localhost
10.0.2.15 sol10 sol10.lab.dbaglobe.com

# cat /etc/nodename
sol10

bash-3.00# cat /etc/netmasks
#
# The netmasks file associates Internet Protocol (IP) address
# masks with IP network numbers.
#
# network-number netmask
#
# The term network-number refers to a number obtained from the Internet Network
# Information Center.
#
# Both the network-number and the netmasks are specified in
# "decimal dot" notation, e.g:
#
# 128.32.0.0 255.255.255.0
#
10.0.2.0 255.255.255.0

# cat /etc/defaultrouter
10.0.2.2

# cat /etc/resolv.conf
nameserver 202.126.40.5
nameserver 4.2.2.1

bash-3.00# init 6

Connect to VirtualBox Guest through NAT network (Solaris+ssh)

Here the e1000 can be replaced by pcnet, if you are using Windows/Linux guest with PCNet Type of ethernet card emulation.

And Solaris here is the Guest Name

C:\Program Files\Oracle\VirtualBox>VBoxManage getextradata "Solaris" enumerate
Oracle VM VirtualBox Command Line Management Interface Version 3.2.12
(C) 2005-2010 Oracle Corporation
All rights reserved.

Key: GUI/InfoDlgState, Value: 400,450,normal
Key: GUI/LastGuestSizeHint, Value: 720,400
Key: GUI/LastWindowPostion, Value: 265,101,720,442
Key: GUI/MiniToolBarAlignment, Value: bottom
Key: GUI/SaveMountedAtRuntime, Value: yes
Key: GUI/ShowMiniToolBar, Value: yes

C:\Program Files\Oracle\VirtualBox>VBoxManage setextradata "Solaris" "VBoxInternal/Devices/e1000/0/LUN#0/Config/ssh/HostPort" 2222
Oracle VM VirtualBox Command Line Management Interface Version 3.2.12
(C) 2005-2010 Oracle Corporation
All rights reserved.

C:\Program Files\Oracle\VirtualBox>VBoxManage setextradata "Solaris" "VBoxInternal/Devices/e1000/0/LUN#0/Config/ssh/GuestPort" 22
Oracle VM VirtualBox Command Line Management Interface Version 3.2.12
(C) 2005-2010 Oracle Corporation
All rights reserved.


C:\Program Files\Oracle\VirtualBox>VBoxManage setextradata "Solaris" "VBoxInternal/Devices/e1000/0/LUN#0/Config/ssh/Protocol" TCP
Oracle VM VirtualBox Command Line Management Interface Version 3.2.12
(C) 2005-2010 Oracle Corporation
All rights reserved.


C:\Program Files\Oracle\VirtualBox>VBoxManage getextradata "Solaris" enumerate
Oracle VM VirtualBox Command Line Management Interface Version 3.2.12
(C) 2005-2010 Oracle Corporation
All rights reserved.

Key: GUI/InfoDlgState, Value: 400,450,normal
Key: GUI/LastGuestSizeHint, Value: 720,400
Key: GUI/LastWindowPostion, Value: 265,101,720,442
Key: GUI/MiniToolBarAlignment, Value: bottom
Key: GUI/SaveMountedAtRuntime, Value: yes
Key: GUI/ShowMiniToolBar, Value: yes
Key: VBoxInternal/Devices/e1000/0/LUN#0/Config/ssh/GuestPort, Value: 22
Key: VBoxInternal/Devices/e1000/0/LUN#0/Config/ssh/HostPort, Value: 2222
Key: VBoxInternal/Devices/e1000/0/LUN#0/Config/ssh/Protocol, Value: TCP

C:\Program Files\Oracle\VirtualBox>netstat -na|findstr 22
TCP 120.28.9.137:3305 61.18.190.228:443 ESTABLISHED



C:\Program Files\Oracle\VirtualBox>netstat -na|findstr 22
TCP 0.0.0.0:2222 0.0.0.0:0 LISTENING
TCP 120.28.9.137:3305 61.18.190.228:443 ESTABLISHED

Friday, November 19, 2010

Another example to prove using index, rather than full table scan to delete large amount of data

The envrionment is Redhat 5.3 + Oracle 10.2.0.4 (x64) + Dell PowerEdge 2950 + EMC Clarrion

Click here to refer to original test case on PC Server: http://www.dbaglobe.com/2010/11/deleting-large-amount-of-data-using.html


SQL> create table large1
2 (id number,
3 char20 varchar2(20),
4 char180 char(180))
5 tablespace assm;

Table created.

SQL> create table large2
2 (id number,
3 char20 varchar2(20),
4 char180 char(180))
5 tablespace assm;

Table created.

SQL> set timing on
SQL> begin
2 for i in 1..4000000
3 loop
4 insert into large1 values (i,dbms_random.string('X',20),' ');
5 insert into large2 values (i,dbms_random.string('X',20),' ');
6 if mod(i,1000)=0 then
7 commit;
8 end if;
9 end loop;
10 end;
11 /

PL/SQL procedure successfully completed.

Elapsed: 00:08:43.77
SQL> create unique index large1_pk on large1(id);

Index created.

Elapsed: 00:00:43.64
SQL> create unique index large2_pk on large2(id);

Index created.

Elapsed: 00:00:43.66
SQL> create index large1_n1 on large1(char20);

Index created.

Elapsed: 00:00:43.47
SQL> create index large2_n1 on large2(char20);

Index created.

Elapsed: 00:01:00.06
SQL> exec dbms_stats.gather_schema_stats('');

PL/SQL procedure successfully completed.

Elapsed: 00:03:25.59
SQL> set timing off
SQL> col segsize format 999,999,999,999
SQL> col segment_name for a30
SQL> select segment_name,sum(bytes) segsize from user_extents
2 where segment_name like 'LARGE%'
3 group by segment_name
4 order by segment_name;

SEGMENT_NAME SEGSIZE
------------------------------ ----------------
LARGE1 973,078,528
LARGE1_N1 149,946,368
LARGE1_PK 75,497,472
LARGE2 973,078,528
LARGE2_N1 149,946,368
LARGE2_PK 75,497,472

6 rows selected.

SQL> explain plan for
2 delete from large1 where id<1000000;

Explained.

SQL> select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 1040529653

-------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------
| 0 | DELETE STATEMENT | | 1000K| 25M| 2256 (1)| 00:00:28 |
| 1 | DELETE | LARGE1 | | | | |
|* 2 | INDEX RANGE SCAN| LARGE1_PK | 1000K| 25M| 2256 (1)| 00:00:28 |
-------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

2 - access("ID"<1000000)

14 rows selected.

SQL> explain plan for
2 delete /*+ full(t) */ from large2 t where id<1000000;

Explained.

SQL> select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 2228625945

-----------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------
| 0 | DELETE STATEMENT | | 999K| 25M| 25917 (1)| 00:05:12 |
| 1 | DELETE | LARGE2 | | | | |
|* 2 | TABLE ACCESS FULL| LARGE2 | 999K| 25M| 25917 (1)| 00:05:12 |
-----------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

2 - filter("ID"<1000000)

14 rows selected.

SQL> alter system flush buffer_cache;

System altered.

SQL> set timing on
SQL> delete from large1 where id<1000000;

999999 rows deleted.

Elapsed: 00:03:05.79
SQL> commit;

Commit complete.

Elapsed: 00:00:00.00
SQL> delete /*+ full(t) */ from large2 t where id<1000000;

999999 rows deleted.

Elapsed: 00:05:15.56
SQL> commit;

Commit complete.

Elapsed: 00:00:00.01

Deleting large amount of data using index or full table scan?

Setup:

[11gr2@rh5 ~]$ uname -snrio
Linux rh5.lab.dbaglobe.com 2.6.18-194.26.1.el5 i386 GNU/LinuxTable created.


SQL> create table large1
2 (id number,
3 char20 varchar2(20),
4 char180 char(180))
5 tablespace assm;

Table created.

SQL> create table large2
2 (id number,
3 char20 varchar2(20),
4 char180 char(180))
5 tablespace assm;

Table created.

SQL> begin
2 for i in 1..4000000
3 loop
4 insert into large1 values (i,dbms_random.string('X',20),' ');
5 insert into large2 values (i,dbms_random.string('X',20),' ');
6 if mod(i,1000)=0 then
7 commit;
8 end if;
9 end loop;
10 end;
11 /

PL/SQL procedure successfully completed.

SQL> create unique index large1_pk on large1(id);

Index created.

SQL> create unique index large2_pk on large2(id);

Index created.

SQL> create index large1_n1 on large1(char20);

Index created.

SQL> create index large2_n1 on large2(char20);

Index created.

SQL> exec dbms_stats.gather_schema_stats('');

PL/SQL procedure successfully completed.

SQL> col segsize format 999,999,999,999
SQL> col segment_name for a30

SQL> select segment_name,sum(bytes) segsize from user_extents
2 where segment_name like 'LARGE%'
3 group by segment_name
4 order by segment_name;

SEGMENT_NAME SEGSIZE
------------------------------ ----------------
LARGE1 964,689,920
LARGE1_N1 150,994,944
LARGE1_PK 75,497,472
LARGE2 964,689,920
LARGE2_N1 150,994,944
LARGE2_PK 75,497,472

6 rows selected.


Round 1:

SQL> explain plan for
2 delete from large1 where id<1000000;

Explained.

SQL> select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 1040529653

-------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------
| 0 | DELETE STATEMENT | | 999K| 25M| 2230 (1)| 00:00:27 |
| 1 | DELETE | LARGE1 | | | | |
|* 2 | INDEX RANGE SCAN| LARGE1_PK | 999K| 25M| 2230 (1)| 00:00:27 |
-------------------------------------------------------------------------------

Predicate Information (identified by operation id):

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
---------------------------------------------------

2 - access("ID"<1000000)

14 rows selected.

SQL> explain plan for
2 delete /*+ full(t) */ from large2 t where id<1000000;

Explained.

SQL> select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 2228625945

-----------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------
| 0 | DELETE STATEMENT | | 999K| 25M| 31892 (1)| 00:06:23 |
| 1 | DELETE | LARGE2 | | | | |
|* 2 | TABLE ACCESS FULL| LARGE2 | 999K| 25M| 31892 (1)| 00:06:23 |
-----------------------------------------------------------------------------

Predicate Information (identified by operation id):

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
---------------------------------------------------

2 - filter("ID"<1000000)

14 rows selected.

SQL> set timing on
SQL> delete from large1 where id<1000000;

999999 rows deleted.

Elapsed: 00:03:23.35
SQL> commit;

Commit complete.

Elapsed: 00:00:00.01
SQL> delete /*+ full(t) */ from large2 t where id<1000000;

999999 rows deleted.

Elapsed: 00:40:21.74

SQL> commit;

Commit complete.


Round 2:

SQL> alter system flush buffer_cache;

System altered.

Elapsed: 00:00:29.88
SQL> exec dbms_stats.gather_schema_stats('');

PL/SQL procedure successfully completed.

Elapsed: 00:02:11.85

SQL> set timing off

SQL> explain plan for
2 delete from large1 where id<2000000;

Explained.

SQL> select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 1040529653

-------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------
| 0 | DELETE STATEMENT | | 1000K| 25M| 2249 (1)| 00:00:27 |
| 1 | DELETE | LARGE1 | | | | |
|* 2 | INDEX RANGE SCAN| LARGE1_PK | 1000K| 25M| 2249 (1)| 00:00:27 |
-------------------------------------------------------------------------------

Predicate Information (identified by operation id):

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
---------------------------------------------------

2 - access("ID"<2000000)

14 rows selected.

SQL> explain plan for
2 delete /*+ full(t) */ from large2 t where id<2000000;

Explained.

SQL> select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 2228625945

-----------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------
| 0 | DELETE STATEMENT | | 1000K| 25M| 31874 (1)| 00:06:23 |
| 1 | DELETE | LARGE2 | | | | |
|* 2 | TABLE ACCESS FULL| LARGE2 | 1000K| 25M| 31874 (1)| 00:06:23 |
-----------------------------------------------------------------------------

Predicate Information (identified by operation id):

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
---------------------------------------------------

2 - filter("ID"<2000000)

14 rows selected.

SQL> set timing on
SQL> alter system flush buffer_cache;

System altered.

SQL> delete from large1 where id<2000000;

1000000 rows deleted.

Elapsed: 00:03:35.38
SQL> commit;

Commit complete.

Elapsed: 00:00:00.01
SQL> delete /*+ full(t) */ from large2 t where id<2000000;

1000000 rows deleted.

Elapsed: 00:45:13.68
SQL> commit;

Commit complete.

Elapsed: 00:00:00.02

SQL> select /*+ full(t) */ count(*) from large2 t;

COUNT(*)
----------
1979001

Elapsed: 00:00:14.37


Round 3: table without index

SQL> drop index large2_pk;

Index dropped.

Elapsed: 00:00:00.86

SQL> drop index large2_n1;

Index dropped.

Elapsed: 00:00:00.36

SQL> alter system flush buffer_cache;

System altered.

Elapsed: 00:00:00.25

SQL> delete /*+ full(t) */ from large2 t where id<3000000;

1000000 rows deleted.

Elapsed: 00:02:50.25

SQL> commit;

Commit complete.

Elapsed: 00:00:00.01

Wednesday, November 17, 2010

Loopback database link & ORA-02082: a loopback database link must have a connection qualifier


SQL> show parameter global_names

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
global_names boolean TRUE



SQL> create database link orcl.local connect to donghua identified by donghua using 'orcl';

Database link created.

SQL> select * from dual@orcl.local;
select * from dual@orcl.local
*
ERROR at line 1:
ORA-02085: database link ORCL.LOCAL connects to ORCL.LAB.DBAGLOBE.COM


SQL> drop database link orcl.local;

Database link dropped.



SQL> create database link orcl.lab.dbaglobe.com connect to donghua identified by donghua using 'orcl';
create database link orcl.lab.dbaglobe.com connect to donghua identified by donghua using 'orcl'
*
ERROR at line 1:
ORA-02082: a loopback database link must have a connection qualifier



SQL> create database link orcl.lab.dbaglobe.com@loopback
2 connect to donghua identified by donghua using 'orcl';

Database link created.

SQL> select instance_name from v$instance@orcl.lab.dbaglobe.com@loopback;

INSTANCE_NAME
----------------
orcl

SQL> drop database link orcl.lab.dbaglobe.com@loopback;

Database link dropped.



SQL> create database link orcl.lab.dbaglobe.com@l
2 connect to donghua identified by donghua using 'orcl';

Database link created.

SQL> select instance_name from v$instance@orcl.lab.dbaglobe.com@l;

INSTANCE_NAME
----------------
orcl

SQL> drop database link orcl.lab.dbaglobe.com@l
2 ;

Database link dropped.

Tuesday, November 16, 2010

Instance Caging in Oracle database

About Instance Caging

You might decide to run multiple Oracle database instances on a single multi-CPU server. A typical reason to do so would be server consolidation—using available hardware resources more efficiently. When running multiple instances on a single server, the instances compete for CPU. One resource-intensive database instance could significantly degrade the performance of the other instances. For example, on a 16-CPU system with four database instances, the operating system might be running one database instance on the majority of the CPUs during a period of heavy load for that instance. This could degrade performance in the other three instances. CPU allocation decisions such as this are made solely by the operating system; the user generally has no control over them.

A simple way to limit CPU consumption for each database instance is to use instance caging. Instance caging is a method that uses an initialization parameter to limit the number of CPUs that an instance can use simultaneously. In the previous example, if you use instance caging to limit the number of CPUs to four for each of the four instances, there is less likelihood that one instance can interfere with the others. When constrained to four CPUs, an instance might become CPU-bound. This is when the Resource Manager begins to do its work to allocate CPU among the various database sessions according to the resource plan that you set for the instance. Thus, instance caging and the Resource Manager together provide a simple, effective way to manage multiple instances on a single server.

Examples:



Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> conn donghua/donghua
Connected.
SQL> set echo on
SQL> @resource_plan.sql
SQL> BEGIN
2 DBMS_RESOURCE_MANAGER.CREATE_PENDING_AREA();
3
4 DBMS_RESOURCE_MANAGER.CREATE_PLAN(
5 PLAN => 'MAXCAP_PLAN',
6 COMMENT => 'Limit overall database CPU');
7
8 DBMS_RESOURCE_MANAGER.CREATE_PLAN_DIRECTIVE(
9 PLAN => 'MAXCAP_PLAN',
10 GROUP_OR_SUBPLAN => 'OTHER_GROUPS',
11 COMMENT => 'This group is mandatory',
12 MAX_UTILIZATION_LIMIT => 90);
13
14 DBMS_RESOURCE_MANAGER.VALIDATE_PENDING_AREA();
15 DBMS_RESOURCE_MANAGER.SUBMIT_PENDING_AREA();
16 END;
17 /

PL/SQL procedure successfully completed.

SQL> alter system set resource_manager_plan='';

System altered.

SQL> select name,cpu_managed,instance_caging from v$rsrc_plan;

NAME CPU INS
-------------------------------- --- ---
INTERNAL_PLAN OFF OFF

SQL> alter system set resource_manager_plan=MAXCAP_PLAN;

System altered.

SQL> alter system set cpu_count=2;

System altered.

SQL> select name,cpu_managed,instance_caging from v$rsrc_plan;

NAME CPU INS
-------------------------------- --- ---
MAXCAP_PLAN ON ON

Monday, November 15, 2010

ORA-01502, constraint, unique index and skip_unusable_indexes=true

This example shows the difference between unique indexes and non-unique indexes used to enforce primary key/unique constraints.

beside this, the only way to enable primary key/unique constraint to deferred is using non-unique index.


SQL> define _editor=vi
SQL> create table emp (
2 empno number(5),
3 ssn number(5),
4 constraint emp_pk primary key(empno) using index
5 (create unique index emp_pk on emp(empno) tablespace users),
6 constraint emp_unique_ssn unique(ssn) using index
7 (create unique index emp_u1 on emp(ssn) tablespace users)
8* )
9 /

Table created.

SQL> alter index emp_pk unusable;

Index altered.

SQL>
SQL>

SQL> insert into emp (empno) select employee_id from hr.employees;
insert into emp (empno) select employee_id from hr.employees
*
ERROR at line 1:
ORA-01502: index 'DONGHUA.EMP_PK' or partition of such index is in unusable
state


SQL> alter table emp disable constraint emp_pk keep index;

Table altered.

SQL> insert into emp (empno) select employee_id from hr.employees;
insert into emp (empno) select employee_id from hr.employees
*
ERROR at line 1:
ORA-01502: index 'DONGHUA.EMP_PK' or partition of such index is in unusable
state


SQL>
SQL> show parameter index

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
optimizer_index_caching integer 0
optimizer_index_cost_adj integer 100
optimizer_use_invisible_indexes boolean FALSE
skip_unusable_indexes boolean TRUE
SQL> drop index emp_pk;

Index dropped.

SQL> create index emp_pk on emp(empno) unusable;

Index created.

SQL> insert into emp (empno) select employee_id from hr.employees;

107 rows created.

SSQL> commit;

Commit complete.

SQL> alter table emp enable constraint emp_pk;
alter table emp enable constraint emp_pk
*
ERROR at line 1:
ORA-14063: Unusable index exists on unique/primary constraint key


SQL> alter index emp_pk rebuild;

Index altered.

SQL> alter table emp enable constraint emp_pk;

Table altered.

SQL> truncate table emp;

Table truncated.

SQL> alter index emp_pk unusable;

Index altered.

SQL> insert into emp (empno) select employee_id from hr.employees;
insert into emp (empno) select employee_id from hr.employees
*
ERROR at line 1:
ORA-01502: index 'DONGHUA.EMP_PK' or partition of such index is in unusable
state


SQL> alter table emp disable constraint emp_pk keep index;

Table altered.

SQL> insert into emp (empno) select employee_id from hr.employees;

107 rows created.

SQL>

Examples: Specifying the index associated with a constraint


donghuas-MacBook:~ donghua$ sqlplus donghua/donghua@orcl @create_emp1

SQL*Plus: Release 10.2.0.4.0 - Production on Mon Nov 15 22:37:46 2010

Copyright (c) 1982, 2007, Oracle. All Rights Reserved.


Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL>
SQL> create table emp (
2 empno number(5) primary key,
3 ssn number(5))
4 enable primary key using index (create unique index emp_pk on emp(empno) tablespace users)
5 /

Table created.

SQL>
SQL> select index_name,constraint_name from user_constraints where table_name='EMP';

INDEX_NAME CONSTRAINT_NAME
------------------------------ ------------------------------
EMP_PK SYS_C0012761

SQL> drop table emp purge;

Table dropped.

SQL>
SQL>
SQL> create table emp (
2 empno number(5) primary key,
3 ssn number(5))
4 enable primary key using index tablespace users
5 /

Table created.

SQL>
SQL> select index_name,constraint_name from user_constraints where table_name='EMP';

INDEX_NAME CONSTRAINT_NAME
------------------------------ ------------------------------
SYS_C0012762 SYS_C0012762

SQL>
SQL> drop table emp purge;

Table dropped.

SQL>
SQL>
SQL> create table emp (
2 empno number(5),
3 ssn number(5),
4 constraint emp_pk primary key(empno)
5 )
6 /

Table created.

SQL>
SQL> select index_name,constraint_name from user_constraints where table_name='EMP';

INDEX_NAME CONSTRAINT_NAME
------------------------------ ------------------------------
EMP_PK EMP_PK

SQL> drop table emp purge;

Table dropped.

SQL>
SQL> create table emp (
2 empno number(5),
3 ssn number(5),
4 constraint emp_pk primary key(empno) using index
5 (create unique index emp_pk on emp(empno) tablespace users)
6 )
7 /

Table created.

SQL>
SQL> select index_name,constraint_name from user_constraints where table_name='EMP';

INDEX_NAME CONSTRAINT_NAME
------------------------------ ------------------------------
EMP_PK EMP_PK

SQL> drop table emp purge;

Table dropped.

SQL>
SQL>
SQL> create table emp (
2 empno number(5),
3 ssn number(5),
4 constraint emp_pk primary key(empno) using index
5 (create unique index emp_pk on emp(empno) tablespace users),
6 constraint emp_unique_ssn unique(ssn) using index
7 (create unique index emp_u1 on emp(ssn) tablespace users)
8 )
9 /

Table created.

SQL>
SQL> select index_name,constraint_name from user_constraints where table_name='EMP';

INDEX_NAME CONSTRAINT_NAME
------------------------------ ------------------------------
EMP_PK EMP_PK
EMP_U1 EMP_UNIQUE_SSN

SQL> drop table emp purge;

Table dropped.

SQL>
SQL> exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

Sunday, November 14, 2010

Deferred segment creation and dbms_space_admin.materialize_deferred_segments


SQL> select * from v$version;

BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - Production
PL/SQL Release 11.2.0.2.0 - Production
CORE 11.2.0.2.0 Production
TNS for Linux: Version 11.2.0.2.0 - Production
NLSRTL Version 11.2.0.2.0 - Production
SQL> show parameter compatible

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
compatible string 11.2.0
SQL> show parameter deferred

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
deferred_segment_creation boolean TRUE




SQL> create table t1 (id number);

Table created.

SQL> create table t2 (id number);

Table created.

SQL> create index t1_pk on t1(id);

Index created.
SQL> create table t3 (doctype clob) lob(doctype) store as securefile;

Table created.

SQL> select table_name,segment_created from user_tables;

TABLE_NAME SEG
------------------------------ ---
T1 NO
T2 NO
T3 NO

SQL> select segment_name from user_segments;

no rows selected





SQL> begin
2 dbms_space_admin.materialize_deferred_segments(schema_name=>'DONGHUA');
3 end;
4 /
dbms_space_admin.materialize_deferred_segments(schema_name=>'DONGHUA');
*
ERROR at line 2:
ORA-06550: line 2, column 1:
PLS-00201: identifier 'DBMS_SPACE_ADMIN' must be declared
ORA-06550: line 2, column 1:
PL/SQL: Statement ignored

SQL> conn / as sysdba
Connected.
SQL> begin
2 dbms_space_admin.materialize_deferred_segments(schema_name=>'DONGHUA');
3 end;
4 /

PL/SQL procedure successfully completed.




SQL> conn donghua/donghua
Connected.
SQL> select table_name,segment_created from user_tables;

TABLE_NAME SEG
------------------------------ ---
T1 YES
T2 YES
T3 YES

SQL> select segment_name from user_segments;

SEGMENT_NAME
--------------------------------------------------------------------------------
T1
T2
T3
T1_PK
SYS_IL0000021560C00001$$
SYS_LOB0000021560C00001$$

6 rows selected.

Oracle compression table restrictions


SQL> select * from v$version;

BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - Production
PL/SQL Release 11.2.0.2.0 - Production
CORE 11.2.0.2.0 Production
TNS for Linux: Version 11.2.0.2.0 - Production
NLSRTL Version 11.2.0.2.0 - Production



SQL> create table compress_table (id number, name varchar2(30)) compress basic;

Table created.

SQL> insert into compress_table select object_id,object_name from dba_objects;

17392 rows created.

SQL> commit;

Commit complete.
SQL> select table_name,pct_free from user_tables
2 order by pct_free;

TABLE_NAME PCT_FREE
------------------------------ ----------
COMPRESS_TABLE 0
DEPARTMENTS 10
TEST_CHAINED_TABLE 10
CHAINED_ROWS 10
T1 10
EMPLOYEES 10



SQL> alter table compress_table add (col3 varchar2(20));

Table altered.

SQL> alter table compress_table add (col4 varchar2(20) default 'col4');
alter table compress_table add (col4 varchar2(20) default 'col4')
*
ERROR at line 1:
ORA-39726: unsupported add/drop column operation on compressed tables


SQL> alter table compress_table drop column col3;
alter table compress_table drop column col3
*
ERROR at line 1:
ORA-39726: unsupported add/drop column operation on compressed tables


SQL> alter table compress_table nocompress;

Table altered.

SQL> alter table compress_table drop column col3;
alter table compress_table drop column col3
*
ERROR at line 1:
ORA-39726: unsupported add/drop column operation on compressed tables



SQL> alter table compress_table shrink space;
alter table compress_table shrink space
*
ERROR at line 1:
ORA-10635: Invalid segment or tablespace type

Claim freespace using shrink and shrink space compact



SQL> select * from v$version;

BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - Production
PL/SQL Release 11.2.0.2.0 - Production
CORE 11.2.0.2.0 Production
TNS for Linux: Version 11.2.0.2.0 - Production
NLSRTL Version 11.2.0.2.0 - Production


SQL> insert into test_chained_table
2 select object_id, rpad(object_name,1000,' ') from dba_objects;

17392 rows created.

SQL> commit;

Commit complete.

SQL> select sum(bytes) from user_extents where segment_name='TEST_CHAINED_TABLE';

SUM(BYTES)
----------
75497472

SQL>


SQL> set serveroutput on
SQL> @show_space_table.sql
SQL> DECLARE
2 p_segname VARCHAR2 (30);
3 p_owner VARCHAR2 (30) DEFAULT USER;
4 p_type VARCHAR2 (30) DEFAULT 'TABLE';
5 p_partition VARCHAR2 (30) DEFAULT NULL;
6 l_free_blks NUMBER;
7 l_total_blocks NUMBER;
8 l_total_bytes NUMBER;
9 l_unused_blocks NUMBER;
10 l_unused_bytes NUMBER;
11 l_lastusedextfileid NUMBER;
12 l_lastusedextblockid NUMBER;
13 l_last_used_block NUMBER;
14 l_segment_space_mgmt VARCHAR2 (255);
15 l_unformatted_blocks NUMBER;
16 l_unformatted_bytes NUMBER;
17 l_fs1_blocks NUMBER;
18 l_fs1_bytes NUMBER;
19 l_fs2_blocks NUMBER;
20 l_fs2_bytes NUMBER;
21 l_fs3_blocks NUMBER;
22 l_fs3_bytes NUMBER;
23 l_fs4_blocks NUMBER;
24 l_fs4_bytes NUMBER;
25 l_full_blocks NUMBER;
26 l_full_bytes NUMBER;
27
28 PROCEDURE p (p_label IN VARCHAR2, p_num IN NUMBER)
29 IS
30 BEGIN
31 DBMS_OUTPUT.put_line ( RPAD (p_label, 40, '.')
32 || TO_CHAR (p_num, '999,999,999,999')
33 );
34 END;
35 BEGIN
36 p_segname := 'TEST_CHAINED_TABLE';
37 p_owner := 'DONGHUA';
38 p_type := 'TABLE';
39
40 IF p_partition IS NOT NULL
41 THEN
42 SELECT ts.segment_space_management
43 INTO l_segment_space_mgmt
44 FROM dba_segments seg, dba_tablespaces ts
45 WHERE seg.segment_name = p_segname
46 AND seg.partition_name = p_partition
47 AND seg.owner = p_owner
48 AND seg.tablespace_name = ts.tablespace_name;
49 ELSE
50 SELECT ts.segment_space_management
51 INTO l_segment_space_mgmt
52 FROM dba_segments seg, dba_tablespaces ts
53 WHERE seg.segment_name = p_segname
54 AND seg.owner = p_owner
55 AND seg.tablespace_name = ts.tablespace_name;
56 END IF;
57
58 IF l_segment_space_mgmt = 'AUTO'
59 THEN
60 DBMS_SPACE.space_usage (p_owner,
61 p_segname,
62 p_type,
63 l_unformatted_blocks,
64 l_unformatted_bytes,
65 l_fs1_blocks,
66 l_fs1_bytes,
67 l_fs2_blocks,
68 l_fs2_bytes,
69 l_fs3_blocks,
70 l_fs3_bytes,
71 l_fs4_blocks,
72 l_fs4_bytes,
73 l_full_blocks,
74 l_full_bytes,
75 p_partition
76 );
77 --
78 p ('Unformatted Blocks ', l_unformatted_blocks);
79 p ('FS1 Blocks (0-25) ', l_fs1_blocks);
80 p ('FS2 Blocks (25-50) ', l_fs2_blocks);
81 p ('FS3 Blocks (50-75) ', l_fs3_blocks);
82 p ('FS4 Blocks (75-100)', l_fs4_blocks);
83 p ('Full Blocks ', l_full_blocks);
84 ELSE
85 DBMS_SPACE.free_blocks (segment_owner => p_owner,
86 segment_name => p_segname,
87 segment_type => p_type,
88 freelist_group_id => 0,
89 free_blks => l_free_blks,
90 partition_name => p_partition
91 );
92 --
93 p ('Free Blocks', l_free_blks);
94 END IF;
95
96 DBMS_SPACE.unused_space (segment_owner => p_owner,
97 segment_name => p_segname,
98 segment_type => p_type,
99 partition_name => p_partition,
100 total_blocks => l_total_blocks,
101 total_bytes => l_total_bytes,
102 unused_blocks => l_unused_blocks,
103 unused_bytes => l_unused_bytes,
104 last_used_extent_file_id => l_lastusedextfileid,
105 last_used_extent_block_id => l_lastusedextblockid,
106 last_used_block => l_last_used_block
107 );
108 p ('Total Blocks', l_total_blocks);
109 p ('Total Bytes', l_total_bytes);
110 p ('Total MBytes', TRUNC (l_total_bytes / 1024 / 1024));
111 p ('Unused Blocks', l_unused_blocks);
112 p ('Unused Bytes', l_unused_bytes);
113 p ('Last Used Ext FileId', l_lastusedextfileid);
114 p ('Last Used Ext BlockId', l_lastusedextblockid);
115 p ('Last Used Block', l_last_used_block);
116 END;
117 /
Unformatted Blocks ..................... 748
FS1 Blocks (0-25) ..................... 1
FS2 Blocks (25-50) ..................... 1
FS3 Blocks (50-75) ..................... 0
FS4 Blocks (75-100)..................... 60
Full Blocks ..................... 8,267
Total Blocks............................ 9,216
Total Bytes............................. 75,497,472
Total MBytes............................ 72
Unused Blocks........................... 0
Unused Bytes............................ 0
Last Used Ext FileId.................... 4
Last Used Ext BlockId................... 2,688
Last Used Block......................... 1,024

PL/SQL procedure successfully completed.

SQL>


SQL> delete from test_chained_table where mod(id,3)=0;

11589 rows deleted.

SQL> commit;

Commit complete.

SQL> set echo off
SQL> @show_space_table.sql
Unformatted Blocks ..................... 748
FS1 Blocks (0-25) ..................... 0
FS2 Blocks (25-50) ..................... 7,742
FS3 Blocks (50-75) ..................... 254
FS4 Blocks (75-100)..................... 62
Full Blocks ..................... 271
Total Blocks............................ 9,216
Total Bytes............................. 75,497,472
Total MBytes............................ 72
Unused Blocks........................... 0
Unused Bytes............................ 0
Last Used Ext FileId.................... 4
Last Used Ext BlockId................... 2,688
Last Used Block......................... 1,024

PL/SQL procedure successfully completed.

SQL> select sum(bytes) from user_extents where segment_name='TEST_CHAINED_TABLE';

SUM(BYTES)
----------
75497472

SQL> alter table test_chained_table shrink space compact;
alter table test_chained_table shrink space compact
*
ERROR at line 1:
ORA-10636: ROW MOVEMENT is not enabled


SQL> alter table test_chained_table enable row movement;

Table altered.

SQL> alter table test_chained_table shrink space compact;

Table altered.

SQL> select sum(bytes) from user_extents where segment_name='TEST_CHAINED_TABLE';

SUM(BYTES)
----------
75497472

SQL> @show_space_table.sql
Unformatted Blocks ..................... 748
FS1 Blocks (0-25) ..................... 0
FS2 Blocks (25-50) ..................... 1
FS3 Blocks (50-75) ..................... 0
FS4 Blocks (75-100)..................... 3,169
Full Blocks ..................... 5,159
Total Blocks............................ 9,216
Total Bytes............................. 75,497,472
Total MBytes............................ 72
Unused Blocks........................... 0
Unused Bytes............................ 0
Last Used Ext FileId.................... 4
Last Used Ext BlockId................... 2,688
Last Used Block......................... 1,024

PL/SQL procedure successfully completed.

SQL> alter table test_chained_table shrink space;

Table altered.

SQL> select sum(bytes) from user_extents where segment_name='TEST_CHAINED_TABLE';

SUM(BYTES)
----------
43057152

SQL> @show_space_table.sql
Unformatted Blocks ..................... 0
FS1 Blocks (0-25) ..................... 0
FS2 Blocks (25-50) ..................... 1
FS3 Blocks (50-75) ..................... 0
FS4 Blocks (75-100)..................... 0
Full Blocks ..................... 5,159
Total Blocks............................ 5,256
Total Bytes............................. 43,057,152
Total MBytes............................ 41
Unused Blocks........................... 4
Unused Bytes............................ 32,768
Last Used Ext FileId.................... 4
Last Used Ext BlockId................... 11,648
Last Used Block......................... 4

PL/SQL procedure successfully completed.

SQL> alter table test_chained_table disable row movement;

Table altered.

SQL>

Sunday, November 7, 2010

Use SQL to check datafile highwater mark for resizing


select t.file_id,t.file_name,
t.tablespace_name,
round(t.bytes/1024/1024) mbytes,
autoextensible,
round(hw_mark/1024/1024) hw_mark,
round((t.bytes-hw_mark)/1024/1024) resizable,
round((f.free_bytes)/1024/1024) free_mbytes
from dba_data_files t,
(select file_id, max(block_id+blocks)*8192
hw_mark from dba_extents group by file_id) w,
(select file_id,sum(bytes) free_bytes
from dba_free_space group by file_id) f
where t.file_id=w.file_id
and t.file_id=f.file_id(+)

How to fix "ORA-08104 : this index object 75350 is being online built or rebuilt"


$ sqlplus donghua2/donghua2

SQL*Plus: Release 10.2.0.4.0 - Production on Wed Sep 22 18:40:36 2010

Copyright (c) 1982, 2007, Oracle. All Rights Reserved.


Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Production
With the Partitioning, Real Application Clusters, OLAP, Data Mining
and Real Application Testing options

SQL> alter index APP_OWNER.SYS_C007740 rebuild online tablespace sec_data01;
alter index APP_OWNER.SYS_C007740 rebuild online tablespace sec_data01
*
ERROR at line 1:
ORA-08104: this index object 75350 is being online built or rebuilt




Errors in file /u01/app/oracle/admin/appprd/udump/appprd1_ora_25717.trc:
ORA-00600: internal error code, arguments: [kdtdelrow-2], [6], [6], [], [], [], [], []
ORA-01502: index 'APP_OWNER.SYS_C007740' or partition of such index is in unusable state



SQL> conn / as sysdba
Connected.
SQL> select obj#,flags from ind$ where obj#=75350;

OBJ# FLAGS
---------- ----------
75350 2563



SQL> declare
2 isclean boolean;
3 begin
4 isclean :=false;
5 while isclean=false
6 loop
7 isclean := DBMS_REPAIR.ONLINE_INDEX_CLEAN(dbms_repair.all_index_id,dbms_repair.lock_wait);
8 dbms_lock.sleep(10);
9 end loop;
10 end;
11 /

PL/SQL procedure successfully completed.


SQL> select obj#,flags from ind$ where obj#=75350;

OBJ# FLAGS
---------- ----------
75350 2051


SQL> conn donghua2/donghua2
Connected.
SQL> alter index APP_OWNER.SYS_C007740 rebuild online tablespace sec_data01;