Wednesday, June 9, 2010

Date Format in oracle

Date Formats of Oracle Language
==============================
==============================


Format mask Description
========== ==============
CC : Century
SCC : Century BC prefixed with -
YYYY :Year with 4 numbers
SYYY :Year BC prefixed with -
IYYY :ISO Year with 4 numbers
YY :Year with 2 numbers
RR :Year with 2 numbers with Y2k compatibility
YEAR :Year in characters
SYEAR :Year in characters, BC prefixed with -
BC :BC/AD Indicator *
Q :Quarter in numbers (1,2,3,4)
MM :Month of year 01, 02...12
MONTH :Month in characters (i.e. January)
MON :JAN, FEB
WW :Weeknumber (i.e. 1)
W :Weeknumber of the month (i.e. 5)
IW :Weeknumber of the year in ISO standard.
DDD :Day of year in numbers (i.e. 365)
DD :Day of the month in numbers (i.e. 28)
D :Day of week in numbers(i.e. 7)
DAY :Day of the week in characters (i.e. Monday)
FMDAY :Day of the week in characters (i.e. Monday)
DY :Day of the week in short character description (i.e. SUN)
J :Julian Day (number of days since January 1 4713 BC, where January 1 4713 BC is 1 in Oracle)
HH :Hournumber of the day (1-12)
HH12 :Hournumber of the day (1-12)
HH24 :Hournumber of the day with 24Hours notation (0-23)
AM :AM or PM
PM :AM or PM
MI :Number of minutes (i.e. 59)
SS :Number of seconds (i.e. 59)
SSSSS :Number of seconds this day.
DS :Short date format. Depends on NLS-settings. Use only with timestamp.
DL :Long date format. Depends on NLS-settings. Use only with timestamp.
E :Abbreviated era name. Valid only for calendars: Japanese Imperial, ROC Official and Thai Buddha.. (Input-only)
EE :The full era name
FF :The fractional seconds. Use with timestamp.
FF1..FF9 ;The fractional seconds. Use with timestamp. The digit controls the number of decimal digits used for fractional seconds.
FM :Fill Mode: suppresses blianks in output from conversion
FX :Format Exact: requires exact pattern matching between data and format model.
IYY or IY or I :the last 3,2,1 digits of the ISO standard year. Output only
RM :The Roman numeral representation of the month (I .. XII)
RR :The last 2 digits of the year.
RRRR :The last 2 digits of the year when used for output. Accepts fout-digit years when used for input.
SCC :Century. BC dates are prefixed with a minus.
CC :Century
SP :Spelled format. Can appear of the end of a number element. The result is always in english. For example month 10 in format MMSP returns "ten"
SPTH :Spelled and ordinal format; 1 results in first.
TH :Converts a number to it's ordinal format. For example 1 becoms 1st.
TS :Short time format. Depends on NLS-settings. Use only with timestamp.
TZD :Abbreviated time zone name. ie PST.
TZH :Time zone hour displacement.
TZM :Time zone minute displacement.
TZR :Time zone region
X :Local radix character. In america this is a period (.)

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

Some Examples:
-----------------------

SQL>
SQL>
SQL> select to_char(sysdate,'CC') from dual;

TO
--
21

SQL>
SQL> select to_char(sysdate,'YYYY') from dual;

TO_C
----
2010

SQL>
SQL> select to_char(sysdate,'YEAR') from dual;

TO_CHAR(SYSDATE,'YEAR')
------------------------------------------
TWENTY TEN

SQL>
SQL> select to_char(sysdate,'MONTH') from dual;

