oracle 11.2.0.1静默升级11.2.0.4

原创

Liujun_Deng 2022-04-21 23:11:57 博主文章分类：Oracle ©著作权

文章标签 oracle sql linux 文章分类 Oracle 数据库

©著作权归作者所有：来自51CTO博客作者Liujun_Deng的原创作品，请联系作者获取转载授权，否则将追究法律责任

系统：CentOS 7.6

数据库：oracle 11.2.0.1

升级致11.2.0.4

1、11.2.0.1环境

SQL> select * from v$version;
BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
PL/SQL Release 11.2.0.1.0 - Production
CORE 11.2.0.1.0 Production
TNS for Linux: Version 11.2.0.1.0 - Production
NLSRTL Version 11.2.0.1.0 - Production

[oracle@dbserver ~]$ cat .bash_profile
# .bash_profile
# Get the aliases and functions
if [ -f ~/.bashrc ]; then
. ~/.bashrc
fi
# User specific environment and startup programs
PATH=$PATH:$HOME/.local/bin:$HOME/bin
export PATH
export ORACLE_BASE=/u01/app/oracle
export ORACLE_HOME=$ORACLE_BASE/product/11.2.0/db_1
export ORACLE_SID=orcl
export PATH=$ORACLE_HOME/bin:$PATH
export LD_LIBRARY_PATH=$ORACLE_HOME/lib

SQL> set line 200
SQL> col comp_id for a25
SQL> select comp_id,status,version from dba_registry
COMP_ID STATUS VERSION
------------------------- ---------------------- ------------------------------
OWB VALID 11.2.0.1.0
APEX VALID 3.2.1.00.10
EM VALID 11.2.0.1.0
AMD VALID 11.2.0.1.0
SDO VALID 11.2.0.1.0
ORDIM VALID 11.2.0.1.0
XDB VALID 11.2.0.1.0
CONTEXT VALID 11.2.0.1.0
EXF VALID 11.2.0.1.0
RUL VALID 11.2.0.1.0
OWM VALID 11.2.0.1.0
COMP_ID STATUS VERSION
------------------------- ---------------------- ------------------------------
CATALOG VALID 11.2.0.1.0
CATPROC VALID 11.2.0.1.0
JAVAVM VALID 11.2.0.1.0
XML VALID 11.2.0.1.0
CATJAVA VALID 11.2.0.1.0
APS VALID 11.2.0.1.0
XOQ VALID 11.2.0.1.0
18 rows selected.

2、升级前准备工作

2.1、备份数据库

升级前做数据库的完全备份，备份内容包括数据文件，控制文件，归档文件，日志文件，参数文件，密码文件等。

可以备份一下整个ORACLE_HOME目录，如果升级有问题，还可以还原回来。备份的方法可以使用dump数据泵，tar，rman等。

2.1.1、Rman全备

RMAN> backup database format '/home/oracle/rmanbak/df_%t_%s_%p.bak';
Starting backup at 19-APR-22
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=7 device type=DISK
channel ORA_DISK_1: starting full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
input datafile file number=00001 name=/u01/app/oracle/oradata/orcl/system01.dbf
input datafile file number=00002 name=/u01/app/oracle/oradata/orcl/sysaux01.dbf
input datafile file number=00003 name=/u01/app/oracle/oradata/orcl/undotbs01.dbf
input datafile file number=00004 name=/u01/app/oracle/oradata/orcl/users01.dbf
channel ORA_DISK_1: starting piece 1 at 19-APR-22
channel ORA_DISK_1: finished piece 1 at 19-APR-22
piece handle=/home/oracle/rmanbak/df_1102460780_3_1.bak tag=TAG20220419T230620 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:25
channel ORA_DISK_1: starting full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
including current control file in backup set
including current SPFILE in backup set
channel ORA_DISK_1: starting piece 1 at 19-APR-22
channel ORA_DISK_1: finished piece 1 at 19-APR-22
piece handle=/home/oracle/rmanbak/df_1102460806_4_1.bak tag=TAG20220419T230620 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:03
Finished backup at 19-APR-22

2.1.2、备份ORACLE_HOME和oraInventory

