askMLabs

6:13 PM

How to identify the physical disk bound to the ASMLIB disk?

Written By askMLabs on Friday, September 24, 2010 | 6:13 PM

We will create ASM disks with oracleasm and we can list the ASM disks using the listdisks option.
While creating the ASM disk we will supply the physical disk path, as

[root@dhcppc1 rpms]# /etc/init.d/oracleasm createdisk ASK_DISK1 /dev/sdc1
Marking disk "ASK_DISK1" as an ASM disk: [ OK ]
[root@dhcppc1 rpms]#

Once after creating the ASM disks, there is no direct command which actually lists the mapping between ASM disks and physical disks.

If you check the syntax,

[root@dhcppc1 rpms]# /etc/init.d/oracleasm -h
Usage: /etc/init.d/oracleasm {start|stop|restart|enable|disable|configure|createdisk|deletedisk|querydisk|listdisks|scandisks|status}
[root@dhcppc1 rpms]#

[root@dhcppc1 rpms]# /etc/init.d/oracleasm listdisks
CRSVOL1
DATAVOL1
FRAVOL1
[root@dhcppc1 rpms]# /etc/init.d/oracleasm querydisk
Usage: oracleasm-querydisk [-l <manager>] [-v] [-d|-p] <label>|<device> ...
[root@dhcppc1 rpms]# /etc/init.d/oracleasm querydisk CRSVOL1
Disk "CRSVOL1" is a valid ASM disk
[root@dhcppc1 rpms]#

So there is no option from oracleasm executable to check how ASM_DISK1 is linked to /dev/sdc1.

We will try the SQL Commands ..

SQL> select name from v$asm_diskgroup;

NAME
------------------------------
CRS
FRA
RACDB_DATA

SQL> select name,path,label from v$asm_disk;

NAME            PATH                                LABEL
--------------- ----------------------------------- ---------------
CRS_0000        /dev/oracleasm/disks/CRSVOL1
FRA_0000        /dev/oracleasm/disks/FRAVOL1
RACDB_DATA_0000 /dev/oracleasm/disks/DATAVOL1

SQL>

Then how do we find, Which Disks Are Handled by ASMLib Kernel Driver?

There are two ways to identify the physical disk bound to the ASMLIB disk

1.

[root@dhcppc1 rpms]# find /dev -type b -exec '/etc/init.d/oracleasm' 'querydisk' '{}' ';' 2>/dev/null | grep "is marked an ASM disk"
Device "/dev/oracleasm/disks/CRSVOL1" is marked an ASM disk with the label "CRSVOL1"
Device "/dev/oracleasm/disks/FRAVOL1" is marked an ASM disk with the label "FRAVOL1"
Device "/dev/oracleasm/disks/DATAVOL1" is marked an ASM disk with the label "DATAVOL1"
Device "/dev/dm-9" is marked an ASM disk with the label "DATAVOL1"
Device "/dev/dm-11" is marked an ASM disk with the label "CRSVOL1"
Device "/dev/dm-10" is marked an ASM disk with the label "FRAVOL1"
Device "/dev/sdc" is marked an ASM disk with the label "DATAVOL1"
Device "/dev/sdd" is marked an ASM disk with the label "FRAVOL1"
Device "/dev/sde" is marked an ASM disk with the label "CRSVOL1"
Device "/dev/sdf" is marked an ASM disk with the label "DATAVOL1"
Device "/dev/sdg" is marked an ASM disk with the label "FRAVOL1"
Device "/dev/sdh" is marked an ASM disk with the label "CRSVOL1"
Device "/dev/sdi" is marked an ASM disk with the label "DATAVOL1"
Device "/dev/sdj" is marked an ASM disk with the label "FRAVOL1"
Device "/dev/sdk" is marked an ASM disk with the label "CRSVOL1"
Device "/dev/sdl" is marked an ASM disk with the label "DATAVOL1"
Device "/dev/sdm" is marked an ASM disk with the label "FRAVOL1"
Device "/dev/sdn" is marked an ASM disk with the label "CRSVOL1"
Device "/dev/sdo" is marked an ASM disk with the label "DATAVOL1"
Device "/dev/sdp" is marked an ASM disk with the label "FRAVOL1"
Device "/dev/sdq" is marked an ASM disk with the label "CRSVOL1"
Device "/dev/sdr" is marked an ASM disk with the label "DATAVOL1"
Device "/dev/sds" is marked an ASM disk with the label "FRAVOL1"
Device "/dev/sdt" is marked an ASM disk with the label "CRSVOL1"
Device "/dev/sdu" is marked an ASM disk with the label "DATAVOL1"
Device "/dev/sdv" is marked an ASM disk with the label "FRAVOL1"
Device "/dev/sdw" is marked an ASM disk with the label "CRSVOL1"
Device "/dev/sdx" is marked an ASM disk with the label "DATAVOL1"
Device "/dev/sdy" is marked an ASM disk with the label "FRAVOL1"
Device "/dev/sdz" is marked an ASM disk with the label "CRSVOL1"
Device "/dev/mapper/vpath2" is marked an ASM disk with the label "CRSVOL1"
Device "/dev/mapper/vpath1" is marked an ASM disk with the label "FRAVOL1"
Device "/dev/mapper/vpath0" is marked an ASM disk with the label "DATAVOL1"
[root@dhcppc1 rpms]#

[root@dhcppc1 rpms]# blkid|grep sd.*oracleasm|while read a b;do echo -n $a$b" scsi_id=";(echo $a|tr -d [:digit:]|tr -d [:]|cut -d"/" -f3|xargs -i scsi_id -g -s /block/{})done;
/dev/sdc:LABEL="DATAVOL1" TYPE="oracleasm" scsi_id=360050768019600fba80000000000036b
/dev/sdd:LABEL="FRAVOL1" TYPE="oracleasm" scsi_id=360050768019600fba80000000000036c
/dev/sde:LABEL="CRSVOL1" TYPE="oracleasm" scsi_id=360050768019600fba80000000000036d
/dev/sdf:LABEL="DATAVOL1" TYPE="oracleasm" scsi_id=360050768019600fba80000000000036b
/dev/sdg:LABEL="FRAVOL1" TYPE="oracleasm" scsi_id=360050768019600fba80000000000036c
/dev/sdh:LABEL="CRSVOL1" TYPE="oracleasm" scsi_id=360050768019600fba80000000000036d
/dev/sdi:LABEL="DATAVOL1" TYPE="oracleasm" scsi_id=360050768019600fba80000000000036b
/dev/sdj:LABEL="FRAVOL1" TYPE="oracleasm" scsi_id=360050768019600fba80000000000036c
/dev/sdk:LABEL="CRSVOL1" TYPE="oracleasm" scsi_id=360050768019600fba80000000000036d
/dev/sdl:LABEL="DATAVOL1" TYPE="oracleasm" scsi_id=360050768019600fba80000000000036b
/dev/sdm:LABEL="FRAVOL1" TYPE="oracleasm" scsi_id=360050768019600fba80000000000036c
/dev/sdn:LABEL="CRSVOL1" TYPE="oracleasm" scsi_id=360050768019600fba80000000000036d
/dev/sdo:LABEL="DATAVOL1" TYPE="oracleasm" scsi_id=360050768019600fba80000000000036b
/dev/sdp:LABEL="FRAVOL1" TYPE="oracleasm" scsi_id=360050768019600fba80000000000036c
/dev/sdq:LABEL="CRSVOL1" TYPE="oracleasm" scsi_id=360050768019600fba80000000000036d
/dev/sdr:LABEL="DATAVOL1" TYPE="oracleasm" scsi_id=360050768019600fba80000000000036b
/dev/sds:LABEL="FRAVOL1" TYPE="oracleasm" scsi_id=360050768019600fba80000000000036c
/dev/sdt:LABEL="CRSVOL1" TYPE="oracleasm" scsi_id=360050768019600fba80000000000036d
/dev/sdu:LABEL="DATAVOL1" TYPE="oracleasm" scsi_id=360050768019600fba80000000000036b
/dev/sdv:LABEL="FRAVOL1" TYPE="oracleasm" scsi_id=360050768019600fba80000000000036c
/dev/sdw:LABEL="CRSVOL1" TYPE="oracleasm" scsi_id=360050768019600fba80000000000036d
/dev/sdx:LABEL="DATAVOL1" TYPE="oracleasm" scsi_id=360050768019600fba80000000000036b
/dev/sdy:LABEL="FRAVOL1" TYPE="oracleasm" scsi_id=360050768019600fba80000000000036c
[root@dhcppc1 rpms]#

