3.18.2018

ORA-01033 when Connecting to the DB Via Scan Listener

Had a very interesting and tricky scenario a few days ago. It was something I never encountered in my DBA career. Hence, thought of sharing the detail of there store here today.

Challenge

During mid-day, an application team reported that they are suddenly getting an ORA-01033 when connecting to a 3 node RAC database (11.2.0.3). Quick basic validations reveals that the issue is happening only when connecting through SCAN IP, but, the VIP and direct (physical IP) connections were having no issues. So, it was clear that the issue is with the SCAN IPs. Verified all the configuration settings , network and firewall to ensure there is no issues accessing the SCAN IPS. To our surprise, everything was just perfectly okay. This really puzzles us.We also suspected the Data Guard configuration of this database, but, it wasn't the case either.

Findings


After a quick search over the internet, we come across of MOS Doc: ORA-01033 is Thrown When Connecting to the Database Via Scan Listener (Doc ID 1446132.1)

The issue was, one of the team members was restoring the database backup on a new host.  The tricky part here is, the new host is part of the same network/subnet where the 3 node RAC database is running, and can access to SCAN IPs too. Perhaps the instance that is restoring is registered with the SCAN, and whenever a new connection request is made through SCAN, the connection was referred to an instance which in mount state (restoring). Hence, an ORA-1033 error is thrown.

Fix 

After reviving the note, the restore immediately stopped, and things got back to normal. Even nullifying the remote_listener parameter to de-register with SCAN would have been also worked in this case.

This issue can be prevented through configuring Class of Secure Transport (COST).

The probabilities of hitting the issue is very low, but, I felt its interesting and sharing it worth while.

References

ORA-01033 is Thrown When Connecting to the Database Via Scan Listener (Doc ID 1446132.1)
NOTE:1340831.1 - Using Class of Secure Transport (COST) to Restrict Instance Registration in Oracle RAC
NOTE:1453883.1 - Using Class of Secure Transport (COST) to Restrict Instance Registration 


1 comment:

Unknown said...

That is why the listener adresses should not be static in pfile. This is the carelessness of the member who is restoring. During restore we should always use an auxiliary instance with same db name. Using original pfile parameters makes room for such issues. Also listeners should never be entered in static in pfile. The cluster will autonatically set the listeners registered in cluster when the instsnce starts up. That is done by the cluster agent with scope equal to memory