ORACLE 19c 统一恢复处于ASM中的CDB含PDB数据文件到某一个文件目录下面

2023-12-13 21:15:01

NOCDB情况下,要把ASM中的文件恢复到文件系统,大家都知道分别设置每个文件的路径即可,但如果是租户环境,每个PDB都有不同路径,而且每个PDB都有SYSTEM,SYSAUX等一些表空降,不可能放在同一个目录中,而是放在不同的目录,那恢复时,怎么设置呢 ?

比如下面数据库以在本地恢复为例,如果是异机恢复,步骤类似:

SYS@orclcdb> show pdbs

? ? CON_ID CON_NAME? ?? ?? ?? ?? ?? ?? ???OPEN MODE??RESTRICTED
---------- ------------------------------ ---------- ----------
? ?? ?? ?2 PDB$SEED? ?? ?? ?? ?? ?? ?? ???READ ONLY??NO
? ?? ?? ?3 ORCLPDB? ?? ?? ?? ?? ?? ?? ?? ?READ WRITE NO

ORCLCDB,有一个PDB,ORCLPDB,一个PDBSEED,数据文件放在ASM磁盘组DATA中,想整体恢复到文件系统,比如 /u01/app/oracle/oradata/orclcdb/

RMAN> report schema;

Report of database schema for database with db_unique_name ORCLCDB

List of Permanent Datafiles
===========================
File Size(MB) Tablespace? ?? ?? ???RB segs Datafile Name
---- -------- -------------------- ------- ------------------------
1? ? 1700? ???SYSTEM? ?? ?? ?? ?? ?***? ???+DATA/ORCLCDB/DATAFILE/system.257.1140286827
3? ? 1024? ???SYSAUX? ?? ?? ?? ?? ?***? ???+DATA/ORCLCDB/DATAFILE/sysaux.258.1140286963
4? ? 1500? ???UNDOTBS1? ?? ?? ?? ? ***? ???+DATA/ORCLCDB/DATAFILE/undotbs1.259.1140287029
5? ? 540? ?? ?PDB$SEED:SYSTEM? ?? ?***? ???+DATA/ORCLCDB/86B637B62FE07A65E053F706E80A27CA/DATAFILE/system.266.1140289995
6? ? 430? ?? ?PDB$SEED:SYSAUX? ?? ?***? ???+DATA/ORCLCDB/86B637B62FE07A65E053F706E80A27CA/DATAFILE/sysaux.267.1140289995
7? ? 5? ?? ???USERS? ?? ?? ?? ?? ? ***? ???+DATA/ORCLCDB/DATAFILE/users.260.1140287029
8? ? 215? ?? ?PDB$SEED:UNDOTBS1? ? ***? ???+DATA/ORCLCDB/86B637B62FE07A65E053F706E80A27CA/DATAFILE/undotbs1.268.1140289995
9? ? 550? ?? ?ORCLPDB:SYSTEM? ?? ? ***? ???+DATA/ORCLCDB/FECB9F1537517278E0537885A8C0E7DC/DATAFILE/system.272.1140292289
10? ?500? ?? ?ORCLPDB:SYSAUX? ?? ? ***? ???+DATA/ORCLCDB/FECB9F1537517278E0537885A8C0E7DC/DATAFILE/sysaux.273.1140292289
11? ?215? ?? ?ORCLPDB:UNDOTBS1? ???***? ???+DATA/ORCLCDB/FECB9F1537517278E0537885A8C0E7DC/DATAFILE/undotbs1.271.1140292289
12? ?15? ?? ? ORCLPDB:USERS? ?? ???***? ???+DATA/ORCLCDB/FECB9F1537517278E0537885A8C0E7DC/DATAFILE/users.275.1140292305

List of Temporary Files
=======================
File Size(MB) Tablespace? ?? ?? ???Maxsize(MB) Tempfile Name
---- -------- -------------------- ----------- --------------------
1? ? 500? ?? ?TEMP? ?? ?? ?? ?? ???32767? ?? ? +DATA/ORCLCDB/TEMPFILE/temp.265.1140287089
2? ? 138? ?? ?PDB$SEED:TEMP? ?? ???32767? ?? ? +DATA/ORCLCDB/FECB1A5B8C146A75E0537885A8C0F418/TEMPFILE/temp.269.1140290063
3? ? 139? ?? ?ORCLPDB:TEMP? ?? ?? ?32767? ?? ? +DATA/ORCLCDB/FECB9F1537517278E0537885A8C0E7DC/TEMPFILE/temp.274.1140292293


步骤如下:
?

1.备份


??rman >backup database plus archivelog;