[root@dhcppc1 rpms]# multipath -ll
vpath2 (360050768019600fba80000000000036d) dm-11 IBM,2145
[size=10G][features=1 queue_if_no_path][hwhandler=0][rw]
\_ round-robin 0 [prio=200][active]
\_ 1:0:6:2 sdk 8:160 [active][ready]
\_ 1:0:7:2 sdn 8:208 [active][ready]
\_ 2:0:6:2 sdw 65:96 [active][ready]
\_ 2:0:7:2 sdz 65:144 [active][ready]
\_ round-robin 0 [prio=40][enabled]
\_ 1:0:4:2 sde 8:64   [active][ready]
\_ 1:0:5:2 sdh 8:112 [active][ready]
\_ 2:0:4:2 sdq 65:0   [active][ready]
\_ 2:0:5:2 sdt 65:48 [active][ready]
vpath1 (360050768019600fba80000000000036c) dm-10 IBM,2145
[size=500G][features=1 queue_if_no_path][hwhandler=0][rw]
\_ round-robin 0 [prio=200][active]
\_ 1:0:4:1 sdd 8:48   [active][ready]
\_ 1:0:5:1 sdg 8:96   [active][ready]
\_ 2:0:4:1 sdp 8:240 [active][ready]
\_ 2:0:5:1 sds 65:32 [active][ready]
\_ round-robin 0 [prio=40][enabled]
\_ 1:0:6:1 sdj 8:144 [active][ready]
\_ 1:0:7:1 sdm 8:192 [active][ready]
\_ 2:0:6:1 sdv 65:80 [active][ready]
\_ 2:0:7:1 sdy 65:128 [active][ready]
vpath0 (360050768019600fba80000000000036b) dm-9 IBM,2145
[size=500G][features=1 queue_if_no_path][hwhandler=0][rw]
\_ round-robin 0 [prio=200][active]
\_ 1:0:6:0 sdi 8:128 [active][ready]
\_ 1:0:7:0 sdl 8:176 [active][ready]
\_ 2:0:6:0 sdu 65:64 [active][ready]
\_ 2:0:7:0 sdx 65:112 [active][ready]
\_ round-robin 0 [prio=40][enabled]
\_ 1:0:4:0 sdc 8:32   [active][ready]
\_ 1:0:5:0 sdf 8:80   [active][ready]
\_ 2:0:4:0 sdo 8:224 [active][ready]
\_ 2:0:5:0 sdr 65:16 [active][ready]
[root@dhcppc1 rpms]#

[root@dhcppc1 rpms]# ls -lrt /dev/mapper/vpath*
brw-rw---- 1 root disk 253, 11 Jul 9 12:49 /dev/mapper/vpath2
brw-rw---- 1 root disk 253, 10 Jul 9 12:49 /dev/mapper/vpath1
brw-rw---- 1 root disk 253, 9 Jul 9 12:49 /dev/mapper/vpath0
[root@dhcppc1 rpms]#

[root@dhcppc1 rpms]# find /dev/oracleasm/disks -type b | xargs -i sh -c "echo {}; dd if={} count=100 2>/dev/null | strings"
/dev/oracleasm/disks/CRSVOL1
ORCLDISKCRSVOL1
CRS_0000
CRS_0000
/dev/oracleasm/disks/FRAVOL1
ORCLDISKFRAVOL1
FRA_0000
FRA_0000
/dev/oracleasm/disks/DATAVOL1
ORCLDISKDATAVOL1
RACDB_DATA_0000
RACDB_DATA
RACDB_DATA_0000
[root@dhcppc1 rpms]#

Hope it helps

SRI

How to identify the physical disk bound to the ASMLIB disk?

We will create ASM disks with oracleasm and we can list the ASM disks using the listdisks option.
While creating the ASM disk we will supply the physical disk path, as

[root@dhcppc1 rpms]# /etc/init.d/oracleasm createdisk ASK_DISK1 /dev/sdc1
Marking disk "ASK_DISK1" as an ASM disk:                   [ OK ]
[root@dhcppc1 rpms]#

Once after creating the ASM disks, there is no direct command which actually lists the mapping between ASM disks and physical disks.

If you check the syntax,
[root@dhcppc1 rpms]# /etc/init.d/oracleasm -h
Usage: /etc/init.d/oracleasm {start|stop|restart|enable|disable|configure|createdisk|deletedisk|querydisk|listdisks|scandisks|status}
[root@dhcppc1 rpms]#

[root@dhcppc1 rpms]# /etc/init.d/oracleasm listdisks
CRSVOL1
DATAVOL1
FRAVOL1
[root@dhcppc1 rpms]# /etc/init.d/oracleasm querydisk
Usage: oracleasm-querydisk [-l <manager>] [-v] [-d|-p] <label>|<device> ...
[root@dhcppc1 rpms]# /etc/init.d/oracleasm querydisk CRSVOL1
Disk "CRSVOL1" is a valid ASM disk
[root@dhcppc1 rpms]#

So there is no option from oracleasm executable to check how ASM_DISK1 is linked to /dev/sdc1.

We will try the SQL Commands ..

SQL> select name from v$asm_diskgroup;

NAME
------------------------------
CRS
FRA
RACDB_DATA

SQL> select name,path,label from v$asm_disk;

NAME            PATH                                LABEL
--------------- ----------------------------------- ---------------
CRS_0000        /dev/oracleasm/disks/CRSVOL1
FRA_0000        /dev/oracleasm/disks/FRAVOL1
RACDB_DATA_0000 /dev/oracleasm/disks/DATAVOL1

SQL>

Then how do we find, Which Disks Are Handled by ASMLib Kernel Driver?

There are two ways to identify the physical disk bound to the ASMLIB disk

[root@dhcppc1 rpms]# find /dev -type b -exec '/etc/init.d/oracleasm' 'querydisk' '{}' ';' 2>/dev/null | grep "is marked an ASM disk"
Device "/dev/oracleasm/disks/CRSVOL1" is marked an ASM disk with the label "CRSVOL1"
Device "/dev/oracleasm/disks/FRAVOL1" is marked an ASM disk with the label "FRAVOL1"
Device "/dev/oracleasm/disks/DATAVOL1" is marked an ASM disk with the label "DATAVOL1"
Device "/dev/dm-9" is marked an ASM disk with the label "DATAVOL1"
Device "/dev/dm-11" is marked an ASM disk with the label "CRSVOL1"
Device "/dev/dm-10" is marked an ASM disk with the label "FRAVOL1"
Device "/dev/sdc" is marked an ASM disk with the label "DATAVOL1"
Device "/dev/sdd" is marked an ASM disk with the label "FRAVOL1"
Device "/dev/sde" is marked an ASM disk with the label "CRSVOL1"
Device "/dev/sdf" is marked an ASM disk with the label "DATAVOL1"
Device "/dev/sdg" is marked an ASM disk with the label "FRAVOL1"
Device "/dev/sdh" is marked an ASM disk with the label "CRSVOL1"
Device "/dev/sdi" is marked an ASM disk with the label "DATAVOL1"
Device "/dev/sdj" is marked an ASM disk with the label "FRAVOL1"
Device "/dev/sdk" is marked an ASM disk with the label "CRSVOL1"
Device "/dev/sdl" is marked an ASM disk with the label "DATAVOL1"
Device "/dev/sdm" is marked an ASM disk with the label "FRAVOL1"
Device "/dev/sdn" is marked an ASM disk with the label "CRSVOL1"
Device "/dev/sdo" is marked an ASM disk with the label "DATAVOL1"
Device "/dev/sdp" is marked an ASM disk with the label "FRAVOL1"
Device "/dev/sdq" is marked an ASM disk with the label "CRSVOL1"
Device "/dev/sdr" is marked an ASM disk with the label "DATAVOL1"
Device "/dev/sds" is marked an ASM disk with the label "FRAVOL1"
Device "/dev/sdt" is marked an ASM disk with the label "CRSVOL1"
Device "/dev/sdu" is marked an ASM disk with the label "DATAVOL1"
Device "/dev/sdv" is marked an ASM disk with the label "FRAVOL1"
Device "/dev/sdw" is marked an ASM disk with the label "CRSVOL1"
Device "/dev/sdx" is marked an ASM disk with the label "DATAVOL1"
Device "/dev/sdy" is marked an ASM disk with the label "FRAVOL1"
Device "/dev/sdz" is marked an ASM disk with the label "CRSVOL1"
Device "/dev/mapper/vpath2" is marked an ASM disk with the label "CRSVOL1"
Device "/dev/mapper/vpath1" is marked an ASM disk with the label "FRAVOL1"
Device "/dev/mapper/vpath0" is marked an ASM disk with the label "DATAVOL1"
[root@dhcppc1 rpms]#