[root@dbserver ~]# tar -zcvPf product.tar.gz /u01/app/oracle >/dev/null
[root@dbserver ~]# ll
total 2617380
-rw-r--r--. 1 root root 233770311 Mar 2 14:04 alert_jbzyydb2.log20220302
-rw-------. 1 root root 2307 Nov 25 21:05 anaconda-ks.cfg
drwxr-xr-x. 2 root root 6 Nov 25 21:27 Desktop
drwxr-xr-x. 2 root root 6 Nov 25 21:27 Documents
drwxr-xr-x. 2 root root 6 Nov 25 21:27 Downloads
-rw-r--r--. 1 root root 2400 Nov 25 21:11 initial-setup-ks.cfg
drwxr-xr-x. 2 root root 6 Nov 25 21:27 Music
drwxr-xr-x. 2 root root 6 Nov 25 21:27 Pictures
-rw-r--r-- 1 root root 2446414252 Apr 19 23:43 product.tar.gz
drwxr-xr-x. 2 root root 6 Nov 25 21:27 Public
drwxr-xr-x. 2 root root 6 Nov 25 21:27 Templates
drwxr-xr-x. 2 root root 6 Nov 25 21:27 Videos

说明：通常情况需备份以下目录

/u01/app/oracle

/u01/app/oraInventory(安装清单)

/u01/app/grid

/u01/app/11.2.0/grid

3、升级数据库

3.1、关闭数据库

SQL> shutdown immediate;

3.2、关闭监听

[oracle@dbserver ~]$ lsnrctl stop
[oracle@dbserver ~]$ netstat -an|grep 1521

3.3、关闭em

[oracle@dbserver ~]$ emctl stop dbconsole
[oracle@dbserver ~]$ netstat -an|grep 1158

备注：切勿对数据库任何原有文件做操作，指导博客中有编辑数据库的监听文件，导致配置监听和EM错误

3.4、安装11.2.0.4软件包

3.4.1、软件包处理

sftp> cd /soft1
sftp> lcd F:\installmedium\11g
sftp> put p13390677_112040_Linux-x86-64_1of7.zip
Uploading p13390677_112040_Linux-x86-64_1of7.zip to /soft1/p13390677_112040_Linux-x86-64_1of7.zip
100% 1362873KB 97348KB/s 00:00:14
F:\installmedium\11g\p13390677_112040_Linux-x86-64_1of7.zip: 1395582860 bytes transferred in 14 seconds (97348 KB/s)
sftp> put p13390677_112040_Linux-x86-64_2of7.zip
Uploading p13390677_112040_Linux-x86-64_2of7.zip to /soft1/p13390677_112040_Linux-x86-64_2of7.zip
100% 1124320KB 112432KB/s 00:00:10
F:\installmedium\11g\p13390677_112040_Linux-x86-64_2of7.zip: 1151304589 bytes transferred in 10 seconds (112432 KB/s)

[root@dbserver soft]# chown -R oracle:oinstall /soft1
[root@dbserver soft]# cd /soft1
[root@dbserver soft1]# ll
total 2487200
-rw-r--r-- 1 oracle oinstall 1395582860 Sep 5 2021 p13390677_112040_Linux-x86-64_1of7.zip
-rw-r--r-- 1 oracle oinstall 1151304589 Sep 5 2021 p13390677_112040_Linux-x86-64_2of7.zip
[oracle@dbserver soft1]$ unzip -q p13390677_112040_Linux-x86-64_1of7.zip
[oracle@dbserver soft1]$ unzip -q p13390677_112040_Linux-x86-64_2of7.zip
[oracle@dbserver soft1]$ ll
total 2487200
drwxr-xr-x 7 oracle oinstall 136 Aug 27 2013 database
-rw-r--r-- 1 oracle oinstall 1395582860 Sep 5 2021 p13390677_112040_Linux-x86-64_1of7.zip
-rw-r--r-- 1 oracle oinstall 1151304589 Sep 5 2021 p13390677_112040_Linux-x86-64_2of7.zip

3.4.2、安装11.2.0.4

[oracle@dbserver database]./runInstaller -silent -debug -force -ignorePrereq \
DECLINE_SECURITY_UPDATES=true \
oracle.install.option=INSTALL_DB_SWONLY \
UNIX_GROUP_NAME=oinstall \
INVENTORY_LOCATION=/u01/app/oraInventory \
ORACLE_HOME=/u01/app/oracle/product/11.2.0.4/dbhome_1 \
ORACLE_BASE=/u01/app/oracle \
oracle.install.db.InstallEdition=EE \
oracle.install.db.DBA_GROUP=dba \
oracle.install.db.OPER_GROUP=oinstall

说明：ORACLE_BASE、INVENTORY_LOCATION可以和老版本共用一个目录。

ORACLE_HOME指向新目录11.2.0.4/dbhome_1(备注：以前是11.2.0/db_1)。

安装过程中有警告[WARNING] 时需要安装一些i386 包。查看log安装即可。

出现以下字样表示安装成功。

