22 December, 2018

SCAN setup for Oracle 11g Release2(11gR2) in RAC implementation

Single client access name (SCAN) is introduced in Oracle 11g R2 Cluster Ready Software(CRS) to simplify the database connection strings that an Oracle Client uses to connect to an Oracle Cluster Database. I have received a few emails about SCAN setup issues and I have promised that I would come up with an article addressing the following questions:

  1. What is SCAN in Oracle 11g R2 RAC?
  2. How does SCAN work ?
  3. How to setup DNS for Oracle 11g R2 SCAN?
  4. How to setup GNS for Oracle 11g R2 SCAN?
  5. How to install Oracle 11g R2 RAC without SCAN Setup in DNS or GNS?
  6. How to move SCAN from DNS to /etc/hosts?
  7. How to move SCAN from /etc/hosts to DNS?
  8. How to rename SCAN in Cluster?
  9. How to rename SCAN Port in Cluster?

What is SCAN in Oracle 11g R2 RAC?

Single client access name (SCAN) is meant to facilitate single name for all Oracle clients to connect to the cluster database, irrespective of number of nodes and node location. Until now, we have to keep adding multiple address records in all clients tnsnames.ora, when a new node gets added to or deleted from the cluster.
Client TNS Entry in pre 11gR2 RAC environments:
[oid1@rac1 admin]$cat tnsnames.ora 
OIDDB =
(DESCRIPTION =
  (ADDRESS = (PROTOCOL = TCP)(HOST = rac1-vip.freeoraclehelp.com)(PORT = 1521))   
  (ADDRESS = (PROTOCOL = TCP)(HOST = rac2-vip.freeoraclehelp.com)(PORT = 1521))   
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = oid.freeoraclehelp.com)
   )
)

[oid1@rac1 admin]$tnsping oiddb

TNS Ping Utility for Linux: Version 11.2.0.2.0 - Production on 15-DEC-2011 14:21:18

Copyright (c) 1997, 2010, Oracle.  All rights reserved.

Used parameter files:


Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = rac1-vip.freeoraclehelp.com)(PORT = 1521)) (ADDRESS = (PROTOCOL = TCP)(HOST = rac2-vip.freeoraclehelp.com)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = oid.freeoraclehelp.com)))
OK (10 msec)
[oid1@rac1 admin]$sqlplus system@oiddb

SQL*Plus: Release 11.2.0.2.0 Production on Thu Dec 15 14:21:32 2011

Copyright (c) 1982, 2010, Oracle.  All rights reserved.

Enter password: 

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

SQL> 
As you see above, we got to list all the addresses of all DB nodes. If the cluster has got more than cluster nodes, we got to list each and every node out here. This need to be updated when a new node is added or existing node is removed.
OIDDB =
(DESCRIPTION =
  (ADDRESS = (PROTOCOL = TCP)(HOST = rac1-vip.freeoraclehelp.com)(PORT = 1521)) 
  (ADDRESS = (PROTOCOL = TCP)(HOST = rac2-vip.freeoraclehelp.com)(PORT = 1521)) 
  (ADDRESS = (PROTOCOL = TCP)(HOST = rac3-vip.freeoraclehelp.com)(PORT = 1521)) 
  (ADDRESS = (PROTOCOL = TCP)(HOST = rac4-vip.freeoraclehelp.com)(PORT = 1521)) 
  ....
  ....
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = oid.freeoraclehelp.com)
   )
)
This is big problem when the cluster has many nodes and end user application makes connections to the databases. SCAN would help us easily manage the DB Connection strings in Client tnsnames.ora files. Here is how a SCAN entry would appear:
[oid1@rac1 admin]$cat tnsnames.ora
OIDDB =
(DESCRIPTION =
  (ADDRESS = (PROTOCOL = TCP)(HOST = scan.freeoraclehelp.com)(PORT = 1521))   
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = oid.freeoraclehelp.com)
   )
)

[oid1@rac1 admin]$tnsping oiddb


TNS Ping Utility for Linux: Version 11.2.0.2.0 - Production on 15-DEC-2011 14:37:16

Copyright (c) 1997, 2010, Oracle.  All rights reserved.

Used parameter files:


Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = scan.freeoraclehelp.com)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = oid.freeoraclehelp.com)))
OK (0 msec)
[oid1@rac1 admin]$
[oid1@rac1 admin]$sqlplus system@OIDDB

SQL*Plus: Release 11.2.0.2.0 Production on Thu Dec 15 14:37:28 2011

Copyright (c) 1982, 2010, Oracle.  All rights reserved.

Enter password: 

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

SQL> 
So, you would see only one SCAN ADDRESS in tnsnames.ora files no matter how many nodes in the cluster are.
  (ADDRESS = (PROTOCOL = TCP)(HOST = scan.freeoraclehelp.com)(PORT = 1521)) 

How does SCAN work ?

SCAN is a GSD resource, which is managed by CRS. So, SCAN is pretty much aware of what's going on in the cluster.  Though Oracle documentation suggests that SCAN is a recommendation, but its a kind of mandatory as Oracle 11gR2 OUI would not proceed without it. SCAN is on top of VIPs, but you can directly connect to the local listener if you would like to bypass SCAN in client’s tnsnames.ora. Clients use SCAN name in tnsnames.ora to make the DB connection. SCAN Listener would forward the request to local listener that’s running on VIPs.
Oracle 11gR2 SCAN load balancing
You would see these redirections at the SQLPLUS trace below.
connect(6, {sa_family=AF_FILE, path="/var/run/nscd/socket"}, 110) = -1 ENOENT (No such file or directory)
connect(6, {sa_family=AF_FILE, path="/var/run/nscd/socket"}, 110) = -1 ENOENT (No such file or directory)
connect(8, {sa_family=AF_FILE, path="/var/run/nscd/socket"}, 110) = -1 ENOENT (No such file or directory)
connect(8, {sa_family=AF_FILE, path="/var/run/nscd/socket"}, 110) = -1 ENOENT (No such file or directory)
connect(8, {sa_family=AF_INET, sin_port=htons(0), sin_addr=inet_addr("192.168.1.10")}, 16) = 0
connect(9, {sa_family=AF_INET, sin_port=htons(53), sin_addr=inet_addr("192.168.1.20")}, 28) = 0
connect(9, {sa_family=AF_INET, sin_port=htons(53), sin_addr=inet_addr("192.168.1.20")}, 28) = 0
connect(9, {sa_family=AF_INET, sin_port=htons(53), sin_addr=inet_addr("192.168.1.20")}, 28) = 0
connect(9, {sa_family=AF_INET, sin_port=htons(53), sin_addr=inet_addr("192.168.1.20")}, 28) = 0
connect(9, {sa_family=AF_INET, sin_port=htons(53), sin_addr=inet_addr("192.168.1.20")}, 28) = 0
connect(9, {sa_family=AF_INET, sin_port=htons(53), sin_addr=inet_addr("192.168.1.20")}, 28) = 0
connect(9, {sa_family=AF_INET, sin_port=htons(1521), sin_addr=inet_addr("192.168.1.23")}, 16) = 0
connect(9, {sa_family=AF_INET, sin_port=htons(1521), sin_addr=inet_addr("192.168.1.24")}, 16) = 0
connect(9, {sa_family=AF_INET, sin_port=htons(1521), sin_addr=inet_addr("192.168.1.25")}, 16) = 0
connect(9, {sa_family=AF_INET, sin_port=htons(53), sin_addr=inet_addr("192.168.1.20")}, 28) = 0
connect(9, {sa_family=AF_INET, sin_port=htons(53), sin_addr=inet_addr("192.168.1.20")}, 28) = 0
connect(9, {sa_family=AF_INET, sin_port=htons(53), sin_addr=inet_addr("192.168.1.20")}, 28) = 0
connect(9, {sa_family=AF_INET, sin_port=htons(53), sin_addr=inet_addr("192.168.1.20")}, 28) = 0
connect(9, {sa_family=AF_INET, sin_port=htons(53), sin_addr=inet_addr("192.168.1.20")}, 28) = 0
connect(9, {sa_family=AF_INET, sin_port=htons(53), sin_addr=inet_addr("192.168.1.20")}, 28) = 0
connect(9, {sa_family=AF_INET, sin_port=htons(0), sin_addr=inet_addr("192.168.1.24")}, 16) = 0
connect(9, {sa_family=AF_INET, sin_port=htons(0), sin_addr=inet_addr("192.168.1.25")}, 16) = 0
connect(9, {sa_family=AF_INET, sin_port=htons(0), sin_addr=inet_addr("192.168.1.23")}, 16) = 0
connect(9, {sa_family=AF_INET, sin_port=htons(1521), sin_addr=inet_addr("192.168.1.25")}, 16) = -1 EINPROGRESS (Operation now in progress)
connect(9, {sa_family=AF_INET, sin_port=htons(1521), sin_addr=inet_addr("192.168.1.11")}, 16) = -1 EINPROGRESS (Operation now in progress)
192.168.1.25 is one of the SCAN IPs and 192.168.1.11 is the VIP of the first RAC node.
[oid1@rac1 ~]$ nslookup 192.168.1.11
Server:         192.168.1.20
Address:        192.168.1.20#53

