12 可插入数据库

来源：互联网发布：c语言写gui 编辑：程序博客网时间：2024/05/20 17:26

一个CDB数据库容器包组件：
ROOT组件：ROOT又叫CDB$ROOT, 存储着ORACLE提供的元数据和Common User,元数据的一个例子是ORACLE提供的PL/SQL包的源代码，Common User 是指在每个容器中都存在的用户。
SEED组件： Seed又叫PDB$SEED,这个是你创建PDBS数据库的模板，你不能在Seed中添加或修改一个对象。一个CDB中有且只能有一个Seed. 这个感念，个人感觉非常类似SQL SERVER中的model数据库。
PDBS： CDB中可以有一个或多个PDBS，PDBS向后兼容，可以像以前在数据库中那样操作PDBS，这里指大多数常规操作。

这些组件中的每一个都可以被称为一个容器。因此，ROOT(根)是一个容器，Seed(种子)是一个容器，每个PDB是一个容器。每个容器在CDB中都有一个独一无二的的ID和名称

关于多租户环境：
1、多个PDB可以可以插入一个CDB中去
2、需要有足够的资源支撑CDB
3 Configuration options that apply to the entire CDB and configuration options that apply to each PDB

可以用以下方法创建一个CDB:
可以用 DBCA to Create a CDB

也可以用 CREATE DATABASE Statement to Create a CDB

1 配置新的可插入数据
一个数据库在存在期间，只能是一个CDB或者一个非CDB，不能将非CDB转换为CDB，反之亦然。必须在创建时指定为CDB，然后在其中创建PDB
SQL语句CREATE DATABASE ... ENABLE PLUGGABLE DATABASE可以创建一个新的CDB。如果没有指定ENABLE PLUGGABLE DATABASE从句，那么新建的数据库为非CDB，并且不能包含PDB。

查看当前数据库是否为多租户数据库，v$database的cdb字段显示yes，表明当前数据库为一个CDB数据库

SQL> select name,cdb from v$database;
NAME CDB
--------- ---
ORCL YES

查看当前CDB数据库中的pdb的状态
SQL> select con_id,dbid,name,open_mode from v$pdbs;
CON_ID DBID NAME OPEN_MODE
---------- ---------- ------------------------------ ----------
2 2972678483 PDB$SEED READ ONLY
3 1145215341 PDBORCL READ WRITE

SQL> select pdb_id,pdb_name,dbid,status,creation_scn from dba_pdbs;
PDB_ID PDB_NAME DBID STATUS CREATION_SCN
---------- ------------------------------ ---------- --------- ------------
3 PDBORCL 1145215341 NORMAL 1740054
2 PDB$SEED 2972678483 NORMAL 1594415

三种方式，创建PDB
1、从种子PDB创建新的PDB
create pluggable database pdb2
admin user boswll identified by oracle roles=(connect)
file_name_convert=('ORCL','PDB2');

2、将非CDB插入或者克隆到CDB中

克隆
将本地的PDB克隆到同一个CDB中
讲远程的PDB克隆岛CDB中

3、将移走的PDB插入到另外一个CDB中

alter pluggable database pdborcl open;

下面举例：
1、从种子PDB创建新的PDB
create pluggable database pdb2
admin user boswll identified by oracle roles=(connect)
file_name_convert=('ORCL','PDB2');

从 pdb$seed 数据库复制数据文件，创建system和sysaux表空间
创建完整目录，包括向ORACLE提供的对象元数据
创建临时表空间TEMP,创建公共用户（超级用户sys system）
创建授予本地 pdb_dbs 角色的本地用户
创建新的默认服务

创建PDB用户
多租户环境下的数据库状态查看，启停，打开，关闭等，操作如下
SQL> select name from v$datafile;
NAME
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
/u01/app/oracle/oradata/ORCL/datafile/o1_mf_system_c6pmswtl_.dbf
/u01/app/oracle/oradata/ORCL/datafile/o1_mf_sysaux_c6pmq6xf_.dbf
/u01/app/oracle/oradata/ORCL/datafile/o1_mf_undotbs1_c6pmxkfs_.dbf
/u01/app/oracle/oradata/ORCL/datafile/o1_mf_system_c6pmzfpf_.dbf
/u01/app/oracle/oradata/ORCL/datafile/o1_mf_users_c6pmxj8s_.dbf
/u01/app/oracle/oradata/ORCL/datafile/o1_mf_sysaux_c6pmzfpb_.dbf
/u01/app/oracle/oradata/ORCL/26A8333E18924AF2E0530210A8C01D68/datafile/o1_mf_system_c6po12ls_.dbf
/u01/app/oracle/oradata/ORCL/26A8333E18924AF2E0530210A8C01D68/datafile/o1_mf_sysaux_c6po12o8_.dbf
/u01/app/oracle/oradata/ORCL/26A8333E18924AF2E0530210A8C01D68/datafile/o1_mf_users_c6po76sc_.dbf

