TNS-01106: Listener using listener name has already been started

Scenario:

DB version: 11.2.0.3.0

The following error was found on trying to start a newly created listener in a shared DB server with multiple Oracle Homes.

TNS-01106: Listener using listener name  has already been started

Verification:

From the error message it seem to happen because of a duplicate LISTENER name, but it was not the case to be. I tried to give different names to the listener but still was getting same error on listener start up

Solution:

A detailed analysis of the error helped me to find that it can happen if any other listener is already listening on one of the parameters in the ADDRESSes specified. In my case the port number I tried to give for the new listener was already in use. But since the error normally expected with a port number conflict is different (TNS-12542: TNS:address already in use), I was wasting my time concentrating on LISTENER name.

I changed the TCP port number in the listener.ora file and successfully started the listener.

ORA-27046: file size is not a multiple of logical block size with SPFILE

Scenario:

Creation of pfile from the existing spfile. The following error stack was seen.

ERROR at line 1:
ORA-01565: error in identifying file '?/dbs/spfile@.ora'
ORA-27046: file size is not a multiple of logical block size
Additional information: 1

Reason:

The SPFILE is corrupted. The corruption could have happened because of the manual editing of the binary SPFILE. There is no way the spfile can be repaired or modified manually because spfile is a binary file.

Solution:


1. Restore from backup (If we have a recent backup of spfile then can restore it)
2. Create pfile from alert log file. We can get the list of non-default parameters from the alert log at startup time and with that can create pfile.
3. Get the parameters from the corrupted spfile using utilities like strings. We can even try using tools like "vi" to open the spfile and get the parameters.
Eg: strings spfile$ORACLE_SID.ora init$ORACLE_SID.ora

Then create spfile from the pfile.

Enable archive log mode in RAC database

Case:

To enable archive log mode in an 11gR2 RAC database.


Steps:

Need to shutdown all instances of the RAC and then "MOUNT"  a single node to switch it to ARCHIVELOG mode. It is recommended to have more than one archive destination.

It is better to keep ArchiveLogs on shared storage because any node of the cluster can be used to backup the ArchiveLogs. If we are using FRA as backup location then it makes sense to keep the files there.


ALTER SYSTEM SET log_archive_dest_1='location=/app/db_backup/archive/ORCL/' SCOPE=spfile; 
ALTER SYSTEM SET log_archive_format='arch_%t_%s_%r.arc' SCOPE=spfile;

Set cluster_database parameter to false since we have to mount the database in exclusive mode. 
.
ALTER SYSTEM SET cluster_database=FALSE SCOPE=spfile;

srvctl stop database -d ORCL

STARTUP MOUNT;

Since the datafiles and spfile are shared between all instances this operation only has to be done from a single node.

ALTER DATABASE ARCHIVELOG;
ALTER SYSTEM SET cluster_database=TRUE SCOPE=spfile;
SHUTDOWN IMMEDIATE;

srvctl start database -d ORCL


Disabling:

Follow similar steps to disable archiving.

ALTER SYSTEM SET cluster_database=FALSE SCOPE=spfile;

srvctl stop database -d ORCL

STARTUP MOUNT;

alter database noarchivelog;

ALTER SYSTEM SET cluster_database=TRUE SCOPE=spfile;

SHUTDOWN IMMEDIATE;

srvctl start database -d ORCL