oracle版本升级11.2.0.3升级11.2.0.4
大体步骤：html
1，核实数据库和操做系统相关信息 node
- 检查升级条件
- 安装12c数据库软件
- 执行dbua升级db或者采用手工执行命令的方式，但根据官方资料看，建议放弃这个想法，DBUA 真的简化了不少，若是手工执行，会多不少步骤，这样会增长出错的几率。（这里用的手工命令）
- 检查数据库状态和无效对象
[oracle@zg3 soft]$ uname -a Linux zg3 2.6.32-431.el6.x86_64 #1 SMP Sun Nov 10 22:19:54 EST 2013 x86_64 x86_64 x86_64 GNU/Linux [oracle@zg3 soft]$ sqlplus / as sysdba SQL*Plus: Release 11.2.0.3.0 Production on Sat Sep 15 16:08:44 2018 Copyright (c) 1982, 2011, Oracle. All rights reserved. Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production With the Partitioning, OLAP, Data Mining and Real Application Testing options SQL> select name from v$database; NAME --------- ORCL SQL> select * from v$version; BANNER ---------------------------------------------------------------------- Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production PL/SQL Release 11.2.0.3.0 - Production CORE 11.2.0.3.0 Production TNS for Linux: Version 11.2.0.3.0 - Production NLSRTL Version 11.2.0.3.0 - Production SQL> col comp_name for a35 SQL> col version for a20 SQL> col name for a20 SQL> set linesize 1000 SQL> set pagesize 1000 SQL> SELECT COMP_NAME, VERSION, STATUS FROM SYS.DBA_REGISTRY; COMP_NAME VERSION STATUS ------------------------------ -------------------- ---------------------- OWB 11.2.0.3.0 VALID Oracle Application Express 3.2.1.00.12 VALID Oracle Enterprise Manager 11.2.0.3.0 VALID OLAP Catalog 11.2.0.3.0 VALID Spatial 11.2.0.3.0 VALID Oracle Multimedia 11.2.0.3.0 VALID Oracle XML Database 11.2.0.3.0 VALID Oracle Text 11.2.0.3.0 VALID Oracle Expression Filter 11.2.0.3.0 VALID Oracle Rules Manager 11.2.0.3.0 VALID Oracle Workspace Manager 11.2.0.3.0 VALID Oracle Database Catalog Views 11.2.0.3.0 VALID Oracle Database Packages and Types 11.2.0.3.0 VALID JServer JAVA Virtual Machine 11.2.0.3.0 VALID Oracle XDK 11.2.0.3.0 VALID Oracle Database Java Packages 11.2.0.3.0 VALID OLAP Analytic Workspace 11.2.0.3.0 VALID Oracle OLAP API 11.2.0.3.0 VALID 18 rows selected. SQL> SELECT name, value FROM v$parameter WHERE name = 'compatible';
2， rman对数据库执行全备sql
[oracle@zg3 ~]$ rman target / Recovery Manager: Release 11.2.0.3.0 - Production on Sat Sep 15 16:34:01 2018 Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved. connected to target database: ORCL (DBID=1514683624) RMAN> backup database plus archivelog delete input format '/oracle/back/full_%U.dbf'; …………………………
3，关闭数据库和监听数据库
SQL> shutdown immediate; Database closed. Database dismounted. ORACLE instance shut down. SQL> exit Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production With the Partitioning, OLAP, Data Mining and Real Application Testing options [oracle@zg3 ~]$ lsnrctl stop LSNRCTL for Linux: Version 11.2.0.3.0 - Production on 15-SEP-2018 16:40:45 Copyright (c) 1991, 2011, Oracle. All rights reserved. Connecting to (ADDRESS=(PROTOCOL=tcp)(HOST=)(PORT=1521)) The command completed successfully [oracle@zg3 ~]$ ps -ef |grep ora
4，备份ORACLE_HOME和oraInventorybash
tar –cvf oraInventory.zip /oracle/app/oraInventory/ tar –cvf product.zip /oracle/app/oracle/product/
5，上传并解压11.2.0.4安装包oracle
[root@zg3 oracle]# cd database/ [root@zg3 database]# ls install readme.html response rpm runInstaller sshsetup stage welcome.html
6，开始安装11.2.0.4软件app
这里替换了软件目录ssh
[root@zg3 ~]# /oracle/app/oraInventory2/orainstRoot.sh
Changing permissions of /oracle/app/oraInventory2.
Adding read,write permissions for group.
Removing read,write,execute permissions for world.
Changing groupname of /oracle/app/oraInventory2 to oinstall.
The execution of the script is complete.
[root@zg3 ~]# /oracle/app/oracle/product/11.2.0/db_2/root.sh
Performing root user operation for Oracle 11g
The following environment variables are set as:
ORACLE_OWNER= oracle
ORACLE_HOME= /oracle/app/oracle/product/11.2.0/db_2
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)
[n]: y
Copying dbhome to /usr/local/bin ...
The contents of "oraenv" have not changed. No need to overwrite.
The contents of "coraenv" have not changed. No need to overwrite.
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 script.
Now product-specific root actions will be performed.
Finished product-specific root actions.
（监听配置这一步根据具体状况，能够选择直接将原版本的监听文件拷贝过来）tcp
到这里11.2.0.4的软件就已经安装完成，可是还须要进行后续的升级操做。监听配置完会自动跳转下面的界面，这里直接关闭，跳过报错，手动进行后续的操做。ide
修改环境变量：
[root@zg3 ~]# su - oracle [oracle@zg3 ~]$ vi .bash_profile export ORACLE_HOME=$ORACLE_BASE/product/11.2.0/db_2 --改为新的目录 [oracle@zg3 ~]$ source ~/.bash_profile [oracle@zg3 ~]$ echo $ORACLE_HOME /oracle/app/oracle/product/11.2.0/db_2 [oracle@zg3 ~]$ vi /etc/oratab orcl:/oracle/app/oracle/product/11.2.0/db_2:N --改为新的目录
拷贝监听文件和参数文件：
[oracle@zg3 ~]$ cp /oracle/app/oracle/product/11.2.0/db_1/network/admin/* /oracle/app/oracle/product/11.2.0/db_2/network/admin/ [oracle@zg3 ~]$ cp /oracle/app/oracle/product/11.2.0/db_1/dbs/* /oracle/app/oracle/product/11.2.0/db_2/dbs/
执行预升级脚本检查
[oracle@zg3 ~]$ sqlplus / as sysdba
SQL*Plus: Release 11.2.0.4.0 Production on Sat Sep 15 17:54:47 2018
Copyright (c) 1982, 2013, Oracle. All rights reserved.
Connected to an idle instance.
SQL> startup upgrade;
ORACLE instance started.
Total System Global Area 4375998464 bytes
Fixed Size 2260328 bytes
Variable Size 956301976 bytes
Database Buffers 3405774848 bytes
Redo Buffers 11661312 bytes
Database mounted.
Database opened.
SQL> @?/rdbms/admin/utlu112i.sql --执行升级检查
Oracle Database 11.2 Pre-Upgrade Information Tool 09-15-2018 17:56:45
Script Version: 11.2.0.4.0 Build: 001
.
**********************************************************************
Database:
**********************************************************************
--> name: ORCL
--> version: 11.2.0.3.0
--> compatible: 11.2.0.0.0
--> blocksize: 8192
--> platform: Linux x86 64-bit
--> timezone file: V14
.
**********************************************************************
Tablespaces: [make adjustments in the current environment]
**********************************************************************
--> SYSTEM tablespace is adequate for the upgrade.
.... minimum required size: 917 MB
--> SYSAUX tablespace is adequate for the upgrade.
.... minimum required size: 624 MB
--> UNDOTBS1 tablespace is adequate for the upgrade.
.... minimum required size: 400 MB
--> TEMP tablespace is adequate for the upgrade.
.... minimum required size: 60 MB
.
**********************************************************************
Flashback: OFF
**********************************************************************
**********************************************************************
Update Parameters: [Update Oracle Database 11.2 init.ora or spfile]
Note: Pre-upgrade tool was run on a lower version 64-bit database.
**********************************************************************
--> If Target Oracle is 32-Bit, refer here for Update Parameters:
-- No update parameter changes are required.
.
--> If Target Oracle is 64-Bit, refer here for Update Parameters:
-- No update parameter changes are required.
.
**********************************************************************
Renamed Parameters: [Update Oracle Database 11.2 init.ora or spfile]
**********************************************************************
-- No renamed parameters found. No changes are required.
.
**********************************************************************
Obsolete/Deprecated Parameters: [Update Oracle Database 11.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 Workspace Manager [upgrade] VALID
--> OLAP Analytic Workspace [upgrade] VALID
--> OLAP Catalog [upgrade] VALID
--> EM Repository [upgrade] VALID
--> Oracle Text [upgrade] VALID
--> Oracle XML Database [upgrade] VALID
--> Oracle Java Packages [upgrade] VALID
--> Oracle interMedia [upgrade] VALID
--> Spatial [upgrade] VALID
--> Expression Filter [upgrade] VALID
--> Rule Manager [upgrade] VALID
--> Oracle Application Express [upgrade] VALID
... APEX will only be upgraded if the version of APEX in
... the target Oracle home is higher than the current one.
--> Oracle OLAP API [upgrade] VALID
.
**********************************************************************
Miscellaneous Warnings
**********************************************************************
WARNING: --> Your recycle bin is turned on and currently contains no objects.
.... Because it is REQUIRED that the recycle bin be empty prior to upgrading
.... and your recycle bin is turned on, you may need to execute the command:
PURGE DBA_RECYCLEBIN
.... prior to executing your upgrade to confirm the recycle bin is empty.
WARNING: --> Database contains schemas with objects dependent on DBMS_LDAP package.
.... Refer to the 11g Upgrade Guide for instructions to configure Network ACLs.
.... USER APEX_030200 has dependent objects.
.
**********************************************************************
Recommendations
**********************************************************************
Oracle recommends gathering dictionary statistics prior to
upgrading the database.
To gather dictionary statistics execute the following command
while connected as SYSDBA:
EXECUTE dbms_stats.gather_dictionary_stats;
**********************************************************************
Oracle recommends removing all hidden parameters prior to upgrading.
To view existing hidden parameters execute the following command
while connected AS SYSDBA:
SELECT name,description from SYS.V$PARAMETER WHERE name
LIKE '\_%' ESCAPE '\'
Changes will need to be made in the init.ora or spfile.
**********************************************************************
Oracle recommends reviewing any defined events prior to upgrading.
To view existing non-default events execute the following commands
while connected AS SYSDBA:
Events:
SELECT (translate(value,chr(13)||chr(10),' ')) FROM sys.v$parameter2
WHERE UPPER(name) ='EVENT' AND isdefault='FALSE'
Trace Events:
SELECT (translate(value,chr(13)||chr(10),' ')) from sys.v$parameter2
WHERE UPPER(name) = '_TRACE_EVENTS' AND isdefault='FALSE'
Changes will need to be made in the init.ora or spfile.
升级以前执行 EXECUTE dbms_stats.gather_dictionary_stats;收集统计信息，缩短升级时间。并且须要清空回收站PURGE DBA_RECYCLEBIN;
开启闪回，手动建立还原点
SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup mount;
ORACLE instance started.
Total System Global Area 4375998464 bytes
Fixed Size 2260328 bytes
Variable Size 956301976 bytes
Database Buffers 3405774848 bytes
Redo Buffers 11661312 bytes
Database mounted.
SQL>alter database flashback on;
SQL> create restore point up_rollback guarantee flashback database;
SQL> select * from v$restore_point;
1048597 2 YES 52428800
15-SEP-18 06.07.00.000000000 PM
YES
UP_ROLLBACK
SQL> shutdown immediate;
SQL> startup upgrade;
SQL>@?/rdbms/admin/utlu112i.sql --再次运行检查
执行升级操做：
SQL> set echo on SQL> spool /oracle/upgrade.log SQL> set time on 18:12:02 SQL> @?/rdbms/admin/catupgrd.sql ………………
运行utlrp.sql编译失效对象
SQL> startup
ORACLE instance started.
Total System Global Area 4375998464 bytes
Fixed Size 2260328 bytes
Variable Size 1023410840 bytes
Database Buffers 3338665984 bytes
Redo Buffers 11661312 bytes
Database mounted.
Database opened.
SQL> @?/rdbms/admin/utlrp
TIMESTAMP
--------------------------------------------------------------------------------
COMP_TIMESTAMP UTLRP_BGN 2018-09-15 18:46:01
DOC> The following PL/SQL block invokes UTL_RECOMP to recompile invalid
DOC> objects in the database. Recompilation time is proportional to the
DOC> number of invalid objects in the database, so this command may take
DOC> a long time to execute on a database with a large number of invalid
DOC> objects.
DOC>
DOC> Use the following queries to track recompilation progress:
DOC>
DOC> 1. Query returning the number of invalid objects remaining. This
DOC> number should decrease with time.
DOC> SELECT COUNT(*) FROM obj$ WHERE status IN (4, 5, 6);
DOC>
DOC> 2. Query returning the number of objects compiled so far. This number
DOC> should increase with time.
DOC> SELECT COUNT(*) FROM UTL_RECOMP_COMPILED;
DOC>
DOC> This script automatically chooses serial or parallel recompilation
DOC> based on the number of CPUs available (parameter cpu_count) multiplied
DOC> by the number of threads per CPU (parameter parallel_threads_per_cpu).
DOC> On RAC, this number is added across all RAC nodes.
DOC>
DOC> UTL_RECOMP uses DBMS_SCHEDULER to create jobs for parallel
DOC> recompilation. Jobs are created without instance affinity so that they
DOC> can migrate across RAC nodes. Use the following queries to verify
DOC> whether UTL_RECOMP jobs are being created and run correctly:
DOC>
DOC> 1. Query showing jobs created by UTL_RECOMP
DOC> SELECT job_name FROM dba_scheduler_jobs
DOC> WHERE job_name like 'UTL_RECOMP_SLAVE_%';
DOC>
DOC> 2. Query showing UTL_RECOMP jobs that are running
DOC> SELECT job_name FROM dba_scheduler_running_jobs
DOC> WHERE job_name like 'UTL_RECOMP_SLAVE_%';
DOC>#
PL/SQL procedure successfully completed.
TIMESTAMP
--------------------------------------------------------------------------------
COMP_TIMESTAMP UTLRP_END 2018-09-15 18:46:43
DOC> The following query reports the number of objects that have compiled
DOC> with errors.
DOC>
DOC> If the number is higher than expected, please examine the error
DOC> messages reported with each object (using SHOW ERRORS) to see if they
DOC> point to system misconfiguration or resource constraints that must be
DOC> fixed before attempting to recompile these objects.
DOC>#
OBJECTS WITH ERRORS
-------------------
0
DOC> The following query reports the number of errors caught during
DOC> recompilation. If this number is non-zero, please query the error
DOC> messages in the table UTL_RECOMP_ERRORS to see if any of these errors
DOC> are due to misconfiguration or resource constraints that must be
DOC> fixed before objects can compile successfully.
DOC>#
ERRORS DURING RECOMPILATION
---------------------------
0
Function created.
PL/SQL procedure successfully completed.
Function dropped.
PL/SQL procedure successfully completed.
至此数据库已经升级完成，查看各组件版本号：
SQL> SELECT COMP_NAME, VERSION, STATUS FROM SYS.DBA_REGISTRY; COMP_NAME VERSION STATUS ------------------------------ -------------------- ---------------------- OWB 11.2.0.3.0 VALID Oracle Application Express 3.2.1.00.12 VALID Oracle Enterprise Manager 11.2.0.4.0 VALID OLAP Catalog 11.2.0.4.0 VALID Spatial 11.2.0.4.0 VALID Oracle Multimedia 11.2.0.4.0 VALID Oracle XML Database 11.2.0.4.0 VALID Oracle Text 11.2.0.4.0 VALID Oracle Expression Filter 11.2.0.4.0 VALID Oracle Rules Manager 11.2.0.4.0 VALID Oracle Workspace Manager 11.2.0.4.0 VALID Oracle Database Catalog Views 11.2.0.4.0 VALID Oracle Database Packages and Types 11.2.0.4.0 VALID JServer JAVA Virtual Machine 11.2.0.4.0 VALID Oracle XDK 11.2.0.4.0 VALID Oracle Database Java Packages 11.2.0.4.0 VALID OLAP Analytic Workspace 11.2.0.4.0 VALID Oracle OLAP API 11.2.0.4.0 VALID 18 rows selected.
处理OWB版本问题：
SQL> @?/owb/UnifiedRepos/clean_owbsys.sql SQL> @?/owb/UnifiedRepos/cat_owb.sql SQL> @?/owb/UnifiedRepos/reset_owbcc_home.sql SQL> SELECT COMP_NAME, VERSION, STATUS FROM SYS.DBA_REGISTRY; COMP_NAME VERSION STATUS ----------------------------------- -------------------- ---------------------- OWB 11.2.0.4.0 VALID Oracle Application Express 3.2.1.00.12 VALID Oracle Enterprise Manager 11.2.0.4.0 VALID OLAP Catalog 11.2.0.4.0 VALID Spatial 11.2.0.4.0 VALID Oracle Multimedia 11.2.0.4.0 VALID Oracle XML Database 11.2.0.4.0 VALID Oracle Text 11.2.0.4.0 VALID Oracle Expression Filter 11.2.0.4.0 VALID Oracle Rules Manager 11.2.0.4.0 VALID Oracle Workspace Manager 11.2.0.4.0 VALID Oracle Database Catalog Views 11.2.0.4.0 VALID Oracle Database Packages and Types 11.2.0.4.0 VALID JServer JAVA Virtual Machine 11.2.0.4.0 VALID Oracle XDK 11.2.0.4.0 VALID Oracle Database Java Packages 11.2.0.4.0 VALID OLAP Analytic Workspace 11.2.0.4.0 VALID Oracle OLAP API 11.2.0.4.0 VALID 18 rows selected.
检查无效对象
SQL> select * from dba_objects where status<>'VALID'; no rows selected
升级成功后删除原来的目录，经过EMCA重建EM （不用EM可忽略）
[oracle@zg3 app]$ ls oracle oraInventory oraInventory2 [oracle@zg3 app]$ pwd /oracle/app [oracle@zg3 app]$ rm -rf oraInventory [oracle@zg3 11.2.0]$ pwd /oracle/app/oracle/product/11.2.0 [oracle@zg3 11.2.0]$ rm -rf db_1
删除restore point
SQL> select * from v$restore_point;
SCN DATABASE_INCARNATION# GUA STORAGE_SIZE TIME RESTORE_POINT_TIME PRE NAME
---------- --------------------- --- ------------ --------------------------------------------------------------------------- --------------------------------------------------------------------------- --- --------------------
1048597 2 YES 629145600 15-SEP-18 06.07.00.000000000 PM YES UP_ROLLBACK
SQL> drop restore point up_rollback;
Restore point dropped.
SQL> select * from v$restore_point;
no rows selected