Oracle Instalation On Linux

Configure the kernel.
Edit /etc/sysctl.conf.
Add the following to the file:
# Kernel parameters required by Oracle 11gR1 / 10g

fs.file-max = 6553600
kernel.shmall = 2097152
kernel.shmmax = 2147483648
kernel.shmmni = 4096
kernel.sem = 250 32000 100 128
net.ipv4.ip_local_port_range = 1024 65000
net.core.rmem_default = 4194304
net.core.rmem_max = 4194304
net.core.wmem_default = 262144
net.core.wmem_max = 262144
************************************************************************
sysctl -p
************************************************************************
Edit /etc/security/limits.conf
Add these lines:

oracle soft nproc 2047
oracle hard nproc 16384
oracle soft nofile 1024
oracle hard nofile 65536

************************************************************************
Edit /etc/pam.d/login

Add these lines:

session required /lib/security/pam_limits.so
session required pam_limits.so
************************************************************************
Edit /etc/profile

Add these lines:

if [ $USER = "oracle" ]; then
if [ $SHELL = "/bin/ksh" ]; then
ulimit -p 16384
ulimit -n 65536
else
ulimit -u 16384 -n 65536
fi
fi
************************************************************************
groupadd oinstall
groupadd dba
groupadd oper
useradd oracle
usermod -g oinstall -G dba,oper,oracle oracle
passwd oracle
*************************************************************************

Edit /home/oracle/.bash_profile

Add these lines:

umask 022
ORACLE_BASE=/u01/app/oracle
ORACLE_HOME=$ORACLE_BASE/product/11.1.0/db_1
ORACLE_SID=orcl
PATH=$ORACLE_HOME/bin:$PATH
export PATH
export ORACLE_BASE ORACLE_HOME ORACLE_SID
************************************************************************
mkdir -p /u01/app
chown root:oinstall /u01/app
chmod 775 /u01/app
************************************************************************

Sample Control file + sqlldr

SQLLDR USERID=account_data/account_data@test CONTROL=D:\share\vivek\test.CTL ERRORS=5000 log=d:\abc.log

test.CTL=
load data
infile 'data_daily1.csv'
infile 'data_daily2.csv'
infile 'data_daily3.csv'
append
into table daily_upload
fields terminated by ","
optionally enclosed by '"'
trailing nullcols
(
col1,
col2,
col3,
col4,
col5
)

How Much time recovery after Killing the Long Running Session will take

select state,undoblocksdone,undoblockstotal,undoblocksdone / undoblockstotal * 100
from v$fast_start_transactions;
************************
The FAST_START_PARALLEL_ROLLBACK parameter controls how much juice SMON will take. The three possible parameters are: FALSE, LOW, and HIGH. A setting of FALSE will tell SMON to do the recovery on its own without parallel assistance. This could work to allow your app to remain online while SMON does its job, but could still cause contention issues. In the end, it is better to let SMON finish the work while other processes try to lay off UNDO as much as possible. A setting of LOW will tell SMON to use 2 * CPU_COUNT parallel processes to do the work, and a setting of high will use 4 * CPU_COUNT. In order to allow SMON to finish its work as quickly as possible, we turned off their app and set the FAST_START_PARALLEL_ROLLBACK parameter to HIGH, allowing SMON all the resources it needed to finish. To determine how long it would take to finish, we consulted the V$FAST_START_TRANSACTIONS view

Delete duplicate row from table

delete from TABLE_NAME where rowid in (select min(rowid) from TABLE_NAME group by (empid, LOCATION) having count(empid)>1 );

Oracle to SQL connection

http://www.databasejournal.com/features/oracle/article.php/10893_3442661_1

1) Create DSN
2) change $ORACLE_HOME/hs/admin/inithsodbc.
3) Change listner.ora as per D:\oracle\product\10.1.0\db_1\hs\admin
4) change tnsname.ora as per D:\oracle\product\10.1.0\db_1\hs\admin
5) Now Start Listner ( Create Database link using SQL server User / Pass )

Oracle Installation on SUN

/etc/system
************************************
set shmsys:shminfo_shmmax=17179869184
set shmsys:shminfo_shmmin=1
set shmsys:shminfo_shmmni=4096
set shmsys:shminfo_shmseg=10
set semsys:seminfo_semmns= 32000
set semsys:seminfo_semmsl=256
set semsys:seminfo_semmni=128
set semsys:seminfo_semvmx=32767
set noexec_user_stack=1

***************SUN AMD 64xDDD*********

set shmsys:shminfo_shmmax=17179869184
set shmsys:shminfo_shmmin=1
set shmsys:shminfo_shmmni=4096
set shmsys:shminfo_shmseg=10
set semsys:seminfo_semmns= 32000
set semsys:seminfo_semmsl=256
set semsys:seminfo_semmni=128
set semsys:seminfo_semvmx=32767
set noexec_user_stack=1

