Vishal Gupta's Blog

Archive for the ‘Oracle’ Category

Oracle related blogs

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 »

Moving spfile between ASM and filesystem

Posted by Vishal Gupta on Mar 11, 2011

Normally in RAC, database instance spfile file is stored on ASM so that parameters remain consistent across the instance and all instance use the same spfile.

For this sometimes init.ora has following parameter

# cat $ORACLE_HOME/dbs/init<ORACLE_SID>.ora
SPFILE='+DATA/ORACLESID/spfileORACLESID.ora'
 

Now if you want to move spfile between ASM and filesystem, here how you can do it.

 

From ASM -> Filesystem

</span>
<pre>SQL> CREATE pfile='/tmp/initORACLESID.ora' from spfile='+DATA/ORACLESID/spfileORACLESID.ora';
SQL> CREATE SPFILE='/tmp/spfileORACLESID.ora' from pfile='/tmp/initORACLESID.ora';</pre>
<span style="font-family: Times New Roman; font-size: small;"> 

From Filesystem -> ASM

</span>
<pre>SQL> CREATE pfile='/tmp/initORACLESID.ora' from spfile='$ORACLE_HOME/dbs/spfileORACLESID.ora';
SQL> CREATE SPFILE='+DATA/ORACLESID/spfileORACLESID.ora' from pfile='/tmp/initORACLESID.ora';</pre>

Posted in Oracle | Leave a Comment »

V$SQL_HINT

Posted by Vishal Gupta on May 2, 2009

In case you wanted to know which Oracle version a particular SQL hint is applicable in or was introduced in. You can query V$SQL_HINT introduced in 11g for that. It holds even historical information. Column “version” gives oracle version in which a particular hint was introduced and version_outline probably gives upto which version upto which it is applicable.

[Update:2009-05-03 : Jonathan Lewis mentioned in a email to me that version_outline gives version in which a particular hint can be used in an outline. ]

 

This even has a column to give you inverse of an hint.

 

This view is a undocumented view.

 

SQL> desc v$sql_hint
Name                                      Null?    Type
----------------------------------------- -------- ----------------------------
NAME                                               VARCHAR2(64)
SQL_FEATURE                                        VARCHAR2(64)
CLASS                                              VARCHAR2(64)
INVERSE                                            VARCHAR2(64)
TARGET_LEVEL                                       NUMBER
PROPERTY                                           NUMBER
VERSION                                            VARCHAR2(25)
VERSION_OUTLINE                                    VARCHAR2(25)

Posted in Oracle | Leave a Comment »

Log File Write And Waits

Posted by Vishal Gupta on Dec 2, 2008

Here are some interesting articles regarding log file writing and associated I/O waits.

Jonanthan Lewis – Log File Write

Riyaj Shamsudeen – Log file sync tuning

Christian Bilien – Log file sync wait

Posted in Oracle, Performance | Leave a Comment »

 
%d bloggers like this: