探索Oracle之資料庫升級七 11gR2 to 12c 升級完成後插入PDB,11gr2pdb
探索Oracle之資料庫升級七
11gR2 to 12c 升級完成後插入PDB
前言:
從Oracle 12c開始,引入了容器資料庫的概念,可以實現資料庫插拔操作,如:
現在我們就來看看如何將11.2.0.4的資料庫插入到12c的CDP裡面去,讓其成為一個PDB
資料庫。
一、查看資料庫資訊:
SQL> col BANNER format a80SQL> set line 300SQL> select * from v$version;BANNER CON_ID-------------------------------------------------------------------------------- ----------Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit Production 0PL/SQL Release 12.1.0.1.0 - Production 0CORE 12.1.0.1.0 Production 0TNS for Linux: Version 12.1.0.1.0 - Production 0NLSRTL Version 12.1.0.1.0 - ProductionSQL> select cdb,name,dbid from v$database;CDB NAME DBID--- --------- ----------NO WOO 4199532651
從這裡我們可以看到實際上通過升級之後上來的資料庫還是一個non-CDB,並非CDB資料庫,那麼這個時候我們需要在這個none-CDB庫中產生使用者PDB的XML檔案,再建立CDB資料庫進行插入進去。
二、查看錶空間及資料檔案資訊:
SQL> select * from v$tablespace; TS# NAME INC BIG FLA ENC CON_ID---------- ------------------------------ --- --- --- --- ---------- 0 SYSTEM YES NO YES 0 1 SYSAUX YES NO YES 0 2 UNDOTBS1 YES NO YES 0 4 USERS YES NO YES 0 3 TEMP NO NO YES 0SQL> set line 300SQL> col file_name format a40SQL> col tablespace_name format a10SQL> select tablespace_name,file_name from dba_data_files;TABLESPACE FILE_NAME---------- ----------------------------------------USERS /DBData/woo/woo/users01.dbfUNDOTBS1 /DBData/woo/woo/undotbs01.dbfSYSAUX /DBData/woo/woo/sysaux01.dbfSYSTEM /DBData/woo/woo/system01.dbf
三、建立用於產生PDB的XML檔案
SQL> shutdown immediateDatabase closed.Database dismounted.ORACLE instance shut down.SQL> startup mountORACLE instance started.Total System Global Area 2772574208 bytesFixed Size 2292240 bytesVariable Size 2533361136 bytesDatabase Buffers 218103808 bytesRedo Buffers 18817024 bytesDatabase mounted.SQL> alter database open read only;Database altered.SQL> exec dbms_pdb.describe(pdb_descr_file => \'/home/oracle/woo_ora11g.xml\');PL/SQL procedure successfully completed.SQL> host ls -rtl /home/oracle/woo*.xml-rw-r--r-- 1 oracle oinstall 4147 Nov 27 19:17 /home/oracle/woo_ora11g.xmlSQL> shutdown immediateDatabase closed.Database dismounted.ORACLE instance shut down
四、檢查升級後的資料庫是否適合以PDB的方式插入到CDB中
SQL> set serveroutput on;SQL> declare 2 compat boolean := FALSE; 3 begin 4 compat := dbms_pdb.check_plug_compatibility(pdb_descr_file => \'/home/oracle/woo_ora11g.xml\'); 5 if compat 6 then 7 dbms_output.put_line(\'YES\'); 8 else 9 dbms_output.put_line(\'No\'); 10 end if; 11 end; 12 /NoPL/SQL procedure successfully completed.
因為是第一次插入,所以執行結果顯示為NO,可以忽略繼續插入。
五、建立CDB資料庫
5.1 執行dbca建立cdb資料庫
5.2 指定需要建立的cdb資料庫名稱
5.3 建立CDB前檢查
5.4 Summar 點擊Finish開始建立
5.5 現在正在開始建立
5.6 至此已經建立完成,告訴我們CDB資料庫的資訊
5.7 點擊Close 關閉建立頁面
六、查看當前pdb資訊
[oracle@db01 ~]$ export ORACLE_SID=woo12cSQL> show pdbs; CON_ID CON_NAME OPEN MODE RESTRICTED---------- ------------------------------ ---------- ---------- 2 PDB$SEED READ ONLY NO 3 PDB01 READ WRITE NOSQL> select con_id,dbid,NAME,OPEN_MODE from v$pdbs; CON_ID DBID NAME OPEN_MODE---------- ---------- ------------------------------ ---------- 2 4107385256 PDB$SEED READ ONLY 3 3926295770 PDB01 READ WRITE
七、將non-cdb資料庫插入到cdb中成為一個pdb
SQL> CREATE PLUGGABLE DATABASE woo_ora11g USING \'/home/oracle/woo_ora11g.xml\';Pluggable database created.
八、查看alert日誌相關資訊
將Non-CDB插入到CDB中成為一個PDB輸出的Alert日誌。
SQL> show pdbs CON_ID CON_NAME OPEN MODE RESTRICTED---------- ------------------------------ ---------- ---------- 2 PDB$SEED READ ONLY NO 3 PDB01 READ WRITE NO 4 WOO_ORA11G MOUNTEDSQL> select con_id,dbid,NAME,OPEN_MODE from v$pdbs; CON_ID DBID NAME OPEN_MODE---------- ---------- ------------------------------ ---------- 2 4107385256 PDB$SEED READ ONLY 3 3926295770 PDB01 READ WRITE 4 4199532651 WOO_ORA11G MOUNTED第一次no-cdb plug cdb是mount狀態,需要將其open;SQL> alter pluggable database open;Pluggable database altered.SQL> show pdbs; CON_ID CON_NAME OPEN MODE RESTRICTED---------- ------------------------------ ---------- ---------- 2 PDB$SEED READ ONLY NO 3 PDB01 MOUNTED 4 WOO_ORA11G READ WRITE YES
插入完成之後需要執行noncdb_to_pdb指令碼,修複原non-cdb和新的pdb不相容的問題:
十、執行noncdb_pdb指令碼
SQL> alter session set container=WOO_ORA11G;Session altered.SQL> @$ORACLE_HOME/rdbms/admin/noncdb_to_pdb.sql………#### 遇到warning,指令碼會自動忽略錯誤,繼續執行。在最後指令碼編譯的時候會修複這些問題。更新完之後需要同步pdb資訊SQL> show pdbs CON_ID CON_NAME OPEN MODE RESTRICTED---------- ------------------------------ ---------- ---------- 4 WOO_ORA11G MOUNTEDSQL> alter pluggable database open restricted;Pluggable database altered.SQL> show pdbs CON_ID CON_NAME OPEN MODE RESTRICTED---------- ------------------------------ ---------- ---------- 4 WOO_ORA11G READ WRITE YESSQL> exec dbms_pdb.sync_pdb();PL/SQL procedure successfully completed.SQL> alter pluggable database close immediate;Pluggable database altered.SQL> alter pluggable database open;Pluggable database altered.
十一、至此no-cdb plug to cdb 成功
SQL> show pdbs; CON_ID CON_NAME OPEN MODE RESTRICTED---------- ------------------------------ ---------- ---------- 2 PDB$SEED READ ONLY NO 3 PDB01 MOUNTED 4 WOO_ORA11G READ WRITE NOSQL>