Oracle Database Experts

Excerpt from Introduction to Oracle Recommended Patches [ID 756388.1]
What are Recommended Patches?

Oracle has introduced a set of Recommended Patches which make it easier for customers to obtain and deploy fixes for known critical issues encountered in targeted environments and configurations. As part of Oracle’s overall maintenance strategy, these provide proactive patch recommendations to customers seeking to upgrade or to improve the stability of their current environments. Customers are advised to install Recommended Patches that apply to their environment.

Recommended Patches are available for products listed below. For details, please review the My Oracle Support notes.

Product My Oracle Support Note
Oracle Database Note:756671.1
Oracle Enterprise Manager Note:822485.1
Oracle Fusion Middleware Note:859115.1

This note will be updated as Oracle announces Recommended Patches for other Oracle products.

Posted by Charles Kim
Oracle ACE Director

Here’s what I think is an incredible piece of code to generate SQL*Loader control files for any table in the database.

cat generate_control.sql
def TAB='&1'
set head off feed off pages 0 trims on serveroutput on size 1000000 lines 2000 ver off
var v_tab VARCHAR2(100);

spool &TAB..control
declare
v_col VARCHAR2(255);

cursor c1 is
select column_name, data_type
from dba_tab_columns
where table_name=upper('&TAB');

v_counter NUMBER := 0;

BEGIN
:v_tab := '&TAB';
dbms_output.put_line('load data');
dbms_output.put_line('INFILE '||chr(39)||'data/'||:v_tab||'.txt'||chr(39)||' BADFILE '||chr(39)||:v_tab||'.bad'||chr(39)||' DISCARDFILE '||
                                chr(39)||:v_tab||'.dis'||chr(39));
dbms_output.put_line('INTO TABLE '||:v_tab);
dbms_output.put_line('APPEND');
dbms_output.put_line('FIELDS TERMINATED BY '||chr(39)||'|'||chr(39)||' trailing nullcols ');

FOR r1 in c1 LOOP

IF v_counter = 0 THEN
  IF substr(r1.data_type,0,9) = 'TIMESTAMP' THEN
    v_col := '( '||r1.column_name||' timestamp '||chr(34)||'YYYY-MM-DD HH24:MI:SS.FF'||chr(34);
    dbms_output.put_line(v_col);
  ELSE
    v_col := '( '||r1.column_name;
    dbms_output.put_line(v_col);
  END IF;
else
  IF substr(r1.data_type,0,9) = 'TIMESTAMP' THEN
    v_col := ','||r1.column_name||' timestamp '||chr(34)||'YYYY-MM-DD HH24:MI:SS.FF'||chr(34);
    dbms_output.put_line(v_col);
  ELSE
    v_col := ','||r1.column_name;
    dbms_output.put_line(v_col);
  END IF;
END IF;

-- CREATETIMESTAMP timestamp "YYYY-MM-DD HH24:MI:SS.FF",

v_counter := v_counter + 1;
END LOOP;
dbms_output.put_line(')');

END;
/

spool off
set lines 66

Posted by Charles Kim
Oracle ACE Director

Note:
1. We are running a single node RAC on Red Hat 5 Update 3
2. We are running 11.2.0.1 of the database

[oracle@jin1 ~]$ . oraenv
ORACLE_SID = [dbtools1] ? +ASM1
The Oracle base for ORACLE_HOME=/apps/11.2.0/grid is /apps/oracle
[oracle@jin1 ~]$ srvctl start database -d DBTOOLS
[oracle@jin1 ~]$ . oraenv
ORACLE_SID = [+ASM1] ? DBTOOLS1
The Oracle base for ORACLE_HOME=/apps/oracle/product/11.2.0/dbhome_1 is /apps/oracle
[oracle@jin1 ~]$ emctl start dbconsole
Environment variable ORACLE_UNQNAME not defined. Please set ORACLE_UNQNAME to database unique name.

Interestingly new, you have to set the ORACLE_UNQNAME environment variable to start dbconsole.

[oracle@jin1 ~]$ export ORACLE_UNQNAME=DBTOOLS
[oracle@jin1 ~]$ emctl start dbconsole
Oracle Enterprise Manager 11g Database Control Release 11.2.0.1.0
Copyright (c) 1996, 2009 Oracle Corporation.  All rights reserved.

https://jin1.dbaexpert.com:1158/em/console/aboutApplication

Starting Oracle Enterprise Manager 11g Database Control ............... started.
------------------------------------------------------------------
Logs are generated in directory /apps/oracle/product/11.2.0/dbhome_1/jin1_DBTOOLS/sysman/log

