使用环境:
OS:centos7.3
DB:oracle11.2.0.4
在使用RMAN恢复数据库时,如需恢复的时间点明确知道,即可以使用时间点不完全恢复。
1.将数据库修改为归档状态;
centos@SYS> archive log list; Database log mode No Archive Mode Automatic archival Disabled Archive destination USE_DB_RECOVERY_FILE_DEST Oldest online log sequence 2 Current log sequence 4 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> alter database archivelog; Database altered. centos@SYS> alter database open; Database altered. centos@SYS>
2.使用RMAN对数据库进行备份;
[oracle@centos ~]$ rman target / Recovery Manager: Release 11.2.0.4.0 - Production on Mon Apr 3 16:29:26 2017 Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved. connected to target database: CENTOS (DBID=3235228290) RMAN> backup database; Starting backup at 03-APR-17 using target database control file instead of recovery catalog allocated channel: ORA_DISK_1 channel ORA_DISK_1: SID=36 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=/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_db11s0p73j_1_1.rman tag=TAG20170403T162939 comment=NONE channel ORA_DISK_1: backup set complete, elapsed time: 00:00:08 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_940350587_dg41zvg0_.bkp comment=NONE Finished Control File and SPFILE Autobackup at 03-APR-17 RMAN> list backup; List of Backup Sets =================== BS Key Type LV Size Device Type Elapsed Time Completion Time ------- ---- -- ---------- ----------- ------------ --------------- 22 Full 1.14G DISK 00:00:06 03-APR-17 BP Key: 22 Status: AVAILABLE Compressed: NO Tag: TAG20170403T092814 Piece Name: /u01/app/oracle/flash/centos_db0vs0oede_1_1.rman List of Datafiles in backup set 22 File LV Type Ckp SCN Ckp Time Name ---- -- ---- ---------- --------- ---- 1 Full 1321415 03-APR-17 /u01/app/oracle/oradata/centos/system01.dbf 2 Full 1321415 03-APR-17 /u01/app/oracle/oradata/centos/sysaux01.dbf 3 Full 1321415 03-APR-17 /u01/app/oracle/oradata/centos/undotbs01.dbf 4 Full 1321415 03-APR-17 /u01/app/oracle/oradata/centos/users01.dbf 5 Full 1321415 03-APR-17 /u01/app/oracle/oradata/centos/example01.dbf 6 Full 1321415 03-APR-17 /u01/app/oracle/oradata/centos/t1.dbf 7 Full 1321415 03-APR-17 /u01/app/oracle/oradata/centos/t2.dbf BS Key Type LV Size Device Type Elapsed Time Completion Time ------- ---- -- ---------- ----------- ------------ --------------- 23 Full 9.39M DISK 00:00:00 03-APR-17 BP Key: 23 Status: AVAILABLE Compressed: NO Tag: TAG20170403T092821 Piece Name: /u01/app/oracle/flash/CENTOS/autobackup/2017_04_03/o1_mf_s_940324964_dg399owc_.bkp SPFILE Included: Modification time: 03-APR-17 SPFILE db_unique_name: CENTOS Control File Included: Ckp SCN: 1321415 Ckp time: 03-APR-17 BS Key Type LV Size Device Type Elapsed Time Completion Time ------- ---- -- ---------- ----------- ------------ --------------- 24 Full 1.14G DISK 00:00:07 03-APR-17 BP Key: 24 Status: AVAILABLE Compressed: NO Tag: TAG20170403T162939 Piece Name: /u01/app/oracle/flash/centos_db11s0p73j_1_1.rman List of Datafiles in backup set 24 File LV Type Ckp SCN Ckp Time Name ---- -- ---- ---------- --------- ---- 1 Full 1339482 03-APR-17 /u01/app/oracle/oradata/centos/system01.dbf 2 Full 1339482 03-APR-17 /u01/app/oracle/oradata/centos/sysaux01.dbf 3 Full 1339482 03-APR-17 /u01/app/oracle/oradata/centos/undotbs01.dbf 4 Full 1339482 03-APR-17 /u01/app/oracle/oradata/centos/users01.dbf 5 Full 1339482 03-APR-17 /u01/app/oracle/oradata/centos/example01.dbf 6 Full 1339482 03-APR-17 /u01/app/oracle/oradata/centos/t1.dbf 7 Full 1339482 03-APR-17 /u01/app/oracle/oradata/centos/t2.dbf BS Key Type LV Size Device Type Elapsed Time Completion Time ------- ---- -- ---------- ----------- ------------ --------------- 25 Full 9.39M DISK 00:00:00 03-APR-17 BP Key: 25 Status: AVAILABLE Compressed: NO Tag: TAG20170403T162947 Piece Name: /u01/app/oracle/flash/CENTOS/autobackup/2017_04_03/o1_mf_s_940350587_dg41zvg0_.bkp SPFILE Included: Modification time: 03-APR-17 SPFILE db_unique_name: CENTOS Control File Included: Ckp SCN: 1339490 Ckp time: 03-APR-17 RMAN>
3.模拟数据库故障:表的误删除;
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> alter session set nls_date_format='yyyy-mm-dd hh24:mi:ss'; Session altered. centos@SCOTT> select sysdate from dual; SYSDATE ------------------- 2017-04-03 16:38:04 centos@SCOTT> truncate table t1; Table truncated. centos@SCOTT> select sysdate from dual; SYSDATE ------------------- 2017-04-03 16:38:44 centos@SCOTT> select * from t1; no rows selected centos@SCOTT>
4.使用RMAN将数据库恢复到t1表误删除之前的时间点2017-04-03 16:38:04;
centos@SCOTT> conn / as sysdba Connected. Session altered. centos@SYS> centos@SYS> 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>
[oracle@centos ~]$ rman target / Recovery Manager: Release 11.2.0.4.0 - Production on Mon Apr 3 16:46:06 2017 Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved. connected to target database: CENTOS (DBID=3235228290, not open) RMAN> run { allocate channel c1 type disk; allocate channel c2 type disk; sql 'alter session set nls_date_format="yyyy-mm-dd hh24:mi:ss"'; set until time = '2017-04-03 16:38:04'; restore database; recover database; alter database open resetlogs;}2> 3> 4> 5> 6> 7> 8> using target database control file instead of recovery catalog allocated channel: c1 channel c1: SID=20 device type=DISK allocated channel: c2 channel c2: SID=21 device type=DISK sql statement: alter session set nls_date_format="yyyy-mm-dd hh24:mi:ss" executing command: SET until clause Starting restore at 03-APR-17 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/centos/system01.dbf channel c1: restoring datafile 00002 to /u01/app/oracle/oradata/centos/sysaux01.dbf channel c1: restoring datafile 00003 to /u01/app/oracle/oradata/centos/undotbs01.dbf channel c1: restoring datafile 00004 to /u01/app/oracle/oradata/centos/users01.dbf channel c1: restoring datafile 00005 to /u01/app/oracle/oradata/centos/example01.dbf channel c1: restoring datafile 00006 to /u01/app/oracle/oradata/centos/t1.dbf channel c1: restoring datafile 00007 to /u01/app/oracle/oradata/centos/t2.dbf channel c1: reading from backup piece /u01/app/oracle/flash/centos_db11s0p73j_1_1.rman channel c1: piece handle=/u01/app/oracle/flash/centos_db11s0p73j_1_1.rman tag=TAG20170403T162939 channel c1: restored backup piece 1 channel c1: restore complete, elapsed time: 00:00:07 Finished restore at 03-APR-17 Starting recover at 03-APR-17 starting media recovery media recovery complete, elapsed time: 00:00:00 Finished recover at 03-APR-17 database opened released channel: c1 released channel: c2 RMAN>
5.验证恢复效果;
centos@SYS> select status from v$instance; STATUS ------------ OPEN 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不完全恢复成功,使用的恢复脚本如下:
run {
allocate channel c1 type disk;
allocate channel c2 type disk;
sql 'alter session set nls_date_format="yyyy-mm-dd hh24:mi:ss"';
set until time = '2017-04-03 16:38:04'; ---->此处时间修改为所需时间即可
restore database;
recover database;
alter database open resetlogs;}
或:
run {
shutdown immediate;
startup mount;
set until time "to_date('2017-04-03 16:38:04','yyyy-mm-dd hh24:mi:ss')";
restore database;
recover database;
alter database open resetlogs;
}