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
SELECT
    a.GROUP#,
    a.THREAD#,
    a.SEQUENCE#,
    a.ARCHIVED,
    a.STATUS,
    b.MEMBER    AS REDOLOG_FILE_NAME,
    (a.BYTES/1024/1024) AS SIZE_MB
FROM v$log a
JOIN v$logfile b ON a.Group#=b.Group#
ORDER BY a.GROUP# ASC;

The query output should look like:
GROUP   #THREAD#  SEQUENCE# ARC STATUS   REDOLOG_FILE_NAME      SIZE_MB
---------- ---------- ---------- --- ---------------- ---------------------
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. 

PROMPT REDO GENERATION:-

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, 
i.block_changes 
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’),
dbms_lob.substr(sql_text,4000,1),
dhss.instance_number,
dhss.sql_id,executions_delta,rows_processed_delta
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;

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
softwares1




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
inventory_loc=/u01/db/oraInventory
[root@apps etc]# cat oraInst.loc
inventory_loc=/u01/db/oraInventory

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
/u01/Stage/startCD/Disk1/rapidwiz/bin

[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:
/u01/Stage
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
/u01/Stage/startCD/Disk1/rapidwiz

[root@apps rapidwiz]# ./rapidwiz



 Click Next


Click Next
 

Click Next
 


CLICK YES

 







 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


opmn



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

For example: (My URL)
http://apps.weblogic.com:8000/OA_HTML/AppsLogin

apps 21

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



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

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

For example: (My URL)
http://apps.weblogic.com:7001/console
username=weblogic
password=oracle123

apps 23

Now, we see the servers details as following

apps 24


**************************END*****************************************





 

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...
  (user-sujeet....group-dba)
 
 COMMAND LINE:- 
 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.
127.0.0.1               localhost.localdomain localhost
#::1            localhost6.localdomain6 loca:lhost6
172.16.3.142    sujeet.sonu.com sujeet
 
5.Check hostname in Network file 
 
[root@r0146 ~]# cat /etc/sysconfig/network
NETWORKING=yes
NETWORKING_IPV6=no
HOSTNAME=sujeet.sonu.com
 
6. Ping hostname and IP 

[root@r0146 ~]# ping 172.16.3.142
[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
6078836
compat-glibc-2.3.4-2.26.i386.rpm
compat-glibc-headers-2.3.4-2.26.i386.rpm
extra.repo
libaio-0.3.106-3.2.i386.rpm
libaio-devel-0.3.106-3.2.i386.rpm
libXp-1.0.0-8.1.el5.i386.rpm
openmotif21-2.1.30-11.EL5.i386.rpm
p6078836_101330_LINUX.zip
pr
rhel54_x86.repo
sysstat-7.0.2-3.el5.i386.rpm
unixODBC-2.2.11-7.1.i386.rpm
unixODBC-devel-2.2.11-7.1.i386.rpm
xorg-x11-libs-compat-6.8.2-1.EL.33.0.1.i386.rpm
 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 
 /etc/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

net.core.rmem_default=4194304

net.core.wmem_default=262144

net.core.rmem_max=4194304

net.core.wmem_max=262144

 
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/

conf_visr12.txt   



 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

ORA-08004: sequence IEX_DEL_BUFFERS_S.NEXTVAL exceeds MAXVALUE

 Error:- IEX: Scoring Engine Harness Error - ORA-08004: sequence IEX_DEL_BUFFERS_S.NEXTVAL exceeds MAXVALUE (Doc ID 2056754.1) SYMPTOMS:- Yo...