TNS-12542 TNS Address Already in Use

 

Problem

While start the listener is prod database getting an error like  this TNS-12542: TNS:address already in use

LSNRCTL for Linux: Version 12.1.0.2.0 - Production on 20-AUG-2020 06:06:57

 

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

 

Starting /oracle/app/oracle/product/12.1.0.2/dbhome/bin/tnslsnr: please wait...

 

TNSLSNR for Linux: Version 12.1.0.2.0 - Production

System parameter file is /oracle/app/oracle/product/12.1.0.2/dbhome/network/admin/listener.ora

Log messages written to /oracle/app/oracle/diag/tnslsnr/dbaclass-host/listener_prod/alert/log.xml

Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=chaitanya-host)(PORT=1524)))

Error listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=chaitanya-host)(PORT=1524)))

TNS-12542: TNS:address already in use ---- >>>

TNS-12560: TNS:protocol adapter error

  TNS-00512: Address already in use

  Linux Error: 125: Address already in use

 

Listener failed to start. See the error message(s) above...

Solution

To find the solution we need to check the Listener file listener.ora

LISTENER_PROD =

  (DESCRIPTION_LIST =

    (DESCRIPTION =

      (ADDRESS = (PROTOCOL = TCP)(HOST = chaitanya-host)(PORT = 1524)) --- >>>

      (ADDRESS = (PROTOCOL = TCP)(HOST = chaitanya-host)(PORT = 1524)) --- >>>

 

    )

  )

 

SID_LIST_LISTENER_PROD =

  (SID_LIST =

    (SID_DESC =

      (SID_NAME = PRODDB)

      (ORACLE_HOME = /oracle/app/oracle/product/12.1.0.2/dbhome)

    )

  )

Inside the listener file we have found that two address entries in the same host and same port number (1524) so starting the listener it will get the conflict so unable to start the database

(ADDRESS = (PROTOCOL = TCP)(HOST = chaitanya-host)(PORT = 1524))

      (ADDRESS = (PROTOCOL = TCP)(HOST = chaitanya-host)(PORT = 1524))

To fix the error in give different port numbers for both the address entries

The listener will look like this after change the port numbers

LISTENER_PROD =

  (DESCRIPTION_LIST =

    (DESCRIPTION =

      (ADDRESS = (PROTOCOL = TCP)(HOST = chaitanya-host)(PORT = 1524)) --- >>>

      (ADDRESS = (PROTOCOL = TCP)(HOST = chaitanya-host)(PORT = 1525)) --- >>>

 

    )

  )

 

SID_LIST_LISTENER_PROD =

  (SID_LIST =

    (SID_DESC =

      (SID_NAME = PRODDB)

      (ORACLE_HOME = /oracle/app/oracle/product/12.1.0.2/dbhome)

    )

  )

Now start the listener 

# lsnrctl start LISTENER_PROD

 

LSNRCTL for Linux: Version 12.1.0.2.0 - Production on 20-AUG-2020 06:15:09

 

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

 

Starting /oracle/app/oracle/product/12.1.0.2/dbhome/bin/tnslsnr: please wait...

 

TNSLSNR for Linux: Version 12.1.0.2.0 - Production

System parameter file is /oracle/app/oracle/product/12.1.0.2/dbhome/network/admin/listener.ora

Log messages written to /oracle/app/oracle/diag/tnslsnr/chaitanya-host/listener_prod/alert/log.xml

Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=chaitanya-host)(PORT=1524)))

Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=chaitanya-host)(PORT=1525)))

 

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=chaitanya-host)(PORT=1524)))

STATUS of the LISTENER

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

Alias                     LISTENER_PROD

Version                   TNSLSNR for Linux: Version 12.1.0.2.0 - Production

Start Date                20-AUG-2020 06:15:09

Uptime                    0 days 0 hr. 0 min. 0 sec

Trace Level               off

Security                  ON: Local OS Authentication

SNMP                      OFF

Listener Parameter File   /oracle/app/oracle/product/12.1.0.2/dbhome/network/admin/listener.ora

Listener Log File         /oracle/app/oracle/diag/tnslsnr/chaitanya-host/listener_prod/alert/log.xml

Listening Endpoints Summary...

  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=chaitanya-host)(PORT=1524)))

  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=chaitanya-host)(PORT=1525)))

Services Summary...

Service "PRODDB" has 1 instance(s).

  Instance "PRODDB", status UNKNOWN, has 1 handler(s) for this service...

The command completed successfully

 

 

Listener started successfully.

Listener started sucessfully and listener listening in on both the ports 1524 and 1525 so ports should be unique for each address in the listener