2.关闭数据库


??sql>shutdown immediate;


3.恢复到文件系统


??RMAN>
run{
allocate channel c1 device type disk;
allocate channel c2 device type disk;
allocate channel c3 device type disk;
allocate channel c4 device type disk;
set newname for database?"cdb$root"?to '/u01/app/oracle/oradata/ORCLCDB/%b';
set newname for database?"PDB$SEED"?to '/u01/app/oracle/oradata/ORCLCDB/pdbseed/%b';
set newname for database?"ORCLPDB"?to '/u01/app/oracle/oradata/ORCLCDB/orclpdb/%b';
restore?database?root?database?"PDB$SEED"?DATABASE?"ORCLPDB";
switch datafile all;
switch tempfile all;
recover database;
}
需要注意的是:这里不同的PDB,要对应到不同的目录,需要单独指定PDB,而且,cdb$root要单独指定。

allocated channel: c1
channel c1: SID=613 device type=DISK

allocated channel: c2
channel c2: SID=11 device type=DISK

allocated channel: c3
channel c3: SID=213 device type=DISK

allocated channel: c4
channel c4: SID=414 device type=DISK

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

Starting restore at 19-AUG-23

channel c2: starting datafile backup set restore
channel c2: specifying datafile(s) to restore from backup set
channel c2: restoring datafile 00001 to /u01/app/oracle/oradata/ORCLCDB/system.257.1140286827
channel c2: restoring datafile 00003 to /u01/app/oracle/oradata/ORCLCDB/sysaux.258.1140286963
channel c2: restoring datafile 00004 to /u01/app/oracle/oradata/ORCLCDB/undotbs1.259.1140287029
channel c2: restoring datafile 00007 to /u01/app/oracle/oradata/ORCLCDB/users.260.1140287029
channel c2: reading from backup piece +FRA/ORCLCDB/BACKUPSET/2023_08_19/nnndf0_tag20230819t170606_0.291.1145293567
channel c3: starting datafile backup set restore
channel c3: specifying datafile(s) to restore from backup set
channel c3: restoring datafile 00009 to /u01/app/oracle/oradata/ORCLCDB/orclpdb/system.272.1140292289
channel c3: restoring datafile 00010 to /u01/app/oracle/oradata/ORCLCDB/orclpdb/sysaux.273.1140292289
channel c3: restoring datafile 00011 to /u01/app/oracle/oradata/ORCLCDB/orclpdb/undotbs1.271.1140292289
channel c3: restoring datafile 00012 to /u01/app/oracle/oradata/ORCLCDB/orclpdb/users.275.1140292305
channel c3: reading from backup piece +FRA/ORCLCDB/FECB9F1537517278E0537885A8C0E7DC/BACKUPSET/2023_08_19/nnndf0_tag20230819t170606_0.290.1145293671
channel c1: restoring datafile 00005
input datafile copy RECID=22 STAMP=1144754940 file name=+FRA/ORCLCDB/FECB1A5B8C146A75E0537885A8C0F418/DATAFILE/system.260.1144754939
destination for restore of datafile 00005: /u01/app/oracle/oradata/ORCLCDB/pdbseed/system.266.1140289995
channel c4: restoring datafile 00006
input datafile copy RECID=24 STAMP=1144754946 file name=+FRA/ORCLCDB/FECB1A5B8C146A75E0537885A8C0F418/DATAFILE/sysaux.259.1144754945
destination for restore of datafile 00006: /u01/app/oracle/oradata/ORCLCDB/pdbseed/sysaux.267.1140289995
channel c4: copied datafile copy of datafile 00006, elapsed time: 00:00:45
output file name=/u01/app/oracle/oradata/ORCLCDB/pdbseed/sysaux.267.1140289995 RECID=34 STAMP=1145295053
channel c4: restoring datafile 00008
input datafile copy RECID=25 STAMP=1144754949 file name=+FRA/ORCLCDB/FECB1A5B8C146A75E0537885A8C0F418/DATAFILE/undotbs1.268.1144754949
destination for restore of datafile 00008: /u01/app/oracle/oradata/ORCLCDB/pdbseed/undotbs1.268.1140289995
channel c1: copied datafile copy of datafile 00005, elapsed time: 00:01:00
output file name=/u01/app/oracle/oradata/ORCLCDB/pdbseed/system.266.1140289995 RECID=36 STAMP=1145295065
channel c3: piece handle=+FRA/ORCLCDB/FECB9F1537517278E0537885A8C0E7DC/BACKUPSET/2023_08_19/nnndf0_tag20230819t170606_0.290.1145293671 tag=TAG20230819T170606
channel c3: restored backup piece 1
channel c3: restore complete, elapsed time: 00:01:00
channel c4: copied datafile copy of datafile 00008, elapsed time: 00:00:15
output file name=/u01/app/oracle/oradata/ORCLCDB/pdbseed/undotbs1.268.1140289995 RECID=38 STAMP=1145295072
channel c2: piece handle=+FRA/ORCLCDB/BACKUPSET/2023_08_19/nnndf0_tag20230819t170606_0.291.1145293567 tag=TAG20230819T170606
channel c2: restored backup piece 1
channel c2: restore complete, elapsed time: 00:01:41
Finished restore at 19-AUG-23

