Vishal Gupta's Blog

Archive for the ‘Oracle’ Category

Oracle related blogs

Oracle 11gR2 on RHEL6

Posted by Vishal Gupta on Sep 19, 2011

Just a quick note to say that RedHat submitted certification test result of Oracle 11gR2 on RHEL6 to Oracle Corporation on 09-Aug-2011, so we should expect the formal certification around last week of Sep-2011.

More news at – http://www.redhat.com/about/news/blog/Red-Hat-Submits-Oracle-11gR2-on-Red-Hat-Enterprise-Linux-6-Certification-Test-Results-to-Oracle

[Update: 17-Apr-2012]

Oracle has finally certified 11gR2 on OEL6 and RHEL6. See the announcement here

Advertisements

Posted in 11gR2, Linux, Oracle | 17 Comments »

Direct Path Reads – 11g Changed Behaviour

Posted by Vishal Gupta on Aug 19, 2011

In 10g, serial full table scans for “large” tables used to always go through cache (by default). If table was small it was placed at most recently used (MRU) end of the buffer cache. If table is large it is placed at least recently used (LRU) end of the buffer cache.

In 11g, full table scan do not always go through the buffer cache. Decision to read via direct path or through cache is based on the size of the table, buffer cache and various other stats. Table is considered to be small or large based value of _small_table_threshold internal parameter.  Default value of this parameter is 2% of buffer cache size and is specified in blocks. This means any object (table) smaller than 2% of the buffer cache will be read via the buffer cache and not using direct path read. And tables larger than 2% of the buffer cache are read via direct path read and not via buffer cache. With AMM (Automatic Memory Management) or ASMM (Automatic Shared Memory Management), buffer cache could drop to a lower value if memory is falling short for shared pool. In such a case after restart of instance, _small_table_threshold parameter would become even lower due to decreased buffer cache.

By enabling event 10949 at either session or system/instance level one can disable the autotuning of direct path reads for full table scans. It means full table scan on every table whether small or large, will go via buffer cache. This could flush out the already cached cached objects. I would strongly advise against setting this for all the databases in our standard build.

[/source]SQL> alter system set events '10949 trace name context forever' scope=spfile;

$ oerr ora 10949 10949, 00000, "Disable autotune direct path read for full table scan"
 // *Cause:
 // *Action: Disable autotune direct path read for serial full table scan.

If you are doing full table scans on the same LARGE table too often, then I would suggest to tune the query or create an index on the table. You could also set the minimum value for db_cache_size, so that it does not fall below the minimum (normal workload) levels and small_table_threshold parameter value does not fall below desired threshold.

Related Oracle MOS Notes

Doc ID 793845.1 – High ‘direct path read’ waits in 11g

Doc ID 787373.1 – How does Oracle load data into the buffer cache for table scans ?

Posted in Oracle | 9 Comments »

Guaranteed Restore Point and Flashback Mode – Incorrect Documentation

Posted by Vishal Gupta on Jul 18, 2011

I was going to write a new post about change in behaviour of Guaranteed Restore Point and Flashback mode. But looks like someone has already written about it at given below link. Its seems behaviour has changed in 11gR2, but documentation still refers to old behaviour.

http://asanga-pradeep.blogspot.com/2011/06/restore-point-prerequisites-change-in.html

I have raised a Oracle Service Request to correct the documentation. Documentation Bug – 12763133.

 

 

Posted in 11gR2, Backup, Flashback, Oracle | Leave a Comment »

Turning Flashback ON – Incorrect Documentation

Posted by Vishal Gupta on Jul 18, 2011

Oracle documentation states that you can not use the FLASHBACK database clause (i.e turn it ON or OFF) while database is OPEN. Flashback mode can be turned ON only when database is in MOUNT mode. This is true in Oracle version 10.1, 10.2, 11.1. But in 11gR2, Oracle actually allows you to turn on the flashback even in OPEN mode, even though documentation says otherwise. I have raised an Oracle Service Request with Oracle to get this documentation bug fixed.