THANKS FOR VIEWING MY BLOG FOR MORE UPDATES FOLLOW ME OR SUBSCRIBE ME

 

ORA-00257 Archiver Error Connect Internal Only Until Freed

 ORA-00257: Archiver Error ,Connect Internal Only Until Freed

Problem

when an application users using database unable to connect to the database aplication log shows the error

ORA-00257:archiver error, connect internal only untill freed

ORA-16014:log1 sequence# 280 not archived,no available destinations

ORA-00312:online log1 thread 1:'/oradata/prod/redo01.log'

Solution

This error comes ,when the archive destination is full and there is no space availability in the destination location archivelogs

Check archivelog destination location

SQL> archive log list

Database log mode              Archive Mode

Automatic archival             Enabled

Archive destination            /archive/PROD

Oldest online log sequence     14

Next log sequence to archive   18

Current log sequence           18

There are different solution for this Error rectification

Option-1:  Delete old archive logs to free up space using Rman utility

rman target /

delete archivelog all completed before 'sysdate-1';

Option-2: Change the archive log location 

incase you cannot  delete the archive logs from the destination location or existing location,then we can change the archive destination to some other mount point 

SQL>show parameter log_achive_dest;

NAME                     TYPE                     VALUE

log_archive_dest      string                  LOCATION =/archive/prod

SQL>alter  system set log_archive_dest= 'LOCATION=/u03/backupdest/archive/prod'  scope=both;

SQL>show parameter log_achive_dest;

NAME                     TYPE                     VALUE

log_archive_dest      string                  LOCATION =u03/backupsest/archive/prod

switch log file

alter sytem switch logfile;

THANKS FOR VIEWING MY BLOG FOR MORE UPDATES FOLLOW ME OR SUBSCRIBE ME

ORA-30034 Undo Tablespace Cannot Be Specified As Temporary Tablespace

 

ORA-30034 :Undo Tablespace Cannot Be Specified As Temporary Tablespace

While Creating an user in temp tablespace user got an error  ORA-30034 :Undo tablespace cannot be specified as temporary tablespace

SQL> create user CHAITANYA  identified by chai123 default tablespace USERS TEMPORARY TABLESPACE UNDOTBS03;

create user CHAITANYA  identified by chai123 default tablespace USERS TEMPORARY TABLESPACE UNDOTBS03;

* 

ERROR ar line 1:

ORA-30034 : Undo tablespace cannot be specified as temporary tablespace

Solution

We cannot set an Undo tablespace has a temporay tablespace for the user ,UNDOTBS03 is a undo tablespace ,so we have to give temp name for the tablespace then it will create user  

 SQL>select distinct tablespace_name from DBA_UNDO_EXTENTS;

TABLESPACE_NAME

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

UNDOTBS03

SQL>show parameter undo_tablespace;

NAME                   TYPE                VALUE

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

undo_tablespace      string              UNDOTBS03

always assign an temp tablespace as temporary  tablespace for an user not for undotablesapce

SQL> create user CHAITANYA  identified by chai123 default tablespace USERS TEMPORARY TABLESPACE TEMP;

user created

THANKS FOR VIEWING MY BLOG FOR MORE UPDATES FOLLOW ME OR SUBSCRIBE ME

ORA-32773 Operation not Supported for SmallFile Tablespace

ORA-32773 : Operation not Supported for SmallFile Tablespace

ORA-ERROR 32773 : Operation not Supported for SmallFile Tablespace

Problem: While Enabling autoextend for a Tablespace,it will get an error like ORA-32773 :Operation not supported for small file table space

SQL>alter tablespace CHAITANYASMALLTBS autoextend on;

alter tablespace CHAITANYASMALLTBS autoextend on

*

ERROR ar line 1:

ORA_32773:operation not supported for small file tablespace  CHAITANYASMALLTBS

Solution:

A standard tablespace that is small file tablespace can have multiple datafiles,so if you want to enable or disable autoextend on so you have done for each datafile not for tablespace  it cannot be done directly on the tablespace

SQL>select tablespace_name,BIGFILE from dba_tablespaces where tablespace_name like 'CHAITANYASMALLTBS';

TABLESPACE_NAME                     BIGFILE

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

CHAITANYASMALLTBS                     NO

SQL>select tablespace_name,file_name,AUTOEXTENSIBLE from dba_data_file where tablespace_name like 'CHAITANYASMALLTBS';

TABLESPACE_NAME        FILE_NAME               AUT

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

CHAITANYASMALLTBS  /oradata/prod/chaitu01.dbf     NO

CHAITANYASMALLTBS  /oradata/prod/chaitu02.dbf     NO

CHAITANYASMALLTBS  /oradata/prod/chaitu03.dbf     NO

