12C添加pdb后创建用户没有相应的表空间

环境:

OS:Centos 7

DB:12.2.0.1

 

 

1.在现有的cdb下创建pdb
create pluggable database ora12cpdb2 admin user hxl identified by oracle create_file_dest='/u01/app/oracle/oradata/ora12c/ora12cpdb2';
SQL> alter pluggable database ORA12CPDB2 open;

Warning: PDB altered with errors.
打开数据库提示告警信息

2.查看pdb情况
SQL> show pdbs

    CON_ID CON_NAME                       OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
         2 PDB$SEED                       READ ONLY  NO
         3 ORA12CPDB1                     READ WRITE NO
         4 ORA12CPDB2                     READ WRITE YES

发现这里是使用RESTRICTED的模式打开的

3.查看错误日志
set linesize 1000;
column name format a16;
column cause format a16;
column type format a16;
column message format a100;
column status format a16;
SQL> select name,cause,type,message,status from PDB_PLUG_IN_VIOLATIONs order by name;

NAME             CAUSE            TYPE             MESSAGE                                                                                              STATUS
---------------- ---------------- ---------------- ---------------------------------------------------------------------------------------------------- ----------------
ORA12CPDB2       Sync Failure     ERROR            Sync PDB failed with ORA-959 during 'create user c##goldengate identified by * default tablespace tp PENDING
                                                   s_goldengate temporary tablespace TEMP quota unlimited on tps_goldengate container = all'

发现这里创建公共用户的时候报错,这个用户是之前部署ogg时候创建的,这里新的pdb没有对应的表空间 tps_goldengate

4.解决办法
创建表空间
SQL> alter session set container=ORA12CPDB2;

Session altered.

SQL> select file_name from dba_data_files;

FILE_NAME
--------------------------------------------------------------------------------------------------------------------
/u01/app/oracle/oradata/ora12c/ora12cpdb2/ORA12C/9EE5C97CA5FB073FE055000000000001/datafile/o1_mf_system_h4s8c4n9_.dbf
/u01/app/oracle/oradata/ora12c/ora12cpdb2/ORA12C/9EE5C97CA5FB073FE055000000000001/datafile/o1_mf_sysaux_h4s8c4nl_.dbf
/u01/app/oracle/oradata/ora12c/ora12cpdb2/ORA12C/9EE5C97CA5FB073FE055000000000001/datafile/o1_mf_undotbs1_h4s8c4nm_.dbf


create tablespace tps_goldengate datafile '/u01/app/oracle/oradata/ora12c/ora12cpdb2/ORA12C/9EE5C97CA5FB073FE055000000000001/datafile/tps_goldengate01.dbf' size 100M autoextend on next 10m MAXSIZE unlimited;

5.重启pdb
alter session set container=cdb$root;
alter pluggable database ORA12CPDB2 close;
alter pluggable database ORA12CPDB2 open;
SQL> show pdbs

    CON_ID CON_NAME                       OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
         2 PDB$SEED                       READ ONLY  NO
         3 ORA12CPDB1                     READ WRITE NO
         4 ORA12CPDB2                     READ WRITE NO