How to check ORA error or Viewing Errors with the oerr Utility

Many Oracle products on UNIX have a utility called "oerr" that can be used to aid error investigation. 
The "oerr" utility is a shell script that is located in the ORACLE_HOME/bin directory. "oerr" requires the ORACLE_HOME to be set and two parameters when called: the facility which is the three to four letter code to the left of dash and the error number.

ORA-27154: post/wait create failed, ORA-27300: OS system dependent operation:semget failed with status: 28, ORA-27301: OS failure message: No space left on device, ORA-27302: failure occurred at: sskgpcreates - When creating new instance

Today I see below errors when trying to create new instance in DEV environment (It has already 3 instance, I was trying to create 4th one).

ORA-27154: post/wait create failed
ORA-27300: OS system dependent operation:semget failed with status: 28
ORA-27301: OS failure message: No space left on device
ORA-27302: failure occurred at: sskgpcreates - When creating new instance

In my case Kernel settings are not sufficient for 4th instance.

Semaphore parameter value caused the issue.

in /etc/sysctl.conf I see kernel.sem = 250 32000 100 128

and changed to kernel.sem = 250 32000 100 256

as a root executed sysctl -p

This fixed my issue and I was able to create 4th instance.

How to enable/disable Archivelog mode in Oracle?

There are two types of logging modes in Oracle database :

ARCHIVELOG : In this type of logging whatever oracle writes in a redo log file related to transactions in database, saved to another location after a log file has been filled . This location is called Archive location. if database is in Archive log mode then in case of any disaster, we can recover our database upto the last commit and user don't have to reenter their data. Until a redo log file is not written to the Archive location it cannot be reused by oracle to write redo related data.

NOARCHIVELOG : In this type of logging whatever oracle writes in a redo log file related to transactions in database must be overwritten when all the log files have been filled. In this type of logging we can recover our database upto the last consistent backup we have with us, after that users have to reenter their data.

How to check log mode

SQL> archive log list

How to enable ARCHIVELOG mode

SQL> shut immediate;

SQL> startup mount

SQL> alter database archivelog;

SQL> alter database open;

How to disable ARCHIVELOG mode

SQL> shut immediate

SQL> startup mount

SQL> alter database noarchivelog;

SQL> alter database open;

SQL> archive log list

How to recreate a control file in Oracle Database?

Oracle provides the "alter database backup controlfile to trace" command to copy the create database syntax into a trace file in your user dump directory.

Use this command to re-create a control file only if:

• All copies of your existing control files have been lost through media failure.
• You want to change the name of the database.
• You want to change the maximum number of redo log file groups, redo log file members, archived redo log files, datafiles, or instances that can concurrently have the database mounted and open.

SQL>  show parameter user_dump_dest

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
user_dump_dest                       string      /mnt/ora01/app/oracle/diag/rdbms/QA/QA/trace
[TEST:QA] pwd
/mnt/ora01/app/oracle/diag/rdbms/QA/QA/trace
[TEST:QA] ls -lrt QA_ora_1858.trc
-rw-r-----. 1 oracle oinstall 10645 Jan 23 11:21 QA_ora_1858.trc

STARTUP NOMOUNT
CREATE CONTROLFILE REUSE DATABASE "QA" RESETLOGS  NOARCHIVELOG
    MAXLOGFILES 16
    MAXLOGMEMBERS 3
    MAXDATAFILES 100
    MAXINSTANCES 8
    MAXLOGHISTORY 292
LOGFILE
  GROUP 1 (
    '/mnt/ora02/oradata/QA/redo01a.rdo',
    '/mnt/ora03/oradata/QA/redo01b.rdo'
  ) SIZE 500M BLOCKSIZE 512,
  GROUP 2 (
    '/mnt/ora02/oradata/QA/redo02a.rdo',
    '/mnt/ora03/oradata/QA/redo02b.rdo'
  ) SIZE 500M BLOCKSIZE 512,
  GROUP 3 (
    '/mnt/ora02/oradata/QA/redo03a.rdo',
    '/mnt/ora03/oradata/QA/redo03b.rdo'
  ) SIZE 500M BLOCKSIZE 512,
  GROUP 4 (
    '/mnt/ora02/oradata/QA/redo04a.rdo',
    '/mnt/ora03/oradata/QA/redo04b.rdo'
  ) SIZE 500M BLOCKSIZE 512,
  GROUP 5 (
    '/mnt/ora02/oradata/QA/redo05a.rdo',
    '/mnt/ora03/oradata/QA/redo05b.rdo'
  ) SIZE 500M BLOCKSIZE 512,
  GROUP 6 (
    '/mnt/ora02/oradata/QA/redo06a.rdo',
    '/mnt/ora03/oradata/QA/redo06b.rdo'
  ) SIZE 500M BLOCKSIZE 512,
  GROUP 7 (
    '/mnt/ora02/oradata/QA/redo07a.rdo',
    '/mnt/ora03/oradata/QA/redo07b.rdo'
  ) SIZE 500M BLOCKSIZE 512
