ORA-01078: && LRM-00109 failure in processing system parameters

SQL> startup
ORA-01078: failure in processing system parameters
LRM-00109: could not open parameter file '/u01/app/oracle/product/11.2.0/db_1/dbs/initORCL.ora'

solution:

there ware two options to resolve this issue.

1. in linux system export the SID and then try to start the db.

[oracle@murthy dbs]$ export ORACLE_SID=orcl
[oracle@murthy dbs]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.1.0 Production on Mon Nov 30 18:23:35 2015

Copyright (c) 1982, 2009, Oracle.  All rights reserved.


Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> 

2. create the init.ora file to startup the db.

SQL> startup
ORA-01078: failure in processing system parameters
LRM-00109: could not open parameter file '/u01/app/oracle/product/11.2.0/db_1/dbs/initORCL.ora'
SQL> exit
Disconnected
[oracle@murthy dbs]$ ls
hc_orcl.dat  init.ora  lkORCL  orapworcl  spfileorcl.ora
[oracle@murthy dbs]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.1.0 Production on Mon Nov 30 18:10:27 2015

Copyright (c) 1982, 2009, Oracle.  All rights reserved.

Connected to an idle instance.

SQL> create pfile from spfile='/u01/app/oracle/product/11.2.0/db_1/dbs/spfileorcl.ora';

File created.


after successfully created the file then try to start the db.

Basics of SQL

1.What is Database :

A database is a collection of information that is organized so that it can easily be accessed, managed, and updated. In one view, databases can be classified according to types of content 

2.What is a table :

Tables are the basic unit of data storage in an Oracle Database. Data is stored in rows and columns. You define a table with a table name, such as employees , and a set of columns.

basic syntax to create table : 

CREATE TABLE table_name ( column1 datatype [ NULL | NOT NULL ], column2 datatype [ NULL | NOT NULL ], ... column_n datatype [ NULL | NOT NULL ] );

Create table Murthy (sno number,name varchar2);

 

3.What is view :

A view is a named and validated SQL query which is stored in the Oracle data dictionary. Views do not contain any data - it is just a stored query in the database that can be executed when called. One can think of a view as a virtual table or mapping of data from one or more tables

Syntax to create a view : 
 
CREATE VIEW emp_view AS SELECT last_name, salary*12 annual_salary FROM employees WHERE department_id = 20;

4.What is join and types of joins :

• INNER JOIN: Returns all rows when there is at least one match in BOTH tables

• LEFT JOIN: Return all rows from the left table, and the matched rows from the right table

• RIGHT JOIN: Return all rows from the right table, and the matched rows from the left table

• FULL JOIN: Return all rows when there is a match in ONE of the tables

Note :
LEFT JOIN is same as LEFT OUTER JOIN
RIGHT JOIN is same as RIGHT OUTER JOIN

5. Oracle Constraints :

• Not null

• Unique Constraint

• Primary Key

•  Foreign key

• Check constraint

A NOT NULL constraint prohibits a database value from being null.

A unique constraint prohibits multiple rows from having the same value in the same column or combination of columns but allows some values to be null.

A primary key constraint combines a NOT NULL constraint and a unique constraint in a single declaration. That is, it prohibits multiple rows from having the same value in the same column or combination of columns and prohibits values from being null.

A foreign key constraint requires values in one table to match values in another table.

A check constraint requires a value in the database to comply with a specified condition.

A REF column by definition references an object in another object type or in a relational table. A REF constraint lets you further describe the relationship between the REF column and the object it references. 

6.What is the difference between primary key and unique key

Unique Key	Primary Key
A table can contain more than one unique key	A table can contain only one primary key (can be one/more columns)
Unique key allows one null value	Primary key will not allow null values

7.What are DDL and DML commands .

DDL (DataDifinition Language)	DML (Data Manipulation Language)
Create	Select
Alter	Insert
Truncate	Update
Drop	Deletet

8. What is the difference between “delete” , “truncate” and “drop” commands

 

