oracle19c RAC远程克隆PDB

19c RAC远程克隆PDB

192.168.105.70-----to----192.168.105.25

     

                                                                ----write by  liqiang  20210708

192.168.105.70:克隆用户需要的权限

CREATE USER c##clone  IDENTIFIED BY remote_clon55e_user CONTAINER=ALL;

GRANT CREATE SESSION, CREATE PLUGGABLE DATABASE TO c##clone  CONTAINER=ALL;

192.168.105.25:

create public database link link_vltdb70 connect to c##clone identified by "remote_clon55e_user"

using '(DESCRIPTION =

(ADDRESS_LIST =

(ADDRESS = (PROTOCOL = TCP)(HOST =192.168.105.70)(PORT = 1521))

)

(CONNECT_DATA =

(SERVER = DEDICATED)

(SERVICE_NAME =vltdb)

)

)';

192.168.105.70:

SQL> show  pdbs;

    CON_ID CON_NAME   OPEN MODE  RESTRICTED

---------- ------------------------------ ---------- ----------

 3 VLTDB   READ WRITE NO

SQL> set line 200 pages 999

col name for a99;

select FILE#,bytes/1024/1024 TOTAL_M,NAME, STATUS from v$datafile;

col FILE_NAME for a80;

select file_name from dba_temp_files;

SQL> SQL>

FILE# TOTAL_M NAME STATUS

---------- ----------                        ---------------------------- --------------

10 5120 +DATA/VLT02DB/AA128082CA7E81BEE0534769A8C0FCB4/DATAFILE/system.283.1045410233 SYSTEM

11 5120 +DATA/VLT02DB/AA128082CA7E81BEE0534769A8C0FCB4/DATAFILE/sysaux.284.1045410233 ONLINE

12 30720 +DATA/VLT02DB/AA128082CA7E81BEE0534769A8C0FCB4/DATAFILE/undotbs1.282.1045410233 ONLINE

13 32760 +DATA/VLT02DB/AA128082CA7E81BEE0534769A8C0FCB4/DATAFILE/undo_2.286.1045410257 ONLINE

14 2048 +DATA/VLT02DB/AA128082CA7E81BEE0534769A8C0FCB4/DATAFILE/users.287.1045410257 ONLINE

15 30720 +DATA/VLT02DB/DATAFILE/vrbt_space_01.dbf ONLINE

16 30720 +DATA/VLT02DB/DATAFILE/vrbt_space_02.dbf ONLINE

46 30720 +DATA/VLT02DB/AA128082CA7E81BEE0534769A8C0FCB4/DATAFILE/vrbt_space.3240.1071224615 ONLINE

47 30720 +DATA/VLT02DB/AA128082CA7E81BEE0534769A8C0FCB4/DATAFILE/vrbt_space.3866.1071224825 ONLINE

48 30720 +DATA/VLT02DB/AA128082CA7E81BEE0534769A8C0FCB4/DATAFILE/vrbt_space.3781.1071225059 ONLINE

49 30720 +DATA/VLT02DB/AA128082CA7E81BEE0534769A8C0FCB4/DATAFILE/vrbt_space.3822.1071225331 ONLINE

50 30720 +DATA/VLT02DB/AA128082CA7E81BEE0534769A8C0FCB4/DATAFILE/vrbt_space.3817.1071225597 ONLINE

51 30720 +DATA/VLT02DB/AA128082CA7E81BEE0534769A8C0FCB4/DATAFILE/vrbt_space.3769.1071225899 ONLINE

52 30720 +DATA/VLT02DB/AA128082CA7E81BEE0534769A8C0FCB4/DATAFILE/vrbt_space.2580.1071226213 ONLINE

53 30720 +DATA/VLT02DB/AA128082CA7E81BEE0534769A8C0FCB4/DATAFILE/vrbt_space.3749.1071226517 ONLINE

