oracle 12c 创建PDB的方式

王亮

1、从PDB$SEED创建新PDB

SQL> create pluggable database PDB3 adminuser pdb3admin identified by oracle

file_name_convert=('/u01/oradata/cdb1/pdbseed','/u01/oradata/cdb1/pdb3');

2、克隆本地PDB

SQL> show pdbs

   CON_ID CON_NAME           OPENMODE  RESTRICTED

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

     2     PDB$SEED           READ ONLY  NO

     3     PDB1               READ WRITE NO

     4     PDB2               MOUNTED

     6     PDB3               MOUNTED

SQL> alter pluggable database pdb1 close;

插接式数据库已变更。

SQL> alter pluggable database pdb1 openread only;

插接式数据库已变更。

SQL> create pluggable database pdb4 frompdb1 file_name_convert=('/u01/oradata/cdb1/pdb1','/u01/oradata/cdb1/pdb4') ;

插接式数据库已创建。

　　

3、克隆远程 PDB

该操作有一些限制。源和目标 CDB 平台必须满足以下要求：它们必须安装有相同的 endian 格式和兼容的数据库项，并使用相同的字符集和国家字符集。

Check theCHARACTERSET of remote and local CDB

col parameter for a30

col value for a30

select * from nls_database_parameters whereparameter='NLS_CHARACTERSET'

or parameter='NLS_LANGUAGE' orparameter='NLS_NCHAR_CHARACTERSET';

Check the remote CDBis in local undo mode and archivelog mode.

COL property_name FORMAT A30

COL property_value FORMAT A30

SELECT property_name, property_value

FROM database_properties

WHERE property_name = 'LOCAL_UNDO_ENABLED';

PROPERTY_NAME PROPERTY_VALUE

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

LOCAL_UNDO_ENABLED TRUE

SELECT log_mode FROM v$database;

LOG_MODE

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

ARCHIVELOG

whitch of two is notbe provided ,need to turn the remote database into read-only mode.

on remote sourceCDB:

SQL> alter session set container=pdb2;

Session altered.

SQL> show pdbs

CON_ID CON_NAME OPEN MODE RESTRICTED

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

4    PDB2    READWRITE    NO

SQL> create user pdbclone identified bywelcome123;

SQL> grant create session,create pluggabledatabase to pdbclone;

SQL> alter pluggable database pdb2 close;

SQL> alter pluggable database pdb2 openread only;

SQL> show pdbs;

CON_ID CON_NAME OPEN MODE RESTRICTED

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

4   PDB2   READ ONLY  NO

on local destinationCDB:

SQL> create database link clone_linkconnect to pdbclone identified by welcome123 using '192.168.133.220:1521/pdb2';

SQL> create pluggable database pdblk fromPDB2@clone_linkfile_name_convert=('/u01/oradata/cdb1/pdb2','/u01/oradata/cdb1/pdblk') ;

插接式数据库已创建。

SQL> ! ls /u01/oradata/cdb1/pdblksysaux01.dbfsystem01.dbf temp01.dbf    undotbs01.dbfusers01.dbf

SQL>on remote source CDB ,Switch thesource PDB back to read/write

SQL> alter pluggable database pdb2 close;

Pluggable database altered.

SQL> alter pluggable database pdb2 open;

Pluggable database altered.

SQL> show pdbs

CON_ID CON_NAME OPEN MODE RESTRICTED

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

4   PDB2   READ WRITE  NO

4、克隆远程 Non-CDB

该操作有一些限制。源和目标 CDB 平台必须满足以下要求：它们必须安装有相同的 endian 格式和兼容的数据库项，并使用相同的字符集和国家字符集。

Check theCHARACTERSET of remote Non-CDB and local CDB

col parameter for a30

col value for a30

select * from nls_database_parameters whereparameter='NLS_CHARACTERSET'

or parameter='NLS_LANGUAGE' orparameter='NLS_NCHAR_CHARACTERSET';

Check the remoteNon-CDB is in archivelog mode.If not need to turn the remote database intoread-only mode.

SQL> SELECT log_mode FROM v$database;

LOG_MODE

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

ARCHIVELOG

SQL> create user nocdbclone identified bywelcome123;

SQL> grant create session,create pluggabledatabase to nocdbclone;

SQL> startup mount;

SQL> alter database open read only;

Database altered.

Create a new PDB inthe local database by cloning the remote non-CDB.

SQL>create database link clone_linkconnect to nocdbclone identified by welcome123 using '192.168.133.220:1521/ora12c';

SQL> CREATE PLUGGABLE DATABASE nocdbtopdbFROM NON$CDB@clone_linkfile_name_convert=('/u01/oradata/ora12c','/u01/oradata/cdb1/nocdbtopdb') ;

插接式数据库已创建。

SQL> !ls /u01/oradata/cdb1/nocdbtopdb

sysaux01.dbf system01.dbf temp01.dbf    undotbs01.dbf users01.dbf

SQL> show pdbs

CON_ID CON_NAME OPEN MODE RESTRICTED

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

2   PDB$SEED      READ ONLY   NO

3   PDB1       READ ONLY   NO

4   PDB2       MOUNTED

5   PDB4       MOUNTED

6   PDB3       MOUNTED

7   PDBLK         MOUNTED

8   NOCDBTOPDB      MOUNTED

SQL> ALTER SESSION SETCONTAINER=NOCDBTOPDB;

会话已更改。

SQL>@$ORACLE_HOME/rdbms/admin/noncdb_to_pdb.sql

SQL> ALTER PLUGGABLE DATABASE nocdbtopdbopen;

插接式数据库已变更。

SQL> show pdbs

CON_ID CON_NAME OPEN MODE RESTRICTED

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

8   NOCDBTOPDB    READWRITE   NO