11.1.168.192.in-addr.arpa       name = rac1-vip.freeoraclehelp.com.

[oid1@rac1 ~]$ nslookup 192.168.1.25
Server:         192.168.1.20
Address:        192.168.1.20#53

25.1.168.192.in-addr.arpa       name = scan.freeoraclehelp.com.

[oid1@rac1 ~]$ nslookup scan.freeoraclehelp.com
Server:         192.168.1.20
Address:        192.168.1.20#53

Name:   scan.freeoraclehelp.com
Address: 192.168.1.25
Name:   scan.freeoraclehelp.com
Address: 192.168.1.23
Name:   scan.freeoraclehelp.com
Address: 192.168.1.24

[oid1@rac1 ~]$ 
So, SCAN needs to resolve to one to three IP addresses with the same name. Oracle recommends using three IP Addresses for SCAN in DNS. There would be three SCAN listeners only, though the cluster has got dozens of nodes. SCAN listeners would be started from GRID Oracle Home, not the database/rdbms home. Since its part of a grid, this can be used for all the database in the cluster. So, we don't to run netca to create listeners in DB Homes anymore. If the default port, 1521, is used, Oracle instances (PMON) automatically registers with the SCAN listener. Here is a quick look at Oracle documentation's load balancing flow with SCAN:
Oracle 11gR2 SCAN load balancing

  1. PMON process of each instance registers the database services with the default listener on the local node and with each SCAN listener, which is specified by the REMOTE_LISTENER database parameter.
  2. Oracle client connects using SCAN name: myscan:1521/sales.example.com
  3. Client queries DNS to resolve scan_name.
  4. SCAN listener selects least loaded node (node in this example)
  5. The client connects to the local listener on node2. The local listener starts a dedicated server process for the connection to the database.
  6. The client connects directly to the dedicated server process on node2 and accesses the sales2 database instance.
SQL> show parameter local_listener

NAME                    TYPE        VALUE
--------------------   ---------    --------- 
local_listener          string     (DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)
       (HOST=192.168.1.11)(PORT=1521))))

SQL> show parameter remote_listener

NAME                    TYPE        VALUE
--------------------   ---------    --------- 
remote_listener        string      scan.freeoraclehelp.com:1521
SQL> 
After the installation, two SCAN listeners would be started on one node and another SCAN listener on another node in a two node cluster. 
[grid@rac1 ~]$srvctl config scan
SCAN name: scan.freeoraclehelp.com, Network: 1/192.168.1.0/255.255.255.0/eth0
SCAN VIP name: scan1, IP: /scan.freeoraclehelp.com/192.168.1.25
SCAN VIP name: scan2, IP: /scan.freeoraclehelp.com/192.168.1.23
SCAN VIP name: scan3, IP: /scan.freeoraclehelp.com/192.168.1.24
[grid@rac1 ~]$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
[grid@rac1 ~]$crsctl stat res -w "TYPE = ora.scan_listener.type"
NAME=ora.LISTENER_SCAN1.lsnr
TYPE=ora.scan_listener.type
TARGET=ONLINE
STATE=ONLINE on rac1

NAME=ora.LISTENER_SCAN2.lsnr
TYPE=ora.scan_listener.type
TARGET=ONLINE
STATE=ONLINE on rac2

NAME=ora.LISTENER_SCAN3.lsnr
TYPE=ora.scan_listener.type
TARGET=ONLINE
STATE=ONLINE on rac2

[grid@rac1 ~]$


[grid@rac1 ~]$ps -ef|grep tnsl
oracle    6873     1  0 13:11 ?        00:00:00 /oracle/product/11.2.0/11.2.0/grid/bin/tnslsnr LISTENER_SCAN1 -inherit
oracle    6892     1  0 13:11 ?        00:00:00 /oracle/product/11.2.0/11.2.0/grid/bin/tnslsnr LISTENER -inherit
[grid@rac1 ~]$

[grid@rac1 ~]$lsnrctl status LISTENER_SCAN1

LSNRCTL for Linux: Version 11.2.0.2.0 - Production on 15-DEC-2011 15:13:48

Copyright (c) 1991, 2010, Oracle.  All rights reserved.

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=LISTENER_SCAN1)))
STATUS of the LISTENER
------------------------
Alias                     LISTENER_SCAN1
Version                   TNSLSNR for Linux: Version 11.2.0.2.0 - Production
Start Date                15-DEC-2011 13:11:15
Uptime                    0 days 2 hr. 2 min. 33 sec
Trace Level               off
Security                  ON: Local OS Authentication
SNMP                      OFF
Listener Parameter File   /oracle/product/11.2.0/11.2.0/grid/network/admin/listener.ora
Listener Log File         /oracle/product/11.2.0/11.2.0/grid/log/diag/tnslsnr/rac1/listener_scan1/alert/log.xml
Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=LISTENER_SCAN1)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=192.168.1.25)(PORT=1521)))
Services Summary...
Service "oid.freeoraclehelp.com" has 2 instance(s).
  Instance "oid1", status READY, has 1 handler(s) for this service...
  Instance "oid2", status READY, has 1 handler(s) for this service...
The command completed successfully
[grid@rac1 ~]$

[grid@rac2 ~]$ps -ef|grep tnsl
oracle    5830     1  0 12:38 ?        00:00:00 /oracle/product/11.2.0/11.2.0/grid/bin/tnslsnr LISTENER_SCAN3 -inherit
oracle    5833     1  0 12:38 ?        00:00:00 /oracle/product/11.2.0/11.2.0/grid/bin/tnslsnr LISTENER_SCAN2 -inherit
oracle    5836     1  0 12:38 ?        00:00:00 /oracle/product/11.2.0/11.2.0/grid/bin/tnslsnr LISTENER -inherit
oracle    7423  7372  0 15:14 pts/1    00:00:00 grep tnsl
[grid@rac2 ~]$

[grid@rac2 ~]$lsnrctl status LISTENER_SCAN2

LSNRCTL for Linux: Version 11.2.0.2.0 - Production on 15-DEC-2011 15:14:04

Copyright (c) 1991, 2010, Oracle.  All rights reserved.

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=LISTENER_SCAN2)))
STATUS of the LISTENER
------------------------
Alias                     LISTENER_SCAN2
Version                   TNSLSNR for Linux: Version 11.2.0.2.0 - Production
Start Date                15-DEC-2011 12:38:45
Uptime                    0 days 2 hr. 35 min. 19 sec
Trace Level               off
Security                  ON: Local OS Authentication
SNMP                      OFF
Listener Parameter File   /oracle/product/11.2.0/11.2.0/grid/network/admin/listener.ora
Listener Log File         /oracle/product/11.2.0/11.2.0/grid/log/diag/tnslsnr/rac2/listener_scan2/alert/log.xml
Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=LISTENER_SCAN2)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=192.168.1.23)(PORT=1521)))
Services Summary...
Service "oid.freeoraclehelp.com" has 2 instance(s).
  Instance "oid1", status READY, has 1 handler(s) for this service...
  Instance "oid2", status READY, has 1 handler(s) for this service...
