Skip to Main Content
Forums

Oracle Database Discussions

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

log file sync and block recovery from logseq

User_96XRQNov 5 2020

Hi all,
sometimes log file sync event spike on our 11.2.0.4 db on Solaris.
when we look at alert log at the same time we see following messages.

Block recovery from logseq 1462532, block 36595759 to scn 3522018885687
Recovery of Online Redo Log: Thread 1 Group 59 Seq 1462532 Reading mem 0
Mem# 0: +REDO/DBNAME/onlinelog/group_59.272.1047579261
Mem# 1: +REDO/DBNAME/onlinelog/group_59.356.1047581307
Block recovery completed at rba 1462532.36596324.16, scn 820.145702968
Block recovery from logseq 1462532, block 36594513 to scn 3522018885686
Recovery of Online Redo Log: Thread 1 Group 59 Seq 1462532 Reading mem 0
Mem# 0: +REDO/DBNAME/onlinelog/group_59.272.1047579261
Mem# 1: +REDO/DBNAME/onlinelog/group_59.356.1047581307
Block recovery completed at rba 1462532.36596324.16, scn 820.145702967

there is no message on lgwr trace and wait chains are below.

%This SECONDS AAS WAIT_CHAIN
------ ---------- ------- --------------------------------------------------------
32% 2985 96.3 -> log file sync -> LGWR wait for redo copy -> ON CPU
26% 2421 78.1 -> ON CPU
21% 1911 61.6 -> log file sync -> log file parallel write

Do you have reason for that?
Regards.

Comments

sb92075
from DB Server system issue following commands

lsnrctl status
lsnrctl service

COPY commands & results then PASTE all back here
772262
[oracle@bnl11237dat01 trace]$ lsnrctl status

LSNRCTL for Linux: Version 11.2.0.2.0 - Production on 07-JUN-2011 17:33:36

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

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=LISTENER)))
STATUS of the LISTENER
------------------------
Alias LISTENER
Version TNSLSNR for Linux: Version 11.2.0.2.0 - Production
Start Date 02-JUN-2011 15:56:50
Uptime 5 days 1 hr. 36 min. 46 sec
Trace Level off
Security ON: Local OS Authentication
SNMP ON
Listener Parameter File /u01/app/11.2.0/grid/network/admin/listener.ora
Listener Log File /u01/app/oracle/diag/tnslsnr/bnl11237dat01/listener/alert/log.xml
Listening Endpoints Summary...
(DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=LISTENER)))
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=127.0.0.1)(PORT=1521)))
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=10.32.24.55)(PORT=1521)))
Services Summary...
Service "DWH" has 1 instance(s).
Instance "DWH11", status READY, has 1 handler(s) for this service...
Service "DWH1XDB" has 1 instance(s).
Instance "DWH11", status READY, has 1 handler(s) for this service...
Service "MDM1" has 1 instance(s).
Instance "MDM11", status READY, has 1 handler(s) for this service...
Service "MDM1XDB" has 1 instance(s).
Instance "MDM11", status READY, has 1 handler(s) for this service...
The command completed successfully


[oracle@bnl11237dat01 trace]$ lsnrctl service

LSNRCTL for Linux: Version 11.2.0.2.0 - Production on 07-JUN-2011 17:34:06

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

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=LISTENER)))
Services Summary...
Service "DWH" has 1 instance(s).
Instance "DWH11", status READY, has 1 handler(s) for this service...
Handler(s):
"DEDICATED" established:4 refused:0 state:ready
LOCAL SERVER
Service "DWH1XDB" has 1 instance(s).
Instance "DWH11", status READY, has 1 handler(s) for this service...
Handler(s):
"D000" established:0 refused:0 current:0 max:1022 state:ready
DISPATCHER <machine: bnl11237dat01, pid: 8294>
(ADDRESS=(PROTOCOL=tcp)(HOST=bnl11237dat01)(PORT=31718))
Service "MDM1" has 1 instance(s).
Instance "MDM11", status READY, has 1 handler(s) for this service...
Handler(s):
"DEDICATED" established:6144 refused:0 state:ready
LOCAL SERVER
Service "MDM1XDB" has 1 instance(s).
Instance "MDM11", status READY, has 1 handler(s) for this service...
Handler(s):
"D000" established:0 refused:0 current:0 max:1022 state:ready
DISPATCHER <machine: bnl11237dat01, pid: 8206>
(ADDRESS=(PROTOCOL=tcp)(HOST=bnl11237dat01)(PORT=18921))
The command completed successfully


