使用oracle工具创建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