ORACLE 从 11.2.0.1 升级到 11.2.0.4 版本之ORA-00119问题处理纪实

Oracle 前端之家
前端之家收集整理的这篇文章主要介绍了ORACLE 从 11.2.0.1 升级到 11.2.0.4 版本之ORA-00119问题处理纪实，前端之家小编觉得挺不错的，现在分享给大家，也给大家做个参考。

@H_403_1@1、@H_403_1@在线对数据库版本进行升级后，@H_403_1@oracle@H_403_1@启动失败

升级之前好好，正常都能启动，从11.2.0.1升级到11.2.0.4后，启动报错

sql> startup;

ORA-00119: invalid specification for system parameter LOCAL_LISTENER

ORA-00130: invalid listener address '(ADDRESS=(PROTOCOL=TCP)(HOST=ht_121_90)(PORT=1521))'

sql>

诡异了，啥都没有变动过，db半年以来都没有变动了，难道listener.ora、sqlnet.ora里面有写？

@H_403_1@2@H_403_1@、检查@H_403_1@oracle@H_403_1@配置文件

@H_403_1@（@H_403_1@1@H_403_1@）检查@H_403_1@sqlnet.ora@H_403_1@，没有@H_403_1@ht_121_90@H_403_1@的配置

[oracle@ht_121_90 admin]$ more sqlnet.ora

# sqlnet.ora Network Configuration File: /home/oracle/app/oracle/product/11.2.0/dbhome_1/network/admin/sqlnet.ora

# Generated by Oracle configuration tools.

NAMES.DIRECTORY_PATH= (TNSNAMES,EZCONNECT)

ADR_BASE = /oracle/app/oracle

[oracle@ht_121_90 admin]$

@H_403_1@（@H_403_1@2@H_403_1@）检查@H_403_1@listenor.ora@H_403_1@，也没有@H_403_1@ht_121_90@H_403_1@的配置

[oracle@ht_121_90 admin]$ more listener.ora

SID_LIST_LISTENER =

(SID_LIST =

(SID_DESC =

(SID_NAME = PLSExtProc)

(ORACLE_HOME = /home/oracle/app/oracle/product/11.2.0/dbhome_1)

(PROGRAM = extproc)

)

(SID_DESC =

(SID_NAME = powerdes)

(ORACLE_HOME = /home/oracle/app/oracle/product/11.2.0/dbhome_1)

)

)

LISTENER =

(DESCRIPTION_LIST =

(DESCRIPTION =

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

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

)

)

ADR_BASE_LISTENER = /home/oracle/app/oracle

[oracle@ht_121_90 admin]$

看了下，oracle的基本配置里面都正常，指向ip地址，没有配置ht_121_90。

@H_403_1@3@H_403_1@、检查服务器网络配置

@H_403_1@（@H_403_1@1@H_403_1@）查看@H_403_1@hosts@H_403_1@配置，没有@H_403_1@ht_121_90@H_403_1@的标识

[oracle@ht_121_90 admin]$ more /etc/hosts

127.0.0.1 hch_test_121_90 hch_test_121_90.

192.168.121.90 hch_test_121_90

[oracle@ht_121_90 admin]$

@H_403_1@（@H_403_1@2@H_403_1@）查看@H_403_1@ifconfig@H_403_1@配置，也没有@H_403_1@ht_121_90@H_403_1@的标识

[oracle@ht_121_90 admin]$ ifconfig

eth0 Link encap:Ethernet HWaddr 00:0C:29:30:AF:9F

inet addr:192.168.121.90 Bcast:192.168.121.255 Mask:255.255.254.0

inet6 addr: fe80::20c:29ff:fe30:af9f/64 Scope:Link

UP BROADCAST RUNNING MULTICAST MTU:1500 Metric:1

RX packets:35786 errors:0 dropped:0 overruns:0 frame:0

TX packets:4150 errors:0 dropped:0 overruns:0 carrier:0

collisions:0 txqueuelen:1000

RX bytes:3853621 (3.6 MiB) TX bytes:671203 (655.4 KiB)

lo Link encap:Local Loopback

inet addr:127.0.0.1 Mask:255.0.0.0

inet6 addr: ::1/128 Scope:Host

UP LOOPBACK RUNNING MTU:16436 Metric:1

RX packets:37 errors:0 dropped:0 overruns:0 frame:0

TX packets:37 errors:0 dropped:0 overruns:0 carrier:0

collisions:0 txqueuelen:0

RX bytes:2806 (2.7 KiB) TX bytes:2806 (2.7 KiB)

[oracle@ht_121_90 admin]$