54 30720 +DATA/VLT02DB/AA128082CA7E81BEE0534769A8C0FCB4/DATAFILE/vrbt_space.3032.1071226829 ONLINE

55 30720 +DATA/VLT02DB/AA128082CA7E81BEE0534769A8C0FCB4/DATAFILE/vrbt_space.3096.1071227107 ONLINE

56 30720 +DATA/VLT02DB/AA128082CA7E81BEE0534769A8C0FCB4/DATAFILE/vrbt_space.2887.1071227367 ONLINE

57 10240 +DATA/VLT02DB/AA128082CA7E81BEE0534769A8C0FCB4/DATAFILE/vrbt_space.2551.1071227629 ONLINE

58 30720 +DATA/VLT02DB/AA128082CA7E81BEE0534769A8C0FCB4/DATAFILE/undotbs1.3253.1074725563 ONLINE

20 rows selected.

SQL> SQL>

FILE_NAME

------------------------------------------------------------------------------

+DATA/VLT02DB/AA128082CA7E81BEE0534769A8C0FCB4/TEMPFILE/temp.2848.1074725431

+DATA/VLT02DB/AA128082CA7E81BEE0534769A8C0FCB4/TEMPFILE/temp.285.1045410239

+DATA/VLT02DB/AA128082CA7E81BEE0534769A8C0FCB4/TEMPFILE/temp.348.1074725455

-------从上述输出看，数据文件分布在三个不同目录：

1，+DATA/VLT02DB/AA128082CA7E81BEE0534769A8C0FCB4/DATAFILE/

2，+DATA/VLT02DB/DATAFILE/

3，+DATA/VLT02DB/AA128082CA7E81BEE0534769A8C0FCB4/TEMPFILE/

第三个临时文件需要手工创建（/opt/oracle/oradata/vltpdb/TEMPFILE）才可以完成整体clone，否则会报错：

192.168.105.25:

SQL> CREATE PLUGGABLE DATABASE VLTDB FROM VLTDB@link_vltdb70 PATH_PREFIX = '/opt/oracle/oradata/vltpdb/' FILE_NAME_CONVERT = ('+DATA/VLT02DB/','/opt/oracle/oradata/vltpdb/');

CREATE PLUGGABLE DATABASE VLTDB FROM VLTDB@link_vltdb70 PATH_PREFIX = '/opt/oracle/oradata/vltpdb/' FILE_NAME_CONVERT = ('+DATA/VLT02DB/','/opt/oracle/oradata/vltpdb/')

*

ERROR at line 1:

ORA-01119: error in creating database file

'/opt/oracle/oradata/vltpdb/AA128082CA7E81BEE0534769A8C0FCB4/TEMPFILE/temp.285.1045410239'

ORA-27040: file create error, unable to create file

Linux-x86_64 Error: 2: No such file or directory

Additional information: 1

正确的sql应该是：

CREATE PLUGGABLE DATABASE VLTDB FROM VLTDB@link_vltdb70 PATH_PREFIX = '/opt/oracle/oradata/vltpdb/' FILE_NAME_CONVERT = ('+DATA/VLT02DB/','/opt/oracle/oradata/vltpdb/','+DATA/VLT02DB/AA128082CA7E81BEE0534769A8C0FCB4/','/opt/oracle/oradata/vltpdb/');

或

CREATE PLUGGABLE DATABASE VLTDB FROM VLTDB@link_vltdb70 PATH_PREFIX = '/opt/oracle/oradata/vltpdb/' FILE_NAME_CONVERT = ('+DATA/VLT02DB/','/opt/oracle/oradata/vltpdb/','+DATA/VLT02DB/AA128082CA7E81BEE0534769A8C0FCB4/TEMPFILE/','/opt/oracle/oradata/vltpdb/','+DATA/VLT02DB/AA128082CA7E81BEE0534769A8C0FCB4/DATAFILE/','/opt/oracle/oradata/vltpdb/');