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> |
[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> |
