【Oracle12C】11GR2升级到12CR1并插入CDB

2025-04-14 技术教程

转自：发表于2013 年 07 月 08 日由惜分飞

联系：手机(+86 13429648788)QQ(107644445)

标题：11GR2升级到12CR1并插入CDB

ORACLE 12C已经发布了十多天,其中一个亮点就是pdb,而在12C之前的数据库没有pdb之说,也就是说如果要把以前的数据库升级到12C,并且想让该库变成一个pdb,那所要做的工作就是先需要升级数据库从12C之前版本升级到12C,然后把一个NO-CDB数据库PLUG到CDB中.本blog演示:在前段时间意外的释放出来ORACLE 11.2.0.4版本,利用该版本升级到12.1.0.1,并插入到一个cdb库中
ORACLE 12C升级版本要求

11.2.0.4到12.1.0.1升级操作操作[升级整体参考文档1503653.1]
当前相关组件版本信息

SQL>select*fromv$version;BANNER--------------------------------------------------------------------------------OracleDatabase11g Enterprise Edition Release 11.2.0.4.0 - 64bit ProductionPL/SQL Release 11.2.0.4.0 - ProductionCORE 11.2.0.4.0 ProductionTNSforLinux: Version 11.2.0.4.0 - ProductionNLSRTL Version 11.2.0.4.0 - ProductionSQL> show parametername;NAMETYPE VALUE------------------------------------ ----------- ------------------------------cell_offloadgroup_name stringdb_file_name_convert stringdb_name string ora11gdb_unique_name string ora11gglobal_names booleanFALSEinstance_name string ora11glock_name_space stringlog_file_name_convert stringprocessor_group_name stringservice_names string ora11gSQL>selectCOMP_NAME,VERSION,STATUSfromdba_registry;COMP_NAME VERSION STATUS---------------------------------------- ------------------------------ ----------------------OWB 11.2.0.4.0 VALIDOracle Application Express 3.2.1.00.12 VALIDSpatial 11.2.0.4.0 VALIDOracle Multimedia 11.2.0.4.0 VALIDOracle XMLDatabase11.2.0.4.0 VALIDOracle Text 11.2.0.4.0 VALIDOracle Expression Filter 11.2.0.4.0 VALIDOracle Rules Manager 11.2.0.4.0 VALIDOracle Workspace Manager 11.2.0.4.0 VALIDOracleDatabaseCatalog Views 11.2.0.4.0 VALIDOracleDatabasePackagesandTypes 11.2.0.4.0 VALIDJServer JAVA Virtual Machine 11.2.0.4.0 VALIDOracle XDK 11.2.0.4.0 VALIDOracleDatabaseJava Packages 11.2.0.4.0 VALIDOLAP Analytic Workspace 11.2.0.4.0 VALIDOracle OLAP API 11.2.0.4.0 VALID16rowsselected.

升级准备工作
执行Pre-Upgrade Utility,具体参考Note 884522.1 How to Download and Run Oracle’s Database Pre-Upgrade Utility

SQL> @/tmp/preupgrd.sqlLoading Pre-Upgrade Package...Executing Pre-Upgrade Checks...Pre-Upgrade Checks Complete.************************************************************Results of the checks are located at:/u02/app/oracle/cfgtoollogs/ora11g/preupgrade/preupgrade.logPre-Upgrade Fixup Script (runinsourcedatabase environment):/u02/app/oracle/cfgtoollogs/ora11g/preupgrade/preupgrade_fixups.sqlPost-Upgrade Fixup Script (run shortly after upgrade):/u02/app/oracle/cfgtoollogs/ora11g/preupgrade/postupgrade_fixups.sql************************************************************Fixup scripts must be reviewed prior to being executed.************************************************************************************************************************====>> USER ACTION REQUIRED <<====************************************************************The following are *** ERROR LEVEL CONDITIONS *** that must be addressedprior to attempting your upgrade.Failure todoso will resultina failed upgrade.You MUST resolve the above errors prior to upgrade************************************************************

这里发生了改变,在12C之前版本直接显示需要修改的相关操作,12C把相关操作封装到了preupgrade_fixups.sql脚本，执行该脚本按照提示修复问题.
这里主要以下问题需要解决

ALTER SYSTEM SET PROCESSES=300 SCOPE=SPFILE;@/u01/app/oracle/product/12.1/db_1/rdbms/admin/emremove.sql@/u02/app/oracle/product/11.2/db_1/olap/admin/catnoamd.sqlEXECUTE dbms_stats.gather_dictionary_stats;

