Hello everyone! This week we are going to see a very common and simple problem to solve. It is a bug in Oracle trace files when they are more than 4 GB.
This type of error causes unavailability in the service so we must solve it as soon as possible so that it impacts as little as possible. TO THE MESS!
We start from the next scenario:
We return to the office, after a fruitful night of rest, phone in hand we receive a call from the client, we can not connect to our database from remote machine. 😱😱😱
We connect to the machine, and we see that when we try to connect the listener, we see that the wait is eternalized. 🤔
For this reason, we enter the LSNRCTL utility and execute a status:
LSNRCTL> status listener Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=) (PORT=1521))) TNS-12547: TNS:lost contact TNS-12560: TNS:protocol adapter error TNS-00517: Lost contact 64-bit Windows Error: 54: Unknown error
In addition, in the log we can see the following errors:
TNS-12518: TNS:listener could not hand off client connection TNS-12571: TNS:packet writer failure TNS-12560: TNS:protocol adapter error TNS-00530: Protocol adapter error 64-bit Windows Error: 58: Unknown error
In other words, the state or lsnrctl services are not responsive.
All this is produced by a BUG in which from the 4GB of trace chips of, the LISTENER, the connection slows down until it stops work. Windows has a hard time handling this size for text files flat so it leaves the services non-operational.
How do we fix this bug?
To solve it we go to the Oracle services in Windows and manually stop the listener.
Then we go to the log path
$ORACLE_BASE\diag\tnslsnr\\listener\trace\ .log
And we delete the file directly (Oracle will create it when you try to write again).
In addition, we have another option, to make LSNRCTL not use logfile.
LSNRCTL>set current_listenerLSNRCTL>set log_status OFF LSNRCTL>save_config
If you want to be up to date with all the entries we publish, you just have to subscribe to our blog. We will only send you a single email per month and you can unsubscribe whenever you want without any problem.
Still do not know Query Performance ? Find out how it can help you in your Oracle environment. More information on their page LinkedIn .
Follow GPS on LinkedIn
We hope that this post has been useful to you. See you in future posts.
See more problems and solutions in: https://www.gpsos.es/2018/03/creacion-fichero-tnsnames/
Official documentation: https://support.oracle.com/knowledge/Oracle%20Database%20Products/1319797_1.html