Posted by Charles Kim, Oracle ACE

Software vendors often hide the source code so that their intellectual property and software can not be reverse engineered or stolen. You too can encrypt your source code using the wrap executable from $ORACLE_HOME/bin directory. You can encrypt packages, stored procedures and functions.

wrap iname=dbi_header.sql oname=dbi_header.plb
wrap iname=dbi_body.sql oname=dbi_body.plb

One important information to reveal is that the wrap utility is upwards compatible. For example, if you wrap code with a 10.1 version of the wrap utility, you can load the wrapped file to a 10.2 database. However, the wrapped code is not backwards compatible.

Contrary to what people think, not everything in the source code is wrapped. String literals, number literals, and names of variables/tables/columns remain in plain text of the wrapped file.

Posted by Charles Kim, Oracle ACE

At a minimum, you should apply all the recommended patch requirements from Metalink Doc ID: 437838.1. Wether you are running Oracle Database 10g Release 2 or Oracle Database 11g, following the recommended Streams patches can, and most likely, save you hours and even days of production replication issues.

Subject: Streams Specific Patches – Doc ID: 437838.1
Subject: 10gR2 Streams Recommended Configuration – Doc ID: 418755.1

Here are other Metalink articles of interest:
Note 273674.1 Streams Configuration Report and Health Check Script
Note 238455.1 Streams Supported and Unsupported Datatypes
Note 782541.1 Streams Replication Supplemental Logging Requirements
Note 290605.1 Oracle Streams STRMMON Monitoring Utility
Note 365648.1 Explain TXN_LCR_SPILL_THRESHOLD in Oracle10GR2 Streams
Note 265201.1 Troubleshooting Streams Error ORA-1403 No Data Found
Note 779801.1 Streams Conflict Resolution
Note 461278.1 Example of a Streams Heartbeat Table
Note 789445.1 Streams Setup Scripts – Master Index
Note 313478.1 Performing Manual DDL in a Streams Environment
Note 335516.1 Streams Performance Recommendations
Note 730036.1 Overview for Troubleshooting Streams Performance Issues

Posted by Charles Kim, Oracle ACE

Here’s the old traditional hide.c program that Oracle provided back may moons ago.

ckim: cat hide.c
/*---------------------------------------------------------------------------+
 |     Copyright (c) 1992 Oracle Corporation Belmont, California, USA        |
 |                       All rights reserved                                 |
 +---------------------------------------------------------------------------*/
/*---------------------------------------------------------------------------+
 |   FILENAME                                                                |
 |      hide.c                                                               |
 |   DESCRIPTION                                                             |
 |      Hides arguments for programs on UNIX systems.                        |
 |      Can be used as a program prefix: hide program arguments              |
 |      or as a symbolic link.  If this program is not invoked as hide, it   |
 |      will hide its arguments and invoke the program name.hide             |
 |      The best way to use this is to rename your critical programs to      |
 |      program.hide, and create a symbolic link program to hide.            |
 |      mv sqlplus sqlplus.hide; ln -s hide sqlplus                          |
 |      Thus when sqlplus is invoked, its arguments will be hidden           |
 |   NOTES                                                                   |
 |      This program works by padding 3000 '/' chars in argv[0].  This fools |
 |      all known ps's.  This will reduce the argument capacity of your      |
 |      program by 3000 chars.  A good enhancement would be to reduce the    |
 |      padding if needed so that no arguments are lost - would require a    |
 |      method of determining the max argument size on the system.  Some     |
 |      system's provide the E2BIG error on exec.                            |
 |      There is some performace penalty for using this program, but it is   |
 |      minimal because this program is so small - the biggest cost is the   |
 |      extra exec required to get this program started.                     |
 |   HISTORY                                                                 |
 |       09/15/92  R Brodersen  Created, based on D Beusee's hideargs()      |
 |       09/17/92  D Beusee     Fixed to compile on any system               |
 +---------------------------------------------------------------------------*/

/*
 * $Header: /local/bin/RCS/hide.c,v 1.6 1992/09/22 22:37:17 dbeusee Exp $
 *
 * $Log: hide.c,v $
 * Revision 1.6  1992/09/22  22:37:17  dbeusee
 * Added exit(1) when cannot execvp the program.
 *
 * Revision 1.5  1992/09/22  11:28:44  dbeusee
 * SOme BSD systems have memset(), so add a #define memset MEMSET to fix
 * compilation errors (like on ultrix).
 *
 * Revision 1.4  1992/09/22  06:34:57  dbeusee
 * BSD systems need memset routine.
 *
 * Revision 1.3  1992/09/22  06:05:13  dbeusee
 * Set JUNK_CHAR to ' ' but force last junk char to '/'.  This looks prettier
 * when doing 'ps'.  Also do not show full path of the program.  Also do not
 * show .hide if prog is a symlink to hide.
 *
 * Revision 1.2  1992/09/22  05:52:26  dbeusee
 * If hide could not execvp the program, give an error message.
 * if hide was invoked with a full path (e.g. /usr/local/bin/hide),
 * do not try to invoke PATH/hide.hide.
 *
 *
 */

#include "os.h"
#include <stdio.h>
#ifdef SYS5
#include <string.h>
#else
#include <strings.h>
#define strrchr rindex
#define memset MEMSET /* some BSD systems have a memset() */
char *memset();
#endif
#define JUNK_SIZE 3000
#define JUNK_CHAR ' '

char arg0buf[4096];
char progbuf[4096];
char errbuf[4096];

int main(argc, argv)
int argc;
char *argv[];
{
    char *name, *base;
    int firstarg;

    if (!(name = strrchr(argv[0], '/')))
        name = argv[0];
    else
        name ++; /* get past '/' */

    firstarg = (!strcmp(name, "hide")) ? 1 : 0;

    if (firstarg && (argc == 1))
    {
        fprintf(stderr, "Usage: hide program arguments\n");
        fprintf(stderr, "   ie: hide sqlplus username/password\n");
        fprintf(stderr, "if hide is not named hide, \
it will execute name.hide (useful as a symbolic link)\n");
        exit(1);
    }

    /* Build program name.  If symbolic link mode, use argv[0] || .hide */
    strcpy(progbuf, argv[firstarg]);
    if (!(base = strrchr(argv[firstarg], '/')))
        base = argv[firstarg];
    else
        base ++; /* get past '/' */
    if (!firstarg) strcat(progbuf, ".hide");

    /* Build arg0 buffer.  First, fill it with junk */
    memset((void *)arg0buf, JUNK_CHAR, JUNK_SIZE);
    arg0buf[JUNK_SIZE-1] = '/'; /* set last char to '/' */
    /* Prepend real program name - so ps can see what prog is running */
    strncpy(arg0buf, base, strlen(base));
    /* Append real program name - so prog can see what prog is running */
    strcpy(arg0buf + JUNK_SIZE, argv[firstarg]);
    /* Assign new arg0 buffer to the argv array */
    argv[firstarg] = arg0buf;

    /* Start the new program with the shifted arguments */
    execvp(progbuf, argv + firstarg);

    sprintf(errbuf, "Could not execvp '%s'", progbuf);
    perror(errbuf);
    exit(1);
}

#ifndef SYS5
char *
memset(s, c, n)
        register char *s;
        register c, n;
{
        register char *p = s;

        while (n-- > 0)
                *s++ = c;

        return (p);
}
#endif /* ifndef SYS5 */

For usage instructions, you can peruse this shell script and make your own choice as far as what executable you would like to hide the login information and password:

ckim: cat hide.sh
if [ ! -f $ORACLE_HOME/bin/hide ]; then
  cp $ORACLE_BASE/general/sh/bin/hide $ORACLE_HOME/bin/hide
fi

cd $ORACLE_HOME/bin 

#  Symlink SQL*PLUS
if [ -f sqlplus.hide ]; then
  echo "sqlplus.hide file already exists"
  echo "skipping ..."
else
  mv sqlplus sqlplus.hide
fi

if [ -L sqlplus ]; then
  echo "sqlplus is already symlinked to hide"
  echo "skipping ..."
else
  ln -s hide sqlplus
fi

#  Symlink RMAN executable
if [ -f rman.hide ]; then
  echo "rman.hide file already exists"
  echo "skipping ..."
else
  mv rman rman.hide
fi

if [ -L rman ]; then
  echo "rman is already symlinked to hide"
  echo "skipping ..."
else
  ln -s hide rman
fi

#  Symlink exp executable
if [ -f exp.hide ]; then
  echo "exp.hide file already exists"
  echo "skipping ..."
else
  mv exp exp.hide
fi

if [ -L exp ]; then
  echo "exp is already symlinked to hide"
  echo "skipping ..."
else
  ln -s hide exp
fi

#  Symlink imp executable
if [ -f imp.hide ]; then
  echo "imp.hide file already exists"
  echo "skipping ..."
else
  mv imp imp.hide
fi

if [ -L imp ]; then
  echo "imp is already symlinked to hide"
  echo "skipping ..."
else
  ln -s hide imp
fi

