多租户数据库部分复制到目标库

windjack · 发表于 2020-8-25 11:30:52

本帖最后由 windjack 于 2020-8-25 12:01 编辑

多租户数据库部分复制到目标库

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

参见官方文档：https://docs.oracle.com/database/121/RCMRF/rcmsynta020.htm

In this example, the PDBs pdb1, pdb5, and the users tablespace in PDB pdb2 are duplicated. By default, RMAN also duplicates the root and the seed database in the CDB. The auxiliary instance must have been started with an initialization parameter file that contains the declaration enable_pluggable_database=TRUE. You are connected to the root as a user with the common SYSBACKUP privilege.

DUPLICATE TARGET DATABASE TO cdb PLUGGABLE DATABASE pdb1,pdb5 TABLESPACE pdb2:users;

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

测试环境：

OS：Centos7.8

DB: Oracle 12.1.0.2

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

前期工作：

目标数据库创建参数文件、复制口令文件、创建静态监听、创建相关的目录、利用spfile启动到nomount等跟NON-CDB复制时一样（略）

--利用源库备份进行复制，需要在源库中创建整个数据库的备份，包含PDB。

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

--复制过程如下：

[oracle@dbserver cdb1]$ rman target sys/oracle@localhost/sztech auxiliary sys/oracle@localhost/cdb1

Recovery Manager: Release 12.1.0.2.0 - Production on Tue Aug 25 10:49:09 2020

connected to target database: SZTECH (DBID=3337735342)

connected to auxiliary database: CDB1 (not mounted)

RMAN> duplicate target database to "cdb1" pluggable database pdb1 tablespace pdb2:tbs1;

Starting Duplicate Db at 25-AUG-20

using target database control file instead of recovery catalog

allocated channel: ORA_AUX_DISK_1

channel ORA_AUX_DISK_1: SID=249 device type=DISK

current log archived

contents of Memory Script:

{

sql clone "alter system set db_name =

''SZTECH'' comment=

''Modified by RMAN duplicate'' scope=spfile";

sql clone "alter system set db_unique_name =

''CDB1'' comment=

''Modified by RMAN duplicate'' scope=spfile";

shutdown clone immediate;

startup clone force nomount

restore clone primary controlfile;

alter clone database mount;

}

executing Memory Script

sql statement: alter system set db_name = ''SZTECH'' comment= ''Modified by RMAN duplicate'' scope=spfile

sql statement: alter system set db_unique_name = ''CDB1'' comment= ''Modified by RMAN duplicate'' scope=spfile

Oracle instance shut down

Oracle instance started

Total System Global Area 805306368 bytes

Fixed Size 2929552 bytes

Variable Size 318770288 bytes

Database Buffers 478150656 bytes

Redo Buffers 5455872 bytes

Starting restore at 25-AUG-20

allocated channel: ORA_AUX_DISK_1

channel ORA_AUX_DISK_1: SID=12 device type=DISK

channel ORA_AUX_DISK_1: starting datafile backup set restore

channel ORA_AUX_DISK_1: restoring control file

channel ORA_AUX_DISK_1: reading from backup piece +FRA/SZTECH/AUTOBACKUP/2020_08_25/s_1049366761.288.1049366761

channel ORA_AUX_DISK_1: piece handle=+FRA/SZTECH/AUTOBACKUP/2020_08_25/s_1049366761.288.1049366761 tag=TAG20200825T104600

channel ORA_AUX_DISK_1: restored backup piece 1

channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:04

output file name=/home/oracle/cdb1/control01.dbf

Finished restore at 25-AUG-20

database mounted

Automatically adding tablespace SYSTEM

Automatically adding tablespace SYSAUX

Automatically adding tablespace PDB$SEED:SYSTEM

Automatically adding tablespace PDB$SEED:SYSAUX

Automatically adding tablespace PDB2:SYSTEM

Automatically adding tablespace PDB2:SYSAUX

