Public Yum Server

Configure your server for Yum (Yellowdog Updater Modified):

There’s no more need to look for CDs from your pile of CDs or from the clutter of your desk. Earlier in 2009, Oracle launched their public yum server which offers a free and convenient way to install packages from the Enterprise Linux and Oracle VM. Obviously, the Yum server if offered without support.

We will show you how to setup your Red Hat or Oracle Enterprise Linux to use Oracle’s public Yum repository. In this example, we only focus on 64-bit Red Hat 5 Update 3.

[root@rac01 ~]# cd /etc/yum.repos.d/
[root@rac01 yum.repos.d]# ls -ltr
total 0
[root@rac01 yum.repos.d]# wget http://public-yum.oracle.com/public-yum-el5.repo
--11:27:04--  http://public-yum.oracle.com/public-yum-el5.repo
Resolving public-yum.oracle.com... 141.146.44.34
Connecting to public-yum.oracle.com|141.146.44.34|:80... connected.
HTTP request sent, awaiting response... 200 OK
Length: 1402 (1.4K) [text/plain]
Saving to: `public-yum-el5.repo'

100%[=========================================================>] 1,402       --.-K/s   in 0s     

11:27:04 (83.6 MB/s) - `public-yum-el5.repo' saved [1402/1402]

Edit the file public-yum-el5.repo with your choice of editor such as vi. Change enabled=0 to enabled=1 for the following sections since we are on RH5 U3

Portions of your yum.repos.d file should look something like this:

[el5_u3_base]
name=Enterprise Linux $releasever U3 - $basearch - base
baseurl=http://public-yum.oracle.com/repo/EnterpriseLinux/EL5/3/base/$basearch/
gpgkey=http://public-yum.oracle.com/RPM-GPG-KEY-oracle-el5
gpgcheck=1
enabled=1

[el5_addons]
name=Enterprise Linux $releasever - $basearch - addons
baseurl=http://public-yum.oracle.com/repo/EnterpriseLinux/EL5/addons/$basearch/
gpgkey=http://public-yum.oracle.com/RPM-GPG-KEY-oracle-el5
gpgcheck=1
enabled=1

[el5_oracle_addons]
name=Enterprise Linux $releasever - $basearch - oracle_addons
baseurl=http://public-yum.oracle.com/repo/EnterpriseLinux/EL5/oracle_addons/$basearch/
gpgkey=http://public-yum.oracle.com/RPM-GPG-KEY-oracle-el5
gpgcheck=1
enabled=1

Install missing RPM needed to install Oracle

Here’s an example of downloading the compat-db RPM and installing the package

[root@rac01 yum.repos.d]# yum install compat-db
Loaded plugins: security
Setting up Install Process
Parsing package install arguments
Package compat-db-4.2.52-5.1.x86_64 already installed and latest version
Resolving Dependencies
--> Running transaction check
---> Package compat-db.i386 0:4.2.52-5.1 set to be updated
--> Finished Dependency Resolution

Dependencies Resolved

==================================================================================================
 Package                Arch              Version                  Repository                Size
==================================================================================================
Installing:
 compat-db              i386              4.2.52-5.1               el5_u3_base              1.7 M

Transaction Summary
==================================================================================================
Install      1 Package(s)
Update       0 Package(s)
Remove       0 Package(s)         

Total download size: 1.7 M
Is this ok [y/N]: y
Downloading Packages:
compat-db-4.2.52-5.1.i386.rpm                                              | 1.7 MB     00:01
Running rpm_check_debug
Running Transaction Test
Finished Transaction Test
Transaction Test Succeeded
Running Transaction
  Installing     : compat-db                                         [1/1] 

Installed: compat-db.i386 0:4.2.52-5.1
Complete!

Posted by Charles Kim, Oracle ACE Director

Viscosity Technology Tags:

Posted January 5, 2012 by admin in Uncategorized

Generate SQL*Loader (sqlldr) Control File Script

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

Posted November 6, 2011 by admin in Uncategorized

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

Posted November 6, 2011 by admin in Linux

Tagged with

Basic RAC configuration

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 November 5, 2011 by Charles.Kim in Uncategorized

Calibrate I/O

cat io_callibrate.sql
set serveroutput on size unlimited
set time on timing on echo on

declare
max_iops integer;
max_mbps integer;
actual_latency integer;
begin
dbms_resource_manager.calibrate_io (
num_physical_disks => 48,
max_latency => 10,
max_iops => max_iops,
max_mbps => max_mbps,
actual_latency => actual_latency);
dbms_output.put_line (‘IOPS = ‘|| max_iops);
dbms_output.put_line (‘MBPS = ‘|| max_mbps);
dbms_output.put_line (‘Latency = ‘|| actual_latency);
end;
/

Notes:
1. num_physical_disks is not the number of LUNs. It is the number of disks the LUNs are comprised of.
2. The max_latecncy is

SQL> exec print_table(‘ select inst_id, status, calibration_time from gv$io_calibration_status’);
INST_ID : 1
STATUS : IN PROGRESS
CALIBRATION_TIME :

Eventually, the status from change to a READY state. Now, you can query the DBA_RSRC_IO_CALIBRATE view:
exec print_table(‘select * from dba_rsrc_io_calibrate’);

Posted September 3, 2011 by admin in Uncategorized

Mass Deployment Agent Installation

$ cat agent_down.ksh
export AGENT_INSTALL_PASSWORD=${AGENT_PASSWORD}
./agentDownload.linux_x64 -b /apps/oracle/product -m rac01.dbaexpert.com -r 4900 -y

