【RMAN】RMAN恢复
一、数据库进行完全介质恢复
如果数据库只剩下控制文件和参数文件,数据文件因为丢失或损坏,之前创建过整库的备价,并且执行备份操作之后,所有的归档日志文件和重做日志文件都还在,这种情况下就可以将数据库恢复到崩溃前那一刻的状态，这种恢复方式，叫做完全介质恢复。
1、我们使用pdb数据库下scott用户中的数据做测试
SQL> create table test as select * from emp; Table created. SQL> select * from test; EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO ---------- ---------- --------- ---------- --------- ---------- ---------- ---------- 7369 SMITH CLERK 7902 17-DEC-80 800 20 7499 ALLEN SALESMAN 7698 20-FEB-81 1600 300 30 7521 WARD SALESMAN 7698 22-FEB-81 1250 500 30 7566 JONES MANAGER 7839 02-APR-81 2975 20 7654 MARTIN SALESMAN 7698 28-SEP-81 1250 1400 30 7698 BLAKE MANAGER 7839 01-MAY-81 2850 30 7782 CLARK MANAGER 7839 09-JUN-81 2450 10 7788 SCOTT ANALYST 7566 19-APR-87 3000 20 7839 KING PRESIDENT 17-NOV-81 5000 10 7844 TURNER SALESMAN 7698 08-SEP-81 1500 0 30 7876 ADAMS CLERK 7788 23-MAY-87 1100 20 7900 JAMES CLERK 7698 03-DEC-81 950 30 7902 FORD ANALYST 7566 03-DEC-81 3000 20 7934 MILLER CLERK 7782 23-JAN-82 1300 10 14 rows selected.
2、备份pdb
RMAN> backup pluggable database pdb;
3、OS下删除pdb的user01.dbf数据文件，打开数据库
[oracle@T1 backupset]$ cd /u01/app/oracle/oradata/T1/pdb/ [oracle@T1 pdb]$ ll total 814952 -rw-r----- 1 oracle oinstall 52436992 Jul 27 11:12 rman_catalog.dbf -rw-r----- 1 oracle oinstall 377495552 Jul 27 11:12 sysaux01.dbf -rw-r----- 1 oracle oinstall 293609472 Jul 27 11:12 system01.dbf -rw-r----- 1 oracle oinstall 37756928 Jul 26 14:12 temp01.dbf -rw-r----- 1 oracle oinstall 104865792 Jul 27 11:12 undotbs01.dbf -rw-r----- 1 oracle oinstall 5251072 Jul 27 11:12 users01.dbf [oracle@T1 pdb]$ rm -rf users01.dbf SQL> alter pluggable database pdb close; Pluggable database altered. SQL> alter pluggable database pdb open; alter pluggable database pdb open * ERROR at line 1: ORA-65368: unable to open the pluggable database due to errors during recovery ORA-01110: data file 12: '/u01/app/oracle/oradata/T1/pdb/users01.dbf' ORA-01157: cannot identify/lock data file 12 - see DBWR trace file ORA-01110: data file 12: '/u01/app/oracle/oradata/T1/pdb/users01.dbf'
4、执行恢复操作
RMAN> restore pluggable database pdb; Starting restore at 27-JUL-21 using channel ORA_DISK_1 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 /u01/app/oracle/oradata/T1/pdb/system01.dbf channel ORA_DISK_1: restoring datafile 00010 to /u01/app/oracle/oradata/T1/pdb/sysaux01.dbf channel ORA_DISK_1: restoring datafile 00011 to /u01/app/oracle/oradata/T1/pdb/undotbs01.dbf channel ORA_DISK_1: restoring datafile 00012 to /u01/app/oracle/oradata/T1/pdb/users01.dbf channel ORA_DISK_1: restoring datafile 00015 to /u01/app/oracle/oradata/T1/pdb/rman_catalog.dbf channel ORA_DISK_1: reading from backup piece /u01/app/backup/backupset/T1_20210727_1079003528.bak channel ORA_DISK_1: piece handle=/u01/app/backup/backupset/T1_20210727_1079003528.bak tag=TAG20210727T111208 channel ORA_DISK_1: restored backup piece 1 channel ORA_DISK_1: restore complete, elapsed time: 00:00:03 Finished restore at 27-JUL-21 RMAN> recover pluggable database pdb; Starting recover at 27-JUL-21 using channel ORA_DISK_1 starting media recovery media recovery complete, elapsed time: 00:00:00 Finished recover at 27-JUL-21
5、打开pdb查看scott用户下test表
SQL> alter pluggable database pdb open; Pluggable database altered. SQL> Disconnected from Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production Version 19.3.0.0.0 [oracle@T1 ~]$ sqlplus scott/scott@pdb SQL*Plus: Release 19.0.0.0.0 - Production on Tue Jul 27 11:22:28 2021 Version 19.3.0.0.0 Copyright (c) 1982, 2019, Oracle. All rights reserved. Last Successful login time: Tue Jul 27 2021 11:14:17 +08:00 Connected to: Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production Version 19.3.0.0.0 SQL> select * from test; EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO ---------- ---------- --------- ---------- --------- ---------- ---------- ---------- 7369 SMITH CLERK 7902 17-DEC-80 800 20 7499 ALLEN SALESMAN 7698 20-FEB-81 1600 300 30 7521 WARD SALESMAN 7698 22-FEB-81 1250 500 30 7566 JONES MANAGER 7839 02-APR-81 2975 20 7654 MARTIN SALESMAN 7698 28-SEP-81 1250 1400 30 7698 BLAKE MANAGER 7839 01-MAY-81 2850 30 7782 CLARK MANAGER 7839 09-JUN-81 2450 10 7788 SCOTT ANALYST 7566 19-APR-87 3000 20 7839 KING PRESIDENT 17-NOV-81 5000 10 7844 TURNER SALESMAN 7698 08-SEP-81 1500 0 30 7876 ADAMS CLERK 7788 23-MAY-87 1100 20 7900 JAMES CLERK 7698 03-DEC-81 950 30 7902 FORD ANALYST 7566 03-DEC-81 3000 20 7934 MILLER CLERK 7782 23-JAN-82 1300 10 14 rows selected.
NOTE：执行 recover 命令时，附加的 DELETE ARCHIVELOG 和 SKIP TABLESPACE 两个参数是可选参数：
- DELETE ARCHIVELOG：表示 RMAN 将在完成恢复后自动删除那些在恢复过程中产生的归档日志文件
- SKIP TABLESPACE：跳过指定表空间，比如临时表，当然临时表空间即使你不跳过它也不会恢复的
二、数据文件恢复
执行表空间的恢复时，数据库可以是mount状态，也可以是open状态。在执行恢复之前如果被操作的表空间未处于offline 状态，必须首先通过alter tablespace … offline 语句将其置为脱机。
1、OS下删除users表空间文件
[oracle@T1 pdb]$ ll total 814952 -rw-r----- 1 oracle oinstall 52436992 Jul 27 11:21 test.dbf -rw-r----- 1 oracle oinstall 377495552 Jul 27 13:21 sysaux01.dbf -rw-r----- 1 oracle oinstall 293609472 Jul 27 13:21 system01.dbf -rw-r----- 1 oracle oinstall 37756928 Jul 26 14:12 temp01.dbf -rw-r----- 1 oracle oinstall 104865792 Jul 27 13:21 undotbs01.dbf -rw-r----- 1 oracle oinstall 5251072 Jul 27 11:21 users01.dbf [oracle@T1 pdb]$ rm -rf test.dbf
2、离线表空间
SQL> alter tablespace test offline immediate; Tablespace altered.
3、RMAN恢复表空间
RMAN> report schema; Report of database schema for database with db_unique_name T1 List of Permanent Datafiles =========================== File Size(MB) Tablespace RB segs Datafile Name ---- -------- -------------------- ------- ------------------------ 1 930 SYSTEM YES /u01/app/oracle/oradata/T1/system01.dbf 3 680 SYSAUX NO /u01/app/oracle/oradata/T1/sysaux01.dbf 4 335 UNDOTBS1 YES /u01/app/oracle/oradata/T1/undotbs01.dbf 5 270 PDB$SEED:SYSTEM NO /u01/app/oracle/oradata/T1/pdbseed/system01.dbf 6 330 PDB$SEED:SYSAUX NO /u01/app/oracle/oradata/T1/pdbseed/sysaux01.dbf 7 5 USERS NO /u01/app/oracle/oradata/T1/users01.dbf 8 100 PDB$SEED:UNDOTBS1 NO /u01/app/oracle/oradata/T1/pdbseed/undotbs01.dbf 9 280 PDB:SYSTEM YES /u01/app/oracle/oradata/T1/pdb/system01.dbf 10 360 PDB:SYSAUX NO /u01/app/oracle/oradata/T1/pdb/sysaux01.dbf 11 100 PDB:UNDOTBS1 YES /u01/app/oracle/oradata/T1/pdb/undotbs01.dbf 12 5 PDB:USERS NO /u01/app/oracle/oradata/T1/pdb/users01.dbf 16 0 PDB:TEST NO /u01/app/oracle/oradata/T1/pdb/test.dbf List of Temporary Files ======================= File Size(MB) Tablespace Maxsize(MB) Tempfile Name ---- -------- -------------------- ----------- -------------------- 1 128 TEMP 32767 /u01/app/oracle/oradata/T1/temp01.dbf 2 36 PDB$SEED:TEMP 32767 /u01/app/oracle/oradata/T1/pdbseed/temp012021-07-08_10-25-45-165-AM.dbf 3 36 PDB:TEMP 32767 /u01/app/oracle/oradata/T1/pdb/temp01.dbf RMAN> restore datafile 16; Starting restore at 27-JUL-21 using channel ORA_DISK_1 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 00016 to /u01/app/oracle/oradata/T1/pdb/test.dbf channel ORA_DISK_1: reading from backup piece /u01/app/backup/backupset/T1_20210727_1079013283.bak channel ORA_DISK_1: piece handle=/u01/app/backup/backupset/T1_20210727_1079013283.bak tag=TAG20210727T135443 channel ORA_DISK_1: restored backup piece 1 channel ORA_DISK_1: restore complete, elapsed time: 00:00:01 Finished restore at 27-JUL-21 RMAN> recover datafile 16; Starting recover at 27-JUL-21 using channel ORA_DISK_1 starting media recovery media recovery complete, elapsed time: 00:00:00 Finished recover at 27-JUL-21
4、上线表空间
SQL> alter tablespace test online; Tablespace altered.
三、恢复控制文件
Oracle默认会在$ORACLE_BASE/oradata/CDB目录中创建服务器控制文件
假设已经还原了实例参数文件和启动了实例，还原控制文件时，一般过程是先设置oracle_sid和登录RMAN，然后设置 DBID，使RMAN知道需要查找哪一个数据库的控制文件。
如果使用默认的位置来存储控制文件的自动备份，就可以简单地执行：restore controlfile from autobackup，这样 RMAN 就可以查找包含最新控制文件的控制文件备份集。一旦恢复了控制文件，就必须关闭重启数据库实例。如果使用的是非默认位置，就需要分配一个指向该位置的通道，然后再使用相同的方法来还原控制文件。
1、关闭数据库&删除控制文件
rm -rf /u01/app/oracle/oradata/T1/control01.ctl rm -rf /u01/app/oracle/fast_recovery_area/T1/control02.ctl
2、恢复控制文件
先把数据库启动到nomount状态
SQL> startup nomount ORACLE instance started. Total System Global Area 2516582152 bytes Fixed Size 9137928 bytes Variable Size 536870912 bytes Database Buffers 1962934272 bytes Redo Buffers 7639040 bytes
目标数据库控制文件丢失，无法启动到 mount 状态，此处必须首先指定 DBID。
要获得目标数据库的 DBID，可以通过多种方式查询，如我们创建自动备份时，如果没有更改其命名方式，文件名中会包含 DBID；或者查看之前生成的 RMAN 备份日志，当使用 RMAN 登录目录数据库后，最先输出的信息中就会显示出目标数据库的 DBID；或者连接到目标端数据库之后，查询v$database 视图也可以获得。
RMAN> set dbid 2888150031 executing command: SET DBID
这里从autobackup默认位置恢复控制文件报错，是因为之前备份没有使用默认路径
RMAN> restore controlfile from autobackup; Starting restore at 27-JUL-21 using target database control file instead of recovery catalog allocated channel: ORA_DISK_1 channel ORA_DISK_1: SID=34 device type=DISK recovery area destination: /u01/app/oracle/fast_recovery_area database name (or database unique name) used for search: T1 channel ORA_DISK_1: no AUTOBACKUPS found in the recovery area channel ORA_DISK_1: looking for AUTOBACKUP on day: 20210727 channel ORA_DISK_1: looking for AUTOBACKUP on day: 20210726 channel ORA_DISK_1: looking for AUTOBACKUP on day: 20210725 channel ORA_DISK_1: looking for AUTOBACKUP on day: 20210724 channel ORA_DISK_1: looking for AUTOBACKUP on day: 20210723 channel ORA_DISK_1: looking for AUTOBACKUP on day: 20210722 channel ORA_DISK_1: looking for AUTOBACKUP on day: 20210721 channel ORA_DISK_1: no AUTOBACKUP in 7 days found RMAN-00571: =========================================================== RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS =============== RMAN-00571: =========================================================== RMAN-03002: failure of restore command at 07/27/2021 14:26:23 RMAN-06172: no AUTOBACKUP found or specified handle is not a valid copy or piece
手动指定备份存在的位置
RMAN> restore controlfile from '/u01/app/backup/backupset/c-2888150031-20210727-07.ctl'; Starting restore at 27-JUL-21 using channel ORA_DISK_1 channel ORA_DISK_1: restoring control file channel ORA_DISK_1: restore complete, elapsed time: 00:00:01 output file name=/u01/app/oracle/oradata/T1/control01.ctl output file name=/u01/app/oracle/fast_recovery_area/T1/control02.ctl Finished restore at 27-JUL-21
3、启动数据库
RMAN> alter database mount; released channel: ORA_DISK_1 Statement processed RMAN> recover database; Starting recover at 27-JUL-21 Starting implicit crosscheck backup at 27-JUL-21 allocated channel: ORA_DISK_1 channel ORA_DISK_1: SID=34 device type=DISK Crosschecked 29 objects Finished implicit crosscheck backup at 27-JUL-21 Starting implicit crosscheck copy at 27-JUL-21 using channel ORA_DISK_1 Finished implicit crosscheck copy at 27-JUL-21 searching for all files in the recovery area cataloging files... no files cataloged using channel ORA_DISK_1 starting media recovery archived log for thread 1 with sequence 14 is already on disk as file /u01/app/oracle/oradata/T1/redo02.log archived log file name=/u01/app/oracle/oradata/T1/redo02.log thread=1 sequence=14 media recovery complete, elapsed time: 00:00:00 Finished recover at 27-JUL-21 RMAN> alter database open resetlogs; Statement processed
四、不完全恢复
1、基于时间的不完全恢复：
基于时间恢复是指当出现用户错误（例如误删除表、误截断表）时，恢复到指定时间点的恢复。执行RMAN，启动数据库到 mount 状态。使用 set until time 命令指定要恢复到的时间点。
14：43删除的表，我们恢复到14：42分时候的状态
RMAN> run{ 2> set until time="to_date('2021-07-27 14:42:00','yyyy-mm-dd hh24:mi:ss')"; 3> restore pluggable database pdb; 4> recover pluggable database pdb; 5> sql 'alter pluggable database pdb open resetlogs'; 6> } executing command: SET until clause Starting restore at 27-JUL-21 using channel ORA_DISK_1 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 /u01/app/oracle/oradata/T1/pdb/system01.dbf channel ORA_DISK_1: restoring datafile 00010 to /u01/app/oracle/oradata/T1/pdb/sysaux01.dbf channel ORA_DISK_1: restoring datafile 00011 to /u01/app/oracle/oradata/T1/pdb/undotbs01.dbf channel ORA_DISK_1: restoring datafile 00012 to /u01/app/oracle/oradata/T1/pdb/users01.dbf channel ORA_DISK_1: restoring datafile 00016 to /u01/app/oracle/oradata/T1/pdb/test.dbf channel ORA_DISK_1: reading from backup piece /u01/app/backup/backupset/T1_20210727_1079013283.bak channel ORA_DISK_1: piece handle=/u01/app/backup/backupset/T1_20210727_1079013283.bak tag=TAG20210727T135443 channel ORA_DISK_1: restored backup piece 1 channel ORA_DISK_1: restore complete, elapsed time: 00:00:03 Finished restore at 27-JUL-21 Starting recover at 27-JUL-21 using channel ORA_DISK_1 starting media recovery archived log for thread 1 with sequence 14 is already on disk as file /u01/app/oracle_archive/1_14_1077358929.dbf archived log for thread 1 with sequence 1 is already on disk as file /u01/app/oracle_archive/1_1_1079015407.dbf media recovery complete, elapsed time: 00:00:00 Finished recover at 27-JUL-21 sql statement: alter pluggable database pdb open resetlogs
查看表是否回来了
SQL> select count(*) from test; COUNT(*) ---------- 14
2、基于SCN不完全恢复
查看当前的SCN号：
SQL> select current_scn from v$database; CURRENT_SCN ----------- 2593307
删表
SQL> drop table test; Table dropped.
RMAN恢复
RMAN> run{ 2> set until scn=2593307; 3> restore pluggable database pdb; 4> recover pluggable database pdb; 5> sql 'alter pluggable database pdb open resetlogs'; 6> } executing command: SET until clause Starting restore at 27-JUL-21 using channel ORA_DISK_1 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 /u01/app/oracle/oradata/T1/pdb/system01.dbf channel ORA_DISK_1: restoring datafile 00010 to /u01/app/oracle/oradata/T1/pdb/sysaux01.dbf channel ORA_DISK_1: restoring datafile 00011 to /u01/app/oracle/oradata/T1/pdb/undotbs01.dbf channel ORA_DISK_1: restoring datafile 00012 to /u01/app/oracle/oradata/T1/pdb/users01.dbf channel ORA_DISK_1: restoring datafile 00016 to /u01/app/oracle/oradata/T1/pdb/test.dbf channel ORA_DISK_1: reading from backup piece /u01/app/backup/backupset/T1_20210727_1079013283.bak channel ORA_DISK_1: piece handle=/u01/app/backup/backupset/T1_20210727_1079013283.bak tag=TAG20210727T135443 channel ORA_DISK_1: restored backup piece 1 channel ORA_DISK_1: restore complete, elapsed time: 00:00:03 Finished restore at 27-JUL-21 Starting recover at 27-JUL-21 current log archived using channel ORA_DISK_1 starting media recovery archived log for thread 1 with sequence 14 is already on disk as file /u01/app/oracle_archive/1_14_1077358929.dbf archived log for thread 1 with sequence 1 is already on disk as file /u01/app/oracle_archive/1_1_1079015407.dbf archived log for thread 1 with sequence 2 is already on disk as file /u01/app/oracle_archive/1_2_1079015407.dbf media recovery complete, elapsed time: 00:00:00 Finished recover at 27-JUL-21 sql statement: alter pluggable database pdb open resetlogs
查询表是否回来了
SQL> select count(*) from test; COUNT(*) ---------- 14
3、基于日志序列号的不完全恢复
基于日志序列号恢复是指恢复数据库到指定日志序列号的状态。
可以通过下面的语句来查询当前的日志序列号。
SQL> archive log list; Database log mode Archive Mode Automatic archival Enabled Archive destination /u01/app/oracle_archive Oldest online log sequence 1 Next log sequence to archive 3 Current log sequence 3
切换日志
SQL> alter system switch logfile; System altered.
再次查看日志序列号&删除test表
SQL> archive log list; Database log mode Archive Mode Automatic archival Enabled Archive destination /u01/app/oracle_archive Oldest online log sequence 2 Next log sequence to archive 4 Current log sequence 4 SQL> drop table test; Table dropped.
RMAN恢复
run{ set until sequence=3; restore pluggable database pdb; recover pluggable database pdb; sql 'alter pluggable database pdb open resetlogs'; 6> }
查看表
SQL> select count(*) from test; COUNT(*) ---------- 14
Note：
- RMAN也可以实现单个表空间或数据文件的恢复，恢复过程可以在mount下或open方式下，如果在open方式下恢复，可以减少down机时间。
- 如果损坏的是一个数据文件，建议 offline 并在open 方式下恢复。
- 这里可以看到，RMAN进行数据文件与表空间恢复的时候，代码都比较简单，而且能保证备份与恢复的可靠性，所以建议采用 RMAN的备份与恢复。
