Listener Dynamic registration and Service name

There was a discussion in oracle forums about the SIDvs Service name...

After going through the thread i just realised that

in 10g on a Dynamic registered instance we will be able to connect only using SERVICE_NAME and not using the SID_NAME..

C:\WINDOWS\system32>lsnrctl status

LSNRCTL for 32-bit Windows: Version 10.2.0.4.0 - Production on 29-DEC-2009 10:43

:35

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

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=pc-44.)(PORT=1521)))

STATUS of the LISTENER

------------------------

Alias LISTENER

Version TNSLSNR for 32-bit Windows: Version 10.2.0.4.0 - Produ

ction

Start Date 29-DEC-2009 10:14:38

Uptime 0 days 0 hr. 28 min. 57 sec

Trace Level off

Security ON: Local OS Authentication

SNMP OFF

Listener Parameter File D:\ORACLE\PRODUCT\10.2.0\DB_1\network\admin\listener.o

ra

Listener Log File D:\ORACLE\PRODUCT\10.2.0\DB_1\network\log\listener.log

Listening Endpoints Summary...

(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=pc-44)(PORT=1521)))

Services Summary...

Service "PLSExtProc" has 1 instance(s).

Instance "PLSExtProc", status UNKNOWN, has 1 handler(s) for this service...

The command completed successfully

C:\WINDOWS\system32>tnsping test

TNS Ping Utility for 32-bit Windows: Version 10.2.0.4.0 - Production on 29-DEC-2

009 10:43:53

Copyright (c) 1997, 2007, Oracle. All rights reserved.

Used parameter files:

D:\ORACLE\PRODUCT\10.2.0\DB_1\network\admin\sqlnet.ora

Used TNSNAMES adapter to resolve the alias

Attempting to contact (DESCRIPTION = (ADDRESS_LIST = (SOURCE_ROUTE=yes) (ADDRESS

=(PROTOCOL=tcp)(HOST=pc-44)(PORT=1630)) (ADDRESS=(PROTOCOL=TCP)(HOST = PC-44)(PO

RT = 1522))) (CONNECT_DATA = (SID = TEST)))

TNS-12541: TNS:no listener

C:\WINDOWS\system32>tnsping test

TNS Ping Utility for 32-bit Windows: Version 10.2.0.4.0 - Production on 29-DEC-2

009 10:44:16

Copyright (c) 1997, 2007, Oracle. All rights reserved.

Used parameter files:

D:\ORACLE\PRODUCT\10.2.0\DB_1\network\admin\sqlnet.ora

Used TNSNAMES adapter to resolve the alias

Attempting to contact (DESCRIPTION = (ADDRESS_LIST = (SOURCE_ROUTE=yes) (ADDRESS

=(PROTOCOL=tcp)(HOST=pc-44)(PORT=1630)) (ADDRESS=(PROTOCOL=TCP)(HOST = PC-44)(PO

RT = 1521))) (CONNECT_DATA = (SID = TEST)))

TNS-12541: TNS:no listener

C:\WINDOWS\system32>tnsping test

TNS Ping Utility for 32-bit Windows: Version 10.2.0.4.0 - Production on 29-DEC-2

009 10:45:58

Copyright (c) 1997, 2007, Oracle. All rights reserved.

Used parameter files:

D:\ORACLE\PRODUCT\10.2.0\DB_1\network\admin\sqlnet.ora

Used TNSNAMES adapter to resolve the alias

Attempting to contact (DESCRIPTION = (ADDRESS_LIST = (ADDRESS=(PROTOCOL=TCP)(HOS

T = PC-44)(PORT = 1521))) (CONNECT_DATA = (SSERVICE_NAME = TEST)))

OK (60 msec)

C:\WINDOWS\system32>

DR excercise with image Backup

I just want the share the experience we had last week when we were doing our Annual DR excercise for our systems

1.There are daily backups(hotbackups) to tape and also entire system image backups..These backups were taken when the DB was running

SUNDAY--Hotbackup
Rest of the Days--Archivelog backups



We did restore the image backups along with tape backups on Saturday
We had 2 production database , 1 was heavily utilised and the other one was just sitting idle..

When we try to start the DB , the database that was idle came up without any issue but the Heavily accesed database didnt comeup...with error messages FUZZY backup..File header verification failed.....

Next Step

1. Resored the Sunday Full backup
2.Restored all the archivelogs upto saturday

I tried restoring a particular file from the backup and applied all the logs but when the point where the backup was taken reached , the redolog has corrupted datablock and the recovery halted....couldnt open the DB but need to restore the entire db from the hotbackup...

Erros
Fuzzy backup
header verification failes....


Do rely on image backups in case of Disaster ..

JDBC and Reverse DNS lookup

We had a strange application slowness in one of our HRIS system and let me describe what happened.

This is our environment

1.Oracle 8.1.7.4.1--Dont ask me why it was never migrated :)
2.Toplink
3.Tomcat 5.5(Uses JDK 1.5)
4.JDK1.5(Appserver builtin JDK1.5)
5.IIS 6.


We have a HRIS system(OLTP ) providing Employee self service for clients.

OUr IIS and APpserver sits on a same Physical server and DB in a diferent box. To comply with auditing standards we are asked to move the IIS to a DMZ(Which is quite normal but we havent so far) separate the APPServer from IIS.


New set up should be similiar to this

IIS-->AJP(1.3)--Tomcat--Appserver--JDBC--DB
IIS will be redirecting to the TOmcat container and then from Tomcat to APPserver. TOmcat and APpserver sits on a Same Server. Different colors reprsents different boxes.

We purchased Brand new servers to replace all the existing servers except the DB server but the IPs/Names were retained to minimize the complexity during migration.All the applications were migrated without any issue and everything looked fine for 2 weeks but all that was waiting so long to explode finally exploded.

We the concurrent access increases we were simply not able to go beyond the login page(Which is the first page). People who have logged in dont have any problems but the problem lied in login page .

Our appserver makes a set of connection when we startup and then makes new set of connections based on the usage

We tried increasing at the Tomcat MAx number of threads, Timeout,Enable lookss=False. After a week long we couldnt arrive at a conclusion. Appserver CPU and DB server CPU utlization hardly went beyond 10% during the slowness so we came to a conslusion that there is no resource shortage.

When contacted the application team they were simply saying "its all because of migration that you guys did, it was working before why now, so please look at the application setup"

Since the iSsue became very critical Development team as requested to look into the issues,We got the thread dump during the problem which stated the message below

at java.net.Inet4AddressImpl.getHostByAddr(Native Method)
at java.net.InetAddress$1.getHostByAddr(InetAddress.java:842)
at java.net.InetAddress.getHostFromNameService(InetAddress.java:532)
at java.net.InetAddress.getHostName(InetAddress.java:475)
at java.net.InetAddress.getHostName(InetAddress.java:447)
at java.net.InetSocketAddress.getHostName(InetSocketAddress.java:210)
at java.net.SocksSocketImpl.connect(SocksSocketImpl.java:341)
at java.net.Socket.connect(Socket.java:507)
at java.net.Socket.connect(Socket.java:457)
at java.net.Socket.(Socket.java:365)
at java.net.Socket.(Socket.java:178)
at oracle.net.nt.TcpNTAdapter.connect(Unknown Source)
at oracle.net.nt.ConnOption.connect(Unknown Source)
at oracle.net.nt.ConnStrategy.execute(Unknown Source)
at oracle.net.resolver.AddrResolution.resolveAndExecute(Unknown Source)
at oracle.net.ns.NSProtocol.establishConnection(Unknown Source)
at oracle.net.ns.NSProtocol.connect(Unknown Source)
at oracle.jdbc.driver.T4CConnection.connect(T4CConnection.java:706)
at oracle.jdbc.driver.T4CConnection.logon(T4CConnection.


The first few lines gave some hint the we are having some problems in the DNS server. When we tried NSLOOKUP IP it didnt work but NSLOOKUP with HOSTNAME works so we cam e to a conclusion that reverse dns lookpup fails which matched our error message along with a Metalink doc.

As a fix we added the Other server names in etc/host file of appserver and DB server.

Oh no...There was problem after that and then we came to know that JDBC uses Reverse DNSlookup to make connections.

Happy reading

Oracle Dataguard in 10GR2

Its been a strange experience for me.This is the first time i set up dataguard configuration on Oracle 10G. Its so straighforward but its too annoying.

I was stuck with this error almost 100 times and i realy dont know why

ORA-16072: a minimum of one standby database destination is required


This is what i did.
1.Forced the Primary to forced logging mode
2.Create pfile2 from spfile
3.Create stdby controlfile

Copied the data,Redolog files, Renamed the controlfile as per init,ora file
Primary:
Added Log_archive_dest_2='Service=STDBY LGWR Sync affirm'