Tuesday, 30 December 2014

Oracle Redo log tuning

Question: How to tune the Oracle redo logs?

Answer: Oracle redo logs are archived when you turn-on archive-log mode, and the overhead relates to the work done by the LGWR and ARCH background processes as they write the redo information. 

The steps for tuning redo log performance are straightforward:

1 - Determine the optimal sizing of the log_buffer.
2 - Size online redo logs to control the frequency of log switches and minimize system waits.

3 - Optimize the redo log disk to prevent bottlenecks.  In high-update databases, no amount of disk tuning may relieve redo log bottlenecks, because Oracle must push all updates, for all disks, into a single redo location.

 Once you have optimized your redo and I/O sub-system, you have few options to relieve redo-induced contention.  This can be overcome by employing super-fast solid-state disk for your online redo log files, since SSD has far greater bandwidth than platter disk.  This will greatly improve DML throughput for high-update databases.  As SSD prices continue to fall, SSD becomes increasing affordable, and SSD will someday replace spindle platter disk for all Oracle data storage.

Size of the redo log members (files on disk) size, then use the query below:
-- Show Redo Logs info
set linesize 300
column REDOLOG_FILE_NAME format a50
    (a.BYTES/1024/1024) AS SIZE_MB
FROM v$log a
JOIN v$logfile b ON a.Group#=b.Group#

The query output should look like:
---------- ---------- ---------- --- ---------------- ---------------------
1       1       4 YES INACTIVE /ORACLE/oradata/orcl1/redo01.log   50
2       1       5 YES INACTIVE /ORACLE/oradata/orcl1/redo02.log   50
3       1       6 NO  CURRENT  /ORACLE/oradata/orcl1/redo03.log   50

 Tracking Redo Log usage

select * from (select a.username,a.sid,b.value 
from v$session a,v$sesstat b,v$statname c 
where a.sid=b.sid and b.statistic#=c.statistic# and c.name='redo size'
order by b.value desc) where rownum < 11;

Some of the scripts which can be useful. 


select value from v$sysstat 
where name = 'redo size' 

PROMPT Redo generated during my session since the session started: 

select value redo_size 
from v$mystat, v$statname 
where v$mystat.STATISTIC# = v$statname.STATISTIC# 
and name = 'redo size' 

PROMPT Redo generated by current user sessions: 

select v$session.sid, username, value redo_size 
from v$sesstat, v$statname, v$session 
where v$sesstat.STATISTIC# = v$statname.STATISTIC# 
and v$session.sid = v$sesstat.sid 
and name = 'redo size' 
and value > 0 
and username is not null 
order by value 
SELECT s.sid, s.serial#, s.username, s.program, 
FROM v$session s, v$sess_io i 
WHERE s.sid = i.sid 
ORDER BY 5 desc, 1, 2, 3, 4; 

SELECT s.sid, s.serial#, s.username, s.program, 
t.used_ublk, t.used_urec 
FROM v$session s, v$transaction t 
WHERE s.taddr = t.addr 
ORDER BY 5 desc, 6 desc, 1, 2, 3, 4;

