Oracle Database Experts

By Charles Kim

August 29th, 2009

Wrapping PL/SQL to hide the source

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

August 28th, 2009

Oracle Streams – Critical Metalink Articles

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

August 27th, 2009

Hide C Program

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
August 17th, 2009
August 15th, 2009

Calculate IOPs

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
/
August 2nd, 2009

RMAN Delete Archive Logs Older than 2 days

Here’s the syntax to to delete all archive logs but only keep a day or two of archive logs.

delete noprompt archivelog until time 'sysdate - 2' backed up 2 times to device type disk;

Posted by Charles Kim, Oracle ACE

|