执行dbupgdiag.sql收集升级前信息
如果有异常核对相应的MOS文章修改,具体见Script to Collect DB Upgrade/Migrate Diagnostic Information (dbupgdiag.sql) [ID 556610.1]

dbua升级数据库
12C的dbua发生了不上变化,因为都是图形化界面,不做过多描述,贴上几幅区别较大图进行说明,关于12C的dbua变化更加详细信息请参考:Complete Checklist to Upgrade the Database to 12c Release 1 using DBUA [ID 1516557.1]

升级后检查
执行postupgrade_fixups.sql脚本查看确定需要升级后处理事宜,因为是dbua升级数据库,很多问题已经自动修复,无需人工再次干预,例如timezone(14–>18)

SQL>select*fromv$version;BANNER CON_ID-------------------------------------------------------------------------------- ----------OracleDatabase12c Enterprise Edition Release 12.1.0.1.0 - 64bit Production 0PL/SQL Release 12.1.0.1.0 - Production 0CORE 12.1.0.1.0 Production 0TNSforLinux: Version 12.1.0.1.0 - Production 0NLSRTL Version 12.1.0.1.0 - Production 0SQL>selectCOMP_NAME,VERSION,STATUSfromdba_registry;COMP_NAME VERSION STATUS--------------------------------------------- ------------------------------ ----------------------Oracle Application Express 4.2.0.00.27 VALIDOWB 11.2.0.4.0 VALIDSpatial 12.1.0.1.0 VALIDOracle Multimedia 12.1.0.1.0 VALIDOracle XMLDatabase12.1.0.1.0 VALIDOracle Text 12.1.0.1.0 VALIDOracle Workspace Manager 12.1.0.1.0 VALIDOracleDatabaseCatalog Views 12.1.0.1.0 VALIDOracleDatabasePackagesandTypes 12.1.0.1.0 VALIDJServer JAVA Virtual Machine 12.1.0.1.0 VALIDOracle XDK 12.1.0.1.0 VALIDOracleDatabaseJava Packages 12.1.0.1.0 VALIDOLAP Analytic Workspace 12.1.0.1.0 VALIDOracle OLAP API 12.1.0.1.0 VALID14rowsselected.

升级前后oratab信息对比
dbua使用12C环境变量shell下执行,注意不要人工修改oratab记录,执行完会自动修改

--升级前[oracle@xifenfei ~]$grepora11g/etc/oratabora11g:/u02/app/oracle/product/11.2/db_1:N:# line added by Agent--升级后[oracle@xifenfei ~]$grepora11g/etc/oratabora11g:/u01/app/oracle/product/12.1/db_1:N:# line added by Agent

到此,我们可以确定11.2.0.4已经顺利升级到12.1.0.1,升级过程比较顺利,但是升级时间比较长,很可能和我的机器配置有关

NO-CDB PLUG CDB
把11.2.0.4升级到12.1.0.1的数据库插入到一个CDB数据库中,让其成为CDB一部分
PLUG操作示意图

升级后数据库信息

SQL>selectcdb,NAME,dbidfromv$database;CDBNAMEDBID--- --------- ----------NOORA11G 4215674657SQL>select*fromv$version;BANNER CON_ID-------------------------------------------------------------------------------- ----------OracleDatabase12c Enterprise Edition Release 12.1.0.1.0 - 64bit Production 0PL/SQL Release 12.1.0.1.0 - Production 0CORE 12.1.0.1.0 Production 0TNSforLinux: Version 12.1.0.1.0 - Production 0NLSRTL Version 12.1.0.1.0 - Production 0

创建XML元数据文件

SQL> shutdown immediateDatabaseclosed.Databasedismounted.ORACLE instance shut down.SQL> startup mount;ORACLE instance started.Total SystemGlobalArea 801701888 bytesFixedSize2293496 bytesVariableSize314573064 bytesDatabaseBuffers 478150656 bytesRedo Buffers 6684672 bytesDatabasemounted.SQL>alterdatabaseopenreadonly;Databasealtered.SQL>EXECDBMS_PDB.DESCRIBE( pdb_descr_file =>'/tmp/ora11g.xml');PL/SQLproceduresuccessfully completed.SQL> shutdown immediateDatabaseclosed.Databasedismounted.ORACLE instance shut down.

CDB数据库信息

检查升级后数据库是否适合插入到该cdb

