Solution Of Startup Files With An Error ORA-00119 and ORA-00132

Error Description: Database Startup fails with oracle error ORA-00119, ORA-00132
SQL> startup mount
ORA-00119: invalid specification for system parameter LOCAL_LISTENER
ORA-00132: syntax error or unresolved network name 'LISTENER_DBASE'


Cause of The problem: If the tnsnames.ora entry was used for the value of the LOCAL_LISTENER and the LOCAL_LISTENER entry inside tnsnames.ora is changed or tnsnames.ora file is moved and hence the tns alias to which the LOCAL_LISTENER parameter points is no longer valid. So, the entry inside spfile or pfile to be unresolvable, and the database will not start.

PMON must be able to resolve whatever value the LOCAL_LISTENER or REMOTE_LISTENER parameter is set to. Here LISTENER_DBASE is the name of the local listener.

Remember the LISTENER_DBASE is NOT the listener name reflected in the listener.ora file but rather it is an alias stored in the tnsnames.ora file.

Solution of The problem 

A)Correct the tnsnames.ora

i)Determine if the tns alias is good by using tnsping.

SQL> !tnsping LISTENER_DBASE
TNS Ping Utility for Solaris: Version 10.2.0.1.0 - Production on 15-MAY-2008 11:57:06
Copyright (c) 1997, 2005, Oracle. All rights reserved.

Used parameter files:
/oracle/app/oracle/product/10.2.0/db_1/network/admin/sqlnet.ora
TNS-03505: Failed to resolve name

So it could not find the name inside tnsnames.ora.
ii)
Add the LISTENER_DBASE entry in the tnsnames.ora file.
SQL>!vi /oracle/app/oracle/product/10.2.0/db_1/network/admin/tnsnames.ora
LISTENER_DBASE=(DESCRIPTION=(address=(protocol=tcp)(host=neptune)(port=1522)) (sid='dbase')

and run tnsping utility,

SQL> !tnsping LISTENER_DBASE
TNS Ping Utility for Solaris: Version 10.2.0.1.0 - Production on 15-MAY-2008 12:11:40
Copyright (c) 1997, 2005, Oracle. All rights reserved.
Used parameter files:
/oracle/app/oracle/product/10.2.0/db_1/network/admin/sqlnet.ora

Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION=(address=(protocol=tcp)(host=neptune)(port=1522)) (sid='dbase')
TNS-12533: TNS:illegal ADDRESS parameters

So there is syntax error in the LISTENER_DBASE alias. Correct it as here add parenthesis.

SQL>!vi /oracle/app/oracle/product/10.2.0/db_1/network/admin/tnsnames.ora
LISTENER_DBASE=(DESCRIPTION=(address=(protocol=tcp)(host=neptune)(port=1522)) (sid='dbase')))

iii)After correction run tnsping and start the database.
SQL> !tnsping LISTENER_DBASE
TNS Ping Utility for Solaris: Version 10.2.0.1.0 - Production on 15-MAY-2008 12:14:25
Copyright (c) 1997, 2005, Oracle. All rights reserved.
Used parameter files:
/oracle/app/oracle/product/10.2.0/db_1/network/admin/sqlnet.ora
Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION=(address=(protocol=tcp)(host=neptune)(port=1522)) (sid='dbase'))
OK (0 msec)

SQL> startup
ORACLE instance started.
Total System Global Area 167772160 bytes
Fixed Size 2019288 bytes
Variable Size 83886120 bytes
Database Buffers 75497472 bytes
Redo Buffers 6369280 bytes
Database mounted.
Database opened.


B)An alternative solution is to remove the LOCAL_LISTENER entry from the listener.ora file.
1)Create spfile from pfile if you don't have pfile upadted.
SQL>CREATE PFILE FROM SPFILE;

2)Open the pfile and remove the LOCAL_LISTENER entry.

$vi /oracle/app/oracle/product/10.2.0/db_1/dbs/initdbase.ora

3)Create spfile from pfile.
SQL>CREATE PFILE FROM SPFILE;

4)Start the database.

SQL> startup
ORACLE instance started.
Total System Global Area 167772160 bytes
Fixed Size 2019288 bytes
Variable Size 83886120 bytes
Database Buffers 75497472 bytes
Redo Buffers 6369280 bytes
Database mounted.
Database opened.

Recommendation:
------------------
Though you can set the value of LOCAL_LISTENER (or REMOTE_LISTENER parameter if used) as alias in tnsnames.ora like in this example but it is not recommended setting. Instead use a full address descriptor inside pfile or set dynamically inside spfile.

In order to set dynamically inside spfile and in memory log in with dba privileges and issue:
ALTER SYSTEM set LOCAL_LISTENER="(address=(protocol=tcp)(host=yourhost)(port=yourport))" scope=both sid='instancename';

In my system,
SQL>ALTER SYSTEM set LOCAL_LISTENER="(address=(protocol=tcp)(host=neptune)(port=1522))" scope=both sid='dbase';

SQL> show parameter local

NAME TYPE VALUE 

local_listener string (address=(protocol=tcp)(host=n
eptune)(port=1522))

Read More

Solution OF An Error ORA-27102

Scenario of The problem: 

In my computer I have two database. One database is running smoothly but another database is not started whenever I invoke startup. It fails with error,

SQL> startup
ORA-27102: out of memory
Solaris-AMD64 Error: 22: Invalid argument

Reason of The problem:

