Archive for category Oracle

Step by Step learning of Oracle ADF 11G using 10 Video Training Sessions

Imran Mirza did a good job compiling those training videos from Oracle:

http://jwebcenter.blogspot.com/2011/06/step-by-step-learning-of-oracle-adf-11g.html

Enjoy!

www.renaps.com

Advertisements

Leave a comment

Interesting Corrupted Redo Log Case

It has been a while since I wrote on Renaps’ Blog .. most probably because I didn’t run through any interesting case to talk about for a while !

Yesterday, at one of our main clients, the production (soon to be – on Monday) database hung. Was it because it was a Feb 29th of a bisextile year ? or simply because the week-end arriving in the next 4 hours was the go-live of the main data warehouse ? — Its hard to say, I believe that it was just a little wink from our old friend Murphy !

The environment is a 16 Itanium CPUs Windows Server running 10.2.0.3.0; ASM is used even though the database runs on a single instance mode.

When the database hung, the alert.log showed the following error messages (of course, no one could connect or run any transaction on the database):

ARC0: All Archive destinations made inactive due to error 354
Committing creation of archivelog ‘+FLASH/inf1/archivelog/arc17972_0627748574.001’ (error 354)
ARCH: Archival stopped, error occurred. Will continue retrying
Fri Feb 29 11:48:05 2008
Errors in file c:oraadmininf1bdumpinf1_arc0_10236.trc:
ORA-16038: Message 16038 not found; No message file for product=RDBMS, facility=ORA; arguments: [1] [17972]
ORA-00354: Message 354 not found; No message file for product=RDBMS, facility=ORA
ORA-00312: Message 312 not found; No message file for product=RDBMS, facility=ORA; arguments: [1] [1] [+FLASH/inf1/onlinelog/group_1.257.627748575]

The first line of this message stack made me believe that the database just hung because there was no more space for the archiver to do its job. But after further analysis, it appeared that the problem was much more serious.

The actual cause of the issue was related to Redo-Log Corruption. The cause of the corruption was a “write or rewrite” SAN related error.

When Redo-Log corruption occurs, Oracle first tries to use the second (or third) member of the same group showing corruption to avoid un-availability of the database. In our case, this was impossible since there was only one member per group of Redo-Log — I was surprised to see that this client did not multiplex the Redo-Logs …

My first attempt to get the database back on its legs was to dump the content of the redo-log showing corruption issues (in this case, group 1).

SQL> ALTER SYSTEM DUMP LOGFILE ‘+FLASH/inf1/onlinelog/group_1.257.627748575’;
ALTER SYSTEM DUMP LOGFILE ‘+FLASH/inf1/onlinelog/group_1.257.627748575’
*
ERREUR à la ligne 1 :
ORA-00354: corrupt redo log block header
ORA-00353: log corruption near block 53504 change 70389305 time 02/29/2008 11:48:02
ORA-00334: archived log: ‘+FLASH/inf1/onlinelog/group_1.257.627748575’

As you can see, this attempt failed, I then attempted to clear the unarchived redo-log:
SQL> alter database clear unarchived logfile ‘+FLASH/inf1/onlinelog/group_1.257.627748575’;

at this point, my command hung indefinitely, and I realized that pmon was holding a lock on the Redo-log Header block…

My margin of maneuver has become much smaller, here is the sequence of commands that brought the database back on track:

  1. shutdown abort;
  2. startup mount;
  3. issue the same command that was previously hanging (this time, it worked right away) :
    alter database clear unarchived logfile ‘+FLASH/inf1/onlinelog/group_1.257.627748575’;
  4. alter database open;
  5. shutdown immediate;
  6. Immediately take a FULL backup at this point;
  7. startup;
  8. Don’t forget to multiplex your Redo-Logs to avoid to run into this situation again !

Here you go, Redo-Log corruption on the header block of a group when only one member is used does look bad at first sight but I hope this article can help you if you run into a similar scenario.

References:

Metalink Notes:
145769.1
332672.1
438176.1
114734.1

Patrick Hamou
E-Mail: phamou@renaps.com
Web: www.renaps.com

4 Comments

Oracle VM

Oracle VM is now available for download.

You can read the news here.

Oracle VM Homepage.

Leave a comment

11g on Windows (32-bit) is out!

Thanks to OTN Headlines. I’m downloading Oracle 11g for windows at this moment.

But only the client version, since all Oracle servers I managed runs on Linux.

With the Windows client, I will be able to use cool new features like errorlogging.

Leave a comment

11g on Linux x86_64 is out!

This morning, I was surprised to learn that the Linux x86_64 announcement release was not in this Oracle New Downloads RSS Feed. I got aware of the news reading Niall Litchfield Blog. I guess news travels faster via the Oracle blog community! , also, I thought Oracle would have normally released 11g for other o/s prior to releasing the Linux x86_64 version. In any case, I will download this release now.

It is now available here: Oracle 11g on Linux x86_64.

I have been waiting for this release for a while since two of my databases are stuck to 32 bits as they need to send and receive data using “hsodbc” (only available for 32bit) for an ERP software.

Heterogeneous systems are quite common, and I am pretty sure that many of the enhancement requests for Generic Connectivity were focused on making them available for 64-bit platforms.
The
Metalink Note:361676.1, Is Generic Connectivity Available On The LINUX Or Windows 64-bit Platforms? , explains that Generic Connectivity will be available on Linux Red Hat 64-bit and SUSE 64-bit with 11gR1.

Another reason that makes me appreciate this new release is that I ran through several issues while trying to run Oracle with larger SGA’s on 32 bits platforms. I used RAMFS and HugePages to enable the use of 8 to 10GB of SGAs. The following links helped me in this process:

Tuning and Optimizing Red Hat Enterprise Linux for Oracle 9i and 10g Databases

Metalink Note: 317141.1, How to Configure RHEL 4 32-bit for Very Large Memory with ramfs and HugePages

I am hoping that “hsodbc” will work fine on 11g x86_64; I’ll keep you posted on my findings…

2 Comments

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

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