【kingsql分享】Oracle 18c可插拔資料庫艦隊新玩法

1.設定CDB艦隊隊長

SQL> select instance_name from v$instance;

INSTANCE_NAME

----------------

ORCLCDB

SQL> show pdbs;

    CON_ID CON_NAME                       OPEN MODE  RESTRICTED

---------- ------------------------------ ---------- ----------

         2 PDB$SEED                       READ ONLY  NO

         3 ORCLPDB1                       READ WRITE NO

SQL> select file_name from cdb_data_files;

FILE_NAME

--------------------------------------------------------------------------------

/opt/oracle/oradata/ORCLCDB/system01.dbf

/opt/oracle/oradata/ORCLCDB/sysaux01.dbf

/opt/oracle/oradata/ORCLCDB/undotbs01.dbf

/opt/oracle/oradata/ORCLCDB/users01.dbf

/opt/oracle/oradata/ORCLCDB/ORCLPDB1/system01.dbf

/opt/oracle/oradata/ORCLCDB/ORCLPDB1/sysaux01.dbf

/opt/oracle/oradata/ORCLCDB/ORCLPDB1/undotbs01.dbf

/opt/oracle/oradata/ORCLCDB/ORCLPDB1/users01.dbf

SQL> ALTER DATABASE SET lead_cdb = TRUE;

Database altered.

SQL> GRANT sysoper,sysdba to system container=all;

Grant succeeded.

2.設定CDB艦隊成員

SQL> select instance_name from v$instance;

INSTANCE_NAME

----------------

kingdb

SQL> show pdbs;

    CON_ID CON_NAME                       OPEN MODE  RESTRICTED

---------- ------------------------------ ---------- ----------

         2 PDB$SEED                       READ ONLY  NO

         3 PDB2                           READ WRITE NO

SQL> CREATE PUBLIC DATABASE LINK l_orclcdb CONNECT TO system IDENTIFIED BY oracle USING 'ORCLCDB';

Database link created.

SQL> ALTER DATABASE SET lead_cdb_uri='dblink:l_orclcdb';

3.從CDB艦隊隊長來檢視資料庫艦隊

SQL> select instance_name from v$instance;

INSTANCE_NAME

----------------

ORCLCDB

SQL> show pdbs;

    CON_ID CON_NAME                       OPEN MODE  RESTRICTED

---------- ------------------------------ ---------- ----------

         2 PDB$SEED                       READ ONLY  NO

         3 ORCLPDB1                       READ WRITE NO

         5 PDB2                           MOUNTED

4.CDB艦隊成員進行增加PDB操作

SQL> select instance_name from v$instance;

INSTANCE_NAME

----------------

kingdb

SQL> create pluggable database pdb_mem1 admin user kingsql identified by oracle

  2  file_name_convert =('/opt/oracle/oradata/KINGDB/pdbseed','/opt/oracle/oradata/KINGDB/pdb_mem1');

Pluggable database created.

SQL> show pdbs;

    CON_ID CON_NAME                       OPEN MODE  RESTRICTED

---------- ------------------------------ ---------- ----------

         2 PDB$SEED                       READ ONLY  NO

         3 PDB2                           READ WRITE NO

         4 PDB_MEM1                       MOUNTED

SQL> alter pluggable database pdb_mem1 open;

Pluggable database altered.

SQL> show pdbs;

    CON_ID CON_NAME                       OPEN MODE  RESTRICTED

---------- ------------------------------ ---------- ----------

         2 PDB$SEED                       READ ONLY  NO

         3 PDB2                           READ WRITE NO

         4 PDB_MEM1                       READ WRITE NO

5.檢視CDB艦隊隊長資訊

SQL> select instance_name from v$instance;

INSTANCE_NAME

----------------

ORCLCDB

SQL> show pdbs;

    CON_ID CON_NAME                       OPEN MODE  RESTRICTED

---------- ------------------------------ ---------- ----------

         2 PDB$SEED                       READ ONLY  NO

         3 ORCLPDB1                       READ WRITE NO

         5 PDB2                           MOUNTED

         6 PDB_MEM1                       MOUNTED

6.檢視CDB艦隊隊長的資料檔案資訊

SQL> select file_name from cdb_data_files;

FILE_NAME

--------------------------------------------------------------------------------

/opt/oracle/oradata/ORCLCDB/system01.dbf

/opt/oracle/oradata/ORCLCDB/sysaux01.dbf

/opt/oracle/oradata/ORCLCDB/undotbs01.dbf

/opt/oracle/oradata/ORCLCDB/users01.dbf

/opt/oracle/oradata/ORCLCDB/ORCLPDB1/system01.dbf

/opt/oracle/oradata/ORCLCDB/ORCLPDB1/sysaux01.dbf

/opt/oracle/oradata/ORCLCDB/ORCLPDB1/undotbs01.dbf

/opt/oracle/oradata/ORCLCDB/ORCLPDB1/users01.dbf

8 rows selected.

可見，CDB艦隊成員資訊只是邏輯的註冊過來

轉載請註明出處

hongzhuohui@qq.com

本來好幾年沒有更新部落格了，最近我的學生們都在勤勞的寫部落格，所以帶動了我。。

--------------------------------------------------------------------------------------------------------------

我曾發現有寫網站直接複製貼上，連圖片都能複製，請把作者也加上謝謝O(∩_∩)O

--------------------------------------------------------------------------------------------------------------