克隆远程PDB报错处理

克隆远程PDB

[oracle@rac01 ~]$ sqlplus / as sysdba

SQL*Plus: Release 19.0.0.0.0 - Production on Wed Apr 8 10:27:24 2020
Version 19.3.0.0.0

Copyright (c) 1982, 2019, Oracle.  All rights reserved.


Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.3.0.0.0

SYS@cdb1-CDB$ROOT> 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
	 5 PDB3 			  READ WRITE NO
	 7 PDB4 			  READ WRITE YES
SYS@cdb1-CDB$ROOT> select file_name from cdb_data_files;

FILE_NAME
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
/u01/app/oracle/oradata/CDB1/pdb1/system01.dbf
/u01/app/oracle/oradata/CDB1/pdb1/sysaux01.dbf
/u01/app/oracle/oradata/CDB1/pdb1/undotbs01.dbf
/u01/app/oracle/oradata/CDB1/pdb1/users01.dbf
/u01/app/oracle/oradata/CDB1/system01.dbf
/u01/app/oracle/oradata/CDB1/sysaux01.dbf
/u01/app/oracle/oradata/CDB1/undotbs01.dbf
/u01/app/oracle/oradata/CDB1/users01.dbf
/u01/app/oracle/oradata/CDB1/pdb2/system01.dbf
/u01/app/oracle/oradata/CDB1/pdb2/sysaux01.dbf
/u01/app/oracle/oradata/CDB1/pdb2/undotbs01.dbf
/u01/app/oracle/oradata/CDB1/pdb2/user01.dbf
/u01/app/oracle/oradata/CDB1/pdb3/users01.dbf
/u01/app/oracle/oradata/CDB1/pdb3/undotbs01.dbf
/u01/app/oracle/oradata/CDB1/pdb3/sysaux01.dbf
/u01/app/oracle/oradata/CDB1/pdb3/system01.dbf

16 rows selected.

Elapsed: 00:00:00.34

SYS@cdb1-CDB$ROOT> !mkdir -p /u01/app/oracle/oradata/CDB1/pdb5/

SYS@cdb1-CDB$ROOT> create pluggable database pdb5 from non$cdb@db_vzoom create_file_dest='/u01/app/oracle/oradata/CDB1/pdb5/';
create pluggable database pdb5 from non$cdb@db_vzoom create_file_dest='/u01/app/oracle/oradata/CDB1/pdb5/'
*
ERROR at line 1:
ORA-17628: Oracle error 1031 returned by remote Oracle server
ORA-01031: insufficient privileges


Elapsed: 00:00:00.57
复制

根据报错可以发现是远程数据库没有权限

登录远程数据库授权

[C:\~]$ sqlplus / as sysdba

SQL*Plus: Release 12.2.0.1.0 Production on Thu Apr 9 09:27:46 2020

Copyright (c) 1982, 2016, Oracle.  All rights reserved.


Connected to:
Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production

SQL> select * from dba_sys_privs where GRANTEE='SYSTEM';

GRANTE PRIVILEGE				ADM COM INH
------ ---------------------------------------- --- --- ---
SYSTEM GLOBAL QUERY REWRITE			NO  YES YES
SYSTEM CREATE TABLE				NO  YES YES
SYSTEM DEQUEUE ANY QUEUE			YES YES YES
SYSTEM ENQUEUE ANY QUEUE			YES YES YES
SYSTEM SELECT ANY TABLE 			NO  YES YES
SYSTEM MANAGE ANY QUEUE 			YES YES YES
SYSTEM UNLIMITED TABLESPACE			NO  YES YES
SYSTEM CREATE MATERIALIZED VIEW 		NO  YES YES

8 rows selected.

SQL> grant create pluggable database to system;

Grant succeeded.

SQL> select * from dba_sys_privs where GRANTEE='SYSTEM';

GRANTE PRIVILEGE				ADM COM INH
------ ---------------------------------------- --- --- ---
SYSTEM CREATE PLUGGABLE DATABASE		NO  NO	NO
SYSTEM GLOBAL QUERY REWRITE			NO  YES YES
SYSTEM CREATE TABLE				NO  YES YES
SYSTEM DEQUEUE ANY QUEUE			YES YES YES
SYSTEM ENQUEUE ANY QUEUE			YES YES YES
SYSTEM SELECT ANY TABLE 			NO  YES YES
SYSTEM MANAGE ANY QUEUE 			YES YES YES
SYSTEM UNLIMITED TABLESPACE			NO  YES YES
SYSTEM CREATE MATERIALIZED VIEW 		NO  YES YES

9 rows selected.

复制

返回容器数据库继续创建PDB

SYS@cdb1-CDB$ROOT> create pluggable database pdb5 from non$cdb@db_vzoom create_file_dest='/u01/app/oracle/oradata/CDB1/pdb5/';

Pluggable database created.

Elapsed: 00:04:08.15
SYS@cdb1-CDB$ROOT> 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
	 5 PDB3 			  READ WRITE NO
	 7 PDB4 			  READ WRITE YES
	 8 PDB5 			  MOUNTED
SYS@cdb1-CDB$ROOT> !ls -l /u01/app/oracle/oradata/CDB1/pdb5/
总用量 0
drwxr-x---. 3 oracle oinstall 46 4月   8 10:38 CDB1

SYS@cdb1-CDB$ROOT> !ls -l /u01/app/oracle/oradata/CDB1/pdb5/CDB1/
总用量 0
drwxr-x---. 3 oracle oinstall 22 4月   8 10:38 A2BF615A45775137E055EDA2F200CE88

