Oracle Validated Configuration RPM for OEL5

In the Oracle® Database Installation Guide for 11g there was a mention of Oracle Validated Configuration RPM.

When I first installed OEL5, the RPM’s for that o/s version were not yet released.

But since Oracle has now released the oracle-validated RPM’s for OEL5 on Unbreakable Linux Network. I decided to give it a try.

I installed OEL5 with the default installation. I then registered my machine with the up2date utility.

Do not forget to register to the proper channel on Unbreakable Linux Network or the following error will happen:

[root@oracletest ~]# up2date oracle-validated

Fetching Obsoletes list for channel: el5_i386_latest…

Fetching rpm headers…
########################################

Name Version Rel
———————————————————-

The following packages you requested were not found:
oracle-validated

Once registered, I validated the channel I was registered to:

[root@oracletest db_1]# up2date –nox –show-channels
el5_i386_oracle
el5_i386_latest

Here is my installation output:

[root@oracletest ~]# up2date oracle-validated

Fetching Obsoletes list for channel: el5_i386_oracle…
########################################

Fetching Obsoletes list for channel: el5_i386_latest…

Fetching rpm headers…
########################################

Name Version Rel
———————————————————-
oracle-validated 1.0.0 3.el5 i386

Testing package set / solving RPM inter-dependencies…
########################################
compat-db-4.2.52-5.1.i386.r ########################## Done.
compat-gcc-34-3.4.6-4.i386. ########################## Done.
compat-gcc-34-c++-3.4.6-4.i ########################## Done.
compat-libstdc++-33-3.2.3-6 ########################## Done.
elfutils-libelf-devel-0.125 ########################## Done.
gcc-4.1.1-52.el5.2.i386.rpm ########################## Done.
gcc-c++-4.1.1-52.el5.2.i386 ########################## Done.
glibc-devel-2.5-12.i386.rpm ########################## Done.
glibc-headers-2.5-12.i386.r ########################## Done.
libXp-1.0.0-8.i386.rpm: ########################## Done.
libaio-devel-0.3.106-3.2.i3 ########################## Done.
libstdc++-devel-4.1.1-52.el ########################## Done.
oracle-validated-1.0.0-3.el ########################## Done.
sysstat-7.0.0-3.el5.i386.rp ########################## Done.
unixODBC-2.2.11-7.1.i386.rp ########################## Done.
unixODBC-devel-2.2.11-7.1.i ########################## Done.
cpp-4.1.1-52.el5.2.i386.rpm ########################## Done.
elfutils-libelf-devel-stati ########################## Done.
libgcc-4.1.1-52.el5.2.i386. ########################## Done.
libgomp-4.1.1-52.el5.2.i386 ########################## Done.
libstdc++-4.1.1-52.el5.2.i3 ########################## Done.
Preparing ########################################### [100%]

Installing…
1:libgcc ########################################### [100%]
2:libstdc++ ########################################### [100%]
3:libstdc++-devel ########################################### [100%]
4:unixODBC ########################################### [100%]
5:glibc-headers ########################################### [100%]
6:glibc-devel ########################################### [100%]
7:compat-gcc-34 ########################################### [100%]
8:compat-gcc-34-c++ ########################################### [100%]
9:unixODBC-devel ########################################### [100%]
10:compat-db ########################################### [100%]
11:compat-libstdc++-33 ########################################### [100%]
12:libgomp ########################################### [100%]
13:cpp ########################################### [100%]
14:gcc ########################################### [100%]
15:gcc-c++ ########################################### [100%]
16:sysstat ########################################### [100%]
17:libaio-devel ########################################### [100%]
18:libXp ########################################### [100%]
19:elfutils-libelf-devel ########################################### [100%]
20:oracle-validated ########################################### [100%]
21:elfutils-libelf-devel-s########################################### [100%]
The following packages were added to your selection to satisfy dependencies:

Name Version Release
————————————————————–
compat-db 4.2.52 5.1
compat-gcc-34 3.4.6 4
compat-gcc-34-c++ 3.4.6 4
compat-libstdc++-33 3.2.3 61
elfutils-libelf-devel 0.125 3.el5
gcc 4.1.1 52.el5.2
gcc-c++ 4.1.1 52.el5.2
glibc-devel 2.5 12
glibc-headers 2.5 12
libXp 1.0.0 8
libaio-devel 0.3.106 3.2
libstdc++-devel 4.1.1 52.el5.2
sysstat 7.0.0 3.el5
unixODBC 2.2.11 7.1
unixODBC-devel 2.2.11 7.1
cpp 4.1.1 52.el5.2
elfutils-libelf-devel-static 0.125 3.el5
libgcc 4.1.1 52.el5.2
libgomp 4.1.1 52.el5.2
libstdc++ 4.1.1 52.el5.2