@H_403_1@（@H_403_1@3@H_403_1@）查看主机名

[root@ht_121_90 ~]# more /etc/sysconfig/network

NETWORKING=yes

HOSTNAME=ht_121_90

[root@ht_121_90 ~]#

[root@ht_121_90 ~]# hostname

ht_121_90

[root@ht_121_90 ~]#

@H_403_1@分析：看到主机名是ht_121_90，猜测oracle升级后，默认是通过主机名hostname来启动listener.ora的，而主机名去对应ip地址，一般走的是/etc/hosts，需要在/etc/hosts里面添加主机名和ip地址的对应。所以去修改/etc/hosts

@H_403_1@4@H_403_1@、修改@H_403_1@hosts@H_403_1@启动@H_403_1@oracle@H_403_1@实例

@H_403_1@（@H_403_1@1@H_403_1@）修改主机名

[root@ht_121_90 ~]# more /etc/hosts

127.0.0.1 hch_test_121_90 hch_test_121_90.

192.168.121.90 hch_test_121_90 ht_121_90

@H_403_1@（@H_403_1@2@H_403_1@）启动@H_403_1@oracle@H_403_1@实例

[root@ht_121_90 ~]#

sql> startup;

ORACLE instance started.

Total System Global Area 1603411968 bytes

Fixed Size 2213776 bytes

Variable Size 1040189552 bytes

Database Buffers 553648128 bytes

Redo Buffers 7360512 bytes

Database mounted.

ORA-01092: ORACLE instance terminated. Disconnection forced

ORA-00704: bootstrap process failure

ORA-39700: database must be opened with UPGRADE option

Process ID: 1605

Session ID: 191 Serial number: 3

sql>

@H_403_1@5@H_403_1@、治疗升级后遗症

看到有“ORA-39700: database must be opened with UPGRADE option”这样的提示，就知道了升级没有完全成功，还需要执行一些系统的sql脚本

@H_403_1@（@H_403_1@1@H_403_1@）执行升级脚本

sql> @$ORACLE_HOME/rdbms/admin/utlu112s.sql

......

sql> @$ORACLE_HOME/rdbms/admin/catuppst.sql

......执行实际比较长

sql> @$ORACLE_HOME/rdbms/admin/utlrp.sql

sql> ALTER SYSTEM SET CLUSTER_DATABASE=true SCOPE=SPFILE;

@H_403_1@（@H_403_1@2@H_403_1@）重启数据库

sql> shutdown immediate;

Database closed.

Database dismounted.

ORACLE instance shut down.

sql>

sql>

sql> startup;

ORACLE instance started.

Total System Global Area 1603411968 bytes

Fixed Size 2213776 bytes

Variable Size 1040189552 bytes

Database Buffers 553648128 bytes

Redo Buffers 7360512 bytes

Database mounted.

Database opened.

sql>

设置默认的路径为新的路径

su - oracle

vim /home/oracle/.bash_profile

将 /home/oracle/app/oracle/product/11.2.0改成 /home/oracle/app/oracle/product/11.2.0.4

然后重新启动oracle实例

[oracle@ht_121_90 ~]$ rlwrap sqlplus / as sysdba

sql*Plus: Release 11.2.0.4.0 Production on Fri Mar 17 20:36:25 2017

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

Connected to an idle instance.

sql> startup;

ORA-01078: failure in processing system parameters

LRM-00109: could not open parameter file '/home/oracle/app/oracle/product/11.2.0.4/dbhome_1/dbs/initpowerdes.ora'

sql>

sql> startup pfile='/oracle/pfile_20160317.ora';

ORACLE instance started.

Total System Global Area 1603411968 bytes

Fixed Size 2253664 bytes

Variable Size 469765280 bytes

Database Buffers 1124073472 bytes

Redo Buffers 7319552 bytes

ORA-00205: error in identifying control file,check alert log for more info

sql>

启动貌似找不到控制文件，去看后台alert日志

[root@ht_121_90 ~]# tail -f /oracle/app/oracle/diag/rdbms/powerdes/powerdes/trace/alert_powerdes.log

starting up 1 shared server(s) ...

ORACLE_BASE from environment = /oracle/app/oracle

Fri Mar 17 20:52:25 2017

ALTER DATABASE MOUNT

ORA-00210: cannot open the specified control file

ORA-00202: control file: '/data/oracle/powerdes/control01.ctl'

ORA-27086: unable to lock file - already in use

Linux-x86_64 Error: 11: Resource temporarily unavailable

Additional information: 8

Additional information: 1940

ORA-205 signalled during: ALTER DATABASE MOUNT..