-- STANDBY LOGFILE
DATAFILE
  '/mnt/ora03/oradata/QA/system01.dbf',
  '/mnt/ora03/oradata/QA/sysaux01.dbf',
  '/mnt/ora06/oradata/QA/undotbs01.dbf',
  '/mnt/ora01/oradata/QA/users01.dbf',
  '/mnt/ora09/oradata/QA/DATA_APPDATA_01.DBF',
  '/mnt/ora09/oradata/QA/DATA_APPDATA_02.DBF',
  '/mnt/ora09/oradata/QA/DATA_APPDATA_03.DBF',
  '/mnt/ora09/oradata/QA/DATA_APPDATA_04.DBF',
  '/mnt/ora09/oradata/QA/DATA_APPDATA_05.DBF',
  '/mnt/ora09/oradata/QA/DATA_APPDATA_06.DBF',
  '/mnt/ora09/oradata/QA/DATA_APPDATA_07.DBF',
  '/mnt/ora09/oradata/QA/DATA_APPDATA_08.DBF',
  '/mnt/ora09/oradata/QA/DATA_APPDATA_09.DBF',
  '/mnt/ora09/oradata/QA/DATA_APPDATA_10.DBF',
  '/mnt/ora09/oradata/QA/DATA_APPDATA_11.DBF',
  '/mnt/ora09/oradata/QA/DATA_APPDATA_12.DBF',
  '/mnt/ora09/oradata/QA/DATA_APPDATA_13.DBF',
  '/mnt/ora09/oradata/QA/DATA_APPDATA_14.DBF',
  '/mnt/ora09/oradata/QA/DATA_APPDATA_15.DBF',
  '/mnt/ora08/oradata/QA/INDX_APPINDX_01.DBF',
  '/mnt/ora08/oradata/QA/INDX_APPINDX_02.DBF',
  '/mnt/ora08/oradata/QA/INDX_APPINDX_03.DBF',
  '/mnt/ora08/oradata/QA/INDX_APPINDX_04.DBF',
  '/mnt/ora08/oradata/QA/INDX_APPINDX_05.DBF',
  '/mnt/ora08/oradata/QA/INDX_APPINDX_06.DBF',
  '/mnt/ora08/oradata/QA/INDX_APPINDX_07.DBF',
  '/mnt/ora08/oradata/QA/INDX_APPINDX_08.DBF',
  '/mnt/ora08/oradata/QA/INDX_APPINDX_09.DBF',
  '/mnt/ora08/oradata/QA/INDX_APPINDX_10.DBF'
CHARACTER SET WE8MSWIN1252
;
-- Commands to re-create incarnation table
-- Below log names MUST be changed to existing filenames on
-- disk. Any one log file from each branch can be used to
-- re-create incarnation records.
-- ALTER DATABASE REGISTER LOGFILE '/mnt/ora01/app/oracle/fast_recovery_area/QA/archivelog/2016_01_23/o1_mf_1_1_%u_.arc';
-- Recovery is required if any of the datafiles are restored backups,
-- or if the last shutdown was not normal or immediate.
RECOVER DATABASE USING BACKUP CONTROLFILE
-- Database can now be opened zeroing the online logs.
ALTER DATABASE OPEN RESETLOGS;
-- Commands to add tempfiles to temporary tablespaces.
-- Online tempfiles have complete space information.
-- Other tempfiles may require adjustment.
ALTER TABLESPACE TEMP ADD TEMPFILE '/mnt/ora04/oradata/QA/temp01.dbf'
     SIZE 61865984  REUSE AUTOEXTEND ON NEXT 655360  MAXSIZE 32767M;
ALTER TABLESPACE TEMP001 ADD TEMPFILE '/mnt/ora05/oradata/QA/TEMP001.DBF'
     SIZE 10000M REUSE AUTOEXTEND ON NEXT 1048576  MAXSIZE 50000M;
-- End of tempfile additions.
--

ORA-01000: maximum open cursors exceeded

To solve this issue we can either increase the no. of open_cursors or kill the inactive session which has open the large number of cursors. Now we connect to the database and check the open_cursors limits.
$sqlplus sboda as sysdba
SQL*Plus: Release 11.2.0.3.0 Production on Fri Jan 22 22:55:38 2016
Copyright (c) 1982, 2011, Oracle.  All rights reserved.
Enter password:
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL> set linesize 256
SQL> show parameter open_cursors
NAME                                 TYPE                             VALUE
------------------------------------ -------------------------------- ------------------------------
open_cursors                         integer                          300

