What is Checksum in Oracle.?

A checksum is a count of the number of bits in a transmission unit that is included with the unit so that the receiver can check to see whether the same number of bits arrived. If the counts match, it's assumed that the complete transmission was received. Both TCP and UDPcommunication layers provide a checksum count and verification as one of their services.

ORA-12514: TNS:listener does not currently know of service requested in connect descriptor / Fatal NI connect error 12514, connecting to /ns main err code: 12564 / TNS-12564: TNS:connection refused

Error like

Fatal NI connect error 12514, connecting to:
 (DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=server1)(PORT=25000)))(CONNECT_DATA=(SERVICE_NAME=DBNAME1)(CID=(PROGRAM=oracle)(HOST=server1)(USER=))))

  VERSION INFORMATION:
        TNS for HPUX: Version 11.2.0.3.0 - Production
        Oracle Bequeath NT Protocol Adapter for HPUX: Version 11.2.0.3.0 - Production
        TCP/IP NT Protocol Adapter for HPUX: Version 11.2.0.3.0 - Production
  Time: 17-JUN-2013 11:08:30
  Tracing not turned on.
  Tns error struct:
    ns main err code: 12564

TNS-12564: TNS:connection refused
    ns secondary err code: 0
    nt main err code: 0
    nt secondary err code: 0
    nt OS err code: 0

Possible Causes :

1.) The listener is down.
2.)The listener received a request to establish a connection to a database or other service. The connect descriptor received by the listener specified a service name for a service (usually a database service) that either has not yet dynamically registered with the listener or has not been statically configured for the listener.
3.)  This may be a temporary condition such as after the listener has started, but before the database instance has registered with the listener.

Solution.:

1.) Check your listner by issueing "lsnrctl status" command. 
Verify if the listener is up and running. 
2.) Verify your alert log and sqlnet.log to see if any errors.
3.) Verify if you have specified SID or SERVICE_NAME in you Global tnsnames.ora file.
Correct it if not set properly.

ORA-02391: exceeded simultaneous SESSIONS_PER_USER limit - ORA-02063: preceding line from

This is happening because the User/Schema has made the maximum number of connections allowed. This is governed by the parameter SESSIONS_PER_USER in the user profile. Once the maximum number of connections are made you will hit this error.

You can get the details by executing the following queries :

*****

*****

select profile from dba_users where username ='USER1';

PROFILE

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

DEFAULT

*****

*****

select * from dba_profiles where profile='DEFAULT';

PROFILE                        RESOURCE_NAME                    RESOURCE LIMIT

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

DEFAULT                        COMPOSITE_LIMIT                  KERNEL   UNLIMITED

DEFAULT                        SESSIONS_PER_USER                KERNEL   50

DEFAULT                        CPU_PER_SESSION                  KERNEL   UNLIMITED

DEFAULT                        CPU_PER_CALL                     KERNEL   UNLIMITED

DEFAULT                        LOGICAL_READS_PER_SESSION        KERNEL   UNLIMITED

DEFAULT                        LOGICAL_READS_PER_CALL           KERNEL   UNLIMITED

DEFAULT                        IDLE_TIME                        KERNEL   UNLIMITED

DEFAULT                        CONNECT_TIME                     KERNEL   UNLIMITED

DEFAULT                        PRIVATE_SGA                      KERNEL   UNLIMITED

DEFAULT                        FAILED_LOGIN_ATTEMPTS            PASSWORD 10

DEFAULT                        PASSWORD_LIFE_TIME               PASSWORD 180

DEFAULT                        PASSWORD_REUSE_TIME              PASSWORD UNLIMITED

DEFAULT                        PASSWORD_REUSE_MAX               PASSWORD UNLIMITED

DEFAULT                        PASSWORD_VERIFY_FUNCTION         PASSWORD NULL

DEFAULT                        PASSWORD_LOCK_TIME               PASSWORD 1

DEFAULT                        PASSWORD_GRACE_TIME              PASSWORD 7

Say for example in the above scenario User1 is allowed to make 50 connections only to the database.

We can check how many connections have been made to the database using the following query.

*****

*****

select count(*) from v$session where username='USER1';

 COUNT(*)

---------

        34

