Oracle ORA-00119, ORA-00132 error handling

A ORA-00119 and ORA-00132 error was received when the recent system was started, which is actually related to LISTENER, the usual solution is to dump spfile to pfile and then start from pfile

And generate a new spfile. However, this operation method is too costly and you need to restart the database. Another way is to directly modify the service name in tnsnames. ora to make it consistent with the listener name in the listener. For details, refer to the following.

I. error message

SQL> startup nomount;

ORA-00119: invalid specification for system parameter LOCAL_LISTENER

ORA-00132: syntax error or unresolved network name 'listener _ ODBP'

Ii. analysis errors

1. view the specific description of the error code.

[Oracle @ odbp admin] $ oerr ora 00132

00132,000 00, "syntax error or unresolved network name '% S '"

// * Cause: Listener address has syntax error or cannot be resolved.

// * Action: If a network name is specified, check that it corresponds

// To an entry in TNSNAMES. ORA or other address repository

// As configured for your system. Make sure that the entry

// Is syntactically correct.

The description shows whether the network name in listener. ora is consistent with that in tnsnames. ora. Check whether

2. View listeners

[Oracle @ odbp admin] $ more listener. ora

# Listener. ora Network Configuration File:/u01/app/oracle/10g/network/admin/listener. ora

# Generated by Oracle configuration tools.

SID_LIST_LISTENER_ODBP =

(SID_LIST =

(SID_DESC =

(GLOBAL_DBNAME = odbp.oradb.com)

(ORACLE_HOME =/u01/app/oracle/10g)

(SID_NAME = odbp)

)

)

LISTENER_ODBP =

(DESCRIPTION =

(ADDRESS = (PROTOCOL = TCP) (HOST = odbp.oradb.com) (PORT = 1521 ))

)

3. View tnsnames. ora

[Oracle @ odbp admin] $ more tnsnames. ora

# Tnsnames. ora Network Configuration File:/u01/app/oracle/10g/network/admin/tnsnames. ora

# Generated by Oracle configuration tools.

ODBP =

(DESCRIPTION =

(ADDRESS_LIST =

(ADDRESS = (PROTOCOL = TCP) (HOST = 192.168.192.11) (PORT = 1521 ))

)

(CONNECT_DATA =

(SERVICE_NAME = odbp.oradb.com)

)

)

EXTPROC_CONNECTION_DATA =

(DESCRIPTION =

(ADDRESS_LIST =

(ADDRESS = (PROTOCOL = IPC) (KEY = EXTPROC0 ))

)

(CONNECT_DATA =

(SID = PLSExtProc)

(PRESENTATION = RO)

)

)

It can be seen from the listener. ora and tnsnames. ora above.

The listener name in listener. ora is inconsistent with the service name in tnsnames. ora.

Iii. Solution

1. Modify the service name LISTENER_ODBP in tnsnames. ora and restart the database.

2. You can dump the spfile (without pfile) to the pfile file, leave the local_listener parameter blank, and then use the pfile file to start the database and regenerate the spfile.

The following method can be used for dumping:

[Oracle @ odbp dbs] $ ls -- no pfile

Hc_odbp.dat initdw. ora init. ora lkODBP orapwodbp spfileodbp. ora

[Oracle @ odbp dbs] $ strings spfileodbp. ora> initodbp. ora

[Oracle @ odbp dbs] $ cat initodbp. ora

Odbp. _ db_cache_size = 130023424

Odbp. _ java_pool_size = 33554432

Odbp. _ large_pool_size = 4194304

Odbp. _ shared_pool_size = 113246208

Odbp. _ streams_pool_size = 0

*. Audit_file_dest = '/u01/app/oracle/admin/odbp/adump'

*. Background_dump_dest = '/u01/app/oracle/admin/odbp/bdump'

*. Compatible = '10. 2.0.4.0'

*. Control_files = '/u01/app/oracle/oradata/odbp/control01.ctl', '/u01/app/oracle/oradata/odbp/control02.ctl'

*. Core_dump_dest = '/u01/app/oracle/admin/odbp/cdump'

*. Db_block_size = 8192

*. Db_domain = 'oradb. com'

*. Db_file_multiblock_read_count = 16

*. Db_name = 'odbp'

*. Db_recovery_file_dest = '/u01/app/oracle/flash_recovery_area'

*. Db_recovery_file_dest_size = 2147483648

*. Dispatchers = '(PROTOCOL = TCP) (SERVICE = odbpXDB )'

*. Job_queue_processes = 10

*. Local_listener = 'listener _ ODBP '-- Comment out or leave the modified line empty.

*. Open_cursors = 300

*. Pga_aggregate_target = 94371840

*. Processses = 150

*. Remote_login_passwordfile = 'clusive'

*. Sga_target = 285212672

*. Undo_management = 'auto'

*. Undo_tablespace = 'undotbs1'

*. User_dump_dest = '/u01/app/oracle/admin/odbp/udump'

SQL> startup nomount pfile = '/u01/app/oracle/10g/dbs/initodbp. ora ';

ORACLE instance started.

Total System Global Area 285212672 bytes

Fixed Size 1267068 bytes

Variable Size 150997636 bytes

Database Buffers 130023424 bytes

Redo Buffers 2924544 bytes

SQL> create spfile from pfile;

File created.

SQL> startup force;

ORACLE instance started.

Total System Global Area 285212672 bytes

Fixed Size 1267068 bytes

Variable Size 150997636 bytes

Database Buffers 130023424 bytes

Redo Buffers 2924544 bytes

Database mounted.

Database opened.