As a root user, execute the following script(s):
1. /u01/app/oraInventory/orainstRoot.sh
2. /u01/app/oracle/product/11.2.0.4/dbhome_1/root.sh
Successfully Setup Software.

执行脚本

[root@dbserver ~]# /u01/app/oraInventory/orainstRoot.sh
Changing permissions of /u01/app/oraInventory.
Adding read,write permissions for group.
Removing read,write,execute permissions for world.
Changing groupname of /u01/app/oraInventory to oinstall.
The execution of the script is complete.

[root@dbserver ~]# /u01/app/oracle/product/11.2.0.4/dbhome_1/root.sh
Check /u01/app/oracle/product/11.2.0.4/dbhome_1/install/root_dbserver_2022-04-20_22-35-20.log for the output of root script

3.4.3、配置新环境变量

[oracle@dbserver ~]$ cat .bash_profile
# .bash_profile
# Get the aliases and functions
if [ -f ~/.bashrc ]; then
. ~/.bashrc
fi
# User specific environment and startup programs
PATH=$PATH:$HOME/.local/bin:$HOME/bin
export PATH
export ORACLE_BASE=/u01/app/oracle
export ORACLE_HOME=$ORACLE_BASE/product/11.2.0/db_1
export ORACLE_SID=orcl
export PATH=$ORACLE_HOME/bin:$PATH
export LD_LIBRARY_PATH=$ORACLE_HOME/lib

说明：将ORACLE_HOME=$ORACLE_BASE/product/11.2.0/db_1
修改为ORACLE_HOME=$ORACLE_BASE/product/11.2.0.4/dbhome_1

[oracle@dbserver ~]$ source .bash_profile
[oracle@dbserver ~]$ env |grep ORA
ORACLE_SID=orcl
ORACLE_BASE=/u01/app/oracle
ORACLE_HOME=/u01/app/oracle/product/11.2.0.4/dbhome_1

注意：在10g以后，一般情况下环境变量中没有必要设置LD_LIBRARY_PATH，

但是一旦将ORACLE_HOME迁移到其他目录，则环境变量中还需要添加这个变量。

3.4.4、修改/etc/oratab内容

[oracle@localhost ~]$ vim /etc/oratab
orcl:/u01/app/oracle/product/11.2.0.4/dbhome_1:N

3.4.5、拷贝文件

拷贝参数文件到新目录

[oracle@dbserver dbs]$ pwd
/u01/app/oracle/product/11.2.0/db_1/database/dbs
[oracle@dbserver dbs]$ cp -a * /u01/app/oracle/product/11.2.0.4/dbhome_1/dbs

拷贝监听文件到新目录

[oracle@dbserver admin]$ pwd
/u01/app/oracle/product/11.2.0/db_1/network/admin
[oracle@dbserver admin]$ cp -a * /u01/app/oracle/product/11.2.0.4/dbhome_1/network/admin

3.4.6、启动监听

先修改新位置下listener.ora的路径，然后启动监听。

[oracle@dbserver admin]$ lsnrctl start
LSNRCTL for Linux: Version 11.2.0.4.0 - Production on 20-APR-2022 22:48:24
Copyright (c) 1991, 2013, Oracle. All rights reserved.
Starting /u01/app/oracle/product/11.2.0.4/dbhome_1/bin/tnslsnr: please wait...
TNSLSNR for Linux: Version 11.2.0.4.0 - Production
System parameter file is /u01/app/oracle/product/11.2.0.4/dbhome_1/network/admin/listener.ora
Log messages written to /u01/app/oracle/diag/tnslsnr/dbserver/listener/alert/log.xml
Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=192.168.133.125)(PORT=1521)))
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=192.168.133.125)(PORT=1521)))
STATUS of the LISTENER
------------------------
Alias LISTENER
Version TNSLSNR for Linux: Version 11.2.0.4.0 - Production
Start Date 20-APR-2022 22:48:24
Uptime 0 days 0 hr. 0 min. 0 sec
Trace Level off
Security ON: Local OS Authentication
SNMP OFF
Listener Parameter File /u01/app/oracle/product/11.2.0.4/dbhome_1/network/admin/listener.ora
Listener Log File /u01/app/oracle/diag/tnslsnr/dbserver/listener/alert/log.xml
Listening Endpoints Summary...
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=192.168.133.125)(PORT=1521)))
The listener supports no services
The command completed successfully

3.4.7、升级数据库

注意：退出oracle用户重新su - oracle，sqlplus能看到11.2.0.4.0字样才行

