ORA-00119，ORA-00132 錯誤處理

--******************************

-- ORA-00119，ORA-00132 錯誤處理

--******************************

最近系統啟動時，收到了ORA-00119以及ORA-00132的錯誤，該錯誤實際上跟LISTENER有關，通常的處理辦法是將spfile轉儲為pfile然後從pfile啟動

並生成新的spfile，不過該操作方式代價太高，需要重新啟動數據庫。另一種方式則是直接修改tnsnames.ora中的服務名，使之與監聽器中的listener名字保持一致，具體參考下面的細節。

一.錯誤提示

SQL> startup nomount;

ORA-00119: invalid specification for system parameter LOCAL_LISTENER

ORA-00132: syntax error or unresolved network name 'LISTENER_ODBP'

二、分析錯誤

1.查看錯誤號對應的具體描述

[oracle@odbp admin]$ oerr ora 00132

00132, 00000, "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.

描述資訊中給出了listener.ora中網路名是否與tnsnames.ora相一致，需要檢查

2.查看監聽

[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.查看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)

)

由上面的listener.ora與tnsnames.ora可知

listener.ora中的監聽名與tnsnames.ora中的服務名不一致

三、解決方法

1.修改tnsnames.ora中的服務名為LISTENER_ODBP，然後重新啟動數據庫即可

2.可以將spfile(無pfile情況下)轉儲為pfile檔案，將local_listener引數置空，然後使用pfile檔案啟動數據庫後重新生成spfile

可以採用下面的方法來轉儲，如下

[oracle@odbp dbs]$ ls --沒有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' --將改行注釋掉或置空

*.open_cursors=300

*.pga_aggregate_target=94371840

*.processes=150

*.remote_login_passwordfile='EXCLUSIVE'

*.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.

ORA-00119，ORA-00132 錯誤處理

ORA-00119，ORA-00132 錯誤處理

相關文章