The command completed successfully
[grid@rac2 ~]$lsnrctl status LISTENER_SCAN3

LSNRCTL for Linux: Version 11.2.0.2.0 - Production on 15-DEC-2011 15:14:06

Copyright (c) 1991, 2010, Oracle.  All rights reserved.

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=LISTENER_SCAN3)))
STATUS of the LISTENER
------------------------
Alias                     LISTENER_SCAN3
Version                   TNSLSNR for Linux: Version 11.2.0.2.0 - Production
Start Date                15-DEC-2011 12:38:45
Uptime                    0 days 2 hr. 35 min. 21 sec
Trace Level               off
Security                  ON: Local OS Authentication
SNMP                      OFF
Listener Parameter File   /oracle/product/11.2.0/11.2.0/grid/network/admin/listener.ora
Listener Log File         /oracle/product/11.2.0/11.2.0/grid/log/diag/tnslsnr/rac2/listener_scan3/alert/log.xml
Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=LISTENER_SCAN3)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=192.168.1.24)(PORT=1521)))
Services Summary...
Service "oid.freeoraclehelp.com" has 2 instance(s).
  Instance "oid1", status READY, has 1 handler(s) for this service...
  Instance "oid2", status READY, has 1 handler(s) for this service...
The command completed successfully
[grid@rac2 ~]$ 

How to setup DNS for Oracle 11g R2 SCAN?

Oracle recommends three IP Addresses be used for SCAN. SCAN setup has become a big problem for RAC Setups at home or test environments.. especially where there is no real DNS Server in the network. If you're trying to set up 11gR2 RAC at home on VMWare, Oracle Virtualbox, Oracle VM, Xen..etc, you probably hit this situation. Oracle 11gR2 RAC SCAN - DNS (bind) Configuration on Linux would explain setting up a DNS daemon on one of the RAC nodes. Of course, you wouldn’t want to run bind in your production RAC nodes. You would have DNS Administrator add three A records for the same name, SCAN.  Once DNS is set up, make sure that all three A records are returned in nslookup.
[oid1@rac1 ~]$ nslookup scan.freeoraclehelp.com
Server:         192.168.1.20
Address:        192.168.1.20#53

Name:   scan.freeoraclehelp.com
Address: 192.168.1.25
Name:   scan.freeoraclehelp.com
Address: 192.168.1.23
Name:   scan.freeoraclehelp.com
Address: 192.168.1.24

[oid1@rac1 ~]$ 

How to setup GNS for Oracle 11g R2 SCAN?

Grid Naming Service (GNS) is another new service introduced in Oracle RAC 11g R2. With GNS, Oracle Cluster Software (CRS) can manage DHCP and DNS Services for the dynamic node registration and configuration. If you’re go to use GNS for the cluster, you should set up a different subdomain in the main DNS Server and delegate name lookups in the subdomain to GNS. For example, if your company domain is freeoraclehelp.com, you’re going to create grid.freeoraclehelp.com in the DNS Servers of freeoraclehelp.com and delegate lookups for grid.freeoraclehelp.com to GNS VIP.
# Delegate to GNS Server in DNS Servers
grid.freeoraclehelp.com NS gnsvip.freeoraclehelp.com
# Address Record for the GNS VIP
gnsvip.freeoraclehelp.com. 192.168.1.31
Once subdomain is complete, lookups for the subdomain names from the main domain would be forwarded to GNS Services. You would be entering the subdomain name and GNS VIP during the Grid Infrastructure installation.
Oracle 11gR2 SCAN GNS Configuration
Oracle 11gR2 RAC Installation with GNS, without SCAN explains the Oracle 11g R2 RAC installation with GNS.

How to install Oracle 11g R2 RAC without SCAN Setup in DNS or GNS?

This is mostly suited in three tier web environments where end users would not connect to the database directly and Application Servers can use Local listener Connections (10g like connections over VIPs). So, you would not see much benefits using SCAN here.  Whatever may be your reasons, if you’d like to use SCAN from hosts file (no DNS changes or No GNS Setup), here is how to do that.
If you would like to Install Oracle 11g R2 RAC without SCAN setup in DNS, you would need to create an entry in /etc/hosts for the scan name to point to a different IP address (other than Public IP, Private IP, and VIP addresses) and supply this name for SCAN Prompts during the installation. Make sure that you pick the new IP address in the same subnet as that of VIP addresses and that it is not currently in use.
[root@rac1 ~]# cat /etc/hosts
# Do not remove the following line, or various programs
# that require network functionality will fail.
127.0.0.1               localhost.localdomain localhost
# Public
192.168.1.10   rac1.freeoraclehelp.com     rac1
192.168.1.20   rac2.freeoraclehelp.com     rac2
#Private
192.168.2.10   rac1-priv.freeoraclehelp.com  rac1-priv
192.168.2.20   rac2-priv.freeoraclehelp.com  rac2-priv
#Virtual
192.168.1.11   rac1-vip.freeoraclehelp.com   rac1-vip
192.168.1.22   rac2-vip.freeoraclehelp.com   rac2-vip
#SCAN Entry
192.168.1.25   scan.freeoraclehelp.com   scan
[root@rac1 ~]# 
This would let you proceed with the installation, however would hit the following error in Cluster Verify Utility, which runs after the installation is complete. Of course, you can ignore this error safely if you’re using SCAN entry from hosts file.
INFO: PRVF-4664 : Found inconsistent name resolution entries for SCAN name "scan.freeoraclehelp.com"
INFO: ERROR:
INFO: PRVF-4657 : Name resolution setup check for "scanclunm" (IP address: 192.168.1.25) failed
INFO: ERROR:
INFO: PRVF-4664 : Found inconsistent name resolution entries for SCAN name "scan.freeoraclehelp.com"

How to move SCAN from DNS to /etc/hosts?

After the installation of the cluster with DNS SCAN name, If you need to switch from DNS SCAN name to local /etc/hosts entry SCAN Name, here are steps. Of course, you cannot have multiple IP addresses in hosts file. So, it would be resolved to only one IP address in this method.
1. Stop the SCAN: Source Grid home, srvctl stop scan_listener, and srvctl stop scan then.
[grid@rac1 ~]$srvctl stop scan
PRCR-1065 : Failed to stop resource ora.scan1.vip
CRS-2529: Unable to act on 'ora.scan1.vip' because that would require stopping or relocating 'ora.LISTENER_SCAN1.lsnr', but the force option was not specified
PRCR-1065 : Failed to stop resource ora.scan2.vip
CRS-2529: Unable to act on 'ora.scan2.vip' because that would require stopping or relocating 'ora.LISTENER_SCAN2.lsnr', but the force option was not specified
PRCR-1065 : Failed to stop resource ora.scan3.vip
CRS-2529: Unable to act on 'ora.scan3.vip' because that would require stopping or relocating 'ora.LISTENER_SCAN3.lsnr', but the force option was not specified
Stop the scan_listener first and then stop scan resource
[grid@rac1 ~]$srvctl stop scan_listener
[grid@rac1 ~]$srvctl stop scan
[grid@rac1 ~]$ps -ef|grep tnsl
oracle    6892     1  0 13:11 ?        00:00:00 /oracle/product/11.2.0/11.2.0/grid/bin/tnslsnr LISTENER -inherit
oracle    6940     1  0 13:11 ?        00:00:00 /oracle/product/10.2.0/rdbms/bin/tnslsnr OID_RAC1 -inherit
[grid@rac1 ~]$
2. Add an entry in /etc/hosts file for SCAN name:
[root@rac2 ~]# nslookup scan.freeoraclehelp.com
Server:         192.168.1.20
Address:        192.168.1.20#53

Name:   scan.freeoraclehelp.com
Address: 192.168.1.25
Name:   scan.freeoraclehelp.com
Address: 192.168.1.23
Name:   scan.freeoraclehelp.com
Address: 192.168.1.24

[root@rac2 ~]# vi /etc/resolv.conf 

