资讯

精准传达 • 有效沟通

从品牌网站建设到网络营销策划，从策略到执行的一站式服务

网站建设 >

查看其它板块

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

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

联系：手机(+86 13429648788) QQ(107644445)【Oracle12C】11GR2升级到12CR1并插入CDB

十余年建站经验， 做网站、网站设计客户的见证与正确选择。成都创新互联提供完善的营销型网页建站明细报价表。后期开发更加便捷高效，我们致力于追求更美、更快、更规范。

标题：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升级版本要求
【Oracle12C】11GR2升级到12CR1并插入CDB
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 Production
PL/SQL Release 11.2.0.4.0 - Production
CORE    11.2.0.4.0      Production
TNSforLinux: Version 11.2.0.4.0 - Production
NLSRTL Version 11.2.0.4.0 - Production
 
SQL> show parametername;
 
NAME                                TYPE        VALUE
------------------------------------ ----------- ------------------------------
cell_offloadgroup_name               string
db_file_name_convert                 string
db_name                              string      ora11g
db_unique_name                       string      ora11g
global_names                         boolean    FALSE
instance_name                        string      ora11g
lock_name_space                      string
log_file_name_convert                string
processor_group_name                 string
service_names                        string      ora11g
 
SQL>selectCOMP_NAME,VERSION,STATUSfromdba_registry;
 
COMP_NAME                                VERSION                        STATUS
---------------------------------------- ------------------------------ ----------------------
OWB                                      11.2.0.4.0                     VALID
Oracle Application Express               3.2.1.00.12                    VALID
Spatial                                  11.2.0.4.0                     VALID
Oracle Multimedia                        11.2.0.4.0                     VALID
Oracle XMLDatabase                     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
OracleDatabaseCatalog Views            11.2.0.4.0                     VALID
OracleDatabasePackagesandTypes       11.2.0.4.0                     VALID
JServer JAVA Virtual Machine             11.2.0.4.0                     VALID
Oracle XDK                               11.2.0.4.0                     VALID
OracleDatabaseJava 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
 
16rowsselected.

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

SQL> @/tmp/preupgrd.sql
Loading 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.log
 
Pre-Upgrade Fixup Script (runinsourcedatabase environment):
 /u02/app/oracle/cfgtoollogs/ora11g/preupgrade/preupgrade_fixups.sql
 
Post-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 addressed
                    prior 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.sql
EXECUTE 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]
【Oracle12C】11GR2升级到12CR1并插入CDB
【Oracle12C】11GR2升级到12CR1并插入CDB
【Oracle12C】11GR2升级到12CR1并插入CDB

升级后检查
执行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              0
PL/SQL Release 12.1.0.1.0 - Production                                                    0
CORE    12.1.0.1.0      Production                                                        0
TNSforLinux: Version 12.1.0.1.0 - Production                                            0
NLSRTL Version 12.1.0.1.0 - Production                                                    0
 
SQL>selectCOMP_NAME,VERSION,STATUSfromdba_registry;
 
COMP_NAME                                     VERSION                        STATUS
--------------------------------------------- ------------------------------ ----------------------
Oracle Application Express                    4.2.0.00.27                    VALID
OWB                                           11.2.0.4.0                     VALID
Spatial                                       12.1.0.1.0                     VALID
Oracle Multimedia                             12.1.0.1.0                     VALID
Oracle XMLDatabase                          12.1.0.1.0                     VALID
Oracle Text                                   12.1.0.1.0                     VALID
Oracle Workspace Manager                      12.1.0.1.0                     VALID
OracleDatabaseCatalog Views                 12.1.0.1.0                     VALID
OracleDatabasePackagesandTypes            12.1.0.1.0                     VALID
JServer JAVA Virtual Machine                  12.1.0.1.0                     VALID
Oracle XDK                                    12.1.0.1.0                     VALID
OracleDatabaseJava Packages                 12.1.0.1.0                     VALID
OLAP Analytic Workspace                       12.1.0.1.0                     VALID
Oracle OLAP API                               12.1.0.1.0                     VALID
 
14rowsselected.

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

--升级前
[oracle@xifenfei ~]$grepora11g/etc/oratab
ora11g:/u02/app/oracle/product/11.2/db_1:N:            # line added by Agent
 
--升级后
[oracle@xifenfei ~]$grepora11g/etc/oratab
ora11g:/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操作示意图
【Oracle12C】11GR2升级到12CR1并插入CDB

升级后数据库信息


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

创建XML元数据文件

SQL> shutdown immediate
Databaseclosed.
Databasedismounted.
ORACLE instance shut down.
SQL> startup mount;
ORACLE instance started.
 
