Script to start and shutdown Oracle Database 11g R2 automatically while reboot

• Edit the oratab file and add or update the instance name as shown below.
  for single or multiple instances.

[root@murthy ~]# vi /etc/oratab
# This file is used by ORACLE utilities.  It is created by root.sh
# and updated by the Database Configuration Assistant when creating
# a database.

# A colon, ':', is used as the field terminator.  A new line terminates
# the entry.  Lines beginning with a pound sign, '#', are comments.
#
# Entries are of the form:
#   $ORACLE_SID:$ORACLE_HOME::
#
# The first and second fields are the system identifier and home
# directory of the database respectively.  The third filed indicates
# to the dbstart utility that the database should , "Y", or should not,
# "N", be brought up at system boot time.
#
# Multiple entries with the same $ORACLE_SID are not allowed.
#
#
orcl:/u01/app/oracle/product/11.2.0/db_1:Y
oradb:/u01/app/oracle/product/11.2.0/db_1:Y

• Edit the bash_profile and update the ORACLE_SID

[root@murthy ~]# vi  /home/oracle/.bash_profile
# add your SID at the last line
export ORACLE_SID=orcl

• Create init script for Oracle

[root@murthy ~]# vi /etc/rc.d/init.d/oracle


#!/bin/bash
# oracle: Start/Stop Oracle Database 11g R2
#
# chkconfig: 345 90 10
# description: The Oracle Database is an Object-Relational Database Management System.
#
# processname: oracle

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

LOCKFILE=/var/lock/subsys/oracle
ORACLE_HOME=/u01/app/oracle/product/11.2.0/db_1
ORACLE_USER=oracle

case "$1" in
'start')
   if [ -f $LOCKFILE ]; then
      echo $0 already running.
      exit 1
   fi
   echo -n $"Starting Oracle Database:"
   su - $ORACLE_USER -c "$ORACLE_HOME/bin/lsnrctl start"
   su - $ORACLE_USER -c "$ORACLE_HOME/bin/dbstart $ORACLE_HOME"
   su - $ORACLE_USER -c "$ORACLE_HOME/bin/emctl start dbconsole"
   touch $LOCKFILE
   ;;
'stop')
   if [ ! -f $LOCKFILE ]; then
      echo $0 already stopping.
      exit 1
   fi
   echo -n $"Stopping Oracle Database:"
   su - $ORACLE_USER -c "$ORACLE_HOME/bin/lsnrctl stop"
   su - $ORACLE_USER -c "$ORACLE_HOME/bin/dbshut"
   su - $ORACLE_USER -c "$ORACLE_HOME/bin/emctl stop dbconsole"
   rm -f $LOCKFILE
   ;;
'restart')
   $0 stop
   $0 start
   ;;
'status')
   if [ -f $LOCKFILE ]; then
      echo $0 started.
      else
      echo $0 stopped.
   fi
   ;;
*)
   echo "Usage: $0 [start|stop|status]"
   exit 1
esac

exit 0

• Give the permission to the scripts

[root@murthy ~]# chmod 755 /etc/rc.d/init.d/oracle
[root@murthy ~]# /etc/rc.d/init.d/oracle start/status

• Add the service using below commands

[root@murthy ~]#chkconfig --add oracle
[root@murthy ~]#chkconfig oracle on

ORA-01000: maximum open cursors exceeded

The OPEN_CURSORS parameter is set to the maximum number of cursors that each session can open at a time.
Example, if the value of OPEN_CURSORS is set to 1000, then each session can have up to 1000 cursors open at one time.

If the number of cursors in the database exceeds the maximum limit then you will get the following error:

java.sql.SQLException: ORA-00604: error occurred at recursive SQL level 1
ORA-01000: maximum open cursors exceeded.

Cause : The initialization parameter OPEN_CURSORS determines the maximum number of cursors per user.

Action: Modify the program to use fewer cursors.
compare the parent and child table rows, if both table rows are not matched then you may get this error
further you can check the following steps:

Login to the schema with DBA privilege of the database.

To find out the session that is causing the error using the below SQL statement:

SELECT A.VALUE, S.USERNAME, S.SID, S.SERIAL# FROM V$SESSTAT A, V$STATNAME B, V$SESSION S WHERE A.STATISTIC# = B.STATISTIC#  AND S.SID=A.SID AND B.NAME = 'OPENED CURSORS CURRENT' AND S.USERNAME IS NOT NULL;

you will get the output as details of all sessions. You can see the maximum out session IDs.

you can see the sql_text and session_id by run the following SQL statement which are causing maxing out of open cursors,

SELECT  SID ,SQL_TEXT, COUNT(*) AS "OPEN CURSORS", USER_NAME FROM V$OPEN_CURSOR GROUP BY SID ,SQL_TEXT ;  OR
SELECT  SID ,SQL_TEXT, COUNT(*) AS "OPEN CURSORS", USER_NAME FROM V$OPEN_CURSOR WHERE SID IN ($SID);($SID = SESIONID IF YOU KNOW)

If some code is running with above SQL queries, then check that Java Statement, Resultset, or connection are closing properly or not.

To verify the maximum OPEN_CURSORS parameter run the following sql statement, or monitor v$sesstat for the maximum opened cursors current:

SELECT  MAX(A.VALUE) AS HIGHEST_OPEN_CUR, P.VALUE AS MAX_OPEN_CUR FROM V$SESSTAT A, V$STATNAME B, V$PARAMETER P WHERE  A.STATISTIC# = B.STATISTIC#  AND B.NAME = 'OPENED CURSORS CURRENT' AND P.NAME= 'OPEN_CURSORS' GROUP BY P.VALUE;

If your sessions are running close to the limit, then increase the value of the OPEN_CURSORS parameter by running the following sql statement and restart Oracle:

ALTER SYSTEM SET OPEN_CURSORS = 500 SCOPE=BOTH;

ORA-02298 – parent keys not found

SQL> ALTER TABLE EMP_DET ADD CONSTRAINT EMP_DET_FK FOREIGN KEY ( NAME1 ) REFERENCES EMP( NAME1 );
ALTER TABLE EMP_DET ADD CONSTRAINT EMP_DET_FK FOREIGN KEY ( NAME1 ) REFERENCES EMP( NAME1 )
*
ERROR at line 1:
ORA-02298: cannot validate (HR.EMP_DET_FK) – parent keys not found

Cause :  This will show you the row (or rows) that exists in the child table that
don't exist in the parent table

Action  :  You have to either (added rows to the parent table which is already there in the child rows) or (removed the child rows that don't have parent table), then you can enable your Referential integrity constraints.

you can modify the below query as per your requirement and execute to know the missing rows.
 
SELECT NAME FROM SCHEMA.CHILD_TABLE A
WHERE NOT EXISTS (SELECT NAME FROM SCHEMA.PARENT_TABLE WHERE PRIMARY_KEY=A.KEY);

Tablespace Management

Tablespace Management:
-----------------------------
A tablespace is a logical group of data-files in a database.
A database is divided into one or more logical storage called tablespace. A tablespace must be at least one datafile,which is physically located in the system.

by default oracle creates system,sysaux datafile at the time of database creation.
Using sql statements we can create,alter and drop the tablespace.

1.Create tablespace Syntax :

CREATE TABLESPACE DATAFILE <'/LOCATION/'> SIZE AUTOEXTEND ON;

EX : CREATE TABLESPACE TEST DATAFILE '/DISK1/ORADATA/ORCL/TEST.DBF' SIZE 100M AUTOEXTEND ON;

Tablespace views:

Desc DBA_TABLESPACE
Desc DBA_DATA_FILES
Desc DBA_TEMP_FILES
Desc DBA_FREE_SPACE

we can't drop a tablespace which holds data in normal drop command.
 
we can drop the datafile which holds the data by specifying the keyword "including contents".
there are 3 types of table spaces : permanent, temporary and undo tablespace.


Example commands:

-- drop tablespace: 
DROP TABLESPACE <tablespace_name>;
DROP TABLESPACE tspace1;
 
-- drop tablespace including contents: 
DROP TABLESPACE <tablespace_name>
INCLUDING CONTENTS;
DROP TABLESPACE tspace1 INCLUDING CONTENTS;
 
-- drop tablespace including contents & datafiles:      
DROP TABLESPACE <tablespace_name>
INCLUDING CONTENTS AND DATAFILES;
DROP TABLESPACE tspace1 INCLUDING CONTENTS AND DATAFILES;
 
-- drop tablespace including contents & datafiles 
   
DROP TABLESPACE <tablespace_name> INCLUDING CONTENTS AND DATAFILES
CASCADE CONSTRAINTS;
DROP TABLESPACE tspace1 INCLUDING CONTENTS AND DATAFILES CASCADE CONSTRAINTS;
 

In a tablespace we can have up to 1024 data files.
depending on the storage we have 2 table spaces one is smallest table space which is default.

In a big file tablespace we can have one data file. and the datafile size grow up to 128TB.

create table space datafile  size ;
example sql statements:

SELECT NAME FROM V$CONTROLFILE;

SELECT MEMBER FROM V$LOGFILE;

SELECT FILE NAME FROM DBA_DATA_FILES;

SELECT NAME FROM V$DATAFILES;

Alter tablespace add datafile '' size 100M;

EX: ALTER TABLESPACE TEST1 ADD DATAFILE '/ORADATA/ORCL/TEST1.DBF' SIZE 100M;

DESC DBA_DATA_FILES;

Resizing the datafile :

ALTER DATABASE DATAFILE '' RESIZE 10M;

EX: ALTER DATABASE DATAFILE '/ORADATA/ORCL/TEST.DBF' RESIZE 10M;

Drop Tablespace :

ALTER TABLESPACE DROP DATAFILE ''; " WE CANT DROP FIRST DATAFILE"

DEFAULT VIEW:
SELECT * FROM DATABASE_PROPERTIES WHERE PROPERTY_NAME LIKE '%DEFAULT%';

ALTER DATABASE DEFAULT TABLESPACE ;
TO MAKE A TABLESPACE AS DEFAULT TABLESPACE.

TEMP Tablespace :

CREATE TEMPORARY TABLESPACE TEMPFILE '' SIZE 10M;

EX: ALTER TABLESPACE TEMP3 ADD TEMPFILE '/ORADATA/ORCL/TEMP.DBF' SIZE 10M;

ALTER TABLESPACE TEMP DROP TEMPFILE '/ORADATA/ORCL/TEMP.DBF' ;

Oracle Database 11g Release 2 (11.2) Installation On Redhat Linux

Install the linux os and then create the directory structure as following
/-- root partition
/boot -- boot partition
/var
/usr
/opt
/tmp
/oraeng or /u01  ---where you want to install the oracle software.i.e Oracle installation directory.
/disk1 -- if you want to write the data-file in separate directory. else: no need to create the directory

Swap is mandatory partition and it should be double of the RAM

Download the Oracle software from OTN  depending on your support status.

Oracle Database 11g Release 2 (11.2.0.1.0)Standard Edition, Standard Edition One, and Enterprise Edition

After successful download unzip the files using unzip command.

Hosts File Configuration
edit the host file using editors "like vi/gedit", "/etc/hosts" update the server ip and host-name in the file.

For example.
127.0.0.1       localhost.localdomain  localhost
192.168.1.164   murthy.localdomain  murthy

Oracle Installation Prerequisites

Perform either the Automatic Setup or the Manual Setup to complete the basic prerequisites. The Additional Setup is required for all installations.Automatic Setup
If you plan to use the "oracle-validated" package to perform all your prerequisite setup, follow the instructions at "http://public-yum.oracle.com" to setup the yum repository for OL, then perform the following command.
# yum install oracle-validated
All necessary prerequisites will be performed automatically.
It is probably worth doing a full update as well, but this is not strictly speaking necessary.
# yum update

Manual configuration

If you have not used on-line configuration to perform all prerequisites packages, you will need to do manually perform the following setups.
Oracle recommend the following minimum parameter settings.

The current values can be tested using the following command.
/sbin/sysctl -a | grep
 

fs.suid_dumpable = 1
fs.aio-max-nr = 1048576
fs.file-max = 6815744
kernel.shmall = 2097152
kernel.shmmax = 536870912
kernel.shmmni = 4096
kernel.sem = 250 32000 100 128
net.ipv4.ip_local_port_range = 9000 65500
net.core.rmem_default = 262144
net.core.rmem_max = 4194304
net.core.wmem_default = 262144
net.core.wmem_max = 1048586

Add  the following lines in the "/etc/sysctl.conf" file.

fs.suid_dumpable = 1
fs.aio-max-nr = 1048576
fs.file-max = 6815744
kernel.shmall = 2097152
kernel.shmmax = 536870912
kernel.shmmni = 4096
# semaphores: semmsl, semmns, semopm, semmni
kernel.sem = 250 32000 100 128
net.ipv4.ip_local_port_range = 9000 65500
net.core.rmem_default=262144
net.core.rmem_max=4194304
net.core.wmem_default=262144
net.core.wmem_max=1048586

Run the following command to change the current kernel parameters.
/sbin/sysctl -p
Add the following lines to the "/etc/security/limits.conf" file.

oracle              soft    nproc   2047
oracle              hard    nproc   16384
oracle              soft    nofile  4096
oracle              hard    nofile  65536
oracle              soft    stack   10240

Install the following packages if they are not already present.
# Mount the Oracle Linux (version) DVD from there you can install/update the following packages.

cd /media/cdrom/Server
rpm -Uvh binutils-2.*
rpm -Uvh compat-libstdc++-33*
rpm -Uvh compat-libstdc++-33*.i386.rpm
rpm -Uvh elfutils-libelf*
rpm -Uvh gcc-4.*
rpm -Uvh gcc-c++-4.*
rpm -Uvh glibc-2.*
rpm -Uvh glibc-common-2.*
rpm -Uvh glibc-devel-2.*
rpm -Uvh glibc-headers-2.*
rpm -Uvh ksh*
rpm -Uvh libaio-0.*
rpm -Uvh libaio-devel-0.*
rpm -Uvh libgomp-4.*
rpm -Uvh libgcc-4.*
rpm -Uvh libstdc++-4.*
rpm -Uvh libstdc++-devel-4.*
rpm -Uvh make-3.*
rpm -Uvh sysstat-7.*
rpm -Uvh unixODBC-2.*
rpm -Uvh unixODBC-devel-2.*
rpm -Uvh numactl-devel-*
cd /
eject

Mandatory steps :

Set secure Linux to permissive by editing the "/etc/selinux/config" file, making sure the SELINUX flag is set as follows.
SELINUX=permissive
If you have the Linux firewall enabled, you will need to disable.

Create the new groups and users.

If you execute the below commands then linux will automatically assign the group and user IDS for the group and users. :

groupadd oinstallgroupadd dbagroupadd oper
useradd -g oinstall -G dba oraclepasswd oracle

or

If you want to manually assign the ID's for groups and users follow the below commands:

groupadd -g 1000(group id should be any one) oinstallgroupadd -g 2000(group id should be any one which id is free) dba

useradd -u 3000(user id is mandatory ) -g (primary group) oinstall -G (secondary group) dba -d (home landing directory) /home/oracle  -m (specify the user name) oracle
useradd -u 3000 -g oinstall -G dba -d /home/oracle -m oracle
password oracle : passwd oracle

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

mkdir -p /u01/app/oracle/product/11.2.0/db_1

change the ownership and permissions for the created directory.you can change the ownership and permissions using root.

chown -R oracle:oinstall /u01
chmod -R 775 /u01

To check the display output, Login as root and issue the following command.

xhost +

Login as oracle user and update the  ".bash_profile"


# Oracle Settings
TMP=/tmp; export TMP
TMPDIR=$TMP; export TMPDIR

ORACLE_HOSTNAME=murthy; export ORACLE_HOSTNAME
ORACLE_UNQNAME=orcl; export ORACLE_UNQNAME
ORACLE_BASE=/u01/app/oracle; export ORACLE_BASE
ORACLE_HOME=$ORACLE_BASE/product/11.2.0/db_1; export ORACLE_HOME
ORACLE_SID=orcl; export ORACLE_SID
PATH=/usr/sbin:$PATH; export PATH
PATH=$ORACLE_HOME/bin:$PATH; export PATH

LD_LIBRARY_PATH=$ORACLE_HOME/lib:/lib:/usr/lib; export LD_LIBRARY_PATHCLASSPATH=$ORACLE_HOME/jlib:$ORACLE_HOME/rdbms/jlib; export CLASSPATH 

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 running the following command in the database directory.

./runInstaller

Shutting Down the Database

Shutting Down the Database: 

Shutdown database will be done in 4 states
1. shutdown normal
2. shutdown transactional
3. shutdown immediate
4. shutdown abort.

1. Shutdown normal : 

• No new connections are not allowed.
• server wait till all the connected users to get disconnected before shutting down database
• DBBC and RLB (redo log buffer ) should be written into the disk.
• SGA memory allocation will be released
• terminates the background process
• database is closed and dismounted.
• It will simply wait forever until you manually kill those sessions. Because of this, we often recommend the shutdown immediate or shutdown abort commands
•  Here is an example of the use of the normal shutdown command. 

SQL> shutdown

2. Shutdown transactional:
 SQL> shutdown

• No new connections are allowed
• Existing user can’t start a new transaction.
• Current users will be disconnected as soon the current transactions ends.
• Shutdown process when all the transactions are completed.
• DBBC and RLB information will be written to disk.
• SGA memory allocation will be released and background process will be terminated.
• Database is closed and dismounted.

3. Shutdown Immediate :

Here is an example of shutting down a database with the shutdown immediate command:
 SQL> shutdown immediate

• No new connections are allowed
• Users will be disconnected immediately
• sql statements which are in process also be terminated. (Oracle will roll back the active transactions)
• Database buffer cache and redo log information will be written to disk.
• SGA memory allocation will be released and background process will be terminated
• Database closed and dismounted.

4. Shutdown abort : 

A shutdown abort can be your first shutdown method of choice, since there may be times when you must force the database down. Here is an example using the shutdown abort command:

SQL> shutdown  abort 

• Aborts all sessions, leaving current DML in need of rollback, de-allocates the SGA and terminates the background processes.
• Note that a shutdown abort is NOT dangerous. 
• The "abort" simply means that Oracle will terminate all active work, which is what most people want during a shutdown.

Starting Up Database

Starting Up Database:

Steps to start the database:
sqlplus / as sysdba  -- login as sysdba.

after successfully login you need to start the database using startup command.

sql> Startup

After executing the command SGA will be allocated based on pfile/spfile values.
and finally database was started.


Database startup stages:

there are 3 stages in startup the database.

1. NOMOUNT STATE:
------------------ --------
"1. it will crate a file called alert log file.
 2. it contains all the database activities.
 3. it will be in diagnostics-dest.(destination).
 4. these are the things will be in the NOMOUNT stage this is called instance startup."

2. MOUNT STATE :
 -----------------------
It read the control file.
the control file location also be specified into pfile.
open the control file to obtain the name and location of data files and and redolog files.
Renaming data files will be done in this mount state.
in order to enable/disable archive log mode also will be happen in mount state.
Recovery also done in mount state.

3. OPEN STATE:
--------------------
in this stage it will check all the redo log and data-files are physically present or not.

Related views :
select status from v$instance;      --> to know the status of the instance

select name from v$database;      --> to know the database name.
select name from v$datafiles;      --> to know the data-file name and locations.
select member from v$logfiles;   --> to know the logfile name and locations.