#nameserver 192.168.1.20  # Second RAC Node
nameserver 192.168.1.1   # Primary DNS in the domain
search freeoraclehelp.com # Local Domain


[root@rac1 ~]# vi /etc/resolv.conf 

#nameserver 192.168.1.20  # Second RAC Node
nameserver 192.168.1.1   # Primary DNS in the domain
search freeoraclehelp.com # Local Domain


[root@rac2 ~]# cat /etc/hosts
# Do not remove the following line, or various programs
# that require network functionality will fail.
127.0.0.1       localhost.localdomain   localhost
# Public
192.168.1.10   rac1.freeoraclehelp.com     rac1
192.168.1.20   rac2.freeoraclehelp.com     rac2
#Private
192.168.2.10   rac1-priv.freeoraclehelp.com  rac1-priv
192.168.2.20   rac2-priv.freeoraclehelp.com  rac2-priv
#Virtual
192.168.1.11   rac1-vip.freeoraclehelp.com   rac1-vip
192.168.1.22   rac2-vip.freeoraclehelp.com   rac2-vip
#SCAN Entry
192.168.1.25   scan.freeoraclehelp.com   scan
[root@rac2 ~]# 

[root@rac1 ~]# cat /etc/hosts
# Do not remove the following line, or various programs
# that require network functionality will fail.
127.0.0.1               localhost.localdomain localhost
# Public
192.168.1.10   rac1.freeoraclehelp.com     rac1
192.168.1.20   rac2.freeoraclehelp.com     rac2
#Private
192.168.2.10   rac1-priv.freeoraclehelp.com  rac1-priv
192.168.2.20   rac2-priv.freeoraclehelp.com  rac2-priv
#Virtual
192.168.1.11   rac1-vip.freeoraclehelp.com   rac1-vip
192.168.1.22   rac2-vip.freeoraclehelp.com   rac2-vip
#SCAN Entry
192.168.1.25   scan.freeoraclehelp.com   scan
[root@rac1 ~]# 

[root@rac1 ~]# nslookup scan.freeoraclehelp.com
Server:         192.168.1.1
Address:        192.168.1.1#53

** server can't find scan.freeoraclehelp.com: NXDOMAIN

[root@rac1 ~]# ping scan.freeoraclehelp.com
PING scan.freeoraclehelp.com (192.168.1.25) 56(84) bytes of data.
From rac1.freeoraclehelp.com (192.168.1.10) icmp_seq=0 Destination Host Unreachable
From rac1.freeoraclehelp.com (192.168.1.10) icmp_seq=1 Destination Host Unreachable
From rac1.freeoraclehelp.com (192.168.1.10) icmp_seq=2 Destination Host Unreachable

--- scan.freeoraclehelp.com ping statistics ---
4 packets transmitted, 0 received, +3 errors, 100% packet loss, time 3027ms
, pipe 4
[root@rac1 ~]# 


[root@rac2 ~]#  nslookup scan.freeoraclehelp.com
Server:         192.168.1.1
Address:        192.168.1.1#53

** server can't find scan.freeoraclehelp.com: NXDOMAIN

[root@rac2 ~]# ping scan.freeoraclehelp.com
PING scan.freeoraclehelp.com (192.168.1.25) 56(84) bytes of data.
From rac2.freeoraclehelp.com (192.168.1.20) icmp_seq=0 Destination Host Unreachable
From rac2.freeoraclehelp.com (192.168.1.20) icmp_seq=1 Destination Host Unreachable
From rac2.freeoraclehelp.com (192.168.1.20) icmp_seq=2 Destination Host Unreachable

--- scan.freeoraclehelp.com ping statistics ---
11 packets transmitted, 0 received, +9 errors, 100% packet loss, time 10071ms
, pipe 4
[root@rac2 ~]# 
3. Configure the Cluster to take the new VIPs
As root user on one of the cluster nodes (not needed on both the nodes):
[root@rac1 ~]# /oracle/product/11.2.0/11.2.0/grid/bin/srvctl modify scan -n scan.freeoraclehelp.com
As grid user on one of the cluster nodes (not needed on both the nodes):
[grid@rac1 ~]$ srvctl modify scan_listener -u
[grid@rac1 ~]$ srvctl start scan_listener
Verify that only one SCAN Listener is started and IP is right. 
[grid@rac1 ~]$ps -ef|grep tnsl
oracle    7961     1  0 15:44 ?        00:00:00 /oracle/product/11.2.0/11.2.0/grid/bin/tnslsnr LISTENER -inherit
oracle    9148  4994  0 16:10 pts/2    00:00:00 grep tnsl
[grid@rac1 ~]$


[grid@rac2 ~]$ps -ef|grep tnsl
oracle    9014     1  0 15:52 ?        00:00:00 /oracle/product/11.2.0/11.2.0/grid/bin/tnslsnr LISTENER -inherit
oracle    9451     1  0 16:10 ?        00:00:00 /oracle/product/11.2.0/11.2.0/grid/bin/tnslsnr LISTENER_SCAN1 -inherit
oracle    9463  7372  0 16:10 pts/1    00:00:00 grep tnsl
[grid@rac2 ~]$lsnrctl status LISTENER_SCAN1

LSNRCTL for Linux: Version 11.2.0.2.0 - Production on 15-DEC-2011 16:10:39

Copyright (c) 1991, 2010, Oracle.  All rights reserved.

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=LISTENER_SCAN1)))
STATUS of the LISTENER
------------------------
Alias                     LISTENER_SCAN1
Version                   TNSLSNR for Linux: Version 11.2.0.2.0 - Production
Start Date                15-DEC-2011 16:10:14
Uptime                    0 days 0 hr. 0 min. 25 sec
Trace Level               off
Security                  ON: Local OS Authentication
SNMP                      OFF
Listener Parameter File   /oracle/product/11.2.0/11.2.0/grid/network/admin/listener.ora
Listener Log File         /oracle/product/11.2.0/11.2.0/grid/log/diag/tnslsnr/rac2/listener_scan1/alert/log.xml
Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=LISTENER_SCAN1)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=192.168.1.25)(PORT=1521)))
The listener supports no services
The command completed successfully
[grid@rac2 ~]$

[grid@rac1 ~]$srvctl config scan
SCAN name: scan.freeoraclehelp.com, Network: 1/192.168.1.0/255.255.255.0/eth0
SCAN VIP name: scan1, IP: /scan.freeoraclehelp.com/192.168.1.25
[grid@rac1 ~]$srvctl config scan_listener
SCAN Listener LISTENER_SCAN1 exists. Port: TCP:1521
[grid@rac1 ~]$

How to move SCAN from /etc/hosts to DNS?

After the installation of the cluster with SCAN with one IP address in /etc/hosts, If you need to switch to DNS SCAN, here are steps.
1. Stop the SCAN: Source Grid home, srvctl stop scan_listener, and srvctl stop scan then.
[grid@rac1 ~]$srvctl stop scan_listener
[grid@rac1 ~]$srvctl stop scan
2. Remove/comment the entry in /etc/hosts file for SCAN and setup a DNS name with multiple A (three A records), if it is not already setup. I have the SCAN setup in a DNS server that runs on the second cluster node. I just have to use it (in resolv.conf). For more information about DNS Setup, refer to How to setup DNS for Oracle 11g R2 SCAN?
[root@rac1 ~]# cat /etc/hosts
# Do not remove the following line, or various programs
# that require network functionality will fail.
127.0.0.1               localhost.localdomain localhost
# Public
192.168.1.10   rac1.freeoraclehelp.com     rac1
192.168.1.20   rac2.freeoraclehelp.com     rac2
#Private
192.168.2.10   rac1-priv.freeoraclehelp.com  rac1-priv
192.168.2.20   rac2-priv.freeoraclehelp.com  rac2-priv
#Virtual
192.168.1.11   rac1-vip.freeoraclehelp.com   rac1-vip
192.168.1.22   rac2-vip.freeoraclehelp.com   rac2-vip
#SCAN Entry
#192.168.1.25   scan.freeoraclehelp.com   scan
[root@rac1 ~]# 

