恢复数据库到指定时间点

1、恢复参数文件

[oracle@dbcheck backupset]$ rman target /

将数据库启动到nomount状态并恢复spfile文件

RMAN> startup nomount

恢复参数文件

RMAN> restore spfile to '/home/oracle/spfileslb.ora' from '/home/oracle/backupset/spfile_7_954091606_1.bkp';

Starting restore at 07-SEP-17

allocated channel: ORA_DISK_1

channel ORA_DISK_1: SID=19 device type=DISK

channel ORA_DISK_1: restoring spfile from AUTOBACKUP /home/oracle/backupset/spfile_7_954091606_1.bkp

channel ORA_DISK_1: SPFILE restore from AUTOBACKUP complete

Finished restore at 07-SEP-17

2、关闭数据库，然后将恢复的参数文件放置到$ORACLE_HOME/dbs下并重启数据库到nomount状态

SQL> shutdown immediate

ORA-01507: database not mounted

ORACLE instance shut down.

移动恢复后的参数文件到相应目录

[oracle@dbcheck ~]$ mv /home/oracle/spfileslb.ora /home/oracle/app/oracle/product/11.2.0/db_1/dbs/

启动数据库到nomount状态

[oracle@dbcheck ~]$ sqlplus / as sysdba

SQL> startup nomount

ORACLE instance started.

Total System Global Area 830930944 bytes

Fixed Size            2257800 bytes

Variable Size          536874104 bytes

Database Buffers      289406976 bytes

Redo Buffers            2392064 bytes

3、恢复控制文件

查看控制文件所在位置，并恢复控制文件到指定位置

SQL> show parameter control_files

NAME                     TYPE   VALUE

------------------------------------ ----------- ------------------------------

control_files                 string   /home/oracle/app/oracle/oradat

                       a/slb/control01.ctl, /home/ora

                       cle/app/oracle/fast_recovery_a

                       rea/slb/control02.ctl

进入rman，使用备份集文件恢复控制文件并启动数据库到mount状态

[oracle@dbcheck ~]$ rman target /

RMAN> restore controlfile to '/home/oracle/app/oracle/oradata/slb/control01.ctl' from '/home/oracle/backupset/control_l1_15_1_954092575.bkp';

Starting restore at 07-SEP-17

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: restoring control file

channel ORA_DISK_1: restore complete, elapsed time: 00:00:01

Finished restore at 07-SEP-17

RMAN> restore controlfile to '/home/oracle/app/oracle/fast_recovery_area/slb/control02.ctl' from '/home/oracle/backupset/control_l1_15_1_954092575.bkp';

Starting restore at 07-SEP-17

using channel ORA_DISK_1

channel ORA_DISK_1: restoring control file

channel ORA_DISK_1: restore complete, elapsed time: 00:00:01

Finished restore at 07-SEP-17

将数据库启动到mount状态

RMAN> startup mount

database is already started

database mounted

released channel: ORA_DISK_1

4、查看数据库备份集

RMAN> list backupset;

List of Backup Sets

===================

BS Key Type LV Size       Device Type Elapsed Time Completion Time

------- ---- -- ---------- ----------- ------------ ---------------

1       Incr 0 70.37M     DISK        00:00:46     07-SEP-17

        BP Key: 1   Status: AVAILABLE Compressed: YES Tag: TAG20170907T172537

        Piece Name: /home/oracle/backupset/full_2_954091537_1.bkp

List of Datafiles in backup set 1

File LV Type Ckp SCN    Ckp Time Name

---- -- ---- ---------- --------- ----

2    0 Incr 971722     07-SEP-17 /home/oracle/app/oracle/oradata/slb/sysaux01.dbf

3    0 Incr 971722     07-SEP-17 /home/oracle/app/oracle/oradata/slb/undotbs01.dbf

......

5、还原数据库

RMAN> restore database;

Starting restore at 07-SEP-17

allocated channel: ORA_DISK_1

channel ORA_DISK_1: SID=18 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 00002 to /home/oracle/app/oracle/oradata/slb/sysaux01.dbf

channel ORA_DISK_1: restoring datafile 00003 to /home/oracle/app/oracle/oradata/slb/undotbs01.dbf

channel ORA_DISK_1: reading from backup piece /home/oracle/backupset/full_2_954091537_1.bkp

channel ORA_DISK_1: piece handle=/home/oracle/backupset/full_2_954091537_1.bkp tag=TAG20170907T172537

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 00001 to /home/oracle/app/oracle/oradata/slb/system01.dbf

channel ORA_DISK_1: restoring datafile 00004 to /home/oracle/app/oracle/oradata/slb/users01.dbf

channel ORA_DISK_1: reading from backup piece /home/oracle/backupset/full_1_954091537_1.bkp

channel ORA_DISK_1: piece handle=/home/oracle/backupset/full_1_954091537_1.bkp tag=TAG20170907T172537

channel ORA_DISK_1: restored backup piece 1

channel ORA_DISK_1: restore complete, elapsed time: 00:00:45

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/app/oracle/oradata/slb/slb01.dbf

channel ORA_DISK_1: reading from backup piece /home/oracle/backupset/full_l1_10_1_954092542.bkp

channel ORA_DISK_1: piece handle=/home/oracle/backupset/full_l1_10_1_954092542.bkp tag=TAG20170907T174222

channel ORA_DISK_1: restored backup piece 1

channel ORA_DISK_1: restore complete, elapsed time: 00:00:03

Finished restore at 07-SEP-17

6、恢复数据库到指定时间点

[oracle@dbcheck ~]$ sqlplus / as sysdba

SQL> recover database until time '2017-09-07 17:27:32' using BACKUP CONTROLFILE;

ORA-00279: change 971721 generated at 09/07/2017 17:25:37 needed for thread 1

ORA-00289: suggestion :

/home/oracle/app/oracle/fast_recovery_area/SLB/archivelog/2017_09_07/o1_mf_1_4d

v246m70.arc

ORA-00280: change 971721 for thread 1 is in sequence #4

Specify log: {<RET>=suggested | filename | AUTO | CANCEL}

一路回车即可

将数据库以resetlogs方式打开

SQL> alter database open resetlogs;

alter database open resetlogs

*

ERROR at line 1:

ORA-01248: file 5 was created in the future of incomplete recovery

ORA-01110: data file 5: '/home/oracle/app/oracle/oradata/slb/slb01.dbf'

由于我这边控制文件恢复的是最近的的控制文件备份集，恢复后包含恢复时间点之后的数据文件信息，当以resetlogs打开数据库时数据库会去寻找相应文件，所以会报错

此处将相应数据文件offline

SQL> alter database datafile 5 offline;

Database altered.

重新以resetlogs方式打开

SQL> alter database open resetlogs;

Database altered.