Total SystemGlobalArea  801701888 bytes
FixedSize                 2293496 bytes
VariableSize            314573064 bytes
DatabaseBuffers          478150656 bytes
Redo Buffers                6684672 bytes
Databasemounted.
SQL>alterdatabaseopenreadonly;
 
Databasealtered.
 
SQL>EXECDBMS_PDB.DESCRIBE( pdb_descr_file =>'/tmp/ora11g.xml');
 
PL/SQLproceduresuccessfully completed.
 
SQL> shutdown immediate
Databaseclosed.
Databasedismounted.
ORACLE instance shut down.

CDB数据库信息

SQL>select*fromv$version;
 
BANNER                                                                               CON_ID
-------------------------------------------------------------------------------- ----------
OracleDatabase12c Enterprise Edition Release 12.1.0.1.0 - 64bit Production              0
PL/SQL Release 12.1.0.1.0 - Production                                                    0
CORE    12.1.0.1.0      Production                                                        0
TNSforLinux: Version 12.1.0.1.0 - Production                                            0
NLSRTL Version 12.1.0.1.0 - Production                                                    0
 
SQL> show pdbs;
 
    CON_ID CON_NAME                      OPENMODE  RESTRICTED
---------- ------------------------------ ---------- ----------
         2 PDB$SEED                      READONLY NO
         3 PDB1                           MOUNTED
         4 PDB2                           MOUNTED

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

SQL>setserveroutputon;
 declare
         compat boolean :=FALSE;
    begin
       compat := dbms_pdb.check_plug_compatibility(pdb_descr_file =>'/tmp/ora11g.xml');
        if compat
        then
               dbms_output.put_line('Yes');
       else
               dbms_output.put_line('No');
      endif;
   end;SQL>   2    3    4    5    6    7    8    9   10   11 
 12  /
No
 
PL/SQLproceduresuccessfully completed.

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

插入no-cdb to cdb库

SQL>CREATEPLUGGABLEDATABASEora11g USING'/tmp/ora11g.xml'NOCOPY;
 
Pluggabledatabasecreated.
 
SQL> show pdbs
 
    CON_ID CON_NAME                      OPENMODE  RESTRICTED
---------- ------------------------------ ---------- ----------
         2 PDB$SEED                      READONLY NO
         3 PDB1                           MOUNTED
         4 PDB2                           MOUNTED
         5 ORA11G                         MOUNTED

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

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

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

执行noncdb_to_pdb脚本

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

同步pdb信息

[oracle@xifenfei ~]$ sqlplus sys/xifenfei@ora11gassysdba
 
SQL*Plus: Release 12.1.0.1.0 ProductiononMon Jul 1 03:05:42 2013
 
Copyright (c) 1982, 2013, Oracle. Allrights reserved.
 
 
Connectedto:
OracleDatabase12c Enterprise Edition Release 12.1.0.1.0 - 64bit Production
Withthe Partitioning,RealApplication Clusters, Automatic Storage Management, OLAP,
Advanced AnalyticsandRealApplication Testing options
 
SYS% 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 /assysdba
Connected.
SYS% cdb1> show pdbs
 
    CON_ID CON_NAME                      OPENMODE  RESTRICTED
---------- ------------------------------ ---------- ----------
         2 PDB$SEED                      READONLY NO
         3 PDB1                           MOUNTED
         4 PDB2                           MOUNTED
         5 ORA11G                        READWRITENO

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

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



分享标题：【Oracle12C】11GR2升级到12CR1并插入CDB
分享网址：http://www.cdkjz.cn/article/jogpip.html
返回首页 了解更多建站资讯

相关资讯

多年建站经验

多一份参考，总有益处

联系快上网，免费获得专属《策划方案》及报价

咨询相关问题或预约面谈，可以通过以下方式与我们联系

大客户专线   成都：13518219792   座机：028-86922220

在线咨询 提交需求
友情链接 交换友情链接
化妆品企业网站建设方案成都网站设计软文发布平台雅安做网站眉山电信机房东电技服wap手机网站成都广告制作四川和正源无边发光字

成都网站建设公司地址：成都市青羊区太升南路288号锦天国际A座10层 建设咨询028-86922220

成都快上网科技有限公司-四川网站建设设计公司 | 蜀ICP备19037934号 Copyright 2020,ALL Rights Reserved cdkjz.cn | 成都网站建设 | © Copyright 2020版权所有.

专家团队为您提供成都网站建设,成都网站设计,成都品牌网站设计,成都营销型网站制作等服务,成都建网站就找快上网！ | 成都网站建设哪家好？ | 网站建设地图