SQL*Loader: Release 11.2.0.3.0 - Production on Wed Jun 8 12:57:43 2016

Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.

SQL*Loader-704: Internal error: ulmtsyn: OCIStmtExecute(tabhp) [1480]

ORA-01480: trailing null missing from STR bind value

Table was of more than 30 character in control file.

Table name corrected and loading was successful.

No input file specified

404 error suggests that there is no file... in my case i saved file test.php.txt ... after saving test.php worked for me but here is really good discussion....

http://jenseng.com/archives/000035.html

[ora1@RHEL1 ~]$ sqlplus
sqlplus: error while loading shared libraries: libsqlplus.so: cannot open shared object file: No such file or directory
[ora1@RHEL1 ~]$

i believe that this error comes when u r running sqlplus from user other than oracle. for this (10.2.0.1) you have to run following script and that is included in Patch 4516865

put both files in $ORACLE_HOME/install/ directory.
n
1. cd $ORACLE_HOME/install
2. Run changePerm.sh script.

then press y

Oracle editor in Linux

simple is the way.

open file at
oraclehome/sqlplus/admin/glogin.sql

add an entry
define_editor=vi

ora-12537: TNS connection closed

one of my solaris and linuxEL5 host name changed and i was faceing this problem(ora-12537). i just opened hosts file added entry
IP hostname localhost

and solved the problem...

SQL*Loader-704: Internal error: ulconnect: OCIServerAttach [0]

faced the following error and solution is very very simple
C:\>sqlldr gcon_user control='E:\test_ctl.ctl'
Password:
SQL*Loader: Release 10.2.0.1.0 - Production on Wed Jul 7 11:11:45 2010
Copyright (c) 1982, 2005, Oracle. All rights reserved.
SQL*Loader-704: Internal error: ulconnect: OCIServerAttach [0]
ORA-12560: TNS:protocol adapter error
as the last line show that (ora-12560) its sid is not set. please use for windows at cmd
set ORACLE_SID=YOUR_SID
for linux
ORACLE_SID=YOUR_SID

Cheers.

Oracle auto start Script for Solaris x86 / Solaris 10

We have 10+ oracle 10g instances on Solaris different versions, I have worked alot with linux(EL, AS, Fedora4-9), but now days I am working with Solaris......
task is to configure oracle autostartup.

i made a file at /etc/rc3.d/S98oradbstart

-------------------------------------------------------------------------------------
#!/bin/sh
#
#echo "Starting Oracle Database" > /dev/consoleORACLE_HOME=/u01/app/oracle/product/10.2.0/db_1
LD_LIBRARY_PATH=$ORACLE_HOME/lib:$LD_LIBRARY_PATHexport ORACLE_HOME LD_LIBRARY_PATH
su oracle -c "${ORACLE_HOME}/bin/dbstart" < /dev/nullexit 0
-------------------------------------------------------------------------------------
now either you will run level 3 or 5 your oracle plus listener will be started automatically.
Here was some error that i faced.
1.
/u01/app/oracle/product/10.2.0/db_1/bin/dbstart: VER10LIST=10: is not an identifier
i looked into file /u01/app/oracle/product/10.2.0/db_1/bin/dbstart
and changed
this export VER10LIST=`$ORACLE_HOME_LISTNER/bin/lsnrctl version grep "LSNRCTL for " cut -d' ' -f5 cut -d'.' -f1`
to

VER10LIST=`$ORACLE_HOME_LISTNER/bin/lsnrctl version grep "LSNRCTL for " cut -d' ' -f5 cut -d'.' -f1`; export VER10LIST

