Disable DRM in 11g Release 1

alter system set “_gc_undo_affinity”=FALSE scope=spfile sid=’*’;
alter system set “_gc_policy_time”=0 scope=spfile sid=’*’;

In 10g
alter system set “_gc_undo_affinity”=FALSE scope=spfile sid=’*’;

alter system set “_gc_affinity_time”=0 scope=spfile sid=’*’;

Posted in RAC

Create a golden image of your database and create a template of that database for future database provisioning

Create Seeded Database with dbca

With proper planning and infrastructure, you should be able to provision a new database in matter of minutes rather than days.  You can create a template of a golden image of your database with all the corporate standards, auditing requirements and security compliance components.


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

</p>
<p>cat generate_control.sql def TAB='&amp;1' set head off feed off pages 0 trims on serveroutput on size 1000000 lines 2000 ver off var v_tab VARCHAR2(100);</p>
<p>spool &amp;TAB..control declare v_col VARCHAR2(255);</p>
<p>cursor c1 is select column_name, data_type from dba_tab_columns where table_name=upper('&amp;TAB');</p>
<p>v_counter NUMBER := 0;</p>
<p>BEGIN :v_tab := '&amp;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 ');</p>
<p>FOR r1 in c1 LOOP</p>
<p>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;</p>
<p>-- CREATETIMESTAMP timestamp "YYYY-MM-DD HH24:MI:SS.FF",</p>
<p>v_counter := v_counter + 1; END LOOP; dbms_output.put_line(')');</p>
<p>END; /</p>
<p>spool off set lines 66</p>
<p>

Posted by Charles Kim Oracle ACE Director


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

Oracle Real Application Cluster (RAC) is the successor to Oracle Parallel Server (OPS) and allows multiple instances to access the same database (storage) accessing and modifying the same data at the same time.

RAC provides fault tolerance, load balancing, and performance benefits by allowing the system to scale out, and at the same time since all nodes access the same database, the failure of one instance will not cause the loss of access to the database.

At the heart of Oracle10g RAC is the OCR and Voting Disk. All the nodes in the cluster must be able to access all of the data, redo log files, control files and spfile. At each instance level exist redo log file(s) and UNDO tablespace. All the instances in the RAC cluster must be able to access the controlfiles, redo logs and UNDO tablespaces to recover a node in the event of a system failure.

As of Oracle Database 10g RAC, data files, redo log files, control files, and archivelog files can reside on shared storage. Shared storage can be on raw-disk devices, NAS, ASM, or a clustered file system. At a minimum, a 1GB bandwidth for Oracle Interconnect and public network is required to successfully implement RAC. Typically, the networks cards (NICs) are aggregated to provide 2-4GBit bandwidth. Likewise, the network bandwidth for the public network is often aggregated to provide 2-4GBit bandwidth. With technologies such as Infiniband, the bandwidth can be amplified upto 10Gbit per second.

We recommend having at a minimum: 3 voting disks and mirroring the OCR. The voting disks must have at least 50% survivability for the RAC cluster to stay up. For example, if you have two voting disks and lose one of the two voting disks, you will only have 50% of the voting disks. In this particular case, the entire RAC cluster will crash.

Posted by: Charles Kim @ DBAExpert.com

Posted in RAC