Find out the session which is causing ORA-01000: maximum open cursors exceeded  using Below Sql

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;

Now one can see which all queries are causing maxing out of open cursors using below Sql:

select  sid ,sql_text, user_name, count(*) as "OPEN CURSORS" from v$open_cursor where sid in (SID) group by sid ,sql_text, user_name

Now check which session opens to many cursors?

SQL>  select sid, status, event, seconds_in_wait state "wait(s)" , blocking_session "blk_sesn", prev_sql_id  "SQL_ID"  from v$session where sid=<sid_number>;

Check the status of the cursor,if it is INACTIVE we can we kill the session by using the below command :
SQL> alter system kill session 'sid,serial#' immediate;
The other alternatives is to increase the no. of the open_cursors parameter as
SQL> alter system set open_cursors=1500 scope=spfile;
In my case i have increased the values of the open_cursors and issue got solved.
Another useful query - total cursors open, by username & machine
select sum(a.value) total_cur, avg(a.value) avg_cur, max(a.value) max_cur, 
s.username, s.machine
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' 
group by s.username, s.machine

order by 1 desc;

ORA-04030: out of process memory when trying to allocate

What is ORA-04030?

• This error indicates that the oracle server process is unable to allocate more memory from the operating system.
• This memory consists of the PGA (Program Global Area) and its contents depend upon the server configuration.
• For dedicated server processes it contains the stack and the UGA (User Global Area) which holds user session data, cursor information and the sort area. 
• In a multithreaded configuration (shared server), the UGA is allocated in the SGA (System Global Area) and will not be responsible for ORA-4030 errors.
• This ORA-04030 can be caused by a shortage of RAM on a dedicated (non shared server) environment, a too small PGA, and by not setting kernel parameters large enough to allow enough RAM.

What will happen?

• We can't allocate more memory from the operating system.

Query to find out the memory usage for processes from the OS

SELECT SID,NAME,VALUE FROM  v$statname n,v$sesstat s WHERE    n.STATISTIC# = s.STATISTIC# AND NAME LIKE 'session%memory%'  ORDER BY 3 ASC;

How to avoid?

• Get more memory or reduce your need for memory
• Increase pga_aggregate_target
• Decrease sort_area_size and/or hash_area_size

The listener supports no services

When i am trying to connect newly created database I am getting below error
ERROR
ORA-12514: TNS: Listener does not currently know of service requested in connect descriptor

When I checked my listener status it shown below

$ /mnt/ora01/app/oracle/product/11.2.0/db_1/bin/lsnrctl status LISTENER_QA

LSNRCTL for Linux: Version 11.2.0.3.0 - Production on 13-JAN-2016 13:47:33

Copyright (c) 1991, 2011, Oracle.  All rights reserved.

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=172.16.240.230)(PORT=1530)))

STATUS of the LISTENER

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

Alias                     LISTENER_QA

Version                   TNSLSNR for Linux: Version 11.2.0.3.0 - Production

Start Date                13-JAN-2016 13:44:43

Uptime                    0 days 0 hr. 2 min. 50 sec

Trace Level               off

Security                  ON: Local OS Authentication

SNMP                      OFF

Listener Parameter File   /mnt/ora01/app/oracle/product/11.2.0/db_1/network/admin/listener.ora

Listener Log File         /mnt/ora01/app/oracle/diag/tnslsnr//listener_qa/alert/log.xml

Listening Endpoints Summary...

  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=172.16.240.230)(PORT=1530)))

The listener supports no services

The command completed successfully

I tried multiple option but no luck. 

Reason behind the issue:
================
- Listener is a process that runs on database server and is responsible for receiving client connection and passing connection to database.
- If you are using default port (1521) for listener, then database will automatically register the instance with listener.
- But what if you are not using default listener port (1521). Instead, you are using some other port – Say 1530. Oracle doesnt do dyamic registration on non-default listener port automatically.
- If you are using non default port (other than 1521). In local_listener parameter we can specify non-default ports on which listener is running and Oracle will register services on those ports.
SQL> alter system set local_listener='(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=172.16.240.230)(PORT=1530)))' sid='QA' scope=spfile;
- As soon as I set the value of local_listener, PMON will know the port number on which listener is running and it will dynamically register the instance with listener.

As sys/system user

SQL> alter system set local_listener='(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=172.16.240.230)(PORT=1530)))' sid='QA' scope=spfile;

System altered.

SQL> shut immediate

Database closed.

