Vishal Gupta's Blog

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.

 

 

Advertisements

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 »

Exadata Model Comparison

Posted by Vishal Gupta on Jun 19, 2011

Many times i find myself comparing various Oracle Exadata models. It becomes tedious to compare various data sheets for various Exadata models. So i have put together this spreadsheet (Exadata Model Comparison) for comparing various models in one glance.

It gives following details and more.

  • At Exadata level,
    • number/model of compute nodes and cell nodes.
    • number of Infiniband switches and their port count.
    • Key capabilities ( as per data sheets)
  • For each database compute node and cell node.
    • Server Model
    • CPU Type, CPU Speed, CPU Cores, CPU Threads
    • RAM
    • Network ports – type and count
    • Flash RAM (Smart Cache)
    • Disk types

I hope someone will find it useful.

Exadata Model Comparison Spreadsheet – link

Posted in Oracle, Oracle Exadata | 5 Comments »

Predefined Oracle System Schemas

Posted by Vishal Gupta on Jun 19, 2011

If you have ever wondered what are the various predefined internal Oracle system schemas, you can find the list of such accounts and their use in Oracle® Database 2 Day + Security Guide
11g Release 2 (11.2)
at this link. This list comes in handy, when you want to do some analysis on database objects, but want to exclude all internal Oracle provided system accounts and want to focus only on application level accounts.

Here is the list of schemas:-

ANONYMOUS
APEX_PUBLIC_USER
CTXSYS
DBSNMP
DIP
EXFSYS
FLOWS_%
FLOWS_FILES
LBACSYS
MDDATA
MDSYS
MGMT_VIEW
OLAPSYS
ORACLE_OCM
ORDDATA
ORDPLUGINS
ORDSYS
OUTLN
OWBSYS
SI_INFORMTN_SCHEMA
SPATIAL_CSW_ADMIN_USR
SPATIAL_WFS_ADMIN_USR
SYS
SYSMAN
SYSTEM
WKPROXY
WKSYS
WK_TEST
WMSYS
XDB
XS$NULL

Posted in Oracle | Leave a Comment »

 
%d bloggers like this: