Archive

Archive for July, 2011

oracle data integrator 11g installation — linux

July 14, 2011 Leave a comment

This article walks you through the steps to install oracle data integrator 11g

Os : Red Hat Enterprise Linux Server release 5.4 (Tikanga)

[root@odil8001 logs]# uname -r
2.6.18-164.el5

Prerequisites:

1)Have installed Oracle Database 11g. If not done before, start the services and components for Oracle Database 11g

2)Install Oracle WebLogic Server 11gR1 (10.3.5)

Download wls1035_generic.jar to install on 64 bit linux.

-rw-r–r–  1 oracle oinstall 1122139308 Jul 13 20:32 wls1035_generic.jar
$ export DISPLAY=192.168.2.154:0.0
$ /u01/jdk1.6.0_18/bin/java -d64 -jar wls1035_generic.jar
Extracting 0%……………………………………………………………………………………….100%

3)Down load repository creation utility and start creating repository for ODI 11g installation:

/u01/rcuHome/bin/rcu

4)Install odi 11g

$ ./runInstaller
Starting Oracle Universal Installer…

Checking if CPU speed is above 300 MHz.    Actual 3200 MHz    Passed
Checking Temp space: must be greater than 300 MB.   Actual 6380 MB    Passed
Checking swap space: must be greater than 512 MB.   Actual 2559 MB    Passed
Checking monitor: must be configured to display at least 256 colors.    Actual 16777216    Passed
Preparing to launch Oracle Universal Installer from /tmp/OraInstall2011-07-13_09-05-42PM. Please wait …
Please specify JDK location ( Ex. /home/jdk ), <location>/bin/java should exist :/u01/jdk1.6.0_18

Starting odi console:

/u01/app/oracle/Middleware/Oracle_ODI1/oracledi/client/$ ./odi.sh

Oracle Data Integrator 11g
Copyright (c) 1997, 2011, Oracle and/or its affiliates. All rights reserved.

Deploying and Configuring the ODI Agent as a Java EE Application :
$ pwd
/u01/app/oracle/Middleware/Oracle_ODI1/common/bin
$ ./config.sh

Deploying and Configuring the ODI Agent as a Java EE Application


In the Select Domain Source screen, just click Next.

$ pwd
/u01/app/oracle/Middleware/Oracle_ODI1/oracledi/client
$ ./odi.sh

Oracle Data Integrator 11g
Copyright (c) 1997, 2011, Oracle and/or its affiliates. All rights reserved

In the Logical architecture, create a new logical agent with the same name as your new physical agent

$ pwd
/u01/app/oracle/Middleware/Oracle_ODI1/common/bin

$ pwd
/u01/app/oracle/Middleware/user_projects/domains/odi_domain
$ ./startWebLogic.sh

Advertisements
Categories: ODI

script to startup single instance oracle database

July 11, 2011 Leave a comment