Oracle Validated Configuration did not only install the necessary RPM’s, it also created the oracle user/group’s, along with a couple of Oracle installation’s Requirements file modifications.

I was able to see my system configuration changes with this log file:

/etc/sysconfig/oracle-validated/results/orakernel.log

Creating oracle user passed

Verifying kernel parameters as per Oracle recommendations…
fs.file-max 327679
kernel.msgmni 2878
kernel.msgmax 65536 8192
kernel.sem 250 32000 100 142
kernel.shmmni 4096
kernel.shmall 268435456 3279547
kernel.sysrq 0 1
net.core.rmem_default 262144
net.core.rmem_max 2097152
net.core.wmem_default 262144
net.core.wmem_max 262144
fs.aio-max-nr 3145728
net.ipv4.ip_local_port_range 1024 65000
Setting kernel parameters as per oracle recommendations…
Altered file /etc/sysctl.conf
Original file backed up at /etc/sysctl.conf.orabackup
Verifying & setting of kernel parameters passed

Verifying oracle user OS limits as per Oracle recommendations…
oracle soft nofile 131072
oracle hard nofile 131072
oracle soft nproc 131072
oracle hard nproc 131072
oracle soft core unlimited
oracle hard core unlimited
oracle soft memlock 3500000
oracle hard memlock 3500000
Setting oracle user OS limits as per Oracle recommendations…
Altered file /etc/security/limits.conf
Original file backed up at /etc/security/limits.conf.orabackup
Verifying & setting of user limits passed

Verifying kernel boot parameters as per Oracle recommendations…
Setting kernel boot parameters as per Oracle recommendations…
Boot parameters will be effected on next reboot
Altered file /boot/grub/grub.conf
Original file backed up at /boot/grub/grub.conf.orabackup
Verifying & setting of boot parameters passed

Verifying module parameters as per Oracle recommendations…
Setting module parameters as per Oracle recommendations…
Altered file /etc/modprobe.conf
Original file backed up at /etc/modprobe.conf.orabackup
e1000 settings modified to include FlowControl=1
insmod /lib/modules/2.6.18-8.el5/kernel/drivers/char/hangcheck-timer.ko hangcheck_reboot=1
hangcheck-timer module settings modified to include hangcheck_reboot=1
Oct 2 12:01:35 oracletest ntpd[3529]: time reset -0.771217 s
Oct 2 12:05:38 oracletest ntpd[3529]: synchronized to LOCAL(0), stratum 10

Note that every modified files are backup up by the installation.

All this automation is very neat and save some time. However, some manual work still needs to be done afterwards.
The following files were not modified by the oracle-validated-1.0.0-3.el5.i386 RPM:

–/etc/pam.d/login

–/etc/profile

connected as user oracle:

–.bash_profile

Setting Shell Limits for the oracle User and Configuring the oracle User’s Environment section in the Oracle® Database Installation Guide recommend to configure those files above.

Also, Oracle 11g installer failed the Prerequisites Checks for 2 kernel parameters.

Checking operating system requirements …
Checking kernel parameters
Checking for rmem_default=4194304; rmem_default=262144. Failed <<<<
Checking for rmem_max=4194304; rmem_max=2097152. Failed <<<<

I had to manually change those 2 parameters in the /etc/sysctl.conf file.

References:

Wim Coekaerts Blog

Kevin Closson’s Oracle Blog

Oracle and Linux on OTN

Advertisements

6 Comments

Oracle 11g Documentation

Since reading the documentation is always a good idea when a new Oracle release is out, I decided to read Oracle® Database Upgrade Guide and the Oracle® Database New Features Guide before getting into the 11g installation guide.

It is funny how I haven’t yet downloaded the new Oracle 11g database and I am already using one of its new features!

Oracle 11g now enables users to send out comments, making it faster and easier to everyone to enhance the documentation.
This new feature have also been discussed here:
IT-eye Weblog
OracleAppsLab
OTN TechBlog

While reading through the 2 guides, I used this new feature to report a couple of errors:
Oracle has responded to my user comments pretty fast:

Thank you for sending us this correction to the Oracle Database Upgrade Guide. The problem has been fixed and the correction should appear in the next revision of the book (probably in part number B28300-02).

Submitter: my_mail
Book title: Oracle Database Upgrade Guide
Part number: b28300
Release: 11g Release 1 (11.1)
Topic title: Compatibility and Interoperability
URL: http://download.oracle.com/docs/cd/B28359_01/server.111/b28300/compat.htm
Status: Forwarded
Submitted on: 28-AUG-07

In the Automatic Maintenance Tasks Management section, the “See Also” link was pointing to the wrong URL: http://download.oracle.com/docs/cd/B28359_01/server.111/b28310/repair.htm#ADMIN022

This page should have been pointing to a subsection of the Automatic Maintenance Tasks Management feature…

2 Comments

Recovering Corrupted Data Blocks

Last Week, I experienced a kernel crash using Oracle 10.2.0.3 on my Oracle Enterprise Linux 4 sandbox server. There was not a single reaction from the server, I had to reboot the box by pressing the reset button.
I happened to have a backup of this sandbox database, which gave me the opportunity to perform a media recovery.

The situation was that the undo Tablespace had a corrupt block.

Here is the alert log content:

Mon Aug 20 08:54:44 2007
SMON: enabling cache recovery
Mon Aug 20 08:54:45 2007
Errors in file /opt/oracle/app/oracle/admin/orcl/udump/orcl_ora_6733.trc:
ORA-01578: ORACLE data block corrupted (file # 2, block # 9)
ORA-01110: data file 2: ‘/OraData2/oradata/orcl/undotbs01.dbf’
Mon Aug 20 08:54:45 2007
Error 1578 happened during db open, shutting down database
USER: terminating instance due to error 1578
Instance terminated by USER, pid = 6733
ORA-1092 signalled during: ALTER DATABASE OPEN…

Just when I was about to restore the whole datafile, I remembered a nice Oracle 10g feature called Block-level media recovery.

Note that this is a Enterprise Edition feature.

I had a huge load of tasks to do that particular morning and I was very happy with the time saved recovering that datafile.

Here is the RMAN output session:

RMAN> blockrecover datafile 2 block 9;
Starting blockrecover at 20-AUG-07
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=156 devtype=DISK
allocated channel: ORA_DISK_2
channel ORA_DISK_2: sid=154 devtype=DISK
channel ORA_DISK_1: restoring block(s)
channel ORA_DISK_1: specifying block(s) to restore from backup set
restoring blocks of datafile 00002
channel ORA_DISK_1: reading from backup piece /OraArch1/ORCL/backupset/2007_08_19/o1_mf_nnndf_BACKUP_ORCL.TO_0_3dl0ygb3_.bkp
channel ORA_DISK_1: restored block(s) from backup piece 1
piece handle=/OraArch1/ORCL/backupset/2007_08_19/o1_mf_nnndf_BACKUP_ORCL.TO_0_3dl0ygb3_.bkp tag=BACKUP_ORCL.TO_081907110003
channel ORA_DISK_1: block restore complete, elapsed time: 00:03:56
starting media recovery
media recovery complete, elapsed time: 00:00:03
Finished blockrecover at 20-AUG-07
RMAN> backup validate datafile 2;
Starting backup at 20-AUG-07
using channel ORA_DISK_1
using channel ORA_DISK_2
channel ORA_DISK_1: starting compressed full datafile backupset
channel ORA_DISK_1: specifying datafile(s) in backupset
input datafile fno=00002 name=/OraData2/oradata/orcl/undotbs01.dbf
channel ORA_DISK_1: backup set complete, elapsed time: 00:01:35
Finished backup at 20-AUG-07

It was as easy as this ! The database is now up & running, with no further issue in this matter.

Leave a comment

Oracle OpenWorld, here I come


Leave a comment

ORA-01555: snapshot too old when using flashback query

Last week, a fellow DBA needed help with the error ORA-01555 using the flashback query feature in oracle 10g. On his database, the automatic undo management feature was enabled.

He wanted to obtain a table’s data from 12 hours ago because of a user’s mistake.
The source of the issue was that the UNDO tablespace was inappropriately sized and that the UNDO_RETENTION parameter value was set to the default value of 900(15 minutes). UNDO_RETENTION Reference

Since the database is an “OLTP” system with hundreds of transactions per minutes, the undo tablespace did not have the needed data to flashback the table to the desired time.
The only solution was to restore the whole database to the last valid backup on another server and then export the desired information. The process took an afternoon, but could have taken 5 minutes if the proper settings where in place.

The ORA-01555 error can be caused when the UNDO_RETENTION parameter is improperly set. And the UNDO_RETENTION parameter is only honored if the current undo tablespace has enough space. ORA-01555 Using Automatic Undo Management – Causes and Solutions

Here is a script estimate_undo.txt to help you size the undo tablespace appropriately with the desired UNDO_RETENTION parameter value.

sys@ORCL> @estimate_undo

Current UNDO_RETENTION value
—————————————————————–
900

Specify the desired UNDO_RETENTION in seconds. Ex: 86400=24 hours
Enter value in seconds: 86400

Using 86400 seconds of UNDO_RETENTION

Megs needed for UNDO
——————–
9444

Current max Undo Size in MB
——————————
5000

UNDO Size in MB FILE_NAME
—————— ——————————————————
2500 /OraData1/oradata/orcl/undotbs01.dbf
2500 /OraData2/oradata/orcl/undotbs02.dbf

In this case, the undo tablespace needs to be enlarged by 4.5 GB.
The undo_retention parameter needs to be set to 86400.

Alter database datafile ‘/OraData1/oradata/orcl/undotbs01.dbf’ resize 4750m;
Alter database datafile ‘/OraData2/oradata/orcl/undotbs02.dbf’ resize 4750m;
Alter system set undo_retention = 86400 scope=both;

The Flashback operations could even be 100% guaranteed by specifying the RETENTION GUARANTEE clause of the undo tablespace. Enabling retention guarantee can cause multiple DML operations to fail. Use with caution.

The UNDO Advisor can also help with the undo retention settings and for the sizing of the UNDO tablespace. Chris Foot has more info about the Undo Advisor here; his article also explains how the ORA-01555 error maintains the read consistency.

No additional Oracle license is required for using the UNDO Advisor; but I recommend you read this Metalink Note.

In Oracle 11g, there is a new feature called “Flashback Data Archive” that captures historical data that can be retained for as long as the business demands.

Yuri van Buren has a good example of this new feature on his blog: Blogging about 11g – Part 3 – Flashback Data Archive a.k.a. Total Recall

More info on Flashback Data Archive:

Flashback Data Archive Whitepaper

Total Recall

References:

How To Size UNDO Tablespace For Automatic Undo Management

Recovering Table data Using the Flashback Table Feature

ORA-01555 “Snapshot too old” – Detailed Explanation

1 Comment

Oracle Critical Patch Update – July 2007

Last Friday, on a test box, I installed the July 2007 – Oracle Critical Patch Update.

The target database is a 10.2.0.3 running on Oracle Enterprise Linux 4. No CPU has yet been installed on this database.

I went through the following error while installing the CPU:

[oracle@orcltest 6079591]$ opatch napply ./6079591 -skip_subset -skip_duplicate
Invoking OPatch 10.2.0.3.0

Oracle interim Patch Installer version 10.2.0.3.0
Copyright (c) 2005, Oracle Corporation. All rights reserved..

Syntax Error… Unrecognized Command or Option: failed to parse arguments “unknown option ‘-skip_subset'”
Please use the option ‘opatch -help’ to get correct syntax

OPatch failed with error code 14

It turns out I read through the readme file a bit too fast, and skipped the following section:

  • 3.2 OPatch Utility Information

3.2 specifies to use OPatch utility release 10.2.0.3.2 or later.

The OPatch 10.2.0.3.2 can be downloaded from Oracle Metalink with patch 4898608.

I strongly suggest to read the July 2007 CPU readme file carefully since there is also new stuff as:

If you have already installed CPUJan2007, CPUApr2007, or CPUApr2007 CPU merge patch, the patch needs to be rolled back first.

Also, a new database CPU patch format is being introduced with the July 2007 CPU, the new format, napply CPU (pronounced “n apply CPU”).

This means, no more:

opatch apply

Use:

opatch napply ./6079591 -skip_subset -skip_duplicate.

Useful links for July’s CPU:

Oracle Critical Patch Update – July 2007

Critical Patch Update Availability Information for Oracle Server and Middleware Products

Critical Patch Update – Introduction to Database Napply CPU Patches

Using Enterprise Manager to achieve grid automation with deployment procedures

Nice to have links for Oracle CPU’s:

Security Technology Center

RSS feed for Oracle Security Alerts

DataCenter Automation and Configuration Management

2 Comments

SQL Tuning Advisor

Even though Oracle Enterprise Manager 10g Grid Control is a very handy tool, it is possible to use the SQL Tuning Advisor via SQL*Plus, so if you don’t have access to OEM, or if you simply are a command line fan, here is the alternative to using OEM for the SQL Tuning Advisor.

Below is a SQL*Plus script execution output, this script (available below) simply requires to paste in the query to be tuned.

SQL> @tune

Enter value for query: select a.empno, a.ename, b.dname from emp a, dept b where a.deptno = b.deptno and a.ename = ”SMITH”

This script will execute the SQL Tuning Advisor and will provide the same recommendations returned by OEM.

Here is the output:

TUNING_TASK
———————————————————————————————–
GENERAL INFORMATION SECTION
——————————————————————————-
Tuning Task Name : TASK_33842
Tuning Task Owner : STEEVE
Scope : COMPREHENSIVE
Time Limit(seconds) : 1800
Completion Status : COMPLETED
Started at : 07/24/2007 09:33:08
Completed at : 07/24/2007 09:33:08
Number of Index Findings : 1

——————————————————————————-
Schema Name: STEEVE
SQL ID : 8uu071up6vk3q
SQL Text : select a.empno, a.ename, b.dname from emp a, dept b where
a.deptno = b.deptno and a.ename = ‘SMITH’

——————————————————————————-
FINDINGS SECTION (1 finding)
——————————————————————————-

1- Index Finding (see explain plans section below)
————————————————–
The execution plan of this statement can be improved by creating one or more
indices.

Recommendation (estimated benefit: 100%)
—————————————-
– Consider running the Access Advisor to improve the physical schema design
or creating the recommended index.
create index STEEVE.IDX$$_84320001 on STEEVE.EMP(‘ENAME’);

Rationale
———
Creating the recommended indices significantly improves the execution plan
of this statement. However, it might be preferable to run “Access Advisor”
using a representative SQL workload as opposed to a single statement. This
will allow to get comprehensive index recommendations which takes into
account index maintenance overhead and additional space consumption.

——————————————————————————-
EXPLAIN PLANS SECTION
——————————————————————————-

1- Original
———–
Plan hash value: 3487251775

—————————————————————————————-
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
—————————————————————————————-
| 0 | SELECT STATEMENT | | 1 | 26 | 5 (0)| 00:00:01 |
| 1 | NESTED LOOPS | | 1 | 26 | 5 (0)| 00:00:01 |
|* 2 | TABLE ACCESS FULL | EMP | 1 | 13 | 4 (0)| 00:00:01 |
| 3 | TABLE ACCESS BY INDEX ROWID| DEPT | 1 | 13 | 1 (0)| 00:00:01 |
|* 4 | INDEX UNIQUE SCAN | DEPT_PK | 1 | | 0 (0)| 00:00:01 |
—————————————————————————————-

Predicate Information (identified by operation id):
—————————————————

2 – filter(“A”.”ENAME”=’SMITH’)
4 – access(“A”.”DEPTNO”=”B”.”DEPTNO”)

2- Using New Indices
——————–
Plan hash value: 2308654808

———————————————————————————————–
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
———————————————————————————————–
| 0 | SELECT STATEMENT | | 1 | 26 | 3 (0)| 00:00:01 |
| 1 | NESTED LOOPS | | 1 | 26 | 3 (0)| 00:00:01 |
| 2 | TABLE ACCESS BY INDEX ROWID| EMP | 1 | 13 | 2 (0)| 00:00:01 |
|* 3 | INDEX RANGE SCAN | IDX$$_84320001 | 1 | | 1 (0)| 00:00:01 |
| 4 | TABLE ACCESS BY INDEX ROWID| DEPT | 1 | 13 | 1 (0)| 00:00:01 |
|* 5 | INDEX UNIQUE SCAN | DEPT_PK | 1 | | 0 (0)| 00:00:01 |
———————————————————————————————–

Predicate Information (identified by operation id):
—————————————————

3 – access(“A”.”ENAME”=’SMITH’)
5 – access(“A”.”DEPTNO”=”B”.”DEPTNO”)

——————————————————————————-

The scripts can be downloaded here: tune_advisor.txt and tune.txt

Note: The user running the script needs the Advisor role.

References:

SQL Tuning Advisor

Using SQL Tuning Advisor APIs

Required License

Leave a comment