ORA-00119，ORA-00132 错误处理

喜欢ヅ旅行 2022-05-15 06:20 160阅读 0赞

*--\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\**

*-- ORA-00119**，ORA-00132* *错误处**理*

*--\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\**

最近系统启动时，收到了ORA-00119以及ORA-00132的错误，该错误实际上跟LISTENER有关，通常的处理办法是将spfile转储为pfile然后从pfile启动

并生成新的spfile，不过该操作方式代价太高，需要重新启动数据库。另一种方式则是直接修改tnsnames.ora中的服务名，使之与监听器中的listener名字保持一致，具体参考下面的细节。

一.错误提示

SQL> startup nomount;

ORA\-00119: invalid specificationforsystem parameter LOCAL\_LISTENER

ORA\-00132: syntax erroror unresolved networkname'LISTENER\_ODBP'

二、分析错误

1.查看错误号对应的具体描述

\[oracle@odbp admin\]$ oerr ora00132

00132, 00000, "syntax error or unresolved network name '%s'"

// \*Cause: Listener address has syntax erroror cannot be resolved.

// \*Action: If a networknameis specified, check that it corresponds

// to an entryin TNSNAMES.ORA or other address repository

// as configured for your system. Make sure that theentry

// 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

\# Generatedby 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

\# Generatedby 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 SystemGlobal Area 285212672 bytes

Fixed Size1267068 bytes

VariableSize150997636 bytes

Database Buffers 130023424 bytes

Redo Buffers 2924544 bytes

SQL> createspfilefrompfile;

File created.

SQL> startup force;

ORACLE instance started.

Total SystemGlobal Area 285212672 bytes

Fixed Size1267068 bytes

VariableSize150997636 bytes

Database Buffers 130023424 bytes

Redo Buffers 2924544 bytes

Database mounted.

Database opened.

四、更多参考

有关性能优化请参考

[Oracle 硬解析与软解析][Oracle]

[共享池的调整与优化(Shared pool Tuning)][Shared pool Tuning]

[Buffer cache 的调整与优化(一)][Buffer cache]

[Oracle 表缓存(caching table)的使用][Oracle _caching table]

有关闪回特性请参考

[Oracle 闪回特性(FLASHBACK DATABASE)][Oracle _FLASHBACK DATABASE]

[Oracle 闪回特性(FLASHBACK DROP & RECYCLEBIN)][Oracle _FLASHBACK DROP _ RECYCLEBIN]

[Oracle 闪回特性(Flashback Query、Flashback Table)][Oracle _Flashback Query_Flashback Table]

[Oracle 闪回特性(Flashback Version、Flashback Transaction)][Oracle _Flashback Version_Flashback Transaction]

有关基于用户管理的备份和备份恢复的概念请参考

[Oracle 冷备份][Oracle 1]

[Oracle 热备份][Oracle 2]

[Oracle 备份恢复概念][Oracle 3]

[Oracle 实例恢复][Oracle 4]

[Oracle 基于用户管理恢复的处理][Oracle 5](详细描述了介质恢复及其处理)

有关RMAN的恢复与管理请参考

[RMAN 概述及其体系结构][RMAN]

[RMAN 配置、监控与管理][RMAN 1]

[RMAN 备份详解][RMAN 2]

[RMAN 还原与恢复][RMAN 3]

有关Oracle体系结构请参考

[Oracle 实例和Oracle数据库(Oracle体系结构)][Oracle _Oracle_Oracle]

[Oracle 表空间与数据文件][Oracle 6]

[Oracle 密码文件][Oracle 7]

[Oracle 参数文件][Oracle 8]

[Oracle 数据库实例启动关闭过程][Oracle 9]

[Oracle 联机重做日志文件(ONLINE LOG FILE)][Oracle _ONLINE LOG FILE]

[Oracle 控制文件(CONTROLFILE)][Oracle _CONTROLFILE]

[Oracle 归档日志][Oracle 10]

[Oracle]: http://blog.csdn.net/robinson_0612/archive/2011/02/19/6195483.aspx
[Shared pool Tuning]: http://blog.csdn.net/robinson_0612/archive/2011/02/25/6208268.aspx
[Buffer cache]: http://blog.csdn.net/robinson_0612/archive/2011/02/25/6208594.aspx
[Oracle _caching table]: http://blog.csdn.net/robinson_0612/archive/2011/03/06/6226767.aspx
[Oracle _FLASHBACK DATABASE]: http://blog.csdn.net/robinson_0612/archive/2010/12/27/6100429.aspx
[Oracle _FLASHBACK DROP _ RECYCLEBIN]: http://blog.csdn.net/robinson_0612/archive/2010/12/29/6105327.aspx
[Oracle _Flashback Query_Flashback Table]: http://blog.csdn.net/robinson_0612/archive/2011/01/02/6112607.aspx
[Oracle _Flashback Version_Flashback Transaction]: http://blog.csdn.net/robinson_0612/archive/2011/01/02/6112981.aspx
[Oracle 1]: http://blog.csdn.net/robinson_0612/archive/2010/08/05/5791597.aspx
[Oracle 2]: http://blog.csdn.net/robinson_0612/archive/2010/08/16/5816012.aspx
[Oracle 3]: http://blog.csdn.net/robinson_0612/archive/2010/08/05/5791585.aspx
[Oracle 4]: http://blog.csdn.net/robinson_0612/archive/2010/07/27/5768233.aspx
[Oracle 5]: http://blog.csdn.net/robinson_0612/archive/2010/11/28/6041217.aspx
[RMAN]: http://blog.csdn.net/robinson_0612/archive/2010/11/24/6032525.aspx
[RMAN 1]: http://blog.csdn.net/robinson_0612/archive/2010/11/24/6032597.aspx
[RMAN 2]: http://blog.csdn.net/robinson_0612/archive/2010/11/24/6032739.aspx
[RMAN 3]: http://blog.csdn.net/robinson_0612/archive/2010/11/28/6041247.aspx
[Oracle _Oracle_Oracle]: http://blog.csdn.net/robinson_0612/archive/2010/04/26/5529239.aspx
[Oracle 6]: http://blog.csdn.net/robinson_0612/archive/2010/05/20/5611738.aspx
[Oracle 7]: http://blog.csdn.net/robinson_0612/archive/2010/05/20/5611672.aspx
[Oracle 8]: http://blog.csdn.net/robinson_0612/archive/2010/05/05/5559174.aspx
[Oracle 9]: http://blog.csdn.net/robinson_0612/archive/2010/04/29/5542983.aspx
[Oracle _ONLINE LOG FILE]: http://blog.csdn.net/robinson_0612/archive/2010/07/20/5749556.aspx
[Oracle _CONTROLFILE]: http://blog.csdn.net/robinson_0612/archive/2010/07/15/5737527.aspx
[Oracle 10]: http://blog.csdn.net/robinson_0612/archive/2010/08/03/5784713.aspx