看到有进程一直在唉用这个控制文件，先关闭下，看看别的进程

sql> shutdown immedaite;

SP2-0717: illegal SHUTDOWN option

sql> shutdown immediate;

ORA-01507: database not mounted

ORACLE instance shut down.

sql>

sql>

sql> exit

Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production

With the Partitioning,OLAP,Data Mining and Real Application Testing options

[oracle@ht_121_90 dbs]$ ps -eaf|grep oracle

oracle 1918 1 0 20:31 ? 00:00:00 ora_pmon_powerdes

oracle 1920 1 0 20:31 ? 00:00:00 ora_vktm_powerdes

oracle 1924 1 0 20:31 ? 00:00:00 ora_gen0_powerdes

oracle 1926 1 0 20:31 ? 00:00:00 ora_diag_powerdes

oracle 1928 1 0 20:31 ? 00:00:00 ora_dbrm_powerdes

oracle 1930 1 0 20:31 ? 00:00:00 ora_psp0_powerdes

oracle 1932 1 0 20:31 ? 00:00:00 ora_dia0_powerdes

oracle 1934 1 0 20:31 ? 00:00:00 ora_mman_powerdes

oracle 1936 1 0 20:31 ? 00:00:00 ora_dbw0_powerdes

oracle 1938 1 0 20:31 ? 00:00:00 ora_lgwr_powerdes

oracle 1940 1 0 20:31 ? 00:00:00 ora_ckpt_powerdes

oracle 1942 1 1 20:31 ? 00:00:21 ora_smon_powerdes

oracle 1944 1 0 20:31 ? 00:00:00 ora_reco_powerdes

oracle 1946 1 0 20:31 ? 00:00:01 ora_mmon_powerdes

oracle 1948 1 0 20:31 ? 00:00:00 ora_mmnl_powerdes

oracle 1950 1 0 20:31 ? 00:00:00 ora_d000_powerdes

oracle 1952 1 0 20:31 ? 00:00:00 ora_s000_powerdes

oracle 1960 1 0 20:31 ? 00:00:00 ora_arc0_powerdes

oracle 1962 1 0 20:31 ? 00:00:00 ora_arc1_powerdes

oracle 1964 1 0 20:31 ? 00:00:00 ora_arc2_powerdes

oracle 1966 1 0 20:31 ? 00:00:00 ora_arc3_powerdes

oracle 1970 1 0 20:31 ? 00:00:00 ora_qmnc_powerdes

oracle 1984 1 0 20:31 ? 00:00:00 ora_cjq0_powerdes

oracle 1994 1 0 20:31 ? 00:00:00 ora_q000_powerdes

oracle 1998 1 0 20:31 ? 00:00:00 ora_q002_powerdes

oracle 2129 1 0 20:36 ? 00:00:00 ora_smco_powerdes

oracle 2287 1 0 20:44 ? 00:00:00 /home/oracle/app/oracle/product/11.2.0.4/dbhome_1/bin/tnslsnr LISTENER -inherit

oracle 2320 1 0 20:47 ? 00:00:01 oraclepowerdes (LOCAL=NO)

oracle 2329 1 0 20:47 ? 00:00:00 /home/oracle/app/oracle/product/11.2.0.4/dbhome_1/bin/tnslsnr LISTENER -inherit

root 2436 1735 0 20:52 pts/2 00:00:00 su - oracle

oracle 2437 2436 0 20:52 pts/2 00:00:00 -bash

oracle 2546 1 0 20:56 ? 00:00:00 ora_w000_powerdes

root 2644 1199 0 20:58 pts/0 00:00:00 tail -f /oracle/app/oracle/diag/rdbms/powerdes/powerdes/trace/alert_powerdes.log

oracle 2769 1 1 21:01 ? 00:00:00 ora_j000_powerdes

oracle 2771 1 0 21:01 ? 00:00:00 ora_j001_powerdes

oracle 2772 2437 1 21:01 pts/2 00:00:00 ps -eaf

oracle 2773 2437 0 21:01 pts/2 00:00:00 grep oracle

[oracle@ht_121_90 dbs]$

想起来了，这是老的版本的sqlplus进程在运行，也有可能是upgrade会用一个辅助实例，把辅助实例关闭，自己找进程去kill就OK了。

[oracle@ht_121_90 dbs]$ ps -eaf|grep oracle

oracle 1918 1 0 20:31 ? 00:00:00 ora_pmon_powerdes

oracle 1920 1 0 20:31 ? 00:00:00 ora_vktm_powerdes

oracle 1924 1 0 20:31 ? 00:00:00 ora_gen0_powerdes