select b.inst_id, b.SID, b.serial# sid_serial, b.username, machine, b.osuser, b.status, a.redo_mb MB
from (select n.inst_id, sid, round(value/1024/1024) redo_mb from gv$statname n, gv$sesstat s
where n.inst_id=s.inst_id and n.statistic#=134 and s.statistic# = n.statistic# order by value desc) a, gv$session b
where b.inst_id=a.inst_id
  and a.sid = b.sid
and   rownum <= 10;


SELECT to_char(begin_interval_time,’YYYY_MM_DD HH24:MI’),
FROM dba_hist_sqlstat dhss,
dba_hist_snapshot dhs,
dba_hist_sqltext dhst
WHERE upper(dhst.sql_text) LIKE ‘%segmentname%’
AND dhss.snap_id=dhs.snap_id
AND dhss.instance_Number=dhs.instance_number
AND begin_interval_time BETWEEN to_date(’2013_06_22 17',’YYYY_MM_DD HH24')
AND to_date(’2013_07_22 21',’YYYY_MM_DD HH24')
AND dhss.sql_id = dhst.sql_id;

The accounting for redo size by session is located in v$sesstat.  You can do a
Simple query for a RAC cluster to see the largest redo generating session:

select s.inst_id, s.sid, serial#, program, module, username, value redo_size
from gv$session s, gv$sesstat ss, v$statname sn where s.sid = ss.sid and
ss.statistic# = sn.statistic# and sn.name = 'redo size'
and s.inst_id = ss.inst_id
order by redo_size

11gR2 RAC Backup

RAC-Database Backup

RAC Database Back Through RMAN:-

Every backup plan has a goal, which is to
provide a speedy recovery. In addition, depending on what your strategy is, you
need to configure your RAC accordingly. RMAN needs to make a dedicated
connection, unlike a typical client, through the regular ONS (Oracle Net
Services), which means all it needs is one
node of the cluster. Moreover, you can allocate channels at each node of your
RAC by specifying a command as such:
allocate channel x1 type sbt connect sys/password@rac1;
allocate channel x2 type sbt connect sys/password@rac2;
allocate channel x3 type sbt connect sys/password@rac3;
allocate channel x4 type sbt connect sys/password@rac4;

That way you can distribute the workload across nodes without doing all of the I/O intensive job via that connected node. Again, it depends on your architectural setup and backup policy. It could very well be that you want to do it via an nth node, which has an additional 10 Gbps card connected to your SAN and this node happens to be supporting a typical DSS system, which is not under stress during the schedule job or on an OLTP environment where that particular node purely services backup. Just an example scenario, but as I mentioned, discuss it thoroughly with your system Admins and SAN admin (should you have a SAN that is) before working on your backup strategy. While some environments may respond well to a distributed RMAN activity (a typical non-24x7 environment comes to mind), a single node might be best for a heavy 24/7 OLTP environment.
Configure the RMAN default channels.
RMAN> configure device type disk parallelism 2 backup type to compressed backupset;
RMAN> configure channel 1 device type disk connect 'sys/sys@rac1';
RMAN> configure channel 2 device type disk connect 'sys/sys@rac2';
RMAN> show all;
Hot backup of the entire database. 
RMAN> backup database plus archivelog; 
Crosscheck the backup. 
RMAN> list backupset summary;

RMAN> allocate channel for maintenance device type disk;
RMAN> crosscheck backup;
RMAN> list backupset summary;

Friday, 26 December 2014

Oracle Apps 12.2.2 Installation steps on Linux5.4

How to install Oracle E-Business Suite 12.2.2 on Linux 5.4(64-bit)?

Create Operating System User depending on Single User or Multi-User Installation :

a) Single -User  Installation : oracle where both Application Tier and Database Tier are owned by single user
b) Multi-User Installation : oracle & applmgr where Application Tier is owned by one user (applmgr) and Database Tier by another (oracle)

I am doing multi-user & Single node  installation and using applmgr & oracle both member of group dba

Download Software:-

 Oracle E-Business Suite Release 12.2.2 Media Pack for Linux x86-64-bit
software from https://edelivery.oracle.com/EPD/Download/get_form?egroup_aru_number=16809533

We need to create the user and group as following:
[root@apps ~]# groupadd dba
[root@apps ~]# useradd  -g dba oracle
[root@apps ~]# useradd  -g dba applmgr
[root@apps ~]# passwd oracle
[root@apps ~]# passwd applmgr

Create the Directory structure as following:
[root@apps ~]# mkdir /u01/db
[root@apps ~]# chown -R oralce:dba /u01/db

We need to copy the all above zip files into /u01/Stage/ and change the permission to oracle:dba and change the mode(chmod 775 /u01/zipfiles)

