聯(lián)系：手機(jī)(+86 13429648788) QQ(107644445)
十余年建站經(jīng)驗(yàn)， 做網(wǎng)站、網(wǎng)站設(shè)計(jì)客戶的見證與正確選擇。成都創(chuàng)新互聯(lián)提供完善的營銷型網(wǎng)頁建站明細(xì)報(bào)價(jià)表。后期開發(fā)更加便捷高效，我們致力于追求更美、更快、更規(guī)范。
標(biāo)題：11GR2升級(jí)到12CR1并插入CDB
作者：惜分飛©版權(quán)所有[未經(jīng)本人同意,不得以任何形式轉(zhuǎn)載,否則有進(jìn)一步追究法律責(zé)任的權(quán)利.]
ORACLE 12C已經(jīng)發(fā)布了十多天,其中一個(gè)亮點(diǎn)就是pdb,而在12C之前的數(shù)據(jù)庫沒有pdb之說,也就是說如果要把以前的數(shù)據(jù)庫升級(jí)到12C,并且想讓該庫變成一個(gè)pdb,那所要做的工作就是先需要升級(jí)數(shù)據(jù)庫從12C之前版本升級(jí)到12C,然后把一個(gè)NO-CDB數(shù)據(jù)庫PLUG到CDB中.本blog演示:在前段時(shí)間意外的釋放出來ORACLE 11.2.0.4版本,利用該版本升級(jí)到12.1.0.1,并插入到一個(gè)cdb庫中
ORACLE 12C升級(jí)版本要求
11.2.0.4到12.1.0.1升級(jí)操作操作[升級(jí)整體參考文檔1503653.1]
當(dāng)前相關(guān)組件版本信息
|
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.
|
升級(jí)準(zhǔn)備工作
執(zhí)行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
************************************************************
|
這里發(fā)生了改變,在12C之前版本直接顯示需要修改的相關(guān)操作,12C把相關(guān)操作封裝到了preupgrade_fixups.sql腳本，執(zhí)行該腳本按照提示修復(fù)問題.
這里主要以下問題需要解決
|
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;
|
執(zhí)行dbupgdiag.sql收集升級(jí)前信息
如果有異常核對(duì)相應(yīng)的MOS文章修改,具體見Script to Collect DB Upgrade/Migrate Diagnostic Information (dbupgdiag.sql) [ID 556610.1]
dbua升級(jí)數(shù)據(jù)庫
12C的dbua發(fā)生了不上變化,因?yàn)槎际菆D形化界面,不做過多描述,貼上幾幅區(qū)別較大圖進(jìn)行說明,關(guān)于12C的dbua變化更加詳細(xì)信息請(qǐng)參考:Complete Checklist to Upgrade the Database to 12c Release 1 using DBUA [ID 1516557.1]
|
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.
|
升級(jí)前后oratab信息對(duì)比
dbua使用12C環(huán)境變量shell下執(zhí)行,注意不要人工修改oratab記錄,執(zhí)行完會(huì)自動(dòng)修改
|
--升級(jí)前
[oracle@xifenfei ~]$grepora11g/etc/oratab
ora11g:/u02/app/oracle/product/11.2/db_1:N: # line added by Agent
--升級(jí)后
[oracle@xifenfei ~]$grepora11g/etc/oratab
ora11g:/u01/app/oracle/product/12.1/db_1:N: # line added by Agent
|
到此,我們可以確定11.2.0.4已經(jīng)順利升級(jí)到12.1.0.1,升級(jí)過程比較順利,但是升級(jí)時(shí)間比較長,很可能和我的機(jī)器配置有關(guān)
NO-CDB PLUG CDB
把11.2.0.4升級(jí)到12.1.0.1的數(shù)據(jù)庫插入到一個(gè)CDB數(shù)據(jù)庫中,讓其成為CDB一部分
PLUG操作示意圖
|
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
|
創(chuàng)建XML元數(shù)據(jù)文件
|
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數(shù)據(jù)庫信息
|
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
|
檢查升級(jí)后數(shù)據(jù)庫是否適合插入到該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.
|
因?yàn)槭堑谝淮尾迦胨燥@示是No,可以忽略該問題繼續(xù)插入
插入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
|
根據(jù)官方文檔描述,如果是第一次是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
|
出現(xiàn)ORA-24344,但是數(shù)據(jù)庫正常open到read write模式,忽略該錯(cuò)誤，繼續(xù)執(zhí)行
執(zhí)行noncdb_to_pdb腳本
|
SQL>altersessionsetcontainer=ora11g;
Session altered.
SQL> shutdown immediate
PluggableDatabaseclosed.
SQL> @$ORACLE_HOME/rdbms/admin/noncdb_to_pdb.sql
--遇到Warning,腳本自動(dòng)忽略該錯(cuò)誤，繼續(xù)執(zhí)行,在最后該腳本編譯的時(shí)候會(huì)修復(fù)該問題,原因很可能是某個(gè)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
|
到這里已經(jīng)完全完成了11.2.0.4數(shù)據(jù)庫插入到12.1.0.1中,實(shí)現(xiàn)把11GR2轉(zhuǎn)化為CDB數(shù)據(jù)庫中的一個(gè)PDB
重慶分公司
重慶分公司
