1>--HOST NAMING METHOD--
--PRIMARY DATABASE------ --------CLIENT-------
1>LISTINER SHOULD BE RUNNING 1>SQLNET.ORA SHOULD BE CONFIGURED WITH
NAMES.DIRECTORY_PATH= (HOST)
--PATH FOR SQLNET.ORA FILE-
LINUX:$ORACLE_HOME/NETWORK/ADMIN
WINDOWS:\oracle\product\10.1.0\db_1\NETWORK\ADMIN
2>HOST FILE SHOULD BE REGISTERED WITH
PRIMARY DATABASE IP AND GLOBAL_DB_NAME
IP GLOBAL_DB_NAME
10.60.10.95 DKDB
--PATH FOR HOST FILE---
LINUX:CD /ETC/HOSTS
WINDOWS:C:\WINDOWS\system32\drivers
----CONNECTING FROM CLIENT---
eg:SQLPLUS SYSTEM/DKDB@DKDB
SQL>CONN SYSTEM/DKDB@DKDB
2>--EZCONNECT NAMING METHOD--
--PRIMARY DATABASE------ -------CLIENT-------
1>LISTINER SHOULD BE RUNNING 1>SQLNET.ORA SHOULD BE CONFIGURED WITH
NAMES.DIRECTORY_PATH= (EZCONNECT)
--PATH FOR SQLNET.ORA FILE--
LINUX:$ORACLE_HOME/NETWORK/ADMIN
WINDOWS:\oracle\product\10.1.0\db_1\NETWORK\ADMIN
--CONNECTING FROM CLIENT--
eg:SQLPLUS SYSTEM/DKDB@10.60.10.95:1521/DKDB
SQL>CONN SYSTEM/DKDB@10.60.10.95:1521/DKDB
3>--TNS NAMING METHOD--
--PRIMARY DATABASE------ -------CLIENT-------
1>LISTINER SHOULD BE RUNNING 1>SQLNET.ORA SHOULD BE CONFIGURED WITH
NAMES.DIRECTORY_PATH= (TNSNAMES)
--PATH FOR SQLNET.ORA FILE--
LINUX:$ORACLE_HOME/NETWORK/ADMIN
WINDOWS:\oracle\product\10.1.0\db_1\NETWORK\ADMIN
2>TNSNAMES.ORA SHOULD BE CONFIGURED WITH
DKDB =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 10.60.10.95)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = dkdb)
)
)
---CONNECTING FROM CLIENT-----
eg:SQLPLUS SYSTEM/DKDB@DKDB
SQL> CONN SYSTEM/DKDB@DKDB
Tuesday, December 1, 2009
To increase DB process
You might also want to check if the number of connections reaches the database's PROCESSES parameter using following Unix command:
ps -ef | grep oracleSID | grep -v grep | wc -l
or
ps aux | grep oracleSID | grep -v grep | wc -l
If the value found is close to the value of the PROCESSES database parameter value, you might want to augment this value using following command:
alter system set processes=new value [scope=spfile];
A database bounce is required to let the new value become active.
ps -ef | grep oracleSID | grep -v grep | wc -l
or
ps aux | grep oracleSID | grep -v grep | wc -l
If the value found is close to the value of the PROCESSES database parameter value, you might want to augment this value using following command:
alter system set processes=new value [scope=spfile];
A database bounce is required to let the new value become active.
crontab clone db
#30 20 * * 5 /home/oracle/clone_scripts/clone_fopsfat1.sh
-------------------clone_fopsfat1.sh-----------------
# .bash_profile
# Get the aliases and functions
if [ -f ~/.bashrc ]; then
. ~/.bashrc
fi
# User specific environment and startup programs
TMP=/tmp; export TMP
ORACLE_HOME_LISTENER=$ORACLE_BASE ;export ORACLE_HOME_LISTENER
TMPDIR=$TMP; export TMPDIR
ORACLE_BASE=/data/app/oracle; export ORACLE_BASE
ORACLE_HOME=$ORACLE_BASE/product/10.2.0/db_1; export ORACLE_HOME
ORACLE_TERM=xterm; export ORACLE_TERM
PATH=${PATH}:$ORACLE_HOME/bin/ export PATH
LD_LIBRARY_PATH=$ORACLE_HOME/lib:/lib:/usr/lib; export LD_LIBRARY_PATH
if [ $USER = "oracle" ]; then
if [ $SHELL = "/bin/ksh" ]; then
ulimit -p 16384
ulimit -n 65536
else
ulimit -u 16384 -n 65536
fi
fi
export ORACLE_SID=fopsfat1
lsnrctl stop listenerfat1
cd /data/fopsfat1
rm *.*
cd /data/fops
cp *.* /data/fopsfat1/
cd /data/fopsfat1
rm *.ctl
rm temp01.dbf
cd /data/app/oracle/admin/fopsfat1
rm -rf udump bdump cdump adump
mkdir udump bdump cdump adump
sqlplus 'sys/fopsdb2008 as sysdba' @/home/oracle/clone_scripts/fopsfat1conttrc.sql
exit
---------------fopsfat1conttrc.sql--------------------------
SHUTDOWN ABORT;
STARTUP NOMOUNT pfile='$ORACLE_HOME/dbs/initfopsfat1.ora'
CREATE CONTROLFILE SET DATABASE "fopsfat1" RESETLOGS NOARCHIVELOG
MAXLOGFILES 16
MAXLOGMEMBERS 3
MAXDATAFILES 100
MAXINSTANCES 8
MAXLOGHISTORY 887
LOGFILE
GROUP 1 '/data/fopsfat1/redo01.log' SIZE 50M,
GROUP 2 '/data/fopsfat1/redo02.log' SIZE 50M,
GROUP 3 '/data/fopsfat1/redo03.log' SIZE 50M
-- STANDBY LOGFILE
DATAFILE
'/data/fopsfat1/system01.dbf',
'/data/fopsfat1/undotbs01.dbf',
'/data/fopsfat1/sysaux01.dbf',
'/data/fopsfat1/users01.dbf',
'/data/fopsfat1/admin_data_01.dbf',
'/data/fopsfat1/admin_data_02.dbf',
'/data/fopsfat1/load_data_01.dbf',
'/data/fopsfat1/navdata_data_01.dbf',
'/data/fopsfat1/spatial_data_01.dbf',
'/data/fopsfat1/statw_data_01.dbf',
'/data/fopsfat1/statw_data_02.dbf',
'/data/fopsfat1/tools_data_01.dbf',
'/data/fopsfat1/weather_data_01.dbf'
CHARACTER SET WE8ISO8859P15
;
alter database open resetlogs;
-- re-create incarnation records.
ALTER TABLESPACE TEMP ADD TEMPFILE '/data/fopsfat1/temp01.dbf'
SIZE 1097M REUSE AUTOEXTEND ON NEXT 655360 MAXSIZE 32767M;
alter user system account unlock;
alter user system identified by fopsdb2008;
!lsnrctl start listenerfat1
exit
------------------------------
Note: 1.add a entry in oratab file
2.create a pfile and edit pfile whit new db name and file locations
3.create a password file
4.edit the contents inside controlfile trace and rename it.
-------------------clone_fopsfat1.sh-----------------
# .bash_profile
# Get the aliases and functions
if [ -f ~/.bashrc ]; then
. ~/.bashrc
fi
# User specific environment and startup programs
TMP=/tmp; export TMP
ORACLE_HOME_LISTENER=$ORACLE_BASE ;export ORACLE_HOME_LISTENER
TMPDIR=$TMP; export TMPDIR
ORACLE_BASE=/data/app/oracle; export ORACLE_BASE
ORACLE_HOME=$ORACLE_BASE/product/10.2.0/db_1; export ORACLE_HOME
ORACLE_TERM=xterm; export ORACLE_TERM
PATH=${PATH}:$ORACLE_HOME/bin/ export PATH
LD_LIBRARY_PATH=$ORACLE_HOME/lib:/lib:/usr/lib; export LD_LIBRARY_PATH
if [ $USER = "oracle" ]; then
if [ $SHELL = "/bin/ksh" ]; then
ulimit -p 16384
ulimit -n 65536
else
ulimit -u 16384 -n 65536
fi
fi
export ORACLE_SID=fopsfat1
lsnrctl stop listenerfat1
cd /data/fopsfat1
rm *.*
cd /data/fops
cp *.* /data/fopsfat1/
cd /data/fopsfat1
rm *.ctl
rm temp01.dbf
cd /data/app/oracle/admin/fopsfat1
rm -rf udump bdump cdump adump
mkdir udump bdump cdump adump
sqlplus 'sys/fopsdb2008 as sysdba' @/home/oracle/clone_scripts/fopsfat1conttrc.sql
exit
---------------fopsfat1conttrc.sql--------------------------
SHUTDOWN ABORT;
STARTUP NOMOUNT pfile='$ORACLE_HOME/dbs/initfopsfat1.ora'
CREATE CONTROLFILE SET DATABASE "fopsfat1" RESETLOGS NOARCHIVELOG
MAXLOGFILES 16
MAXLOGMEMBERS 3
MAXDATAFILES 100
MAXINSTANCES 8
MAXLOGHISTORY 887
LOGFILE
GROUP 1 '/data/fopsfat1/redo01.log' SIZE 50M,
GROUP 2 '/data/fopsfat1/redo02.log' SIZE 50M,
GROUP 3 '/data/fopsfat1/redo03.log' SIZE 50M
-- STANDBY LOGFILE
DATAFILE
'/data/fopsfat1/system01.dbf',
'/data/fopsfat1/undotbs01.dbf',
'/data/fopsfat1/sysaux01.dbf',
'/data/fopsfat1/users01.dbf',
'/data/fopsfat1/admin_data_01.dbf',
'/data/fopsfat1/admin_data_02.dbf',
'/data/fopsfat1/load_data_01.dbf',
'/data/fopsfat1/navdata_data_01.dbf',
'/data/fopsfat1/spatial_data_01.dbf',
'/data/fopsfat1/statw_data_01.dbf',
'/data/fopsfat1/statw_data_02.dbf',
'/data/fopsfat1/tools_data_01.dbf',
'/data/fopsfat1/weather_data_01.dbf'
CHARACTER SET WE8ISO8859P15
;
alter database open resetlogs;
-- re-create incarnation records.
ALTER TABLESPACE TEMP ADD TEMPFILE '/data/fopsfat1/temp01.dbf'
SIZE 1097M REUSE AUTOEXTEND ON NEXT 655360 MAXSIZE 32767M;
alter user system account unlock;
alter user system identified by fopsdb2008;
!lsnrctl start listenerfat1
exit
------------------------------
Note: 1.add a entry in oratab file
2.create a pfile and edit pfile whit new db name and file locations
3.create a password file
4.edit the contents inside controlfile trace and rename it.
crontab monitor sessions blocking
#* * * * * /data/app/oracle/product/10.2.0/db_1/kill/monitor.sh > /dev/null 2>&1
-----------------------------------monitor.sh----------------------------------
# .bash_profile
# Get the aliases and functions
if [ -f ~/.bashrc ]; then
. ~/.bashrc
fi
# User specific environment and startup programs
TMP=/tmp; export TMP
ORACLE_HOME_LISTENER=$ORACLE_BASE ;export ORACLE_HOME_LISTENER
TMPDIR=$TMP; export TMPDIR
ORACLE_BASE=/data/app/oracle; export ORACLE_BASE
ORACLE_HOME=$ORACLE_BASE/product/10.2.0/db_1; export ORACLE_HOME
ORACLE_TERM=xterm; export ORACLE_TERM
PATH=${PATH}:$ORACLE_HOME/bin/ export PATH
LD_LIBRARY_PATH=$ORACLE_HOME/lib:/lib:/usr/lib; export LD_LIBRARY_PATH
if [ $USER = "oracle" ]; then
if [ $SHELL = "/bin/ksh" ]; then
ulimit -p 16384
ulimit -n 65536
else
ulimit -u 16384 -n 65536
fi
fi
sqlplus system/fopsdb2008@fopsfat1 @/data/app/oracle/product/10.2.0/db_1/kill/monitor.sql
-----------------monitor.sql-----------------------------
set linesize 120
set echo off
set heading off
spool on
spool /data/app/oracle/product/10.2.0/db_1/kill/monitor_lock.spool
select s1.username,s1.osuser || '@' || s1.machine
|| ' ( SID=' || s1.sid || ' ) is blocking '
|| s2.username || '@' || s2.machine || ' ( SID=' || s2.sid || ' ) ' AS blocking_status
from v$lock l1, v$session s1, v$lock l2, v$session s2
where s1.sid=l1.sid and s2.sid=l2.sid
and l1.BLOCK=1 and l2.request > 0
and l1.id1 = l2.id1
and l2.id2 = l2.id2 ;
select sysdate from dual;
spool off
!cat /data/app/oracle/product/10.2.0/db_1/kill/monitor_lock.spool >> /data/app/oracle/product/10.2.0/db_1/kill/monitor.txt
exit
-----------------------------------monitor.sh----------------------------------
# .bash_profile
# Get the aliases and functions
if [ -f ~/.bashrc ]; then
. ~/.bashrc
fi
# User specific environment and startup programs
TMP=/tmp; export TMP
ORACLE_HOME_LISTENER=$ORACLE_BASE ;export ORACLE_HOME_LISTENER
TMPDIR=$TMP; export TMPDIR
ORACLE_BASE=/data/app/oracle; export ORACLE_BASE
ORACLE_HOME=$ORACLE_BASE/product/10.2.0/db_1; export ORACLE_HOME
ORACLE_TERM=xterm; export ORACLE_TERM
PATH=${PATH}:$ORACLE_HOME/bin/ export PATH
LD_LIBRARY_PATH=$ORACLE_HOME/lib:/lib:/usr/lib; export LD_LIBRARY_PATH
if [ $USER = "oracle" ]; then
if [ $SHELL = "/bin/ksh" ]; then
ulimit -p 16384
ulimit -n 65536
else
ulimit -u 16384 -n 65536
fi
fi
sqlplus system/fopsdb2008@fopsfat1 @/data/app/oracle/product/10.2.0/db_1/kill/monitor.sql
-----------------monitor.sql-----------------------------
set linesize 120
set echo off
set heading off
spool on
spool /data/app/oracle/product/10.2.0/db_1/kill/monitor_lock.spool
select s1.username,s1.osuser || '@' || s1.machine
|| ' ( SID=' || s1.sid || ' ) is blocking '
|| s2.username || '@' || s2.machine || ' ( SID=' || s2.sid || ' ) ' AS blocking_status
from v$lock l1, v$session s1, v$lock l2, v$session s2
where s1.sid=l1.sid and s2.sid=l2.sid
and l1.BLOCK=1 and l2.request > 0
and l1.id1 = l2.id1
and l2.id2 = l2.id2 ;
select sysdate from dual;
spool off
!cat /data/app/oracle/product/10.2.0/db_1/kill/monitor_lock.spool >> /data/app/oracle/product/10.2.0/db_1/kill/monitor.txt
exit
crontab kill blocking sessions using process id
How to kill locking session: -
1) Creating .sh file to automate the job through crontab: -
kill.sh: -
# .bash_profile
ORACLE_BASE=/u01/app/oracle; export ORACLE_BASE
ORACLE_HOME=$ORACLE_BASE/product/10.2.0/db_1; export ORACLE_HOME
PATH=${PATH}:$ORACLE_HOME/bin/ export PATH
LD_LIBRARY_PATH=$ORACLE_HOME/lib:/lib:/usr/lib; export LD_LIBRARY_PATH
cd $ORACLE_HOME/kill
sqlplus system/fopsdb2008@fopsfat1 @/u01/app/oracle/product/10.2.0/db_1/kill/kill.sql
exit
2) Creating .sql file to get the locked session ID in OS level: -
kill.sql: -
set heading off
set echo off
spool /u01/app/oracle/product/10.2.0/db_1/kill/kill_in.sh
select 'kill -9 '|| vp.spid from v$session vs, v$process vp
where vs.paddr = vp.addr
and vs.sid in (select s1.sid from v$lock l1, v$session s1, v$lock l2, v$session s2
where s1.sid=l1.sid and s2.sid=l2.sid and l1.BLOCK=1 and l2.request > 0
and l1.id1 = l2.id1 and l2.id2 = l2.id2);
spool off
select name from v$database;
!chmod 777 /u01/app/oracle/product/10.2.0/db_1/kill/kill_in.sh
!export ORACLE_HOME=/u01/app/oracle/product/10.2.0/db_1
!cd /u01/app/oracle/product/10.2.0/db_1/kill
!pwd
!./kill_in.sh
Exit
3) Making an entry to automate this job every 1 minute: -
Crontab -e: -
* * * * * /u01/app/oracle/product/10.2.0/db_1/kill/kill.sh > /dev/null 2>&1
1) Creating .sh file to automate the job through crontab: -
kill.sh: -
# .bash_profile
ORACLE_BASE=/u01/app/oracle; export ORACLE_BASE
ORACLE_HOME=$ORACLE_BASE/product/10.2.0/db_1; export ORACLE_HOME
PATH=${PATH}:$ORACLE_HOME/bin/ export PATH
LD_LIBRARY_PATH=$ORACLE_HOME/lib:/lib:/usr/lib; export LD_LIBRARY_PATH
cd $ORACLE_HOME/kill
sqlplus system/fopsdb2008@fopsfat1 @/u01/app/oracle/product/10.2.0/db_1/kill/kill.sql
exit
2) Creating .sql file to get the locked session ID in OS level: -
kill.sql: -
set heading off
set echo off
spool /u01/app/oracle/product/10.2.0/db_1/kill/kill_in.sh
select 'kill -9 '|| vp.spid from v$session vs, v$process vp
where vs.paddr = vp.addr
and vs.sid in (select s1.sid from v$lock l1, v$session s1, v$lock l2, v$session s2
where s1.sid=l1.sid and s2.sid=l2.sid and l1.BLOCK=1 and l2.request > 0
and l1.id1 = l2.id1 and l2.id2 = l2.id2);
spool off
select name from v$database;
!chmod 777 /u01/app/oracle/product/10.2.0/db_1/kill/kill_in.sh
!export ORACLE_HOME=/u01/app/oracle/product/10.2.0/db_1
!cd /u01/app/oracle/product/10.2.0/db_1/kill
!pwd
!./kill_in.sh
Exit
3) Making an entry to automate this job every 1 minute: -
Crontab -e: -
* * * * * /u01/app/oracle/product/10.2.0/db_1/kill/kill.sh > /dev/null 2>&1
crontab dbshut and dbstart
00 18 * * 6 /data/app/oracle/product/10.2.0/db_1/all_db_shut.sh
---------------------------all_db_shut.sh-----------------------------------
[oracle@FOPSDB ~]$ cat /data/app/oracle/product/10.2.0/db_1/all_db_shut.sh
# .bash_profile
# Get the aliases and functions
if [ -f ~/.bashrc ]; then
. ~/.bashrc
fi
# User specific environment and startup programs
TMP=/tmp; export TMP
ORACLE_HOME_LISTENER=$ORACLE_BASE ;export ORACLE_HOME_LISTENER
TMPDIR=$TMP; export TMPDIR
ORACLE_BASE=/data/app/oracle; export ORACLE_BASE
ORACLE_HOME=$ORACLE_BASE/product/10.2.0/db_1; export ORACLE_HOME
ORACLE_TERM=xterm; export ORACLE_TERM
PATH=${PATH}:$ORACLE_HOME/bin/ export PATH
LD_LIBRARY_PATH=$ORACLE_HOME/lib:/lib:/usr/lib; export LD_LIBRARY_PATH
if [ $USER = "oracle" ]; then
if [ $SHELL = "/bin/ksh" ]; then
ulimit -p 16384
ulimit -n 65536
else
ulimit -u 16384 -n 65536
fi
fi
lsnrctl stop listenerdev
lsnrctl stop listenerint
lsnrctl stop listenerfat1
lsnrctl stop listenerfat2
#lsnrctl stop listenerret
lsnrctl stop listenerdem1
lsnrctl stop listenersat1
#echo FOPSTEST db shutdown `date` >> $ORACLE_HOME/db.log
dbshut
#echo FOPSTEST db shutdown completed `date` >> $ORACLE_HOME/db.log
++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
----------------------------all_db_start.sh------------------------------
30 4 * * 0 /data/app/oracle/product/10.2.0/db_1/all_db_start.sh
# .bash_profile
# Get the aliases and functions
if [ -f ~/.bashrc ]; then
. ~/.bashrc
fi
# User specific environment and startup programs
TMP=/tmp; export TMP
ORACLE_HOME_LISTENER=$ORACLE_BASE ;export ORACLE_HOME_LISTENER
TMPDIR=$TMP; export TMPDIR
ORACLE_BASE=/data/app/oracle; export ORACLE_BASE
ORACLE_HOME=$ORACLE_BASE/product/10.2.0/db_1; export ORACLE_HOME
ORACLE_TERM=xterm; export ORACLE_TERM
PATH=${PATH}:$ORACLE_HOME/bin/ export PATH
LD_LIBRARY_PATH=$ORACLE_HOME/lib:/lib:/usr/lib; export LD_LIBRARY_PATH
if [ $USER = "oracle" ]; then
if [ $SHELL = "/bin/ksh" ]; then
ulimit -p 16384
ulimit -n 65536
else
ulimit -u 16384 -n 65536
fi
fi
lsnrctl start listenerdev
lsnrctl start listenerint
lsnrctl start listenerfat1
lsnrctl start listenerfat2
#lsnrctl start listenerret
lsnrctl start listenerdem1
lsnrctl start listenersat1
#echo FOPSTEST db startup `date` >> $ORACLE_HOME/db.log
dbstart
#echo FOPSTEST db startup completed `date` >> $ORACLE_HOME/db.log
------------------------------------------------------------
---------------------------all_db_shut.sh-----------------------------------
[oracle@FOPSDB ~]$ cat /data/app/oracle/product/10.2.0/db_1/all_db_shut.sh
# .bash_profile
# Get the aliases and functions
if [ -f ~/.bashrc ]; then
. ~/.bashrc
fi
# User specific environment and startup programs
TMP=/tmp; export TMP
ORACLE_HOME_LISTENER=$ORACLE_BASE ;export ORACLE_HOME_LISTENER
TMPDIR=$TMP; export TMPDIR
ORACLE_BASE=/data/app/oracle; export ORACLE_BASE
ORACLE_HOME=$ORACLE_BASE/product/10.2.0/db_1; export ORACLE_HOME
ORACLE_TERM=xterm; export ORACLE_TERM
PATH=${PATH}:$ORACLE_HOME/bin/ export PATH
LD_LIBRARY_PATH=$ORACLE_HOME/lib:/lib:/usr/lib; export LD_LIBRARY_PATH
if [ $USER = "oracle" ]; then
if [ $SHELL = "/bin/ksh" ]; then
ulimit -p 16384
ulimit -n 65536
else
ulimit -u 16384 -n 65536
fi
fi
lsnrctl stop listenerdev
lsnrctl stop listenerint
lsnrctl stop listenerfat1
lsnrctl stop listenerfat2
#lsnrctl stop listenerret
lsnrctl stop listenerdem1
lsnrctl stop listenersat1
#echo FOPSTEST db shutdown `date` >> $ORACLE_HOME/db.log
dbshut
#echo FOPSTEST db shutdown completed `date` >> $ORACLE_HOME/db.log
++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
----------------------------all_db_start.sh------------------------------
30 4 * * 0 /data/app/oracle/product/10.2.0/db_1/all_db_start.sh
# .bash_profile
# Get the aliases and functions
if [ -f ~/.bashrc ]; then
. ~/.bashrc
fi
# User specific environment and startup programs
TMP=/tmp; export TMP
ORACLE_HOME_LISTENER=$ORACLE_BASE ;export ORACLE_HOME_LISTENER
TMPDIR=$TMP; export TMPDIR
ORACLE_BASE=/data/app/oracle; export ORACLE_BASE
ORACLE_HOME=$ORACLE_BASE/product/10.2.0/db_1; export ORACLE_HOME
ORACLE_TERM=xterm; export ORACLE_TERM
PATH=${PATH}:$ORACLE_HOME/bin/ export PATH
LD_LIBRARY_PATH=$ORACLE_HOME/lib:/lib:/usr/lib; export LD_LIBRARY_PATH
if [ $USER = "oracle" ]; then
if [ $SHELL = "/bin/ksh" ]; then
ulimit -p 16384
ulimit -n 65536
else
ulimit -u 16384 -n 65536
fi
fi
lsnrctl start listenerdev
lsnrctl start listenerint
lsnrctl start listenerfat1
lsnrctl start listenerfat2
#lsnrctl start listenerret
lsnrctl start listenerdem1
lsnrctl start listenersat1
#echo FOPSTEST db startup `date` >> $ORACLE_HOME/db.log
dbstart
#echo FOPSTEST db startup completed `date` >> $ORACLE_HOME/db.log
------------------------------------------------------------
Tuesday, November 17, 2009
Oracle SDU and TDU parameters in listener.ora and tnsnames.ora
Oracle SDU and TDU parameters in listener.ora and tnsnames.ora
The session data unit (SDU) specifies the size of the packets to send over the network. The maximum transmission unit (MTU) is a fixed value that depends on the actual network implementation used. Ideally, SDU should not surpass the size of the MTU. Oracle recommends that SDU be set equal to the MTU. The tnsnames.ora and listener.ora files house the SDU and TDU parameters.
To group data together, the TDU value is the default packet size used within Oracle*Net. The default value for both SDU and TDU is 2,048 bytes and the maximum value is 32,767 bytes. The TDU parameter should ideally be a multiple of the SDU parameter. For SDU and TDU, the following guidelines apply:
On fast network connections such as T1 or T3 lines, SDU and TDU should be set equal to the MTU for the network. On standard Ethernet networks, the default MTU size should be set to 1,514 bytes. On standard token ring networks, the default MTU size is 4,202 bytes.
If the users are connecting via dial-up modem lines, it may be desirable to set SDU and TDU to smaller values in consideration of the frequent resends that occur over modem connections.
The mts_dispatchers must also be set with the proper MTU - TDU configuration, if a Multi-Threaded Server (MTS) is used.
As a rule, SDU should not be set greater than the TDU because network resources will be wasted by shipping wasted space in each packet.
This brief review of network-related parameters was intended provide an introduction to the scope and complexity of network tuning. It is important to understand that Oracle*Net is simply a layer in the OSI model that is above the network-specific protocol stack, and therefore, virtually all network tuning is external to Oracle.
Ex: -
Doc ID: Note:71921.1
Subject: ORA-12151, ORA-12571 errors on Windows NT
Type: BULLETIN
Status: PUBLISHED
Content Type: TEXT/PLAIN
Creation Date: 09-JUN-1999
Last Revision Date: 07-FEB-2002
PURPOSE
Give an overview of what to adjust and verify in case of ORA-12151
and ORA-12571 errors
SCOPE AND APPLICATION
This notes applies to all who are facing intermittent SQL*Net read and
write errors while working on NT.
==============================================================================
ORA-12151 and ORA-12571 errors on Windows NT
Intermittent SQL*Net TCP/IP read and write errors are sometimes encountered
on NT. The underlying reasons of these errors are a synchronization error
in the TCP/IP layer on NT. To help prevent this kind of errors, a
few things can be adjusted to help the synchronization:
1. TCP.NODELAY parameter
This parameter is to be added in the "PROTOCOL.ORA" file in the
"NETWORK\\ADMIN" directory.
In most cases, TCP/IP info send across the network is buffered
till at least a complete network packet can be send. This means
that in certain cases commands are not issued directly, and kept
buffered until some other info can be send as well.
This has the potential to generate timeouts and errors.
To avoid this, this delay can be switched off.
tcp.nodelay = yes
2. Disabling AUTOMATIC_IPC on Clients
On client PC's, checking for IPC connections is pointless as there
is never a database installed on them. So, in order to save some
time during the connections phase, set AUTOMATIC_IPC=OFF in the
"SQLNET.ORA" file.
3. "NAMES.DIRECTORY_PATH" to force using "TNSNAMES.ORA" or "ONAMES"
If you have a fixed environment, it's best to specify this in the
"SQLNET.ORA" file. The parameter "NAMES.DIRECTORY_PATH" specifies how
the TNS resolving takes place.
By default, if this parameter is not present - the SQL*Net layer
will first check if there is a Names server anywhere on the network,
and will afterwards start checking for a "TNSNAMES.ORA" file.
If you have only a "TNSNAMES.ORA" file, and no Oracle Names installed,
it is best to specify this parameter to skip the Names searching in
order to speed up the TNS resolving time.
The value of this parameter is a comma separated list, with as
possible values TNSNAMES (for "TNSNAMES.ORA" searching) and "ONAMES"
(for Oracle Names searching).
4. TCP/IP timeouts on NT
The default retransmission count on NT is 5, before it detects that
the network is down. With the value of 5, the actual timeout is
aproximately 15 seconds.
This default value can be easily increased to a higher value.
In the registry, change the following value:
HKEY_LOCAL_MACHINE
System
CurrentControlSet
Services
TCP/IP
Parameters
TcpMaxDataRetransmissions REG_DWORD "number"
This parameter is not present in the registry by default. This
means that the first time, this parameter will need to added to
this registry key.
This parameter can be useful on both client and server. Suggested
course of action is to first add this parameter on the machine
generating the SQL*Net errors, and if the problem persists, also
include the parameter in the registry of the other machine.
5. TCP/IP keepalive on NT
KEEPALIVE is an extension to TCP/IP which enables the closing of
dead connections which are no longer being used.
Problems can occur when the server does not close a connection
after a PC client process has disappeared. This typically happens
when a PC user switches off or reboots their machine while still
connected to Oracle. Note that this is not an Oracle problem, but
a limitation of TCP/IP, which has no way of knowing for sure
whether a remote connection has disappeared.
This feature is enabled by default on NT. Problem can occur however
if the timeout period is too fast for some heavily used or slow
network. In those conditions, the KEEPALIVE registry value can be
used to specify a KEEPALIVE value before a connection gets cut.
HKEY_LOCAL_MACHINE
System
CurrentControlSet
Services
TCP/IP
Parameters
KeepAlive REG_DWORD "number"
A value of '10' is a common value specified for this variable.
Again, this parameter can be useful on both client and server.
Start with the machine generating the error, and if needed, also add
it on the machine on the other side.
6. TCP/IP timeouts on Windows 95
The same parameter can also be specified on Windows 95. It has the
same functionality, only the location of the parameter in the
registry is different.
HKEY_LOCAL_MACHINE
System
CurrentControlSet
Services
Winsock
Parameters
TcpMaxDataRetransmissions REG_DWORD "number"
This parameter is not present in the registry by default. This
means that the first time, this parameter will need to added to
this registry key.
The purpose and behavior of the parameter is the same on the Windows 95
and Windows 98, as on the Windows NT platform.
7. SDU & TDU parameters
Part of this problem is the sequence of information that gets transmitted.
If there are disruptions in the sequence, the errors ORA-12151 and
ORA-12571 can also appear, alerting the application that not all information
has been send across the network succesfully.
The sequence of information is determined by the amount of data the program
is sending, and the actual size the protocol can send across the network
at a time.
The more data the program wants to send in one 'go', the more sequences and
transport packet split-ups will have to be made.
By default, SQL*Net is using an SDU (Session Data Unit) of 2048 bytes (2Kb)
and a TDU (Transport Data Unit) of 32768 (32Kb) bytes.
On standard Ethernet connections, with no special modifications made, the
SDU is 1500 bytes, while the TDU is 8760 bytes.
With these values, each data request made by SQL*Net will have to be split
up in several smaller packets to actually be able to transmit.
Therefore, to minize the creation the additional packets, it is advised, in
case of these errors, to synchronize the SDU and TDU parameters at the
SQL*Net level with those of the actual network topology used.
These SDU and TDU parameters have to be specified at both the client and
the server configuration files:
TNSNAMES.ORA:
-------------
ORCL.WORLD =
(DESCRIPTION =
(SDU=1500)
(TDU=8760)
(ADDRESS_LIST =
(ADDRESS =(PROTOCOL=TCP)(Host=foobar)(Port=1521))
)
(CONNECT_DATA =
(SID = ORCL)
)
)
LISTENER.ORA:
-------------
SID_DESC_LISTENER =
(SID_LIST =
(SID_DESC =
(SDU = 1500)
(TDU = 8760)
(SID_NAME = ORCL)
)
)
For more information about the SDU and TDU parameter, see Note 44694.1,
Note 44694.1: SQL*Net Packet Sizes (SDU & TDU Parameters)
8. Setting a new TDU size on Windows NT
You can modify the TDU size on NT, via the TcpWindowSize parameter:
HKEY_LOCAL_MACHINE
System
CurrentControlSet
Services
Tcpip
Parameters
TcpWindowSize REG_DWORD "number"
Additional information about the NT network parameters on NT:
-------------------------------------------------------------
Q120642: TCP/IP & NBT Configuration Parameters for Windows NT
http://support.microsoft.com/support/kb/articles/Q120/6/42.asp
Q140375: Default MTU Size for Different Network Topology
http://support.microsoft.com/support/kb/articles/Q140/3/75.asp
The session data unit (SDU) specifies the size of the packets to send over the network. The maximum transmission unit (MTU) is a fixed value that depends on the actual network implementation used. Ideally, SDU should not surpass the size of the MTU. Oracle recommends that SDU be set equal to the MTU. The tnsnames.ora and listener.ora files house the SDU and TDU parameters.
To group data together, the TDU value is the default packet size used within Oracle*Net. The default value for both SDU and TDU is 2,048 bytes and the maximum value is 32,767 bytes. The TDU parameter should ideally be a multiple of the SDU parameter. For SDU and TDU, the following guidelines apply:
On fast network connections such as T1 or T3 lines, SDU and TDU should be set equal to the MTU for the network. On standard Ethernet networks, the default MTU size should be set to 1,514 bytes. On standard token ring networks, the default MTU size is 4,202 bytes.
If the users are connecting via dial-up modem lines, it may be desirable to set SDU and TDU to smaller values in consideration of the frequent resends that occur over modem connections.
The mts_dispatchers must also be set with the proper MTU - TDU configuration, if a Multi-Threaded Server (MTS) is used.
As a rule, SDU should not be set greater than the TDU because network resources will be wasted by shipping wasted space in each packet.
This brief review of network-related parameters was intended provide an introduction to the scope and complexity of network tuning. It is important to understand that Oracle*Net is simply a layer in the OSI model that is above the network-specific protocol stack, and therefore, virtually all network tuning is external to Oracle.
Ex: -
Doc ID: Note:71921.1
Subject: ORA-12151, ORA-12571 errors on Windows NT
Type: BULLETIN
Status: PUBLISHED
Content Type: TEXT/PLAIN
Creation Date: 09-JUN-1999
Last Revision Date: 07-FEB-2002
PURPOSE
Give an overview of what to adjust and verify in case of ORA-12151
and ORA-12571 errors
SCOPE AND APPLICATION
This notes applies to all who are facing intermittent SQL*Net read and
write errors while working on NT.
==============================================================================
ORA-12151 and ORA-12571 errors on Windows NT
Intermittent SQL*Net TCP/IP read and write errors are sometimes encountered
on NT. The underlying reasons of these errors are a synchronization error
in the TCP/IP layer on NT. To help prevent this kind of errors, a
few things can be adjusted to help the synchronization:
1. TCP.NODELAY parameter
This parameter is to be added in the "PROTOCOL.ORA" file in the
"NETWORK\\ADMIN" directory.
In most cases, TCP/IP info send across the network is buffered
till at least a complete network packet can be send. This means
that in certain cases commands are not issued directly, and kept
buffered until some other info can be send as well.
This has the potential to generate timeouts and errors.
To avoid this, this delay can be switched off.
tcp.nodelay = yes
2. Disabling AUTOMATIC_IPC on Clients
On client PC's, checking for IPC connections is pointless as there
is never a database installed on them. So, in order to save some
time during the connections phase, set AUTOMATIC_IPC=OFF in the
"SQLNET.ORA" file.
3. "NAMES.DIRECTORY_PATH" to force using "TNSNAMES.ORA" or "ONAMES"
If you have a fixed environment, it's best to specify this in the
"SQLNET.ORA" file. The parameter "NAMES.DIRECTORY_PATH" specifies how
the TNS resolving takes place.
By default, if this parameter is not present - the SQL*Net layer
will first check if there is a Names server anywhere on the network,
and will afterwards start checking for a "TNSNAMES.ORA" file.
If you have only a "TNSNAMES.ORA" file, and no Oracle Names installed,
it is best to specify this parameter to skip the Names searching in
order to speed up the TNS resolving time.
The value of this parameter is a comma separated list, with as
possible values TNSNAMES (for "TNSNAMES.ORA" searching) and "ONAMES"
(for Oracle Names searching).
4. TCP/IP timeouts on NT
The default retransmission count on NT is 5, before it detects that
the network is down. With the value of 5, the actual timeout is
aproximately 15 seconds.
This default value can be easily increased to a higher value.
In the registry, change the following value:
HKEY_LOCAL_MACHINE
System
CurrentControlSet
Services
TCP/IP
Parameters
TcpMaxDataRetransmissions REG_DWORD "number"
This parameter is not present in the registry by default. This
means that the first time, this parameter will need to added to
this registry key.
This parameter can be useful on both client and server. Suggested
course of action is to first add this parameter on the machine
generating the SQL*Net errors, and if the problem persists, also
include the parameter in the registry of the other machine.
5. TCP/IP keepalive on NT
KEEPALIVE is an extension to TCP/IP which enables the closing of
dead connections which are no longer being used.
Problems can occur when the server does not close a connection
after a PC client process has disappeared. This typically happens
when a PC user switches off or reboots their machine while still
connected to Oracle. Note that this is not an Oracle problem, but
a limitation of TCP/IP, which has no way of knowing for sure
whether a remote connection has disappeared.
This feature is enabled by default on NT. Problem can occur however
if the timeout period is too fast for some heavily used or slow
network. In those conditions, the KEEPALIVE registry value can be
used to specify a KEEPALIVE value before a connection gets cut.
HKEY_LOCAL_MACHINE
System
CurrentControlSet
Services
TCP/IP
Parameters
KeepAlive REG_DWORD "number"
A value of '10' is a common value specified for this variable.
Again, this parameter can be useful on both client and server.
Start with the machine generating the error, and if needed, also add
it on the machine on the other side.
6. TCP/IP timeouts on Windows 95
The same parameter can also be specified on Windows 95. It has the
same functionality, only the location of the parameter in the
registry is different.
HKEY_LOCAL_MACHINE
System
CurrentControlSet
Services
Winsock
Parameters
TcpMaxDataRetransmissions REG_DWORD "number"
This parameter is not present in the registry by default. This
means that the first time, this parameter will need to added to
this registry key.
The purpose and behavior of the parameter is the same on the Windows 95
and Windows 98, as on the Windows NT platform.
7. SDU & TDU parameters
Part of this problem is the sequence of information that gets transmitted.
If there are disruptions in the sequence, the errors ORA-12151 and
ORA-12571 can also appear, alerting the application that not all information
has been send across the network succesfully.
The sequence of information is determined by the amount of data the program
is sending, and the actual size the protocol can send across the network
at a time.
The more data the program wants to send in one 'go', the more sequences and
transport packet split-ups will have to be made.
By default, SQL*Net is using an SDU (Session Data Unit) of 2048 bytes (2Kb)
and a TDU (Transport Data Unit) of 32768 (32Kb) bytes.
On standard Ethernet connections, with no special modifications made, the
SDU is 1500 bytes, while the TDU is 8760 bytes.
With these values, each data request made by SQL*Net will have to be split
up in several smaller packets to actually be able to transmit.
Therefore, to minize the creation the additional packets, it is advised, in
case of these errors, to synchronize the SDU and TDU parameters at the
SQL*Net level with those of the actual network topology used.
These SDU and TDU parameters have to be specified at both the client and
the server configuration files:
TNSNAMES.ORA:
-------------
ORCL.WORLD =
(DESCRIPTION =
(SDU=1500)
(TDU=8760)
(ADDRESS_LIST =
(ADDRESS =(PROTOCOL=TCP)(Host=foobar)(Port=1521))
)
(CONNECT_DATA =
(SID = ORCL)
)
)
LISTENER.ORA:
-------------
SID_DESC_LISTENER =
(SID_LIST =
(SID_DESC =
(SDU = 1500)
(TDU = 8760)
(SID_NAME = ORCL)
)
)
For more information about the SDU and TDU parameter, see Note 44694.1,
Note 44694.1: SQL*Net Packet Sizes (SDU & TDU Parameters)
8. Setting a new TDU size on Windows NT
You can modify the TDU size on NT, via the TcpWindowSize parameter:
HKEY_LOCAL_MACHINE
System
CurrentControlSet
Services
Tcpip
Parameters
TcpWindowSize REG_DWORD "number"
Additional information about the NT network parameters on NT:
-------------------------------------------------------------
Q120642: TCP/IP & NBT Configuration Parameters for Windows NT
http://support.microsoft.com/support/kb/articles/Q120/6/42.asp
Q140375: Default MTU Size for Different Network Topology
http://support.microsoft.com/support/kb/articles/Q140/3/75.asp
Subscribe to:
Posts (Atom)