How Database Connections are Created When Using SCANs

Based on the environment, the following actions occur when you use a SCAN to connect to an Oracle RAC database using a service name.

The numbered actions correspond to the arrows shown in Load Balancing Actions for Oracle RAC Connections That Use SCAN:

1. The LREG 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. The listeners are dynamically updated on the amount of work being handled by the instances and dispatchers.
2. The client issues a database connection request using a connect descriptor of the form:

orausr/@scan_name:1521/webapp

1. Note:

   If you use the Easy Connect naming method, then ensure that the sqlnet.ora file on the client contains EZCONNECT in the list of naming methods specified by the NAMES.DIRECTORY_PATH parameter.
2. The client uses DNS to resolve scan_name. After DNS returns the three addresses assigned to the SCAN, the client sends a connect request to the first IP address. If the connect request fails, then the client attempts to connect using the next IP address.
3. When the connect request is successful, the client connects to a SCAN listener for the cluster that hosts the sales database and has an instance offering the webapp service, which in this example is sales1 and sales2. The SCAN listener compares the workload of the instances sales1 and sales2 and the workload of the nodes on which they run. If the SCAN listener determines that node2 is less loaded than node1, then the SCAN listener selects node2 and sends the address for the local listener on that node back to the client.
4. The client connects to the local listener on node2. The local listener starts a dedicated server process for the connection to the database.
5. The client connects directly to the dedicated server process on node2 and accesses the sales2 database instance.

Figure Load Balancing Actions for Oracle RAC Connections That Use SCAN

1. Public IP- Public IP is used for Direct connection to a particular instance only.

2. VIP- it's used for transferring the connection to another VIP in case of any failure.

3. Scan IP- its a new concept from 11g R2 onwards, wherein it has pre-information of which node has how much of load and it balances the connection between multiple instances.

     Scan IP max cab be 3 (and Oracle has tested it and as per there internal result, 3 is more than           enough for N instances.)

Remote_listener will have your SCAN_IP.

The local listener will have your VIP of a particular node.

VIP will do failover for your existing connection and incoming new request for that IP

SCAN IP will have re-information about load and will redirect your request to respective less load VIP

if you avoid VIP then you are left with public IP i.e your network card assigned to your server. That public IP is not able to failover in case of any failure.

Private IP, it's only for internal communication of RAC components maintained and managed by Oracle software itself

Reference- docs.oracle.com

Troubleshooting 10g and 11.1 Clusterware Reboots (Doc ID 265769.1) 11gR2 Clusterware and Grid Home - What You Need to Know (Doc ID 1053147.1) To Bottom

Troubleshooting Assistant: Handling Block Corruptions in Oracle7 / 8 / 8i / 9i / 10g / 11g (Doc ID 1598103.2)

tkprof: When and How To Use It

http://www.dbatoolz.com/t/tkprof-when-and-how-to-use-it.html

How the COMPATIBLE Initialization Parameter Operates in Oracle Database

The COMPATIBLE initialization parameter enables or disables Oracle Database features based on release compatibility

The COMPATIBLE initialization parameter operates in the following way:

• The COMPATIBLE initialization parameter enables or disables the use of features, to help protect your existing application use of data.
  If you run an Oracle Database 12c database with the COMPATIBLE initialization parameter set to 11.2.0, then the database software generates database structures on disk that are compatible with Oracle Database Release 11g release 2 (11.2). If you try to use features that are part of a later release of Oracle Database, and make the database incompatible with the COMPATIBLE initialization parameter, then an error occurs. However, new features are enabled that do not create changes on disk that are incompatible with Oracle Database Release 11g release 2.
• If you make changes to the database that make the database incompatible with the COMPATIBLE initialization parameter setting you want to use, then the database does not start, and initialization terminates in an error. If this happens, then you must set the COMPATIBLE initialization parameter to an appropriate value for the database.

Source- https://docs.oracle.com

SHMMAX and SHMALL for Oracle in Linux

