12c使用DBCA和手工兩種方式建立PDB時如何正確指定pdb檔案路徑

本次測試中會使用到兩個CDB的環境
CDB環境1：

CDB2中的PDB$SEED檔案路徑如下：

+CDB1DG/CDBS/CDB21/cdb2/pdbseed_temp01.dbf => +CDB1DG/CDB2/EF61F2A3DA6D4548E043C838A8C02E9A/TEMPFILE/TEMP.267.836254857

+CDB1DG/CDBS/CDB21/cdb2/pdbseed_system01.dbf => +CDB1DG/CDB2/EF61F2A3DA6D4548E043C838A8C02E9A/DATAFILE/SYSTEM.262.836254795

+CDB1DG/CDBS/CDB21/cdb2/pdbseed_sysaux01.dbf => +CDB1DG/CDB2/EF61F2A3DA6D4548E043C838A8C02E9A/DATAFILE/SYSAUX.264.836254841

 

CDB環境2：

ORA12C 中的PDB$SEED檔案路徑如下：

+DATADG1/ORA12C/DD7C48AA5A4404A2E04325AAE80A403C/DATAFILE/pdbseed_temp01.dbf

+DATADG1/ORA12C/DD7C48AA5A4404A2E04325AAE80A403C/DATAFILE/sysaux.268.828826479

+DATADG1/ORA12C/DD7C48AA5A4404A2E04325AAE80A403C/DATAFILE/system.269.828826479

 

測試目標：摸索manually create pdb時如何正確指定file_name_covert引數、展示dbca方式建庫時oracle自動轉換pdb檔案路徑的特性

 

場景一（DBCA）：輸入” +cdb1dg”作為建立路徑，Oracle生成的建立語句如下：

CREATE PLUGGABLE DATABASE pdb21 ADMIN USER pdb21_admin IDENTIFIED BY * ROLES=(CONNECT)  file_name_convert=('+CDB1DG/CDBS/CDB21/cdb2/pdbseed_system01.dbf','+CDB1DG/pdb21_system01.dbf',

'+CDB1DG/CDBS/CDB21/cdb2/pdbseed_sysaux01.dbf','+CDB1DG/pdb21_sysaux01.dbf','+CDB1DG/CDBS/CDB21/cdb2/pdbseed_temp01.dbf','+CDB1DG/pdb21_temp01.dbf')

 

場景一（Manual）：執行命令：

---指定的源路徑和目標路徑一致，讓oracle誤以為在源路徑上覆蓋建立pdb，所以報錯

CREATE PLUGGABLE DATABASE pdb21 ADMIN USER pdb21_admin IDENTIFIED BY * ROLES=(CONNECT)  file_name_convert=('+CDB1DG','+CDB1DG');

CREATE PLUGGABLE DATABASE pdb21 ADMIN USER pdb21_admin IDENTIFIED BY * ROLES=(CONNECT)  file_name_convert=('+CDB1DG','+CDB1DG')

*

ERROR at line 1:

ORA-01537: cannot add file '+CDB1DG/CDBS/CDB21/cdb2/pdbseed_system01.dbf' -

file already part of database

 

--將源路徑具體化到datafile的上層路徑後建立成功：

SQL> create pluggable database pdb21 admin user pdb21_admin identified by 773946 roles=(connect) file_name_convert=('+CDB1DG/CDBS/CDB21/cdb2/','+CDB1DG/');

 

Pluggable database created.

——————————檢視pdb21的檔案路徑，發現建立在了+CDB1dg /目錄下——————————

+cdb1dg/pdbseed_sysaux01.dbf => +CDB1DG/CDB2/EFC6F3C9DA61745DE043C838A8C0C436/DATAFILE/SYSAUX.270.836688623

+cdb1dg/pdbseed_system01.dbf => +CDB1DG/CDB2/EFC6F3C9DA61745DE043C838A8C0C436/DATAFILE/SYSTEM.269.836688613

+cdb1dg/pdbseed_temp01.dbf => +CDB1DG/CDB2/EFC6F3C9DA61745DE043C838A8C0C436/TEMPFILE/TEMP.271.836688637

 