Create the oraInventory Directory as following:
[root@apps ~]# mkdir /u01/db/oraInventory
[root@apps ~]# chown -R oralce:dba /u01/db/ oraInventory
[root@apps ~]# chmod -R 775 /u01/db/ oraInventory

goto /etc and edit the oraInst.loc file and set the oraInventory location as following:
[root@apps etc]# vi oraInst.loc
[root@apps etc]# cat oraInst.loc

Goto the zip files location through root user and unzip the following zip file only

[root@apps Stage]#unzip V35215-01_1of3.zip
after unzip the above zip file, you need to follow the below steps:
[root@apps bin]# pwd

[root@apps bin]# sh buildStage.sh
Build Stage Menu
1.     Create new stage area
2.     Copy new patches to current stage area.
3.     Display existing files in stage TechPatches.
4.     Exit menu
Enter your choice [4]: 1
Rapid Install Platform Menu
1.    Oracle Solaris SPARC (64-bit)
2.    Linux x86-64
3.    IBM AIX on Power Systems (64-bit)
4.    HP-UX Itanium
5.    Exit Menu
Enter your choice [5]: 2
/u01/Stage/startCD/Disk1/rapidwiz/bin/../jre/Linux_x64/1.6.0/bin/java -classpath /u01/Stage/startCD/Disk1/rapidwiz/bin/../jlib/emocmutl.jar:/u01/Stage/startCD/Disk1/rapidwiz/bin/../jlib/ewt-3_4_22.jar:/u01/Stage/startCD/Disk1/rapidwiz/bin/../jlib/share-1_1_18.jar:/u01/Stage/startCD/Disk1/rapidwiz/bin/../jlib/jnls.jar:/u01/Stage/startCD/Disk1/rapidwiz/bin/../jlib/ACC.JAR:/u01/Stage/startCD/Disk1/rapidwiz/bin/../jlib/netcfg.jar:/u01/Stage/startCD/Disk1/rapidwiz/bin/../jlib/ojdbc14.jar:/u01/Stage/startCD/Disk1/rapidwiz/bin/../jlib/OraInstaller.jar:/u01/Stage/startCD/Disk1/rapidwiz/bin/../jlib/java oracle.apps.ad.rapidwiz.util.StageBuilder /u01/Stage/startCD/Disk1/rapidwiz/bin
Please enter the directory containing the zipped installation media:
Unzipping V29856-01.zip
Unzipping V35803-01_1of3.zip
Unzipping V35231-01_2of5.zip
Unzipping V35804-01_1of2.zip
 After unzip all the files Stage is shown below:
Verifying stage area…

Directory /u01/Stage/TechInstallMedia is valid
Directory /u01/Stage/TechPatches/DB is valid
Directory /u01/Stage/TechPatches/MiddleTier is valid
Directory /u01/Stage/EBSInstallMedia/AppDB is valid
Directory /u01/Stage/EBSInstallMedia/Apps is valid
Directory /u01/Stage/EBSInstallMedia/AS10.1.2 is valid
Directory /u01/Stage/TechInstallMedia/database is valid
Directory /u01/Stage/TechInstallMedia/ohs11116 is valid
Directory /u01/TechInstallMedia/wls1036_generic is valid
Stage area verified.
Press enter to continue…

Build Stage Menu
1.     Create new stage area
2.     Copy new patches to current stage area.
3.     Display existing files in stage TechPatches.
4.     Exit menu

Enter your choice [4]: 4
Now, we start the Installation through root user as following:

[root@apps rapidwiz]# pwd

[root@apps rapidwiz]# ./rapidwiz

 Click Next

Click Next

Click Next



 Database configurations and Click Next

 Select suite licensing and Click Next

 Click Next

Click Next

Click Next

Select the  Language and character set as following
 Click Next

Enter the Application configurations and Click Next as following

Enter the Weblogic username & password as following: (password like oracle123)


