測試環境

源 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

PDB克隆遷移轉換

相關文章