Donghua's Blog - DBAGlobe: 2010

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;

Donghua's Blog - DBAGlobe