1、从种子PDB创建新的PDB
create pluggable database pdb2
admin user boswll identified by oracle roles=(connect)
file_name_convert=('ORCL','PDB2');

SQL> create pluggable database pdb1
admin user boswll identified by oracle roles=(connect)
file_name_convert=('pdbseed','PDB1');
2 3
create pluggable database pdb1
*
ERROR at line 1:
ORA-65005: missing or invalid file name pattern for file - /u01/app/oracle/oradata/ORCL/datafile/o1_mf_system_c6pmzfpf_.dbf

SQL> create pluggable database pdb2
admin user boswll identified by oracle roles=(connect)
file_name_convert=('ORCL','PDB2'); 2 3
create pluggable database pdb2
*
ERROR at line 1:
ORA-01276: Cannot add file /u01/app/oracle/oradata/PDB2/datafile/o1_mf_system_c6pmzfpf_.dbf. File has an Oracle Managed Files file name.

原因是之前创建种子数据库时用的omf创建的，需要用路径转换file_name_convert

查看种子用数据库文件
alter session set container=pdb$seed;
SQL> show con_name
CON_NAME
------------------------------
PDB$SEED

SQL> select name from v$datafile;
NAME
------------------------------------------------------------------------------------------------------------------------------------------------------
/u01/app/oracle/oradata/ORCL/datafile/o1_mf_undotbs1_c6pmxkfs_.dbf
/u01/app/oracle/oradata/ORCL/datafile/o1_mf_system_c6pmzfpf_.dbf
/u01/app/oracle/oradata/ORCL/datafile/o1_mf_sysaux_c6pmzfpb_.dbf

SQL> select FILE_NAME,TABLESPACE_NAME,STATUS from dba_data_files;
FILE_NAME TABLESPACE_NAMESTATUS
-------------------------------------------------------------------------------- ------------------------------ ---------
/u01/app/oracle/oradata/ORCL/datafile/o1_mf_system_c6pmzfpf_.dbfSYSTEMAVAILABLE
/u01/app/oracle/oradata/ORCL/datafile/o1_mf_sysaux_c6pmzfpb_.dbfSYSAUXAVAILABLE

show parameter SOURCE_FILE_NAME_CONVERT
show parameter FILE_NAME_CONVERT
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_file_name_convert string
log_file_name_convert string
pdb_file_name_convert string
SQL> show parameter SOURCE_FILE_NAME_CONVERT

SQL> create pluggable database pdb2
admin user boswll identified by oracle roles=(connect)
DEFAULT TABLESPACE pdb2 DATAFILE '/u01/app/oracle/oradata/ORCL/datafile/pdb2/pdb201.dbf' SIZE 50M
file_name_convert=('/u01/app/oracle/oradata/ORCL/datafile/o1_mf_system_c6pmzfpf_.dbf','/u01/app/oracle/oradata/ORCL/datafile/pdb2/pdb2_mf_system_c6pmzfpf_.dbf','/u01/app/oracle/oradata/ORCL/datafile/o1_mf_sysaux_c6pmzfpb_.dbf','/u01/app/oracle/oradata/ORCL/datafile/pdb2/pdb2_o1_mf_sysaux_c6pmzfpb_.dbf');

2 3 4 create pluggable database pdb2
*
ERROR at line 1:
ORA-65040: operation not allowed from within a pluggable database
必须用cdb$root下创建

SQL> show con_name
CON_NAME
------------------------------
PDB1
SQL> alter session set container=cdb$root;
Session altered.

create pluggable database pdb2
admin user boswll identified by oracle roles=(connect)
DEFAULT TABLESPACE pdb2 DATAFILE '/u01/app/oracle/oradata/ORCL/datafile/pdb2/pdb201.dbf' SIZE 50M
file_name_convert=('/u01/app/oracle/oradata/ORCL/datafile/o1_mf_system_c6pmzfpf_.dbf','/u01/app/oracle/oradata/ORCL/datafile/pdb2/system.dbf',
'/u01/app/oracle/oradata/ORCL/datafile/o1_mf_sysaux_c6pmzfpb_.dbf','/u01/app/oracle/oradata/ORCL/datafile/pdb2/sysaux.dbf',
'/u01/app/oracle/oradata/ORCL/datafile/pdbseed_temp012015-12-12_06-37-39-AM.dbf','/u01/app/oracle/oradata/ORCL/datafile/pdb2/temp.dbf'
);