Delete Command	Truncate Command	Drop Command
Delete Command Belongs to DML	Truncate Command Belongs to DDL	Drop Command Belongs to DDL
Can be Used to delete entire Table data	Can be Used to delete entire Table data	Can be Used to delete entire Table data and also the structure
Can be used to delete specific rows using where clause	Can't be used to delete specific rows using where clause	Can't be used to delete specific rows using where clause
Can be rolled back	Can't be rolled back	Can't be rolled back

 

9. Relation between views and tables

Tables are the actual database entities that hold your rows.

Views are "imaginary" tables that are constructed based on the actual tables.

A table contains data, a view is just a SELECT statement which has been saved in the database
The advantage of a view is that it can join data from several tables thus creating a new view of it. Say you have a database with salaries and you need to do some complex statistical queries on it.

Instead of sending the complex query to the database all the time, you can save the query as a view and then SELECT * FROM view

Syntax : 

CREATE VIEW view_name AS SELECT columns
  FROM tables WHERE conditions;

10. What are function,trigger and stored procedures :

A stored procedure is a user defined piece of code written in the local version of PL/SQL, which may return a value that is invoked by calling it explicitly.

A trigger is a stored procedure that runs automatically when various events happen (eg update, insert, delete).

A function is a user defined piece of code and it should return a value making it a function.

Syntax : 
Procedure : 
CREATE [OR REPLACE] PROCEDURE procedure_name
    [ (parameter [,parameter]) ]
IS
   [declaration_section]
BEGIN
    executable_section
[EXCEPTION
    exception_section]
END [procedure_name];

11. What is the purpose of ‘distinct’ in sql

Distinct is used to display unique values

Ex:

Select distinct from

Select distinct ename from emp

12. What is the difference between union and union all

UNION removes duplicate records (where all columns in the results are the same), UNION ALL does not.

There is a performance hit when using UNION vs UNION ALL, since the database server must do additional work to remove the duplicate rows, but usually you do not want the duplicates (especially when developing reports).

ORA-03113: & ORA-19809: & ORA-19804: &ORA-19815: WARNING: db_recovery_file_dest_size is 100.00% used,

It might be just because the lack of space in the recovery dest.

SQL> startup
ORACLE instance started.

Total System Global Area  778387456 bytes
Fixed Size                  1374808 bytes
Variable Size             385877416 bytes
Database Buffers          385875968 bytes
Redo Buffers                5259264 bytes
Database mounted.
ORA-03113: end-of-file on communication channel
Process ID: 4152
Session ID: 5 Serial number: 3

Check the alert log for further investination.
==============================================
contents from alert log
=======================
ORA-19815: WARNING: db_recovery_file_dest_size of 4039114752 bytes is 100.00% used, and has 0 remaining bytes available.
Errors in file c:\app\rpilla\diag\rdbms\mntest1\mntest1\trace\mntest1_arc2_4956.trc:
ORA-19809: limit exceeded for recovery files
ORA-19804: cannot reclaim 2866688 bytes disk space from 4039114752 limit

ORA-19815: WARNING: db_recovery_file_dest_size of 4039114752 bytes is 100.00% used, and has 0 remaining bytes available.
************************************************************************
You have following choices to free up space from recovery area:
1. Consider changing RMAN RETENTION POLICY. If you are using Data Guard,
   then consider changing RMAN ARCHIVELOG DELETION POLICY.
2. Back up files to tertiary device such as tape using RMAN
   BACKUP RECOVERY AREA command.
3. Add disk space and increase db_recovery_file_dest_size parameter to
   reflect the new space.
4. Delete unnecessary files using RMAN DELETE command. If an operating
   system command was used to delete files, then use RMAN CROSSCHECK and
   DELETE EXPIRED commands.
************************************************************************
I used the 4th suggestion by deleting archive log files from db_recovery_file_dest.

login

rman target /

list backup;
list copy;
delete archivelog all;


ORA-19815: WARNING: db_recovery_file_dest_size is 100.00% used,


