Somehow, our database instance is not able to connect to the database instance when no any Oracle server files were modified, listed below :
Database Instance : UP, Listener : DOWN, Agent Connection to Instance : DOWN
The version is Oracle Database 11g Release 2 (11.2.0.1)
When run this command using sql plus :
select value from v$parameter where name='service_names';
The output is : orcl1
Here's my listener.ora (D:\Oracle\product\11.2.0\dbhome_1\NETWORK\ADMIN) :
LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = XXXDS1)(PORT = 1521))
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
)
)
ADR_BASE_LISTENER = c:\Oracle
tnsnames.ora :
ORCL1 =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.0.12)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SID = ORCL1)
(SERVICE_NAME = ORCL1)
)
)
log.xml for the first error occured :
<msg time='2021-02-20T09:19:24.203+07:00' org_id='oracle' comp_id='tnslsnr'
type='UNKNOWN' level='16' host_id='XXXDS1'
host_addr='::1'>
<txt>20-FEB-2021 09:19:24 * (CONNECT_DATA=(CID=(PROGRAM=)(HOST=__jdbc__)(USER=XXXDS1$))(SERVICE_NAME=orcl1)) * (ADDRESS=(PROTOCOL=tcp)(HOST=192.168.0.12)(PORT=56955)) * establish * orcl1 * 12560
</txt>
</msg>
<msg time='2021-02-20T09:19:34.343+07:00' org_id='oracle' comp_id='tnslsnr'
type='UNKNOWN' level='16' host_id='XXXDS1'
host_addr='::1'>
<txt>TNS-12560: TNS:protocol adapter error
TNS-00530: Protocol adapter error
64-bit Windows Error: 53: Unknown error
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: 54: Unknown error
</txt>
</msg>
Another error from log.xml :
<msg time='2021-02-20T10:51:15.390+07:00' org_id='oracle' comp_id='tnslsnr'
type='UNKNOWN' level='16' host_id='XXXDS1'
host_addr='::1'>
<txt>20-FEB-2021 10:51:15 * (CONNECT_DATA=(SID=orcl1)(CID=(PROGRAM=D:\Oracle\product\11.2.0\dbhome_1\bin\emagent.exe)(HOST=XXXDS1)(USER=SYSTEM))) * (ADDRESS=(PROTOCOL=tcp)(HOST=192.168.0.12)(PORT=49401)) * establish * orcl1 * 12505
</txt>
</msg>
<msg time='2021-02-20T10:51:25.514+07:00' org_id='oracle' comp_id='tnslsnr'
type='UNKNOWN' level='16' host_id='XXXDS1'
host_addr='::1'>
<txt>TNS-12505: TNS:listener does not currently know of SID given in connect descriptor
</txt>
</msg>
<msg time='2021-02-20T10:51:42.490+07:00' org_id='oracle' comp_id='tnslsnr'
type='UNKNOWN' level='16' host_id='XXXDS1'
host_addr='::1'>
<txt>20-FEB-2021 10:51:35 * (CONNECT_DATA=(SID=orcl1)(CID=(PROGRAM=D:\Oracle\product\11.2.0\dbhome_1\perl\bin\perl.exe)(HOST=XXXDS1)(USER=SYSTEM))) * (ADDRESS=(PROTOCOL=tcp)(HOST=::1)(PORT=49406)) * establish * orcl1 * 12505
</txt>
</msg>
<msg time='2021-02-20T10:51:52.614+07:00' org_id='oracle' comp_id='tnslsnr'
type='UNKNOWN' level='16' host_id='XXXDS1'
host_addr='::1'>
<txt>TNS-12505: TNS:listener does not currently know of SID given in connect descriptor
</txt>
</msg>
<msg time='2021-02-20T10:52:02.738+07:00' org_id='oracle' comp_id='tnslsnr'
type='UNKNOWN' level='16' host_id='XXXDS1'
host_addr='::1'>
<txt>20-FEB-2021 10:52:02 * (CONNECT_DATA=(CID=(PROGRAM=)(HOST=__jdbc__)(USER=XXXDS1$))(SERVICE_NAME=orcl1)) * (ADDRESS=(PROTOCOL=tcp)(HOST=192.168.0.12)(PORT=49408)) * establish * orcl1 * 12514
</txt>
</msg>
<msg time='2021-02-20T10:52:12.862+07:00' org_id='oracle' comp_id='tnslsnr'
type='UNKNOWN' level='16' host_id='XXXDS1'
host_addr='::1'>
<txt>TNS-12514: TNS:listener does not currently know of service requested in connect descriptor
</txt>
</msg>
Another error from log.xml :
<msg time='2021-02-20T10:53:40.705+07:00' org_id='oracle' comp_id='tnslsnr'
type='UNKNOWN' level='16' host_id='XXXDS1'
host_addr='::1'>
<txt>TNS-12571: TNS:packet writer failure
TNS-12560: TNS:protocol adapter error
TNS-00530: Protocol adapter error
64-bit Windows Error: 54: Unknown error
TNS-12514: TNS:listener does not currently know of service requested in connect descriptor
</txt>
</msg>
<msg time='2021-02-20T10:53:50.844+07:00' org_id='oracle' comp_id='tnslsnr'
type='UNKNOWN' level='16' host_id='XXXDS1'
host_addr='::1'>
<txt>Dynamic address is already listened on (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=XXXDS1.xxxad.local)(PORT=1521)))
</txt>
</msg>
These error was written several times
The lsnrctl status is READY
How can this case suddenly happen and how to fix it?
Sincerely,
Oviedityanto
Oke I found the answer
My listener.log file is 4Gb. So I changed the file name, restart services and it works. Speed performance when open enterprise manager increased too.
Thank you