11.2.0.3升级到11.2.0.4 oracle数据库

最新推荐文章于 2022-07-26 19:20:21 发布

cuichuoyu9573

最新推荐文章于 2022-07-26 19:20:21 发布

阅读量298

点赞数

文章标签：数据库 java 网络

一、升级之前的准备工作

1．Execute the Pre-Upgrade Information Tool执行预检查工具

1.1脚本来源于最新的安装包$11g_ORACLE_HOME/rdbms/admin/utlu112i.sql检查

SQL> SPOOL upgrade_info.log

SQL> @$11g_ORACLE_HOME/rdbms/admin/utlu112i.sql

SQL> SPOOL OFF

Check the output of the Pre-Upgrade Information Tool in upgrade_info.log.

........

</Component>

</Component>

</Component>

</Component>

</Component>

</Component>

</Component>

</Component>

</Component>

</Component>

</Component>

</Component>

</Component>

</Component>

<......

2.升级预检查工具的结果处理方法如下：

这个过程注意日志中出现的warning，按照日志下方的The command进行修复，也可以先不处理。

2.1、遇到INVALID objects执行下列的脚本执行以下脚本(对失效对象进行再编译)

SQL> @?/rdbms/admin/utlrp.sql

2.2、清理DBA回收站

SQL> PURGE dba_recyclebin

2.3、检查是否还存在失效对象

SQL> SELECT count(*) FROM dba_invalid_objects;

SQL> SELECT distinct object_name FROM dba_invalid_objects;

2.4兼容的初始化参数COMPATIBLE Initialization Parameter（预检查错误处理一，无责跳过）

The COMPATIBLE initialization parameter needs a special mention here because it has consequences if the database needs to be downgraded. Once the database has been upgraded, the COMPATIBLE parameter has been set to 11.2, and the database has been restarted, then the datafiles, controlfiles and online logfiles are updated to the new version. This in turn will prevent the database from being downgraded in the future. Any attempt to downgrade the database will report an error:

SQL> STARTUP DOWNGRADE;

ORACLE instance started.

Total System Global Area 436207616 bytes

Fixed Size 2029528 bytes

Variable Size 327157800 bytes

Database Buffers 104857600 bytes

Redo Buffers 2162688 bytes

ORA-00201: control file version 11.1.0.0.0 incompatible

with ORACLE version 10.2.0.0.0

ORA-00202: control file: '/u01/oradata/B920/control01.ctl'

When this error occurs the only way to downgrade the database is to restore the database from the backup taken before the database was upgraded or to use any alternate strategies in place like Streams or Export/Import. For further details on planning a fallback strategy, read the 'When to Fallback' section below.

Because of the inability to downgrade the database once it has been opened with the new COMPATIBLE parameter, it is recommended to leave the parameter set to 10.1.0 or 10.2.0 depending on the setting used before the upgrade until the newly upgraded database performance and functionality is acceptable. When

upgrading from Oracle9i Release 2 directly to Oracle Database 11g the minimum setting for COMPATIBLE is 10.1 - so a downgrade from Oracle Database 11g to Oracle9i Release 2 won't be possible. At that time, the COMPATIBLE parameter can be reset to the new, higher version and any new features that require

COMPATIBLE to be 10.1 or higher can begin to be used.

SQL> show parameter compatible

compatible string 11.2.0.0.0

3.查询非默认参数的值

SQL> col name format a30

SQL> col value format a60

SQL> set linesize 130

SQL> set pagesize 2000

SQL> SELECT KSPPINM "Name", KSPFTCTXVL "Value" FROM X$KSPPI A, X$KSPPCV2 B WHERE A.INDX + 1 = KSPFTCTXPN AND KSPFTCTXDF <> 'TRUE' ORDER BY 2;

Name Value

------------------------------ ------------------------------------------------------------

dispatchers (PROTOCOL=TCP) (SERVICE=DBNAMEXDB)

diagnostic_dest /oracle

__oracle_base /oracle

audit_file_dest /oracle/admin/DBNAME/adump

db_recovery_file_dest /oracle/fast_recovery_area

control_files /oracle/fast_recovery_area/DBNAME/control02.ctl

control_files /oracle/oradata/DBNAME/control01.ctl

__shared_io_pool_size 0

__streams_pool_size 0

compatible 11.2.0.0.0

__db_cache_size 1493172224

processes 1500

__large_pool_size 16777216

__java_pool_size 16777216

sessions 2272

__shared_pool_size 2432696320

__pga_aggregate_target 2684354560

open_cursors 300

__sga_target 4009754624

db_recovery_file_dest_size 5218762752

memory_target 6694109184

db_block_size 8192

audit_trail DB

remote_login_passwordfile EXCLUSIVE

undo_tablespace UNDOTBS1

db_name DBNAME

log_archive_dest_1 location=/oracle/oradata/DBNAME/arch

log_archive_format orcl_%t_%s_%r.arc

db_domain

For Grid Infrastructure Installation: Review Environment Variables

Unset Oracle environment variables. If you have ORA_CRS_HOME set as an environment variable, then unset it before starting an installation or upgrade. You should never use ORA_CRS_HOME as an environment variable. If you previously had or currently have an installation on your system and you are

using the same user account to install this installation, then unset the following environment variables: ORA_CRS_HOME; ORACLE_HOME; ORA_NLS10;TNS_ADMIN.

4.备份数据文件、日志文件、控制文件

rman 操作

run

{allocate channel c1 type disk;

allocate channel c2 type disk;

backup filesperset 3 database format '/oracle/bak/full_%d_%T_%s_%p';

sql 'alter system archive log current';

backup archivelog all format '/oracle/bak/arch_%d_%T_%s_%p' ;

backup current controlfile format '/oracle/bak/ctl_%d_%T_%s_%p';

}

5.备份参数文件

mkdir -p bak

create pfile='/oracle/bak/pfile_DBNAME_20181203.ora' from spfile;

6.关闭监听

lsnrctl stop

7.关闭数据库

shutdown immediate;

8.检查是否依然存在oracle进程

ps -ef | grep ora_

9.备份ORACLE目录

Root用户

tar -zcvf full_20180116.tar.gz /oracle

二、升级oracle software

上传文件p13390677_112040_Linux-x86-64_1of7.zip和p13390677_112040_Linux-x86-64_2of7.zip到Oracle目录下，解压后执行

unzip -d /oracle p13390677_112040_Linux-x86-64_1of7.zip

unzip -d /oracle p13390677_112040_Linux-x86-64_2of7.zip

10.执行runInstaller开始升级

用vnc软件操作

cd /oracle/p13390677_112040_Linux-x86-64/database

./runInstaller

..........具体参照图文版数据库安装

第三步选择,Upgrade an existing database

