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'

EM

Recovery from corrupted rollback segments

I had a hard day from yesterday as we had a hardisk crash in one of our servers.It was in a QA environment so the impact was not great.

No Backups for QA except weekly Export backup but it failed for last two weeks,so realistically no backup availble.Instance terminated leaving a message in alert.log saying that UNDO tablespace segments got corrupted.

Oracle 9i and 10g on a windows 2003 server.

The behaviour was different between 9i and 10g

First 9i

The lost hard disk had 9i and 10g on it.

I tried to start the DB but after mounting the undo datafile didnt come up with a messgae Corrupted block

I tried to offline drop and it worked and then i tried to drop the UNDO tablespace it didnt allow with Recurvis sql, and ora 600 error.

I managed to find out the rollback segments which needed recovery ,SMON was throwng errors in the Alert log saying the Rollback segament corrupted

Then i decided to go for UNSUPPORTED parameter in the INIT.ora

shutdown the database
edit the init and added the following
_OFFLINE_ROLLBACK_SEGEMNTS=(...)
UNDO_management=MANUAL
commented the UNDO_TABLESPACE in the init.ora

Started the database and opened it

now it tried to drop the UNDO tablespace and it worked
Created a new undo tablespace
Shutdwon the DB
Edit the init.ora again to comment the _OFFLINE_ROLLBACK_SEGEMNTS and set the UNDO_TABLESPACE value

Database opened and no more errors and did a full backup of the DB with Export.

The scenario was slightly is different in 10g
once i offline drop the undo tablespace datafile i opened the database and created and new undoablespace--This i couldnt so it in 9i
Now found the ROLLABCKSEGEMNT and edited the init.ora _OFFLINE_ROLLBACK_SEGEMENTS and changed the UNDO_TABLESPACE to the new UNDO

Opened the DB dropped the OLD UNDO

Database was UP

Export full backups
:)
Hard day.

DBlink and TNS could not resolve connect identifier specified

I had a strange problem yesterday

We were upgrading Oracle Servers and migrating and back and forth .We need to establish a DB link to move data from one db to one another

Oracle 9.2.0.6 on Solaris 10 =DBNAME=PRD
Oracle 10.2.0.3 on solaris 10=DBNAME=UAT

Steps we followed

1.Connect admin/*******@UAT
Connected

2.create database link PRD connect to admin identified by **** using 'PRD'

where PRD is the service name..

Database link created;


select * from hrm_employee@prd

ora-12514 TNS could not resolve connect identifier specified

PRD service entry is present in the TNSNAMES.ora in the client side..

connect admin/*******@PRD

connectes:

so we tried all the option including replace the 'servicename' in the create DB link stmt with the

'(DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = *********)(PORT = 1521)) ) (CONNECT_DATA = (SID = PRD) ) )'

But still it didnt work out

Then i realised that DBlink uses the Server TNSNAMES.ora but not the Cleint TNSNAMES.ora which i am connecting from

We added the PRD entry in the TNSNAMES.ora and the DBlink worked


Pretty hard day...

New things are learned everyday..

Cheers maran

More Over

If the GLOBAL_NAMES=TRUE then the DBlink name should be same as the SID name..Or else you will hit the ora-2085 error.