Creating spatial index.

First of all , We need to insert a value in MDSYS.USER_SDO_GEOM_METADATA


Creating spatial index syntax.


CREATE INDEX ADDRESSES_SIDX ON ADDRESS
(OGC_GEOMETRY)
INDEXTYPE IS MDSYS.SPATIAL_INDEX
PARAMETERS('layer_gtype=point')
NOPARALLEL;


give gtype as your spatial value


In the above example gtype is point.Means 2001 in table.

Installaing ORACLE 11G R2 in Centos 5 (Minimum requirement)

*****************************

Pre-Instalation Tasks
1. Create oracle User Account

Login as root and create te user oracle which belongs to dba group.

su -
# groupadd dba
# useradd -g dba oracle
# passwd oracle - give password

2. Setting System parameters -for 4+ GB RAM
********************************************


Edit the /etc/sysctl.conf and add following lines:
kernel.shmall = 2097152
kernel.shmmax = 2147483648
kernel.shmmni = 4096
kernel.sem = 250 32000 100 128
fs.file-max = 6553600
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
fs.aio-max-nr = 1048576

2. Setting System parameters -for 2 GB RAM
********************************************
kernel.shmall = 2097152
kernel.shmmax = 1258291200
kernel.shmmni = 4096
kernel.sem = 250 32000 100 128
fs.file-max = 8388608
net.ipv4.ip_local_port_range = 9000 65500
net.core.rmem_default = 4194304
net.core.rmem_max =4194304
net.core.wmem_default = 262144
net.core.wmem_max = 2097152
fs.aio-max-nr = 1048576

Note: You need reboot system or execute "sysctl -p" command to apply above settings.

For RedHat (OEL, Centos) 5 version: Edit the /etc/pam.d/login file and add following line:
session required pam_limits.so

Edit the /etc/security/limits.conf file and add following lines:
oracle soft nproc 2047
oracle hard nproc 16384
oracle soft nofile 1024
oracle hard nofile 65536

3. Creating oracle directories
# mkdir /opt/oracle
# mkdir /opt/oracle/db_1
# chown -R oracle:dba /opt/oracle


Must Log in as oracle user
*************************

4. Setting Oracle Enviroment
Edit the /home/oracle/.bash_profile file and add following lines:
ORACLE_BASE=/opt/oracle
ORACLE_HOME=$ORACLE_BASE/db_1
ORACLE_SID=ORCL
LD_LIBRARY_PATH=$ORACLE_HOME/lib
PATH=$PATH:$ORACLE_HOME/bin

export ORACLE_BASE ORACLE_HOME ORACLE_SID LD_LIBRARY_PATH PATH


Save the .bash_profile and execute following commands for load new enviroment:
cd /home/oracle
. .bash_profile

1. Download and install required .rpm packages

Login as Root user in terminal
******************************

Some additional packages are required for succesful instalation of Oracle software. To check wheter required packages are installed on your operating system use following command:

rpm -q binutils elfutils elfutils-libelf gcc gcc-c++ glibc glibc-common glibc-devel compat-libstdc++-33 cpp make compat-db sysstat libaio libaio-devel unixODBC unixODBC-devel|sort

We get the list of not installed packages
*****************************************

package compat-db is not installed
package compat-libstdc++-33 is not installed
package elfutils is not installed
package gcc-c++ is not installed
package gcc is not installed
package glibc-devel is not installed
package libaio-devel is not installed
package sysstat is not installed
package unixODBC-devel is not installed
package unixODBC is not installed

Give the following command to install each of them

# yum install compat-db
# yum install elfutils

giving permission to oracle installation folder and the home folder at time of installation.

# chmod -R 777 /opt/oracle11g/

# chmod -R 777 /opt/

Logout and Login in GUI as Oracle User
*************************************

Go to the Instalation setup file path and execute the following command

./runInstaller

Post-Instalation Tasks
1. (Optional) Auto Startup and Shutdown of Database and Listener

Login as root and modify /etc/oratab file and change last character to Y for apropriate database.
ORCL:/opt/oracle/db_1:Y

As root user create new file "oracle" (init script for startup and shutdown the database) in /etc/init.d/ directory with following content:

#!/bin/bash
#
# oracle Init file for starting and stopping
# Oracle Database. Script is valid for 10g and 11g versions.
#
# chkconfig: 35 80 30
# description: Oracle Database startup script

# Source function library.

. /etc/rc.d/init.d/functions

ORACLE_OWNER="oracle"
ORACLE_HOME="/opt/oracle/db_1"

case "$1" in
start)
echo -n $"Starting Oracle DB:"
su - $ORACLE_OWNER -c "$ORACLE_HOME/bin/dbstart $ORACLE_HOME"
echo "OK"
;;
stop)
echo -n $"Stopping Oracle DB:"
su - $ORACLE_OWNER -c "$ORACLE_HOME/bin/dbshut $ORACLE_HOME"
echo "OK"
;;
*)
echo $"Usage: $0 {start|stop}"
esac

Execute (as root) following commands (First script change the permissions, second script is configuring execution for specific runlevels):

chmod 750 /etc/init.d/oracle
chkconfig --add oracle --level 0356

ORA-12520: TNS:listener could not find available handler for requested type of server

Got this error.


Try to connect your database server directly.Definitely you will get this error.

Enter user-name: sys as sysdba
Enter password:
ERROR:
ORA-00020: maximum number of processes (250) exceeded
you need to down your listener

Try to login directly using sqlplus

If that not possible

Then ... shutdown your db service

After that increase your number of processes
by

SQL>ALTER SYSTEM SET PROCESSES=350 SCOPE=SPFILE;

you must shutdown after this commands for changing this parameter.

kewastUnPackStats(): bad magic 1 (0x2b2b8f4652ad, 0)

Found the error in rdbms alert log...?

It generates thousands of lines per day which will expand the alert log size.

We can override this error by

ALTER SYSTEM SET control_management_pack_access='NONE' SCOPE=MEMORY;

Otherwise we can download new patches from Metalink.

Cheers...!!!

ORA-00119: invalid specification for system parameter LOCAL_LISTENER

ORA-00119: invalid specification for system parameter LOCAL_LISTENER
ORA-00132: syntax error or unresolved network name 'LISTENER_ORA11G'
you need to check your tnsnames.ora file.In this above example.You can see "LISTENER_ORA11G" that is actually a service name from tnsnames.ora file.Check that service name configuration in tnsnames.ora.

Cheers.!!!

ORA-00845: MEMORY_TARGET not supported on this system

Got this error...?


This type of error depends on your server memory allocated to your database.You need to change your memory_target.

Alter system set memory_target= 2000M scope=both;



cheers!!!

The OracleDBConsolev8it3 service terminated with service-specific error 1 (0x1).

Got this error?

The OracleDBConsolev8it3 service terminated with service-specific error 1 (0x1).



emca -config dbcontrol db

SEVERE: Failed to unlock all EM-related accounts


Could not complete the configuration. Refer to the log file for more details

It`s a common error. you need to drop dbconsole first.after that you can recreate the same


1)DROP DBCONSOLE first

emca -deconfig dbcontrol db -repos drop

then

2)CREATE NEW REPOSITORY

emca -repos create

emca -config dbcontrol db


It will take sometime to create new repository.Also you can see the path of EM at end of the execution.

Restart EM Console


emctl stop dbconsole db

emctl start dbconsole db



Cheers!!!