1. 创建第一个PDB:Creatinga PDB by Using the Seed
[oracle@12cr2 ~]$ export ORACLE_SID=zylong
[oracle@12cr2 ~]$ sqlplus / as sysdba
SQL*Plus: Release 12.2.0.1.0 Production on Tue May 16 20:04:22 2017
Copyright (c) 1982, 2016, Oracle. All rights reserved.
Connected to an idle instance.
SQL> startup
ORACLE instance started.
Total System Global Area 1207959552 bytes
Fixed Size 8792152 bytes
Variable Size 436209576 bytes
Database Buffers 754974720 bytes
Redo Buffers 7983104 bytes
Database mounted.
Database opened.
SQL> show pdbs;
CON_ID CON_NAME OPEN MODE RESTRICTED
---------- ------------------------------ ---------- ----------
2 PDB$SEED READ ONLY NO
SQL> set timing on
SQL> CREATE PLUGGABLE DATABASE seed_pdb
2 ADMIN USER pdbadm IDENTIFIED BY oracle
3 ROLES = (dba) ##授予pdbadm用户DBA权限
4 DEFAULT TABLESPACE seed_pdb_tbs
5 DATAFILE '/u01/app/oracle/oradata/zylong/seed_pdb/seed_pdb_tbs01.dbf' SIZE 250M AUTOEXTEND ON
6 FILE_NAME_CONVERT = ('/u01/app/oracle/oradata/zylong/pdbseed/',
7 '/u01/app/oracle/oradata/zylong/seed_pdb/')
8 STORAGE (MAXSIZE 2G) ##当前PDB所有数据文件不超过2G
9 PATH_PREFIX = '/u01/app/oracle/oradata/zylong/seed_pdb/';
Pluggable database created.
Elapsed: 00:00:15.43
2. 创建第二个PDB:Cloninga PDB From an Existing PDB
此处创建PDB的SQL中没有指定STORAGE (MAXSIZE 2G),说明这个PDB的数据文件大小没有限制。
SQL> CREATE PLUGGABLE DATABASE clon_pdb FROM seed_pdb
2 FILE_NAME_CONVERT = ('/u01/app/oracle/oradata/zylong/seed_pdb/', '/u01/app/oracle/oradata/zylong/clon_pdb/')
3 PATH_PREFIX = '/u01/app/oracle/oradata/zylong/clon_pdb/';
CREATE PLUGGABLE DATABASE clon_pdb FROM seed_pdb
*
ERROR at line 1:
ORA-65036: pluggable database SEED_PDB not open in required mode
##提示SEED_PDB数据库没有OPEN,下面将SEED_PDB数据库OPEN后重建执行
SQL> alter session set container=seed_pdb;
Session altered.
SQL> alter database open;
Database altered.
SQL> conn / as sysdba
Connected.
SQL> CREATE PLUGGABLE DATABASE clon_pdb FROM seed_pdb
2 FILE_NAME_CONVERT = ('/u01/app/oracle/oradata/zylong/seed_pdb/', '/u01/app/oracle/oradata/zylong/clon_pdb/')
3 PATH_PREFIX = '/u01/app/oracle/oradata/zylong/clon_pdb/';
Pluggable database created.
Elapsed: 00:00:34.23
SQL> alter pluggable database CLON_PDB open;
Pluggable database altered.
3. 创建第三个PDB:Plugginga PDB into a CDB
将NON-CDB的数据库作为PDB插入到CDB中。
[oracle@12cr2 oradata]$ export ORACLE_SID=orcl
[oracle@12cr2 oradata]$ sqlplus / as sysdba
SQL*Plus: Release 12.2.0.1.0 Production on Tue May 16 21:07:07 2017
Copyright (c) 1982, 2016, Oracle. All rights reserved.
Connected to an idle instance.
SQL> startup
ORACLE instance started.
Total System Global Area 1610612736 bytes
Fixed Size 8621232 bytes
Variable Size 1040188240 bytes
Database Buffers 553648128 bytes
Redo Buffers 8155136 bytes
Database mounted.
Database opened.
SQL> select name ,cdb from v$database;
NAME CDB
--------- ---
ORCL NO
##插入测试数据
SQL> create table tb1 (id int);
Table created.
SQL> insert into tb1 values (1);
1 row created.
SQL> commit;
Commit complete.
SQL> select * from tb1;
ID
----------
1
##将NON-CDB启动到read only
SQL> startup mount force;
ORACLE instance started.
Total System Global Area 1610612736 bytes
Fixed Size 8621232 bytes
Variable Size 1040188240 bytes
Database Buffers 553648128 bytes
Redo Buffers 8155136 bytes
Database mounted.
SQL> alter database open read only;
alter database open read only
*
ERROR at line 1:
ORA-16005: database requires recovery
##可以说明startup mount force是不正常关闭数据库,数据库启动后需要恢复,还是老老实实shutdown immediate吧。
SQL> alter database open;
Database altered.
SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup mount;
ORACLE instance started.
Total System Global Area 1610612736 bytes
Fixed Size 8621232 bytes
Variable Size 1040188240 bytes
Database Buffers 553648128 bytes
Redo Buffers 8155136 bytes
Database mounted.
SQL> alter database open read only;
Database altered.
SQL> exec dbms_pdb.describe(PDB_DESCR_FILE=>'/u01/app/oracle/oradata/zylong/noncdb_pdp.xml');
PL/SQL procedure successfully completed.
SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> exit
Disconnected from Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production
##切换到CDB数据库,将NON-CDB作为PDB插入到CDB中
[oracle@12cr2 oradata]$ export ORACLE_SID=zylong
[oracle@12cr2 oradata]$ sqlplus / as sysdba
SQL*Plus: Release 12.2.0.1.0 Production on Tue May 16 21:40:47 2017
Copyright (c) 1982, 2016, Oracle. All rights reserved.
Connected to:
Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production
SQL> set timing on;
SQL> CREATE PLUGGABLE DATABASE noncdb_pdp
2 USING '/u01/app/oracle/oradata/zylong/noncdb_pdp.xml'
3 FILE_NAME_CONVERT =
4 ('/u01/app/oracle/oradata/orcl/', '/u01/app/oracle/oradata/zylong/noncdb_pdp/')
5 COPY;
Pluggable database created.
Elapsed: 00:00:44.08
SQL> alter pluggable database noncdb_pdp open;
Warning: PDB altered with errors.
##启动PDB有Warning,上网搜搜原来需要执行noncdb_to_pdb.sql脚本。
SQL> alter session set container=NONCDB_PDP;
Session altered.
sql> @$ORACLE_HOME/rdbms/admin/noncdb_to_pdb.sql
SQL> conn / as sysdba
Connected.
SQL> show pdbs;
CON_ID CON_NAME OPEN MODE RESTRICTED
---------- ------------------------------ ---------- ----------
2 PDB$SEED READ ONLY NO
3 SEED_PDB READ WRITE NO
4 NONCDB_PDP READ WRITE YES
5 CLON_PDB READ WRITE NO
## 查一下PDB状态,NONCDB_PDP受限,RESTRICTED是YES,重启一下PDB
SQL> alter session set container=NONCDB_PDP;
Session altered.
SQL> shutdown immediate
Pluggable Database closed.
SQL> conn / as sysdba
Connected.
SQL> alter pluggable database NONCDB_PDP open;
Pluggable database altered.
SQL> show pdbs;
CON_ID CON_NAME OPEN MODE RESTRICTED
---------- ------------------------------ ---------- ----------
2 PDB$SEED READ ONLY NO
3 SEED_PDB READ WRITE NO
4 NONCDB_PDP READ WRITE NO
5 CLON_PDB READ WRITE NO
## NONCDB_PDP状态正常了,下面查查之前插入的数据还在吗
SQL> alter session set container=NONCDB_PDP;
Session altered.
SQL> select * from tb1;
ID
----------
1