目录
- 一、前言
- 二、升级oracle数据库简介
- 三、升级前的准备
- 1、选择oracle数据库升级方法
- 2、数据库备份
- 3、oracle数据库升级核对清单
- 4、安装新版本oracle数据库软件
- 4.1、创建目录并授权
- 4.2、添加用户组
- 4.3、修改环境变量
- 4.4、上传oracle数据库安装软件到服务器并解压
- 4.5、编辑db_install.rsp响应文件
- 4.6、安装数据库软件
- 4.7、执行配置脚本
- 5、升级前信息检查
- 6、升级前验证物化视图刷新是否完成
- 7、确保升级前没有处于备份模式的文件
- 8、确保升级前没有文件需要介质恢复
- 9、升级前清除数据库回收站
- 四、升级oracle数据库
- 五、升级后的工作
- 六、总结
- 七、写在后面
今天向大家介绍如何将oracle数据库通过静默方式将11.2.0.4升级到12.2.0,并提供详细的步骤和建议。升级到新版本的数据库可以带来许多优势,包括更好的性能、更高的安全性和更多的功能。然而,在进行升级之前,强烈建议在测试环境中进行充分的测试和验证,以确保升级过程顺利,并且不会对现有的应用程序造成不必要的影响。
可以从以下版本直接升级到新版本(12.2.0):
11.2.0.3 或更高版本
12.1.0.1 和 12.1.0.2
如果当前oracle版本低于 11.2.0.3 的版本,则无法直接将oracle升级到最新版本。在这种情况下,您需要先升级到中间版本,然后再升级到oracle 12cr2。
如果无法直接升级到当前最新版本,那么请升级到支持直接升级的最新版本,再二次升级到当前最新版本。
图 2-1 oracle 12c升级路线图
1、选择oracle数据库升级方法
1.1、database upgrade assistant
数据库升级助手(dbua)以交互方式指导您完成升级过程,并为新的oracle数据库版本配置数据库。
dbua启动预升级工具,该工具会自动将一些配置设置修复为升级所需的值。例如,预升级工具可以将初始化参数更改为升级所需的值。升级前工具还为您提供了在继续升级之前需要手动修复的项目列表。
本文采用dbua方式进行数据库升级。
1.2、command-line method
手动升级包括从命令行运行sql脚本,将数据库升级到新的oracle数据库版本。
手动升级使您能够更好地控制升级过程。但是,如果没有遵循升级或预升级步骤,或者执行顺序不正确,手动升级很容易出错。
2、数据库备份
升级前请务必做好数据库备份!
rman>
configure controlfile autobackup on;
run
{
allocate channel d1 type disk;
backup database format '/soft/rmanbak/hellodb_%u';
backup current controlfile format '/soft/rmanbak/hellodb_ctl';
}
3、oracle数据库升级核对清单
3.1、升级前清单核对
在开始升级之前,请使用此核对表为当前oracle数据库服务器进行核对。
表 3-1 源服务器准备升级清单
3.2、升级后清单核对
在升级后的 oracle 数据库环境中完成这些检查。
表 3-2 目标服务器升级后核对清单
4、安装新版本oracle数据库软件
4.1、创建目录并授权
[root@hellodba ~]# mkdir -p /u01/app/oracle/product/12.2.0/dbhome_1
[root@hellodba ~]# chown -r oracle:oinstall /u01/app/oracle/product/12.2.0/dbhome_1
[root@hellodba ~]# chmod -r 775 /u01/app/oracle/product/12.2.0/dbhome_1
4.2、添加用户组
[root@hellodba ~]# /usr/sbin/groupadd -g 504 backupdba
[root@hellodba ~]# /usr/sbin/groupadd -g 505 dgdba
[root@hellodba ~]# /usr/sbin/groupadd -g 506 kmdba
[root@hellodba ~]# /usr/sbin/usermod -g oinstall -g dba,backupdba,dgdba,kmdba,oper oracle
4.3、修改环境变量
[oracle@hellodba ~]$ cp ~/.bash_profile ~/.bash_profile_bak
[oracle@hellodba ~]$ vi ~/.bash_profile
umask=022
export ps1
export tmp=/tmp
export tmpdir=$tmp
export oracle_unqname=hellodb
export oracle_sid=hellodb;
export oracle_base=/u01/app/oracle;
export oracle_home=$oracle_base/product/12.2.0/dbhome_1;
export oracle_term=xterm;
export nls_date_format="yyyy-mm-dd hh24:mi:ss";
export nls_lang=american_america.utf8;
export lang=en_us.utf8;
export path=.:$path:$home/.local/bin:$home/bin:$oracle_base/product/12.2.0/dbhome_1/bin:$oracle_home/bin;
export threads_flag=native;
[oracle@hellodba ~]$ source ~/.bash_profile
4.4、上传oracle数据库安装软件到服务器并解压
//通过oracle用户上传oracle 12.2数据库安装软件到服务器 /soft 目录
[oracle@hellodba ~]$ cd /soft
[oracle@hellodba soft]$ unzip -q linuxx64_12201_database.zip
4.5、编辑db_install.rsp响应文件
[oracle@hellodba ~]$ cd /soft/database/response
[oracle@hellodba response]$ echo '' > db_install.rsp
[oracle@hellodba response]$ vi db_install.rsp
#软件版本信息
oracle.install.responsefileversion=/oracle/install/rspfmt_dbinstall_response_schema_v12.2.0
#安装选项-仅安装数据库软件
oracle.install.option=install_db_swonly
#oracle用户用于安装软件的组名
unix_group_name=oinstall
#oracle产品清单目录
inventory_location=/u01/app/orainventory
#oracle安装目录
oracle_home=/u01/app/oracle/product/12.2.0/dbhome_1
#oracle基础目录
oracle_base=/u01/app/oracle
#安装版本类型:企业版
oracle.install.db.installedition=ee
#指定组信息
oracle.install.db.osdba_group=dba
oracle.install.db.osoper_group=oper
oracle.install.db.osbackupdba_group=backupdba
oracle.install.db.osdgdba_group=dgdba
oracle.install.db.oskmdba_group=kmdba
oracle.install.db.osracdba_group=dba
#不配置安全更新
decline_security_updates=true
4.6、安装数据库软件
[root@hellodba ~]# su - oracle
[oracle@hellodba ~]$ cd /soft/database
[oracle@hellodba database]$ ./runinstaller -silent -noconfig -ignoreprereq -responsefile /soft/database/response/db_install.rsp
starting oracle universal installer...
checking temp space: must be greater than 500 mb. actual 29658 mb passed
checking swap space: must be greater than 150 mb. actual 3095 mb passed
preparing to launch oracle universal installer from /tmp/orainstall2023-11-01_07-54-32am. please wait ...[oracle@hellodba database]$ you can find the log of this install session at:
/u01/oracle/orainventory/logs/installactions2023-11-01_07-54-32am.log
the installation of oracle database 12c was successful.
please check '/u01/oracle/orainventory/logs/silentinstall2023-11-01_07-54-32am.log' for more details.
as a root user, execute the following script(s):
1. /u01/app/oracle/product/12.2.0/dbhome_1/root.sh
successfully setup software.
4.7、执行配置脚本
[root@hellodba ~]# /u01/app/oracle/product/12.2.0/dbhome_1/root.sh
check /u01/app/oracle/product/12.2.0/dbhome_1/install/root_hellodba_2023-11-01_07-56-38-016845727.log for the output of root script
5、升级前信息检查
[oracle@hellodba ~]$ export oracle_sid=hellodb;
[oracle@hellodba ~]$ export oracle_base=/u01/app/oracle;
[oracle@hellodba ~]$ export oracle_home=$oracle_base/product/11.2.0/dbhome_1
[oracle@hellodba ~]$ cd $oracle_home/bin
[oracle@hellodba bin]$ java -jar /u01/app/oracle/product/12.2.0/dbhome_1/rdbms/admin/preupgrade.jar
preupgrade generated files:
/u01/app/oracle/cfgtoollogs/hellodb/preupgrade/preupgrade.log
/u01/app/oracle/cfgtoollogs/hellodb/preupgrade/preupgrade_fixups.sql
/u01/app/oracle/cfgtoollogs/hellodb/preupgrade/postupgrade_fixups.sql
//日志文件
preupgrade.log
日志文件包含所有工具建议和升级要求。
//升级前修正文件,执行该脚本自动修复来满足升级需求,建议通过上面日志文件提示的内容手动修复。
preupgrade_fixups.sql
//升级后修正文件
postupgrade_fixups.sql
5.1、查看日志
[oracle@hellodba ~]$ more /u01/app/oracle/cfgtoollogs/hellodb/preupgrade/preupgrade.log
report generated by oracle database pre-upgrade information tool version
12.2.0.1.0
upgrade-to version: 12.2.0.1.0
=======================================
status of the database prior to upgrade
=======================================
database name: hellodb
container name: not applicable in pre-12.1 database
container id: not applicable in pre-12.1 database
version: 11.2.0.4.0
compatible: 11.2.0.4.0
blocksize: 8192
platform: linux x86 64-bit
timezone file: 14
database log mode: noarchivelog
readonly: false
edition: ee
oracle component upgrade action current status
---------------- -------------- --------------
oracle server [to be upgraded] valid
jserver java virtual machine [to be upgraded] valid
oracle xdk for java [to be upgraded] valid
oracle workspace manager [to be upgraded] valid
olap analytic workspace [to be upgraded] valid
oracle enterprise manager repository [to be upgraded] valid
oracle text [to be upgraded] valid
oracle xml database [to be upgraded] valid
oracle java packages [to be upgraded] valid
oracle multimedia [to be upgraded] valid
oracle spatial [to be upgraded] valid
expression filter [to be upgraded] valid
rule manager [to be upgraded] valid
oracle application express [to be upgraded] valid
oracle olap api [to be upgraded] valid
==============
before upgrade
==============
run /preupgrade_fixups.sql to complete all
of the before upgrade action items below marked with '(autofixup)'.
required actions
================
adjust tablespace sizes as needed.
auto 12.2.0.1.0
tablespace size extend min size action
---------- ---------- -------- ---------- ------
sysaux 520 mb enabled 1433 mb none
system 750 mb enabled 1251 mb none
temp 29 mb enabled 150 mb none
undotbs1 75 mb enabled 400 mb none
note that 12.2.0.1.0 minimum sizes are estimates.
if you plan to upgrade multiple pluggable databases concurrently,
then you must ensure that the undo tablespace size is equal to at least
the number of pluggable databases that you upgrade concurrently,
multiplied by that minimum. failing to allocate sufficient space can
cause the upgrade to fail.
update numeric initialization parameters to meet estimated minimums.
parameter 12.2.0.1.0 minimum
--------- ------------------
memory_target* 1535115264
processes 300
* these minimum memory/pool sizes are recommended for the upgrade process
recommended actions
===================
remove the em repository.
- copy the $oracle_home/rdbms/admin/emremove.sql script from the target
12.2.0.1.0 oracle_home into the source 11.2.0.4.0 oracle_home.
step 1: if database control is configured, stop em database control,
using the following command
$> emctl stop dbconsole
step 2: connect to the database using the sys account as sysdba
set echo on;
set serveroutput on;
@emremove.sql
without the set echo and serveroutput commands, you will not be able to
follow the progress of the script.
the database has an enterprise manager database control repository.
starting with oracle database 12c, the local enterprise manager database
control does not exist anymore. the repository will be removed from your
database during the upgrade. this step can be manually performed before
the upgrade to reduce downtime.
remove olap catalog by running the 11.2.0.4.0 sql script
$oracle_home/olap/admin/catnoamd.sql script.
the olap catalog component, amd, exists in the database.
starting with oracle database 12c, the olap catalog (olap amd) is
desupported and will be automatically marked as option off during the
database upgrade if present. oracle recommends removing olap catalog
(olap amd) before database upgrade.
directly grant administer database trigger privilege to the owner of the
trigger or drop and re-create the trigger with a user that was granted
directly with such. you can list those triggers using "select owner,
trigger_name from dba_triggers where base_object_type=''database'' and
owner not in (select grantee from dba_sys_privs where
privilege=''administer database trigger'')"
there is one or more database triggers whose owner does not have the
right privilege on the database.
the creation of database triggers must be done by users granted with
administer database trigger privilege. privilege must have been granted
directly.
information only
================
consider upgrading apex manually, before the database upgrade.
the database contains apex version 3.2.1.00.12 and will need to be
upgraded to at least version 5.0.4.00.12.
to reduce database upgrade time, you can upgrade apex manually before
the database upgrade. refer to my oracle support note 1088970.1 for
information on apex installation upgrades.
=============
after upgrade
=============
run /postupgrade_fixups.sql to complete all
of the after upgrade action items below marked with '(autofixup)'.
required actions
================
none
recommended actions
===================
upgrade the database time zone version using the dbms_dst package.
the database is using timezone datafile version 14 and the target
12.2.0.1.0 database ships with timezone datafile version 26.
oracle recommends using the most recent timezone data. for further
information, refer to my oracle support note 1585343.1.
(autofixup) gather dictionary statistics after the upgrade using the
command:
execute dbms_stats.gather_dictionary_stats;
oracle recommends gathering dictionary statistics after upgrade.
dictionary statistics provide essential information to the oracle
optimizer to help it find efficient sql execution plans. after a
database upgrade, statistics need to be re-gathered as there can now be
tables that have significantly changed during the upgrade or new tables
that do not have statistics gathered yet.
gather statistics on fixed objects two weeks after the upgrade using the
command:
execute dbms_stats.gather_fixed_objects_stats;
this recommendation is given for all preupgrade runs.
fixed object statistics provide essential information to the oracle
optimizer to help it find efficient sql execution plans. those
statistics are specific to the oracle database release that generates
them, and can be stale upon database upgrade.
information only
================
check the oracle documentation for the identified components for their
specific upgrade procedure.
the database upgrade script will not upgrade the following oracle
components: olap catalog,owb
the oracle database upgrade script upgrades most, but not all oracle
database components that may be installed. some components that are not
upgraded may have their own upgrade scripts, or they may be deprecated
or obsolete.
5.2、手动修复
//数据文件大小调整
sql> select tablespace_name,file_name from dba_data_files;
tablespace_name file_name
-----------------------------------------------------------------------------------------
users /u01/app/oracle/oradata/hellodb/users01.dbf
undotbs1 /u01/app/oracle/oradata/hellodb/undotbs01.dbf
sysaux /u01/app/oracle/oradata/hellodb/sysaux01.dbf
system /u01/app/oracle/oradata/hellodb/system01.dbf
sql> select tablespace_name,file_name from dba_data_files;
sql> alter database datafile '/u01/app/oracle/oradata/hellodb/sysaux01.dbf' resize 1500m;
sql> alter database datafile '/u01/app/oracle/oradata/hellodb/system01.dbf' resize 1500m;
sql> alter database datafile '/u01/app/oracle/oradata/hellodb/undotbs01.dbf' resize 500m;
//内存调整
sql> show parameter memory_target;
name type value
-----------------------------------------------------------------------------------------
memory_target big integer 1088m
sql> alter system set memory_target=2g scope=spfile;
sql> shutdown immediate;
sql> startup;
sql> show parameter memory_target;
name type value
-----------------------------------------------------------------------------------------
memory_target big integer 2g
//删除olap目录
sql> @$oracle_home/olap/admin/catnoamd.sql
6、升级前验证物化视图刷新是否完成
升级oracle数据库之前,必须等待所有具物化视图都完成刷新。
//确定是否有任何物化视图刷新仍在进行中
sql> select s.obj#,o.obj#,s.containerobj#,lastrefreshdate,pflags,xpflags,o.name,o.owner#,bitand(s.mflags, 8) from obj$ o, sum$ s where o.obj# = s.obj# and o.type# =42 and bitand(s.mflags, 8) = 8;
7、确保升级前没有处于备份模式的文件
升级oracle数据库时,不能使文件处于备份模式。
sql> select * from v$backup where status != 'not active';
8、确保升级前没有文件需要介质恢复
在升级oracle数据库之前,必须确保没有需要介质恢复的文件。
//查看需要介质恢复的文件列表
sql> select * from v$recover_file;
9、升级前清除数据库回收站
在开始oracle数据库的升级过程之前,数据库中的所有用户回收站都必须为空。
sql> purge dba_recyclebin;
1、静默方式下运行dbua
使用命令行选项启动dbua时,dbua将以静默模式运行。在静默模式下,dbua不提供用户界面。dbua将消息(包括信息、错误和警告)写入中日志文件:
oracle_home/cfgtoollogs/dbua/upgrade-timestamp
[oracle@hellodba ~]$ dbua -silent -sid hellodb
logs directory: /u01/app/oracle/cfgtoollogs/dbua/upgrade2023-11-01_06-09-12-am
preupgrade generated files:
/u01/app/oracle/cfgtoollogs/dbua/upgrade2023-11-01_06-09-12-am/hellodb/upgrade.xml
/u01/app/oracle/cfgtoollogs/dbua/upgrade2023-11-01_06-09-12-am/hellodb/preupgrade_fixups.sql
/u01/app/oracle/cfgtoollogs/dbua/upgrade2023-11-01_06-09-12-am/hellodb/postupgrade_fixups.sql
[warning] [dbt-20060] one or more of the pre-upgrade checks on the database have resulted into warning conditions that require manual intervention. it is recommended that you address these warnings as suggested before proceeding.
10% complete
13% complete
21% complete
32% complete
33% complete
33% complete
33% complete
33% complete
34% complete
34% complete
34% complete
34% complete
34% complete
35% complete
35% complete
35% complete
35% complete
36% complete
36% complete
36% complete
36% complete
36% complete
37% complete
37% complete
37% complete
37% complete
38% complete
38% complete
38% complete
38% complete
38% complete
39% complete
39% complete
39% complete
39% complete
40% complete
40% complete
40% complete
40% complete
40% complete
41% complete
41% complete
41% complete
41% complete
41% complete
42% complete
42% complete
42% complete
42% complete
43% complete
43% complete
43% complete
43% complete
43% complete
44% complete
45% complete
56% complete
67% complete
68% complete
69% complete
78% complete
79% complete
89% complete
database upgrade has been completed successfully, and the database is ready to use.
100% complete
1、执行升级后脚本
[oracle@hellodba ~]$ sqlplus / as sysdba
sql> spool postupgrade.log
sql> @/u01/app/oracle/cfgtoollogs/hellodb/preupgrade/postupgrade_fixups.sql
sql> spool off
2、编译失效对象
该脚本将重新编译所有无效对象。升级后立即运行脚本,以确保用户不会遇到无效对象。
[oracle@hellodba ~]$ sqlplus / as sysdba
sql> @$oracle_home/rdbms/admin/utlrp.sql
3、设置 compatible 初始化参数
compatible 参数控制数据库的兼容级别。
sql> show parameter compatible;
name type value
------------------------------------------------------------------------
compatible string 11.2.0.4.0
noncdb_compatible boolean false
sql> alter system set compatible = '12.2.0' scope=spfile;
sql> shutdown immediate;
sql> startup
sql> show parameter compatible;
name type value
------------------------------------------------------------------------
compatible string 12.2.0
noncdb_compatible boolean false
4、启用新的扩展数据类型功能
extended表示 oracle database 12c 中引入的 32767 字节限制适用。
sql> show parameter max_string_size;
name type value
-------------------------------------------------------------------------
max_string_size string standard
sql> shutdown immediate;
sql> startup upgrade;
sql> alter system set max_string_size = extended scope=both;
sql> @/u01/app/oracle/product/12.2.0/dbhome_1/rdbms/admin/utl32k.sql
sql> shutdown immediate;
sql> startup;
sql> @/u01/app/oracle/product/12.2.0/dbhome_1/rdbms/admin/utlrp.sql
sql> show parameter max_string_size;
name type value
-------------------------------------------------------------------------
max_string_size string extended
5、收集统计信息
sql> exec dbms_stats.gather_dictionary_stats; sql> exec dbms_stats.gather_fixed_objects_stats;
6、/etc/oratab 检查
[oracle@hellodba ~]$ cat /etc/oratab
#
# this file is used by oracle utilities. it is created by root.sh
# and updated by either database configuration assistant while creating
# a database or asm configuration assistant while creating asm instance.
# a colon, ':', is used as the field terminator. a new line terminates
# the entry. lines beginning with a pound sign, '#', are comments.
#
# entries are of the form:
# $oracle_sid:$oracle_home:<n|y>:
#
# the first and second fields are the system identifier and home
# directory of the database respectively. the third filed indicates
# to the dbstart utility that the database should , "y", or should not,
# "n", be brought up at system boot time.
#
# multiple entries with the same $oracle_sid are not allowed.
#
#
hellodb:/u01/app/oracle/product/12.2.0/dbhome_1:n
7、监听检查
sql> show parameter listener;
name type value
---------------------------------------------------------------------------------
listener_networks string
local_listener string listener_hellodb
remote_listener string
sql> alter system set local_listener='';
[oracle@hellodba admin]# vi tnsnames.ora
# tnsnames.ora network configuration file: /u01/app/oracle/product/12.2.0/dbhome_1/network/admin/tnsnames.ora
# generated by oracle configuration tools.
hellodb =
(description =
(address = (protocol = tcp)(host = hellodba)(port = 1521))
(connect_data =
(server = dedicated)
(service_name = hellodb)
)
)
[oracle@hellodba admin]# vi listener.ora
# listener.ora network configuration file: /u01/app/oracle/product/12.2.0/dbhome_1/network/admin/listener.ora
# generated by oracle configuration tools.
listener =
(description_list =
(description =
(address = (protocol = tcp)(host = hellodba)(port = 1521))
(address = (protocol = ipc)(key = extproc1521))
)
)
[oracle@hellodba admin]$ lsnrctl stop
[oracle@hellodba admin]$ lsnrctl start
8、连接测试
[oracle@hellodba ~]$ sqlplus sys/oracle@hellodb as sysdba
到此数据库升级就完成了。在升级前,务必备份所有重要的数据,升级数据库是一项重大操作,可能会影响到现有的应用程序和业务流程。在进行升级之前,请先评估应用程序和相关环境的兼容性,并与开发团队和业务方进行充分的沟通和协调。建议先在测试环境中进行测试和验证,以确保升级过程的顺利进行,且不会对业务造成负面影响。
由于本人有限的能力和知识储备,如有错误敬请批评指正!
公众号:hello dba