Automatically adding tablespace UNDOTBS1

Checking that duplicated tablespaces are self-contained

Skipping tablespace USERS

contents of Memory Script:

{

set until scn 2807186;

set newname for clone datafile 1 to new;

set newname for clone datafile 3 to new;

set newname for clone datafile 4 to new;

set newname for clone datafile 5 to new;

set newname for clone datafile 7 to new;

set newname for clone datafile 13 to new;

set newname for clone datafile 14 to new;

set newname for clone datafile 15 to new;

set newname for clone datafile 16 to new;

set newname for clone datafile 17 to new;

set newname for clone datafile 18 to new;

restore

clone database

skip forever tablespace "USERS" ;

}

executing Memory Script

executing command: SET until clause

executing command: SET NEWNAME

Starting restore at 25-AUG-20

using channel ORA_AUX_DISK_1

channel ORA_AUX_DISK_1: starting datafile backup set restore

channel ORA_AUX_DISK_1: specifying datafile(s) to restore from backup set

channel ORA_AUX_DISK_1: restoring datafile 00001 to /home/oracle/cdb1/CDB1/datafile/o1_mf_system_%u_.dbf

channel ORA_AUX_DISK_1: restoring datafile 00003 to /home/oracle/cdb1/CDB1/datafile/o1_mf_sysaux_%u_.dbf

channel ORA_AUX_DISK_1: restoring datafile 00004 to /home/oracle/cdb1/CDB1/datafile/o1_mf_undotbs1_%u_.dbf

channel ORA_AUX_DISK_1: reading from backup piece +FRA/SZTECH/BACKUPSET/2020_08_25/nnndf0_tag20200825t104544_0.284.1049366745

channel ORA_AUX_DISK_1: piece handle=+FRA/SZTECH/BACKUPSET/2020_08_25/nnndf0_tag20200825t104544_0.284.1049366745 tag=TAG20200825T104544

channel ORA_AUX_DISK_1: restored backup piece 1

channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:07

channel ORA_AUX_DISK_1: starting datafile backup set restore

channel ORA_AUX_DISK_1: specifying datafile(s) to restore from backup set

channel ORA_AUX_DISK_1: restoring datafile 00013 to /home/oracle/cdb1/CDB1/datafile/o1_mf_system_%u_.dbf

channel ORA_AUX_DISK_1: restoring datafile 00014 to /home/oracle/cdb1/CDB1/datafile/o1_mf_sysaux_%u_.dbf

channel ORA_AUX_DISK_1: restoring datafile 00015 to /home/oracle/cdb1/CDB1/datafile/o1_mf_tbs1_%u_.dbf

channel ORA_AUX_DISK_1: reading from backup piece +FRA/SZTECH/A6FDF91F1CEF1FDFE0537885A8C0A53A/BACKUPSET/2020_08_25/nnndf0_tag20200825t104544_0.285.1049366751

channel ORA_AUX_DISK_1: piece handle=+FRA/SZTECH/A6FDF91F1CEF1FDFE0537885A8C0A53A/BACKUPSET/2020_08_25/nnndf0_tag20200825t104544_0.285.1049366751 tag=TAG20200825T104544

channel ORA_AUX_DISK_1: restored backup piece 1

channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:03

channel ORA_AUX_DISK_1: starting datafile backup set restore

channel ORA_AUX_DISK_1: specifying datafile(s) to restore from backup set

channel ORA_AUX_DISK_1: restoring datafile 00016 to /home/oracle/cdb1/CDB1/datafile/o1_mf_system_%u_.dbf

channel ORA_AUX_DISK_1: restoring datafile 00017 to /home/oracle/cdb1/CDB1/datafile/o1_mf_sysaux_%u_.dbf

channel ORA_AUX_DISK_1: restoring datafile 00018 to /home/oracle/cdb1/CDB1/datafile/o1_mf_tbs1_%u_.dbf