Database dismounted.

ORACLE instance shut down.

SQL> startup

ORACLE instance started.

 Total System Global Area 4.1823E+10 bytes

Fixed Size                  2237488 bytes

Variable Size            4429188048 bytes

Database Buffers         3.7313E+10 bytes

Redo Buffers               78639104 bytes

Database mounted.

sDatabase opened.

SQL>show parameter local

 NAME                                 TYPE        VALUE

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

local_listener                       string      (DESCRIPTION=(ADDRESS=(PROTOCO

                                                 L=tcp)(HOST=172.16.240.230)(PO

                                                 RT=1530)))

log_archive_local_first              boolean     TRUE

parallel_force_local                 boolean     FALSE

SQL> alter system register;

System altered.

Above steps fixed my issue.

Check the listener status now

$/mnt/ora01/app/oracle/product/11.2.0/db_1/bin/lsnrctl status LISTENER_QA

LSNRCTL for Linux: Version 11.2.0.3.0 - Production on 13-JAN-2016 14:25:41

Copyright (c) 1991, 2011, Oracle.  All rights reserved.

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=172.16.240.230)(PORT=1530)))

STATUS of the LISTENER

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

Alias                     LISTENER_QA

Version                   TNSLSNR for Linux: Version 11.2.0.3.0 - Production

Start Date                13-JAN-2016 14:22:39

Uptime                    0 days 0 hr. 3 min. 2 sec

Trace Level               off

Security                  ON: Local OS Authentication

SNMP                      OFF

Listener Parameter File   /mnt/ora01/app/oracle/product/11.2.0/db_1/network/admin/listener.ora

Listener Log File         /mnt/ora01/app/oracle/diag/tnslsnr//listener_qa/alert/log.xml

Listening Endpoints Summary...

  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=172.16.240.230)(PORT=1530)))

Services Summary...

Service "QA.tiermeddc.inc" has 1 instance(s).

  Instance "QA", status READY, has 1 handler(s) for this service...

Service "QAXDB.tiermeddc.inc" has 1 instance(s).

  Instance "QA", status READY, has 1 handler(s) for this service...

The command completed successfully

My TNSNAMES.ora as below

QA =

  (DESCRIPTION =

    (ADDRESS = (PROTOCOL = TCP)(HOST = 172.16.240.230)(PORT = 1530))

    (CONNECT_DATA =

      (SERVER = DEDICATED)

      (SERVICE_NAME = QA.domain.inc)

    )

  )  

Also add host name to /etc/hosts

How to send Mail through database using UTL_MAIL??

As a sys user execute below scripts
{ORACLE_HOME}/rdbms/admin/utlmail.sql
{ORACLE_HOME}/rdbms/admin/prvtmail.plb

SMTP_OUT_SERVER parameter must be set to identify the SMTP server (We can give SMTP FQDN or IP address)
SQL> alter system set smtp_out_server='smtp.server.com:25' scope=both;
SQL> grant execute on UTL_MAIL to public;
Create ACL (Access Control list)
BEGIN
   DBMS_NETWORK_ACL_ADMIN.CREATE_ACL (
    acl          => 'mail_access.xml',
    description  => 'Permissions to access e-mail server.',
    principal    => 'PUBLIC',
    is_grant     => TRUE,
    privilege    => 'connect');
   COMMIT;
END;

/
Assign the ACL to the SMTP (Mail server)
BEGIN
   DBMS_NETWORK_ACL_ADMIN.ASSIGN_ACL (
    acl          => 'mail_access.xml',
    host         => 'smtp.server.com',
    lower_port   => 25,
    upper_port   => 25
    );
   COMMIT;
END;

/
Example send procedure
SQL>declare
begin
 utl_mail.send(
   sender => 'abc@xyz.com',
   recipients => 'xyz@abc.com',
   subject => 'Testing utl_mail',
   message => 'The receipt of this email means'||
              'UTL_MAIL works for you!'
   );
EXCEPTION
   WHEN OTHERS THEN
     raise_application_error(-20001,'The following error has occured: ' || sqlerrm);   
END;

/

PL/SQL procedure successfully completed.

Check you inbox you should see Test mail!!

How To Find Out What Privileges a User Currently Has?

Privileges granted to users are listed in two system views: DBA_SYS_PRIVS and USER_SYS_PRIVS.
We can find out what privileges a user currently has by running below query - as normal user

We can find out what privileges a user currently has by running below query - as SYS user

How to find DB links in Oracle??

select * from DBA_DB_LINKS   - All DB links defined in the database.
select * from ALL_DB_LINKS    - All DB links the current user has access to.
select * from USER_DB_LINKS - All DB links owned by current user.