m6米乐安卓版下载-米乐app官网下载
暂无图片
9
暂无图片
暂无图片
暂无图片

oracle 数据库升级:从11g到12c最佳实践解析 -m6米乐安卓版下载

1260

目录

  今天向大家介绍如何将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升级路线图
image.png

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 源服务器准备升级清单
image.png

3.2、升级后清单核对

在升级后的 oracle 数据库环境中完成这些检查。
表 3-2 目标服务器升级后核对清单
image.png

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
 
扫码_搜索联合传播样式标准色版.png

「喜欢这篇文章,您的关注和赞赏是给作者最好的鼓励」
关注作者
1人已赞赏
【米乐app官网下载的版权声明】本文为墨天轮用户原创内容,转载时必须标注文章的来源(墨天轮),文章链接,文章作者等基本信息,否则作者和墨天轮有权追究责任。如果您发现墨天轮中有涉嫌抄袭或者侵权的内容,欢迎发送邮件至:contact@modb.pro进行举报,并提供相关证据,一经查实,墨天轮将立刻删除相关内容。

文章被以下合辑收录

评论

网站地图