oracle数据库ora-00119和ora-00132问题解决

最新推荐文章于 2023-01-17 15:03:50 发布

悠然~

最新推荐文章于 2023-01-17 15:03:50 发布

阅读量712

点赞数 1

分类专栏：数据库文章标签： oracle ora-00119 ora-00132

本文链接：https://blog.csdn.net/wz122330/article/details/85338645

版权

数据库专栏收录该内容

0 篇文章 0 订阅

订阅专栏

oracle数据库ora-00119和ora-00132问题解决

概述
问题
解决办法
- 修改init.ora.*文件

概述

在oracle数据库机器断电后（非正常关闭），数据库无法启动，报ora-00119和ora-00132错误。

问题

[root@oracle_ci ~]# su oracle
[oracle@oracle_ci root]$ cd
[oracle@oracle_ci ~]$ ls
create_ci_schema.sql  table.log  view.log
[oracle@oracle_ci ~]$ source .bash
.bash_history  .bash_profile  
.bash_logout   .bashrc        
[oracle@oracle_ci ~]$ source .bash_
.bash_history  .bash_logout   .bash_profile
[oracle@oracle_ci ~]$ source .bash_profile 
[oracle@oracle_ci ~]$ lsn
lsnodes   lsnrctl   lsnrctl0  
[oracle@oracle_ci ~]$ lsnrctl status

LSNRCTL for Linux: Version 11.2.0.1.0 - Production on 29-DEC-2018 00:31:30

Copyright (c) 1991, 2009, Oracle.  All rights reserved.

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC1521)))
TNS-12541: TNS:no listener
 TNS-12560: TNS:protocol adapter error
  TNS-00511: No listener
   Linux Error: 111: Connection refused
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=oracle_ci)(PORT=1521)))
TNS-12541: TNS:no listener
 TNS-12560: TNS:protocol adapter error
  TNS-00511: No listener
   Linux Error: 111: Connection refused
[oracle@oracle_ci ~]$ lsnrctl start

LSNRCTL for Linux: Version 11.2.0.1.0 - Production on 29-DEC-2018 00:31:36

Copyright (c) 1991, 2009, Oracle.  All rights reserved.

Starting /u01/app/oracle/product/11.2.0/dbhome_1/bin/tnslsnr: please wait...

TNSLSNR for Linux: Version 11.2.0.1.0 - Production
System parameter file is /u01/app/oracle/product/11.2.0/dbhome_1/network/admin/listener.ora
Log messages written to /u01/app/oracle/diag/tnslsnr/oracle_ci/listener/alert/log.xml
Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521)))
Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=oracle_ci)(PORT=1521)))

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC1521)))
STATUS of the LISTENER
------------------------
Alias                     LISTENER
Version                   TNSLSNR for Linux: Version 11.2.0.1.0 - Production
Start Date                29-DEC-2018 00:31:38
Uptime                    0 days 0 hr. 0 min. 0 sec
Trace Level               off
Security                  ON: Local OS Authentication
SNMP                      OFF
Listener Parameter File   /u01/app/oracle/product/11.2.0/dbhome_1/network/admin/listener.ora
Listener Log File         /u01/app/oracle/diag/tnslsnr/oracle_ci/listener/alert/log.xml
Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=oracle_ci)(PORT=1521)))
The listener supports no services
The command completed successfully
[oracle@oracle_ci ~]$ sql
sqlite3  sqlldr   sqlldrO  sqlplus  
[oracle@oracle_ci ~]$ sqlplus /nolog

SQL*Plus: Release 11.2.0.1.0 Production on Sat Dec 29 00:34:56 2018

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

SQL> conn /as sysdba
Connected to an idle instance.
SQL> startup
ORA-00119: invalid specification for system parameter LOCAL_LISTENER
ORA-00132: syntax error or unresolved network name 'LISTENER_ORCL'
SQL>

解决办法

修改init.ora.*文件

修改init.ora.4242018222136文件的local_listener，值为tnsnames.ora文件中的值(ADDRESS = (PROTOCOL = TCP)(HOST = oracle_ci)(PORT = 1521))，切记用引号包起来。
如：local_listener="(ADDRESS = (PROTOCOL = TCP)(HOST = oracle_ci)(PORT = 1521))"

[root@oracle_ci pfile]# cat /u01/app/oracle/admin/orcl/pfile/init.ora.4242018222136
##############################################################################
# Copyright (c) 1991, 2001, 2002 by Oracle Corporation
##############################################################################
 
###########################################
# Cache and I/O
###########################################
db_block_size=8192
 
###########################################
# Cursors and Library Cache
###########################################
open_cursors=300
 
###########################################
# Database Identification
###########################################
db_domain=""
db_name=orcl
 
###########################################
# File Configuration
###########################################
control_files=("/u01/app/oracle/oradata/orcl/control01.ctl", "/u01/app/oracle/flash_recovery_area/orcl/control02.ctl")
db_recovery_file_dest=/u01/app/oracle/flash_recovery_area
db_recovery_file_dest_size=4070572032
 
###########################################
# Miscellaneous
###########################################
compatible=11.2.0.0.0
diagnostic_dest=/u01/app/oracle
memory_target=787480576
 
###########################################
# Network Registration
###########################################
local_listener="(ADDRESS = (PROTOCOL = TCP)(HOST = oracle_ci)(PORT = 1521))"
 
###########################################
# Processes and Sessions
###########################################
processes=150
 
###########################################
# Security and Auditing
###########################################
audit_file_dest=/u01/app/oracle/admin/orcl/adump
audit_trail=db
remote_login_passwordfile=EXCLUSIVE
 
###########################################
# Shared Server
###########################################
dispatchers="(PROTOCOL=TCP) (SERVICE=orclXDB)"
 
###########################################
# System Managed Undo and Rollback Segments
###########################################
undo_tablespace=UNDOTBS1

[root@oracle_ci oracle]# cat /u01/app/oracle/product/11.2.0/dbhome_1/network/admin/tnsnames.ora
# tnsnames.ora Network Configuration File: /u01/app/oracle/product/11.2.0/dbhome_1/network/admin/tnsnames.ora
# Generated by Oracle configuration tools.

#LISTENER_ORCL =
#  (ADDRESS = (PROTOCOL = TCP)(HOST = oracle_ci)(PORT = 1521))


ORCL =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = oracle_ci)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME =orcl)
    )
  )
[root@oracle_ci oracle]#

执行startup pfile=’/u01/app/oracle/admin/orcl/pfile/init.ora.4242018222136’，数据库启动

SQL> startup pfile='/u01/app/oracle/admin/orcl/pfile/init.ora.4242018222136'
LRM-00116: syntax error at 'ADDRESS' following '('
ORA-01078: failure in processing system parameters
SQL> startup pfile='/u01/app/oracle/admin/orcl/pfile/init.ora.4242018222136'
ORACLE instance started.

Total System Global Area  784998400 bytes
Fixed Size		    2217464 bytes
Variable Size		  465570312 bytes
Database Buffers	  314572800 bytes
Redo Buffers		    2637824 bytes
Database mounted.
Database opened.