TNS-12537: TNS:connection closed
When creating the database
Oracle Version 220.127.116.11.0 on Linux Version 5.10 Listener Creation failed during Net Configuration setup. Database however got created successfully.
Post database creation database had to be started manually by giving pfile name as parameter in startup command
sqlplus / as sysdba
To create pfile from spfile following command is to be issued from sql command prompt
create pfile='<directory_path>/initORCL.ora’ from spfile='<directory_path>/spfileORCL.ora’
litener.ora and tnsnames.ora also needed to be created using any standard template for the same.
When starting the listener however by issuing the following command
System is throwing the following error
LSNRCTL for Linux: Version 18.104.22.168.0 – Production on 26-JUL-2017 23:07:53
Copyright (c) 1991, 2009, Oracle. All rights reserved.
Starting /u01/app/oracle/product/11.2.0/db_1/bin/tnslsnr: please wait…
TNS-12537: TNS:connection closed
TNS-12560: TNS:protocol adapter error
TNS-00507: Connection closed
Linux Error: 29: Illegal seek
After quite a bit of searching on the net following was the solution to the problem
Following entry in /etc/hosts was commented out
127.0.0.1 localhost.localdomain localhost
Once this entry was made, listener started successfully.
There can be other reasons also for this error like the permissions for oracle files and directories not being set correctly and will need to be checked and verified separately.
Oracle table flashback – ORA-01555: snapshot too old
To flashback a table to a certains point in time, you can issue the below command.
FLASHBACK TABLE DEPARTMENTS TO TIMESTAMP TO_TIMESTAMP(’25-AUG-2016 09:15:01′, ‘DD-Mon-RRRR HH24:MI:SS’);
SQL Error: ORA-08189: cannot flashback the table because row movement is not enabled
08189. 00000 – “cannot flashback the table because row movement is not enabled”
*Cause: An attempt was made to perform Flashback Table operation on a table for
which row movement has not been enabled. Because the Flashback Table
does not preserve the rowids, it is necessary that row
movement be enabled on the table.
*Action: Enable row movement on the table
ALTER TABLE DEPARTMENTS ENABLE ROW MOVEMENT;
After enabling row movement if you get the below error
FLASHBACK TABLE HR.DEPARTMENTS TO TIMESTAMP TO_TIMESTAMP(’26-AUG-2016 09:15:01′, ‘DD-Mon-RRRR HH24:MI:SS’)
Error report –
SQL Error: ORA-00604: error occurred at recursive SQL level 1
ORA-12801: error signaled in parallel query server P002
ORA-01555: snapshot too old:
rollback segment number 2 with name “_SYSSMU2_788469661$” too small
00604. 00000 – “error occurred at recursive SQL level %s”
*Cause: An error occurred while processing a recursive SQL statement
(a statement applying to internal dictionary tables).
*Action: If the situation described in the next error on the stack
can be corrected, do so; otherwise contact Oracle Support.
show parameter undo_retention
NAME TYPE VALUE
undo_retention integer 300
This parameter is an indicator of the time in seconds the data for this table will be in UNDO. In this case the table data before modification will be in UNDO for 300 seconds.
Undo_retention does not mean it will delete all undo data older than this. If it does not need space it keeps them.
Similarly it does not guarantee that it will retain data in the undo segment for this period of time.
Generally, if the transaction volumes are high and oracle need the undo space, lesser is the time for which undo data will be kept.
If you want the data to be retained in the undo segment for the time specified by the undo_retention parameter, the RETENTION_GUARANTEE parameter can be set to true.
The v$undostat dynamic performance view gives information on setting the value of this parameter to an optimum value.
This view contains data for last 4 days and the information in this view is updated every 10 mins.
If the value of this parameter is too low then there will be limitations in
* flashing back a table beyond a certain time.
* executing long running queries.
In both cases you will receive the ORA-1555 error, “snapshot too old.”.