Oracle Database 12C 学习之多租户(连载四)

使用克隆现存PDB的方式创建新的PDB:这里有两种情况,一种为使用本地PDB,另外一种为使用远程PDB。二者并无太大差异。只是第二种需要使用DBLINK而已。

克隆本地方式:

SYS@ora12g> show pdbs;

CON_ID CON_NAME  OPEN MODE RESTRICTED

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

 2 PDB$SEED  READ ONLY NO

 3 ORA12C_PDB1  READ WRITE YES

 4 ORACDB_PDB2  READ WRITE NO

SYS@ora12g> alter pluggable database ORA12C_PDB1 close;

Pluggable database altered.

SYS@ora12g> alter pluggable database ORA12C_PDB1 open read only;

Pluggable database altered.

SYS@ora12g> show pdbs;

CON_ID CON_NAME  OPEN MODE RESTRICTED

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

 2 PDB$SEED  READ ONLY NO

 3 ORA12C_PDB1  READ ONLY NO

 4 ORACDB_PDB2  READ WRITE NO

--我们这里克隆ORA12C_PDB1来创建新的PDB,需要先将其置于read only模式。

SYS@ora12c> alter session set container=ORA12C_PDB1;

Session altered.

SYS@ora12c> select file_name from dba_data_files;

FILE_NAME

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

/u01/oracle/oradata/ora12c/ora12c_pdb1/system01.dbf

/u01/oracle/oradata/ora12c/ora12c_pdb1/sysaux01.dbf

/u01/oracle/oradata/ora12c/ora12c_pdb1/SAMPLE_SCHEMA_users01.dbf

/u01/oracle/oradata/ora12c/ora12c_pdb1/example01.dbf

SYS@ora12c> create pluggable database ORA12C_PDB2

from ORA12c_PDB1

file_name_convert = ('/u01/oracle/oradata/ora12c/ora12c_pdb1',

'/u01/oracle/oradata/ora12c/ora12c_pdb2')

storage (MAXSIZE 4G MAX_SHARED_TEMP_SIZE 100M); 2 3 4 5

create pluggable database ORA12C_PDB2

*

ERROR at line 1:

ORA-65040: operation not allowed from within a pluggable database

--不能在PDB中复制PDB。

SYS@ora12c> alter session set container=CDB$ROOT;

Session altered.

SYS@ora12c> create pluggable database ORA12C_PDB2

from ORA12C_PDB1

file_name_convert = ('/u01/oracle/oradata/ora12c/ora12c_pdb1',

'/u01/oracle/oradata/ora12c/ora12c_pdb2')

storage (MAXSIZE 4G MAX_SHARED_TEMP_SIZE 100M);

2 3 4 5

Pluggable database created.

--目标PDB的数据文件存储目录也可以不用事先创建,oracle会自动创建。

--可以在创建PDB的同时指定该PDB的空间使用限额。

SYS@ora12c> show pdbs;

CON_ID CON_NAME  OPEN MODE RESTRICTED

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

 2 PDB$SEED  READ ONLY NO

 3 ORA12C_PDB1  READ ONLY NO

 4 ORACDB_PDB2  READ WRITE NO

 5 ORA12C_PDB2  MOUNTED

使用远程PDB创建:

SYS@ora12c> show pdbs;

CON_ID CON_NAME  OPEN MODE RESTRICTED

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

 2 PDB$SEED  READ ONLY NO

 3 ORA12C_PDB1  READ ONLY NO

 4 ORACDB_PDB2  READ WRITE NO

 5 ORA12C_PDB2  MOUNTED

SYS@ora12c> conn sys/oracle@ORACDB_PDB2 as sysdba

Connected.

SYS@ORACDB_PDB2> shutdown immediate;

Pluggable Database closed.

SYS@ORACDB_PDB2> startup;

Pluggable Database opened.

SYS@ORACDB_PDB2> show pdbs;

CON_ID CON_NAME  OPEN MODE RESTRICTED

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

 4 ORACDB_PDB2  READ WRITE NO

SYS@ORACDB_PDB2> create user remote_pdb identified by remote;

User created.

SYS@ORACDB_PDB2> grant create pluggable database to remote_pdb;

