Oracle 备份与恢复--数据文件--控制文件恢复

一、使用rman 的backup database

备份了数据文件、控制文件和spfile文件，备份位置在闪回区

[oracle@oracle11 ~]$ rman target /

Recovery Manager: Release 11.2.0.4.0 - Production on Sun Mar 21 23:01:15 2021

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

connected to target database: ORCL (DBID=1593839605)

RMAN> backup database;

Starting backup at 2021-03-21 23:01:22
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=19 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=00001 name=/data/oracle/oradata/orcl/system01.dbf
input datafile file number=00002 name=/data/oracle/oradata/orcl/sysaux01.dbf
input datafile file number=00005 name=/data/oracle/oradata/orcl/example01.dbf
input datafile file number=00003 name=/data/oracle/oradata/orcl/undotbs01.dbf
input datafile file number=00004 name=/data/oracle/oradata/orcl/users01.dbf
channel ORA_DISK_1: starting piece 1 at 2021-03-21 23:01:23
channel ORA_DISK_1: finished piece 1 at 2021-03-21 23:02:48
piece handle=/data/oracle/fast_recovery_area/ORCL/backupset/2021_03_21/o1_mf_nnndf_TAG20210321T230123_j5gqy3wo_.bkp tag=TAG20210321T230123 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:01:25
channel ORA_DISK_1: starting full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
including current control file in backup set
including current SPFILE in backup set
channel ORA_DISK_1: starting piece 1 at 2021-03-21 23:02:50
channel ORA_DISK_1: finished piece 1 at 2021-03-21 23:02:51
piece handle=/data/oracle/fast_recovery_area/ORCL/backupset/2021_03_21/o1_mf_ncsnf_TAG20210321T230123_j5gr0tg0_.bkp tag=TAG20210321T230123 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01
Finished backup at 2021-03-21 23:02:51

RMAN>
 

如何恢复数据库，全库备份的恢复，数据库要在mount状态下执行，删除system01.dbf，使用RMAN恢复

1.关闭数据库

shutdown immediate

2.删除系统数据文件

rm -rf system01.dbf

3.启动数据库失败，此时数据库无法启动了，因为删除了system01.dbf

SQL> startup
ORACLE instance started.

Total System Global Area 1670221824 bytes
Fixed Size                  2253824 bytes
Variable Size            1073744896 bytes
Database Buffers          587202560 bytes
Redo Buffers                7020544 bytes
Database mounted.
ORA-01157: cannot identify/lock data file 1 - see DBWR trace file
ORA-01110: data file 1: '/data/oracle/oradata/orcl/system01.dbf'
SQL>
4.查看数据当前状态为mount,因为控制文件没坏，mount就是加载控制文件，连接RMAN

SQL> select status from v$instance;

STATUS
------------
MOUNTED

SQL>
5.恢复数据库

restore database;#重建数据库的物理文件:全部

[oracle@oracle11 orcl]$ rman target /

Recovery Manager: Release 11.2.0.4.0 - Production on Sun Mar 21 23:13:46 2021

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

connected to target database: ORCL (DBID=1593839605, not open)

RMAN> restore database;

Starting restore at 2021-03-21 23:13:54
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=245 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 /data/oracle/oradata/orcl/system01.dbf
channel ORA_DISK_1: restoring datafile 00002 to /data/oracle/oradata/orcl/sysaux01.dbf
channel ORA_DISK_1: restoring datafile 00003 to /data/oracle/oradata/orcl/undotbs01.dbf
channel ORA_DISK_1: restoring datafile 00004 to /data/oracle/oradata/orcl/users01.dbf
channel ORA_DISK_1: restoring datafile 00005 to /data/oracle/oradata/orcl/example01.dbf
channel ORA_DISK_1: reading from backup piece /data/oracle/fast_recovery_area/ORCL/backupset/2021_03_21/o1_mf_nnndf_TAG20210321T230123_j5gqy3wo_.bkp
channel ORA_DISK_1: piece handle=/data/oracle/fast_recovery_area/ORCL/backupset/2021_03_21/o1_mf_nnndf_TAG20210321T230123_j5gqy3wo_.bkp tag=TAG20210321T230123
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:45
Finished restore at 2021-03-21 23:14:40

RMAN>
recover database;#同步恢复

RMAN> recover database;

Starting recover at 2021-03-21 23:16:45
using channel ORA_DISK_1

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

Finished recover at 2021-03-21 23:16:47

RMAN>
 

打开数据库

alter database open resetlogs;  #两个错误

RMAN> alter database open resetlogs;

RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of alter db command at 03/21/2021 23:18:17
ORA-01139: RESETLOGS option only valid after an incomplete database recovery 

RMAN>

不需要resetlogs,-----RESETLOGS option only valid after an incomplete database recovery ，只有非完全恢复时才用。

RMAN> alter database open;

using target database control file instead of recovery catalog
database opened

RMAN>
数据库状态是打开状态，恢复正常。

SQL> select status from v$instance;
STATUS
------------
OPEN
SQL>

控制文件的备份与恢复有两种情况

一、数据在关闭的时候删除了控制文件恢复：

1）备份控制文件

