环境:
OS:centos7.3
DB:oracle11.2.0.4
1.确认数据库是否处于归档模式;
centos@SYS> archive log list; Database log mode Archive Mode Automatic archival Enabled Archive destination USE_DB_RECOVERY_FILE_DEST Oldest online log sequence 1 Next log sequence to archive 1 Current log sequence 1 centos@SYS>
2.确认实验对象状态;
centos@SCOTT> select * from t1; ID NAME PHONE ---------- ---------- -------------------- 1 beijing 2 tianjin 3 shanxi 4 shandong centos@SCOTT>
3.使用RMAN备份数据库;
[oracle@centos ~]$ rman target / Recovery Manager: Release 11.2.0.4.0 - Production on Mon Apr 3 20:55:46 2017 Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved. connected to target database: CENTOS (DBID=3235228290) RMAN> backup database plus archivelog; Starting backup at 03-APR-17 current log archived using channel ORA_DISK_1 channel ORA_DISK_1: starting archived log backup set channel ORA_DISK_1: specifying archived log(s) in backup set input archived log thread=1 sequence=4 RECID=35 STAMP=940351582 channel ORA_DISK_1: starting piece 1 at 03-APR-17 channel ORA_DISK_1: finished piece 1 at 03-APR-17 piece handle=/u01/app/oracle/flash/centos_db16s0pmp9_1_1.rman tag=TAG20170403T205713 comment=NONE channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01 channel ORA_DISK_1: starting archived log backup set channel ORA_DISK_1: specifying archived log(s) in backup set input archived log thread=1 sequence=1 RECID=36 STAMP=940366633 channel ORA_DISK_1: starting piece 1 at 03-APR-17 channel ORA_DISK_1: finished piece 1 at 03-APR-17 piece handle=/u01/app/oracle/flash/centos_db17s0pmpa_1_1.rman tag=TAG20170403T205713 comment=NONE channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01 Finished backup at 03-APR-17 Starting backup at 03-APR-17 using channel ORA_DISK_1 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=/u01/app/oracle/oradata/centos/system01.dbf input datafile file number=00002 name=/u01/app/oracle/oradata/centos/sysaux01.dbf input datafile file number=00005 name=/u01/app/oracle/oradata/centos/example01.dbf input datafile file number=00003 name=/u01/app/oracle/oradata/centos/undotbs01.dbf input datafile file number=00006 name=/u01/app/oracle/oradata/centos/t1.dbf input datafile file number=00007 name=/u01/app/oracle/oradata/centos/t2.dbf input datafile file number=00004 name=/u01/app/oracle/oradata/centos/users01.dbf channel ORA_DISK_1: starting piece 1 at 03-APR-17 channel ORA_DISK_1: finished piece 1 at 03-APR-17 piece handle=/u01/app/oracle/flash/centos_db18s0pmpb_1_1.rman tag=TAG20170403T205715 comment=NONE channel ORA_DISK_1: backup set complete, elapsed time: 00:00:07 Finished backup at 03-APR-17 Starting backup at 03-APR-17 current log archived using channel ORA_DISK_1 channel ORA_DISK_1: starting archived log backup set channel ORA_DISK_1: specifying archived log(s) in backup set input archived log thread=1 sequence=2 RECID=37 STAMP=940366642 channel ORA_DISK_1: starting piece 1 at 03-APR-17 channel ORA_DISK_1: finished piece 1 at 03-APR-17 piece handle=/u01/app/oracle/flash/centos_db19s0pmpj_1_1.rman tag=TAG20170403T205723 comment=NONE channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01 Finished backup at 03-APR-17 Starting Control File and SPFILE Autobackup at 03-APR-17 piece handle=/u01/app/oracle/flash/CENTOS/autobackup/2017_04_03/o1_mf_s_940366644_dg4kon7z_.bkp comment=NONE Finished Control File and SPFILE Autobackup at 03-APR-17 RMAN>
4.查看当前SCN号并重启数据库至mount状态;
centos@SYS> select CURRENT_SCN from v$database; CURRENT_SCN ----------- 1352013 centos@SYS> conn scott/tiger Connected. Session altered. centos@SCOTT> drop table t1; Table dropped. centos@SCOTT> shutdown immediate ORA-01031: insufficient privileges centos@SCOTT> conn / as sysdba Connected. Session altered. centos@SYS> shutdown immediate; Database closed. Database dismounted. ORACLE instance shut down. centos@SYS> startup mount; ORACLE instance started. Total System Global Area 830930944 bytes Fixed Size 2257800 bytes Variable Size 587205752 bytes Database Buffers 239075328 bytes Redo Buffers 2392064 bytes Database mounted. centos@SYS>
5.使用RMAN基于SCN号恢复至1352013处;
RMAN> run { set until scn 1352013; restore database; recover database; alter database open resetlogs; }2> 3> 4> 5> 6> executing command: SET until clause Starting restore at 03-APR-17 using target database control file instead of recovery catalog allocated channel: ORA_DISK_1 channel ORA_DISK_1: SID=20 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/centos/system01.dbf channel ORA_DISK_1: restoring datafile 00002 to /u01/app/oracle/oradata/centos/sysaux01.dbf channel ORA_DISK_1: restoring datafile 00003 to /u01/app/oracle/oradata/centos/undotbs01.dbf channel ORA_DISK_1: restoring datafile 00004 to /u01/app/oracle/oradata/centos/users01.dbf channel ORA_DISK_1: restoring datafile 00005 to /u01/app/oracle/oradata/centos/example01.dbf channel ORA_DISK_1: restoring datafile 00006 to /u01/app/oracle/oradata/centos/t1.dbf channel ORA_DISK_1: restoring datafile 00007 to /u01/app/oracle/oradata/centos/t2.dbf channel ORA_DISK_1: reading from backup piece /u01/app/oracle/flash/centos_db18s0pmpb_1_1.rman channel ORA_DISK_1: piece handle=/u01/app/oracle/flash/centos_db18s0pmpb_1_1.rman tag=TAG20170403T205715 channel ORA_DISK_1: restored backup piece 1 channel ORA_DISK_1: restore complete, elapsed time: 00:00:07 Finished restore at 03-APR-17 Starting recover at 03-APR-17 using channel ORA_DISK_1 starting media recovery media recovery complete, elapsed time: 00:00:00 Finished recover at 03-APR-17 database opened RMAN>
6.验证数据是否恢复至正确的时刻;
centos@SYS> select status from v$instance; STATUS ------------ OPEN centos@SYS> select CURRENT_SCN from v$database; CURRENT_SCN ----------- 1352488 centos@SYS> conn scott/tiger Connected. Session altered. centos@SCOTT> select * from t1; ID NAME PHONE ---------- ---------- -------------------- 1 beijing 2 tianjin 3 shanxi 4 shandong centos@SCOTT>
至此使用RMAN基于SCN不完全恢复完成,使用恢复脚本如下:
run {
set until scn 1352013; ---->修改为需要恢复的SCN
restore database;
recover database;
alter database open resetlogs;
}