[root@dhcppc1 rpms]# blkid|grep sd.*oracleasm|while read a b;do echo -n $a$b" scsi_id=";(echo $a|tr -d [:digit:]|tr -d [:]|cut -d"/" -f3|xargs -i scsi_id -g -s /block/{})done;
/dev/sdc:LABEL="DATAVOL1" TYPE="oracleasm" scsi_id=360050768019600fba80000000000036b
/dev/sdd:LABEL="FRAVOL1" TYPE="oracleasm" scsi_id=360050768019600fba80000000000036c
/dev/sde:LABEL="CRSVOL1" TYPE="oracleasm" scsi_id=360050768019600fba80000000000036d
/dev/sdf:LABEL="DATAVOL1" TYPE="oracleasm" scsi_id=360050768019600fba80000000000036b
/dev/sdg:LABEL="FRAVOL1" TYPE="oracleasm" scsi_id=360050768019600fba80000000000036c
/dev/sdh:LABEL="CRSVOL1" TYPE="oracleasm" scsi_id=360050768019600fba80000000000036d
/dev/sdi:LABEL="DATAVOL1" TYPE="oracleasm" scsi_id=360050768019600fba80000000000036b
/dev/sdj:LABEL="FRAVOL1" TYPE="oracleasm" scsi_id=360050768019600fba80000000000036c
/dev/sdk:LABEL="CRSVOL1" TYPE="oracleasm" scsi_id=360050768019600fba80000000000036d
/dev/sdl:LABEL="DATAVOL1" TYPE="oracleasm" scsi_id=360050768019600fba80000000000036b
/dev/sdm:LABEL="FRAVOL1" TYPE="oracleasm" scsi_id=360050768019600fba80000000000036c
/dev/sdn:LABEL="CRSVOL1" TYPE="oracleasm" scsi_id=360050768019600fba80000000000036d
/dev/sdo:LABEL="DATAVOL1" TYPE="oracleasm" scsi_id=360050768019600fba80000000000036b
/dev/sdp:LABEL="FRAVOL1" TYPE="oracleasm" scsi_id=360050768019600fba80000000000036c
/dev/sdq:LABEL="CRSVOL1" TYPE="oracleasm" scsi_id=360050768019600fba80000000000036d
/dev/sdr:LABEL="DATAVOL1" TYPE="oracleasm" scsi_id=360050768019600fba80000000000036b
/dev/sds:LABEL="FRAVOL1" TYPE="oracleasm" scsi_id=360050768019600fba80000000000036c
/dev/sdt:LABEL="CRSVOL1" TYPE="oracleasm" scsi_id=360050768019600fba80000000000036d
/dev/sdu:LABEL="DATAVOL1" TYPE="oracleasm" scsi_id=360050768019600fba80000000000036b
/dev/sdv:LABEL="FRAVOL1" TYPE="oracleasm" scsi_id=360050768019600fba80000000000036c
/dev/sdw:LABEL="CRSVOL1" TYPE="oracleasm" scsi_id=360050768019600fba80000000000036d
/dev/sdx:LABEL="DATAVOL1" TYPE="oracleasm" scsi_id=360050768019600fba80000000000036b
/dev/sdy:LABEL="FRAVOL1" TYPE="oracleasm" scsi_id=360050768019600fba80000000000036c
[root@dhcppc1 rpms]#

[root@dhcppc1 rpms]# multipath -ll
vpath2 (360050768019600fba80000000000036d) dm-11 IBM,2145
[size=10G][features=1 queue_if_no_path][hwhandler=0][rw]
\_ round-robin 0 [prio=200][active]
\_ 1:0:6:2 sdk 8:160 [active][ready]
\_ 1:0:7:2 sdn 8:208 [active][ready]
\_ 2:0:6:2 sdw 65:96 [active][ready]
\_ 2:0:7:2 sdz 65:144 [active][ready]
\_ round-robin 0 [prio=40][enabled]
\_ 1:0:4:2 sde 8:64   [active][ready]
\_ 1:0:5:2 sdh 8:112 [active][ready]
\_ 2:0:4:2 sdq 65:0   [active][ready]
\_ 2:0:5:2 sdt 65:48 [active][ready]
vpath1 (360050768019600fba80000000000036c) dm-10 IBM,2145
[size=500G][features=1 queue_if_no_path][hwhandler=0][rw]
\_ round-robin 0 [prio=200][active]
\_ 1:0:4:1 sdd 8:48   [active][ready]
\_ 1:0:5:1 sdg 8:96   [active][ready]
\_ 2:0:4:1 sdp 8:240 [active][ready]
\_ 2:0:5:1 sds 65:32 [active][ready]
\_ round-robin 0 [prio=40][enabled]
\_ 1:0:6:1 sdj 8:144 [active][ready]
\_ 1:0:7:1 sdm 8:192 [active][ready]
\_ 2:0:6:1 sdv 65:80 [active][ready]
\_ 2:0:7:1 sdy 65:128 [active][ready]
vpath0 (360050768019600fba80000000000036b) dm-9 IBM,2145
[size=500G][features=1 queue_if_no_path][hwhandler=0][rw]
\_ round-robin 0 [prio=200][active]
\_ 1:0:6:0 sdi 8:128 [active][ready]
\_ 1:0:7:0 sdl 8:176 [active][ready]
\_ 2:0:6:0 sdu 65:64 [active][ready]
\_ 2:0:7:0 sdx 65:112 [active][ready]
\_ round-robin 0 [prio=40][enabled]
\_ 1:0:4:0 sdc 8:32   [active][ready]
\_ 1:0:5:0 sdf 8:80   [active][ready]
\_ 2:0:4:0 sdo 8:224 [active][ready]
\_ 2:0:5:0 sdr 65:16 [active][ready]
[root@dhcppc1 rpms]#

[root@orrcdbtsl01 ~]# ls -lrt /dev/mapper/vpath*
brw-rw---- 1 root disk 253, 11 Jul 9 12:49 /dev/mapper/vpath2
brw-rw---- 1 root disk 253, 10 Jul 9 12:49 /dev/mapper/vpath1
brw-rw---- 1 root disk 253, 9 Jul 9 12:49 /dev/mapper/vpath0
[root@orrcdbtsl01 ~]#

[root@orrcdbtsl01 ~]# find /dev/oracleasm/disks -type b | xargs -i sh -c "echo {}; dd if={} count=100 2>/dev/null | strings"
/dev/oracleasm/disks/CRSVOL1
ORCLDISKCRSVOL1
CRS_0000
CRS_0000
/dev/oracleasm/disks/FRAVOL1
ORCLDISKFRAVOL1
FRA_0000
FRA_0000
/dev/oracleasm/disks/DATAVOL1
ORCLDISKDATAVOL1
RACDB_DATA_0000
RACDB_DATA
RACDB_DATA_0000
[root@orrcdbtsl01 ~]#

6:15 PM

Exadata ?

Written By askMLabs on Thursday, September 23, 2010 | 6:15 PM

What is Exadata ? Why is it used ? who will use this ?

Is it replacement to Oracle RAC Server?

Do we just need to power on to use the server ?

Why is it so popular ? Is it really popular or getting popular ?

Is it best for DataWarehouse or OLTP or Mixture ?
Well, Just started working on Exadata .....

Will post many things sooooon .....

:-)

6:34 AM

ORA-02298 - parent keys not found

Written By askMLabs on Wednesday, September 22, 2010 | 6:34 AM

SQL> sho user
USER is "SH"
SQL> ALTER TABLE emp_det ADD CONSTRAINT fk_emp_det FOREIGN KEY ( name ) REFERENCES emp( name );
ALTER TABLE emp_det ADD CONSTRAINT fk_emp_det FOREIGN KEY ( name ) REFERENCES emp( name )
*
ERROR at line 1:
ORA-02298: cannot validate (SH.FK_EMP_DET) - parent keys not found

SQL>

In the above example, we are trying to create a foreign constraint on the table emp_det referencing column name in the table emp. But the error shows that there are some emp records exists in emp_det table for which there is no master entries in the table EMP.

So when ever we are creating foreign constraint on the detailed table(emp_det), all the column values which are referencing the master table should exist in the master table.

Troubleshooting

SQL> select unique name from emp_det ed
2 where ed.name is not null and not exists
3 (select null from emp e where e.name = ed.name);

NAME
--------------------------------
John
Marry
Farah

SQL>

SQL> select count(1) from emp_det where name in ('JOHN','MARRY','FARAH');

COUNT(1)
----------
162

SQL> select count(1) from emp where name in ('JOHN','MARRY','FARAH');

COUNT(1)
----------
0

SQL>

WorkAround

1) Need to delete the rows in the table emp_det table corresponding to the above specified Names
or
2) Insert the data in the table emp corresponding to the above specified Names

Hope it helps

SRI

7:31 AM

Prepare Linux virtual machine for 11gR2 installation on Linux 5.x

Written By askMLabs on Tuesday, September 21, 2010 | 7:31 AM

There are some OS preparation steps to be performed before installing database on linux.

Pls refer the Master Note ID : 851598.1 and complete all the steps specified in the note as per your OS.

In the present article, i am using the OEL 5.4 (32-bit) as OS and following the note id : 880936.1 to complete all the preparatory steps for 11gR2 database creation.

Create the required user and groups for the installation

groupadd -g 1000 oinstall

groupadd -g 1200 dba

useradd -u 1100 -g oinstall -G dba oracle

mkdir -p /u01/app/oracle

chown -R oracle:oinstall /u01/app

chmod -R 775 /u01/app

Required RPMs installation :

Make sure that the following RPMs are installed,

binutils-2.17.50.0.6-2.el5

compat-libstdc++-33-3.2.3-61

elfutils-libelf-0.125-3.el5

glibc-2.5-24

glibc-common-2.5-12

ksh-20060214-1.7

libaio-0.3.106

libgcc-4.1.2-42.el5

libstdc++-4.1.2-42

make-3.81-1.1

elfutils-libelf-devel-0.125-3.el5.i386.rpm