[oracle@dbserver admin]$ sqlplus / as sysdba
SQL*Plus: Release 11.2.0.4.0 Production on Wed Apr 20 22:49:22 2022
Copyright (c) 1982, 2013, Oracle. All rights reserved.
Connected to an idle instance.
SQL> startup upgrade
ORACLE instance started.
Total System Global Area 3273641984 bytes
Fixed Size 2257680 bytes
Variable Size 1862274288 bytes
Database Buffers 1392508928 bytes
Redo Buffers 16601088 bytes
Database mounted.
Database opened.

SQL> @$ORACLE_HOME/rdbms/admin/catupgrd.sql 说明：此脚本在该库无数据的情况下耗时15min

备注：以上catupgrd.sql脚本执行完后会shutdown immediate数据库，重启数据库即可。

[oracle@dbserver admin]$ sqlplus /nolog
SQL*Plus: Release 11.2.0.4.0 Production on Wed Apr 20 23:06:11 2022
Copyright (c) 1982, 2013, Oracle. All rights reserved.
SQL> conn / as sysdba
Connected to an idle instance.
SQL> startup
ORACLE instance started.
Total System Global Area 3273641984 bytes
Fixed Size 2257680 bytes
Variable Size 1946160368 bytes
Database Buffers 1308622848 bytes
Redo Buffers 16601088 bytes
Database mounted.
Database opened.
SQL> select * from v$version;
BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
PL/SQL Release 11.2.0.4.0 - Production
CORE 11.2.0.4.0 Production
TNS for Linux: Version 11.2.0.4.0 - Production
NLSRTL Version 11.2.0.4.0 - Production

3.4.8、编译失效对象

3.4.8.1、查询失效对象

SQL> select * from dba_objects where status !='VALID';
SQL> select count(*) from dba_objects where status='INVALID';
COUNT(*)
----------
453

3.4.8.2、编译失效对象

运行utlrp.sql编译失效对象

SQL> @?/rdbms/admin/utlrp

至此数据库已经升级完成。重启数据库没有问题就是升级成功。

SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup
ORACLE instance started.
Total System Global Area 3273641984 bytes
Fixed Size 2257680 bytes
Variable Size 1862274288 bytes
Database Buffers 1392508928 bytes
Redo Buffers 16601088 bytes
Database mounted.
Database opened.
SQL> set line 200
SQL> select comp_id,status,version from dba_registry
COMP_ID STATUS VERSION
------------------------------ ---------------------- ------------------------------
OWB VALID 11.2.0.1.0
APEX VALID 3.2.1.00.10
EM VALID 11.2.0.4.0
AMD VALID 11.2.0.4.0
SDO VALID 11.2.0.4.0
ORDIM VALID 11.2.0.4.0
XDB VALID 11.2.0.4.0
CONTEXT VALID 11.2.0.4.0
EXF VALID 11.2.0.4.0
RUL VALID 11.2.0.4.0
OWM VALID 11.2.0.4.0
COMP_ID STATUS VERSION
------------------------------ ---------------------- ------------------------------
CATALOG VALID 11.2.0.4.0
CATPROC VALID 11.2.0.4.0
JAVAVM VALID 11.2.0.4.0
XML VALID 11.2.0.4.0
CATJAVA VALID 11.2.0.4.0
APS VALID 11.2.0.4.0
XOQ VALID 11.2.0.4.0
18 rows selected.

3.4.9、更新服务脚本

更新数据库服务脚本

将/u01/app/oracle/product/11.2.0.4/dbhome_1/bin/下的dbstart和dbshut，ORACLE_HOME_LISTNER=$1改为$ORACLE_HOME，如下：

#ORACLE_HOME_LISTNER=$1
ORACLE_HOME_LISTNER=$ORACLE_HOME

3.4.10、检查确认

SQL> select status from v$instance;
STATUS
------------
OPEN #注意：成功打开数据库后，这里将是OPEN，而非OPEN MIGRATE

SQL> select * from v$version;
BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
PL/SQL Release 11.2.0.4.0 - Production
CORE 11.2.0.4.0 Production
TNS for Linux: Version 11.2.0.4.0 - Production
NLSRTL Version 11.2.0.4.0 - Production

3.4.11、重建EM

升级成功后需重建EM，没有EM可忽略

手工创建EM资料库:

####emca -repos drop
[oracle@localhost admin]$ emca -reposdrop
####emca -repos create
[oracle@localhost admin]$ emca -reposcreate
###emca -config dbcontrol db
[oracle@localhost admin]$ emca-config dbcontrol db

参考网址：http://blog.itpub.net/28916011/viewspace-2683059/