RMAN> configure controlfile autobackup on;
new RMAN configuration parameters:
CONFIGURE CONTROLFILE AUTOBACKUP ON;
new RMAN configuration parameters are successfully stored
RMAN> backup tablespace example; #会自动备份控制文件和spfile文件在开启了控制文件自动备份的时候。

Finished backup at 2021-03-22 00:28:28
Starting Control File and SPFILE Autobackup at 2021-03-22 00:28:28
piece handle=/data/oracle/fast_recovery_area/ORCL/autobackup/2021_03_22/o1_mf_s_1067819308_j5gx1f6s_.bkp comment=NONE
Finished Control File and SPFILE Autobackup at 2021-03-22 00:28:29

2）关闭数据库，删除控制文件，然后启动数据库

shutdown immediate

rm -rf control*

startup 

ORA-00205: error in identifying control file, check alert log for more info----------------控制文件丢失了，所以数据库是启动不了了；那么现在就恢复控制文件；

3)恢复控制文件，所有控制文件都自动恢复

RMAN> restore controlfile from autobackup;
Starting restore at 2021-03-22 00:32:43
using channel ORA_DISK_1
recovery area destination: /data/oracle/fast_recovery_area
database name (or database unique name) used for search: ORCL
channel ORA_DISK_1: AUTOBACKUP /data/oracle/fast_recovery_area/ORCL/autobackup/2                                                                                                             021_03_22/o1_mf_s_1067819406_j5gx4gdw_.bkp found in the recovery area
AUTOBACKUP search with format "%F" not attempted because DBID was not set
channel ORA_DISK_1: restoring control file from AUTOBACKUP /data/oracle/fast_rec                                                                                                             overy_area/ORCL/autobackup/2021_03_22/o1_mf_s_1067819406_j5gx4gdw_.bkp
channel ORA_DISK_1: control file restore from AUTOBACKUP complete
output file name=/data/oracle/oradata/orcl/control01.ctl
output file name=/data/oracle/oradata/orcl/control02.ctl
output file name=/data/oracle/oradata/orcl/control03.ctl
output file name=/data/oracle/fast_recovery_area/orcl/control04.ctl
Finished restore at 2021-03-22 00:32:44

4）关闭数据库，再次启动数据库

shutdown immediate

startup

ORA-01589: must use RESETLOGS or NORESETLOGS option for database open----------此时就得做一次不完全恢复

SQL> startup
ORACLE instance started.

Total System Global Area 1670221824 bytes
Fixed Size                  2253824 bytes
Variable Size            1073744896 bytes
Database Buffers          587202560 bytes
Redo Buffers                7020544 bytes
Database mounted.
ORA-01589: must use RESETLOGS or NORESETLOGS option for database open
SQL> alter database open resetlogs;
alter database open resetlogs
*
ERROR at line 1:
ORA-01152: file 1 was not restored from a sufficiently old backup
ORA-01110: data file 1: '/data/oracle/oradata/orcl/system01.dbf'

在打开数据库的时候出现上面的错误，解决过程如下：
错误原因分析：
是由于controlfile里所记录的scn与datafile里的scn不一致。比如从备份里restore出的controlfile上的scn < datafile上所记录的scn，因此理论上二种思路：
1、以old controlfile为准的，datafile上的scn是新的，这样存在着数据的不一致，要继续恢复下去，将datafile上的scn也要restore到与controlfile一致的情况，
  但这样会丢失datafile上的数据。
2、就是以datafile上的scn为基准，将controlfile恢复到与datafile scn一致。

于是，在controlfile控制文件restore过后如果遭遇到ORA-01152之类的问题，可以这样来操作(实际上就是上面第二种思路的实现)：
RMAN>RECOVER DATABASE;　---找出同步controlfile scn和datafile scn所需的archivelog，如果归档目录缺少所列出log就从备份里
  ---(比如说是在磁带里)取出来并放回归档目录，比如说所列的archivelog是1_20.dbf - 1_21.dbf。

查找当前归档序号：21
SQL> archive log list
Database log mode              Archive Mode
Automatic archival             Enabled
Archive destination            USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence     19
Next log sequence to archive   21
Current log sequence           21
SQL> select status from

恢复到当前最新的归档日志序号---21
run{
allocate channel d1 type disk;
allocate channel d2 type disk;
set until sequence 21 thread 1;--注意这里指定为1_22，大于之前的1_21,这样下面的操作就没问题
recover database;
release channel d1;
release channel d2;
}

这样就恢复出一致性的数据，然后用open resetlogs打开数据即可，但记得resetlogs后应该全备一次当前数据库。当然，如果不愿意用resetlogs后，重建controlfile后，用noresetlogs也是ok的。
此方法同样适合于RAC数据库的恢复测试时遇到的ORA-01152错误。

全备命令：

backup database

删除过期归档命令：

RMAN> restore database;

Starting restore at 2021-03-22 00:44:17
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=10 device type=DISK

RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of restore command at 03/22/2021 00:44:17
RMAN-06026: some targets not found - aborting restore
RMAN-06023: no backup or copy of datafile 4 found to restore
RMAN-06023: no backup or copy of datafile 3 found to restore
RMAN-06023: no backup or copy of datafile 2 found to restore
RMAN-06023: no backup or copy of datafile 1 found to restore