Sat May 30 08:28:20 2015
ARC2 started with pid=22, OS id=38112
ARC1: Archival started
ARC2: Archival started
ARC1: Becoming the 'no FAL' ARCH
ARC1: Becoming the 'no SRL' ARCH
ARC2: Becoming the heartbeat ARCH
Sat May 30 08:28:20 2015
ARC3 started with pid=23, OS id=39704
Errors in file e:\app\murthy\diag\rdbms\orcl\orcl\trace\orcl_arc1_39816.trc:
ORA-19815: WARNING: db_recovery_file_dest_size of 4039114752 bytes is 100.00% used, and has 0 remaining bytes available.
Errors in file e:\app\murthy\diag\rdbms\orcl\orcl\trace\orcl_ora_38608.trc:
ORA-19815: WARNING: db_recovery_file_dest_size of 4039114752 bytes is 100.00% used, and has 0 remaining bytes available.
************************************************************************
You have following choices to free up space from recovery area:
1. Consider changing RMAN RETENTION POLICY. If you are using Data Guard,
   then consider changing RMAN ARCHIVELOG DELETION POLICY.
2. Back up files to tertiary device such as tape using RMAN
   BACKUP RECOVERY AREA command.
3. Add disk space and increase db_recovery_file_dest_size parameter to
   reflect the new space.
4. Delete unnecessary files using RMAN DELETE command. If an operating
   system command was used to delete files, then use RMAN CROSSCHECK and
   DELETE EXPIRED commands.
************************************************************************
Errors in file e:\app\murthy\diag\rdbms\orcl\orcl\trace\orcl_ora_38608.trc:
ORA-19809: limit exceeded for recovery files
ORA-19804: cannot reclaim 44892672 bytes disk space from 4039114752 limit
ARCH: Error 19809 Creating archive log file to 'E:\APP\MURTHY\FLASH_RECOVERY_AREA\ORCL\ARCHIVELOG\2015_05_30\O1_MF_1_98_%U_.ARC'
Errors in file e:\app\murthy\diag\rdbms\orcl\orcl\trace\orcl_ora_38608.trc:
ORA-16038: log 2 sequence# 98 cannot be archived
ORA-19809: limit exceeded for recovery files
ORA-00312: online log 2 thread 1: 'E:\APP\MURTHY\ORADATA\ORCL\REDO02.LOG'
USER (ospid: 38608): terminating the instance due to error 16038
************************************************************************
You have following choices to free up space from recovery area:
1. Consider changing RMAN RETENTION POLICY. If you are using Data Guard,
   then consider changing RMAN ARCHIVELOG DELETION POLICY.
2. Back up files to tertiary device such as tape using RMAN
   BACKUP RECOVERY AREA command.
3. Add disk space and increase db_recovery_file_dest_size parameter to
   reflect the new space.
4. Delete unnecessary files using RMAN DELETE command. If an operating
   system command was used to delete files, then use RMAN CROSSCHECK and
   DELETE EXPIRED commands.
************************************************************************
Instance terminated by USER, pid = 38608

To check the free space :

select name,
floor(space_limit/1024/1024) "Size_MB",
ceil(space_used/1024/1024) "Used_MB"
from v$recovery_file_dest
order by name;
/
  Solution : 

  open CMD>

1. RMAN
2. connect /
3. crosscheck archivelog all;
4. delete archivelog all completed before 'sysdate -7';
5. Prompt for Yes or No
6. Y and enter
7. exit

C:\Users\jnmurthy>rman target /

Recovery Manager: Release 11.2.0.1.0 - Production on Sat May 30 08:44:13 2015

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

connected to target database: ORCL (DBID=1381995653)

RMAN> show all;

