创建PDB的两种操作

bisal

发布于 2021-11-08 10:58:58

1.4K0

发布于 2021-11-08 10:58:58

文章被收录于专栏：bisal的个人杂货铺bisal的个人杂货铺

Oracle 19c的安装写了一些文章，

《非Oracle Linux下Oracle 19c CDB数据库安装》

《Oracle 19c的examples静默安装》

《Oracle Cloud创建19c数据库》

《非Oracle Linux下安装Oracle 19c 》

《Oracle 19c之RPM安装》

之前介绍的PDB都是通过配置文件在数据库初始化的时候就装上了，如果要在一个Oracle 19c已有的CDB上创建PDB，主要有两种方式。

方式1，直接创建PDB

直接从PDB$SEED这个种子PDB，创建新的PDB，

SQL> create pluggable database test1 admin user bisal identified by bisal
  2  storage (maxsize 2G) 
  3  default tablespace users
  4  path_prefix='/opt/oracle/oradata/BISALCDB/TEST1/'
  5  file_name_convert=('/opt/oracle/oradata/BISALCDB/pdbseed','/opt/oracle/oradata/BISALCDB/TEST1');

trace显示执行过程，

create pluggable database test1 admin user bisal identified by *
storage (maxsize 2G)
default tablespace users
path_prefix='/opt/oracle/oradata/BISALCDB/TEST1/'
file_name_convert=('/opt/oracle/oradata/BISALCDB/pdbseed','/opt/oracle/oradata/BISALCDB/TEST1')
2021-10-31T15:41:46.183962+08:00
PDB$SEED(2): AUDSYS.AUD$UNIFIED (SQL_TEXT) - CLOB populated
2021-10-31T15:42:23.580231+08:00
TEST1(4):Endian type of dictionary set to little
****************************************************************
Pluggable Database TEST1 with pdb id - 4 is created as UNUSABLE.
If any errors are encountered before the pdb is marked as NEW,
then the pdb must be dropped
local undo-1, localundoscn-0x0000000000000118
****************************************************************
2021-10-31T15:42:28.108404+08:00
TEST1(4):Autotune of undo retention is turned on. 
2021-10-31T15:42:29.304164+08:00
TEST1(4):Undo initialization recovery: err:0 start: 1926300190 end: 1926300215 diff: 25 ms (0.0 seconds)
TEST1(4):[51756] Successfully onlined Undo Tablespace 2.
TEST1(4):Undo initialization online undo segments: err:0 start: 1926300215 end: 1926300477 diff: 262 ms (0.3 seconds)
TEST1(4):Undo initialization finished serial:0 start:1926300190 end:1926300520 diff:330 ms (0.3 seconds)
TEST1(4):Database Characterset for TEST1 is AL32UTF8
TEST1(4):JIT: pid 51756 requesting stop
2021-10-31T15:42:30.389724+08:00
TEST1(4):Buffer Cache flush started: 4
TEST1(4):Buffer Cache flush finished: 4
Completed: create pluggable database test1 admin user bisal identified by *
storage (maxsize 2G)
default tablespace users
path_prefix='/opt/oracle/oradata/BISALCDB/TEST1/'
file_name_convert=('/opt/oracle/oradata/BISALCDB/pdbseed','/opt/oracle/oradata/BISALCDB/TEST1')

TEST1的初始状态是MOUNTED，

SQL> show pdbs;
 CON_ID   CON_NAME   OPEN MODE    RESTRICTED
-------- ---------- ------------ ------------
      2   PDB$SEED   READ ONLY     NO
      3   BISALPDB1  MOUNTED
      4   TEST1      MOUNTED
      5   BISALPDB2  READ WRITE    NO

打开这个PDB，就可以用了，

SQL> alter pluggable database test1 open;
Pluggable database altered.


SQL> show pdbs;
 CON_ID   CON_NAME   OPEN MODE    RESTRICTED
-------- ---------- ------------ ------------
      2   PDB$SEED   READ ONLY     NO
      3   BISALPDB1  MOUNTED
      4   TEST1      READ WRITE    NO
      5   BISALPDB2  READ WRITE    NO

方式2，克隆创建PDB

如果非CDB，复制一个库，我们能选择逻辑导出导入、克隆数据库、dblink等，但是在CDB，直接支持克隆PDB，某些场景下，更实用，更简单，

Oracle支持克隆本地PDB、克隆远程CDB的PDB，支持克隆非CDB等多种形式，我们尝试下克隆一个本地PDB，

Oracle 12.1的克隆数据库，要求源库是Read Only，这个不太方便，影响正常业务，12.2开始引入了Hot Clone技术，支持在线复制，不影响源库使用的情况下，实现克隆数据库，

官方对Hot Clone的介绍，

When the CDB is in ARCHIVELOG mode and local undo mode, the source PDB can be open in read/write mode and operational during the cloning process. This technique is known as hot cloning.

为了验证Hot，我们在源库中，执行如下SQL，模拟实时事务，

create table test(id number, dt timestamp);


begin
  for i in 1 .. 500 loop
    insert into test values(i, systimestamp);
  commit;
  dbms_lock.sleep(2);
  end loop;
end;
/

我们选择BISALPDB1作为复制的源库，Read Write状态下克隆，

SQL> show pdbs;
 CON_ID     CON_NAME    OPEN MODE    RESTRICTED
---------- ----------- ------------ ------------
     2      PDB$SEED    READ ONLY        NO
     3      BISALPDB1   READ WRITE       NO
     5      BISALPDB2   READ WRITE       N

找到BISALPDB1的文件信息，

SQL> select con_id, name from v$datafile where con_id=3;
  CON_ID    NAME
---------- -------------------------------------------------------
     3      /opt/oracle/oradata/BISALCDB/BISALPDB1/system01.dbf
     3      /opt/oracle/oradata/BISALCDB/BISALPDB1/sysaux01.dbf
     3      /opt/oracle/oradata/BISALCDB/BISALPDB1/undotbs01.dbf
     3      /opt/oracle/oradata/BISALCDB/BISALPDB1/users01.dbf

创建目标库的数据文件路径，

mkdir -p /opt/oracle/oradata/BISALCDB/TEST1

执行复制操作，

SQL> alter system set db_create_file_dest='/opt/oracle/oradata/BISALCDB/TEST1';
System altered.


SQL> create pluggable database test1 from bisalpdb1;
Pluggable database created.

从trace日志，能看到Oracle做了Incomplete Recovery，

create pluggable database test1 from bisalpdb1
2021-10-31T18:37:36.221609+08:00
BISALPDB1(3): AUDSYS.AUD$UNIFIED (SQL_TEXT) - CLOB populated
2021-10-31T18:38:08.719482+08:00
TEST1(4):Endian type of dictionary set to little
****************************************************************
Pluggable Database TEST1 with pdb id - 4 is created as UNUSABLE.
If any errors are encountered before the pdb is marked as NEW,
then the pdb must be dropped
local undo-1, localundoscn-0x0000000000000118
****************************************************************
2021-10-31T18:38:09.796408+08:00
TEST1(4):Media Recovery Start
2021-10-31T18:38:09.827728+08:00
TEST1(4):Serial Media Recovery started
TEST1(4):max_pdb is 5
2021-10-31T18:38:10.401253+08:00
TEST1(4):Recovery of Online Redo Log: Thread 1 Group 3 Seq 79 Reading mem 0
TEST1(4):  Mem# 0: /oradata/REDO/redo03.dbf
2021-10-31T18:38:10.881738+08:00
TEST1(4):Incomplete Recovery applied until change 7357265 time 10/31/2021 18:38:05
2021-10-31T18:38:10.898436+08:00
TEST1(4):Media Recovery Complete (BISALCDB)
TEST1(4):Autotune of undo retention is turned on. 
2021-10-31T18:38:13.065567+08:00
TEST1(4):Undo initialization recovery: err:0 start: 1936843789 end: 1936843976 diff: 187 ms (0.2 seconds)
TEST1(4):[48600] Successfully onlined Undo Tablespace 2.
TEST1(4):Undo initialization online undo segments: err:0 start: 1936843976 end: 1936843988 diff: 12 ms (0.0 seconds)
TEST1(4):Undo initialization finished serial:0 start:1936843789 end:1936844007 diff:218 ms (0.2 seconds)
TEST1(4):Database Characterset for TEST1 is AL32UTF8
TEST1(4):JIT: pid 48600 requesting stop
TEST1(4):Buffer Cache flush started: 4
TEST1(4):Buffer Cache flush finished: 4
Completed: create pluggable database test1 from bisalpdb1

打开这个PDB，能看到源库已经创建的对象了，

SQL> alter pluggable database test1 open;
Pluggable database altered.


SQL> alter session set current_schema=test;
Session altered.


SQL> select count(*) from test;
  COUNT(*)
----------
  12

本文参与腾讯云自媒体分享计划，分享自作者个人站点/博客。

原始发表：2021-11-03 ，如有侵权请联系 cloudcommunity@tencent.com 删除

oracle