#!/bin/ksh
#——————————————————————
# name : oradbstart.ksh
# This script is used to shudown oracle database
# Arguments $1 = ORACLE_SID(Required)
# Argument $2 = open,mount(optional)
# If argument 2 is not specified then default open
# usage: orashutdb.ksh <ora sid> [<startup type>]
#       Created By : Uma M Katru
#       Date : 07-08-2010
#——————————————————————-
dbstartUsage()
{
printf “\nError: $Me – incorrect number of arguments!\n\n”
printf “Usage: $Me <ora sid> [open | mount ].\n\n”
printf “where:\n\n”
printf ”  <ora sid>   : is the unique oracle instance identifier to shutdown non rac and rac instance on the local node required.\n”
printf ”   [<shutdown type>]      : startup type (Valid start up arguments are:  open,mount.\n”
printf “examples:\n”
printf ”  $Me oradv1  — startup database and open\n”
printf ”  $Me oradv1 mount — Starting an Instance and Mounting a Database\n”
exit 1
}
#—————————————————————————-
#Check init or file for the given database instance
#—————————————————————————-
dbstartCheckinitora(){
#check spfile first
spfile=$ORACLE_HOME/dbs/spfile$ORACLE_SID.ora
spfileexists=NO
fileexists=NO
pfile=$ORACLE_HOME/dbs/init$ORACLE_SID.ora
if [[ -f $spfile ]];then
spfileexists=YES
elif [[ -f $pfile ]];then
pfileexists=YES
else
echo “neither spfile nor pfile exists to start $ORACLE_SID database instance”
echo “Please check the issue and restart the process”
upLog “$Me aborted @$(\date)”
exit 1
fi
}
#############################################################################
# write log entries
# $1 is the actual message to log
# —————————————————————————–
upLog()
{
\echo “$1”
}
# —————————————————————————–
# main
#—————————————————————————–
Me=$(\basename $0)
mypath=$(\dirname $0)
export ORACLE_BASE=/u01/app/oracle
#set -vx

args=$#
if [[ $args = 1 ]]; then
ORACLE_SID=$1
STARTMODE=open
elif [[ $args = 2 ]];then
ORACLE_SID=$1
STARTMODE=$2
else
dbstartUsage
fi

#check sid exists in oratab
sidexists=$(grep -i “^$ORACLE_SID:” /etc/oratab |wc -l | awk ‘{print $1}’)
if [[ $sidexists -eq 1 ]];then
echo
ORACLE_HOME=$(grep -w ^”$ORACLE_SID” /etc/oratab | awk -F”:” ‘{print $2}’)
else
echo “oracle instance is not available  on this machine”
exit 1
fi
#Check spfile exists
export ORACLE_SID=$ORACLE_SID
export ORACLE_HOME=$ORACLE_HOME
upLog “$Me started @$(\date)”
upLog “ORACLE_BASE ………………:$ORACLE_BASE”
upLog “ORACLE_HOME ………………:$ORACLE_HOME”
upLog “DB_NAME ………………….:$ORACLE_SID”
dbstartCheckinitora

#Check if db is running or not
ChekDBRunning=`ps -ef | grep -v grep | grep ora_.*_${ORACLE_SID} | wc -l`
if [ $ChekDBRunning -lt 1 ]; then
echo “Info: Database $ORACLE_SID is not up,we can safely start the database .”
else
echo “$ORACLE_SID is already up and running,we can startup shutdown databases only”
exit 0
fi
LogDir=/home/oracle/admin/logs/$ORACLE_SID
if [[ ! -d /home/oracle/admin/logs/$ORACLE_SID ]];then
mkdir -p /home/oracle/admin/logs/$ORACLE_SID
fi
logFile=$LogDir/$(\echo $Me | \awk -F. ‘{print $1}’)$ORACLE_SID.blog.$(\date +%Y%m%d)

echo “Starting up $ORACLE_SID … Please wait…”
$ORACLE_HOME/bin/sqlplus “/ as sysdba”  << ! >> $logFile
startup $STARTMODE
exit
!
if [[ $rc -eq 0 ]] ; then
echo “”
echo “$Me: Database $ORACLE_SID started successfully.”
echo “$Me: Check $logFile logfile for complete details”
upLog “$Me finished @$(\date)”
echo “”
else
echo “”
echo “$Me: unable to startup $ORACLE_SID database instance,Please see log file for details.”
echo “$Me: Check $logFile logfile for complete details”
upLog “$Me failed to start $ORACLE_SID @$(\date)”
exit 1
fi
echo “—————————————————————-” >> $logFile
exit 0

 

Error: orastartdb.ksh – incorrect number of arguments!

Usage: orastartdb.ksh <ora sid> [open | mount ].

where:

<ora sid>   : is the unique oracle instance identifier to shutdown non rac and rac instance on the local node required.
[<shutdown type>]      : startup type (Valid start up arguments are:  open,mount.
examples:
orastartdb.ksh oradv1  — startup database and open
orastartdb.ksh oradv1 mount — Starting an Instance and Mounting a Database

 

====entry exisis in oratab but pfile or spfile is missing

./orastartdb.ksh uk10204

orastartdb.ksh started @Mon Jul 11 12:25:55 PDT 2011
ORACLE_BASE ………………:/u01/app/oracle
ORACLE_HOME ………………:/u01/app/oracle/product/10.2.0.5/db_1
DB_NAME ………………….:uk10204
neither spfile nor pfile exists to start uk10204 database instance
Please check the issue and restart the process
orastartdb.ksh aborted @Mon Jul 11 12:25:55 PDT 2011

======

./orastartdb.ksh uk10205

orastartdb.ksh started @Mon Jul 11 12:26:11 PDT 2011
ORACLE_BASE ………………:/u01/app/oracle
ORACLE_HOME ………………:/u01/app/oracle/product/10.2.0.5/db_1
DB_NAME ………………….:uk10205
Info: Database uk10205 is not up,we can safely start the database .
Starting up uk10205 … Please wait…

orastartdb.ksh: Database uk10205 started successfully.
orastartdb.ksh: Check /home/oracle/admin/logs/uk10205/orastartdbuk10205.blog.20110711 logfile for complete details
orastartdb.ksh finished @Mon Jul 11 12:26:29 PDT 2011

=====

./orastartdb.ksh uk10205

orastartdb.ksh started @Mon Jul 11 12:27:00 PDT 2011
ORACLE_BASE ………………:/u01/app/oracle
ORACLE_HOME ………………:/u01/app/oracle/product/10.2.0.5/db_1
DB_NAME ………………….:uk10205
uk10205 is already up and running,we can startup shutdown databases only

 

Thanks

Categories: dba_scripts

script to shutdown single instance oracle database

July 11, 2011 Leave a comment

#!/bin/ksh
#——————————————————————
# name : oradbshut.ksh
# This script is used to shudown oracle database
# Arguments $1 = ORACLE_SID(Required)
# Argument $2 = normal, immediate, abort(optional)
# If argument 2 is not specified then default normal
# usage: orashutdb.ksh <ora sid> [<shutdown type>]
#       Created By : Uma M Katru
#       Date : 07-08-2010
#——————————————————————-
dbshutUsage()
{
printf “\nError: $Me – incorrect number of arguments!\n\n”
printf “Usage: $Me <ora sid> [normal | immediate | abort].\n\n”
printf “where:\n\n”
printf ”  <ora sid>   : is the unique oracle instance identifier to shutdown non rac and rac instance on the local node required.\n”
printf ”   [<shutdown type>]      : shutdown type (Valid shutdown arguments are:  normal, immediate, abort.\n”
printf “examples:\n”
printf ”  $Me oradv1  — shutdown oracle database in normal mode\n”
printf ”  $Me oradv1 immediate — shutdown oracle database immediately\n”
exit 1
}
#############################################################################
# $1 is the actual message to log
# —————————————————————————–
upLog()
{
\echo “$1”
}
# —————————————————————————–
# main
#—————————————————————————–
Me=$(\basename $0)
mypath=$(\dirname $0)
export ORACLE_BASE=/u01/app/oracle
#set -vx

args=$#
if [[ $args = 1 ]]; then
ORACLE_SID=$1
SHUTMODE=normal
elif [[ $args = 2 ]];then
ORACLE_SID=$1
SHUTMODE=$2
else
dbshutUsage
fi

#check sid exists in oratab
sidexists=$(grep -i “^$ORACLE_SID:” /etc/oratab |wc -l | awk ‘{print $1}’)
if [[ $sidexists -eq 1 ]];then
echo
ORACLE_HOME=$(grep -w ^”$ORACLE_SID” /etc/oratab | awk -F”:” ‘{print $2}’)
else
echo “oracle instance is not available  on this machine”
exit 1
fi

#Check if db is running or not
ChekDBRunning=`ps -ef | grep -v grep | grep ora_.*_${ORACLE_SID} | wc -l`
if [ $ChekDBRunning -lt 1 ]; then
echo “Error: Database $ORACLE_SID is not up, shutdown not performed.”
exit 0
fi
LogDir=/home/oracle/admin/logs/$ORACLE_SID
if [[ ! -d /home/oracle/admin/logs/$ORACLE_SID ]];then
mkdir -p /home/oracle/admin/logs/$ORACLE_SID
fi
logFile=$LogDir/$(\echo $Me | \awk -F. ‘{print $1}’)$ORACLE_SID.blog.$(\date +%Y%m%d)
export ORACLE_SID=$ORACLE_SID
export ORACLE_HOME=$ORACLE_HOME
upLog “$Me started @$(\date)”
upLog “ORACLE_BASE ………………:$ORACLE_BASE”
upLog “ORACLE_HOME ………………:$ORACLE_HOME”
upLog “DB_NAME ………………….:$ORACLE_SID”
upLog “CurrentTime ………………:`date`”
echo “Shutting down $ORACLE_SID … Please wait…”
$ORACLE_HOME/bin/sqlplus “/ as sysdba”  << ! >> $logFile
shutdown $SHUTARG
exit
!
if [[ $rc -eq 0 ]] ; then
echo “”
echo “$Me: Database $ORACLE_SID shutdown completed successfully.”
upLog “$Me finished @$(\date)”
echo “”
else
echo “”
echo “$Me: unable to shutdown $ORACLE_SID database instance,Please see log file for details.”
upLog “$Me failed to shutdown $ORACLE_SID @$(\date)”
exit 1
fi
echo “—————————————————————-” >> $logFile
exit 0

Sample output :

./oradbshut.ksh

Error: oradbshut.ksh – incorrect number of arguments!

Usage: oradbshut.ksh <ora sid> [normal | immediate | abort].

where:

<ora sid>   : is the unique oracle instance identifier to shutdown non rac and rac instance on the local node required.
[<shutdown type>]      : shutdown type (Valid shutdown arguments are:  normal, immediate, abort.
examples:
oradbshut.ksh oradv1  — shutdown oracle database in normal mode
oradbshut.ksh oradv1 immediate — shutdown oracle database immediately

=============

./oradbshut.ksh uk10205

Error: Database uk10205 is not up, shutdown not performed.

=================

./oradbshut.ksh uk10205

oradbshut.ksh started @Mon Jul 10 10:50:55 PDT 2011
ORACLE_BASE ………………:/u01/app/oracle
ORACLE_HOME ………………:/u01/app/oracle/product/10.2.0.5/db_1
DB_NAME ………………….:uk10205
CurrentTime ………………:Mon Jul 10 10:50:55 PDT 2011
Shutting down uk10205 … Please wait…

oradbshut.ksh: Database uk10205 shutdown completed successfully.
oradbshut.ksh finished @Mon Jul 10 10:51:01 PDT 2011

 

Categories: dba_scripts

How do I find the Cluster name(oracle 10g)

July 8, 2011 Leave a comment

oracle 10g you can find the cluster name using the following command:

CRS_HOME=/u01/app/root/product/10.2.0/crs

$CRS_HOME/bin/cemutlo -n

oral6001:/home/oracle=>$CRS_HOME/bin/cemutlo -n
crsoral6001

Categories: oracle-install

Create NFS share on Linux system

July 8, 2011 Leave a comment

Create NFS Shared File System:

Log in as root user:

[root@vhost001 ~]# id
uid=0(root) gid=0(root) groups=0(root),1(bin),2(daemon),3(sys),4(adm),6(disk),10(wheel)

[root@vhost001 ~]# mkdir /dba

Add the following lines to the /etc/exports file.

/dba                 *(rw,sync,no_wdelay,insecure_locks,no_root_squash)

Run the following command to export the NFS shares.
chkconfig nfs on
service nfs restart
Log into another server and mount the the NFS share:

Add the following lines to the “/etc/fstab” file.
vhost001:/dba  /home/oracle/admin/scripts  nfs  rw,bg,hard,nointr,tcp,vers=3,timeo=300,rsize=32768,wsize=32768,actimeo=0  0 0
mount -a

vhost001:/dba         894G  151G  697G  18% /home/oracle/admin/scripts

chown -R oracle:oinstall /home/oracle/admin/scripts

Thanks
Uma

Categories: Linux

11gR2 database creation script(dbca.sh)

July 7, 2011 Leave a comment

I have created following script to automate database creation process on file system:Please modify the script to suit your env.

#!/bin/ksh
###########################################################
#Set password
##########################################################
setPasswd(){
## PASSWORDS ##
echo
echo “Please specify the passwords for the ”
echo “following database administrative ”
echo “accounts.”
echo

for dbuser in SYS SYSTEM
do
echo
if [ “${dbuser}” = “SYS” ] || [ “${dbuser}” = “SYSTEM” ]
then
display_user=”${dbuser} (Database Administrative Account)”
fi
echo -n “${display_user} Password:  ”
while [ 1 ]
do
/bin/stty -echo > /dev/null 2>&1
temp=`echo $IFS`
export IFS=”\n”
while [ 1 ]
do
read LINE

while [ -z “$LINE” ]
do
echo
echo -n “Password can not be null, please try again:  ”
read LINE
done

result=`expr index “$LINE” [\’\”]`
if [ $result != 0 ];
then
echo
echo -n “The password you entered contains invalid characters. Please try again:  ”
else
break
fi
done
echo
echo -n “Confirm ${dbuser} password:  ”
read LINE1
echo
if [ “$LINE” != “$LINE1” ];
then
echo
echo -n “Passwords do not match.  Please enter the password again:  ”
else
break
fi
done

if [ ${dbuser} = “SYS” ]
then
SYS_PWD=$LINE
elif [ ${dbuser} = “SYSTEM” ]
then
SYSTEM_PWD=$LINE
else
echo “Invalid database user: ${dbuser}”
exit 30
fi

/bin/stty echo > /dev/null 2>&1
export IFS=$temp
done
}
#################################################################
#Set ORacle SID
################################################################
setOraSid(){
echo
echo “Please enter the name for your Oracle Database. ”
echo “This name will be used as your ”
echo -n “ORACLE SID (System Identifier):  ”
read LINE
while [ -z “$LINE” ]
do
echo -n “ORACLE SID can not be null, please try again:  ”
read LINE
done

NDB_LENGTH=”`echo ${LINE} | wc -L`”

while [ ${NDB_LENGTH} -gt 8 ]
do
echo “The new database name: ${LINE} is too long.  The database name must be 8 bytes or less, please try again:  ”
read LINE
NDB_LENGTH=”`echo ${LINE} | wc -L`”
done
ORACLE_SID=$LINE
GLOBAL_NAME=$ORACLE_SID
export ORACLE_SID=$LINE
}
##################################################################
# main
##################################################################
Me=$(\basename $0)
mypath=$(\dirname $0)

RUID=`/usr/bin/id|awk -F\( ‘{print $2}’|awk -F\) ‘{print $1}’`
if [ ${RUID} != “oracle” ];then
echo “You must be logged in as oracle to  dbca.sh. ”
echo “Log in as oracle and restart dbca.sh execution.”
exit 1
fi

if [[ -z $ORACLE_HOME || -z $ORACLE_BASE ]];then
echo
echo “either ORACLE_HOME or ORACLE_BASE are not set ,Please set both the variables and restart the script again”
exit 30
fi

setOraSid
setPasswd

export ORACLE_BASE=$ORACLE_BASE
export ORACLE_HOME=$ORACLE_HOME
export SID=$ORACLE_SID
DBCA_TEMPLATE_DIR=${ORACLE_HOME}/assistants/dbca/templates
DBCA_TEMPLATE_NAME=${DBCA_TEMPLATE_DIR}/General_Purpose.dbc
ORACLE_OWNER=oracle
DBCA=${ORACLE_HOME}/bin/dbca
SQLPLUS=${ORACLE_HOME}/bin/sqlplus
DB_FILE_DIR=/u01/app/oradata
RECO_AREA=/u01/app/flash_recovery_area

#mkdir $ORACLE_BASE/admin/${ORACLE_SID}
#mkdir $ORACLE_BASE/admin/${ORACLE_SID}/dbs
#mkdir $ORACLE_BASE/admin/${ORACLE_SID}/adump

${DBCA} -silent -createDatabase -templateName ${DBCA_TEMPLATE_NAME} -gdbName ${GLOBAL_NAME} -sid ${SID} -sysPassword ${SYS_PWD} -systemPassword ${SYSTEM_PWD} -emConfiguration NONE -storageType FS -datafileJarLocation ${DBCA_TEMPLATE_DIR} -sampleSchema false -datafileDestination ${DB_FILE_DIR} -recoveryAreaDestination ${RECO_AREA}
RV=$?
if [ $RV -ne 0 ]
then
echo
echo “There was a problem creating your database.  Please review the log file shown above.”
else
echo
echo “The database was created successfully.”
fi

Usage :./dbca.sh

either ORACLE_HOME or ORACLE_BASE are not set ,Please set both the variables and restart the script again

exprot ORACLE_HOME=<your oracle home>

export ORACLE_BASE=<oracle base>

./dbca.sh

Please enter the name for your Oracle Database.
This name will be used as your
ORACLE SID (System Identifier):  dgprm1

Please specify the passwords for the
following database administrative
accounts.

SYS (Database Administrative Account) Password:
Confirm SYS password:

SYSTEM (Database Administrative Account) Password:
Confirm SYSTEM password:

Sample log:

Copying database files
DBCA_PROGRESS : 1%
DBCA_PROGRESS : 3%
DBCA_PROGRESS : 11%
DBCA_PROGRESS : 18%
DBCA_PROGRESS : 26%
DBCA_PROGRESS : 33%
DBCA_PROGRESS : 37%
Creating and starting Oracle instance
DBCA_PROGRESS : 40%
DBCA_PROGRESS : 45%
DBCA_PROGRESS : 50%
DBCA_PROGRESS : 55%
DBCA_PROGRESS : 56%
DBCA_PROGRESS : 60%
DBCA_PROGRESS : 62%
Completing Database Creation
DBCA_PROGRESS : 66%
DBCA_PROGRESS : 70%
DBCA_PROGRESS : 73%
DBCA_PROGRESS : 85%
DBCA_PROGRESS : 96%
DBCA_PROGRESS : 100%
Database creation complete. For details check the logfiles at:
/u01/app/oracle/cfgtoollogs/dbca/dgprm.
Database Information:
Global Database Name:dgprm
System Identifier(SID):dgprm

****script to create database on asm********

Categories: oracle-install

11g add a column with not null to the existing table

July 1, 2011 Leave a comment

Prior to Oracle database 11g, adding a new column with a default NOT NULL value were a challenge.if we have table with millions of rows and if we try to add not null column then we will get following error.

/home/oracle=>sqlplus /nolog

SQL*Plus: Release 10.2.0.3.0 – Production on Mon Jun 27 17:01:56 2011

Copyright (c) 1982, 2006, Oracle.  All Rights Reserved.

SQL> create table add_notnull_column (customer_name varchar2(10),customer_city varchar2(10));

Table created.

SQL> insert into add_notnull_column values(‘UMA’,’CYPRESS’);

1 row created.

SQL> commit;

Commit complete.

SQL> select * from add_notnull_column;

CUSTOMER_N CUSTOMER_C
———- ———-
UMA        CYPRESS

SQL> alter table add_notnull_column add  ACTIVE_IND char(1) not null;
alter table add_notnull_column add  ACTIVE_IND char(1) not null
*
ERROR at line 1:
ORA-01758: table must be empty to add mandatory (NOT NULL) column

In this case we need to create a new table with same structure and populate the data and then rename to the original table.

Oracle database 11g offers a new quick, easy, instant solution; instead of updating ALL the rows of the table, Oracle 11g now updates the metadata only

<oracle:ora11gr21>:/home/oracle=>sqlplus / as sysdba

SQL*Plus: Release 11.2.0.2.0 Production on Mon Jun 27 14:58:26 2011

Copyright (c) 1982, 2010, Oracle.  All rights reserved.

Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 – 64bit Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,
Data Mining and Real Application Testing options

Connected.
SQL> create table add_notnull_column (customer_name varchar2(10),customer_city varchar2(10));
insert into add_notnull_column values(‘UMA’,’CYPRESS’);
commit;
Table created.

SQL>
1 row created.

SQL> select * from add_notnull_column;

CUSTOMER_N CUSTOMER_C
———- ———-
UMA        CYPRESS

SQL> alter table add_notnull_column add  ACTIVE_IND char(1) default ‘Y’ not null;

Table altered.

SQL> select * from add_notnull_column;

CUSTOMER_N CUSTOMER_C A
———- ———- –
UMA        CYPRESS    Y

%d bloggers like this: