ORACLE 10.2.0.1手动升级到10.2.0.4
1、解压zip包以及查看目录权限
p6810189_10204_Linux-x86-64.zip
oracle@single-10g:/u01>ll
total 1950552
-rw-r—r—. 1 oracle oinstall 801603584 Jan 9 10:35 10201_database_linux_x86_64.cpio
drwxr-xr-x. 3 oracle oinstall 4096 Apr 2 09:48 backup
drwxr-xr-x. 6 oracle oinstall 4096 Oct 23 2005 database
drwxr-xr-x. 5 oracle oinstall 4096 Mar 12 2008 Disk1
drwxr-xr-x. 3 oracle oinstall 4096 Mar 30 15:03 marvin
drwxrwx—-. 6 oracle oinstall 4096 Jan 9 12:10 oracle
-rw-r—r—. 1 root root 1195551830 Apr 2 09:53 p6810189_10204_Linux-x86-64.zip
-rw-r—r—. 1 oracle oinstall 902 Mar 26 11:21 pfile.ora
-rwxr-xr-x. 1 oracle oinstall 171882 Mar 17 2008 README.html
drwxr-xr-x. 3 oracle oinstall 4096 Mar 30 16:27 scripts
2、查看升级前版本信息
版本信息
SQL> select * from v$version;
BANNER
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - 64bi
PL/SQL Release 10.2.0.1.0 - Production
CORE 10.2.0.1.0
Production
TNS for Linux: Version 10.2.0.1.0 - Production
NLSRTL Version 10.2.0.1.0 - Productionhttps://www.cndba.cn/cndba/Marvinn/article/2704
调整下内存参数(share pool和java pool 加快升级速度)值大小根据情况确定,也不用太大
SQL> show parameter sga;
NAME TYPE VALUEhttps://www.cndba.cn/cndba/Marvinn/article/2704
lock_sga boolean FALSE
pre_page_sga boolean FALSE
sga_max_size big integer 492M
sga_target big integer 492M
SQL> select name,bytes/1024/1024 from v$sgainfo;
[java] view plain copy
NAME BYTES/1024/1024
Fixed SGA Size 1.1638031
Redo Buffers 2.8359375
Buffer Cache Size 328
Shared Pool Size 124
Large Pool Size 12
Java Pool Size 24
Streams Pool Size 0
Granule Size 4
Maximum SGA Size 492
Startup overhead in Shared Pool 36
Free SGA Memory Available 0
11 rows selected.
SQL>alter system set shared_pool_size = 150m;
System altered.
SQL> alter system set java_pool_size =100m;
System altered.
3、停止所有数据库服务以及监听
lsnrctl stop
sqlplus / as sysdba
SQL*Plus: Release 10.2.0.1.0 - Production on Mon Apr 2 10:15:18 2018
Copyright (c) 1982, 2005, Oracle. All rights reserved.
Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - 64bit Production
With the Partitioning, OLAP and Data Mining options
SQL> shuttdown immediate;
SP2-0734: unknown command beginning “shuttdown …” - rest of line ignored.
SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
4、冷备份所有数据库
备份数据文件、控制文件以及日志文件
这里可以直接物理备份,采用系统cp命令
oracle@single-10g:/u01/oracle/oradata/ORCL>cp controlfile ORCLBAKUP/
cp: omitting directory `controlfile’
oracle@single-10g:/u01/oracle/oradata/ORCL>cp -r controlfile ORCLBAKUP/
oracle@single-10g:/u01/oracle/oradata/ORCL>ls
controlfile datafile onlinelog ORCLBAKUP
oracle@single-10g:/u01/oracle/oradata/ORCL>cp -r datafile/ ORCLBAKUP/
oracle@single-10g:/u01/oracle/oradata/ORCL>cp -r onlinelog/ ORCLBAKUP/
oracle@single-10g:/u01/oracle/oradata/ORCL/ORCLBAKUP>ll
total 12
drwxr-x—-. 2 oracle oinstall 4096 Apr 2 10:29 controlfile
drwxr-x—-. 2 oracle oinstall 4096 Apr 2 10:30 datafile
drwxr-x—-. 2 oracle oinstall 4096 Apr 2 10:30 onlinelog
或者exp/imp以及数据泵
或者重启库到mount状态,用RMAN全备
当前环境有两个库,测试我只备了一个库
备份spfile以及口令文件
oracle@single-10g:/u01/oracle/oradata/ORCL/ORCLBAKUP>cd $ORACLE_HOME/dbs
oracle@single-10g:/u01/oracle/12.2.0/dbs>mkdir backup
oracle@single-10g:/u01/oracle/12.2.0/dbs>cp spfileorcl.ora orapworcl backup/
oracle@single-10g:/u01/oracle/12.2.0/dbs>
5、配置并运行安装脚本
采用静默安装
oracle@single-10g:/u01/Disk1/response>cp -a patchset.rsp patchset.rsp.bak (-a 表示复制保留原来文件同样的所有属性)
oracle@single-10g:/u01/Disk1/response>ls
patchset.rsp patchset.rsp.bak
编辑文件,修改如下:(未提及的不变动)
安装用户组为oinstall
UNIX_GROUP_NAME=oinstall
升级patch包的路径(以自己机子里面的路径为准)
FROM_LOCATION=/u01/Disk1/stage/products.xml
环境变量中ORACLE_HOME
ORACLE_HOME=/opt/oracle/app/oracle/product/10.2/db_1
ORACLE_HOME的HOME NANME,该值位于oraInventory目录下的ContentsXML
ORACLE_HOME_NAME=OraDb10g_home1
oracle@single-10g:/u01/oracle/oraInventory/ContentsXML>cat inventory.xml
10.2.0.1.0
2.1.0.6.0
https://www.cndba.cn/cndba/Marvinn/article/2704
安装升级软件并指定patchset.rsp使用用这个文件
oracle@single-10g:/u01/Disk1>./runInstaller -silent -responseFile /u01/Disk1/response/patchset.rsp
Starting Oracle Universal Installer…
Checking installer requirements…
Checking operating system version: must be redhat-3, SuSE-9, SuSE-10, redhat-4, redhat-5, UnitedLinux-1.0, asianux-1, asianux-2 or asianux-3
Failed <<<
Exiting Oracle Universal Installer, log for this session can be found at /u01/oracle/oraInventory/logs/installActions2018-04-02_10-51-17AM.log
解决方案:
1、修改Linux的发行标记
如在redhat-5上安装oracle的时候,需要将文件 ‘/etc/redhat-release’的内容由
Red Hat Enterprise Linux Server release 6.6 (Santiago)
修改为
Red Hat Enterprise Linux Server release 4 (Tikanga)
2、runInstaller的时候加上-ignoreSysPreReqs参数,如:
./runInstaller -ignoreSysPreReqs
不过不推荐这种方式,因为这可能会错过对操作系统内核参数以及oracle必需的包的检测
3、修改补丁包中安装文件oraparam.ini(比较推荐)
oracle@single-10g:/u01/Disk1/l>cd Disk1
oracle@single-10g:/u01/Disk1/install>vi oraparam.ini
找到改行文件,添加自身所在系统发行标记
[Certified Versions]
Linux=redhat-3,SuSE-9,SuSE-10,redhat-4,redhat-5,redhat-6.6,UnitedLinux-1.0,asianux-1,asianux-2,asianux-3
重新安装即可…
oracle@single-10g:/u01/Disk1>./runInstaller -silent -responseFile /u01/Disk1/response/patchset.rsp
Starting Oracle Universal Installer…
Checking installer requirements…
Checking operating system version: must be redhat-3, SuSE-9, SuSE-10, redhat-4, redhat-5, redhat-6.6, UnitedLinux-1.0, asianux-1, asianux-2 or asianux-3
Passed
Root script to run
/u01/oracle/12.2.0/root.sh
To execute the configuration scripts:
1. Open a terminal window
2. Log in as "root"
3. Run the scripts
The installation of Oracle Database 10g Release 2 Patch Set 3 was successful.
Please check ‘/u01/oracle/oraInventory/logs/silentInstall2018-04-02_10-56-48AM.log’ for more details.
运行root.sh脚本(一路按y,覆盖原来文件)
[root@single-10g ~]# /u01/oracle/12.2.0/root.sh
Running Oracle10 root.sh script…
The following environment variables are set as:
ORACLE_OWNER= oracle
ORACLE_HOME= /u01/oracle/12.2.0
Enter the full pathname of the local bin directory: [/usr/local/bin]:
The file “dbhome” already exists in /usr/local/bin. Overwrite it? (y/n)
Copying dbhome to /usr/local/bin …
The file “oraenv” already exists in /usr/local/bin. Overwrite it? (y/n)
Copying oraenv to /usr/local/bin …
The file “coraenv” already exists in /usr/local/bin. Overwrite it? (y/n)
Copying coraenv to /usr/local/bin …
Entries will be added to the /etc/oratab file as needed by
Database Configuration Assistant when a database is created
Finished running generic part of root.sh script.
Now product-specific root actions will be performed.
6、手工升级数据库(以Oracle身份)
验证环境变量
当前数据库软件中存在两个数据库,所以都需要启动到升级模式按以下方式跑utlu102i.sql以及catupgrd.sql升级脚本,升级数据字典.以下就跑一个数据库,另一个数据库第6跟第7步骤过程书写忽略(可以同时运行跑)https://www.cndba.cn/cndba/Marvinn/article/2704
由于当前存在两个库,所以都需要升级下数据字典
oracle@single-10g:/home/oracle>echo $ORACLE_HOME
/u01/oracle/12.2.0/
oracle@single-10g:/home/oracle>echo $ORACLE_SID
orcl
oracle@single-10g:/home/oracle>echo $ORACLE_BASE
/u01/oracle
oracle@single-10g:/home/oracle>sqlplus / as sysdba
https://www.cndba.cn/cndba/Marvinn/article/2704
SQL*Plus: Release 10.2.0.4.0 - Production on Mon Apr 2 11:10:21 2018
Copyright (c) 1982, 2007, Oracle. All Rights Reserved.
Connected to an idle instance.
启动到升级模式
SQL> startup upgrade;
ORACLE instance started.
Total System Global Area 520093696 bytes
Fixed Size 2085160 bytes
Variable Size 146804440 bytes
Database Buffers 364904448 bytes
Redo Buffers 6299648 bytes
Database mounted.
Database opened.
设置升级日志
SQL>SPOOL /u01/Disk1/upgrade_info.log
运行升级前信息工具
SQL> @?/rdbms/admin/utlu102i.sql
Oracle Database 10.2 Upgrade Information Utility 04-02-2018 11:13:35
.
Database:
—> name: ORCL
—> version: 10.2.0.1.0
—> compatible: 10.2.0.1.0
—> blocksize: 8192
.
Tablespaces: [make adjustments in the current environment]
—> SYSTEM tablespace is adequate for the upgrade.
…. minimum required size: 500 MB
…. AUTOEXTEND additional space required: 10 MB
—> UNDOTBS1 tablespace is adequate for the upgrade.
…. minimum required size: 400 MB
…. AUTOEXTEND additional space required: 130 MB
—> SYSAUX tablespace is adequate for the upgrade.
…. minimum required size: 367 MB
—> TEMP tablespace is adequate for the upgrade.
…. minimum required size: 58 MB
…. AUTOEXTEND additional space required: 38 MB
.
Update Parameters: [Update Oracle Database 10.2 init.ora or spfile]
— No update parameter changes are required.
.
Renamed Parameters: [Update Oracle Database 10.2 init.ora or spfile]
— No renamed parameters found. No changes are required.
.
Obsolete/Deprecated Parameters: [Update Oracle Database 10.2 init.ora or spfile]
— No obsolete parameters found. No changes are required
.
Components: [The following database components will be upgraded or installed]
—> Oracle Catalog Views [upgrade] VALID
—> Oracle Packages and Types [upgrade] VALID
—> JServer JAVA Virtual Machine [upgrade] VALID
—> Oracle XDK for Java [upgrade] VALID
—> Oracle Java Packages [upgrade] VALID
—> Oracle Text [upgrade] VALID
—> Oracle XML Database [upgrade] VALID
—> Oracle Workspace Manager [upgrade] VALID
—> Oracle Data Mining [upgrade] VALID
—> OLAP Analytic Workspace [upgrade] VALID
—> OLAP Catalog [upgrade] VALID
—> Oracle OLAP API [upgrade] VALID
—> Oracle interMedia [upgrade] VALID
—> Spatial [upgrade] VALID
—> Expression Filter [upgrade] VALID
—> EM Repository [upgrade] VALID
—> Rule Manager [upgrade] VALID
.
Miscellaneous Warnings
WARNING: —> Database contains INVALID objects prior to upgrade.
…. USER PUBLIC has 19 INVALID objects.
.
https://www.cndba.cn/cndba/Marvinn/article/2704
PL/SQL procedure successfully completed.
关闭日志输出
SQL> SPOOL OFF
再运行catupgrd.sql脚本
SQL>SPOOL /u01/Disk1/catinfo.log
SQL> @?/rdbms/admin/catupgrd.sql #时间较长
升级到最后输出内容
Total Upgrade Time: 00:38:49
DOC>#######################################################################
DOC>#######################################################################
DOC>
DOC> The above PL/SQL lists the SERVER components in the upgraded
DOC> database, along with their current version and status.
DOC>
DOC> Please review the status and version columns and look for
DOC> any errors in the spool log file. If there are errors in the spool
DOC> file, or any components are not VALID or not the current version,
DOC> consult the Oracle Database Upgrade Guide for troubleshooting
DOC> recommendations.
DOC>
DOC> Next shutdown immediate, restart for normal operation, and then
DOC> run utlrp.sql to recompile any invalid application objects.
DOC>
DOC>#######################################################################
DOC>#######################################################################
DOC>#
SQL> SQL> https://www.cndba.cn/cndba/Marvinn/article/2704
关闭日志输出
SQL> SPOOL OFF;
··························································································································································
若运行脚本有报如下错误跳出执行(若无可忽略),可按以下解决…
报错1:
No errors.
SELECT version_script AS file_name FROM DUAL
*
ERROR at line 1:
ORA-20000: Upgrade re-run not supported from version
ORA-06512: at “SYS.VERSION_SCRIPT”, line 45
解决方法:
通过以下SQL更新到你要升级到的版本:10.2.0.1升级到10.2.0.4执行catupgrd.sql报错——BUG
update registry$ set prv_version=’10.2.0.4.0’ where cid=’CATPROC’;
quit退出会话,再conn /as sysdba连接到数据库
再次运行, 不会报错
报错2:
ERROR at line 1:
ORA-03113: end-of-file on communication channel
SQL>
SQL> alter system flush shared_pool;
ERROR:
ORA-03114: not connected to ORACLE
报错终止执行解决,重新执行catupgrd脚本
oracle@single-10g:/home/oracle>sqlplus / as sysdba
SQL>update registry$ set prv_version=’10.2.0.4.0’ where cid=’CATPROC’;
SQL> alter system flush shared_pool;
SQL>exit;
oracle@single-10g:/home/oracle> sqlplus / as sysdba
SQL>SPOOL /u01/Disk1/catinfo.log
SQL>@?/rdbms/admin/catupgrd.sql #时间较长,直到出现sql> –升级脚本[刷新数据字典] #版本要是最新版本10.2.0.4.0,status要是valid
SQL>SPOOL OFF;
·········································································································································
检查path.log中的错误,如果有错误重新运行catupgrd.sql,若无错误,关库,正常起库 (#升级运行的日志文件路径/u01/Disk1/catinfo.log)
SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup
ORACLE instance started.
Total System Global Area 520093696 bytes
Fixed Size 2085160 bytes
Variable Size 209719000 bytes
Database Buffers 301989888 bytes
Redo Buffers 6299648 bytes
Database mounted.
Database opened.
https://www.cndba.cn/cndba/Marvinn/article/2704
退出EXIT,重进数据库,重新编译无效对象
oracle@single-10g>sqlplus / as sysdba
SQL*Plus: Release 10.2.0.4.0 - Production on Mon Apr 2 12:01:24 2018
Copyright (c) 1982, 2007, Oracle. All Rights Reserved.
Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL> @?rdbms/admin/utlrp.sql ##耐心等待
7、检查组件的升级情况
SQL> select * from UTL_RECOMP_ERRORS;
no rows selected
SQL>col comp_name format a30;
col version format a20;
col status format a20;
set line 200;
SQL> select comp_name,version,status from sys.dba_registry;
COMP_NAME VERSION STATUS
Oracle Database Catalog Views 10.2.0.4.0 VALID
Oracle Database Packages and T 10.2.0.4.0 VALID
ypes
Oracle Workspace Manager 10.2.0.4.3 VALID
JServer JAVA Virtual Machine 10.2.0.4.0 VALID
Oracle XDK 10.2.0.4.0 VALID
Oracle Database Java Packages 10.2.0.4.0 VALID
Oracle Expression Filter 10.2.0.4.0 VALID
Oracle Data Mining 10.2.0.4.0 VALID
Oracle Text 10.2.0.4.0 VALID
COMP_NAME VERSION STATUS
Oracle XML Database 10.2.0.4.0 VALID
Oracle Rule Manager 10.2.0.4.0 VALID
Oracle interMedia 10.2.0.4.0 VALID
OLAP Analytic Workspace 10.2.0.4.0 VALID
Oracle OLAP API 10.2.0.4.0 VALID
OLAP Catalog 10.2.0.4.0 VALID
Spatial 10.2.0.4.0 VALID
Oracle Enterprise Manager 10.2.0.4.0 VALID
17 rows selected.
显示的值为valid, 表示成功完成升级
SQL> select comp_name,version,status from sys.dba_registry; #版本要是最新版本10.2.0.4.0,status要是valid
COMP_NAME VERSION STATUS
Oracle Database Catalog Views 10.2.0.4.0 VALID
Oracle Database Packages and T 10.2.0.4.0 VALID
ypes
Oracle Workspace Manager 10.2.0.4.3 VALID
JServer JAVA Virtual Machine 10.2.0.4.0 VALID
Oracle XDK 10.2.0.4.0 VALID
Oracle Database Java Packages 10.2.0.4.0 VALID
Oracle Expression Filter 10.2.0.4.0 VALID
Oracle Data Mining 10.2.0.4.0 VALID
Oracle Text 10.2.0.4.0 VALID
COMP_NAME VERSION STATUS
Oracle XML Database 10.2.0.4.0 VALID
Oracle Rule Manager 10.2.0.4.0 VALID
Oracle interMedia 10.2.0.4.0 VALID
OLAP Analytic Workspace 10.2.0.4.0 VALID
Oracle OLAP API 10.2.0.4.0 VALID
OLAP Catalog 10.2.0.4.0 VALID
Spatial 10.2.0.4.0 VALID
Oracle Enterprise Manager 10.2.0.4.0 VALID
17 rows selected.
查看数据,表空间,用户等等
sql>select tablespace_name,status from dba_tablespaces;
sql>select username from dba_user;
sql>select object_name from dba_objects where owner=’marvin’;
sql>select * from marvin,marvin;
检查是否有无效对象
SQL> select count(*) from dba_objects where status=’INVALID’;
COUNT(*)
0
检查是否有无效的对象
SQL> select object_name,status from dba_objects where object_Name in (‘SYS’,’SYSTEM’) and status = ‘INVALID’;
no rows selected
如果值大于0则说明有错误,就要重新执行sql>@d:/oracle/product/10.2.0/db_1/rdbms/admin/catupgrd.sql,知道没有错误
查看版本
SQL> select * from v$version;
BANNER
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bi
PL/SQL Release 10.2.0.4.0 - Production
CORE 10.2.0.4.0 Production
TNS for Linux: Version 10.2.0.4.0 - Production
NLSRTL Version 10.2.0.4.0 - Production
更新EM资料库
oracle@single-10g:/u01/oracle/12.2.0/rdbms/admin>emca -upgrade db
https://www.cndba.cn/cndba/Marvinn/article/2704
STARTED EMCA at Apr 2, 2018 12:10:32 PM
EM Configuration Assistant, Version 10.2.0.1.0 Production
Copyright (c) 2003, 2005, Oracle. All rights reserved.
Enter the following information:
ORACLE_HOME for the database to be upgraded:
ORACLE_HOME for the database to be upgraded: /u01/oracle/12.2.0/
Database SID: orcl
Listener port number: 1521
Do you wish to continue? [yes(Y)/no(N)]: y
Apr 2, 2018 12:11:19 PM oracle.sysman.emcp.EMConfig perform
INFO: This operation is being logged at /u01/oracle/12.2.0/cfgtoollogs/emca/emca_2018-04-02_12-10-32-PM.log.
Apr 2, 2018 12:11:19 PM oracle.sysman.emcp.EMConfig perform
SEVERE: EM is not configured for this database. No EM-specific actions can be performed.
Refer to the log file at /u01/oracle/12.2.0/cfgtoollogs/emca/emca_2018-04-02_12-10-32-PM.log for more details.
Could not complete the configuration. Refer to the log file at /u01/oracle/12.2.0/cfgtoollogs/emca/emca_2018-04-02_12-10-32-PM.log for more details.
收集下数据字典统计信息
SQL> EXECUTE dbms_stats.gather_dictionary_stats;
PL/SQL procedure successfully completed.
修改数据库compatibel
alter system set compatible=’10.2.0.4.0’ scope=spfile;
等到数据库所有都升级完成后,重启监听,对外服务
oracle@single-10g>lsnrctl start
版权声明:本文为博主原创文章,未经博主允许不得转载。