从NONCDB复制到PDB报ORA-17628错误

郑全

--使用SYSTEM创建的DBLINK
SQL> create database link link_orcl
  2    connect to system identified by oracle_4U
  3     using '192.168.133.134:1521/orcl';

Database link created.

SQL> select * from dual@link_orcl;

D
-
X




SQL> create pluggable database pdb_orcl
  2    from non$cdb@link_orcl
  3    ;
create pluggable database pdb_orcl
*
ERROR at line 1:
ORA-17628: Oracle error 1031 returned by remote Oracle server
ORA-01031: insufficient privileges

郑全

这个错误是执行复制的这个DBLINK连接用户没有直接授权create pluggable database 导致，即使是DBA角色也是如此。
检查源端NONCDB库中SYSTEM 的权限：
SQL> select privilege from dba_sys_privs where  grantee='SYSTEM';         

PRIVILEGE
----------------------------------------
GLOBAL QUERY REWRITE
CREATE TABLE
DEQUEUE ANY QUEUE
ENQUEUE ANY QUEUE
SELECT ANY TABLE
MANAGE ANY QUEUE
UNLIMITED TABLESPACE
CREATE MATERIALIZED VIEW

确实没有CREATE PLUGGABLE DATABASE 权限，、
只在源端授予改权限给SYSTEM即可

SQL> grant create PLUGGABLE DATABASE to system;

Grant succeeded.

SQL> select privilege from dba_sys_privs where  grantee='SYSTEM';

PRIVILEGE
----------------------------------------
GLOBAL QUERY REWRITE
CREATE TABLE
DEQUEUE ANY QUEUE
ENQUEUE ANY QUEUE
CREATE PLUGGABLE DATABASE
SELECT ANY TABLE
MANAGE ANY QUEUE
UNLIMITED TABLESPACE
CREATE MATERIALIZED VIEW

9 rows selected.

之后，再去目标端执行


sql> create pluggable database pdb_orcl
    from non$cdb@link_orcl；

成功。

郑全

在 19.10 环境中遇到。

郑全

SQL> conn system/oracle_4U
Connected.
SQL> select privilege from dba_sys_privs where grantee like 'SYSTEM' and privilege like '%PLUGG%';

PRIVILEGE
----------------------------------------


SQL> desc session_privs;
Name                                      Null?    Type
----------------------------------------- -------- ----------------------------
PRIVILEGE                                 NOT NULL VARCHAR2(40)

SQL> select * from session_privs where privilege like '%PLUGG%';

PRIVILEGE
----------------------------------------
CREATE PLUGGABLE DATABASE

需要显示赋权。

参考：
     ORA-01031: Insufficient Privileges When Cloning PDB - After Applying Oct 2018 Patchset (Doc ID 2485839.1)