[Update 19-Jul-2011]

Following bug has been raised by Oracle Support.

Bug 12768808: FLASHBACK DATABASE DO NOT REQUIRE TO BE IN MOUNT MODE FROM 112 ONWARDS

Excerpt from 11gR2 documentation, SQL Reference for “ALTER DATABASE” statement  (as of 18-Jul-2011) – Click Here

flashback_mode_clause

Use this clause to put the database in or take the database out of FLASHBACK mode. You can specify this clause only if the database is in ARCHIVELOG mode and you have already prepared a fast recovery area for the database.You can specify this clause when the database is mounted but not open. This clause cannot be specified on a physical standby database if redo apply is active.

See Also:

Oracle Database Backup and Recovery User’s Guide for information on preparing the fast recovery area for Flashback operations

FLASHBACK ON Use this clause to put the database in FLASHBACK mode. When the database is in FLASHBACK mode, Oracle Database automatically creates and manages Flashback Database logs in the fast recovery area. Users with SYSDBA system privilege can then issue a FLASHBACK DATABASE statement.

FLASHBACK OFF Use this clause to take the database out of FLASHBACK mode. Oracle Database stops logging Flashback data and deletes all existing Flashback Database logs. Any attempt to issue a FLASHBACK DATABASE will fail with an error.

11gR2

$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.1.0 Production on Mon Jul 18 04:56:02 2011

Copyright (c) 1982, 2009, Oracle.  All rights reserved.

Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> select flashback_on, log_mode, open_mode from v$database;

FLASHBACK_ON	   LOG_MODE	OPEN_MODE
------------------ ------------ --------------------
NO		   ARCHIVELOG	READ WRITE

SQL> alter database flashback on;

Database altered.

SQL> select flashback_on, log_mode, open_mode from v$database;

FLASHBACK_ON	   LOG_MODE	OPEN_MODE
------------------ ------------ --------------------
YES		   ARCHIVELOG	READ WRITE

SQL> alter database flashback off;

Database altered.

SQL> select flashback_on, log_mode, open_mode from v$database;

FLASHBACK_ON	   LOG_MODE	OPEN_MODE
------------------ ------------ --------------------
NO		   ARCHIVELOG	READ WRITE

11gR1

$ sqlplus / as sysdba

SQL*Plus: Release 11.1.0.6.0 - Production on Mon Jul 18 05:15:52 2011

Copyright (c) 1982, 2007, Oracle.  All rights reserved.

Connected to:
Oracle Database 11g Enterprise Edition Release 11.1.0.6.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> select flashback_on, log_mode, open_mode from v$database;

FLASHBACK_ON	   LOG_MODE	OPEN_MODE
------------------ ------------ ----------
NO		   ARCHIVELOG	READ WRITE

SQL> alter database flashback on;
alter database flashback on
*
ERROR at line 1:
ORA-38759: Database must be mounted by only one instance and not open.

SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup mount;
ORACLE instance started.

Total System Global Area  217157632 bytes
Fixed Size		    2142976 bytes
Variable Size		  159386880 bytes
Database Buffers	   50331648 bytes
Redo Buffers		    5296128 bytes
Database mounted.
SQL> select flashback_on, log_mode, open_mode from v$database;

FLASHBACK_ON	   LOG_MODE	OPEN_MODE
------------------ ------------ ----------
NO		   ARCHIVELOG	MOUNTED

SQL> alter database flashback on;

Database altered.

SQL> select flashback_on, log_mode, open_mode from v$database;

FLASHBACK_ON	   LOG_MODE	OPEN_MODE
------------------ ------------ ----------
YES		   ARCHIVELOG	MOUNTED

SQL> alter database open;

Database altered.

SQL> select flashback_on, log_mode, open_mode from v$database;

FLASHBACK_ON	   LOG_MODE	OPEN_MODE
------------------ ------------ ----------
YES		   ARCHIVELOG	READ WRITE

SQL> alter database flashback off;

Database altered.

SQL> select flashback_on, log_mode, open_mode from v$database;