using target database control file instead of recovery catalog
RMAN configuration parameters for database with db_unique_name ORCL are:
CONFIGURE RETENTION POLICY TO REDUNDANCY 1; # default
CONFIGURE BACKUP OPTIMIZATION OFF; # default
CONFIGURE DEFAULT DEVICE TYPE TO DISK; # default
CONFIGURE CONTROLFILE AUTOBACKUP OFF; # default
CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO '%F'; # default
CONFIGURE DEVICE TYPE DISK PARALLELISM 1 BACKUP TYPE TO BACKUPSET; # default
CONFIGURE DATAFILE BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default
CONFIGURE ARCHIVELOG BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default
CONFIGURE MAXSETSIZE TO UNLIMITED; # default
CONFIGURE ENCRYPTION FOR DATABASE OFF; # default
CONFIGURE ENCRYPTION ALGORITHM 'AES128'; # default
CONFIGURE COMPRESSION ALGORITHM 'BASIC' AS OF RELEASE 'DEFAULT' OPTIMIZE FOR LOAD TRUE ; # default
CONFIGURE ARCHIVELOG DELETION POLICY TO NONE; # default
CONFIGURE SNAPSHOT CONTROLFILE NAME TO 'E:\APP\MURTHY\PRODUCT\11.2.0\DBHOME_1\DATABASE\SNCFORCL.ORA'; # default


before deleted the file location and size
SYS@orcl:SQL>show parameter recovery_file_dest;

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
db_recovery_file_dest                string      E:\app\Murthy\flash_recovery_area
db_recovery_file_dest_size           big integer 30M


Above 3rd and 4th command check for the archieve log file and delete the old archieve log file which is older than the week from current date.
So Now we have free space in "flash_recovery_area" .
 After deleted the backup-ed archive files:

SYS@orcl:SQL>show parameter recovery_file_dest;

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
db_recovery_file_dest                string      E:\app\Murthy\flash_recovery_area
db_recovery_file_dest_size           big integer 300M
After successfully deleted will get the space and then try to startup the db.

SYS@orcl:SQL>startup
ORACLE instance started.

Total System Global Area 1071333376 bytes
Fixed Size                  1375792 bytes
Variable Size             713032144 bytes
Database Buffers          352321536 bytes
Redo Buffers                4603904 bytes
Database mounted.
Database opened.

more commands :

SQL> show parameter alert
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
log_checkpoints_to_alert             boolean     FALSE

SQL> show parameter BACKGROUND_DUMP_DEST
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
background_dump_dest                 string      e:\app\****\diag\rdbms\za1\za1\trace

SQL> show parameter db_recovery_file_dest_size
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
db_recovery_file_dest_size           big integer 3852M

Block Change Tracking file

Block Change Tracking (file)

RMAN's change tracking feature for incremental backups improves incremental backup performance by recording changed blocks in each datafile in a change tracking file. If change tracking is enabled, RMAN uses the change tracking file to identify changed blocks for incremental backup, thus avoiding the need to scan every block in the datafile.

Change tracking is disabled by default, because it introduces some minimal performance overhead on database during normal operations. However, the benefits of avoiding full datafile scans during backup are considerable, especially if only a small percentage of data blocks are changed between backups. If backup strategy involves incremental backups, then we should enable change tracking.

One change tracking file is created for the whole database. By default, the change tracking file is created as an Oracle managed file in DB_CREATE_FILE_DEST. We can also specify the name of the block change tracking file, placing it in any desired location.

Using change tracking in no way changes the commands used to perform incremental backups, and the change tracking files themselves generally require little maintenance after initial configuration.

From Oracle 10g, the background process Block Change Tracking Writer (CTWR) will do the job of writing modified block details to block change tracking file.

In a Real Applications Clusters (RAC) environment, the change tracking file must be located on shared storage accessible from all nodes in the cluster.

Oracle saves enough change-tracking information to enable incremental backups to be taken using any of the 8 most recent incremental backups as its parent.

Although RMAN does not support backup and recovery of the change-tracking file itself, if the whole database or a subset needs to be restored and recovered, then recovery has no user-visible effect on change tracking. After the restore and recovery, the change tracking file is cleared, and starts recording block changes again. The next incremental backup after any recovery is able to use change-tracking data.

After enabling change tracking, the first level 0 incremental backup still has to scan the entire datafile, as the change tracking file does not yet reflect the status of the blocks. Subsequent incremental backup that use this level 0 as parent will take advantage of the change tracking file.

Checking Whether Change Tracking is enabled

From SQL*Plus, we can query V$BLOCK_CHANGE_TRACKING to determine whether change tracking is enabled or not. 