TO_CHAR(S
---------
JUNE

SQL>
SQL> select to_char(sysdate,'BC') from dual;

TO
--
AD

SQL>
SQL> select to_char(sysdate,'RM') from dual;

TO_C
----
VI

SQL>
SQL> select to_char(sysdate,'Q') from dual;

T
-
2

SQL>

Tuesday, June 8, 2010

Oracle 10g installation on sun solaries 5.10 (spark) for myself

installation serial
=========================

1. oracle software 10.2.0.1
2. oracle companion
3 oracle patchset for 10.2.0.4


================================================
Checking Requirement:
================================================
#/usr/sbin/prtconf | grep “Memory size” [Check RAM size]
# /usr/sbin/swap -s [check swap]
# df -k /tmp [check /tmp size (>400mb)]
# uname -r [check solaris version]

# pkginfo -i SUNWarc SUNWbtool SUNWhea SUNWlibm SUNWlibms SUNWsprot SUNWsprox SUNWtoo SUNWi1of SUNWi1cs SUNWi15cs SUNWxwfnt

# cat /etc/nsswitch.conf | grep hosts
# hostname
# domainname


Determine oracle user exist or not
====================================
# id -a oracle


#mkdir /export/home/oracle
#chown oracle:dba /export/home/oracle

set password=
=======================
root# passwd -r files oracle

=======================================================
Verify that the owner, group, and mode of the /oracle/oracle/product/10.2.0/bin/oracle file
are correct.
============================================================

# ls -l /oracle/oracle/product/10.2.0/bin/oracle

-rwsr-s--x 1 oracle dba 3195 Apr 27 2005 oracle

-----------------------------------------------------

-------------------------------------------------------

Prevent the Oracle cssd daemon from being started.

Remove the following entry from the /etc/inittab file. This action prevents
unnecessary error messages from being displayed.

h1:23:respawn:/etc/init.d/init.cssd run >/dev/null 2>&| >

------------------------------------------------------------


configuration Start from here .................................
.................................................................


===========================
Unzip the files:
==========================

root# unzip 10202_database_solx86.zip

(You should now have a single directory called "database" containing installation files.)


==================================
/etc/hosts
==================================
root@BADB2 #
root@BADB2 # cat /etc/hosts
#
# Internet host table
#
::1 localhost
127.0.0.1 localhost
10.11.2.254 BADB8
root@BADB2 #

[note:
]
==================================system==============================
root@BADB2 # cat /etc/system
-------
------
[note: For EMC they can do it ]


set md:mirrored_root_flag=1
*** Begin EMCpower added lines *** DO NOT EDIT BELOW THIS LINE ***
forceload: drv/emcpsf
forceload: drv/sd
forceload: drv/ssd
forceload: drv/emcp
forceload: misc/emcpmpx
forceload: misc/emcpsapi
forceload: misc/emcpcg
forceload: misc/emcpgpx
forceload: misc/emcpdm
forceload: misc/emcpioc
set emcp:bPxEnableInit=1
*** End EMCpower added lines *** DO NOT EDIT ABOVE THIS LINE ***
-----------------------
[NOTE: Bellow is for our

set noexec_user_stack=1
set semsys:seminfo_semmni=100
set semsys:seminfo_semmsl=256
set shmsys:shminfo_shmmax=42949672960
set shmsys:shminfo_shmmni=100


root@BADB2 #

[note:- 40 GB ]
======================================================================
Create the new groups and users:
======================================================================

root# groupadd oinstall
root# groupadd dba
root# useradd -g oinstall -G dba -d /export/home/oracle oracle
root# mkdir /export/home/oracle
root# chown -R oracle:oinstall /export/home/oracle
root# passwd -r files oracle
root# projadd oracle

======================================================================
Append the following line to the "/etc/user_attr" file:
======================================================================

root# vi /etc/user_attr

oracle::::project=oracle


==================================project==============================
root@BADB2 #
root@BADB2 # cat /etc/project
system:0::::
user.root:1::::
noproject:2::::
default:3::::
group.staff:10::::
oracle:100::::project.max-shm-memory=(priv,28991029248,deny)
root@BADB2 #
root@BADB2 # projmod -s -K "project.max-shm-memory=(priv,42949672960,deny)" oracle
root@BADB2 #
root@BADB2 #
root@BADB2 # cat /etc/project
system:0::::
user.root:1::::
noproject:2::::
default:3::::
group.staff:10::::
oracle:100::::project.max-shm-memory=(priv,42949672960,deny)
root@BADB2 #
root@BADB2 #

[ Note: 40 GB Memory RAM in Project]


=========================================================
To determine whether the required packages are installed:
=========================================================

root# pkginfo -i SUNWarc SUNWbtool SUNWhea SUNWlibm SUNWlibms SUNWsprot \
SUNWsprox SUNWtoo SUNWi1of SUNWi1cs SUNWi15cs SUNWxwfnt

root# pkgadd -d /cdrom/sol_10_106_x86/Solaris_10/Product SUNWi1cs SUNWi15cs


===========================================================================
Create oracle home (added by halim)
==========================================================================


Create the directories in which the Oracle software will be installed:

oracle$ mkdir -p /d02/oracle/ora102

chown -R oracle:oinstall /d02

==========================================================================
====================ORACLE_HOME$ CAT .profile=======================================
root@BADB2 #
root@BADB2 #
root@BADB2 # su - oracle
Sun Microsystems Inc. SunOS 5.10 Generic January 2005
You have new mail.
$
$ bash
bash-3.00$ pwd
/export/home/oracle
bash-3.00$
bash-3.00$ cat .profile
ORACLE_OWNER=oracle; export ORACLE_OWNER
ORACLE_BASE=/d04; export ORACLE_BASE
ORACLE_HOME=$ORACLE_BASE/oracle/ora102; export ORACLE_HOME
PATH=$ORACLE_HOME/bin:$HOME/bin:/usr/bin/X11:/usr/bin/X11R6:/usr/local/sbin:/sbin:/bin:/emc/EMCpower/reloc/EMCpower/bin/sparcv9:/etc/emc/bin:/usr/bin:/usr/sbin:/usr/local/bin:/usr/openwin/bin:.; export PATH
ORACLE_PATH=$ORACLE_HOME/bin:.; export ORACLE_PATH
LD_LIBRARY_PATH=$ORACLE_HOME/lib; export LD_LIBRARY_PATH
LD_LIBRARY_PATH_64=$ORACLE_HOME/lib; export LD_LIBRARY_PATH_64
CLASSPATH=$CLASSPATH:$ORACLE_HOME/JRE:$ORACLE_HOME/jlib:$ORACLE_HOME/rdbms/jlib:$ORACLE_HOME/network/jlib; export CLASSPATH
ORACLE_SID=stlbas; export ORACLE_SID
ORACLE_TERM=xterm; export ORACLE_TERM
TNS_ADMIN=$ORACLE_HOME/network/admin; export TNS_ADMIN
set TWO_TASK $ORACLE_HOME/bin
TMP=/tmp; export TMP
TEMP=/tmp; export TEMP
TMPDIR=$TMP; export TMPDIR
ORAENV_ASK=NO

bash-3.00$

[note :- umask 022
why umask 022 need to know ]

=================================
Install the oracle software
=================================

Log into the oracle user. If you are using X emulation then set the DISPLAY environmental variable:

DISPLAY=:0.0; export DISPLAY


==============================================================
Performing the Oracle Installation
==============================================================

root# su – oracle
oracle$ xclock
oracle$ xhost +
oracle$ export DISPLAY=localhost;0.0
oracle$ xhost + >>run this if you install from remote PC
oracle$ cd /export/home/database/ [the source unzipped here]

oracle$ ./runInstaller

it prerequist check problem happen then

oracle$ ./runInstaller -ignoreSysPrereqs


============================
post installation work
===========================
================================================================
In "/var/opt/oracle/oratab" file modify for auto start Database:
================================================================

root# vi /var/opt/oracle/oratab

stlbas:/d02/oracle/ora102:Y


=========================================================
Create a file "/etc/init.d/dbstart" with following lines:
=========================================================

root# vi /etc/init.d/dbstart


# Start the Oracle Database and listeners
echo
echo "################################################################################"
echo "# Begin of O R A C L E startup section #"
echo "################################################################################"

echo
echo " WELCOME TO BANK'S DATABASE"
echo


su - oracle<
lsnrctl start

sqlplus /nolog<connect / as sysdba
startup
EOS

echo
echo " Database started..."
echo

#emctl start dbconsole
#isqlplusctl start

EOO

echo
echo "################################################################################"
echo "# End of O R A C L E section #"
echo "################################################################################"
echo


========================================================
Create a file "/etc/init.d/dbstop" with following lines:
========================================================

root# vi /etc/init.d/dbstop


# Stop the Oracle Database and linteners
echo
echo "################################################################################"
echo "# Begin of O R A C L E startup section #"
echo "################################################################################"


echo
echo " HAVE A NICE DAY"
echo


su - oracle<
lsnrctl stop

sqlplus /nolog<connect / as sysdba
shutdown immediate

EOS

echo
echo " Bank's database stoped..."
echo

#emctl stop dbconsole
#isqlplusctl stop

EOO

echo
echo "################################################################################"
echo "# End of O R A C L E section #"
echo "################################################################################"
echo


===================
Some UNIX Commands:
===================

root# ps -ef

root# ls -al

root# rm

root# rm -R

root# mv [from] [to]

root# cp [from] [to]

root# mkdir

root# rmdir

root# df -k [Display mount point]

root# mount [Display mounted device]

root# umount

root# cat

root# kill -9 PID

root# prtconf|grep -i mem [Display RAM size]

root# ipcs -pmb [Display allocated memory segment]

root# psrinfo -v [Display the no of CPU]

root# sar -u 2 5 [Display CPU report, 1st argument time interval in second, 2nd argument no. of sample]

root# sar -w 5 5 [Memory switching and swaping]

root# sar -b 1 6 [buffer activity]

root# vmstat 5 3 [Various server values, 1st argument time interval in second, 2nd argument no. of sample]

root# mpstat 10 2 [per processor report, 1st argument time interval in second, 2nd argument no. of sample]

root# w [Server load average]

root# iostat 3 [I/O against physical disk in time interval]

===============
NFS Mount (Sun)
===============

The following deamons must be running for the share to be seen by a PC:
-----------------------------------------------------------------------

root# /usr/lib/nfs/nfsd -a
root# /usr/lib/nfs/mountd
root# /opt/SUNWpcnfs/sbin/rpc.pcnfsd


To see a list of the nfs mounted drives already present type:
-------------------------------------------------------------

root# exportfs

First the mount point must be shared so it can be seen by remote machines:
--------------------------------------------------------------------------

root# share F nfs o ro /cdrom

Next the share can be mounted on a remote machine by root using:
----------------------------------------------------------------

root# mkdir /cdrom#1

root# mount o ro myhost:/cdrom /cdrom#1

============
Useful Files
============

/etc/passwd [User settings]
/etc/group [Group settings for users]
/etc/hosts [Hostname lookup information]
/etc/system [Kernel parameters]




If you do not know the correct port number to use, look for
the following line in the
===========================================================

$ORACLE_HOME/install/portlist.ini file.


patchset for 10.2.0.4 on solaries (oracle software 10.2.0.1 is abailable for solaries
======================================================================================


p3731593_10204_SOLARIS64.zip is the patchset 10.2.0.4 for Enterprise Manager Grid Control,
correct patchset is:

6810189
Skip Headers
Oracle® Database Patch Set Notes
10g Release 2 (10.2.0.4) Patch Set 3 for Solaris Operating System (SPARC 64-Bit)

BEFORE installing a patchset it's always a good idea to read the README file.

Werner

-------------------------------------------------

Shutdown
====================
You have more than one command option that you can use. The best command is this, executed as root:

shutdown -y -i5 -g0

This will immediately shut the system down. You can also use the older command that still works:

sync;sync;init 5

You can even use:

poweroff

Reboot
==================================
If you are trying to reboot the system as opposed to turning it off, you could use:

shutdown -y -i6 -g0

Or:

sync;sync;init 6

Or even:

reboot

So many commands to do the same thing… almost seems silly.




Monday, June 7, 2010

WARNING: inbound connection timed out (ORA-3136)

WARNING: inbound connection timed out (ORA-3136)


WARNING: inbound connection timed out (ORA-3136)
Mon Jun 7 15:03:02 2010
Errors in file /d04/admin/stlbas/bdump/stlbas_j000_21589.trc:
ORA-07445: exception encountered: core dump [ksuklms()+672] [SIGSEGV] [Address not mapped to object] [0x000000062] [] []
Mon Jun 7 15:03:04 2010
WARNING: inbound connection timed out (ORA-3136)
Mon Jun 7 15:03:06 2010
WARNING: inbound connection timed out (ORA-3136)
Mon Jun 7 15:05:04 2010
WARNING: inbound connection timed out (ORA-3136)
Mon Jun 7 15:10:10 2010
Thread 1 advanced to log sequence 53804 (LGWR switch)
Current log# 1 seq# 53804 mem# 0: /d01/oracle/oradata/stlbas/redo01.log
Mon Jun 7 15:35:13 2010
Timed out trying to start process P643.
Mon Jun 7 15:37:32 2010
Timed out trying to start process PA55.
Mon Jun 7 15:39:00 2010
Timed out trying to start process PE56.
Mon Jun 7 15:40:13 2010
Timed out trying to start process PH77.
Mon Jun 7 15:44:49 2010
Error occured while spawning process PO17; error = 3135
Mon Jun 7 15:46:58 2010
Timed out trying to start process PO79.
Mon Jun 7 15:47:01 2010
Timed out trying to start process PO84.
Mon Jun 7 15:47:02 2010


We facing this error in our Database

-----------------------------------
Explanation of this problem:
-----------------------------------

This problem can occur on any platform, When you use Oracle Net services - Version 10.2 up.

Because; The Oracle Net 10G parameter
"SQLNET.INBOUND_CONNECT_TIMEOUT and
INBOUND_CONNECT_TIMEOUT_listenername default to 0 in 10.1.
To address Denial of Service (DOS) issues, the parameter were set to have a default of 60 (seconds) in 10.2

The following may be seen in the alert log:

WARNING: inbound connection timed out (ORA-3136)

SQLNET.INBOUND_CONNECT_TIMEOUT is set to a value in seconds and determines how long a client has to provide the necessary authentication information to a database.

INBOUND_CONNECT_TIMEOUT_listenername is set to a value in seconds and determines how long a client has to complete its connect request to the listener after the network connection has been established.

To protect both the listener and the database server, Oracle Corporation recommends setting INBOUND_CONNECT_TIMEOUT_listenername in combination with the SQLNET.INBOUND_CONNECT_TIMEOUT parameter.

Cause:
======
Whenever default timeouts are assigned to a parameter, there may be cases where this default does not work well with a particular application. However, some type of timeout on the connection establishment is necessary to combat Denial of Service attacks on the database. In this case, SQLNET.INBOUND_CONNECT__TIMEOUT and INBOUND_CONNECT_TIMEOUT_listenername were given default values of 60 seconds in Oracle 10.2. It is these timeout values that can cause the errors described in this note.

Also note that it is possilbe the reason the database is slow to authenticate, may be due to an overloaded Oracle database or node.

Solution:
=======
Set the parameters SQLNET.INBOUND_CONNECT_TIMEOUT and INBOUND_CONNECT_TIMEOUT_listenername to 0 (indefinite) or to an approprate value for the application yet still combat DOS attacks (120 for example).

These parameters are set on the SERVER side:
listener.ora: INBOUND_CONNECT_TIMEOUT_listenername
sqlnet.ora: SQLNET.INBOUND_CONNECT_TIMEOUT



Another cause may be
---------------------------
Maximum processes exceed, so no new connection occurs......

First find out the parameter values by this query:-

sessions
processes
transactions

select * from v$resource_limit where RESOURCE_NAME in ('sessions','processes','transactions');


This is the formula, to calculate their values
------------------------------------------------
processes = a
sessions =a*1.1+5
transactions =sessions*1.1

example:-

processes=150
sessions=170
transactions= 187

Change the value in parameter file:-

SQL> alter system set processes=1000 scope=spfile
/

SQL>alter system set transactions=1215 scope=spfilE
/

note:- sessions value automatically change after shutdown the db.

SHUTDOWN THE DATABASE
AND
START THE DATABASE

Sunday, June 6, 2010

ADRCI (Automatic Diagnostic Repository Command Interface)

Automatic Diagnostic Repository
================================


ADR ( In Oracle 11g)
========
ADR (Automatic Diagnostic Repository) is part of the Oracle database's fault diagnosability
infrastructure, first introduced with Oracle 11g, to reduce time for resolving
problems (round trip time between DBA's and Oracle Support).

DBA's can change the ADR directory by changing the DIAGNOSTIC_DEST initialization parameter.

The following subdirectories are present in the ADR home directory:

alert - The XML formatted alert log file (called log.xml)
trace - files and text alert.log file
cdump - core files

cmd> ADRCI


adrci> help

HELP [topic]
Available Topics:
CREATE REPORT
ECHO
EXIT
HELP
HOST
IPS
PURGE
RUN
SET BASE
SET BROWSER
SET CONTROL
SET ECHO
SET EDITOR
SET HOMES | HOME | HOMEPATH
SET TERMOUT
SHOW ALERT
SHOW BASE
SHOW CONTROL
SHOW HM_RUN
SHOW HOMES | HOME | HOMEPATH
SHOW INCDIR
SHOW INCIDENT
SHOW PROBLEM
SHOW REPORT
SHOW TRACEFILE
SPOOL

There are other commands intended to be used directly by Oracle, type
"HELP EXTENDED" to see the list

adrci> show base
ADR base is "d:\oracle11g_app_upgrade\era-1"
adrci>
adrci>
adrci> show incdir

ADR Home = d:\oracle11g_app_upgrade\era-1\diag\rdbms\beftn\beftn:
*************************************************************************
adrci>
adrci> show report
DIA-48415: Syntax error found in string [show report
] at column [12]

adrci> show alert

ADR Home = d:\oracle11g_app_upgrade\era-1\diag\rdbms\beftn\beftn:
*************************************************************************
Output the results to file: c:\docume~1\era-1\locals~1\temp\alert_4316_3868_beft
n_1.ado
'vi' is not recognized as an internal or external command,
operable program or batch file.
adrci>
adrci>
adrci>



ADRCI :=
=============================
(Automatic Diagnostic Repository Command Interface) is a command line utility of ADR
that ships with the Oracle database software.

ORA-00600: internal error code

ORA-00600: internal error code
====================================

Today i face a problem. problem is, when i am tring to update a table with oracle form6i
whenever I am pressing F10(save), first time it saves but after the second time
it hang and shows message "Connection timeout....."
at the same time i found in alertlog the follwoing error message..

"ORA-00600: internal error code"

At that time i searching the oracle Forms code and found there have many variable
are assign by dual table like :-

select trunc(sysdate)
into v_date
from dual ;


whenever i replace all dual table assignment by directly

then the problem is out.....................



ORA-00600 is an internal error
================================

ORA-600 is an internal error generated by the generic kernel code of the Oracle RDBMS software.


1. Each ORA-00600 error generates a database trace file.
You can find in Details about the application code involve with this error.



2. Each ORA-00600 error comes with a list of arguments They usually enclosed in
square brackets and follow the error on the same line for example:

ORA-00600 [14000][51202][1][51200][][]

Each argument has a specific meaning which can only be interpreted by an Oracle
support analyst. The arguments may also change meaning from version to version.



Possible causes
========================
Possible causes include:

1. time-outs,
2. file corruption,
3. failed data checks in memory, hardware, memory, or I/O messages,
4. incorrectly restored files
5. SELECT FROM DUAL statement in PL/SQL within Oracle Forms (you have to use SELECT FROM SYS.DUAL instead!)



How to fix it
========================
Contact Oracle Support with the following information:

1. events that led up to the error
2. the operations that were attempted that led to the error
3. the conditions of the operating system and database at the time of the error
4. any unusual circumstances that occurred prior to receiving the ORA-00600 message.
5. contents of any trace files generated by the error
6. the relevant portions of the Alert file
7. in Oracle Forms PL/SQL, use SELECT FROM SYS.DUAL to access the system "dual" table




Another way may be it will be fix
===================================

The BUG is fixed in 10.1.0.4 Server Patch Set


ORA-00600: internal error code, arguments: [17183], [0xA2C0044], [], [], [], [], [], []

It is reported as BUG.

It can be found in Oracle 9i,10gr2.

The BUG is fixed in 10.1.0.4 Server Patch Set

Kindly Refer Metalink Note: 3350337.8



Another problem like this and solution
=======================================

ORA-00600: internal error code, arguments: [kjhn_post_ha_alert0-862], [], [], [], [], [], [], []

ORA-600 [kjhn_post_ha_alert0-862] Continuously Repeated in the Alert Log - 401640.1


metalink Doc ID: 401640.1

solutions:
-----------

1. Log in as SYS
2. SQL> truncate table recent_resource_incarnations$
3. Shutdown DB
4. Startup DB