Validate the system configurations Click Next 


E-Business Suite. It will take 3-4 hours time to Install.

 Validate the complete Installation Click Next
apps 19

Click Finish

apps 20

Now, Installation has completed successfully….
Now, we check opmnctl status are alive or not by using the  following command


Now, you should be able to access applications home page as following
http://<host name>.<domain name>:<HTTP port>/OA_HTML/AppsLogin

For example: (My URL)

apps 21

Now, we enter the username and password to access the applications

Also you should be able to access web-logic console home page as following

http://<host name>.<domain name>:<HTTP port>/console

For example: (My URL)

apps 23

Now, we see the servers details as following

apps 24



Multi-Node Installation of Oracle EBS (Apps) R12 on Linux

Installation of an Oracle EBS 12.1.1 on Linux5.5(32bit).

  Single  user Multi Node installation steps.
Prepare 2 system with same configuration.
All prerequisite done on both system.

Software preparation:-


Once you click on the link “Oracle E-Business Suite Release 12.1.1 Media Pack for Linux x86”, you have a lot of file to download. Not all of them are necessary for the installation of EBS. 

Check Free space:-
[root@sujeet ~]# df -h
[root@sujeet ~]# free
 Check Physical Memory. 
[root@sujeet ~]# grep MemTotal /proc/meminfoMemTotal:      
 2059516 kB/* At least 2GB of physical Memory (RAM) is required.
  In my case I have 2GB.*/ 
 Check Swap Space. 
[root@sujeet ~]# grep SwapTotal /proc/meminfoSwapTotal:      
     3148732 kB
1.Check Selinux
System>>Administration>>Security level & Firewell
  click on SElinux tab
  check SElinux--Disable.....
2.Now setup User that we will use as sujeet owner and the 
groups that it will need for installing and managing Oracle.
 GUI Mode:-
Create User,Group and assign group to user. 
System>>Administration>>User & Group create...
 Create User,Group and assign group to user.
[root@sujeet ~]# useradd -g dba -m sujeet 
[root@sujeet ~]# passwd sujeet 
Changing password for user oracle.
 New UNIX password: BAD PASSWORD: 
 Retype new UNIX password: 
 passwd: all authentication tokens updated successfully.
 3.create Base directory and give full permission RWX..
[root@r0146 /]# mkdir r12
[root@r0146 /]# chown -R sujeet:dba /r12
[root@r0146 /]# chmod -R 777 r12/
[root@r0146 /]# ll
4.Enter Hostname and IP Address in Host file.
[root@r0146 /]# vi /etc/hosts
# Do not remove the following line, or various programs
# that require network functionality will fail.               localhost.localdomain localhost
#::1            localhost6.localdomain6 loca:lhost6    sujeet.sonu.com sujeet
5.Check hostname in Network file 
[root@r0146 ~]# cat /etc/sysconfig/network
6. Ping hostname and IP 

[root@r0146 ~]# ping
[root@r0146 ~]# ping sujeet
7.Install RPM 
[root@r0146 /]# cd R12-SETUP/
[root@r0146 R12-SETUP]# ls
rpms  stageR12_32bit
[root@r0146 R12-SETUP]# cd rpms/
[root@r0146 rpms]# ls
 install RPM command:-
[root@r0146 rpms]# rpm -ivh compat-glibc-2.3.4-2.26.i386.rpm
[root@r0146 rpms]# rpm Uvh compat-glibc-2.3.4-2.26.i386.rpm

 Check RPM install or not through this command:-
[root@r0146 rpms]# rpm -qa|grep unixODBC
Edit Limit.conf file :- 
[root@panora03 ~]# vi /etc/security/limits.conf
hard      nofile         65535  soft       nofile         4096 hard      nproc         16384 soft       nproc         2047
8.check kernel parameter:-
[root@r0146 ~]# vi /etc/sysctl.conf
[root@r0146 ~]# cd /
 Go to location of sysctl.conf file:-
