12c可插拔 数据库CDB与pdb管理总结
-创建pdb
--1直接创建
CREATE PLUGGABLE DATABASE pdb2
ADMIN USER boswll IDENTIFIED BY oracle
STORAGE (MAXSIZE 2G MAX_SHARED_TEMP_SIZE 100M)
DEFAULT TABLESPACE test DATAFILE '/opt/oracle/oradata/orcl/datafile/test_001.dbf' SIZE 25M AUTOEXTEND ON;
--2以现有pdb为模板创建
--现有pdb必须在read only模式下才能被创建,否则报错
--以pdb1为模板创建pdb2
alter pluggable database pdb2 close immediate;
drop pluggable database pdb2 including datafiles;
alter pluggable database pdb1 close;
alter pluggable database pdb1 open read only;
create pluggable database pdb2
from pdb1
file_name_convert =('/opt/oracle/oradata/orcl/E98BEB85B1A80C56E0439A02A8C05841/datafile/','/opt/oracle/oradata/orcl/datafile/pdb2');
--打开关闭pdb
alter pluggable database all open;
alter pluggable database pdb2 close;
alter pluggable database all close;
alter pluggable database pdb2 close immediate;
alter pluggable database pdb2 open;
alter pluggable database all open;
--删除pdb
--与删除表空间类似,可以级联删除数据文件
drop pluggable database pdb2;
drop pluggable database pdb2 including datafiles;
---只是删除库,不删除文件
DROP PLUGGABLE DATABASE pdb2 KEEP DATAFILES;
--unplug 数据库pdb
--unplug后pdb只能mount不能open
alter pluggable database pdb2 unplug into '/tmp/pdb2.xml';
--unplug后删除pdb,再使用xml文件加回pdb
drop pluggable database pdb2;
create pluggable database pdb2 using '/tmp/pdb2.xml' nocopy;
---插入数据库pdb
CREATE PLUGGABLE DATABASE pdb5 USING '/tmp/pdb2.xml' COPY FILE_NAME_CONVERT=('orcl/PDB2/ORCL/270349FEACBF115BE0530210A8C0B69A/datafile/o1_mf_sysaux_c72mqpft_.dbf','TEST/PDB5/sysaux.dbf','orcl/PDB2/ORCL/270349FEACBF115BE0530210A8C0B69A/datafile/o1_mf_system_c72mqpfl_.dbf','TEST/PDB2/system.dbf','orcl/PDB5/ORCL/270349FEACBF115BE0530210A8C0B69A/datafile/o1_mf_temp_c72mqpfw_.dbf','TEST/PDB5/temp.dbf')
静默方式创建pdb 数据库
dbca -silent -createDatabase -templateName $ORACLE_HOME/assistants/dbca/templates/General_Purpose.dbc -gdbname newtest -sid newtest -characterSet UTF8 -createAsContainerDatabase true -sysPassword oracle -systemPassword oracle
保持启动CDB后PDB的状态
alter pluggable database all save state;
shut immediate;
startup
--容器之间的切换
alter session set container=CDB$ROOT;
alter session set container=pdb5;
---查看当前容器
show con_name
select sys_context('userenv', 'con_name') "Container DB" from dual;
--查看pdb的信息
查看CDB中有多少个pluggable database
alter session set container=CDB$ROOT;
show pdbs
select con_id, dbid, guid, name , open_mode from v$pdbs;