Grant succeeded.

--在源PDB中创建拥有create PDB权限的用户。

SYS@ORACDB_PDB2> conn / as sysdba

Connected.

SYS@ora12g> create database link dbl_pdb connect to remote_pdb identified by remote using 'ORACDB_PDB2';

Database link created.

--我们这里以ORACDB_PDB2作为远程数据库,也就是创建PDB的源PDB。

--利用前面创建的用户创建db link。

SYS@ora12c> alter pluggable database ORACDB_PDB2 open read only force;

Pluggable database altered.

SYS@ora12c> show pdbs;

CON_ID CON_NAME  OPEN MODE RESTRICTED

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

 2 PDB$SEED  READ ONLY NO

 3 ORA12C_PDB1  READ ONLY NO

 4 ORACDB_PDB2  READ ONLY NO

 5 ORA12C_PDB2  MOUNTED

--同样将其置于read only状态。

SYS@ora12c> create pluggable database ORACDB_PDB_NEW

from ORACDB_PDB2@dbl_pdb

file_name_convert = ('/u01/oracle/oradata/ora12c/cdb/pdb2',

'/u01/oracle/oradata/ora12c/cdb/pdb2_new'); 2 3 4

Pluggable database created.

SYS@ora12c> show pdbs;

CON_ID CON_NAME  OPEN MODE RESTRICTED

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

 2 PDB$SEED  READ ONLY NO

 3 ORA12G_PDB1  READ ONLY NO

 4 ORACDB_PDB2  READ ONLY NO

 5 ORA12C_PDB2  MOUNTED

 6 ORACDB_PDB_NEW  MOUNTED

克隆非CDB的数据库来创建PDB。

这里有三种方法:

1,使用DBMS_PDB包生成源数据库的元数据,然后再利用create pluggable database语句创建;

2,使用数据泵(可传输表空间);

3,使用OGG。

使用数据泵方式,请参考官方文档Oracle® Database Utilities 12c Release 1 (12.1)

使用OGG方式,请参阅OGG相关文档。

我们这里测试下第一种方式。

1,先创建一个新的非CDB数据库。我们这里用DBCA创建。这步我就不多写了。各位一路next下去就好。记得别勾选create as a container database即可。

另外需要注意的是,数据库版本必须得是12c或者更高版本。

2,将该数据库以read only模式打开

[oracle@ora12 ~]$ sqlplus / as sysdba

SQL*Plus: Release 12.1.0.2.0 Production on Fri Dec 18 09:56:43 2015

Copyright (c) 1982, 2014, Oracle. All rights reserved.

Connected to:

Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production

With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options

SYS@noncdb> shutdown immediate;

Database closed.

Database dismounted.

ORACLE instance shut down.

SYS@noncdb> startup mount;

ORACLE instance started.

Total System Global Area 838860800 bytes

Fixed Size  2929936 bytes

Variable Size  599788272 bytes

Database Buffers  230686720 bytes

Redo Buffers  5455872 bytes

Database mounted.

SYS@noncdb> alter database open read only;

Database altered.

--这里,数据库需要开归档才能以read only模式打开,至于原因嘛,恩,各位小伙伴应该都能想的出来吧

3,利用DBMS_PDB包生成该数据库的pdb描述文件并关闭数据库。

SYS@noncdb> begin

dbms_pdb.describe(pdb_descr_file => '/home/oracle/noncdb.xml');

end;

/

PL/SQL procedure successfully completed.

SYS@noncdb>

SYS@noncdb> shutdown immediate;

Database closed.

Database dismounted.

ORACLE instance shut down.

--这里生成的pdb描述文件为xml格式的,各位可以进去看一下它的内容。

--该文件中包含了数据库的版本信息,非默认值的初始化参数信息,表空间及数据文件信息,其他可选组件的版本信息,service信息,以及AWR中loadprofile的内容。

4,登陆CDB,检测要插入的pdb是否存在兼容性问题

SYS@ora12c> set serveroutput on

declare

test_via boolean;

begin

test_via := dbms_pdb.check_plug_compatibility(pdb_descr_file => '/home/oracle/noncdb.xml');