[root@r0146 /]# mv R12-SETUP/stageR12_32bit/pre_req/sysctl.conf 
mv: overwrite `/etc/sysctl.conf'? yes
[root@r0146 /]# cat /etc/sysctl.conf
# Kernel sysctl configuration file for Red Hat Linux
check kernel parameter:-
[root@r0146 ~]# /sbin/sysctl -p
kernel.shmall = 2097152
kernel.shmmax = 536870912

kernel.shmmni = 4096

kernel.sem = 250 32000 100 128

fs.file-max = 65536

net.ipv4.ip_local_port_range = 1024 5000





Restart system:- 
[root@r0146 ~]# init 6
LOGIN AS sujeet(new user)
[sujeet@sujeet ~]$ cd /R12-SETUP/stageR12_32bit/startCD/Disk1/rapidwiz/
[sujeet@sujeet ~]$  ./rapidwiz 
Open window>>>>>>>>>>>>>>

Select the Oracle Applications Release 12.1.1

Select suitable option if you need to download updates

Select [New Configuration]

Select the port pool, I have chosen the default i.e. 0, for my env.

Enter DB host info along with user and group

Select [Suite Licensing]


 Install the languages if you need multi-lingual support

Enter Application Node info along with owner and group

Review the info you entered for hosts. You can also add Additional Nodes here for Application tier file system
 Now the OUI will perform the pre-checks before the actual installation kicks in

Review the Validation Report. check the failed ones and click on Retry.

Installation will kick in, keep an eye on it. It will usually take around couple of hours depending on the speed of your machine.

 Once installation completes, the post install validation box will reappear. Make sure everything is green before going further

 At the end, click on Finish to end the installation of DB tier.

Next step is to install the application tier on appsnode host.

Before you start the install of the App tier you need to copy the config file from DB tier to apps tier.

To do this there are two ways

    1. Copy <sid>_<host>.xml file aka context file to appsnode
    2. start the install by issuing ./rapidwiz and provide the DB details to copy the config from DB

One can copy context file as follows

 [oracle@appsdbnode appsutil]$ cd /u01/app/oracle/visr12/db/tech_st/11.1.0/appsutil
[oracle@appsdbnode appsutil]$ scp conf_visr12.txt  appsnode:/home/oracle/


 click on [Saved configuration] option, and enter the path copied context file 

check the validation report to make sure everything is healthy.

 Click on [Next]

Review the progress of the installation

Review the progress of the installation

After install was finished, my post install checks failed as my HTTP server failed to start hence all other dependencies failed

Cause - 

After a bit of research I found out that the start failed due to the fact it failed to locate on library component and fix is the create the link for that missing component 

Solution -

[root@appsnode ~]# ln -s /usr/lib/libgdbm.so.2.0.0 /usr/lib/libdb.so.2

[oracle@appsnode scripts]$ adstrtal.sh  apps/apps

[oracle@appsnode scripts]$ adopmnctl.sh status

You are running adopmnctl.sh version 120.6

Checking status of OPMN managed processes...

Processes in Instance: visr12_appsnode.appsnode.localdomain


ias-component                              | process-type       |  pid         | status


OC4JGroup:default_group          | OC4J:oafm           |   31135 | Alive

OC4JGroup:default_group          | OC4J:forms          |   31070 | Alive

OC4JGroup:default_group          | OC4J:oacore        |   30984 | Alive

HTTP_Server                                   | HTTP_Server        |   30933 | Alive

After manually start the opmn stack, I click on [Retry] on validation page of installer and there you go. Everything came up


 Click on connect to Oracle application Release 12.1.1 link.

Login page open.

Post install, I tried to login to Apps console using following URL>>http://<hostname.domain>:<port_pool_no>

URL - http://appsnode.localdomain:8000

use default credentials - sysadmin/sysadmin to login console