oralce 18c 创建PDB几种方式

亦凉 2022-05-10 19:06 224阅读 0赞

原文地址链接:[https://docs.oracle.com/en/database/oracle/oracle-database/18/multi/creating-a-pdb-from-scratch.html\#GUID-534B3B1D-0ED7-4828-9CA4-B1757398801F][https_docs.oracle.com_en_database_oracle_oracle-database_18_multi_creating-a-pdb-from-scratch.html_GUID-534B3B1D-0ED7-4828-9CA4-B1757398801F]

**1.利用seed(种子)模板来创建**

使用CREATE PLUGGABLE DATABASE语句使用PDB种子（PDB $ SEED）的文件在CDB中创建PDB。

您还可以使用此语句使用应用程序种子或PDB种子的文件在应用程序容器中创建应用程序PDB

*  使用PDB $ SEED文件在CDB根目录中创建PDB

![70][]

*  使用应用程序种子文件在应用程序根目录中创建PDB

![70 1][]

下面介绍具体的创建方法:

SQL> create pluggable database pdb1 admin user pdb1test1 identified by pdb1test1 file_name_convert=('/u01/oradata/CDB/pdbseed','/u01/oradata/pdb1');
    
    
    Pluggable database created.

![70 2][]

**2.克隆已有的PDB/non-CDB**

原文地址链接:[https://docs.oracle.com/en/database/oracle/oracle-database/18/multi/cloning-a-pdb.html\#GUID-8A6287A1-14E5-4B45-972F-FC2FB3CDCF8B][https_docs.oracle.com_en_database_oracle_oracle-database_18_multi_cloning-a-pdb.html_GUID-8A6287A1-14E5-4B45-972F-FC2FB3CDCF8B]

要从源PDB或非CDB克隆PDB，请使用CREATE PLUGGABLE DATABASE语句。

该技术将源PDB或非CDB克隆为新的PDB，并自动将克隆插入CDB。 要使用此技术，必须包含指定源的FROM子句。 源是您要克隆的现有PDB或非CDB。 目标PDB是源的克隆。 来源可以是以下任何一种：

*  本地PDB

*  PDB在远程CDB中

*   非CDB

CREATE PLUGGABLE DATABASE语句将与源关联的文件复制到新位置，并将文件与目标PDB关联。 当CDB处于ARCHIVELOG模式和本地撤消模式时，源PDB可以在读/写模式下打开并在克隆过程中运行。 这种技术称为热克隆。

注意:

如果克隆PDB，并且源数据库具有加密数据或密钥库集，则必须通过在CREATE PLUGGABLE DATABASE ... FROM SQL语句中包含由keystore\_password子句标识的密钥库来提供密钥库密码。 即使源数据库使用自动登录软件密钥库，您也必须提供此密码。 您可以通过查询DBA\_ENCRYPTED\_COLUMNS数据字典视图来确定源数据库是否具有加密数据或密钥库。

**克隆本地PDB**

Prerequisites

You must meet the following prerequisites:

*  Complete the prerequisites described in "[General Prerequisites for PDB Creation][]".
 *  The current user must have the CREATE PLUGGABLE DATABASE system privilege in both the root and the source PDB.
 *  The source PDB cannot be closed.
 *  If the CDB is not in local undo mode, then the source PDB must be in open read-only mode. This requirement does not apply if the CDB is in local undo mode.
 *  If the CDB is not in ARCHIVELOG mode, then the source PDB must be in open read-only mode. This requirement does not apply if the CDB is in ARCHIVELOG mode.
 *  If you are creating an application PDB, then the application PDB must have the same character set and national character set as the application container.

If the database character set of the CDB is AL32UTF8, then the character set and national character set of the application container can be different from the CDB. However, all application PDBs in an application container must have same character set and national character set, matching that of the application container.

To clone a local PDB:

1.  In SQL\*Plus, ensure that the current container is the CDB root or an application root.

When the current container is the CDB root, the PDB is created in the CDB. When the current container is an application root, the application PDB is created in the application container.

1.  Run the CREATE PLUGGABLE DATABASE statement, and specify the source PDB in the FROM clause. Specify other clauses when required.

After you create the PDB, it is in mounted mode, and its status is NEW. You can view the open mode of a PDB by querying the OPEN\_MODE column in the V$PDBS view. You can view the status of a PDB by querying the STATUS column of the CDB\_PDBS or DBA\_PDBS view.

A new default service is created for the PDB. The service has the same name as the PDB and can be used to access the PDB. Oracle Net Services must be configured properly for clients to access this service.

1.  Open the new PDB in read/write mode.

You must open the new PDB in read/write mode for Oracle Database to complete the integration of the new PDB into the CDB. An error is returned if you attempt to open the PDB in read-only mode. After the PDB is opened in read/write mode, its status is NORMAL.

1.  Back up the PDB.

A PDB cannot be recovered unless it is backed up.

注意:

如果在创建PDB期间返回错误，则正在创建的PDB可能处于UNUSABLE状态。 您可以通过查询CDB\_PDBS或DBA\_PDBS视图来检查PDB的状态，并通过检查警报日志了解有关PDB创建错误的更多信息。 只能删除不可用的PDB，并且必须先删除它，然后才能创建与不可用的PDB同名的PDB。

Cloning a Local PDB Using No Clauses: Example

This example shows the simplest way to clone a PDB.

This example assumes the following factors:

*  The PATH\_PREFIX clause is not required.
 *  The FILE\_NAME\_CONVERT clause and the CREATE\_FILE\_DEST clause are not required.

Either Oracle Managed Files is enabled, or the PDB\_FILE\_NAME\_CONVERT initialization parameter is set. Therefore, the FILE\_NAME\_CONVERT clause is not required. The files will be copied to a new location based on the Oracle Managed Files configuration or the initialization parameter setting.

*  Storage limits are not required for the PDB. Therefore, the STORAGE clause is not required.
 *  There is no file with the same name as the new temp file that will be created in the target location. Therefore, the TEMPFILE REUSE clause is not required.

The following statement clones the pdb2 PDB from the pdb1 PDB:

CREATE PLUGGABLE DATABASE pdb2 FROM pdb1;

例如:

SQL> create pluggable database pdb2 from pdb1 file_name_convert=('/u01/oradata/pdb1','/u02/oradata/pdb2');
    
    
    Pluggable database created.

![70 3][]

**Cloning a Local PDB Using DBCA: Example**

This example clones a PDB using the silent mode of DBCA. Hot cloning is supported.

This example assumes the following factors:

*  The source CDB is a single-instance database with the SID orcl.
 *  The source PDB is pdb1. You intend for pdb1 to remain open during the cloning operation, which means that local undo and ARCHIVELOG mode are enabled in the CDB. Otherwise, DBCA closes the PDB during the clone operation, and after receiving confirmation, opens the source PDB in read-only mode.

(源PDB是pdb1。 您希望pdb1在克隆操作期间保持打开状态，这意味着在CDB中启用了本地undo和ARCHIVELOG模式。 否则，DBCA在克隆操作期间关闭PDB，并在收到确认后，然后以只读模式打开以执行克隆操作)

*  The new PDB is pdb2.
 *  You are running DBCA in noninteractive mode.

The following command clones the pdb2 PDB from the pdb1 PDB:

./dbca -silent -create pluggable database -source db orcl -create pdb from PDB -pdbName pdb2 -sourcepdb pdb1

**第一种情况：在shared undo**

SQL> archive log list;
    
    Database log mode Archive Mode
    
    Automatic archival Enabled
    
    Archive destination /home/u01/app/oracle/product/18c/db_1/18c/dbs/arch
    
    Oldest online log sequence 30
    
    Next log sequence to archive 32
    
    Current log sequence 32
    
    SQL> col PROPERTY_NAME for a25;
    
    SQL> col PROPERTY_VALUE for a25;
    
    SQL> select PROPERTY_NAME,PROPERTY_VALUE from database_properties where property_name='LOCAL_UNDO_ENABLED';
    
    
    PROPERTY_NAME PROPERTY_VALUE
    
    ------------------------- -------------------------
    
    LOCAL_UNDO_ENABLED FALSE
    
    
    [oracle@foidndb ~]$ dbca -silent -createpluggabledatabase -sourcedb shiyu -createpdbfrom PDB -pdbName salespdb2 -sourcepdb salespdb
    
    [WARNING] [DBT-15030] 将关闭插接式数据库 (salespdb), 然后以只读模式打开以执行克隆操作。
    
    准备执行数据库操作
    
    已完成 13%
    
    正在创建插接式数据库
    
    已完成 15%
    
    已完成 19%
    
    已完成 23%
    
    已完成 31%
    
    已完成 53%
    
    正在完成创建插接式数据库
    
    已完成 60%
    
    执行配置后操作
    
    已完成 100%
    
    已成功插入插接式数据库 "salespdb2"。
    
    
    SQL> show pdbs
    
    
    CON_ID CON_NAME OPEN MODE RESTRICTED
    
    ---------- ------------------------------ ---------- ----------
    
    2 PDB$SEED READ ONLY NO
    
    3 SALESPDB MOUNTED
    
    4 SALESPDB2 READ WRITE NO

**第二种情况:在local undo**

[oracle@oracle18c ~]$ sqlplus / as sysdba
    
    
    SQL*Plus: Release 18.0.0.0.0 - Production on Tue Sep 11 21:18:14 2018
    
    Version 18.3.0.0.0
    
    
    Copyright (c) 1982, 2018, Oracle. All rights reserved.
    
    
    
    Connected to:
    
    Oracle Database 18c Enterprise Edition Release 18.0.0.0.0 - Production
    
    Version 18.3.0.0.0
    
    
    SQL> archive log list;
    
    Database log mode Archive Mode
    
    Automatic archival Enabled
    
    Archive destination /u01/app/oracle/product/18.0.0/dbhome_1/dbs/arch
    
    Oldest online log sequence 27
    
    Next log sequence to archive 29
    
    Current log sequence 29
    
    SQL> col PROPERTY_NAME for a25;
    
    SQL> col PROPERTY_VALUE for a25;
    
    SQL> select PROPERTY_NAME,PROPERTY_VALUE from database_properties where property_name='LOCAL_UNDO_ENABLED';
    
    
    PROPERTY_NAME PROPERTY_VALUE
    
    ------------------------- -------------------------
    
    LOCAL_UNDO_ENABLED TRUE
    
    
    SQL> exit
    
    Disconnected from Oracle Database 18c Enterprise Edition Release 18.0.0.0.0 - Production
    
    Version 18.3.0.0.0
    
    [oracle@oracle18c ~]$ dbca -silent -createpluggabledatabase -sourcedb cdb -createpdbfrom PDB -pdbName pdb2 -sourcepdb pdb1
    
    Prepare for db operation
    
    13% complete
    
    Creating Pluggable Database
    
    15% complete
    
    19% complete
    
    23% complete
    
    31% complete
    
    53% complete
    
    Completing Pluggable Database Creation
    
    60% complete
    
    Executing Post Configuration Actions
    
    100% complete
    
    
    SQL> show pdbs
    
    
    CON_ID CON_NAME OPEN MODE RESTRICTED
    
    ---------- ------------------------------ ---------- ----------
    
    2 PDB$SEED READ ONLY NO
    
    3 PDB1 READ WRITE NO
    
    4 PDB2 READ WRITE NO

**Cloning a Local PDB with the NO DATA Clause: Example**

This example clones the data model definition of the PDB, but does not clone the data in the PDB.

This example assumes the following factors:

*  The NO DATA clause is required because the goal is to clone the data model definition of the source PDB without cloning its data.
 *  The PATH\_PREFIX clause is not required.
 *  The FILE\_NAME\_CONVERT clause and the CREATE\_FILE\_DEST clause are not required.

Either Oracle Managed Files is enabled, or the PDB\_FILE\_NAME\_CONVERT initialization parameter is set. Therefore, the FILE\_NAME\_CONVERT clause is not required. The process copies the files to a new location based on the Oracle Managed Files configuration or the initialization parameter setting.

具体测试如下:

1.准备数据:

切记切记记住 no data 对于数据存放在 SYSTEM 上是无效的。

SQL> show pdbs;
    
    
    CON_ID CON_NAME OPEN MODE RESTRICTED
    
    ---------- ------------------------------ ---------- ----------
    
    2 PDB$SEED READ ONLY NO
    
    5 PDB1 READ WRITE NO
    
    SQL> alter session set container=pdb1;
    
    
    Session altered.
    
    
    SQL> select name from v$datafile;
    
    
    NAME
    
    --------------------------------------------------------------------------------
    
    /u01/oradata/pdb1/system01.dbf
    
    /u01/oradata/pdb1/sysaux01.dbf
    
    /u01/oradata/pdb1/system01_i1_undo.dbf
    
    
    SQL> create tablespace test datafile '/u01/oradata/pdb1/test01.bdf' size 32M autoextend on next 32M maxsize unlimited;
    
    
    Tablespace created.
    
    
    SQL> select name from v$datafile;
    
    
    NAME
    
    --------------------------------------------------------------------------------
    
    /u01/oradata/pdb1/system01.dbf
    
    /u01/oradata/pdb1/sysaux01.dbf
    
    /u01/oradata/pdb1/system01_i1_undo.dbf
    
    /u01/oradata/pdb1/test01.bdf
    
    
    SQL> create user test identified by oracle default tablespace test;
    
    
    User created.
    
    
    SQL> grant dba to test;
    
    
    Grant succeeded.
    
    
    SQL> create table test.test(id int);
    
    
    Table created.
    
    
    SQL> insert into test.test values(1);
    
    
    1 row created.
    
    
    SQL> insert into test.test values(2);
    
    
    1 row created.
    
    
    SQL> insert into test.test values(3);
    
    
    1 row created.
    
    
    SQL> commit;
    
    
    Commit complete.
    
    SQL> col table_name for a20
    
    SQL> select table_name,tablespace_name from dba_tables where owner='TEST';
    
    
    TABLE_NAME TABLESPACE_NAME
    
    -------------------- ------------------------------
    
    TEST TEST
    
    
    SQL> conn test/oracle@pdb1
    
    Connected.
    
    SQL> select * from test;
    
    
    ID
    
    ----------
    
    1
    
    2
    
    3

2.将源pdb1 设置成只读模式

切记切记！！！！，这个步骤一定要做 不然会报：

ORA-65359: unable to create pluggable database with no data
    
    
    SQL> show pdbs;
    
    
    CON_ID CON_NAME OPEN MODE RESTRICTED
    
    ---------- ------------------------------ ---------- ----------
    
    2 PDB$SEED READ ONLY NO
    
    5 PDB1 READ WRITE NO
    
    
    SQL> alter pluggable database pdb1 close;
    
    
    Pluggable database altered.
    
    
    SQL> show pdbs;
    
    
    CON_ID CON_NAME OPEN MODE RESTRICTED
    
    ---------- ------------------------------ ---------- ----------
    
    2 PDB$SEED READ ONLY NO
    
    5 PDB1 MOUNTED
    
    SQL> alter pluggable database pdb1 open read only;
    
    
    Pluggable database altered.
    
    
    SQL> show pdbs;
    
    
    CON_ID CON_NAME OPEN MODE RESTRICTED
    
    ---------- ------------------------------ ---------- ----------
    
    2 PDB$SEED READ ONLY NO
    
    5 PDB1 READ ONLY NO

3.通过pdb1 创建pdb2

SQL> create pluggable database pdb2 from pdb1 no data file_name_convert=('/u01/oradata/pdb1','/u02/oradata/pdb2');
    
    
    Pluggable database created.

3.打开pdb2

SQL> show pdbs
    
    
    CON_ID CON_NAME OPEN MODE RESTRICTED
    
    ---------- ------------------------------ ---------- ----------
    
    2 PDB$SEED READ ONLY NO
    
    3 PDB2 MOUNTED
    
    5 PDB1 READ ONLY NO
    
    SQL> alter pluggable database pdb2 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
    
    5 PDB1 READ ONLY NO

4.确认pdb2

SQL> alter session set container=pdb2;
    
    
    Session altered.
    
    
    SQL> show con_name
    
    
    CON_NAME
    
    ------------------------------
    
    PDB2
    
    SQL> select * from test.test;
    
    
    no rows selected
    
    
    SQL> select count(*) from test.test;
    
    
    COUNT(*)
    
    ----------
    
    0

5，最后记住将pdb1 改成read write 模式

SQL> show pdbs
    
    
    CON_ID CON_NAME OPEN MODE RESTRICTED
    
    ---------- ------------------------------ ---------- ----------
    
    2 PDB$SEED READ ONLY NO
    
    3 PDB2 READ WRITE NO
    
    5 PDB1 READ ONLY NO
    
    SQL> alter pluggable database pdb1 close;
    
    
    Pluggable database altered.
    
    
    SQL> show pdbs
    
    
    CON_ID CON_NAME OPEN MODE RESTRICTED
    
    ---------- ------------------------------ ---------- ----------
    
    2 PDB$SEED READ ONLY NO
    
    3 PDB2 READ WRITE NO
    
    5 PDB1 MOUNTED
    
    SQL> alter pluggable database pdb1 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
    
    5 PDB1 READ WRITE NO
    
    SQL>

**Creating a Point-in-Time Clone of a PDB Snapshot: Example**

A PDB snapshot is a point-in-time copy of a PDB. You can create snapshots manually using the SNAPSHOT clause of CREATE PLUGGABLE DATABASE (or ALTER PLUGGABLE DATABASE), or automatically using the EVERY interval clause. This example demonstrates how to clone a PDB snapshot.

This example assumes the following factors:

*  A PDB snapshot carousel exists with 8 daily snapshots, named after the weekday, day of the month, and time when they were created: pdb1\_mon\_2\_1201, pdb1\_tue\_3\_1201, pdb1\_wed\_4\_1201, and so on.
 *  The new PDB will be a clone of a snapshot named pdb1\_wed\_4\_1201, which is a snapshot of pdb1 taken last Wednesday on the 4th of the month at 12:01 a.m.
 *  The PATH\_PREFIX clause is not required.
 *  The FILE\_NAME\_CONVERT clause and the CREATE\_FILE\_DEST clause are not required.

The following statement clones the pdb1\_copy PDB from the PDB snapshot named

SQL> alter session set container=pdb1;
    
    
    Session altered.
    
    
    SQL> set linesize 150
    
    SQL> col con_name format a10
    
    SQL> col snapshot_name format a30
    
    SQL> col snap_scn format 9999999
    
    SQL> col full_snapshot_path format a55
    
    SQL> select con_id, con_name, snapshot_name,snapshot_scn as snap_scn, full_snapshot_path from dba_pdb_snapshots order by snap_scn;
    
    
    CON_ID CON_NAME SNAPSHOT_NAME SNAP_SCN FULL_SNAPSHOT_PATH
    
    ---------- ---------- ------------------------------ -------- -------------------------------------------------------
    
    5 PDB1 PDB1_TEST1 1711651 /u01/oradata/pdb1/snap_70485319_1711651.pdb

![70 4][]

SQL> alter system set db_create_file_dest='/u02/oradata/pdb1_copy';
    
    
    System altered.
    
    
    SQL> show pdbs
    
    
    CON_ID CON_NAME OPEN MODE RESTRICTED
    
    ---------- ------------------------------ ---------- ----------
    
    2 PDB$SEED READ ONLY NO
    
    5 PDB1 READ WRITE NO
    
    SQL> show parameter db_create_file_dest;
    
    
    NAME TYPE VALUE
    
    ------------------------------------ ----------- ------------------------------
    
    db_create_file_dest string /u02/oradata/pdb1_copy
    
    SQL> create pluggable database pdb1_copy from pdb1 using snapshot pdb1_test1;
    
    
    Pluggable database created.

![70 5][]

这里先测试到这里，其它的创建方式，以后再继续补充！！

[https_docs.oracle.com_en_database_oracle_oracle-database_18_multi_creating-a-pdb-from-scratch.html_GUID-534B3B1D-0ED7-4828-9CA4-B1757398801F]: https://docs.oracle.com/en/database/oracle/oracle-database/18/multi/creating-a-pdb-from-scratch.html#GUID-534B3B1D-0ED7-4828-9CA4-B1757398801F
[70]: /images/20220505/ed2cfd24fbc642fc9846b02e291219d0.png
[70 1]: /images/20220505/b293125ff84d4ef382064f570ef6a4f1.png
[70 2]: /images/20220505/de48bab3911f45559aeb0b0b3ca13d22.png
[https_docs.oracle.com_en_database_oracle_oracle-database_18_multi_cloning-a-pdb.html_GUID-8A6287A1-14E5-4B45-972F-FC2FB3CDCF8B]: https://docs.oracle.com/en/database/oracle/oracle-database/18/multi/cloning-a-pdb.html#GUID-8A6287A1-14E5-4B45-972F-FC2FB3CDCF8B
[General Prerequisites for PDB Creation]: https://docs.oracle.com/en/database/oracle/oracle-database/18/multi/overview-of-pdb-creation.html#GUID-B26D56E1-B958-444F-864C-8AFDFF1566A6
[70 3]: /images/20220505/ce01f5346c0e49d381eb494e40505891.png
[70 4]: /images/20220505/cef8b4dd5d504711b72a2580f25b57fe.png
[70 5]: /images/20220505/3f426d844b084ee2a369f3a9793bb0a7.png