channel ORA_AUX_DISK_1: reading from backup piece +FRA/SZTECH/ADAB61BBA394634DE0537885A8C0C7DD/BACKUPSET/2020_08_25/nnndf0_tag20200825t104544_0.286.1049366755

channel ORA_AUX_DISK_1: piece handle=+FRA/SZTECH/ADAB61BBA394634DE0537885A8C0C7DD/BACKUPSET/2020_08_25/nnndf0_tag20200825t104544_0.286.1049366755 tag=TAG20200825T104544

channel ORA_AUX_DISK_1: restored backup piece 1

channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:03

channel ORA_AUX_DISK_1: starting datafile backup set restore

channel ORA_AUX_DISK_1: specifying datafile(s) to restore from backup set

channel ORA_AUX_DISK_1: restoring datafile 00005 to /home/oracle/cdb1/CDB1/datafile/o1_mf_system_%u_.dbf

channel ORA_AUX_DISK_1: restoring datafile 00007 to /home/oracle/cdb1/CDB1/datafile/o1_mf_sysaux_%u_.dbf

channel ORA_AUX_DISK_1: reading from backup piece +FRA/SZTECH/A69E85502BE7B0AFE0537885A8C0CDD8/BACKUPSET/2020_08_25/nnndf0_tag20200825t104544_0.287.1049366757

channel ORA_AUX_DISK_1: piece handle=+FRA/SZTECH/A69E85502BE7B0AFE0537885A8C0CDD8/BACKUPSET/2020_08_25/nnndf0_tag20200825t104544_0.287.1049366757 tag=TAG20200825T104544

channel ORA_AUX_DISK_1: restored backup piece 1

channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:03

Finished restore at 25-AUG-20

contents of Memory Script:

{

switch clone datafile all;

}

executing Memory Script

datafile 1 switched to datafile copy

input datafile copy RECID=14 STAMP=1049367022 file name=/home/oracle/cdb1/CDB1/datafile/o1_mf_system_hn8z2y51_.dbf

datafile 3 switched to datafile copy

input datafile copy RECID=15 STAMP=1049367022 file name=/home/oracle/cdb1/CDB1/datafile/o1_mf_sysaux_hn8z2y54_.dbf

datafile 4 switched to datafile copy

input datafile copy RECID=16 STAMP=1049367022 file name=/home/oracle/cdb1/CDB1/datafile/o1_mf_undotbs1_hn8z2y56_.dbf

datafile 5 switched to datafile copy

input datafile copy RECID=17 STAMP=1049367022 file name=/home/oracle/cdb1/CDB1/datafile/o1_mf_system_hn8z3ccj_.dbf

datafile 7 switched to datafile copy

input datafile copy RECID=18 STAMP=1049367022 file name=/home/oracle/cdb1/CDB1/datafile/o1_mf_sysaux_hn8z3cc9_.dbf

datafile 13 switched to datafile copy

input datafile copy RECID=19 STAMP=1049367022 file name=/home/oracle/cdb1/CDB1/datafile/o1_mf_system_hn8z357v_.dbf

datafile 14 switched to datafile copy

input datafile copy RECID=20 STAMP=1049367022 file name=/home/oracle/cdb1/CDB1/datafile/o1_mf_sysaux_hn8z357q_.dbf

datafile 15 switched to datafile copy

input datafile copy RECID=21 STAMP=1049367022 file name=/home/oracle/cdb1/CDB1/datafile/o1_mf_tbs1_hn8z357w_.dbf

datafile 16 switched to datafile copy

input datafile copy RECID=22 STAMP=1049367022 file name=/home/oracle/cdb1/CDB1/datafile/o1_mf_system_hn8z389j_.dbf

datafile 17 switched to datafile copy

input datafile copy RECID=23 STAMP=1049367022 file name=/home/oracle/cdb1/CDB1/datafile/o1_mf_sysaux_hn8z389g_.dbf