a.requires elfutils-libelf-devel-static-0.125-3.el5.i386.rpm as a prerequisite.

b.elfutils-libelf-devel and elfutils-libelf-devel-static each depend upon the other. Therefore, they must be installed together, in one (1"rpm -ivh" command as follows:

rpm -ivh elfutils-libelf-devel-0.125-3.el5.i386.rpm elfutils-libelf-devel-static-0.125-3.el5.i386.rpm

kernel-headers-2.6.18-92.el5.i386.rpm

glibc-headers-2.5-24.i386.rpm

glibc-devel-2.5-24.i386.rpm

libstdc++-devel-4.1.2-42.el5.i386.rpm

libgomp-4.1.2-42.el5.i386.rpm

gcc-4.1.2-42.el5.i386.rpm

gcc-c++-4.1.2-42.el5.i386.rpm

libaio-devel-0.3.106-3.2.i386.rpm

sysstat-7.0.2-1.el5.i386.rpm

unixODBC-2.2.11-7.1.i386.rpm

unixODBC-devel-2.2.11-7.1.i386.rpm

NOTE :
rpm -qa | grep -i <rpm name> ==> to check if the rpm is installed or not
rpm -ivh <rpm name> ==> to install rpm
rpm -e <rpm name> ==> to remove package from the OS

In my OS following four RPMs are not installed.

libaio-devel-0.3.106-3.2.i386.rpm
sysstat-7.0.2-1.el5.i386.rpm
unixODBC-2.2.11-7.1.i386.rpm
unixODBC-devel-2.2.11-7.1.i386.rpm

Session Log :

[root@localhost ~]# rpm -qa | grep -i binutils-
binutils-2.17.50.0.6-12.el5
[root@localhost ~]# rpm -qa | grep -i compat-libstdc
compat-libstdc++-296-2.96-138
compat-libstdc++-33-3.2.3-61
[root@localhost ~]# rpm -qa | grep -i elfutils-libelf
elfutils-libelf-0.137-3.el5
elfutils-libelf-devel-static-0.137-3.el5
elfutils-libelf-devel-0.137-3.el5
[root@localhost ~]# rpm -qa | grep -i glibc-
glibc-headers-2.5-42
glibc-common-2.5-42
glibc-devel-2.5-42
glibc-2.5-42
[root@localhost ~]# rpm -qa | grep -i glibc-common-
glibc-common-2.5-42
[root@localhost ~]# rpm -qa | grep -i ksh-
ksh-20080202-14.el5
[root@localhost ~]# rpm -qa | grep -i libaio-
libaio-0.3.106-3.2
[root@localhost ~]# rpm -qa | grep -i libgcc-
libgcc-4.1.2-46.el5
compat-libgcc-296-2.96-138
[root@localhost ~]# rpm -qa | grep -i libstdc++
compat-libstdc++-296-2.96-138
libstdc++-4.1.2-46.el5
libstdc++-devel-4.1.2-46.el5
compat-libstdc++-33-3.2.3-61
[root@localhost ~]# rpm -qa | grep -i make-
imake-1.0.2-3
make-3.81-3.el5
automake-1.9.6-2.1
[root@localhost ~]# rpm -qa | grep -i elfutils-libelf-devel
elfutils-libelf-devel-static-0.137-3.el5
elfutils-libelf-devel-0.137-3.el5
[root@localhost ~]# rpm -qa | grep -i kernel-headers-
kernel-headers-2.6.18-164.el5
[root@localhost ~]# rpm -qa | grep -i glibc-headers-
glibc-headers-2.5-42
[root@localhost ~]# rpm -qa | grep -i glibc-devel-
glibc-devel-2.5-42
[root@localhost ~]# rpm -qa | grep -i libstdc++-devel-
libstdc++-devel-4.1.2-46.el5
[root@localhost ~]# rpm -qa | grep -i libgomp-
libgomp-4.4.0-6.el5
[root@localhost ~]# rpm -qa | grep -i gcc-
libgcc-4.1.2-46.el5
gcc-c++-4.1.2-46.el5
compat-libgcc-296-2.96-138
gcc-4.1.2-46.el5
gcc-gfortran-4.1.2-46.el5
[root@localhost ~]# rpm -qa | grep -i libaio-devel
[root@localhost ~]# rpm -qa | grep -i sysstat-
[root@localhost ~]# rpm -qa | grep -i unixODBC
[root@localhost ~]#

[root@localhost Server]# rpm -ivh unixODBC-2.2.11-7.1.i386.rpm
warning: unixODBC-2.2.11-7.1.i386.rpm: Header V3 DSA signature: NOKEY, key ID 1e5e0159
Preparing...                ########################################### [100%]
1:unixODBC               ########################################### [100%]
[root@localhost Server]#

[root@localhost Server]# rpm -ivh libaio-devel-0.3.106-3.2.i386.rpm
warning: libaio-devel-0.3.106-3.2.i386.rpm: Header V3 DSA signature: NOKEY, key ID 1e5e0159
Preparing...                ########################################### [100%]
1:libaio-devel           ########################################### [100%]
[root@localhost Server]# rpm -ivh unixODBC-devel-2.2.11-7.1.i386.rpm
warning: unixODBC-devel-2.2.11-7.1.i386.rpm: Header V3 DSA signature: NOKEY, key ID 1e5e0159
Preparing...                ########################################### [100%]
1:unixODBC-devel         ########################################### [100%]
[root@localhost Server]# rpm -ivh sysstat-7.0.2-3.el5.i386.rpm
warning: sysstat-7.0.2-3.el5.i386.rpm: Header V3 DSA signature: NOKEY, key ID 1e5e0159
Preparing...                ########################################### [100%]
1:sysstat                ########################################### [100%]
[root@localhost Server]#

Following files should be modified as per the following session log:
/etc/security/limits.conf
/etc/pam.d/login
/etc/profile
/etc/sysctl.conf ==> Can be modified while installing using OUI.

Session Log

[root@localhost ~]# diff /etc/security/limits.conf /etc/security/limits.conf_bak
49,52c49
< oracle soft nproc 2047
< oracle hard nproc 16384
< oracle soft nofile 1024
< oracle hard nofile 65536
---
>
[root@localhost ~]#

[root@localhost ~]# diff /etc/pam.d/login /etc/pam.d/login_bak
15d14
< session required pam_limits.so
[root@localhost ~]#

[root@localhost ~]# diff /etc/profile /etc/profile_bak
57,65d56
< if [ $USER = "oracle" ]; then
<     if [ $SHELL = "/bin/ksh" ]; then
<        ulimit -u 16384
<        ulimit -n 65536
<     else
<        ulimit -u 16384 -n 65536
<     fi
< fi
<
[root@localhost ~]#

Make sure the following versions are verified.

[root@localhost ~]# gcc --version
gcc (GCC) 4.1.2 20080704 (Red Hat 4.1.2-46)
Copyright (C) 2006 Free Software Foundation, Inc.
This is free software; see the source for copying conditions. There is NO
warranty; not even for MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.

[root@localhost ~]# g++ --version
g++ (GCC) 4.1.2 20080704 (Red Hat 4.1.2-46)
Copyright (C) 2006 Free Software Foundation, Inc.
This is free software; see the source for copying conditions. There is NO
warranty; not even for MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.

[root@localhost ~]#

Finally add the following entries in the oracle user profile ( .bash_profile )

ORACLE_BASE=/u01/app/oracle
ORACLE_HOME=/u01/app/oracle/11.2.0/db_1
ORACLE_SID=TESTDB
PATH=$ORACLE_HOME/bin:$PATH
export ORACLE_BASE
export ORACLE_HOME
export ORACLE_SID
export PATH

Now the OS is ready for installing 11gR2 database.

Hope it helps
SRI

7:01 AM

RAC Nodes interconnect Checks

Written By askMLabs on Monday, September 20, 2010 | 7:01 AM

[grid@rac02 cssd]$ olsnodes -s
rac01     Active
rac02     Active
[grid@rac02 cssd]$ oifcfg getif
eth2 139.22.38.0 global public
eth3 192.168.50.0 global cluster_interconnect
[grid@rac02 cssd]$

[root@rac01 ~]# ifconfig eth3
eth3      Link encap:Ethernet HWaddr 00:13:72:60:69:84
inet addr:192.168.50.1 Bcast:192.168.50.255 Mask:255.255.255.0
inet6 addr: fe80::213:72ff:fe60:6984/64 Scope:Link
UP BROADCAST RUNNING MULTICAST MTU:1500 Metric:1
RX packets:38565202 errors:0 dropped:0 overruns:0 frame:0
TX packets:30148892 errors:0 dropped:0 overruns:0 carrier:0
collisions:0 txqueuelen:1000
RX bytes:25947483525 (24.1 GiB) TX bytes:13988725497 (13.0 GiB)
Base address:0xbcc0 Memory:fe0e0000-fe100000

[root@rac01 ~]#

[root@rac02 ~]# ifconfig eth3
eth3      Link encap:Ethernet HWaddr 00:13:72:5E:7C:C2
inet addr:192.168.50.2 Bcast:192.168.50.255 Mask:255.255.255.0
inet6 addr: fe80::213:72ff:fe5e:7cc2/64 Scope:Link
UP BROADCAST RUNNING MULTICAST MTU:1500 Metric:1
RX packets:30513289 errors:0 dropped:0 overruns:0 frame:0
TX packets:39265110 errors:0 dropped:0 overruns:0 carrier:0
collisions:0 txqueuelen:1000
RX bytes:14191064255 (13.2 GiB) TX bytes:26225686434 (24.4 GiB)
Base address:0xbcc0 Memory:fe0e0000-fe100000

[root@rac02 ~]#

[grid@rac02 grid]$ ping 192.168.50.1
PING 192.168.50.1 (192.168.50.1) 56(84) bytes of data.
64 bytes from 192.168.50.1: icmp_seq=1 ttl=64 time=0.192 ms
64 bytes from 192.168.50.1: icmp_seq=2 ttl=64 time=0.195 ms
64 bytes from 192.168.50.1: icmp_seq=3 ttl=64 time=0.199 ms

--- 192.168.50.1 ping statistics ---
3 packets transmitted, 3 received, 0% packet loss, time 2001ms
rtt min/avg/max/mdev = 0.192/0.195/0.199/0.011 ms
[grid@rac02 grid]$ ping rac01-priv.testing.com
PING rac01-priv.testing.com (192.168.50.1) 56(84) bytes of data.
64 bytes from rac01-priv.testing.com (192.168.50.1): icmp_seq=1 ttl=64 time=0.240 ms
64 bytes from rac01-priv.testing.com (192.168.50.1): icmp_seq=2 ttl=64 time=0.206 ms
64 bytes from rac01-priv.testing.com (192.168.50.1): icmp_seq=3 ttl=64 time=0.162 ms
64 bytes from rac01-priv.testing.com (192.168.50.1): icmp_seq=4 ttl=64 time=0.180 ms

--- rac01-priv.testing.com ping statistics ---
4 packets transmitted, 4 received, 0% packet loss, time 3000ms
rtt min/avg/max/mdev = 0.162/0.197/0.240/0.029 ms
[grid@rac02 grid]$

[grid@rac01 grid]$ ping 192.168.50.2
PING 192.168.50.2 (192.168.50.2) 56(84) bytes of data.
64 bytes from 192.168.50.2: icmp_seq=1 ttl=64 time=0.171 ms
64 bytes from 192.168.50.2: icmp_seq=2 ttl=64 time=0.175 ms
64 bytes from 192.168.50.2: icmp_seq=3 ttl=64 time=0.165 ms
64 bytes from 192.168.50.2: icmp_seq=4 ttl=64 time=0.188 ms

--- 192.168.50.2 ping statistics ---
4 packets transmitted, 4 received, 0% packet loss, time 3001ms
rtt min/avg/max/mdev = 0.165/0.174/0.188/0.018 ms
[grid@rac01 grid]$ ping rac02-priv.testing.com
PING rac02-priv.testing.com (192.168.50.2) 56(84) bytes of data.
64 bytes from rac02-priv.testing.com (192.168.50.2): icmp_seq=1 ttl=64 time=0.160 ms
64 bytes from rac02-priv.testing.com (192.168.50.2): icmp_seq=2 ttl=64 time=0.223 ms
64 bytes from rac02-priv.testing.com (192.168.50.2): icmp_seq=3 ttl=64 time=0.198 ms
64 bytes from rac02-priv.testing.com (192.168.50.2): icmp_seq=4 ttl=64 time=0.172 ms

--- rac02-priv.testing.com ping statistics ---
4 packets transmitted, 4 received, 0% packet loss, time 2998ms
rtt min/avg/max/mdev = 0.160/0.188/0.223/0.026 ms
[grid@rac01 grid]$

Hope it helps

SRI

7:46 AM

Physical Standby Switch Over Demo

Written By askMLabs on Thursday, September 16, 2010 | 7:46 AM

This demo shows how to perform switch over operations for physical stand by operations.

One important point to remember:
Though the fact, that the previous Standby database became the primary afterwards applies to both variants - there are small differences which should for sure be considered. The most important is: After a FailOver there is no way to convert the former primary instance into a standby database for the new primary system: Due to the fact that both of them created redo information after the FailOver, they are completely out of sync, and in no way they can be synchronized again . The only possibility to get a new standby system again is to create it from the scratch.

After a SwitchOver, on the other hand, we have both, the primary and the standby system, available again as we had before. This means you should think twice before doing a FailOver.

2:38 PM

How to add more disk space to Linux Virtual Machine

Written By askMLabs on Wednesday, September 15, 2010 | 2:38 PM

The present demo shows how to add more disk space to linux virtual machine. If you are creating Linux virtual machines for testing purpose , obviously you will create with less disk space. If there is any requirement to add more disk space to linux virtual machine, just follow the steps in the demo and add more disk space to linux virtual machine.

[root@dhcppc1 ~]# fdisk -l

Disk /dev/sda: 8589 MB, 8589934592 bytes
255 heads, 63 sectors/track, 1044 cylinders
Units = cylinders of 16065 * 512 = 8225280 bytes

Device Boot      Start         End      Blocks   Id System
/dev/sda1   *           1          13      104391   83 Linux
/dev/sda2              14        1044     8281507+ 8e Linux LVM

Disk /dev/sdb: 8589 MB, 8589934592 bytes
255 heads, 63 sectors/track, 1044 cylinders
Units = cylinders of 16065 * 512 = 8225280 bytes

Disk /dev/sdb doesn't contain a valid partition table
[root@dhcppc1 ~]# fdisk /dev/sdb
Device contains neither a valid DOS partition table, nor Sun, SGI or OSF disklabel
Building a new DOS disklabel. Changes will remain in memory only,
until you decide to write them. After that, of course, the previous
content won't be recoverable.

The number of cylinders for this disk is set to 1044.
There is nothing wrong with that, but this is larger than 1024,
and could in certain setups cause problems with:
1) software that runs at boot time (e.g., old versions of LILO)
2) booting and partitioning software from other OSs
(e.g., DOS FDISK, OS/2 FDISK)
Warning: invalid flag 0x0000 of partition table 4 will be corrected by w(rite)

