12c创建pdb可以说是相当简单,比11g建库要简单许多。
下面是创建pdb到交付使用的步骤。
1 检查
--查看pdb是否有重名
show pdbs
--asm磁盘是否足够
set lines 200 pages 200
col name for a15
col "AU_size" for 999,999,999
col state for a12
col type for a10
col total_mb for 999,999,999
col free_mb for 999,999,999
col usable_file_mb for 999,999,999
select group_number,
name,
allocation_unit_size as "AU_size",
state,
type,
total_mb,
free_mb,
usable_file_mb,
offline_disks
from v$asm_diskgroup;
2 创建pdb
--创建pdb,改dbname即可
CREATE PLUGGABLE DATABASE dbname ADMIN USER pdbadmin IDENTIFIED BY pdbadmin ROLES=(CONNECT) file_name_convert=NONE STORAGE ( MAXSIZE UNLIMITED MAX_SHARED_TEMP_SIZE UNLIMITED)
--打开pdb
alter pluggable database dbname open instances=all;
--进入pdb
alter session set container=dbname
这样就算是建好了
3 创建表空间
show parameter file_dest
--如果表空间不创建在默认磁盘组上,需要指定磁盘组
--表空间过大需要写脚本跑,确认pdb是否正确,不要建到cdb里面去了
create SMALLFILE tablespace users LOGGING DATAFILE SIZE 10240M AUTOEXTEND OFF EXTENT MANAGEMENT LOCAL SEGMENT SPACE MANAGEMENT AUTO;
--给表空间添加数据文件
alter tablespace xxx add datafile size 10240m autoextend off;
--临时表空间添加数据文件
ALTER TABLESPACE temptbs ADD TEMPFILE SIZE 10240m AUTOEXTEND OFF;
--12c共用undo,一般不需要添加,在cdb中查看和添加undo
alter tablespace undotbs add datafile size 10240m ;
4 创建用户
--创建用户,必须在pdb中创建
create user username identified by "password" default tablespace tbs temporary tablespace temp;
--授权
grant connect,resource to username;
5 Tns
检查监听是否存在
lsnrctl status
service必须存在,tns中的servicename就是写这个,12c通过ip:port和service name来连接pdb
在远端创建tns并做连接测试,其他节点替换ip和tns串名即可
tnschuan =
(description =
(address_list =
(load_balance = off)
(failover = on)
(address = (protocol = tcp)(host = xx.xx.xx.xx)(port = 1521))
(address = (protocol = tcp)(host = xx.xxx.xx.xx)(port = 1521))
)
(connect_data =
(service_name = epmsbi)
(failover_mode =
(type=select)
(method=basic)
)
)
)