oralce 18c 创建PDB几种方式-白红宇

oralce 18c 创建PDB几种方式

阅读量：4070 次

发布时间：2019-05-25

本文共 14958 字，大约阅读时间需要 49 分钟。

原文地址链接:

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

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

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

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

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

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

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

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

原文地址链接:

要从源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 "".

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:

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.

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.

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.

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.

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 ModeAutomatic archival EnabledArchive destination /home/u01/app/oracle/product/18c/db_1/18c/dbs/archOldest online log sequence 30Next log sequence to archive 32Current log sequence 32SQL> 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 pdbsCON_ID CON_NAME OPEN MODE RESTRICTED---------- ------------------------------ ---------- ----------2 PDB$SEED READ ONLY NO3 SALESPDB MOUNTED4 SALESPDB2 READ WRITE NO

第二种情况:在local undo

[oracle@oracle18c ~]$ sqlplus / as sysdbaSQL*Plus: Release 18.0.0.0.0 - Production on Tue Sep 11 21:18:14 2018Version 18.3.0.0.0Copyright (c) 1982, 2018, Oracle. All rights reserved.Connected to:Oracle Database 18c Enterprise Edition Release 18.0.0.0.0 - ProductionVersion 18.3.0.0.0SQL> archive log list;Database log mode Archive ModeAutomatic archival EnabledArchive destination /u01/app/oracle/product/18.0.0/dbhome_1/dbs/archOldest online log sequence 27Next log sequence to archive 29Current log sequence 29SQL> 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 TRUESQL> exitDisconnected from Oracle Database 18c Enterprise Edition Release 18.0.0.0.0 - ProductionVersion 18.3.0.0.0[oracle@oracle18c ~]$ dbca -silent -createpluggabledatabase -sourcedb cdb -createpdbfrom PDB -pdbName pdb2 -sourcepdb pdb1Prepare for db operation13% completeCreating Pluggable Database15% complete19% complete23% complete31% complete53% completeCompleting Pluggable Database Creation60% completeExecuting Post Configuration Actions100% completeSQL> show pdbsCON_ID CON_NAME OPEN MODE RESTRICTED---------- ------------------------------ ---------- ----------2 PDB$SEED READ ONLY NO3 PDB1 READ WRITE NO4 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.

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.

具体测试如下:

1.准备数据:

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

SQL> show pdbs;CON_ID CON_NAME OPEN MODE RESTRICTED---------- ------------------------------ ---------- ----------2 PDB$SEED READ ONLY NO5 PDB1 READ WRITE NOSQL> 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.dbfSQL> 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.bdfSQL> 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 a20SQL> select table_name,tablespace_name from dba_tables where owner='TEST';TABLE_NAME TABLESPACE_NAME-------------------- ------------------------------TEST TESTSQL> conn test/oracle@pdb1Connected.SQL> select * from test;ID----------123

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

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

ORA-65359: unable to create pluggable database with no dataSQL> show pdbs;CON_ID CON_NAME OPEN MODE RESTRICTED---------- ------------------------------ ---------- ----------2 PDB$SEED READ ONLY NO5 PDB1 READ WRITE NOSQL> alter pluggable database pdb1 close;Pluggable database altered.SQL> show pdbs;CON_ID CON_NAME OPEN MODE RESTRICTED---------- ------------------------------ ---------- ----------2 PDB$SEED READ ONLY NO5 PDB1 MOUNTEDSQL> 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 NO5 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 pdbsCON_ID CON_NAME OPEN MODE RESTRICTED---------- ------------------------------ ---------- ----------2 PDB$SEED READ ONLY NO3 PDB2 MOUNTED5 PDB1 READ ONLY NOSQL> alter pluggable database pdb2 open;Pluggable database altered.SQL> show pdbs;CON_ID CON_NAME OPEN MODE RESTRICTED---------- ------------------------------ ---------- ----------2 PDB$SEED READ ONLY NO3 PDB2 READ WRITE NO5 PDB1 READ ONLY NO

4.确认pdb2

SQL> alter session set container=pdb2;Session altered.SQL> show con_nameCON_NAME------------------------------PDB2SQL> select * from test.test;no rows selectedSQL> select count(*) from test.test;COUNT(*)----------0

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

SQL> show pdbsCON_ID CON_NAME OPEN MODE RESTRICTED---------- ------------------------------ ---------- ----------2 PDB$SEED READ ONLY NO3 PDB2 READ WRITE NO5 PDB1 READ ONLY NOSQL> alter pluggable database pdb1 close;Pluggable database altered.SQL> show pdbsCON_ID CON_NAME OPEN MODE RESTRICTED---------- ------------------------------ ---------- ----------2 PDB$SEED READ ONLY NO3 PDB2 READ WRITE NO5 PDB1 MOUNTEDSQL> alter pluggable database pdb1 open;Pluggable database altered.SQL> show pdbs;CON_ID CON_NAME OPEN MODE RESTRICTED---------- ------------------------------ ---------- ----------2 PDB$SEED READ ONLY NO3 PDB2 READ WRITE NO5 PDB1 READ WRITE NOSQL>

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.

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 pdb1_copy PDB from the PDB snapshot named

SQL> alter session set container=pdb1;Session altered.SQL> set linesize 150SQL> col con_name format a10SQL> col snapshot_name format a30SQL> col snap_scn format 9999999SQL> col full_snapshot_path format a55SQL> 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

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