本文模拟了Oracle的重定向恢复

上一篇文章讲了Oracle本机的备份与恢复

本文主要讲了Oracle重定向恢复的过程。将原来的数据库删除之后，改变了备份片的存储位置，然后恢复数据文件到一个不同的目录

原始备份片位置：/home/oracle/orcbackup
原始数据文件路径：/home/oracle/app/oracle/oradata/orcl/

把备份片移动到 /home/oracle/ newbackup下
目标是把数据文件路径恢复到/home/oracle/ newfilepath/orcl下

1. 删除数据库

sql> shutdown immediate;
sql> startup force mount;
sql> ALTER SYSTEM ENABLE RESTRICTED SESSION;
sql> drop database;

2.移动备份片

创建备份片新的目录，并移动备份片，里面包括了spfile/control file、数据文件、归档日志的备份：
[oracle@node01 ~]$ mkdir -p /home/oracle/newbackup
[oracle@node01 ~]$ mv /home/oracle/orcbackup/* /home/oracle/newbackup/
[oracle@node01 ~]$ ls -l /home/oracle/newbackup/
total 2379324
-rw-r-----. 1 oracle oinstall 1091559424 Mar 9 21:25 rman1_0pstcmht_1_1.bk
-rw-r-----. 1 oracle oinstall 549445632 Mar 9 21:26 rman1_0qstcmjb_1_1.bk
-rw-r-----. 1 oracle oinstall 537649152 Mar 9 21:26 rman1_0rstcmk5_1_1.bk
-rw-r-----. 1 oracle oinstall 201296384 Mar 9 21:29 rman1_0tstcmp1_1_1.bk
-rw-r-----. 1 oracle oinstall 18825216 Mar 9 21:26 rman_ctl_c-1496578512-20180309-01.bk
-rw-r-----. 1 oracle oinstall 18825216 Mar 9 21:29 rman_ctl_c-1496578512-20180309-02.bk
-rw-r-----. 1 oracle oinstall 18825216 Mar 9 22:04 rman_ctl_c-1496578512-20180309-03.bk

3. 创建数据文件目录

[oracle@node01 ~]$ mkdir -p /home/oracle/newfilepath/orcl

4. 恢复spfile

[oracle@node01 ~]$ export ORACLE_SID=orcl
[oracle@node01 ~]$ echo "db_name=orcl" > $ORACLE_HOME/dbs/pfile_tmp.ora

RMAN> startup nomount pfile='$ORACLE_HOME/dbs/pfile_tmp.ora';
RMAN> restore spfile from '/home/oracle/newbackup/rman_ctl_c-1496578512-20180309-02.bk';
RMAN> shutdown immediate;
RMAN> startup nomount;

5. 恢复控制文件

由于备份片位置发生了变化，所以要用catalog start with 指定新的位置
RMAN> restore controlfile from '/home/oracle/newbackup/rman_ctl_c-1496578512-20180309-03.bk';
RMAN> alter database mount;
RMAN> catalog start with '/home/oracle/newbackup/';

searching for all files that match the pattern /home/oracle/newbackup/

List of Files Unknown to the Database
=====================================
File Name: /home/oracle/newbackup/rman1_0pstcmht_1_1.bk
File Name: /home/oracle/newbackup/rman1_0qstcmjb_1_1.bk
File Name: /home/oracle/newbackup/rman1_0rstcmk5_1_1.bk
File Name: /home/oracle/newbackup/rman1_0tstcmp1_1_1.bk
File Name: /home/oracle/newbackup/rman_ctl_c-1496578512-20180309-01.bk
File Name: /home/oracle/newbackup/rman_ctl_c-1496578512-20180309-02.bk
File Name: /home/oracle/newbackup/rman_ctl_c-1496578512-20180309-03.bk

Do you really want to catalog the above files (enter YES or NO)? YES
cataloging files...
cataloging done

List of Cataloged Files
=======================
File Name: /home/oracle/newbackup/rman1_0pstcmht_1_1.bk
File Name: /home/oracle/newbackup/rman1_0qstcmjb_1_1.bk
File Name: /home/oracle/newbackup/rman1_0rstcmk5_1_1.bk
File Name: /home/oracle/newbackup/rman1_0tstcmp1_1_1.bk
File Name: /home/oracle/newbackup/rman_ctl_c-1496578512-20180309-01.bk
File Name: /home/oracle/newbackup/rman_ctl_c-1496578512-20180309-02.bk
File Name: /home/oracle/newbackup/rman_ctl_c-1496578512-20180309-03.bk

6. 恢复数据文件

先从控制文件里查询出所在待重定向的文件
sql> set linesize 120
sql> set heading off
sql> col name for a45
sql> select 'set newname for datafile '||file#||' to '||'''' ||rtrim(name)||''||''';' from v$datafile;

set newname for datafile 1 to '/home/oracle/app/oracle/oradata/orcl/system01.dbf';
set newname for datafile 3 to '/home/oracle/app/oracle/oradata/orcl/sysaux01.dbf';
set newname for datafile 4 to '/home/oracle/app/oracle/oradata/orcl/undotbs01.dbf';
set newname for datafile 5 to '/home/oracle/app/oracle/oradata/orcl/pdbseed/system01.dbf';
set newname for datafile 6 to '/home/oracle/app/oracle/oradata/orcl/pdbseed/sysaux01.dbf';
set newname for datafile 7 to '/home/oracle/app/oracle/oradata/orcl/users01.dbf';
set newname for datafile 8 to '/home/oracle/app/oracle/oradata/orcl/pdbseed/undotbs01.dbf';
set newname for datafile 9 to '/home/oracle/app/oracle/oradata/orcl/orclpdb/system01.dbf';
set newname for datafile 10 to '/home/oracle/app/oracle/oradata/orcl/orclpdb/sysaux01.dbf';
set newname for datafile 11 to '/home/oracle/app/oracle/oradata/orcl/orclpdb/undotbs01.dbf';
set newname for datafile 12 to '/home/oracle/app/oracle/oradata/orcl/orclpdb/users01.dbf';

11 rows selected.

sql> select 'set newname for tempfile '||file#||' to '||'''' ||rtrim(name)||''||''';' from v$tempfile;

set newname for tempfile 1 to '/home/oracle/app/oracle/oradata/orcl/temp01.dbf';
set newname for tempfile 2 to '/home/oracle/app/oracle/oradata/orcl/pdbseed/temp012018-02-22_22-31-25-051-PM.dbf';
set newname for tempfile 3 to '/home/oracle/app/oracle/oradata/orcl/orclpdb/temp01.dbf';

[oracle@node01 orcl]$ mkdir -p /home/oracle/newfilepath/orcl/pdbseed/
[oracle@node01 orcl]$ mkdir -p /home/oracle/newfilepath/orcl/orclpdb/

RMAN> run
{
set newname for datafile 1 to '/home/oracle/newfilepath/orcl/system01.dbf';
set newname for datafile 3 to '/home/oracle/newfilepath/orcl/sysaux01.dbf';
set newname for datafile 4 to '/home/oracle/newfilepath/orcl/undotbs01.dbf';
set newname for datafile 5 to '/home/oracle/newfilepath/orcl/pdbseed/system01.dbf';
set newname for datafile 6 to '/home/oracle/newfilepath/orcl/pdbseed/sysaux01.dbf';
set newname for datafile 7 to '/home/oracle/newfilepath/orcl/users01.dbf';
set newname for datafile 8 to '/home/oracle/newfilepath/orcl/pdbseed/undotbs01.dbf';
set newname for datafile 9 to '/home/oracle/newfilepath/orcl/orclpdb/system01.dbf';
set newname for datafile 10 to '/home/oracle/newfilepath/orcl/orclpdb/sysaux01.dbf';
set newname for datafile 11 to '/home/oracle/newfilepath/orcl/orclpdb/undotbs01.dbf';
set newname for datafile 12 to '/home/oracle/newfilepath/orcl/orclpdb/users01.dbf';
set newname for tempfile 1 to '/home/oracle/newfilepath/orcl/temp01.dbf';
set newname for tempfile 2 to '/home/oracle/newfilepath/orcl/pdbseed/temp012018-02-22_22-31-25-051-PM.dbf';
set newname for tempfile 3 to '/home/oracle/newfilepath/orcl/orclpdb/temp01.dbf';
restore database;
switch datafile all;
switch tempfile all;
}

Starting restore at 20-MAR-18
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=390 device type=DISK

channel ORA_DISK_1: starting datafile backup set restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_DISK_1: restoring datafile 00001 to /home/oracle/newfilepath/orcl/system01.dbf
channel ORA_DISK_1: restoring datafile 00003 to /home/oracle/newfilepath/orcl/sysaux01.dbf
channel ORA_DISK_1: restoring datafile 00004 to /home/oracle/newfilepath/orcl/undotbs01.dbf
channel ORA_DISK_1: restoring datafile 00007 to /home/oracle/newfilepath/orcl/users01.dbf
channel ORA_DISK_1: reading from backup piece /home/oracle/orcbackup/rman1_0pstcmht_1_1.bk
channel ORA_DISK_1: errors found reading piece handle=/home/oracle/orcbackup/rman1_0pstcmht_1_1.bk
channel ORA_DISK_1: failover to piece handle=/home/oracle/newbackup/rman1_0pstcmht_1_1.bk tag=TAG20180309T212517
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete,elapsed time: 00:00:55
channel ORA_DISK_1: starting datafile backup set restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_DISK_1: restoring datafile 00009 to /home/oracle/newfilepath/orcl/orclpdb/system01.dbf
channel ORA_DISK_1: restoring datafile 00010 to /home/oracle/newfilepath/orcl/orclpdb/sysaux01.dbf
channel ORA_DISK_1: restoring datafile 00011 to /home/oracle/newfilepath/orcl/orclpdb/undotbs01.dbf
channel ORA_DISK_1: restoring datafile 00012 to /home/oracle/newfilepath/orcl/orclpdb/users01.dbf
channel ORA_DISK_1: reading from backup piece /home/oracle/orcbackup/rman1_0qstcmjb_1_1.bk
channel ORA_DISK_1: errors found reading piece handle=/home/oracle/orcbackup/rman1_0qstcmjb_1_1.bk
channel ORA_DISK_1: failover to piece handle=/home/oracle/newbackup/rman1_0qstcmjb_1_1.bk tag=TAG20180309T212517
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete,elapsed time: 00:00:25
channel ORA_DISK_1: starting datafile backup set restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_DISK_1: restoring datafile 00005 to /home/oracle/newfilepath/orcl/pdbseed/system01.dbf
channel ORA_DISK_1: restoring datafile 00006 to /home/oracle/newfilepath/orcl/pdbseed/sysaux01.dbf
channel ORA_DISK_1: restoring datafile 00008 to /home/oracle/newfilepath/orcl/pdbseed/undotbs01.dbf
channel ORA_DISK_1: reading from backup piece /home/oracle/orcbackup/rman1_0rstcmk5_1_1.bk
channel ORA_DISK_1: errors found reading piece handle=/home/oracle/orcbackup/rman1_0rstcmk5_1_1.bk
channel ORA_DISK_1: failover to piece handle=/home/oracle/newbackup/rman1_0rstcmk5_1_1.bk tag=TAG20180309T212517
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete,elapsed time: 00:00:25
Finished restore at 20-MAR-18

datafile 1 switched to datafile copy
input datafile copy RECID=15 STAMP=971300987 file name=/home/oracle/newfilepath/orcl/system01.dbf
datafile 3 switched to datafile copy
input datafile copy RECID=16 STAMP=971300987 file name=/home/oracle/newfilepath/orcl/sysaux01.dbf
datafile 4 switched to datafile copy
input datafile copy RECID=17 STAMP=971300987 file name=/home/oracle/newfilepath/orcl/undotbs01.dbf
datafile 5 switched to datafile copy
input datafile copy RECID=18 STAMP=971300987 file name=/home/oracle/newfilepath/orcl/pdbseed/system01.dbf
datafile 6 switched to datafile copy
input datafile copy RECID=19 STAMP=971300988 file name=/home/oracle/newfilepath/orcl/pdbseed/sysaux01.dbf
datafile 7 switched to datafile copy
input datafile copy RECID=20 STAMP=971300988 file name=/home/oracle/newfilepath/orcl/users01.dbf
datafile 8 switched to datafile copy
input datafile copy RECID=21 STAMP=971300988 file name=/home/oracle/newfilepath/orcl/pdbseed/undotbs01.dbf
datafile 9 switched to datafile copy
input datafile copy RECID=22 STAMP=971300988 file name=/home/oracle/newfilepath/orcl/orclpdb/system01.dbf
datafile 10 switched to datafile copy
input datafile copy RECID=23 STAMP=971300988 file name=/home/oracle/newfilepath/orcl/orclpdb/sysaux01.dbf
datafile 11 switched to datafile copy
input datafile copy RECID=24 STAMP=971300988 file name=/home/oracle/newfilepath/orcl/orclpdb/undotbs01.dbf
datafile 12 switched to datafile copy
input datafile copy RECID=25 STAMP=971300988 file name=/home/oracle/newfilepath/orcl/orclpdb/users01.dbf

renamed tempfile 1 to /home/oracle/newfilepath/orcl/temp01.dbf in control file
renamed tempfile 2 to /home/oracle/newfilepath/orcl/pdbseed/temp012018-02-22_22-31-25-051-PM.dbf in control file
renamed tempfile 3 to /home/oracle/newfilepath/orcl/orclpdb/temp01.dbf in control file

7. recover database

RMAN> recover database;

Starting recover at 20-MAR-18
using channel ORA_DISK_1

starting media recovery

archived log for thread 1 with sequence 10 is already on disk as file /home/oracle/archlog/orcl/orcl_1_10_968797779.arc
archived log for thread 1 with sequence 11 is already on disk as file /home/oracle/archlog/orcl/orcl_1_11_968797779.arc
archived log file name=/home/oracle/archlog/orcl/orcl_1_10_968797779.arc thread=1 sequence=10
archived log file name=/home/oracle/archlog/orcl/orcl_1_11_968797779.arc thread=1 sequence=11
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 03/20/2018 21:50:23
RMAN-06054: media recovery requesting unknown archived log for thread 1 with sequence 1 and starting SCN of 1483277

8. open database

RMAN> alter database open resetlogs;

Statement processed

9. 文件状态检查及数据验证

sql> set linesize 130
sql> col file_name for a50
sql> col online_status for a30
sql> select file_name,status,online_status from dba_data_files;

FILE_NAME STATUS ONLINE_STATUS
--------------------------------------------- --------- ------------------------------
/home/oracle/newfilepath/orcl/users01.dbf AVAILABLE ONLINE
/home/oracle/newfilepath/orcl/undotbs01.dbf AVAILABLE ONLINE
/home/oracle/newfilepath/orcl/system01.dbf AVAILABLE SYSTEM
/home/oracle/newfilepath/orcl/sysaux01.dbf AVAILABLE ONLINE

sql> select file_name,status from dba_temp_files;

FILE_NAME STATUS
-------------------------------------------------- -------
/home/oracle/newfilepath/orcl/temp01.dbf ONLINE

Redo log检查：
sql> set linesize 120
sql> col member for a50
sql> select a.group#,a.members,b.member,a.bytes/1024/1024 as "Size(MB)",a.status from v$log a,v$logfile b where a.group#=b.group#;

GROUP# MEMBERS MEMBER Size(MB) STATUS
---------- ---------- -------------------------------------------------- ---------- ----------------
3 1 /home/oracle/app/oracle/oradata/orcl/redo03.log 200 UNUSED
2 1 /home/oracle/app/oracle/oradata/orcl/redo02.log 200 UNUSED
1 1 /home/oracle/app/oracle/oradata/orcl/redo01.log 200 CURRENT

sql> select * from t1; ID ---------- 10010 20020