本文介绍Oracle 12数据库新特性之三，创建PDB的方法。
创建PDB的方法一：从pdb$seed创建pdb
使用种子PDB PDB$SEED在容器数据库cdb1中创建新的PDB:pdb2
查看 pdb 状态:
SYS@cdb1>show pdbs
CON_ID CON_NAME OPEN MODE RESTRICTED
--------------------------------------------------
2 PDB$SEED READ ONLY NO
3 PDB1 READ WRITE NO
查看 pdb$seed 数据文件路径:
SYS@cdb1> alter session set container=pdb$seed;
SYS@cdb1> select file_name from cdb_data_files;
FILE_NAME
-----------------------------------------------------
/u01/app/oracle/oradata/cdb1/pdbseed/system01.dbf
/u01/app/oracle/oradata/cdb1/pdbseed/sysaux01.dbf
/u01/app/oracle/oradata/cdb1/pdbseed/undotbs01.dbf
切回 cdb1
SYS@cdb1> conn / as sysdba
SYS@cdb1> alter session set container=cdb$root;
SYS@cdb1> show pdbs
创建新 PDB 目录
SYS@cdb1> !mkdir /u01/app/oracle/oradata/cdb1/pdb2
创建 pdb2
SYS@cdb1> create pluggable database pdb2 admin user pdb2_admin identified by oracle roles=(connect) file_name_convert=(’/u01/app/oracle/oradata/cdb1/pdbseed’,’/u01/app/oracle/oradata/cdb1/pdb2’);
或者省略一样的路径/u01/app/oracle/oradata/cdb1/
SYS@cdb1> create pluggable database pdb2 admin user pdb2_admin identified by oracle roles=(connect) file_name_convert=(‘pdbseed’,‘pdb2’);
Pluggable database created.
查看新创建的pdb2
SYS@cdb1> show pdbs
CON_ID CON_NAME OPEN MODE RESTRICTED
---------------------------------------------------------------
2 PDB$SEED READ ONLY NO
3 PDB1 READ WRITE NO
4 PDB2 MOUNTED
SYS@cdb1>
查看监听程序状态
$lsnrctl status
Service “pdb2” has 1 instance(s).
Instance “cdb1”, status READY, has 1 handler(s) for this service…
打开pdb2：
SYS@cdb1> alter pluggable database pdb2 open;
Pluggable database altered.
SYS@cdb1> show pdbs
CON_ID CON_NAME OPEN MODE RESTRICTED
--------------------------------------------
2 PDB$SEED READ ONLY NO
3 PDB1 READ WRITE NO
4 PDB2 READ WRITE NO
SYS@cdb1>
查看 pdb2 数据文件
SYS@cdb1> col file_name for a50
SYS@cdb1> select file_id,file_name,tablespace_name,con_id from cdb_data_files where con_id=4;
FILE_ID FILE_NAME TABLESPACE_NAME CON_ID
---------------------------------------------------------------------
13 /u01/app/oracle/oradata/cdb1/pdb2/system01.dbf SYSTEM 4
14 /u01/app/oracle/oradata/cdb1/pdb2/sysaux01.dbf SYSAUX 4
15 /u01/app/oracle/oradata/cdb1/pdb2/undotbs01.dbf UNDOTBS1 4
SYS@cdb1>
配置 tnsnames.ora
$ cd $ORACLE_HOME/network/admin
$ vi tnsnames.ora
pdb2 =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = enmoedu1)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = pdb2)
)
)
pdb2 通过服务名登录
$ sqlplus sys/oracle@pdb2 as sysdba
SYS@pdb2> show pdbs
CON_ID CON_NAME OPEN MODE RESTRICTED
------ ------- -------- --------
4 PDB2 READ WRITE NO
SYS@pdb2>
SYS@pdb2> col username for a30
SYS@pdb2> select username,common,con_id from cdb_users where account_status=‘OPEN’;
USERNAME COM CON_ID
-------- ---- -----
SYS YES 4
SYSTEM YES 4
PDB2_ADMIN NO 4
SYS@pdb2>
创建并指定默认表空间
SYS@pdb2> create tablespace users datafile ‘/u01/app/oracle/oradata/cdb1/pdb2/users01.dbf’ size 50m;
SYS@pdb2> alter pluggable database default tablespace users;
SYS@cdb1> select file_id,file_name,tablespace_name,con_id from cdb_data_files where con_id=4;
FILE_ID FILE_NAME TABLESPACE_NAME CON_ID
------ -------------------------------------------- -------- ----
13 /u01/app/oracle/oradata/cdb1/pdb2/system01.dbf SYSTEM 4
14 /u01/app/oracle/oradata/cdb1/pdb2/sysaux01.dbf SYSAUX 4
15 /u01/app/oracle/oradata/cdb1/pdb2/undotbs01.dbf UNDOTBS1 4
16 /u01/app/oracle/oradata/cdb1/pdb2/users01.dbf USERS 4
查看临时表空间：
SYS@pdb2> select file_id,file_name,tablespace_name,con_id from cdb_temp_files;
FILE_ID FILE_NAME TABLESPACE_NAME CON_ID
------ ---------------------------------------------------------------- ---- ---
4 /u01/app/oracle/oradata/cdb1/pdb2/temp012020-08-16_11-56-49-011-AM.dbf TEMP 4
增加一个tmep文件：
SYS@pdb2>alter tablespace temp add tempfile ‘/u01/app/oracle/oradata/cdb1/pdb2/temp01.dbf’ size 10m autoextend on;
删除原来的temp文件：
SYS@pdb2>alter tablespace temp drop tempfile ‘/u01/app/oracle/oradata/cdb1/pdb2/temp012020-08-16_11-56-49-011-AM.dbf’;
再次查看临时表空间：
SYS@pdb2> select file_id,file_name,tablespace_name,con_id from cdb_temp_files;
FILE_ID FILE_NAME TABLESPACE_NAME CON_ID
------- ------------------------------------- -------------- -----
4 /u01/app/oracle/oradata/cdb1/pdb2/temp01.dbf TEMP 4
SYS@pdb2>
设置默认临时表空间：
SYS@pdb2> alter database default temporary tablespace temp;
查看默认的临时表空间：
SYS@PROD>select PROPERTY_NAME,PROPERTY_VALUE from database_properties where property_name like ‘DEFAULT_TEMP%’;
PROPERTY_NAME PROPERTY_VALUE
------------------ ----------------
DEFAULT_TEMP_TABLESPACE TEMP
相关阅读
您阅读本篇文章共花了：
发表评论