The resolution is to change the parameter SESSIONS_PER_USER in the profile attached to user or Identifying if the application is hitting the bug , if so many connections to database is not anticipated.

The profile can be altered using :

Alter profile DEFAULT  limit sessions per user 100;

Hardware Requirements for Oracle RAC Installation

Each system must meet the following minimum hardware requirements:

• At least 1 GB of physical RAM
• Swap space equivalent to the multiple of the available RAM, as indicated in the following table:

  Available RAM	Swap Space Required
  Between 1 GB and 2 GB	1.5 times the size of RAM
  Between 2 GB and 8 GB	Equal to the size of RAM
  More than 8 GB	.75 times the size of RAM

• 400 MB of disk space in the /tmp directory
• 2 GB of disk space for Oracle Clusterware files, in partitions on separate physical disks, assuming standard redundancy (2 Oracle Cluster Registry partitions and 3 voting disks)
• 650 MB of disk space for the Oracle Clusterware home
• 5 GB of disk space for the Oracle database software (Oracle base), depending on the installation type and platform
• 1.2 GB of disk space for a preconfigured database that uses file system storage (optional)
  Additional disk space, either on a file system or in an Automatic Storage Management disk group, is required for the Fast recovery area if you choose to configure automated backups.
  
  
  
  
  To ensure that each system meets these requirements:

1. To determine the physical RAM size, enter the following command:

   # /usr/sbin/prtconf | grep "Memory size"
   

   If the size of the physical RAM installed in the system is less than the required size, then you must install more memory before continuing.
2. To determine the size of the configured swap space, enter the following command:

   # /usr/sbin/swap -s
   

   If necessary, refer to your operating system documentation for information about how to configure additional swap space.
3. To determine the amount of disk space available in the /tmp directory, enter the following command:

   # df -k /tmp
   

   If there is less than 400 MB of disk space available in the /tmp directory, then complete one of the following steps:
   • Delete unnecessary files from the /tmp directory to meet the disk space requirement.
   • Set the TEMP and TMPDIR environment variables when setting the oracle user's environment (described later).
   • Extend the file system that contains the /tmp directory. If necessary, contact your system administrator for information about extending file systems.
4. To determine the amount of free disk space on the system, enter the following command:

   # df -k /tmp
   

   The following table shows the approximate disk space requirements for software files for each installation type:

   Installation Type	Requirement for Software Files (GB)
   Enterprise Edition	4
   Standard Edition	4
   Custom (maximum)	4

5. To determine if the system architecture can run the Oracle software, enter the following command:

   # /bin/isainfo -kv
   

   Note:

   The following is the expected output of this command:

   64-bit SPARC installation:

   64-bit sparcv9 kernel modules

   Ensure that the Oracle software you have is the correct Oracle software for your processor type.

   If the output of this command indicates that your system architecture does not match the system for which the Oracle software you have is written, then you cannot install the software. Obtain the correct software for your system architecture before proceeding further.

Temporary Segments cleanup problem in Oracle / SMON: disabling tx recovery / oracle database shutdown hangs

Recently I had an Issue in which I was supposed to  bounce the database and open in Archive log mode. 

When i was trying to bring the database down , database was hung and was not coming down and was waiting at the following statement for hours.

Mon Jun  3 18:59:02 2013

Job queue slave processes stopped

Mon Jun  3 18:59:05 2013

ALTER DATABASE CLOSE NORMAL

Mon Jun  3 18:59:05 2013

SMON: disabling tx recovery

Later on when i went digging the problem i found out that there were temporary segments in database .  use following query to verify..

Select * from dba_segments where segment_type='TEMPORARY';

OWNER                          SEGMENT_NAME                   SEGMENT_TYPE       TABLESPACE_NAME                     Size in GB

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

USER1                       792.200737                     TEMPORARY          TBLSPC_ DATA01                          43.857872

SMON was trying to clean up the temporary segemnts for a clean shutdown, but it was just hanging there.

I did a sanity check to verify  the temporary segments issue using  following check list

1.)     Checked V$sessions and it doesn't show any sessions for the USER1 schema.

2.)     Checked V$session_longops and it doesn't show any entries.

3.)     Checked  if SMON is disabled for cleanup of temporary segments. There is no event set to value 10 . there is one event which is set to 4

NAME                                 TYPE        VALUE

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