SQL> select status from V$BLOCK_CHANGE_TRACKING;

        ENABLED   => block change tracking is enabled.

        DISABLED  => block change tracking is disabled.

Query V$BLOCK_CHANGE_TRACKING to display the filename.

SQL> select filename from V$BLOCK_CHANGE_TRACKING;

 

Enabling and Disabling Change Tracking

We can enable or disable change tracking when the database is either open or mounted. To alter the change tracking setting, we must use SQL*Plus to connect to the target database with administrator privileges.

To store the change tracking file in the database area, set DB_CREATE_FILE_DEST in the target database. Then issue the following SQL statement to enable change tracking:

SQL> ALTER DATABASE ENABLE BLOCK CHANGE TRACKING;  

We can also create the change tracking file in a desired location, using the following SQL statement:

SQL> ALTER DATABASE ENABLE BLOCK CHANGE TRACKING USING FILE '/u01/rman/rman_change_track.f';

The REUSE option tells Oracle to overwrite any existing file with the specified name.

SQL> ALTER DATABASE ENABLE BLOCK CHANGE TRACKING USING FILE '/u01/rman/rman_change_track.f' REUSE;

To disable change tracking, use this SQL statement:

SQL> ALTER DATABASE DISABLE BLOCK CHANGE TRACKING;  

If the change tracking file was stored in the database area, then it will be deleted when we disable change tracking.

Moving the Change Tracking File

If you need to move the change tracking file, the ALTER DATABASE RENAME FILE command updates the control file to refer to the new location.

1. If necessary, determine the current name of the change tracking file:

SQL> SELECT filename FROM V$BLOCK_CHANGE_TRACKING;

        /u01/rman/rman_change_track.f

2. Shutdown the database.
SQL> SHUTDOWN IMMEDIATE

3. Using host operating system commands, move the change tracking file to its new location.
$ mv /u01/rman/rman_change_track.f /u01/rman_new/rman_change_track.f

4. Mount the database and move the change tracking file to a location that has more space. For example:

SQL> ALTER DATABASE RENAME FILE '/u01/rman/rman_change_track.f' TO '/u01/rman_new/rman_change_track.f';

5. Open the database.
SQL> ALTER DATABASE OPEN;

SQL> SELECT filename FROM V$BLOCK_CHANGE_TRACKING;

        /u01/rman_new/rman_change_track.f

If you cannot shutdown the database, then you must disable change tracking and re-enable it, at the new location:

SQL> ALTER DATABASE DISABLE BLOCK CHANGE TRACKING;

SQL> ALTER DATABASE ENABLE BLOCK CHANGE TRACKING USING FILE '/u01/rman_new/rman_change_track.f';

If you choose this method, you will lose the contents of the change tracking file. Until the next time you complete a level 0 incremental backup, RMAN will have to scan the entire file.

Estimating Size of the Change Tracking File on Disk

The size of the change tracking file is proportional to the size of the database and the number of enabled threads of redo. The size is not related to the frequency of updates to the database.

Typically, the space required for block change tracking is approximately 1/30,000 the size of the data blocks to be tracked. The following two factors that may cause the file to be larger than this estimate suggests:

• To avoid overhead of allocating space as database grows, the change tracking file size starts at 10MB, and new space is allocated in 10MB increments. Thus, for any database up to approximately 300GB the file size is no smaller than 10MB, for up to approximately 600GB the file size is no smaller than 20MB, and so on.
• For each datafile, a minimum of 320K of space is allocated in the change tracking file, regardless of the size of the file. Thus, if you have a large number of relatively small datafiles, the change tracking file is larger than for databases with a smaller number of larger datafiles containing the same data.

SELECT FILE#, INCREMENTAL_LEVEL, COMPLETION_TIME, BLOCKS, DATAFILE_BLOCKS FROM V$BACKUP_DATAFILE WHERE INCREMENTAL_LEVEL > 0 AND BLOCKS / DATAFILE_BLOCKS > .5 ORDER BY COMPLETION_TIME;

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.