【Oracle12C】11GR2升級到12CR1並插入CDB
聯絡：手機(+86 13429648788) QQ(107644445)
作者：惜分飛©版權所有[未經本人同意,不得以任何形式轉載,否則有進一步追究法律責任的權利.]
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 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]
升級後檢查
執行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操作示意圖
升級後資料庫資訊
|
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)
來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/29487349/viewspace-2148375/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- 【資料庫升級】Oracle指令碼升級12c CDB to 19c CDB資料庫Oracle指令碼
- 探索Oracle之資料庫升級七 11gR2 to 12c 升級完成後插入PDBOracle資料庫
- oracle 12c non-cdb升級成cdb模式Oracle模式
- Oracle12c多租戶如何連線到CDB或PDB、CDB與PDB容器切換Oracle
- 1.安裝 kali到vm並升級
- 10.2.0.1安裝並升級到10.2.0.5
- Debian 升級到 PHP 7，並支援並行安裝PHP並行
- oracle12c的CDB和PDB初探Oracle
- 【Oracle升級】Oracle指令碼升級11g to 19c non-CDBOracle指令碼
- 【PATCH】Oracle12c升級補丁初體驗Oracle
- spring升級到3.1.1 hibernate升級到4備忘Spring
- mongodb單機從3.2升級到4.0.4升級MongoDB
- gitlab遷移到docker並升級大版本到10.1.1和漢化GitlabDocker
- oracle 10 rac 升級 10.2.0.1升級到10.2.0.5Oracle
- CentOS升級MySQL到5.5CentOSMySql
- yum升級php到5.3PHP
- oracle 升級到 11.2.0.2Oracle
- Linux環境下CRS升級到10.2.0.4.2 Database升級到10.2.0.4.3LinuxDatabase
- ABP Framework 手動升級指南：從6.0.1升級到7.0.0Framework
- oracle資料庫升級11.2.0.3升級到11.2.0.4Oracle資料庫
- RedHat Advance Server 3上安裝Oracle 9.2.0 並升級到9.2.0.4(轉)RedhatServerOracle
- 11gR2 RAC out-of-place 滾動升級(2)
- 11gR2 RAC out-of-place 滾動升級(1)
- Mac 升級 PHP 到 7.4 版本MacPHP
- ccproject升級到1135Project
- Cocospod 升級到指定版本
- MongoDB升級--從3.4到3.6MongoDB
- centos 升級nginx到1.10.2CentOSNginx
- PHP版本升級:從php7.1升級到php7.2PHP
- 9.2.0.4 升級到10.2.0.5升級後 Oracle Ultra Search 元件NO SCRIPTOracle元件
- ORACLE11G從WINDOWS到LINUX跨平臺遷移並升級OracleWindowsLinux
- 升級到資料庫到10.2.0.5.0版本資料庫
- Oracle 19c - 手動升級 Oracle 12.x, 18c CDB 到 Oracle 19c (19.x)Oracle
- ASM例項 10gR2升到11gR2ASM
- oracle從10.2.0.4升級到11.2.0.1的三種升級方法Oracle
- Fedora 1/ rh as 3 安裝oracle 9.2.0, 並升級到9.2.0.4詳細過程(轉)Oracle
- Oracle 19c - 手動升級到 Non-CDB 19c 的完整核對清單 (Doc ID 2577572.1)Oracle
- MySQL 5.6.47升級到5.7.20（一）MySql