教你创建PDB的三种方法

MeetDB 2019-10-26

1802

12C官方文档提到6种创建PDB的技术，如下：

Create a PDB by using the seed
Create a PDB by cloning an existing PDB or non-CDB
Create a PDB by relocating it to a different CDB
Create a PDB by plugging an unplugged PDB into a CDB
Create a PDB as a proxy PDB
Create s PDB by using a non-CDB

本文将演示常用的前三种。

演示环境：两套12.2.0.1 RAC集群。

Part 1

Create a PDB by using the seed

SQL> show pdbs;
    CON_ID CON_NAME                       OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
         2 PDB$SEED                       READ ONLY  NO
         3 KINSER1                        READ WRITE NO
         4 KINSER2                        MOUNTED
         5 KINSER3                        MOUNTED
SQL> create pluggable database KINSER4 admin user kevin identified by kevinA123;
Pluggable database created.
SQL> show pdbs
    CON_ID CON_NAME                       OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
         2 PDB$SEED                       READ ONLY  NO
         3 KINSER1                        READ WRITE NO
         4 KINSER2                        MOUNTED
         5 KINSER3                        MOUNTED
         6 KINSER4                        MOUNTED
SQL> alter pluggable database KINSER4 open;
SQL> show pdbs
    CON_ID CON_NAME                       OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
         2 PDB$SEED                       READ ONLY  NO
         3 KINSER1                        READ WRITE NO
         4 KINSER2                        MOUNTED
         5 KINSER3                        MOUNTED
         6 KINSER4                        READ WRITE NO

Part 2

Cloning a Local PDB

SQL> create pluggable database KINSER5 from KINSER1;
Pluggable database created.
SQL> show pdbs
    CON_ID CON_NAME                       OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
         2 PDB$SEED                       READ ONLY  NO
         3 KINSER1                        READ WRITE NO
         4 KINSER2                        MOUNTED
         5 KINSER3                        MOUNTED
         6 KINSER4                        READ WRITE NO
         7 KINSER5                        MOUNTED

Cloning a Remote PDB or Non-CDB

配置tnsnames.ora tns链接串

[oracle@exn1 ~]$ cat $ORACLE_HOME/network/admin/tnsnames.ora
danae =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.67)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = danae)
    )
  )
[oracle@exn1 ~]$

创建DBLINK

create database link   remote   connect to system identified by "123456" using 'danae';

使用该DBLINK创建PDB KINSER6;

SQL> create pluggable database KINSER6 from danaepdb1@remote;
Pluggable database created.
SQL> show pdbs
    CON_ID CON_NAME                       OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
         2 PDB$SEED                       READ ONLY  NO
         3 KINSER1                        MOUNTED
         4 KINSER2                        MOUNTED
         5 KINSER3                        MOUNTED
         6 KINSER4                        READ WRITE NO
         7 KINSER5                        MOUNTED
         8 KINSER6                        MOUNTED
SQL>

Part 3

Create a PDB by relocating it to a different CDB

源库：

SQL> show pdbs;
    CON_ID CON_NAME                       OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
         2 PDB$SEED                       READ ONLY  NO
         3 DANAEPDB1                      MOUNTED
         4 DANAEPDB2                      MOUNTED
         5 DANAEPDB3                      MOUNTED
         6 DANAEPDB4                      MOUNTED
         7 DANAEPDB5                      MOUNTED
         8 DANAEPDB6                      MOUNTED
         9 DANAEPDB7                      READ WRITE NO
SQL> select file_name from cdb_data_files where con_id=9;
FILE_NAME
-------------------------------------------------------------------------------------------
+DATADG/DANAE/75630C2CD0C88319E0533D01A8C05A49/DATAFILE/system.310.986358857
+DATADG/DANAE/75630C2CD0C88319E0533D01A8C05A49/DATAFILE/sysaux.311.986358857
+DATADG/DANAE/75630C2CD0C88319E0533D01A8C05A49/DATAFILE/undotbs1.309.986358857
+DATADG/DANAE/75630C2CD0C88319E0533D01A8C05A49/DATAFILE/undo_2.313.986358933
+DATADG/DANAE/75630C2CD0C88319E0533D01A8C05A49/DATAFILE/users.314.986358937
SQL>

检查undo模式是否为本地管理：

SQL> col property_name for a30
SQL> col property_value for a30
SQL> select property_name,property_value
  2  from database_properties
  3  where property_name='LOCAL_UNDO_ENABLED';
PROPERTY_NAME                  PROPERTY_VALUE
------------------------------ ------------------------------
LOCAL_UNDO_ENABLED             TRUE                
SQL>

检查是否开启归档：