SQL>setserveroutputon;declarecompat boolean :=FALSE;begincompat := dbms_pdb.check_plug_compatibility(pdb_descr_file =>'/tmp/ora11g.xml');if compatthendbms_output.put_line('Yes');elsedbms_output.put_line('No');endif;end;SQL> 2 3 4 5 6 7 8 9 10 1112 /NoPL/SQLproceduresuccessfully completed.

因为是第一次插入所以显示是No,可以忽略该问题继续插入

插入no-cdb to cdb库

SQL>CREATEPLUGGABLEDATABASEora11g USING'/tmp/ora11g.xml'NOCOPY;Pluggabledatabasecreated.SQL> show pdbsCON_ID CON_NAMEOPENMODE RESTRICTED---------- ------------------------------ ---------- ----------2 PDB$SEEDREADONLYNO3 PDB1 MOUNTED4 PDB2 MOUNTED5 ORA11G MOUNTED

根据官方文档描述,如果是第一次是no-cdb plug cdb,需要先open一次库

SQL>altersessionsetcontainer=ora11g;Session altered.SQL>alterdatabaseopen;alterdatabaseopen*ERRORatline 1:ORA-24344: successwithcompilation errorSQL> !oerr ora 2434424344, 00000,"success with compilation error"// *Cause: A sql/plsql compilation error occurred.// *Action:ReturnOCI_SUCCESS_WITH_INFO alongwiththe error codeSQL> show pdbs;CON_ID CON_NAMEOPENMODE RESTRICTED---------- ------------------------------ ---------- ----------5 ORA11GREADWRITE YES

出现ORA-24344,但是数据库正常open到read write模式,忽略该错误，继续执行

执行noncdb_to_pdb脚本

SQL>altersessionsetcontainer=ora11g;Session altered.SQL> shutdown immediatePluggableDatabaseclosed.SQL> @$ORACLE_HOME/rdbms/admin/noncdb_to_pdb.sql--遇到Warning,脚本自动忽略该错误，继续执行,在最后该脚本编译的时候会修复该问题,原因很可能是某个plslq异常SQL>alterpluggabledatabase"&pdbname"openrestricted;old 1:alterpluggabledatabase"&pdbname"openrestrictednew 1:alterpluggabledatabase"ORA11G"openrestrictedWarning: PDB alteredwitherrors.

同步pdb信息

[oracle@xifenfei ~]$ sqlplus sys/xifenfei@ora11gassysdbaSQL*Plus: Release 12.1.0.1.0 ProductiononMon Jul 1 03:05:42 2013Copyright (c) 1982, 2013, Oracle.Allrights reserved.Connectedto:OracleDatabase12c Enterprise Edition Release 12.1.0.1.0 - 64bit ProductionWiththe Partitioning,RealApplication Clusters, Automatic Storage Management, OLAP,Advanced AnalyticsandRealApplication Testing optionsSYS% ora11g>alterpluggabledatabaseopenrestricted;Pluggabledatabasealtered.SYS% ora11g>execdbms_pdb.sync_pdb();PL/SQLproceduresuccessfully completed.SYS% ora11g>alterpluggabledatabasecloseimmediate;Pluggabledatabasealtered.SYS% ora11g>alterpluggabledatabaseopen;Pluggabledatabasealtered.

确定no-cdb plug cdb 成功

SYS% ora11g> conn /assysdbaConnected.SYS% cdb1> show pdbsCON_ID CON_NAMEOPENMODE RESTRICTED---------- ------------------------------ ---------- ----------2 PDB$SEEDREADONLYNO3 PDB1 MOUNTED4 PDB2 MOUNTED5 ORA11GREADWRITENO

到这里已经完全完成了11.2.0.4数据库插入到12.1.0.1中,实现把11GR2转化为CDB数据库中的一个PDB

升级到Oracle 10.2.0.4ORACLE 12C PDB 维护基础介绍expdp遭遇ORA-39006/ORA-39213故障解决ORA-06553: PLS-801: internal error [56319]深入分析数据库版本相关视图设置pdb随cdb一起启动Oracle 10.2.0.x升级到11.2.0.3ORACLE 12C PDB部分功能测试exp导出数据报EXP-00056/ORA-01403错误恢复被rm意外删除数据文件修改props$.NLS_CHARACTERSET导致ORA-00900异常恢复关于ORACLE 11G密码大小写敏感猜想(USER$.SPARE4)此条目发表在ORACLE 12C,Oracle安装升级分类目录。将固定链接加入收藏夹。