[root@rac2 ~]# cat /etc/hosts
# Do not remove the following line, or various programs
# that require network functionality will fail.
127.0.0.1       localhost.localdomain   localhost
# Public
192.168.1.10   rac1.freeoraclehelp.com     rac1
192.168.1.20   rac2.freeoraclehelp.com     rac2
#Private
192.168.2.10   rac1-priv.freeoraclehelp.com  rac1-priv
192.168.2.20   rac2-priv.freeoraclehelp.com  rac2-priv
#Virtual
192.168.1.11   rac1-vip.freeoraclehelp.com   rac1-vip
192.168.1.22   rac2-vip.freeoraclehelp.com   rac2-vip
#SCAN Entry
#192.168.1.25   scan.freeoraclehelp.com   scan
[root@rac2 ~]# 

[root@rac2 ~]# vi /etc/resolv.conf 

nameserver 192.168.1.20  # Second RAC Node
nameserver 192.168.1.1   # Primary DNS in the domain
search freeoraclehelp.com # Local Domain


[root@rac1 ~]# vi /etc/resolv.conf 

nameserver 192.168.1.20  # Second RAC Node
nameserver 192.168.1.1   # Primary DNS in the domain
search freeoraclehelp.com # Local Domain

[root@rac2 ~]# nslookup scan.freeoraclehelp.com
Server:         192.168.1.20
Address:        192.168.1.20#53

Name:   scan.freeoraclehelp.com
Address: 192.168.1.25
Name:   scan.freeoraclehelp.com
Address: 192.168.1.23
Name:   scan.freeoraclehelp.com
Address: 192.168.1.24
3. Configure the Cluster to take the new VIPs
As root user on one of the cluster nodes (not needed on both the nodes):
[root@rac1 ~]# /oracle/product/11.2.0/11.2.0/grid/bin/srvctl modify scan -n scan.freeoraclehelp.com
As grid user on one of the cluster nodes (not needed on both the nodes):
[grid@rac1 ~]$ srvctl modify scan_listener -u
[grid@rac1 ~]$ srvctl start scan_listener
Verify that configuration is right and three SCAN listeners are started. 
[grid@rac1 ~]$srvctl config scan
SCAN name: scan.freeoraclehelp.com, Network: 1/192.168.1.0/255.255.255.0/eth0
SCAN VIP name: scan1, IP: /scan.freeoraclehelp.com/192.168.1.25
SCAN VIP name: scan2, IP: /scan.freeoraclehelp.com/192.168.1.23
SCAN VIP name: scan3, IP: /scan.freeoraclehelp.com/192.168.1.24
[grid@rac1 ~]$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
[grid@rac1 ~]$ps -ef|grep tnsl
oracle    6873     1  0 13:11 ?        00:00:00 /oracle/product/11.2.0/11.2.0/grid/bin/tnslsnr LISTENER_SCAN1 -inherit
oracle    6892     1  0 13:11 ?        00:00:00 /oracle/product/11.2.0/11.2.0/grid/bin/tnslsnr LISTENER -inherit
[grid@rac1 ~]$

[grid@rac1 ~]$lsnrctl status LISTENER_SCAN1

LSNRCTL for Linux: Version 11.2.0.2.0 - Production on 15-DEC-2011 15:13:48

Copyright (c) 1991, 2010, Oracle.  All rights reserved.

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=LISTENER_SCAN1)))
STATUS of the LISTENER
------------------------
Alias                     LISTENER_SCAN1
Version                   TNSLSNR for Linux: Version 11.2.0.2.0 - Production
Start Date                15-DEC-2011 13:11:15
Uptime                    0 days 2 hr. 2 min. 33 sec
Trace Level               off
Security                  ON: Local OS Authentication
SNMP                      OFF
Listener Parameter File   /oracle/product/11.2.0/11.2.0/grid/network/admin/listener.ora
Listener Log File         /oracle/product/11.2.0/11.2.0/grid/log/diag/tnslsnr/rac1/listener_scan1/alert/log.xml
Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=LISTENER_SCAN1)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=192.168.1.25)(PORT=1521)))
Services Summary...
Service "oid.freeoraclehelp.com" has 2 instance(s).
  Instance "oid1", status READY, has 1 handler(s) for this service...
  Instance "oid2", status READY, has 1 handler(s) for this service...
The command completed successfully
[grid@rac1 ~]$

[grid@rac2 ~]$ ps -ef|grep tnsl
oracle    5830     1  0 12:38 ?        00:00:00 /oracle/product/11.2.0/11.2.0/grid/bin/tnslsnr LISTENER_SCAN3 -inherit
oracle    5833     1  0 12:38 ?        00:00:00 /oracle/product/11.2.0/11.2.0/grid/bin/tnslsnr LISTENER_SCAN2 -inherit
oracle    5836     1  0 12:38 ?        00:00:00 /oracle/product/11.2.0/11.2.0/grid/bin/tnslsnr LISTENER -inherit
oracle    7423  7372  0 15:14 pts/1    00:00:00 grep tnsl
[grid@rac2 ~]$

[grid@rac2 ~]$ lsnrctl status LISTENER_SCAN2

LSNRCTL for Linux: Version 11.2.0.2.0 - Production on 15-DEC-2011 15:14:04

Copyright (c) 1991, 2010, Oracle.  All rights reserved.

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=LISTENER_SCAN2)))
STATUS of the LISTENER
------------------------
Alias                     LISTENER_SCAN2
Version                   TNSLSNR for Linux: Version 11.2.0.2.0 - Production
Start Date                15-DEC-2011 12:38:45
Uptime                    0 days 2 hr. 35 min. 19 sec
Trace Level               off
Security                  ON: Local OS Authentication
SNMP                      OFF
Listener Parameter File   /oracle/product/11.2.0/11.2.0/grid/network/admin/listener.ora
Listener Log File         /oracle/product/11.2.0/11.2.0/grid/log/diag/tnslsnr/rac2/listener_scan2/alert/log.xml
Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=LISTENER_SCAN2)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=192.168.1.23)(PORT=1521)))
Services Summary...
Service "oid.freeoraclehelp.com" has 2 instance(s).
  Instance "oid1", status READY, has 1 handler(s) for this service...
  Instance "oid2", status READY, has 1 handler(s) for this service...
The command completed successfully
[grid@rac2 ~]$ lsnrctl status LISTENER_SCAN3

LSNRCTL for Linux: Version 11.2.0.2.0 - Production on 15-DEC-2011 15:14:06

Copyright (c) 1991, 2010, Oracle.  All rights reserved.

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=LISTENER_SCAN3)))
STATUS of the LISTENER
------------------------
Alias                     LISTENER_SCAN3
Version                   TNSLSNR for Linux: Version 11.2.0.2.0 - Production
Start Date                15-DEC-2011 12:38:45
Uptime                    0 days 2 hr. 35 min. 21 sec
Trace Level               off
Security                  ON: Local OS Authentication
SNMP                      OFF
Listener Parameter File   /oracle/product/11.2.0/11.2.0/grid/network/admin/listener.ora
Listener Log File         /oracle/product/11.2.0/11.2.0/grid/log/diag/tnslsnr/rac2/listener_scan3/alert/log.xml
Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=LISTENER_SCAN3)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=192.168.1.24)(PORT=1521)))
Services Summary...
Service "oid.freeoraclehelp.com" has 2 instance(s).
  Instance "oid1", status READY, has 1 handler(s) for this service...
  Instance "oid2", status READY, has 1 handler(s) for this service...
The command completed successfully
[grid@rac2 ~]$

How to rename SCAN in Cluster?

After the installation of the Oracle 11g R2 cluster with SCAN, If you need to change the scan name from scan.freeoraclehelp.com to newscan.freeoraclehelp.com, here is how.
1. Stop the SCAN: Source Grid home, srvctl stop scan_listener, and srvctl stop scan then.
[grid@rac1 ~]$ srvctl stop scan_listener
[grid@rac1 ~]$ srvctl stop scan
2.  Configure the new SCAN in your DNS, or /etc/hosts, or GNS and make sure that lookups are working for the new name.
[root@rac2 ~]# nslookup newscan.freeoraclehelp.com
Server:         192.168.1.20
Address:        192.168.1.20#53