oracle 1926 1 0 20:31 ? 00:00:00 ora_diag_powerdes

oracle 1928 1 0 20:31 ? 00:00:00 ora_dbrm_powerdes

oracle 1930 1 0 20:31 ? 00:00:00 ora_psp0_powerdes

oracle 1932 1 0 20:31 ? 00:00:01 ora_dia0_powerdes

oracle 1934 1 0 20:31 ? 00:00:00 ora_mman_powerdes

oracle 1936 1 0 20:31 ? 00:00:00 ora_dbw0_powerdes

oracle 1938 1 0 20:31 ? 00:00:00 ora_lgwr_powerdes

oracle 1940 1 0 20:31 ? 00:00:00 ora_ckpt_powerdes

oracle 1942 1 0 20:31 ? 00:00:23 ora_smon_powerdes

oracle 1944 1 0 20:31 ? 00:00:00 ora_reco_powerdes

oracle 1946 1 0 20:31 ? 00:00:01 ora_mmon_powerdes

oracle 1948 1 0 20:31 ? 00:00:00 ora_mmnl_powerdes

oracle 1950 1 0 20:31 ? 00:00:00 ora_d000_powerdes

oracle 1952 1 0 20:31 ? 00:00:00 ora_s000_powerdes

oracle 1960 1 0 20:31 ? 00:00:00 ora_arc0_powerdes

oracle 1962 1 0 20:31 ? 00:00:00 ora_arc1_powerdes

oracle 1964 1 0 20:31 ? 00:00:00 ora_arc2_powerdes

oracle 1966 1 0 20:31 ? 00:00:00 ora_arc3_powerdes

oracle 1970 1 0 20:31 ? 00:00:00 ora_qmnc_powerdes

oracle 1984 1 0 20:31 ? 00:00:00 ora_cjq0_powerdes

oracle 1994 1 0 20:31 ? 00:00:00 ora_q000_powerdes

oracle 1998 1 0 20:31 ? 00:00:00 ora_q002_powerdes

oracle 2129 1 0 20:36 ? 00:00:00 ora_smco_powerdes

oracle 2320 1 0 20:47 ? 00:00:01 oraclepowerdes (LOCAL=NO)

oracle 2329 1 0 20:47 ? 00:00:00 /home/oracle/app/oracle/product/11.2.0.4/dbhome_1/bin/tnslsnr LISTENER -inherit

root 2436 1735 0 20:52 pts/2 00:00:00 su - oracle

oracle 2437 2436 0 20:52 pts/2 00:00:00 -bash

oracle 2546 1 0 20:56 ? 00:00:00 ora_w000_powerdes

root 2644 1199 0 20:58 pts/0 00:00:00 tail -f /oracle/app/oracle/diag/rdbms/powerdes/powerdes/trace/alert_powerdes.log

oracle 2872 2437 0 21:12 pts/2 00:00:00 ps -eaf

oracle 2873 2437 0 21:12 pts/2 00:00:00 grep oracle

[oracle@ht_121_90 dbs]$

[oracle@ht_121_90 dbs]$

[oracle@ht_121_90 dbs]$ kill -9 1918 ;

[oracle@ht_121_90 dbs]$ kill -9 1920 ;

[oracle@ht_121_90 dbs]$ kill -9 1924 ;

[oracle@ht_121_90 dbs]$ kill -9 1926 ;

[oracle@ht_121_90 dbs]$ kill -9 1928 ;

[oracle@ht_121_90 dbs]$ kill -9 1930 ;

[oracle@ht_121_90 dbs]$ kill -9 1932 ;

[oracle@ht_121_90 dbs]$ kill -9 1934 ;

[oracle@ht_121_90 dbs]$ kill -9 1936 ;

[oracle@ht_121_90 dbs]$ kill -9 1938 ;

[oracle@ht_121_90 dbs]$ kill -9 1940 ;

[oracle@ht_121_90 dbs]$ kill -9 1942 ;

[oracle@ht_121_90 dbs]$ kill -9 1944 ;

[oracle@ht_121_90 dbs]$ kill -9 1946 ;

[oracle@ht_121_90 dbs]$ kill -9 1948 ;

[oracle@ht_121_90 dbs]$ kill -9 1950 ;

[oracle@ht_121_90 dbs]$ kill -9 1952 ;

[oracle@ht_121_90 dbs]$ kill -9 1960 ;

[oracle@ht_121_90 dbs]$ kill -9 1962 ;

[oracle@ht_121_90 dbs]$ kill -9 1964 ;

[oracle@ht_121_90 dbs]$ kill -9 1966 ;