--或者將源路徑具體化到datafile的上兩層路徑後建立成功：

SQL> create pluggable database pdb21 admin user pdb21_admin identified by 773946 roles=(connect) file_name_convert=('+CDB1DG/CDBS/CDB21/','+CDB1DG/');

 

Pluggable database created.

——————————檢視pdb21的檔案路徑，發現建立在了+CDB1dg/cdb2/目錄下——————————

+CDB1dg/cdb2/pdbseed_sysaux01.dbf => +CDB1DG/CDB2/EFC6F3C9DA62745DE043C838A8C0C436/DATAFILE/SYSAUX.270.836688979

+CDB1dg/cdb2/pdbseed_system01.dbf => +CDB1DG/CDB2/EFC6F3C9DA62745DE043C838A8C0C436/DATAFILE/SYSTEM.269.836688969

+CDB1dg/cdb2/pdbseed_temp01.dbf => +CDB1DG/CDB2/EFC6F3C9DA62745DE043C838A8C0C436/TEMPFILE/TEMP.271.836688993

 

--再或者將源路徑具體化到datafile的上三層路徑後建立也能成功：

SQL> create pluggable database pdb21 admin user pdb21_admin identified by 773946 roles=(connect) file_name_convert=('+CDB1DG/CDBS/','+CDB1DG/');

*

  at line 1:

ORA-00604: error occurred at recursive SQL level

ORA-19504: failed to create file "+CDB1DG/CDB21/cdb2/pdbseed_system01.dbf"

 

--建立出+CDB1DG/cdb21/cdb2的目錄後，執行成功：

ASMCMD> cd +CDB1DG/

ASMCMD> mkdir cdb21

ASMCMD> mkdir cdb21/cdb2

ASMCMD> ls -ld +cdb1dg/cdb21/cdb2

Type  Redund  Striped  Time             Sys  Name

                                        N    cdb2/

 

SQL> create pluggable database pdb21 admin user pdb21_admin identified by 773946 roles=(connect) file_name_convert=('+CDB1DG/CDBS/','+CDB1DG/');

 

Pluggable database created.

結論：DBCA方式下輸入的只是目標路徑，oracle能自動識別PDB$SEED的儲存源路徑並逐個資料檔案的與目標路徑下的資料檔案形成一一對應關係，而手動方式建立時若源和目標路徑完全一樣時會引導oracle將目標pdbfile覆蓋原始檔，從而報錯，正確的方式是指定到源資料檔案的上N層路徑、且當N>1時必須要保證目標DG下事先要有這個目錄。

 

 

場景二（DBCA）：輸入” +CDB2DG/pdb21/”作為建立路徑(或者”+CDB2DG/pdb21”也可)，Oracle生成的建立語句如下：

CREATE PLUGGABLE DATABASE pdb21 ADMIN USER pdb21_admin IDENTIFIED BY * ROLES=(CONNECT)  file_name_convert=('+CDB1DG/CDBS/CDB21/cdb2/pdbseed_system01.dbf','+CDB2DG/pdb21//pdb21_system01.dbf',

'+CDB1DG/CDBS/CDB21/cdb2/pdbseed_sysaux01.dbf','+CDB2DG/pdb21//pdb21_sysaux01.dbf','+CDB1DG/CDBS/CDB21/cdb2/pdbseed_temp01.dbf','+CDB2DG/pdb21//pdb21_temp01.dbf')

 

場景二（Manual）：手工指定” +CDB2DG/pdb21/”作為目標路徑：

CREATE PLUGGABLE DATABASE pdb21 ADMIN USER pdb21_admin IDENTIFIED BY * ROLES=(CONNECT)  file_name_convert=('+CDB1DG/CDBS/CDB21/cdb2/',' ');

 

Pluggable database created.

 

結論：DBCA方式下輸入的目標路徑可以不帶/，但手工方式下輸入的目標路徑必須帶/。

 

場景三（DBCA）：在ORA12C下建立名為orapdbc的pdb，指定+SYSDG作為目標路徑，Oracle生成的建立語句如下：