event                                string      10499 trace name context forever, level 4

But nothing came out of it.

Later i found out a simple way to do so. 

1.) Shutdown Abort

2.) Startup

3.) Shutdown immediate

Next you start the database in mount state and issue the Alter database Archivelog command and that does the magic.

4.) Startup mount

5.) Alter database archivelog;

6.) alter database open

Further details :

 Metalink Note 1076161.6 

Description
===========
SHUTDOWN NORMAL or SHUTDOWN IMMEDIATE hangs. In the alert.log, you see only
the following:

Shutting down instance (immediate)
License high water mark = 12
Thu Dec  8 18:43:16 1994
alter database  close normal
Thu Dec  8 18:43:17 1994
SMON: disabling tx recovery
SMON: disabling cache recovery
or
     waiting for smon to disable tx recovery

There are no ORA errors or trace files.



Scope & Application
===================
Informational

During a SHUTDOWN IMMEDIATE and SHUTDOWN NORMAL, SMON is cleaning up extents
which are no longer needed and marking them as freed.

Either wait for SMON to clean up the free extents in the database as it
shuts down or perform a SHUTDOWN ABORT to shutdown the instance. A SHUTDOWN
ABORT will not perform a clean shutdown.

Verify that temporary segments are decreasing
---------------------------------------------
To verify that the temporary segments are decreasing have an active session
available in Server Manager or SQLPLUS during the SHUTDOWN IMMEDIATE. Issue the following
query to ensure the database is not hanging, but is actually perform extent
cleanup:

 SVRMGR/SQL> select count(block#) from fet$;
 COUNT(BLOC
 ----------
          7

 SVRMGR/SQL> select count(block#) from uet$;
 COUNT(BLOC
 ----------
        402

After some time has elapsed, reissue the query and see that the values for fet$
have increased while the values or uet$ have decreased:

 SVRMGR/SQL> select count(block#) from fet$;
 COUNT(BLOC
 ----------
         10

 SVRMGR/SQL> select count(block#) from uet$;
 COUNT(BLOC
 ----------
        399

During shutdown the SMON process is cleaning up extents and updating the data
dictionary tables with the marked free extents. As the extents are marked as
freed, they are removed from the table for used extents, UET$ and placed on the
table for free extents, FET$.

How to Avoid creating many Temporary Extents
--------------------------------------------
Once the database has shutdown cleanly, to avoid creating many temporary
extents change the initial and next extent sizes on temporary tablespaces
to a more appropriate size:

 ALTER TABLESPACE  DEFAULT STORAGE (INITIAL M/K NEXT M/K);

Note: If the temporary tablespace is of type TEMPORARY, then this change
will only affect temporary segments created after issuing the above
command. Any existing temporary segments already in the TEMPORARY tablespace
will not be affected till the instance is restarted. On shutdown, existing
temporary segments are dropped. If the TEMPORARY TABLESPACE is of type
PERMANENT, then cleanup is performed by SMON after completion of the process
using it.

Increasing the initial and next extent size will decrease the number of extents
that are allocated to temporary segments. Since there are fewer extents to
deallocate, the database should shutdown more speedily.

Take the following scenario:

A database was subject to large sorts with the following sort parameter in
the "init.ora" file:

      - sort_area_size=1000000

The temporary tablespaces for this database were all created with initial and
next extents sized at 50k and the total database size was about 300mb.

Database sorts will utilize memory as much as possible based on the "init.ora"
parameter "sort_area_size".  Once this memory-based sort area is filled, the
database will utilize the temporary table space associated with the database
user to complete the sort operation.  During a shutdown normal, the database
will attempt to clean up the temporary tablespaces.

If a small extent size is used, then a large number of extents will be created
for a large sort.  The cleanup of the temporary tablespace takes much longer
with a large number of extents.

Note:
=====
You have to do a shutdown abort and then bring the database
back up to run the suggested queries.

For other reasons for slow/hung shutdown see also these notes:

Note 375935.1 - What To Do and Not To Do When 'shutdown immediate' Hangs
Note 428688.1 - Bug 5057695: Shutdown Immediate Very Slow To Close Database.

References:
===========
Note 61997.1 SMON - Temporary Segment Cleanup and Free Space Coalescing