FLASHBACK_ON	   LOG_MODE	OPEN_MODE
------------------ ------------ ----------
NO		   ARCHIVELOG	READ WRITE

Posted in 11gR2, Flashback, Oracle | 4 Comments »

Oracle RMAN command prompt hang???

Posted by Vishal Gupta on Jun 22, 2011

This afternoon on a Oracle 11.2.0.2 installation, i was trying to run the Oracle RMAN utility. Rman cli was just hanging and not going into RMAN.

[oracle@linux2 ~]$ . oraenv
ORACLE_SID = [oracle] ? 11202
The Oracle base has been set to /opt/oracle/product/rhel4/database/11.2.0.2
[oracle@linux2 ~]$ rman
 

After entering above rman command, nothing happened. It just hung on the command prompt. I did not receive any error at all. Hmm… whats happening? I issued an CTRL-C to cancel it and tried again. Still same result. Okay lets trace it using Linux strace utility.

oracle@linux2 ~]$ strace rman
execve("/usr/X11R6/bin/rman", ["rman"], [/* 31 vars */]) = 0
uname({sys="Linux", node="linux2.vishalgupta.com", ...}) = 0
brk(0)                                  = 0x5a3000
mmap(NULL, 4096, PROT_READ|PROT_WRITE, MAP_PRIVATE|MAP_ANONYMOUS, -1, 0) = 0x2a95556000
access("/etc/ld.so.preload", R_OK)      = 0
open("/etc/ld.so.preload", O_RDONLY)    = 3
fstat(3, {st_mode=S_IFREG|0444, st_size=23, ...}) = 0
mmap(NULL, 23, PROT_READ|PROT_WRITE, MAP_PRIVATE, 3, 0) = 0x2a95557000
close(3)                                = 0
readlink("/proc/self/exe", "/usr/X11R6/bin/rman", 4096) = 19
open("/stub/lib64/libcwait.so", O_RDONLY) = 3
read(3, "\177ELF\2\1\1\3>\1\20\6"..., 832) = 832
fstat(3, {st_mode=S_IFREG|0755, st_size=4176, ...}) = 0
mmap(NULL, 1051008, PROT_READ|PROT_EXEC, MAP_PRIVATE|MAP_DENYWRITE, 3, 0) = 0x2a95558000
mprotect(0x2a95559000, 1046912, PROT_NONE) = 0
mmap(0x2a95658000, 4096, PROT_READ|PROT_WRITE, MAP_PRIVATE|MAP_FIXED|MAP_DENYWRITE, 3, 0) = 0x2a95658000
close(3)                                = 0
munmap(0x2a95557000, 23)                = 0
open("/opt/oracle/product/rhel4/database/11.2.0.2/home/lib/tls/x86_64/libc.so.6", O_RDONLY) = -1 ENOENT (No such file or directory)
stat("/opt/oracle/product/rhel4/database/11.2.0.2/home/lib/tls/x86_64", 0x7fbfffec30) = -1 ENOENT (No such file or directory)
open("/opt/oracle/product/rhel4/database/11.2.0.2/home/lib/tls/libc.so.6", O_RDONLY) = -1 ENOENT (No such file or directory)
stat("/opt/oracle/product/rhel4/database/11.2.0.2/home/lib/tls", 0x7fbfffec30) = -1 ENOENT (No such file or directory)
open("/opt/oracle/product/rhel4/database/11.2.0.2/home/lib/x86_64/libc.so.6", O_RDONLY) = -1 ENOENT (No such file or directory)
stat("/opt/oracle/product/rhel4/database/11.2.0.2/home/lib/x86_64", 0x7fbfffec30) = -1 ENOENT (No such file or directory)
open("/opt/oracle/product/rhel4/database/11.2.0.2/home/lib/libc.so.6", O_RDONLY) = -1 ENOENT (No such file or directory)
stat("/opt/oracle/product/rhel4/database/11.2.0.2/home/lib", {st_mode=S_IFDIR|0755, st_size=12288, ...}) = 0
open("/etc/ld.so.cache", O_RDONLY)      = 3
fstat(3, {st_mode=S_IFREG|0644, st_size=146311, ...}) = 0
mmap(NULL, 146311, PROT_READ, MAP_PRIVATE, 3, 0) = 0x2a95659000
close(3)                                = 0
open("/lib64/tls/libc.so.6", O_RDONLY)  = 3
read(3, "\177ELF\2\1\1\3>\1p\305\1"..., 832) = 832
fstat(3, {st_mode=S_IFREG|0755, st_size=1636072, ...}) = 0
mmap(NULL, 4096, PROT_READ|PROT_WRITE, MAP_PRIVATE|MAP_ANONYMOUS, -1, 0) = 0x2a9567d000
mmap(NULL, 2330696, PROT_READ|PROT_EXEC, MAP_PRIVATE|MAP_DENYWRITE, 3, 0) = 0x2a9567e000
mprotect(0x2a957ae000, 1085512, PROT_NONE) = 0
mmap(0x2a958ad000, 24576, PROT_READ|PROT_WRITE, MAP_PRIVATE|MAP_FIXED|MAP_DENYWRITE, 3, 0x12f000) = 0x2a958ad000
mmap(0x2a958b3000, 16456, PROT_READ|PROT_WRITE, MAP_PRIVATE|MAP_FIXED|MAP_ANONYMOUS, -1, 0) = 0x2a958b3000
close(3)                                = 0
mmap(NULL, 4096, PROT_READ|PROT_WRITE, MAP_PRIVATE|MAP_ANONYMOUS, -1, 0) = 0x2a958b8000
mprotect(0x2a958ad000, 16384, PROT_READ) = 0
mprotect(0x3e48a14000, 4096, PROT_READ) = 0
arch_prctl(ARCH_SET_FS, 0x2a958b86e0)   = 0
munmap(0x2a95659000, 146311)            = 0
brk(0)                                  = 0x5a3000
brk(0x5c4000)                           = 0x5c4000
fstat(0, {st_mode=S_IFCHR|0620, st_rdev=makedev(136, 1), ...}) = 0
mmap(NULL, 4096, PROT_READ|PROT_WRITE, MAP_PRIVATE|MAP_ANONYMOUS, -1, 0) = 0x2a95659000
read(0,  <unfinished ...>
[oracle@linux2 ~]$

Had to again issue a CTRL-c to come out of trace, as it was just hanging again. I tried connecting to database using sqlplus, that worked fine.

oracle@linux2 ~]$ sqlplus / as sysdba
SQL*Plus: Release 11.2.0.2.0 Production on Mon Jun 20 06:08:58 2011
Copyright (c) 1982, 2010, Oracle.  All rights reserved.

Connected to:

Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL>

Hmm… Lets recompile the Oracle binaries, it case something funny was going on with libraries. And then we will re-run our RMAN cli.

[oracle@linux2 tns_admin]$ relink
writing relink log to: /opt/oracle/product/rhel4/database/11.2.0.2/home/install/relink.log
[oracle@linux2 tns_admin]$ rman

Hmm… still the same.

It turns out the there is Linux RMAN command as well, which is installed by xorg-x11-devel rpm package.

[oracle@linux2 ~]$ which rman
/usr/X11R6/bin/rman
[oracle@linux2 bin]$ man rman
PolyglotMan(1)                                                                                                                                                    PolyglotMan(1)

NAME
       PolyglotMan, rman - reverse compile man pages from formatted form to a number of source formats

So lets run the actual Oracle RMAN command.

[oracle@linux2 bin]$ $ORACLE_HOME/bin/rman

Recovery Manager: Release 11.2.0.2.0 - Production on Mon Jun 20 07:14:58 2011

Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.

RMAN>

So, moral of the story is set your PATH correctly. I hope someone will find it useful, in case their rman command is hung.

 

Posted in Linux, Oracle, RMAN | 13 Comments »

 
%d bloggers like this: