Listener Issues after patching oracle binaries in oracle database 10.2.0.4

I had patched my oracle database binaries of 10.2.0.4 with patch 6460304 but i had to do this by creating a new oracle home for my database as this was a prod database and could not bring these binaries down by bringing down all the dependent databases as they were very critical. So i created a new oracle binaries 10.2.0.4 and then applied this patch on this.

So first brought down the listeners running for the database and database as well. Then i changed the oratab entries and tried bringing up the databases and the listeners.


Did the below steps as per oracle documentation:
# Downloaded the patch from oracle and then hosted in my unix solaris machine.
# % cd 6460304
#
#
# Ensure that the directory containing the opatch script appears in
# your $PATH; then enter the following command:
#
# % opatch apply
#
#
# 4. PostInstall Steps:
#
# cd rdbms/admin
# sqlplus "/ as sysdba"
@@catnomtt.sql
@@dbmsmeta.sql
@@dbmsmeti.sql
@@dbmsmetu.sql
@@dbmsmetb.sql
@@dbmsmetd.sql
@@catmeta.sql
@@prvtmeta.plb
@@prvtmeti.plb
@@prvtmetu.plb
@@prvtmetb.plb
@@prvtmetd.plb
@@catmet2.sql

ORCL:/app/oracle/product/10.2.0.4_64_v2:Y ---> My new Oracle Home location

LISTENER_ORCL:/app/oracle/product/10.2.0.4_64_v2:N ---> My new listener location
#ORCL:/app/oracle/product/10.2.0.4_64:Y
#LISTENER_ORCL:/app/oracle/product/10.2.0.4_64:N

C:\Documents and Settings\User1>sqlplus user@orcl

SQL*Plus: Release 9.2.0.6.0 - Production on Sat Jan 7 13:12:33 2012

Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.

Enter password:
ERROR:
ORA-01034: ORACLE not available
ORA-27101: shared memory realm does not exist
SVR4 Error: 2: No such file or directory


SOlution: Make sure all the changes of the new oracle home is done in oratab and listener.ora. In my case i missed to make this change in listener.ora.

LISTENER_ORCL=
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = XXXXXX.domain.com)(PORT = 1200))
)
)
)
SID_LIST_LISTENER_ORCL=
(SID_LIST =
(SID_DESC =
(GLOBAL_DBNAME = ORCL.domain.com)
(ORACLE_HOME = /app/oracle/product/10.2.0.4_64)
### (ORACLE_HOME = /app/oracle/product/10.2.0.4_64_v2) ###New Oracle Home after patch 6460304 07th Jan 2012####
(SID_NAME = ORCL)
)
)

This is how my issue got resolved.

Error: Oracle executable binary mismatch detected

Error:
I recently faced this issue. After patching binaries of 10.2.0.4 saw the below error continuosly in alert log of the database.

WARNING: Oracle executable binary mismatch detected.
Binary of new process does not match binary which started instance
issue alter system set "_disable_image_check" = true to disable these messages

Please ignore if this is after oracle patch. Solution is just to bounce the database.

Gathering stats in the database

Version 9.2 onwards You can gather stats of the database by the below ways:

GATHER_TABLE_STATS
EXEC DBMS_STATS.GATHER_TABLE_STATS (ownname =>'SCHEMA_NAME'',tabname=>'TABLE_NAME',method_opt => 'FOR ALL INDEXED COLUMNS SIZE AUTO',granularity =>'ALL',cascade => TRUE,degree => DBMS_STATS.DEFAULT_DEGREE);

GATHER_INDEX_STATS
Execute DBMS_STATS.GATHER_INDEX_STATS ('TEST','TEST_PK',estimate_percent => 30, degree => 6);

GATHER_SCHEMA_STATS
EXEC DBMS_STATS.GATHER_TABLE_STATS (ownname =>'SCHEMA_NAME'',estimate_percent=>dbms_stats.auto_sample_size,cascade=>TRUE,method_opt=>'FOR ALL COLUMNS SIZE AUTO',degree => 12)

Renaming table in Oracle

In Oracle you can rename a table in the same schema as below:

alter table OWNER. rename to NEW_TABLE NAME;

Here the new table will be created in the OWNER schema. Here the target table's schema name need not be required to mention.

Oracle database Broken job 10g

TO check status of broken job:

SQL> select job, broken,what from dba_jobs
JOB BROKEN WHAT
----- ------ ----
3 N PROC1;
2 Y PROC2;

Here the BROKEN column of job 2 is Y which means that this is a broken one.

To mark it as not broken:

EXEC DBMS_JOB.BROKEN(2,FALSE);


TO verify if the broken job is fixed.

SQL> select job, broken,what from dba_jobs

JOB BROKEN WHAT
--- ---- ----
3 N PROC1;
2 N PROC2;

Invalid XML/EXF/CATALOG/CATPROC component in registry

In one of my database, I found that my XML/EXF/CATALOG/CATPROC component in registry got invalid.


COMP_ID COMP_NAM STATUS
---------- ----------------------- ---------
EXF Oracle Expression Filter INVALID
CATALOG Oracle Database Catalog Views INVALID
CATPROC Oracle Database Packages and Types INVALID
JAVAVM JServer JAVA Virtual Machine VALID
XML Oracle XDK INVALID
CATJAVA Oracle Database Java Packages VALID

6 rows selected.


Steps below:

SHUTDOWN IMMEDIATE
STARTUP UPGRADE
@?/rdbms/admin/catalog.sql
@?/rdbms/admin/catproc.sql
@?/rdbms/admin/catexf.sql

-- To validate EXF components
@utlrp.sql

SHUTDOWN IMMEDIATE

STARTUP

Finally all components became valid:

SQL> select COMP_ID,COMP_NAME,STATUS from dba_registry;

COMP_ID COMP_NAM STATUS
---------- ----------------------- ---------
EXF Oracle Expression Filter VALID
CATALOG Oracle Database Catalog Views VALID
CATPROC Oracle Database Packages and Types VALID
JAVAVM JServer JAVA Virtual Machine VALID
XML Oracle XDK VALID
CATJAVA Oracle Database Java Packages VALID

6 rows selected.

SQL> select owner,count(*) from dba_objects where status='INVALID' group by owner;

OWNER COUNT(*)
------------------------------ ----------
PUBLIC 25
SYS 73
EXFSYS 20

Run utlrp

@?/rdbms/admin/utlrp.sql

SQL> select owner,count(*) from dba_objects where status='INVALID' group by owner;

OWNER COUNT(*)
------------------------------ ----------
PUBLIC 21
SYS 43
EXFSYS 2

SQL> SQL> select COMP_ID,COMP_NAME,STATUS from dba_registry;

COMP_ID COMP_NAM STATUS
---------- ----------------------- ---------
EXF Oracle Expression Filter VALID
CATALOG Oracle Database Catalog Views VALID
CATPROC Oracle Database Packages and Types VALID
JAVAVM JServer JAVA Virtual Machine VALID
XML Oracle XDK VALID
CATJAVA Oracle Database Java Packages VALID

6 rows selected.

WARNING: Inbound connection timed out (ORA-3136) after DR

I just want to share one of my experience today with this error.
We did a DR test on one of our informatica standby database server using flashback letting primary database up and used by the application. Basically here standby database was opened as standalone database after cancelling the media recovery and opening the database. Before opening we enabled flashback in the database.

There was n number of issues faced and i would like to write on it in a separate post. Lets discuss considering error Inbound connection timed out (ORA-3136)
After we got the standby database after the application test during DR to put it back in sync with primary database and we did so.
After syncing though i was seeing that both the primary and standby was in sync i was continuouly seeing below error in alert log if standby database:


WARNING: inbound connection timed out (ORA-3136)
Sun Oct 2 20:10:50 2011
WARNING: inbound connection timed out (ORA-3136)
Sun Oct 2 20:11:20 2011
WARNING: inbound connection timed out (ORA-3136)
Sun Oct 2 20:11:51 2011
WARNING: inbound connection timed out (ORA-3136)
Sun Oct 2 20:12:21 2011
WARNING: inbound connection timed out (ORA-3136)
Sun Oct 2 20:12:31 2011
WARNING: inbound connection timed out (ORA-3136)



When I checked it further in sqlnet.log, it tells that the connection to the standby database is coming continuously from Informatica production server


***********************************************************************
Fatal NI connect error 12170.

VERSION INFORMATION:
TNS for Solaris: Version 10.2.0.3.0 - Production
Oracle Bequeath NT Protocol Adapter for Solaris: Version 10.2.0.3.0 - Production
TCP/IP NT Protocol Adapter for Solaris: Version 10.2.0.3.0 - Production
Time: 02-OCT-2011 19:53:15
Tracing not turned on.
Tns error struct:
ns main err code: 12535
TNS-12535: TNS:operation timed out
ns secondary err code: 12606
nt main err code: 0
nt secondary err code: 0
nt OS err code: 0
Client address: (ADDRESS=(PROTOCOL=tcp)(HOST=148.168.**.**)(PORT=12345))


***********************************************************************
Fatal NI connect error 12170.

VERSION INFORMATION:
TNS for Solaris: Version 10.2.0.3.0 - Production
Oracle Bequeath NT Protocol Adapter for Solaris: Version 10.2.0.3.0 - Production
TCP/IP NT Protocol Adapter for Solaris: Version 10.2.0.3.0 - Production
Time: 02-OCT-2011 19:53:25
Tracing not turned on.
Tns error struct:
ns main err code: 12535
TNS-12535: TNS:operation timed out
ns secondary err code: 12606
nt main err code: 0
nt secondary err code: 0
nt OS err code: 0
Client address: (ADDRESS=(PROTOCOL=tcp)(HOST=148.168.**.**)(PORT=12345))
***********************************************************************


SQL> !nslookup 148.168.**.**)(
Server: 127.0.0.1
Address: 127.0.0.1#53

Non-authoritative answer:
53.67.168.148.in-addr.arpa name = server001.pfizer.com.

Authoritative answers can be found from:


========================================================================


When i checked the server details of server001.pfizer.com in CMDB, i got to know that its informatica production application server. Then it understood that during DR application would have been pointed to the DR standby databaase and later after DR, they missed to disconnect those informatica connection to DR standby database.

Then i requested informatica team to disconnect and disable those connection to standby database from informatica production application server. Once this was done this error WARNING: Inbound connection timed out (ORA-3136) disappeared :).