共计 11848 个字符，预计需要花费 30 分钟才能阅读完成。
Oracle 从 11.2.0.1 升级到 11.2.0.4 版本提示 ORA-00119 错误解决
1、在线对数据库版本进行升级后，Oracle 启动失败。
升级之前好好的，正常都能启动，从 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 里面有写？
2、检查 oracle 配置文件
（1）检查 sqlnet.ora，没有 ht_121_90 的配置
[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]$
（2）检查 listenor.ora，也没有 ht_121_90 的配置
[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。
3、检查服务器网络配置
（1）查看 hosts 配置，没有 ht_121_90 的标识
[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]$
（2）查看 ifconfig 配置，也没有 ht_121_90 的标识
[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]$
（3）查看主机名
[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 ~]#
分析：看到主机名是 ht_121_90，猜测 oracle 升级后，默认是通过主机名 hostname 来启动 listener.ora 的，而主机名去对应 ip 地址，一般走的是 /etc/hosts，需要在 /etc/hosts 里面添加主机名和 ip 地址的对应。所以去修改 /etc/hosts
4、修改 hosts 启动 oracle 实例
（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
（2）启动 oracle 实例
[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>
5、治疗升级后遗症
看到有“ORA-39700: database must be opened with UPGRADE option”这样的提示，就知道了升级没有完全成功，还需要执行一些系统的 sql 脚本
（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;
（2）重启数据库
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 专题页面 http://www.linuxidc.com/topicnews.aspx?tid=12
本文永久更新链接地址 ：http://www.linuxidc.com/Linux/2018-01/150243.htm
