12c可插拔 数据库CDB与pdb管理总结

时间:2021-06-09 16:49:17


-创建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;