Download the agentDownload.linux_x64 script from Grid Control Server to each of the clients.

Leveraging the mass deployment agent installation method, you can deploy Oracle Intelligent Agents in matter of minutes. This script will basically download the product jar file and install the agent in the $ORACLE_BASE/product/agent11g directory.

Posted by Charles Kim, Oracle ACE Director

Posted July 22, 2011 by Charles.Kim in New Category

Partition Alignment with sfdisk   1 comment

# –
# — Partition alignment of OCR / Vote Disks with 1MB offset
echo “2048,,” | sfdisk -uS /dev/emcpowera
echo “2048,,” | sfdisk -uS /dev/emcpowerb
echo “2048,,” | sfdisk -uS /dev/emcpowerc

# –
# — Partition alignment of Data / FRA disks with 4MB offset
echo “8192,,” | sfdisk -uS /dev/emcpowerd
echo “8192,,” | sfdisk -uS /dev/emcpowere
echo “8192,,” | sfdisk -uS /dev/emcpowerf
echo “8192,,” | sfdisk -uS /dev/emcpowerg
echo “8192,,” | sfdisk -uS /dev/emcpowerh

Posted by Charles Kim, Oracle ACE Director

Posted July 18, 2011 by Charles.Kim in Uncategorized

Tagged with

Check for space on your ASM instance   Leave a comment

Here’s a simple script to see how much space that you have at the disk and disk group level. This script should work on all the operating systems but only tested on Linux. The only portion that you will have to change is the ‘ps -ef’ line.

export DB=$(ps -ef |grep +ASM |grep -i pmon |awk {'print $8'} |sed -e 's/asm_pmon_//g')
export ORACLE_SID=${DB}
export ORAENV_ASK=NO
. oraenv
sqlplus -s / as sysasm <<!!
col name for a15
col path for a20
set lines 122 pages 66
col AU for 9 hea 'AU|MB'
col state for a12
col compatibility for a10 hea 'ASM|Compat'
col database_compatibility for a10 hea 'Database|Compat'
col pct_Free for 99.99 head 'Pct|Free'
col block_size for 99,999 head 'Block|Size'
col Total_GB for 999,999.99 head 'Total|GB'
col Free_GB for 999,999.99 head 'Free|GB'

col pct_free for 999 hea 'Pct|Free'
select name, path, total_mb, free_mb,
       round(free_mb/total_mb*100,2) pct_Free
from v\$asm_disk
where total_mb >1
order by name;

select name, state, round(total_mb/1024,2) Total_GB, round(free_mb/1024,2) Free_GB,
       round(free_mb/total_mb*100,2) pct_Free,
       allocation_unit_size/1024/1024 AU, compatibility, database_compatibility
from v\$asm_diskgroup
where total_mb > 1;
!!

Posted by Charles Kim, Oracle ACE Director

Posted July 17, 2011 by Charles.Kim in ASM, Linux

ASM Sector Size for disk groups   Leave a comment

Being able to specify a sector size larger than the default 512 bytes is another ASM feature of Oracle Databases 11g Release 2. Now, we can specify a sector size up to 4k. There is a new SECTOR_SIZE attribute while create the ASM disk group.

ASM Cluster File System (Oracle ACFS) does not support 4 KB sector drives.
There’s performance implications for Oracle ACFS when utilizing 4 KB sector disk drives in 512 sector emulation mode.

Posted by Charles Kim, Oracle ACE Director

Posted July 17, 2011 by Charles.Kim in ASM

Ways to mount and dismount disk groups in Oracle Database 11g Release 2   Leave a comment

Prior to Oracle Database 11g Release 2, you had to issue the following commands in sqlplus to mount and dismount a diskgroup:

SQL> alter diskgroup fra mount;

Diskgroup altered.

SQL> alter diskgroup fra dismount;

Diskgroup altered.

As of Oracle Database 11g Release 2, an easier and more SA friendly approach is provided with asmcmd:

ASMCMD> mount fra

Looking at the disk group information with the lsdg command, we see that the fra disk group is now mounted:

ASMCMD> lsdg
State    Type    Rebal  Sector  Block       AU  Total_MB  Free_MB  Req_mir_free_MB  Usable_file_MB  Offline_disks  Voting_files  Name
MOUNTED  EXTERN  N         512   4096  1048576     15640     5352                0            5352              0             N  DATA/
MOUNTED  EXTERN  N         512   4096  1048576     45792    45736                0           45736              0             N  DG_EXTERNAL/
MOUNTED  EXTERN  N         512   4096  1048576     42068    42016                0           42016              0             N  FRA/

Let’s issue the umount command in asmcmd. Notice that the syntax is umount in asmcmd and dismount in SQL*Plus:

ASMCMD> umount fra

Looking at the disk group information with the lsdg command, we see that the fra disk group is no longer mounted:

ASMCMD> lsdg
State    Type    Rebal  Sector  Block       AU  Total_MB  Free_MB  Req_mir_free_MB  Usable_file_MB  Offline_disks  Voting_files  Name
MOUNTED  EXTERN  N         512   4096  1048576     15640     5352                0            5352              0             N  DATA/
MOUNTED  EXTERN  N         512   4096  1048576     45792    45736                0           45736              0             N  DG_EXTERNAL/

Posted by Charles Kim, Oracle ACE

Posted July 17, 2011 by Charles.Kim in ASM