Oracle 19c的安装写了一些文章,
《非Oracle Linux下Oracle 19c CDB数据库安装》
之前介绍的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