Command (m for help): p

Disk /dev/sdb: 8589 MB, 8589934592 bytes
255 heads, 63 sectors/track, 1044 cylinders
Units = cylinders of 16065 * 512 = 8225280 bytes

Device Boot      Start         End      Blocks   Id System

Command (m for help): n
Command action
e   extended
p   primary partition (1-4)
p
Partition number (1-4): 1
First cylinder (1-1044, default 1):
Using default value 1
Last cylinder or +size or +sizeM or +sizeK (1-1044, default 1044):
Using default value 1044

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

Calling ioctl() to re-read partition table.
Syncing disks.
[root@dhcppc1 ~]# fdisk -l

Disk /dev/sda: 8589 MB, 8589934592 bytes
255 heads, 63 sectors/track, 1044 cylinders
Units = cylinders of 16065 * 512 = 8225280 bytes

Device Boot      Start         End      Blocks   Id System
/dev/sda1   *           1          13      104391   83 Linux
/dev/sda2              14        1044     8281507+ 8e Linux LVM

Disk /dev/sdb: 8589 MB, 8589934592 bytes
255 heads, 63 sectors/track, 1044 cylinders
Units = cylinders of 16065 * 512 = 8225280 bytes

Device Boot      Start         End      Blocks   Id System
/dev/sdb1               1        1044     8385898+ 83 Linux
[root@dhcppc1 ~]# mkfs -t ext3 /dev/sdb1
mke2fs 1.39 (29-May-2006)
Filesystem label=
OS type: Linux
Block size=4096 (log=2)
Fragment size=4096 (log=2)
1048576 inodes, 2096474 blocks
104823 blocks (5.00%) reserved for the super user
First data block=0
Maximum filesystem blocks=2147483648
64 block groups
32768 blocks per group, 32768 fragments per group
16384 inodes per group
Superblock backups stored on blocks:
32768, 98304, 163840, 229376, 294912, 819200, 884736, 1605632

Writing inode tables: done
Creating journal (32768 blocks):

done
Writing superblocks and filesystem accounting information: done

This filesystem will be automatically checked every 33 mounts or
180 days, whichever comes first. Use tune2fs -c or -i to override.
[root@dhcppc1 ~]#
[root@dhcppc1 ~]#
[root@dhcppc1 ~]# mkdir /u01
[root@dhcppc1 ~]# chmod 777 /u01
[root@dhcppc1 ~]# df -h
Filesystem            Size Used Avail Use% Mounted on
/dev/mapper/VolGroup00-LogVol00
3.9G 3.1G 657M 83% /
/dev/sda1              99M   20M   75M 21% /boot
tmpfs                 864M     0 864M   0% /dev/shm
none                  864M 104K 864M   1% /var/lib/xenstored
[root@dhcppc1 ~]# mount /dev/sdb1 /u01
[root@dhcppc1 ~]# df -h
Filesystem            Size Used Avail Use% Mounted on
/dev/mapper/VolGroup00-LogVol00
3.9G 3.1G 657M 83% /
/dev/sda1              99M   20M   75M 21% /boot
tmpfs                 864M     0 864M   0% /dev/shm
none                  864M 104K 864M   1% /var/lib/xenstored
/dev/sdb1             7.9G 147M 7.4G   2% /u01
[root@dhcppc1 ~]# vi /etc/fstab
[root@dhcppc1 ~]# grep -i "/dev/sdb1" /etc/fstab
/dev/sdb1               /u01                    ext3    defaults        0 0
[root@dhcppc1 ~]#

