探索Oracle之資料庫升級七 11gR2 to 12c 升級完成後插入PDB

dbhelper發表於2015-01-17
Oracle資料庫

探索Oracle之資料庫升級七
11gR2 to 12c 升級完成後插入PDB

前言：

       從Oracle 12c開始，引入了容器資料庫的概念，可以實現資料庫插拔操作，如下圖:
 
探索Oracle之資料庫升級七 11gR2 to 12c 升級完成後插入PDB
       現在我們就來看看如何將11.2.0.4的資料庫插入到12c的CDP裡面去，讓其成為一個PDB

資料庫。

一、檢視資料庫資訊:

 

  1. SQL> col BANNER format a80
  2. SQL> set line 300
  3. SQL> select * from v$version;

  5. BANNER CON_ID
  6. -------------------------------------------------------------------------------- ----------
  7. Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit Production 0
  8. PL/SQL Release 12.1.0.1.0 - Production 0
  9. CORE 12.1.0.1.0 Production 0
  10. TNS for Linux: Version 12.1.0.1.0 - Production 0
  11. NLSRTL Version 12.1.0.1.0 - Production

  13. SQL> select cdb,name,dbid from v$database;

  15. CDB NAME DBID
  16. --- --------- ----------
  17. NO WOO 4199532651

     從這裡我們可以看到實際上通過升級之後上來的資料庫還是一個non-CDB，並非CDB資料庫，那麼這個時候我們需要在這個none-CDB庫中生成使用者PDB的XML檔案，再建立CDB資料庫進行插入進去。

二、檢視錶空間及資料檔案資訊：

  1. SQL> select * from v$tablespace;

  3.        TS# NAME INC BIG FLA ENC CON_ID
  4. ---------- ------------------------------ --- --- --- --- ----------
  5.          0 SYSTEM YES NO YES 0
  6.          1 SYSAUX YES NO YES 0
  7.          2 UNDOTBS1 YES NO YES 0
  8.          4 USERS YES NO YES 0
  9.          3 TEMP NO NO YES 0

  11. SQL> set line 300
  12. SQL> col file_name format a40
  13. SQL> col tablespace_name format a10
  14. SQL> select tablespace_name,file_name from dba_data_files;

  16. TABLESPACE FILE_NAME
  17. ---------- ----------------------------------------
  18. USERS /DBData/woo/woo/users01.dbf
  19. UNDOTBS1 /DBData/woo/woo/undotbs01.dbf
  20. SYSAUX /DBData/woo/woo/sysaux01.dbf
  21. SYSTEM /DBData/woo/woo/system01.dbf