Name:   newscan.freeoraclehelp.com
Address: 192.168.1.32
Name:   newscan.freeoraclehelp.com
Address: 192.168.1.33
Name:   newscan.freeoraclehelp.com
Address: 192.168.1.34
[root@rac2 ~]#
3. Configure the Cluster to take the new VIPs
As root user on one of the cluster nodes (not needed on both the nodes):
[root@rac1 ~]# /oracle/product/11.2.0/11.2.0/grid/bin/srvctl modify scan -n newscan.freeoraclehelp.com
[root@rac1 ~]# 
As grid user on one of the cluster nodes (not needed on both the nodes):
[grid@rac1 ~]$ srvctl modify scan_listener -u
[grid@rac1 ~]$ srvctl start scan_listener
Verify that configuration is right and three SCAN listeners are started. 
[grid@rac1 ~]$ srvctl config scan
SCAN name: newscan.freeoraclehelp.com, Network: 1/192.168.1.0/255.255.255.0/eth0
SCAN VIP name: scan1, IP: /newscan.freeoraclehelp.com/192.168.1.34
SCAN VIP name: scan2, IP: /newscan.freeoraclehelp.com/192.168.1.33
SCAN VIP name: scan3, IP: /newscan.freeoraclehelp.com/192.168.1.32
[grid@rac1 ~]$ 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
[grid@rac1 ~]$

How to rename SCAN Port in Cluster?

As grid user, source the grid environment to make sure $GRID_HOME/bin is in PATH and
1. Modify SCAN listener port:
[oid1@rac1 ~]$ srvctl modify scan_listener -p 1522
2. Restart SCAN listener so the new port will be effective:
[oid1@rac1 ~]$ srvctl stop scan_listener
[oid1@rac1 ~]$ srvctl start scan_listener
3. Confirm the change:
[oid1@rac1 ~]$ srvctl config scan_listener
SCAN Listener LISTENER_SCAN1 exists. Port: TCP:1522
SCAN Listener LISTENER_SCAN2 exists. Port: TCP:1522
SCAN Listener LISTENER_SCAN3 exists. Port: TCP:1522
[oid1@rac1 ~]$

Oracle Row Chaining and Migration

Overview

If you notice poor performance in your Oracle database Row Chaining and Migration may be one of several reasons, but we can prevent some of them by properly designing and/or diagnosing the database.
Row Migration & Row Chaining are two potential problems that can be prevented. By suitably diagnosing, we can improve database performance. The main considerations are:
  • What is Row Migration & Row Chaining ?
  • How to identify Row Migration & Row Chaining ?
  • How to avoid Row Migration & Row Chaining ?
Migrated rows affect OLTP systems which use indexed reads to read singleton rows. In the worst case, you can add an extra I/O to all reads which would be really bad. Truly chained rows affect index reads and full table scans.

Oracle Block

The Operating System Block size is the minimum unit of operation (read /write) by the OS and is a property of the OS file system. While creating an Oracle database we have to choose the «Data Base Block Size» as a multiple of the Operating System Block size. The minimum unit of operation (read /write) by the Oracle database would be this «Oracle block», and not the OS block. Once set, the «Data Base Block Size» cannot be changed during the life of the database (except in case of Oracle 9i). To decide on a suitable block size for the database, we take into consideration factors like the size of the database and the concurrent number of transactions expected.
The database block has the following structure (within the whole database structure)
 Header
Header contains the general information about the data i.e. block address, and type of segments (table, index etc). It Also contains the information about table and the actual row (address) which that holds the data.
Free Space
Space allocated for future update/insert operations. Generally affected by the values of PCTFREE and PCTUSEDparameters.
Data
 Actual row data.
FREELIST, PCTFREE and PCTUSED
While creating / altering any table/index, Oracle used two storage parameters for space control.
  • PCTFREE - The percentage of space reserved for future update of existing data.
     
  • PCTUSED - The percentage of minimum space used for insertion of new row data.
    This value determines when the block gets back into the FREELISTS structure.
     
  • FREELIST - Structure where Oracle maintains a list of all free available blocks.
Oracle will first search for a free block in the FREELIST and then the data is inserted into that block. The availability of the block in the FREELIST is decided by the PCTFREE value. Initially an empty block will be listed in the FREELIST structure, and it will continue to remain there until the free space reaches the PCTFREE value.
When the free space reach the PCTFREE value the block is removed from the FREELIST, and it is re-listed in the FREELIST table when the volume of data in the block comes below the PCTUSED value.
Oracle use FREELIST to increase the performance. So for every insert operation, oracle needs to search for the free blocks only from the FREELIST structure instead of searching all blocks.

Row Migration

We will migrate a row when an update to that row would cause it to not fit on the block anymore (with all of the other data that exists there currently).  A migration means that the entire row will move and we just leave behind the «forwarding address». So, the original block just has the rowid of the new block and the entire row is moved.
Full Table Scans are not affected by migrated rows
The forwarding addresses are ignored. We know that as we continue the full scan, we'll eventually get to that row so we can ignore the forwarding address and just process the row when we get there.  Hence, in a full scan migrated rows don't cause us to really do any extra work -- they are meaningless.
Index Read will cause additional IO's on migrated rows
When we Index Read into a table, then a migrated row will cause additional IO's. That is because the index will tell us «goto file X, block Y, slot Z to find this row». But when we get there we find a message that says «well, really goto file A, block B, slot C to find this row». We have to do another IO (logical or physical) to find the row.

Row Chaining

A row is too large to fit into a single database block. For example, if you use a 4KB blocksize for your database, and you need to insert a row of 8KB into it, Oracle will use 3 blocks and store the row in pieces. Some conditions that will cause row chaining are: Tables whose rowsize exceeds the blocksize. Tables with LONG and LONG RAW columns are prone to having chained rows. Tables with more then 255 columns will have chained rows as Oracle break wide tables up into pieces. So, instead of just having a forwarding address on one block and the data on another we have data on two or more blocks.
Chained rows affect us differently. Here, it depends on the data we need. If we had a row with two columns that was spread over two blocks, the query:
SELECT column1 FROM table
where column1 is in Block 1, would not cause any «table fetch continued row»It would not actually have to get column2, it would not follow the chained row all of the way out. On the other hand, if we ask for:
SELECT column2 FROM table
and column2 is in Block 2 due to row chaining, then you would in fact see a «table fetch continued row»
Example
The following example was published by Tom Kyte, it will show row migration and chaining. We are using an 4k block size:
SELECT name,value
  FROM v$parameter
 WHERE name = 'db_block_size';

NAME                 VALUE
--------------      ------
db_block_size         4096
Create the following table with CHAR fixed columns:
CREATE TABLE row_mig_chain_demo (
  x int
 PRIMARY KEY,
  a CHAR(1000),
  b CHAR(1000),
  c CHAR(1000),
  d CHAR(1000),
  e CHAR(1000)
);
That is our table. The CHAR(1000)'s will let us easily cause rows to migrate or chain. We used 5 columns a,b,c,d,e so that the total rowsize can grow to about 5K, bigger than one block, ensuring we can truly chain a row.
INSERT INTO row_mig_chain_demo (x) VALUES (1);
INSERT INTO row_mig_chain_demo (x) VALUES (2);
INSERT INTO row_mig_chain_demo (x) VALUES (3);
COMMIT;
We are not interested about seeing a,b,c,d,e - just fetching them. They are really wide so we'll surpress their display.
column a noprint
column b noprint
column c noprint
column d noprint
column e noprint
SELECT * FROM row_mig_chain_demo;
         X
----------
         1
         2
         3
Check for chained rows:
SELECT a.name, b.value
  FROM v$statname a, v$mystat b
 WHERE a.statistic# = b.statistic#
   AND lower(a.name) = 'table fetch continued row';
