It was very cold and early on a Monday morning when I received a call from one of my fellow system administrators. He reported that one of our production databases would not come back online after the server hosting the database was restarted.
Most DBAs would start investigating this issue by looking at database alert logs. But my experience led me to ask my fellow system admin the following question: “What changes did you make on the server prior to the reboot?”
It was his answer to that question that allowed me to quickly understand the issue and fix it in just a few minutes.
Apparently the system admin (not the DBA) was conducting vulnerability testing and, as a result, made a change to the main listener.ora file that disabled all databases from being able to dynamically register to Oracle database listeners.
By default, an Oracle database will try to dynamically register to an Oracle database listener on port 1521. This registration process allows connections to the database from outside of the server. The database was online and operational, but because the dynamic registration option was disabled it could no longer register to the listener. So no users could connect to the database.
The fix for this was adding a static listener to the listener.ora for the database hosted on the server, thus allowing it to receive connections. Once the static listener was added, all users were able to connect to the production database without error.
The Technical Problem\
Let’s break this incident down in more detail:
This is the original Listener file
LISTENER=
(DESCRIPTION=
(ADDRESS_LIST=
(ADDRESS=(PROTOCOL=tcp)(HOST=MyServer)(PORT=1521))
(ADDRESS=(PROTOCOL=ipc)(KEY=extproc))))
The administrator added one line (see below in red):
LISTENER=
(DESCRIPTION=
(ADDRESS_LIST=
(ADDRESS=(PROTOCOL=tcp)(HOST=MyServer)(PORT=1521))
(ADDRESS=(PROTOCOL=ipc)(KEY=extproc))))
DYNAMIC_REGISTRATION_LISTENER=OFF
This prevented any databases that do not have a static listener specified in the listener.ora file from accepting connections..
The Technical Solution
To correct the problem, I added a static listener to the listener.ora file (see below in red):
LISTENER=
(DESCRIPTION=
(ADDRESS_LIST=
(ADDRESS=(PROTOCOL=tcp)(HOST=MyServer)(PORT=1521))
(ADDRESS=(PROTOCOL=ipc)(KEY=extproc))))
DYNAMIC_REGISTRATION_LISTENER=OFF
SID_LIST_LISTENER=
(SID_LIST=
(SID_DESC=
(GLOBAL_DBNAME=MyDBName)
(ORACLE_HOME=/u01/app/oracle/product/12.1.0/dbhome_1)
(SID_NAME=MySID))
)
You can find detailed information about the listener file for Oracle version 19c here.
The Communication Problem
We have mentioned in this blog before that almost all problems with technology projects are the result of poor communication. This principle holds here as well. Because the system administrator did not keep any of the DBAs on our team “in the loop” about their vulnerability testing, or the resulting changes, those changes caused production downtime.
The Communication Solution
Any change to a server, database, or application must be communicated to all responsible parties beforehand. In fact, a better approach in this case would have been to ask the DBA to make the change to the listener file rather than the administrator making the change himself. This would have ensured that an experienced DBA had reviewed the change and understood the potential impact.
The moral of the story is: Keep your DBAs in the loop when you’re making system changes. It’s our job to proactively prevent database issues others might miss.
A Word on Database Security
While an action taken by the system administrator caused a problem in this situation, it should be applauded from a database security standpoint that vulnerability testing was conducted because it exposed a potential vulnerability (the dynamic registration). It is a best practice to disable dynamic registration unless it is necessary for the organization, and unless the associated risk is mitigated by other practices, such as changing the default listener port.
Database vulnerability testing is a crucial part of a comprehensive IT security plan and is often overlooked. For the reasons described above, the process should always include a member of the DBA team. See a few of our Database Security related blogs here.