Monday, March 28, 2011

Installing Oracle Database 11.2 on IBM AIX 6.1 (Includes HACMP)

SWAP space recommendation from Oracle corp. for Oracle 11g Release 2

If RAM is between 1 GB and 2 GB, SAWP should be 1.5 times the size of RAM
If RAM is between 2 GB and 16 GB, SWAP should be equal to the size of RAM
If RAM is more than 16 GB, SWAP should be 16 GB

Check RAM
# /usr/sbin/lsattr -E -l sys0 -a realmem

Check swap space
# /usr/sbin/lsps -a


Operating System Requirements
The following, or later versions, of the operating systems are required for Oracle Database 11g Release 2 (11.2):
- AIX 5L V5.3 TL 09 SP1 ("5300-09-01"), 64 bit kernel
- AIX 6.1 TL 02 SP1 ("6100-02-01), 64-bit kernel

The following operating system filesets are required for AIX 6.1:
- bos.adt.base
- bos.adt.lib
- bos.adt.libm
- bos.perf.libperfstat - 6.1.2.1 or later
- bos.perf.perfstat
- bos.perf.proctools
- rsct.basic.rte
- rsct.compat.clients.rte
- xlC.aix61.rte -10.1.0.0 or later

1. To determine the distribution and version of AIX installed, enter the following command:

# oslevel -s

For AIX 6.1: If the operating system version is lower than AIX 6.1 Technology Level 2 SP 1, then upgrade your operating system to this, or a later, level.

2. To determine whether the required filesets are installed and committed, enter a command similar to the following:
# lslpp -l bos.adt.base bos.adt.lib bos.adt.libm bos.perf.perfstat \
bos.perf.libperfstat bos.perf.proctools rsct.basic.rte rsct.compat.clients.rte \
xlC.aix61.rte

3. To determine the supported kernel mode, enter a command similar to the
following:
# getconf KERNEL_BITMODE
64


The following, or later, patches are required for Oracle Database 11g Release 2 (11.2) for AIX Based Systems:


Authorized Problem Analysis Reports (APARs) for AIX 6L:
- IZ41855
- IZ51456
- IZ52319


# /usr/sbin/instfix -i -k "IZ51456 IZ52319 IZ41855"
Note: IZ52319 and IZ41855 might not displayed because these are included in Isome other installed APARs


Create OS Level Groups and User
  1. Check all OS requirements are met.
  2. Add dba OS level groups using “smit security” command. For HACMP, use “smit hacmp” to add the shared group for the cluster
  3. Add oracle user using “smit security” command. Select dba as Primary group for this user and /home/oracle as home directory. For HACMP, use “smit hacmp” to add the shared user for the cluster. Home directory is recommended to be on the local storage of the node.
  4. Reset the password for oracle user. For HACMP, use “smit security” to reset password for both nodes in HACMP.

Configure Shell Limits (Copied from Oracle documentation)
  1. Run “smit chuser” command and select “oracle” user to set the limits as follows.

Shell Limit (As Shown in smit)                Recommended Value
Soft FILE size                                                    -1 (Unlimited)
Soft CPU time                                                   -1 (Unlimited) Note: This is the default value.
Soft DATA segment                                           -1 (Unlimited)
Soft STACK size                                                -1 (Unlimited)
Soft Real Memory size                                       -1 (Unlimited)
Processes (per user)                                          -1 (Unlimited)
Note: This limit is available only in AIX 6.1 or later.
Refer to “Configure System Configuration Parameters” step for information on configuration of processes per user limits.

Edit /etc/security/limits file to set “rss = -1” under user “oracle” to set the “Soft Real Memory Size”. This file should have entries for “oracle” user as follows
oracle:
        fsize = -1
        data = -1
        stack_hard = -1
        stack = -1
        rss = -1

Configure System Configuration Parameters
Parameter                                                     Recommended Value
maxuprocs                                                        16348
ncargs                                                              128

  1. Execute “smit chgsys” command and make sure that value in “Maximum number of PROCESSES is equal or greater to the value of “maxuprocs” mentioned above. Also make sure that value in “ARG/ENV list size in 4K byte blocks is greater or equal to the value of “ncarg” mentioned above.
  2. Login as user “oracle” and execute command “ulimit -a” and confirm that limits have been set mentioned under “Configure Shell Limits.”

Checking Asynchronous Input Output Processes(Copied from IBM Document)

On AIX 6, the AIO device drivers are enabled by default. Increase the number of aioserver processes from the default value. The recommended value for aio_maxreqs is 64k (65536). Confirm this value.
Confirm the aio_maxreqs value using the procedure for your release:
AIX 6.1:
# ioo –o aio_maxreqs
aio_maxreqs = 65536

When performing an asynchronous I/O to a file system, note that each asynchronous I/O operation is tied to an asynchronous I/O server. Thus, the number of asynchronous I/O servers limits the number of concurrent synchronous I/O operations in the system.

The initial number of servers that are started during a system restart is determined by the minservers parameter. As concurrent asynchronous I/O operations occur, additional asynchronous I/O servers are started, up to a maximum of the value set in the maxservers parameter.

In general, to set the number of asynchronous I/O servers, complete the following procedure:
1. Adjust the initial value of maxservers to 10 times the number of disks that are to be used concurrently but no more than 80.
2. Monitor the performance effects on the system during periods of high I/O activity. If all AIO server processes are started, then increase the maxservers value. Also, continue to monitor the system performance during peak I/O activity to determine if there was a benefit from the additional AIO servers. Too many
asynchronous I/O servers increase memory and processor overload of additional
processes, but this disadvantage is small.

To monitor the number of AIO server processes that have started, enter the following:
# ps -ek|grep -v grep|grep –v posix_aioserver|grep -c aioserver
Note: Value could be “0” if there is not activity going on to the server


Tuning the AIX for Oracle
  1. Execute following commands while logged in as root user, for more details about this, see document “Tuning IBM AIX 5.3 and AIX 6.1 for Oracle Database.pdf”
# vmo -L lgpg_size 
# vmo -L lgpg_regions
# vmo -p -o lgpg_size=16777216  -o lgpg_regions=256
# vmo -L lgpg_size 
# vmo -L lgpg_regions
# vmo -p -o lru_file_repage=0 -o minperm%=3 -o maxperm%=90 -o  maxclient%=90
# vmo -r -o page_steal_method=1  
# bosboot -ad /dev/ipldevice
  1. Reboot the server

Miscellaneous Settings
  1. Check /etc/hosts file. It should contain the host name and alias in it.
  2. /etc/resolv.conf file should contain following entry

    nameserver      168.232.220.220
    domain  mydomain.com
  3. Check /etc/netsvc.conf. This file should contain following entry to make sure that /etc/hosts file is used first for name resolution

    host = local, bind, nis
  4. Set the xntpd for network time synchronization.
    1. Make entry of time serve in the /etc/ntp.conf as follows
      server oa-10.utac.com.sg prefer
    2. Use “smit xntpd” to start xntpd service.


Installation of Oracle 11G Release 2

  1. Set the environment variables for user oracle in .profile which could be found in /home/oracle directory. For HACMP, do this on both nodes.
    ###################################################
    ORACLE_HOSTNAME=ibmaixha01
    ORACLE_BASE=/oracle;export ORACLE_BASE
ORACLE_HOME=/oracle/product/11.2.0.1/db_1;export ORACLE_HOME
ORACLE_SID=mydb;export ORACLE_SID
ORACLE_TERM=vt100; export ORACLE_TERM
NLS_LANG=american_america.UTF8; export NLS_LANG
ORA_NLS33=$ORACLE_HOME/ocommon/nls/admin/data; export ORA_NLS33
LD_LIBRARY_PATH=$ORACLE_HOME/lib:$ORACLE_HOME/lib32:/lib:/usr/lib:/usr/local/lib
export LD_LIBRARY_PATH
PATH=$ORACLE_HOME/bin:$PATH:$HOME/bin
set -o vi###################################################
  1. Run the rootpre.sh script and root user. For HACMP, execute this script on both nodes of HACMP. This will also create a group hagsuser, add user oracle and root into this group on both nodes.
    Note: Do not run the rootpre.sh script if you have a later release
of the Oracle Database software already installed on this system.
Switch user to root:
$ su -
password:
#
Complete one of the following steps, depending on the location of the installation
files:
·        If the installation files are on disc, enter a command similar to the following, where directory_path is the disc mount point directory or the path of the db directory on the DVD:
# /directory_path/rootpre/rootpre.sh
·        If the installation files are on the hard disk, change directory to the Disk1 directory and enter the following command:
# ./rootpre.sh


  1. Set ORACLE_HOSTNAME environment variable appropriately if required.
  2. Login as oracle user and check if all environment variables are set correctly.
  3. Set DISPLAY environment variable if required.
  4. Run Oracle installer from the directory where software is placed.
    $ ./runInstaller
    Uncheck the box for security updates and click Next.
  5. Select “Install database software only”, and click Next.
  6. Select “Single database installation”, and click Next.
  7. Select appropriate option for installation and click Next.
  8. Enter ORACLE_BASE and ORACLE_HOME directories and click Next.
  9. Specify oracle inventory location as mentioned bellow and click Next.
  10. Click Next.
  11. Click on Finish to start installation
  12. Following screen shows the installation progress.
  13. Run “orainstRoot.sh” and then “root.sh” as root.
  14. Click Close to finish the installation.

Monday, March 7, 2011

Don't COMMIT Too Often

Log Writer or LGWR is Oracle instance’s background process which has the responsibility to read from the redo log buffer cache and write into the redo log files. LGWR writes to the redo log file
1) Every 3 seconds
2) When the redo log buffer is one-third full
3) Before DBWR (Database Writer) process writes the modified buffers to the disk.
4) When COMMIT is issued