Hope it helps

SRI

11:37 AM

VMware Server - Linux installation

Written By askMLabs on Sunday, September 12, 2010 | 11:37 AM

Linux installation on vmware server Part 1

Linux installation on vmware server part 2

1:36 PM

How to add SCAN LISTENER in 11gR2

Written By askMLabs on Friday, September 3, 2010 | 1:36 PM

My 11gR2 grid infrastructure completed with only one scan listener. The following log shows that ....

[grid@rac01 grid]$ srvctl config scan
SCAN name: rac-scan, Network: 1/133.22.38.0/255.255.255.0/eth2
SCAN VIP name: scan1, IP: /rac-scan.example.com/133.22.67.192
[grid@rac01 grid]$ srvctl config scan_listener
SCAN Listener LISTENER_SCAN1 exists. Port: TCP:1521
[grid@rac01 grid]$

[grid@rac01 grid]$ ping rac-scan.example.com
PING rac-scan.example.com (133.22.67.193) 56(84) bytes of data.
From rac01.seo.int (133.22.38.50) icmp_seq=2 Destination Host Unreachable
From rac01.seo.int (133.22.38.50) icmp_seq=3 Destination Host Unreachable
From rac01.seo.int (133.22.38.50) icmp_seq=4 Destination Host Unreachable

--- rac-scan.example.com ping statistics ---
6 packets transmitted, 0 received, +3 errors, 100% packet loss, time 5000ms
, pipe 3
[grid@rac01 grid]$ ping rac-scan
PING rac-scan.example.com (133.22.67.194) 56(84) bytes of data.
From rac01.seo.int (133.22.38.50) icmp_seq=2 Destination Host Unreachable
From rac01.seo.int (133.22.38.50) icmp_seq=3 Destination Host Unreachable
From rac01.seo.int (133.22.38.50) icmp_seq=4 Destination Host Unreachable
From rac01.seo.int (133.22.38.50) icmp_seq=6 Destination Host Unreachable
From rac01.seo.int (133.22.38.50) icmp_seq=7 Destination Host Unreachable
From rac01.seo.int (133.22.38.50) icmp_seq=8 Destination Host Unreachable

--- rac-scan.example.com ping statistics ---
11 packets transmitted, 0 received, +6 errors, 100% packet loss, time 10000ms
, pipe 3
[grid@rac01 grid]$ ping rac-scan
PING rac-scan.example.com (133.22.67.192) 56(84) bytes of data.
64 bytes from rac-scan.example.com (133.22.67.192): icmp_seq=1 ttl=64 time=0.039 ms
64 bytes from rac-scan.example.com (133.22.67.192): icmp_seq=2 ttl=64 time=0.054 ms
64 bytes from rac-scan.example.com (133.22.67.192): icmp_seq=3 ttl=64 time=0.049 ms
64 bytes from rac-scan.example.com (133.22.67.192): icmp_seq=4 ttl=64 time=0.063 ms

--- rac-scan.example.com ping statistics ---
4 packets transmitted, 4 received, 0% packet loss, time 3001ms
rtt min/avg/max/mdev = 0.039/0.051/0.063/0.010 ms
[grid@rac01 grid]$

[grid@rac01 grid]$ host rac-scan
rac-scan.example.com has address 133.22.67.194
rac-scan.example.com has address 133.22.67.193
rac-scan.example.com has address 133.22.67.192
[grid@rac01 grid]$ nslookup rac-scan
Server:         133.22.5.53
Address:        133.22.5.53#53

Name:   rac-scan.example.com
Address: 133.22.67.194
Name:   rac-scan.example.com
Address: 133.22.67.192
Name:   rac-scan.example.com
Address: 133.22.67.193

[grid@rac01 grid]$

[root@rac01 network-scripts]# host rac-scan
rac-scan.example.com has address 133.22.67.194
rac-scan.example.com has address 133.22.67.193
rac-scan.example.com has address 133.22.67.192
[root@rac01 network-scripts]#

rac-scan.example.com IN A 133.22.67.194
IN A 133.22.67.193
IN A 133.22.67.192

[grid@rac02 grid]$ Check_CRS.txt
HA Resource                                        Target     State
-----------                                        ------     -----
ora.CRS.dg                                         ONLINE     ONLINE on rac01
ora.FRA.dg                                         ONLINE     ONLINE on rac01
ora.LISTENER.lsnr                                  ONLINE     ONLINE on rac01
ora.LISTENER_SCAN1.lsnr                            ONLINE     ONLINE on rac01
ora.RACDB_DATA.dg                                  ONLINE     ONLINE on rac01
ora.asm                                            ONLINE     ONLINE on rac01
ora.eons                                           ONLINE     ONLINE on rac01
ora.gsd                                            OFFLINE    OFFLINE
ora.net1.network                                   ONLINE     ONLINE on rac01
ora.oc4j                                           OFFLINE    OFFLINE
ora.ons                                            ONLINE     ONLINE on rac01
...
...
ora.rac02.vip                                      ONLINE     ONLINE on rac02
ora.rac10g.db                                      ONLINE     ONLINE on rac02
ora.rac10g.rac10g1.inst                            ONLINE     ONLINE on rac01
ora.rac10g.rac10g2.inst                            ONLINE     OFFLINE
ora.racpoc.askm1.svc                               ONLINE     ONLINE on rac01
ora.racpoc.db                                      ONLINE     ONLINE on rac01
ora.registry.acfs                                  ONLINE     ONLINE on rac01
ora.scan1.vip                                      ONLINE     ONLINE on rac01
ora.testdb.db                                      ONLINE     ONLINE on rac01

tnsnames.ora file :

TESTDB =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = rac-scan.example.com)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = testdb.example.com)
)
)

The above session data clearly shows there is only one scan listener configured. When we use scan name in tnsnames.ora, the client connections will be established to database only when the scan name resolves to valid IP address.
But with the above configurations, the client connections some times may fail when the SCAN is resolving to 133.22.67.193 or 133.22.67.194. It establishes connection only when the SCAN resolves to 133.22.67.192.

SQL> conn test/test@testdb ==> Connection failed. rac-scan resolves either to 133.22.67.193 or 133.22.67.194
ERROR:
ORA-12543: TNS:destination host unreachable

SQL>conn test/test@testdb ==> Connection established.rac-scan resolves to 133.22.67.192.
connected
SQL>

Now to avoid this , we need to create two more scan listeners.

[grid@rac01 grid]$ srvctl stop scan_listener
[grid@rac01 grid]$ srvctl stop scan
[grid@rac01 grid]$ srvctl config scan
SCAN name: rac-scan, Network: 1/133.22.38.0/255.255.255.0/eth2
SCAN VIP name: scan1, IP: /rac-scan.example.com/133.22.67.192
[grid@rac01 grid]$ srvctl config scan_listener
SCAN Listener LISTENER_SCAN1 exists. Port: TCP:1521
[grid@rac01 grid]$ which srvctl
/u01/home/11.2.0/grid/bin/srvctl
[grid@rac01 grid]$

[root@rac01 network-scripts]# /u01/home/11.2.0/grid/bin/srvctl modify scan -h

Modifies the SCAN name.

Usage: srvctl modify scan -n <scan_name>
-n <scan_name> Domain name qualified SCAN name
-h Print usage
[root@rac01 network-scripts]# /u01/home/11.2.0/grid/bin/srvctl modify scan -n rac-scan
[root@rac01 network-scripts]# /u01/home/11.2.0/grid/bin/srvctl config scan
SCAN name: rac-scan, Network: 1/133.22.38.0/255.255.255.0/eth2
SCAN VIP name: scan1, IP: /rac-scan.example.com/133.22.67.193
SCAN VIP name: scan2, IP: /rac-scan.example.com/133.22.67.194
SCAN VIP name: scan3, IP: /rac-scan.example.com/133.22.67.192
[root@rac01 network-scripts]# /u01/home/11.2.0/grid/bin/srvctl modify scan_listener -u

[root@rac01 network-scripts]# /u01/home/11.2.0/grid/bin/srvctl config scan_listener
SCAN Listener LISTENER_SCAN1 exists. Port: TCP:1521
SCAN Listener LISTENER_SCAN2 exists. Port: TCP:1521
SCAN Listener LISTENER_SCAN3 exists. Port: TCP:1521
[root@rac01 network-scripts]# /u01/home/11.2.0/grid/bin/srvctl start scan_listener
[root@rac01 network-scripts]# /u01/home/11.2.0/grid/bin/srvctl config scan
SCAN name: rac-scan, Network: 1/133.22.38.0/255.255.255.0/eth2
SCAN VIP name: scan1, IP: /rac-scan.example.com/133.22.67.193
SCAN VIP name: scan2, IP: /rac-scan.example.com/133.22.67.194
SCAN VIP name: scan3, IP: /rac-scan.example.com/133.22.67.192
[root@rac01 network-scripts]#

