【Oracle12C】11GR2升級到12CR1並插入CDB

xysoul_雲龍發表於2017-12-06


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升級版本要求
12C_UPGRADE_VERSION
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

    <1div style="border:0px;margin:24px 0px 0px;padding:0px;vertical-align:baseline;font-family:" color:#888888;"=""> 此條目發表在 ,  分類目錄。將加入收藏夾。

    來自 “ ITPUB部落格 ” ,連結:http://blog.itpub.net/29487349/viewspace-2148375/,如需轉載,請註明出處,否則將追究法律責任。

    相關文章