listener.ora – tnsnames.ora – alert log

listener.ora
The listener is a server-side process that listens for database connection requests from user processes and launches dedicated server processes to establish sessions. The sessions become the connections between the user process and the database unless shared servers are in use, in which case a dispatcher process is used to share time to shared server processes.

With local naming the user supplies an Oracle Net service alias for the connect string and this is resolved by a local file into the full network address. This local file is the tnsnames.ora file. There's no need for any relationship between the alias, service name and the instance name - it's recommended to keep them the same! The Service Naming branch of the Net Manager creates or edits the Local Naming tnsnames.ora file that resides in ORACLE_HOME/network/admin directory.

You can configure several listeners in the listener.ora file but they must all have different names and addresses. Under Windows the listener will run as a Windows service, but there's no need to create the service explicitly; it'll be created implicitly the first time the listener is started.

A listener finds out about instances by the process of "registration". For static registration, you hard-code a list of instances in the listener.ora file. Dynamic registration means that the instance itself, at startup time, locates a listener and registers with it.

You can start and stop listeners through Database Control, but there's also a command-line utility lsnrctl.
 

The tnsnames.ora file is a client-side file used for name resolution. It is used by user processes to locate database listeners. It may also be used the instance itself, to locate a listener with which to register.

TNS stands for Transparent Network Substrate

The heart of Oracle Net, a proprietary layered protocol running on top of whatever underlying network transport protocol you choose to use - probably TCP/IP.

The Oracle Net files by default exist in the directory ORACLE_HOME/network/admin. It's possible to relocate them with an environment variable: TNSADMIN. Mainly on systems that have several Oracle Homes. Example on Windows:

set TNSADMIN=c:\oracle\net

alert_.log

The standard Oracle alert log location defined by the initialization parameter DIAGNOSTIC_DEST or USER_DUMP_DEST.

The alert log file, located in the directory specified by the initialization parameter BACKGROUND_DUMP_DEST, contains the most significant routine status messages as well as critical error conditions. When the database is started up or shut down, a message is recorded in the alert log, along with a list of initialization parameters that are different from their default values. In addition, any ALTER DATABASE or ALTER SYSTEM commands issued by the DBA are recorded. Operations involving tablespaces and their datafiles are also recorded here. All error/critical conditions are recorded. The alert log file can be deleted or renamed at any time; it's re-created the next time an alert log message is generated. The trace files for the Oracle instance background processes are also located in BACKGROUND_DUMP_DEST. Trace files are also created for individual user sessions or connections to the database; these are located in the directory specified by the initialization parameter USER_DUMP_DEST. As of Oracle Database 11g Release 1, the diagnostics for an instance are centralized in a single directory specified by the initialization parameter DIAGNOSTIC_DEST.

The DBA will often set up a daily batch job to rename and archive the alert log on a daily basis.

The alert log is a continuous record of critical operations applied to the instance and the database. Its location is determined by the instance parameter BACKGROUND_DUMP_DEST, and its name is alert_SID.log where SID is the name of the instance. The alert log entry for a startup shows all the non-default initialization parameters. Locate the file with this command:

SQL> select value from v$parameter where name='background_dump_dest';