测试环境
源 10.8.98.150 19c
目标 10.8.98.103 19c
noncdb 迁移转成pdb见
http://blog.itpub.net/70004783/viewspace-2854421/
1、本地克隆pdb
1.1环境检查
Connected to: Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production Version 19.3.0.0.0 SQL> show pdbs; CON_ID CON_NAME OPEN MODE RESTRICTED ---------- ------------------------------ ---------- ---------- 2 PDB$SEED READ ONLY NO
1.2 通过PDB$SEED创建新的pdb
SQL> CREATE PLUGGABLE DATABASE PDB1 ADMIN USER PDB1ADMIN IDENTIFIED BY PASS ROLES=(CONNECT) FILE_NAME_CONVERT=('/u01/app/oracle/oradata/PROD/pdbseed/','/u01/app/oracle/oradata/PROD/pdb1/');
Pluggable database created.
1.3通过pdb1克隆创建新的pdb2
SQL> create pluggable database pdb2 from pdb1 file_name_convert=('pdb1','pdb2');
Pluggable database created.
SQL> show pdbs;
CON_ID CON_NAME OPEN MODE RESTRICTED
---------- ------------------------------ ---------- ----------
2 PDB$SEED READ ONLY NO
3 PDB1 READ WRITE NO
4 PDB2 MOUNTED
#####################################
兼容性参考文档http://blog.itpub.net/70004783/viewspace-2854421/
2、跨服务器克隆pdb（我这边是跨服务器克隆插拔式数据库PDB2）
2.1本地复制异地进行插入方式
2.1.1源环境克隆
源pdb开启到read only模式， 再生成xml文件。
SQL> show pdbs; CON_ID CON_NAME OPEN MODE RESTRICTED ---------- ------------------------------ ---------- ---------- 4 PDB2 MOUNTED SQL> alter pluggable database open read only; Pluggable database altered. SQL> show pdbs; CON_ID CON_NAME OPEN MODE RESTRICTED ---------- ------------------------------ ---------- ---------- 4 PDB2 READ ONLY NO SQL> SQL> begin 2 dbms_pdb.describe(PDB_DESCR_FILE=>'/home/oracle/pdb2.xml'); 3 end; 4 / PL/SQL procedure successfully completed. SQL>
复制xml以及pdb2文件到目标环境，且目标环境创建相应的目录
目标环境 mkdir -p /u01/app/oracle/oradata/PROD/pdb2/ 源环境 scp pdb2.xml 10.8.98.103:/home/oracle/ [oracle@dbserver pdb2]$ pwd /u01/app/oracle/oradata/PROD/pdb2 [oracle@dbserver pdb2]$ scp * 10.8.98.103:/u01/app/oracle/oradata/PROD/pdb2/ oracle@10.8.98.103's password: sysaux01.dbf 100% 310MB 77.5MB/s 00:04 system01.dbf 100% 270MB 90.0MB/s 00:03 temp012022-01-29_11-07-13-474-AM.dbf 100% 36MB 61.5MB/s 00:00 undotbs01.dbf 100% 100MB 57.6MB/s 00:01
目标环境插入pdb2
SQL> create pluggable database pdb2 using '/home/oracle/pdb2.xml' nocopy ; create pluggable database pdb2 using '/home/oracle/pdb2.xml' nocopy * ERROR at line 1: ORA-27038: created file already exists ORA-01119: error in creating database file '/u01/app/oracle/oradata/PROD/pdb2/temp012022-01-29_11-07-13-474-AM.dbf' SQL>
报错： 这里设置了tempfile reuse,如果不设置oracle会自动尝试创建tempfile,但是此tempfile已经手工拷贝了,就会报错:
所以将
/u01/app/oracle/oradata/PROD/pdb2/temp012022-01-29_11-07-13-474-AM.dbf
文件在目标端删除后，重新目标端插入pdb2
[oracle@dbserver pdb2]$ rm -f temp012022-01-29_11-07-13-474-AM.dbf SQL> show pdbs; CON_ID CON_NAME OPEN MODE RESTRICTED ---------- ------------------------------ ---------- ---------- 2 PDB$SEED READ ONLY NO 4 PDB1 MOUNTED 5 NONCDB MOUNTED SQL> SQL> SQL> create pluggable database pdb2 using '/home/oracle/pdb2.xml' nocopy ; Pluggable database created. SQL> show pdbs; CON_ID CON_NAME OPEN MODE RESTRICTED ---------- ------------------------------ ---------- ---------- 2 PDB$SEED READ ONLY NO 4 PDB1 MOUNTED 5 NONCDB MOUNTED 7 PDB2 MOUNTED SQL> alter pluggable database pdb2 open; Pluggable database altered. SQL> show pdbs; CON_ID CON_NAME OPEN MODE RESTRICTED ---------- ------------------------------ ---------- ---------- 2 PDB$SEED READ ONLY NO 4 PDB1 MOUNTED 5 NONCDB MOUNTED 7 PDB2 READ WRITE NO SQL>
#########################################
3、dblink方式
源端创建用户用于dblink
SQL> alter session set container = pdb3; Session altered. SQL> show pdbs; CON_ID CON_NAME OPEN MODE RESTRICTED ---------- ------------------------------ ---------- ---------- 5 PDB3 READ WRITE NO SQL> SQL> create user remote_clone_user identified by remote_clone_user; User created. SQL> grant create session,create pluggable database to remote_clone_user; Grant succeeded. SQL>
在目标端创建到非cdb的数据库链接
首先目标端的tnsnames.ora添加本地链接名称给dblink使用，我这里加了tns_150_pdb3,并且测试tnsping OK.
[oracle@dbserver admin]$ tnsping tns_150_pdb3 TNS Ping Utility for Linux: Version 19.0.0.0.0 - Production on 15-FEB-2022 10:49:33 Copyright (c) 1997, 2019, Oracle. All rights reserved. Used parameter files: Used TNSNAMES adapter to resolve the alias Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = 10.8.98.150)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICA TED) (SERVICE_NAME = pdb3))) OK (110 msec) [oracle@dbserver admin]$ cat tnsnames.ora # tnsnames.ora Network Configuration File: /u01/app/oracle/product/19c/dbhome_1/network/admin/tnsnames.ora # Generated by Oracle configuration tools. PROD = (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = dbserver)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = prod) ) ) tns_150 = (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = 10.8.98.150)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = prod) ) ) tns_150_pdb3 = (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = 10.8.98.150)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = pdb3) ) ) LISTENER_PROD = (ADDRESS = (PROTOCOL = TCP)(HOST = dbserver)(PORT = 1521))
在目标端创建dblink
SQL> create database link pdb3_clone_link connect to remote_clone_user identified by remote_clone_user using 'tns_150_pdb3'; Database link created.
登录测试
SQL> desc user_tables@PDB3_CLONE_LINK; Name Null? Type ----------------------------------------- -------- ---------------------------- TABLE_NAME NOT NULL VARCHAR2(128) TABLESPACE_NAME VARCHAR2(30) CLUSTER_NAME VARCHAR2(128) IOT_NAME VARCHAR2(128) STATUS VARCHAR2(8) INITIAL_EXTENT NUMBER NEXT_EXTENT NUMBER MIN_EXTENTS NUMBER 。。。。。。。。。。。。。。。。。。。。。。。。。。。。。。。。
在目标端执行数据库克隆，记住 先在 目标端创建好对应的目录
SQL> !mkdir -p /u01/app/oracle/oradata/PROD/pdb3_clone/
SQL> create pluggable database pdb3_clone from pdb3@pdb3_clone_link file_name_convert=('/u01/app/oracle/oradata/PROD/pdb3/','/u01/app/oracle/oradata/PROD/pdb3_clone/');
Pluggable database created.
SQL> show pdbs;
CON_ID CON_NAME OPEN MODE RESTRICTED
---------- ------------------------------ ---------- ----------
2 PDB$SEED READ ONLY NO
3 PDB3_CLONE READ WRITE NO
4 PDB1 MOUNTED
5 NONCDB MOUNTED
7 PDB2 READ WRITE NO