SYS@cdb1-CDB$ROOT> exit
Disconnected from Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.3.0.0.0

复制

创建成功添加TNS

[oracle@rac01 ~]$ cd $ORACLE_HOME/
[oracle@rac01 dbhome_1]$ cd network/admin/
[oracle@rac01 admin]$ vim tnsnames.ora 
复制

PDB5 =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = rac01)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = pdb5.vzoom.com)
)
)

[oracle@rac01 admin]$ lsnrctl status

LSNRCTL for Linux: Version 19.0.0.0.0 - Production on 08-APR-2020 10:53:32

Copyright (c) 1991, 2019, Oracle.  All rights reserved.

Connecting to (ADDRESS=(PROTOCOL=tcp)(HOST=)(PORT=1521))
STATUS of the LISTENER
------------------------
Alias                     LSNRCTL
Version                   TNSLSNR for Linux: Version 19.0.0.0.0 - Production
Start Date                24-MAR-2020 16:25:35
Uptime                    14 days 18 hr. 27 min. 57 sec
Trace Level               off
Security                  ON: Local OS Authentication
SNMP                      OFF
Listener Parameter File   /u01/app/oracle/product/19.3.0/dbhome_1/network/admin/listener.ora
Listener Log File         /u01/app/oracle/diag/tnslsnr/rac01/lsnrctl/alert/log.xml
Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=rac01)(PORT=1521)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcps)(HOST=rac01)(PORT=5500))(Security=(my_wallet_directory=/u01/app/oracle/admin/cdb1/xdb_wallet))(Presentation=HTTP)(Session=RAW))
Services Summary...
Service "86b637b62fdf7a65e053f706e80a27ca.vzoom.com" has 1 instance(s).
  Instance "cdb1", status READY, has 1 handler(s) for this service...
Service "a196aa6bf8a7293fe055eda2f200ce88.vzoom.com" has 1 instance(s).
  Instance "cdb1", status READY, has 1 handler(s) for this service...
Service "a2af4a277fd7218fe055eda2f200ce88.vzoom.com" has 1 instance(s).
  Instance "cdb1", status READY, has 1 handler(s) for this service...
Service "a2afa232917b2371e055eda2f200ce88.vzoom.com" has 1 instance(s).
  Instance "cdb1", status READY, has 1 handler(s) for this service...
Service "a2b08aaab6142635e055eda2f200ce88.vzoom.com" has 1 instance(s).
  Instance "cdb1", status READY, has 1 handler(s) for this service...
Service "a2bf615a45775137e055eda2f200ce88.vzoom.com" has 1 instance(s).
  Instance "cdb1", status READY, has 1 handler(s) for this service...
Service "cdb1.vzoom.com" has 1 instance(s).
  Instance "cdb1", status READY, has 1 handler(s) for this service...
Service "cdb1XDB.vzoom.com" has 1 instance(s).
  Instance "cdb1", status READY, has 1 handler(s) for this service...
Service "pdb1.vzoom.com" has 1 instance(s).
  Instance "cdb1", status READY, has 1 handler(s) for this service...
Service "pdb2.vzoom.com" has 1 instance(s).
  Instance "cdb1", status READY, has 1 handler(s) for this service...
Service "pdb3.vzoom.com" has 1 instance(s).
  Instance "cdb1", status READY, has 1 handler(s) for this service...
Service "pdb4.vzoom.com" has 1 instance(s).
  Instance "cdb1", status READY, has 1 handler(s) for this service...
Service "pdb5.vzoom.com" has 1 instance(s).
  Instance "cdb1", status READY, has 1 handler(s) for this service...
The command completed successfully

[oracle@rac01 admin]$ sqlplus sys/test@pdb5 as sysdba

SQL*Plus: Release 19.0.0.0.0 - Production on Wed Apr 8 10:55:22 2020
Version 19.3.0.0.0

Copyright (c) 1982, 2019, Oracle.  All rights reserved.


Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.3.0.0.0

SYS@cdb1-PDB5> @?/rdbms/admin/noncdb_to_pdb.sql
.
.
.
SYS@cdb1-PDB5> set timing ON
SYS@cdb1-PDB5> set trimout ON
SYS@cdb1-PDB5> set trimspool ON
SYS@cdb1-PDB5> set underline "-"
SYS@cdb1-PDB5> set verify OFF
SYS@cdb1-PDB5> set wrap ON
SYS@cdb1-PDB5> set xmloptimizationcheck OFF
SYS@cdb1-PDB5> 
SYS@cdb1-PDB5> show pdbs;

    CON_ID CON_NAME                       OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
         8 PDB5                           MOUNTED
SYS@cdb1-PDB5> exit
Disconnected from Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.3.0.0.0
sq[oracle@rac01 admin]$ sqlplus / as sysdba

SQL*Plus: Release 19.0.0.0.0 - Production on Wed Apr 8 11:13:54 2020
Version 19.3.0.0.0

Copyright (c) 1982, 2019, Oracle.  All rights reserved.


Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.3.0.0.0

SYS@cdb1-CDB$ROOT> 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
	 5 PDB3 			  READ WRITE NO
	 7 PDB4 			  READ WRITE YES
	 8 PDB5 			  MOUNTED
SYS@cdb1-CDB$ROOT> alter pluggable database pdb5 open;

Warning: PDB altered with errors.

Elapsed: 00:00:07.25
SYS@cdb1-CDB$ROOT> 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
	 5 PDB3 			  READ WRITE NO
	 7 PDB4 			  READ WRITE YES
	 8 PDB5 			  READ WRITE YES

复制