create pluggable database pdb3
admin user boswll identified by oracle roles=(connect)
file_name_convert=('/o1_mf_system_c6pmzfpf_.dbf','/pdb3/system.dbf',
'/o1_mf_sysaux_c6pmzfpb_.dbf','/pdb3/sysaux.dbf',
'/pdbseed_temp012015-12-12_06-37-39-AM.dbf','/pdb3/temp.dbf'
);

alter pluggable database pdb2 open;
alter pluggable database pdb3 open;

SQL> select NAME,CON_ID,OPEN_MODE from v$pdbs;
NAME CON_ID OPEN_MODE
------------------------------------------------------------------------------------------------------------------------------------------------------ ---------- ----------
PDB$SEED 2 READ ONLY
PDBORCL 3 READ WRITE
PDB1 4 READ WRITE
PDB2 5 READ WRITE
PDB3 6 READ WRITE

如果PDB$SEED 不是omf 自动管理的，那么可以用下面的方式直接建库

[oracle@test12c orcl]$ ls
control01.ctl pdborcl pdbseed redo01.log redo02.log redo03.log sysaux01.dbf system01.dbf temp01.dbf undotbs01.dbf users01.dbf

create pluggable database pdb4
admin user boswll identified by oracle roles=(connect)
file_name_convert=('pdbseed','PDB4');

CONN / AS SYSDBA
CREATE PLUGGABLE DATABASE pdb2 ADMIN USER pdb_adm IDENTIFIED BY Password1
FILE_NAME_CONVERT=('/u01/app/oracle/oradata/cdb1/pdbseed/','/u01/app/oracle/oradata/cdb1/pdb2/');

CONN / AS SYSDBA
ALTER SESSION SET PDB_FILE_NAME_CONVERT='/u01/app/oracle/oradata/cdb1/pdbseed/','/u01/app/oracle/oradata/cdb1/pdb3/';
CREATE PLUGGABLE DATABASE pdb3 ADMIN USER pdb_adm IDENTIFIED BY Password1;

---删除
DROP PLUGGABLE DATABASE pdb2 KEEP DATAFILES;

[oracle@test12c PDB4]$ ls -lrt
总用量 839760
-rw-r----- 1 oracle oinstall 20979712 12月 15 22:01 PDB4_temp012015-12-15_07-45-14-PM.dbf
-rw-r----- 1 oracle oinstall 262152192 12月 15 22:08 system01.dbf
-rw-r----- 1 oracle oinstall 597696512 12月 15 22:08 sysaux01.dbf

2、将非CDB插入或者克隆到CDB中

克隆
将本地的PDB克隆到同一个CDB中
将远程的PDB克隆到CDB中

---设置 db_create_file_dest 或者配置使用 db_file_name_convert
---停顿要复制的PDB数据库
alter session set container=cdb$root
alter pluggable database pdb4 close immediate;
alter pluggable database pdb4 open read only;

----从pdb4 克隆pdb5
----此方式创建的omf管理
create pluggable database pdb5 from pdb4 create_file_dest='/u01/app/oracle/oradata/orcl/PDB5';
---此方式创建
create pluggable database pdb6 from pdb4 FILE_NAME_CONVERT=('PDB4','PDB6');
---在读写模式下打开pdb5
alter pluggable database pdb5 open;

---重新以读写模式打开pdb4
alter pluggable database pdb4 close immediate;
alter pluggable database pdb4 open;

---建好PDB5,查看数据文件
alter session set container=pdb5
select FILE_NAME,TABLESPACE_NAME,STATUS from dba_data_files;
FILE_NAME TABLESPACE_NAMESTATUS
-------------------------------------------------------------------------------- ------------------------------ ---------
/u01/app/oracle/oradata/orcl/PDB5/ORCL/270349FEACBF115BE0530210A8C0B69A/datafile/o1_mf_system_c72mqpfl_.dbf SYSTEMAVAILABLE
/u01/app/oracle/oradata/orcl/PDB5/ORCL/270349FEACBF115BE0530210A8C0B69A/datafile/o1_mf_sysaux_c72mqpft_.dbf SYSAUXAVAILABLE