[grid@rac02 grid]$ Check_CRS.txt
HA Resource                                        Target     State
-----------                                        ------     -----
ora.CRS.dg                                         ONLINE     ONLINE on rac01
ora.FRA.dg                                         ONLINE     ONLINE on rac01
ora.LISTENER.lsnr                                  ONLINE     ONLINE on rac01
ora.LISTENER_SCAN1.lsnr                            ONLINE     ONLINE on rac01
ora.LISTENER_SCAN2.lsnr                            ONLINE     ONLINE on rac02
ora.LISTENER_SCAN3.lsnr                            ONLINE     ONLINE on rac02
ora.RACDB_DATA.dg                                  ONLINE     ONLINE on rac01
ora.asm                                            ONLINE     ONLINE on rac01
ora.eons                                           ONLINE     ONLINE on rac01
ora.gsd                                            OFFLINE    OFFLINE
ora.net1.network                                   ONLINE     ONLINE on rac01
ora.oc4j                                           OFFLINE    OFFLINE
ora.ons                                            ONLINE     ONLINE on rac01
...
...
ora.rac02.vip                                      ONLINE     ONLINE on rac02
ora.rac10g.db                                      ONLINE     ONLINE on rac02
ora.rac10g.rac10g1.inst                            ONLINE     ONLINE on rac01
ora.rac10g.rac10g2.inst                            ONLINE     OFFLINE
ora.racpoc.askm1.svc                               ONLINE     ONLINE on rac01
ora.racpoc.db                                      ONLINE     ONLINE on rac01
ora.registry.acfs                                  ONLINE     ONLINE on rac01
ora.scan1.vip                                      ONLINE     ONLINE on rac01
ora.scan2.vip                                      ONLINE     ONLINE on rac02
ora.scan3.vip                                      ONLINE     ONLINE on rac02
ora.testdb.db                                      ONLINE     ONLINE on rac01
[grid@rac02 grid]$

Status :

[grid@rac01 ~]$ srvctl status scan
SCAN VIP scan1 is enabled
SCAN VIP scan1 is running on node rac01
SCAN VIP scan2 is enabled
SCAN VIP scan2 is running on node rac02
SCAN VIP scan3 is enabled
SCAN VIP scan3 is running on node rac02
[grid@rac01 ~]$ srvctl status scan_listener
SCAN Listener LISTENER_SCAN1 is enabled
SCAN listener LISTENER_SCAN1 is running on node rac01
SCAN Listener LISTENER_SCAN2 is enabled
SCAN listener LISTENER_SCAN2 is running on node rac02
SCAN Listener LISTENER_SCAN3 is enabled
SCAN listener LISTENER_SCAN3 is running on node rac02
[grid@rac01 ~]$

Hope it helps
SRI

My 11gR2 grid infrastructure completed with only one scan listener. The following log shows that ....

[grid@rac01 grid]$ srvctl config scan

SCAN name: rac-scan, Network: 1/133.22.38.0/255.255.255.0/eth2

SCAN VIP name: scan1, IP: /rac-scan.example.com/133.22.67.192

[grid@rac01 grid]$ srvctl config scan_listener

SCAN Listener LISTENER_SCAN1 exists. Port: TCP:1521

[grid@rac01 grid]$

[grid@rac01 grid]$ ping rac-scan.example.com

PING rac-scan.example.com (133.22.67.193) 56(84) bytes of data.

From rac01.seo.int (133.22.38.50) icmp_seq=2 Destination Host Unreachable

From rac01.seo.int (133.22.38.50) icmp_seq=3 Destination Host Unreachable

From rac01.seo.int (133.22.38.50) icmp_seq=4 Destination Host Unreachable

--- rac-scan.example.com ping statistics ---

6 packets transmitted, 0 received, +3 errors, 100% packet loss, time 5000ms

, pipe 3

[grid@rac01 grid]$ ping rac-scan

PING rac-scan.example.com (133.22.67.194) 56(84) bytes of data.

From rac01.seo.int (133.22.38.50) icmp_seq=2 Destination Host Unreachable

From rac01.seo.int (133.22.38.50) icmp_seq=3 Destination Host Unreachable

From rac01.seo.int (133.22.38.50) icmp_seq=4 Destination Host Unreachable

From rac01.seo.int (133.22.38.50) icmp_seq=6 Destination Host Unreachable

From rac01.seo.int (133.22.38.50) icmp_seq=7 Destination Host Unreachable

From rac01.seo.int (133.22.38.50) icmp_seq=8 Destination Host Unreachable

--- rac-scan.example.com ping statistics ---

11 packets transmitted, 0 received, +6 errors, 100% packet loss, time 10000ms

, pipe 3

[grid@rac01 grid]$ ping rac-scan

PING rac-scan.example.com (133.22.67.192) 56(84) bytes of data.

64 bytes from rac-scan.example.com (133.22.67.192): icmp_seq=1 ttl=64 time=0.039 ms

64 bytes from rac-scan.example.com (133.22.67.192): icmp_seq=2 ttl=64 time=0.054 ms

64 bytes from rac-scan.example.com (133.22.67.192): icmp_seq=3 ttl=64 time=0.049 ms

64 bytes from rac-scan.example.com (133.22.67.192): icmp_seq=4 ttl=64 time=0.063 ms

--- rac-scan.example.com ping statistics ---

4 packets transmitted, 4 received, 0% packet loss, time 3001ms

rtt min/avg/max/mdev = 0.039/0.051/0.063/0.010 ms

[grid@rac01 grid]$

[grid@rac01 grid]$ host rac-scan

rac-scan.example.com has address 133.22.67.194

rac-scan.example.com has address 133.22.67.193

rac-scan.example.com has address 133.22.67.192

[grid@rac01 grid]$ nslookup rac-scan

Server: 133.22.5.53

Address: 133.22.5.53#53

Name: rac-scan.example.com

Address: 133.22.67.194

Name: rac-scan.example.com

Address: 133.22.67.192

Name: rac-scan.example.com

Address: 133.22.67.193

[grid@rac01 grid]$

[root@rac01 network-scripts]# host rac-scan

rac-scan.example.com has address 133.22.67.194

rac-scan.example.com has address 133.22.67.193

rac-scan.example.com has address 133.22.67.192

[root@rac01 network-scripts]#

rac-scan.example.com IN A 133.22.67.194

IN A 133.22.67.193

IN A 133.22.67.192

[grid@rac02 grid]$ Check_CRS.txt

HA Resource Target State

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

ora.CRS.dg ONLINE ONLINE on rac01

ora.FRA.dg ONLINE ONLINE on rac01

ora.LISTENER.lsnr ONLINE ONLINE on rac01

ora.LISTENER_SCAN1.lsnr ONLINE ONLINE on rac01

ora.RACDB_DATA.dg ONLINE ONLINE on rac01

ora.asm ONLINE ONLINE on rac01

ora.eons ONLINE ONLINE on rac01

ora.gsd OFFLINE OFFLINE

ora.net1.network ONLINE ONLINE on rac01

ora.oc4j OFFLINE OFFLINE

ora.ons ONLINE ONLINE on rac01

...

ora.rac02.vip ONLINE ONLINE on rac02

ora.rac10g.db ONLINE ONLINE on rac02

ora.rac10g.rac10g1.inst ONLINE ONLINE on rac01

ora.rac10g.rac10g2.inst ONLINE OFFLINE

ora.racpoc.askm1.svc ONLINE ONLINE on rac01

ora.racpoc.db ONLINE ONLINE on rac01

ora.registry.acfs ONLINE ONLINE on rac01

ora.scan1.vip ONLINE ONLINE on rac01

ora.testdb.db ONLINE ONLINE on rac01

TESTDB =