三、建立用於生成PDB的XML檔案
  1. SQL> shutdown immediate
  2. Database closed.
  3. Database dismounted.
  4. ORACLE instance shut down.
  5. SQL> startup mount
  6. ORACLE instance started.

  8. Total System Global Area 2772574208 bytes
  9. Fixed Size 2292240 bytes
  10. Variable Size 2533361136 bytes
  11. Database Buffers 218103808 bytes
  12. Redo Buffers 18817024 bytes
  13. Database mounted.

  15. SQL> alter database open read only;
  16. Database altered.

  18. SQL> exec dbms_pdb.describe(pdb_descr_file => \'/home/oracle/woo_ora11g.xml\');
  19. PL/SQL procedure successfully completed.

  21. SQL> host ls -rtl /home/oracle/woo*.xml
  22. -rw-r--r-- 1 oracle oinstall 4147 Nov 27 19:17 /home/oracle/woo_ora11g.xml

  24. SQL> shutdown immediate
  25. Database closed.
  26. Database dismounted.
  27. ORACLE instance shut down.

四、檢查升級後的資料庫是否適合以PDB的方式插入到CDB中

  1. SQL> set serveroutput on;
  2. SQL> declare
  3.   2         compat boolean := FALSE;
  4.   3 begin
  5.   4         compat := dbms_pdb.check_plug_compatibility(pdb_descr_file => \'/home/oracle/woo_ora11g.xml\');
  6.   5     if compat
  7.   6     then
  8.   7         dbms_output.put_line(\'YES\');
  9.   8     else
  10.   9         dbms_output.put_line(\'No\');
  11.  10     end if;
  12.  11 end;
  13.  12 /

  15. No

  17. PL/SQL procedure successfully completed.

      因為是第一次插入，所以執行結果顯示為NO，可以忽略繼續插入。

五、建立CDB資料庫
   5.1 執行dbca建立cdb資料庫
探索Oracle之資料庫升級七 11gR2 to 12c 升級完成後插入PDB
    5.2 指定需要建立的cdb資料庫名稱
探索Oracle之資料庫升級七 11gR2 to 12c 升級完成後插入PDB
     5.3 建立CDB前檢查
探索Oracle之資料庫升級七 11gR2 to 12c 升級完成後插入PDB
    5.4 Summar 點選Finish開始建立
探索Oracle之資料庫升級七 11gR2 to 12c 升級完成後插入PDB
    5.5  現在正在開始建立
探索Oracle之資料庫升級七 11gR2 to 12c 升級完成後插入PDB
  5.6 至此已經建立完成，告訴我們CDB資料庫的資訊
探索Oracle之資料庫升級七 11gR2 to 12c 升級完成後插入PDB
   5.7 點選Close 關閉建立頁面
探索Oracle之資料庫升級七 11gR2 to 12c 升級完成後插入PDB

六、檢視當前pdb資訊

  1. [oracle@db01 ~]$ export ORACLE_SID=woo12c
  2. SQL> show pdbs;

  4.     CON_ID CON_NAME OPEN MODE RESTRICTED
  5. ---------- ------------------------------ ---------- ----------
  6.          2 PDB$SEED READ ONLY NO
  7.          3 PDB01 READ WRITE NO

  9. SQL> select con_id,dbid,NAME,OPEN_MODE from v$pdbs;

  11.     CON_ID DBID NAME OPEN_MODE
  12. ---------- ---------- ------------------------------ ----------
  13.          2 4107385256 PDB$SEED READ ONLY
  14.          3 3926295770 PDB01 READ WRITE
七、將non-cdb資料庫插入到cdb中成為一個pdb
  1. SQL> CREATE PLUGGABLE DATABASE woo_ora11g USING \'/home/oracle/woo_ora11g.xml\';
  2. Pluggable database created.
八、檢視alert日誌相關資訊
              將Non-CDB插入到CDB中成為一個PDB輸出的Alert 日誌。
  1. CREATE PLUGGABLE DATABASE woo_ora11g USING '/home/oracle/woo_ora11g.xml'
    Thu Nov 27 23:45:05 2014
    ****************************************************************
    Pluggable Database WOO_ORA11G with pdb id - 4 is created as UNUSABLE.
    If any errors are encountered before the pdb is marked as NEW,
    then the pdb must be dropped
    ****************************************************************
    Deleting old file#1 from file$
    Deleting old file#2 from file$
    Deleting old file#3 from file$
    Deleting old file#4 from file$
    Deleting old file#5 from file$
    Adding new file#13 to file$(old file#1)
    Adding new file#14 to file$(old file#2)
    Adding new file#15 to file$(old file#4)
    Marking tablespace #2 invalid since it is not present               in the describe file
    Marking tablespace #5 invalid since it is not present               in the describe file
    Successfully created internal service woo_ora11g at open
    ALTER SYSTEM: Flushing buffer cache inst=0 container=4 local
    ****************************************************************
    Post plug operations are now complete.
    Pluggable database WOO_ORA11G with pdb id - 4 is now marked as NEW.
    ****************************************************************
    Completed: CREATE PLUGGABLE DATABASE woo_ora11g USING '/home/oracle/woo_ora11g.xml'

九、插入完成後檢視pdbs資訊

  1. SQL> show pdbs

  3.     CON_ID CON_NAME OPEN MODE RESTRICTED
  4. ---------- ------------------------------ ---------- ----------
  5.          2 PDB$SEED READ ONLY NO
  6.          3 PDB01 READ WRITE NO
  7.          4 WOO_ORA11G MOUNTED
  8. SQL> select con_id,dbid,NAME,OPEN_MODE from v$pdbs;

  10.     CON_ID DBID NAME OPEN_MODE
  11. ---------- ---------- ------------------------------ ----------
  12.          2 4107385256 PDB$SEED READ ONLY
  13.          3 3926295770 PDB01 READ WRITE
  14.          4 4199532651 WOO_ORA11G MOUNTED

  16. 第一次no-cdb plug cdb是mount狀態，需要將其open;
  17. SQL> alter pluggable database open;
  18. Pluggable database altered.

  20. SQL> show pdbs;

  22.     CON_ID CON_NAME OPEN MODE RESTRICTED
  23. ---------- ------------------------------ ---------- ----------
  24.          2 PDB$SEED READ ONLY NO
  25.          3 PDB01 MOUNTED
  26.          4 WOO_ORA11G READ WRITE YES

插入完成之後需要執行noncdb_to_pdb指令碼，修復原non-cdb 和新的pdb不相容的問題：

十、執行noncdb_pdb指令碼
  1. SQL> alter session set container=WOO_ORA11G;
  2. Session altered.

  4. SQL> @$ORACLE_HOME/rdbms/admin/noncdb_to_pdb.sql

  6. ………

  8. #### 遇到warning,指令碼會自動忽略錯誤，繼續執行。在最後指令碼編譯的時候會修復這些問題。

  10. 更新完之後需要同步pdb資訊
  11. SQL> show pdbs

  13.     CON_ID CON_NAME OPEN MODE RESTRICTED
  14. ---------- ------------------------------ ---------- ----------
  15.          4 WOO_ORA11G MOUNTED
  16. SQL> alter pluggable database open restricted;

  18. Pluggable database altered.

  20. SQL> show pdbs

  22.     CON_ID CON_NAME OPEN MODE RESTRICTED
  23. ---------- ------------------------------ ---------- ----------
  24.          4 WOO_ORA11G READ WRITE YES
  25. SQL> exec dbms_pdb.sync_pdb();

  27. PL/SQL procedure successfully completed.

  29. SQL> alter pluggable database close immediate;

  31. Pluggable database altered.

  33. SQL> alter pluggable database open;

  35. Pluggable database altered.

十一、至此no-cdb plug to cdb 成功
  1. SQL> show pdbs;

  3.     CON_ID CON_NAME OPEN MODE RESTRICTED
  4. ---------- ------------------------------ ---------- ----------
  5.          2 PDB$SEED READ ONLY NO
  6.          3 PDB01 MOUNTED
  7.          4 WOO_ORA11G READ WRITE NO
  8. SQL>


 

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

相關文章