datafile 18 switched to datafile copy

input datafile copy RECID=24 STAMP=1049367022 file name=/home/oracle/cdb1/CDB1/datafile/o1_mf_tbs1_hn8z389k_.dbf

contents of Memory Script:

{

set until scn 2807186;

recover

clone database

skip forever tablespace "USERS" delete archivelog

;

}

executing Memory Script

executing command: SET until clause

Starting recover at 25-AUG-20

using channel ORA_AUX_DISK_1

Executing: alter database datafile 6 offline drop

starting media recovery

archived log for thread 1 with sequence 25 is already on disk as file +FRA/SZTECH/ARCHIVELOG/2020_08_25/thread_1_seq_25.289.1049366771

archived log for thread 1 with sequence 26 is already on disk as file +FRA/SZTECH/ARCHIVELOG/2020_08_25/thread_1_seq_26.290.1049366971

archived log file name=+FRA/SZTECH/ARCHIVELOG/2020_08_25/thread_1_seq_25.289.1049366771 thread=1 sequence=25

archived log file name=+FRA/SZTECH/ARCHIVELOG/2020_08_25/thread_1_seq_26.290.1049366971 thread=1 sequence=26

media recovery complete, elapsed time: 00:00:01

Finished recover at 25-AUG-20

Oracle instance started

Total System Global Area 805306368 bytes

Fixed Size 2929552 bytes

Variable Size 318770288 bytes

Database Buffers 478150656 bytes

Redo Buffers 5455872 bytes

contents of Memory Script:

{

sql clone "alter system set db_name =

''CDB1'' comment=

''Reset to original value by RMAN'' scope=spfile";

sql clone "alter system reset db_unique_name scope=spfile";

}

executing Memory Script

sql statement: alter system set db_name = ''CDB1'' comment= ''Reset to original value by RMAN'' scope=spfile

sql statement: alter system reset db_unique_name scope=spfile

Oracle instance started

Total System Global Area 805306368 bytes

Fixed Size 2929552 bytes

Variable Size 318770288 bytes

Database Buffers 478150656 bytes

Redo Buffers 5455872 bytes

sql statement: CREATE CONTROLFILE REUSE SET DATABASE "CDB1" RESETLOGS ARCHIVELOG

MAXLOGFILES 16

MAXLOGMEMBERS 3

MAXDATAFILES 1024

MAXINSTANCES 8

MAXLOGHISTORY 292

LOGFILE

GROUP 1 SIZE 50 M ,

GROUP 2 SIZE 50 M ,

GROUP 3 SIZE 50 M

DATAFILE

'/home/oracle/cdb1/CDB1/datafile/o1_mf_system_hn8z2y51_.dbf',

'/home/oracle/cdb1/CDB1/datafile/o1_mf_system_hn8z3ccj_.dbf',

'/home/oracle/cdb1/CDB1/datafile/o1_mf_system_hn8z357v_.dbf',

'/home/oracle/cdb1/CDB1/datafile/o1_mf_system_hn8z389j_.dbf'

CHARACTER SET AL32UTF8

contents of Memory Script:

{

set newname for clone tempfile 1 to new;

set newname for clone tempfile 2 to new;

set newname for clone tempfile 3 to new;

set newname for clone tempfile 4 to new;

switch clone tempfile all;

catalog clone datafilecopy "/home/oracle/cdb1/CDB1/datafile/o1_mf_sysaux_hn8z2y54_.dbf",

"/home/oracle/cdb1/CDB1/datafile/o1_mf_undotbs1_hn8z2y56_.dbf",

"/home/oracle/cdb1/CDB1/datafile/o1_mf_sysaux_hn8z3cc9_.dbf",

"/home/oracle/cdb1/CDB1/datafile/o1_mf_sysaux_hn8z357q_.dbf",

"/home/oracle/cdb1/CDB1/datafile/o1_mf_tbs1_hn8z357w_.dbf",

"/home/oracle/cdb1/CDB1/datafile/o1_mf_sysaux_hn8z389g_.dbf",

"/home/oracle/cdb1/CDB1/datafile/o1_mf_tbs1_hn8z389k_.dbf";

switch clone datafile all;

}