ON NODE 2
[oracle@bnl11237dat02 ~]$ lsnrctl status

LSNRCTL for Linux: Version 11.2.0.2.0 - Production on 07-JUN-2011 17:34:40

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

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=LISTENER)))
STATUS of the LISTENER
------------------------
Alias LISTENER
Version TNSLSNR for Linux: Version 11.2.0.2.0 - Production
Start Date 19-MAY-2011 12:17:45
Uptime 19 days 5 hr. 16 min. 55 sec
Trace Level off
Security ON: Local OS Authentication
SNMP ON
Listener Parameter File /u01/app/11.2.0/grid/network/admin/listener.ora
Listener Log File /u01/app/oracle/diag/tnslsnr/bnl11237dat02/listener/alert/log.xml
Listening Endpoints Summary...
(DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=LISTENER)))
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=127.0.0.1)(PORT=1521)))
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=10.32.24.56)(PORT=1521)))
Services Summary...
Service "DWH" has 1 instance(s).
Instance "DWH12", status READY, has 1 handler(s) for this service...
Service "DWH1XDB" has 1 instance(s).
Instance "DWH12", status READY, has 1 handler(s) for this service...
Service "MDM1" has 1 instance(s).
Instance "MDM12", status READY, has 1 handler(s) for this service...
Service "MDM1XDB" has 1 instance(s).
Instance "MDM12", status READY, has 1 handler(s) for this service...
The command completed successfully
[oracle@bnl11237dat02 ~]$


[oracle@bnl11237dat02 ~]$ lsnrctl service

LSNRCTL for Linux: Version 11.2.0.2.0 - Production on 07-JUN-2011 17:34:51

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

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=LISTENER)))
Services Summary...
Service "DWH" has 1 instance(s).
Instance "DWH12", status READY, has 1 handler(s) for this service...
Handler(s):
"DEDICATED" established:11 refused:0 state:ready
LOCAL SERVER
Service "DWH1XDB" has 1 instance(s).
Instance "DWH12", status READY, has 1 handler(s) for this service...
Handler(s):
"D000" established:0 refused:0 current:0 max:1022 state:ready
DISPATCHER <machine: bnl11237dat02, pid: 9488>
(ADDRESS=(PROTOCOL=tcp)(HOST=bnl11237dat02)(PORT=16589))
Service "MDM1" has 1 instance(s).
Instance "MDM12", status READY, has 1 handler(s) for this service...
Handler(s):
"DEDICATED" established:22053 refused:0 state:ready
LOCAL SERVER
Service "MDM1XDB" has 1 instance(s).
Instance "MDM12", status READY, has 1 handler(s) for this service...
Handler(s):
"D000" established:0 refused:0 current:0 max:1022 state:ready
DISPATCHER <machine: bnl11237dat02, pid: 2688>
(ADDRESS=(PROTOCOL=tcp)(HOST=bnl11237dat02)(PORT=12553))
The command completed successfully
[oracle@bnl11237dat02 ~]$
sb92075
http://www.orafaq.com/wiki/JDBC

need to decide whether connecting to SID or SERVICE & utilize proper syntax & name

Old syntax

jdbc:oracle:thin:@[HOST][:PORT]:SID

New syntax

jdbc:oracle:thin:@//[HOST][:PORT]/SERVICE


Oracle correctly threw error for posted request
772262
thanks for the url

