CNDBA社区
1 升级说明
12cR2也是今年官方正式放出来，由于众多非常好的新特性，所以很多用户也想将自己的数据库升级/迁移到12CR2中。但是无法从10G直接升级到12cR2,必须通过多次升级才行。升级具体步骤和之前升级数据库没有什么区别，只要做好前提检查就行。
大概方法：
数据库升级助手 （DBUA） --最简单
手动升级 （基于脚本） --最容易出错
2 直接升级到12c的路线
只支持从下面的数据库版本直接升级到12cR1，R2。其他数据库版本无法直接升级，需要经常多次升级才行或迁移。
| Target Upgrade-To Version |
Upgrading From Version |
| 12cR2 (12.2.0.1) |
12.1.0.2 12.1.0.1 11.2.0.4 11.2.0.3 已经不支持10G直接升级到12C R2了。 |
| 12cR1 (12.1.0.2) |
12.1.0.1 11.2.0.4 11.2.0.3 11.2.0.2 11.1.0.7 10.2.0.5 |
| 12cR1 (12.1.0.1) |
11.2.0.4 11.2.0.3 11.2.0.2 11.1.0.7 10.2.0.5 |
3 检查数据库状态
官方文档：
http://docs.oracle.com/database/121/UPGRD/preup.htm#UPGRD12356
总结：上面的表格里都有说到
1. 升级前一定要备份数据库:最好冷备
2. 所有的对象必须都是有效的(如果有无效对象需运行$ORACLE_HOME/rdbms/admin/utlrp.sql)
3. SYS和SYSTEM用户下没有重复的对象
4. 禁用所有用户的触发器
5. Bug 20369415 - UPGRADE TO 12C FAILS - XDB ERROR ORA-1830 ORA-6512: AT "SYS.XS_OBJECT_MIGRATION"。（是从11.2.0.x升级到12.1.0.x）
4 新环境准备
注意：如果是自己测试的，要给大点内存。4G起步
4.1 安装12C R1软件
注意：只安装软件，不要装数据库。并且，ORACLE_HOME不能相同。
4.1.1 创建新ORACLE_HOME
[oracle@db10 ~]$ echo $ORACLE_HOME --源库ORACLE_HOME
/u01/app/oracle/product/10.2.0/db_1
[oracle@db10 ~]$ mkdir -p /u01/app/oracle/product/12.1.0/db_1 --目标ORACLE_HOME
[oracle@db10 ~]$4.1.2 修改系统参数
红色部分为修改/新增
fs.file-max = 65536 -> 6815744
net.ipv4.ip_local_port_range =1024 65000 -> 9000 65000
net.core.rmem_max=262144 -> 4194304
net.core.wmem_default=262144 -> 1048576
net.core.wmem_max=262144 -> 1048576
fs.aio-max-nr=10485764.1.3 解压文件，安装软件
$./runInstaller
最后执行一个root.sh脚本了事。
4.2 源库创建测试数据
SQL> create user cndba identified by cndba;
User created.
SQL> create table cndba.test as select * from dba_tables;
create table cndba.test as select * from dba_tables
*
ERROR at line 1:
ORA-01950: no privileges on tablespace 'USERS'
SQL> grant resource,connect,dba to cndba;
Grant succeeded.
SQL> create table cndba.test as select * from dba_tables;
Table created.
SQL> select count(*) from cndba.test;
COUNT(*)
----------
15454.3 检查无效对象
SQL> set pagesize500
set linesize 100
select substr(comp_name,1,40) comp_name, status, substr(version,1,10) version from dba_registry order by comp_name;SQL> SQL>
COMP_NAME STATUS VERSION
---------------------------------------- ----------- ----------
JServer JAVA Virtual Machine VALID 10.2.0.5.0
OLAP Analytic Workspace VALID 10.2.0.5.0
OLAP Catalog VALID 10.2.0.5.0
Oracle Data Mining VALID 10.2.0.5.0
Oracle Database Catalog Views VALID 10.2.0.5.0
Oracle Database Java Packages VALID 10.2.0.5.0
Oracle Database Packages and Types VALID 10.2.0.5.0
Oracle Enterprise Manager VALID 10.2.0.5.0
Oracle Expression Filter VALID 10.2.0.5.0
Oracle OLAP API VALID 10.2.0.5.0
Oracle Rule Manager VALID 10.2.0.5.0
Oracle Text VALID 10.2.0.5.0
Oracle Workspace Manager VALID 10.2.0.5.0
Oracle XDK VALID 10.2.0.5.0
Oracle XML Database VALID 10.2.0.5.0
Oracle interMedia VALID 10.2.0.5.0
Spatial VALID 10.2.0.5.0
17 rows selected.---SYS,SYSTEM用户下
SQL> select owner,object_type,count(*) from dba_objects where status='INVALID' group by owner,object_type order by owner,object_type ;
no rows selected
4.4 用于检测环境是否满足升级的脚本
MOS上提供了脚本用于检测源数据库是否满足升级的条件，非常方便。MOS文档How to Download and Run Oracle's Database Pre-Upgrade Utility (文档 ID 884522.1)，当然安装完成后，新的ORACLE_HOME下面也会有相应的脚本。
4.4.1 拷贝脚本到临时文件夹(也可以不用拷贝)
[oracle@db10 ~]$ cd /software/
[oracle@db10 software]$ cp /u01/app/oracle/product/12.1.0/db_1/rdbms/admin/preupgrd.sql .
[oracle@db10 software]$ cp /u01/app/oracle/product/12.1.0/db_1/rdbms/admin/utluppkg.sql .
4.4.2 执行sql检查
SQL> @/software/preupgrd.sql
Loading Pre-Upgrade Package...
***************************************************************************
Executing Pre-Upgrade Checks in CNDBA...
***************************************************************************
************************************************************
====>> ERRORS FOUND for CNDBA <<====
The following are *** ERROR LEVEL CONDITIONS *** that must be addressed
prior to attempting your upgrade.
Failure to do so will result in a failed upgrade.
1) Check Tag: COMPATIBLE_PARAMETER
Check Summary: Verify compatible parameter value is valid
Fixup Summary:
""compatible" parameter must be increased manually prior to upgrade."
+++ Source Database Manual Action Required +++
2) Check Tag: PURGE_RECYCLEBIN
Check Summary: Check that recycle bin is empty prior to upgrade
Fixup Summary:
"The recycle bin will be purged."
You MUST resolve the above errors prior to upgrade
************************************************************
************************************************************
====>> PRE-UPGRADE RESULTS for CNDBA <<====
ACTIONS REQUIRED:
1. Review results of the pre-upgrade checks:
/u01/app/oracle/cfgtoollogs/cndba/preupgrade/preupgrade.log
2. Execute in the SOURCE environment BEFORE upgrade:
/u01/app/oracle/cfgtoollogs/cndba/preupgrade/preupgrade_fixups.sql
3. Execute in the NEW environment AFTER upgrade:
/u01/app/oracle/cfgtoollogs/cndba/preupgrade/postupgrade_fixups.sql
************************************************************
***************************************************************************
Pre-Upgrade Checks in CNDBA Completed.
***************************************************************************
***************************************************************************
根据提示运行preupgrade_fixups.sql脚本
红色部分都是有问题的，需要处理掉
SQL> @/u01/app/oracle/cfgtoollogs/cndba/preupgrade/preupgrade_fixups.sql
Pre-Upgrade Fixup Script Generated on 2017-07-27 16:59:38 Version: 12.1.0.2 Build: 006
Beginning Pre-Upgrade Fixups...
Executing in container CNDBA
**********************************************************************
Check Tag: DEFAULT_PROCESS_COUNT
Check Summary: Verify min process count is not too low
Fix Summary: Review and increase if needed, your PROCESSES value.
**********************************************************************
Fixup Returned Information:
WARNING: --> Process Count may be too low
Database has a maximum process count of 150 which is lower than the
default value of 300 for this release.
You should update your processes value prior to the upgrade
to a value of at least 300.
For example:
ALTER SYSTEM SET PROCESSES=300 SCOPE=SPFILE
or update your init.ora file.
**********************************************************************
**********************************************************************
Check Tag: COMPATIBLE_PARAMETER
Check Summary: Verify compatible parameter value is valid
Fix Summary: "compatible" parameter must be increased manually prior to upgrade.
**********************************************************************
Fixup Returned Information:
ERROR: --> Compatible set too low
"compatible" currently set at 10.2.0.1.0 and must
be set to at least 11.0.0 prior to upgrading the database.
Do not make this change until you are ready to upgrade
because a downgrade back to 10.2 is not possible once compatible
has been raised.
Update your init.ora or spfile to make this change.
**********************************************************************
**********************************************************************
Check Tag: OCM_USER_PRESENT
Check Summary: Check for OCM schema
Fix Summary: Drop the ORACLE_OCM user.
**********************************************************************
Fixup Succeeded
**********************************************************************
**********************************************************************
Check Tag: EM_PRESENT
Check Summary: Check if Enterprise Manager is present
Fix Summary: Execute emremove.sql prior to upgrade.
**********************************************************************
Fixup Returned Information:
WARNING: --> Enterprise Manager Database Control repository found in the database
In Oracle Database 12c, Database Control is removed during
the upgrade. To save time during the Upgrade, this action
can be done prior to upgrading using the following steps after
copying rdbms/admin/emremove.sql from the new Oracle home
- Stop EM Database Control:
$> emctl stop dbconsole
- 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.
**********************************************************************
**********************************************************************
Check Tag: REMOVE_DMSYS
Check Summary: Check for existance of DMSYS schema
Fix Summary: The DMSYS schema is removed as part of the upgrade.
**********************************************************************
Fixup Returned Information:
WARNING: --> "DMSYS" schema exists in the database
The DMSYS schema (Oracle Data Mining) will be removed
from the database during the database upgrade.
All data in DMSYS will be preserved under the SYS schema.
Refer to the Oracle Data Mining User's Guide for details.
**********************************************************************
**********************************************************************
Check Tag: AMD_EXISTS
Check Summary: Check to see if AMD is present in the database
Fix Summary: Manually execute ORACLE_HOME/oraolap/admin/catnoamd.sql script to remove OLAP.
**********************************************************************
Fixup Returned Information:
INFORMATION: --> OLAP Catalog(AMD) exists in database Starting with Oracle Database 12c, OLAP Catalog component is desupported.
If you are not using the OLAP Catalog component and want
to remove it, then execute the
ORACLE_HOME/olap/admin/catnoamd.sql script before or
after the upgrade.
**********************************************************************
**********************************************************************
Check Tag: PURGE_RECYCLEBIN
Check Summary: Check that recycle bin is empty prior to upgrade
Fix Summary: The recycle bin will be purged.
**********************************************************************
Fixup Succeeded
**********************************************************************
**********************************************************************
Check Tag: NACL_OBJECTS_EXIST
Check Summary: Check for Network ACL Objects in use
Fix Summary: Objects with network acls are displayed and need to be reviewed.
**********************************************************************
Fixup Returned Information:
WARNING: --> Existing schemas with network ACLs exist
Database contains schemas with objects dependent on network packages.
Refer to the Upgrade Guide for instructions to configure Network ACLs.
**********************************************************************
**********************************************************************
[Pre-Upgrade Recommendations]
**********************************************************************
*****************************************
********* Dictionary Statistics *********
*****************************************
Please gather dictionary statistics 24 hours prior to
upgrading the database.
To gather dictionary statistics execute the following command
while connected as SYSDBA:
EXECUTE dbms_stats.gather_dictionary_stats;
^^^ MANUAL ACTION SUGGESTED ^^^
**************************************************
************* Fixup Summary ************
2 fixup routines were successful.
5 fixup routines returned INFORMATIONAL text that should be reviewed.
1 ERROR LEVEL check returned INFORMATION that must be acted on prior to upgrade.
************************************************************
====>> USER ACTION REQUIRED <<====
************************************************************
1) Check Tag: COMPATIBLE_PARAMETER failed.
Check Summary: Verify compatible parameter value is valid
Fixup Summary:
""compatible" parameter must be increased manually prior to upgrade."
^^^ MANUAL ACTION REQUIRED ^^^
**************************************************
You MUST resolve the above error prior to upgrade
**************************************************
**************** Pre-Upgrade Fixup Script Complete *********************
SQL>处理脚本中提到的问题：
SQL> ALTER SYSTEM SET PROCESSES=300 SCOPE=SPFILE;
SQL> EXECUTE dbms_stats.gather_dictionary_stats;
SQL> alter system set compatible='10.2.0.5.0' scope=spfile;
[oracle@db10 software]$ emctl stop dbconsole
[oracle@db10 software]$ cp /u01/app/oracle/product/12.1.0/db_1/rdbms/admin/emremove.sql .
SQL> @/software/emremove.sql
4.4.3 清空aud$表
SQL> truncate table sys.aud$;
Table truncated.
SQL> truncate table sys.fga_log$;
Table truncated.4.4.4 清空回收站
SQL> purge DBA_RECYCLEBIN;
DBA Recyclebin purged.5 运行DBUA升级
[root@db10 software]# su - oracle
[oracle@db10 ~]$ cd /u01/app/oracle/product/12.1.0/db_1/bin
[oracle@db10 ~]$ ./dbua 
关闭窗口即可
5.1 再跑升级脚本
[oracle@db10 ~]$ sqlplus / as sysdba
SQL*Plus: Release 12.1.0.2.0 Production on Thu Jul 27 19:04:14 2017
Copyright (c) 1982, 2014, Oracle. All rights reserved.
Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options
SQL> @/u01/app/oracle/cfgtoollogs/cndba/preupgrade/postupgrade_fixups.sql
Post Upgrade Fixup Script Generated on 2017-07-27 16:59:38 Version: 12.1.0.2 Build: 006
Beginning Post-Upgrade Fixups...
**********************************************************************
Check Tag: INVALID_OBJECTS_EXIST
Check Summary: Check for invalid objects
Fix Summary: Invalid objects are displayed and must be reviewed.
**********************************************************************
Fixup Returned Information:
WARNING: --> Database contains INVALID objects prior to upgrade
The list of invalid SYS/SYSTEM objects was written to
registry$sys_inv_objs.
The list of non-SYS/SYSTEM objects was written to
registry$nonsys_inv_objs unless there were over 5000.
Use utluiobj.sql after the upgrade to identify any new invalid
objects due to the upgrade.
**********************************************************************
**********************************************************************
Check Tag: OLD_TIME_ZONES_EXIST
Check Summary: Check for use of older timezone data file
Fix Summary: Update the timezone using the DBMS_DST package after upgrade is complete.
**********************************************************************
Fixup Returned Information:
INFORMATION: --> Older Timezone in use
Database is using a time zone file older than version 18.
After the upgrade, it is recommended that DBMS_DST package
be used to upgrade the 12.1.0.2.0 database time zone version
to the latest version which comes with the new release.
Please refer to My Oracle Support note number 977512.1 for details.
**********************************************************************
**********************************************************************
Check Tag: NOT_UPG_BY_STD_UPGRD
Check Summary: Identify existing components that will NOT be upgraded
Fix Summary: This fixup does not perform any action.
**********************************************************************
Fixup Returned Information:
This fixup does not perform any action.
If you want to upgrade those other components, you must do so manually.
**********************************************************************
**********************************************************************
[Post-Upgrade Recommendations]
**********************************************************************
*****************************************
******** Fixed Object Statistics ********
*****************************************
Please create stats on fixed objects two weeks
after the upgrade using the command:
EXECUTE DBMS_STATS.GATHER_FIXED_OBJECTS_STATS;
^^^ MANUAL ACTION SUGGESTED ^^^
**************************************************
************* Fixup Summary ************
3 fixup routines generated INFORMATIONAL messages that should be reviewed.
*************** Post Upgrade Fixup Script Complete ********************
PL/SQL procedure successfully completed.5.2 检查timezone
SQL> select * from v$timezone_file;
FILENAME VERSION CON_ID
-------------------- ---------- ----------
timezlrg_18.dat 18 0
5.3 检查SYS和SYSTEM用户无效对象
可以看到SYS,SYSTEM用户下有关于OLAP对象。
SQL> select owner,object_type,count(*) from dba_objects where status='INVALID' group by owner,object_type order by owner,object_type ;
SYS PACKAGE BODY 1
1 rows selected.Starting with Oracle Database 12c, OLAP Catalog component is desupported.
运行ORACLE_HOME/olap/admin/catnoamd.sql
--再次查看
SQL> select owner,object_type,count(*) from dba_objects where status='INVALID' group by owner,object_type order by owner,object_type ;
no rows selected5.4 编译无效对象
SQL> @$ORACLE_HOME/rdbms/admin/utlrp.sql
TIMESTAMP
------------------------------------------------------------------------------------------------------------------------------------------------------------------
COMP_TIMESTAMP UTLRP_BGN 2017-07-27 19:09:57
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 2017-07-27 19:10:01
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
5.5 确认所有组件有效
SQL> select comp_id,status from dba_registry;
COMP_ID STATUS
------------------------------ -----------
SDO VALID
ORDIM VALID
XDB VALID
CONTEXT VALID
OWM VALID
CATALOG VALID
CATPROC VALID
JAVAVM VALID
XML VALID
CATJAVA VALID
APS VALID
COMP_ID STATUS
------------------------------ -----------
XOQ VALID
12 rows selected.5.6 确认数据库版本
SQL> select * from v$version;
BANNER CON_ID
-------------------------------------------------------------------------------- ----------
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production 0
PL/SQL Release 12.1.0.2.0 - Production 0
CORE 12.1.0.2.0 Production 0
TNS for Linux: Version 12.1.0.2.0 - Production 0
NLSRTL Version 12.1.0.2.0 - Production 05.7 验证之前插入的数据
SQL> select count(*) from cndba.test;
COUNT(*)
----------
1545至此已经成功升级到12C R1
版权声明：本文为博主原创文章，未经博主允许不得转载。
10g 12c