SHMMAX and SHMALL are two key shared memory parameters that directly impact’s the way by which Oracle creates an SGA. Shared memory is nothing but part of Unix IPC System (Inter Process Communication) maintained by kernel where multiple processes share a single chunk of memory to communicate with each other. 

While trying to create an SGA during a database startup, Oracle chooses from one of the 3 memory management models a) one-segment or b) contiguous-multi segment or c) non-contiguous multi segment. Adoption of any of these models is dependent on the size of SGA and values defined for the shared memory parameters in the Linux kernel, most importantly SHMMAX.


So what are these parameters - SHMMAX and SHMALL?

SHMMAX is the maximum size of a single shared memory segment set in “bytes”.

Oracle:~ #  cat /proc/sys/kernel/shmmax


536870912



SHMALL is the total size of Shared Memory Segments System wide set in “pages”.



Oracle:~ #  cat /proc/sys/kernel/shmall

1415577



The key thing to note here is the value of SHMMAX is set in "bytes" but the value of SHMMALL is set in "pages".


What’s the optimal value for SHMALL?


As SHMALL is the total size of Shard Memory Segments System wide, it should always be less than the Physical Memory on the System and should also be less than sum of SGA’s of all the oracle databases on the server. Once this value (sum of SGA’s) hit the limit, i.e. the value of shmall, then any attempt to start a new database (or even an existing database with a resized SGA) will result in an “out of memory” error (below). This is because there won’t be any more shared memory segments that Linux can allocate for SGA.


ORA-27102: out of memory

Linux-x86_64 Error: 28: No space left on device.


So above can happen for two reasons. Either the value of shmall is not set to an optimal value or you have reached the threshold on this server.

Setting the value for SHMALL to optimal is straight forward. All you want to know is how much “Physical Memory” (excluding Cache/Swap) you have on the system and how much of it should be set aside for Linux Kernel and to be dedicated to Oracle Databases.

For e.g. Let say the Physical Memory of a system is 6 GB, out of which you want to set aside 1 GB for Linux Kernel for OS Operations and dedicate the rest of 5 GB to Oracle Databases. Then here’s how you will get the value for SHMALL.

Convert this 5 GB to bytes and divide by page size. Remember SHMALL should be set in “pages” not “bytes”.

So here goes the calculation.


Determine Page Size first, can be done in two ways. In my case it’s 4096 and that’s the recommended and default in most cases which you can keep the same. 



Oracle:~ # getconf PAGE_SIZE

4096


or

Oracle:~ # cat /proc/sys/kernel/shmmni
4096

Convert 5 GB into bytes and divide by page size, I used the Linux calc to do the math.


Oracle:~ # echo "( 5 * 1024 * 1024 * 1024 ) / 4096 " | bc -l

1310720.00000000000000000000


Reset shmall and load it dynamically into kernel


Oracle:~ # echo "1310720" > /proc/sys/kernel/shmall
Oracle:~ # sysctl –p

Verify if the value has been taken into effect.

Oracle:~ # sysctl -a | grep shmall
kernel.shmall = 1310720

Another way to look this up is

Oracle:~ # ipcs -lm

------ Shared Memory Limits --------
max number of segments = 4096                          /* SHMMNI  */
max seg size (k bytes) = 524288                  /* SHMMAX  */
max total shared memory (k bytes) = 5242880      /* SHMALL  */
min seg size (bytes) = 1


To keep the value effective after every reboot, add the following line to /etc/sysctl.conf


echo “kernel.shmall = 1310720” >> /etc/sysctl.conf

Also verify if sysctl.conf is enabled or will be read during boot.

Oracle:~ # chkconfig boot.sysctl
boot.sysctl  on

If returns “off”, means it’s disabled. Turn it on by running

Oracle:~ # chkconfig boot.sysctl on
boot.sysctl  on

What’s the optimal value for SHMMAX?


Oracle makes use of one of the 3 memory management models to create the SGA during database startup and it does this in following sequence. First Oracle attempts to use the one-segment model and if this fails, it proceeds with the next one which's the contiguous multi-segment model and if that fails too, it goes with the last option which is the non-contiguous multi-segment model.

