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

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

前言：

從Oracle 12c開始，引入了容器資料庫的概念，可以實現資料庫插拔操作，如下圖:

探索Oracle之資料庫升級七 11gR2 to 12c 升級完成後插入PDB
現在我們就來看看如何將11.2.0.4的資料庫插入到12c的CDP裡面去，讓其成為一個PDB

資料庫。

一、檢視資料庫資訊:

SQL> col BANNER format a80
SQL> set line 300
SQL> select * from v$version;
BANNER CON_ID
-------------------------------------------------------------------------------- ----------
Oracle Database 12c 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
TNS for Linux: Version 12.1.0.1.0 - Production 0
NLSRTL Version 12.1.0.1.0 - Production
SQL> 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 0
SQL> set line 300
SQL> col file_name format a40
SQL> col tablespace_name format a10
SQL> select tablespace_name,file_name from dba_data_files;
TABLESPACE FILE_NAME
---------- ----------------------------------------
USERS /DBData/woo/woo/users01.dbf
UNDOTBS1 /DBData/woo/woo/undotbs01.dbf
SYSAUX /DBData/woo/woo/sysaux01.dbf
SYSTEM /DBData/woo/woo/system01.dbf

三、建立用於生成PDB的XML檔案

SQL> shutdown immediate

Database closed.

Database dismounted.

ORACLE instance shut down.

SQL> startup mount

ORACLE instance started.

Total System Global Area 2772574208 bytes

Fixed Size 2292240 bytes

Variable Size 2533361136 bytes

Database Buffers 218103808 bytes

Redo Buffers 18817024 bytes

Database 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.xml

SQL> shutdown immediate

Database 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 /
No
PL/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=woo12c
SQL> show pdbs;
CON_ID CON_NAME OPEN MODE RESTRICTED
---------- ------------------------------ ---------- ----------
2 PDB$SEED READ ONLY NO
3 PDB01 READ WRITE NO
SQL> 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 日誌。

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資訊

SQL> show pdbs
CON_ID CON_NAME OPEN MODE RESTRICTED
---------- ------------------------------ ---------- ----------
2 PDB$SEED READ ONLY NO
3 PDB01 READ WRITE NO
4 WOO_ORA11G MOUNTED
SQL> 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 MOUNTED
SQL> alter pluggable database open restricted;
Pluggable database altered.
SQL> show pdbs
CON_ID CON_NAME OPEN MODE RESTRICTED
---------- ------------------------------ ---------- ----------
4 WOO_ORA11G READ WRITE YES
SQL> 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 NO
SQL>

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

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

相關文章