NAME                                                                  VALUE
---------------------------------------------------------------- ----------
table fetch continued row                                                 0
Now that is to be expected, the rows came out in the order we put them in (Oracle full scanned this query, it processed the data as it found it). Also expected is the table fetch continued row is zero. This data is so small right now, we know that all three rows fit on a single block. No chaining.
Demonstration of the Row Migration
Now, lets do some updates in a specific way. We want to demonstrate the row migration issue and how it affects the full scan:
UPDATE row_mig_chain_demo SET a = 'z1', b = 'z2', c = 'z3' WHERE x = 3;
COMMIT;
UPDATE row_mig_chain_demo SET a = 'y1', b = 'y2', c = 'y3' WHERE x = 2;
COMMIT;
UPDATE row_mig_chain_demo SET a = 'w1', b = 'w2', c = 'w3' WHERE x = 1;
COMMIT;
Note the order of updates, we did last row first, first row last.
SELECT * FROM row_mig_chain_demo;
         X
----------
         3
         2
         1
SELECT a.name, b.value
  FROM v$statname a, v$mystat b
 WHERE a.statistic# = b.statistic#
   AND lower(a.name) = 'table fetch continued row';
NAME                                                                  VALUE
---------------------------------------------------------------- ----------
table fetch continued row                                                 0
Interesting, the rows came out «backwards» now. That is because we updated row 3 first. It did not have to migrate, but it filled up block 1. We then updated row 2. It migrated to block 2 with row 3 hogging all of the space, it had to. We then updated row 1, it migrated to block 3. We migrated rows 2 and 1, leaving 3 where it started.
So, when Oracle full scanned the table, it found row 3 on block 1 first, row 2 on block 2 second and row 1 on block 3 third. It ignored the head rowid piece on block 1 for rows 1 and 2 and just found the rows as it scanned the table. That is why the table fetch continued row is still zero. No chaining.
So, lets see a migrated row affecting the «table fetch continued row»:
SELECT * FROM row_mig_chain_demo WHERE x = 3;

         X
----------
         3
SELECT a.name, b.value
  FROM v$statname a, v$mystat b
 WHERE a.statistic# = b.statistic#
   AND lower(a.name) = 'table fetch continued row';
NAME                                                                  VALUE
---------------------------------------------------------------- ----------
table fetch continued row                                                 0
This was an index range scan / table access by rowid using the primary key.  We didn't increment the «table fetch continued row» yet since row 3 isn't migrated.
SELECT * FROM row_mig_chain_demo WHERE x = 1;

 
        X
----------
         1
SELECT a.name, b.value
  FROM v$statname a, v$mystat b
 WHERE a.statistic# = b.statistic#
   AND lower(a.name) = 'table fetch continued row';
NAME                                                                  VALUE
---------------------------------------------------------------- ----------
table fetch continued row                                                 1
Row 1 is migrated, using the primary key index, we forced a «table fetch continued row».
Demonstration of the Row Chaining
UPDATE row_mig_chain_demo SET d = 'z4', e = 'z5' WHERE x = 3;
COMMIT;
Row 3 no longer fits on block 1. With d and e set, the rowsize is about 5k, it is truly chained.
SELECT x,a FROM row_mig_chain_demo WHERE x = 3;
         X
----------
         3
SELECT a.name, b.value
  FROM v$statname a, v$mystat b
 WHERE a.statistic# = b.statistic#
   AND lower(a.name) = 'table fetch continued row';
NAME                                                                  VALUE
---------------------------------------------------------------- ----------
table fetch continued row                                                 1
We fetched column «x» and «a» from row 3 which are located on the «head» of the row, it will not cause a «table fetch continued row». No extra I/O to get it.
SELECT x,d,e FROM row_mig_chain_demo WHERE x = 3;
SELECT a.name, b.value
  FROM v$statname a, v$mystat b
 WHERE a.statistic# = b.statistic#
   AND lower(a.name) = 'table fetch continued row';
NAME                                                                  VALUE
---------------------------------------------------------------- ----------
table fetch continued row                                                 2
Now we fetch from the «tail» of the row via the primary key index. This increments the «table fetch continued row»by one to put the row back together from its head to its tail to get that data.
Now let's see a full table scan - it is affected as well:
SELECT * FROM row_mig_chain_demo;
         X
----------
         3
         2
         1
SELECT a.name, b.value
  FROM v$statname a, v$mystat b
 WHERE a.statistic# = b.statistic#
   AND lower(a.name) = 'table fetch continued row';
NAME                                                                  VALUE
---------------------------------------------------------------- ----------
table fetch continued row                                                 3
The «table fetch continued row» was incremented here because of Row 3, we had to assemble it to get the trailing columns.  Rows 1 and 2, even though they are migrated don't increment the «table fetch continued row» since we full scanned.
SELECT x,a FROM row_mig_chain_demo;
         X
----------
         3
         2
         1
SELECT a.name, b.value
  FROM v$statname a, v$mystat b
 WHERE a.statistic# = b.statistic#
   AND lower(a.name) = 'table fetch continued row';
NAME                                                                  VALUE
---------------------------------------------------------------- ----------
table fetch continued row                                                 3
No «table fetch continued row» since we didn't have to assemble Row 3, we just needed the first two columns.
SELECT x,e FROM row_mig_chain_demo;
         X
----------
         3
         2
         1
SELECT a.name, b.value
  FROM v$statname a, v$mystat b
 WHERE a.statistic# = b.statistic#
   AND lower(a.name) = 'table fetch continued row';
NAME                                                                  VALUE
---------------------------------------------------------------- ----------
table fetch continued row                                                 4
But by fetching for d and e, we incemented the «table fetch continued row». We most likely have only migrated rows but even if they are truly chained, the columns you are selecting are at the front of the table.
So, how can you decide if you have migrated or truly chained?
Count the last column in that table. That'll force to construct the entire row.
SELECT count(e) FROM row_mig_chain_demo;
  COUNT(E)
----------
         1
SELECT a.name, b.value
  FROM v$statname a, v$mystat b
 WHERE a.statistic# = b.statistic#
   AND lower(a.name) = 'table fetch continued row';
NAME                                                                  VALUE
---------------------------------------------------------------- ----------
table fetch continued row                                                 5
Analyse the table to verify the chain count of the table:
ANALYZE TABLE row_mig_chain_demo COMPUTE STATISTICS;

SELECT chain_cnt
  FROM user_tables
 WHERE table_name = 'ROW_MIG_CHAIN_DEMO';
 CHAIN_CNT
----------
         3
Three rows that are chained. Apparently, 2 of them are migrated (Rows 1 and 2) and one is truly chained (Row 3).

Total Number of «table fetch continued row» since instance startup?

The V$SYSSTAT view tells you how many times, since the system (database) was started you did a «table fetch continued row» over all tables.
sqlplus system/<password>

SELECT 'Chained or Migrated Rows = '||value
  FROM v$sysstat
 WHERE name = 'table fetch continued row';
Chained or Migrated Rows = 31637
You could have 1 table with 1 chained row that was fetched 31'637 times. You could have 31'637 tables, each with a chained row, each of which was fetched once. You could have any combination of the above -- any combo.
Also, 31'637 - maybe that's good, maybe that's bad. it is a function of
  • how long has the database has been up
  • how many rows is this as a percentage of total fetched rows.
    For example if 0.001% of your fetched are table fetch continued row, who cares!
Therefore, always compare the total fetched rows against the continued rows.
SELECT name,value FROM v$sysstat WHERE name like '%table%';
NAME                                                                  VALUE
---------------------------------------------------------------- ----------
table scans (short tables)                                           124338
table scans (long tables)                                              1485
table scans (rowid ranges)                                                0
table scans (cache partitions)                                           10
table scans (direct read)                                                 0
table scan rows gotten                                             20164484
table scan blocks gotten                                            1658293
table fetch by rowid                                                1883112
table fetch continued row                                             31637table lookup prefetch client count                                        0

How many Rows in a Table are chained?

The USER_TABLES tells you immediately after an ANALYZE (will be null otherwise) how many rows in the table are chained.
ANALYZE TABLE row_mig_chain_demo COMPUTE STATISTICS;
SELECT chain_cnt,
       round(chain_cnt/num_rows*100,2) pct_chained,
       avg_row_len, pct_free , pct_used
  FROM user_tables
