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>