rman异机异目录恢复

1.收集源库信息

1.1 备份策略

• 周日全备 周一、周二增量
• 周三全备 周四、周五、周六增量，每天凌晨 1 点执行备份，同时备份数据文件、归档日志
• 备份脚本如下

#!/usr/bin/ksh
export date=`date +%Y-%m-%d-%H-%M`
export dir=/data01/orcl/backupset/$date  
mkdir -p $dir
mkdir -p $dir/log

rman target / nocatalog log="$dir/log/backup.log" << EOF
run {
show all;
CONFIGURE RETENTION POLICY TO recovery window of 15 days;
configure controlfile autobackup on;
configure controlfile autobackup format for device type disk to '$dir/control_%F'; 
allocate channel c1 device type disk;
allocate channel c2 device type disk;
allocate channel c3 device type disk;
allocate channel c4 device type disk;
backup as compressed backupset incremental level 0 tag 'db_level0' database format '$dir/db_%n_%T_%U';
sql 'alter system archive log current';
backup  archivelog all not backed up format '$dir/arch_%n_%T_%U' delete all input;
release channel c1;
release channel c2;
release channel c3;
release channel c4;
}
EOF

• 恢复需求：要求恢复截止 2018-08-31 （周五）的所有数据，因此，本次恢复需要使用 8.29 - 9.1 的备份

1.2 物理结构

• 1）控制文件

SYS@orcl> select name from v$controlfile;

NAME
--------------------------------------------------------------------------------
/oradata02/orcl/control01.ctl
/oradata02/fast_recovery_area/orcl/control02.ctl

• 2）数据文件

SYS@orcl> select name,BYTES/1024/1024 from v$datafile;

NAME                                      BYTES/1024/1024
----------------------------------------- ------------------
/oradata02/orcl/system01.dbf                        2048
/oradata02/orcl/sysaux01.dbf                        2048
/oradata02/orcl/undotbs01.dbf                       4096
/oradata02/orcl/users01.dbf                          500
/oradata02/orcl/NNC_DATA01_01.dbf                  30720
/oradata02/orcl/NNC_INDEX01.dbf                    30720

• 3）临时文件

SYS@orcl> select name,BYTES/1024/1024 from v$tempfile;

NAME                               BYTES/1024/1024
---------------------------------  -----------------
/oradata02/orcl/temp01.dbf                      20
/oradata02/orcl/temp02.dbf                   10240

• 4）redo 日志文件

SYS@orcl> select l.GROUP#,l.BYTES/1024/1024,lf.MEMBER from v$log l,v$logfile lf where l.GROUP#=lf.GROUP#;

    GROUP# L.BYTES/1024/1024   MEMBER
---------- -----------------   --------------------------------------------------------
         1              200     /oradata02/orcl/redo/redo01a.log
         1              200     /oradata02/fast_recovery_area/orcl/redo/redo01b.log
         2              200     /oradata02/orcl/redo/redo02a.log
         2              200     /oradata02/fast_recovery_area/orcl/redo/redo02b.log
         3              200     /oradata02/orcl/redo/redo03a.log
         3              200     /oradata02/fast_recovery_area/orcl/redo/redo03b.log
         4              200     /oradata02/orcl/redo/redo04a.log
         4              200     /oradata02/fast_recovery_area/orcl/redo/redo04b.log
         5              200     /oradata02/orcl/redo/redo05a.log
         5              200     /oradata02/fast_recovery_area/orcl/redo/redo05b.log
         6              200     /oradata02/orcl/redo/redo06a.log
         6              200     /oradata02/fast_recovery_area/orcl/redo/redo06b.log

• 5）因此，控制文件+数据文件+临时文件+重做日志文件+归档日志文件，至少需要 80G 存储空间

2. 异机恢复

2.1 配置参数文件

• 1）拷贝源库的 pfile 文件到目标主机，根据目标主机的环境进行修改（文件路径、监听、内存等）

*._PX_use_large_pool=TRUE
*._serial_direct_read='FALSE'
*.audit_file_dest='/u01/app/oracle/admin/orcl/adump'
*.audit_trail='none'
*.compatible='11.2.0.4.0'
*.control_files='/oradata/orcl/control01.ctl','/oradata/fast_recovery_area/orcl/control02.ctl'
*.db_block_size=8192
*.db_domain=''
*.db_name='orcl'
*.db_recovery_file_dest='/oradata/fast_recovery_area'
*.db_recovery_file_dest_size=5218762752
*.db_securefile='PERMITTED'
*.diagnostic_dest='/u01/app/oracle'
*.dispatchers='(PROTOCOL=TCP) (SERVICE=orclXDB)'
*.event='28401 trace name context forever,level 1'
*.local_listener=''
*.log_archive_dest_1='location=/oradata/archivelog/orcl'
*.log_archive_format='%t_%s_%r.dbf'
*.open_cursors=1000
*.pga_aggregate_target=2G
*.processes=1000
*.remote_login_passwordfile='EXCLUSIVE'
*.resource_limit=TRUE
*.session_cached_cursors=300
*.sessions=1105
*.sga_target=4G
*.undo_tablespace='UNDOTBS1'

• 2）创建所需目录

[oracle@db11g ~]$ mkdir -p /u01/app/oracle/admin/orcl/adump
[oracle@db11g ~]$ mkdir -p /oradata/orcl
[oracle@db11g ~]$ mkdir -p /oradata/fast_recovery_area/orcl
[oracle@db11g ~]$ mkdir -p /oradata/archivelog/orcl

• 3）启动数据库到 nomount

[oracle@db11g ~]$ export ORACLE_SID=orcl
[oracle@db11g ~]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.4.0 Production on Sat Sep 29 10:41:12 2018

Copyright (c) 1982, 2013, Oracle.  All rights reserved.

Connected to an idle instance.

SYS@orcl> create spfile from pfile='/home/oracle/initorcl.ora';

File created.

SYS@orcl> startup nomount
ORACLE instance started.

Total System Global Area 4275781632 bytes
Fixed Size          2260088 bytes
Variable Size        1157628808 bytes
Database Buffers     3103784960 bytes
Redo Buffers           12107776 bytes

SYS@orcl> show parameter control_files

NAME                         TYPE    VALUE
-------------------   -----------   ------------------------------------------------------------------------
control_files              string   /oradata/orcl/control01.ctl, /oradata/fast_recovery_area/orcl/control02.ctl

2.2 恢复控制文件

• 使用rman恢复控制文件

[oracle@db11g ~]$ rman target /

Recovery Manager: Release 11.2.0.4.0 - Production on Sat Sep 29 10:44:57 2018

Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.

connected to target database: orcl (not mounted)

RMAN> restore controlfile from '/backup/2018-08-29/control_c-1572781485-20180829-01';

Starting restore at 29-SEP-18
using channel ORA_DISK_1

channel ORA_DISK_1: restoring control file
channel ORA_DISK_1: restore complete, elapsed time: 00:00:03
output file name=/oradata/orcl/control01.ctl
output file name=/oradata/fast_recovery_area/orcl/control02.ctl
Finished restore at 29-SEP-18

RMAN> alter database mount;

database mounted
released channel: ORA_DISK_1

2.3 更新控制文件元数据

• 1）检查是否存在 expired 及 obsolete 的备份片

crosscheck backup;
report obsolete;

• 2）如果有，删除过期备份

delete expired backup;
delete obsolete;

• 3）注册备份片

RMAN> catalog start with '/backup/';

• 4）查看 v$logfile ，指定到新路径（sqlplus 操作）

select member from v$logfile;

-- 先创建 redo 目录
alter database rename file '/oradata02/orcl/redo/redo01a.log' to '/oradata/orcl/redo/redo01a.log';
alter database rename file '/oradata02/fast_recovery_area/orcl/redo/redo01b.log' to '/oradata/fast_recovery_area/orcl/redo/redo01b.log';
alter database rename file '/oradata02/orcl/redo/redo02a.log' to '/oradata/orcl/redo/redo02a.log';                                   
alter database rename file '/oradata02/fast_recovery_area/orcl/redo/redo02b.log' to '/oradata/fast_recovery_area/orcl/redo/redo02b.log';
alter database rename file '/oradata02/orcl/redo/redo03a.log' to '/oradata/orcl/redo/redo03a.log';                                  
alter database rename file '/oradata02/fast_recovery_area/orcl/redo/redo03b.log' to '/oradata/fast_recovery_area/orcl/redo/redo03b.log';
alter database rename file '/oradata02/orcl/redo/redo04a.log' to '/oradata/orcl/redo/redo04a.log';                                   
alter database rename file '/oradata02/fast_recovery_area/orcl/redo/redo04b.log' to '/oradata/fast_recovery_area/orcl/redo/redo04b.log';
alter database rename file '/oradata02/orcl/redo/redo05a.log' to '/oradata/orcl/redo/redo05a.log';                                  
alter database rename file '/oradata02/fast_recovery_area/orcl/redo/redo05b.log' to '/oradata/fast_recovery_area/orcl/redo/redo05b.log';
alter database rename file '/oradata02/orcl/redo/redo06a.log' to '/oradata/orcl/redo/redo06a.log';                                  
alter database rename file '/oradata02/fast_recovery_area/orcl/redo/redo06b.log' to '/oradata/fast_recovery_area/orcl/redo/redo06b.log';

• 5）查看 v$tempfile ，指定到新路径（sqlplus 操作）

select name from v$tempfile;

alter database rename file '/oradata02/orcl/temp01.dbf' to '/oradata/orcl/temp01.dbf';
alter database rename file '/oradata02/orcl/temp02.dbf' to '/oradata/orcl/temp02.dbf';

2.4 还原数据库

• 基于时间点的不完全恢复

run{
ALLOCATE CHANNEL C1 DEVICE TYPE DISK;
ALLOCATE CHANNEL C2 DEVICE TYPE DISK;
set newname for datafile 1 to '/oradata/orcl/system01.dbf';
set newname for datafile 2 to '/oradata/orcl/sysaux01.dbf';
set newname for datafile 3 to '/oradata/orcl/undotbs01.dbf';
set newname for datafile 4 to '/oradata/orcl/users01.dbf';
set newname for datafile 5 to '/oradata/orcl/NNC_DATA01_01.dbf';
set newname for datafile 6 to '/oradata/orcl/NNC_INDEX01.dbf';
restore database UNTIL TIME "to_date('2018-09-01 00:00:00','yyyy-mm-dd hh24:mi:ss')";
switch datafile all; 
RELEASE CHANNEL C1;
RELEASE CHANNEL C2;
}

2.5 恢复数据库

• 恢复数据库

run
{
ALLOCATE CHANNEL C1 DEVICE TYPE DISK;
ALLOCATE CHANNEL C2 DEVICE TYPE DISK;
recover database until time "TO_DATE('2018-09-01 00:00:00','yyyy-mm-dd hh24:mi:ss')";
RELEASE CHANNEL C1;
RELEASE CHANNEL C2;
}

2.6 开启数据库

• RMAN 不恢复 redo 和临时文件，开库过程中会自动创建重做日志和临时文件

SYS@orcl> alter database open resetlogs;

Database altered.

• 查看物理结构

RMAN> report schema;

using target database control file instead of recovery catalog
Report of database schema for database with db_unique_name orcl

List of Permanent Datafiles
===========================
File Size(MB) Tablespace           RB segs Datafile Name
---- -------- -------------------- ------- ------------------------
1    2048     SYSTEM               ***     /oradata/orcl/system01.dbf
2    2048     SYSAUX               ***     /oradata/orcl/sysaux01.dbf
3    4096     UNDOTBS1             ***     /oradata/orcl/undotbs01.dbf
4    500      USERS                ***     /oradata/orcl/users01.dbf
5    30720    NNC_DATA01           ***     /oradata/orcl/NNC_DATA01_01.dbf
6    30720    NNC_INDEX01          ***     /oradata/orcl/NNC_INDEX01.dbf

List of Temporary Files
=======================
File Size(MB) Tablespace           Maxsize(MB) Tempfile Name
---- -------- -------------------- ----------- --------------------
1    20       TEMP                 20          /oradata/orcl/temp01.dbf
2    10240    TEMP                 10240       /oradata/orcl/temp02.dbf

• 数据库异机恢复完成