第六步注意oracle_basez正确,选择新创建的Software Location(这里一定新建，直接输入就可）；

安装完成后用root执行脚本

三、升级oracle DB

11 .升级DB

./dbca ------后面基本按照省缺

四、升级后的工作

12.检查监听：lsnrctl status

13.修改环境变量

cd ~

手动修改再生效

vi .bash_profile

14.显示Oracle数据字典的当前状态

SQL> spool /tmp/regInvalid.out

SQL> set echo on

SQL> set lines 80 pages 100

SQL> select substr(comp_id,1,15) comp_id,substr(comp_name,1,30) comp_name,substr(version,1,10) version,status from dba_registry order by modified;

检查无效的对象，和检查之前的数量进行比较，检查是否需要对象重建

SQL> select substr(owner,1,12) owner,substr(object_name,1,30) object,substr(object_type,1,30) type, status from dba_objects where status <> 'VALID'order by owner, type;

SQL> spool off

SQL> set echo off

15．检查无效对象的处理

关闭维护模式，启动应用

SQL> select count(*) from dba_objects where status='INVALID';

15.1、编译整个数据库中的无效对象

SQL>@$ORACLE_HOME/rdbms/admin/utlrp.sql

15.2、编译数据库中单个的无效对象：

编译无效包体： alter package package_name complie;

编译无效的函数：alter function function_name complies;

16.升级数据库后的OPatch工具检查

16.1．Opatch版本检查

cd /oracle/product/11.2.0.4/db_1/OPatch/

16.2查看oracle inventory信息

Upgrading the Recovery Catalog After Upgrading Oracle Database升级Oracle数据库后升级恢复目录

For complete information about upgrading the recovery catalog and the UPGRADE CATALOG command, see Oracle Database Backup and Recovery User's Guide for the topic that describes the procedures.

Upgrading the Time Zone File Version After Upgrading Oracle Database升级Oracle数据库后升级时区文件版本

If the Pre-Upgrade Information Tool instructed you to upgrade the time zone files after completing the database upgrade, then use the DBMS_DSTPL/SQL package to upgrade the time zone file.

Oracle Database supplies multiple versions of time zone files, and there are two types of file associated with each one: a large file, which contains all the time zones defined in the database, and a small file, which contains only the most commonly used time zones. The large versions are designated as timezlrg_version_number.dat, while the small versions are designated as timezone_version_number.dat. The files are located in the oracore/zoneinfo subdirectory under the Oracle Database home directory.

16.3收集数据库统计信息

exec dbms_stats.gather_database_stats;

五、打补丁及后续

17 数据库补丁介绍及安装

Patch 26392168 - Database Patch Set Update 11.2.0.4.171017 (Includes CPUOct2017)

17.1 下载OPatch工具

17.2备份原OPatch工具目录

17.3删除原OPatch目录

上传p6880880_112000_Linux-x86-64.zip文件到OPatch原目录下，并解压

rm -rf OPatch

unzip p6880880_112000_Linux-x86-64.zip

17.4确定升级后的OPatch版本信息

17.5冲突预检查

[oracle@EDBNAME001 26636031]$ /oracle/product/11.2.0.4/db_1/OPatch/opatch prereq CheckConflictAgainstOHWithDetail -ph ./

17.6 关闭所有的实例和监听

中间很多省略…

The following steps load modified SQL files into the database. For an Oracle RAC environment, perform these steps on only one node.

For each database instance running on the Oracle home being patched, connect to the database using SQL*Plus. Connect as SYSDBA and run the catbundle.sql script as follows:

17.7 检查日志报错（一般没有错误）

cd $ORACLE_HOME/rdbms/admin

sqlplus /nolog

SQL> CONNECT / AS SYSDBA

SQL> STARTUP

SQL> @catbundle.sql psu apply

检查日志文件是否有错

SQL> QUIT

The catbundle.sql execution is reflected in the dba_registry_history view by a row associated with bundle series PSU.

---If the OJVM PSU was applied for a previous PSU patch, you may see invalid Java classes after execution of the catbundle.sql script in the previous step. If this is the case, run utlrp.sql to re-validate these Java classes.

cd $ORACLE_HOME/rdbms/admin

sqlplus /nolog

SQL> CONNECT / AS SYSDBA

SQL>@utlrp.sql

Check the following log files in $ORACLE_HOME/cfgtoollogs/catbundle or $ORACLE_BASE/cfgtoollogs/catbundle for any errors:

catbundle_PSU_<database SID>_APPLY_<TIMESTAMP>.log

catbundle_PSU_<database SID>_GENERATE_<TIMESTAMP>.log

18 OJVM补丁安装

18.1 检查及准备：

Ensure that the following Oct2014 or greater to be already installed prior to installing this patch:

Database PSU 11.2.0.4.4 (Oct2014) (Patch number: 19121551)

Database SPU 11.2.0.4 (CPUOct2014)

Database patch for Exadata 11.2.0.4.12 (Oct2014)

You must use the OPatch utility version 11.2.0.3.5 or later to apply this patch. Oracle recommends that you use the latest released OPatch version for 11.2, which is available for download from My Oracle Support patch 6880880 by selecting the 11.2.0.0.0 release.

Ensure that the $PATH definition has the following executables: make, ar, ld and nm. The location of these executables depends on your operating system. On many operating systems, they are located in /usr/ccs/bin.

18.2 检查冲突：

/oracle/product/11.2.0.4/db_1/OPatch/opatch prereq CheckConflictAgainstOHWithDetail -ph ./

如若出现报错，还是检查进程ora

18.3 保证所有的oracle进程服务都是关闭的

Ps -ef|grep ora

Kill -9

18.4 执行OJVM补丁安装

/oracle/product/11.2.0.4/db_1/OPatch/opatch apply

18.5 后续工作：

@/oracle/app/oracle/product/11.2.0/db_1/sqlpatch/28790660/postinstall.sql

set head off

SQL> select * from registry$history order by id;

@?/rdbms/admin/utlrp.sql

set lin 300 pages 5000

col object_name for a40

col owner for a20

select count(*) from dba_objects where status='INVALID';

select count(*) from dba_objects where status='INVALID' and owner='SYS';

select owner,object_name,object_type,status from dba_objects where status='INVALID';

5.7、检查数据字典中补丁信息

set lines 500 pages 500

col description for a75

col action_time for a35

col action for a10

col comments for a50

col VERSION for a25

col NAMESPACE for a20

col BUNDLE_SERIES for a20

select * from registry$history;

3. Follow directions in My Oracle Support Document 461082.1: Do I Need To Run catcpu.sql After Upgrading A Database?

4. Adjust time zone data in the database to DST V14 or higher. For more information see the Oracle Database Globalization Support Guide.

5. Gather system statistics during a regular workload period.

SQL>execute dbms_stats.gather_system_stats('start');

<<Run for several hours during a regular workload period.>>

SQL>execute dbms_stats.gather_system_stats('stop');

Refer to the Performance Tuning Guide for a listing of all stats.

6. Create fixed table statistics immediately after catupgrd.sql has completed:

SQL> execute dbms_stats.gather_fixed_objects_stats;

NOTE: This displays a recommendation to remove all hidden or underscore parameters and events from init.ora/spfile.

19 修改NBU客户端配置文件

20 启动监听（完）

lsnrctl start

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/69924428/viewspace-2644778/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/69924428/viewspace-2644778/

cuichuoyu9573

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
11.2.0.3升级到11.2.0.4 oracle数据库

一、升级之前的准备工作1．Execute the Pre-Upgrade Information Tool执行预检查工具1.1脚本来源于最新的安装包$11g_O...
复制链接

扫一扫