then faced
2. “`COUNT=$’ unexpected” at or near line 259
i changed in dbstart scropt
from
COUNT=$((COUNT+1))toCOUNT=`expr $COUNT + 1`

Now life is good.

Failed to allocate port(s) in the specified range(s) for the following process(es):

Failed to allocate port(s) in the specified range(s) for the following process(es): JMS [5540-5559],RMI [5520-5539],Database Control [5500-5519],EM Agent [3938] [1830-1849]
Refer to the log file at /u01/app/oracle/product/10.2.0/db_1/cfgtoollogs/emca/orcl/emca_2008-03-12_03-42-45-PM.log for more details.
Could not complete the configuration. Refer to the log file at /u01/app/oracle/product/10.2.0/db_1/cfgtoollogs/emca/orcl/emca_2008-03-12_03-42-45-PM.log for more details.
I just added ip-address machinename in /etc/hosts file

TNS-00584: Valid node checking configuration error

Hi guys,

wonderful error duing my jubb.
"TNS-00584: Valid node checking configuration error"
I was asked from my repected developer to add his IP to my Database server,
I just edited my sqlnet.ora file with his ip againt " tcp.invited_nodes " and restared the listener, and i got "Error listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=dbserver)(PORT=1521)))TNS-12560: TNS:protocol adapter error TNS-00584: Valid node checking configuration error"

hmmm this error was due to my wrong entry in sqlnet.ora file, there was a dot(.) was missing in IP address. I just corrected it and started the linstener ...


Now all is well,


cheers.

ORA-00313: open failed for members of log group

Errors in file /u01/app/oracle/admin/ORCL/bdump/orcl_arc1_7586.trc:
ORA-00313: open failed for members of log group 4 of thread 1.

I encountered this error when group 4 was current and i just added a log member in this group.
After that I make too many log switch with
alter database switch logfile;
and not got any errors.

due to frequent log switches I got "Thread 1 cannot allocate new log, sequence 161
Private strand flush not complete"
This issue is infact not a bug and is expected behavior.
This is the same as "Checkpoint not completed" this is due to that all the redo entries from buffers have not been just written to redo files.
Strand is the new technology in 10g that deals with redo latches. this machnism allows multiple redo buffer latches to be written in redo, same parallel redo writing in 9i.

ref.Doc ID: Note:372557.1
A good discussion .....
http://www.lazydba.com/oracle/0__26265.html

Oracle Enterprise Manager

~

Installation of Oracle express on Fedora 8

Hi,
Last day i did installation on Fedora 8, so what I did is here below...

check entry in /etc/hosts file for localhost.localdomain it should be like this
IP address localhost.localdomain localhost
there are two packages that must be installed before Oracle Ex installation......those are
# rpm -ivh libaio-*warning: libaio-0.3.106-3.2.i386.rpm: Header V3 DSA signature: NOKEY, key ID 4f2a6fd2Preparing... ########################################### [100%] 1:libaio ########################################### [ 33%] 2:libaio-devel ########################################### [ 67%] 3:libaio warning: user brewbuilder does not exist - using rootwarning: group brewbuilder does not exist - using rootwarning: user brewbuilder does not exist - using rootwarning: group brewbuilder does not exist - using root########################################### [100%]
# Kernel sysctl configuration file for Red Hat Linux## For binary values, 0 is disabled, 1 is enabled. See sysctl(8) and# sysctl.conf(5) for more details.
# Controls IP packet forwardingnet.ipv4.ip_forward = 0
# Controls source route verificationnet.ipv4.conf.default.rp_filter = 1# Do not accept source routingnet.ipv4.conf.default.accept_source_route = 0# Controls the System Request debugging functionality of the kernelkernel.sysrq = 0# Controls whether core dumps will append the PID to the core filename.# Useful for debugging multi-threaded applications.kernel.core_uses_pid = 1# Controls the use of TCP syncookiesnet.ipv4.tcp_syncookies = 1kernel.shmall = 2097152kernel.shmmax = 536870912kernel.shmmni = 4096# semaphores: semmsl, semmns, semopm, semmnikernel.sem = 250 32000 100 128fs.file-max = 65536net.ipv4.ip_local_port_range = 1024 65000
# vi /etc/sysctl.conf # # # # # # rpm -ivh oracle-xe-univ-10.2.0.1-1.0.i386.rpmPreparing... ########################################### [100%] 1:oracle-xe-univ ########################################### [100%]Executing Post-install steps...You must run '/etc/init.d/oracle-xe configure' as the root user toconfigure the database.
Now configure Oracle for
port number for Oracle Application Express and for listener.auto-startup on recycling
# /etc/init.d/oracle-xe configure
Oracle Database 10g Express Edition Configuration-------------------------------------------------This will configure on-boot properties of Oracle Database 10g ExpressEdition. The following questions will determine whether the database shouldbe starting upon system boot, the ports it will use, and the passwords thatwill be used for database accounts. Press to accept the defaults.Ctrl-C will abort.
Specify the HTTP port that will be used for Oracle Application Express [8080]:8080
Specify a port that will be used for the database listener [1521]:1521
Specify a password to be used for database accounts. Note that the samepassword will be used for SYS and SYSTEM. Oracle recommends the use ofdifferent passwords for each database account. This can be done afterinitial configuration:Confirm the password:
Do you want Oracle Database 10g Express Edition to be started on boot (y/n) [y]:y
Starting Oracle Net Listener...DoneConfiguring Database...DoneStarting Oracle Database 10g Express Edition Instance...DoneInstallation Completed Successfully.To access the Database Home Page go to "http://127.0.0.1:8080/apex"

So installation completed i just added oracle envirnment variables in .bash_profile.

Rest is good!

ORA-00600: internal error code, arguments: [keltnfy-ldmInit], [46], [1], [], [], [], [], []

Hi,
After a long time I have an error of ora-00600.
One of my Database is Oracle Express10.2.0.1.0

Cause:
In log we were receiving these errors.
Oracle Server was not resolving HOSTNAME.

Mon Jan 7 14:58:48 2008Errors in file /usr/lib/oracle/xe/app/oracle/admin/XE/udump/xe_ora_2462.trc:ORA-00600: internal error code, arguments: [keltnfy-ldmInit], [46], [1], [], [], [], [], []USER: terminating instance due to error 600

Solution:
added an entry for localhost in /etc/hosts.
open /etc/hosts file
vi /etc/hosts
:0:1 localhost.localdomain localhost
alter this to ....
127.0.0.1 localhost.localdomain localhost
or
ip_of_your_DBbox localhost.localdomain localhost


after that now check

-bash-3.1$ sqlplus
SQL*Plus: Release 10.2.0.1.0 - Production on Mon Jan 7 15:05:56 2008
Copyright (c) 1982, 2005, Oracle. All rights reserved.
Enter user-name: /as sysdba
Connected to:Oracle Database 10g Express Edition Release 10.2.0.1.0 - Production
SQL> select count(*) from tab;
COUNT(*)
----------
3535


So, Be Happy!

Oracle Database 10g Release 2 (10.2.0.1) Installation On Red Hat Enterprise Linux 5.1 (RHEL5)

Oracle Database 10g Release 2 (10.2.0.1) Installation On Red Hat Enterprise Linux 5.1 (RHEL5)

In this document I'll describe the installation of Oracle Database 10g Release 2 (10.2.0.1) on Red Hat Enterprise Linux 5 (RHEL5). With a minimum of 2G swap, secure Linux disabled and the following package groups installed:
GNOME Desktop Environment
Editors
Graphical Internet
Text-based Internet
Development Libraries
Development Tools
Legacy Software Development
Server Configuration Tools
Administration Tools
Base
Legacy Software Support
System Tools
X Window System
Alternative installations may require more packages to be loaded, in addition to the ones listed below.
Get Software
Unpack Files
Hosts File
Set Kernel Parameters
Setup
Installation
Post Installation
1. Get Software
Download the software from www.oracle.com:
Oracle Database 10g Release 2 (10.2.0.1) Software
2. Unpack Files
Unzip the files:
unzip 10201_database_linux32.zip
You should now have a single directory containing installation files. Depending on the age of the download this may either be named "database".
3. Hosts File
The /etc/hosts file must contain a fully qualified name for the server:
XXX.XXX.XXX.XXX TESTBOX.domainname TESTBOX

4. Set Kernel Parameters
Add the following lines to the /etc/sysctl.conf file:
#kernel.shmall = 2097152#kernel.shmmax = 2147483648kernel.shmmni = 4096# semaphores: semmsl, semmns, semopm,
semmnikernel.sem = 250 32000 100 128
#fs.file-max =65536
net.ipv4.ip_local_port_range = 1024 65000
net.core.rmem_default=262144
net.core.rmem_max=262144
net.core.wmem_default=262144
net.core.wmem_max=262144
Run the following command to change the current kernel parameters:
/sbin/sysctl -p
Add the following lines to the /etc/security/limits.conf file:
* soft nproc 2047* hard nproc 16384* soft nofile 1024* hard nofile 65536
Add the following line to the /etc/pam.d/login file, if it does not already exist:
session required /lib/security/pam_limits.so
Disable secure linux by editing the /etc/selinux/config file, making sure the SELINUX flag is set as follows:
SELINUX=disabled
Alternatively, this alteration can be done using the GUI tool (System > Administration > Security Level and Firewall). Click on the SELinux tab and disable the feature.
5. Setup
Install the following packages:
cd /media/cdrom/Serverrpm -Uvh setarch-2*rpm -Uvh make-3*rpm -Uvh glibc-2*binutilscompat-dbcompat-libstdc++control-centergccgcc-c++glibcglibc-commongnome-libslibstdc++libstdc++-develmakepdksh (RHEL 5 ships with ksh instead)sysstatxscreensaversetarchlibXp (to start runInstaller)
For checking user rpm -q and use rpm –Uvh switch and install all these packages.

Create the new groups and users:
--
# groupadd dba
# groupadd oinstall
# useradd -G dba -d /home/oracle -g oinstall oracle
# passwd oracle
--
Create the directories in which the Oracle software will be installed:
mkdir -p /u01/app/oracle/product/10.2.0/db_1chown -R oracle.oinstall /u01
Login as root and issue the following command:
xhost +
Edit the /etc/redhat-release file replacing the current release information (Red Hat Enterprise Linux Server release 5.1 (Tikanga)) with the following:
redhat-4
Login as the oracle user and add the following lines at the end of the .bash_profile file:
# Oracle SettingsTMP=/tmp; export TMPTMPDIR=$TMP; export TMPDIR ORACLE_BASE=/u01/app/oracle; export ORACLE_BASEORACLE_HOME=$ORACLE_BASE/product/10.2.0/db_1; export ORACLE_HOMEORACLE_SID=TSH1; export ORACLE_SIDORACLE_TERM=xterm; export ORACLE_TERMPATH=/usr/sbin:$PATH; export PATHPATH=$ORACLE_HOME/bin:$PATH; export PATH LD_LIBRARY_PATH=$ORACLE_HOME/lib:/lib:/usr/lib; export LD_LIBRARY_PATHCLASSPATH=$ORACLE_HOME/JRE:$ORACLE_HOME/jlib:$ORACLE_HOME/rdbms/jlib; export CLASSPATH

6. Installation
Log into the oracle user. If you are using X emulation then set the DISPLAY environmental variable:
DISPLAY=:0.0; export DISPLAY
Start the Oracle Universal Installer (OUI) by issuing the following command in the database directory:
./runInstaller
During the installation enter the appropriate ORACLE_HOME and name then continue installation. For a more detailed look at the installation process, click on the links below to see screen shots of each stage.
Select Installation Method
Specify Inventory Directory and Credentials
Select Installation Type
Specify Home Details
Product-Specific Prerequisite Checks
Select Configuration Option
Select Database Configuration
Specify Database Configuration Options
Select Database Management Option
Specify Database Storage Option
Specify Backup and Recovery Options
Specify Database Schema Passwords
Summary
Install
Configuration Assistants
Database Configuration Assistant
Database Configuration Assistant Password Management
Execute Configuration Scripts
End Of Installation
7. Post Installation
Edit the /etc/redhat-release file restoring the original release information:
Red Hat Enterprise Linux Server release 5.1 (Tikanga)
Edit the /etc/oratab file setting the restart flag for each instance to 'Y':
TSH1:/u01/app/oracle/product/10.2.0/db_1:Y


References:
http://wiki.ittoolbox.com/index.php/Installation_of_Oracle_database_10g_on_RHEL_5
http://www.oracle-base.com/articles/10g/OracleDB10gR2InstallationOnRHEL5.php

ORA-00980: synonym translation is no longer valid

ORA-00980: synonym translation is no longer valid
Cause:
The synonym used is based on a table, view, or synonym that no longer exists.
Action:
Replace the synonym with the name of the object it references or re-create the synonym so that it refers to a valid table, view, or synonym.

This problem can come when you make an operation on a synonym that has no base object.
for retification this problem you can recreate synonym with proper base object.

Memory Notification: Library Cache Object loaded into SGA

Hi,

I was getting "Memory Notification: Library Cache Object loaded into SGA". If you search the web or metalink account then can find healty knowledge.

If you are using spfile then
alter system set "_kgl_large_heap_warning_threshold"=8388608 scope=spfile ;

If you are using pifle then just add this parameter.
_kgl_large_heap_warning_threshold=8388608

shutdown immediate;

startup from this new pifle.


Cheers

dbca -silent

Hi,
Today I fell with dbca ..... (database configuration assistant)

dbca -silent -createDatabase -templateName "New Database" -gdbName orcl -sid orcl -sysPassword "orcl" -systemPassword "orcl" -emConfiguration NONE -dbsnmpPassword "orcl" -sysmanPassword "orcl" -datafileDestination /u01/app/oracle/orcl/
Database is an invalid command line argument.
Database is an invalid command line argument.


Oracle stores .dbc files against templates. so go for those.......

[root@imran oracle]# find / -name *.dbc -print
/u01/app/oracle/product/10.2.0/db_1/assistants/dbca/templates/Data_Warehouse.dbc
/u01/app/oracle/product/10.2.0/db_1/assistants/dbca/templates/General_Purpose.dbc
/u01/app/oracle/product/10.2.0/db_1/assistants/dbca/templates/Transaction_Processing.dbc

[oracle@imran oracle]$ dbca -silent -createDatabase -templateName /u01/app/oracle/product/10.2.0/db_1/assistants/dbca/templates/Data_Warehouse.dbc -gdbName orcl -sid orcl -sysPassword "orcl" -systemPassword "orcl" -emConfiguration NONE -dbsnmpPassword "orcl" -sysmanPassword "orcl" -datafileDestination /u01/app/oracle/orcl/

Copying database files
1% complete
3% complete
11% complete
18% complete
26% complete
33% complete
37% complete
Creating and starting Oracle instance
40% complete
45% complete
50% complete
55% complete
56% complete
60% complete
62% complete
Completing Database Creation
66% complete
70% complete
73% complete
85% complete
96% complete
100% complete
Look at the log file "/u01/app/oracle/product/10.2.0/db_1/cfgtoollogs/dbca/orcl/orcl.log" for further details.

This was a happy day for me :)

ORA-01102: and ORA-16014:

SQL> startup pfile='/u01/app/oracle/product/10.2.0/db_1/dbs/initorcl.ora';
ORACLE instance started.
Total System Global Area 83886080 bytes
Fixed Size 1217836 bytes
Variable Size 75500244 bytes
Database Buffers 4194304 bytes
Redo Buffers 2973696 bytes
ORA-01102: cannot mount database in EXCLUSIVE mode
Cause:
Can be that you are trying to startup second instance with privously opened database.
If you have only one database and gets this error then that means you are trying to open an opened database!.


2.
Total System Global Area 83886080 bytes
Fixed Size 1217836 bytes
Variable Size 75500244 bytes
Database Buffers 4194304 bytes
Redo Buffers 2973696 bytes
Database mounted.ORA-16014: log 1 sequence# 188 not archived, no available destinationsORA-00312: online log 1 thread 1: '/u01/app/oracle/pkstg102/redo01.log'

Cause: you archives are of size equal to your database parameter DB_RECOVERY_FILE_DEST_SIZE. get rid of this error just increase this parameter's value.

SQL> alter system set DB_RECOVERY_FILE_DEST_SIZE = 6g;
System altered.
SQL> alter database open;
Database altered.

Automatic Oracle 10g export using cron job.

Hi,
Today, I want to share shell script and steps for automatic export using Oracle 10g datapump.
As Oracle says that datapump is fater than imp/exp 45%. and I admit as I experienced it.
Agenda: automatic export (Oracle says it "logical backup") On Unix/Linux Machine.
Steps:
1. Make directory in oracle (it is mendatory for impdp/expdp).
2. Give read, write privileges on directoy to user.
3. Shell script.
4. Configurations.

1.
connect to oracle as "as sysdba"


I will complete it on monday hopefully....

StatsPack Installation and Use.

Hi,

Today I have installed statpack on a customer production machine for performance checking.
Here are steps.
1. Create tablespace for perfstat user. (optional)

CREATE TABLESPACE perfstat DATAFILE ' path_for_datafile_&_datafile_name.dbf ' SIZE 400M ;

2. Install statspack
$ cd $ORACLE_HOME/rdbms/admin$ sqlplus "/ as sysdba"
SQL> @spcreate.sql
*It will prompt you for perfstat user password, its default tablespace, its temporary tablespace.
** At the end you will receive
No errors.
NOTE:SPCPKG complete. Please check spcpkg.lis for any errors.
*** If there are some errors, then go for spcpkg.lis file.

3. Now time to take snap!
SQL> execute statspack.snap;

PL/SQL procedure successfully completed.

* After 10minutes take another snap!

SQL> execute statspack.snap;

PL/SQL procedure successfully completed.

4. Now get the Report!
SQL> @spreport.sql
* You will get

Listing all Completed Snapshots
SnapInstance DB Name Snap Id Snap Started Level Comment
------------ ------------ --------- ----------------- ----- --------------------
ORCL ORCL 1 29 Nov 2007 02:40 5
2 29 Nov 2007 02:49 5

Specify the Begin and End Snapshot Ids
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Enter value for begin_snap: 1
Begin Snapshot Id specified: 1
Enter value for end_snap: 2
End Snapshot Id specified: 2

*At the end you will name the report.
*Now Take report n enjoy.