CREATE PLUGGABLE DATABASE orapdbc ADMIN USER c_admin IDENTIFIED BY * ROLES=(CONNECT)  file_name_convert=('+DATADG1/ORA12C/DD7C48AA5A4404A2E04325AAE80A403C/DATAFILE/system.269.828826479','+SYSDG/orapdbc_system.dbf','+DATADG1/ORA12C/DD7C48AA5A4404A2E04325AAE80A403C/DATAFILE/sysaux.268.828826479','+SYSDG/orapdbc_sysaux.dbf','+DATADG1/ORA12C/DD7C48AA5A4404A2E04325AAE80A403C/DATAFILE/pdbseed_temp01.dbf','+SYSDG/orapdbc_temp01.dbf')

 

場景三（Manual）：在ORA12C下手工建立名為orapdbc的pdb，指定+SYSDG作為目標路徑

CREATE PLUGGABLE DATABASE orapdbc ADMIN USER c_admin IDENTIFIED BY 773946 FILE_NAME_CONVERT=('+DATADG1', '+SYSDG') STORAGE UNLIMITED;

CREATE PLUGGABLE DATABASE orapdbc ADMIN USER c_admin IDENTIFIED BY 773946 FILE_NAME_CONVERT=('+DATADG1', '+SYSDG') STORAGE UNLIMITED

*

  at line 1:

ORA-01119: error in creating database file

'+SYSDG/ORA12C/DD7C48AA5A4404A2E04325AAE80A403C/DATAFILE/pdbseed_temp01.dbf'

ORA-17502: ksfdcre:4 Failed to create file

+SYSDG/ORA12C/DD7C48AA5A4404A2E04325AAE80A403C/DATAFILE/pdbseed_temp01.dbf

ORA-15173: entry 'DD7C48AA5A4404A2E04325AAE80A403C' does not exist in directory

'ORA12C'

 

---手工建立"+SYSDG/ORA12C/DD7C48AA5A4404A2E04325AAE80A403C/DATAFILE/"目錄後再次執行後成功：

SQL> CREATE PLUGGABLE DATABASE orapdbc ADMIN USER c_admin IDENTIFIED BY 773946 FILE_NAME_CONVERT=('+DATADG1', '+SYSDG/');

 

Pluggable database created.

————檢視orapdbc的檔案路徑，發現temp檔案被當做了一個user-generated file指向TEMPFILE目錄————

+SYSDG/ORA12C/DD7C48AA5A4404A2E04325AAE80A403C/DATAFILE/pdbseed_temp01.dbf=> +SYSDG/ora12c/EFC2E36477614330E0436438A8C0565D/TEMPFILE/TEMP.259.836672729

+sysdg/ora12c/EFC2E36477614330E0436438A8C0565D/datafile/SYSAUX.257.836672687

+sysdg/ora12c/EFC2E36477614330E0436438A8C0565D/datafile/SYSTEM.258.836672671

 

--或者將源路徑指定到上一層的方式，也能成功：

SQL> CREATE PLUGGABLE DATABASE orapdbc ADMIN USER c_admin IDENTIFIED BY 773946 FILE_NAME_CONVERT=('+DATADG1/ORA12C/DD7C48AA5A4404A2E04325AAE80A403C/DATAFILE/', '+SYSDG/');

 

Pluggable database created.

 

————檢視orapdbc的檔案路徑，發現temp檔案被當做了一個user-generated file指向TEMPFILE目錄————

+SYSDG/ORA12C/EFC7FFC41D766A8BE0436438A8C0873B/DATAFILE/system.258.836693083

+SYSDG/ORA12C/EFC7FFC41D766A8BE0436438A8C0873B/DATAFILE/sysaux.257.836693099

+sysdg/pdbseed_temp01.dbf => +SYSDG/ORA12C/EFC7FFC41D766A8BE0436438A8C0873B/TEMPFILE/TEMP.259.836693141

結論：DBCA方式下只要確保目標路徑存在總能成功，而手工方式下遇到system、sysaux這類System generated的檔案是沒有連結的，tempfile不知為何被當成了user generated的檔案了，透過一個連結才指向真正的檔案。使用DBCA建立完後pdb會被自動的open，Manual方式不會