(DESCRIPTION =

(ADDRESS = (PROTOCOL = TCP)(HOST = rac-scan.example.com)(PORT = 1521))

(CONNECT_DATA =

(SERVER = DEDICATED)

(SERVICE_NAME = testdb.example.com)

)

The above session data clearly shows there is only one scan listener configured. When we use scan name in tnsnames.ora, the client connections will be established to database only when the scan name resolves to valid IP address.

But with the above configurations, the client connections some times may fail when the SCAN is resolving to 133.22.67.193 or 133.22.67.194. It establishes connection only when the SCAN resolves to 133.22.67.192.

SQL> conn test/test@testdb ==> Connection failed. rac-scan resolves either to 133.22.67.193 or 133.22.67.194

ERROR:

ORA-12543: TNS:destination host unreachable

SQL>conn test/test@testdb ==> Connection established.rac-scan resolves to 133.22.67.192.

connected

SQL>

Now to avoid this , we need to create two more scan listeners.

[grid@rac01 grid]$ srvctl stop scan_listener

[grid@rac01 grid]$ srvctl stop scan

[grid@rac01 grid]$ srvctl config scan

SCAN name: rac-scan, Network: 1/133.22.38.0/255.255.255.0/eth2

SCAN VIP name: scan1, IP: /rac-scan.example.com/133.22.67.192

[grid@rac01 grid]$ srvctl config scan_listener

SCAN Listener LISTENER_SCAN1 exists. Port: TCP:1521

[grid@rac01 grid]$ which srvctl

/u01/home/11.2.0/grid/bin/srvctl

[grid@rac01 grid]$

[grid@rac01 ~]$ srvctl status scan
SCAN VIP scan1 is enabled
SCAN VIP scan1 is running on node rac01
SCAN VIP scan2 is enabled
SCAN VIP scan2 is running on node rac02
SCAN VIP scan3 is enabled
SCAN VIP scan3 is running on node rac02
[grid@rac01 ~]$ srvctl status scan_listener
SCAN Listener LISTENER_SCAN1 is enabled
SCAN listener LISTENER_SCAN1 is running on node rac01
SCAN Listener LISTENER_SCAN2 is enabled
SCAN listener LISTENER_SCAN2 is running on node rac02
SCAN Listener LISTENER_SCAN3 is enabled
SCAN listener LISTENER_SCAN3 is running on node rac02
[grid@rac01 ~]$

[grid@rac01 ~]$ ps -ef | grep -i tns
grid      9052     1 0 Aug09 ?        00:00:01 /o001/home/11.2.0/grid/bin/tnslsnr LISTENER -inherit
grid      9098     1 0 Aug09 ?        00:00:01 /o001/home/11.2.0/grid/bin/tnslsnr LISTENER_SCAN1 -inherit
grid     21388 20424 0 05:06 pts/1    00:00:00 grep -i tns
[grid@rac01 ~]$

[grid@rac02 ~]$ ps -ef | grep -i tns
grid      1323 1292 0 05:07 pts/0    00:00:00 grep -i tns
grid      9110     1 0 Aug09 ?        00:00:02 /o001/home/11.2.0/grid/bin/tnslsnr LISTENER -inherit
grid     17278     1 0 Aug09 ?        00:00:01 /o001/home/11.2.0/grid/bin/tnslsnr LISTENER_SCAN2 -inherit
grid     17285     1 0 Aug09 ?        00:00:01 /o001/home/11.2.0/grid/bin/tnslsnr LISTENER_SCAN3 -inherit
[grid@rac02 ~]$

[root@rac01 network-scripts]# /u01/home/11.2.0/grid/bin/srvctl modify scan -h

Modifies the SCAN name.

Usage: srvctl modify scan -n <scan_name>

-n <scan_name> Domain name qualified SCAN name

-h Print usage

[root@rac01 network-scripts]# /u01/home/11.2.0/grid/bin/srvctl modify scan -n rac-scan

[root@rac01 network-scripts]# /u01/home/11.2.0/grid/bin/srvctl config scan

SCAN name: rac-scan, Network: 1/133.22.38.0/255.255.255.0/eth2

SCAN VIP name: scan1, IP: /rac-scan.example.com/133.22.67.193

SCAN VIP name: scan2, IP: /rac-scan.example.com/133.22.67.194

SCAN VIP name: scan3, IP: /rac-scan.example.com/133.22.67.192

[root@rac01 network-scripts]# /u01/home/11.2.0/grid/bin/srvctl modify scan_listener -u

[root@rac01 network-scripts]# /u01/home/11.2.0/grid/bin/srvctl config scan_listener

SCAN Listener LISTENER_SCAN1 exists. Port: TCP:1521

SCAN Listener LISTENER_SCAN2 exists. Port: TCP:1521

SCAN Listener LISTENER_SCAN3 exists. Port: TCP:1521

[root@rac01 network-scripts]# /u01/home/11.2.0/grid/bin/srvctl start scan_listener

[root@rac01 network-scripts]# /u01/home/11.2.0/grid/bin/srvctl config scan

SCAN name: rac-scan, Network: 1/133.22.38.0/255.255.255.0/eth2

SCAN VIP name: scan1, IP: /rac-scan.example.com/133.22.67.193

SCAN VIP name: scan2, IP: /rac-scan.example.com/133.22.67.194

SCAN VIP name: scan3, IP: /rac-scan.example.com/133.22.67.192

[root@rac01 network-scripts]#

[grid@rac02 grid]$ Check_CRS.txt

HA Resource Target State

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

ora.CRS.dg ONLINE ONLINE on rac01

ora.FRA.dg ONLINE ONLINE on rac01

ora.LISTENER.lsnr ONLINE ONLINE on rac01

ora.LISTENER_SCAN1.lsnr ONLINE ONLINE on rac01

ora.LISTENER_SCAN2.lsnr ONLINE ONLINE on rac02

ora.LISTENER_SCAN3.lsnr ONLINE ONLINE on rac02

ora.RACDB_DATA.dg ONLINE ONLINE on rac01

ora.asm ONLINE ONLINE on rac01

ora.eons ONLINE ONLINE on rac01

ora.gsd OFFLINE OFFLINE

ora.net1.network ONLINE ONLINE on rac01

ora.oc4j OFFLINE OFFLINE

ora.ons ONLINE ONLINE on rac01

...

ora.rac02.vip ONLINE ONLINE on rac02

ora.rac10g.db ONLINE ONLINE on rac02

ora.rac10g.rac10g1.inst ONLINE ONLINE on rac01

ora.rac10g.rac10g2.inst ONLINE OFFLINE

ora.racpoc.askm1.svc ONLINE ONLINE on rac01

ora.racpoc.db ONLINE ONLINE on rac01

ora.registry.acfs ONLINE ONLINE on rac01

ora.scan1.vip ONLINE ONLINE on rac01

ora.scan2.vip ONLINE ONLINE on rac02

ora.scan3.vip ONLINE ONLINE on rac02

ora.testdb.db ONLINE ONLINE on rac01

[grid@rac02 grid]$

Hope it helps

SRI

askMLabs

How to identify the physical disk bound to the ASMLIB disk?

Written By askMLabs on Friday, September 24, 2010 | 6:13 PM

Exadata ?

Written By askMLabs on Thursday, September 23, 2010 | 6:15 PM

What is Exadata ? Why is it used ? who will use this ?

Is it replacement to Oracle RAC Server?

Do we just need to power on to use the server ?

Why is it so popular ? Is it really popular or getting popular ?

ORA-02298 - parent keys not found

Written By askMLabs on Wednesday, September 22, 2010 | 6:34 AM

Prepare Linux virtual machine for 11gR2 installation on Linux 5.x

Written By askMLabs on Tuesday, September 21, 2010 | 7:31 AM

RAC Nodes interconnect Checks

Written By askMLabs on Monday, September 20, 2010 | 7:01 AM

Physical Standby Switch Over Demo

Written By askMLabs on Thursday, September 16, 2010 | 7:46 AM

How to add more disk space to Linux Virtual Machine

Written By askMLabs on Wednesday, September 15, 2010 | 2:38 PM

VMware Server - Linux installation

Written By askMLabs on Sunday, September 12, 2010 | 11:37 AM

Linux installation on vmware server Part 1

Linux installation on vmware server part 2

How to add SCAN LISTENER in 11gR2

Written By askMLabs on Friday, September 3, 2010 | 1:36 PM

Popular post

Labels

Blog Archive

Certifications

Total Pageviews

About askMLabs

Written By askMLabs on Friday, September 24, 2010 | 6:13 PM

Written By askMLabs on Thursday, September 23, 2010 | 6:15 PM

What is Exadata ? Why is it used ? who will use this ?

Is it replacement to Oracle RAC Server?

Do we just need to power on to use the server ?

Why is it so popular ? Is it really popular or getting popular ?

Written By askMLabs on Wednesday, September 22, 2010 | 6:34 AM

Written By askMLabs on Tuesday, September 21, 2010 | 7:31 AM

Written By askMLabs on Monday, September 20, 2010 | 7:01 AM

Written By askMLabs on Thursday, September 16, 2010 | 7:46 AM

Written By askMLabs on Wednesday, September 15, 2010 | 2:38 PM

Written By askMLabs on Sunday, September 12, 2010 | 11:37 AM

Linux installation on vmware server Part 1

Linux installation on vmware server part 2

Written By askMLabs on Friday, September 3, 2010 | 1:36 PM

Popular post

Labels

Blog Archive

Subscribe To

Certifications

Total Pageviews

About askMLabs

Written By askMLabs on Friday, September 24, 2010 | 6:13 PM

Written By askMLabs on Thursday, September 23, 2010 | 6:15 PM

Written By askMLabs on Wednesday, September 22, 2010 | 6:34 AM

Written By askMLabs on Tuesday, September 21, 2010 | 7:31 AM

Written By askMLabs on Monday, September 20, 2010 | 7:01 AM

Written By askMLabs on Thursday, September 16, 2010 | 7:46 AM

Written By askMLabs on Wednesday, September 15, 2010 | 2:38 PM

Written By askMLabs on Sunday, September 12, 2010 | 11:37 AM

Written By askMLabs on Friday, September 3, 2010 | 1:36 PM