#  Symlink sqlldr executable
if [ -f sqlldr.hide ]; then
  echo "sqlldr.hide file already exists"
  echo "skipping ..."
else
  mv sqlldr sqlldr.hide
fi

if [ -L sqlldr ]; then
  echo "sqlldr is already symlinked to hide"
  echo "skipping ..."
else
  ln -s hide sqlldr
fi

chmod 755 hide

You can calculate the difference between executions to determine the I/Os per second on your database environment.

--
-- get_iops.sql
--
set linesize 100
set head off
select to_char(sysdate, 'dd-mon-rr hh24:mi:ss') from dual;

SELECT   'Number of Small Reads :'
         || SUM(DECODE (name, 'physical read total IO requests', VALUE, 0)
                - DECODE (name,
                          'physical read total multi block requests', VALUE,
                          0)),
         'Number of Small Writes:'
         || SUM(DECODE (name, 'physical write total IO requests', VALUE, 0)
                - DECODE (name,
                          'physical write total multi block requests', VALUE,
                          0)),
         'Number of Large Reads :'
         || SUM(DECODE (name,
                        'physical read total multi block requests', VALUE,
                        0)),
         'Number of Large Writes:'
         || SUM(DECODE (name,
                        'physical write total multi block requests', VALUE,
                        0)),
         'Total Bytes Read      :'
         || SUM (DECODE (name, 'physical read total bytes', VALUE, 0)),
         'Total Bytes Written   :'
         || SUM (DECODE (name, 'physical write total bytes', VALUE, 0))
  FROM   v$sysstat
/

You can use SQL to generate SQL for DDL for database objects. Here’s another alternative using the DBMS_METADATA package:

----------------------------------------------------------------------------------------------
--
SELECT dbms_metadata.get_dependent_ddl('OBJECT_GRANT', 'ORDERS', 'OE') from dual;
SELECT  DBMS_METADATA.GET_DEPENDENT_DDL('TRIGGER','<table_name>','<schema>') from dual;

----------------------------------------------------------------------------------------------
--
set pagesize 0
set long 90000
set feedback off
set echo off 

spool schema.sql 

SELECT DBMS_METADATA.GET_DDL('TABLE',u.table_name)
     FROM USER_TABLES u;

SELECT DBMS_METADATA.GET_DDL('INDEX',u.index_name)
     FROM USER_INDEXES u;

spool off;

----------------------------------------------------------------------------------------------
--
SELECT dbms_metadata.get_dependent_xml('OBJECT_GRANT', 'ORDERS', 'OE') from dual;

----------------------------------------------------------------------------------------------
--  View system grants for a user
SELECT dbms_metadata.get_granted_ddl('SYSTEM_GRANT', 'OE_USER') FROM dual

----------------------------------------------------------------------------------------------
--
SELECT dbms_metadata.get_granted_ddl('ROLE_GRANT', 'OE') from dual;

SELECT dbms_metadata.get_ddl('VIEW', 'MY_TABLES')
FROM dual;

Function
CREATE OR REPLACE FUNCTION whoami RETURN VARCHAR2 IS

BEGIN
   RETURN user;
END whoami;
/

SELECT dbms_metadata.get_ddl('FUNCTION', 'WHOAMI')
FROM dual; 

# --------------------------------------------------------------------------------------------
# For maximum flexibility, extract object definitions for tables, indexes, and constraints
exec dbms_metadata.set_transform_param( dbms_metadata.session_transform, 'PRETTY', true);

exec dbms_metadata.set_transform_param( dbms_metadata.session_transform, 'SEGMENT_ATTRIBUTES', true);
exec dbms_metadata.set_transform_param( dbms_metadata.session_transform, 'STORAGE', true);
exec dbms_metadata.set_transform_param( dbms_metadata.session_transform, 'TABLESPACE', true);

exec dbms_metadata.set_transform_param( dbms_metadata.session_transform, 'CONSTRAINTS', true);
exec dbms_metadata.set_transform_param( dbms_metadata.session_transform, 'REF_CONSTRAINTS', true);
exec dbms_metadata.set_transform_param( dbms_metadata.session_transform, 'CONSTRAINTS_AS_ALTER', true);

Posted by Charles Kim, Oracle ACE

First, create a new temporary tablespace and set the new temp tablespace at the database level:

create temporary tablespace temp2 tempfile '+data';
Tablespace created.
alter database default temporary tablespace temp2;
Database altered.

Drop the original temp tablespace:

drop tablespace temp;
Tablespace dropped.

Create the new temp tablespace and set the new default temporary tablespace at the database level:

create temporary tablespace temp tempfile '+data' size 1g;<br />
<br />
Tablespace created.</p>
alter database default temporary tablespace temp;<br />
<br />
Database altered.