[oracle@ht_121_90 dbs]$ kill -9 1970 ;

[oracle@ht_121_90 dbs]$ kill -9 1984 ;

[oracle@ht_121_90 dbs]$ kill -9 1994 ;

[oracle@ht_121_90 dbs]$ kill -9 1998 ;

[oracle@ht_121_90 dbs]$ kill -9 2129 ;

[oracle@ht_121_90 dbs]$

[oracle@ht_121_90 dbs]$ ps -eaf|grep oracle

oracle 2320 1 0 20:47 ? 00:00:01 oraclepowerdes (LOCAL=NO)

oracle 2329 1 0 20:47 ? 00:00:00 /home/oracle/app/oracle/product/11.2.0.4/dbhome_1/bin/tnslsnr LISTENER -inherit

root 2436 1735 0 20:52 pts/2 00:00:00 su - oracle

oracle 2437 2436 0 20:52 pts/2 00:00:00 -bash

oracle 2546 1 0 20:56 ? 00:00:00 ora_w000_powerdes

root 2644 1199 0 20:58 pts/0 00:00:00 tail -f /oracle/app/oracle/diag/rdbms/powerdes/powerdes/trace/alert_powerdes.log

oracle 2886 2437 0 21:15 pts/2 00:00:00 ps -eaf

oracle 2887 2437 0 21:15 pts/2 00:00:00 grep oracle

[oracle@ht_121_90 dbs]$

然后再进去重启oracle服务，不会再报错，能正常启动了

sql> shutdown immediate;

Database closed.

Database dismounted.

ORACLE instance shut down.

sql> startup;

ORACLE instance started.

Total System Global Area 1603411968 bytes

Fixed Size 2253664 bytes

Variable Size 553651360 bytes

Database Buffers 1040187392 bytes

Redo Buffers 7319552 bytes

Database mounted.

Database opened.

sql>

sql> create pfile from spfile;

File created.

sql> shutdown immediate;

Database closed.

Database dismounted.

ORACLE instance shut down.

sql> create spfile from pfile;

File created.

sql> startup;

ORACLE instance started.

Total System Global Area 1603411968 bytes

Fixed Size 2253664 bytes

Variable Size 469765280 bytes

Database Buffers 1124073472 bytes

Redo Buffers 7319552 bytes

Database mounted.

Database opened.

sql>

猜你在找的Oracle相关文章

[Oracle]记一次由sequence引发的enq sv-contention等待事件
数据库版本：11.2.0.4 RAC(1)问题现象从EM里面可以看到，在23号早上8：45~8：55时，数据库...
oracle统计信息的锁定与解锁
（一）问题背景最近在对一个大约200万行数据的表查看执行计划时，发现存在异常，理论上应该...
【转】RMAN删除过期备份或非过期备份
（一）删除备份--DELETE命令用于删除RMAN备份记录及相应的物理文件。当使用RMAN执行备份操...
数据恢复顾问（DRA）
（1）DRA介绍 数据恢复顾问（Data Recovery Advise）是一个诊断和修复数据库的工具，DRA能...
RMAN备份与恢复（一）--认识RMAN
RMAN（Recovery Manager）是Oracle恢复管理器的简称，是集数据库备份（backup）、修复（re...
RMAN备份与恢复（二）--RMAN常用操作学习
（1）连接目标数据库 在RMAN中可以建立与目标数据库或恢复目录数据库的连接。与目标数据库...
ORA-00245问题总结
（1）问题描述 在进行数据库归档备份时（备份归档日志文件和控制文件），有时成功，有时失...
使用RMAN对数据文件进行恢复
（1）备份数据库 在使用RMAN进行数据库恢复之前，先用RMAN进行全库备份 （2）删除数据文件...
Oracle使用fy_recover_data恢复truncate删除的数据
(一)truncate操作概述在生产中，truncate是使用较多的命令，在使用不当的情况下，往往会造...
备份与恢复（四）--RMAN使用恢复目录catalog来保存档案资料库
（一）恢复目录概述RMAN档案资料库用于存储数据库备份、修复以及恢复所需的信息。这些信息...
MySQLMsSQLOracleSqlitePostgre SQLMariadbMongoDBNoSQLHBaseJDBC
undo日志persistent-cmysql-error-postal-codesql-match-almysql-5.6mysql-8.0database-tri安装路径系统错误data_dir丢失文件主从同步sql_mode数据库目录匿名用户character_seID归零数据库位置查询表重复字段查询字段截断日志SUSPECT7391Remote ServeLinked Serve玄学问题登录不上开启远程访问