TNS-12542: TNS:address Already In Use

PROBLEM:

While starting the listener, getting an error like

TNS-12542: TNS:address already in use

# lsnrctl start LISTENER_TEST

LSNRCTL for Solaris: Version 12.1.0.2.0 - Production on 03-SEP-2018 11:06:57

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

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

TNSLSNR for Solaris: Version 12.1.0.2.0 - Production

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

Log messages written to /oracle/app/oracle/diag/tnslsnr/ram.doyen.com/listener_test2/alert/log.xml

Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=ram.doyen.com)(PORT=1524)))

Error listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=ram.doyen.com)(PORT=1524)))

TNS-12542: TNS:address already in use 

TNS-12560: TNS:protocol adapter error

  TNS-00512: Address already in use

   Solaris Error: 125: Address already in use

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

SOLUTION:

To find out the issue, Let’s check the content of the listener in listener.ora file.

LISTENER_TEST =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = ram.doyen.com)(PORT = 1524)) --- >>> 
(ADDRESS = (PROTOCOL = TCP)(HOST = ram.doyen.com)(PORT = 1524)) --- >>>

)
)

SID_LIST_LISTENER_TEST =
(SID_LIST =
(SID_DESC =
(SID_NAME = FRANCE)
(ORACLE_HOME = /oracle/app/oracle/product/12.1.0.2/dbhome_1)
)
)

Inside the listener entry, we have 2 ADDRESS entries with same host(ram.doyen.com) and same port number(1524) .

So starting the listener is failing with conflict.

To fix the issue, give different ports for both the ADDRESS entries.

the listener will look as below:( 1524 and 1526)

LISTENER_TEST =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = ram.doyen.com)(PORT = 1524)) --- >>> 
(ADDRESS = (PROTOCOL = TCP)(HOST = ram.doyen.com)(PORT = 1526)) --- >>>
)
)

SID_LIST_LISTENER_TEST =
(SID_LIST =
(SID_DESC =
(SID_NAME = FRANCE)
(ORACLE_HOME = /oracle/app/oracle/product/12.1.0.2/dbhome_1)
)
)

Now start the listener.

# lsnrctl start LISTENER_TEST

LSNRCTL for Solaris: Version 12.1.0.2.0 - Production on 03-JAN-2019 12:08:09

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

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

TNSLSNR for Solaris: Version 12.1.0.2.0 - Production
System parameter file is /oracle/app/oracle/product/12.1.0.2/dbhome_1/network/admin/listener.ora
Log messages written to /oracle/app/oracle/diag/tnslsnr/ram.doyen.com/listener_test/alert/log.xml
Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=ram.doyen.com)(PORT=1524)))
Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=ram.doyen.com)(PORT=1526)))

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=ram.doyen.com)(PORT=1524)))
STATUS of the LISTENER
------------------------
Alias LISTENER_TEST
Version TNSLSNR for Solaris: Version 12.1.0.2.0 - Production
Start Date 03-JAN-2019 12:08: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_1/network/admin/listener.ora
Listener Log File /oracle/app/oracle/diag/tnslsnr/ram.doyen.com/listener_test/alert/log.xml
Listening Endpoints Summary...
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=ram.doyen.com)(PORT=1524)))
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=ram.doyen.com)(PORT=1526)))
Services Summary...
Service "FRANCE" has 1 instance(s).
Instance "FRANCE", status UNKNOWN, has 1 handler(s) for this service...
The command completed successfully

Listener started successfully.

Listener started successfully and listening on both 1524 and 1526 port. So in simple words, port should be unique for each ADDRESS entry of the listener.

Catch Me On:- Hariprasath Rajaram 

Telegram:https://t.me/joinchat/I_f4DkeGfZuxgMIoJSpQZg LinkedIn:https://www.linkedin.com/in/hari-prasath-aa65bb19/ Facebook:https://www.facebook.com/HariPrasathdba                        FB Group:https://www.facebook.com/groups/894402327369506/                 FB Page: https://www.facebook.com/dbahariprasath/? Twitter: https://twitter.com/hariprasathdba

ORA-01950: no privileges on tablespace ‘USERS’

PROBLEM:

While creating a table using CTAS, got an error,                                                    ORA-01950: no privileges on tablespace ‘USERS’.

SQL> create table FRANCE.EMPLO as select * from user_objects;
create table EMPLO as select * from user_objects
*
ERROR at line 1:
ORA-01950: no privileges on tablespace ‘USERS’

SOLUTION:

This error comes, when the user the user doesn’t have tablespace quota.

1. Check the tablespace quota.

select username,tablespace_name,bytes/1024/1024/1024, MAX_BYTES/1024/1024/1024 from dba_ts_quotas where username='FRANCE';

no rows selected.

2. Grant some QUOTA to the user.

Now we user FRANCE has 5G quota on tablespace USERS. Let’s run the same create statement.
SQL> create table FRANCE.EMPLO as select * from user_objects;

Table created.
It worked. Now, check how much quota has been used.
SQL> select username,tablespace_name,bytes/1024/1024/1024, MAX_BYTES/1024/1024/1024 from dba_ts_quotas where username='FRANCE';

USERNAME TABLESPACE_NAME BYTES/1024/1024/1024 MAX_BYTES/1024/1024/1024
--------  -------------- -------------------   -------------------- 
FRANCE         USERS        .005493164                  5
Catch Me On:- Hariprasath Rajaram 

Telegram:https://t.me/joinchat/I_f4DkeGfZuxgMIoJSpQZg LinkedIn:https://www.linkedin.com/in/hari-prasath-aa65bb19/ Facebook:https://www.facebook.com/HariPrasathdba                      FB Group:https://www.facebook.com/groups/894402327369506/                 FB Page: https://www.facebook.com/dbahariprasath/? Twitter: https://twitter.com/hariprasathdba