A client asked about why it takes 14.96 seconds for below sql(1):
SQL>select * from dba_tables; Elapsed: 00:00:14.96
While it takes only 0.59 second for this sql(2):
SQL>select count(*) from dba_tables; Elapsed: 00:00:00.59
Let’s compare the tkprof summary from sql tracing for those two sqls:
Read more of this content when you subscribe today.
Setting “ADMIN_RESTRICTIONS_listener_name=on” disables the runtime
modification of parameters in listener.ora.
Got error message “TNS-12508: TNS:listener could not resolve the COMMAND given” when trying to switch off listener log.
LSNRCTL> set log_status off Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=racnode1)(PORT=1521))) TNS-12508: TNS:listener could not resolve the COMMAND given LSNRCTL>
Read more of this content when you subscribe today.
Suppress noisy alerts by re-configure the default metric.
Everyday at the same time, there is an OEM 12c alert for the 10g listener as below:
Host=racnode1.raccluster Target type=Listener Target name=LISTENER_racnode1.raccluster Message=TNS-12508. Please check log for details. Severity=Critical Event reported time=Dec 1, 2016 5:41:19 PM EST
Information in LISTENER.log :
01-DEC-2016 17:34:21 * version * 0 01-DEC-2016 17:34:21 * version * 0 01-DEC-2016 17:34:22 * 12508 TNS-12508: TNS:listener could not resolve the COMMAND given WARNING: Subscription for node down event still pending 01-DEC-2016 17:34:22 * (CONNECT_DATA=(CID=(PROGRAM=)(HOST=racnode1.raccluster)(USER=oracle))(COMMAND=status)(ARGUMENTS=64)(SERVICE=LISTENER)(VERSION=186647552)) * status * 0 01-DEC-2016 17:34:22 * log_directory * 0 01-DEC-2016 17:34:22 * trc_directory * 0
From LISTENER.log, most likely one command does not exist or failed while running against the 10g LISTENER. Some doc said it is trying to run “show oracle_home” which is not available for 10g listener. After this failing command, there are two successful commands against “log_directory” and “trc_directory”.
LSNRCTL> show oracle_home NL-00853: Message 853 not found; No message file for product=network, facility=NL [ show] [oracle_home] [ show]
After manually running “show oracle_home” against 10g LISTENER, I did NOT see “TNS-12508: TNS:listener could not resolve the COMMAND given” recorded in LISTENER.log as per Doc ID 1596633.1
Suppress the alerts by changing the default threshold for “TNS Errors” metric from:
TNS-[ ]*0*(1169|1189|12508|1190)
to:
TNS-[ ]*0*(1169|1189|1190)
“Upgrading the Database Plug-in to version 12.1.0.6 or higher” as per (Doc ID 1596633.1) is NOT working, since our database plug-in is 12.1.0.8 already.
ouafDatabasePatch.cmd requires Instance Name ( SID ) instead of Service Name.
When installing Rollup Pack for Oracle Utilities Application Framework Version 4.3.0.1.0, ran ouafDatabasePatch.cmd command , and got the following errors:
D:\RMB\RMB-V2.5.0.1.0-FW-PREREQ-MultiPlatform\FW-V4.3.0.1.0-Rollup\Database>set TOOLSBIN=D:\dbpatch_tools\bin D:\RMB\RMB-V2.5.0.1.0-FW-PREREQ-MultiPlatform\FW-V4.3.0.1.0-Rollup\Database>set JAVA_HOME=D:\java D:\RMB\RMB-V2.5.0.1.0-FW-PREREQ-MultiPlatform\FW-V4.3.0.1.0-Rollup\Database>ouafDatabasePatch.cmd "CMDLINE::: D:\java\bin\java.exe -cp D:\RMB\RMB-V2.5.0.1.0-FW-PREREQ-MultiPlatform\dbpatch_tools\lib\*;D:\RMB\RMB-V2.5.0.1.0-FW-PREREQ-MultiPlatform\dbpatch_tools\config com.oracle.ouaf.database.patch.OUAFPatch" Enter the target database type (O/M/D) [O]: O Enter the username that owns the schema: CISADM Enter the password for the CISADM user: XXXXXX Enter the name of the Oracle Database Connection String: ractest-scan.ractest.local:1521:ORMB Couldn't connect to database ORACLE ractest-scan.ractest.local:1521:ORMB CISADM : java.sql.SQLException: Listener refused the connection with the following error: ORA-12505, TNS:listener does not currently know of SID given in connect descriptor
In connection string “ractest-scan.ractest.local:1521:ORMB”, ORMB is a service name instead of instance name.
1)Apply patch 22505470: PATCHES APPEND EXTRA SPACE TO STRINGS AND ADD DBSERVICE CONNECTION SUPPORT.
2) Replace D:\dbpatch_tools\lib with new ones.
OR
2) Add below parameter into LISTENER.ora , and bounce/reload the listener.
USE_SID_AS_SERVICE_listener=on
Listener port should be dedicated to listener only.
Trying to create a dedicated Listener for building active standby database without touching current listeners in a critical production environment. Then got those errors:
LSNRCTL for Linux: Version 11.2.0.4.0 - Production on 11-OCT-2016 14:24:31 Copyright (c) 1991, 2013, Oracle. All rights reserved. Starting /u01/app/oracle/product/11.2.0/dbhome_2/bin/tnslsnr: please wait... TNSLSNR for Linux: Version 11.2.0.4.0 - Production System parameter file is /u01/app/oracle/product/11.2.0/dbhome_2/network/admin/listener.ora Log messages written to /u01/app/oracle/diag/tnslsnr/racnode1/listener_standby/alert/log.xml Error listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=racnode1)(PORT=8888))) TNS-12542: TNS:address already in use TNS-12560: TNS:protocol adapter error TNS-00512: Address already in use Linux Error: 98: Address already in use Listener failed to start. See the error message(s) above...
Checked port 8888 has been used :
$ netstat -na|grep -i 8888 tcp 0 0 127.0.0.1:34244 127.0.0.1:8888 TIME_WAIT tcp 0 0 127.0.0.1:34245 127.0.0.1:8888 TIME_WAIT tcp 0 0 ::ffff:127.0.0.1:8888 :::* LISTEN
It is resolved by using a free port 9999.
$lsnrctl start LISTENER_STANDBY $lsnrctl status LISTENER_STANDBY LSNRCTL for Linux: Version 11.2.0.4.0 - Production on 11-OCT-2016 15:09:53 Copyright (c) 1991, 2013, Oracle. All rights reserved. Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=racnode1)(PORT=9999))) STATUS of the LISTENER ------------------------ Alias LISTENER_STANDBY Version TNSLSNR for Linux: Version 11.2.0.4.0 - Production Start Date 11-OCT-2016 15:07:36 Uptime 0 days 0 hr. 2 min. 17 sec Trace Level off Security ON: Local OS Authentication SNMP OFF Listener Parameter File /u01/app/oracle/product/11.2.0/dbhome_2/network/admin/listener.ora Listener Log File /u01/app/oracle/diag/tnslsnr/racnode1/listener_standby/alert/log.xml Listening Endpoints Summary... (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=racnode1)(PORT=9999))) Services Summary... Service "TSTSTBY1" has 1 instance(s). Instance "TSTSTBY1", status UNKNOWN, has 1 handler(s) for this service... The command completed successfully
Make DBA Life Easy 