Posted by Charles Kim, Oracle ACE

Large database in today’s market have reached up to 200TB.  Large databases are tripling every two years.  Disk storage that exist today cannot cope with the data bandwidth.  Storage subsystem can house the data but run into the data bandwidth problems.  At about 1TB is where the problem raises it’s ugly head and the problem gets worse and worse. 

Oracle is annoucing their first ever hardware product – The HP Oracle Exadata Programmable Storage Server with 2 Intel Processors with 4 cores each (8 cores) and allows oracle to build intelligence for the disk subsystem.  In two months, it will be 2 processes with 6 cores each (12 cores).  The Storage Server runs Parallel Query Server and reduces data going through the pipes.  Instead of packets being served (all the disk blocks), the actual query results are passed to the database servers.  Exadata Storage Servers are query processing servers.  The Exadata Storage Servers can be a grid of storage servers. 

Brawnier Hardware are two infiband pipes per storage server to allow transfer of 1 Gigabyte per second.  Each Exabyte is 20 gigabits thus providing wider pipes and more of them. 

The HP Oracle Exadata Storage Server Grid is available immediately for Linux Servers.

Larry also announced the HP Oracle Databas Machine !!!!  It doesn’t make sense to just provide just the storage.
The world’s fastest database machine.

64 cores for DB
112 cores for exadata
168 TB of disk data
14GB/sec data bandwidth
Using the proliant technology

Customer testing program began on Oct 2007.  This has been a three year development programme.  10-72 times speed up reported by an European telecommunication system (M-Tel).  From 2 IBM p570’s on EMC CX3-40 storage reported 28 X average performance with just half of the exabyte.

LGR outsources DW.  30 minute query
HP Superdome on Hitachie XP24000

10-15 times CME Group

Giant Eagle Exadata speedup:13 IBM 570 CPU on EMC clarion and DMX Storage Array (16 times average performance increases)
NetApp Storage for Oracle Primary Financial DW – 10.7 queries/Hour to 337 queries/Hour on TPCH query set (30X speed up)

Problem:  move disk blocks to the database servers.  The arrays typically have low bandwidth.

Oracle DB Machine
1000GB/Hour
20GB/Sec with Infiniband
Open Standard
Fault tolerant
Disk Failure
100 X 1TB disks
368GB of RAM

System Price:  650k
Price per TB:  4K
Software license:  1,680,000
Designed to speed up OLTP as well as DW – 10-50 X faster

Trace SQL statements with bind variables
> oradebug setospid 10121
Oracle pid: 91, Unix process pid: 10121, image: oracleorcl
> oradebug EVENT 10046 trace name context forever, level 12
Statement processed.
> ! vi /app/oracle/admin/orcl/bdump/ora_10121.trc

Trace Process Statistics
> oradebug setorapid 2
Unix process pid: 1436, image: ora_pmon_orcl
> oradebug procstat
Statement processed.
> oradebug TRACEFILE_NAME
/app/oracle/admin/orcl/bdump/pmon_1436.trc

List semaphores and shared memory segments in use
> oradebug ipc

Dump Error Stack
> oradebug setospid <pid>
> oradebug event immediate trace name errorstack level 3

exec dbms_system.set_bool_param_in_session(8,5,’timed_statistics’,true);

PL/SQL procedure successfully completed.

SQL> exec dbms_system.set_int_param_in_session(8,5,’max_dump_file_size’,10000000);

PL/SQL procedure successfully completed.

SQL> execute sys.dbms_system.set_ev(8,5,10046,8, ‘ ‘);

PL/SQL procedure successfully completed.

To Turn Off Tracing:

******* run all of your processing here *******

SQL> execute sys.dbms_system.set_ev(&&SID, &&SERIAL, 10046, 0, ‘ ‘);

— disables extended SQL tracing.

Another alternative:

The DBMS_SUPPORT package is preferred and fully supported by Oracle but the above DBMS_SYSTEM works just fine. You will have to install this package and it is not available on all platforms. To install run the dbmssupp.sql script as sysdba located in $ORACLE_HOME/rdbms/admin directory.

SQL> execute sys.dbms_support.start_trace_in_session(&&SID, &&SERIAL, waits=>true, binds=>false);

— equivalent to level 8 tracing, bind=>true would be equivalent to level 12 tracing.

******* run all of your processing here *******

SQL> execute sys.dbms_support.stop_trace_in_session(&&SID, &&SERIAL); — end tracing.

Set you local session:

alter session set events ‘10046 trace name context forever, level 12′;