alter session set container=pdb6
SQL> select FILE_NAME,TABLESPACE_NAME,STATUS from dba_data_files;
FILE_NAME TABLESPACE_NAMESTATUS
-------------------------------------------------------------------------------- ------------------------------ ---------
/u01/app/oracle/oradata/orcl/PDB6/system01.dbf SYSTEM AVAILABLE
/u01/app/oracle/oradata/orcl/PDB6/sysaux01.dbf SYSAUX AVAILABLE


将non-CDB插入到CDB中
可用三种方法： tts (transportable tablespace)或者tdb (Transportable Database) 或完全导出/导入
使用 dbms_pdb 构造XML文件定义
复制

新的PDB 中会创建以下实体：表空间：system sysaux，temp
完整目录
公共用户：sys system
本地管理员 pdba
新的默认服务

--- 使用dbms_pdb包进行迁移
Basically it is very straight forward:
(1) Upgrade your stand-alone database to 12c
(2) Put it into Read-Only mode
(3) DBMS_PDB.DESCRIBE will create an XML manifest file
(4) Shut your database down IMMEDIATE
(5) Connect into the CDB$ROOT
(6) CREATE PLUGGABLE DATABASE pdb1 USING (pdb.xml) NOCOPY TEMPFILE REUSE;
(7) Start @noncdb_to_pdb.sql

--导出非cdb中的数据
select name ,cdb from v$database;
NAME CDB
--------- ---
dog NO

shut immediate;
conn /as sysdba
startup mount;
alter database open read only;
exec dbms_pdb.describe(PDB_DESCR_FILE=>'/home/oracle/dog.xml');

---导入到cdb中去作为pdb5
export ORACLE_SID=orcl
sqlplus sys/oracle as sysdba
create pluggable database pdb5 using '/home/oracle/dog.xml'
file_name_convert=('DOG','ORCL/PDB6');
ALTER SESSION SET CONTAINER=pdb5;
@$ORACLE_HOME/rdbms/admin/noncdb_to_pdb;
select name,open_mode from v$pdbs;
alter session set container=pdb5;
select FILE_NAME,TABLESPACE_NAME,STATUS from dba_data_files;

----dblink此方式可以是把cdb中的pdb插入到其他cdb中去，也可以是non-cdb，插入到其他cdb中去，如果是non-cdb，必须要执行一个脚本noncdb_to_pdb

Source database must be at least Oracle 12.1.0.1
Source database must be on the same OS platform
Source database must be at the same (equal) version as the container database
Script noncdb_to_pdb.sql needs to be run

--连接到非CDB上
sqlplus sys/oracle as sysdba
shut immediate；
alter database open read only;

---连接到CDB数据库，创建dblink，在监听文件中要配置non-cdb
non-cdb =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = your_instance.your_host)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = non-cdb)
)
)
create database link orcl connect to system identified by oracle using 'non-cdb';
mkdir -p /u01/app/oracle/oradata/test/pdb6
create pluggable database pdb6 from non$cdb@orcl create_file_dest='/u01/app/oracle/oradata/test/pdb6' TEMPFILE REUSE;;

--执行脚本，如果是non-cdb,如果不是non-cdb，可以跳过脚本，直接打开库
---But when you check the status of the new PDB you will realize it is OPEN but only in RESTRICTED mode. Therefore noncdb_to_pdb,sql needs to be run. Connect to the new PDB and start the script:
ALTER SESSION SET CONTAINER=pdb6;
@$ORACLE_HOME/rdbms/admin/noncdb_to_pdb;

---或者通过下列方式执行脚本，总之要在pdb6上执行脚本

SQL> select name from v$services where pdb='PDB6';
NAME
----------------------------------------------------------------------------------------------------
pdb6

pdb6 =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = your_instance.your_host)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = PDB6)
)
)
sqlplus sys/pdb6 as sysdba
@$ORACLE_HOME/rdbms/admin/noncdb_to_pdb;

alter pluggable database pdb6 open;
---验证
SELECT message, action FROM pdb_plug_in_violations WHERE name = 'PDB6';

3、将移走的PDB插入到另外一个CDB中
a 、将根容器orcl中的pdb5移走
以公共用户的身份链接到pdb5上
验证pdb5是否已经关闭
alter pluggable database pdb5 unplug into‘pdb5.xml’

以公共用户的身份链接到test 根容器上
使用dbms_pdb 程序包检查pdb5 与test 根容器的兼容性
create pluggable database pdb1 using 'pdb5.xml' nocopy;
在读写模式下打开pdb2