/export/home/oracle:/usr/bin/bash
**********************************************

***************************
set semsys:seminfo_semopm= 100
set semsys:seminfo_shmall= 2097152
set semsys:seminfo_file-max= 65536
set semsys:seminfo_ip_local_port_range= 1024 65000
set semsys:seminfo_rmem_default= 262144
set semsys:seminfo_rmem_max= 262144
set semsys:seminfo_wmem_default= 262144
set semsys:seminfo_wmem_max= 262144

Step-2
**********************************
groupadd oinstall
groupadd dba
groupadd oper
userdel oracle
useradd -d /export/home/oracle -g oinstall -G dba,oper -m -s /usr/bin/bash oracle
passwd -r files oracle
**********************************
Step-3
editing the /export/home/oracle/.profile
**********************************
umask 022
TMP=/tmp
TMPDIR=$TMP
DISPLAY=localhost:0.0
export TMP TMPDIR DISPLAY
ORACLE_BASE=/opt/oracle
ORACLE_HOME=/opt/oracle/product/10.2.0/db_1
PATH=$ORACLE_HOME/bin:$PATH
export ORACLE_BASE ORACLE_HOME PATH
**********************************
Step-4
**********************************
su - oracle
env
-------------------------------
mkdir /oracle
mkdir /oracle/10g
chown -R oracle:oinstall /ora_soft
chmod -R 775 /ora_soft
**********************************
Step-5
**********************************
projadd oracle
/etc/user_attr
****************
Add the line
****************
oracle::::project=oracle
****************
# su - oracle
Sun Microsystems Inc. SunOS 5.10 Generic January 2005

$ id -p
uid=100(oracle) gid=100(oinstall) projid=100(oracle)
****************************************************

Extracting SQL from an EXPORT file

TIP: Click help for a detailed explanation of this page.
Bookmark Go to End

Subject: Unix Script: IMPSHOW2SQL - Extracting SQL from an EXPORT file
Doc ID: Note:29765.1 Type: SCRIPT
Last Revision Date: 25-APR-2001 Status: PUBLISHED


This is a short script that allows you to extract the SQL from an export
file. It is intended for use on UNIX machines. Instructions are in the
header of the script.

When entering the script ensure:

A> The first line in the file should be a single colon (:)

Please note this is a proto-type script so there may be some statements
in the resultant file that need manual editting.

:
# impshow2sql Tries to convert output of an IMP SHOW=Y command into a
# usage SQL script.
#
# To use:
# Start a Unix script session and import with show=Y thus:
#
# $ imp user/password file=exportfile show=Y log=/tmp/showfile
#
# You now have the SHOW=Y output in /tmp/showfile .
# Run this script against this file thus:
#
# $ ./impshow2sql /tmp/showfile > /tmp/imp.sql
#
# The file /tmp/imp.sql should now contain the main SQL for
# the IMPORT.
# You can edit this as required.
# Note: This script may split lines incorrectly for some statements
# so it is best to check the output.
#
# CONSTRAINT "" problem:
# You can use this script to help get the SQL from an export
# then correct it if it includes bad SQL such as CONSTRAINT "".
# Eg:
# Use the steps above to get a SQL script and then
# $ sed -e 's/CONSTRAINT ""//' infile > outfile
# Now precreate all the objects and import the export file.
#
# Extracting Specific Statements only:
# It is fairly easy to change the script to extract certain statements
# only. For statements you do NOT want to extract change N=1 to N=0
# Eg: To extract CREATE TRIGGER statements only:
# a) Change all lines to set N=0.
# Eg: / \"CREATE / { N=0; }
# This stops CREATE statements being output.
#
# b) Add a line (After the general CREATE line above):
# / \"CREATE TRIGGER/ { N=1; }
# This flags that we SHOULD output CREATE TRIGGER statements.
#
# c) Run the script as described to get CREATE TRIGGER statements.
#

awk ' BEGIN { prev=";" }
/ \"CREATE / { N=1; }
/ \"ALTER / { N=1; }
/ \"ANALYZE / { N=1; }
/ \"GRANT / { N=1; }
/ \"COMMENT / { N=1; }
/ \"AUDIT / { N=1; }
N==1 { printf "\n/\n\n"; N++ }
/\"$/ { prev=""
if (N==0) next;
s=index( $0, "\"" );
if ( s!=0 ) {
printf "%s",substr( $0,s+1,length( substr($0,s+1))-1 )
prev=substr($0,length($0)-1,1 );
}
if (length($0)<78) printf( "\n" );
}' $*