本文为云贝教育 刘峰 原创，请尊重知识产权，转发请注明出处，不接受任何抄袭、演绎和未经注明出处的转载。

一、RMAN完全恢复

归档：完全恢复和不完全恢复

非归档：只能还原到最后一次备份状态（还原），没有恢复。

1、首先对数据库做一个全备。

$ vim rman.sql
$ cat rman.sql
run{
allocate channel c1 type disk;
allocate channel c2 type disk;
backup database format '/u01/app/oracle/backup/%U_%d_%s.bak';
}

$ rman target /
RMAN> @rman.sql

实验 1：SYSTEM 表空间损坏

1.1 模拟删除关键表空间

RMAN> report schema;

$rm /u01/app/oracle/oradata/CDB19C/system01.dbf

1.2 启动数据库

观察到以下错误

RMAN> startup force


Oracle instance started
database mounted
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of startup command at 09/22/2023 11:07:27
ORA-01157: cannot identify/lock data file 1 - see DBWR trace file
ORA-01110: data file 1: '/u01/app/oracle/oradata/CDB19C/system01.dbf

1.3 通过rman恢复

RMAN> restore datafile 1;

Starting restore at 22-SEP-23
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=259 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 /u01/app/oracle/oradata/CDB19C/system01.dbf
channel ORA_DISK_1: reading from backup piece /u01/app/oracle/backup/3p270iae_1_1_CDB19C_121.bak
channel ORA_DISK_1: piece handle=/u01/app/oracle/backup/3p270iae_1_1_CDB19C_121.bak tag=TAG20230922T110446
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:03
Finished restore at 22-SEP-23

RMAN> alter database open;

Statement processed

提问：为什么没有recover datafile 1就能打开数据了？

实验 2：非关键表空间损坏

2.1 在users表空间上创建表

conn u1/u1@pdb1

create table rman_recover2 (id number) tablespace users;
insert into rman_recover2 values(1);
commit;

2.2 模拟介质损坏，物理删除数据文件：

$ rm /u01/app/oracle/oradata/CDB19C/users01.dbf

2.3 继续插入数据，并提交

insert into rman_recover2 values(2);
commit;

2.4 触发完全检查点，写脏数据，报错：

alter system checkpoint
*
ERROR at line 1:
ORA-03113: end-of-file on communication channel
Process ID: 15015
Session ID: 36 Serial number: 39927

2.5 新建目录，假设这时候更换设备需要时间，先把数据恢复到另外一个新的目录下（不同的物理位置）：

$ mkdir -p /u01/app/oracle/oradata/tmp

2.6 启动数据库

> startup

ORACLE instance started.

Total System Global Area 1073738488 bytes
Fixed Size                  9143032 bytes
Variable Size             289406976 bytes
Database Buffers          767557632 bytes
Redo Buffers                7630848 bytes
Database mounted.
ORA-01157: cannot identify/lock data file 7 - see DBWR trace file
ORA-01110: data file 7: '/u01/app/oracle/oradata/CDB19C/users01.dbf'

2.7 使用 RMAN 恢复表空间

RMAN>
run{
sql 'alter database datafile 7 offline';
set newname for datafile 7 to '/u01/app/oracle/oradata/tmp/users01.dbf';
restore datafile 7 ;
switch datafile 7 ;
recover datafile 7 ;
sql 'alter database datafile 7 online';
}

set newname for：告诉 RMAN 还原数据文件的新位置指向哪里，这个命令在 restore前出现。

switch datafile：更新 controlfile，记录这个新位置，这个命令要在 recover 前出现。

2.8 恢复完成后，验证数据：

 select * from rman_recover2;

        ID
----------
         1
         2   

完全恢复，恢复到最后一次 commit

2.9 后面等到介质更换完成后，可以将表空间迁移回原来的位置：

alter database move datafile '/u01/app/oracle/oradata/tmp/users01.dbf' to '/u01/app/oracle/oradata/CDB19C/users01.dbf'

2.10 再次验证数据：

select * from rman_recover2;

实验 3：新建表空间，数据文件损坏

3.1 创建表空间并创建表

create tablespace tbs_rman datafile
'/u01/app/oracle/oradata/CDB19C/tbs_rman01.dbf' size 1m;

create table tbs_rman (id number) tablespace tbs_rman;
insert into tbs_rman values(1);
insert into tbs_rman values(2);
commit;

3.2、物理删除数据文件：

$ rm /u01/app/oracle/oradata/CDB19C/tbs_rman01.dbf

3.3、继续插入数据，模拟触发检查点

insert into tbs_rman values(3);
commit;

--模拟触发检查点
alter system checkpoint;
alter system checkpoint
*
ERROR at line 1:
ORA-03113: end-of-file on communication channel
Process ID: 15482
Session ID: 18 Serial number: 31359

3.4、尝试启动数据库

> startup


ORACLE instance started.

Total System Global Area 1073738488 bytes
Fixed Size                  9143032 bytes
Variable Size             289406976 bytes
Database Buffers          767557632 bytes
Redo Buffers                7630848 bytes
Database mounted.
ORA-01157: cannot identify/lock data file 13 - see DBWR trace file
ORA-01110: data file 13: '/u01/app/oracle/oradata/CDB19C/tbs_rman01.dbf

3.5 利用 RMAN 恢复数据文件，注意：RMAN 备份集里面没有新增DBF记录。

RMAN
run {
sql 'alter database datafile 13 offline';
restore datafile 13 ;
recover datafile 13 ;
sql 'alter database datafile 13 online';
alter database open;
}

3.6 验证数据：

 SElect * from tbs_rman;

        ID
----------
         1
         2
         3

二、RMAN不完全恢复

实验 1：基于 TIME 或 SCN

1.1、开启控制文件自动备份

RMAN> 
show all;
CONFIGURE CONTROLFILE AUTOBACKUP ON;

1.2 备份全库

控制文件自动备份，备份目的地默认为闪回恢复区 flash_recovery_area。

list backup;
CROSSCHECK backup;
delete backup;

backup database format '/home/oracle/bak/%U_%d_%s.bak';

1.3 获取当前时间或 SCN

实验情况可以提前考虑，生产环境需要进行日志挖掘或其他方式。

 alter session set nls_date_format = 'yyyy-mm-dd hh24:mi:ss';

 select sysdate from dual;

SYSDATE
-------------------
2023-09-22 11:50:44

 select current_scn from v$database;

CURRENT_SCN
-----------
    3197729

 select * from tbs_rman;

        ID
----------
         1
         2
         3

1.4 TRUNCATE 表

truncate table tbs_rman;

1.5 利用 RMAN 按时间点做不完全恢复

RMAN>
 run {
allocate channel c1 type disk;
allocate channel c2 type disk;
set until scn 3197729;
restore database;
recover database;
alter database open resetlogs;
}

如果数据库是OPEN状态，恢复会失败

RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of restore command at 09/22/2023 11:53:06
ORA-19870: error while restoring backup piece /home/oracle/bak/41270klf_1_1_CDB19C_129.bak
ORA-19573: cannot obtain exclusive enqueue for datafile 1
ORA-19890: data file already in use
ORA-45909: restore, recover or block media recovery may be in progress

需要将数据库启动到mount状态

shutdown immediate
startup mount

再次执行不完全恢复

RMAN>  run {
2> allocate channel c1 type disk;
3> allocate channel c2 type disk;
4> set until scn 3197729;
5> restore database;
6> recover database;
7> alter database open resetlogs;
8> }

using target database control file instead of recovery catalog
allocated channel: c1
channel c1: SID=18 device type=DISK

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

executing command: SET until clause

Starting restore at 22-SEP-23

skipping datafile 5; already restored to file /u01/app/oracle/oradata/CDB19C/pdbseed/system01.dbf
skipping datafile 6; already restored to file /u01/app/oracle/oradata/CDB19C/pdbseed/sysaux01.dbf
skipping datafile 8; already restored to file /u01/app/oracle/oradata/CDB19C/pdbseed/undotbs01.dbf
channel c1: starting datafile backup set restore
channel c1: specifying datafile(s) to restore from backup set
channel c1: restoring datafile 00001 to /u01/app/oracle/oradata/CDB19C/system01.dbf
channel c1: restoring datafile 00003 to /u01/app/oracle/oradata/CDB19C/sysaux01.dbf
channel c1: restoring datafile 00004 to /u01/app/oracle/oradata/CDB19C/undotbs01.dbf
channel c1: restoring datafile 00007 to /u01/app/oracle/oradata/CDB19C/users01.dbf
channel c1: restoring datafile 00013 to /u01/app/oracle/oradata/CDB19C/tbs_rman01.dbf
channel c1: reading from backup piece /home/oracle/bak/41270klf_1_1_CDB19C_129.bak
channel c2: starting datafile backup set restore
channel c2: specifying datafile(s) to restore from backup set
channel c2: restoring datafile 00009 to /u01/app/oracle/oradata/CDB19C/pdb1/system01.dbf
channel c2: restoring datafile 00010 to /u01/app/oracle/oradata/CDB19C/pdb1/sysaux01.dbf
channel c2: restoring datafile 00011 to /u01/app/oracle/oradata/CDB19C/pdb1/undotbs01.dbf
channel c2: restoring datafile 00012 to /u01/app/oracle/oradata/CDB19C/pdb1/users02.dbf
channel c2: reading from backup piece /home/oracle/bak/42270kln_1_1_CDB19C_130.bak
channel c2: piece handle=/home/oracle/bak/42270kln_1_1_CDB19C_130.bak tag=TAG20230922T114447
channel c2: restored backup piece 1
channel c2: restore complete, elapsed time: 00:00:19
channel c1: piece handle=/home/oracle/bak/41270klf_1_1_CDB19C_129.bak tag=TAG20230922T114447
channel c1: restored backup piece 1
channel c1: restore complete, elapsed time: 00:00:20
Finished restore at 22-SEP-23

Starting recover at 22-SEP-23

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

Finished recover at 22-SEP-23

Statement processed
released channel: c1
released channel: c2

1.6 验证数据

 select * from tbs_rman;

        ID
----------
         1
         2
         3

注意：基于 time 的方法与基于 scn 相似，语法是 set until time '2023-09-22 11:50:44';

基于日志的方法类似手工恢复的例子，语法是 set until sequence 3;

不完全恢复中手工与 RMAN 语法比较：

实验 2：恢复 SPFILE 或 CONTROLFILE

2.1 查看备份集

RMAN> list backup;

2.2 直接删除动态参数文件：

$ mv spfilecdb19c.ora spfilecdb19c.ora.bak

2.3 重启数据库

sys@CDB19C> startup force
ORA-01078: failure in processing system parameters
LRM-00109: could not open parameter file '/u01/app/oracle/product/19.3.0/dbs/initcdb19c.ora'

2.4 使用rman恢复参数文件

没有参数文件，SQL*PLUS 无法启动实例，但 RMAN 利用自身可以启动到 nomount状态下，因此 startup nomount 一定要在 RMAN 下做。

RMAN> 
startup nomount;
RMAN> 
restore spfile from
'/home/oracle/fast_recovery/CDB19C/autobackup/2023_09_22/o1_mf_s_1148211901_ljt3fxl1_.bkp'
或
RMAN> restore spfile from autobackup;

执行完毕后，查看$ORACLE_HOME/dbs/下已经生成动态参数文件。

2.5 控制文件恢复语句

方式与恢复参数文件一致

RMAN> restore controlfile from autobackup;

实验 3：恢复误删除的表空间（已备份）

3.1 打开告警日志，查看 drop tablespace 时间点

tail -f alert_cdb19c.log

3.2 删除 TBS_RMAN 表空间

drop tablespace TBS_RMAN including contents and datafiles;

3.3 查看告警有关信息

023-09-22T14:04:41.739151+08:00
Deleted file /u01/app/oracle/oradata/CDB19C/tbs_rman01.dbf
Completed: drop tablespace TBS_RMAN including contents and datafiles

3.4 删除所有控制文件和数据文件

这步可以不执行

3.5 利用 RMAN 做不完全恢复

run{
allocate channel c1 type disk;
allocate channel c2 type disk;
startup force nomount;
set dbid=629132443;
sql 'alter session set nls_date_format="yyyy-mm-dd hh24:mi:ss"';
restore controlfile from autobackup until time '2023-09-22 14:43:41';
alter database mount;
sql 'alter session set nls_date_format="yyyy-mm-dd hh24:mi:ss"';
set until time '2023-09-22 14:43:41';
restore database;
recover database;
alter database open resetlogs;
}

注：代码里加入了时间格式设置，如果不设置，可能会出现以下报错

ORA-01861: literal does not match format string

注：上面代码里加入了存储格式设置，如果不设置，可能会出现以下报错

ORA-19554: error allocating device, device type: SBT_TAPE, device name:
ORA-27211: Failed to load Media Management Library

3.6 验证数据

注意：不能使用当前的控制文件恢复误删除的表空间，因为当前控制文件已经没有该表空间的记录了。

DBID 是数据库的身份证，保存在控制文件中，set dbid 选项对于控制文件自动恢复（from autobackup）是必要的。

可以通过以下方式查询

#sqlplus
select dbid from v$database;

      DBID
----------
 629132443

#rman
$ rman target /
connected to target database: CDB19C (DBID=629132443)

实验 4：表空间时间点恢复

表空间时间点恢复（TableSpace Point In Time Recovery）TSPITR作为一条基本原则，不完全恢复必须应用到整个数据库，即必须还原整个数据库并运用日志一起向前滚动。TSPITR 是一种对个别表空间执行不完全恢复的技术，一般是针对用户错误的删除（或截断）表。TSPITR 的最大好处是不需要生产库停机。

注意：TSPITR 这个过程除了复制需要恢复的表空间外，也必须复制 system，sysaux和 undo 表空间（考点）。

4.1 做全备

 backup database;
或
 backup database format '/u01/app/oracle/backup/%U_%s_%d.bak';

4.2 创建新的表空间

create tablespace tbs_tspitr datafile
'/u01/app/oracle/oradata/CDB19C/tbs_tspitr01.dbf' size 1m;

4.3 创建测试表

create table tbs_tspitr (id number) tablespace tbs_tspitr;
insert into tbs_tspitr values(1);
commit;

4.4 单独备份表空间

RMAN> backup tablespace tbs_tspitr format
'/home/oracle/bak/tspitr_%s.bak';

4.5 取当前时间或 SCN

SQL> select sysdate from dual;
SQL> select current_scn from v$database;
CURRENT_SCN
-----------
    3417439

4.6 PURGE 方式删除表

drop table tbs_tspitr purge;

4.7 建立目录指定辅助库目标

!mkdir -p /home/oracle/bak/auxi

4.8 做 RMAN TSPITR

RMAN> recover tablespace tbs_tspitr until scn 3417439 auxiliary destination
'/home/oracle/bak/auxi';

4.9 联机后验证

select file#,checkpoint_change# from v$datafile;
 select file#,checkpoint_change# from v$datafile_header;
 select status from dba_tablespaces;
 select status from v$datafile;

 select * from tbs_tspitr;

注意：TSPITR 是保持在线业务下表空间级的不完全恢复，恢复的表空间要自包含。

三、RMAN归档备份与删除

3.1、使用backup archivelog来备份当前的归档

1) 备份所有归档

backup archivelog all format 'D:\orcl\rmanbackup\arch_%d_%T_%s' delete all input; 

delete input 表示只删除target数据库的节点输入的归档文件。
delete all input 标识删除所有节点输入的归档文件。

2）备份指定归档

备份seq为100至110的archive log
–说明:list backup of archivelog中限定日志的位置也适合backup archivelog
RMAN> backup archivelog sequence between 10 and 18 format '/home/oracle/bak/arcbak' delete input;

3) 使用 backup … keep 命令

RMAN> backup archivelog all format '/u01/app/oracle/backup/%d_%U.arc' tag 'Q107' keep forever;
RMAN> backup datafile 7 format '/u01/app/oracle/backup/%U_%d_%s.bak' keep until time 'sysdate+30' restore point abc;
RMAN> list restore point all;

Backup database plus archivelog;

1）查看备份的归档

select THREAD#,sequence#,FIRST_TIME,NEXT_TIME,COMPLETION_TIME
from v$archived_log order by thread#,sequence#;

RMAN> list backup of archivelog all;
列出所有archive log 备份

RMAN> list backup of archivelog from logseq 100 until logseq 120;
列出archive log 从100到120

RMAN> list backup of archivelog sequence between 100 and 110;
列出archive log 从100到120
–说明:between……and只能使用sequence，而不能使用logseq

RMAN> list backup of archivelog from logseq 100;
列出seq大于等于100的archive log

RMAN> list backup of archivelog low logseq 120;
列出seq大于等于120的archive log

RMAN> list backup of archivelog sequence 100;
列出seq为100的archive log
–说明:在对于rman中关于archivelog的操作中logseq与sequence作用相同，但是建议尽量使用sequence

RMAN> list backup of archivelog logseq 85;
列出seq为85的archive log

RMAN> list backup of archivelog until logseq 85;
列出seq小于等于85的archive log

RMAN> list backup of archivelog high logseq 40;
列出seq小于等于40的archive log

RMAN> list backup of archivelog from time ‘sysdate-7′;
列出7天以前的archive log


RMAN> list archivelog until time ‘sysdate-1’;
此时是按照归档日志开始时间即fisrt_time作为截止时间

RMAN> list archivelog all completed before ‘sysdate-1’;
是按照归档日志完成时间即completion_time作为截止时间

2）恢复指定归档

run {
set archivelog destination to '/home/oracle/bak/newlog';
restore archivelog low logseq 5;
}

四、 IMAGE COPY 的快速恢复

IMAGE COPY 的快速恢复指的是，没有 restore 这个过程，直接指向映像文件进行操作，之后 recover 即可，减少了因还原过程中所带来的时间消耗。

4.1 将 TEST 表空间进行备份

RMAN> backup as copy datafile 16 format
'/u01/app/oracle/backup/TBS_TSPITR_%U_%d_%s.bak';

4.2 查看下控制文件中目前标注的数据文件地址

 select name from v$datafile;

4.3 删除 test 数据文件

!rm -rf /u01/app/oracle/oradata/CDB19C/tbs_tspitr01.dbf

startup force

4.4 rman切换数据文件

 sql 'alter database datafile 16 offline';
 switch datafile 16 to copy;

这一步是直接更改了控制文件相关信息。

4.5、recover数据文件

recover datafile 16;

sql 'alter database datafile 16 online';
alter database open;

4.6 查看下控制文件中目前标注的数据文件地址

 select name from v$datafile;

4.7 后面如果想恢复原状，可以再进行数据复制后，改变指向

alter database move datafile '/u01/app/oracle/backup/TBS_TSPITR_data_D-CDB19C_I-629132443_TS-TBS_TSPITR_FNO-16_4s271117_CDB19C_156.bak' to '/u01/app/oracle/oradata/CDB19C/tbs_tspitr01.dbf';