---拔出pdb

ALTER PLUGGABLE DATABASE pdb4 CLOSE;
ALTER PLUGGABLE DATABASE pdb4 UNPLUG INTO '/home/oracle/pdb4.xml';

[oracle@test12c dog]$ echo $ORACLE_SID
test
[oracle@test12c dog]$ sqlplus /nolog
SQL*Plus: Release 12.1.0.2.0 Production on Thu Dec 17 05:08:08 2015
Copyright (c) 1982, 2014, Oracle. All rights reserved.
SQL> conn /as sysdba
Connected.
--插入pdb
把PDB 插入到CDB 类似于新建一个PDB,首先做兼容性检测
First check the PBD is compatible with the CDB by calling the DBMS_PDB.CHECK_PLUG_COMPATIBILITY function,
passing in the XML metadata file and the name of the PDB you want to create using it.
SET SERVEROUTPUT ON
DECLARE
l_result BOOLEAN;
BEGIN
l_result := DBMS_PDB.check_plug_compatibility(
pdb_descr_file => '/home/oracle/pdb4.xml',
pdb_name => 'pdb2');

IF l_result THEN
DBMS_OUTPUT.PUT_LINE('compatible');
ELSE
DBMS_OUTPUT.PUT_LINE('incompatible');
END IF;
END;
/
compatible

---Instead, we want to plug the database back into the same container, so we don't need to copy the files or recreate the temp file, so we can do the following'
CREATE PLUGGABLE DATABASE pdb2 USING '/home/oracle/pdb4.xml' NOCOPY TEMPFILE REUSE;

ALTER PLUGGABLE DATABASE pdb2 OPEN READ WRITE;

SQL> show pdbs
CON_ID CON_NAME OPEN MODE RESTRICTED
---------- ------------------------------ ---------- ----------
2 PDB$SEED READ ONLY NO
3 TESTPDB MOUNTED
4 PDB2 READ WRITE NO

或者路径转换
---If the PDB is not compatible, violations are listed in the PDB_PLUG_IN_VIOLATIONS view. If the PDB is compatible, create a new PDB using it as the source.
--If we were creating it with a new name we might do something like this

CREATE PLUGGABLE DATABASE pdb5 USING '/home/oracle/pdb4.xml'
FILE_NAME_CONVERT=('/u01/app/oracle/oradata/cdb1/pdb2/','/u01/app/oracle/oradata/cdb1/pdb5/');

alter pluggable database pdb5 open;

原来的数据
SQL> alter pluggable database pdb4 open read write;
alter pluggable database pdb4 open read write
*
ERROR at line 1:
ORA-65086: cannot open/close the pluggable database

SQL> SELECT pdb_id,pdb_name,status FROM dba_pdbs;

PDB_ID PDB_NAME STATUS
---------- -------------------------------------------------------------------------------------------------------------------------------- ---------
3 PDBORCL NORMAL
2 PDB$SEED NORMAL
4 PDB4 UNPLUGGED
5 PDB5 NORMAL
6 PDB6 NORMAL

解决方法：
SQL> DROP pluggable DATABASE pdb4;
Pluggable database dropped.

CREATE pluggable DATABASE pdb1 USING '/home/oracle/pdb4.xml' NOCOPY;
SQL> CREATE pluggable DATABASE pdb1 USING '/home/oracle/pdb4.xml' NOCOPY;
CREATE pluggable DATABASE pdb1 USING '/home/oracle/pdb4.xml' NOCOPY
*
ERROR at line 1:
ORA-65139: Mismatch between XML metadata file and data file /u01/app/oracle/oradata/orcl/PDB4/system01.dbf for value of afn (12 in the plug XML file, 11 in the data file)
原因是重新打开原来的none-cdb后，会往里面写入一些数据，导致数据文件大小和xml文件中描述不一致。否则只能重新生成xml文件了.

CREATE pluggable DATABASE pdb4 ;
ORA-65018: FILE_NAME_CONVERT or NOCOPY must be specified

CREATE pluggable DATABASE pdb4 USING '/home/oracle/pdb4.xml' FILE_NAME_CONVERT=('/home/oracle/','/u01/app/oracle/oradata/orcl/PDB4/')

CREATE pluggable DATABASE pdb4 USING '/home/oracle/pdb4.xml' NOCOPY;
ALTER pluggable DATABASE pdb4 OPEN;

0 0