I am having a rac database ,, connect with a cluster name and service name
the example given is for a single instance
ORCL =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = myhost)(PORT = 1521))
(CONNECT_DATA =
(SID = ORCL)
)
)




so can I use service name DWH as I don't have SID
DWH =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = cc2pre01cl)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = DWH)
)
)
sb92075
JDBC does not utilize or require any tnsnames.ora file or entry.
772262
<?xml version="1.0" encoding="UTF-8" ?>
- <repositories>
- <connection>
<name>DWH_ETL_REPO</name>
<server*>//cc2pre01cl*</server>
<type>ORACLE</type>
<access>Native</access>
<database*>/DWH*</database>
<port>1521</port>
<username>DWH_ETL_REPO</username>
<password>DWH_ETL_REPO</password>
<servername />
<data_tablespace />
<index_tablespace />
- <attributes>
- <attribute>
<code>FORCE_IDENTIFIERS_TO_LOWERCASE</code>
<attribute>N</attribute>
</attribute>
- <attribute>
<code>FORCE_IDENTIFIERS_TO_UPPERCASE</code>
<attribute>N</attribute>
</attribute>
- <attribute>
<code>IS_CLUSTERED</code>
<attribute>N</attribute>
</attribute>
- <attribute>
<code>PORT_NUMBER</code>
<attribute>1521</attribute>
</attribute>
- <attribute>
<code>QUOTE_ALL_FIELDS</code>
<attribute>N</attribute>
</attribute>
- <attribute>
<code>SUPPORTS_BOOLEAN_DATA_TYPE</code>
<attribute>N</attribute>
</attribute>
- <attribute>
<code>USE_POOLING</code>
<attribute>N</attribute>
</attribute>
</attributes>
</connection>
- <repository>
<id>KettleDatabaseRepository</id>
<name>DWH_ETL_REPO</name>
<description>DWH_ETL_REPO</description>
<connection>DWH_ETL_REPO</connection>
</repository>
</repositories>



I have given //<cluster_name> and /<database_name>
.. still I get the same error
772262
<?xml version="1.0" encoding="UTF-8" ?>
- <repositories>
- <connection>
<name>DWH_ETL_REPO</name>
<server/>
<type>ORACLE</type>
<access>Native</access>
<database>//cc2pre01cl:1521/DWH</database>
<port/>
<username>DWH_ETL_REPO</username>
<password>DWH_ETL_REPO</password>
<servername />
<data_tablespace />
<index_tablespace />
- <attributes>
- <attribute>
<code>FORCE_IDENTIFIERS_TO_LOWERCASE</code>
<attribute>N</attribute>
</attribute>
- <attribute>
<code>FORCE_IDENTIFIERS_TO_UPPERCASE</code>
<attribute>N</attribute>
</attribute>
- <attribute>
<code>IS_CLUSTERED</code>
<attribute>N</attribute>
</attribute>
- <attribute>
<code>PORT_NUMBER</code>
<attribute>1521</attribute>
</attribute>
- <attribute>
<code>QUOTE_ALL_FIELDS</code>
<attribute>N</attribute>
</attribute>
- <attribute>
<code>SUPPORTS_BOOLEAN_DATA_TYPE</code>
<attribute>N</attribute>
</attribute>
- <attribute>
<code>USE_POOLING</code>
<attribute>N</attribute>
</attribute>
</attributes>
</connection>
- <repository>
<id>KettleDatabaseRepository</id>
<name>DWH_ETL_REPO</name>
<description>DWH_ETL_REPO</description>
<connection>DWH_ETL_REPO</connection>
</repository>
</repositories>


Cannot connect to Oracle 11g (11.2.0.2) RAC database using JDBC (ojdbc14-10.2.0.3.0.jar ) driver.

I followed this link http://www.orafaq.com/wiki/JDBC. but still can't connect to RAC database.

Any help will be highly appreciated.
1 - 7

Post Details

Added on Nov 5 2020
#11gr2, #general-database-discussions, #performance, #tuning
3 comments
649 views