Oracle 12c 多租戶 手工建立 pdb 與 手工刪除 pdb
實驗環境:
Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production
一、 手工建立 PDB
SQL>SELECT NAME,CDBFROM V$DATABASE;
SQL>show pdbs
CON_ID CON_NAME OPEN MODE RESTRICTED
---------- ------------------------------ ---------- ----------
2 PDB$SEED READ ONLY NO
3 PDB01 READ WRITE NO
SQL> alter session set container=pdb$seed;
SQL>
set lin 400;
col name for a60;
select * from v$dbfile;
FILE# NAME CON_ID
---------- ------------------------------------------------------------ ----------
5 /home/oracle/app/oracle/oradata/ANDYCDB/datafile/o1_mf_syste 2
m_djsz1z31_.dbf
6 /home/oracle/app/oracle/oradata/ANDYCDB/datafile/o1_mf_sysau 2
x_djsz1z2x_.dbf
8 /home/oracle/app/oracle/oradata/ANDYCDB/datafile/o1_mf_undot 2
bs1_djsz1z34_.dbf
SQL> create pluggable database pdb02 admin user pdb02 identified by pdb02
file_name_convert=('pdbseed','pdb02');
ORA-65005: missing or invalid file name pattern for file -
/home/oracle/app/oracle/oradata/ANDYCDB/datafile/o1_mf_system_djsz1z31_.dbf
SQL> alter session set db_create_file_dest='/home/oracle/app/oracle/oradata/';
SQL> create pluggable database pdb02 admin user pdb02 identified by pdb02;
Pluggable database created.
說明:使用了OMF格式的檔名,目標庫無法使用convert轉換,只能配置db_create_file_dest同樣對於該PDB使用OMF。
SQL> show pdbs
CON_ID CON_NAME OPEN MODE RESTRICTED
---------- ------------------------------ ---------- ----------
2 PDB$SEED READ ONLY NO
3 PDB01 READ WRITE NO
4 PDB02 MOUNTED
SQL> alter pluggable database pdb02 open;
Pluggable database altered.
二、手工刪除 PDB
SQL> show pdbs
CON_ID CON_NAME OPEN MODE RESTRICTED
---------- ------------------------------ ---------- ----------
2 PDB$SEED READ ONLY NO
3 PDB01 READ WRITE NO
4 PDB02 READ WRITE NO
SQL> alter session set container=pdb02;
Session altered.
SQL> select name from v$datafile;
NAME
--------------------------------------------------------------------------------
/home/oracle/app/oracle/oradata/ANDYCDB/ANDYCDB/4F21240B3F45400BE0531019640A46FB
/datafile/o1_mf_system_dk4oy9tr_.dbf
/home/oracle/app/oracle/oradata/ANDYCDB/ANDYCDB/4F21240B3F45400BE0531019640A46FB
/datafile/o1_mf_sysaux_dk4oy9xy_.dbf
/home/oracle/app/oracle/oradata/ANDYCDB/ANDYCDB/4F21240B3F45400BE0531019640A46FB
/datafile/o1_mf_undotbs1_dk4oy9y1_.dbf
SQL> alter session set container=cdb$root;
Session altered.
drop pdb 方式一:including DATAFILES
SQL> drop pluggable database pdb02 including datafiles;
ERROR at line 1: > 報錯說明刪除pdb,這個pdb要在 close 狀態。
ORA-65025: Pluggable database PDB02 is not closed on all instances.
SQL> alter pluggable database pdb02 close immediate;
Pluggable database altered.
SQL> drop pluggable database pdb02 including datafiles;
Pluggable database dropped.
SQL> show pdbs
CON_ID CON_NAME OPEN MODE RESTRICTED
---------- ------------------------------ ---------- ----------
2 PDB$SEED READ ONLY NO
3 PDB01 READ WRITE NO
[oracle@12c01 ~]cd /home/oracle/app/oracle/oradata/ANDYCDB/ANDYCDB/4F21240B3F45400BE0531019640A46FB/datafile
[oracle@12c01 datafile]$ ll
total 0 > 作業系統層對應的檔案也一併刪除了
drop pdb 方式二:keep DATAFILES
SQL> show pdbs
CON_ID CON_NAME OPEN MODE RESTRICTED
---------- ------------------------------ ---------- ----------
2 PDB$SEED READ ONLY NO
3 PDB01 READ WRITE NO
SQL> alter session set container=pdb01;
Session altered.
SQL> select name from v$datafile;
NAME
--------------------------------------------------------------------------------
/home/oracle/app/oracle/oradata/ANDYCDB/4ECF8621E3DA38EEE0531019640AA598/datafil
e/o1_mf_system_djszmxhq_.dbf
/home/oracle/app/oracle/oradata/ANDYCDB/4ECF8621E3DA38EEE0531019640AA598/datafil
e/o1_mf_sysaux_djszmxkb_.dbf
/home/oracle/app/oracle/oradata/ANDYCDB/4ECF8621E3DA38EEE0531019640AA598/datafil
e/o1_mf_users_djszpsmt_.dbf
/home/oracle/app/oracle/oradata/ANDYCDB/4ECF8621E3DA38EEE0531019640AA598/datafil
e/o1_mf_undo_1_djz4jq1z_.dbf
NAME
--------------------------------------------------------------------------------
SQL> alter session set container=cdb$root;
Session altered.
SQL> alter pluggable database pdb01 close immediate;
Pluggable database altered.
SQL>alter pluggable database pdb01 unplug into '/home/oracle/pdb01.xml';
Pluggable database altered.
SQL> drop pluggable database pdb01 keep datafiles;
Pluggable database dropped.
[oracle@12c01 ~]cd /home/oracle/app/oracle/oradata/ANDYCDB/4ECF8621E3DA38EEE0531019640AA598/datafile
[oracle@12c01 datafile]$ ll
total 775720
-rw-r-----. 1 oracle oinstall 408952832 May 10 18:02 o1_mf_sysaux_djszmxkb_.dbf
-rw-r-----. 1 oracle oinstall 272637952 May 10 18:02 o1_mf_system_djszmxhq_.dbf
-rw-r-----. 1 oracle oinstall 104865792 May 10 18:02 o1_mf_undo_1_djz4jq1z_.dbf > 作業系統層對應的檔案沒有被刪除
-rw-r-----. 1 oracle oinstall 7872512 May 10 18:02 o1_mf_users_djszpsmt_.dbf
說明:在drop PDB的時候,有2種方式 keep DATAFILES 和 including DATAFILES,預設是KEEP
Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production
一、 手工建立 PDB
SQL>SELECT NAME,CDBFROM V$DATABASE;
SQL>show pdbs
CON_ID CON_NAME OPEN MODE RESTRICTED
---------- ------------------------------ ---------- ----------
2 PDB$SEED READ ONLY NO
3 PDB01 READ WRITE NO
SQL> alter session set container=pdb$seed;
SQL>
set lin 400;
col name for a60;
select * from v$dbfile;
FILE# NAME CON_ID
---------- ------------------------------------------------------------ ----------
5 /home/oracle/app/oracle/oradata/ANDYCDB/datafile/o1_mf_syste 2
m_djsz1z31_.dbf
6 /home/oracle/app/oracle/oradata/ANDYCDB/datafile/o1_mf_sysau 2
x_djsz1z2x_.dbf
8 /home/oracle/app/oracle/oradata/ANDYCDB/datafile/o1_mf_undot 2
bs1_djsz1z34_.dbf
SQL> create pluggable database pdb02 admin user pdb02 identified by pdb02
file_name_convert=('pdbseed','pdb02');
ORA-65005: missing or invalid file name pattern for file -
/home/oracle/app/oracle/oradata/ANDYCDB/datafile/o1_mf_system_djsz1z31_.dbf
SQL> alter session set db_create_file_dest='/home/oracle/app/oracle/oradata/';
SQL> create pluggable database pdb02 admin user pdb02 identified by pdb02;
Pluggable database created.
說明:使用了OMF格式的檔名,目標庫無法使用convert轉換,只能配置db_create_file_dest同樣對於該PDB使用OMF。
SQL> show pdbs
CON_ID CON_NAME OPEN MODE RESTRICTED
---------- ------------------------------ ---------- ----------
2 PDB$SEED READ ONLY NO
3 PDB01 READ WRITE NO
4 PDB02 MOUNTED
SQL> alter pluggable database pdb02 open;
Pluggable database altered.
二、手工刪除 PDB
SQL> show pdbs
CON_ID CON_NAME OPEN MODE RESTRICTED
---------- ------------------------------ ---------- ----------
2 PDB$SEED READ ONLY NO
3 PDB01 READ WRITE NO
4 PDB02 READ WRITE NO
SQL> alter session set container=pdb02;
Session altered.
SQL> select name from v$datafile;
NAME
--------------------------------------------------------------------------------
/home/oracle/app/oracle/oradata/ANDYCDB/ANDYCDB/4F21240B3F45400BE0531019640A46FB
/datafile/o1_mf_system_dk4oy9tr_.dbf
/home/oracle/app/oracle/oradata/ANDYCDB/ANDYCDB/4F21240B3F45400BE0531019640A46FB
/datafile/o1_mf_sysaux_dk4oy9xy_.dbf
/home/oracle/app/oracle/oradata/ANDYCDB/ANDYCDB/4F21240B3F45400BE0531019640A46FB
/datafile/o1_mf_undotbs1_dk4oy9y1_.dbf
SQL> alter session set container=cdb$root;
Session altered.
drop pdb 方式一:including DATAFILES
SQL> drop pluggable database pdb02 including datafiles;
ERROR at line 1: > 報錯說明刪除pdb,這個pdb要在 close 狀態。
ORA-65025: Pluggable database PDB02 is not closed on all instances.
SQL> alter pluggable database pdb02 close immediate;
Pluggable database altered.
SQL> drop pluggable database pdb02 including datafiles;
Pluggable database dropped.
SQL> show pdbs
CON_ID CON_NAME OPEN MODE RESTRICTED
---------- ------------------------------ ---------- ----------
2 PDB$SEED READ ONLY NO
3 PDB01 READ WRITE NO
[oracle@12c01 ~]cd /home/oracle/app/oracle/oradata/ANDYCDB/ANDYCDB/4F21240B3F45400BE0531019640A46FB/datafile
[oracle@12c01 datafile]$ ll
total 0 > 作業系統層對應的檔案也一併刪除了
drop pdb 方式二:keep DATAFILES
SQL> show pdbs
CON_ID CON_NAME OPEN MODE RESTRICTED
---------- ------------------------------ ---------- ----------
2 PDB$SEED READ ONLY NO
3 PDB01 READ WRITE NO
SQL> alter session set container=pdb01;
Session altered.
SQL> select name from v$datafile;
NAME
--------------------------------------------------------------------------------
/home/oracle/app/oracle/oradata/ANDYCDB/4ECF8621E3DA38EEE0531019640AA598/datafil
e/o1_mf_system_djszmxhq_.dbf
/home/oracle/app/oracle/oradata/ANDYCDB/4ECF8621E3DA38EEE0531019640AA598/datafil
e/o1_mf_sysaux_djszmxkb_.dbf
/home/oracle/app/oracle/oradata/ANDYCDB/4ECF8621E3DA38EEE0531019640AA598/datafil
e/o1_mf_users_djszpsmt_.dbf
/home/oracle/app/oracle/oradata/ANDYCDB/4ECF8621E3DA38EEE0531019640AA598/datafil
e/o1_mf_undo_1_djz4jq1z_.dbf
NAME
--------------------------------------------------------------------------------
SQL> alter session set container=cdb$root;
Session altered.
SQL> alter pluggable database pdb01 close immediate;
Pluggable database altered.
SQL>alter pluggable database pdb01 unplug into '/home/oracle/pdb01.xml';
Pluggable database altered.
SQL> drop pluggable database pdb01 keep datafiles;
Pluggable database dropped.
[oracle@12c01 ~]cd /home/oracle/app/oracle/oradata/ANDYCDB/4ECF8621E3DA38EEE0531019640AA598/datafile
[oracle@12c01 datafile]$ ll
total 775720
-rw-r-----. 1 oracle oinstall 408952832 May 10 18:02 o1_mf_sysaux_djszmxkb_.dbf
-rw-r-----. 1 oracle oinstall 272637952 May 10 18:02 o1_mf_system_djszmxhq_.dbf
-rw-r-----. 1 oracle oinstall 104865792 May 10 18:02 o1_mf_undo_1_djz4jq1z_.dbf > 作業系統層對應的檔案沒有被刪除
-rw-r-----. 1 oracle oinstall 7872512 May 10 18:02 o1_mf_users_djszpsmt_.dbf
說明:在drop PDB的時候,有2種方式 keep DATAFILES 和 including DATAFILES,預設是KEEP
來自 “ ITPUB部落格 ” ,連結:http://blog.itpub.net/31383567/viewspace-2138820/,如需轉載,請註明出處,否則將追究法律責任。
相關文章
- Oracle 12c 手工建立 和 刪除 PDB 示例Oracle
- Oracle 12c 多租戶 CDB 與 PDB 備份Oracle
- Oracle 12c 多租戶在 CDB 中 Plug A PDB,Unplugging A PDBOracle
- Oracle 12c多租戶特性詳解:PDB 的建立、克隆與維護Oracle
- Part III PDB建立概述-Oracle多租戶管理員指南Oracle
- oracle 12c pdb測試:建立、開關、刪除Oracle
- 多租戶:防止意外建立可插拔資料庫(PDB)- Lone-PDB資料庫
- Oracle 12c多租戶特性詳解:PDB 的出與入 InAndOutOracleNaN
- Oracle 12c多租戶特性詳解:PDB 的備份與恢復Oracle
- Oracle 12c 多租戶配置和修改 CDB 和 PDB 引數Oracle
- Oracle 12c 多租戶 CDB 與 PDB之 shared undo 與 Local undo 切換Oracle
- oracle 12c 多租戶 pdb 恢復(單個pdb資料檔案、非系統pdb表空間、整個pdb資料庫)Oracle資料庫
- 12c使用DBCA和手工兩種方式建立PDB時如何正確指定pdb檔案路徑
- Oracle 12C -- 使用seed PDB建立新的pdbOracle
- Oracle12c多租戶如何連線到CDB或PDB、CDB與PDB容器切換Oracle
- Oracle 12c 多租戶 CDB 與 PDB 級別 expdb 與 impdb(表、使用者、全庫)Oracle
- 12C多租戶關於CDB、PDB的常用SQL語句SQL
- Oracle12c多租戶如何啟動關閉CDB或PDB (PDB自動啟動)Oracle
- Oracle 12c系列(二)|PDB的建立Oracle
- 手工刪除oracle資料庫Oracle資料庫
- Oracle 12c 多租戶專題|12cR2中PDB記憶體資源管理Oracle記憶體
- Oracle12c_PDB的拔插與刪除Oracle
- Oracle12c多租戶CDB 與 PDB 引數檔案位置探討、查詢 CDB 與 PDB 不同值的引數Oracle
- Oracle12c日常pdb建立、開關和刪除操作Oracle
- Oracle 12c Relocate PDBOracle
- ORACLE 12C新特性——CDB與PDBOracle
- Oracle 12c 使用Non-CDB來建立PDBOracle
- Oracle 12C 建立使用者連線pdbOracle
- 建立、克隆pdb---oracle 12c 學習(1)Oracle
- 手工建立/刪除資料庫的步驟資料庫
- Oracle 建立PDB-Plugging In an Unplugged PDBOracle
- 【PDB】Oracle 建立pdb說明(create pluggable database)OracleDatabase
- Oracle12c多租戶資料庫備份與恢復 - 恢復一個PDBOracle資料庫
- 多租戶:在Oracle12.2中 從Non-CDB遷移到PDB,從PDB遷移另一個CDB中Oracle
- Oracle 12C RMAN transport tablespace from PDB of RAC CDB to remote PDBOracleREM
- Oracle 12c PDB淺析Oracle
- 手工建立、刪除11gR2資料庫資料庫
- Oracle 12c PDB遷移(一)Oracle