Oracle 从 11.2.0.1 升级到 11.2.0.4 版本提示ORA

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

内容版权声明:除非注明,否则皆为本站原创文章。

转载注明出处:https://www.heiqu.com/e6f8711d87b516e4182a064acfc06242.html