揭秘Oracle 12c数据库新特性之三：创建PDB的方法（一）从pdb$seed创建pdb

本文链接：https://blog.csdn.net/lzyever/article/details/136310049

本文介绍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