SQL>alter database datafile '/oradata/prod/chaitu01.dbf' autoextend on;

Database altered;

SQL>alter database datafile '/oradata/prod/chaitu02.dbf' autoextend on;

Database altered;

SQL>alter database datafile '/oradata/prod/chaitu03.dbf' autoextend on;

Database altered;

SQL>select tablespace_name,file_name,AUTOEXTENSIBLE from dba_data_file where tablespace_name like 'CHAITANYASMALLTBS';

TABLESPACE_NAME        FILE_NAME               AUT

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

CHAITANYASMALLTBS  /oradata/prod/chaitu01.dbf     YES

CHAITANYASMALLTBS  /oradata/prod/chaitu02.dbf     YES

CHAITANYASMALLTBS  /oradata/prod/chaitu03.dbf     YES

In BIGFILE TABLESPACE we can do autoextensible in tablelevel also but it have only one datafile 

SQL> create bigfile tablespace CHAITANYABIGFTBS datafile '/oradata/prod/chaitubigfile01.dbf' size 2G;

Tablespace Created.

SQL>select tablespace_name,file_name,AUTOEXTENSIBLE from dba_data_files where tablespace_name like 'CHAITANYABIGFTBS';

TABLESPACE_NAME        FILE_NAME                                     AUT

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

CHAITANYABIGFTBS         /oradata/prod/chaitubigfile01.dbf         NO

SQL>select tablespace_name,BIGFILE from dba_tablespaces where tablespace_name like ' CHAITANYABIGFTBS';

TABLESPACE_NAME               BIG

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

CHAITANYABIGFTBS                YES

SQL>alter tablespace CHAITANYABIGFTBS autoextend on;

Tablespace altered

SQL>select tablespace_name,file_name,AUTOEXTENSIBLE from dba_data_files where tablespace_name like 'CHAITANYABIGFTBS';

TABLESPACE_NAME        FILE_NAME                                     AUT

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

CHAITANYABIGFTBS         /oradata/prod/chaitubigfile01.dbf         YES

THANKS FOR VIEWING MY BLOG FOR MORE UPDATES FOLLOW ME OR SUBSCRIBE ME

ORA-01536: Space Quotas Exceeded for Table Space for a Particular User

 ORA-01536: Space Quotas Exceeded for Table Space for a Particular User

Problem

while creating the table or insert the data values into the table user is getting an error  ORA-01536 :space quota exceeded for tablesapce

sql> create table accountmaster tablespace smalltbs as select * from dba_objects;

create table accountmaster tablespace smalltbs as select * from dba_objects;

*

ERROR at line 1;

ORA-01536 : space quota exceeded for tablespace 'SMALLTBS'

Solution

Table space quota is the allocated in a particular user in tablespace, Once the particular user reaches the max allocated space it shows error like this

SQL>select tablespace_name,username,bytes/1024/1024,max_bytes/1024/1024 from dba_ts_quotas where username like 'CHAITANYA';

TABLESPACE_NAME       USER_NAME  BYTES/1024/1024   MAX_BYTES /1024/1024

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

SMALLTBS                         CHAITANYA               18                          25

Here CHAITANYA user can have maximum upto 25 MB space  of SMALLTBS tablespace but currently  it reaches used upto 18 MB

SQL> create table accountmaster tablespace smalltbs as select * from dba_objects;

create table accountmaster tablespace smalltbs as select * from dba_objects;

*

ERROR at line 1;

ORA-01536 : space quota exceeded for tablespace 'SMALLTBS'

Let us start the  process to start the fix it and increase the quota for that user 'CHAITANYA'

SQL> alter user CHAITANYA quota 100M on SMALLTBS;

user altered

SQL>select tablespace_name,username,bytes/1024/1024,max_bytes/1024/1024 from dba_ts_quotas where username like 'CHAITANYA';

TABLESPACE_NAME       USER_NAME  BYTES/1024/1024   MAX_BYTES /1024/1024

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

SMALLTBS                         CHAITANYA               18                         100

Now create the table and insert the table values into that particular user

SQL> create table accountmaster tablespace smalltbs as select * from dba_objects;

create table accountmaster tablespace smalltbs as select * from dba_objects;

table created sucessfully

SQL>select tablespace_name,username,bytes/1024/1024,max_bytes/1024/1024 from dba_ts_quotas where username like 'CHAITANYA';

TABLESPACE_NAME       USER_NAME  BYTES/1024/1024   MAX_BYTES /1024/1024

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

SMALLTBS                         CHAITANYA               30                        100

THANKS FOR VIEWING MY BLOG FOR MORE UPDATES FOLLOW ME OR SUBSCRIBE ME