So during startup it looks for shmmax parameter and compares it with the initialization parameter *.sga_target. If shmmax > *.sga_target, then oracle goes with one-segment model approach where the entire SGA is created within a single shared memory segment.

But the above attempt (one-segment) fails if SGA size otherwise *.sga_target  > shmmax, then Oracle proceeds with the 2nd option – contiguous multi-segment model. Contiguous allocations, as the name indicates are a set of shared memory segments which are contiguous within the memory and if it can find such a set of segments then entire SGA is created to fit in within this set. 


But if cannot find a set of contiguous allocations then last of the 3 option’s is chosen – non-contiguous multi-segment allocation and in this Oracle has to grab the free memory segments fragmented between used spaces.

So let’s say if you know the max size of SGA of any database on the server stays below 1 GB, you can set shmmax to 1 GB. But say if you have SGA sizes for different databases spread between 512 MB to 2 GB, then set shmmax to 2 Gigs and so on.

Like SHMALL, SHMMAX can be defined by one of these methods..

Dynamically reset and reload it to the kernel..


Oracle:~ #  echo "536870912" >  /proc/sys/kernel/shmmax

Oracle:~ #  sysctl –p           -- Dynamically reload the parameters.

Or use sysctl to reload and reset ..

Oracle:~ #  sysctl -w kernel.shmmax=536870912

To permanently set so it’s effective in reboots…

Oracle:~ #  echo "kernel.shmmax=536870912" >>  /etc/systctl.conf


Install doc for 11g recommends the value of shmmax to be set to "4 GB – 1 byte" or half the size of physical memory whichever is lower. I believe “4 GB – 1 byte” is related to the limitation on the 32 bit (x86) systems where the virtual address space for a user process can only be little less than 4 GB. As there’s no such limitation for 64 bit (x86_64) bit systems, you can define SGA’s larger than 4 Gig’s. But idea here is to let Oracle use the efficient one-segment model and for this shmmax should stay higher than SGA size of any individual database on the system.

Credit- Kannan Venkatachalam

Oracle RAC Startup sequence with ASM file system

OHASD is the root for bringing up Oracle Clusterware.

 

1.      
OHASD  ------------------------------------->   OLR (Provided Needed data to complete OHASD initialization)

has  access

 

2.        OHASD     --------------------------------------------->    GPNPD & CSSD (Cluster Sync Services)

                       Brings UP

                                                                                                                                            CSSD Has access of Grid Plug and Play

GPNP-à Profile Stored on the Local File system

       

Contains

è  ASM Diskgroup Discovery String

è  ASM SPFILE location (Diskgroup Name)

è  Name of the ASM Diskgroup containing the voting files.

3.       The Voting File location on ASM disks are accessed by CSSD with well-known pointers in the ASM disk header and CSSD is able to complete initialization and start or join an existing cluster.

4.       OHASD start as ASM instance and ASM can now operate with CSSD initialization and operating. The ASM instance uses special code to locate the contents of the ASM SPFILE assuming it is stored in diskgroup.

5.       With an ASM instance operating and its Diskgroup mounted, access to Clusterware’s OCR is available to CRSD.

6.       OHASD starts CRSD with access to the OCR in an ASM Diskgroup.

7.       Clusterware complete initialization and brings up other services under its control.

When Clusterware start, three files are involved.

OLR- First file to read and opened. This file contains information regarding where the voting disk is stored. And information to Startup the ASM (e.g. ASM Diskgroup string)

VOTING DISK- This is the 2^nd file to be opened & read, this is depend on only OLR being accessible (ASM starts after CSSD or ASM does not start if CSSD is offline (i.e voting file missing).

To check voting file use- v$ASM_DISK column Voting_file

OCR- Finally the ASM instance start and mount all Diskgroup, then Clusterware daemon (CRSD) opens and reads the OCR which is stored on Diskgroup.

So, if ASM already started, ASM does not depends on OCR or OLR to be online. ASM depends on CSSD (vote disk) to be online.


- Piyush Mishra