RMAN> recover database;
Starting recover at 2021-03-22 00:44:37
using channel ORA_DISK_1
starting media recovery
archived log for thread 1 with sequence 21 is already on disk as file /data/oracle/oradata/orcl/redo03.log
archived log file name=/data/oracle/oradata/orcl/redo03.log thread=1 sequence=21
media recovery complete, elapsed time: 00:00:00
Finished recover at 2021-03-22 00:44:38
RMAN> alter database open resetlogs;
database opened
RMAN>

数据库成功恢复控制文件。

 

二、数据库在打开的时候删除了控制文件

1)备份

backup current controlfile

2)关闭数据库，然后启动数据库

SQL>shutdonw abort

[oracle@oracle11 ~]$ rman target /

Recovery Manager: Release 11.2.0.4.0 - Production on Mon Mar 22 01:19:16 2021

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

connected to target database (not started)

RMAN> startup nomount

Oracle instance started

Total System Global Area    1670221824 bytes

Fixed Size                     2253824 bytes
Variable Size               1073744896 bytes
Database Buffers             587202560 bytes
Redo Buffers                   7020544 bytes

RMAN> restore controlfile from autobackup;

Starting restore at 2021-03-22 01:19:44
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=246 device type=DISK

recovery area destination: /data/oracle/fast_recovery_area
database name (or database unique name) used for search: ORCL
channel ORA_DISK_1: AUTOBACKUP /data/oracle/fast_recovery_area/ORCL/autobackup/2021_03_22/o1_mf_s_1067822244_j5gzx49w_.bkp found in the recovery area
AUTOBACKUP search with format "%F" not attempted because DBID was not set
channel ORA_DISK_1: restoring control file from AUTOBACKUP /data/oracle/fast_recovery_area/ORCL/autobackup/2021_03_22/o1_mf_s_1067822244_j5gzx49w_.bkp
channel ORA_DISK_1: control file restore from AUTOBACKUP complete
output file name=/data/oracle/oradata/orcl/control01.ctl
output file name=/data/oracle/oradata/orcl/control02.ctl
output file name=/data/oracle/oradata/orcl/control03.ctl
output file name=/data/oracle/fast_recovery_area/orcl/control04.ctl
Finished restore at 2021-03-22 01:19:46
重启数据库

RMAN> shutdown immediate

Oracle instance shut down

RMAN> startup

connected to target database (not started)
Oracle instance started
database mounted
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of startup command at 03/22/2021 01:20:56
ORA-01589: must use RESETLOGS or NORESETLOGS option for database open

RMAN> alter database open resetlogs;

RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of alter db command at 03/22/2021 01:21:09
ORA-01194: file 1 needs more recovery to be consistent    #数据库开启状态删除控制导致恢复时提示不一致，和关闭有区别，但做法都一样，更新控制文件的scn号
ORA-01110: data file 1: '/data/oracle/oradata/orcl/system01.dbf'
 

RMAN执行下面命令：

run{
allocate channel d1 type disk;
allocate channel d2 type disk;
set until sequence 4 thread 1;--注意这里指定为1_5，大于之前的1_4,这样下面的操作就没问题
recover database;
release channel d1;
release channel d2;
 }

RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of recover command at 03/22/2021 01:23:01
RMAN-06556: datafile 5 must be restored from backup older than SCN 1242677

数据文件5的版本号得恢复到1242677版本。

还是失败，于是查找RMAN-06556的错误解决方法，此错误是由于datafile 1 必须只能从SCN比1242677旧的备份中还原出来，此时还是可以
通过时间点来进行还原，可以采用SCN来确定还原的时间点，此时时间就是 SCN=1242677的所在时刻
run{
set until scn 1242677;
restore database;
recover database;
alter database open resetlogs;
 }

using channel ORA_DISK_1
starting media recovery
archived log for thread 1 with sequence 4 is already on disk as file /data/oracle/oradata/orcl/redo01.log
archived log file name=/data/oracle/oradata/orcl/redo01.log thread=1 sequence=4
media recovery complete, elapsed time: 00:00:01
Finished recover at 2021-03-22 01:24:26
database opened

恢复成功

查看当前SCN
SQL> select current_scn from v$database;
CURRENT_SCN
-----------
    1244322
SQL>

 

做不完全恢复必须使用resetlogs, resetlogs也可以做完全恢复。而noresetlogs则是必须做完全恢复时使用。resetlogs会重置日志序列号强制清空或重建REDO，而noresetlogs则不会这么做。

什么叫SCN?

SCN：system change number
SCN是用于标示数据库变化的唯一标识号，其数值顺序递增。当执行事务时（DML语句、DDL语句），系统会为每个事务变化生成相应的SCN。当备份数据文件时，备份文件会记录备份点的SCN值，当恢复时，oracle会从备份点的SCN值开始恢复，一直到失败点的SCN值
sequence：日志序列号
日志序列号是重做日志的使用标识号，其数值也是顺序递增的。当进行日志切换时，日志序列号会自动增加1