The database which could not start is because of the low memory on the system or in the sga_max_size there is high value set. So the system could not allocate so large memory as it does not have free so much. There may be other reasons like OS limitation in order of usage the memory. As in this case one database is ok (Both database are running on same user)and another database is failed so I suspect either low memory on the system or in the sga_max_size parameter inside spfile there is high value set.

Solution of The problem:

1)Set a lower amount of memory in the first database.
You can do it by, On dbase1,

SQL> alter system set sga_max_size=1600M scope=spfile;
System altered.

SQL> alter system set sga_target=1600M;
System altered.

SQL>shutdown

Now set ORACLE_SID and start the instance.

bash-3.00$ export ORACLE_SID=dupbase
bash-3.00$ sqlplus / as sysdba

SQL*Plus: Release 10.2.0.1.0 - Production on Thu May 15 01:34:15 2008

Copyright (c) 1982, 2005, Oracle. All rights reserved.

Connected to an idle instance.

SQL> startup
ORACLE instance started.

Total System Global Area 171966464 bytes
Fixed Size 2019320 bytes
Variable Size 113246216 bytes
Database Buffers 50331648 bytes
Redo Buffers 6369280 bytes
Database mounted.
Database opened.

Or,
2)Lower the setting of SGA_MAX_SIZE, SGA_TARGET on the 2nd database. To do this create pfile from spfile.
SQL>create pfile from spfile;

And then edit the pfile parameter of SGA_MAX_SIZE and SGA_TARGET.

And start the database with the pfile.

SQL>STARTUP PFILE='pfile_name';

Later , Create spfile from pfile,
SQL>CREATE SPFILE from PFILE;

Read More

Oracle Initialization Parameters File Types

There are different types of initialization parameters in oracle, such as,

1)Derived Parameters
2)Operating System-Dependent Parameters
3)Variable Parameters

1)Derived Parameters:

As the name indicates these parameter values are calculated from the values of other parameters. Normally, you should not alter values for derived parameters, but if you do, then the value you specify will override the calculated value.

For example, the default value of the SESSIONS parameter is derived from the value of the PROCESSES parameter. If the value of PROCESSES changes, then the default value of SESSIONS changes as well, unless you override it with a specified value.

2)Operating System-Dependent Parameters 

The valid value of value ranges of these parameters are host Operating System dependent. For example, the value of the parameter DB_BLOCK_SIZE, has an operating system-dependent default value.

3)Variable Parameters 

The variable initialization parameters offer the most potential for improving system performance.
Some variable parameters set capacity limits but do not affect performance. For example, when the value of OPEN_CURSORS is 10, a user process attempting to open its eleventh cursor receives an error. Other variable parameters affect performance but do not impose absolute limits.

Read More

Initialization Parameter Files

Oracle provides two different types of parameter files that you can use, PFILE and SPFILE. When an Oracle Instance is started, the characteristics of the Instance are established by parameters specified within the initialization parameter file. These initialization parameters are either stored in a PFILE or SPFILE. Static parameter file Pfile commonly referred to as intiSID.ora and Persistent parameter file SPfile commonly referred to as spfileSID.ora. A PFILE is a traditional text based init.ora parameter file. Typically this resides on the server in the $ORACLE_BASE/admin/SID/pfile directory.Parameter File contents...

•        A list of instance parameters

•        The name of the database the instance is associated with

•        Allocations for memory structures of the System Global Area (SGA)

•        What to do with filled online redo log files

•        The names and locations of control files

•        Information on undo segments

 Pfile:- Until Oracle 8i, we were using a text file called the PFILE. The PFILE is a text file that can be maintained using a standard operating system editor. The parameter file is read only during instance startup. If the file is modified, the instance must be shut down and restarted in order to make the new parameter values effective. Some parameters are dynamic, which means that they can be modified while the instance is running. Changes to dynamic parameters are not reflected in the PFILE.

By default, the PFILE is located in the $ORACLE_HOME/dbs directory on a Unix machine and named initSID.ora. DBA may keep copies of this file on their local PC to allow remote startup.

SQL> CONNECT sys/password AS SYSDBA

SQL> STARTUP PFILE=C:LocalInit.ora

SPfile:- In Oracle9i, a new feature called SPFILE. SPFILE is a binary file that contains the same information as the old PFILE. If a parameter is changed using the ALTER SYSTEM SET command Oracle will apply this parameter change to the current SPFILE. The file is located in $ORACLE_HOME/dbs and has a default name in the format of spfileSID.ora. Once the file is created it is maintained by the Oracle server. The SPFILE provides the ability to make changes to the database persistent across shutdown and startup. The ALTER SYSTEM command is used to change the value of instance parameters. The SCOPE setting determines the scope of the change. The scope of the ALTER SYSTEM SET command can be defined using the following.

ALTER SYSTEM SET parameter = value SCOPE=[SPFILE/MEMORY/BOTH];

The actions of the scope parameters are listed below.

BOTH - (Default) The parameter takes affect in the current instance and is stored in the SPFILE.

SPFILE - The parameter is altered in the SPFILE only. It does not affect the current instance.

MEMORY - The parameter takes affect in the current instance, but is not stored in the SPFILE.

Creating an SPFILE

An SPFILE is created from an initSID.ora file using the CREATE SPFILE command. This can be executed before or after the database is open.

SQL> CREATE SPFILE FROM PFILE;

One can also specify a non-default location for either (or both) the PFILE and SPFILE parameters

SQL> CREATE SPFILE='/oradata/spfileORCL.ora' from PFILE='/oradata/initORCL.ora';

Related Post:-  

• Manually Database Creation

Read More