SQL> archive log list;
Database log mode              Archive Mode
Automatic archival             Enabled
Archive destination            +ARCHDG
Oldest online log sequence     82
Next log sequence to archive   83
Current log sequence           83
SQL>

检查字节顺序是否一致：

SQL> col PLATFORM_NAME for a16
SQL> col ENDIAN_FORMAT for a15
SQL> select a.platform_id,a.platform_name,b.endian_format
  2  from v$database a,v$transportable_platform b
  3  where a.platform_id=b.platform_id;
PLATFORM_ID PLATFORM_NAME   ENDIAN_FORMAT
----------- ---------------- ---------------
         13 Linux x86 64-bit Little
SQL>

检查字符集是否兼容：

SQL> col nls_lang for a30
SQL> r
  1  SELECT a.value || '_' || b.value || '.' || c.value NLS_LANG
  2  FROM nls_database_parameters a, nls_database_parameters b,nls_database_parameters c
  3  WHERE a.parameter = 'NLS_LANGUAGE'
  4  AND b.parameter = 'NLS_TERRITORY'
  5* AND c.parameter = 'NLS_CHARACTERSET'
NLS_LANG
------------------------------
AMERICAN_AMERICA.AL32UTF8
SQL>

赋予system用户相应超级权限：

SQL> show user;
USER is "SYS"
SQL> grant connect,sysoper,create pluggable database to system container=all;
Grant succeeded.
SQL>

目标库：

SQL> show pdbs;
    CON_ID CON_NAME                       OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
         2 PDB$SEED                       READ ONLY  NO
         3 KINSER1                        READ WRITE NO
         4 KINSER2                        MOUNTED
         5 KINSER3                        MOUNTED
         6 KINSER4                        READ WRITE NO
         7 KINSER5                        MOUNTED
         8 KINSER6                        MOUNTED
SQL> select property_name,property_value
  2  from database_properties
  3  where property_name='LOCAL_UNDO_ENABLED';
PROPERTY_NAME                  PROPERTY_VALUE
------------------------------ ------------------------------
LOCAL_UNDO_ENABLED             TRUE
SQL> archive log list;
Database log mode              Archive Mode
Automatic archival             Enabled
Archive destination            +ARCH
Oldest online log sequence     71
Next log sequence to archive   72
Current log sequence           72
SQL>

查看字节顺序

SQL> col PLATFORM_NAME for a16
SQL> col ENDIAN_FORMAT for a15
select a.platform_id,a.platform_name,b.endian_format
from v$database a,v$transportable_platform b
  3  where a.platform_id=b.platform_id;
PLATFORM_ID PLATFORM_NAME    ENDIAN_FORMAT
----------- ---------------- ---------------
         13 Linux x86 64-bit Little
SQL>

查看字符集

SQL> col nls_lang for a30
SQL> SELECT a.value || '_' || b.value || '.' || c.value NLS_LANG
  2  FROM nls_database_parameters a, nls_database_parameters b,nls_database_parameters c
  3  WHERE a.parameter = 'NLS_LANGUAGE'
  4  AND b.parameter = 'NLS_TERRITORY'
  5* AND c.parameter = 'NLS_CHARACTERSET'
NLS_LANG
------------------------------
AMERICAN_AMERICA.AL32UTF8
SQL>

准备迁移

目标库：

确认tns连接串无误

[oracle@exn1 ~]$ tail -n 9 $ORACLE_HOME/network/admin/tnsnames.ora
danae =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.67)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = danae)
    )
  )
[oracle@exn1 ~]$

创建迁移中使用的DBLINK

SQL> create database link danae connect to system identified by "123456" using 'danae';
Database link created.
SQL>

执行迁移：

SQL> create pluggable database KINSER7 from danaepdb7@danae relocate;
Pluggable database created.
SQL> show pdbs;
    CON_ID CON_NAME                       OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
         2 PDB$SEED                       READ ONLY  NO
         3 KINSER1                        READ WRITE NO
         4 KINSER2                        MOUNTED
         5 KINSER3                        MOUNTED
         6 KINSER4                        READ WRITE NO
         7 KINSER5                        MOUNTED
         8 KINSER6                        MOUNTED
         9 KINSER7                        MOUNTED
SQL> alter pluggable database kinser7 open;
Warning: PDB altered with errors.
SQL

目标库手动OPEN KINSER7之后，原库将自动把DANAEPDB7关闭并删除。

参考：

1.官方文档《Database Administrator’s Guide》中 Creating and Removing PDBs with SQL*Plus 章节

数据库

文章转载自MeetDB，如果涉嫌侵权，请发送邮件至：contact@modb.pro进行举报，并提供相关证据，一经查实，墨天轮将立刻删除相关内容。

教你创建PDB的三种方法

评论