if test_via then

dbms_output.put_line('Yes');

else

dbms_output.put_line('No');

end if;

end;

/

Yes

PL/SQL procedure successfully completed.

--输出结果为yes,表示没有兼容性问题。

--如果为no,则需要去检查pdb_plug_in_violations视图。

5,创建PDB

SYS@ora12c> create pluggable database PDB_NEW

using '/home/oracle/noncdb.xml'

copy

file_name_convert = ('/u01/oracle/oradata/noncdb',

'/u01/oracle/oradata/cdb/pdb_new');

Pluggable database created.

SYS@ora12c> show pdbs;

CON_ID CON_NAME  OPEN MODE RESTRICTED

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

 2 PDB$SEED  READ ONLY NO

 3 PDB_NEW  MOUNTED

6,执行脚本

SYS@ora12c> conn sys/oracle@ora12:1521/PDB_NEW as sysdba;

Connected.

SYS@ora12:1521/PDB_NEW> @?/rdbms/admin/noncdb_to_pdb.sql;

--该脚本的用处是:更新非CDB的数据库中的数据字典表,将其调整为PDB。

执行完成之后,该PDB就可以使用了。

需要注意的是:

在执行该脚本的时候,建议同时查看alert 日志,因为我的虚拟机只有2G内存,然后新创建的这个数据库noncdb我将其内存设置为了800M。所以在将该数据库创建为PDB时,oracle需要调整其参数设置。alert 日志中就看到如下类似的内容:

Active Session History (ASH) performed an emergency flush. This may mean that ASH is undersized. If emergency flushes are a recurring issue, you may consider increasing ASH size by setting the value of _ASH_SIZE to a sufficiently large value. Currently, ASH size is 2097152 bytes. Both ASH size and the total number of emergency flushes since instance startup can be monitored by running the following query:

select total_size,awr_flush_emergency_count from v$ash_info;

以及:

Default pga_aggregate_limit value is too high for the

amount of physical memory in the system

pga_aggregate_limit is 2048 MB

limit based on physical memory and SGA usage is 1285 MB

因此,在虚拟机上创建新的PDB时,需要考虑内存以及其他比如说磁盘容量等方面的限制。

从CDB中拔出和插入PDB:

SYS@ora12c> create pluggable database PDB1

2 admin user test_admin identified by test

3 file_name_convert = ('/u01/oracle/oradata/ora12c/pdbseed','/u01/oracle/oradata/ora12c/cdb/pdb1');

Pluggable database created.

SYS@ora12c> show pdbs;

CON_ID CON_NAME  OPEN MODE RESTRICTED

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

 2 PDB$SEED  READ ONLY NO

 3 PDB1   MOUNTED

SYS@ora12c> alter pluggable database PDB1 open;

Pluggable database altered.

SYS@ora12c> show pdbs;

CON_ID CON_NAME  OPEN MODE RESTRICTED

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

 2 PDB$SEED  READ ONLY NO

 3 PDB1   READ WRITE NO

SYS@ora12c> alter pluggable database PDB1 close;

Pluggable database altered.

--需要先关闭可插拔数据库,然后再unplug。

SYS@ora12c> alter pluggable database PDB1

unplug into '/home/oracle/pdb1.xml';

Pluggable database altered.

--需要注意的是,这里只是unplug了,实际上该PDB还存在。如果想重新插入该PDB,则需要先删除。

SYS@ora12c> drop pluggable database PDB1;

Pluggable database dropped.

插入PDB的时候,首先需要做兼容性检查。也就是上篇文章中提到的DBMS_PDB.check_plug_compatibility函数。

如果没有问题,再插入。

插入语句跟创建新的PDB一样

SYS@ora12c> create pluggable database PDB1

using '/home/oracle/pdb1.xml'

copy

file_name_convert = ('/u01/oracle/oradata/pdb1',

'/u01/oracle/oradata/cdb/pdb_new');

使用DBCA创建PDB的方式,这里不再多说

本文来自云栖社区合作伙伴“DBGEEK”

上一篇:预告:语音交互前沿技术分享——INTERSPEECH 2017系列


下一篇:UITableView整理