Here my focus would be point 4. When a COMMIT is issued by a user, LWGR puts a commit record in the log buffer cache and then writes changes made by the transaction in the CURRENT redo log file (in all members of redo log group). Here the session issuing commit needs to wait for a small (yet ignorable) amount of time until LGWR confirms the corresponding server process that changes have been written to the redo log file and guarantees that the transactions is recoverable in case of a failure (instance/media). Remember that actual changed data blocks (buffers) may still be in the memory (buffer cache) and may be chosen to be written to the datafiles at some later point in time (checkpoint, incremental checkpoint etc.)

Point to note here is; a physical write on to the disk (redo log file) at each commit which means more physical writes and more burdens on the storage sub system. Although each single COMMIT by a session puts a very very small and ignorable burden on the storage, but if taken as a whole, if we have thousands of COMMITs being issues in a minute, certainly latency of storage may increase. This is the reason that it is not recommended by Oracle to use too many commits in your application code and you should try to COMMIT as less as possible. As explained above, committing data causes the data in the log buffer cache to be written to the redo log files which requires physical writes and along with it, a wait event "log file sync" occurs every time the data is written to the current redo log group. Although this is a background wait event, but still if you find in your top 5 wait event, you might need to look at your application code and do a discussion with your development team on these COMMIT statements embedded in your code. More commits, more “log file sync” wait event and eventually poisonous for the scalability of your system. Let’s have an example where we insert 100000 rows in a table with testing COMMIT after every insertion and then testing one COMMIT after all 100000 insertions and see the time difference. This time difference also proves that each commit further slows down the writings.

Enter user-name: scott@usgumsd
Enter password:

Connected to:
Oracle Database 11g Release 11.1.0.7.0 - 64bit Production

USGUMSD$SCOTT> create table test (id number);

Table created.

USGUMSD$SCOTT> set timing on

USGUMSD$SCOTT>   begin
  for i in 1..100000
  loop
  insert into test values(1);
  commit;
  end loop;
  end;
  /

PL/SQL procedure successfully completed.

Elapsed: 00:00:16.73

USGUMSD$SCOTT> truncate table test;

Table truncated.

Elapsed: 00:00:00.09
USGUMSD$SCOTT> begin
  for i in 1..100000
  loop
  insert into test values(1);
  end loop;
  commit;
  end;
  /

PL/SQL procedure successfully completed.

Elapsed: 00:00:12.12
USGUMSD$SCOTT>

Conclusion:
There is a difference of 4 seconds. If you have thousands of DMLs running simultaneously without taking care of too many commit, you can imagine how much burden it will have on your IO system and increase in "database time" in AWR