WHERE table_name = 'ROW_MIG_CHAIN_DEMO';

 CHAIN_CNT PCT_CHAINED AVG_ROW_LEN   PCT_FREE   PCT_USED
---------- ----------- ----------- ---------- ----------
         3         100        3691         10         40
PCT_CHAINED shows 100% which means all rows are chained or migrated.

List Chained Rows

You can look at the chained and migrated rows of a table using the ANALYZE statement with the LIST CHAINED ROWS clause. The results of this statement are stored in a specified table created explicitly to accept the information returned by the LIST CHAINED ROWS clause. These results are useful in determining whether you have enough room for updates to rows.
Creating a CHAINED_ROWS Table
To create the table to accept data returned by an ANALYZE ... LIST CHAINED ROWS statement, execute the UTLCHAIN.SQL or UTLCHN1.SQL script in $ORACLE_HOME/rdbms/admin. These scripts are provided by the database. They create a table named CHAINED_ROWS in the schema of the user submitting the script.
create table CHAINED_ROWS (
  owner_name         varchar2(30),
  table_name         varchar2(30),
  cluster_name       varchar2(30),
  partition_name     varchar2(30),
  subpartition_name  varchar2(30),
  head_rowid         rowid,
  analyze_timestamp  date
);
After a CHAINED_ROWS table is created, you specify it in the INTO clause of the ANALYZE statement.
ANALYZE TABLE row_mig_chain_demo LIST CHAINED ROWS;
SELECT owner_name,
       table_name,
       head_rowid
 FROM chained_rows
OWNER_NAME                     TABLE_NAME                     HEAD_ROWID
------------------------------ ------------------------------ ------------------
SCOTT                          ROW_MIG_CHAIN_DEMO             AAAPVIAAFAAAAkiAAA
SCOTT                          ROW_MIG_CHAIN_DEMO             AAAPVIAAFAAAAkiAAB

How to avoid Chained and Migrated Rows?

Increasing PCTFREE can help to avoid migrated rows. If you leave more free space available in the block, then the row has room to grow. You can also reorganize or re-create tables and indexes that have high deletion rates. If tables frequently have rows deleted, then data blocks can have partially free space in them. If rows are inserted and later expanded, then the inserted rows might land in blocks with deleted rows but still not have enough room to expand. Reorganizing the table ensures that the main free space is totally empty blocks.
The ALTER TABLE ... MOVE statement enables you to relocate data of a nonpartitioned table or of a partition of a partitioned table into a new segment, and optionally into a different tablespace for which you have quota. This statement also lets you modify any of the storage attributes of the table or partition, including those which cannot be modified using ALTER TABLE. You can also use the ALTER TABLE ... MOVE statement with the COMPRESS keyword to store the new segment using table compression.
  1. ALTER TABLE MOVE
    First count the number of Rows per Block before the ALTER TABLE MOVE

    SELECT dbms_rowid.rowid_block_number(rowid) "Block-Nr", count(*) "Rows"
      FROM row_mig_chain_demo
    GROUP BY dbms_rowid.rowid_block_number(rowid) order by 1;

     Block-Nr        Rows
    ---------- ----------
          2066          3
    Now, de-chain the table, the ALTER TABLE MOVE rebuilds the row_mig_chain_demo table in a new segment, specifying new storage parameters:

    ALTER TABLE row_mig_chain_demo MOVE
       PCTFREE 20
       PCTUSED 40
       STORAGE (INITIAL 20K
                NEXT 40K
                MINEXTENTS 2
                MAXEXTENTS 20
                PCTINCREASE 0);

    Table altered.
    Again count the number of Rows per Block after the ALTER TABLE MOVE

    SELECT dbms_rowid.rowid_block_number(rowid) "Block-Nr", count(*) "Rows"
      FROM row_mig_chain_demo
    GROUP BY dbms_rowid.rowid_block_number(rowid) order by 1;
     Block-Nr        Rows
    ---------- ----------
          2322          1
          2324          1
          2325          1

     
  2. Rebuild the Indexes for the Table
    Moving a table changes the rowids of the rows in the table. This causes indexes on the table to be marked UNUSABLE, and DML accessing the table using these indexes will receive an ORA-01502 error. The indexes on the table must be dropped or rebuilt. Likewise, any statistics for the table become invalid and new statistics should be collected after moving the table.

    ANALYZE TABLE row_mig_chain_demo COMPUTE STATISTICS;

    ERROR at line 1:
    ORA-01502: index 'SCOTT.SYS_C003228' or partition of such index is in unusable
    state
    This is the primary key of the table which must be rebuilt.
    ALTER INDEX SYS_C003228 REBUILD;Index altered.
    ANALYZE TABLE row_mig_chain_demo COMPUTE STATISTICS;Table analyzed.
    SELECT chain_cnt,
           round(chain_cnt/num_rows*100,2) pct_chained,
           avg_row_len, pct_free , pct_used
      FROM user_tables
     WHERE table_name = 'ROW_MIG_CHAIN_DEMO';
     CHAIN_CNT PCT_CHAINED AVG_ROW_LEN   PCT_FREE   PCT_USED
    ---------- ----------- ----------- ---------- ----------
             1       33.33        3687         20         40
    If the table includes LOB column(s), this statement can be used to move the table along with LOB data and LOB index segments (associated with this table) which the user explicitly specifies. If not specified, the default is to not move the LOB data and LOB index segments.

Detect all Tables with Chained and Migrated Rows

Using the CHAINED_ROWS table, you can find out the tables with chained or migrated rows.
  1. Create the CHAINED_ROWS table

    cd $ORACLE_HOME/rdbms/admin
    sqlplus scott/tiger
    @utlchain.sql
     
  2. Analyse all or only your Tables

    SELECT 'ANALYZE TABLE '||table_name||' LIST CHAINED ROWS INTO CHAINED_ROWS;'  FROM user_tables
    /


    ANALYZE TABLE ROW_MIG_CHAIN_DEMO LIST CHAINED ROWS INTO CHAINED_ROWS;
    ANALYZE TABLE DEPT LIST CHAINED ROWS INTO CHAINED_ROWS;
    ANALYZE TABLE EMP LIST CHAINED ROWS INTO CHAINED_ROWS;
    ANALYZE TABLE BONUS LIST CHAINED ROWS INTO CHAINED_ROWS;
    ANALYZE TABLE SALGRADE LIST CHAINED ROWS INTO CHAINED_ROWS;
    ANALYZE TABLE DUMMY LIST CHAINED ROWS INTO CHAINED_ROWS;

    Table analyzed.
     
  3. Show the RowIDs for all chained rows

    This will allow you to quickly see how much of a problem chaining is in each table. If chaining is prevalent in a table, then that table should be rebuild with a higher value for PCTFREE

    SELECT owner_name,
           table_name,
           count(head_rowid) row_count
      FROM chained_rows
    GROUP BY owner_name,table_name
    /


    OWNER_NAME                     TABLE_NAME                      ROW_COUNT
    ------------------------------ ------------------------------ ----------
    SCOTT                          ROW_MIG_CHAIN_DEMO                      1

Conclusion

Migrated rows affect OLTP systems which use indexed reads to read singleton rows. In the worst case, you can add an extra I/O to all reads which would be really bad. Truly chained rows affect index reads and full table scans.
  • Row migration is typically caused by UPDATE operation
  • Row chaining is typically caused by INSERT operation.
  • SQL statements which are creating/querying these chained/migrated rows will degrade the performance due to more I/O work.
  • To diagnose chained/migrated rows use ANALYZE command , query V$SYSSTAT view
  • To remove chained/migrated rows use higher PCTFREE using ALTER TABLE MOVE.

OEM Agent Installation

./agentDeploy.sh AGENT_BASE_DIR=/u001/oracle/product/agent13c OMS_HOST=oem-dev.xxx.com EM_UPLOAD_PORT=1159 AGENT_REGISTRATION_PASSWORD=*****...