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> @/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脚本,执行该脚本按照提示修复问题.
这里主要以下问题需要解决
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记录,执行完会自动修改
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>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>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> 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信息
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