oracle adg初始化的时候缺少几个数据文件
oracle adg初始化的时候缺少几个数据文件，启动mrp进程报错，或者scn不一致，datafile n需要重新恢复的时候。我们是否想到现在又要重新duplicate一次，或者删掉，重新rman restore一次。如果数据量大，这样就会很浪费时间，那么我们有没有方法可以单独的restore这几个出问题的datafile哪？答案是有的，以下就提供了集中方法，供测试。
1、backup as copy
1)cp 到asm路径中
2)catalog datafilecopy 路径；
switch datafile 4 to copy
2、backup datafile and restore
下面没有adg的环境，但是思想一致，先备份，删除datafile，然后用不同的方法，进行恢复就行。
基本环境
SQL> select file#,name,bytes/1024/1024 from v$datafile order by 1;
FILE# NAME BYTES/1024/1024
---------- ---------------------------------------------------------------------- ---------------
1 +DATADG/orcl/datafile/system.260.1023146137 700
2 +DATADG/orcl/datafile/sysaux.261.1023146139 600
3 +DATADG/orcl/datafile/undotbs1.262.1023146139 350
4 +DATADG/orcl/datafile/users.264.1023146143 5
5 +DATADG/orcl/datafile/zhuo.266.1024792071 5120
6 +DATADG/orcl/datafile/test.267.1057188065 10
6 rows selected.
备份：
RMAN> backup as copy datafile 4,6 format '/home/oracle/copy_%U.bak';
Starting backup at 22-NOV-20
using channel ORA_DISK_1
channel ORA_DISK_1: starting datafile copy
input datafile file number=00006 name=+DATADG/orcl/datafile/test.267.1057188065
output file name=/home/oracle/copy_data_D-ORCL_I-1550926869_TS-TEST_FNO-6_03vg6qmr.bak tag=TAG20201122T232931 RECID=2 STAMP=1057188571
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:01
channel ORA_DISK_1: starting datafile copy
input datafile file number=00004 name=+DATADG/orcl/datafile/users.264.1023146143
output file name=/home/oracle/copy_data_D-ORCL_I-1550926869_TS-USERS_FNO-4_04vg6qms.bak tag=TAG20201122T232931 RECID=3 STAMP=1057188572
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:01
Finished backup at 22-NOV-20
[root@11gasm oracle]# ll
total 17996
-rw-r--r-- 1 oracle oinstall 25192 Oct 31 2019 db.rsp
drwxr-xr-x 2 oracle oinstall 4096 Oct 31 2019 Desktop
d
[root@11gasm oracle]# rm -rf copy.bak
[root@11gasm oracle]# ll
total 23124
-rw-r----- 1 oracle dba 10493952 Nov 22 23:29 copy_data_D-ORCL_I-1550926869_TS-TEST_FNO-6_03vg6qmr.bak ---说明as copy备份的是按os级别的cp来的，有几个文件，cp几次。
-rw-r----- 1 oracle dba 5251072 Nov 22 23:29 copy_data_D-ORCL_I-1550926869_TS-USERS_FNO-4_04vg6qms.bak
-rw-r--r-- 1 oracle oinstall 25192 Oct 31 2019 db.rsp
drwxr-xr-x 2 oracle oinstall 4096 Oct 31 2019 Desktop
drwxr-xr-x 2 oracle oinstall 4096 Oct 31 2019 Documents
RMAN> report schema;
Report of database schema for database with db_unique_name ORCL
List of Permanent Datafiles
===========================
File Size(MB) Tablespace RB segs Datafile Name
---- -------- -------------------- ------- ------------------------
1 700 SYSTEM *** +DATADG/orcl/datafile/system.260.1023146137
2 600 SYSAUX *** +DATADG/orcl/datafile/sysaux.261.1023146139
3 350 UNDOTBS1 *** +DATADG/orcl/datafile/undotbs1.262.1023146139
4 5 USERS *** +DATADG/orcl/datafile/users.264.1023146143
5 5120 ZHUO *** +DATADG/orcl/datafile/zhuo.266.1024792071
6 10 TEST *** +DATADG/orcl/datafile/test.267.1057254521
List of Temporary Files
=======================
File Size(MB) Tablespace Maxsize(MB) Tempfile Name
---- -------- -------------------- ----------- --------------------
1 20 TEMP 32767 +DATADG/orcl/tempfile/temp.263.1023146139
RMAN> list backup of datafile 4,6; ---image copy的备份不能用备份集的命令查看
specification does not match any backup in the repository
RMAN> list copy of datafile 4,6;
List of Datafile Copies
=======================
Key File S Completion Time Ckp SCN Ckp Time
------- ---- - --------------- ---------- ---------------
2 4 A 23-NOV-20 372076 23-NOV-20
Name: /home/oracle/copy_data_D-ORCL_I-1550926869_TS-USERS_FNO-4_04vg8r6r
Tag: TAG20201123T175018
1 6 A 23-NOV-20 372075 23-NOV-20
Name: /home/oracle/copy_data_D-ORCL_I-1550926869_TS-TEST_FNO-6_03vg8r6q
Tag: TAG20201123T175018
删除源文件，进行恢复。
[grid@11gasm ~]$ asmcmd
ASMCMD> cd datafile
ASMCMD> rm -rf TEST.267.1057188065
ORA-15032: not all alterations performed
ORA-15028: ASM file '+DATADG/orcl/datafile/TEST.267.1057188065' not dropped; currently being accessed (DBD ERROR: OCIStmtExecute)
SQL> alter database datafile 4,6 offline;
Database altered.
ASMCMD> rm -rf TEST.267.1057188065
ASMCMD> rm -rf USERS.264.1023146143
ASMCMD> ls
SYSAUX.261.1023146139
SYSTEM.260.1023146137
UNDOTBS1.262.1023146139
ZHUO.266.1024792071
SQL> alter database datafile 4,6 online;
alter database datafile 4,6 online
*
ERROR at line 1:
ORA-01157: cannot identify/lock data file 4 - see DBWR trace file
ORA-01110: data file 4: '+DATADG/orcl/datafile/users.264.1023146143'
恢复
backup as copy
asmcmd cp
复制备份的映像副本到asm中，并且重新命令为有意的名字
ASMCMD> cp /home/oracle/copy_data_D-ORCL_I-1550926869_TS-TEST_FNO-6_03vg6qmr.bak test.dbf
ASMCMD-8014: file '/home/oracle/copy_data_D-ORCL_I-1550926869_TS-TEST_FNO-6_03vg6qmr.bak' does not exist
[root@11gasm oracle]# chown grid:oinstall copy_data_D-ORCL_I-1550926869_TS-*
ASMCMD> cp /home/oracle/copy_data_D-ORCL_I-1550926869_TS-TEST_FNO-6_03vg6qmr.bak test.dbf
ASMCMD-8014: file '/home/oracle/copy_data_D-ORCL_I-1550926869_TS-TEST_FNO-6_03vg6qmr.bak' does not exist
[root@11gasm oracle]# mv copy_data_D-ORCL_I-1550926869_TS-* /tmp/
ASMCMD> cp /tmp/copy_data_D-ORCL_I-1550926869_TS-TEST_FNO-6_03vg6qmr.bak test.dbf
copying /tmp/copy_data_D-ORCL_I-1550926869_TS-TEST_FNO-6_03vg6qmr.bak -> +DATADG/orcl/datafile/test.dbf
ASMCMD> cp /tmp/copy_data_D-ORCL_I-1550926869_TS-USERS_FNO-4_04vg6qms.bak user.dbf
copying /tmp/copy_data_D-ORCL_I-1550926869_TS-USERS_FNO-4_04vg6qms.bak -> +DATADG/orcl/datafile/user.dbf
ASMCMD> ls
SYSAUX.261.1023146139
SYSTEM.260.1023146137
UNDOTBS1.262.1023146139
ZHUO.266.1024792071
test.dbf
user.dbf
一定要注意路径和权限，对grid用户来说，要有权限访问。
在控制文件中添加映像副本的路径。
把ASM上的文件都认为是数据文件的copy备份，完全可以使用RMAN的catalog start with手工将这些copy“备份”加进来，数据库会认为这些文件就只是数据文件的copy备份，然后直接switch database to copy就可以成功切换。
RMAN> catalog start with '+DATADG/orcl/datafile/';
using target database control file instead of recovery catalog
searching for all files that match the pattern +DATADG/orcl/datafile/
List of Files Unknown to the Database
=====================================
File Name: +datadg/orcl/DATAFILE/test.dbf
File Name: +datadg/orcl/DATAFILE/user.dbf
Do you really want to catalog the above files (enter YES or NO)? yes
cataloging files...
cataloging done
List of Cataloged Files
=======================
File Name: +datadg/orcl/DATAFILE/test.dbf
File Name: +datadg/orcl/DATAFILE/user.dbf
RMAN> catalog datafilecopy '+DATADG/orcl/datafile/test.dbf'; ----二选一即可。
cataloged datafile copy
datafile copy file name=+DATADG/orcl/datafile/test.dbf RECID=6 STAMP=1057189081
RMAN> catalog datafilecopy '+DATADG/orcl/datafile/user.dbf';
cataloged datafile copy
datafile copy file name=+DATADG/orcl/datafile/user.dbf RECID=7 STAMP=1057189090
catalog datafile的时候，数据文件表多的时候，就会比较麻烦。
有个技巧，在做adg增量恢复的时候，为了避免后面的繁琐，最好按照doc第9步，先查询standby的datafile信息，然后我们就可以根据这个，直接catalog datafilecopy了。
查看信息：
RMAN> report schema
2> ;
Report of database schema for database with db_unique_name ORCL
List of Permanent Datafiles
===========================
File Size(MB) Tablespace RB segs Datafile Name
---- -------- -------------------- ------- ------------------------
1 700 SYSTEM *** +DATADG/orcl/datafile/system.260.1023146137
2 600 SYSAUX *** +DATADG/orcl/datafile/sysaux.261.1023146139
3 350 UNDOTBS1 *** +DATADG/orcl/datafile/undotbs1.262.1023146139
4 0 USERS *** +DATADG/orcl/datafile/users.264.1023146143
5 5120 ZHUO *** +DATADG/orcl/datafile/zhuo.266.1024792071 ----路径没有变化。
6 0 TEST *** +DATADG/orcl/datafile/test.267.1057188065
List of Temporary Files
=======================
File Size(MB) Tablespace Maxsize(MB) Tempfile Name
---- -------- -------------------- ----------- --------------------
1 20 TEMP 32767 +DATADG/orcl/tempfile/temp.263.1023146139
此时控制文件中数据文件路径没有变化，还是原路径，大小也是0，说明还没被控制文件识别到新的备份的数据文件。
switch修改控制文件中的路径（相当于rename）
RMAN> switch database to copy; --整库才用
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of switch to copy command at 11/22/2020 23:38:32
RMAN-06572: database is open and datafile 1 is not offline
RMAN> switch datafile 4,6 to copy; --针对个别数据文件。
datafile 4 switched to datafile copy "+DATADG/orcl/datafile/user.dbf"
datafile 6 switched to datafile copy "+DATADG/orcl/datafile/test.dbf"
RMAN> report schema;
Report of database schema for database with db_unique_name ORCL
List of Permanent Datafiles
===========================
File Size(MB) Tablespace RB segs Datafile Name
---- -------- -------------------- ------- ------------------------
1 700 SYSTEM *** +DATADG/orcl/datafile/system.260.1023146137
2 600 SYSAUX *** +DATADG/orcl/datafile/sysaux.261.1023146139
3 350 UNDOTBS1 *** +DATADG/orcl/datafile/undotbs1.262.1023146139
4 5 USERS *** +DATADG/orcl/datafile/user.dbf 路径已经发生变化，switch更新了控制文件
5 5120 ZHUO *** +DATADG/orcl/datafile/zhuo.266.1024792071
6 10 TEST *** +DATADG/orcl/datafile/test.dbf
List of Temporary Files
=======================
File Size(MB) Tablespace Maxsize(MB) Tempfile Name
---- -------- -------------------- ----------- --------------------
1 20 TEMP 32767 +DATADG/orcl/tempfile/temp.263.1023146139
online数据文件
RMAN> recover datafile 4,6;
Starting recover at 22-NOV-20
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=405 device type=DISK
starting media recovery
archived log for thread 1 with sequence 26 is already on disk as file /u01/app/oracle/product/11.2.0/dbhome_1/dbs/arch1_26_1023146133.dbf
archived log for thread 1 with sequence 27 is already on disk as file /u01/app/oracle/product/11.2.0/dbhome_1/dbs/arch1_27_1023146133.dbf
archived log for thread 1 with sequence 28 is already on disk as file /u01/app/oracle/product/11.2.0/dbhome_1/dbs/arch1_28_1023146133.dbf
archived log for thread 1 with sequence 29 is already on disk as file /u01/app/oracle/product/11.2.0/dbhome_1/dbs/arch1_29_1023146133.dbf
archived log file name=/u01/app/oracle/product/11.2.0/dbhome_1/dbs/arch1_26_1023146133.dbf thread=1 sequence=26
archived log file name=/u01/app/oracle/product/11.2.0/dbhome_1/dbs/arch1_27_1023146133.dbf thread=1 sequence=27
media recovery complete, elapsed time: 00:00:00
Finished recover at 22-NOV-20
SQL> alter database datafile 4,6 online;
Database altered.
至此，恢复完成。
此处，在adg备库，可能和之前不一样，有如下报错：
删除数据文件：
SQL> alter database datafile 2 offline drop;
alter database datafile 2 offline drop
*
ERROR at line 1:
ORA-01156: recovery or flashback in progress may need access to files
SQL> recover managed standby database cancel;
Media recovery complete.
SQL> alter database datafile 2 offline;
alter database datafile 2 offline
*
ERROR at line 1:
ORA-01668: standby database requires DROP option for offline of data file
SQL> alter database datafile 2 offline drop;
Database altered.
SQL> select
2 ts#,
3 file#,rfile#,
4 BYTES/1024/1024/1024 file_size_G,
5 status,
6 enabled,
7 CREATION_TIME,
8 checkpoint_change# "SCN",
9 last_change# "STOP_SCN",
10 offline_change#,
11 online_change#,
12 online_time,
13 name
14 from v$datafile
15 order by 1,2;
TS# FILE# RFILE# FILE_SIZE_G STATUS ENABLED CREATION_TIM SCN STOP_SCN OFFLINE_CHANGE# ONLINE_CHANGE# ONLINE_TIME NAME
---------- ---------- ---------- ----------- ------- ---------- ------------ ---------- ---------- --------------- -------------- ------------ ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
0 1 1 .68359375 SYSTEM READ ONLY 21-NOV-19 789201 0 0 /u01/app/oracle/oradata/zhuodg/ZHUODG/datafile/o1_mf_system_05uvp9bj_.dbf
1 2 2 0 RECOVER READ ONLY 21-NOV-19 789201 0 0 /u01/app/oracle/oradata/zhuodg/ZHUODG/datafile/o1_mf_sysaux_06uvp9bj_.dbf
2 3 3 .3515625 ONLINE READ ONLY 21-NOV-19 789201 0 0 /u01/app/oracle/oradata/zhuodg/ZHUODG/datafile/o1_mf_undotbs1_07uvp9bj_.dbf
4 4 4 .004882813 ONLINE READ ONLY 21-NOV-19 789201 0 0 /u01/app/oracle/oradata/zhuodg/ZHUODG/datafile/o1_mf_users_08uvp9c4_.dbf
5 5 5 5 ONLINE READ ONLY 21-NOV-19 789201 0 0 /u01/app/oracle/oradata/zhuodg/ZHUODG/datafile/o1_mf_zhuo_04uvp9bj_.dbf
状态为recover。
当我们switch 重命名文件的时候，会报错：
RMAN> switch datafile 2 to copy;
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of switch to copy command at 05/09/2020 16:15:17
ORA-01121: cannot rename database file 2 - file is in use or recovery
ORA-01110: data file 2: '/u01/app/oracle/oradata/zhuodg/ZHUODG/datafile/o1_mf_sysaux_06uvp9bj_.dbf'
重启在mount下恢复：
SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup mount;
重命名正常：
RMAN> switch datafile 2 to copy;
using target database control file instead of recovery catalog
datafile 2 switched to datafile copy "/home/oracle/copy_data_D-ZHUO_I-3982294478_TS-SYSAUX_FNO-2_09uvpa9p.bak"
RMAN> recover datafile 2;
Starting recover at 09-MAY-20
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=30 device type=DISK
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of recover command at 05/09/2020 16:21:02
RMAN-06067: RECOVER DATABASE required with a backup or created control file
此时直接recover 会报错，我们可以直接启动mrp进程，让数据库自己去恢复。
然后数据库adg备库恢复正常：
SQL> recover managed standby database using current logfile disconnect from session;
Media recovery complete.
SQL> recover managed standby database cancel;
Media recovery complete.
SQL> shutdown immediate;
ORA-01109: database not open
Database dismounted.
ORACLE instance shut down.
SQL> startup
ORACLE instance started.
Total System Global Area 521936896 bytes
Fixed Size 2254824 bytes
Variable Size 352323608 bytes
Database Buffers 163577856 bytes
Redo Buffers 3780608 bytes
Database mounted.
Database opened.
SQL>
所以为了避免在adg上出错，启动到mount状态进行恢复。
restore，switch
重新恢复快照，备份一遍，删除测试。
RMAN> report schema;
Report of database schema for database with db_unique_name ORCL
List of Permanent Datafiles
===========================
File Size(MB) Tablespace RB segs Datafile Name
---- -------- -------------------- ------- ------------------------
1 700 SYSTEM *** +DATADG/orcl/datafile/system.260.1023146137
2 600 SYSAUX *** +DATADG/orcl/datafile/sysaux.261.1023146139
3 350 UNDOTBS1 *** +DATADG/orcl/datafile/undotbs1.262.1023146139
4 5 USERS *** +DATADG/orcl/datafile/users.264.1023146143
5 5120 ZHUO *** +DATADG/orcl/datafile/zhuo.266.1024792071
6 10 TEST *** +DATADG/orcl/datafile/test.267.1057273025
List of Temporary Files
=======================
File Size(MB) Tablespace Maxsize(MB) Tempfile Name
---- -------- -------------------- ----------- --------------------
1 20 TEMP 32767 +DATADG/orcl/tempfile/temp.263.1023146139
RMAN> list backup of datafile 4,6;
specification does not match any backup in the repository
RMAN> list copy of datafile 4,6;
List of Datafile Copies
=======================
Key File S Completion Time Ckp SCN Ckp Time
------- ---- - --------------- ---------- ---------------
2 4 A 23-NOV-20 371288 23-NOV-20
Name: /home/oracle/copy_data_D-ORCL_I-1550926869_TS-USERS_FNO-4_02vg9d87
Tag: TAG20201123T225814
1 6 A 23-NOV-20 371287 23-NOV-20
Name: /home/oracle/copy_data_D-ORCL_I-1550926869_TS-TEST_FNO-6_01vg9d86
Tag: TAG20201123T225814
删除后，大小为0：
RMAN> report schema;
Report of database schema for database with db_unique_name ORCL
List of Permanent Datafiles
===========================
File Size(MB) Tablespace RB segs Datafile Name
---- -------- -------------------- ------- ------------------------
1 700 SYSTEM *** +DATADG/orcl/datafile/system.260.1023146137
2 600 SYSAUX *** +DATADG/orcl/datafile/sysaux.261.1023146139
3 350 UNDOTBS1 *** +DATADG/orcl/datafile/undotbs1.262.1023146139
4 0 USERS *** +DATADG/orcl/datafile/users.264.1023146143
5 5120 ZHUO *** +DATADG/orcl/datafile/zhuo.266.1024792071
6 0 TEST *** +DATADG/orcl/datafile/test.267.1057273025
List of Temporary Files
=======================
File Size(MB) Tablespace Maxsize(MB) Tempfile Name
---- -------- -------------------- ----------- --------------------
1 20 TEMP 32767 +DATADG/orcl/tempfile/temp.263.1023146139
模拟路径不在备份位置，即控制文件中的路径不存在，用新的路径去恢复。
[oracle@11gasm ~]$ cp copy_data_D-ORCL_I-1550926869_TS-* /tmp/
catalog添加映像副本信息
RMAN> catalog start with '/tmp/copy';
searching for all files that match the pattern /tmp/copy
List of Files Unknown to the Database
=====================================
File Name: /tmp/copy_data_D-ORCL_I-1550926869_TS-USERS_FNO-4_02vg9d87
File Name: /tmp/copy_data_D-ORCL_I-1550926869_TS-TEST_FNO-6_01vg9d86
Do you really want to catalog the above files (enter YES or NO)? no
RMAN> catalog datafilecopy '/tmp/copy_data_D-ORCL_I-1550926869_TS-USERS_FNO-4_02vg9d87';
cataloged datafile copy
datafile copy file name=/tmp/copy_data_D-ORCL_I-1550926869_TS-USERS_FNO-4_02vg9d87 RECID=3 STAMP=1057273756
RMAN> catalog start with '/tmp/copy';
searching for all files that match the pattern /tmp/copy
List of Files Unknown to the Database
=====================================
File Name: /tmp/copy_data_D-ORCL_I-1550926869_TS-TEST_FNO-6_01vg9d86 ---catalog start with执行的时候，当控制文件中已经存在此备份集或者映像副本信息的时候，再次添加会忽略
Do you really want to catalog the above files (enter YES or NO)? yes
cataloging files...
cataloging done
List of Cataloged Files
=======================
File Name: /tmp/copy_data_D-ORCL_I-1550926869_TS-TEST_FNO-6_01vg9d86
RMAN> list copy of datafile 4,6;
List of Datafile Copies
=======================
Key File S Completion Time Ckp SCN Ckp Time
------- ---- - --------------- ---------- ---------------
3 4 A 23-NOV-20 371288 23-NOV-20
Name: /tmp/copy_data_D-ORCL_I-1550926869_TS-USERS_FNO-4_02vg9d87 --新路径已经添加进来了
Tag: TAG20201123T225814
2 4 A 23-NOV-20 371288 23-NOV-20
Name: /home/oracle/copy_data_D-ORCL_I-1550926869_TS-USERS_FNO-4_02vg9d87
Tag: TAG20201123T225814
4 6 A 23-NOV-20 371287 23-NOV-20
Name: /tmp/copy_data_D-ORCL_I-1550926869_TS-TEST_FNO-6_01vg9d86
Tag: TAG20201123T225814
1 6 A 23-NOV-20 371287 23-NOV-20
Name: /home/oracle/copy_data_D-ORCL_I-1550926869_TS-TEST_FNO-6_01vg9d86
Tag: TAG20201123T225814
resotere恢复映像副本至asm中，相当于异机恢复。
参考：
run{
ALLOCATE CHANNEL c1 DEVICE TYPE DISK;
ALLOCATE CHANNEL c2 DEVICE TYPE DISK;
ALLOCATE CHANNEL c3 DEVICE TYPE DISK;
set newname for datafile 4 to ‘+DATADG’;
set newname for datafile 6 to ‘+DATADG’;
restore datafile 4,6;
switch datafile all;
}
或者
run{
ALLOCATE CHANNEL c1 DEVICE TYPE DISK;
ALLOCATE CHANNEL c2 DEVICE TYPE DISK;
ALLOCATE CHANNEL c3 DEVICE TYPE DISK;
set newname for datafile ‘+DATADG/orcl/datafile/users.264.1023146143’ to ‘+DATADG/orcl/datafile/user.dbf’;
set newname for datafile ‘+DATADG/orcl/datafile/TEST.267.1057307953’ to ‘+DATADG/orcl/datafile/test.dbf’;
restore datafile 4,6;
switch datafile all;
}
RMAN> run{
2> ALLOCATE CHANNEL c1 DEVICE TYPE DISK;
3> ALLOCATE CHANNEL c2 DEVICE TYPE DISK;
4> ALLOCATE CHANNEL c3 DEVICE TYPE DISK;
5> set newname for datafile 4 to '+DATADG';
6> set newname for datafile 6 to '+DATADG';
7> restore datafile 4,6;
8> switch datafile all;
9> }
released channel: ORA_DISK_1
allocated channel: c1
channel c1: SID=403 device type=DISK
allocated channel: c2
channel c2: SID=405 device type=DISK
allocated channel: c3
channel c3: SID=28 device type=DISK
executing command: SET NEWNAME
executing command: SET NEWNAME
Starting restore at 24-NOV-20
channel c1: starting datafile backup set restore
channel c1: specifying datafile(s) to restore from backup set
channel c1: restoring datafile 00004 to +DATADG
channel c1: restoring datafile 00006 to +DATADG
channel c1: reading from backup piece /home/oracle/copy.bak
channel c1: piece handle=/home/oracle/copy.bak tag=TAG20201124T085423
channel c1: restored backup piece 1
channel c1: restore complete, elapsed time: 00:00:01
Finished restore at 24-NOV-20
datafile 4 switched to datafile copy
input datafile copy RECID=11 STAMP=1057322415 file name=+DATADG/orcl/datafile/users.264.1057322415
datafile 6 switched to datafile copy
input datafile copy RECID=12 STAMP=1057322415 file name=+DATADG/orcl/datafile/test.267.1057322415
released channel: c1
released channel: c2
released channel: c3
online datafile
SQL> alter database datafile 4,6 online;
Database altered.
至此恢复完成。
- 补充
在此过程中，由于没有catalog，出现了错误，但是给人了幻觉，特此记录下：
接上面没有catalog那一步骤，直接进行恢复。
RMAN> run{
2> ALLOCATE CHANNEL c1 DEVICE TYPE DISK;
3> ALLOCATE CHANNEL c2 DEVICE TYPE DISK;
4> ALLOCATE CHANNEL c3 DEVICE TYPE DISK;
5> set newname for datafile '+DATADG/orcl/datafile/users.264.1023146143' to '+DATADG/orcl/datafile/user.dbf';
6> set newname for datafile '+DATADG/orcl/datafile/TEST.267.1057307953' to '+DATADG/orcl/datafile/test.dbf';
7> restore datafile 4,6;
8> switch datafile all;
9> }
allocated channel: c1
channel c1: SID=405 device type=DISK
allocated channel: c2
channel c2: SID=27 device type=DISK
allocated channel: c3
channel c3: SID=408 device type=DISK
executing command: SET NEWNAME
executing command: SET NEWNAME
Starting restore at 22-NOV-20
channel c1: restoring datafile 00004
input datafile copy RECID=3 STAMP=1057188572 file name=/home/oracle/copy_data_D-ORCL_I-1550926869_TS-USERS_FNO-4_04vg6qms.bak
destination for restore of datafile 00004: +DATADG/orcl/datafile/user.dbf
channel c2: restoring datafile 00006
input datafile copy RECID=2 STAMP=1057188571 file name=/home/oracle/copy_data_D-ORCL_I-1550926869_TS-TEST_FNO-6_03vg6qmr.bak
destination for restore of datafile 00006: +DATADG/orcl/datafile/test.dbf
ORA-19505: failed to identify file "/home/oracle/copy_data_D-ORCL_I-1550926869_TS-USERS_FNO-4_04vg6qms.bak"
ORA-27037: unable to obtain file status
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3
ORA-19600: input file is datafile-copy 3 (/home/oracle/copy_data_D-ORCL_I-1550926869_TS-USERS_FNO-4_04vg6qms.bak)
ORA-19601: output file is datafile 4 (+DATADG/orcl/datafile/user.dbf)
ORA-19505: failed to identify file "/home/oracle/copy_data_D-ORCL_I-1550926869_TS-TEST_FNO-6_03vg6qmr.bak"
ORA-27037: unable to obtain file status
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3
ORA-19600: input file is datafile-copy 2 (/home/oracle/copy_data_D-ORCL_I-1550926869_TS-TEST_FNO-6_03vg6qmr.bak)
ORA-19601: output file is datafile 6 (+DATADG/orcl/datafile/test.dbf)
failover to previous backup
creating datafile file number=4 name=+DATADG/orcl/datafile/user.dbf
channel c1: restoring datafile 00006
input datafile copy RECID=1 STAMP=1057188460 file name=/home/oracle/copy.bak
destination for restore of datafile 00006: +DATADG/orcl/datafile/test.dbf
ORA-19505: failed to identify file "/home/oracle/copy.bak"
ORA-27037: unable to obtain file status
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3
ORA-19600: input file is datafile-copy 1 (/home/oracle/copy.bak)
ORA-19601: output file is datafile 6 (+DATADG/orcl/datafile/test.dbf)
failover to previous backup
creating datafile file number=6 name=+DATADG/orcl/datafile/test.dbf
Finished restore at 22-NOV-20
released channel: c1
released channel: c2
released channel: c3
路径识别错误，控制文件中的备份信息还是在原来的路径下，我们现在路径已经变化，必须更新下。但是尽然还能resotre成功，比较迷惑。
ASMCMD> ls -ltr
WARNING:option 'r' is deprecated for 'ls'
please use 'reverse'
Type Redund Striped Time Sys Name
DATAFILE UNPROT COARSE NOV 23 23:00:00 Y SYSAUX.261.1023146139
DATAFILE UNPROT COARSE NOV 23 23:00:00 Y SYSTEM.260.1023146137
DATAFILE UNPROT COARSE NOV 23 23:00:00 Y TEST.264.1057273615
DATAFILE UNPROT COARSE NOV 23 23:00:00 Y UNDOTBS1.262.1023146139
DATAFILE UNPROT COARSE NOV 23 23:00:00 Y USERS.267.1057273615
DATAFILE UNPROT COARSE NOV 23 23:00:00 Y ZHUO.266.1024792071
虽然看起来此时的数据文件已经restore出来了
RMAN> report schema;
Report of database schema for database with db_unique_name ORCL
List of Permanent Datafiles
===========================
File Size(MB) Tablespace RB segs Datafile Name
---- -------- -------------------- ------- ------------------------
1 700 SYSTEM *** +DATADG/orcl/datafile/system.260.1023146137
2 600 SYSAUX *** +DATADG/orcl/datafile/sysaux.261.1023146139
3 350 UNDOTBS1 *** +DATADG/orcl/datafile/undotbs1.262.1023146139
4 5 USERS *** +DATADG/orcl/datafile/users.267.1057273615
5 5120 ZHUO *** +DATADG/orcl/datafile/zhuo.266.1024792071
6 10 TEST *** +DATADG/orcl/datafile/test.264.1057273615
List of Temporary Files
=======================
File Size(MB) Tablespace Maxsize(MB) Tempfile Name
---- -------- -------------------- ----------- --------------------
1 20 TEMP 32767 +DATADG/orcl/tempfile/temp.263.1023146139
数据文件4，6的大小也正常了，但是recover的时候报错了。
RMAN> recover datafile 4,6;
Starting recover at 23-NOV-20
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=22 device type=DISK
starting media recovery
archived log for thread 1 with sequence 26 is already on disk as file /u01/app/oracle/product/11.2.0/dbhome_1/dbs/arch1_26_1023146133.dbf
archived log for thread 1 with sequence 27 is already on disk as file /u01/app/oracle/product/11.2.0/dbhome_1/dbs/arch1_27_1023146133.dbf
unable to find archived log
archived log thread=1 sequence=1
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of recover command at 11/23/2020 23:07:36
RMAN-06054: media recovery requesting unknown archived log for thread 1 with sequence 1 and starting SCN of 16117
此时recover的时候，就会报错，但是我们的归档文件都是存在的。
SQL> alter database datafile 4,6 online;
alter database datafile 4,6 online
*
ERROR at line 1:
ORA-01157: cannot identify/lock data file 4 - see DBWR trace file
ORA-01110: data file 4: '+DATADG/orcl/datafile/users.264.1023146143'
所以此方法还是错误的。
我们删除数据文件4，6，按照新的路径执行恢复
ASMCMD> rm -rf USERS.267.1057273615
ASMCMD> rm -rf TEST.264.1057273615
backup datafile
备份：
RMAN> backup datafile 4,6 format '/home/oracle/copy.bak';
Starting backup at 24-NOV-20
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=403 device type=DISK
channel ORA_DISK_1: starting full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
input datafile file number=00006 name=+DATADG/orcl/datafile/test.dbf
input datafile file number=00004 name=+DATADG/orcl/datafile/user.dbf
channel ORA_DISK_1: starting piece 1 at 24-NOV-20
channel ORA_DISK_1: finished piece 1 at 24-NOV-20
piece handle=/home/oracle/copy.bak tag=TAG20201124T085423 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01
Finished backup at 24-NOV-20
RMAN> list backup of datafile 4,6;
List of Backup Sets
===================
BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
1 Full 2.04M DISK 00:00:00 24-NOV-20
BP Key: 1 Status: AVAILABLE Compressed: NO Tag: TAG20201124T085423
Piece Name: /home/oracle/copy.bak
List of Datafiles in backup set 1
File LV Type Ckp SCN Ckp Time Name
---- -- ---- ---------- --------- ----
4 Full 373380 24-NOV-20 +DATADG/orcl/datafile/user.dbf
6 Full 373380 24-NOV-20 +DATADG/orcl/datafile/test.dbf
修改备份文件的路径，模拟异机恢复，路径不一致的情况。
[oracle@11gasm tmp]$ mv /home/oracle/copy.bak /tmp/
[oracle@11gasm tmp]$ ll copy.bak
-rw-r----- 1 oracle dba 2146304 Nov 24 08:54 copy.bak
按照前面步骤，offline表空间，删除数据库文件。
查看备份集信息，并把新路径下的备份集注册到控制文件中
RMAN> list backup of datafile 4,6;
List of Backup Sets
===================
BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
1 Full 2.04M DISK 00:00:00 24-NOV-20
BP Key: 1 Status: AVAILABLE Compressed: NO Tag: TAG20201124T085423
Piece Name: /home/oracle/copy.bak
List of Datafiles in backup set 1
File LV Type Ckp SCN Ckp Time Name
---- -- ---- ---------- --------- ----
4 Full 373380 24-NOV-20 +DATADG/orcl/datafile/users.264.1057322415
6 Full 373380 24-NOV-20 +DATADG/orcl/datafile/test.267.1057322415
RMAN> catalog start with '/tmp/copy';
searching for all files that match the pattern /tmp/copy
List of Files Unknown to the Database
=====================================
File Name: /tmp/copy.bak
Do you really want to catalog the above files (enter YES or NO)? yes
cataloging files...
cataloging done
List of Cataloged Files
=======================
File Name: /tmp/copy.bak
[oracle@11gasm ~]$ cp copy.bak /tmp/
[oracle@11gasm ~]$ rm -rf copy.bak
RMAN> crosscheck backupset;
using channel ORA_DISK_1
crosschecked backup piece: found to be 'EXPIRED'
backup piece handle=/home/oracle/copy.bak RECID=1 STAMP=1057328967
crosschecked backup piece: found to be 'AVAILABLE'
backup piece handle=/tmp/copy.bak RECID=2 STAMP=1057329074
Crosschecked 2 objects
RMAN> delete expired backupset;
using channel ORA_DISK_1
List of Backup Pieces
BP Key BS Key Pc# Cp# Status Device Type Piece Name
------- ------- --- --- ----------- ----------- ----------
1 1 1 1 EXPIRED DISK /home/oracle/copy.bak
Do you really want to delete the above objects (enter YES or NO)? yes
deleted backup piece
backup piece handle=/home/oracle/copy.bak RECID=1 STAMP=1057328967
Deleted 1 EXPIRED objects
RMAN> list backup of datafile 4,6;
List of Backup Sets
===================
BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
1 Full 2.04M DISK 00:00:01 24-NOV-20
BP Key: 2 Status: AVAILABLE Compressed: NO Tag: TAG20201124T142927
Piece Name: /tmp/copy.bak
List of Datafiles in backup set 1
File LV Type Ckp SCN Ckp Time Name
---- -- ---- ---------- --------- ----
4 Full 371915 24-NOV-20 +DATADG/orcl/datafile/users.264.1023146143
6 Full 371915 24-NOV-20 +DATADG/orcl/datafile/test.267.1057328945
执行恢复：
RMAN> run{
2> ALLOCATE CHANNEL c1 DEVICE TYPE DISK;
3> ALLOCATE CHANNEL c2 DEVICE TYPE DISK;
4> ALLOCATE CHANNEL c3 DEVICE TYPE DISK;
5> set newname for datafile 4 to '+DATADG';
6> set newname for datafile 6 to '+DATADG';
7> restore datafile 4,6;
8> switch datafile all;
9> }
released channel: ORA_DISK_1
allocated channel: c1
channel c1: SID=400 device type=DISK
allocated channel: c2
channel c2: SID=26 device type=DISK
allocated channel: c3
channel c3: SID=404 device type=DISK
executing command: SET NEWNAME
executing command: SET NEWNAME
Starting restore at 24-NOV-20
channel c1: starting datafile backup set restore
channel c1: specifying datafile(s) to restore from backup set
channel c1: restoring datafile 00004 to +DATADG
channel c1: restoring datafile 00006 to +DATADG
channel c1: reading from backup piece /tmp/copy.bak
channel c1: piece handle=/tmp/copy.bak tag=TAG20201124T142927
channel c1: restored backup piece 1
channel c1: restore complete, elapsed time: 00:00:02
Finished restore at 24-NOV-20
datafile 4 switched to datafile copy
input datafile copy RECID=3 STAMP=1057329180 file name=+DATADG/orcl/datafile/users.267.1057329179
datafile 6 switched to datafile copy
input datafile copy RECID=4 STAMP=1057329180 file name=+DATADG/orcl/datafile/test.264.1057329179
released channel: c1
released channel: c2
released channel: c3
RMAN> recover datafile 4,6;
Starting recover at 24-NOV-20
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=400 device type=DISK
starting media recovery
media recovery complete, elapsed time: 00:00:00
Finished recover at 24-NOV-20
SQL> alter database datafile 4,6 online;
Database altered.
至此恢复完成。
参考：
https://www.cnblogs.com/jyzhao/p/7459415.html
扫一扫
1212
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