executing Memory Script

executing command: SET NEWNAME

renamed tempfile 1 to /home/oracle/cdb1/CDB1/datafile/o1_mf_temp_%u_.tmp in control file

renamed tempfile 2 to /home/oracle/cdb1/CDB1/datafile/o1_mf_temp_%u_.tmp in control file

renamed tempfile 3 to /home/oracle/cdb1/CDB1/datafile/o1_mf_temp_%u_.tmp in control file

renamed tempfile 4 to /home/oracle/cdb1/CDB1/datafile/o1_mf_temp_%u_.tmp in control file

cataloged datafile copy

datafile copy file name=/home/oracle/cdb1/CDB1/datafile/o1_mf_sysaux_hn8z2y54_.dbf RECID=1 STAMP=1049367045

cataloged datafile copy

datafile copy file name=/home/oracle/cdb1/CDB1/datafile/o1_mf_undotbs1_hn8z2y56_.dbf RECID=2 STAMP=1049367045

cataloged datafile copy

datafile copy file name=/home/oracle/cdb1/CDB1/datafile/o1_mf_sysaux_hn8z3cc9_.dbf RECID=3 STAMP=1049367045

cataloged datafile copy

datafile copy file name=/home/oracle/cdb1/CDB1/datafile/o1_mf_sysaux_hn8z357q_.dbf RECID=4 STAMP=1049367045

cataloged datafile copy

datafile copy file name=/home/oracle/cdb1/CDB1/datafile/o1_mf_tbs1_hn8z357w_.dbf RECID=5 STAMP=1049367045

cataloged datafile copy

datafile copy file name=/home/oracle/cdb1/CDB1/datafile/o1_mf_sysaux_hn8z389g_.dbf RECID=6 STAMP=1049367045

cataloged datafile copy

datafile copy file name=/home/oracle/cdb1/CDB1/datafile/o1_mf_tbs1_hn8z389k_.dbf RECID=7 STAMP=1049367045

datafile 3 switched to datafile copy

input datafile copy RECID=1 STAMP=1049367045 file name=/home/oracle/cdb1/CDB1/datafile/o1_mf_sysaux_hn8z2y54_.dbf

datafile 4 switched to datafile copy

input datafile copy RECID=2 STAMP=1049367045 file name=/home/oracle/cdb1/CDB1/datafile/o1_mf_undotbs1_hn8z2y56_.dbf

datafile 7 switched to datafile copy

input datafile copy RECID=3 STAMP=1049367045 file name=/home/oracle/cdb1/CDB1/datafile/o1_mf_sysaux_hn8z3cc9_.dbf

datafile 14 switched to datafile copy

input datafile copy RECID=4 STAMP=1049367045 file name=/home/oracle/cdb1/CDB1/datafile/o1_mf_sysaux_hn8z357q_.dbf

datafile 15 switched to datafile copy

input datafile copy RECID=5 STAMP=1049367045 file name=/home/oracle/cdb1/CDB1/datafile/o1_mf_tbs1_hn8z357w_.dbf

datafile 17 switched to datafile copy

input datafile copy RECID=6 STAMP=1049367045 file name=/home/oracle/cdb1/CDB1/datafile/o1_mf_sysaux_hn8z389g_.dbf

datafile 18 switched to datafile copy

input datafile copy RECID=7 STAMP=1049367045 file name=/home/oracle/cdb1/CDB1/datafile/o1_mf_tbs1_hn8z389k_.dbf

contents of Memory Script:

{

Alter clone database open resetlogs;

}

executing Memory Script

database opened

contents of Memory Script:

{

sql clone "alter pluggable database all open";

}

executing Memory Script

sql statement: alter pluggable database all open

Dropping offline and skipped tablespaces

Executing: alter database default tablespace system

Executing: drop tablespace "USERS" including contents cascade constraints

Finished Duplicate Db at 25-AUG-20

RMAN-00571: ===========================================================

RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============

RMAN-00571: ===========================================================

RMAN-12005: error during channel cleanup

ORA-19550: cannot use backup/restore functions while using dispatcher

--虽然此处报错了但目标库已经完成复制，不影响使用

--报错原因是：源库开启共享服务模式，修改源库参数再复制不再报错。

SQL> show parameter dispatchers

NAME TYPE VALUE

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

dispatchers string (PROTOCOL=TCP)

SQL> show parameter shared_servers

NAME TYPE VALUE

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

shared_servers integer 1

SQL> alter system set dispatchers='';

SQL> alter system set shared_servers=0;

-删除目标库相关文件后重新复制

[oracle@dbserver cdb1]$ rman target sys/oracle@localhost/sztech auxiliary sys/oracle@localhost/cdb1

Recovery Manager: Release 12.1.0.2.0 - Production on Tue Aug 25 10:56:27 2020

connected to target database: SZTECH (DBID=3337735342)

connected to auxiliary database: CDB1 (not mounted)

RMAN> duplicate target database to "cdb1" pluggable database pdb1 tablespace pdb2:tbs1;

Starting Duplicate Db at 25-AUG-20

using target database control file instead of recovery catalog

allocated channel: ORA_AUX_DISK_1

channel ORA_AUX_DISK_1: SID=249 device type=DISK

current log archived

contents of Memory Script:

{

sql clone "alter system set db_name =

''SZTECH'' comment=

''Modified by RMAN duplicate'' scope=spfile";

sql clone "alter system set db_unique_name =

''CDB1'' comment=

''Modified by RMAN duplicate'' scope=spfile";

shutdown clone immediate;

startup clone force nomount

restore clone primary controlfile;

alter clone database mount;

}

executing Memory Script

sql statement: alter system set db_name = ''SZTECH'' comment= ''Modified by RMAN duplicate'' scope=spfile

sql statement: alter system set db_unique_name = ''CDB1'' comment= ''Modified by RMAN duplicate'' scope=spfile

Oracle instance shut down

Oracle instance started

Total System Global Area 805306368 bytes

Fixed Size 2929552 bytes

Variable Size 318770288 bytes

Database Buffers 478150656 bytes

Redo Buffers 5455872 bytes

Starting restore at 25-AUG-20

allocated channel: ORA_AUX_DISK_1

channel ORA_AUX_DISK_1: SID=12 device type=DISK

channel ORA_AUX_DISK_1: starting datafile backup set restore

channel ORA_AUX_DISK_1: restoring control file

channel ORA_AUX_DISK_1: reading from backup piece +FRA/SZTECH/AUTOBACKUP/2020_08_25/s_1049366761.288.1049366761

channel ORA_AUX_DISK_1: piece handle=+FRA/SZTECH/AUTOBACKUP/2020_08_25/s_1049366761.288.1049366761 tag=TAG20200825T104600

channel ORA_AUX_DISK_1: restored backup piece 1

channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:05

output file name=/home/oracle/cdb1/control01.dbf

Finished restore at 25-AUG-20

database mounted

Automatically adding tablespace SYSTEM

Automatically adding tablespace SYSAUX

Automatically adding tablespace PDB$SEED:SYSTEM

Automatically adding tablespace PDB$SEED:SYSAUX

Automatically adding tablespace PDB2:SYSTEM

Automatically adding tablespace PDB2:SYSAUX

Automatically adding tablespace UNDOTBS1

Checking that duplicated tablespaces are self-contained

Skipping tablespace USERS

contents of Memory Script:

{

set until scn 2807935;