datafile 1 switched to datafile copy
input datafile copy RECID=41 STAMP=1145295112 file name=/u01/app/oracle/oradata/ORCLCDB/system.257.1140286827
datafile 3 switched to datafile copy
input datafile copy RECID=42 STAMP=1145295112 file name=/u01/app/oracle/oradata/ORCLCDB/sysaux.258.1140286963
datafile 4 switched to datafile copy
input datafile copy RECID=43 STAMP=1145295112 file name=/u01/app/oracle/oradata/ORCLCDB/undotbs1.259.1140287029
datafile 7 switched to datafile copy
input datafile copy RECID=44 STAMP=1145295112 file name=/u01/app/oracle/oradata/ORCLCDB/users.260.1140287029
datafile 5 switched to datafile copy
input datafile copy RECID=45 STAMP=1145295112 file name=/u01/app/oracle/oradata/ORCLCDB/pdbseed/system.266.1140289995
datafile 6 switched to datafile copy
input datafile copy RECID=46 STAMP=1145295113 file name=/u01/app/oracle/oradata/ORCLCDB/pdbseed/sysaux.267.1140289995
datafile 8 switched to datafile copy
input datafile copy RECID=47 STAMP=1145295113 file name=/u01/app/oracle/oradata/ORCLCDB/pdbseed/undotbs1.268.1140289995
datafile 9 switched to datafile copy
input datafile copy RECID=48 STAMP=1145295113 file name=/u01/app/oracle/oradata/ORCLCDB/orclpdb/system.272.1140292289
datafile 10 switched to datafile copy
input datafile copy RECID=49 STAMP=1145295113 file name=/u01/app/oracle/oradata/ORCLCDB/orclpdb/sysaux.273.1140292289
datafile 11 switched to datafile copy
input datafile copy RECID=50 STAMP=1145295113 file name=/u01/app/oracle/oradata/ORCLCDB/orclpdb/undotbs1.271.1140292289
datafile 12 switched to datafile copy
input datafile copy RECID=51 STAMP=1145295113 file name=/u01/app/oracle/oradata/ORCLCDB/orclpdb/users.275.1140292305


Starting recover at 19-AUG-23

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

Finished recover at 19-AUG-23
released channel: c1
released channel: c2
released channel: c3
released channel: c4


4.打开数据库


RMAN> alter database open;



5.验证数据库数据文件


SYS@orclcdb> select name from v$datafile;

NAME
--------------------------------------------------------------------------------
/u01/app/oracle/oradata/ORCLCDB/system.257.1140286827
/u01/app/oracle/oradata/ORCLCDB/sysaux.258.1140286963
/u01/app/oracle/oradata/ORCLCDB/undotbs1.259.1140287029
/u01/app/oracle/oradata/ORCLCDB/pdbseed/system.266.1140289995
/u01/app/oracle/oradata/ORCLCDB/pdbseed/sysaux.267.1140289995
/u01/app/oracle/oradata/ORCLCDB/users.260.1140287029
/u01/app/oracle/oradata/ORCLCDB/pdbseed/undotbs1.268.1140289995
/u01/app/oracle/oradata/ORCLCDB/orclpdb/system.272.1140292289
/u01/app/oracle/oradata/ORCLCDB/orclpdb/sysaux.273.1140292289
/u01/app/oracle/oradata/ORCLCDB/orclpdb/undotbs1.271.1140292289
/u01/app/oracle/oradata/ORCLCDB/orclpdb/users.275.1140292305


6. 临时文件及其他文件的处理

可以参照这个处理:
http://bbs.cqsztech.com/forum.ph ... hlight=%D2%EC%BB%FA


附录:
? ???mos:

Doc ID 2818346.1

文章来源:https://blog.csdn.net/cqsztech/article/details/134911991
本文来自互联网用户投稿,该文观点仅代表作者本人,不代表本站立场。本站仅提供信息存储空间服务,不拥有所有权,不承担相关法律责任。